sqldef.pl - Convert RDBMS schema to mSQL DDL

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.

SEE ALSO Msql::RDBMS

 sqldef.pl - Convert RDBMS schema to mSQL DDL