6.6.0.15.3:Modifying the schema document

A schema document is created during the top-down mapping generation done by the ejbdeploy tool. The schema document contains descriptions of tables and their columns. After completing the top-down operation, the resulting schema may need to be modified to fit existing data or models (meet-in-the-middle mapping). This topic discusses that process.

XMI vocabulary: Schema.rdbxmi

This topic discusses the structure and vocabulary of an XMI schema document. The following is a sample XMI code block from the 
schema.rdbxmi file that describes the BankAccount table, all its columns and constraints (Primary Key).

 <xmi:XMI xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI" xmlns:RDBSchema="RDBSchema.xmi">
<RDBSchema:RDBDatabase xmi:id="TopDownDB_ID" name="TopDownDB" schemata="USERID_ID" tableGroup="BankAccount">
<dataTypeSet href="UDBV7_Primitives.xmi#SQLPrimitives_1"/>
</RDBSchema:RDBDatabase>
<RDBSchema:RDBSchema xmi:id="USERID_ID" name="USERID" database="TopDownDB_ID" tables="BankAccount"/>
<RDBSchema:RDBTable xmi:id="BankAccount" name="BankAccount" primaryKey="SQLReference_1" database="TopDownDB_ID"
schema="USERID_ID">
<namedGroup xsi:type="RDBSchema:SQLReference" xmi:id="SQLReference_1" members="RDBColumn_1"
table="BankAccount" constraint="Constraint_BankAccountPK"/>
<constraints xmi:id="Constraint_BankAccountPK" name="BankAccountPK" type="PRIMARYKEY" primaryKey="SQLReference_1"/>
<columns xmi:id="RDBColumn_1" name="accountNum" allowNull="false" group="SQLReference_1">
<type xsi:type="RDBSchema:SQLExactNumeric" xmi:id="SQLExactNumeric_1">
<originatingType xsi:type="RDBSchema:SQLExactNumeric" href="UDBV7_Primitives.xmi#SQLExactNumeric_1"/>
</type>
</columns>
<columns xmi:id="RDBColumn_2" name="customerName">
<type xsi:type="RDBSchema:SQLCharacterStringType" xmi:id="SQLCharacterStringType_1" characterSet="800" length="32">
<originatingType xsi:type="RDBSchema:SQLCharacterStringType" href="UDBV7_Primitives.xmi#SQLCharacterStringType_3"/>
</type>
</columns>
<columns xmi:id="RDBColumn_3" name="balance">
<type xsi:type="RDBSchema:SQLNumeric" xmi:id="SQLNumeric_1" precision="5" scale="0">
<originatingType xsi:type="RDBSchema:SQLNumeric" href="UDBV7_Primitives.xmi#SQLNumeric_3"/>
</type>
</columns>
</RDBSchema:RDBTable>
</xmi:XMI>

XML element descriptions

RDBSchema:RDBDatabase - defines the name and id of the database.

RDBSchema:RDDTable - defines the name and id of the table.

RDBSchema:SQLConstraint - defines the constraints, this sample only includes the primary key constraint. The constraint points to another object that describes the type of constraint. In this case, the SQLReference "BankAccountPK" describes the primary key, and what members(columns) are included.

RDBSchema:RDBColumn  - defines the column type by referencing a type instance in a sqlprimitives document. The primitives documents are located in the /plugins/sqlmodel/runtime/primitives directory.

Changing type definitions

If you need to change between two closely-related types, such as, VARCHAR to LONGVARCHAR, you only need to change the type id in the href. A column might looks like this:

<columns xmi:id="RDBColumn_52" name="location">
  <type xsi:type="RDBSchema:SQLCharacterStringType" xmi:id="SQLCharacterStringType_20" characterSet="800" length="1">
     <originatingType xsi:type="RDBSchema:SQLCharacterStringType" 
     href="ORACLEV8i_Primitives.xmi#SQLCharacterStringType_2"/> 
  </type> 
</columns>

In this case, the href is referring to the following type in ORACLEV8i_Primitives.xmi:

<types xsi:type="RDBSchema:SQLCharacterStringType"
  xmi:id="SQLCharacterStringType_2" externalName="CHARACTER VARYING" name="VARCHAR2" 
  dbcEnumType="12 domain="ORACLE_V8" requiredUniqueInstance="true" renderedString="VARCHAR2" 
  typeEnum="CHARACTERVARYING" formatterClassName="com.ibm.etools.rdbschema.formatter.oracle.CharacterTextFormatter" 
  characterSet="800" length="1"/>

If you just need to change the length, you can do so by directly changing the length (seen in red in the code snippet above) on the type object. If you want to switch to one of the other character types, you can change the type reference (seen in red in the code snippet above) to one of the other similar types in the primitives doc. For example, SQLCharacterStringType_1 to SQLCharacterStringType_6. As long as you are switching between closely-related types, you do not need to change the syntax of the <type>.

For numerics, the same thing will apply. You should only have to modify the precision and scale. However, you can get the more specific numeric type you want by switching between the SQLNUMERIC_ types.

Adding table columns

Adding a new column to the table is easy if you use the following steps:

  1. Add the new column under the columns tag. Make sure the order of the column in the list corresponds to the order in the database.
 <columns xmi:id="RDBColumn_1" name="accountNum" allowNull="false" group="SQLReference_1">

  2.   Add the column type.

<type xsi:type="RDBSchema:SQLExactNumeric" xmi:id="SQLExactNumeric_1">
<originatingType xsi:type="RDBSchema:SQLExactNumeric" href="UDBV7_Primitives.xmi#SQLExactNumeric_1"/>
</type>

Make sure all of the id's chosen are unique in the entire XMI document.

More examples of type definitions

The following type definition examples were taken from UDBV6_Primitives.xmi. This and other type definition example files can be found in the following location: X:\..\eclipse\plugins\sqlmodel\runtime\primitives\

CHARACTER FOR BIT DATA

<types xmi:type="RDBSchema:SQLBinaryLargeObject" xmi:id="SQLBinaryLargeObject_3" externalName="BINARY LARGE OBJECT" name="CHARACTER () FOR BIT DATA" jdbcEnumType="-2" domain="DB2UDBNT_V61" requiredUniqueInstance="true" renderedString="CHARACTER" typeEnum="BINARYLARGEOBJECT" formatterClassName="com.ibm.etools.rdbschemagen.formatter.db2nt.BinaryTextFormatter" length="1"/>
VARCHAR FOR BIT DATA


<types xsi:type="RDBSchema:SQLBinaryLargeObject" xmi:id="SQLBinaryLargeObject_5" externalName="BINARY LARGE OBJECT" name="VARCHAR () FOR BIT DATA" jdbcEnumType="-3" domain="DB2UDBNT_V61" requiredUniqueInstance="true" renderedString="VARCHAR" typeEnum="BINARYLARGEOBJECT" formatterClassName="com.ibm.etools.rdbschema.formatter.db2nt.BinaryTextFormatter" length="1"/>

SMALLINT


<types xsi:type="RDBSchema:SQLExactNumeric" xmi:id="SQLExactNumeric_4" externalName="SMALLINT" name="SMALLINT" jdbcEnumType="5" domain="DB2UDBNT_V61" requiredUniqueInstance="false" renderedString="SMALLINT" typeEnum="SMALLINT" formatterClassName="com.ibm.etools.rdbschema.formatter.db2nt.SimpleTextFormatter"/>

FLOAT


<types xsi:type="RDBSchema:SQLApproximateNumeric" xmi:id="SQLApproximateNumeric_4" externalName="DOUBLE PRECISION" name="FLOAT" jdbcEnumType="8" domain="DB2UDBNT_V61" requiredUniqueInstance="false" renderedString="DOUBLE" typeEnum="DOUBLEPRECISION" formatterClassName="com.ibm.etools.rdbschema.formatter.db2nt.SimpleTextFormatter"/>

DOUBLE


<types xsi:type="RDBSchema:SQLApproximateNumeric" xmi:id="SQLApproximateNumeric_2" externalName="DOUBLE PRECISION" name="DOUBLE" jdbcEnumType="8" domain="DB2UDBNT_V61" requiredUniqueInstance="false" renderedString="DOUBLE" typeEnum="DOUBLEPRECISION" formatterClassName="com.ibm.etools.rdbschema.formatter.db2nt.SimpleTextFormatter"/>

LONG VARCHAR FOR BIT DATAoka


<types xsi:type="RDBSchema:SQLBinaryLargeObject" xmi:id="SQLBinaryLargeObject_8" externalName="BINARY LARGE OBJECT" name="LONG VARCHAR FOR BIT DATA" jdbcEnumType="-4" domain="DB2UDBNT_V61" requiredUniqueInstance="false" renderedString="LONG VARCHAR" typeEnum="BINARYLARGEOBJECT" formatterClassName="com.ibm.etools.rdbschema.formatter.db2nt.BinaryTextFormatter" length="0"/>

DATE


<types xsi:type="RDBSchema:SQLDate" xmi:id="SQLDate_1" externalName="DATE" name="DATE" jdbcEnumType="91" domain="DB2UDBNT_V61" requiredUniqueInstance="false" renderedString="DATE" typeEnum="DATE" formatterClassName="com.ibm.etools.rdbschema.formatter.db2nt.SimpleTextFormatter"/>

SMALLINT


<types xsi:type="RDBSchema:SQLExactNumeric" xmi:id="SQLExactNumeric_4" externalName="SMALLINT" name="SMALLINT" jdbcEnumType="5" domain="DB2UDBNT_V61" requiredUniqueInstance="false" renderedString="SMALLINT" typeEnum="SMALLINT" formatterClassName="com.ibm.etools.rdbschema.formatter.db2nt.SimpleTextFormatter"/>

DECIMAL (20,2)


<types xsi:type="RDBSchema:SQLNumeric" xmi:id="SQLNumeric_1" externalName="NUMERIC" name="DECIMAL" jdbcEnumType="2" domain="DB2UDBNT_V61" requiredUniqueInstance="true" renderedString="NUMERIC" typeEnum="NUMERIC" formatterClassName="com.ibm.etools.rdbschema.formatter.db2nt.NumericTextFormatter" precision="5" scale="0"/>

REAL


<types xmi:type="RDBSchema:SQLApproximateNumeric" xmi:id="SQLApproximateNumeric_1" externalName="REAL" name="REAL" jdbcEnumType="7" domain="DB2UDBNT_V61" requiredUniqueInstance="false" renderedString="REAL" typeEnum="REAL" formatterClassName="com.ibm.etools.rdbschemagen.formatter.db2nt.SimpleTextFormatter"/>

INTEGER


<types xsi:type="RDBSchema:SQLExactNumeric" xmi:id="SQLExactNumeric_1" externalName="INTEGER" name="INTEGER" jdbcEnumType="4" domain="DB2UDBNT_V61" requiredUniqueInstance="false" renderedString="INTEGER" typeEnum="INTEGER" formatterClassName="com.ibm.etools.rdbschema.formatter.db2nt.SimpleTextFormatter"/>

BIGINT


<types xsi:type="RDBSchema:SQLExactNumeric" xmi:id="SQLExactNumeric_3" externalName="INTEGER" name="BIGINT" jdbcEnumType="-5" domain="DB2UDBNT_V61" requiredUniqueInstance="false" renderedString="BIGINT" typeEnum="INTEGER" formatterClassName="com.ibm.etools.rdbschema.formatter.db2nt.SimpleTextFormatter"/>

TIME


<types xsi:type="RDBSchema:SQLTime" xmi:id="SQLTime_1" externalName="TIME" name="TIME" jdbcEnumType="92" domain="DB2UDBNT_V61" requiredUniqueInstance="true" renderedString="TIME" typeEnum="TIME" formatterClassName="com.ibm.etools.rdbschema.formatter.db2nt.SimpleTextFormatter" precision="2" timezone="false"/>

TIMESTAMP


<types xsi:type="RDBSchema:SQLExactNumeric" xmi:id="SQLExactNumeric_1" externalName="INTEGER" name="INTEGER" jdbcEnumType="4" domain="DB2UDBNT_V61" requiredUniqueInstance="false" renderedString="INTEGER" typeEnum="INTEGER" formatterClassName="com.ibm.etools.rdbschema.formatter.db2nt.SimpleTextFormatter"/>

BIGINT


<types xsi:type="RDBSchema:SQLExactNumeric" xmi:id="SQLExactNumeric_3" externalName="INTEGER" name="BIGINT" jdbcEnumType="-5" domain="DB2UDBNT_V61" requiredUniqueInstance="false" renderedString="BIGINT" typeEnum="INTEGER" formatterClassName="com.ibm.etools.rdbschema.formatter.db2nt.SimpleTextFormatter"/>

CLOB


<types xmi:type="RDBSchema:SQLCharacterLargeObject" xmi:id="SQLCharacterLargeObject_1" externalName="CHARACTER LARGE OBJECT" name="CLOB" jdbcEnumType="2005" domain="DB2UDBNT_V61" requiredUniqueInstance="true" renderedString="CLOB" typeEnum="CHARACTERLARGEOBJECT" formatterClassName="com.ibm.etools.rdbschemagen.formatter.db2nt.LargeObjectTextFormatter" characterSet="800" length="1" multiplier="K"/>

BLOB


<types xsi:type="RDBSchema:SQLBinaryLargeObject" xmi:id="SQLBinaryLargeObject_1" externalName="BINARY LARGE OBJECT" name="BLOB" jdbcEnumType="2004" domain="DB2UDBNT_V61" requiredUniqueInstance="true" renderedString="BLOB" typeEnum="BINARYLARGEOBJECT" formatterClassName="com.ibm.etools.rdbschema.formatter.db2nt.LargeObjectTextFormatter" length="1" multiplier="K"/>

Go to previous article: Meet-in-the-middle mapping support Go to next article: Modifying the map

 

 
Go to previous article: Meet-in-the-middle mapping support Go to next article: Modifying the map