1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
#!/usr/bin/python
# sql2dot.py
#
# Creates table graphics by parsing SQL table DML commands and
# generating DOT language output.
#
# Adapted from a post at https://energyblog.blogspot.com/2006/04/blog-post_20.html.
#
sampleSQL = """
create table students
(
student_id integer primary key,
firstname varchar(20),
lastname varchar(40),
address1 varchar(80),
address2 varchar(80),
city varchar(30),
state varchar(2),
zipcode varchar(10),
dob date
);
create table classes
(
class_id integer primary key,
id varchar(8),
maxsize integer,
instructor varchar(40)
);
create table student_registrations
(
reg_id integer primary key,
student_id integer,
class_id integer
);
alter table only student_registrations
add constraint students_link
foreign key
(student_id) references students(student_id);
alter table only student_registrations
add constraint classes_link
foreign key
(class_id) references classes(class_id);
""".upper()
from pyparsing import (
Literal,
Word,
delimitedList,
alphas,
alphanums,
OneOrMore,
ZeroOrMore,
CharsNotIn,
replaceWith,
)
skobki = "(" + ZeroOrMore(CharsNotIn(")")) + ")"
field_def = OneOrMore(Word(alphas, alphanums + "_\"':-") | skobki)
def field_act(s, loc, tok):
return ("<" + tok[0] + "> " + " ".join(tok)).replace('"', '\\"')
field_def.setParseAction(field_act)
field_list_def = delimitedList(field_def)
def field_list_act(toks):
return " | ".join(toks)
field_list_def.setParseAction(field_list_act)
create_table_def = (
Literal("CREATE")
+ "TABLE"
+ Word(alphas, alphanums + "_").setResultsName("tablename")
+ "("
+ field_list_def.setResultsName("columns")
+ ")"
+ ";"
)
def create_table_act(toks):
return (
""""%(tablename)s" [\n\t label="<%(tablename)s> %(tablename)s | %(columns)s"\n\t shape="record"\n];"""
% toks
)
create_table_def.setParseAction(create_table_act)
add_fkey_def = (
Literal("ALTER")
+ "TABLE"
+ "ONLY"
+ Word(alphanums + "_").setResultsName("fromtable")
+ "ADD"
+ "CONSTRAINT"
+ Word(alphanums + "_")
+ "FOREIGN"
+ "KEY"
+ "("
+ Word(alphanums + "_").setResultsName("fromcolumn")
+ ")"
+ "REFERENCES"
+ Word(alphanums + "_").setResultsName("totable")
+ "("
+ Word(alphanums + "_").setResultsName("tocolumn")
+ ")"
+ ";"
)
def add_fkey_act(toks):
return """ "%(fromtable)s":%(fromcolumn)s -> "%(totable)s":%(tocolumn)s """ % toks
add_fkey_def.setParseAction(add_fkey_act)
other_statement_def = OneOrMore(CharsNotIn(";")) + ";"
other_statement_def.setParseAction(replaceWith(""))
comment_def = "--" + ZeroOrMore(CharsNotIn("\n"))
comment_def.setParseAction(replaceWith(""))
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.parseString(sampleSQL):
if i != "":
print(i)
print("}")
|
