Item Description
Add Specifies the statement required to add the object inside the creation statement of another object.
Example (adding a column):
%20:COLUMN% %30:DATATYPE% [default %DEFAULT%]
[%IDENTITY%?identity:[%NULL%][%NOTNULL%]]
[[constraint %CONSTNAME%] check (%CONSTRAINT%)]
AfterCreate/ After-Drop/ AfterModify
Specify extended statements executed after the main Create, Drop or Modify statements. For more information, see Script generation on page 121.
Alter Specifies the statement required to alter the object.
AlterDBIgnored Specifies a list of attributes that should be ignored when performing a com-parison before launching an update database.
AlterStatementList Specifies a list of attributes which, when changed, should give rise to an alter statement. Each attribute in the list is mapped to the alter statement that should be used.
BeforeCreate/ Befor-eDrop/ BeforeModi-fy
Specify extended statements executed before the main Create, Drop or Mod-ify statements. For more information, see Script generation on page 121.
ConstName Specifies a constraint name template for the object. The template controls how the name of the object will be generated.
The template applies to all the objects of this type for which you have not defined an individual constraint name. The constraint name that will be ap-plied to an object is displayed in its property sheet.
Examples (ASE 15):
• Table: CKT_%.U26:TABLE%
• Column: CKC_%.U17:COLUMN%_%.U8:TABLE%
• Primary Key: PK_%.U27:TABLE%
Create [generation and reverse] Specifies the statement required to create the object.
Example:
create table %TABLE%
Item Description
DefOptions Specifies default values for physical options (see Physical Options (DBMS) on page 207) that will be applied to all objects. These values must respect SQL syntax.
Example:
in default_tablespace
Drop Specifies the statement required to drop the object.
Example (SQL Anywhere 10):
if exists( select 1 from sys.systable where table_name=%.q:TABLE%
and table_type in ('BASE', 'GBL TEMP')[%QUALIFIER
%?
and creator=user_id(%.q:OWNER%)]
) then drop table [%QUALIFIER%]%TABLE%
end if
Enable Specifies whether an object is supported.
EnableOwner Enables the definition of owners for the object. The object owner can differ from the owner of the parent table. The following settings are available:
• Yes - The Owner list is enabled in the object's property sheet.
• No – Owners are not supported for the object.
Note that, in the case of index owners, you must ensure that the Create state-ment takes into account the table and index owner. For example, in Oracle 9i, the Create statement of an index is the following:
create [%UNIQUE%?%UNIQUE% :[%INDEXTYPE% ]]index [%QUALIFIER%]%INDEX% on [%CLUSTER%?cluster C_%TABLE
%:[%TABLQUALIFIER%]%TABLE% ( %CIDXLIST%
)][%OPTIONS%]
Where %QUALIFIER% refers to the current object (index) and %TABL-QUALIFIER% refers to the parent table of the index.
EnableSynonym Enables support for synonyms on the object.
Footer / Header Specify the object footer and header. The contents are inserted directly after or before each
create object
statement.MaxConstLen Specifies the maximum constraint name length supported for the object in the target database, where this value differs from the default specified in
Max-ConstLen
(see .Script/Objects Category (DBMS) on page 142).Item Description
MaxLen Specifies the maximum code length for an object. This value is used when checking the model and produces an error if the code exceeds the defined value. The object code is also truncated at generation time.
Modifiable Attrib-utes
Specifies a list of extended attributes that will be taken into account in the merge dialog during database synchronization. For more information, see Script generation on page 121.
Example (ASE 12.5):
ExtTablePartition
Options Specifies physical options (see Physical Options (DBMS) on page 207) available to apply when creating an object.
Example (ASA 6):
in %s : category=tablespace
Permission Specifies a list of available permissions for the object. The first column is the SQL name of permission (SELECT for example), and the second column is the shortname that is displayed in the title of grid columns.
Example (table permissions in ASE 15):
SELECT / Sel INSER / Ins DELETE / Del UPDATE / Upd REFERENCES / Ref
Reversed Queries Specifies a list of additional attribute queries to be called during live database reverse engineering. For more information, see Live database reverse engi-neering on page 125.
Reversed Statements Specifies a list of additional statements that will be reverse engineered. For more information, see Script reverse engineering on page 124.
SqlAttrQuery Specifies a SQL query to retrieve additional information on objects reversed by
SQLListQuery
.Example (Join Index in Oracle 10g):
{OWNER ID, JIDX ID, JIDXWHERE ...}
select index_owner, index_name,
outer_table_owner || '.' || outer_table_name || '.'
|| outer_table_column || '=' || inner_table_owner
|| '.' || inner_table_name || '.' || inner_ta-ble_column || ','
from all_join_ind_columns where 1=1
[ and index_owner=%.q:OWNER%]
[ and index_name=%.q:JIDX%]
Item Description
SqlListQuery Specifies a SQL query for listing objects in the reverse engineering dialog.
The query is executed to fill header variables and create objects in memory.
Example (Dimension in Oracle 10g):
{ OWNER, DIMENSION }
SqlOptsQuery Specifies a SQL query to retrieve physical options from objects reversed by
SqlListQuery
. The result of the query will fill the variable %OPTIONS% and must respect SQL syntax.
Example (Table in SQL Anywhere 10):
{OWNER, TABLE, OPTIONS}
select u.user_name, t.table_name, 'in '+ f.dbspace_name
from sys.sysuserperms u
join sys.systab t on (t.creator = u.user_id) join sys.sysfile f on (f.file_id = t.file_id) where f.dbspace_name <> 'SYSTEM'
and t.table_type in (1, 3, 4) [ and t.table_name = %.q:TABLE%]
[ and u.user_name = %.q:OWNER%]
SqlPermQuery Specifies a SQL query to reverse engineer permissions granted on the object.
Example (Procedure in SQL Anywhere 10):
{ GRANTEE, PERMISSION}
select
u.user_name grantee, 'EXECUTE'
from sysuserperms u, sysprocedure s, sysprocperm p where (s.proc_name = %.q:PROC% ) and
(s.proc_id = p.proc_id) and (u.user_id = p.grantee)
Default Variable
In a column, if the type of the default variable is text or string, the query must retrieve the value of the default variable between quotes. Most DBMS automatically add these quotes to the value of the default variable. If the DBMS you are using does not add quotes automatically, you have to specify it in the different queries using the default variable.
For example, in IBM DB2 UDB 8 for OS/390, the following line has been added in
SqlListQuery in order to add quotes to the value of the default variable:
... case(default) when '1' then '''' concat defaultvalue concat '''' when '5' then '''' concat defaultvalue concat '''' else defaultvalue end,
...