Common Object Items

The following items are available in various objects located in the Root > Script > Objects category.

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/ AfterDrop/ AfterModify

Specifies extended statements executed after the main Create, Drop or Modify statements. For more information, see Script generation.

Alter

Specifies the statement required to alter the object.

AlterDBIgnored

Specifies a list of attributes that should be ignored when performing a comparison 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/ BeforeDrop/ BeforeModify

Specifies extended statements executed before the main Create, Drop or Modify statements. For more information, see Script generation.

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 applied 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%

DefOptions

Specifies default values for physical options that will be applied to all objects. These values must respect SQL syntax.

Example:

in default_tablespace

For more information, see Physical Options.

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 statement 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 %TABLQUALIFIER% refers to the parent table of the index.

EnableSynonym

Enables support for synonyms on the object.

Footer

Specifies the object footer. The contents are inserted directly after each create object statement.

Header

Specifies the object header. The contents are inserted directly 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. See also MaxConstLen – defining a maximum constraint name length).

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 Attributes

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.

Example (ASE 12.5):

ExtTablePartition

Options

Specifies physical options for creating an object.

Example (ASA 6):

in %s : category=tablespace

For more information, see Physical Options.

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 engineering.

Reversed Statements

Specifies a list of additional statements that will be reverse engineered. For more information, see Script reverse engineering.

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_table_column || ','
from all_join_ind_columns
where 1=1
[  and index_owner=%.q:OWNER%]
[  and index_name=%.q:JIDX%]

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 }
select d.owner, d.dimension_name
from sys.all_dimensions d
where 1=1
[  and d.dimension_name=%.q:DIMENSION%]
[  and d.owner=%.q:SCHEMA%]
order by d.owner, d.dimension_name

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,
...