Index...
Centrallix Documentation
|
6. SQL Language
Overview
The Centrallix SQL language is used in SQL queries and to a lesser extent (functions and operators) in expressions within applications, components, reports, and more.
With a built-in SQL engine, Centrallix has the capability of operating as a stand-alone SQL database server, though for the most robust capability we recommend using a separate RDBMS as the back end.
Though Centrallix contains a SQL engine, its SQL language has some differences from ANSI SQL. The primary reason for this is the flexible data environment that Centrallix models. In normal ANSI SQL, a data source is a table specification, typically in the form of Database.Table. In Centrallix, a data source is an ObjectSystem pathname to an object which contains subobjects to be queried. Centrallix also allows for direct referencing of a particular object's attribute by typing /path/to/object:attrname. For this reason (and others) the SQL language does differ somewhat, although the basic concepts are all still the same.
This document assumes a moderate prior knowledge of SQL.
Key Differences from Other SQL's
Without getting into various extended features in Centrallix SQL, here is a brief list of major differences between Centrallix SQL and most other SQL languages (including ANSI SQL).
| Category | Centrallix SQL | Other SQL's |
|---|
| Outer Joins | Centrallix SQL uses Sybase / MS SQL Server outer join syntax, using *= or =* in the WHERE clause join criteria. | Most other SQL's use the keywords "OUTER JOIN" in the FROM clause. |
| Attributes | Because Centrallix must handle attribute names of any syntax and format, Centrallix attributes are always preceded by a colon, as in "SELECT :first_name, :table2:last_name". This will also, in the future, allow attribute and object names to be dynamic (computed), without having to use the eval() function. | Other SQL's sometimes restrict the format of a column name, or require the use of ANSI quotes mode which makes a distinction between single quotes and double quotes, or use backticks around the identifiers. A typical SELECT statement would be "SELECT first_name, table2.last_name". |
| Table Names | All data sources are pathnames in the OSML, and so FROM sources normally start with a slash (/). Example: SELECT ... FROM /myDatabase/myTable/rows | Table names are generally of the form DatabaseName.TableName or Database.Owner.Table. |
| GROUP BY | Centrallix follows MySQL conventions for GROUP BY - the non-aggregate columns in the query need not be exhaustively listed in the GROUP BY clause. | In most SQL's, failing to include all non-aggregate columns in the GROUP BY clause results in a cartesian product result set. |
| Result Set | The result set may not be entirely regular in form -- different rows may have differing sets of attributes, depending on the data source (especially in a SELECT * query). | SELECT queries normally return a result set where each row has the exact same set of attributes, though some may be NULL. |
| Data Types | Centrallix uses a simplified set of data types, where constraints such as bit length and such are handled in a different manner. | Most SQL's mix the basic character of the data type (char, int, etc.) with the physical storage characteristics (number of bits, number of bytes, etc.), such as char(10) or tinyint. |
| FROM Clause | Centrallix has several extensions, mostly used for dealing with hierarchical data. These include the IDENTITY, SUBTREE, INCLUSIVE SUBTREE, OBJECT, and WILDCARD keywords in the FROM clause. | Standard SQL's do not have these extensions, since they deal with flat relational data instead of (potentially) hierarchical data. |
| Stored Procedures, Functions, and Views | In Centrallix, the equivalent of stored procedures or views would be Query Objects (.qy files). Such an object can have declared parameters and a multi-statement SQL query, can be called as a SELECT data source or via an EXEC statement, and can usually support updates, deletes, and inserts. | Most SQLs have a way of creating stored procedures or functions or views using the SQL language itself. |
| Temporary Tables | Centrallix implements this need via "temporary collections" (see DECLARE). These collections are in-memory and feature automatic indexing based on the query criteria used to access them. | Other SQLs usually have a temporary table feature in the SQL language, with an alternate storage method and limited table lifetime. Indexes on such tables must be manually created. |
SQL Syntax Summary
In most contexts (notably, except for subqueries), multiple SQL statements can be concatenated by separating them with a semicolon.
Below is an overview of the different components of Centrallix SQL (CXSQL):
SELECT [ column_alias = | :declared_object:attribute = ] expression [, ... ]
[ FROM [ IDENTITY ] [ NONEMPTY ] [ OBJECT ] [ [ INCLUSIVE ] SUBTREE ] [ WILDCARD ]
[ PAGED ] [ EXPRESSION ] [ COLLECTION ]
pathname [ source_alias ] [, ... ] ]
[ WHERE expression ]
[ GROUP BY expression [, expression ... ] ]
[ ORDER BY expression [ ASC | DESC ] [, ... ] ]
[ HAVING expression ]
[ LIMIT [ start, ] count ]
[ FOR UPDATE ]
------
INSERT [ INTO ] pathname select-statement
[ ON DUPLICATE expression [ , ... ]
UPDATE SET expression = expression [, ... ] ]
------
UPDATE [ SUBTREE ] [ IDENTITY ] pathname [ source_alias ] [, ... ]
SET expression = expression [ IF MODIFIED ] [, ... ]
[ WHERE expression ]
------
DELETE [ FROM ] [ SUBTREE ] pathname [ source_alias ]
[ FROM [ SUBTREE ] [ IDENTITY ] pathname [ source_alias ] [, ... ] ]
[ WHERE expression ]
------
DECLARE OBJECT objectname [ SCOPE scopetype ]
------
DECLARE COLLECTION collectionname [ SCOPE scopetype ]
------
EXEC pathname parametername = expression [, ...]
Comments...
(none yet)
Add a Comment...
|
