TBLFIX

TBLFIX reads a file table and type definitions generated by Sybase PowerDesigner, and splits the statements into the structure for table files that AbaPerls imposes.

Contents:
   Command-Line Syntax
   Files Created
   The Shortnames File
   Reformatting and Renaming
   Table-Level CHECK Constraints
   The ‑types File
   PowerDesigner Configuration

Command-Line Syntax

tblfix [-shortnames file] [-types file] file1 [-header file]

`‑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.
`‑`header	Header file to include in all files generated. If you do not provide this option, but there is a file tblfix.header in the local directory, TBLFIX will use this file as a header. If neither the option is provided, nor the file is present, TBLFIX will generate files without headers.

Files Created

TBLFIX reads a file with type and table definitions generated by PowerDesigner. More precisely, TBLFIX has been tested with versions 9.5.1 and 16.1 of PowerDesigner. TBLFIX splits the file 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.

TBLFIX also reformats the SQL code and changes names as detailed below.

If there are triggers, views, stored procedures etc in the script generated by PowerDesigner, TBLFIX ignores them. If you try to feed TBLFIX a script from a different source, SQL Server Management Studio, this is not likely to work out well.

The Shortnames File

In PowerDesigner all objects have both a name and a code. For most type of objects, you are best of letting Name and Code be the same. However, since table names are used to form names of constraints, and also as prefix in names for stored procedures, it makes sense to have separate short-codes for the tables. For instance, if you have a table instruments, you could use ins as the short-code. In this case, you would enter instruments for Name and ins for Code in PowerDesigner. In the ‑shortnames files, you would have a list of these names and codes, with the table name in the first column and the code in the second colunm.

You can produce such a list from PowerDesigner from a report that includes a list of tables. You need to copy the generated table to a text file; TBLFIX is not able to read the .rtf file that PowerDesigner produces.

Beware that if you follow this pattern, PowerDesigner will use the Code in the Preview, and also in the script it produces. Don't worry, TBLFIX takes care of the script, see below.

Reformatting and Renaming

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

If a file header is provided with the option ‑header or there is a file tblfix.header in the current directory, TBLFIX adds this header to the file.
If a type name starts with "T_", TBLFIX removes this. Same goes for initial R_ for rules and D_ for defaults. (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 TBLFIX finds an ON clause an index definition for a filegroup and the name of the filegroup starts with WHERE, TBLFIX assumes that this is actually a filter for the index. See further the page for PDREP for details.
If the name of a table matches the shortname from the ‑shortnames file, TBLFIX replaces the code with the matching name from the file.
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.
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 the first part of the name of the constraint in the file, forced to lowercase. Usually it's "pk", unless you have named the key differently.
UNIQUE	aan_xxx	n is a running number, but is only included if the table has more than one one UNIQUE constraint. aa is the first part of the name of the constraint in the file, forced to lowercase. Usually this is "ak".
CHECK	ckc_xxx_colname	This applies to CHECK constraints on column level; see below about table-level CHECK constraints.
DEFAULT	default_xxx_colname
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 in PowerDesigner is that you cannot define more than one table-level CHECK constraint per table. You can of course construct one big table constraint by AND:ing all business rules you want to validate, but if the constraint raises an error, you don't know what rule that was violated. TBLFIX (as well as PDREP) therefore supports a special convention. In the the definition for the table constraint, you can enter one or more constraints that consists of three hash marks, a constraint name and a condition as in this example:

### ckt_ins_pledgevalues (NOT  (pawnpercent IS NOT NULL AND pawnprice IS NOT NULL))
### ckt_ins_marginvalues  
     (NOT (marginpercent IS NOT NULL AND marginamt IS NOT NULL)),
### ckt_ins_expiration (expirationday <= expirationsettlementday),

This results in:

 CONSTRAINT ckt_ins_pledgevalues CHECK
   (NOT (pawnpercent IS NOT NULL AND pawnprice IS NOT NULL)),
CONSTRAINT ckt_ins_marginvalues CHECK
   (NOT (marginpercent IS NOT NULL AND marginamt IS NOT NULL)),
CONSTRAINT ckt_ins_expiration CHECK
   (expirationday <= expirationsettlementday)

If you look closely, you see that in the source text, there is no comma on the first line, but there is one on the second and third lines. In the result from TBLFIX, there are commas on the first two lines but not on the last, as mandated by the SQL syntax. That is, TBLFIX adds or removes commas as needed. Also, as the example shows, you can put the constraint on several lines. TBLFIX will always split after CHECK, and then respect whatever line-breaks you have in there. TBLFIX largely respects your level of indentation.

TBLFIX retains the name of the CHECK constraint, but if the name does not start with ckt_xxx is is the shortname for the table, TBLFIX issues a warning.

TBLFIX does not perform any complete syntax check on the condition for the constraint. However, if it finds that the number of left and right parentheses do not match for the table, it issues a warning.

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.

PowerDesigner Configuration

PowerDesigner has several options for how to generate the script. It is not likely that all these settings will work with TBLFIX. I will have to confess that I don't know whether the default settings work well or not; it is not very often I start working with a completely new model. Rather than listing all settings that could affect script generation, I refer you to the PowerDesigner file for the ABAPERLS subsystem, which is included in the AbaPerls distribution. That is, if you run into problems, open the abaperls.pdm and compare with your file.

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.