Skip to content

Instantly share code, notes, and snippets.

@pepijnolivier
Last active October 31, 2020 12:48
Show Gist options
  • Save pepijnolivier/61ba3d69af2884ef2d6fa107d657e2d5 to your computer and use it in GitHub Desktop.
Save pepijnolivier/61ba3d69af2884ef2d6fa107d657e2d5 to your computer and use it in GitHub Desktop.
Create laravel timestamps on each table in mysqlworkbench.
# -*- coding: utf-8 -*-
# MySQL Workbench Python script
#
# <description>
# Written in MySQL Workbench 6.3.6
# Fetch all tables,
# For each table, add created_at, updated_at, deleted_at timestamps,
# Except for many-to-many tables
# Configurable options:
# addDeletedAt: True / False. Add the deleted_at timestamp column
# ignoreManyToMany: True / False. Don't add timestamps to many-to-many tables
addDeletedAt = False
ignoreManyToMany = True
import grt
# get a reference to the schema in the model. This will get the 1st schema in it.
schema = grt.root.wb.doc.physicalModels[0].catalog.schemata[0]
# iterate through all tables
for table in schema.tables:
print ''
print table.name
hasCreatedAt = False
hasUpdatedAt = False
hasDeletedAt = False
isManyToMany = False
# Detect many to many tables
tableColumnCount = len(table.columns)
if(tableColumnCount == 2):
fks = table.foreignKeys
tableForeignKeysCount = len(table.foreignKeys)
if(tableForeignKeysCount == 2):
isManyToMany = True
# Skip many to many tables
if((ignoreManyToMany == False) | (isManyToMany == False)):
# iterate all columns of the table,
# Find whether or not timestamps have already been created,
# If not, create
for column in table.columns:
name = column.name
print ' ' + name
if (name == 'created_at'):
hasCreatedAt = True
elif (name == 'updated_at'):
hasUpdatedAt = True
elif (name == 'deleted_at'):
hasDeletedAt = True
if(hasCreatedAt == False):
# create a new column object and set its name
column = grt.classes.db_mysql_Column()
column.name = "created_at"
# add it to the table
table.addColumn(column)
if(hasUpdatedAt == False):
# same thing for the updated_at column
column = grt.classes.db_mysql_Column()
column.name = "updated_at"
table.addColumn(column)
# same thing for the deleted_at column
if((addDeletedAt == True) & (hasDeletedAt == False)):
column = grt.classes.db_mysql_Column()
column.name = "deleted_at"
table.addColumn(column)
for column in table.columns:
name = column.name
if (name == 'created_at'):
column.isNotNull = 1
column.setParseType("TIMESTAMP", None)
column.defaultValue = "CURRENT_TIMESTAMP"
elif (name == 'updated_at'):
column.isNotNull = 1
column.setParseType("TIMESTAMP", None)
column.defaultValue = "NULL"
elif (name == 'deleted_at'):
column.isNotNull = 0
column.setParseType("TIMESTAMP", None)
column.defaultValue = "NULL"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment