Commits

Mike Bayer committed 28d4092

initial rev, turn things into a respectable format

  • Participants

Comments (0)

Files changed (4)

+*.pyc
+.*.swp
+*.egg-info
+.venv
+build/
+dist/
+.coverage
+Schema to Graffle
+=================
+
+Uses the Applescript API to render a database model in
+OmniGraffle.
+
+Usage::
+
+	Usage: schema_to_graffle.py [options]
+
+	Options:
+	  -h, --help       show this help message and exit
+	  --app=APP        Application name
+	  --uri=URI        Database uri
+	  --tables=TABLES  List of table names to generate, separated by spaces
+	  --schema=SCHEMA  Schema name
+

File schema_to_graffle.py

+#!/usr/bin/env python
+
+# schema_to_graffle.py
+# Original code + concept by Paul Davis
+# modernizations by Mike Bayer
+
+from appscript import *
+from sqlalchemy import create_engine
+from sqlalchemy.engine import reflection
+
+
+
+# Common to title and all types of columns.
+common_props = {}
+common_props[k.shadow_vector] = [7.0, 7.0]
+common_props[k.shadow_fuzziness] = 17.45
+common_props[k.autosizing] = k.full
+common_props[k.text_placement] = k.top
+common_props[k.draws_stroke] = False
+common_props[k.fill] = k.linear_fill
+common_props[k.fill_color] = [1, 1, 1]
+common_props[k.gradient_center] = [0.9, 0]
+common_props[k.magnets] = [[1, 0], [-1, 0]]
+#common_props[k.size] = [90, 14]
+
+# Table Name
+table_name = common_props.copy()
+table_name[k.gradient_color] = [.15, .9, .1]
+
+# right hand column after 'table name'
+# would be better if we could lay out a single "header"
+# but I don't know omnigraffle well enough to figure this
+# out
+table_name_contd = common_props.copy()
+table_name_contd[k.fill_color] = [.15, .9, .1]
+del table_name_contd[k.gradient_center]
+del table_name_contd[k.fill]
+
+# column
+column_norm = common_props.copy()
+column_norm[k.gradient_color] = [1, 1, 1]
+
+#Line Properties
+line_props = {}
+line_props[k.line_type] = k.orthogonal
+line_props[k.head_type] = "FilledArrow"
+line_props[k.jump] = True
+
+
+class Render(object):
+    def __init__(self, omnigraffle):
+        self.omnigraffle = omnigraffle
+
+        self.graffle = app(omnigraffle)
+
+        self.document = self.graffle.make(new=k.document, with_properties={})
+
+        self.og_tables = {}
+
+    def render_tables(self, info):
+        for tname in info.sql_tables:
+            self._render_table(tname, info.sql_tables[tname])
+
+    def _render_table(self, name, sql_table):
+        print("Rendering table %s" % name)
+
+        graphics = self.document.canvases[1].graphics
+
+        shapes = []
+        shapes.append(self._make_shape(graphics, name, table_name))
+        shapes.append(self._make_shape(graphics, '', table_name_contd))
+
+        for colname, type_, nullable, attrs in sql_table:
+
+            type_text = type_.__class__.__name__
+            if attrs.get('pk'):
+                type_text += " (PK)"
+            if attrs.get('fk'):
+                type_text += " (FK)"
+
+            shapes.append(self._make_shape(graphics, colname, column_norm))
+            shapes.append(self._make_shape(graphics, type_text, column_norm))
+
+        self.og_tables[name] = self.graffle.assemble(shapes, table_shape=[len(shapes) / 2, 2])
+        self.og_tables[name].slide(by={k.x: 25, k.y: 25})
+
+    def render_references(self, info):
+        for ref in info.sql_references:
+            self._render_reference(ref)
+
+    def _make_shape(self, graphics, text, props):
+        graphics.end.make(new=k.shape, with_properties=props)
+        shape = graphics.last.get()
+        shape.text.set(text)
+        return shape
+
+    def _get_og_graphics_from_reference(self, sql_reference, connect_cells=False):
+
+        ftbl_name, fcol, ttbl_name, tcol, unique, nullable = sql_reference
+
+        og_tables = self.og_tables
+
+        ftbl = og_tables.get(ftbl_name)
+        if ftbl is None:
+            return None, None
+
+        ttbl = og_tables.get(ttbl_name)
+        if ttbl is None:
+            return None, None
+
+        if connect_cells:
+            for col in ftbl.columns[1].graphics.get():
+                if col.text.get() == fcol:
+                    fg = col.get()
+                    break
+            else:
+                raise Exception(
+                    "Failed to find graphic for %s(%s)" % (ftbl_name, fcol)
+                )
+
+
+            for col in ttbl.columns[1].graphics.get():
+                if col.text.get() == tcol:
+                    tg = col.get()
+                    break
+            else:
+                raise Exception(
+                    "Failed to find graphic for %s(%s)" % (ttbl_name, tcol)
+                )
+            return fg, tg
+        else:
+            return ftbl, ttbl
+
+    def _render_reference(self, sql_reference):
+        print("rendering reference %s" % (sql_reference,))
+        from_, to_ = self._get_og_graphics_from_reference(sql_reference)
+        ftbl_name, fcol, ttbl_name, tcol, unique, nullable = sql_reference
+
+        # CrowBall, CrowBar, DoubleBar, BarBall
+        if from_ is not None and to_ is not None:
+            props = line_props.copy()
+            props[k.head_type] = 'DoubleBar'
+            if unique:
+                if nullable:
+                    props[k.tail_type] = 'BarBall'
+                else:
+                    props[k.tail_type] = 'DoubleBar'
+            elif nullable:
+                props[k.tail_type] = 'CrowBall'
+            else:
+                props[k.tail_type] = 'CrowBar'
+            from_.connect(to=to_, with_properties=props)
+
+class Graffle5Render(Render):
+    def layout_canvas(self):
+        self.document.\
+                    canvases[1].layout_info.\
+                    properties.set(
+                            {
+                                k.type: k.force_directed,
+                                k.radial_separation: .5
+                            })
+
+        self.document.canvases[1].layout()
+
+class Graffle4Render(Render):
+    def layout_canvas(self):
+        self.document.\
+                    canvases[1].layout_info.\
+                    properties.set(
+                            {
+                                k.random_start: False,
+                                k.animates: True,
+                                k.type: k.force_directed,
+                                k.edge_force: 20.0
+                            })
+        self.document.canvases[1].layout()
+
+class Info(object):
+    def __init__(self, url, schema, only_tables):
+        engine = create_engine(url)
+
+        sql_tables = {}
+        sql_references = []
+
+        inspector = reflection.Inspector.from_engine(engine)
+
+        for table_name in inspector.get_table_names(schema=schema):
+            if only_tables and table_name not in only_tables:
+                continue
+            primary_key = inspector.get_primary_keys(table_name, schema=schema)
+            foreign_keys = inspector.get_foreign_keys(table_name, schema=schema)
+
+            cols_with_fk = set()
+            for rec in foreign_keys:
+                cols_with_fk.update(rec['constrained_columns'])
+
+            sql_tables[table_name] = table = []
+
+            col_lookup = {}
+            for column in inspector.get_columns(table_name, schema=schema):
+                rec = (
+                                column['name'],
+                                column['type'],
+                                column['nullable'],
+                                {
+                                    'pk': column['name'] in primary_key,
+                                    'fk': column['name'] in cols_with_fk
+                                }
+                            )
+                table.append(rec)
+                col_lookup[(table_name, column['name'])] = rec
+
+            for rec in foreign_keys:
+                for fcol, tcol in zip(rec['constrained_columns'], rec['referred_columns']):
+                    fcol_rec = col_lookup[(table_name, fcol)]
+                    sql_references.append((
+                                            table_name,
+                                            fcol,
+                                            rec['referred_table'],
+                                            tcol,
+                                            fcol_rec[3]['pk'],
+                                            fcol_rec[2]))
+        self.sql_tables = sql_tables
+        self.sql_references = sql_references
+
+
+def main(argv=None, **kwargs):
+    import optparse
+    import sys
+
+    GRAFFLE = "/Applications/OmniGraffle Professional 5.app"
+
+    #GRAFFLE = "Omnigraffle Professional"
+
+    parser = optparse.OptionParser(
+        "usage: %prog [options]"
+    )
+    parser.add_option("--app", type="string",
+                        default=GRAFFLE,
+                        help="Application name")
+    parser.add_option("--uri", type="string", help="Database uri")
+    parser.add_option("--tables", type="string",
+                help="List of table names to generate, separated by spaces")
+    parser.add_option("--schema", type="string",
+                            default="public", help="Schema name")
+
+    options, argv = parser.parse_args(argv)
+
+    if not options.uri:
+        sys.exit("--uri argument is required")
+
+    if options.tables:
+        only_tables = set([t.strip() for t in options.tables.split(" ")])
+    else:
+        only_tables = None
+
+    info = Info(options.uri, options.schema, only_tables)
+
+    renderer = Graffle5Render(options.app)
+    #renderer = Graffle4Render(options.app)
+
+    renderer.render_tables(info)
+    renderer.layout_canvas()
+    renderer.render_references(info)
+
+if __name__ == '__main__':
+    main()
+
+from setuptools import setup
+import os
+
+readme = os.path.join(os.path.dirname(__file__), 'README.rst')
+
+setup(name='schema_to_graffle',
+      version="0.1",
+      description="Render database schemas as OmniGraffle docuemnts",
+      long_description=open(readme).read(),
+      classifiers=[
+      'Development Status :: 3 - Alpha',
+      'Environment :: Console',
+      'Intended Audience :: Developers',
+      'Programming Language :: Python',
+      'Programming Language :: Python :: 3',
+      'Programming Language :: Python :: Implementation :: CPython',
+      ],
+      keywords='Blogofile',
+      author='Mike Bayer',
+      author_email='mike@zzzcomputing.com',
+      license='MIT',
+      py_modules=['schema_to_graffle'],
+      zip_safe=False,
+      install_requires=[
+            'SQLAlchemy>=0.8.0',
+            "appscript"
+      ],
+      entry_points={
+        'console_scripts': ['schema_to_graffle = schema_to_graffle:main'],
+      },
+)