Rjydthnjh

RELATIONAL DATABASE MANAGEMENT SYSTEM

Linter Standard Linter Bastion Linter RealTime Linter Multiversion

Data Model Converter

RELATIONAL EXPERT SYSTEMS

Trademarks RELEX™, LINTER®, NEVOD®, LAB™, LAKUNA are trademarks of «Relational Expert Systems, Ltd.» (hereafter, "RELEX"). Other names and brandings are trademarks of their manufacturers, sellers or developers. Intellectual Property All rights to LINTER®, NEVOD®, LAB™, and LAKUNA belong to RELEX (1990–2015). All rights reserved. This particular document is property of RELEX. No part of this document can be reproduced, transmitted, reformed, saved in an information search system, translated to another language or computer language in any form, by any electronic, mechanic, magnetic, optical, chemical, manual or other means without prior permission of RELEX. About the Document The material of this document has gone through cautious examination, but RELEX does not guarantee that the document contains no errors or omissions. RELEX reserves the right to bring in corrections and alterations, to reconsider and update contained information at any moment. Address Office: 119 20-letiya Oktyabrya St., Voronezh, 394006, Russia Phone / Fax: +7 (473) 2-711-711, 2-778-333 e-mail: [email protected].

Technical Support Software product support and maintenance division: Phone:+7 (473) 2-711–711 from 9:00 to 18:00 MSK (UTC+3). e-mail: [email protected], [email protected]. To improve the quality of developed software and delivered services, RELEX provides an automated system of filing and processing the reclamations. We request that you report us on any detected defects and errors in software products and/or documentations on our Internet website reclamation.

Contents Introduction ............................................................................................................................1 Document Purpose .....................................................................................................................1 Intended Readers........................................................................................................................1 Accepted Designations and Conventions ...................................................................................1 General....................................................................................................................................3 Converter Capabilities .................................................................................................................4 Operation Conditions.............................................................................................................6 Data Model Conversion .........................................................................................................7 Command Line............................................................................................................................7 Convert Table Synonyms............................................................................................................7 Convert Table..............................................................................................................................8 Convert Table Columns ..............................................................................................................9 Convert Reference Integrity ........................................................................................................9 Convert Primary and Foreign Keys...........................................................................................10 Convert Indexes ........................................................................................................................11 Convert Database Object Names .............................................................................................12 Convert Data Types ..................................................................................................................12 Converter Messages ............................................................................................................14

Data Model Converter

I

Introduction Document Purpose The document describes the ewc utility that converts the data model developed for a database using the Erwin software tool to the DBMS Linter data model. The document can be used for working with any DBMS Linter version. Specific features of particular versions are described further in the text.

Intended Readers The document is intended for DBMS Linter database architects and developers.

Accepted Designations and Conventions Designation Italic type

Example Raster is an image data…

Definition A new term in the text

Boldface type

In this case it is necessary to move all physical files. For more information about application performance see www.linter.ru. Execute the command

Highlighting in the text

Underlined blue type Text divided with  Text enclosed in < > with + between them Large monospaced text

View  Properties +

SQL> _q

Internet website address

Command execution sequence Pressing the specified keys together Command line text

Small monospaced text

Page Time Count

Program text

Capital letters

BROWSE

Text in < >

< return statement>

Command names, words reserved in SQL, key words Specified element of a semantic phrase Equality by definition. The defined notion stands to the left of the symbol, the concept definition itself stands to the right Optional phrase elements. In this particular example the keys are non-obligatory command elements

::= Character

Square brackets [ ]

Data Model Converter

DBSTORE [-d –r –t –u]

1

Introduction Designation Vertical line |

Example ::= | NULL

Curved brackets { }

CODEPAGE {866 | 1251 | KOI8} Column characteristics MAKE CHAR(20) MODEL CHAR(20) ... SQL>

Elision marks «…»

Elision marks with a comma inside «... , »

Text with  against gray background

2



If the page layout configuration ignores the properties, the command will be executed inappropriately.

Definition Specifies that one of the elements separated by the vertical line should be selected Specifies обязательный выбор из представленного списка obligatory choice from the proposed list Specifies that the previous part can be repeated any number of times.

Specifies that the previous statement part that consists of several elements divided by commas can be repeated a random number of times Note

Data Model Converter

General The ewc utility is used to convert the database schema developed using the ERwin/ERX data modeling tool to a DBMS Linter database schema. The result of ewc operation is a set of SQL scripts in the DBMS Linter dialect, starting from version 5.x. The initial (convertible) database schema should be created in advance using the ERwin database schema development tool, version 2.6 or 3.5, for any database supported by ERwin, and saved in .erx format. To get a clear understanding of the ewc utility purpose, let us describe the basic capabilities of ERwin/ERX. The ERwin/ERX data modeling tool is intended mainly for database designers, developers and system analysts. Using ERwin/ERX, developers can describe the database schema first using visual tools, and then automatically generate the data files for the selected relational DBMS (forward engineering). The triggers providing the database reference integrity are also generated automatically. Stored procedures are supported. The user describes the data structure visually by setting the entities that serve as relational table prototypes along with their attributes, and then, using the mouse, setting connections between them, the connections serving as relation prototypes. Reverse development is also possible. ERwin/ERX allows you to restore the logical data structure using the existing database files. This is called reverse engineering. It allows migrating the database structure (not data!) from one DBMS to another, and to explore old projects. Reverse engineering is most widely used in transitions from one technology to another (from file server to client server), and when changing the database server. ERwin/ERX supports forward and reverse engineering of over 20 databases by directly accessing the database system directory (without having to use the ODBC driver). ERwin/ERX supports the following DBMSs:  Oracle;  Sybase;  Informix;  CA Ingres;  DB2, Rdb;  Watcom;  Centura SQLBase;  Microsoft SQL Server;  AS/400;  Progress;  FoxPro;  InterBase;  dBASE;  Clipper;  Paradox;  Access etc. As you can see, DBMS Linter is not listed among the systems supported by Erwin, so it is not possible to use ERwin directly to model DBMS Linter data. However, you can get access to all Erwin modeling facilities using the ewc converter utility. To do this: 1) Describe the DBMS Linter data model using ERwin based on any DBMS supported by ERwin (the SQL language of the database used for modeling should be as close to DBMS Linter SQL

Data Model Converter

3

General as possible). The result is a ERX output file of the Erwin system containing the DBMS Linter data model description in SQL terms of the source database. 2) Process the resulting DBMS Linter data model as a ERX file by the ewc conversion utility. The result is the data model description as DBMS Linter SQL scripts. 3) If necessary, edit the resulting SQL scripts manually taking into account the DBMS Linter SQL language specifics. 4) Execute the SQL scripts. The result is a set of Linter database objects implementing the data model created using ERwin.

Converter Capabilities The converter allows generating the following SQL constructions: 1) Creating tables. Depending on the converter startup parameters, table creation implies the generation of the following: 

delete table SQL statements;



SQL statements for adding a primary key to the tables;



SQL statements for assigning a UNIQUE attribute to a table column;



create index SQL statements;



create synonym SQL statements.

2) Modifying table parameters. Table modification implies generating SQL statements for adding reference keys. 3) Field names (physical, logical, roles). ERwin allows assigning three types of names to table fields: 

logical name (name assigned to the field by the Erwin program when describing the database logical schema);



physical name (name assigned when describing the field);



role (additional name assigned to clarify the field role in the database schema. Used to distinguish between homonymic fields in different database objects).

If only the logical name is specified, the field in the newly created table will be designated with a name that coincides with the logical name, with the '_' character instead of spaces. If the physical name is specified, the field in the table will be designated by the physical name. If roles are specified for a logical name, as many fields will be created in the table as many roles the logical name has. If triggers are specified for one or several tables, the converter will place the stored procedure text into the file with the source file name and .spc extension. 4) Supported database types. Different DBMSs support different data type names. Therefore the converter provides two options to match the source DBMS data type and the DBMS Linter type.

4

Data Model Converter

General The first option is to create the data type correspondence table for the source DBMS and Linter. In this case, the DBMS the schema was developed for is irrelevant, the main thing is to find the corresponding DBMS Linter data type in the table. If the data type correspondence table is not set or does not contain the specific type, use the second option. The second option is standard conversion of DBMS data type to the Linter data type. For convenience, the converter supports the data format of several server types that the schema is created for: DB2, NetWare SQL Server, Oracle, Watcom SQL Anywhere (Table 1). If the schema is created for a server that is not listed and there is no type correspondence table (or the type is not specified in the table), the system will return the «Database type not supported» error. If such an error occurs, create the schema anew for any server from the list above, or use the type correspondence table. If the table contains a field that belongs to a type not supported by DBMS Linter, its type will be empty. Table 1. Correspondence of DBMS Linter Data Types to Various Servers Linter BLOB

DB2

Oracle

Watcom SQL Anywhere

GRAPHIC

BYTE() CHAR()

NetWare SQL

BIT()

B I N A RY ( )

CHAR(),

CHARACTER(),

CHAR(),

CHAR(),

CHARACTER(),

VA R C H A R ( )

CHARACTER(),

VA R C H A R ( )

VA R C H A R ( )

VA R C H A R ( )

D AT E

D AT E

D AT E

D AT E

D AT E

DECIMAL

DECIMAL

DECIMAL

DECIMAL

DECIMAL

DOUBLE

DOUBLE

INT INTEGER

INT INTEGER

INT INTEGER, LONG

INTEGER

I N T, A U TO I N C

A U TO I N C ( 4 )

ROWID

NUMERIC

NUMERIC

NUMBER

NUMERIC

REAL

F L O AT, R E A L

B F L O AT, F L O AT

F L O AT

F L O AT, R E A L

SMALLINT

SMALLINT

LOGICAL

SMALLINT

SMALLINT

Data Model Converter

5

Operation Conditions To run the ewc converter, the following conditions should be met: 1) the DBMS Linter kernel v. 5.9 and greater should be active; 2) the user working with ewc should have at least the minimum database access rights (Resource); 3) there should be no tables with the following names in the database:  .ERW_TEXT; 

< owner name >.ERWIN_ENTITY;



< owner name>.ERWIN_RELATIONSHIP;



< owner name >.ERWIN_EAU;



< owner name >.INDEX_MEMBER;



< owner name >.DOMAIN;



< owner name >.DOMAIN_PROP_VALUE;



< owner name >.ENT_PHYSICAL_PV;



< owner name >.ERWIN_DEFAULT;



< owner name >.ENTITY_SYNONYM,

where < owner name > is the name os the user on whose behalf the ewc converter is running.



6

The ewc converter uses the above-specified names to name its work tables, so when starting ERwin, the tables with such names will be deleted from the database, and new ones will be created instead of them.

Data Model Converter

Data Model Conversion To start the converter, submit the following command line to the OS command interpreter: ewc

Command Line Syntax ewc -u /< password> [-n ] [] [] [] [] [] [] [] [ ] [ ] [] [] < input file> [] ::=database user registration name ::=database user registration password ::=-ci ::= -{h | ?} : : = - v ::= .erx file specification ::= output file specification ::=codepage name

Convert Table Synonyms Syntax < table synonym conversion rules> : : = - A [ < synonym type> ] [ ] < synonym type > : : = < create PRIVATE synonym> | < create PRIVATE synonym > : : = y < create PUBLIC synonym > : : = n < owner >::= owner ID Default  -A corresponds to -Ay (PRIVATE synonyms for all owners are created);  if < owner > is specified, < synonym type > should be expressly set.

Data Model Converter

7

Data Model Conversion General Rules 1) Synonym creation SQL statements are generated only if table creation is allowed (-Ty option) and –A option is specified. 2) The generated SQL statement looks as follows: CREATE [PUBLIC] SYNONYM < synonym name> FOR TABLE [< table owner> . ] < table name>; The corresponding name from the .erx input file is used as . Examples 1) Create a PRIVATE synonym for all database object owners. -A 2) Create a PUBLIC synonym for all database object owners. -An 3) Create a PRIVATE synonym for all database object owners. -Ay 4) Create a PUBLIC synonym only for those database objects whose owner is Petrov. -An"Petrov" 5) Create a private PRIVATE synonym only for those database objects whose owner is yaun. -Aуyaun

Convert Table Syntax < table conversion rules>::= -T {} {< delete table?>} < create table?>::= y | n < delete table?>::= y | n Default  -T corresponds to -Tyn (include CREATE TABLE… constructions and not include DROP TABLE…);  If the table conversion option is not specified, the default is -T (i.e. table creation SQL statements are generated always, unless it is expressly forbidden). General Rules 1) In general, the generated SQL statement will look as follows: CREATE TABLE ( , …); 2) The table parameters (MAXROWID, MAXROW, PCTFILL etc.) and file parameters (INDEXFILES, DATAFILES etc.) are not generated. If necessary, these parameters can be added to SQL statements manually (by editing the output file).

8

Data Model Converter

Data Model Conversion 3) If reference integrity column conversion options are specified (see subsection «Reference Integrity Conversion»), the corresponding SQL constructions will be added to the SQL statement. Examples -T -Tyy -Tnn -Tny

Convert Table Columns Syntax < table column conversion rules> : : = - C { } {< physical column order ?> } < use default value?> : : = y | n < physical column order?> : : = y | n Default  -C corresponds to -Cyy (use default values and position columns in the table in physical order);  if the table column conversion option is not set, the default is -C. General Rules 1) To enable the -C option, table creation should be allowed. 2) If the -Cy option (use default values) is specified, the DEFAULT construction will be added to the generated table creation SQL statement for all columns with default values, otherwise the assigned default values will be ignored. 3) If the -C {y|n} n option (not use physical column order) is specified, the columns will be positioned according to their sequence numbers assigned by ERwin. Examples 1) Not use default values, position columns in physical order.

-Cn 2) Use default values, position columns in logical order.

-Cyn

Convert Reference Integrity Syntax ::= -R { } { } { } < create primary keys?>::= y | n < create foreign keys ?>::= y | n < create alternative keys?>::= y | n

Data Model Converter

9

Data Model Conversion Default  -R corresponds to -Ryyn (create only primary and foreign keys);  if the reference integrity conversion option is not specified, the default is -R. General Rules 1) This option defines only the necessity to support reference integrity using a set of keys. The key generation mechanism (i.e. which SQL statement (CREATE TABLE… or ALTER TABLE…) the corresponding constructions should be generated to) is specified by the -S option (see subsection «Convert Primary and Foreign Keys»). Therefore, to use the -R option, the -S option parameters should be agreed in order to allow table creation or modification (see -T option in subsection «Convert Table»). 2) To create foreign keys, primary key creation should be allowed. 3) Primary keys may be simple or compound. Examples 1) Do not create any keys.

-Rnnn 2) Create only foreign keys.

-Rnyn

Convert Primary and Foreign Keys Syntax < primary and foreign key conversion rules>::= -S{ | < foreign key>} < primary key >::= PC|PA < foreign key >::= FC|FA PC – create primary key in CREATE TABLE statement PA –create primary key in ALTER TABLE statement FC – create foreign key in CREATE TABLE statement



The parameter is not implemented in the current converter version. FA – create foreign key in ALTER TABLE statement

Default  -S corresponds to -SPCFA (include the primary key creation construction in the CREATE TABLE… statement and the foreign key creation construction in the ALTER TABLE… statement);  if the primary and foreign key conversion option is not specified, the default is -S. General Rules 1) If primary key creation is specified, the generated SQL statement CREATE TABLE… or ALTER TABLE… will contain the PRIMARY KEY construction for the column that is a simple primary key or PRIMARY KEY (, < column name >[,…]) for a compound primary key.

10

Data Model Converter

Data Model Conversion 2) If foreign key creation is specified, the generated ALTER TABLE… SQL statement will contain the following construction: FOREIGN KEY (< c o l u m n n a m e >) { R E F E R E N C E S < r e f e r e n c e t o t a b l e > (< r e f e r e n c e t o c o l u m n >) [ O N U P D AT E { CASCADE | SET NULL | S E T D E FA U LT | { NO ACTION | RESTRICT} } ] [ON DELETE { CASCADE | SET NULL | S E T D E FA U LT | { NO ACTION | RESTRICT } } ] 3) If alternative key creation is specified, the generated CREATE TABLE…statement will contain a UNIQUE construction for the corresponding column.



Unlike PRIMARY KEY a column with the UNIQUE attribute allows NULL values.

Convert Indexes Syntax ::= -I { {< create indexes for foreign key?> {< create indexes for alternative key?>} < create indexes for primary key?>::= y | n < create indexes for foreign key?>::= y | n < create indexes for alternative key?>::= y | n Default  -I corresponds to -Iyyn (create indexes only for primary and foreign keys);  if the index conversion option is not specified, the default is -I. General Rules 1) This option applies to simple and aggregative indexes. 2) For a simple index, the following construction is generated: CREATE INDEX ON ; For an aggregative index, the following construction is generated: CREATE INDEX ON < table name> ; In both cases, the INDEXFILE parameter is not generated. To specify it, edit the output file manually. Examples 1) Do not create any indexes.

Data Model Converter

11

Data Model Conversion -Innn 2) Create indexes only for foreign keys.

-Inyn

Convert Database Object Names Syntax < database object name conversion rules> : : = - O {< in quotes ?> } < in quotes ?>: : = y | n Default  -O corresponds to -On (do not use quotes);  if the database object name conversion option is not specified, the default is -O. General Rules According to the rules of the DBMS Linter SQL language, all database object names are converted to the upper case of the Latin alphabet. In order not to convert a database object name, put it in double quotes. Examples Table 2 contains examples of using options when converting database object names. Table 2. Using Options When Converting Database Object Names Object name TEST Test Test Банк Банк

Option

Value in the output file

-O -O -Oy -On -Oy

TEST TEST "Test" ::= -B The type correspondence table sets the correspondence between the data types supported by ERwin and DBMS Linter. It should be a text file with strings of the following structure: < source data type>#< target data type> < source data type > : : = < delimiter>< ERwin data type >< delimiter> < target data type > : : = < delimiter >< DBMS Linter data type >< delimiter > < delimiter >::= { space character | tab character} [] < ERwin data type > – name of the data type supported by the ERwin converter. < DBMS Linter data type > – name of the data type supported by DBMS Litner and corresponding to < ERwin data type >.

12

Data Model Converter

Data Model Conversion Table Syntax Rules 1) The row length should not exceed 512 characters. 2) The name may consist of several words. 3) The words in the name may be separated by any number of spaces. 4) The words in the name may be in the upper or lower case. 5) The type name length should not exceed 80 characters. 6) If a row contains a «!» character in any position, it will be treated as a comment and skipped. 7) Empty rows are skipped. 8) The table should not contain over 64 type descriptions.



If the correspondence table contains over 64 rows, the ewc converter will return the warning: «Type correspondence file contains over 64 type descriptions» and use only the first 64 rows, ignoring the rest.

Sample Table Rows :! this is a comment this is a comment, too ! this ! is a comment, too !!!

LONG VARCHAR LONG BINARY VARCHAR() VARCHAR



# # # #

BLOB BLOB CHAR CHAR

The () and < type name > data types (e.g. VARCHAR() and VARCHAR) are considered different. Brackets are not allowed after a DBMS Linter type name. The type will be considered nonexistent.

Data Model Converter

13

Converter Messages Diagnostic and information converter message texts are listed in Table 3. Table 3. ewc Converter Messages Cause Note Message text Input file opening error No input file, or file system error Output file creation The output file cannot be generated error Internal error Converter internal structure error Contact the developer Not enough memory Not enough resources to allocate the required memory space Input file read error File system error Output file write error No space on the disk, or file system error Positioning error in the Contact the developer file Row buffer overflow Contact the developer ERwin.ERX version is not ERwin.ERX version is not 2.x 2.x The .erx is damaged, or The input file is not .erx no .erx format Linter error DBMS Linter error Contact the developer Logical error in Contact the developer internal tables Database type not See subsection «Convert supported Data Types» Type correspondence file No type correspondence file at the opening error specified path Type correspondence file See subsection «Convert contains over 64 type Data Types» descriptions Incorrect type See subsection «Convert correspondence file Data Types» format Output file creation error Cannot create output file File system error

14

Data Model Converter

Readers Questionnaire The purpose of this questionnaire is gathering information to create documentation that fully meets the requirements of our clients. Please fill it in and send it to us in one of the following ways:  by mail: 119 20-letia Oktyabrya St., Voronezh, 394006, Russia  by fax:+7 (4732) 711–711, 778–333.  by e-mail: [email protected]. Data Model Converter Question

Was the information full enough? Was it easy to find the needed information? Was the information easy to understand?

Circle the Right Answer Yes / No Yes / No Yes / No

What did you particularly like (dislike) about the document structure, style, design and the way of representing the information?

Name: Position: Company: Address: Phone: e-mail:

Notes Write your comments for the RELEX staff here.