Changes for page HQL Scripting Examples in Velocity

Summary

• Page properties (1 modified, 0 added, 0 removed)

Details

Page properties

Content

...	...	@@ -1,159 +1,140 @@
1	1	{{box cssClass="floatinginfobox" title="**Contents**"}}{{toc start="2" depth="4"/}}{{/box}}
2	2
3		-{{include document="DevGuide.velocityHqlExamplesMacro"/}}
4		-
5	5	= HQL Query Examples in Velocity =
6	6
7		-XWiki allows user to access documents and objects with [[HQL>>http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html]] queries in [[Velocity>>http://jakarta.apache.org/velocity/docs/user-guide.html]] scripts. \\
	5	+XWiki allows user to access documents and objects with [[HQL>>http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html]] queries in [[Velocity>>http://jakarta.apache.org/velocity/docs/user-guide.html]] scripts.
8	8
9		-== Public API (searchDocuments) ==
	7	+General example showing how to display the first 5 results of a given query:
10	10
11		-{{velocity filter="none"}}
12		-{{html clean="false" wiki="true"}}
13		-#info("With this API the query consist in the WHERE condition.\\
14		- Any user with edit rights can use this API (as in write a script using it).\\
15		- Any user with view rights can view the result of such a query.")
16		-<p/>
17		-You can execute queries as follows:
18		-
19		-{{code}}
20		-#set($query="where doc.creator='XWiki.VincentMassol'")
21		-#set($results = $xwiki.searchDocuments($query, 5, 0))
	9	+{{code language="none"}}
	10	+#set($hql="<query here>")
	11	+#set($results = $xwiki.searchDocuments($hql, 5, 0))
22	22	#foreach ($item in $results)
23	23	* $item
24	24	#end
25	25	{{/code}}
26	26
	17	+The examples below will show you various HQL queries that you can write.
	18	+
	19	+== Public API (##searchDocuments##) ==
	20	+
	21	+{{velocity}}{{html}}
	22	+#info("With this API the query consist in the WHERE condition of a full HQL query. Any user with edit rights can write a script using this API. Any user with view rights can view the result of such a query.")
	23	+{{/html}}{{/velocity}}
	24	+
27	27	=== Simple Query ===
28	28
29		-#set($hql="where doc.creator='XWiki.VincentMassol'")
30		-#displayQuery($hql false)
	27	+Displays all documents who have been created by the user ##XWiki.JohnDoe##:
31	31
	29	+{{code language="none"}}
	30	+#set($hql = "where doc.creator='XWiki.JohnDoe'")
	31	+{{/code}}
	32	+
32	32	=== Ordered Query ===
33	33
34		-#set($hql="where doc.creator='XWiki.VincentMassol' order by doc.date asc")
35		-#displayQuery($hql false)
	35	+Displays all documents who have been created by the user ##XWiki.JohnDoe## and sorted by document's last modification date, in ascending order:
36	36
	37	+{{code language="none"}}
	38	+#set($hql = "where doc.creator='XWiki.VincentMassol' order by doc.date asc")
	39	+{{/code}}
	40	+
37	37	=== Advanced Query (date & time) ===
38	38
39		-Since there is no [[standard way to calculate dates interval in HQL>>http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434]] those queries are a bit unnatural.
40		-<p/>
41		-#set($hql="where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) = day(current_date()) and hour(doc.date) > (hour(current_time()) - 1) order by doc.date desc")
42		-#displayQuery($hql false)
	43	+{{velocity}}{{html wiki="true"}}
	44	+#info("Since there is no [[standard way to calculate dates interval in HQL>>http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434]] those queries are a bit unnatural.")
	45	+{{/html}}{{/velocity}}
43	43
44		-{{code}}
45		-Other examples, documents modified :
46		-
47		-during current day : "where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) > (day(current_date()) - 1) order by doc.date desc"
48		-during current week : "where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) > (day(current_date()) - 7) order by doc.date desc"
49		-during current month : "where year(doc.date) = year(current_date()) and month(doc.date) > (month(current_date()) - 1) order by doc.date desc"
	47	+{{code language="none"}}
	48	+#set($hql = "where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) = day(current_date()) and hour(doc.date) > (hour(current_time()) - 1) order by doc.date desc")
50	50	{{/code}}
51	51
52		-== Privileged API (search : Documents, Objects, Properties, etc) ==
	51	+Other examples:
	52	+* Listing all documents modified during the current day: {{code language="none"}}where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) > (day(current_date()) - 1) order by doc.date desc{{/code}}
	53	+* Listing all documents modified during the current week: {{code language="none"}}where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) > (day(current_date()) - 7) order by doc.date desc{{/code}}
	54	+* Listing all documents modified during the current month: {{code language="none"}}where year(doc.date) = year(current_date()) and month(doc.date) > (month(current_date()) - 1) order by doc.date desc{{/code}}
53	53
54		-#warning("Calls to te privileged API are only executed when the calling page has been saved by an Admin. \\
55		- The reason is that search can be used to send any HQL command like update, delete, etc.")
56		-<p/>
57		-You can execute queries as follows:
	56	+== Privileged API (##search##: Documents, Objects, Properties, etc) ==
58	58
59		-{{code}}
60		-#set($query="select doc.name from XWikiDocument doc")
61		-#set($results = $xwiki.search($query, 5, 0))
62		-#foreach ($item in $results)
63		- * $item <br/>
64		-#end
65		-{{/code}}
	58	+{{velocity}}{{html}}
	59	+#warning("Calls to te privileged API are only executed when the calling page has been saved by a user with Programming Rights. The reason is that search can be used to send dangerous HQL command like update, delete, etc.")
	60	+{{/html}}{{/velocity}}
66	66
67	67	=== Simple Query ===
68	68
69		-#set($hql="select doc.name from XWikiDocument doc")
70		-#displayQuery($hql true)
	64	+{{code language="none"}}
	65	+#set($hql = "select doc.name from XWikiDocument doc")
	66	+{{/code}}
71	71
72	72	=== Count Query ===
73	73
74		-{{code}}
75		-#set($query="select count(doc) from XWikiDocument doc")
76		-#set($results = $xwiki.search($query))
77		-## $xwiki.search returning a list, we get its first element
78		-$query result : $results.get(0)
	70	+{{code language="none"}}
	71	+#set($results = $xwiki.search("select count(doc) from XWikiDocument doc"))
	72	+## Since $xwiki.search is returning a list, we get its first element
	73	+Count : $results.get(0)
79	79	{{/code}}
80	80
81		-#set($query="select count(doc) from XWikiDocument doc")
82		-#set($results = $xwiki.search($query))
83		-$query results : $results.get(0)
84		-<p/>
85		-<br/>
86		-
87	87	=== Simple Query with multiple fields ===
88	88
89		-{{code}}
90		-#set($results=$xwiki.search("select doc.name, doc.date from XWikiDocument doc", 5, 0))
	78	+{{code language="none"}}
	79	+#set($results = $xwiki.search("select doc.name, doc.date from XWikiDocument doc", 5, 0))
91	91	#foreach ($row in $results)
92	92	#foreach ($col in $row)
93		- #if ($velocityCount==1)
94		- #set($docName=$col)
95		- #elseif ($velocityCount==2)
96		- #set($docDate=$col)
	82	+ #if ($velocityCount == 1)
	83	+ #set($docName = $col)
	84	+ #elseif ($velocityCount == 2)
	85	+ #set($docDate = $col)
97	97	#end
98	98	#end
99		-$docName : $docDate <br/>
	88	+ $docName : $docDate <br/>
100	100	#end
101	101	{{/code}}
102	102
103		-#set($hql="select doc.name, doc.date from XWikiDocument doc")
104		-#displayQuery($hql true)
105		-<p/>
106		-<br/>
107		-
108	108	=== Getting objects of a specific class ===
109	109
110		-#set($hql="select obj.name from BaseObject obj where obj.className='XWiki.XWikiUsers'")
111		-#displayQuery($hql true)
112		-<p/>
113		-<br/>
	94	+{{code language="none"}}
	95	+#set($hql = "select obj.name from BaseObject obj where obj.className='XWiki.XWikiUsers'")
	96	+{{/code}}
114	114
115	115	=== Getting objects' properties ===
116	116
117		-#set($hql="select obj.name, prop.value from BaseObject obj, StringProperty prop where obj.className='XWiki.XWikiUsers' and prop.id.id=obj.id and prop.name='first_name'")
118		-#displayQuery($hql true)
119		-<p/>
120		-<br/>
	100	+{{code language="none"}}
	101	+#set($hql = "select obj.name, prop.value from BaseObject obj, StringProperty prop where obj.className='XWiki.XWikiUsers' and prop.id.id=obj.id and prop.name='first_name'")
	102	+{{/code}}
121	121
122	122	=== Getting documents where objects' properties equals some value ===
123	123
124		-#set($hql="select doc.fullName from XWikiDocument doc, BaseObject obj, StringProperty prop where doc.fullName=obj.name and obj.className='XWiki.XWikiUsers' and prop.id.id=obj.id and prop.name='first_name' and prop.value='Jean-Vincent'")
125		-#displayQuery($hql true)
	106	+{{code language="none"}}
	107	+#set($hql = "select doc.fullName from XWikiDocument doc, BaseObject obj, StringProperty prop where doc.fullName=obj.name and obj.className='XWiki.XWikiUsers' and prop.id.id=obj.id and prop.name='first_name' and prop.value='Jean-Vincent'")
	108	+{{/code}}
126	126
127	127	=== List users currently editing pages ===
128	128
129		-#set($hql="select distinct lock.userName from XWikiLock lock")
130		-#displayQuery($hql true)
	112	+{{code language="none"}}
	113	+#set($hql = "select distinct lock.userName from XWikiLock lock")
	114	+{{/code}}
131	131
132	132	=== List attachments of a page ===
133	133
134		-#set($hql="select att.filename from XWikiAttachment att, XWikiDocument doc where doc.fullName='Main.WebHome' and att.docId=doc.id")
135		-#displayQuery($hql true)
	118	+{{code language="none"}}
	119	+#set($hql = "select att.filename from XWikiAttachment att, XWikiDocument doc where doc.fullName='Main.WebHome' and att.docId=doc.id")
	120	+{{/code}}
136	136
137		-== Non-exhaustive list of queryable object fields ==
	122	+== Non-exhaustive list of queryable Object Fields ==
138	138
139		-#macro(exval $value)Example of value : //$value//#end
140		-
141		-
142	142	=== XWikiDocument ===
143	143
144		- * **XWikiDocument.fullName** : full name, including space and page name. #exval("Main.WebHome").
145		- * XWikiDocument.author : last editor. #exval("XWiki.Admin").
146		- * XWikiDocument.creator : first editor. #exval("XWiki.Admin").
	126	+* **XWikiDocument.fullName** : full name, including space and page name. Example value: ##Main.WebHome##
	127	+* XWikiDocument.author : last editor. Example value: ##XWiki.Admin##
	128	+* XWikiDocument.creator : first editor. Example value: ##XWiki.Admin##
147	147
148	148	=== BaseObject ===
149	149
150		- * **BaseObject.id** : arbitrary unique id of the object. #exval("123456789").
151		- * BaseObject.className : class. #exval("XWiki.XWikiUsers").
	132	+* **BaseObject.id** : arbitrary unique id of the object. Example value: ##123456789##
	133	+* BaseObject.className : class. Example value: ##XWiki.XWikiUsers##
152	152
153		-=== *Property (StringProperty, etc) ===
	135	+=== *Property (StringProperty, IntegerProperty, etc) ===
154	154
155		- * **Property.id.id** : unique id of the object the property belongs to. #exval("123456789").
156		- * Property.name : name of the property. #exval("first_name").
157		- * Property.value : value. #exval("John").
158		-{{/html}}
159		-{{/velocity}}
	137	+* **Property.id.id** : unique id of the object the property belongs to. Example value: ##123456789##
	138	+* Property.name : name of the property. Example value: ##first_name##
	139	+* Property.value : value. Example value: ##John##
	140	+