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 _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 _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 _db_select(self, key=None):
"""Return a DBSelect querying for this model"""
if not key:
key = self.__class__._pk
where = "%s = ?" % (DBHelper.quote_identifier(key),)
return DBSelect(self.get_table()).where(where, self.get_data(key)).limit(1)
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):
"""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 _db_select(self, key=None):
"""Return a DBSelect querying for this model"""
if not key:
key = self.__class__._pk
where = '%s = ?' % (DBHelper.quote_identifier(key), )
return DBSelect(self.get_table()).where(where,
self.get_data(key)).limit(1)
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 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):
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"""
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 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 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 _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 _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 join_filter_values(cls, select, filter_id, file_field, columns=()):
"""Support method for indexers needed since
sqlite does not implement right join
"""
filter_id = cls.get_filter_id(filter_id)
file_field = DBHelper.quote_identifier(file_field)
select.left_join(
(cls._filter_value_table, 'fv'),
'fv.filter = %d AND %s = fv.file' % (filter_id, file_field),
columns)
return select
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 _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 join_filter_values(cls, select, filter_id, file_field, columns=()):
"""Support method for indexers needed since
sqlite does not implement right join
"""
filter_id = cls.get_filter_id(filter_id)
file_field = DBHelper.quote_identifier(file_field)
select.left_join(
(cls._filter_value_table, 'fv'),
'fv.filter = %d AND %s = fv.file' % (filter_id, file_field),
columns
)
return select
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 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 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):
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 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 _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 _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 _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 reset(cls):
"""Reset all versions"""
cls._state = None
DBHelper().query('DROP TABLE IF EXISTS %s'
% DBHelper.quote_identifier(cls._table))
class TestDBHelper(unittest.TestCase):
"""DBHelper test case class"""
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 tearDown(self):
"""Clean up after test"""
DBHelper().set_db(None)
os.unlink('/tmp/box.db')
def test_set_db(self):
"""Test DBHelper.set_db"""
self.assertIsInstance(self._helper.get_connection(), Connection)
self._helper.set_db(None)
self.assertIsNone(self._helper.get_connection())
self.assertRaises(OperationalError, self._helper.set_db, '.')
self._helper.set_db(':memory:')
self.assertIsInstance(self._helper.get_connection(), Connection)
def test_quote_identifier(self):
"""Test DBHelper identifier quoting"""
quote = DBHelper.quote_identifier
self.assertEqual(quote('id'), '"id"')
self.assertEqual(quote('"id"'), '"""id"""')
self.assertEqual(quote('tbl.col'), '"tbl"."col"')
self.assertEqual(quote('tbl.col', 'tc'), '"tbl"."col" AS "tc"')
def test_query(self):
"""Test query execution"""
cursor = self._helper.query('SELECT DATE() AS "date"')
self.assertIsInstance(cursor, Cursor)
self.assertIn('date', cursor.fetchone().keys())
self.assertRaises(OperationalError, self._helper.query, 'SYNTAX ERROR')
def test_describe_table(self):
"""Test describing a table"""
desc = self._helper.describe_table('test_table')
self.assertIn('col_a', desc)
self.assertIn('col_b', desc)
self.assertNotIn('col_c', desc)
self.assertEqual(desc['col_a']['pk'], 1)
self.assertEqual(desc['col_b']['pk'], 0)
self.assertEqual(desc['col_b']['type'], 'INTEGER')
def test_insert(self):
"""Test helper insert method"""
ids = self._helper.insert('test_table', (
{'col_b': randint(0, 99)},
{'col_b': randint(0, 99)},
{'col_b': randint(0, 99)}
))
self.assertEqual(len(ids), 3)
self.assertEqual(ids[0] + 1, ids[1])
self.assertEqual(ids[0] + 2, ids[2])
last = ids[2]
ids = self._helper.insert('test_table', {'col_b': randint(0, 99)})
self.assertEqual(ids[0], last + 1)
self.assertRaises(
OperationalError,
self._helper.insert,
'missing_table',
{'col_b': 0}
)
ids = self._helper.insert('test_table', {})
self.assertEqual(len(ids), 0)
def test_select_from(self):
"""Test the SELECT <columns> FROM part of DBSelect"""
select = DBSelect('a')
sql = re.sub(r'\s+', ' ', select.render())
self.assertEqual(sql, 'SELECT "a".* FROM "a"')
select.set_from('b', ())
sql = re.sub(r'\s+', ' ', select.render())
self.assertEqual(sql, 'SELECT "a".* FROM "b" INNER JOIN "a"')
select.set_from('c', ('d', 'e'))
sql = re.sub(r'\s+', ' ', select.render())
self.assertEqual(
sql,
'SELECT "a".*, "c"."d", "c"."e" ' +
'FROM "c" INNER JOIN "b" INNER JOIN "a"'
)
count = self._helper.query(
DBSelect('test_table', {'count': 'COUNT(*)'}).render()
).fetchone()
self.assertTrue(type(count['count']) == int)
self.assertTrue(count['count'] >= 0)
def test_select_columns(self):
"""Test DBSelect.columns"""
select = DBSelect('a', {})
select.columns({'c': 'b'})
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "a"."b" AS "c" FROM "a"'
)
select.left_join('b', 'a.b = b.a', {})
select.columns('*', 'b')
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "a"."b" AS "c", "b".* FROM "a" LEFT JOIN "b" ON a.b = b.a'
)
def test_select_join(self):
"""Test the DBSelects' *_join mehtods"""
self._helper.query('CREATE TABLE a (x INTEGER)')
self._helper.query('CREATE TABLE b (y INTEGER)')
self._helper.insert('a', ({'x': 1}, {'x': 2}, {'x': 3}))
self._helper.insert('b', ({'y': 2}, {'y': 3}, {'y': 4}))
sql = DBSelect('a', {'n': 'COUNT(*)'}).inner_join('b', 'a.x = b.y', ())
self.assertTrue(self._helper.query(sql.render()).fetchone()['n'] == 2)
sql = DBSelect('a', {'n': 'COUNT(*)'}).left_join('b', 'a.x = b.y', ())
self.assertTrue(self._helper.query(sql.render()).fetchone()['n'] == 3)
# Right and outer joins are currently not supported by SQLite
#sql = DBSelect('a', {'n': 'COUNT(*)'}).right_join('b', 'a.x = b.y', ())
#self.assertTrue(self._helper.query(sql.render()).fetchone()['n'] == 3)
#sql = DBSelect('a', {'n': 'COUNT(*)'}).full_join('b', 'a.x = b.y', ())
#self.assertTrue(self._helper.query(sql.render()).fetchone()['n'] == 4)
def test_select_where(self):
"""Test the WHERE part of DBSelect"""
select = DBSelect('test_table')
select.where('"col_a" = ?', 1)
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "test_table".* FROM "test_table" WHERE ("col_a" = ?)'
)
self.assertEqual(len(select.query().fetchall()), 1)
select.or_where('"col_a" = ?', 2)
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "test_table".* FROM "test_table" ' +
'WHERE ("col_a" = ?) OR ("col_a" = ?)'
)
self.assertEqual(len(select.query().fetchall()), 2)
select.where('"col_a" IN (?)', (3, 4, 5))
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "test_table".* ' +
'FROM "test_table" ' +
'WHERE ("col_a" = ?) ' +
'OR ("col_a" = ?) ' +
'AND ("col_a" IN (?, ?, ?))'
)
self.assertEqual(len(select.query().fetchall()), 1)
def test_select_distinct(self):
"""Test the DISTINCT directive"""
select = DBSelect('a')
select.distinct()
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT DISTINCT "a".* FROM "a"'
)
select.distinct(False)
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "a".* FROM "a"'
)
def test_select_limit(self):
"""Test LIMIT and OFFSET parts"""
select = DBSelect('test_table')
select.limit(10)
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "test_table".* FROM "test_table" LIMIT 10'
)
self.assertEqual(len(select.query().fetchall()), 10)
select.limit(10, self._num_rows - 5)
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "test_table".* FROM "test_table" LIMIT %d, 10'
% (self._num_rows - 5,)
)
self.assertEqual(len(select.query().fetchall()), 5)
def test_select_order(self):
"""Test the ORDER BY part of DBSelect"""
self._helper.query('CREATE TABLE a (x INTEGER, y INTEGER)')
self._helper.insert('a', (
{'x': 1, 'y': 1},
{'x': 2, 'y': 2},
{'x': 3, 'y': 2},
))
select = DBSelect('a').order('x')
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "a".* FROM "a" ORDER BY "x" ASC'
)
self.assertEqual(select.query().fetchone()['x'], 1)
select = DBSelect('a').order('y', 'DESC').order('x')
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "a".* FROM "a" ORDER BY "y" DESC, "x" ASC'
)
self.assertEqual(select.query().fetchone()['x'], 2)
def test_select_unset(self):
"""Test unsetting parts of SELECT"""
select = DBSelect('a', {'c': 'col'}
).left_join('b', 'a.c = b.d', {'d': 'col'}
).where('a.col = ?', 1
).order('b.d', 'DESC'
).limit(1, 2
).distinct(True)
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT DISTINCT "a"."col" AS "c", "b"."col" AS "d" ' +
'FROM "a" LEFT JOIN "b" ON a.c = b.d ' +
'WHERE (a.col = ?) ' +
'ORDER BY "b"."d" DESC ' +
'LIMIT 2, 1'
)
select.unset(select.FROM | select.COLUMNS).set_from('x')
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT DISTINCT "x".* ' +
'FROM "x" WHERE (a.col = ?) ORDER BY "b"."d" DESC LIMIT 2, 1'
)
select.unset(select.WHERE)
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT DISTINCT "x".* ' +
'FROM "x" ORDER BY "b"."d" DESC LIMIT 2, 1'
)
select.unset(select.DISTINCT | select.ORDER | select.LIMIT)
self.assertEqual(
re.sub(r'\s+', ' ', str(select)),
'SELECT "x".* FROM "x"'
)
def test_select_clone(self):
"""Test cloning a SELECT"""
select1 = DBSelect('a', {'c': 'col'}
).left_join('b', 'a.c = b.d', {'d': 'col'}
).where('a.col = ?', 1
).order('b.d', 'DESC'
).limit(1, 2
).distinct(True)
select2 = select1.clone()
self.assertEqual(str(select1), str(select2))
select2.or_where('b.col = ?', 1)
self.assertNotEqual(str(select1), str(select2))
select1.unset(select1.WHERE)
select2.unset(select2.WHERE)
self.assertEqual(str(select1), str(select2))
def test_select_update(self):
"""Test executing UPDATEs based on SELECTs"""
select = DBSelect('test_table').where('col_a = ?', 1)
self.assertEqual(select.query_update({'col_b': 512}), 1)
select = DBSelect('test_table').order('col_a').limit(3, 1)
self.assertEqual(select.query_update({'col_b': 512}), 3)
select = DBSelect('test_table').where('col_b = ?', 512)
self.assertEqual(select.query_update({'col_b': 1024}), 4)
def test_select_delete(self):
"""Test executing DELETEs based on SELECTs"""
select = (DBSelect('test_table')
.order('col_a', 'DESC')
.limit(self._num_rows - 10))
self.assertEqual(select.query_delete(), self._num_rows - 10)
select = DBSelect('test_table').where('col_a = ?', 1)
self.assertEqual(select.query_delete(), 1)
select = DBSelect('test_table').where('col_a IN (?)', (1, 2, 3, 4))
self.assertEqual(select.query_delete(), 3)
def _prepare_filter_attribute(self, attribute):
"""Prepare attribute for use in WHERE"""
# TODO: check attribute against describe table?
return DBHelper.quote_identifier(attribute)