ComboStrap - Page Sql

page sql is a SELECT sql syntax that permits to select pages and return page attribute used in a template iterator.

A page select should follow the following railroad diagram.

'SELECT'  [ from ] \
[ 'WHERE' predicate {',' predicate } ] \
[ 'ORDER BY' attribute ('ASC'|'DESC')? {',' attribute ('ASC'|'DESC')?} ] \
[ limit 'Number' ] ;

The from clause permits to set where the page data comes from.

' FROM' ('pages'|'backlinks') 

where:

• pages is the default table (ie all pages of the website)
• backlinks are the backlink pages. Pages that points to the actual requested page with a link.

The from clause is is facultative. The selection will use the pages table if the from clause is not found.

The attribute are defined in the page attribute

A predicate is a filter that will reduce the set of pages and takes the below form:

filter-attribute  ( ('='|'!=')  'value' ||  ['NOT'] ('GLOB' glob-pattern | 'LIKE'  like-pattern  ['ESCAPE' 'char']))

If your sqlite does not support Json, you can't filter on manually added metadata, otherwise you can filter on all attributes

GLOB pattern matching is case sensitive and uses the Unix file globbing syntax for its wildcards:

• * matches any number of characters (including none)
• ? matches exactly one character.
• [] specify a set of single characters or, when the hyphen character - is used, a range of characters. Example: [aeiou] matches any lowercase vowel, [0-9] matches any digit.
• the backslash character \ is the escape character. ie \\ matches a single backslash, \? matches the question mark.
• any other characters match itself.

Subpatterns are not supported by Sqlite (ie {sun,moon,stars} to match sun, moon, or stars)

example: the path and title of all pages that ends with 3 digits.

select where path glob '*[0-9][0-9][0-9]'

This sql returns the following data for this website.

The LIKE is not case sensitive and in the pattern expression:

• % matches any sequence of zero or more characters
• _ matches any single character.
• any other characters match itself.

example: If you want to get:

• the path and title of all pages
• that have the terms image, svg or raster in their path

you would use the following page sql:

select where path like '%image%' or path like '%svg%' or path like '%raster%'

This page sql will return the following pages for this website.

The limit clause permits to return the first rows. It's mostly used in Recent statement such as the last pages recently modified or created.

Example: If you want to get the last 4 pages along with the modification date and the path, you would execute this query:

select order by date_modified desc limit 4

This sql returns the following pages for this website.