Differences

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

--- dev:sql:home [2010/07/03 22:48]
tdo
+++ dev:sql:home [2016/06/28 22:38]
127.0.0.1 external edit
@@ Line 1: / Line 1: @@
 {{page>:top_add&nofooter&noeditbtn}}
-<html><div align="center"><span style="color:red">DRAFT</span></div></html>
 ~~NOTOC~~
-====== Omnidex SQL Quick Reference ======
+====== Omnidex SQL Statement Quick Reference ======
-{{page>:sql_bar&nofooter&noeditbtn}}
-{{page>:odxsql_bar&nofooter&noeditbtn}}
-===== Omnidex SQL Statements and OdxSQL Commands =====
+[[dev:sql:home|Statements]] | [[dev:sql:functions|Functions]] | [[dev:sql:datatypes|Datatypes]] | [[dev:sql:examples | Examples]]
-^ 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 ]] |
+|< 95% 50% 50% >|
-^ 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 ]] |
+^                                           Omnidex SQL Statements                                                                   ^^
-^ 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 ]] |
+^**Connections**                                                   ^**Data Definition**                                               ^
-^ 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 ]] |
+|[[dev:sql:statements:connect:home|CONNECT]]                       |[[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] |
-^ 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 ]] |
+|[[dev:sql:statements:disconnect:home|DISCONNECT]]                 |[[dev:sql:statements:create_database:home|CREATE DATABASE]]       |
-^ 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 ]]|
+|[[dev:sql:statements:set:home|SET]]                               |[[dev:sql:statements:create_table:home|CREATE TABLE]]             |
-^ 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 *]]|
+|[[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]]         |
+\\
+\\
-* Statement is only available from OdxSQL.
+====== Omnidex SQL Statement Descriptions =======
-===== Omnidex SQL Standard Functions =====
+Omnidex SQL Statements can be issued through any standard ODBC and JDBC connection, and through the OdxSQL program.
-| [[ 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 =====
+|< 95% 25% 75% >|
-| [[ 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]] |
+^Statement  ^Description  ^
-|[[ 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 ]] |
+^**Connections**  ^^
-| [[ dev:sql:functions:rj | $RJ ]]  | [[ dev:sql:functions:rpad | $RPAD ]] | [[ dev:sql:functions:score | $SCORE ]] | [[ dev:sql:functions:soundex | $SOUNDEX ]] |
+| [[dev:sql:statements:connect:home|CONNECT]] | Connect to an Omnidex Environment. |
-===== Oracle Functions =====
+| [[dev:sql:statements:disconnect:home|DISCONNECT]] | Disconnect from a connected Omnidex Environment. |
-| [[ 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 ]] |
+| [[dev:sql:statements:set:home|SET]] | Set Omnidex SQL processing options. |
-===== SETTINGS that affect SQL =====
+| [[dev:sql:statements:attach:home|ATTACH]] | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment File. |
-| SET | SET SQL_SYNTAX=<ORACE%%|%%OMNIDEX%%|%%SQLSERVER> | Allow Oracle functions and SqlServer bracket syntax. |
+| [[dev:sql:statements:detach:home|DETACH]] | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. |
-| OdxSQL | SET TERMINATION [ ON %%|%% OFF ] | Set auto termination of statements. |
+|  ||
+^**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. |
-===== 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. ]]
-===== 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}}

Back to top

dev/sql/home.txt · Last modified: 2020/11/30 18:42 by doc

Sitemap Recent Changes