ComboStrap - Page Sql

About

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

Syntax

Select

A page select should follow the following railroad diagram.

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

From

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

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

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.
• descendants are the descendants page. Pages that have the same or a higher level than the current page.

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

Attribute

The attribute are defined in the page attribute

Predicate

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']))

filter-attribute

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

glob-pattern

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.

like-pattern

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.

date predicate

You can use:

• the now keyword
• the date function and the datetime function (They follow the same usage than Sqlite)

Example:

• All active event pages

select from pages where date_end >= now order by date_start asc

• All pages modified last week

select from pages where date_modified >= date('now','-7 days') order by date_modified asc

Limit

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.