pyrocmsfields

gistfile1.txt

# -*- 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 = True
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
   hasCreatedById = False
   hasUpdatedById = False
   hasSortOrder = 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 == 'sort_order'):
                hasSortOrder = True
            elif (name == 'created_at'):
                hasCreatedAt = True
            elif (name == 'created_by_id'):
                hasCreatedById = True
            elif (name == 'updated_at'):
                hasUpdatedAt = True
            elif (name == 'updated_by_id'):
                hasUpdatedById = True
            elif (name == 'deleted_at'):
                hasDeletedAt = True

       if (hasSortOrder == False):
           column = grt.classes.db_mysql_Column()
           column.name = "sort_order"
           # add it to the table
           table.addColumn(column)


       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 (hasCreatedById == False):
           column = grt.classes.db_mysql_Column()
           column.name = "created_by_id"
           # 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)

       if (hasUpdatedById == False):
           column = grt.classes.db_mysql_Column()
           column.name = "updated_by_id"
           # add it to the table
           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 = 0
                column.setParseType("TIMESTAMP", None)
                column.defaultValue = "NULL"
                #column.defaultValue = "'0000-00-00 00:00:00'"
            elif (name == 'deleted_at'):
                column.isNotNull = 0
                column.setParseType("TIMESTAMP", None)
                column.defaultValue = "NULL"
            elif (name == 'created_by_id'):
                column.isNotNull = 1
                column.setParseType("INTEGER", None)
                column.flags.append('UNSIGNED')
            elif (name == 'updated_by_id'):
                column.isNotNull = 0
                column.setParseType("INTEGER", None)
                column.flags.append('UNSIGNED')
            elif (name == 'sort_order'):
                column.isNotNull = 1
                column.setParseType("INTEGER", None)
                column.flags.append('UNSIGNED')