Differences

This shows you the differences between two versions of the page.

Link to this comparison view

integration:rawdata:ost:example [2016/06/28 22:38] (current)
Line 1: Line 1:
 +~~NOTOC~~
  
 +{{page>:top_add&nofooter&noeditbtn}}
 +
 +====== Integration: Raw Data Files ======
 +
 +===== Omnidex Standalone Tables =====
 +
 +[[integration:rawdata:ost:home|Overview]] |
 +[[integration:rawdata:ost:environments|Environments]] |
 +[[integration:rawdata:ost:options|Options]] |
 +[[integration:rawdata:ost:datatypes|Datatypes]] |
 +[[integration:rawdata:ost:ofx|Offset Indexes]] |
 +[[integration:rawdata:ost:export|Exporting]] |
 +[[integration:rawdata:ost:limitations|Limitations]] |
 +**[[integration:rawdata:ost:example|Example]]**
 +
 +----
 +
 +==== Example ====
 +
 +The following example is a simple database using Omnidex Standalone Tables.  The Omnidex Environment File is displayed below; however, the entire example, including sample data files, can be downloaded as a ZIP file at the following link:  **{{:integration:rawdata:ost:ost_example.zip|Download ZIP File}}**.
 +
 +The following statements will create and build an Omnidex Environment containing Omnidex Standalone Tables:
 +
 +<code>
 +> use sql/create_all.sql
 +-> use sql/simple.sql
 +-> create environment
 +>>  in                   "simple.xml"
 +>>  with                 delete;
 +Environment created in simple.xml
 +-> ;
 +-> ;
 +-> create database       "SIMPLE"
 +>>   type                FILE
 +>>   index_directory     "idx"
 +>>  in                   "simple.xml";
 +Database SIMPLE created in simple.xml
 +-> ;
 +-> ;
 +-> create table          "COUNTRIES"
 +>>  options              "OST"
 +>>  physical             "dat/countries.ost"
 +>>  (
 +>>   "COUNTRY"           CHARACTER(2)      omnidex,
 +>>   "DESCRIPTION"       STRING(47)        quicktext,
 +>>   "LATITUDE"          FLOAT             omnidex      usage "LATITUDE",
 +>>   "LONGITUDE"         FLOAT             omnidex      usage "LONGITUDE",
 +>>   "CAPITAL"           STRING(31)        quicktext,
 +>>   "CAPITAL_LAT"       FLOAT             omnidex      usage "LATITUDE",
 +>>   "CAPITAL_LONG"      FLOAT             omnidex      usage "LONGITUDE",
 +>>   constraint COUNTRIES_COUNTRY_PK primary ("COUNTRY"),
 +>>   native COUNTRY_PK ("COUNTRY")
 +>>  )
 +>>  in                   "simple.xml";
 +Table COUNTRIES created in simple.xml
 +-> ;
 +-> ;
 +-> create table          "STATES"
 +>>  options              "OST"
 +>>  physical             "dat/states.ost"
 +>>  (
 +>>   "STATE"             CHARACTER(2)      omnidex,
 +>>   "DESCRIPTION"       STRING(31)        quicktext,
 +>>   "STATE_CODE"        CHARACTER(2)      omnidex,
 +>>   "REGION"            CHARACTER(2)      omnidex,
 +>>   "COUNTRY"           CHARACTER(2)      omnidex,
 +>>   "TAX_RATE"          FLOAT             omnidex,
 +>>   constraint STATES_STATE_PK primary ("STATE"),
 +>>   constraint STATES_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES",
 +>>   native STATE_PK ("STATE")
 +>>  )
 +>>  in                   "simple.xml";
 +Table STATES created in simple.xml
 +-> ;
 +-> ;
 +-> create table          "GENDERS"
 +>>  options              "OST"
 +>>  physical             "dat/genders.ost"
 +>>  (
 +>>   "GENDER"            CHARACTER(1)      omnidex,
 +>>   "DESCRIPTION"       STRING(31)        quicktext,
 +>>   constraint GENDERS_GENDER_PK primary ("GENDER"),
 +>>   native GENDER_PK ("GENDER")
 +>>  )
 +>>  in                   "simple.xml";
 +Table GENDERS created in simple.xml
 +-> ;
 +-> ;
 +-> create table          "HOUSEHOLDS"
 +>>  options              "OST"
 +>>  physical             "dat/households.ost"
 +>>  (
 +>>   "HOUSEHOLD"         CHARACTER(12)     omnidex,
 +>>   "ADDRESS"           CHARACTER(50)     quicktext,
 +>>   "CITY"              CHARACTER(28)     quicktext,
 +>>   "STATE"             CHARACTER(2)      omnidex,
 +>>   "ZIP"               CHARACTER(5)      omnidex,
 +>>   "COUNTRY"           CHARACTER(2)      omnidex,
 +>>   constraint HOUSEHOLDS_HOUSEHOLD_PK primary ("HOUSEHOLD"),
 +>>   constraint HOUSEHOLDS_STATE_FK foreign ("STATE") references "STATES",
 +>>   constraint HOUSEHOLDS_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES"
 +
 +>>  )
 +>>  in                   "simple.xml";
 +Table HOUSEHOLDS created in simple.xml
 +-> ;
 +-> ;
 +-> create table          "INDIVIDUALS"
 +>>  options              "OST"
 +>>  physical             "dat/individuals.ost"
 +>>  (
 +>>   "INDIVIDUAL"        CHARACTER(12)     omnidex,
 +>>   "HOUSEHOLD"         CHARACTER(12)     omnidex,
 +>>   "NAME"              CHARACTER(50)     quicktext,
 +>>   "GENDER"            CHARACTER(1)      omnidex bitmap,
 +>>   "BIRTHDATE"         ANSI DATE         omnidex,
 +>>   "PHONE"             CHARACTER(14)     omnidex,
 +>>   "EMAIL"             CHARACTER(60)     quicktext,
 +>>   constraint INDIVIDUALS_INDIVIDUAL_PK primary ("INDIVIDUAL"),
 +>>   constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS",
 +>>   constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS",
 +>>  )
 +>>  in                   "simple.xml";
 +Table INDIVIDUALS created in simple.xml
 +-> ;
 +-> connect simple.xml
 +Connected to D:\class\lab2b\simple.xml
 +->
 +-> ; Update the Omnidex indexes
 +-> update indexes;
 +Updated indexes for COUNTRIES (239 rows, 0.016 cpu, 0.390 elapsed)
 +Updated indexes for STATES (76 rows, 0.016 cpu, 0.031 elapsed)
 +Updated indexes for GENDERS (2 rows, 0.000 cpu, 0.031 elapsed)
 +Updated indexes for HOUSEHOLDS (1,909 rows, 0.016 cpu, 0.110 elapsed)
 +Updated indexes for INDIVIDUALS (5,000 rows, 0.031 cpu, 0.328 elapsed)
 +Omnidex indexes updated for environment USER_ENVIRONMENT
 +-> ;
 +-> ; Update statistics to improve performance optimization;
 +-> update statistics;
 +Updated statistics for COUNTRIES (239 rows, 0.063 cpu, 0.063 elapsed)
 +Updated statistics for STATES (76 rows, 0.047 cpu, 0.047 elapsed)
 +Updated statistics for GENDERS (2 rows, 0.031 cpu, 0.031 elapsed)
 +Updated statistics for HOUSEHOLDS (1,909 rows, 0.109 cpu, 0.109 elapsed)
 +Updated statistics for INDIVIDUALS (5,000 rows, 0.125 cpu, 0.125 elapsed)
 +Statistics updated for environment USER_ENVIRONMENT
 +-> ;
 +-> ; Update the text database to enable PowerSearch;
 +-> update text;
 +Updated text for COUNTRIES (0.016 cpu, 0.047 elapsed)
 +Updated text for STATES (0.000 cpu, 0.000 elapsed)
 +Updated text for GENDERS (0.016 cpu, 0.016 elapsed)
 +Updated text for HOUSEHOLDS (0.063 cpu, 0.063 elapsed)
 +Updated text for INDIVIDUALS (0.063 cpu, 0.078 elapsed)
 +Text metadata updated for environment USER_ENVIRONMENT
 +-> ;
 +-> disconnect
 +Disconnected
 +</code>
 +
 +The ZIP file also contains the file sql/export.sql. This file gives examples of how to create Omnidex Standalone Tables from another Omnidex environment using EXPORT statements:
 +
 +<code>
 +connect to    simple.xml
 +
 +export        (select        *
 +                 from        COUNTRIES)
 +  to          dat/countries.ost
 +  with        OST INDEXBY=COUNTRY DELETE;
 +
 +export        (select        *
 +                 from        STATES)
 +  to          dat/states.ost
 +  with        OST INDEXBY=STATE DELETE;
 +
 +export        (select        *
 +                 from        GENDERS)
 +  to          dat/genders.ost
 +  with        OST INDEXBY=GENDER DELETE;
 +
 +export        (select        *
 +                 from        HOUSEHOLDS)
 +  to          dat/households.ost
 +  with        OST NULL_INDICATORS=OFF DELETE;
 +
 +export        (select        *
 +                 from        INDIVIDUALS)
 +  to          dat/individuals.ost
 +  with        OST NULL_INDICATORS=OFF DELETE;
 +
 +disconnect
 +</code>
 +
 +=====  =====
 +
 +**[[integration:rawdata:fixed:limitations|Prev]]**
 +
 +====== Additional Resources ======
 +
 +See also: 
 +
 +{{page>:integration:rawdata:see_also&nofooter&noeditbtn}}
 +
 +{{page>:bottom_add&nofooter&noeditbtn}}
 
Back to top
integration/rawdata/ost/example.txt ยท Last modified: 2016/06/28 22:38 (external edit)
 
 
chimeric.de = chi`s home Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0