def _install(cls):
"""Install attribute meta data tables"""
return (
lambda: (
DBHelper().query("""
CREATE TABLE "attribute" (
"_id" INTEGER PRIMARY KEY AUTOINCREMENT,
"code" TEXT NOT NULL DEFAULT "",
"label" TEXT NOT NULL DEFAULT "",
"parent" TEXT NOT NULL DEFAULT "",
"type" TEXT NOT NULL DEFAULT ""
)
"""),
DBHelper().query("""
CREATE TABLE "attribute_group" (
"_id" INTEGER PRIMARY KEY AUTOINCREMENT,
"code" TEXT NOT NULL DEFAULT "",
"label" TEXT NOT NULL DEFAULT "",
"type" TEXT NOT NULL DEFAULT ""
)
"""),
DBHelper().query("""
CREATE TABLE "attribute_group_attribute" (
"_id" INTEGER PRIMARY KEY AUTOINCREMENT,
"group" TEXT NOT NULL DEFAULT "",
"attribute" TEXT NOT NULL DEFAULT "",
FOREIGN KEY ("group") REFERENCES "attribute_group"("_id")
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("attribute") REFERENCES "attribute"("_id")
ON DELETE CASCADE ON UPDATE CASCADE
)
""")
),
)
def install():
"""Install dependencies and tables"""
FileModel.install()
q = DBHelper.quote_identifier
thumb_table = q(ImageHelper._thumbnail_table)
file_table = q(FileModel._table)
file_table_pk = q(FileModel._pk)
InstallHelper.install('image', (
lambda: (
DBHelper().query("""
CREATE TABLE %s (
"_id" INTEGER PRIMARY KEY AUTOINCREMENT,
"file" INTEGER NOT NULL DEFAULT 0,
"width" INTEGER,
"height" INTEGER,
"thumbnail" TEXT,
FOREIGN KEY ("file") REFERENCES %s(%s)
ON DELETE CASCADE ON UPDATE CASCADE
)
""" % (thumb_table, file_table, file_table_pk)),
DBHelper().query("""
CREATE UNIQUE INDEX "UNQ_FILE_THUMB_SIZE"
ON %s("file", "width", "height")
""" % thumb_table),
DBHelper().query("""
CREATE INDEX "IDX_FILE_THUMB_WIDTH" ON %s("width")
""" % thumb_table),
DBHelper().query("""
CREATE INDEX "IDX_FILE_THUMB_HEIGHT" ON %s("height")
""" % thumb_table)
),
))
def test_install(self):
"""Test InstallHelper.install"""
if os.path.isfile('/tmp/box.db'):
os.unlink('/tmp/box.db')
DBHelper().set_db('/tmp/box.db')
InstallHelper.reset()
module = 'sample_module'
q = DBHelper.quote_identifier
install_routines = ["""
CREATE TABLE IF NOT EXISTS %s (
id INTEGER
)
""" % q(module)]
InstallHelper.install(module, install_routines)
self.assertIn('id', DBHelper().describe_table(module))
self.assertEqual(InstallHelper.version(module), 1)
install_routines.append(
lambda: DBHelper().insert(module, [{'id': 1}, {'id': 2}]))
InstallHelper.install(module, install_routines)
self.assertEqual(
DBSelect(module, {'c': 'COUNT(*)'}).query().fetchone()['c'],
2
)
self.assertEqual(InstallHelper.version(module), 2)
install_routines.append(
'ALTER TABLE %s ADD COLUMN %s TEXT' % (q(module), q('col')))
InstallHelper.install(module, install_routines)
self.assertIn('col', DBHelper().describe_table(module))
self.assertEqual(InstallHelper.version(module), 3)
InstallHelper.reset()
DBHelper().set_db(None)
os.unlink('/tmp/box.db')
def _create_attribute(cls, code, label, datatype, group):
"""Create a new attribute for this model"""
ids = DBHelper().insert('attribute', {
'code': code,
'label': label,
'parent': cls._table,
'type': datatype
})
group_id = cls._get_attribute_group(group, True)
for new_id in ids:
DBHelper().insert('attribute_group_attribute', {
'group': group_id,
'attribute': new_id
})
def tearDown(self):
"""Clean up after test"""
InstallHelper.reset()
DBHelper().set_db(None)
os.unlink('/tmp/box.db')
FileModel._group_index = {} # clear cached attr groups
def tearDown(self):
"""Clean up after test"""
InstallHelper.reset()
DBHelper().set_db(None)
os.unlink('/tmp/box.db')
LogHelper._model_installed = False
def install():
"""Install filter tables"""
FileModel.install()
q = DBHelper.quote_identifier
filter_table = q(FilterHelper._filter_table)
option_table = q(FilterHelper._filter_option_table)
value_table = q(FilterHelper._filter_value_table)
file_table = q(FileModel._table)
file_table_pk = q(FileModel._pk)
InstallHelper.install('filter', (lambda: (DBHelper().query("""
CREATE TABLE %s (
"_id" INTEGER PRIMARY KEY AUTOINCREMENT,
"code" TEXT NOT NULL DEFAULT '',
"label" TEXT NOT NULL DEFAULT ''
)
""" % filter_table), DBHelper().query("""
CREATE UNIQUE INDEX "UNQ_FILE_FILTER_CODE"
ON %s ("code")
""" % filter_table), DBHelper().query("""
CREATE TABLE %s (
"filter" INTEGER NOT NULL DEFAULT 0,
"value" INTEGER NOT NULL DEFAULT 0,
"label" TEXT NOT NULL DEFAULT '',
FOREIGN KEY ("filter") REFERENCES %s("_id")
ON DELETE CASCADE ON UPDATE CASCADE
)
""" % (option_table, filter_table)), DBHelper().query("""
CREATE UNIQUE INDEX "UNQ_FILE_FILTER_OPTION_FILTER_VALUE"
ON %s("filter", "value")
""" % option_table), DBHelper().query("""
CREATE TABLE %s (
"file" INTEGER NOT NULL DEFAULT 0,
"filter" INTEGER NOT NULL DEFAULT 0,
"value" INTEGER NOT NULL DEFAULT 0,
FOREIGN KEY ("file") REFERENCES %s(%s)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("filter") REFERENCES %s("_id")
ON DELETE CASCADE ON UPDATE CASCADE
)
""" % (value_table, file_table, file_table_pk, filter_table)),
DBHelper().query("""
CREATE UNIQUE INDEX "UNQ_FILE_FILTER_VALUE"
ON %s("file", "filter", "value")
""" % value_table), DBHelper().query("""
CREATE INDEX "IDX_FILE_FILTER_VALUE_FILE" ON %s("file")
""" % value_table), DBHelper().query("""
CREATE INDEX "IDX_FILE_FILTER_VALUE_FILTER" ON %s("filter")
""" % value_table), DBHelper().query("""
CREATE INDEX "IDX_FILE_FILTER_VALUE_VALUE" ON %s("value")
""" % value_table)), ))
def setUp(self):
"""LogHelper test set up"""
if os.path.isfile('/tmp/box.db'):
os.unlink('/tmp/box.db')
DBHelper().set_db('/tmp/box.db')
InstallHelper.reset()
LogHelper._model_installed = False
def setUp(self):
"""LogModel test set up"""
if os.path.isfile('/tmp/box.db'):
os.unlink('/tmp/box.db')
DBHelper().set_db('/tmp/box.db')
InstallHelper.reset()
LogModel.install()
def _initialize(cls, force=False):
"""Load version info from db"""
if cls._state and not force:
return
if not DBHelper().describe_table(cls._table):
DBHelper().query("""
CREATE TABLE %s (
"module" TEXT PRIMARY KEY,
"version" INTEGER NOT NULL DEFAULT 0
)
""" % DBHelper.quote_identifier(cls._table))
cls._state = {}
for module in DBSelect(cls._table).query().fetchall():
cls._state[module['module']] = module['version']
def add_file_thumbnail(cls, file_id, width, height, thumbnail):
""""""
DBHelper().insert(cls._thumbnail_table, {
'file': file_id,
'width': width,
'height': height,
'thumbnail': thumbnail
}, True)
def _install(cls):
"""Define install routines for this model"""
table = DBHelper.quote_identifier(cls._table)
return (lambda: (DBHelper().query("""
CREATE TABLE %s (
"_id" INTEGER PRIMARY KEY AUTOINCREMENT,
"serial" TEXT NOT NULL DEFAULT '',
"product_id" TEXT NOT NULL DEFAULT '',
"product_name" TEXT NOT NULL DEFAULT '',
"model" TEXT NOT NULL DEFAULT '',
"vendor_id" TEXT NOT NULL DEFAULT '',
"vendor_name" TEXT NOT NULL DEFAULT '',
"manufacturer" TEXT NOT NULL DEFAULT ''
)
""" % table), DBHelper().query(
'CREATE UNIQUE INDEX "UNQ_DEVICE_SERIAL" ON %s ("serial")' % table)
), )
def setUp(self):
"""Initiate DBHelper singleton"""
if os.path.isfile('/tmp/box.db'):
os.unlink('/tmp/box.db')
DBHelper().set_db('/tmp/box.db')
self._helper = DBHelper()
self._helper.query("""
CREATE TABLE "test_table" (
"col_a" INTEGER PRIMARY KEY AUTOINCREMENT,
"col_b" INTEGER
)
""")
self._num_rows = randint(128, 255)
for i in range(self._num_rows):
self._helper.query(
'INSERT INTO "test_table" ("col_b") VALUES (?)',
[randint(0, 99)]
)
def _install(cls):
"""Install base table and attributes"""
DeviceModel.install()
ExtendedModel.install()
table = DBHelper.quote_identifier(cls._table)
pk = DBHelper.quote_identifier(cls._pk)
return (
lambda:
(DBHelper().query("""
CREATE TABLE %s (
%s INTEGER PRIMARY KEY AUTOINCREMENT,
"name" TEXT NOT NULL DEFAULT '',
"abspath" TEXT NOT NULL DEFAULT '',
"extension" TEXT,
"type" TEXT NOT NULL DEFAULT '',
"subtype" TEXT NOT NULL DEFAULT '',
"charset" TEXT,
"checksum" TEXT NOT NULL DEFAULT '',
"size" INTEGER,
"created_at" INTEGER,
"indexed_at" INTEGER,
"is_hidden" INTEGER NOT NULL DEFAULT 0,
"rating" INTEGER NOT NULL DEFAULT 0,
"device" INTEGER,
"devpath" TEXT NOT NULL DEFAULT '',
FOREIGN KEY ("device") REFERENCES "device"("_id")
ON DELETE SET NULL ON UPDATE SET NULL
)
""" % (table, pk)), DBHelper().query("""
CREATE UNIQUE INDEX "UNQ_FILE_DUPLICATE"
ON %s ("name", "devpath", "checksum")
""" % table), cls._create_attribute_tables(),
cls._create_attribute('width', 'Width', 'integer', 'Image'),
cls._create_attribute('height', 'Height', 'integer', 'Image'),
cls._create_attribute('latitude', 'Latitude', 'real', 'Image'),
cls._create_attribute('longitude', 'Longitude', 'real', 'Image'),
cls._create_attribute('timestamp', 'Timestamp', 'integer', 'Image'
),
cls._create_attribute('orientation', 'Orientation', 'integer',
'Image')), )
def setUp(self):
"""BaseModel test set up"""
if os.path.isfile('/tmp/box.db'):
os.unlink('/tmp/box.db')
DBHelper().set_db('/tmp/box.db')
InstallHelper.reset()
cache = FileSystemCache('/tmp/werkzeug')
cache.clear()
BaseModel.set_cache(cache)
SampleModel.install()
def save(self):
"""Store this models data"""
data = self.clean_data(self._data)
if self.id():
self._db_select().query_update(data)
else:
ids = DBHelper().insert(self.get_table(), data)
self.set_data(self.__class__._pk, ids[0])
return self
def _install(cls):
"""Define install routines for this model"""
table = DBHelper.quote_identifier(cls._table)
pk = DBHelper.quote_identifier(cls._pk)
return (
lambda: (
DBHelper().query("""
CREATE TABLE %s (
%s INTEGER PRIMARY KEY AUTOINCREMENT,
"level" INTEGER NOT NULL DEFAULT %d,
"message" TEXT NOT NULL DEFAULT '',
"created_at" INTEGER NOT NULL DEFAULT (STRFTIME('%%s', 'now'))
)""" % (table, pk, cls.DEBUG)),
DBHelper().query(
'CREATE INDEX "IDX_LOG_LEVEL" ON %s ("level")'
% table),
DBHelper().query(
'CREATE INDEX "IDX_LOG_CREATED_AT" ON %s ("created_at")'
% table)
),
)
def _create_attribute_tables(cls):
"""Install attribute tables for this model"""
q = DBHelper.quote_identifier
for datatype in ('text', 'integer', 'real', 'blob'):
table_name = cls._get_attribute_table(datatype)
idx = '%s_ATTR_%s' % (cls._table.upper(), datatype.upper())
DBHelper().query("""
CREATE TABLE %s (
"_id" INTEGER PRIMARY KEY AUTOINCREMENT,
"attribute" INTEGER,
"parent" INTEGER,
"value" %s,
FOREIGN KEY ("attribute") REFERENCES "attribute"("_id")
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("parent") REFERENCES %s(%s)
ON DELETE CASCADE ON UPDATE CASCADE
);
""" % (q(table_name), datatype.upper(),
q(cls._table), q(cls._pk))
)
DBHelper().query("""
CREATE UNIQUE INDEX "UNQ_%s_ATTR_PARENT"
ON %s ("attribute", "parent");
""" % (idx, q(table_name))
)
DBHelper().query("""
CREATE INDEX "IDX_%s_ATTR" ON %s ("attribute");
""" % (idx, q(table_name))
)
DBHelper().query("""
CREATE INDEX "IDX_%s_PARENT" ON %s ("parent");
""" % (idx, q(table_name))
)
DBHelper().query("""
CREATE INDEX "IDX_%s_VALUE" ON %s ("value");
""" % (idx, q(table_name))
)
def _update_version(cls, module, version):
"""Update module version number"""
cls._initialize()
if module in cls._state:
DBSelect(cls._table
).where('module = ?', module
).query_update({'version': version})
else:
DBHelper().insert(cls._table, {
'module': module,
'version': version
})
cls._initialize(True)
def add_filter_option(cls, filter_id, value, label=None):
"""Add an option to the given filter"""
if cls.filter_has_option(filter_id, value):
return
filter_id = cls.get_filter_id(filter_id)
if not label:
label = str(value)
DBHelper().insert(cls._filter_option_table, {
'filter': filter_id,
'value': value,
'label': label
})
def create_filter(cls, code, label=None):
"""Create a new filter"""
if not label:
label = code.capitalize()
ids = DBHelper().insert(
cls._filter_table,
{
'code': code,
'label': label
},
True # ignore
)
return ids[0] if len(ids) > 0 else None
def clean_data(self, data):
"""Clean the given dict from non-savable data"""
cache_key = 'DESCRIBE_%s' % self._table
desc = self.__class__._cache.get(cache_key)
if type(desc) is not dict:
desc = DBHelper().describe_table(self._table)
self.__class__._cache.set(cache_key, desc)
clean_data = {}
for key in data.keys():
if key == self._pk:
continue
elif key in desc:
#TODO: type cast?
clean_data[key] = data[key]
return clean_data
def install(cls, module, routines):
"""Run module install/upgrade routines"""
if not routines:
return
version = cls.version(module)
if version >= len(routines):
return
while version < len(routines):
if isroutine(routines[version]):
routines[version]()
else:
DBHelper().query(routines[version])
version = version + 1
cls._update_version(module, version)
def _install(cls):
return (
# "Version #1" routine
"""
CREATE TABLE %s (
"_id" INTEGER PRIMARY KEY AUTOINCREMENT,
"some_attribute" INTEGER,
"another_attribute" TEXT
)
""" % DBHelper.quote_identifier(cls._table),
# "Version #2" routine
lambda: DBHelper().insert(SampleModel._table, (
{'_id': 1, 'some_attribute': 10, 'another_attribute': 'ten'},
{'_id': 2, 'some_attribute': 20, 'another_attribute': 'twenty'},
{'_id': 3, 'some_attribute': 30, 'another_attribute': 'thirty'}
)
)
)
def _install(cls):
"""Install base table and attributes"""
ExtendedModel.install()
table = DBHelper.quote_identifier(cls._table)
return (
lambda:
(DBHelper().query("""
CREATE TABLE %s (
"_id" INTEGER PRIMARY KEY AUTOINCREMENT,
"static" TEXT NOT NULL DEFAULT ''
)
""" % table), SampleExtModel._create_attribute_tables(),
SampleExtModel._create_attribute(
'sample_attr_1', 'Sample Attribute #1', 'integer', 'Default'),
SampleExtModel._create_attribute(
'sample_attr_2', 'Sample Attribute #2', 'text', 'Default'),
SampleExtModel._create_attribute(
'sample_attr_3', 'Sample Attribute #3', 'real', 'Not Default')
), )
def set_filter_values(cls, file_id, filter_id, values=None):
"""Set the values for the given file and filter"""
if isinstance(file_id, FileModel):
file_id = file_id.id()
filter_id = cls.get_filter_id(filter_id)
DBSelect(cls._filter_value_table).where('file = ?', file_id).where(
'filter = ?', filter_id).query_delete()
if values is None:
return
elif type(values) not in (list, tuple):
values = (values, )
for value in values:
DBHelper().insert(cls._filter_value_table, {
'file': file_id,
'filter': filter_id,
'value': value
})
def _get_attribute_group(cls, group_id, create=False):
"""Retrieve an attribute group id"""
if (cls._table in cls._group_index
and group_id in cls._group_index[cls._table]):
return cls._group_index[cls._table][group_id]
group_desc = {
'_id': group_id,
'code': str(group_id).replace(' ', '_'),
'label': str(group_id.replace('_', ' ')).capitalize()
}
cls._group_index[cls._table] = {}
for field in ('_id', 'code', 'label'):
select = DBSelect('attribute_group').where(
'%s = ?' % field, group_desc[field]).where(
'type = ?', cls._table).limit(1)
group = select.query().fetchone()
if type(group) is dict:
cls._group_index[cls._table][group_id] = group['_id']
return cls._group_index[cls._table][group_id]
if not create:
return None
ids = DBHelper().insert('attribute_group', {
'code': group_desc['code'],
'label': group_desc['label'],
'type': cls._table
})
if len(ids) > 0:
cls._group_index[cls._table][group_id] = ids[0]
return cls._group_index[cls._table][group_id]
return None
def tearDown(self):
"""Clean up after test"""
DBHelper().set_db(None)
os.unlink('/tmp/box.db')
try:
hour = int(
datetime.fromtimestamp(time['value']).strftime('%H'))
tod = 4 # Night
if hour >= 5 and hour <= 10:
tod = 0 # Morning
elif hour >= 11 and hour <= 13:
tod = 1 # Noon
elif hour >= 14 and hour <= 17:
tod = 2 # Afternoon
elif hour >= 18 and hour <= 21:
tod = 3 # Evening
FilterHelper.set_filter_values(time['parent'], filter_code,
tod)
except ValueError:
pass # invalid format
if (__name__ == '__main__'):
"""$ python filetime.py path/to/database"""
if len(sys.argv) < 2:
exit(1)
database = sys.argv[1]
DBHelper(database)
FileTimeIndexer.index_seasons()
FileTimeIndexer.index_time_of_day()
"""Log an info message"""
return LogHelper.log(message, LogModel.INFO)
@staticmethod
def debug(message):
"""Log a debug message"""
return LogHelper.log(message, LogModel.DEBUG)
if (__name__ == '__main__'):
"""$ python log.py path/to/database message level"""
if len(sys.argv) < 3:
exit(1)
try:
DBHelper(sys.argv[1])
except:
exit(2)
message = sys.argv[2]
if len(message) == 0:
exit(3)
level = None
if len(sys.argv) >= 4:
level_str = sys.argv[3][:2].lower()
if level_str == 'em':
level = LogModel.EMERG
elif level_str == 'al':
level = LogModel.ALERT
elif level_str == 'cr':
