def setUpClass(cls) -> None:
cls.tearDownClass()
init_db_sql = os.path.join('misc', 'init_test_db.sql')
conn = sqlite3.connect(TEST_DB)
c = conn.cursor()
utils.execute_sql_file(c, init_db_sql)
conn.commit()
conn.close()
def get_transactions(self, sql_engine):
pattern = self.bank_config['patterns']
dml_file = self.current_path + self.bank_config['stage_dml_select_description']
result = utils.execute_sql_file(pattern, dml_file, sql_engine)
rows = utils.convert_sql_result_to_dict(result)
return rows
def normalize(self):
conn = sqlite3.connect('normalize.sqlite')
c = conn.cursor()
utils.execute_sql_file(c, os.path.join('misc', 'init_df_table.sql'))
tables = self.tables
if 'FuncDep' in tables:
tables.remove('FuncDep')
for table in tables:
decom = self.normalize_table(table)
for n, nt in enumerate(decom):
self.create_new_table(c, nt, n, table)
self.add_content(c, nt, n, table)
self.add_new_df(c, nt, n, table)
conn.commit()
conn.close()
def add_df(self, table: str, lhs: str, rhs: str):
# La table doit exister
if table not in self.tables:
raise UnknownTableError()
# La table n'est pas celle des DF
if table == 'FuncDep':
raise DFTableError()
table_fields = self.get_fields(table)
# Tous les champs de la prémisse existent dans la table
for field in lhs.split():
if field not in table_fields:
raise UnknownFieldsError()
# La doit être singulière
if len(rhs.split()) > 1:
raise DFNotSingularError()
# Le champ de déffini doit exister dans la table
if rhs not in self.get_fields(table):
raise UnknownFieldsError()
# Le champ rhs ne doit pas être dans les champs lhs
if rhs in lhs.split():
raise RHSIncludeToLHSError()
c = self._conn.cursor()
# On crée la tables des DF si besoin
if not self.has_df_table:
utils.execute_sql_file(c, os.path.join('misc',
'init_df_table.sql'))
try:
c.execute('INSERT INTO `FuncDep` VALUES (?, ?, ?)',
(table, lhs, rhs))
except sqlite3.IntegrityError:
raise DFAddTwiceError()
def update_categories(self, rows, sql_engine):
match = []
pattern = self.bank_config['patterns']
dml_file = self.current_path + self.bank_config['stage_dml_update_categories']
string_init = [*self.bank_config['regex'].keys()]
for row in rows:
category = "unknown"
info = "unknown"
for item in string_init:
if 'contains' in self.bank_config['regex'][item].keys() and \
item in row['description']:
match.append(item)
else if row['description'].startswith(item):
match.append(item)
if len(match):
category = self.return_info(row['description'], match[0])['category']
info = self.return_info(row['description'], match[0])['info']
pattern['<CATEGORY>'] = category.strip().rstrip("\\")
pattern['<INFO>'] = info.strip().rstrip("\\")
pattern['<ROW_SHA>'] = row['row_sha']
utils.execute_sql_file(pattern, dml_file, sql_engine)
def update_master_transactions(self, pattern, dml_file, sql_engine):
utils.execute_sql_file(pattern, dml_file, sql_engine)
def create_stage_transactions(self, pattern, ddl_file, sql_engine):
utils.execute_sql_file(pattern, ddl_file, sql_engine)
def reset_db(self):
super(PrototypeSQL, self).reset_db()
print('Running db upgrade')
execute_sql_file(self.psql, 'raw_case_data.sql')