Sculptor SQL Engine Features

Introduction

The Sculptor SQL implementation is based entirely on the SQL-92 standard.

The entire SQL-92 parser has been coded but the engine is still under development and not all constructions are currently implemented. Here you can see the currently implemented features.

Summary

Data Query Language (DQL)

  • SELECT Partially implemented. See below for details.

Data Manipulation Language (DML)

  • UPDATE Change records.

  • INSERT Create records.

  • DELETE Delete records.

Sculptor SQL extensions

  • CACHE Control and monitor the Sculptor Cache Service.

  • AUDIT Control the Sculptor Audit Service.

  • SET Sculptor SQL Engine special settings.

Database elements

  • Catalogs

  • Schemas

  • Tables

  • Transaction Management

Sculptor to SQL data type mappings

Sculptor

SQL

a#

CHAR(#)

u1

TINYINT

i2

SMALLINT

i4

INTEGER

r8

REAL

i4/dn

DATETIME

n4.0/my

SMALLMONEY

n8.0/my

MONEY

Identifiers

Because field names in Sculptor are case sensitive, Sculptor SQL is not following the SQL-92 identifier standard. In Sculptor SQL identifiers are always case sensitive, so it is not necessary to use double quotes to make them case sensitive. A future implementation may provide an option to choose the behaviour.

Since SQL does not support array fields, Sculptor array fields are mapped to multiple SQL fields with names derived as follows:

Sculptor Array

SQL Equivalent Fields

myfield[n]

myfield_1, myfield_2, … myfield_n

SELECT (DQL)

Quantifier

  • ALL (same as nothing)

  • DISTINCT

Select List

  • *

  • Table.*

  • Schema.Table.*

  • Catalog.Schema.Table.*

  • Expressions (see later for list of implemented expressions).

Any of the above with an AS clause. Example:

SELECT tbl1.*, fld1, fld2 + fld3, 1 AS "Number", NULL AS "NullCol" ...

Literals and Operators

  • ‘STRING’ CHAR()

  • 12 INTEGER

  • 2.99 NUMERIC (money, …)

  • 1.43E-4 REAL

  • DATE ‘yyyy-mm-dd’ DATETIME

  • {d ‘yyyy-mm-dd’} DATETIME

  • VALUES (row1_col1, row1_col2, …, row1_colN),
           (row2_col1, row2_col2, …, row2_colN),
           …
           (rowM_col1, rowM_col2, …, rowM_colN)

  • TABLE TableName (Explicit table)

  • ‘+’ ‘-’ ‘*’ ‘/’ ‘(’ ‘)’ (with INTEGERS and REALS)

  • Set functions (COUNT, MAX, MIN, SUM, AVG)

  • String value functions (SUBSTRING)

SELECT clause

  • FROM

  • WHERE

  • GROUP BY

  • HAVING

  • Sub-queries (nested queries)

  • ORDER BY (cursor part)

FROM clause

  • Single table select

  • Old style joins (comma separated tables)

  • CROSS JOIN

  • INNER JOIN

  • OUTER JOINS

  • CONDITIONAL JOINS (ON clause is similar to WHERE clause)

WHERE clause

  • AND and OR

  • BETWEEN predicate (Since Sculptor version 6.0.1)

  • Comparison predicates (=, <>, <, >, <=, >=)

  • IN predicate (Since Sculptor version 6.0.1)

  • IS [NOT] NULL predicate

  • NOT and IS [NOT] {TRUE|FALSE|UNKNOWN}

  • LIKE predicate

  • EXISTS predicate

  • UNIQUE predicate

ORDER BY clause

  • ORDER BY {Column Number|Column Name} [{ASC|DESC}] [,{Column Number|Column Name} [{ASC|DESC}]]*

Optional ODBC Escape “markers”

"{" -> "$(VENDOR(MICROSOFT),PRODUCT(ODBC)"
"}" -> "*)"
{d ...}
{oj ...}

ODBC special options

  • Sculptor special connect attributes (SqlSetConnectAttr and SqlGetConnectAttr):

#define SQL_SC_ATTR_PROCESS_PRIORITY 0x00004000 /* SQL_DRIVER_CONNECT_ATTR_BASE */
#define SQL_SC_ATTR_THREAD_PRIORITY 0x00004001

  • The attribute value is a long value which must be one of:

/* Sculptor Process Priorities (SQL_SC_ATTR_PROCESS_PRIORITY) */
#define SPP_IDLE 1
#define SPP_BELOW_NORMAL 2
#define SPP_NORMAL 3
#define SPP_ABOVE_NORMAL 4
#define SPP_HIGH 5
#define SPP_REALTIME 6

/* Sculptor Thread Priorities (SQL_SC_ATTR_THREAD_PRIORITY) */
#define STP_IDLE 1
#define STP_LOW 2
#define STP_BELOW_NORMAL 3
#define STP_NORMAL 4
#define STP_ABOVE_NORMAL 5
#define STP_HIGH 6
#define STP_TIME_CRITICAL 7

  • To setup the login timeout connect attribute (SqlSetConnectAttr and SqlGetConnectAttr), Use the standard ODBC attribute define: SQL_ATTR_LOGIN_TIMEOUT

Sculptor SQL system metadata

Metadata tables defined inside the “Sculptor” schema. These can be accessed like any other table. * Sculptor.DataTypes * Sculptor.Procedures * Sculptor.Catalogs * Sculptor.Schemas * Sculptor.Tables * Sculptor.Columns * Sculptor.TableTypes

Note: This will change soon to the SQL-92 standard “INFORMATION_SCHEMA”.

Sculptor Catalog, Schema, Tables and Procedures structure

Directory location: $SCULPTOR/sql

Catalog metadata

Keyed file: catalogs

Field Name

Key

Data Type

Comments

CATALOG_NAME

Yes

a32

CATALOG_COMMENT

No

a255

Schema metadata

Keyed file: schemas

Field Name

Key

Data Type

Comments

CATALOG_NAME

Yes

a32

SCHEMA_NAME

Yes

a32

SCHEMA_DIRECTORY

No

a255

USER_NAME

No

a128

Authorised User

SCHEMA_COMMENT

No

a255

Table metadata

Keyed file: tables

Field Name

Key

Data Type

Comments

SCHEMA_NAME

Yes

a32

TABLE_NAME

Yes

a32

Table name or * for all tables

TABLE_COMMENT

No

a255

Stored procedures metadata

Keyed file: procedures

Field Name

Key

Data Type

Comments

CATALOG_NAME

Yes

a32

SCHEMA_NAME

Yes

a32

PROCEDURE_NAME

Yes

a48

NUM_INPUT_PARAMS

No

i4

NUM_OUTPUT_PARAMS

No

i4

NUM_RESULT_SETS

No

i4

PROCEDURE_COMMENT

No

a255

PROCEDUTE_TYPE

No

i4

PROCEDURE_CODE

No

a232[16]

(a3712)

SPARE

No

a1

Procedure types: SQL_PT_UNKNOWN, SQL_PT_PROCEDURE, SQL_PT_FUNCTION

Sculptor audit Service

Since Sculptor version 6.1 there is a new audit service. Some new special Sculptor SQL queries have been added to the SQL Engine. See the audit functions for information.

A new schema AuditInfo, has been created to conveninetly access the current audit database information.

RELATED TOPICS

Sculptor SQL engine introduction

Getting started with Sculptor SQL engine

Sculptor SQL engine examples

Sculptor SQL command