This is an old revision of the document!
Overview | Environments | Databases | Tables | Constraints | Datatypes | Queries | Updates | Example
The CREATE TABLE statement is used to declare a table or view within an Omnidex Environment File to be accessed using ODBC. The Omnidex Environment File will contain a declaration for each table or view to be accessed, and will correlate all of the schema information between Omnidex and ODBC, including object names and datatypes. This statement can either be issued directly, or it can be extracted from the database through ODBC using the EXTRACT statement as discussed in the previous section.
The following example compares the ODBC declarations with the Omnidex declarations.
ODBC 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 DOUBLE);
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";
In the Omnidex declaration, note that the PHYSICAL clause for table points to the actual table name within the database. Also note that the columns use the PHYSICAL clause to point to the underlying column name, and use Omnidex datatypes rather than ODBC datatypes.
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 ODBC environment. This allows administrators to shape the Omnidex Environment the way they want. Some of the opportunities this provides are:
Omnidex table declarations can reference views in the underlying database. Omnidex will retrieve from the view just as though it is a table. Views can be used to provide different views of the data, appropriate for the application.
When referencing a 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.
ODBC 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;
Omnidex CREATE TABLE Statement
create table "STATES_VIEW" physical "STATES_VIEW" ( "CTRY_DESCRIPTION" STRING(47), "CTRY_CAPITAL" STRING(31), "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, constraint STATES_STATE_UK unique ("STATE") ) in "simple.xml";
See also: