Differences

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

Link to this comparison view

integration:rawdata:fixed:example [2016/06/28 22:38] (current)
Line 1: Line 1:
 +~~NOTOC~~
  
 +{{page>:​top_add&​nofooter&​noeditbtn}}
 +
 +====== Integration:​ Raw Data Files ======
 +
 +===== Fixed-length Files =====
 +
 +[[integration:​rawdata:​fixed:​home|Overview]] |
 +[[integration:​rawdata:​fixed:​environments|Environments]] |
 +[[integration:​rawdata:​fixed:​datatypes|Datatypes]] |
 +[[integration:​rawdata:​fixed:​export|Exporting]] |
 +[[integration:​rawdata:​fixed:​limitations|Limitations]] |
 +**[[integration:​rawdata:​fixed:​example|Example]]**
 +
 +----
 +
 +==== Example ====
 +
 +The following example is a simple database using fixed-length files. ​ 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:​fixed:​fixed_example.zip|Download ZIP File}}**.
 +
 +The following statements will create and build an Omnidex Environment containing fixed-length files:
 +
 +<​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"​
 +>> ​ physical ​            "​dat/​cnt.dat"​
 +>> ​ (
 +>> ​  "​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"​)
 +>> ​ )
 +>> ​ in                   "​simple.xml";​
 +Table COUNTRIES created in simple.xml
 +-> ;
 +-> ;
 +-> create table          "​STATES"​
 +>> ​ physical ​            "​dat/​sta.dat"​
 +>> ​ (
 +>> ​  "​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"​
 +>> ​ )
 +>> ​ in                   "​simple.xml";​
 +Table STATES created in simple.xml
 +-> ;
 +-> ;
 +-> create table          "​GENDERS"​
 +>> ​ physical ​            "​dat/​gdr.dat"​
 +>> ​ (
 +>> ​  "​GENDER" ​           CHARACTER(1) ​     omnidex,
 +>> ​  "​DESCRIPTION" ​      ​STRING(31) ​       quicktext,
 +>> ​  ​constraint GENDERS_GENDER_PK primary ("​GENDER"​)
 +>> ​ )
 +>> ​ in                   "​simple.xml";​
 +Table GENDERS created in simple.xml
 +-> ;
 +-> ;
 +-> create table          "​HOUSEHOLDS"​
 +>> ​ physical ​            "​dat/​households.dat"​
 +>> ​ (
 +>> ​  "​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"​
 +>> ​ physical ​            "​dat/​individuals.dat"​
 +>> ​ (
 +>> ​  "​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 "HOUSE
 +HOLDS",​
 +>> ​  ​constraint INDIVIDUALS_GENDER_FK foreign ("​GENDER"​) references "​GENDERS",​
 +>> ​ )
 +>> ​ in                   "​simple.xml";​
 +Table INDIVIDUALS created in simple.xml
 +-> ;
 +-> connect simple.xml
 +Connected to D:​\class\lab2\simple.xml
 +->
 +-> ; Update the Omnidex indexes
 +-> update indexes;
 +Updated indexes for COUNTRIES (239 rows, 0.016 cpu, 0.484 elapsed)
 +Updated indexes for STATES (76 rows, 0.016 cpu, 0.031 elapsed)
 +Updated indexes for GENDERS (2 rows, 0.000 cpu, 0.032 elapsed)
 +Updated indexes for HOUSEHOLDS (1,909 rows, 0.016 cpu, 0.093 elapsed)
 +Updated indexes for INDIVIDUALS (5,000 rows, 0.016 cpu, 0.313 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.031 cpu, 0.031 elapsed)
 +Updated statistics for GENDERS (2 rows, 0.016 cpu, 0.016 elapsed)
 +Updated statistics for HOUSEHOLDS (1,909 rows, 0.109 cpu, 0.110 elapsed)
 +Updated statistics for INDIVIDUALS (5,000 rows, 0.109 cpu, 0.109 elapsed)
 +Statistics updated for environment USER_ENVIRONMENT
 +-> ;
 +-> ; Update the text database to enable PowerSearch;​
 +-> update text;
 +Updated text for COUNTRIES (0.016 cpu, 0.031 elapsed)
 +Updated text for STATES (0.000 cpu, 0.000 elapsed)
 +Updated text for GENDERS (0.000 cpu, 0.000 elapsed)
 +Updated text for HOUSEHOLDS (0.063 cpu, 0.078 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 fixed-length files from another Omnidex environment using EXPORT statements:
 +
 +<​code>​
 +connect to    simple.xml
 +
 +export ​       (select ​       *
 +                 ​from ​       "​COUNTRIES"​)
 +  to          "​dat/​cnt.dat"​
 +  with        DELETE;
 +export ​       (select ​       *
 +                 ​from ​       "​STATES"​)
 +  to          "​dat/​sta.dat"​
 +  with        DELETE;
 +export ​       (select ​       *
 +                 ​from ​       "​GENDERS"​)
 +  to          "​dat/​gdr.dat"​
 +  with        DELETE;
 +export ​       (select ​       *
 +                 ​from ​       "​HOUSEHOLDS"​)
 +  to          "​dat/​households.dat"​
 +  with        DELETE;
 +export ​       (select ​       *
 +                 ​from ​       "​INDIVIDUALS"​)
 +  to          "​dat/​individuals.dat"​
 +  with        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/fixed/example.txt ยท Last modified: 2016/06/28 22:38 (external edit)