Accessing Reality Files using SQL

What is a Reality SQL Catalog?

An SQL catalog on a Reality database is a set of Reality data files and, optionally, indexes which are defined as a set of SQL tables and indexes, and configured to be accessed via an ODBC or JDBC client-server interface. The data is accessed by issuing SQL retrieval and update instructions to an SQL server program on the host.

Client/Server Interface

Both SQL/ODBC and SQL/JDBC operate using a client/server interface. In both cases, the server is a Reality database containing an SQL Catalog and running an SQL Server program. When working with SQL/ODBC, an ODBC compliant application running from a PC acts as the client. This can establish a connection to a Reality database using a pre-defined ODBC Data Source which contains references to where the SQL Catalog resides (for a Reality system this includes the remote host system, database, account, and login details). The Data Source also contains a reference to the RealSQL driver on the PC, which is used to act as an interface between the application and the remote database. The application can forward ODBC commands and SQL statements to the SQL server via the RealSQL driver. The server processes these commands and may return results back to the application, again via the RealSQL driver.

SQL/JDBC is similar, but in this case, the client is a Java application, applet, or servlet, residing on any platform connected to a network via TCP/IP and capable of hosting a Java Virtual Machine. A Java application can import the RealSQL-JDBC driver package and use the standard JDBC API calls to issue commands and SQL statements to a remote Reality database. The commands are passed into the driver using a driver-specific URL (unlike ODBC, there is no intermediate Data Source that contains references to the SQL Catalog, etc.). Results are returned to the client application via the RealSQL-JDBC driver.

Note

For test purposes, SQL statements can be executed by entering the SQL command at the TCL prompt in the Reality environment on the host.

Definition of the SQL Environment in Reality

A Reality SQL catalog is defined by a set of four files, SQLTABLES, SQLCOLUMNS, SQLINDEXES and SQLTABLEINFO; there is one SQL catalog per Reality account. Two other SQL system files SQLINFO and SQLTYPES are also supported on each Reality database. These are used by the SQL Maintenance (SQLM) utility and the SQL server to define the SQL environment on the Reality database. See SQL Environment Files for details.

So, for example, a database with accounts Account1 and Account2 set up as SQL catalogs can be represented as follows:

Database with Account1 and Account2 set up as SQL catalogs

The SQL catalog (SQLTABLES, SQLCOLUMNS, SQLINDEXES and SQLTABLEINFO files) defines the SQL tables, columns and indexes of the SQL catalog in terms of Reality files and indexes, typically, in its local account. The section SQL Environment Files describes the contents of these files.

Where an application places data in more than one account, the SQL catalog should be located in the main account and related data files residing in remote accounts are referenced by Q-pointers. See the topic Multiple Account Catalogs.

Administration Using SQLM

The SQL Maintenance utility (SQLM) enables you to administer the security of an SQL catalog. It is run in the Reality environment by entering the SQLM command at TCL.

Setting up an SQL Catalog

An SQL catalog is set up in a Reality account using the SQLM utility, run by entering the SQLM command at TCL. The SQLM utility creates the SQL environment by creating the SQL catalog, SQLTABLES, SQLCOLUMNS, SQLINDEXES and SQLTABLEINFO files, in the Reality account and pointers to system files, SQLINFO and SQLTYPES, in SYSFILES.

SQLM prompts for the information to define the tables, columns and indexes in the SQL catalog, held in SQLTABLES, SQLCOLUMNS and SQLINDEXES. Any Existing English dictionaries are used to facilitate this process. SQLM also enables subsequent routine maintenance of the SQL catalog. Pointers to SQLINFO and SQLTYPES files are created when SQLM is first run and do not need to be modified. The following diagram illustrates the process.

SQLM - sources of information

For a detailed description of how to use the SQLM command at TCL, see Catalog Administration.

Multiple Account Catalogs

Some application suites place their data files in more than one account. In this case, the set of accounts is really a single catalog from the SQL viewpoint and should therefore have a single set of SQL catalog files. This is achieved by nominating one account as the main account and keeping the SQL catalog files (SQLTABLES, SQLCOLUMNS, SQLINDEXES and SQLTABLEINFO files) in that account. The SQLTABLES file then references files in related accounts by Q-pointers. None of the other accounts forming the SQL catalog should contain SQL catalog files. This allows the application to retain its normal account structure, but avoids the problems, when using Q-pointers, of allowing a file to be defined in more than one SQL catalog.

SQL-Reality Relationships

Each SQL table corresponds to a Reality file, either in the local account, or via a Q-pointer in a remote account. In its basic form, each row in a table corresponds to an item in the Reality data file and each column corresponds approximately to an attribute. If multivalues and subvalues are used in an item, then a row can be generated for each multivalue and subvalue.

Column definitions are similar to data definitions in Reality dictionaries and can be based on Reality data definitions using SQLM. In addition, an SQL index corresponds directly to a Reality index and can be created based on an English index definition (but see Recommendations for Creating Indexes).