NAME
sqldef.pl - Convert RDBMS schema to mSQL DDL
SYNOPSIS
sqldef.pl [ --catalog-only ] [ --no-sequences ] [ --help ]
DESCRIPTION
sqldef.pl will read a text-based schema for a relational database on standard input and generate the corresponding Data Definition Language (DDL) on standard output to create an mSQL 2.x database.
Most commonly you would pipe the output of sqldef.pl to the msql monitor program (this will wipe out all of the data in the specified database):
sqldef.pl db.schema | msql db SCHEMA FILE FORMAT
Table and column definitions consist of lists of colon seperated fields.
Where an optional parameters is omitted the corresponding field should be left blank.
All lines beginning with # are comments.
TABLE DEFINITION
- table_name
-
name of the table
- table_description
-
table description (used in various HTML pages)
- view_file (optional)
-
HTML file to use when viewing the file as a result of a query
COLUMN DEFINITION
- column_name
-
name of the column
- type
-
data type of the column. This can be set to any one of the standard mSQL types int|char|text|date or the `pseudo types' datetime|created|modified|boolean.
- width (optional)
-
the width of a char or text column
- constraints (optional)
-
additional mSQL constraints on the column e.g. not null.
- description
-
short description of the table (used in various HTML pages)
- key_types
-
list of key types seperated by +. Valid key types are
- PRIMARY
-
for a primary key
- FOREIGN
-
for a foreign key
- LABEL
-
for a column to be used in a selection list to identify a row from a related table
- fkey_table (optional)
-
if one of the key types is FOREIGN, the name of the related table
- fkey_column (optional)
-
if one of the key types is FOREIGN, the name of the related column in fkey_table
- link_type
-
type of link: MAILTO, URL or IMG
- prompt
-
non-zero to prompt for this column in queries
- display
-
non-zero to display this column in query results
- link
-
name of column to use as label of link
EXAMPLE
# company.schema
# company table
table:company:companies::
cmpny_id:int::not null:Unique Id:PRIMARY::::0:0:
cmpny_name:char:40::Company name:LABEL::::1:1:
# product table
table:product:goods/services::
prod_id:int::not null:Unique Id:PRIMARY::::0:0:
prod_name:char:40::Product/services:LABEL::::1:1:
# supply table
# M:N (company:product)
table:supply:supply of goods/services::
supply_cmpny:int:::Company:PRIMARY+FOREIGN+LABEL:company:cmpny_id::1:1:
supply_prod:int:::Product:PRIMARY+FOREIGN+LABEL:product:prod_id::1:1:OPTIONS
--catalog-only only create DDL for system catalog
--no-sequences don't create DDL for table sequences
Useful if you plan to dump your data with a program like msqldump,
change the schema and then reload the data, as it will preserve your
table sequences.--help print a usage messages, then exit
AUTHOR
Brian Jepson <bjepson@conan.ids.net>
Paul Sharpe <paul@miraclefish.com>
You may distribute this under the same terms as Perl itself.