This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
dev:sql:home [2010/07/06 21:46] tdo |
dev:sql:home [2020/11/30 18:42] (current) doc |
||
---|---|---|---|
Line 1: | Line 1: | ||
{{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
- | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
~~NOTOC~~ | ~~NOTOC~~ | ||
- | ====== Omnidex SQL Quick Reference ====== | + | ====== Omnidex SQL Statement Quick Reference ====== |
- | {{page>:sql_odxsql_bar&nofooter&noeditbtn}} | + | |
- | ===== Omnidex SQL Statements and OdxSQL Commands ===== | + | |
- | ^ DML ^| [[dev:sql:statements:select:home |Select ]]| [[dev:sql:statements:insert:home |Insert ]] | [[dev:sql:statements:delete:home |Delete ]] | [[dev:sql:statements:update:home |Update ]] | [[dev:sql:statements:qualify:home |Qualify ]] | | + | [[dev:sql:home|Statements]] | [[dev:sql:functions:home|Functions]] | [[dev:sql:datatypes|Datatypes]] | [[dev:sql:examples:home|Examples]] |
- | ^ Access ^| [[dev:sql:statements:connect:home |Connect *]] | [[dev:sql:statements:disconnect:home | Disconnect *]] | [[dev:sql:statements:attach:home | Attach ]] | [[dev:sql:statements:detach:home | Detach ]] | [[dev:sql:statements:set:home | Set ]] | | + | |
- | ^ DDL Create ^| [[dev:sql:statements:create_environment:home | Create Environment ]] | [[dev:sql:statements:create_database:home | Create Database ]] | [[dev:sql:statements:create_table:syntax | Create Table ]] | [[dev:sql:statements:create_index:home | Create Index ]] | [[dev:sql:statements:create_index_group:home | Create Index Group ]] | | + | |
- | ^ DDL Drop ^| [[dev:sql:statements:drop_environment:home | Drop Environment ]] | [[dev:sql:statements:drop_database:home |Drop Datebase ]] | [[dev:sql:statements:drop_table:home | Drop Table ]] | [[dev:sql:statements:drop_index:home | Drop Index ]] | [[dev:sql:statements:drop_index_group | Drop Index Group ]] | | + | |
- | ^ Syncing ^| [[dev:sql:statements:update_indexes:home | Update Indexes ]] | [[dev:sql:statements:update_text:home | Update Text ]] | [[dev:sql:statements:update_statistics:home | Update Statistics ]] | [[dev:sql:statements:update_rollups:home | Update Rollups ]] | [[dev:sql:statements:update_rollup_table:home | Update Rollup Table ]] | | + | |
- | ^ Special ^| [[dev:sql:statements:export:home | Export ]] | [[dev:sql:statements:Partition:home | Partition ]] | [[dev:sql:statements:join:home | Join ]] | [[dev:sql:statements:open_cursor:home | Open Cursor ]] | [[dev:sql:statements:close_cursor:home | Close Cursor ]]| | + | |
- | ^ Other ^| [[dev:sql:statements:install_indexes:home | Install Indexes ]] | [[ dev:sql:statements:remove_indexes:home | Remove Indexes ]] | [[dev:sql:statements:load_ofx:home | Load OFX ]]| [[dev:sql:statements:command:home | Command ]] | [[dev:odxsql:commands:extract:home | Extract *]]| | + | |
- | * Statement is only available from OdxSQL. | + | |< 95% 50% 50% >| |
+ | ^ Omnidex SQL Statements ^^ | ||
+ | ^**Connections** ^**Data Definition** ^ | ||
+ | |[[dev:sql:statements:connect:home|CONNECT]] |[[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] | | ||
+ | |[[dev:sql:statements:disconnect:home|DISCONNECT]] |[[dev:sql:statements:create_database:home|CREATE DATABASE]] | | ||
+ | |[[dev:sql:statements:set:home|SET]] |[[dev:sql:statements:create_table:home|CREATE TABLE]] | | ||
+ | |[[dev:sql:statements:attach:home|ATTACH]] |[[dev:sql:statements:create_index:home|CREATE INDEX]] | | ||
+ | |[[dev:sql:statements:detach:home|DETACH]] |[[dev:sql:statements:create_index_group:home|CREATE INDEX GROUP]] | | ||
+ | | |[[dev:sql:statements:drop_environment:home|DROP ENVIRONMENT]] | | ||
+ | ^**Queries and Exports** |[[dev:sql:statements:drop_database:home|DROP DATABASE]] | | ||
+ | |[[dev:sql:statements:select:home|SELECT]] |[[dev:sql:statements:drop_table:home|DROP TABLE]] | | ||
+ | |[[dev:sql:statements:export:home|EXPORT]] |[[dev:sql:statements:drop_index:home|DROP INDEX]] | | ||
+ | |[[dev:sql:statements:open_cursor:home|OPEN CURSOR]] |[[dev:sql:statements:drop_index_group:home|DROP INDEX GROUP]] | | ||
+ | |[[dev:sql:statements:close_cursor:home|CLOSE CURSOR]] |[[dev:sql:statements:extract:home|EXTRACT]] | | ||
+ | |. | | | ||
+ | ^**Updates** ^**Maintenance** ^ | ||
+ | |[[dev:sql:statements:insert:home|INSERT]] |[[dev:sql:statements:update_indexes:home|UPDATE INDEXES]] | | ||
+ | |[[dev:sql:statements:delete:home|DELETE]] |[[dev:sql:statements:update_rollups:home|UPDATE ROLLUPS]] | | ||
+ | |[[dev:sql:statements:update:home|UPDATE]] |[[dev:sql:statements:update_statistics:home|UPDATE STATISTICS]] | | ||
+ | | |[[dev:sql:statements:update_text:home|UPDATE TEXT]] | | ||
+ | ^**Omnidex Index Queries** |[[dev:sql:statements:partition:home|PARTITION]] | | ||
+ | |[[dev:sql:statements:qualify:home|QUALIFY]] |[[dev:sql:statements:load_ofx:home|LOAD OFX]] | | ||
+ | |[[dev:sql:statements:join:home|JOIN]] |[[dev:sql:statements:install_indexes:home|INSTALL INDEXES]] | | ||
+ | | |[[dev:sql:statements:remove_indexes:home|REMOVE INDEXES]] | | ||
+ | \\ | ||
+ | \\ | ||
- | ===== Omnidex SQL Standard Functions ===== | + | ====== Omnidex SQL Statement Descriptions ======= |
- | | [[ dev:sql:functions:abs | ABS ]]| [[ dev:sql:functions:case | CASE ]] | [[ dev:sql:functions:cast | CAST ]] |[[ dev:sql:functions:character_length:home | CHAR[ACTER]_LENGTH ]] |[[ dev:sql:functions:coalesce | COALESCE ]] | [[ dev:sql:functions:concatenation | || (Concatenation) ]] | | + | |
- | |[[ dev:sql:functions:current_date | CURRENT_DATE ]] | [[ dev:sql:functions:current_time | CURRENT_TIME]] | [[ dev:sql:functions:current_timestamp | CURRENT_TIMESTAMP]] |[[ dev:sql:functions:current_user | CURRENT_USER]] | [[ dev:sql:functions:extract | EXTRACT]] | [[ dev:sql:functions:lower | LOWER]] | | + | |
- | | [[ dev:sql:functions:mod | MOD ]] | [[ dev:sql:functions:position | POSITION]] | [[ dev:sql:functions:power | POWER ]] | [[ dev:sql:functions:round | ROUND ]] | [[ dev:sql:statements:select:sample | SAMPLE ]] | [[ dev:sql:functions:substring | SUBSTRING]] | | + | |
- | | [[ dev:sql:functions:session_user | SESSION_USER]] | [[ dev:sql:functions:system_user | SYSTEM_USER ]] | [[ dev:sql:functions:trim | TRIM ]] | [[ dev:sql:functions:trunc | TRUNC ]] | [[ dev:sql:functions:upper | UPPER ]] | [[ dev:sql:functions:user | USER ]] | | + | |
- | ===== Omnidex Extended Functions ===== | + | Omnidex SQL Statements can be issued through any standard ODBC and JDBC connection, and through the OdxSQL program. |
- | | [[ dev:sql:functions:calc_date | $CALC_DATE ]] | [[ dev:sql:functions:col_length | $COL[UMN]_LENGTH ]] | [[ dev:sql:functions:contains:home | $CONTAINS ]] | [[ dev:sql:functions:context:home | $CONTEXT ]] | [[ dev:sql:functions:convert | $CONVERT ]] | [[ dev:sql:functions:compare_dates | $COMPARE_DATES]] | | + | |
- | |[[ dev:sql:functions:current_row | $CURRENT_ROW ]] | [[ dev:sql:functions:distance | $DISTANCE ]] | [[ dev:sql:functions:external | $EXTERNAL ]] | [[ [[ dev:sql:functions:highlight_criteria:home | $HIGHLIGHT_CRITERIA ]] | [[ dev:sql:functions:highlight_keywords:home | $HIGHLIGHT_KEYWORDS ]] | [[ dev:sql:functions:ifnull | $IFNULL ]] | | + | |
- | | [[ dev:sql:functions:lj | $LJ ]] |[[ [[ dev:sql:functions:lookup:home | $LOOKUP ]] | [[ dev:sql:functions:lpad | $LPAD ]] | [[ dev:sql:functions:mod | $MOD ]] | [[ dev:sql:functions:proper | $PROPER ]] | [[ dev:sql:functions:random | $RANDOM ]] | | + | |
- | | [[ dev:sql:functions:rj | $RJ ]] | [[ dev:sql:functions:rpad | $RPAD ]] | [[ dev:sql:functions:score | $SCORE ]] | [[ dev:sql:functions:soundex | $SOUNDEX ]] | | + | |
- | ===== Oracle Functions ===== | + | |
- | Use the CREATE ENVIRONMENT SQL_SYNTAX setting or the SET SQL_SYNTAX setting to activate. | + | |
- | | [[ dev:sql:orafunctions:chr | CHR ]] | [[ dev:sql:orafunctions:instr | INSTR ]] | [[ dev:sql:orafunctions:new_time | NEW_TIME ]] | [[ dev:sql:orafunctions:nvl | NVL ]] | [[ dev:sql:orafunctions:substr | SUBSTR ]] | [[ dev:sql:orafunctions:sys_extract_utc | SYS_EXTRACT_UTC ]] | [[ dev:sql:orafunctions:sysdate | SYSDATE ]] | [[ dev:sql:orafunctions:systimestamp | SYSTIMESTAMP ]] | [[ dev:sql:orafunctions:to_date | TO_DATE ]] | [[ dev:sql:orafunctions:to_number | TO_NUMBER ]] | | + | |
- | ===== Omnidex Data Types ===== | + | |
- | ^See Also:^[[dev:sql:datatypes:home | List of Datatypes]]^ | + | |
- | ==== Omnidex Datatypes ==== | + | |< 95% 25% 75% >| |
+ | ^Statement ^Description ^ | ||
+ | | || | ||
+ | ^**Connections** ^^ | ||
+ | | [[dev:sql:statements:connect:home|CONNECT]] | Connect to an Omnidex Environment. | | ||
+ | | [[dev:sql:statements:disconnect:home|DISCONNECT]] | Disconnect from a connected Omnidex Environment. | | ||
+ | | [[dev:sql:statements:set:home|SET]] | Set Omnidex SQL processing options. | | ||
+ | | [[dev:sql:statements:attach:home|ATTACH]] | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment File. | | ||
+ | | [[dev:sql:statements:detach:home|DETACH]] | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. | | ||
+ | | || | ||
+ | ^**Queries and Exports** ^| | ||
+ | | [[dev:sql:statements:select:home|SELECT]] | Peform a Query Specification and SELECT rows. | | ||
+ | | [[dev:sql:statements:export:home|EXPORT]] | Export rows or rowids to an external file. | | ||
+ | | [[dev:sql:statements:open_cursor:home |OPEN CURSOR]] | Open a Cursor. | | ||
+ | | [[dev:sql:statements:close_cursor:home|CLOSE CURSOR]] | Close a previously opened cursor. | | ||
+ | | || | ||
+ | ^**Updates** ^| | ||
+ | | [[dev:sql:statements:insert:home|INSERT]] | Insert rows into a Table. | | ||
+ | | [[dev:sql:statements:update:home|UPDATE]] | Update rows in a Table. | | ||
+ | | [[dev:sql:statements:delete:home|DELETE]] | Delete rows in a Table. | | ||
+ | | || | ||
+ | ^**Omnidex Index Queries** ^| | ||
+ | | [[dev:sql:statements:qualify:home|QUALIFY]] | Qualify index counts using Omnidex criteria. | | ||
+ | | [[dev:sql:statements:join:home|JOIN]] | Join tables using Omnidex Indexes. | | ||
+ | | || | ||
+ | ^**Data Definition** ^| | ||
+ | | [[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] | Create an Omnidex Environment into a named physical operating system file. | | ||
+ | | [[dev:sql:statements:create_database:home|CREATE DATABASE]] | Create a Database within an Omnidex Environment. | | ||
+ | | [[dev:sql:statements:create_table:home|CREATE TABLE]] | Create a Table within a Database in the Omnidex metadata or an Omnidex or RDBMS temporary table. | | ||
+ | | [[dev:sql:statements:create_index:home|CREATE INDEX]] | Specify an Index in the Omnidex metadata but will not physically create the index. | | ||
+ | | [[dev:sql:statements:create_index_group:home | CREATE INDEX GROUP ]] | Specify an Index Group within the Omnidex metadata. | | ||
+ | | [[dev:sql:statements:drop_environment:home|DROP ENVIRONMENT]] | Deletes the Omnidex Metadata Environment by physically deleting the operating system file. | | ||
+ | | [[dev:sql:statements:drop_database:home|DROP DATABASE]] | Remove the named Database from the Omnidex metadata. | | ||
+ | | [[dev:sql:statements:drop_table:home|DROP TABLE]] | Remove a table from the Omnidex metadata or deletes a previously created temporary table. | | ||
+ | | [[dev:sql:statements:drop_index:home|DROP INDEX]] | Removes an index from the Omnidex metadata. | | ||
+ | | [[dev:sql:statements:drop_index_group:home | DROP INDEX GROUP]] | Removes a named Index Group from the Omnidex metadata. | | ||
+ | | || | ||
+ | ^**Maintenance** ^| | ||
+ | | [[dev:sql:statements:update_indexes:home|UPDATE INDEXES]] | Update Omnidex Indexes. | | ||
+ | | [[dev:sql:statements:update_rollup_table:home|UPDATE ROLLUP TABLE]] | Update Rollup Table with summarized information. | | ||
+ | | [[dev:sql:statements:update_rollups:home|UPDATE ROLLUPS]] | Update all Rollup Tables for an Omnidex Database. | | ||
+ | | [[dev:sql:statements:update_statistics:home|UPDATE STATISTICS]] | Update Statistics Information. | | ||
+ | | [[dev:sql:statements:home:update_text|UPDATE TEXT]] | Update Textual Statistics. | | ||
+ | | [[dev:sql:statements:partition:home|PARTITION]] | Partition a table into smaller sub tables based on criteria. | | ||
+ | | [[dev:sql:statements:load_ofx:home|LOAD OFX]] | Load Indexes for a table. | | ||
+ | | [[ dev:sql:statements:install_indexes:home | INSTALL INDEXES ]]| Specifies the Omnidex indexes into the Omnidex XML Environment. No indexing is performed. | | ||
+ | | [[ dev:sql:statements:remove_indexes:home | REMOVE INDEXES]]| Removes the Omnidex indexes from the Omnidex XML Environment. | | ||
- | === Standard Datatypes === | ||
- | |||
- | Omnidex supports the following standard datatypes: | ||
- | |||
- | ^ Datatype ^ Description ^ | ||
- | | CHARACTER(//n//) | Space-filled character string of //n// characters | | ||
- | | CHAR(//n//) | Synonym for CHARACTER(//n//) | | ||
- | | C STRING(//n//) | Null-terminated character string of //n// characters | | ||
- | | VARCHAR(//n//)* | String of max //n// characters, with length variable | | ||
- | | CLOB(//n//)* | Character large object of max //n// characters, with length variable | | ||
- | | [SIGNED] TINYINT | 1-byte, signed integer ( -128 to 127 ) | | ||
- | | UNSIGNED TINYINT | 1-byte, unsigned integer ( 0 to 255 ) | | ||
- | | [SIGNED] SMALLINT | 2-byte, signed integer ( -32768 to 32767 ) | | ||
- | | UNSIGNED SMALLINT | 2-byte, unsigned integer ( 0 to 65535 ) | | ||
- | | [SIGNED] INTEGER | 4-byte, signed integer ( 2147483648 to 2147483647 ) | | ||
- | | UNSIGNED INTEGER | 4-byte, unsigned integer ( 0 to 4294967295 ) | | ||
- | | [SIGNED] BIGINT | 8-byte, signed integer ( %%-2^63 to 2^63-1%% ) | | ||
- | | UNSIGNED BIGINT | 8-byte, unsigned integer ( %%0 to 2^64-1%% ) | | ||
- | | FLOAT | IEEE four-byte, single-precision floating point | | ||
- | | DOUBLE | IEEE eight-byte, double-precision floating point | | ||
- | | DATE | 10-byte ANSI date | | ||
- | | TIME | 11-byte ANSI time | | ||
- | | DATETIME | 24-byte ANSI datetime | | ||
- | | ASCII DATE | 8-byte ASCII date ( YYYYMMDD ) | | ||
- | | ASCII DATE(6) | 6-byte ASCII date ( YYMMDD ) | | ||
- | | ODBC DATE | 6-byte ODBC proprietary date | | ||
- | | ODBC TIME | 6-byte ODBC proprietary time | | ||
- | | ODBC DATETIME | 6-byte ODBC proprietary datetime | | ||
- | | ORACLE DATE | 7-byte Oracle proprietary date | | ||
- | | ORACLE TIME | 7-byte Oracle proprietary time | | ||
- | | ORACLE DATETIME | 7-byte Oracle proprietary datetime | | ||
- | | OMNIDEX DATE(//n//) | Omnidex proprietary date supporting //n// digits of YYYYMMDD | | ||
- | | OMNIDEX TIME(//n//) | Omnidex proprietary time supporting //n// digits of HHMMSSNN | | ||
- | | OMNIDEX DATETIME(//n//) | Omnidex proprietary datetime supporting //n// digits of YYYYMMDDHHMMSSNN | | ||
- | | * Omnidex recommends use of the CHARACTER and C STRING datatypes rather than VARCHAR and CLOB due to the requirement of a length variable. || | ||
- | |||
- | === National Character Datatypes === | ||
- | |||
- | Omnidex also provides partial support for National Character Datatypes (sometimes called | ||
- | Unicode datatypes or wide characters). The Omnidex SQL Engine will process these | ||
- | datatypes, but the Omnidex Indexing Engine will only index the lower 8 bits of these | ||
- | datatypes. | ||
- | |||
- | ^ Datatype ^ Description ^ | ||
- | | NATIONAL CHARACTER(//n//) | Space-filled character string of //n// characters | | ||
- | | NATIONAL CHAR(//n//) | Synonym for CHARACTER(//n//) | | ||
- | | NATIONAL C STRING(//n//) | Null-terminated character string of //n// characters | | ||
- | | NATIONAL VARCHAR(//n//)* | String of max //n// characters, with length variable | | ||
- | | NATIONAL CLOB(//n//)* | Character large object of max //n// characters, with length variable | | ||
- | | * Omnidex recommends use of the NATIONAL CHARACTER and NATIONAL C STRING datatypes rather than NATIONAL VARCHAR and NATIONAL CLOB due to the requirement of a length variable. || | ||
- | |||
- | |||
- | ===== Create a simple functioning Environment with an Omnidex index ===== | ||
- | <code> | ||
- | os> odxsql | ||
- | > CREATE ENVIRONMENT IN "myenv.xml"; | ||
- | > CREATE DATABASE mydb TYPE FLATFILE IN "myenv.xml"; | ||
- | > CREATE TABLE mytable PHYSICAL "test1" | ||
- | (myseq integer, mytext char(60) quicktext) | ||
- | IN "myenv.xml"; | ||
- | > CONNECT myenv.xml; | ||
- | > UPDATE INDEXES; | ||
- | > SELECT * FROM mytable where mytext = '<some criteria>'; | ||
- | > DISCONNECT; | ||
- | > EXIT | ||
- | os> | ||
- | </code> | ||
- | ===== Learning Omnidex ===== | ||
- | * [[ tutorials:simple_flatfile | Creating a very simple flat file Environment and doing retrievals. ]] | ||
- | * [[ tutorials:simple_flatfile_java_JDBC | Writing a simple JDBC program to access the simple flatfile Environment. ]] | ||
- | |||
{{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} |