Omnidex SQL: CREATE DATABASE

The CREATE DATABASE statement declares a database in an Omnidex Environment File. Once one or more databases and their respective tables have been declared in an environment, they can be indexed and retrieved using SQL statements.

An Omnidex database can point to an underlying Oracle, SQL Server or MySQL database, or any other database that can be accessed using ODBC. An Omnidex database can also point to a collection of raw data files that combine together to form a database. These databases are accessible using the Omnidex SQL engines, available through standard interfaces like ODBC and JDBC.

The CREATE DATABASE statement records information about the underlying database in the Omnidex Environment Catalog, but it does not actually create an underlying relational database. The physical database should be created using the relational database's tools. In the case of raw data files, the files are usually received from a data provider, and application or an ETL tool. The CREATE DATABASE statement simply stores meta-data about the database in the Omnidex Environment File.

Omnidex allows multiple databases to be declared in an environment. When an application connects to an Omnidex Environment File, then SQL statements can be issued that access all of the databases in the environment. Databases can be of different types as well, and this allows applications to issue SELECT statements that join tables from different database types, including raw data files.

  CREATE DATABASE database_name
      [NODE node_name]
          TYPE type
          [VERSION “version”]
          [SUBTYPE subtype]
          [INDEX_DIRECTORY “directory”]
          [PHYSICAL “physical”]
          [USER “user”][PASSWORD “password”]
          [INDEX_MAINTENANCE index_maintenance_type]
      IN “filename”                             /* must be after all previous clauses */
      [WITH options]                            /* if used, must be the very last clause  */

The database_name uniquely identifies the database within this environment. It may be up to 32 characters long, and can contain letters, digits and the following special characters: ! @ # $ % ^ _. It must begin with a letter and is case-insensitive. The database_name must be unique within the environment.

Database_name is used to qualify table and index names when the same table or index name occurs more than once within an Omnidex Environment.

Database_name is also used as the prefix for index files located in the index_directory as specified in the INDEX_DIRECTORY setting.

CREATE DATABASE SALES IN "SALES.XML";
...
UPDATE INDEXES
exit
os> dir
SALES0001, SALES0002, SALES0003, SALES.XML

The TYPE clause specifies the type of database being declared. The type is always required.

Valid database types are:

• FLATFILE
• ORACLE
• SQLSERVER
• ODBC

When a database is spread across multiple nodes of an Omnidex Grid, the node is declared for each time. At present, the type must be the same across all nodes; however, support for different database types for each node may be supported in the future.

The SUBTYPE setting within the TYPE clause is used to specify a subtype for ODBC databases. Valid subtypes are:

• MYSQL
• SQLSERVER

Omnidex can access other databases through ODBC, even if the subtype is not declared. Note that there can be differences in the way that each underlying database implements ODBC. Because of this, some Omnidex functionality may not be available.

The VERSION clause is used to by Omnidex to determine which syntax and API to use with the underlying relational database.

For Oracle, the choices are:

For SQL Server, the choices are:

The INDEX_DIRECTORY clause specifies the operating system location of the Omnidex index files and optionally the index file prefix.

The directory portion can either be an absolute or relative path. Relative paths are relative to the location of the Omnidex Environment File. Relative locations are recommended when feasible.

If no index_directory is declared, then the Omnidex index files will reside in the same directory as the Omnidex Environment File.

The optional NODE clause is used to spread a database across the various nodes in an Omnidex Grid Environmnet.

Each NODE name used in the CREATE DATABASE NODE clause must correspond to a node_name specified in the CREATE ENVIRONMENT statement.

create environment           
 data_cache                  32
 max_threads                 2
 node                        "GRID01" partitioned
 node                        "GRID02" partitioned
 node                        "GRID03" partitioned
 node                        "GRID04" partitioned
 node                        "GRID05" unpartitioned
 in                          "list.xml";

create database              "LIST"
 node "GRID01"
  type                       FLATFILE
  index_directory            "idx\LIST1_"
 node "GRID02"
  type                       FLATFILE
  index_directory            "idx\LIST2_"
 node "GRID03"
  type                       FLATFILE
  index_directory            "idx\LIST3_"
 node "GRID04"
  type                       FLATFILE
  index_directory            "idx\LIST4_"
 node "GRID05"
  type                       FLATFILE
  index_directory            "idx\LIST5_"
 in                          "list.xml";

The PHYSICAL “dbms_string” declares RDBMS connection information and is used primarily with relational databases and ODBC.

The “dbms_string” varies according to database type as follows:

• FLATFILES - ignored
• ORACLE with SQLNET - if using Oracle SQLNET, use the name of the SQLNET service and specify “SQLNET x” in the VERSION clause.
• ORACLE without SQLNET - The PYSICAL caluse is ignored and Oracle will use the default database for the specified user.
• SQLSERVER - The PHYSICAL clause contains the ODBC connection information. For FILE DSNs, use “FILEDSN=file_dsn_filespec”. For System/User DSNs, use “DSN=dsn_name” or just “dsn_name” where dsn_name is the data source name set up in the Windows ODBC Administrator program.
• ODBC - The PHYSICAL clause contains the ODBC connection information. For FILE DSNs, use “FILEDSN=file_dsn_filespec”. For System/User DSNs, use “DSN=dsn_name” or just “dsn_name” where dsn_name is the data source name set up in the Windows ODBC Administrator program.

The USER / PASSWORD clause is used to provide a valid RDBMS database user and password to be used for Omnidex to access the underlying RDBMS data source.

The USER / PASSWORD clause is required for ORACLE. It declares either or both the name and password of a valid database. All connections to the database will connect with this option. USER and PASSWORD can be filespecs where the contents of the file contain the user and password.

The INDEX_MAINTENANCE clause is an optional clause that specifies how Omnidex indexes will be updated.

The index_maintenance_type can be one of either API or DBMS.

API - The indexes will be updated by Omnidex automatically as the database data is updated.

DBMS - The indexes will be updated by some other means, ODXAIM for example, in a separated process.

This setting applies to the entire index installation on this database. It can be over-ridden on a table-by-table basis by explicitly defining the index_maintenance setting on an individual table.

The IN “filespec” clause specifies the physical file name and directory containing the metadata for an Omnidex Environment.

The IN “filespec” can be specified either as a relative or absolute file specification and much be enclosed in double quotes.

There are currently no options for the CREATE DATABASE statement.

create environment in "env1.xml" with delete;
create database db1 type flatfile in "env1.xml";

create environment in "env1.xml" with delete;

create database db1 type flatfile in "env1.xml";
create database db2 type flatfile in "env1.xml";

create table db1.table1 physical "db1table1"       /* Note the db1 database qualification */
  ( pk integer,
    mytext char(60) quicktext
  )
  in "env1.xml";

create table db2.table1 physical "db2table1"       /* Note the db2 database qualification */
  ( pk integer,
    mytext char(60) quicktext
  )
  in "env1.xml";

create file db1table1;
create file db2table1;

connect env1

insert into db1.table1 values (1, 'db1 row 1');
insert into db1.table1 values (2, 'db1 row 2');

select * from db1.table1;

insert into db2.table1 values (1, 'db2 row 1'); 
insert into db2.table1 values (2, 'db2 row 2'); 

select * from db2.table1;

select * from db1.table1
union
select * from db2.table1;

disconnect