Автоматическое построение диаграмм сущность-связь

Я не люблю всякие визуальные редакторы для создания ER-моделей. Предпочитаю писать SQL код вручную, но для всяких отчетов и обсуждений хотелось иметь графическое представление и желательно в векторном формате.

Достичь этого удалось довольно легко на Python с помощью стандартного модуля re и дополнительного gv для связи с библиотекой Graphviz.Начну сразу с результата. Вот как выглядит диаграмма сущность-связь для системы документооборота и учета нагрузки в ВУЗе, точнее часть системы.

Основная идея поиска с помошью регулярных выражений состоит вначале в нахождении оператора CREATE TABLE. Он может быть описан с помощью следующий конструкции:

table_re = re.compile(r"CREATEs+TABLEs+([w_.]+)s*(([^;]*));", re.UNICODE)

Затем выделения идентификаторов строк описывающих клонки таблицы:

identifier_re = re.compile(r"s*([w_.]+)s*", re.UNICODE)

При их разборе, также осуществляется поиск конструкций связанных с первичными и внешними ключами.
Также одновременно создается словарь таблиц и связей между ними с учетом конкретных колонок, на которые эти связи указывают.

После построение словаря таблиц и связей между ними с помощью модуля gv для связи с библиотекой Graphviz производится непосредственное построение диаграмм сущность-связь. При построение было обнаружено некорректное построение связей таблиц при работе с русскими названиями. Выход был найден с использованием html кода, укороченную версию которого поддерживает Graphviz.

В результате программа корректно рисует связи между таблицами. Первичный ключ имеет световую подсветку.

Текст самой программы ниже. Разбор с помощью регулярных выражений сделан поверхностно, лишь бы заработало. Он не умеет парсить SQL код содержащий конструкции отличные от CREATE TABLE. Все никак руки не доходят до клавиатуры, чтобы сделать все грамотно. Так что код не очень.

#-*- coding: utf-8 -*-

import re, gv

sql = "".join(open('LP.sql', 'r')).decode('utf-8')

class Table:
  tables = {}

  def __init__(self, name):
    assert name not in Table.tables
    Table.tables[name] = self
    self.name = name
    self.cols = []
    self.primarys = []
    self.references = {}

table_re = re.compile(r"CREATEs+TABLEs+([w_.]+)s*(([^;]*));", re.UNICODE)
identifier_re = re.compile(r"s*([w_.]+)s*", re.UNICODE)
i = 0
while True:
  m = table_re.search(sql)
  if not m:
    break
  sql = sql[len(m.group(0)):]
  tbl = Table(m.group(1).encode('utf-8'))
  i += 1
  tbl.i = i
  sql1 = m.group(2)
  while True:
    m1 = identifier_re.search(sql1)
    if not m1:
      break
    sql1 = sql1[len(m1.group(0)):]
    left, comma = sql1.find('('), sql1.find(',')
    if left >=0 and left < comma:
      comma = sql1.find(',', sql1.find(')', left + 1) + 1)
    if comma > 0:
      tail = sql1[:comma]
      sql1 = sql1[comma:]
    else:
      tail = sql1
      sql1 = ""
    if m1.group(1) not in ['UNIQUE', 'CHECK']:
      tbl.cols.append(m1.group(1).encode('utf-8'))
      f = tail.find('PRIMARY')
      if f >= 0:
        tbl.primarys.append(m1.group(1).encode('utf-8'))
      f = tail.find('REFERENCES')
      if f >= 0:
        m1 = identifier_re.search(tail[f+len('REFERENCES'):])
        assert m1
        tbl.references[tbl.cols[-1]] = m1.group(1).encode('utf-8')

g = gv.graph('LP')
for tbl in Table.tables.itervalues():
  tbl.node = gv.node(g, 'tbl%d' % tbl.i)
  gv.setv(tbl.node, 'shape', 'plaintext')
  lst = []
  for c in tbl.cols:
    if tbl.primarys and c == tbl.primarys[0]:
      lst.append('<TR><TD ALIGN="LEFT" BGCOLOR="LightGoldenrod" PORT="pk">%s</TD></TR>' % c)
    elif c in tbl.references:
      lst.append('<TR><TD ALIGN="LEFT" PORT="fk%d">%s</TD></TR>' % (tbl.cols.index(c), c))
    else:
      lst.append('<TR><TD ALIGN="LEFT">%s</TD></TR>' % c)
  gv.setv(tbl.node, 'label', """<
<FONT FACE="Helvetica">
<TABLE BORDER="0" BGCOLOR="Bisque" CELLBORDER="0" CELLSPACING="0"
    CELLPADDING="1" VALIGN="MIDDLE">
  <TR><TD ALIGN="CENTER" BGCOLOR="LightBlue"><FONT
    FACE="Helvetica Bold">%s</FONT></TD></TR>
  %s
</TABLE></FONT>>""" % (tbl.name, 'n  '.join(lst)))

for tbl in Table.tables.itervalues():
  for c, r in tbl.references.iteritems():
    e = gv.edge(tbl.node, Table.tables[r].node)
    gv.setv(e, 'style', 'solid')
    gv.setv(e, 'dir', 'both')
    gv.setv(e, 'arrowtail', 'dot')
    gv.setv(e, 'arrowhead', 'odiamond')
    gv.setv(e, 'tailport', 'fk%d' % tbl.cols.index(c))
    gv.setv(e, 'headport', 'pk')

gv.layout(g, 'dot')
gv.render(g, 'svg', './LP.svg')
gv.render(g, 'pdf', './LP.pdf')
gv.render(g, 'png', './LP.png')

Если кому интересно ниже исходный SQL код.

CREATE TABLE tblФормыОбучения (
  pk_формаОбучения        SERIAL PRIMARY KEY,
  мнемо                   TEXT UNIQUE NOT NULL,
  название                TEXT UNIQUE NOT NULL
);

CREATE TABLE tblУровниОбучения (
  pk_уровеньОбучения      SERIAL PRIMARY KEY,
  мнемо                   TEXT UNIQUE NOT NULL,
  название                TEXT UNIQUE NOT NULL
);

CREATE TABLE tblПредметы (
  pk_предмет              SERIAL PRIMARY KEY,
  мнемо                   TEXT UNIQUE NOT NULL,
  название                TEXT UNIQUE NOT NULL
);

CREATE TABLE tblРазбиения (
  pk_разбиение            SERIAL PRIMARY KEY,
  мнемо                   TEXT UNIQUE NOT NULL,
  название                TEXT UNIQUE NOT NULL
);

CREATE TABLE tblУчебныеГода (
  pk_учебныйГод           SERIAL PRIMARY KEY,
  мнемо                   TEXT UNIQUE NOT NULL,
  название                TEXT UNIQUE NOT NULL
);

CREATE TABLE tblФакультеты (
  pk_факультет            SERIAL PRIMARY KEY,
  мнемо                   TEXT UNIQUE NOT NULL,
  название                TEXT UNIQUE NOT NULL,
  логин                   TEXT UNIQUE NOT NULL,
  пароль                  TEXT NOT NULL
);

CREATE TABLE tblСпециальности (
  pk_специальность        SERIAL PRIMARY KEY,
  fk_учебныйГод           INTEGER REFERENCES tblУчебныеГода(pk_учебныйГод) NOT NULL,
  fk_уровеньОбучения      INTEGER REFERENCES tblУровниОбучения(pk_уровеньОбучения) NOT NULL,
  номер                   TEXT NOT NULL,
  мнемо                   TEXT NOT NULL,
  название                TEXT NOT NULL,
  стандарт                BYTEA,
  дополнение              HSTORE,
  UNIQUE(fk_учебныйГод, fk_уровеньОбучения, номер),
  UNIQUE(fk_учебныйГод, fk_уровеньОбучения, мнемо),
  UNIQUE(fk_учебныйГод, fk_уровеньОбучения, название)
);

CREATE TABLE tblКафедры (
  pk_кафедра              INTEGER PRIMARY KEY,
  fk_учебныйГод           INTEGER REFERENCES tblУчебныеГода(pk_учебныйГод) NOT NULL,
  fk_факультет            INTEGER REFERENCES tblФакультеты(pk_факультет) NOT NULL,
  мнемо                   TEXT UNIQUE NOT NULL,
  название                TEXT UNIQUE NOT NULL,
  логин                   TEXT UNIQUE NOT NULL,
  пароль                  TEXT NOT NULL,
  дополнение              HSTORE,
  UNIQUE(fk_учебныйГод, fk_факультет, мнемо),
  UNIQUE(fk_учебныйГод, fk_факультет, название)
);

CREATE TABLE tblПотоки (
  pk_поток                SERIAL PRIMARY KEY,
  fk_учебныйГод           INTEGER REFERENCES tblУчебныеГода(pk_учебныйГод) NOT NULL,
  fk_факультет            INTEGER REFERENCES tblФакультеты(pk_факультет) NOT NULL,
  название                TEXT NOT NULL,
  дополнение              HSTORE,
  UNIQUE(fk_учебныйГод, fk_факультет, название)
);

CREATE TABLE tblГруппы (
  pk_группа               SERIAL PRIMARY KEY,
  fk_формаОбучения        INTEGER REFERENCES tblФормыОбучения(pk_формаОбучения) NOT NULL,
  fk_специальность        INTEGER REFERENCES tblСпециальности(pk_специальность) NOT NULL,
  курс                    INTEGER NOT NULL,
  комм                    BOOLEAN NOT NULL,
  колБюджет               INTEGER NOT NULL,
  колКомм                 INTEGER NOT NULL,
  дополнение              HSTORE
) INHERITS (tblПотоки);

CREATE TABLE tblПодгруппы (
  pk_подгруппа            SERIAL PRIMARY KEY,
  fk_группа               INTEGER REFERENCES tblГруппы(pk_группа) NOT NULL,
  fk_разбиение            INTEGER REFERENCES tblРазбиения(pk_разбиение) NOT NULL,
  колБюджет               INTEGER NOT NULL,
  колКомм                 INTEGER NOT NULL,
  дополнение              HSTORE
) INHERITS (tblПотоки);

CREATE TABLE tblПотоковЛес (
  fk_поток                INTEGER REFERENCES tblПотоки(pk_поток),
  fk_надПоток             INTEGER REFERENCES tblПотоки(pk_поток),
  UNIQUE(fk_поток, fk_надПоток)
);

CREATE TABLE tblЗаявки (
  pk_заявка               SERIAL PRIMARY KEY,
  fk_предмет              INTEGER REFERENCES tblПредметы(pk_предмет) NOT NULL,
  fk_поток                INTEGER REFERENCES tblПотоки(pk_поток) NOT NULL,
  fk_кафедра              INTEGER REFERENCES tblКафедры(pk_кафедра),
  семестр                 BOOLEAN NOT NULL,
  УМК                     BYTEA,
  дополнение              HSTORE,
-- аудиторные занятия
  лек                     INTEGER,
  сем                     INTEGER,
  лаб                     INTEGER,
  СРС                     INTEGER,
  "т/конс"                BOOLEAN,
  экз                     BOOLEAN,
  зач                     BOOLEAN,
  "п/зач"                 BOOLEAN,
  контр                   BOOLEAN,
-- руководство
  "курс/раб"              BOOLEAN,
  "дип/бак"               BOOLEAN,
-- дополнительная
  практ                   REAL,
  ГЭК                     INTEGER,
  ГАК                     INTEGER,
  прием                   INTEGER,
  курат                   INTEGER,
  "асп/рук"               INTEGER,
  факул                   INTEGER,
  UNIQUE(fk_предмет, fk_поток, семестр)
);

Если на вход подавать таблицы разбивая на связные группы, то получится набор вполне читаемых диаграмм.

Также неплохо сделать наследование, но нет времени. Для этой задачи лучше иметь иметь иерархическое представление групп, подгрупп и потоков.

PS.Топик не посвящен использованию русских букв в SQL, а автоматическому построению диаграмм. Если кого-то раздражает зайдите на сайт www.translit.ru и переведите SQL код и читайте его на здоровье. Для сильно знающих английский, переведите, например, себе термины:

ГЭК — Государственная экзаменационная комиссия (The State Board of Examiners — google);

ГАК — Государственная аттестационная комиссия (National Certification Commission — google).

Автор: bya