F4F16ECD-F2F1-4006-AF6F-638D5C65F35E MySQL 5.0 MYSQL50 0 1129821012 mledier MySQL '****************************************************************************** '* Purpose: This VB-Script holds global definitions shared by all the custom- '* checks scripts of the model extension. '****************************************************************************** Option Explicit ' This is to ensure all used variables are defined function CheckColumn(pCol) dim retval, sdttp if(Instr(pCol.Datatype, "(") = 0) then sdttp = lCase(pCol.Datatype) else sdttp = lcase(left(pCol.Datatype, Instr(pCol.Datatype, "(") - 1)) Select Case(sdttp) Case "bit", "bool", "boolean","date","datetime","timestamp","time","year": CheckColumn = unAvailableAttribute(pCol, "CharSet,Collate,National,Unsigned,ZeroFill") Case "mediumblob", "longblob", "tinyblob", "binary", "varbinary" CheckColumn = unAvailableAttribute(pCol, "CharSet,Collate,National,Unsigned,ZeroFill") Case "tinyint","smallint","mediumint","int","integer","bigint","float","double precision","real": CheckColumn = unAvailableAttribute(pCol, "Charset,Collate,National") Case "decimal","dec","numeric","num": CheckColumn = unAvailableAttribute(pCol, "Charset,Collate,National") case "char","varchar","text","mediumtext","longtext","tinytext" CheckColumn = unAvailableAttribute(pCol, "Unsigned,ZeroFill") case else CheckColumn = True End Select end function function unAvailableAttribute(pCol, sAttrList) dim pos, sAttr, sVal pos = instr(sAttrList, ",") if (pos = 0) then sAttr = sAttrList else sAttr = left(sAttrList, pos - 1) sVal = lCase(pCol.GetExtendedAttribute(sAttr)) if ((sVal <> "false") and (sVal <> "")) then unAvailableAttribute = False elseif (pos = 0) then unAvailableAttribute = True else unAvailableAttribute = unAvailableAttribute(pCol, mid(sAttrList, pos + 1)) end if end function General Target DBMS identification SqlSupport SQL syntax allowed. This does not impact the script generation, but it impacts the SQL Preview 1 EnableCheck Determines if the generation of check parameters is authorized or not 1 Enableconstname Determines if constraint names are used during the generation UniqConstName Determines if unique constraint names for objects are authorized or not Script DBMS characteristics, command definition, and data type translations for the Script generation and reverse engineering Sql Contains sub-categories Syntax, Format, File and Keywords. Each sub-category contains entries whose values define general syntax for the database Syntax Contains general parameters for SQL syntax Terminator End of command character ; BlockTerminator End of block character Delimiter Field separation character. Example: col1, col2, col3 , Quote Character used to enclose string values ' SqlContinue Continuation character UseBlockTerm Use end of block character by default BlockComment Characters used to enclose a multi-line comment /* */ LineComment Characters used to enclose a single line comment # Format Contains entries that define script formatting IllegalChar Invalid characters for names " +-*/!=<>'"()". UpperCaseOnly Uppercase only LowerCaseOnly Lowercase only EnableOwnerPrefix Object codes can have a prefix made of the object owner code EnableDtbsPrefix Object codes can have a prefix made of the object database code File Contains header, footer and usage text entries used during the generation Header Header text for a database generation script Footer Footer text for a database generation script EnableMultiFile Multi-script allowed 1 ScriptExt Main script extension in database generation sql TableExt Other scripts extension in database generation tab StartCommand Command for executing a script mysql> source <file_name> Usage1 (1) Start command prompt (2) Go to the directory %PATHSCRIPT% (3) Start the SQL interpreter: mysql.exe (4) Run the database creation script: mysql> source %NAMESCRIPT% Usage for a single script in database generation Usage2 (1) Start command prompt (2) Go to the directory %PATHSCRIPT% (3) Start the SQL interpreter: mysql.exe (4) Run the database creation scripts: mysql> source <script_file_name> Usage for multiple scripts in database generation Keywords Contains the list of reserved words and functions available in SQL ReservedDefault NULL Reserved default values Commit commit Command for validating the transaction by OBDC ReservedWord ADD ALL ALTER ANALYZE AND AS ASC AUTO_INCREMENT BDB BEFORE BERKELEYDB BETWEEN BIGINT BINARY BLOB BOTH BTREE BY CASCADE CASE CHANGE CHAR CHARACTER CHECK COLLATE COLUMN COLUMNS CONSTRAINT CREATE CROSS CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP DATABASE DATABASES DAY_HOUR DAY_MINUTE DAY_SECOND DEC DECIMAL DEFAULT DELAYED DELETE DESC DESCRIBE DISTINCT DISTINCTROW DIV DOUBLE DROP ELSE ENCLOSED ERRORS ESCAPED EXISTS EXPLAIN FALSE FIELDS FLOAT FOR FORCE FOREIGN FROM FULLTEXT FUNCTION GRANT GROUP HASH HAVING HIGH_PRIORITY HOUR_MINUTE HOUR_SECOND IF IGNORE IN INDEX INFILE INNER INNODB INSERT INT INTEGER INTERVAL INTO IS JOIN KEY KEYS KILL LEADING LEFT LIKE LIMIT LINES LOAD LOCALTIME LOCALTIMESTAMP LOCK LONG LONGBLOB LONGTEXT LOW_PRIORITY MASTER_SERVER_ID MATCH MEDIUMBLOB MEDIUMINT MEDIUMTEXT MIDDLEINT MINUTE_SECOND MOD MRG_MYISAM NATURAL NOT NULL NUMERIC ON OPTIMIZE OPTION OPTIONALLY OR ORDER OUTER OUTFILE PRECISION PRIMARY PRIVILEGES PROCEDURE PURGE READ REAL REFERENCES REGEXP RENAME REPLACE REQUIRE RESTRICT RETURNS REVOKE RIGHT RLIKE RTREE SELECT SET SHOW SMALLINT SOME SONAME SPATIAL SQL_BIG_RESULT SQL_CALC_FOUND_ROWS SQL_SMALL_RESULT SSL STARTING STRAIGHT_JOIN STRIPED TABLE TABLES TERMINATED THEN TINYBLOB TINYINT TINYTEXT TO TRAILING TRUE TYPES UNION UNIQUE UNLOCK UNSIGNED UPDATE USAGE USE USER_RESOURCES USING VALUES VARBINARY VARCHAR VARCHARACTER VARYING WARNINGS WHEN WHERE WITH WRITE XOR YEAR_MONTH ZEROFILL Reserved words ListOperators = != <> <= < >= > <=> not ! or || xor and && | & ^ << >> ~ in not in between not between like not like is null is not null isnull ifnull nullif if case coalesce interval List of operators for comparing values, boolean, and various semantic operators NumberFunc abs) sign() mod() floor() ceiling() round() exp() ln() log() log2() log10() pow() power() sqrt() pi() cos() sin() tan() acos() asin() atan() atan2() cot() rand() least() greatest() degrees() radians() truncate() bit_count() List of SQL functions used on numbers DateFunc dayofweek() weekday() dayofmonth() dayofyear() month() dayname() monthname() quarter() week() year() yearweek() hour() minute() second() period_add() period_diff() date_add() date_sub() adddate() subdate() extract() to_days() from_days() date_format() time_format() curdate() current_date curtime() current_time now() sysdate() current_timestamp unix_timestamp() from_unixtime() sec_to_time() time_to_sec() List of SQL functions for dates CharFunc ascii() ord() conv() bin() oct() hex() char() concat() concat_ws() length() octet_length() char_length() character_length() bit_lenght() locate() position() instr() lpad() rpad() left() right() substring() mid() substring_index() ltrim() rtrim() trim() soundex() space() replace() repeat() reverse() insert() elt() field() find_in_set() make_set() export_set() lcase() lower() ucase() upper() load_file() quote() strcmp() match() List of SQL functions for characters and strings GroupFunc count() avg() min() max() sum() variance() std() stddev() bit_or() bit_and() List of SQL functions to use with group keywords. ConvertFunc List of SQL functions used to convert values between hex and integer and handling strings OtherFunc database() user() system_user() session_user() current_user() password() old_password() encrypt() encode() decode() md5() sha1() sha2() aes_encrypt() aes_decrypt() des_encrypt() des_decrypt() last_insert_id() format() version() connection_id() get_lock() release_lock() is_free_lock() benchmark() inet_ntoa() inet_aton() master_pos_wait() found_rows() List of other SQL functions Objects Contains sub-categories for each type of object in the database, for example: Table, or Reference. Each sub-category contains entries whose values define database commands and object-related characteristics Table TABL The following system variables are available: "TABLE" // generated code of the table "TNAME" // name of the table "TCODE" // code of the table "TLABL" // comment of the table "PKEYCOLUMNS" // list of primary key columns. Eg: A, B "TABLDEFN" // complete body of the table definition. Contains definition of columns, checks and keys Maxlen Maximum object code length 64 Create create [%Temporary%?temporary ]table[%R%?[ if not exists]] [%Schema%.]%TABLE% ( %TABLDEFN% ) [comment = "%TLABL%" ][%OPTIONS%] Command for creating a table. Example: create table %TABLE% Options type = %s : list = BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM, default = ISAM auto_increment = %d avg_row_length = %d checksum = %d : list = 0 | 1, default = 0 min_rows = %d max_rows = %d pack_keys = %d : list = 0 | 1 | default, default = default password = %s delay_key_write = %d : list = 0 | 1, default = 0 row_format = %s : list = default | dynamic | static | compressed, default = default raid_type = %d : list = 1 | striped | raid0 raid_chunks = %d raid_chunksize = %d union = %s insert_method = %s: list = no | first | last, default = no data directory = %s index directory = %s Available options for creating a table TableComment Command for adding a table comment Drop drop table if exists [%Schema%.]%TABLE% Command for dropping a table. Example: drop table %TABLE% Rename rename table %OLDTABL% to %NEWTABL% Command for renaming a table AlterTableHeader Alter table header AlterTableFooter Alter table footer DefineTableCheck check (%.A:CONSTRAINT%) Allows to customize the script of table check constraints Enable Table allowed 1 SqlListQuery {Schema, TABLE} select t.table_schema, t.table_name from information_schema.tables t where 1=1 [ and t.table_schema = %.q:CATALOG%] order by 1 SQL query to list objects Index INDX The following system variables are available: (parent table items are also available for indexes) "INDEX" // generated code of the index "INDEXNAME" // index name "INDEXCODE" // index code "UNIQUE" // keyword "unique" when the index is unique "INDEXTYPE" // index type (available only for a few DBMS) "INDEXKEY" // keywords "primary", "unique" or "foreign" depending on the index origin "CIDXLIST" // list of index columns. Eg: A asc, B desc, C asc "CLUSTER" // keyword "cluster" when the index is cluster For index columns, the following system variables are available: "ASC" // keywords "ASC" or "DESC" depending on sort order "ISASC" // TRUE if the index column sort is ascending Maxlen Maximum object code length 64 MaxColIndex Maximum number of columns in an index 16 Enable Index allowed 1 EnableAscDesc ASC, DESC keywords allowed UniqName Unique index name in the database Create create [%UNIQUE% ][%FullText%?fulltext ]index %INDEX% on %TABLE% ( %CIDXLIST% ) Command for creating an index. Example: create index %INDEX% AddColIndex %COLUMN% Command for defining an index column Options Default options for creating an index Drop drop index %INDEX% on %TABLE% Command for dropping an index. Example: drop index %INDEX% SqlListQuery {TABLE ID, INDEX ID, FullText ID, CIDXLIST ...} select x.table_name, x.index_name, if(x.index_type = 'FULLTEXT', 'true', ''), x.column_name from information_schema.statistics x where not exists (select 1 from information_schema.table_constraints k where k.constraint_name = x.index_name and k.constraint_schema = x.index_schema and k.table_name = x.table_name) [ and x.table_name = %.q:TABLE%] SQL query to list objects Column COLN The following system variables are available: (parent table items are also available for columns) "COLUMN" // generated code of the column "COLNNO" // position of the column in the list of columns of the table "COLNNAME" // name of the column "COLNCODE" // code of the column "PRIMARY" // keyword "primary" if the column is primary "ISPKEY" // TRUE if the column is part of the primary key "FOREIGN" // TRUE if the column is part of one foreign key Maxlen Maximum object code length 64 EnableDefault Default values allowed 1 Add %20:COLUMN% [%National%?national ]%DATATYPE%[%Unsigned%? unsigned][%ZeroFill%? zerofill][ [.O:[character set][charset]] %CharSet%][.Z:[ %NOTNULL%][%IDENTITY%? auto_increment:[ default %DEFAULT%]][ comment %.q:@OBJTLABL%]] Command for defining a table column ColumnComment Command for adding a column comment Rename alter table %TABLE% change column %OLDCOLN% %NEWCOLN% %DATATYPE%[ %NOTNULL%][ default %DEFAULT%] Command for renaming a column Create alter table %TABLE% add %COLUMN% [%National%?national ]%DATATYPE%[%Unsigned%? unsigned][%ZeroFill%? zerofill][ [.O:[character set][charset]] %CharSet%][.Z:[ %NOTNULL%][%IDENTITY%? auto_increment:[ default %DEFAULT%]][ comment %.q:@OBJTLABL%]] Command for adding a column ModifyColumn alter table %TABLE% modify column %COLUMN% %DATATYPE% [%NOTNULL%] [default %DEFAULT%][%IDENTITY% ? AUTO_INCREMENT:] Command for modifying a column Drop alter table %TABLE% drop column %COLUMN% Command for dropping a column Enable Column allowed 1 ModifyColnDflt alter table %TABLE% alter column %COLUMN%[%DEFAULT% ? set default %DEFAULT%: drop default] Command for modifying a column default in an alter table statement EnableIdentity Identity keyword entry support. Identity columns are serial counters maintains by the database 1 SqlListQuery {TABLE, COLUMN, DATATYPE, NOTNULL, DEFAULT, IDENTITY, COMMENT, National, Unsigned, ZeroFill} select c.table_name, c.column_name, replace(replace(lower(c.column_type), ' unsigned', ''), ' zerofill', ''), case(c.is_nullable) when 'NO' then 'notnull' else 'null' end, c.column_default, if(c.column_default is null, if(lower(c.extra) = 'auto_increment', 'identity', ''), ''), c.column_comment, case (ifnull(c.character_set_name, '-')) when '-' then null when 'utf8' then 'true' else 'false' end, if(InStr(lower(c.column_type), ' unsigned')=0, '', 'true'), if(InStr(lower(c.column_type), ' zerofill')=0, '', 'true') from information_schema.columns c [where c.table_name = %.q:TABLE%] order by 1, c.ordinal_position SQL query to list objects MaxConstLen Maximum constraint name length 30 EnableOption Physical options allowed YES/NO 1 Reference REFR The following system variables are available: "REFR" // generated code of the reference "REFNAME" // reference name "PARENT" // generated code of the parent table "PNAME" // name of the parent table "PCODE" // code of the parent table "CHILD" // generated code of the reference "CNAME" // name of the child table "CCODE" // code of the child table "PQUALIFIER" // qualifier of the parent table. See QUALIFIER "CQUALIFIER" // qualifier of the child table. See QUALIFIER "REFRNAME" // Reference name "REFRCODE" // Reference code "FKCONSTRAINT" // Reference constraint name (foreign key) "PKCONSTRAINT" // constraint name of the parent key used to reference object "CKEYCOLUMNS" // list of parent key columns. Eg: C1, C2, C3 "FKEYCOLUMNS" // list of child foreign key columns. Eg: "UPDCONST" // Update declarative constraint. Keywords "restrict", "cascade", "set null" or "set default" "DELCONST" // Delete declarative constraint. Keywords "restrict", "cascade", "set null" or "set default" "MINCARD" // Min cardinality "MAXCARD" // Max cardinality "POWNER" // Parent table owner "COWNER" // child table owner "CHCKONCMMT" // TRUE when check on commit is selected on the reference (ASA 6.0 specific) For reference joins (couple of column in a reference), the following system variables are available: "CKEYCOLUMN" // generated code of the parent table column (primary key) "FKEYCOLUMN" // generated code of the child table column (foreign key) "PK" // primary key column generated code "PKNAME" // primary key column name "FK" // foreign key column generated code "FKNAME" // foreign key column name "AK" // alternate key column code (same as PK) "AKNAME" // alternate key column name (same as PKNAME) "COLTYPE" // primary column column datatype "DEFAULT" // foreign key column default value Enable References in MySQL are only present as script compatibility with other databases. 1 Add [constraint %CONSTNAME% ]foreign key[%R%? %CONSTNAME%] (%FKEYCOLUMNS%) references %PARENT%[ (%CKEYCOLUMNS%)][ %ReferenceMatch%][ on delete %DELCONST%][ on update %UPDCONST%] Command for defining a foreign key FKAutoIndex Foreign key is auto-indexed 1 DclDelIntegrity NO ACTION RESTRICT CASCADE SET NULL Declarative referential integrity constraint allowed for delete DclUpdIntegrity NO ACTION RESTRICT CASCADE SET NULL Declarative referential integrity constraint allowed for update Create alter table %CHILD% add [constraint %CONSTNAME% ]foreign key[%R%? %CONSTNAME%] (%FKEYCOLUMNS%) references %PARENT%[ (%CKEYCOLUMNS%)][ %ReferenceMatch%][ on delete %DELCONST%][ on update %UPDCONST%] Command for adding a foreign key SqlListQuery {CONSTNAME ID, TABLE ID, PARENT ID, FKEYCOLUMNS ..., PKEYCOLUMNS ...} select f.constraint_name, f.table_name, c.referenced_table_name, concat(c.column_name, ', '), concat(c.referenced_column_name, ', ') from information_schema.table_constraints f, information_schema.key_column_usage c where f.constraint_type = 'FOREIGN KEY' and c.constraint_name = f.constraint_name and c.constraint_schema = f.constraint_schema and c.table_name = f.table_name [ and f.table_name = %.q:TABLE%] order by 1, 2, 3, c.position_in_unique_constraint SQL query to list objects PKey PKEY The following system variables are available: (parent table items are also available for keys) "PKEYCOLUMNS" // list of primary key columns. Eg: A, B "ISPKEY" // TRUE when the key is the primary key of the table "KEY" // constraint name "PKEY" // constraint name for primary key "AKEY" // constraint name for alternate key "ISMULTICOLN" // TRUE if key has more than one column "CLUSTER" // keyword cluster Enable Primary key allowed 1 PkAutoIndex Primary key is auto-indexed 1 Add primary key (%PKEYCOLUMNS%) Command for defining a primary key Create alter table %TABLE% add primary key (%PKEYCOLUMNS%) Command for adding a primary key Drop alter table %TABLE% drop primary key Command for dropping a primary key Key KEY The following system variables are available: (parent table items are also available for keys) "COLUMNS" // List of columns of the key. Eg: "A, B, C" "ISPKEY" // TRUE when the key is the primary key of the table "KEY" // constraint name "PKEY" // constraint name for primary key "AKEY" // constraint name for alternate key "ISMULTICOLN" // TRUE if key has more than one column "CLUSTER" // keyword cluster Enable UNIQUE constraint allowed for tables 1 UniqConstAutoIndex UNIQUE constraint is auto-indexed 1 SqlAkeyIndex SQL query to obtain the alternate key indexes of a table by ODBC Add [%ExtUnique%?unique ][.O:[key ][index ]][%CONSTNAME% ](%COLUMNS%) Command for defining an alternate key Create alter table %TABLE% add unique [%CONSTNAME% ](%COLUMNS%) Command for adding an alternate key SqlListQuery {TABLE ID, CONSTNAME ID, ISPKEY ID, COLUMNS ...} select k.table_name, case(k.constraint_type) when 'PRIMARY KEY' then concat('PK_', k.table_name) else k.constraint_name end, case(k.constraint_type) when 'PRIMARY KEY' then 'true' else 'false' end, concat(c.column_name, ', ') from information_schema.table_constraints k, information_schema.key_column_usage c where k.constraint_type in ('PRIMARY KEY', 'UNIQUE') and c.constraint_name = k.constraint_name and c.constraint_schema = k.constraint_schema and c.table_name = k.table_name [ and k.table_name = %.q:TABLE%] order by 1, 2, c.position_in_unique_constraint SQL query to list objects Database DTBS The following system variables are available: "DATABASE" // generated code of the database Create create database[%R%?[ if not exists]] %DATABASE% Command for creating a database. Example: create database %DATABASE% Options Available options for creating a database OpenDatabase use %DATABASE% Command for opening a database. Example: open database %DATABASE% CloseDatabase Command for closing a database. Example: close database Drop drop database if exists %DATABASE% Command for dropping a database. Example: drop database %DATABASE% Enable Database allowed 1 SqlListQuery {DATABASE} select schema_name from information_schema.schemata SQL query to list objects EnableManyDatabases Many databases allowed 1 View The following system variables are available: "VIEW" // generated code of the view "VIEWNAME" // view name "VIEWCODE" // view code "VIEWCOLN" // List of columns of the view. Eg: "A, B, C" "SQL" // SQL text of the view. Eg: Select * from T1 "VIEWCHECK" // Keyword "with check option" if selected on the view "SCRIPT" // complete view creation order. Eg: create view V1 as select * from T1 Enable View allowed Create Command for creating a view. Example: create view %VIEW% Drop Command for dropping a view. Example: drop view %VIEW% ViewCheck Option for checking a view Domain The following system variables are available: "DOMAIN" // generated code of the domain (also available for columns) SQL Server specific domain system variables: "RULENAME" // name of the rule object associated with the domain "DEFAULTNAME" // name of the default object associated with the domain Enable User defined data types allowed Maxlen Maximum object code length 30 Create Command for creating a user defined data type Drop Command for dropping a user defined data type User USER The following system variables are available: "USER" // generated code of the user Enable User allowed 1 SqlListQuery {USER} select Left(u.grantee, Instr(u.grantee, "@") - 1) from information_schema.user_privileges u where u.privilege_type in ('USAGE', 'CREATE') SQL query to list objects Create create user %USER%[ identified by %.q:PASSWORD%] Command for creating a user DataType Contains data type translation entries. These entries list the correspondence between internal data types and the target database data types AmcdDataType Data types translation table from internal data types to target database data types. %n is the length of the data type %s is the size of the data type %p is the precision of the data type <UNDEF>=<Undefined> A%n=char(%n) VA%n=varchar(%n) LA=longtext LA%n=longtext LVA=longtext LVA%n=longtext BT=tinyint BT%n=tinyint SI=smallint I=int LI=bigint N=numeric(8,0) N%n=numeric(%n,0) N%s,%p=numeric(%s,%p) DC=decimal DC%n=decimal(%n) DC%s,%p=decimal(%s,%p) SF=real F=float F%n=float(%n) LF=double MN=float(8,2) MN%n=float(%n,0) MN%s,%p=float(%s,%p) D=date T=time DT=datetime TS=timestamp BL=bool NO=int NO%n=int(%n) BIN%n=blob LBIN=longblob LBIN%n=longblob TXT=text TXT%n=text MBT=national char(1) MBT%n=national char(%n) VMBT=national varchar(255) VMBT%n=national varchar(%n) PIC=longblob PIC%n=longblob BMP=longblob BMP%n=longblob OLE=longblob OLE%n=longblob ENUM(%s)=enum(%s) SET(%s)=set(%s) *=char(10) <UNDEF> A%n VA%n LA LA%n LVA LVA%n BT BT%n SI I LI N N%n N%s,%p DC DC%n DC%s,%p SF F F%n LF MN MN%n MN%s,%p D T DT TS BL NO NO%n BIN%n LBIN LBIN%n TXT TXT%n MBT MBT%n VMBT VMBT%n PIC PIC%n BMP BMP%n OLE OLE%n ENUM(%s) SET(%s) PhysDataType Data types translation table from target database data types to internal data types. <Undefined>=<UNDEF> bit=BT bit(%n)=BT%n bool=BL char=A1 char(%n)=A%n char(%n) binary=CHAR(%n) national char(%n)=MBT%n national char(%n) binary=MBT%n varchar(%n)=VA%n varchar(%n) binary=VA%n national varchar(%n)=VMBT%n national varchar(%n) binary=VMBT%n decimal=DC decimal(%n)=DC%n decimal(%s,%p)=DC%s,%p dec=DC dec(%n)=DC%n dec(%s,%p)=DC%s,%p numeric=N numeric(%n)=N%n numeric(%s,%p)=N%s,%p integer=I integer(%n)=I int=I int(%n)=I tinyint=BT tinyint(%n)=BT%n smallint=SI smallint(%n)=SI mediumint=I mediumint(%n)=I bigint=LI bigint(%n)=LI real=SF real(%s,%p)=SF float=F float(%n)=F%n float(%s,%p)=F%s double=LF double(%s,%p)=LF double precision=LF double precision(%s,%p)=LF date=D time=T datetime=DT timestamp=TS timestamp(%n)=TS year=D year(%n)=D tinyblob=LBIN tinytext=TXT blob=LBIN text=TXT mediumblob=LBIN mediumtext=TXT longblob=LBIN longtext=TXT enum(%s)=ENUM(%s) set(%s)=SET(%s) *=A10 <Undefined> bit bit(%n) bool char char(%n) char(%n) binary national char(%n) national char(%n) binary varchar(%n) varchar(%n) binary national varchar(%n) national varchar(%n) binary decimal decimal(%n) decimal(%s,%p) dec dec(%n) dec(%s,%p) numeric numeric(%n) numeric(%s,%p) integer integer(%n) int int(%n) tinyint tinyint(%n) smallint smallint(%n) mediumint mediumint(%n) bigint bigint(%n) real real(%s,%p) float float(%n) float(%s,%p) double double(%s,%p) double precision double precision(%s,%p) date time datetime timestamp timestamp(%n) year year(%n) tinyblob tinytext blob text mediumblob mediumtext longblob longtext enum(%s) set(%s) PhysDttpSize Table of storage sizes of target database data type PhysOdbcDataType Data types translation table from target database data types to ODBC data types. char=char(1) int=int(11) tinyint=tinyint(4) bool=tinyint(1) smallint=smallint(6) mediumint=mediumint(9) bigint=bigint(20) decimal=decimal(10,0) decimal(%n)=decimal(%n,0) year=year(4) char int tinyint bool smallint mediumint bigint decimal decimal(%n) year OdbcPhysDataType Data types translation table from ODBC data types to target database data types. Profile 1 Shared Shared 1 ExtendedAttributeTypeTargetItem Extended Attribute Types 1 CC4E9770-FD82-4A12-A934-265A0DF2CC1F ReferenceMatchType Reference match type 1 MATCH FULL MATCH PARTIAL 7323C92E-5C24-4E40-8227-6D059042CC89 DatabaseType Database types 1 ISAM BDB HEAP ISAM InnoDB MERGE MRG_MYISAM MYISAM AC77C020-F18D-4645-8248-73E8D7268576 CharSets Contains the list of available Character Sets. 1 big5 dec8 cp850 hp8 koi8r latin1 latin2 swe7 ascii ujis sjis hebrew tis620 euckr koi8u gb2312 greek cp1250 gbk latin5 armscii8 utf8 ucs2 cp866 keybcs2 macce macroman cp852 latin7 cp1251 cp1256 cp1257 binary geostd8 cp932 eucjpms Index 1 ExtendedAttributeTargetItem Extended Attributes 1 D50A4A6A-409C-4FA2-93F8-666FE6F7615C FullText The index is a full text index 1 false FormTargetItem Forms 1 MySQL <Form > <ExtendedAttribute Name="FullText" Caption="Full-text index" Attribute="FullText" AttributeID="{D50A4A6A-409C-4FA2-93F8-666FE6F7615C}" GrayHiddenAttributes="Yes" /> </Form> 1 CustomCheckTargetItem Custom Checks 1 Fulltext indexes validity 1 This check ensures that a full text index is based on a char column and on a table having the MyISAM type. Autofix reset the FullText property of the index. The following fulltext indexes have incorrect definition Function %Check%(obj) if (CBool(obj.GetExtendedAttribute("FullText"))) then ' Having a full text index, we must ensure all columns are ' char, varchar or text dim pCol, sDttp, bOK for each pCol in obj.IndexColumns if (left(lcase(pCol.Column.Datatype), len("char")) <> "char") and _ (left(lcase(pCol.Column.Datatype), len("varchar")) <> "varchar") and _ (left(lcase(pCol.Column.Datatype), len("text")) <> "text") and _ (left(lcase(pCol.Column.Datatype), len("mediumtext")) <> "mediumtext") and _ (left(lcase(pCol.Column.Datatype), len("longtext")) <> "longtext") and _ (left(lcase(pCol.Column.Datatype), len("tinytext")) <> "tinytext") then %Check% = false exit function end if next ' good. Now we must check table has type=MyISAM dim sOpts : sOpts = lcase(replace(obj.Table.PhysicalOptions, " ", "")) if (InStr(sOpts, "type=myisam") = 0) then %Check% = false exit function end if end if %Check% = True End Function Function %Fix%(obj, outmsg) ' Autofix will uncheck the "Fulltext" property obj.SetExtendedAttribute "FullText", False outmsg = "index " & obj.Table.Name & "." & obj.Name & " is no longer a Full-text index" %Fix% = True End Function 1 Reference 1 ExtendedAttributeTargetItem Extended Attributes 1 208169ED-E14F-4B04-93BD-2FE48112A769 ReferenceMatch Reference match type 1 -1 Table 1 CustomCheckTargetItem Custom Checks 1 Table storage type 1 This check ensures that the table has the correct storage type. The following tables have invalid storage type: Function %Check%(obj) %Check% = True Dim dbType dbType = obj.Model.GetExtendedAttribute("DatabaseType") output "type = " + dbType If dbType <> "" Then If (InStr(1, obj.PhysicalOptions, "type=" + dbType, 1) = 0) and (InStr(1, obj.PhysicalOptions, "type = " + dbType, 1) = 0) Then %Check% = False End If End If End Function Function %Fix%(obj, outmsg) Dim dbType dbType = obj.Model.GetExtendedAttribute("DatabaseType") obj.PhysicalOptions = "type = " + dbType + " " + obj.PhysicalOptions outmsg = "Add type = " + dbType + " in physical options" %Fix% = False End Function 1 1 ExtendedAttributeTargetItem Extended Attributes 1 31D066BE-2F61-4B4D-B7B4-230D38F22B99 Temporary Used to create a temporary table. A temporary table is visible only to the current connection, and is dropped automatically when the connection is closed. 1 false 811818B1-0FD6-4FA0-843C-C06C679470C6 Schema Defines the database the table belongs to. 1 12 FormTargetItem Forms 1 MySQL <Form > <ExtendedAttribute Name="Temporary" Caption="Temporary table" Attribute="Temporary" AttributeID="{31D066BE-2F61-4B4D-B7B4-230D38F22B99}" GrayHiddenAttributes="Yes" /> <HorizontalLayout Name="HorizontalLayout1" > <ExtendedAttribute Name="Schema" Caption="Schema" Attribute="Schema" AttributeID="{811818B1-0FD6-4FA0-843C-C06C679470C6}" GrayHiddenAttributes="Yes" /> <CustomMethod Name="PickSchema" Caption="..." Method="PickSchema" /> </HorizontalLayout> </Form> 1 MethodTargetItem Methods 1 PickSchema 1 Sub %Method%(obj) Dim pModel : Set pModel = obj.Model If (pModel is Nothing) then Exit Sub Dim pSelct : Set pSelct = pModel.CreateSelection if (pSelct is Nothing) then output "Unable to create a selection..." exit sub end if Dim pDB : set pDB = pSelct.ShowObjectPicker ("Database", "", "Select a schema from the list", False) if (pDB is Nothing) then output "Schema selection cancelled or no Database defined in the model" else obj.SetExtendedAttribute "Schema", pDB.Code End If pSelct.Delete End Sub Model 1 ExtendedAttributeTargetItem Extended Attributes 1 6EBD0427-31BE-44D4-BA6D-77622CBC4F1E DatabaseType Database type 1 -1 Key 1 ExtendedAttributeTargetItem Extended Attributes 1 D1C795B8-8F7B-4AA7-A240-85C5B737C543 ExtUnique TRUE if the key is unique 1 false Column 1 ExtendedAttributeTargetItem Extended Attributes 1 4A2BD2F3-4A8A-4421-8A48-A8029BDA28E8 Unsigned Indicates negative values are not allowed for the column. 1 4EC131E7-C7D4-4E04-B6F6-A6BF9CCF838C ZeroFill When displayed, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column. 1 9C949EAB-FF87-446D-938C-8F03A4ABDC8E National A way to indicate that a CHAR column should use UTF8 character set. 1 9480E6AA-83DD-4AA7-9C91-E48F709C29D9 CharSet Character set (set of symbols and encodings.) 1 -1 B314652C-DD43-4F81-8524-6F97A1BAACBA Collate Collation (set of rules for comparing characters in a character set.) 1 12 FormTargetItem Forms 1 MySQL <Form > <GroupBox Name="GroupBox1" Caption="Numerics" ControlAsLabel="No" > <ExtendedAttribute Name="ZeroFill" Caption="Retrieve with leading zeros" Attribute="ZeroFill" AttributeID="{4EC131E7-C7D4-4E04-B6F6-A6BF9CCF838C}" GrayHiddenAttributes="Yes" /> <ExtendedAttribute Name="Unsigned" Caption="Unsigned" Attribute="Unsigned" AttributeID="{4A2BD2F3-4A8A-4421-8A48-A8029BDA28E8}" GrayHiddenAttributes="Yes" /> </GroupBox> <GroupBox Name="GroupBox2" Caption="Characters" ControlAsLabel="No" > <ExtendedAttribute Name="National" Caption="National" Attribute="National" AttributeID="{9C949EAB-FF87-446D-938C-8F03A4ABDC8E}" GrayHiddenAttributes="Yes" /> <ExtendedAttribute Name="CharSet" Caption="Character set" Attribute="CharSet" AttributeID="{9480E6AA-83DD-4AA7-9C91-E48F709C29D9}" GrayHiddenAttributes="Yes" /> <ExtendedAttribute Name="Collate" Caption="Collation" Attribute="Collate" AttributeID="{B314652C-DD43-4F81-8524-6F97A1BAACBA}" GrayHiddenAttributes="Yes" /> </GroupBox> </Form> List of extended attributes of the column 1 CustomCheckTargetItem Custom Checks 1 Datatype attributes 1 This check ensures that extended attributes of the column are consistent with its datatype The following columns do not have compatible datatype and attributes: Function %Check%(obj) %Check% = CheckColumn(obj) End Function Function %Fix%(obj, outmsg) ' Implement your automatic correction on <obj> here ' filling <outmsg> as you wish ' and return True if successful. outmsg = "Automatic correction not implemented" %Fix% = False End Function auto increment key Rules is that the first column of an unique index (or key) must contains the auto_increment column. 1 This check ensure that the auto_increment column is defined as first column of a table key (unique or primary) The following auto_increment columns must be defined as a key: Function %Check%(obj) if (obj.Identity) then dim bFound : bFound = False dim pKey for each pKey in obj.Keys if (pKey.Columns.Item(0) is obj) then bFound = True Exit For end if next %Check% = bFound else %Check% = True end if End Function Function %Fix%(obj, outmsg) ' Autofix can create a supplementary table key ' which only contains the column auto_increment (identity) dim pKey : Set pKey = obj.Table.Keys.CreateNew pKey.Columns.Add obj pKey.SetNameAndCode "K" & obj.Name, "K" & obj.Code, True outmsg = "Create unique key " & pKey.Name & "(" & pKey.Code & ") for auto_increment column " & obj.DisplayName %Fix% = True End Function 1 Odbc DBMS characteristics, command definition, and data type translations for the ODBC generation and reverse engineering Objects Contains sub-categories for each type of object in the database, for example: Table, or Reference. Each sub-category contains entries whose values define database commands and object-related characteristics Qualifier Manages the use of qualifier in ODBC reverse engineering Enable Allows using the qualifier combo box during ODBC reverse engineering 1 SqlListQuery select schema_name from information_schema.schemata Query to retrieve qualifier during ODBC reverse engineering