The D3 SQL User's Guide describes working with SQL tables, D3 SQL syntax and D3 query optimization.
This introductory section contains information helpful for understanding the relationship between D3 files and SQL tables.
SQL is a standardized data query, update, and definition language, based on the relational model, that has been adopted as a standard by the International Organization for Standardization. Many types of applications use SQL statements to access data, like client-side report generation tools, ad-hoc query utilities and application development environments for databases.
Since SQL is based on the relational model, it works on two-dimensions, with the major axes defined as rows and columns. D3 works on the MultiValue Pick data model with axes defined as items, attributes, values, subvalues, etc. An item in a D3 file corresponds to a row in an SQL table. An attribute in a D3 file corresponds to one or more columns in an SQL table.
Retrieving and displaying data using SQL is accomplished with the SELECT statement. The SELECT statement is similar to D3’s AQL statements LIST and SORT, although the SQL syntax is more extensive. Like LIST and SORT, the SELECT statement can sort a file in ascending or descending order, use comparison expressions on data to limit the range of the items displayed, or specify which attributes (columns) are displayed, and so on.
Data can be modified using SQL with the INSERT, DELETE and UPDATE statements. There is no shell-level equivalent in D3.
As you use SQL and study the syntax in this manual, keep in mind that the SQL terms TABLE, ROW and COLUMN correspond to the D3 terms FILE, ITEM and ATTRIBUTE, respectively.
The table below compares SQL and D3 terms.
| SQL Terms | D3 Terms |
|---|---|
| Table | File |
| Table Descriptor | Dictionary |
| Row | Item |
| Column | Attribute |
| Column Descriptor | Attribute-defining Item |
It is important to realize that D3 data exists in a D3 file, even when a query is made using an SQL statement. When an SQL table is created on a D3 database, a table descriptor is created, not an actual SQL table. The table descriptor (which is similar to a D3 dictionary) can be thought of as a mapping between the D3 file and the two-dimensional data structure recognized by SQL. The data always remains in the D3 file and is viewed by SQL using the mapping.
Please note that the term table is used throughout this manual, in syntax and narrative, to refer to what, technically, is often only a table descriptor. As far as SQL is concerned, the table descriptor is the table itself. The data remains in a D3 file.
To use SQL to access D3 data, each D3 file must have a mapping that maps the structure of a MultiValue D3 data file to the structure of one or more two-dimensional SQL tables.
There are two methods that can be used to create a mapping.
One method uses the TCL command SQL-CREATE-VIEW or SQL-CREATE-TABLE and creates a mapping from a D3 file that already exists. See Working with SQL tables from existing D3 files for details.
Another method uses the SQL statement CREATE TABLE. This method first creates a new D3 file and then creates the mapping. See CREATE TABLE for details.
The D3 SQL Catalog is the Tables file located in the SQL account. The D3 SQL Catalog contains the table descriptors for all user-created SQL tables, as well as the table descriptors for the provided system tables needed for D3 SQL.
When SQL-CREATE-VIEW, SQL-CREATE-TABLE or ISQL CREATE TABLE is executed from any account, a new table descriptor is added to the Tables file. Whenever attribute-defining items are created or modified in a D3 file’s dictionary, SQL-CREATE-VIEW or SQL-CREATE-TABLE can be run again to reflect those changes in the SQL table descriptor for that particular file.
Direct user modification of the internal SQL catalog files is not supported. The contents of the SQL Catalog are documented in D3 SQL catalog.
The SQL rules for naming accounts are significantly more strict than with D3.
Account names must begin with a letter (A-Z, case insensitive) followed by any combination of letters, numbers (0-9) and the underscore (_). (Note: D3 SQL does not allow a period (.) in the account name.)
Account names cannot be a D3 SQL Reserved Word. Reserved word are listed in D3 SQL reserved words.
If account names do not meet the criteria described above, the user must either rename the accounts or create q-pointers to the accounts. If using the q-pointer approach, all SQL usage and administration must be done using that q-pointer account.