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 a new column to the table is easy if you use the following
steps:
- 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.
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"/>
|
|