Difference between revisions of "SQL queries"
m (moved SQL Queries to SQL queries) |
|
(No difference)
|
Revision as of 18:34, 13 April 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/%'