Integration: Relational Databases

SQL Server

Tables

The CREATE TABLE Statement

The CREATE TABLE statement is used to declare a SQL Server table or view within an Omnidex Environment File. The Omnidex Environment File will contain a declaration for each SQL Server table or view to be accessed, and will correlate all of the schema information between Omnidex and SQL Server, including object names and datatypes. This statement can either be issued directly, or it can be extracted from SQL Server using the EXTRACT statement as discussed in the previous section.

The following example compares the SQL Server declarations with the Omnidex declarations.

SQL Server CREATE TABLE Statement

create table STATES
       (STATE_CD                CHAR(2),
        DESCRIPTION             VARCHAR(31),
        STATE_NUM               CHAR(2),
        REGION_CD               CHAR(2),
        COUNTRY_CD              CHAR(2),
        TAX_RATE                FLOAT);

Omnidex CREATE TABLE Statement

create table          "STATES"
 physical             "STATES"
 (
  "STATE"             CHARACTER(2)   physical "STATE_CD",
  "DESCRIPTION"       STRING(31),
  "STATE_CODE"        CHARACTER(2)   physical "STATE_NUM",
  "REGION"            CHARACTER(2)   physical "REGION_CD",
  "COUNTRY"           CHARACTER(2)   physical "COUNTRY_CD",
  "TAX_RATE"          FLOAT
 )
 in                   "simple.xml";

Using the Table and Column PHYSICAL Clauses

In the Omnidex declaration, note that the optional PHYSICAL clause for the table allows Omnidex to have a different table name than SQL Server. This is most commonly used when administrators wish a different name in the Omnidex environment or need to shorten a table name to meet the 32-character limit in Omnidex. If the PHYSICAL clause is not present, Omnidex assumes that the table names are the same between Omnidex and SQL Server.

Column declarations can similarly use a PHYSICAL clause to allow Omnidex to have a different column name than SQL Server.

Modifying Omnidex's View of the SQL Server Data Objects

When Omnidex accesses a table, it only knows about the data objects that are declared in the Omnidex Environment File. It does not have an independent understanding of the SQL Server environment. This allows administrators to shape the Omnidex Environment the way they want. Some of the opportunities this provides are:

Omnidex can have a controlled view of the SQL Server database, limited to only the tables that the application requires. Similarly, administrators can choose to include only the columns for each table that the application requires.
Omnidex tables can point to SQL Server views, allowing an easy approach to reshaping the application's view of the data.
Omnidex can use different names for tables and columns by using the PHYSICAL clause in the TABLE or COLUMN sections of the CREATE TABLE statement to map to the underlying SQL Server names.
Omnidex tables can include Expression-based Columns, which are columns derived from a SQL expression rather than a specific column in the underlying database.

Referencing SQL Server Views

Omnidex table declarations can reference SQL Server Views. Omnidex will retrieve from the SQL Server view just as though it is a table. SQL Server Views can be used to provide different views of the data, appropriate for the application.

When referencing a SQL Server view, it is necessary to declare a UNIQUE constraint. This requirement for the UNIQUE constraint is independent of any PRIMARY or FOREIGN constraints, discussed in the next page. The UNIQUE constraint tells Omnidex how to uniquely identify a row. When Omnidex retrieves individual rows, it will use this unique value; therefore, it is important that access to this column (or columns) be properly indexed in the underlying table.

SQL Server CREATE VIEW Statement

create view STATES_VIEW 
 (
   CTRY_DESCRIPTION,
   CTRY_CAPITAL,
   STATE_CD,
   DESCRIPTION,
   STATE_NUM,
   REGION_CD,
   COUNTRY_CD,
   TAX_RATE
 ) as 
   select C.DESCRIPTION,
          C.CAPITAL,
          S.STATE_CD,
          S.DESCRIPTION,
          S.STATE_NUM,
          S.REGION_CD,
          S.COUNTRY_CD,
          S.TAX_RATE
   from   SIMPLE.STATES S, SIMPLE.COUNTRIES C
   where  S.COUNTRY_CD = C.COUNTRY_CD;