NAME

Etora - Easy Tables for Oracle


SYNOPSIS

etora tables.ddl > tables.sql


DESCRIPTION

Etora reads a file of database table definitions and outputs the Oracle SQL to create or drop the tables and associated objects. All constraints, sequences and triggers are intelligently named, which aids in interpetation of Oracle error messages.


INPUT FILE FORMAT

Each line of the input file is one of the following:


Directives:

Directives always start with '.'. Directives only affect the lines following the directive - therefore directives which define variables and types should usually be at the top of the file. The following directives are recognized:

.set <VARIABLE> <VALUE>

The variable named <VARIABLE> is assigned value <VALUE>.

.isql <SQL_STRING>

<SQL_STRING> will be inserted inside the current table definition. It's hard to think of a valid use for this directive.

.sql <SQL_STATEMENT>

<SQL_STATEMENT> will be inserted verbatim into the output after all the table definitions but before the constraint definitions. A good place to insert rows which have to be present from the beginning, or to add an unusual constraint.

Any number of .sql directives can appear in the input file - they will all generate output in the same place.

.suffix <SUFFIX> <TYPE>

Columns with names ending in <SUFFIX> will be assigned <TYPE>. <TYPE> can be a literal or symbolic type (see TYPES). It's a good idea to start suffixes with '_'.

.type <SYMBOLIC_TYPE> <LITERAL_TYPE>

Any column marked with <SYMBOLIC_TYPE> in the input file will be assigned <LITERAL_TYPE> in the output file. Symbolic types should start with a capital letter to avoid name conflict. Literal types are actual SQL types like NUMBER(10).


Table Definition Start

A table definition start tells etora that subsequent lines of input pertain to the current table until another table definition start is encountered.


Column Definition

<COLUMN_NAME> [n] [<LITERAL_TYPE>|<SYMBOLIC_TYPE>] [pk] [fk <OTHER_TABLE>] [[u][i]++]

Defines a column in the current table. The following items may occur in a column definition:

<COLUMN_NAME>

The name of the column in the output file. If it ends in any of the defined suffixes, the column type will be set to the type associated with the suffix.

n (nullable)

If 'n' is present, the column is nullable and a 'NOT NULL' constraint will not be applied to it. By default, columns are not nullable.

<LITERAL_TYPE>

A literal SQL type like NUMBER(10). Should be used sparingly - it's more maintainable to define symbolic types.

<SYMBOLIC_TYPE>

The name of a type previously defined with a .type directive, or the name of a built-in type.

pk

Marks this column as part of the primary key for the table. Any number of columns may be part of the primary key.

fk <OTHER_TABLE>

Marks this column as a foreign key to <OTHER_TABLE>.

u++, i++ or ui++

When applied to a column of type DATE, marks the column for automatic insertion of the current date. When applied to a column of type NUMBER, marks the column for automatic insertion of an incrementing serial number. This is automatically accomplished by creation of an appropriately named sequence and trigger.

u means update the column whenever the row is updated.

i means insert the value whenever a new row is inserted.

If both are present, the value will be updated upon both insert and update.

VARIABLES

The following variables, which can be modified with the .set directive, affect the behavior of etora:

default_type

The type applied to a column when no other criteria are met for assigning a type.

prefix

A string prepended to each table name in the output. Useful for clearly separating a number of tables from the existing tables in a schema. Note that foreign key (fk) option in column creation does not automatically get the prefix - it must be specified manually. This is so that foreign keys can point to tables that don't have the prefix.

cache

Size of cache for sequences. By default, 20.

max_ident_len

Maximum length for any identifiers (object names) in the output file. Identifiers are progressively shortened by removal of vowels, or truncated if this fails.


HISTORY

Written by Asher Blum in January 2002.