Difference between revisions of "SQL queries"

Revision as of 19:49, 13 April 2010

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.

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.

Select 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.

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

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/%'