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/02 21:23] 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_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:home | 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:home | Update Rollup ]] | | + | |
| - | ^ 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 ]]| | + | |
| - | ===== Omnidex SQL Standard Functions ===== | + | |
| - | | [[ 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: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:upper | UPPER ]] | [[ dev:sql:functions:user | USER ]] | | + | |
| - | ===== Omnidex Extended Functions ===== | + | |
| - | | [[ 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 ===== | + | |< 95% 50% 50% >| |
| - | | [[ dev:sql:orafunctions:chr | CHR ]] | [[ dev:sql:orafunctions:instr | INSTR ]] | [[ dev:sql:orafunctions:new_time | NEW_TIME ]] | [[ dev:sql:orafunctions:nvl | NVL ]] | [[ SUBSTR ]] | [[ SYS_EXTRACT_UTC ]] | [[ SYSDATE ]] | [[ dev:sql:orafunctions:systimestamp | SYSTIMESTAMP ]] | [[ dev:sql:orafunctions:to_date | TO_DATE ]] | [[ dev:sql:orafunctions:to_number | TO_NUMBER ]] | | + | ^ 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]] | | ||
| + | \\ | ||
| + | \\ | ||
| - | ===== Create a simple functioning Environment with an Omnidex index ===== | + | ====== Omnidex SQL Statement Descriptions ======= |
| - | <code> | + | |
| - | os> odxsql | + | Omnidex SQL Statements can be issued through any standard ODBC and JDBC connection, and through the OdxSQL program. |
| - | > CREATE ENVIRONMENT IN "myenv.xml"; | + | |
| - | > CREATE DATABASE mydb TYPE FLATFILE IN "myenv.xml"; | + | |< 95% 25% 75% >| |
| - | > CREATE TABLE mytable PHYSICAL "test1" | + | ^Statement ^Description ^ |
| - | (myseq integer, mytext char(60) quicktext) | + | | || |
| - | IN "myenv.xml"; | + | ^**Connections** ^^ |
| - | > CONNECT myenv.xml; | + | | [[dev:sql:statements:connect:home|CONNECT]] | Connect to an Omnidex Environment. | |
| - | > UPDATE INDEXES; | + | | [[dev:sql:statements:disconnect:home|DISCONNECT]] | Disconnect from a connected Omnidex Environment. | |
| - | > SELECT * FROM mytable where mytext = '<some criteria>'; | + | | [[dev:sql:statements:set:home|SET]] | Set Omnidex SQL processing options. | |
| - | > DISCONNECT; | + | | [[dev:sql:statements:attach:home|ATTACH]] | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment File. | |
| - | > EXIT | + | | [[dev:sql:statements:detach:home|DETACH]] | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. | |
| - | os> | + | | || |
| - | </code> | + | ^**Queries and Exports** ^| |
| - | ===== Learning Omnidex ===== | + | | [[dev:sql:statements:select:home|SELECT]] | Peform a Query Specification and SELECT rows. | |
| - | * [[ tutorials:simple_flatfile | Creating a very simple flat file Environment and doing retrievals. ]] | + | | [[dev:sql:statements:export:home|EXPORT]] | Export rows or rowids to an external file. | |
| - | * [[ tutorials:simple_flatfile_java_JDBC | Writing a simple JDBC program to access the simple flatfile Environment. ]] | + | | [[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. | | ||
| - | ===== Special Omnidex SQL tokens ===== | ||
| - | |||
| - | | $omnidex | special table of one row and one column. Good to test functions, etc. | | ||
| - | | $uniquekey | | | ||
| - | | $ROWID | | | ||
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||