TBLFIX

TBLFIX reads one or more files with table and type definitions, and splits the statements into the structure for table files that AbaPerls imposes.

Contents:
    Command-Line Syntax
    Created Files
    Reformatting and Renaming
    Table-Level CHECK Constraints
    The -types File
    Limitations and Known Issues

Command-Line Syntax

tblfix [-shortnames file] [-types file] file1 [file2 ...]

`-shortnames`	File that defines shortnames for the table. File format: one line per table, with the table name followed by the shortname, separated by white space. You can use -- (two hyphens) to introduce a comment.
`-types`	File that maps type names to physical types. See below for the format.

Created Files

TBLFIX reads one or more files with type and table definitions, generated by Enterprise Manager 6.5 or PowerDesigner, and splits the input files in this way:

Each type is place in a file of its own under ..\TYPE, any rules and defaults that are bound to the type are also placed in the file, as well as the calls to sp_bindrule and sp_bindefault.
For each table, TBLFIX creates one, two or three files:

tbl.TBL CREATE TABLE including PRIMARY KEY, UNIQUE, CHECK and DEFAULT constraints.

tbl.FKEY ALTER TABLE commands for FOREIGN KEY constraints.

tbl.IX CREATE INDEX statements.

Temp tables with names starting with # are skipped.

TBLFIX also reformats the SQL code and changes names as we will detail in just a few moments.

TBLFIX does not handle triggers, stored procedures, views or user-defined functions, but is smart enough to ignore them. Thus you can feed TBLFIX with a script with everything in it. You can use SPFIX to extract the rest.

Reformatting and Renaming

These are the changes that TBLFIX makes in the extracted SQL code:

Each created file gets a file header with the SourceSafe keywords $Header: $ and $History: $.
If a type name starts with "T_", TBLFIX removes this. (Fix for a PowerDesigner convention.)
Reserved words are cast in uppercase.
All comments in the original files are removed.
TBLFIX removes the prefix "dbo.".
Column names, types and NULL indicator are aligned in nice pretty columns.
If TBLFIX finds a CHECK constraint which checks whether a value is in set of permitted values with a chain of OR conditions, TBLFIX rewrites this to use the far more readable IN.
If TBLFIX finds nameless CHECK or DEFAULT constraints, they are named, according to the table below.
If there is a shortname for the table, specified in the file given with -shortnames, the shortname replaces the table in constraint names. E.g. pk_instruments becomes pk_ins, if the shortname for instruments is ins.
Lines starting with ### are handled specially, see below.
If you have specified -shortnames, constraint are named as in this table: (aa refers to the part before the first underscore in the constraint name in the file. aa is left unchanged, xxx is the shortname of the table.)

Constraint	Name	Comment
PRIMARY KEY	aa_xxx	aa is usually "pk".
UNIQUE	aan_xxx	n is a running number, but is only included if the table has more than one one UNIQUE constraint. aa is usually either "u" or "ak".
CHECK	aa_xxx_colname	TBLFIX does not change the name of CHECK constraints on table level. aa is usually "check", "ck" or "ckc".
DEFAULT	aa_xxx_colname	aa is usually "df" or "default".
FOREIGN KEY	fkn_xxx_yyy	n is a running number. yyy is the short name for the table that the foreign-key constraint refers to.

Table-Level CHECK Constraints

A shortcoming with PowerDesigner (including the most recent version, 9.5 as of this writing), is that you cannot define more than one table-level CHECK constraint per table. You can still include all you checks you need by AND:ing the conditions into one big constraint. But when huge constraint fires, you don't know which condition that was violated. Therefore TBLFIX has the convention that if a line in the input file starts with ###, it assumes that this is a table-level CHECK constraint. We best explain this through an example. In the Server-rule box in Powerdesigner you have entered:

### ckt_evt_hasnewins (NOT (hasnewins = 1 AND isissue = 0)),

This becomes:

CONSTRAINT ckt_evt_hasnewins CHECK
    (NOT (hasnewins = 1 AND isissue = 0)),

Thus, aftter ### follows the name of the constraint and then the conditions for the CHECK constraint with parentheses and all. If the conditions are followed by more conditions, you must have a comma at the end. You can split the condition on several lines, but do not indent in PowerDesigner, because TBLFIX will indent.

The `-types` File

The file you specify with -types, should consists of lines with this appearance:

typename;typedef;default;rule

Or with an example:

aba_poseffect;char(1);;@x IN ('H', 'W', ' ')

This line causes TBLFIX to create the following code:

EXEC sp_addtype aba_poseffect, 'char(1)'
go
CREATE RULE aba_poseffect_rule AS @x IN ('H', 'W', ' ')
go
EXEC sp_bindrule 'aba_poseffect_rule', 'aba_poseffect'
go

Thus, TBLFIX creates a rule, names the rule from the type by adding _rule, and binds it to the type. In this example there was no DEFAULT; but as the reader may guess, it works analoguously.

Limitations and Known Issues

To repeat: only scripts from Enterprise Manager 6.5 (or maybe even only 6.0) are handled well. Scripts from EM in SQL7 and SQL2000 are not handled well.
I have not tried TBLFIX on every single version of PowerDesigner. It works with 6.1 and 9.5. I seem to have had problems with one of the versions in between, but I might have been mistaken.
A rule or a default is assumed to belong one single type. (This is to be seen as a standard imposed by AbaPerls.)
Binding of rules and default on column level are ignored. (This again is a standard imposed by AbaPerls.)
Constraints with negative numbers, e.g. IN (-1, 1) in script from Enterprise Manager 6.5 results in syntactically incorrect SQL.

tbl.TBL	CREATE TABLE including PRIMARY KEY, UNIQUE, CHECK and DEFAULT constraints.
tbl.FKEY	ALTER TABLE commands for FOREIGN KEY constraints.
tbl.IX	CREATE INDEX statements.