Index...
Centrallix Documentation
|
6. SQL Language
Overview
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, admittedly its SQL language is somewhat different 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, :last_name". | 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. |
| Table Names | All data sources are pathnames in the OSML, and so FROM sources normally start with a slash (/). Furthermore, an additional set of keywords control how these tree-structured data sources are used (see FROM Clause for details). 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. |
SQL Syntax Summary
SELECT [ column_alias = ] expression [, ... ]
[ FROM [ IDENTITY ] [ OBJECT ] [ [ INCLUSIVE ] SUBTREE ] [ WILDCARD ]
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
UPDATE [ SUBTREE ] [ IDENTITY ] pathname [ source_alias ] [, ... ]
SET expression = expression [, ... ]
[ WHERE expression ]
DELETE [ FROM ] [ SUBTREE ] pathname [ source_alias ]
[ FROM [ SUBTREE ] [ IDENTITY ] pathname [ source_alias ] [, ... ] ]
[ WHERE expression ]
Comments...
(none yet)
Add a Comment...
|
