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

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

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". 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 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.
Stored Procedures, Functions, and ViewsIn 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 TablesCentrallix 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...


Your Name:
Comment:


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

Project Hosting Provided By:
Hosted by Sourceforge