6. SQL Language
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".||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.|
SQL Syntax Summary
In most contexts (notably, except for subqueries), multiple SQL statements can be concatenated by separating them with a semicolon.
SELECT [ column_alias = | :declared_object:attribute = ] expression [, ... ]
[ FROM [ IDENTITY ] [ OBJECT ] [ [ INCLUSIVE ] SUBTREE ] [ WILDCARD ] [ EXPRESSION ]
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 [, ... ]
[ WHERE expression ]
DELETE [ FROM ] [ SUBTREE ] pathname [ source_alias ]
[ FROM [ SUBTREE ] [ IDENTITY ] pathname [ source_alias ] [, ... ] ]
[ WHERE expression ]
DECLARE OBJECT objectname
Add a Comment...