in mrs_plugin/db_schema/mysql_rest_service_metadata.msm.project/development/wb/Audit_Log_Triggers_grt.py [0:0]
def create_audit_log_table():
import datetime
# iterate through all tables from schema
schema = grt.root.wb.doc.physicalModels[0].catalog.schemata[0]
# Check if audit_log table is already in the schema
audit_log_table = False
for table in schema.tables:
if table.name == 'audit_log':
audit_log_table = True
break
if not audit_log_table:
audit_table = schema.addNewTable("db.mysql") # grt.classes.db_mysql_Table()
audit_table.owner = grt.root.wb.doc.physicalModels[0].catalog.schemata[0]
audit_table.name = "audit_log"
audit_table.oldName = "audit_log"
audit_table.tableEngine = "InnoDB"
audit_table.createDate = f'{datetime.datetime.now():%Y-%m-%d %H:%M:%S}'
audit_table.lastChangeDate = f'{datetime.datetime.now():%Y-%m-%d %H:%M:%S}'
# Column ---------
c_id = grt.classes.db_mysql_Column()
c_id.autoIncrement = 1
c_id.formattedType = "INT"
c_id.isNotNull = 1
c_id.length = -1
c_id.name = "id"
c_id.oldName = "id"
c_id.precision = -1
c_id.scale = -1
c_id.owner = audit_table
c_id.simpleType = grt.root.wb.doc.physicalModels[0].catalog.simpleDatatypes[3]
audit_table.addColumn(c_id)
# Column ---------
c_t_n = grt.classes.db_mysql_Column()
c_t_n.autoIncrement = 0
c_t_n.formattedType = "VARCHAR(255)"
c_t_n.isNotNull = 1
c_t_n.length = 255
c_t_n.name = "table_name"
c_t_n.oldName = "table_name"
c_t_n.precision = -1
c_t_n.scale = -1
c_t_n.owner = audit_table
c_t_n.simpleType = grt.root.wb.doc.physicalModels[0].catalog.simpleDatatypes[11]
audit_table.addColumn(c_t_n)
# Column ---------
c = grt.classes.db_mysql_Column()
c.autoIncrement = 0
c.datatypeExplicitParams = "('INSERT','UPDATE','DELETE')"
c.formattedType = "ENUM('INSERT','UPDATE','DELETE')"
c.isNotNull = 1
c.length = -1
c.name = "dml_type"
c.oldName = "dml_type"
c.precision = -1
c.scale = -1
c.owner = audit_table
c.simpleType = grt.root.wb.doc.physicalModels[0].catalog.simpleDatatypes[42]
audit_table.addColumn(c)
# Column ---------
c = grt.classes.db_mysql_Column()
c.autoIncrement = 0
c.formattedType = "JSON"
c.isNotNull = 0
c.length = -1
c.name = "old_row_data"
c.oldName = "old_row_data"
c.precision = -1
c.scale = -1
c.owner = audit_table
c.simpleType = grt.root.wb.doc.physicalModels[0].catalog.simpleDatatypes[23]
audit_table.addColumn(c)
# Column ---------
c = grt.classes.db_mysql_Column()
c.autoIncrement = 0
c.formattedType = "JSON"
c.isNotNull = 0
c.length = -1
c.name = "new_row_data"
c.oldName = "new_row_data"
c.precision = -1
c.scale = -1
c.owner = audit_table
c.simpleType = grt.root.wb.doc.physicalModels[0].catalog.simpleDatatypes[23]
audit_table.addColumn(c)
# Column ---------
c_c_b = grt.classes.db_mysql_Column()
c_c_b.autoIncrement = 0
c_c_b.formattedType = "VARCHAR(255)"
c_c_b.isNotNull = 1
c_c_b.length = 255
c_c_b.name = "changed_by"
c_c_b.oldName = "changed_by"
c_c_b.precision = -1
c_c_b.scale = -1
c_c_b.owner = audit_table
c_c_b.simpleType = grt.root.wb.doc.physicalModels[0].catalog.simpleDatatypes[11]
audit_table.addColumn(c_c_b)
# Column ---------
c_c_a = grt.classes.db_mysql_Column()
c_c_a.autoIncrement = 0
c_c_a.formattedType = "TIMESTAMP"
c_c_a.isNotNull = 1
c_c_a.length = -1
c_c_a.name = "changed_at"
c_c_a.oldName = "changed_at"
c_c_a.precision = -1
c_c_a.scale = -1
c_c_a.owner = audit_table
c_c_a.simpleType = grt.root.wb.doc.physicalModels[0].catalog.simpleDatatypes[31]
audit_table.addColumn(c_c_a)
# Column ---------
c_o_r_i = grt.classes.db_mysql_Column()
c_o_r_i.autoIncrement = 0
c_o_r_i.expression = 'old_row_data->"$.id"'
c_o_r_i.formattedType = "INT"
c_o_r_i.generated = 1
c_o_r_i.isNotNull = 0
c_o_r_i.length = -1
c_o_r_i.name = "old_row_id"
c_o_r_i.oldName = "old_row_id"
c_o_r_i.precision = -1
c_o_r_i.scale = -1
c_o_r_i.owner = audit_table
c_o_r_i.simpleType = grt.root.wb.doc.physicalModels[0].catalog.simpleDatatypes[3]
audit_table.addColumn(c_o_r_i)
# Column ---------
c_n_r_i = grt.classes.db_mysql_Column()
c_n_r_i.autoIncrement = 0
c_n_r_i.expression = 'new_row_data->"$.id"'
c_n_r_i.formattedType = "INT"
c_n_r_i.generated = 1
c_n_r_i.isNotNull = 0
c_n_r_i.length = -1
c_n_r_i.name = "new_row_id"
c_n_r_i.oldName = "new_row_id"
c_n_r_i.precision = -1
c_n_r_i.scale = -1
c_n_r_i.owner = audit_table
c_n_r_i.simpleType = grt.root.wb.doc.physicalModels[0].catalog.simpleDatatypes[3]
audit_table.addColumn(c_n_r_i)
# Index ---------
i = grt.classes.db_mysql_Index()
i.name = "PRIMARY"
i.oldName = "PRIMARY"
i.indexType = "PRIMARY"
i.owner = audit_table
i.visible = 1
i.isPrimary = 1
# Index Column ---------
ic = grt.classes.db_mysql_IndexColumn()
ic.owner = i
ic.referencedColumn = c_id
i.columns.append(ic)
audit_table.addIndex(i)
audit_table.primaryKey = i
# Index ---------
i = grt.classes.db_mysql_Index()
i.name = "idx_table_name"
i.oldName = "idx_table_name"
i.indexType = "INDEX"
i.owner = audit_table
i.visible = 1
i.isPrimary = 0
# Index Column ---------
ic = grt.classes.db_mysql_IndexColumn()
ic.owner = i
ic.referencedColumn = c_t_n
i.columns.append(ic)
audit_table.addIndex(i)
# Index ---------
i = grt.classes.db_mysql_Index()
i.name = "idx_changed_at"
i.oldName = "idx_changed_at"
i.indexType = "INDEX"
i.owner = audit_table
i.visible = 1
i.isPrimary = 0
# Index Column ---------
ic = grt.classes.db_mysql_IndexColumn()
ic.owner = i
ic.referencedColumn = c_c_a
i.columns.append(ic)
audit_table.addIndex(i)
# Index ---------
i = grt.classes.db_mysql_Index()
i.name = "idx_changed_by"
i.oldName = "idx_changed_by"
i.indexType = "INDEX"
i.owner = audit_table
i.visible = 1
i.isPrimary = 0
# Index Column ---------
ic = grt.classes.db_mysql_IndexColumn()
ic.owner = i
ic.referencedColumn = c_c_b
i.columns.append(ic)
audit_table.addIndex(i)
# Index ---------
i = grt.classes.db_mysql_Index()
i.name = "idx_new_row_id"
i.oldName = "idx_new_row_id"
i.indexType = "INDEX"
i.owner = audit_table
i.visible = 1
i.isPrimary = 0
# Index Column ---------
ic = grt.classes.db_mysql_IndexColumn()
ic.owner = i
ic.referencedColumn = c_n_r_i
i.columns.append(ic)
audit_table.addIndex(i)
# Index ---------
i = grt.classes.db_mysql_Index()
i.name = "idx_old_row_id"
i.oldName = "idx_old_row_id"
i.indexType = "INDEX"
i.owner = audit_table
i.visible = 1
i.isPrimary = 0
# Index Column ---------
ic = grt.classes.db_mysql_IndexColumn()
ic.owner = i
ic.referencedColumn = c_o_r_i
i.columns.append(ic)
audit_table.addIndex(i)