Difference between revisions of "SQL queries"

Revision as of 10:44, 3 March 2010

A SQL query is composed normally by clausule SELECT and optionally FROM, WHERE and ORDER BY

SELECT

Used to specigy a property list ( columns ) by the name it'll apppears in results. The character * indicates all properties are selected.

FROM

Defines the nodes type ( tables ) selected.

selecting all keyword in all documents

SELECT okm:keywords FROM okm:document

okm:keyword is a okm:document property. The nodes in repository has a hierarchical structura, the base type is nt:base. To searching in all nodes mus be used this type ( FROM nt:base )

WHERE

Used to filter selected rows by some criteria.

selecting 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

LIKE

Used for pattern purposes in where clausule

documents name starting with linux

SELECT * FROM okm:document WHERE okm:name LIKE 'linux%'

CONTAINS

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

ORDER BY

Used to ordering 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

Property jcr:path

Always it'll appear in results and indicates the full node path. jcr:path it can be used in queries like:

exact search

jcr:path='/books/mybooks/EffectiveJava'

searching for children

jcr:path LIKE '/books/%' AND NOT jcr:path LIKE '/books/%/%'

descendants

jcr:path LIKE '/books/mybooks/%'