connect table with foreign keys (singularize(table)_id)

connect_table_with_foreign_keys.py

# -*- coding: utf-8 -*-
# MySQL Workbench Python script
# connect tables with foreign keys
# table: plural (sites)
# foreign_key: singularize(table)_id (site_id)
# Written in MySQL Workbench 6.3.9

import grt
import mforms
from wb import *
import re

SINGULARS = [
    (r"(?i)(database)s$", r'\1'),
    (r"(?i)(quiz)zes$", r'\1'),
    (r"(?i)(matr)ices$", r'\1ix'),
    (r"(?i)(vert|ind)ices$", r'\1ex'),
    (r"(?i)^(ox)en", r'\1'),
    (r"(?i)(alias|status)(es)?$", r'\1'),
    (r"(?i)(octop|vir)(us|i)$", r'\1us'),
    (r"(?i)^(a)x[ie]s$", r'\1xis'),
    (r"(?i)(cris|test)(is|es)$", r'\1is'),
    (r"(?i)(shoe)s$", r'\1'),
    (r"(?i)(o)es$", r'\1'),
    (r"(?i)(bus)(es)?$", r'\1'),
    (r"(?i)(m|l)ice$", r'\1ouse'),
    (r"(?i)(x|ch|ss|sh)es$", r'\1'),
    (r"(?i)(m)ovies$", r'\1ovie'),
    (r"(?i)(s)eries$", r'\1eries'),
    (r"(?i)([^aeiouy]|qu)ies$", r'\1y'),
    (r"(?i)([lr])ves$", r'\1f'),
    (r"(?i)(tive)s$", r'\1'),
    (r"(?i)(hive)s$", r'\1'),
    (r"(?i)([^f])ves$", r'\1fe'),
    (r"(?i)(t)he(sis|ses)$", r"\1hesis"),
    (r"(?i)(s)ynop(sis|ses)$", r"\1ynopsis"),
    (r"(?i)(p)rogno(sis|ses)$", r"\1rognosis"),
    (r"(?i)(p)arenthe(sis|ses)$", r"\1arenthesis"),
    (r"(?i)(d)iagno(sis|ses)$", r"\1iagnosis"),
    (r"(?i)(b)a(sis|ses)$", r"\1asis"),
    (r"(?i)(a)naly(sis|ses)$", r"\1nalysis"),
    (r"(?i)([ti])a$", r'\1um'),
    (r"(?i)(n)ews$", r'\1ews'),
    (r"(?i)(ss)$", r'\1'),
    (r"(?i)s$", ''),
]

UNCOUNTABLES = set([
    'equipment',
    'fish',
    'information',
    'jeans',
    'money',
    'rice',
    'series',
    'sheep',
    'species',
])

def singularize(word):
    for inflection in UNCOUNTABLES:
        if re.search(r'(?i)\b(%s)\Z' % inflection, word):
            return word

    for rule, replacement in SINGULARS:
        if re.search(rule, word):
            return re.sub(rule, replacement, word)
    return word


def get_fk_candidate_list(schema, fk_name_format, match_types=False):
    candidate_list = []
    possible_fks = {}
    # create list of fk keys
    for table in schema.tables:
        if table.primaryKey and len(table.primaryKey.columns) == 1: # composite FKs not supported
            fkname = fk_name_format.format(table = singularize(table.name))
            possible_fks[fkname] = table
            possible_fks["target_" + fkname] = table

    # search refrence column
    for table in schema.tables:
        for column in table.columns:
            if possible_fks.has_key(column.name):
                ref_table = possible_fks[column.name]
                ref_column = ref_table.primaryKey.columns[0].referencedColumn
                if ref_column == column: # self match ignore
                    continue
                if match_types and ref_column.formattedType != column.formattedType: # type unmatch ignore
                    continue

                candidate_list.append((table, column, ref_table, ref_column))
    return candidate_list

catalog = grt.root.wb.doc.physicalModels[0].catalog

candidates = []
print "seach foreignKey"
for schema in catalog.schemata:
    candidates += get_fk_candidate_list(schema, "{table}_id")

for table, column, ref_table, ref_column in candidates:
    print(table.name, column.name, "=>", ref_table.name, ref_column.name)

connect = True
if connect:
    print "start connecting"
    for table, column, ref_table, ref_column in candidates:
        fk = table.createForeignKey(ref_column.name+"_fk")
        fk.referencedTable = ref_table
        fk.columns.append(column)
        fk.referencedColumns.append(ref_column)