Difference between revisions of "SQL queries"

Latest revision as of 17:50, 11 December 2012

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

@@ 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 ==
-Used to specigy a property list ( columns ) by the name it'll apppears in results. The character * indicates all properties are selected.
+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.
-'''selecting all keyword in all documents'''
+=== Select all keyword in all documents ===
- SELECT okm:keywords FROM okm:document
+<source lang="sql">
+SELECT okm:keywords FROM okm:document
+</source>
-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 )
+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.
-'''selecting all documents with keywords not empty'''
+=== Select all documents with keywords not empty ===
+<source lang="sql">
- SELECT okm:author, okm:keywords FROM okm:document WHERE 	okm:keywords <> ''
+SELECT okm:author, okm:keywords FROM okm:document WHERE okm:keywords <> ''
+</source>
 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 ==
 Used for pattern purposes in where clausule
-'''documents name starting with linux'''
+=== Documents name starting with linux ===
+<source lang="sql">
- SELECT * FROM okm:document WHERE okm:name LIKE 'linux%'
+SELECT * FROM okm:document WHERE okm:name LIKE 'linux%'
+</source>
 == CONTAINS ==
 Used to full text searching ( the indexed content )
-'''documents that contains jackrabbit'''
+=== Documents that contains jackrabbit ===
+<source lang="sql">
- SELECT * FROM okm:resource WHERE CONTAINS(., 'jackrabbit')
+SELECT * FROM okm:resource WHERE CONTAINS(., 'jackrabbit')
+</source>
-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')]
+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 ==
-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'''
- SELECT * FROM okm:document WHERE CONTAINS(., 'linux') ORDER BY jcr:score DESC
+=== 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 ==
 Always it'll appear in results and indicates the full node path. jcr:path it can be used in queries like:
-'''exact search'''
+=== Exact search ===
+<source lang="sql">
- jcr:path='/books/mybooks/EffectiveJava'
+jcr:path='/books/mybooks/EffectiveJava'
+</source>
-'''searching for children'''
- jcr:path LIKE '/books/%' AND NOT jcr:path LIKE '/books/%/%'
-'''descendants'''
- jcr:path LIKE '/books/mybooks/%'
+=== 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: OKM Network]]