On 06/01/2016 02:40 PM, Chase wrote:
We have found it useful to be able to print out the schema in SQL to
look at it, and to diff versions of it alongside versions of the orm.
Printing it out is relatively straightforward, diffing slightly less;
currently we're using something very close to the following:
def dump_schema(TheModelBase):
out = StringIO()
def dump(sql, *multiparams, **params):
out.write(('%s' % sql.compile(dialect=engine.dialect)).strip()+';\n\n')
engine = create_engine('postgres://', strategy='mock', executor=dump)
# patch tables with consistent collections so things get output in a
# consistent order
# Note: This doesn't fully work. The topo sort yields sets of nodes from the
# graph, these sets are not guaranteed to be consistent.
tables = TheModelBase.metadata.tables
for table in tables.values():
table.indexes = SortedSet(table.indexes)
TheModelBase.metadata.tables = OrderedDict(
(k, tables[k]) for k in sorted(tables.keys())
)
TheModelBase.metadata.create_all(engine)
TheModelBase.metadata.tables = tables
# tweak the spacing
text = out.getvalue().strip()
text = text.replace('\t', ' ')
text = re.sub(r' *$', '', text, flags=re.MULTILINE)
# Make this and "PRIMARY KEY (" line up
text = text.replace('FOREIGN KEY(', 'FOREIGN KEY (')
# alphabetically sort certain statements within their groupings
def sort_statement_groups(text, prefix):
return '\n\n'.join(it.chain(*[
sorted(statements) if prefixed else list(statements)
for prefixed, statements in it.groupby(text.split('\n\n'),
key=lambda x: x.startswith(prefix))
]))
text = sort_statement_groups(text, 'CREATE TYPE')
text = sort_statement_groups(text, 'CREATE INDEX')
HEADER = '-- Autogenerated SQL, manual edits will be discarded\n\n\n'
return HEADER + text + '\n'
I think this has been pretty useful, I'm wondering if it would be worth
having upstream, or if the consensus is that just the first four lines
of this function are "good enough" (it wasn't good enough for me).
There's always been an interest in the part of this that adds newlines
and whitespace in the form of "pretty printing", but for this feature to
be part of SQLAlchemy it needs to be implemented as a SQLCompiler class
that acts as a filter on top of an existing SQLCompiler. There have
been some attempts by contributors to implement parts of this but nobody
has seen this through to a real feature.
As far as the deterministic ordering, the mechanisms internally are
there to do this but they are not called upon within the DDL emission
scheme; that is, if you call upon metadata.sorted_tables, you get the
tables sorted not just by constraint dependency but deterministically
within those groups. But the DDL rendering scheme doesn't make use of
the secondary sorting right now, it sends the list of tables into the
constraint-dependency sort without first sorting them alphabetically.
It would need to be made to do so, and similar logic applied to the
rendering of indexes.
Until then, what you have here is a useful recipe for sure.
~Cheers
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
