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')?} ] \
[ limit 'Number' ] ;

From

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.

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.

Page
:docs:router:404 - What is a missing page (404) and how ComboStap solve them

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.

Pages Title
:docs:content:image How to use the image tag in ComboStrap
:docs:content:svg How to use a SVG in ComboStrap
:docs:performance:svg_optimization Svg Optimization included
:docs:content:raster Raster Image in ComboStrap (jpg, png, ...)
:docs:content:page-image Page-image Component: Renders the illustrative image of your page
:docs:content:image How to use the image tag in ComboStrap
:docs:content:page-image Page-image Component: Renders the illustrative image of your page
:docs:content:raster Raster Image in ComboStrap (jpg, png, ...)
:docs:content:svg How to use a SVG in ComboStrap
:docs:performance:svg_optimization Svg Optimization included
:docs:page:image Page Image

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.

Pages Date Modified
:docs:menubar:menubar Saturday, 03 September 2022
:docs:page:canonical_automatic Saturday, 27 August 2022
:release:1.24.1 Saturday, 27 August 2022
:release:latest Saturday, 27 August 2022
Powered by ComboStrap