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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
|
<html>
<body>
<h1>tedia2sql Brief Help</h1>
<hr>
<h2>tedia2sqlrc file</h2>
Some of these options can be specified in a $HOME/.tedia2sqlrc file (or,
optionally, "tedia2sqlrc" stored in the same path as the tedia2sql
executable).
<p>
Commandline arguments always override preferences specified in the rcfile.
<h2>-h: The Barebones Help</h2>
When you run tedia2sql with the <i>-h</i> commandline switch, you will get a short
help that looks like the following.
<p>
<blockquote>
tedia2sql -- vX.YY.ZZ Copyright (c)2002 by Tim Ellis <tim[dot]ellis[at]gamet[dot]com>
<p>
This program is free software; you can redistribute it and/or modify it
under the terms of the GNU General Public License as published by the Free
Software Foundation; version 2. This program is copyrighted!
<p>
This program is distributed in the hope that it will be useful, but WITHOUT
ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
more details.
<p>
Usage: tedia2sql [-t <i>dbType</i>] [-v {1 | 2}]<br>
[-s] [-d] [-u] [-h]<br>
[-i <i>xmlIn</i>] -o <i>sqlOut</i>
[<i>files...</i>]
<p>
<table cellspacing=0 cellpadding=0>
<tr><td width=5%></td><td width=20% valign=top>-t <i>dbType</i></td><td valign=top>Type of database to gen SQL for (informix, ingres, postgres, mysql, mssql, sybase, oracle, db2)</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-c </td><td valign=top>Print less comments in output (useful for version control comparisons);</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-s </td><td valign=top>Separate constraint drops, constraints, table drops, tables into separate SQL output files</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-d </td><td valign=top>Add 'drop' syntax to SQL output file</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-u </td><td valign=top>Use UML interpretation of the diagram rather than the default ERD interpretation</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-C </td><td valign=top>Use case of names in name comparisons, even if the database ignores case</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-p <i>names</i>:<i>types</i> </td><td valign=top>Automatically generate primary keys whan needed with name and type given</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-f </td><td valign=top>Automatically generate foreign keys in tables when needed</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-b </td><td valign=top>Backwards-compatibility - generate SQL more like tedia2sql v1.2.9b</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-k </td><td valign=top>Write/keep output files even if there is an error</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-m </td><td valign=top>Produce warnings, not fatal errors where foreign key and primary keys don't have same type</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-M </td><td valign=top>Don't test types of foreign keys against their primary key types</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-v {1 | 2} </td><td valign=top>Verbosity level, 1=verbose, 2=very verbose</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-h </td><td valign=top>This help, copyright, and licensing information</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-i <i>xmlIn</i> </td><td valign=top>XML File to parse, should be created by Dia</td></tr>
<tr><td width=5%></td><td width=20% valign=top>-o <i>sqlOut</i></td><td valign=top>Name of output -- don't give extension, .sql will be appended</td></tr>
<tr><td width=5%></td><td width=20% valign=top><i>files...</i></td><td valign=top>More input files - at least one input file must be specified</td></tr>
</table>
</blockquote>
<h2>vX.YY.ZZ -- Version</h2>
The version of tedia2sql you are running. The X means the major
revision. If tedia2sql is completely rewritten or uses a new
set of libraries to achieve its goals, the X will be
incremented by one. If YY is ODD, then you are using a
development (unstable or untested) version of the script. If it
is EVEN, you are using a stable, tested version.
<h2>Copyright and License</h2>
This script is not public domain. It is a copyrighted script
licensed under the GNU GPL version 2. Note specifically this
slightly varies from the standard method of licensing GPL
scripts in that you cannot use a later version of the GPL at
your discretion. This is licensed as version 2 of the GPL, the
text of which is included in the file LICENSE which is
distributed with tedia2sql.
<h2>-t: Database SQL DDL Type</h2>
tedia2sql supports multiple databases. To select which database
to output for, pass one of the following on the commandline:
<ul>
<li> <i>-t db2</i> -- IBM's DB/2 v7 or greater
<li> <i>-t mssql</i> -- Microsoft's SQL Server
<li> <i>-t mysql</i> -- MySQL
<li> <i>-t oracle</i> -- Oracle v8i or greater
<li> <i>-t postgres</i> -- PostgreSQL (Postgres) 7.2 or greater
<li> <i>-t sybase</i> -- Sybase ASE v11.0.3.3 or greater
<li> <i>-t informix</i> -- IBM's Informix
<li> <i>-t ingres</i> -- Ingres
<li> <i>-t sas</i> -- SAS
</ul>
<h2>-s: Separate the Add, Drop, Constraint, and Table Scripts</h2>
When you specify the <i>-s</i> option, there will be multiple
files created (four if you pass the <i>-d</i> option, two if
you don't) to specify adds, drops, constraints, and table names
in separate files. Each file will be prefixed with the word you
pass to the <i>-o</i> option, and will have an extension of the
extension you pass to the <i>-o</i> option.
<p>
Each SQL script generated will have a "1st" "2nd"... etc in the
filename so that when sorted ASCIIbetically, a parent script will know
in which order to run the SQL scripts. Also, if you look at the
filenames, you will know in which order to run the scripts.
<p>
If you <strong>do not</strong> pass the <i>-s</i> option, then
you will only ever get one file with all statements in the order
tedia2sql thinks is best:
<ol>
<li> Drop constraints
<li> Drop tables
<li> Create tables
<li> Insert data
<li> Create constraints and indexes
</ol>
Because the script doesn't have any intelligence about the order
in which it creates the INSERT statements (ie: fulfill foreign-key
constraints), it must create the constraints <strong>after</strong>
it inserts the data. If you don't like this idea (ie: you're using
constraints to make sure your data is correct), then you can pass
the <i>-s</i> option and run the scripts in the order you choose.
<h2>-d: Create drop statements</h2>
If you want to drop each constraint and table before you create
them, pass this option to the script. Note that if you also
pass <i>-s</i> that the drop statements will appear in their
own SQL DDL file.
<h2>-u: Interpret as UML</h2>
Interpret the diagram as a UML diagram, rather than as
an ERD diagram using UML symbology. In the default (ERD)
mode, many-to-one associations are written using the UML
aggregation notation, with the aggregation diamond mark
on the 'many' end of the association. With the <i>-u</i>
option, the UML convention is followed where the diamond
aggregation or composition marker is on the <i>'one'</i>
end of the association.
<h2>-p <i>names</i>:<i>types</i> : Automatically generate primary keys</h2>
Where a reference is made to a primary key, and the
key does not exist, generate a key with the given
<i>names</i> and <i>types</i>. The default primary
key can be multi-part, in which case <i>names</i> and
<i>types</i> are comma-separated lists of the primary
key names and types; the lists must be the same length.
E.g: <code>-p 'key1, key2: integer, integer'</code>.
White space is ignored.
<h2>-f: Automatically generate foreign keys</h2>
Where a reference is made to a foreign key, and the key does
not exist, generate a key from the corresponding primary key's
names and types. If there is no role determining the name of the
foreign key, the key name is generated using the normal rules.
<h2>-b: Backwards compatibility</h2>
<code>tedia2sql</code> v1.2.10 makes some changes in the
rules for generating private key constraint names and some
minor formatting chamges. The <i>-b</i> option makes v1.2.10
generate SQL that is more like that from v1.2.9. For <i>-t
oracle</i>, it is necessary to do <code>diff -b</code> to
ignore whitespace changes when comparing SQL generated by
v1.2.10 with SQL generated by v1.2.9, because v1.2.10 for
Oracle allows the use of <code>on delete</code> clauses,
and that generates an extra space character in each foreign
key constraint definition.
<p>
<em>This option is only intended for use in simplifing
the verification of SQL generated by v1.2.10 against SQL
generated by v1.2.9.</em>
<h2>-m: Only warn on type mismatches</h2>
Normally, tedia2sql generates a fatal error if there are any type
mismatches between foreign keys and their corresponding primary keys.
This is done on the basis of the text form of the type, and ignores
possible type equivalences in the database. The <i>-m</i> option
produces the same warning messages, but permits generation of SQL even
if there are type mismatches. Should be used with some caution.
<h2>-M: Ignore type mismatches</h2>
As for <i>-M</i> but disables foreign key/primary key typechecking
altogether. Use with caution.
<h2>-v: Verbosity Level</h2>
If you would like to see what the script is doing while it runs
via printing to STDOUT, then pass <i>-v 1</i>. Sometimes, if
the script is malfunctioning, then a developer might ask you to
run the script with <i>-v 2</i> which will put even more
debugging output to STDOUT. You will probably not be interested
in either of these options unless you plan on changing the
tedia2sql script.
<h2>-i: Input File</h2>
The input file must be a Dia UML diagram created by Dia
version 0.90 or greater. tedia2sql detects if the file is
compressed with gzip or not.
<h2>-o: Output File Format</h2>
tedia2sql will parse the filename you pass it for a BASE (what
appears before the final dot) and extension (what appears after
the final dot) and will generate output files accordingly. You
should just pass to <i>-o</i> what you feel is intuitive, and it
should do the right thing. If you pass the <i>-s</i> commandline
option, you will get more than one SQL DDL file. See the
section on <i>-s</i>.
<h2>files...</h2>
tedia2sql allows its input file names to be specified
either with the <i>-i</i> option, or by giving a list of
filenames following the options. Multiple <i>-i</i>
options may be given, but only the <em>last</em>
of them is used (and no warning is given -- this is
a feature(?) of the perl <code>Getopt::Std</code>
package). If you want to use multiple files, list them
after the options.
<p>
At least one input file must be named.
<p>
If both <i>-i</i> and the file
list are used to name input files, the <em>last</em>
file indicated by <i>-i</i> is processed, followed by the
files in the file name list. Processing order in the files
may change the order that SQL statements are produced,
but it should not affect the function of the SQL.
</body>
</html>
|
