SchemafreeSQL

SFSQL

Schema-Free, Structured Query Language

In order to query your database, SFSQL markup is passed to the "query" command.

SFSQL is a SQL-like query language which allows related objects to be represented via simple dot-notation (e.g. $s:company.person.address)

SELECT

If you know SQL, then you already know how to query data using SFSQL because SFSQL is SQL, but simplified.

What is simplified? For starters, you don't need to specify elaborate JOIN syntax.

For example, to retrieve the following data (stored in nested company, person and address objects):

Company Name	Person - First Name	Person - Last Name	Address Name	City	State
ABC, Inc.	John	Public	home	Newark	NJ
ABC, Inc.	John	Public	work	New York	NY
ABC, Inc.	Sue	Shee	home	Manhattan	NY

...use the following SFSQL:

SELECT

$s:company.name,
$s:company.person.firstName,
$s:company.person.lastName,
$s:company.person.address.name,
$s:company.person.address.city,
$s:company.person.address.state

WHERE

$s:company.name = 'ABC, Inc.'

NOTE: The s: attribute prefix denotes a string type. See datatypes.

Notice that all "columns" (in SchemafreeSQL they are "attributes") must be prefixed with a dollar sign ($) which differentiates a SchemafreeSQL attribute from other SQL grammar.

Optionally, the double dollar sign ($$) can be used which denotes a left-join relationship (i.e. include the parent object even when it doesn't have a value for a specified child attribute.)

As you can see, SFSQL is essentially standard SQL syntax but without the JOIN complexities and FROM clauses.

Syntax:

    	SELECT
    	    select_expr [, select_expr ...]
    	    FROM
    	    JOIN
    	    [WHERE where_condition]
    	    [GROUP BY {col_name | expr | position}
    	      [ASC | DESC], ... [WITH ROLLUP]]
    	    [HAVING where_condition]
    	    [ORDER BY {col_name | expr | position}
    	      [ASC | DESC], ...]
    	    [LIMIT {[offset,] row_count 
              | row_count OFFSET offset}]
    	    [FOR UPDATE]

DateTime Expressions/Shortcuts

Along with explicit date/time values, you may also use DateTime expressions as shortcuts when inputting date values.
These expressions allow you to perform date/time math and simplify the input of date/time data from your application.

Examples:

now +5d (five days from now)
1/1/2012 +4h30m (4 hours 30 minutes after new years 2012)
now -1w (1 week ago)
now +1y -2d (2 days before 1 year from now)

Grammar

DateTime Expression
DateTime_Expression

DateTime Operation
DateTime_Operation

DateTime Unit
DateTime_Unit

Where:

DateTime Units are: year, Month, week, day, hour, minute, second