The Basic SELECT Statement

[This is preliminary documentation and subject to change.]

A SELECT statement can return all items within a scope of the Web Store hierarchy or just those items having specified properties or, using a WHERE clause, those items whose specified properties have specified values. In addition you can optionally sort on any property. This should sound very familiar to database programmers, however because the Web Store is a semistructured database, you cannot the SELECT DISCTINCT qualifier.

To return specific properties, each property with its namespace must be surrounded by double quotes and separated by a comma. Two sets of double quotes are seen in this example in order to concatenate them correctly into a string variable called strQ:

strQ = "SELECT ""DAV:href"", ""DAV:displayname"" "

You can also use the aliases for the properties to use later in your SQL query.

A SELECT * statement returns all the properties belonging to all content classes that are among the list of expected content classes for that folder.

A SELECT * will not return values from subfolders recursively, unless a deep traversal is specified in the scope of the folder. A sallow traversal, the default, accounts for the contents specified by a URL, but does not extend its scope into any subfolder contents. A deep traversal is a comprehensive and recursive accounting of all subfolders and items within the scope of a URL.

A basic Web Store SQL statement has the following syntax:

SELECT * | select-list
FROM SCOPE( resource-list) 
[WHERE search-condition]
[order-by-clause]

SELECT * has the meaning of SELECT ALL. SELECT DISTINCT is not supported. SELECT * (select star) retrieves all properties for a folder's expected content-classes. One important consequence of this mechanism is that custom properties set by applications are not returned by such queries unless the property is part of one of the expected content classes for the folder.

DAV:SQL has a concept of tokens, which are properties in a select list or values in a WHERE clause. They must be delimited by single quotes. A token can contain only alphabet characters unless they are surrounded by double quotes. In the following WHERE clause, “this” and “that” are tokens:

WHERE CONTAINS (' "this" AND "that" ')

This clause does not work:

WHERE CONTAINS ('10 dollars')

To fix the clause, surround the token with double quotes within the single quotes:

WHERE CONTAINS ('"10 dollars"')