Brad Allen wrote: > Faced with a large database schema, I am considering writing a script to > introspect the db and generate classes with Storm properties. This would > save a lot of manual labor, and could be re-run whenever our database > team changes the schema. > > Has anyone already written similar scripts? I would prefer not to > duplicate effort if someone has already done it. >
Hi! I`m do it for postgresql. Try script from attachment. Please email me directly if you make effort. -- Vsevolod Balashov http://vsevolod.balashov.name
#!/usr/bin/env python # -*- coding: utf-8 -*- # vim:ts=4:sw=4:et # # $Id: revers.py,v ef7cf5d3acc8 2008/01/31 13:44:59 vsevolod $ # # RevERS: Reverse Entity-Relationship Storm # (C) 2008 Vsevolod Balashov <[EMAIL PROTECTED]> under terms of GPL v3 http://www.gnu.org/licenses/gpl.html # # extract DB structire into Storm http://storm.canonical.com classes set (now only for PostgreSQL) # # This is very alpha... if you use it please send me feedback # # known problems: # - many to many relationships not detected ( 2 one to many ) # - postgres column types to storm field types translation is ugly # - many to one column names may be override other columns (not transform like Name -> Names) from storm.locals import * from sys import argv from os.path import isfile auto_warning = u'# WARNING! this file is automatically generated by revers.py' class Schema(Storm): __storm_table__ = 'information_schema.schemata' __storm_primary__ = 'schema_name' schema_name = Unicode() schema_owner = Unicode() class Table(Storm): __storm_table__ = 'information_schema.tables' __storm_primary__ = 'table_schema', 'table_name' table_schema = Unicode() table_name = Unicode() class Column(Storm): __storm_table__ = 'information_schema.columns' __storm_primary__ = 'table_schema', 'table_name', 'column_name' table_schema = Unicode() table_name = Unicode() column_name = Unicode() ordinal_position = Int() is_nullable = Unicode() # YES|NO data_type = Unicode() udt_name = Unicode() class Constraint(Storm): __storm_table__ = 'information_schema.table_constraints' __storm_primary__ =\ 'constraint_schema', 'constraint_name',\ 'table_schema', 'table_name' constraint_schema = Unicode() constraint_name = Unicode() table_schema = Unicode() table_name = Unicode() constraint_type = Unicode() # PRIMARY KEY|FOREIGN KEY|UNIQUE|CHECK class ColumnConstraint(Storm): __storm_table__ = 'information_schema.constraint_column_usage' __storm_primary__ =\ 'constraint_schema', 'constraint_name',\ 'table_schema', 'table_name', 'column_name' table_schema = Unicode() table_name = Unicode() column_name = Unicode() constraint_schema = Unicode() constraint_name = Unicode() class KeyColumnConstraint(Storm): __storm_table__ = 'information_schema.key_column_usage' __storm_primary__ =\ 'constraint_schema', 'constraint_name',\ 'table_schema', 'table_name', 'column_name' constraint_schema = Unicode() constraint_name = Unicode() table_schema = Unicode() table_name = Unicode() column_name = Unicode() ordinal_position = Int() position_in_unique_constraint = Int() Table.schema = Reference(Table.table_schema, Schema.schema_name) Schema.tables = ReferenceSet(Schema.schema_name, Table.table_schema) Column.table = Reference((Column.table_schema, Column.table_name),\ (Table.table_schema, Table.table_name)) Table.columns = ReferenceSet((Table.table_schema, Table.table_name),\ (Column.table_schema, Column.table_name),\ order_by = Column.ordinal_position) Constraint.table = Reference((Constraint.table_schema, Constraint.table_name),\ (Table.table_schema, Table.table_name)) Table.constraints = ReferenceSet((Table.table_schema, Table.table_name),\ (Constraint.table_schema, Constraint.table_name)) ColumnConstraint.constraint = Reference((ColumnConstraint.constraint_schema,\ ColumnConstraint.constraint_name),\ (Constraint.constraint_schema, Constraint.constraint_name)) Constraint.columns = ReferenceSet((Constraint.constraint_schema, Constraint.constraint_name),\ (ColumnConstraint.constraint_schema, ColumnConstraint.constraint_name)) KeyColumnConstraint.constraint = Reference((KeyColumnConstraint.constraint_schema,\ KeyColumnConstraint.constraint_name),\ (Constraint.constraint_schema, Constraint.constraint_name)) Constraint.key_columns = ReferenceSet((Constraint.constraint_schema, Constraint.constraint_name),\ (KeyColumnConstraint.constraint_schema, KeyColumnConstraint.constraint_name),\ order_by = KeyColumnConstraint.ordinal_position) def class_name(table_name): """ >>> class_name(u'table_name') u'TableName' """ return u''.join(map(lambda s: s.capitalize(), table_name.split('_'))) def column_name(column): #TODO Column can`t have constructor and broke doctest """ >>> column_name(Column(u'schema', u'table', u'column')) u'schema.Table.column' >>> column_name(Column(u'public', u'table', u'column')) u'Table.column' """ if column.table_schema == u'public': return u'%s.%s' % (class_name(column.table_name), column.column_name) else: return u'%s.%s.%s' % (column.table_schema, class_name(column.table_name), column.column_name) # storm field types # "Bool", "Int", "Float", "Decimal", "RawStr", "Unicode", # "DateTime", "Date", "Time", "TimeDelta", "Enum", "Pickle", "List", # # postgres column types # is a subset of # SELECT typname FROM pg_type WHERE typtype = 'b'; #TODO i dont know it`s right or no type_mapper = {\ u'bool' : u'Bool', u'char' : u'Unicode', # ? u'int8' : u'Int', u'int2' : u'Int', u'int4' : u'Int', u'text' : u'Unicode', u'float4' : u'Float', u'float8' : u'Float', u'abstime' : u'Time', u'reltime' : u'Time', u'tinterval' : u'TimeDelta', u'varchar' : u'Unicode', u'date' : u'Date', u'time' : u'Time', u'timestamp' : u'DateTime', u'timestamptz' : u'DateTime', u'timetz' : u'Time', u'numeric' : u'Decimal', # ? } def field_type(udt_name): try: return type_mapper[udt_name] except KeyError: return u'RawStr' def table_name(table, reference, s = False): """ TODO equal referenced table names in different schemas trap here. add reference.table_schema to reference.table_name """ if table.table_schema == u'public': return u'%s.%s' % (class_name(table.table_name), reference.table_name) else: return u'%s.%s.%s' % (table.table_schema, class_name(table.table_name), reference.table_name) def primary_keys(table): for constraint in table.constraints.find(Constraint.constraint_type == u'PRIMARY KEY'): for column in constraint.columns: yield column.column_name def each_schema(schema): "@return (schema name, tables for schema)" yield u'public', store.find(Table, Table.table_schema == u'public') for schema in store.find(Schema): yield schema.schema_name, schema.tables def print_tables(source, schema_tables): print >>source, auto_warning print >>source tables = [class_name(table.table_name) for table in schema_tables] if len(tables) == 0: return print >>source, u'from storm.locals import *' print >>source print >>source, u'__all__ = ["%s"]' % u'", "'.join(tables) for table in schema_tables: print >>source print >>source, u'class %s(Storm):' % class_name(table.table_name) print >>source, u' __storm_table__ = "%s.%s"' % (table.table_schema, table.table_name) #TODO empty primary key generate invalid code, but emty primary disallowed? by storm print >>source, u' __storm_primary__ = %s'\ % u', '.join([u'"%s"' % p for p in primary_keys(table)]) for column in table.columns: print >>source, u' %s = %s()' % (column.column_name, field_type(column.udt_name)) def print_references(source, tableset): for table in tableset: for constraint in table.constraints.find(Constraint.constraint_type == u'FOREIGN KEY'): #TODO single column relation (most polular?) do not need brackets and commas key = u'(%s)' % u', '.join([column_name(column) for column in constraint.key_columns]) ref = u'(%s)' % u', '.join([column_name(column) for column in constraint.columns]) r = table_name(constraint.key_columns.one(), constraint.columns.one()) rs = table_name(constraint.columns.one(), constraint.key_columns.one()) print >>source #TODO many 2 many relation cant detected print >>source, u'%s = Reference(%s, %s)' %(r, key, ref) print >>source, u'%s = ReferenceSet(%s, %s)' %(rs, ref, key) if __name__ == '__main__': if len(argv) != 2: print \ """usage: python revers.py <dburi> where <dburi> like postgres://user:[EMAIL PROTECTED]/database then create ./models/*.py files, ./models/ must be exist warning!!! exists .py files in ./models/ are replaced """ exit(1) dburi = argv[1] db = create_database(dburi) store = Store(db) #TODO exists file replaced!!! db_source = open('models/__db__.py', 'w') print >>db_source, auto_warning print >>db_source print >>db_source, u'from storm.references import Reference, ReferenceSet' print >>db_source print >>db_source, u'from public import *' for schema in store.find(Schema): print >>db_source, u'import %s' % schema.schema_name for schema_name, schema_tables in each_schema(store): schema_source = open('models/%s.py' % schema_name, 'w') print_tables(schema_source, schema_tables) schema_source.close() print_references(db_source, schema_tables) db_source.close() if not isfile('models/__init__.py'): init_source = open('models/__init__.py', 'w') print >>init_source,\ u"""%s (only if not exist) from __db__ import * from storm.database import create_database from storm.store import Store db = create_database("%s") store = Store(db) """ % (auto_warning, unicode(dburi)) init_source.close()
-- storm mailing list [email protected] Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/storm
