def db_setup_as_string():
tables = db_utils.get_tables()
#tables = db_utils.sql_load_fr_db(r"""SELECT tbl_name FROM sqlite_master WHERE (type='table' or type='view') and not (name in""" + db_utils.sqlite_internal_tables() + r""") ORDER BY tbl_name""")[1]
res = []
for table in sorted(tables):
res.append((table, ))
table_info = db_utils.get_table_info(table)
res.append(table_info)
return utils.anything_to_string_representation(res)
def db_setup_as_string():
tables = db_utils.get_tables()
#tables = db_utils.sql_load_fr_db(r"""SELECT tbl_name FROM sqlite_master WHERE (type='table' or type='view') and not (name in""" + db_utils.sqlite_internal_tables() + r""") ORDER BY tbl_name""")[1]
res = []
for table in sorted(tables):
res.append((table,))
table_info = db_utils.get_table_info(table)
res.append(table_info)
return utils.anything_to_string_representation(res)
def add_metadata_to_about_db(self, dbconnection, created_tables_sqls=None):
tables = sorted(db_utils.get_tables(dbconnection=dbconnection, skip_views=True))
#Matches comment inside /* */
#create_table_sql CREATE TABLE meteo /*meteorological observations*/(
table_descr_reg = re.compile(r'/\*(.+)\*/', re.MULTILINE)
#Matches comment after --:
# strata text NOT NULL --clay etc
#, color_mplot text NOT NULL --color codes for matplotlib plots
column_descr_reg = re.compile(r'([A-Za-z_]+)[ ]+[A-Za-z ]*--(.+)', re.MULTILINE)
table_name_reg = re.compile(r'([A-Za-z_]+)[ ]+[A-Za-z ]*--(.+)', re.MULTILINE)
for table in tables:
#Get table and column comments
if created_tables_sqls is None:
table_descr_sql = ("SELECT name, sql from sqlite_master WHERE name = '%s';"%table)
create_table_sql = dbconnection.execute_and_fetchall(table_descr_sql)[0][1]
else:
create_table_sql = created_tables_sqls[table]
table_descr = table_descr_reg.findall(create_table_sql)
try:
table_descr = table_descr[0]
except IndexError:
table_descr = None
else:
table_descr = table_descr.rstrip('\n').rstrip('\r').replace("'", "''")
columns_descr = dict(column_descr_reg.findall(create_table_sql))
table_info = db_utils.get_table_info(table, dbconnection)
foreign_keys_dict = {}
foreign_keys = db_utils.get_foreign_keys(table, dbconnection)
for _table, _from_to in foreign_keys.items():
_from = _from_to[0][0]
_to = _from_to[0][1]
foreign_keys_dict[_from] = (_table, _to)
sql = r"""INSERT INTO about_db (tablename, columnname, description, data_type, not_null, default_value, primary_key, foreign_key) VALUES """
sql += r'({});'.format(', '.join(["""(CASE WHEN '%s' != '' or '%s' != ' ' or '%s' IS NOT NULL THEN '%s' else NULL END)"""%(col, col, col, col) for col in [table, r'*', table_descr, r'', r'', r'', r'', r'']]))
dbconnection.execute(sql)
for column in table_info:
colname = column[1]
data_type = column[2]
not_null = str(column[3]) if str(column[3]) == '1' else ''
default_value = column[4] if column[4] else ''
primary_key = str(column[5]) if str(column[5]) != '0' else ''
_foreign_keys = ''
if colname in foreign_keys_dict:
_foreign_keys = '%s(%s)'%(foreign_keys_dict[colname])
column_descr = columns_descr.get(colname, None)
if column_descr:
column_descr = column_descr.rstrip('\n').rstrip('\r').replace("'", "''")
sql = 'INSERT INTO about_db (tablename, columnname, data_type, not_null, default_value, primary_key, foreign_key, description) VALUES '
sql += '({});'.format(', '.join(["""CASE WHEN '%s' != '' or '%s' != ' ' or '%s' IS NOT NULL THEN '%s' else NULL END"""%(col, col, col, col) for col in [table, colname, data_type, not_null, default_value, primary_key, _foreign_keys, column_descr]]))
try:
dbconnection.execute(sql)
except:
try:
print(sql)
except:
pass
raise