далее ниже приведенным скриптом генерим .dot файл для graphviz
pg_dump -s base >dump.sql
#!/usr/bin/python
from pyparsing import Literal, CaselessLiteral, Word, delimitedList ,Optional, Combine, Group, alphas, nums, alphanums, Forward ,oneOf, sglQuotedString, OneOrMore, ZeroOrMore, CharsNotIn ,Suppress
parenthesis = Forward()
parenthesis <<= "(" + ZeroOrMore(CharsNotIn("()") | parenthesis) + ")"
field_def = OneOrMore(Word(alphanums+"_\"':-") | parenthesis)
def field_act(s,loc,tok):
return ("<"+tok[0]+"> " + " ".join(tok)).replace("\"","\\\"")
field_def.setParseAction(field_act)
field_list_def = field_def + ZeroOrMore(Suppress(",") + field_def)
def field_list_act(s,loc,tok):
return " | ".join(tok)
field_list_def.setParseAction(field_list_act)
create_table_def = Literal("CREATE")+"TABLE"+Word(alphas).setResultsName("tableName")+"("+field_list_def.setResultsName("fields")+")"+";"
def create_table_act(s,loc,tok):
return """"%(tableName)s" [\n\t label="<%(tableName)s> %(tableName)s | %(fields)s"\n\t shape="record"\n];""" % tok
create_table_def.setParseAction(create_table_act)
add_fkey_def=Literal("ALTER")+"TABLE"+"ONLY" + Word(alphanums+"_").setResultsName("tableName") + "ADD" + "CONSTRAINT" + Word(alphanums+"_") + "FOREIGN"+"KEY"+"("+Word(alphanums+"_").setResultsName("keyName")+")" +"REFERENCES"+Word(alphanums+"_").setResultsName("fkTable")+"("+Word(alphanums+"_").setResultsName("fkCol")+")"+";"
def add_fkey_act(s,loc,tok):
return """ "%(tableName)s":%(keyName)s -> "%(fkTable)s":%(fkCol)s """ % tok
add_fkey_def.setParseAction(add_fkey_act)
other_statement_def = ( OneOrMore(CharsNotIn(";") ) + ";")
def other_statement_act(s,loc,tok):
return ""
other_statement_def.setParseAction(other_statement_act)
comment_def = "--" + ZeroOrMore(CharsNotIn("\n"))
comment_def.setParseAction(other_statement_act)
statement_def = comment_def | create_table_def | add_fkey_def | other_statement_def
defs = OneOrMore(statement_def)
print """digraph g { graph [ rankdir = "LR" ]; """
for i in defs.parseFile("dump.sql"):
if i!="":
print i
print "}"
на выходе имеем файлик
python sql_dia.py|dot -Tpng > sql_dia.png
2 коментарі:
Dear EnergyBlog:
From time to time I use Google to look for any intrepid pyparsing users out there, and I found your program on energyblog. You have done a very nice job with this. I have some *minor* comments on your grammar, use them or not as you wish (free advice is worth what you pay for it):
1. I had to stare at this a bit to see what you were doing:
reduce(lambda a,b:a+" "+b,tok)
I think a more common form of this is:
" ".join(tok)
For that matter, you could change:
field_def = OneOrMore(Word(alphanums+"_\"':-") | skobki)
to use the Combine class:
field_def = Combine( OneOrMore(Word(alphanums+"_\"':-"), joinString=" ", adjacent=False ) \
| skobki)
and do away with the parse action completely.
2. I very much like these constructions:
create_table_def = Literal("CREATE") + "TABLE" + ...
add_fkey_def=Literal("ALTER")+"TABLE"+"ONLY" + ...
and so on. Many people make the mistake of defining such expressions as:
create_table_def = Literal("CREATE TABLE") + ...
add_fkey_def=Literal("ALTER TABLE ONLY") + ...
but this defeats the flexible whitespace handling between keywords. Well done!
3. Your definition of field_list_def as:
field_list_def = field_def + ZeroOrMore("," + field_def)
forces you to add a parse action to skip over the delimiting commas. Try switching to delimitedList:
field_list_def = delimitedList( field_def )
delimitedList generates almost the exact same expression, but also suppresses the delimiters, so you don't need to define the parse action. Or you can just modify your existing definition to suppress the commas, using
field_list_def = field_def + ZeroOrMore(Suppress(",") + field_def)
This is almost exactly what delimitedList generates.
4. You might want to look into defining some results names. Results names are there to help you avoid creating expressions like:
return """ "%s":%s -> "%s":%s """ %(tok[3],tok[10],tok[13],tok[15])
Explicit reference to token elements by index number can make your grammar fragile, especially if you update it later to insert new fields, thereby forcing you to update all of the index references after the inserted field. It's even worse if you insert an optional field, because then you have to first check to see if the optional field was used, and then recalculate indexes dynamically.
If you instead define you grammar expressions like this:
add_fkey_def=Literal("ALTER")+"TABLE"+"ONLY" + Word(alphanums+"_").setResultsName("tableName") + "ADD" \
+ "CONSTRAINT" + Word(alphanums+"_") + \
"FOREIGN"+"KEY"+"("+Word(alphanums+"_").setResultsName("foreignKeyName")+")" \
+"REFERENCES"+Word(alphanums+"_").setResultsName("fkTableName")+\
"("+Word(alphanums+"_").setResultsName("fkColName")+")"+";"
You can then reference the tokens by field name:
return """ "%s":%s -> "%s":%s """ %(tok.tableName,tok.foreignKeyName,tok.fkTableName,tok.fkColName)
or even:
return """ "%(tableName)s":%(foreignKeyName)s -> "%(fkTableName)s":%(fkColName)s """ % tok
Overall, though, a nice SQL graphing utility. Would you mind if I included it with the pyparsing examples? Either yes or no, I'm glad pyparsing is working for you.
Dasvedanya,
-- Paul
i wouldn`t mind if you iclude this into pyparsing.
i accept your suggestions
Дописати коментар