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