Centrallix - It's Different than what you Think
Home   |   Technology   |   Screen Shots   |   Download   |   Documentation   |   History   |   For Developers

Search...


Search For:

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, 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).

CategoryCentrallix SQLOther SQL's
Outer JoinsCentrallix 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.
AttributesBecause 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 NamesAll data sources are pathnames in the OSML, and so FROM sources normally start with a slash (/). Example: SELECT ... FROM /myDatabase/myTable/rowsTable names are generally of the form DatabaseName.TableName or Database.Owner.Table.
GROUP BYCentrallix 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 SetThe 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 TypesCentrallix 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 ClauseCentrallix 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


Comments...


(none yet)

Add a Comment...


Your Name:
Comment:


(c) 2001-2011 LightSys Technology Services, Inc. All trademarks are property of their respective owners.

Project Hosting Provided By:
Hosted by Sourceforge