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 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 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():
"""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 install(cls):
"""Install model"""
InstallHelper.install(cls._table, cls._install())
def install(cls):
"""Install model"""
InstallHelper.install(cls._table, cls._install())