cQL Missing Features

The following features still need to be added to cQL to handle some queries.

• JSON Syntax Support
• More SQL Standardization
• Relationships
• Dimension Support
• Load All Objects Referenced in SELECT Clause
• Chained Attributes
• Hierarchical Queries
• Order Indexes
• Collection/Deep Field Aliases
• Collection Indexes
• Joins
• Namespaces

JSON Syntax Support

Objects

INSERT Contact SET Phone={"Number": "867-5309", "Description": "Home"}

Property quotes are optional.  Property and value quotes may be double- or single-quotes.

INSERT Contact SET Phone={'Number': "867-5309", Description: 'Home'}

Arrays/Lists

INSERT Contact SET Name='Jenny', Phones=[
    {Number: '867-5309', Description: 'Home'},
    {Number: '867-5305', Description: 'Work'}
]

Contextual comparisons can be done in WHERE and HAVING clauses:

SELECT * FROM Contact WHERE Phone={
    Number LIKE '%716%'
    AND PhoneType='Mobile'
}

More SQL Standardization

Support more existing SQL standard functions and query expressions/additions, even if only supported by specific flavors of SQL (e.g. PostgreSQL).

• CREATE with Inheritance.  PostgreSQL has:

  CREATE TABLE capitals (
      state           char(2)
  ) INHERITS (cities);

• ALTER
• GRANT
• MERGE/UPSERT
• PostgreSQL-style Content-Type Inheritance (ie. SELECT * FROM ONLY parent_content_type and SELECT * FROM parent_content_type*).  Postgres defaults to ContentType+ (aka parent_content_type*).  Should we?
• table.field->relationship is called a REF type.  Maybe this is what we should use instead of dots?  It is similar to PHP, but different than JavaScript.

Relationships

Reference other content to define relationships in a single query.  Any unique key can be used by referencing the key directly.

INSERT Contact SET Name='Jenny', Mother={OID: 123}, Phone={
    Number: '867-5309',
    PhoneType: {OID: 456}
}

The primary key can be used for an even simpler reference.

INSERT Contact SET Name='Jenny', Mother='JaneSmith', Phone={
    Number: '867-5309',
    PhoneType: 'Mobile'
}

Relationship comparisons can be done in expressions:

SELECT * FROM Contact WHERE Mother='JaneSmith'

Dimension Support

Need to be able to easily create dimensional content by setting the dimensional reference field(s).

INSERT Contact SET Language='EN', Phone={
    Number: "867-5309",
    Description: "Home"
}

Change the dimension(s) of an object by settings dimensional reference field(s).

UPDATE Contact SET Language='EN' WHERE OID=123

Load All Objects Referenced in SELECT Clause

When the get() or get_first() method is called, ALL objects in a collection referenced in the SELECT clause should be loaded, not just the ones that match the internal query.

Solution 1 (Temporary, Quick and Dirty)

Do not load sub-content from get() and get_first(), which will cause it to lazy-load correctly when referenced. Less efficient, but will correct results.

Solution 2 (Move WHERE clause to SELECT clause)

When get() or get_first() is called, only perform LEFT JOINs and move the WHERE clause into the SELECT clause so that it won't filter out SQL results, but can be checked when rows are rolled up into objects.

In other words, for the following cQL

SELECT Contents FROM Folder WHERE Contents.Title='Hello'

SQL similar to the following should be produced by the engine

SELECT
	ContentsRef.ChildType AS _ContentsType,
	ContentsRef.ChildID AS _ContentsID,
	(ContentField_Title.Value='hello') AS _WhereResult
FROM Folder
LEFT JOIN ContentsRef ON (
	ContentsRef.ParentFieldID=[Folder.Contents] AND
	ContentsRef.ParentType=[Folder] AND
	Contents.ParentID=Folder.ID)
LEFT JOIN ContentField_Title ON (
	ContentField_Title._ContentTypeID=ContentsRef.ChildType AND
	ContentField_Title._ContentID=ContentsRef.ChildID AND
	ContentField_Title._ActiveID=0)

Then, when results are rolled up into objects, only include objects that have at least one TRUE _WhereResult, since we can't put it in the SQL WHERE clause or it will filter out ContentsRef rows that we need to load.

Solution 3

Flag subcontent (objects and collections) as "incomplete", so subsequent references to those subcontent fields can act accordingly. For example, referencing a collection would update the field object or collection with something like:

SELECT
	ContentsRef.ChildType AS _ContentsType,
	ContentsRef.ChildID AS _ContentsID,
FROM ContentsRef ON (
	ContentsRef.ParentFieldID=[Folder.Contents] AND
	ContentsRef.ParentType=[Folder] AND
	Contents.ParentID=Folder.ID)

The SQL results would update the object or collection with "object stubs" where they were missing, but keep the loaded objects where they already existed. They would have already been loaded, so this would happen automatically.

Order Indexes

SELECT Contents FROM Folder WHERE Contents.Title='Hello'
ORDER_INDEX(ParentObject,FieldThatReferencesFROMobjects)

Collection/Deep Field Aliases

There is currently no way to compare values in more than one collection object. For exapmle, it is not possible to get content types that have a Title and Children field. Collection/deep aliases would make it possible to name collection object "JOINs".

SELECT *, Fields AS Fields1, Fields AS Fields2 FROM ContentType
WHERE Fields1.Identifier='Title' AND Fields2.Identifier='Children'

Field aliasing can also be used to eliminate repetative and redundant deep dot-notation fields.

SELECT *, Fields.FieldType.Identifier AS TypeName FROM ContentType
WHERE TypeName='TextLine'

Both uses above can also be combined. For example, to get Content Types that have a content type and data type field.

SELECT *, Fields.FieldType AS FieldType,
       FieldType AS Type1, FieldType AS Type2
FROM ContentType WHERE
       Type1.Type.Identifier='ContentType' AND
       Type2.Type.Identifier='DataType'

Collection Indexes

Another potentially useful feature missing from SQL is a way to index into a collection/join to match on a specific object in a collection.

SELECT *, Fields[0] AS Fields1, Fields[1] AS Fields2 FROM ContentType
WHERE Fields1.Identifier='Title' AND Fields2.Identifier='Children'

This could also be possible without aliases.

SELECT * FROM ContentType
WHERE Fields[0].Identifier='Title' AND Fields[1].Identifier='Children'

Open question: What would this do? Maybe all indexed objects should be considered first, regardless of order given in cQL, then non-indexed aliases are considered second, excluding indexed objects.

SELECT *, Fields AS Fields1, Fields[0] AS Fields2 FROM ContentType
WHERE Fields1.Identifier='Title' AND Fields2.Identifier='Children'

Open question: Should there be some way to access the order index metadata?

SELECT * FROM ContentType
WHERE (Fields[a].Identifier='Title' AND ORDER_INDEX(Fields[a])=0)
AND (Fields[b].Identifier='Children' AND ORDER_INDEX(Fields[b])=1)

Joins

It should be possible to join filtered collections to efficiently select subsets of collections.

SELECT Accordions.*, Websites
FROM WebPage
JOIN WebPage.Sections AS Accordions ON SectionType='accordion'
JOIN Websites ON Websites.Hostname=WebPage.Hostname

The results of the query above would include an ad-hoc (aka alias) collection named Accordions in each WebPage object, which only includes the accordion sections.  Using ContentType(or alias).Field notation causes the join to be made on the FROM object automatically, additional filtered by the ON clause.

The results would also include an ad-hoc (aka alias) collection of Websites  Using ContentType only notation causes the join to be made on a content type as a whole, only filtered by the ON clause.

Namespaces (v4)

There should be a way to define a namespace for the entire query, and specific content types. It could also be useful to namespace fields, but that would be more complex and may be unnecessary.

Namespace identifiers can correspond to the namespace of a Package in the repository or a URL to a package object. The URL should resolve to one of the recognized/supported web service formats (JSON, XML).

Namespaces should follow these rules:

• One or more namespaces can be specified to only match content types in those packages.
• If no namespace is provided, content type names will match against any in the repository.
• Two content types matching an identifier in the FROM clause should result in an error, to avoid unexpected results.

Specify a namespace for the entire query:

NAMESPACE com_comandsystems_core
FROM User WHERE Username='Ted'

Specify one or more namespaces for specific content types in the query:

NAMESPACE comand = https://comand.io/rest/?cpath=[:Package]com_comandsystems_core
NAMESPACE intranet = com_comandsolutions_intranet
FROM comand:User, intranet:User+ WHERE Username='Ted'

Assume a namespace for the entire query, but use specifics for some content types:

NAMESPACE https://comand.io/rest/?cpath=[:Package]com_comandsystems_core
NAMESPACE intranet = com_comandsolutions_intranet
FROM User, intranet:User+ WHERE Username='Ted'

• < cQL vs SQL
• cQuery >