def table_column_ddl(columnObj, tableObj, engine, debug_level=-1):
table_name = tableObj.name
column_name = columnObj.name
# xcolumn_name = str(columnObj.compile(dialect=engine.dialect)).strip()
# print(xcolumn_name)
# cname = columnObj.key
# xcolumn_name = str(tableObj.c[cname].compile(dialect=engine.dialect)).strip()
# print(xcolumn_name)
column_type = str(columnObj.type.compile(dialect=engine.dialect)).strip()
try:
column_default = str(
columnObj.default.compile(dialect=engine.dialect)).strip()
except Exception as e:
# print(e)
column_default = ''
try:
column_default = str(
columnObj.server_default.compile(dialect=engine.dialect)).strip()
except Exception as e:
# print(e)
column_default = ''
try:
ddl_obj = ddl.CreateColumn(columnObj)
ddl_string = str(ddl_obj.compile(dialect=engine.dialect)).strip()
except Exception as e:
# print(e)
ddl_string = ''
if not ddl_string:
ddl_string = f"TABLE {table_name} COLUMN {column_name} {column_type} {column_default}"
if int(debug_level) > 0:
msg = f"table [[{tableObj.name}]] table_column_DDL: [{ddl_string}]"
log_message(msg)
return ddl_string
def alter_table_column_ddl(columnObj, tableObj, engine, debug_level=-1):
table_name = tableObj.name
# column_name = columnObj.name
# xcolumn_name = str(columnObj.compile(dialect=engine.dialect)).strip()
# column_type = str(columnObj.type.compile(dialect=engine.dialect)).strip()
# try:
# column_default = str(columnObj.default.compile(dialect=engine.dialect)).strip()
# except Exception as e:
# # if e.message.find('it has no name')<0:
# # print(e)
# column_default = ''
# try:
# column_default = str(columnObj.server_default.compile(dialect=engine.dialect)).strip()
# except Exception as e:
# # if e.message.find('it has no name')<0:
# # print(e)
# column_default = ''
# ddl_string = f"ALTER TABLE {table_name} ALTER COLUMN {column_name} {column_type}"
column_ddl_string = table_column_ddl(columnObj, tableObj, engine)
#ddl_string = f"ALTER TABLE {table_name} ADD COLUMN {column_name} {column_type}"
ddl_string = f"ALTER TABLE {table_name} ALTER COLUMN {column_ddl_string}"
if int(debug_level) > 0:
msg = f"table [[{tableObj.name}]] alter_table_column_DDL: [{ddl_string}]"
log_message(msg)
return ddl_string
def get_dbsession(**kwargs):
xSession = sessionmaker(bind=engine)
xsession = xSession()
if 'debug' in kwargs.keys():
debug = kwargs.get('debug', -1)
else:
debug = kwargs.get('debug_level', -1)
debug_level = get_debug_option_as_level(debug)
try:
session_id = get_session_id()
dbsession = db_session_class(engine, xsession, db_schema_models,
session_id, debug_level)
except:
dbsession = None
msg = f"FAILED to create database session for [database ganimides]"
log_message(msg, msgType='error', msgColor=thisApp.Fore.RED)
#exit(0)
if dbsession:
process_id = kwargs.get('process_msgID', '')
if debug_level >= 0:
kwargs.update({'debug_level': debug_level})
if not kwargs.get('indent_method'):
kwargs.update({'indent_method': 'CALL_LEVEL'})
msg = f"[session] [[{dbsession.session_id}]] [CREATED]"
if process_id:
msg = msg + '#C0# in #C0#' + process_id
log_process_result_message('', 'session', msg, **kwargs)
return dbsession
def create_table_ddl(tableObj, engine, debug_level=-1):
ddl_obj = ddl.CreateTable(tableObj)
ddl_string = str(ddl_obj.compile(dialect=engine.dialect)).strip()
if int(debug_level) > 0:
msg = f"table [[{tableObj.name}]] create_table_DDL: [{ddl_string}]"
log_message(msg)
return ddl_string
def drop_table_column_ddl(columnObj, tableObj, engine, debug_level=-1):
table_name = tableObj.name
column_name = columnObj.name
ddl_string = f"ALTER TABLE {table_name} DROP COLUMN {column_name}"
if int(debug_level) > 0:
msg = f"table [[{tableObj.name}]] drop_table_column_DDL: [{ddl_string}]"
log_message(msg)
return ddl_string
def clear_table_ddl(tableObj, engine, debug_level=-1):
ddl_obj = tableObj.delete()
try:
ddl_string = str(ddl_obj.compile(dialect=engine.dialect)).strip()
except Exception as e:
print(e)
# ddl_string = ''
if int(debug_level) > 0:
msg = f"table [[{tableObj.name}]] clear_table_DDL: [{ddl_string}]"
log_message(msg)
return ddl_string
def drop_table_ddl(tableObj, engine, debug_level=-1):
ddl_obj = DropTable(tableObj)
try:
ddl_string = str(ddl_obj.compile(dialect=engine.dialect)).strip()
except Exception as e:
# print(e)
ddl_string = ''
if int(debug_level) > 0:
msg = f"table [[{tableObj.name}]] drop_table_DDL: [{ddl_string}]"
log_message(msg)
return ddl_string
def clear_table(tableInstance, debug=None):
debug_level = get_table_debug_level(tableInstance, debug)
engine = tableInstance.session.bind.engine
tableObj = tableInstance.model.__table__
table_name = tableInstance.model.__tablename__
table_rows = tableInstance.rowCount()
ddl_string = clear_table_ddl(tableObj, engine)
engine.execute(ddl_string)
if int(debug_level) > 0:
msg = f"table [[{table_name}]] cleared with [{table_rows} rows]"
log_message(msg)
def alter_column(tableInstance, columnObj, debug=None):
debug_level = get_table_debug_level(tableInstance, debug)
engine = tableInstance.session.bind.engine
tableObj = tableInstance.model.__table__
table_name = tableInstance.model.__tablename__
ddl_string = alter_table_column_ddl(columnObj, tableObj, engine,
debug_level - 1)
# if int(debug_level) > 0:
# msg = f"DDL:[{ddl_string}]"
# log_message(msg)
engine.execute(ddl_string)
msg = f"table [[{table_name}]] [column {columnObj.name}] altered with DDL:[{ddl_string}]"
if int(debug_level) > 0:
log_message(msg)
def check_table(tableInstance,
silent=None,
debug=None,
auto_synchronize=True,
synchronization_method='drop-create,recreate,add-columns',
copy_records=True):
debug_level = get_table_debug_level(tableInstance, debug)
engine = tableInstance.session.bind.engine
tableObj = tableInstance.model.__table__
metadata = MetaData(bind=engine)
table_name = tableInstance.model.__tablename__
table_rows = tableInstance.rowCount()
if table_name == 'api_subscriptions':
x = 1
compare_with_physical_table(tableInstance, debug_level - 1)
if tableInstance.physical_table_is_synchronized:
msg = f"table [[{table_name}]] loaded with [{table_rows} rows]"
if int(debug_level) > 0 and not silent:
log_message(msg)
else:
msg = f"table [[{table_name}]] with [{table_rows} rows]"
if tableInstance.new_columns > 0:
msg = msg + f", [[[[{tableInstance.new_columns} new]]]]"
if tableInstance.unmapped_columns > 0:
msg = msg + f", [[[{tableInstance.unmapped_columns} unmapped]]]"
if tableInstance.changed_columns > 0:
msg = msg + f", [[[[[{tableInstance.changed_columns} changed]]]]]"
if tableInstance.new_columns + tableInstance.unmapped_columns + tableInstance.changed_columns > 1:
x = 's'
else:
x = ''
msg = msg + f" column{x}"
if not auto_synchronize:
msg = msg + " loaded [UnSynchronized]"
if debug_level > 0 and not silent:
log_message(msg)
else:
if tableInstance.new_columns == 0 and tableInstance.changed_columns == 0 and synchronization_method.upper(
).find('ADD') >= 0:
msg = msg + " loaded [UnSynchronized]"
if debug_level > 0 and not silent:
log_message(msg)
else:
if synchronization_method.upper().find('DROP') >= 0:
drop_and_create_table(tableInstance, copy_records,
debug_level - 1)
elif synchronization_method.upper().find('ADD') >= 0:
add_columns_to_physical_table(tableInstance,
debug_level - 1)
elif synchronization_method.upper().find('RECREATE') >= 0:
recreate_table(tableInstance, copy_records,
debug_level - 1)
compare_with_physical_table(tableInstance, debug_level - 1)
if tableInstance.physical_table_is_synchronized:
msg = msg + f" [Synchronized] (method used:[[[{synchronization_method}]]]) and loaded"
else:
msg = msg + f" [Synchronized] (method used:[[[{synchronization_method}]]]) and loaded but is [still UnSynchronized]"
if int(debug_level) > 0:
log_message(msg)
def recreate_tables(dbmodelBase, engine, debug_level=1):
tables_before = get_tables_directory(engine)
# engine = tableInstance.session.bind.engine
# tableObj = tableInstance.model.__table__
# metadata = MetaData(bind=engine)
# dbmodel.Base.metadata.create_all(bind=engine)
dbmodelBase.metadata.create_all(bind=engine)
tables_after = get_tables_directory(engine)
ix = 0
for table_name in tables_after:
if table_name not in tables_before:
ix = ix + 1
if int(debug_level) > 0:
msg = f'table [{table_name}] created'
log_message(msg)
if int(debug_level) > 0:
msg = f'[{ix}] tables created in database [{engine}]'
log_message(msg)
def drop_table_constraints_ddl(tableObj, engine, debug_level=-1):
ddl_text = ''
constraints = tableObj.constraints
for constraint in constraints:
ddl_obj = DropConstraint(constraint)
try:
ddl_string = str(ddl_obj.compile(dialect=engine.dialect)).strip()
except Exception as e:
# # if e. str(e.message).find('it has no name')<0:
# print(e)
ddl_string = ''
if ddl_string:
if not ddl_text:
ddl_text = ddl_string
else:
ddl_text = ddl_text + '\n' + ddl_string
ddl_string = ddl_text
if int(debug_level) > 0:
msg = f"table [[{tableObj.name}]] drop_table_constraints_DDL: [{ddl_string}]"
log_message(msg)
return ddl_string
def drop_table_indexes_ddl(tableObj, engine, debug_level=-1):
ddl_text = ''
indexes = sorted(list(tableObj.indexes),
key=lambda k: k.name,
reverse=False)
for index in indexes:
ddl_obj = DropIndex(index)
try:
ddl_string = str(ddl_obj.compile(dialect=engine.dialect)).strip()
except Exception as e:
# print(e)
ddl_string = ''
if ddl_string:
if not ddl_text:
ddl_text = ddl_string
else:
ddl_text = ddl_text + '\n' + ddl_string
ddl_string = ddl_text
if int(debug_level) > 0:
msg = f"table [[{tableObj.name}]] drop_table_indexes_DDL: [{ddl_string}]"
log_message(msg)
return ddl_string
def recreate_table(tableInstance, copy_records=True, debug=None):
debug_level = get_table_debug_level(tableInstance, debug)
engine = tableInstance.session.bind.engine
tableObj = tableInstance.model.__table__
metadata = MetaData(bind=engine)
table_name = tableInstance.model.__tablename__
table_rows = tableInstance.rowCount()
old_rows = table_rows
if copy_records:
backup_table_name = tableInstance.model.__tablename__ + '_backup'
backup_table_name = copy_table(tableInstance, backup_table_name)
if not backup_table_name:
msg = '#ERROR#copy to backup failed#RESET#'
log_message(msg)
return
backuptable = Table(backup_table_name, metadata, autoload=True)
old_columns = backuptable.columns
old_rows = engine.execute(
f"select count(*) from {backup_table_name}").scalar()
#drop and recreate with the new structure
tableObj.drop(engine, checkfirst=True)
tableObj.create(engine, checkfirst=True)
new_rows = tableInstance.rowCount()
if not copy_records:
msg = f"table [[{tableInstance.model.__tablename__}]] [recreated] with [[[{new_rows}/{old_rows} rows copied]]]"
else:
columns = [c.copy() for c in tableObj.columns]
#copy data from backup
columns_str = ''
for column in columns:
if column.key in old_columns.keys():
if columns_str:
columns_str = columns_str + ' , ' + column.name
else:
columns_str = column.name
from_table = backup_table_name
to_table = tableInstance.model.__tablename__
ddl_string = f"INSERT INTO {to_table} ({columns_str}) select {columns_str} from {from_table}"
if int(debug_level) > 0:
msg = f"table [[{tableInstance.model.__tablename__}]] copy records DDL: [{ddl_string}]"
log_message(msg)
try:
engine.execute(ddl_string)
except Exception as e:
print(e)
new_rows = tableInstance.rowCount()
if new_rows == old_rows:
backuptable.drop(engine, checkfirst=True)
#kill garbages
del backuptable
# del BACKUPTABLE_TABLE
msg = f"table [[{tableInstance.model.__tablename__}]] [recreated] with [[[{new_rows}/{old_rows} rows copied]]] from backup table {backup_table_name}"
if int(debug_level) > 0:
log_message(msg)
def copy_table(tableInstance, new_table_name, debug=None, overwrite=False):
debug_level = get_table_debug_level(tableInstance, debug)
engine = tableInstance.session.bind.engine
tableObj = tableInstance.model.__table__
metadata = MetaData(bind=engine)
table_name = tableInstance.model.__tablename__
table_rows = tableInstance.rowCount()
physical_table = Table(table_name, metadata, autoload=True)
physical_table.__tablename__ = table_name
columns = [c.copy() for c in physical_table.columns]
#temp_schema={}
# PHYSICAL_TABLE_TABLE = db_table_class(physical_table, temp_schema, engine, tableInstance.session, debug_level - 1)
# query_rows1 = PHYSICAL_TABLE_TABLE.rowCount()
query_rows1 = engine.execute(f"select count(*) from {table_name}").scalar()
# m = MetaData()
# m.reflect(engine)
# for table in m.tables.values():
# print(table.name)
# # for column in table.c:
# # print(column.name)
if not overwrite:
tables_list = get_tables_directory(engine)
xnew_table_name = new_table_name
ix = 0
while ix <= 99:
ix = ix + 1
if xnew_table_name in tables_list:
xnew_table_name = new_table_name + '_' + str(ix)
else:
break
new_table_name = xnew_table_name
new_table = Table(new_table_name, metadata, *columns)
new_table.drop(engine, checkfirst=True)
new_table.create(engine, checkfirst=True)
new_table = Table(new_table_name, metadata, autoload=True)
columns = [c.copy() for c in new_table.columns]
# temp_schema={}
# NEW_TABLE_TABLE = db_table_class(new_table, temp_schema, engine, tableInstance.session, debug_level - 1)
columns_str = ''
for column in columns:
if columns_str:
columns_str = columns_str + ' , ' + column.name
else:
columns_str = column.name
from_table = tableInstance.model.__tablename__
to_table = new_table.name
ddl_string = f"INSERT INTO {to_table} ({columns_str}) select {columns_str} from {from_table}"
if int(debug_level) > 0:
msg = f"table [[{tableInstance.model.__tablename__}]] copy records to [[{to_table}]] DDL: [[[{ddl_string}]]]"
log_message(msg)
try:
engine.execute(ddl_string)
except Exception as e:
print(e)
return False
# query_rows2= NEW_TABLE_TABLE.rowCount()
query_rows2 = engine.execute(f"select count(*) from {to_table}").scalar()
#garbage kill
del new_table
del physical_table
# del PHYSICAL_TABLE_TABLE
# del NEW_TABLE_TABLE
msg = f"table [[{from_table}]] [copied to ] [[{to_table}]] with [[[{query_rows2}/{query_rows1} rows]]]."
if int(debug_level) > 0:
log_message(msg)
if query_rows1 == query_rows2:
return to_table
else:
return None
APIS = db_table(db_schema, db_session,db_model.API,'apis','api')
APPLICATIONS = db_table(db_schema, db_session,db_model.APPLICATION,'applications','application')
REGISTERED_APIS = db_table(db_schema, db_session,db_model.APPLICATION_API,'registered apis','registered api')
TOKENS = db_table(db_schema, db_session,db_model.TOKEN,'tokens','token')
MERCHANTS = db_table(db_schema, db_session,db_model.MERCHANT,'merchants','merchant')
POINTS_OF_SALES = db_table(db_schema, db_session,db_model.POINT_OF_SALE,'points_of_sale','point_of_sale')
MERCHANT_EMPLOYEES = db_table(db_schema, db_session,db_model.MERCHANT_EMPLOYEE,'merchant_employees','employee')
CONSUMERS = db_table(db_schema, db_session,db_model.CONSUMER,'consumers','consumer')
BANKS = db_table(db_schema, db_session,db_model.BANK,'banks','bank')
BANK_SUBSCRIPTIONS = db_table(db_schema, db_session,db_model.BANK_SUBSCRIPTION,'bank_subscriptions','bank_subscription')
BANK_AUTHORIZATIONS = db_table(db_schema, db_session,db_model.BANK_AUTHORIZATION,'bank_authorizations','bank_authorization')
BANK_ACCOUNTS = db_table(db_schema, db_session,db_model.BANK_ACCOUNT,'BANK_ACCOUNTS','bank_account')
DEVICES = db_table(db_schema, db_session,db_model.DEVICE,'devices','device')
DEVICE_USAGE = db_table(db_schema, db_session, db_model.DEVICE_USAGE, 'device_usage', 'device_usage')
CLIENT_DEVICES = db_table(db_schema, db_session,db_model.CLIENT_DEVICE,'client_devices','client_device')
INTERACTIONS = db_table(db_schema, db_session,db_model.INTERACTION,'interactions','interaction')
INTERACTION_MESSAGES = db_table(db_schema, db_session, db_model.INTERACTION_MESSAGE, 'interaction_messages', 'interaction_message')
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
t = 0
r = 0
for table_alias in db_schema:
tableObj = db_schema.get(table_alias)
if tableObj:
t = t + 1
r = r + tableObj.table_rows
msg=f"database tables schema [ganimides] loaded with [[{t} tables]] and [{r} rows]"
if thisApp.CONSOLE_ON:
log_message(msg)
else:
log_message(msg)
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
'module_is_externally_configurable': False,
}
module_configuration = {}
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
module_configuration = retrieve_module_configuration(
__file__,
module_identityDictionary,
module_configuration,
print_enabled=thisApp.DEBUG_ON,
filelog_enabled=thisApp.FILELOG_ON,
handle_as_init=False)
#(print_enabled, filelog_enabled, log_file, errors_file,consolelog_enabled)=get_globals_from_configuration(module_configuration)
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
msg = f"database [ganimides] [[[[engine created]]]]:[[{database_uri}]] with [{tables_modeled} tables]"
if thisApp.get_module_debug_level(module_id):
log_message(msg)
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# 2. import the models
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
import _database_ganimides_model as dbmodel
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# 3. create missing tables (use the Base of the model. Base must be declared only once)
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
