Categoria: Exporting and Importing Definitions

The Import/Export functions of the Zim Development Center application provides support for the import/export and translation of database object definitions between the Zim and SQL server environments. In particular, Zim EntitySet and data relationship definitions can be exported to the SQL server to create corresponding tables and indexes. Additionally, SQL table definitions can be extracted from the server’s catalog tables, translated, and delivered into the Zim environment as members of the “Fields” EntitySet which is part of the Zim Data Dictionary.

While you are using the import or export facility of the development center, do not be explicitly connected to the SQL Server. Both facilities connect and disconnect from the SQL Server as needed.

This option is used to import table definitions from the SQL server and to translate them into equivalent Zim definitions. To import SQL definitions into Zim, select the Tools/Import/Objects/From SQL option from the development center main menu.

The SQL Definition Import window appears. On this window, choose the ODBC Server as the server from which to import. Next click the “Fetch Def’n” button.  The Server Parameters window appears. The connection parameters are identical to parameters 2 through 7 of the “CONNECT” command. The SAM uses these to access the correct database. Refer to Connecting to SQL Servers for information on how to supply the correct values for these items. (Note that while these utilities connect to the SQL database, they do not connect Zim to the database.) The “Table Owner Name” field can be used to denote the owner of the tables which Import retrieves definitions. Note that the column definitions for all tables owned by “Table Owner Name” are retrieved. If the table owner name field is left blank, Import retrieves all table definitions which are available to the user.

If the check box to the left of the table name is selected when the “Import” button is selected, that table’s column (field) definitions are imported into Zim. By default all tables are flagged for import. Use the “Deselect All” button to clear all check boxes. The “Select All” button selects all check boxes. If more than 30 table definitions are retrieved, you can scroll forwards and backwards through the table name. SQL table definitions that you select to import are added by ZOM. All tables are added as EntitySets with the enttype set to the name of the selected SAM. Imported SQL table definitions do not have any index information. The “index” field for all imported SQL fields always has the value ‘NO’. You have to modify the imported fields to indicate any indices that you wish Zim to maintain.

The export facility is used to translate Zim definitions for entity sets and/or data relationships and create them on the designated SQL server. The definitions of the resulting tables (with associated indexes) correspond to each exported Zim EntitySet or data relationship definition. The Zim definitions to be exported are selected from the EntitySets and Relationships and their associated Fields that are in your database. The selected objects to be exported are grouped according to their Remote Owner Name field in EntitySets and Relationships and a complete export cycle is accomplished for each different Remote Owner Name.

In a Zim Client-Server application, all EntitySets and data relationships should have at least one unique index defined on a field or virtual field. Limitations of SQL require that if the unique index is on a virtual field, then the field expression must be

$concat(field1,field2,…)

where “field1”, “field2” are the names of real fields only. They cannot be expressions or other virtual field names. An error occurs during the export process if an attempt is made to export an indexed virtual field whose expression does not abide by this rule. See Exporting Zim Table Definitions for more information on this subject.

To export Zim table definitions to the server, choose the Tools/Export/Objects/To SQL option from the main menu of DC. The SQL Export Definiton window appears. Choose the server to which you wish to export the definitions by selecting it from the “Export selected items to” list box. The names of the EntitySets and data relationships in your Zim database are listed. (If more than 30 table definitions exist, you can scroll forwards and backwards through the table names.) To the left of each table name is a check box. Clear the check box if you do not wish to export the definition to the server. Selecting the “Deselect All” button clears all check boxes and selecting the “Select All” button selects all boxes. Initially, all entries have their check boxes selected.

Also appearing on this window is the Replace Existing Table check box. If this box is selected, Zim attempts to DROP each table on the server before it creates it. Hence, selecting this box unconditionally replaces each existing table definition. By clearing this check box, the server generates an error if the table already exists and no new table is created. Therefore to guard against the accidental replacement of a table definition, clear this box. If the box is selected and the table does not exist, the server generates a benign error.

By clearing this check box, the server generates an error if the table already exists and no new table is created. Therefore to guard against the accidental replacement of a table definition, clear this box. If the box is selected and the table does not exist, the server generates a benign error.

The “CREATE TABLE” syntax for a specific SQL server can optionally include additional syntax which relates to the physical storage characteristics of the created table. This syntax varies widely from server to server. If the specification of some or all of the optional physical table characteristic parameters is desired, enter the appropriate syntax in the field “Physical Table Characteristics Syntax:“. The syntax specified is appended to the “CREATE TABLE” statement for each name entered in the form. For example, entering “TABLESPACE ts_account” ensures that the table is created in the “ts_account” tablespace.

Another way to change characteristics is to check the “Modify SQL Definitions” check box. This options brings the resulting SQL definitions through the default text  editor for editing. Edit the definitions you want to apply and save the text. The export then proceeds.

To export those EntitySets and/or data relationships selected, click the “Export” button. The table definitions are translated to the appropriate “CREATE TABLE” syntax for the selected SAM and the output is written to a file. Next, the server parameter dialog box appears. Values for these items are required so that the utility program “SQLEXEC” (which the DC invokes) can access the appropriate database in the SQL server in which to create the tables. SQLEXEC in turn invokes the “Server Interface Module” of the selected SAM. These items are identical to parameters 2 through 7 of the “CONNECT” command. Refer to the Connecting to SQL Servers topic for information on how to supply the correct values for these items. Although these values are used to connect to the SQL database, Zim itself does not need to be connected to the database.

With the appropriate values supplied for these items, the export process proceeds to execute a general purpose SQL statement executor program “SQLEXEC”. SQLEXEC traces the SQL statements that it is executing as well as any error conditions that are detected. It does not stop executing on an error condition. In general, the only error condition that is acceptable is an error from the “DROP TABLE” statement when the table does not already exist. All other error conditions should be investigated. A common error is attempting to create an SQL table with a name beginning with the underscore (“_”) character. This is legal in Zim, but generally not legal in SQL.

If you are developing a Zim application to run against an existing SQL database, you must create an equivalent representation of that data in Zim. Zim’s definition interface translates SQL definitions into equivalent definitions in Zim.

The mapping of SQL tables to Zim EntitySets or data relationships is straight-forward. A Zim developer must first establish a clear understanding of the characteristics of each table in the SQL database. Do this by examining the SQL statements (in particular, the join conditions of “select” statements) in existing application code to successfully identify the relationships relevant to the database.

SQL tables that exhibit the same properties as Zim EntitySets (i.e. contain data about “things” only) can be mapped directly to Zim EntitySets. Tables that appear to contain information representing interactions between other tables are candidates for mapping to either data relationships or EntitySets. In Zim, a binary data relationship “r”, defined between the EntitySets “e1” and “e2”, can be decomposed into an EntitySet called “e3” and two non-data binary relationships “r1” between “e1” and “e3” and “r2” between “e3” and “e2”. SQL statements in existing application code (in particular the join conditions of “select” statements) should be examined to identify potential non-data relationships and the relationship conditions for all relationships.

If you have developed an application in Zim and want to maintain a portion of the data in an SQL database, the definitions of those objects must be maintained in the SQL database as well as in Zim. Zim’s SQL definition interface translates Zim definitions into equivalent definitions in the SQL server environment. Refer to Importing and Exporting SQL Definitions topics. An EntitySet in the E-R model maps directly to a table in the relational model. Thus a Zim EntitySet maps to an SQL table with the same field (column) structure. Relationships, however, are explicitly represented in the E-R model and implicitly in the relational model. A Zim relationship with no fields is not represented in a relational database. Zim generates SQL join statements when the relationship is referenced. If a relationship is defined to own fields (a data relationship), then the relationship maps to a table in the SQL server environment with the same field (column) structure. The associated relationship condition is handled in the same manner as for relationships without fields – that is, it contributes to SQL join conditions in the generated SQL statement(s).