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.
tblfix [-shortnames file] [-types file] file1 [-header file]
||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.|
|File that maps type names to physical types. See below for the format.|
|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.|
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:
|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.
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.
These are the changes that TBLFIX makes in the extracted SQL code:
‑headeror there is a file tblfix.header in the current directory, TBLFIX adds this header to the file.
‑shortnamesfile, TBLFIX replaces the code with the matching name from the file.
‑shortnames, the shortname replaces the table in constraint names. E.g. pk_instruments becomes pk_ins, if the shortname for instruments is ins.
‑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.)
|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.|
|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.|
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 file you specify with
‑types, should consists of lines with this appearance:
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 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.
Copyright © 1996-2014,
Erland Sommarskog SQL-Konsult AB.
All rights reserved. AbaPerls is available under Perl Artistic License
This page last updated 14-01-03 16:13