Physical Parameters
Colors used:
Gray: parameters where reverse engineering is non-directional.
Red: parameters not processed.
Green: parameters reverse engineered.
Tables
• Clause type: table properties
o POSTGRESQL_TBL_TEMPORARY (boolean)
o POSTGRESQL_ TBL_LOG (boolean)
o POSTGRESQL_ TBL_NOT_EXISTS (boolean)
o POSTGRESQL_ TBL_COMMIT (PRESERVE ROWS, DELETE ROWS, DROP)
o POSTGRESQL_ TBL_OIDS (yes, no)
o Link to a data group
o Link to a clause type storage parameter
Example:
CREATE TABLE example_with_storage_parameter (code char(5)) WITH (fillfactor=60, autovacuum_enabled);
• Clause type: table constraint
o POSTGRESQL_CONSTRAINT_NAME (String)
o POSTGRESQL_CONSTRAINT_TYPE (UNIQUE, PRIMARY KEY, EXCLUDE, FOREIGN KEY)
o POSTGRESQL _EXPRESSION (String)
o POSTGRESQL_DEFERRABLE (yes, no)
o POSTGRESQL_DEFERRED (yes, no)
o POSTGRESQL_MATCH_TYPE (Full, partial, simple)
o POSTGRESQL_UPDATE_ACTION (String)
o POSTGRESQL_DELETE_ACTION (String)
o POSTGRESQL_CONDITION (String)
o Link to a clause type storage parameter
o Link to a clause type column option (multiple clause)
o Link to a data group
o Link to a table
o Link to a column
CREATE TABLE table_with_constraint (
did integer CHECK (did > 100),
name varchar(40)
);
Columns
• Clause type: column properties
o POSTGRESQL_COLLATE (String)
Example:
CREATE TABLE table_with_collation (c1 serial, c2 text COLLATE "C");
• Clause type: column constraint
o POSTGRESQL_CONSTRAINT_NAME (String)
o POSTGRESQL_CONSTRAINT_TYPE (UNIQUE, PRIMARY KEY, FOREIGN KEY)
o POSTGRESQL_CONSTRAINT_EXPRESSION (String)
o POSTGRESQL_DEFERRABLE (yes, no)
o POSTGRESQL_DEFERRED (yes, no)
o POSTGRESQL_MATCH_TYPE (Full, partial, simple)
o POSTGRESQL_ACTION (String)
o Link to a clause type storage parameter
o Link to a data group
o Link to a table
o Link to a column
Indexes
• Clause type: index properties
o POSTGRESQL_IDX_CONCURRENTLY (boolean)
o POSTGRESQL_IDX_METHOD (btree,hash,gist,spgist,gin)
o POSTGRESQL_PREDICATE (String)
o POSTGRESQL_EXPRESSION (String)
o Link to a clause type storage parameter
o Link to a data group
• Clause type: column option
Examples:
CREATE INDEX example_character_idx ON example_character_type USING btree (col_char2) WITH (fillfactor=60)
CREATE INDEX index_with_tbs ON example_character_type (col_varchar1) TABLESPACE dbspace;
Tablespaces
• Clause type: tablespace properties
o POSTGRESQL_TBS_LOCATION (String)
CREATE TABLESPACE dbspace LOCATION 'C:\Travail\2014\Database\PostGreSql';
CREATE TABLE table_with_tablespace (
id serial,
name text,
location text
) TABLESPACE dbspace;
Generic clause types
• Clause type: storage parameter
o POSTGRESQL_ FILLFACTOR (integer)
o POSTGRESQL_VACUUM (boolean)
o POSTGRESQL_THRESHOLD (integer)
o POSTGRESQL_SCALE_FACTOR (float)
o POSTGRESQL_ANALYSE_THRESHOLD (integer)
o POSTGRESQL_ANALYSE_SCALE_FACTOR (double)
o POSTGRESQL_COST_DELAY (integer)
o POSTGRESQL_COST_LIMIT (integer)
o POSTGRESQL_FREEZE_MIN_AGE (integer)
o POSTGRESQL_FREEZE_MAX_AGE (integer)
o POSTGRESQL_FREEZE_TABLE_AGE (integer)
o POSTGRESQL_MULTIXACT_FREEZE_MIN_AGE (integer)
o POSTGRESQL_ MULTIXACT_FREEZE_MAX_AGE (integer)
• Clause type: column option
o Link to a column
o POSTGRESQL_EXPRESSION (String)
o POSTGRESQL_COLLATE (String)
o POSTGRESQL_CLASS (String)
o POSTGRESQL_SORT (ASK, DESK)
o POSTGRESQL_NULL (NULLS, NULLS FIRST, NULLS LAST)
Unmanaged physical parameters
• INHERITS
The optional INHERITS clause specifies a list of tables from which the new table automatically inherits all columns. Use of INHERITS creates a persistent relationship between the new child table and its parent table(s). Schema modifications to the parent(s) normally propagate to children as well, and by default the data of the child table is included in scans of the parent(s).
• LIKE
The LIKE clause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints. Unlike INHERITS, the new table and original table are completely decoupled after creation is complete. Changes to the original table will not be applied to the new table, and it is not possible to include data of the new table in scans of the original table. Default expressions for the copied column definitions will only be copied if INCLUDING DEFAULTS is specified. The default behavior is to exclude default expressions, resulting in the copied columns in the new table having null defaults.
• TYPE_NAME
Creates a typed table, which takes its structure from the specified composite type (name optionally schema-qualified). A typed table is tied to its type; for example the table will be dropped if the type is dropped (with DROP TYPE ... CASCADE). When a typed table is created, then the data types of the columns are determined by the underlying composite type and are not specified by the CREATE TABLE command. But the CREATE TABLE command can add defaults and constraints to the table and can specify storage parameters.
Properties pages
An Sql Clause contains the definition of all physical parameters of all DBMSs managed by MEGA. To display the correct properties page according to the DBMS and the clause type, a specific properties page must be generated for each DBMS version.
• Create a MetaAttributGroup "PostgreSQL_93_Parameters"
This should be connected to the SQL Clause MetaClass
• Create a MetaPropertyPage "PostgreSQL_93_ Parameters"
Setup as follows:
[Page]
Order=10
[Filter]
Condition = (ItemCount(~ISl(gRH(HTB8[IsClauseForPostgreSQL93])>0)
[Template]
ExtraParam=IncludeProfile(~ESl(CMH(HHX7[GeneratePropertyPageForPostgreSQL93]),Origin(Macro)
Where IsClauseForPostgreSQL93 is a query and GeneratePropertyPageForPostgreSQL93 is a macro. We can use as models the query IsClauseForPostgre93 and the macro GeneratePropertyPageForPostgreSQL93.
• Clause type: table constraint