The tables and stored procedures exposed by an IP are defined by the OpenAccess SDK Schema tables OA_TABLES, OA_COLUMNS, OA_STATISTICS, OA_FKEYS, OA_PROC and OA_PROCCOLUMNS. The contents of these schema tables together define the user tables, columns, stored procedures and the relationship between the tables. The contents of the schema tables can come either from the OpenAccess SDK Schema Database (referred to as static schema mode) or from the SCHEMA function in the IP (referred to as dynamic schema mode).
The ODBC, ADO, JDBC and .NET layers map schema functions like SQLTables to queries on one of the schema tables. If the OpenAccess SDK SQL engine’s schema database is used, this information is retrieved from CSV files or a compiled binary schema file. If the dynamic schema is used, the SCHEMA function in the IP is called. In either case, the information required is the same.
The OpenAccess SDK SQL engine’s schema database should be populated by using the DDL commands such as CREATE TABLE. Executing a DDL command results in the modification of entries in the underlying schema tables. The OA_TABLES table defines all the table names that are accessible from that IP and OA_COLUMNS defines information about each column of the tables. The OA_STATISTICS table defines indexes in tables. The OA_FKEYS table defines the foreign key relationships between tables and the primary keys. Index information is required to support optimized access to your data source. OA_FKEYS is required for optimized join processing. The OA_PROC and OA_PROCCOLUMNS together define the stored procedures and the stored procedure columns. The schema tables are exposed as SQL tables and therefore can be accessed by Interactive SQL and other applications through SQL queries.
The OpenAccess built-in schema manager supports two formats for storing the Schema Database:
• CSV Schema format
• Binary Schema format
CSV Schema format
In the CSV Schema format, the Schema Database tables are contained in the CSV Schema format files, as shown in the following table:
Table 21: Schema Database tables in Schema format files
Schema Database table
CSV Schema format file
OA_TABLES
oa_tables.csv
OA_COLUMNS
oa_columns.csv
OA_STATISTICS
oa_stat.csv
OA_FKEYS
oa_fkeys.csv
OA_PROC
oa_proc.csv
OA_PROCCOLUMNS
oa_proccol.csv
The Schema Database tables are defined specific to each IP and are accessible from the client without the IP implementing additional code. These files are located in the directory defined by the DataSourceIPSchemaPath attribute. You configure this attribute using the OpenAccess SDK Manager. Templates of these files for starting a new schema database are installed as part of the product. They are copied into a new directory when you follow the steps to set up the development of your IP as described in the specific OpenAccess SDK Programmer’s Reference for your programming language.
Note: The template schema database contains entries that must not be deleted.
Binary schema format
The binary schema format supports fast access to very large schema databases. The binary schema format is suitable when you have many tables and each table has hundreds of columns.
The binary schema compiler can read the source schema from the CSV format of the OpenAccess SDK Schema database or from a Microsoft Access Database (MDB file) that is configured with the required schema objects such as OA_TABLES. The read-only binary schema file that is generated by the schema compiler for Windows can be used on any platform. Use of the binary schema format requires the IP to work in the static schema mode.
In the dynamic schema mode, the IP’s SCHEMA function is called to request the information that is required to materialize the various schema tables. It is up to the IP how it generates the requested information. Typically, the IP is coded to use an existing data dictionary of the data source and to map this information to the OpenAccess SDK required information as needed.
