Difference between revisions of "SQL queries"
From OpenKM Documentation
| (12 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| − | A SQL query is composed normally by clausule SELECT and optionally FROM, WHERE and ORDER BY | + | {{TOCright}} __TOC__ |
| + | |||
| + | 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 == | == SELECT == | ||
| − | Used to | + | Used to specify a property list ( columns ) by name that will apppear in results. The character * indicates all properties are selected. |
| − | |||
== FROM == | == FROM == | ||
Defines the nodes type ( tables ) selected. | Defines the nodes type ( tables ) selected. | ||
| − | + | === Select all keyword in all documents === | |
| − | + | <source lang="sql"> | |
| − | + | SELECT okm:keywords FROM okm:document | |
| − | + | </source> | |
| + | 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) | ||
== WHERE == | == WHERE == | ||
Used to filter selected rows by some criteria. | Used to filter selected rows by some criteria. | ||
| − | + | === Select all documents with keywords not empty === | |
| − | + | <source lang="sql"> | |
| − | + | SELECT okm:author, okm:keywords FROM okm:document WHERE okm:keywords <> '' | |
| − | + | </source> | |
Operators evaluation order: | Operators evaluation order: | ||
| − | () | + | * () |
| − | operators: <, >, =, <=, >=, <>, LIKE, IS NULL, IS NOT NULL | + | * operators: <, >, =, <=, >=, <>, LIKE, IS NULL, IS NOT NULL |
| − | functions: CONTAINS | + | * functions: CONTAINS |
| − | logic opertors: NOT, AND, OR | + | * logic opertors: NOT, AND, OR |
| − | |||
== LIKE == | == LIKE == | ||
Used for pattern purposes in where clausule | Used for pattern purposes in where clausule | ||
| − | + | === Documents name starting with linux === | |
| − | + | <source lang="sql"> | |
| − | + | SELECT * FROM okm:document WHERE okm:name LIKE 'linux%' | |
| − | + | </source> | |
== CONTAINS == | == CONTAINS == | ||
Used to full text searching ( the indexed content ) | Used to full text searching ( the indexed content ) | ||
| − | + | === Documents that contains jackrabbit === | |
| − | + | <source lang="sql"> | |
| − | + | SELECT * FROM okm:resource WHERE CONTAINS(., 'jackrabbit') | |
| + | </source> | ||
| − | There's some limitation there's no | + | 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. |
| − | |||
| − | |||
| + | <source lang="xml"> | ||
| + | //element(*, okm:document)[jcr:contains(okm:content, 'linux')] | ||
| + | </source> | ||
== ORDER BY == | == ORDER BY == | ||
| − | Used to ordering results. Normally used the jcr:score property. | + | Used to sort (control ordering of) results. Normally used the jcr:score property. |
| − | |||
| − | |||
| − | |||
| − | |||
| + | === Documents that contains linux ordered by score === | ||
| + | <source lang="sql"> | ||
| + | SELECT * FROM okm:document WHERE CONTAINS(., 'linux') ORDER BY jcr:score DESC | ||
| + | </source> | ||
== Property jcr:path == | == Property jcr:path == | ||
Always it'll appear in results and indicates the full node path. jcr:path it can be used in queries like: | Always it'll appear in results and indicates the full node path. jcr:path it can be used in queries like: | ||
| − | + | === Exact search === | |
| − | + | <source lang="sql"> | |
| − | + | jcr:path='/books/mybooks/EffectiveJava' | |
| − | + | </source> | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| + | === Searching for children === | ||
| + | <source lang="sql"> | ||
| + | jcr:path LIKE '/books/%' AND NOT jcr:path LIKE '/books/%/%' | ||
| + | </source> | ||
| + | === Descendants === | ||
| + | <source lang="sql"> | ||
| + | jcr:path LIKE '/books/mybooks/%' | ||
| + | </source> | ||
[[Category: Administration Guide]] | [[Category: Administration Guide]] | ||
| − | |||
Latest revision as of 16:50, 11 December 2012
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 specify a property list ( columns ) by name that will apppear 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 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)
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 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')]
ORDER BY
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
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/%'
