def setupadditionaltables(testcase):
""" Adds additional tables for further testing """
testcase.connection.execute(TESTTABLESQL2)
testcase.connection.execute(TESTTABLESQL3)
testcase.connection.execute(TESTTABLESQL4)
testcase.testtables = [
Table.Table(TESTTABLESQL),
Table.Table(TESTTABLESQL2),
Table.Table(TESTTABLESQL3),
Table.Table(TESTTABLESQL4)
]
def test_addtables_multiple(self):
""" Tests adding multiple tables successfully via .addtables """
testtable2 = Table.Table(utils.TESTTABLESQL2)
testtable3 = Table.Table(utils.TESTTABLESQL3)
testtables = [testtable2, testtable3]
success, fail = self.connection.addtables(*testtables)
## Ensure tables were sccessfully added
self.assertListEqual(success, testtables)
self.assertListEqual(fail, [])
## Double-check that tables are properly in the database
self.assertTrue(self.connection.validatetable(testtable2))
self.assertTrue(self.connection.validatetable(testtable3))
def test_database_tableexists(self):
""" Makes sure that database.tableexists functions the same as the base sql.Table.tableexists function """
## Real table
tablename = "testtable"
## This will fail automatically if table doesn't exist
table = self.connection.gettable(tablename)
self.assertEqual(self.connection.tableexists(tablename),
Table.tableexists(self.connection, tablename))
## Fake Table
tablename = "notarealtable"
self.assertRaises(ValueError, self.connection.gettable, tablename)
self.assertEqual(self.connection.tableexists(tablename),
Table.tableexists(self.connection, tablename))
def getalltables(self, advanced=True):
""" Returns all tables in the database as Table Objects.
If advanced is True (default), returns Advanced Objects when possible.
"""
tables = self.execute("""SELECT sql FROM sqlite_master WHERE type="table";""").fetchall()
## Let parser determine type (if available)
if self.parser:
tables = [self.parser(table['sql']).obj for table in tables]
tables = [table.to_advancedtable(self) if hasattr(table,'to_advancedtable') else table for table in tables]
else:
if advanced:
tables = [Table.AdvancedTable(table['sql'],self) for table in tables]
else:
tables = [Table.Table(table['sql'],self) for table in tables]
return tables
def test_addandvalidatetables_mixed(self):
""" Tests that addandvalidatetables will result in a failure when a table's definition is different from its version in the database, and success for tables that are added or are properly implemented """
badtesttable = Table.TableConstructor("testtable",
columns=dict(name="TEXT",
value="FLOAT"))
goodtesttable = Table.Table(utils.TESTTABLESQL5)
testtables = self.testtables + [badtesttable, goodtesttable]
success, fail = self.connection.addandvalidatetables(*testtables)
## Preexisting and good testtable should be in success
self.assertListEqual(success, self.testtables + [
goodtesttable,
])
## The malformed table should be in fail
self.assertListEqual(fail, [
badtesttable,
])
def tableexists(self,tablename):
""" Tests that a table exists """
if isinstance(tablename,(Table.Table,Table.TableConstructor)):
tablename = tablename.fullname
if not isinstance(tablename,str):
raise ValueError("tablename must be a string or a table")
return Table.tableexists(self,tablename)
def test_initialize(self):
""" Tests that AdvancedTable can be properly initialized """
testtable = Table.AdvancedTable(utils.TESTTABLESQL,self.connection)
## Make sure it's actually an AdvancedTable
self.assertIsInstance(testtable,Table.AdvancedTable)
## Make sure it's attributes are correct
self.assertEqual(testtable._definition,utils.TESTTABLESQL)
self.assertEqual(testtable.database,self.connection)
## Should be Equal (at the moment) to a normal Table
self.assertEqual(testtable,self.connection.gettable("testtable"))
def test_validatetable_bad_different(self):
""" Tests that validatetable method returns False when supplied with a table that is in the database, but whose structure is different than implemented. """
testtable = Table.TableConstructor("testtable",
columns=[
objects.Column("notaname",
datatype="REAL"),
objects.Column("notavalue",
datatype="BLOB")
])
self.assertFalse(self.connection.validatetable(testtable))
def test_addtables_exists_bad(self):
""" Tests that adding a table that already exists and does not have the "IF NOT EXISTS" tag fails """
testtable3 = Table.Table(utils.TESTTABLESQL3)
testtables = [
testtable3,
]
success, fail = self.connection.addtables(*testtables)
## Ensure tables were sccessfully added
self.assertListEqual(success, [])
self.assertListEqual(fail, testtables)
def test_removetable(self):
""" Tests that remove table can remove a table based on its table name """
tablename = "testtable"
## This will fail if the table was not already created (thus rendering this test invalid)
self.connection.gettable(tablename)
self.connection.removetable(tablename)
self.assertFalse(Table.tableexists(self.connection, tablename))
self.assertRaisesRegex(ValueError, "Table .* does not exist",
self.connection.gettable, tablename)
def test_validatetable_bad_missing(self):
""" Tests that validatetable method returns False when supplied a table not in the database """
testtable = Table.TableConstructor("notatable",
columns=[
objects.Column("notaname",
datatype="REAL"),
objects.Column("notavalue",
datatype="BLOB")
])
self.assertFalse(self.connection.validatetable(testtable))
def test_removetable_object(self):
""" Tests that tables can be removed using Tables and Table Subclasses """
for table in [
self.connection.gettable("testtable"),
self.connection.getadvancedtable("testtable")
]:
with self.subTest(table=table):
self.connection.removetable(table)
self.assertFalse(Table.tableexists(self.connection, table))
self.assertRaisesRegex(ValueError, "Table .* does not exist",
self.connection.gettable, table)
def test_addtables_one(self):
""" Tests that a single table can be successfully added using Database.addtables """
testtable = Table.Table(utils.TESTTABLESQL2)
success, fail = self.connection.addtables(testtable)
## Make sure method output is correct
self.assertListEqual(success, [
testtable,
])
self.assertListEqual(fail, [])
## Ensure table was ACTUALLY added (and not just stuck in the success column)
self.assertTrue(self.connection.validatetable(testtable))
def test_addandvalidatetables_addone(self):
""" Tests that addandvalidatetables will correctly add and validate a table not in the database """
testtable5 = Table.Table(utils.TESTTABLESQL5)
success, fail = self.connection.addandvalidatetables(
testtable5, *self.testtables)
## Should only succeed
self.assertListEqual(success, [
testtable5,
] + self.testtables)
self.assertListEqual(fail, [])
## Table should now exist
self.assertTrue(self.connection.validatetable(testtable5))
def test_database_listconstructs(self):
""" Tests that list constructs works """
c = self.connection
c.removetable("testtable")
## No Tables
self.assertEqual(c.list_constructs(), [])
## One Table
c.addtables(Table.Table(utils.TESTTABLESQL))
self.assertEqual(c.list_constructs(), [
utils.TESTTABLESQL,
])
## Multiple Tables
utils.setupadditionaltables(self)
## utils.setupadditionaltables sets self.testtables as a list of all presumed tables (testtable1 + tables added by utils.setupadditionaltables)
## sqlite_master doesn't save the ending ";" or "IF NOT EXISTS" clause
self.assertEqual(c.list_constructs(), [
table.definition.rstrip(";").replace("IF NOT EXISTS ", "")
for table in self.testtables
])
## Remove a table
c.removetable("testtable2")
self.testtables.remove(Table.Table(utils.TESTTABLESQL2))
self.assertEqual(c.list_constructs(), [
table.definition.rstrip(";").replace("IF NOT EXISTS ", "")
for table in self.testtables
])
## Add View
## TODO: Fix this to be addview
c.execute(utils.TESTVIEWSQL)
self.assertEqual(c.list_constructs(), [
table.definition.rstrip(";").replace("IF NOT EXISTS ", "")
for table in self.testtables
] + [
utils.TESTVIEWSQL.rstrip(";"),
])
def test_addandvalidatetables_bad(self):
""" Tests that addandvalidatetables will result in a failure when a table's definition is different from its version in the database """
testtable = Table.TableConstructor("testtable",
columns=dict(name="TEXT",
value="FLOAT"))
success, fail = self.connection.addandvalidatetables(testtable)
## Should only fail
self.assertListEqual(success, [])
self.assertListEqual(fail, [
testtable,
])
## Table in database should be different from testtable
self.assertFalse(self.connection.gettable("testtable") == testtable)
def gettablebyid(self,rowid):
""" Returns a Table Object representing the table with the given rowid.
rowid should be an integer which represents the table's rowid in the sqlite_master table.
Raises a ValueError if the table does not exist.
"""
if not isinstance(rowid,int):
raise TypeError("rowid should be an integer")
with Utilities.temp_row_factory(self,objects.dict_factory):
tableentry = self.execute("""SELECT sql FROM sqlite_master WHERE type="table" AND rowid=?;""",(rowid,)).fetchone()
if not tableentry:
raise ValueError(f"Table {tablename} does not exist.")
if self.parser: return self.parser(tableentry['sql'],database = self).obj.to_advancedtable()
return Table.AdvancedTable(tableentry['sql'],database = self)
def gettable(self,tablename):
""" Returns a Table Object representing the table of tablename.
tablename should be the string name of an existing table (including schema name for attached tables).
Raises a ValueError if the table does not exist.
"""
if tablename != "sqlite_master":
tableentry = self.execute("""SELECT sql FROM sqlite_master WHERE type="table" AND tbl_name=?;""",(str(tablename),)).fetchone()
if not tableentry:
raise ValueError(f"Table {tablename} does not exist.")
else:
tableentry = {"sql":SQLITE_MASTER_SCHEMA}
if self.parser: return self.parser(tableentry['sql'],database = self).obj
return Table.Table(tableentry['sql'],database = self)
def test_addtables_exists(self):
""" Tests that adding a table that already exists but has the "IF NOT EXISTS" tag succeeds """
testtable4 = Table.Table(utils.TESTTABLESQL4)
testtables = [
testtable4,
]
success, fail = self.connection.addtables(*testtables)
## Ensure tables were sccessfully added
self.assertListEqual(success, testtables)
self.assertListEqual(fail, [])
alltables = self.connection.getalltables()
for table in testtables:
with self.subTest(table=table, alltables=alltables):
self.assertIn(table, alltables)
## Check the table is in the database
self.assertTrue(self.connection.validatetable(testtable4))
def test_addmultiple_argslimit(self):
""" Tests that inserts are split into batches when the ReplacementDict is larger than the REPLACEMENT_LIMIT (900) """
## 10 Columns, so we can surpass the RELACEMENT_LIMIT with 91 Inserts
#self.connection.row_factory = objects.advancedrow_factory
self.connection.row_factory = objects.dict_factory
table = Table.Table("""CREATE TABLE massive (
a, b, c, d, e, f, g, h, i, j
);""")
self.connection.addandvalidatetables(table)
table = self.connection.getadvancedtable("massive")
columns = ['a','b','c','d','e','f','g','h','i','j']
inserts = [dict(list(zip(columns,list(range(10))))) for row in range(91)]
self.assertEqual(len(inserts),91)
self.assertGreater(len(inserts)*len(columns),constants.REPLACEMENT_LIMIT)
table.addmultiple(*inserts)
## In theory, if this wasn't working it would have raised an OperationalError, but we'll fetch the rows
rows = table.selectall()
#def mapid(rowtup):
# """ AdvancedRows automatically have
# rowtup[1]['rowid'] = rowtup[0]
#map(mapid,inserts)
self.assertEqual(inserts,rows)
def test_remove(self):
""" Tests that AdvancedTables can use their parents to remove themselves """
table = self.connection.getadvancedtable("testtable")
table.remove()
self.assertFalse(Table.tableexists(self.connection,table),table)
self.assertRaisesRegex(ValueError,"Table .* does not exist.",self.connection.gettable,table)
def test_gettable(self):
""" Tests the gettable method of the Database Object. """
testtable = self.connection.gettable("testtable")
self.assertEqual(testtable.definition, utils.TESTTABLESQL)
self.assertEqual(testtable, Table.Table(utils.TESTTABLESQL))
def test_table_rowid_withoutrowid(self):
""" Tests that a Table that specifies no Integer Primary Key and "WITHOUT ROWID" returns None for it's rowid. """
table = Table.Table(utils.TESTTABLESQL5)
self.connection.addtables(table)
testtable = self.connection.gettable("testtable5")
self.assertIsNone(testtable.rowid)
def removetable(self,tablename):
""" Removes a table from the database. tablename can be a string representing the table's name, or a Table object """
Table.removetable(self,tablename)
def test_tableexists(self):
""" Tests that tableexists works as expected"""
self.assertTrue(Table.tableexists(self.connection,"testtable"))
def test_tableexists_object(self):
""" Tests that tableexists accepts Tables and Table Subclasses """
for table in [self.connection.gettable("testtable"),
self.connection.getadvancedtable("testtable")]:
self.assertTrue(Table.tableexists(self.connection,table))
def gettesttableconstructor():
return Table.TableConstructor("testtable",
columns=dict(name="TEXT", value="INTEGER"))
