SQL, often referred to as Structured Query Language, is a database computer language designed for managing data in relational database management systems (RDBMS), and originally based upon relational algebra.
A SQL query is composed normally by clausule SELECT and optionally FROM, WHERE and ORDER BY.
Used to specify a property list ( columns ) by name that will apppear in results. The character * indicates all properties are selected.
Defines the nodes type ( tables ) selected.
Select all keyword in all documents
SELECT okm:keywords FROM okm:document
okm:keyword is an okm:document property. The nodes in repository has a hierarchical structure; the base type is nt:base: This type must be used to search all nodes (FROM nt:base)
Used to filter selected rows by some criteria.
Select all documents with keywords not empty
SELECT okm:author, okm:keywords FROM okm:document WHERE okm:keywords <> ''
Operators evaluation order:
- operators: <, >, =, <=, >=, <>, LIKE, IS NULL, IS NOT NULL
- functions: CONTAINS
- logic opertors: NOT, AND, OR
Used for pattern purposes in where clausule
Documents name starting with linux
SELECT * FROM okm:document WHERE okm:name LIKE 'linux%'
Used to full text searching ( the indexed content )
Documents that contains jackrabbit
SELECT * FROM okm:resource WHERE CONTAINS(., 'jackrabbit')
There's some limitation there's no equivalent with this XPath query in SQL. There's a limitation in SQL to using CONTAINS in a descendant query.
//element(*, okm:document)[jcr:contains(okm:content, 'linux')]
Used to sort (control ordering of) results. Normally used the jcr:score property.
Documents that contains linux ordered by score
SELECT * FROM okm:document WHERE CONTAINS(., 'linux') ORDER BY jcr:score DESC
Always it'll appear in results and indicates the full node path. jcr:path it can be used in queries like:
Searching for children
jcr:path LIKE '/books/%' AND NOT jcr:path LIKE '/books/%/%'
jcr:path LIKE '/books/mybooks/%'