This shows you the differences between two versions of the page.
— |
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}} |