eXtremeSQL is a high-performance implementation of the SQL programming language for interfacing with eXtremeDB databases. Built on the unsurpassed performance of eXtremeDB, and a SQL optimizer tuned for main memory database access, eXtremeSQL delivers blazingly fast processing of dynamic SQL queries.
Some of the benefits of using eXtremeSQL are:
- Co-existence with native eXtremeDB - Use eXtremeSQL alongside the eXtremeDB API in the same application. Where maximum performance is critical, the eXtremeDB API can’t be beat. When a higher level of access would be advantageous, such as retrieving data from multiple tables or performing aggregation, then use standard SQL statements with eXtremeSQL.
- Broad coverage of the SQL-89 standard - eXtremeSQL implements much of the ANSI SQL-89 specification.
- Extensions to exploit eXtremeDB features and data types - In addition, eXtremeSQL implements eXtremeDB-specific extensions including support for structures, arrays and vectors, as well as query optimizations based on specific eXtremeDB capabilities.
- Compatibility with all eXtremeDB editions - eXtremeSQL is fully compatible and interoperable with eXtremeDB APIs, eXtremeDB Transaction Logging, eXtremeDB High Availability, and eXtremeDB Cluster. There is no need to worry about the migration path within the eXtremeDB product family.
- No client / server inter-process communications - Like eXtremeDB, eXtremeSQL can be embedded in the application, not deployed as a separate process. This eliminates client / server inter-process communication round-trips from the execution path, resulting in breakthrough performance and zero administration.
- Interactive SQL utilities - eXtremeSQL comes with the powerful interactive SQL tool xSQL which can be used to manage eXtremeDB databases and test SQL statements independently from application programs. (Please use this link for a basic introduction to this versatile tool.)
Introduction to SQL
SQL (Structured Query Language) was developed by IBM in the mid ‘70s as a way to get information in and out of database systems. Contrary to what its name suggests, SQL is not only a query language, but also a language for manipulating (adding, changing or deleting) information already in the database.
SQL is a declarative language, whereas conventional programming languages (C/C++, Java, etc.) are procedural languages. Practically speaking, this means that SQL has no control statements (if and while, for example).
SQL has been widely adopted by database vendors and has been institutionalized as a standard by the ANSI and ISO organizations. SQL skills learned with one database system can be largely carried over to other SQL-capable database systems, though virtually every vendor implements proprietary extensions to the language to exploit capabilities of their own database technology.
ANSI SQL-89 Compliance
The ANSI SQL standard has evolved over the years, beginning with the SQL-89 standard, followed by SQL-92 and, most recently, the SQL-99 standard. eXtremeSQL provides broad implementation of the SQL-89 standard for SQL DML with many extensions:
- Arrays
- Structures
- References
- Support of all C++ built-in types
- User-defined functions
Also there are some incompatibilities with the ANSI standard:
- Assigning null values to columns of some types is not supported.
- Changing the database schema at runtime (add table, add index,...) is not supported.
- Identifiers are case sensitive (SQL keywords are case insensitive).
- Views are not updatable.
- Insofar as eXtremeDB has no notion of a “remote table”, materialized views are irrelevant (i.e. not supported).
- Constraints are not enforced by the SQL interface itself, but are enforced by the underlying eXtremeDB runtime.
RIGHT OUTER JOINandFULL OUTER JOINare not implemented.- Triggers are not supported, but underlying eXtremeDB event notifications will be invoked if the eXtremeSQL action causes such an event.