def select_folder_rows(self, FolderName='', Where='', **kwargs):
Data = None
if FolderName:
Data = [ FolderName ]
if not Where:
Where = "Where foldername=?" % (Data[0])
else:
Where += " and foldername=?" % (Data[0])
return sql.select(FolderComp.FolderTable, WhereClause=Where, Data=Data, Verbose=self.Verbose, **kwargs)
def Register(self):
row = None
if sql.tables(FolderComp.RegistryTable):
row = sql.select(FolderComp.RegistryTable, WhereClause=Expand(r"WHERE folder=?"), Verbose=self.Verbose, Data=[self.Folder])
now = time.time()
if not row:
row = [self.Folder, now, now]
else:
row = row[0]
row[2] = now
sql.write_to_table(FolderComp.RegistryTable, [row], FolderComp.RegistryColumns, UseExistingTable=True, IdentityIndex=True, Verbose=self.Verbose)
def dump():
rows = sql.select(FolderComp.FolderTable)
PrettyPrint(rows)
def SQL_UnitTest(GenerateTestData=False):
Trace()
data = [
[ 55, 54, 53, 52, 51],
[ 55, 44, 43, 42, 41],
[ 35, 34, 33, 32, 31],
[ 35, 34, 33, 22, 21],
[ 15, 14, 13, 12, 11],
[ 5, 4, 3, 2, 1],
]
columns = [
[ 'col0', 'int' ],
[ 'col1', 'int' ],
[ 'col2', 'int' ],
[ 'col3', 'int' ],
[ 'col4', 'int' ],
]
primaryKeys = ['col0', 'col1']
sorted = sql.sort_data(data, [0, 1, 2, 3], columns)
PrettyPrintList(sorted)
for idx in range(0, len(data[0]), 1):
UnitTest.Step('Check column [idx]')
col = [row[idx] for row in data]
sorted = [row[idx] for row in data]
sorted.sort()
sorted.reverse()
UnitTest.Verify(col == sorted, 'Column [idx] is sorted correctly')
Log('col: %s' % col)
Log('sorted: %s' % sorted)
table = 'SQL_UnitTest'
if GenerateTestData:
jsonData = dictn()
jsonData.expectedResultsJson = data
UnitTest.SaveExpectedResults(jsonData)
UnitTest.Step('Write & Read from table')
sql.write_to_table(table, data, columns, Verbose=True)
read = sql.select(table)
UnitTest.Verify(data == read)
UnitTest.Step('Update Tests')
row = [
[ 55, 54, 1, 1, 1]
]
count = sql.update(table, row, WhereClause=r'Where col0=55 and col1=54', Verbose=True)
Log('count=[count]')
UnitTest.Verify(count == 1)
updated = sql.select(table, WhereClause=r'Where col0=55 and col1=54', Verbose=True)
UnitTest.VerifyMatch(updated, row)
UnitTest.Step('Write & Read unique data')
unique_data = []
unique_data.append(list(range(100, 200, 20)))
unique_data.append(list(range(200, 300, 20)))
unique_data.append(list(range(300, 400, 20)))
unique_data.append(list(range(400, 500, 20)))
unique_data.append(list(range(500, 600, 20)))
PrettyPrint(unique_data)
sql.write_to_table(table, unique_data, columns, PrimaryKey=primaryKeys, UseExistingTable=False, IdentityIndex=False, Verbose=True)
read = sql.select(table)
PrettyPrint(read)
UnitTest.Verify(unique_data == read)
UnitTest.Step('Write & Read indexed unique data')
sql.write_to_table(table, unique_data, columns, PrimaryKey=None, UseExistingTable=False, IdentityIndex=True, Verbose=True)
read = sql.select(table)
PrettyPrint(read)
[row.insert(0, idx) for idx, row in enumerate(unique_data, 1)]
UnitTest.Verify(unique_data == read)
def select_rows(self, Where='', Types='[AllMediaTypes]', **kwargs):
where = self.get_where_clause(Types, Where=Where)
return sql.select(self.Table, WhereClause=where, Verbose=self.Verbose, **kwargs)