Java > Open Source Codes > org > outerj > daisy > repository > test > AbstractQuery2Test


1 /*
2  * Copyright 2004 Outerthought bvba and Schaubroeck nv
3  *
4  * Licensed under the Apache License, Version 2.0 (the "License");
5  * you may not use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  * http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  */
16 package org.outerj.daisy.repository.test;
17
18 import org.outerj.daisy.repository.testsupport.AbstractDaisyTestCase;
19 import org.outerj.daisy.repository.*;
20 import org.outerj.daisy.repository.query.QueryManager;
21 import org.outerj.daisy.repository.query.QueryHelper;
22 import org.outerj.daisy.repository.query.EvaluationContext;
23 import org.outerj.daisy.repository.user.UserManager;
24 import org.outerj.daisy.repository.user.User;
25 import org.outerj.daisy.repository.user.Role;
26 import org.outerj.daisy.repository.schema.RepositorySchema;
27 import org.outerj.daisy.repository.schema.PartType;
28 import org.outerj.daisy.repository.schema.FieldType;
29 import org.outerj.daisy.repository.schema.DocumentType;
30 import org.outerx.daisy.x10.SearchResultDocument;
31 import org.outerx.daisy.x10.LinkValueType;
32
33 import java.math.BigDecimal ;
34 import java.util.Locale ;
35 import java.util.Date ;
36 import java.util.Calendar ;
37 import java.util.GregorianCalendar ;
38 import java.text.DateFormat ;
39
40 /**
41  * Additional query tests: test of all identifiers, functions, other constructs.
42  */
43 public abstract class AbstractQuery2Test extends AbstractDaisyTestCase {
44
45     public void testQuery() throws Exception {
46         RepositoryManager repositoryManager = getRepositoryManager();
47         Repository repository = repositoryManager.getRepository(new Credentials("testuser", "testuser"));
48         repository.switchRole(Role.ADMINISTRATOR);
49
50         UserManager userManager = repository.getUserManager();
51         User user1 = userManager.createUser("user1");
52         user1.setPassword("user1");
53         user1.setFirstName("User1First");
54         user1.setLastName("User1Last");
55         user1.addToRole(userManager.getRole(1, false));
56         user1.save();
57         User user2 = userManager.createUser("user2");
58         user2.setPassword("user2");
59         user2.setFirstName("User2First");
60         user2.setLastName("User2Last");
61         user2.addToRole(userManager.getRole(1, false));
62         user2.save();
63
64         CollectionManager collectionManager = repository.getCollectionManager();
65         DocumentCollection collection1 = collectionManager.createCollection("collection 1");
66         collection1.save();
67         DocumentCollection collection2 = collectionManager.createCollection("collection 2");
68         collection2.save();
69
70         RepositorySchema schema = repository.getRepositorySchema();
71         PartType partType = schema.createPartType("HtmlPart", "");
72         partType.setDaisyHtml(true);
73         partType.save();
74
75         FieldType fieldType1 = schema.createFieldType("StringField", ValueType.STRING);
76         fieldType1.save();
77         FieldType fieldType2 = schema.createFieldType("DecimalField1", ValueType.DECIMAL);
78         fieldType2.save();
79         FieldType fieldType2b = schema.createFieldType("DecimalField2", ValueType.DECIMAL);
80         fieldType2b.save();
81         FieldType fieldType3 = schema.createFieldType("MVField", ValueType.STRING, true);
82         fieldType3.save();
83         FieldType fieldType4 = schema.createFieldType("LinkField", ValueType.LINK);
84         fieldType4.save();
85         FieldType fieldType5 = schema.createFieldType("MVLinkField", ValueType.LINK, true);
86         fieldType5.save();
87
88         DocumentType docType = schema.createDocumentType("DocType");
89         docType.addPartType(partType, false);
90         docType.addFieldType(fieldType1, false);
91         docType.addFieldType(fieldType2, false);
92         docType.addFieldType(fieldType2b, false);
93         docType.addFieldType(fieldType3, false);
94         docType.addFieldType(fieldType4, false);
95         docType.addFieldType(fieldType5, false);
96         docType.save();
97
98         Repository user1Repo = repositoryManager.getRepository(new Credentials("user1", "user1"));
99         Repository user2Repo = repositoryManager.getRepository(new Credentials("user2", "user2"));
100
101         Document document = user1Repo.createDocument("My Document", "DocType");
102         document.setPart("HtmlPart", "text/xml", "<html><body><p>hello!</p></body></html>".getBytes("UTF-8"));
103         document.setField("StringField", "abc");
104         document.setField("DecimalField1", new BigDecimal ("123.45"));
105         document.setField("DecimalField2", new BigDecimal ("300.89"));
106         document.setField("MVField", new String [] {"aaa", "bbb"});
107         document.setField("LinkField", new VariantKey(5, -1, -1));
108         document.setField("MVLinkField", new VariantKey[] { new VariantKey(20, 1, 1), new VariantKey(21, 1, -1) });
109         document.addToCollection(collection1);
110         document.save();
111
112         // create a draft last version
113 document = user2Repo.getDocument(document.getId(), true);
114         document.setName("My Document updated");
115         document.setPart("HtmlPart", "text/xml", "<html><body><p>hello, world!</p></body></html>".getBytes("UTF-8"));
116         document.setField("StringField", "uyt");
117         document.setField("DecimalField1", new BigDecimal ("100"));
118         document.setField("DecimalField2", new BigDecimal ("200"));
119         document.setField("MVField", new String [] {"aaa", "bbb", "ccc"});
120         document.addToCollection(collection2);
121         document.setNewVersionState(VersionState.DRAFT);
122         document.save();
123
124         document.lock(1000000, LockType.PESSIMISTIC);
125
126         //
127 //
128 // Test all identifiers
129 // - for live and last version
130 // - in the 'select' part (evaluated by the repository server) and the where clause (evaluated by the SQL engine)
131 //
132 //
133 QueryManager queryManager = repository.getQueryManager();
134         SearchResultDocument result;
135         DateFormat dateTimeFormat = DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.MEDIUM, Locale.US);
136         DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.SHORT, Locale.US);
137
138         // id
139 result = queryManager.performQuery("select id where id = " + document.getId(), Locale.US);
140         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), String.valueOf(document.getId()));
141
142         result = queryManager.performQuery("select id where id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
143         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), String.valueOf(document.getId()));
144
145         // documentType
146 result = queryManager.performQuery("select documentType where documentType = 'DocType' and id = " + document.getId(), Locale.US);
147         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "DocType");
148
149         result = queryManager.performQuery("select documentType where documentType = 'DocType' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
150         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "DocType");
151
152         // name
153 result = queryManager.performQuery("select name where name = 'My Document' and id = " + document.getId(), Locale.US);
154         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "My Document");
155
156         result = queryManager.performQuery("select name where name = 'My Document updated' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
157         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "My Document updated");
158
159         // creationTime
160 result = queryManager.performQuery("select creationTime where creationTime = " + QueryHelper.formatDateTime(document.getCreated()) + " and id = " + document.getId(), Locale.US);
161         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), dateTimeFormat.format(document.getCreated()));
162
163         result = queryManager.performQuery("select creationTime where creationTime = " + QueryHelper.formatDateTime(document.getCreated()) + " and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
164         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), dateTimeFormat.format(document.getCreated()));
165
166         // summary (not version dependent)
167 result = queryManager.performQuery("select summary where id = " + document.getId(), Locale.US);
168         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "hello!");
169
170         result = queryManager.performQuery("select summary where id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
171         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "hello!");
172
173         // versionCreationTime
174 result = queryManager.performQuery("select versionCreationTime where versionCreationTime = " + QueryHelper.formatDateTime(document.getLiveVersion().getCreated()) + " and id = " + document.getId(), Locale.US);
175         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), dateTimeFormat.format(document.getLiveVersion().getCreated()));
176
177         result = queryManager.performQuery("select versionCreationTime where versionCreationTime = " + QueryHelper.formatDateTime(document.getLastVersion().getCreated()) + " and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
178         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), dateTimeFormat.format(document.getLastVersion().getCreated()));
179
180         // versionCreatorId
181 result = queryManager.performQuery("select versionCreatorId where versionCreatorId = " + user1.getId() + " and id = " + document.getId(), Locale.US);
182         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), String.valueOf(user1.getId()));
183
184         result = queryManager.performQuery("select versionCreatorId where versionCreatorId = " + user2.getId() + " and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
185         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), String.valueOf(user2.getId()));
186
187         // versionCreatorName
188 result = queryManager.performQuery("select versionCreatorName where id = " + document.getId(), Locale.US);
189         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "User1First User1Last");
190
191         result = queryManager.performQuery("select versionCreatorName where id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
192         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "User2First User2Last");
193
194         // versionCreatorLogin
195 result = queryManager.performQuery("select versionCreatorLogin where versionCreatorLogin = 'user1' and id = " + document.getId(), Locale.US);
196         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "user1");
197
198         result = queryManager.performQuery("select versionCreatorLogin where versionCreatorLogin = 'user2' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
199         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "user2");
200
201         // versionState
202 result = queryManager.performQuery("select versionState where versionState = 'publish' and id = " + document.getId(), Locale.US);
203         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "publish");
204
205         result = queryManager.performQuery("select versionState where versionState = 'publish' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
206         assertEquals(result.getSearchResult().getRows().getRowArray().length, 0);
207
208         result = queryManager.performQuery("select versionState where versionState = 'draft' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
209         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "draft");
210
211         // totalSizeOfParts
212 result = queryManager.performQuery("select totalSizeOfParts where totalSizeOfParts < 1000 and id = " + document.getId(), Locale.US);
213         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "39");
214
215         result = queryManager.performQuery("select totalSizeOfParts where totalSizeOfParts < 1000 and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
216         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "46");
217
218         // versionStateLastModified
219 result = queryManager.performQuery("select versionStateLastModified where versionStateLastModified <= " + QueryHelper.formatDateTime(new Date ()) + " and id = " + document.getId(), Locale.US);
220         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), dateTimeFormat.format(document.getLiveVersion().getStateLastModified()));
221
222         result = queryManager.performQuery("select versionStateLastModified where versionStateLastModified <= " + QueryHelper.formatDateTime(new Date ()) + " and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
223         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), dateTimeFormat.format(document.getLastVersion().getStateLastModified()));
224
225         // retired
226 result = queryManager.performQuery("select retired where retired = 'false' and id = " + document.getId(), Locale.US);
227         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "false");
228
229         result = queryManager.performQuery("select retired where retired = 'false' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
230         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "false");
231
232         // private
233 result = queryManager.performQuery("select private where private = 'false' and id = " + document.getId(), Locale.US);
234         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "false");
235
236         result = queryManager.performQuery("select private where private = 'false' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
237         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "false");
238
239         // lockType
240 result = queryManager.performQuery("select lockType where lockType = 'pessimistic' and id = " + document.getId(), Locale.US);
241         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "pessimistic");
242
243         result = queryManager.performQuery("select lockType where lockType = 'pessimistic' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
244         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "pessimistic");
245
246         // lockOwnerId
247 result = queryManager.performQuery("select lockOwnerId where lockOwnerId = " + user2.getId() + " and id = " + document.getId(), Locale.US);
248         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), String.valueOf(user2.getId()));
249
250         result = queryManager.performQuery("select lockOwnerId where lockOwnerId = " + user2.getId() + " and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
251         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), String.valueOf(user2.getId()));
252
253         // lockOwnerLogin
254 result = queryManager.performQuery("select lockOwnerLogin where lockOwnerLogin = 'user2' and id = " + document.getId(), Locale.US);
255         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "user2");
256
257         result = queryManager.performQuery("select lockOwnerLogin where lockOwnerLogin = 'user2' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
258         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "user2");
259
260         // lockOwnerName
261 result = queryManager.performQuery("select lockOwnerName where lockOwnerLogin = 'user2' and id = " + document.getId(), Locale.US);
262         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "User2First User2Last");
263
264         result = queryManager.performQuery("select lockOwnerName where lockOwnerLogin = 'user2' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
265         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "User2First User2Last");
266
267         // lockDuration
268 result = queryManager.performQuery("select lockDuration where lockDuration = 1000000 and id = " + document.getId(), Locale.US);
269         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "1000000");
270
271         result = queryManager.performQuery("select lockDuration where lockDuration = 1000000 and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
272         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "1000000");
273
274         // lockTimeAcquired
275 queryManager.performQuery("select lockTimeAcquired where lockTimeAcquired <= " + QueryHelper.formatDateTime(new Date ()) + " and id = " + document.getId(), Locale.US);
276         queryManager.performQuery("select lockTimeAcquired where lockTimeAcquired <= " + QueryHelper.formatDateTime(new Date ()) + " and id = " + document.getId() + "option search_last_version = 'true'", Locale.US);
277
278         // ownerId
279 result = queryManager.performQuery("select ownerId where ownerId = " + user1.getId() + " and id = " + document.getId(), Locale.US);
280         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), String.valueOf(user1.getId()));
281
282         result = queryManager.performQuery("select ownerId where ownerId = " + user1.getId() + " and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
283         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), String.valueOf(user1.getId()));
284
285         // ownerLogin
286 result = queryManager.performQuery("select ownerLogin where ownerLogin = 'user1' and id = " + document.getId(), Locale.US);
287         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "user1");
288
289         result = queryManager.performQuery("select ownerLogin where ownerLogin = 'user1' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
290         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "user1");
291
292         // ownerName
293 result = queryManager.performQuery("select ownerName where id = " + document.getId(), Locale.US);
294         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "User1First User1Last");
295
296         result = queryManager.performQuery("select ownerName where id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
297         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "User1First User1Last");
298
299         // lastModifierId
300 result = queryManager.performQuery("select lastModifierId where lastModifierId = " + user1.getId() + " and id = " + document.getId(), Locale.US);
301         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), String.valueOf(user1.getId()));
302
303         result = queryManager.performQuery("select lastModifierId where lastModifierId = " + user1.getId() + " and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
304         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), String.valueOf(user1.getId()));
305
306         // lastModifierLogin
307 result = queryManager.performQuery("select lastModifierLogin where lastModifierLogin = 'user1' and id = " + document.getId(), Locale.US);
308         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "user1");
309
310         result = queryManager.performQuery("select lastModifierLogin where lastModifierLogin = 'user1' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
311         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "user1");
312
313         // lastModifierName
314 result = queryManager.performQuery("select lastModifierName where id = " + document.getId(), Locale.US);
315         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "User1First User1Last");
316
317         result = queryManager.performQuery("select lastModifierName where id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
318         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "User1First User1Last");
319
320         // branchId
321 result = queryManager.performQuery("select branchId where branchId = 1 and id = " + document.getId(), Locale.US);
322         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "1");
323
324         result = queryManager.performQuery("select branchId where branchId = 1 and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
325         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "1");
326
327         // branchName
328 result = queryManager.performQuery("select branch where branch = 'main' and id = " + document.getId(), Locale.US);
329         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "main");
330
331         result = queryManager.performQuery("select branch where branch = 'main' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
332         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "main");
333
334         // languageId
335 result = queryManager.performQuery("select languageId where languageId = 1 and id = " + document.getId(), Locale.US);
336         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "1");
337
338         result = queryManager.performQuery("select languageId where languageId = 1 and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
339         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "1");
340
341         // languageName
342 result = queryManager.performQuery("select language where language = 'default' and id = " + document.getId(), Locale.US);
343         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "default");
344
345         result = queryManager.performQuery("select language where language = 'default' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
346         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "default");
347
348         // variantLastModified
349 result = queryManager.performQuery("select variantLastModified where variantLastModified = " + QueryHelper.formatDateTime(document.getVariantLastModified()) + " and id = " + document.getId(), Locale.US);
350         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), dateTimeFormat.format(document.getVariantLastModified()));
351
352         result = queryManager.performQuery("select variantLastModified where variantLastModified = " + QueryHelper.formatDateTime(document.getVariantLastModified()) + " and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
353         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), dateTimeFormat.format(document.getVariantLastModified()));
354
355         // variantLastMofifierId
356 result = queryManager.performQuery("select variantLastModifierId where variantLastModifierId = " + user2.getId() + " and id = " + document.getId(), Locale.US);
357         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), String.valueOf(user2.getId()));
358
359         result = queryManager.performQuery("select variantLastModifierId where variantLastModifierId = " + user2.getId() + " and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
360         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), String.valueOf(user2.getId()));
361
362         // variantLastModifierLogin
363 result = queryManager.performQuery("select variantLastModifierLogin where variantLastModifierLogin = 'user2' and id = " + document.getId(), Locale.US);
364         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "user2");
365
366         result = queryManager.performQuery("select variantLastModifierLogin where variantLastModifierLogin = 'user2' and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
367         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "user2");
368
369         // variantLastModifierName
370 result = queryManager.performQuery("select variantLastModifierName where id = " + document.getId(), Locale.US);
371         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "User2First User2Last");
372
373         result = queryManager.performQuery("select variantLastModifierName where id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
374         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "User2First User2Last");
375
376         // collections
377 result = queryManager.performQuery("select collections where collections has exactly ('collection 1', 'collection 2') and id = " + document.getId(), Locale.US);
378         assertEquals(result.getSearchResult().getRows().getRowArray(0).getMultiValueArray(0).getValueArray(1), "collection 1");
379         assertEquals(result.getSearchResult().getRows().getRowArray(0).getMultiValueArray(0).getValueArray(0), "collection 2");
380
381         result = queryManager.performQuery("select collections where collections has exactly ('collection 1', 'collection 2') and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
382         assertEquals(result.getSearchResult().getRows().getRowArray(0).getMultiValueArray(0).getValueArray(1), "collection 1");
383         assertEquals(result.getSearchResult().getRows().getRowArray(0).getMultiValueArray(0).getValueArray(0), "collection 2");
384
385         // collections.valueCount
386 result = queryManager.performQuery("select collections.valueCount where collections.valueCount = 2 and id = " + document.getId(), Locale.US);
387         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "2");
388
389         result = queryManager.performQuery("select collections.valueCount where collections.valueCount = 2 and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
390         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "2");
391
392         // versionId
393 result = queryManager.performQuery("select versionId where versionId = 1 and id = " + document.getId(), Locale.US);
394         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "1");
395
396         result = queryManager.performQuery("select versionId where versionId = 2 and id = " + document.getId() + " option search_last_version = 'true'", Locale.US);
397         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "2");
398
399         // link field identifiers
400 result = queryManager.performQuery("select $LinkField.documentId, $LinkField.branch, $LinkField.branchId, $LinkField.language, $LinkField.languageId where $LinkField.documentId = 5 and $LinkField.branchId = 1 and $LinkField.branch = 'main' and $LinkField.languageId = 1 and $LinkField.language = 'default'", Locale.US);
401         assertEquals(result.getSearchResult().getRows().getRowArray().length, 1);
402
403         result = queryManager.performQuery("select $MVLinkField.documentId, $MVLinkField.branch, $MVLinkField.branchId, $MVLinkField.language, $MVLinkField.languageId where $MVLinkField.documentId = 20 and $MVLinkField.branchId = 1 and $MVLinkField.branch = 'main' and $MVLinkField.languageId = 1 and $MVLinkField.language = 'default'", Locale.US);
404         assertEquals(result.getSearchResult().getRows().getRowArray().length, 1);
405
406         result = queryManager.performQuery("select $LinkField, $MVLinkField where $LinkField = 'daisy:5@1:1' and $LinkField = 'daisy:5@main:default' and $LinkField = 'daisy:5' and $MVLinkField has all ('daisy:20', 'daisy:21')", Locale.US);
407         assertEquals(result.getSearchResult().getRows().getRowArray().length, 1);
408         LinkValueType linkResult = result.getSearchResult().getRows().getRowArray(0).getLinkValueArray(0);
409         assertEquals(5, linkResult.getDocumentId());
410         assertEquals(1, linkResult.getBranchId());
411         assertEquals(1, linkResult.getLanguageId());
412
413         result = queryManager.performQuery("select link where link = 'daisy:1@1:1'", Locale.US);
414         assertEquals(result.getSearchResult().getRows().getRowArray().length, 1);
415
416         //
417 // Test functions
418 //
419
420         // basic math functions
421 result = queryManager.performQuery("select $DecimalField1 + $DecimalField2 where $DecimalField1 + $DecimalField2 = 424.34 and id = " + document.getId(), Locale.US);
422         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "424.34");
423
424         result = queryManager.performQuery("select $DecimalField1 - $DecimalField2 where $DecimalField1 - $DecimalField2 = -177.44 and id = " + document.getId(), Locale.US);
425         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "-177.44");
426
427         result = queryManager.performQuery("select $DecimalField1 / $DecimalField2 where $DecimalField1 / $DecimalField2 > 0.410 and $DecimalField1 / $DecimalField2 < 0.411 and id = " + document.getId(), Locale.US);
428         assertTrue("division function", result.getSearchResult().getRows().getRowArray(0).getValueArray(0).startsWith("0.41"));
429
430         result = queryManager.performQuery("select $DecimalField1 * $DecimalField2 where $DecimalField1 * $DecimalField2 > 37144.85 and $DecimalField1 * $DecimalField2 < 37144.95 and id = " + document.getId(), Locale.US);
431         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "37,144.87");
432
433         // precedence of operations and grouping
434 result = queryManager.performQuery("select 2 * 3 + 5 where 2 * 3 + 5 = 11 and id = " + document.getId(), Locale.US);
435         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "11");
436
437         result = queryManager.performQuery("select 5 + 3 * 2 where 5 + 3 * 2 = 11 and id = " + document.getId(), Locale.US);
438         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "11");
439
440         result = queryManager.performQuery("select 2 * (3 + 5) where 2 * (3 + 5) = 16 and id = " + document.getId(), Locale.US);
441         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "16");
442
443         result = queryManager.performQuery("select (5 + 3) * 2 where (5 + 3) * 2 = 16 and id = " + document.getId(), Locale.US);
444         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "16");
445
446         result = queryManager.performQuery("select 5 + 4 + 3 + 2 where 5 + 4 + 3 + 2 = 14 and id = " + document.getId(), Locale.US);
447         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "14");
448
449         // Random function
450 result = queryManager.performQuery("select Random(), Random() where Random() != Random() and id = " + document.getId(), Locale.US);
451         assertFalse(result.getSearchResult().getRows().getRowArray(0).getValueArray(0).equals(result.getSearchResult().getRows().getRowArray(0).getValueArray(1)));
452
453         // Mod function
454 result = queryManager.performQuery("select Mod(10, 4) where Mod(10, 4) = 2 and id = " + document.getId(), Locale.US);
455         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "2");
456
457         // Abs function
458 result = queryManager.performQuery("select Abs(-5) where Abs(-5) = 5 and id = " + document.getId(), Locale.US);
459         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "5");
460
461         // Floor function
462 result = queryManager.performQuery("select Floor(5.22) where Floor(5.22) = 5 and id = " + document.getId(), Locale.US);
463         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "5");
464
465         // Ceiling function
466 result = queryManager.performQuery("select Ceiling(5.22) where Ceiling(5.22) = 6 and id = " + document.getId(), Locale.US);
467         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "6");
468
469         // Round function
470 result = queryManager.performQuery("select Round(5.22, 1), Round(5.28, 1), Round(5.25, 1) where Round(5.22, 1) = 5.2 and Round(5.28, 1) = 5.3 and Round(5.25, 1) = 5.2 and id = " + document.getId(), Locale.US);
471         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "5.2");
472         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(1), "5.3");
473         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(2), "5.2");
474
475         // Length function
476 result = queryManager.performQuery("select Length(name) where Length(name) = 11 and id = " + document.getId(), Locale.US);
477         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "11");
478
479         // Concat function
480 result = queryManager.performQuery("select Length(Concat(name, 'abc')), Concat(name, 'abc') where Length(Concat(name, 'abc')) = 14 and id = " + document.getId(), Locale.US);
481         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "14");
482         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(1), "My Documentabc");
483
484         // Left function
485 result = queryManager.performQuery("select Left(name, 2) where id = " + document.getId(), Locale.US);
486         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "My");
487
488         result = queryManager.performQuery("select Left(name, 200) where id = " + document.getId(), Locale.US);
489         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "My Document");
490
491         result = queryManager.performQuery("select Left(name, 0) where id = " + document.getId(), Locale.US);
492         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "");
493
494         result = queryManager.performQuery("select name where Left(name, 2) = 'My' and id = " + document.getId(), Locale.US);
495         assertEquals(result.getSearchResult().getRows().sizeOfRowArray(), 1);
496
497         // Right function
498 result = queryManager.performQuery("select Right(name, 2) where id = " + document.getId(), Locale.US);
499         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "nt");
500
501         result = queryManager.performQuery("select Right(name, 200) where id = " + document.getId(), Locale.US);
502         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "My Document");
503
504         result = queryManager.performQuery("select name where Right(name, 2) = 'nt' and id = " + document.getId(), Locale.US);
505         assertEquals(result.getSearchResult().getRows().sizeOfRowArray(), 1);
506
507         result = queryManager.performQuery("select name where Left(Right(name, 2), 1) = 'n' and id = " + document.getId(), Locale.US);
508         assertEquals(result.getSearchResult().getRows().sizeOfRowArray(), 1);
509
510         // Substring function
511 result = queryManager.performQuery("select Substring(name, 4, 4) where id = " + document.getId(), Locale.US);
512         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "Docu");
513
514         result = queryManager.performQuery("select Substring(name, 1, 5000) where id = " + document.getId(), Locale.US);
515         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "My Document");
516
517         result = queryManager.performQuery("select name where Substring(name, 4, 4) = 'Docu' and id = " + document.getId(), Locale.US);
518         assertEquals(result.getSearchResult().getRows().sizeOfRowArray(), 1);
519
520         // UpperCase function
521 result = queryManager.performQuery("select UpperCase(name) where id = " + document.getId(), Locale.US);
522         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "MY DOCUMENT");
523
524         // note: SQL doesn't search case sensitive so this just tests the SQL generation works ok
525 result = queryManager.performQuery("select name where UpperCase(name) = 'MY DOCUMENT' and id = " + document.getId(), Locale.US);
526         assertEquals(result.getSearchResult().getRows().sizeOfRowArray(), 1);
527
528         // LowerCase function
529 result = queryManager.performQuery("select LowerCase(name) where id = " + document.getId(), Locale.US);
530         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "my document");
531
532         // note: SQL doesn't search case sensitive so this just tests the SQL generation works ok
533 result = queryManager.performQuery("select name where LowerCase(name) = 'my document' and id = " + document.getId(), Locale.US);
534         assertEquals(result.getSearchResult().getRows().sizeOfRowArray(), 1);
535
536         // CurrentDate function
537 Date nowDate = getDate();
538         result = queryManager.performQuery("select CurrentDate() where CurrentDate() = CurrentDate() and id = " + document.getId(), Locale.US);
539         assertTrue(dateFormat.parse(result.getSearchResult().getRows().getRowArray(0).getValueArray(0)).compareTo(nowDate) == 0);
540
541         // CurrentDateTime function
542 Date nowDateTime = getDateTime();
543         result = queryManager.performQuery("select CurrentDateTime() where lastModified < CurrentDateTime() and id = " + document.getId(), Locale.US);
544         System.out.println(dateTimeFormat.parse(result.getSearchResult().getRows().getRowArray(0).getValueArray(0)) + " and " + nowDateTime);
545         assertTrue(dateTimeFormat.parse(result.getSearchResult().getRows().getRowArray(0).getValueArray(0)).compareTo(nowDateTime) >= 0);
546
547         // ContextDoc function
548 final Document contextDoc = document;
549         final Version contextVersion = document.getLiveVersion();
550         EvaluationContext evaluationContext = new EvaluationContext();
551         evaluationContext.setContextDocument(contextDoc, contextVersion);
552         result = queryManager.performQuery("select ContextDoc(branch), ContextDoc(language) where ContextDoc(branch) = branch and ContextDoc(language) = language and id = " + document.getId(), Locale.US, evaluationContext);
553         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), "main");
554         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(1), "default");
555
556         // Year function
557 String currentYear = String.valueOf(new GregorianCalendar (Locale.US).get(Calendar.YEAR));
558         result = queryManager.performQuery("select Year(CurrentDate()) where Year(CurrentDate()) = " + currentYear + " and id = " + document.getId(), Locale.US);
559         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), currentYear);
560
561         // Month function
562 String currentMonth = String.valueOf(new GregorianCalendar (Locale.US).get(Calendar.MONTH) + 1);
563         result = queryManager.performQuery("select Month(CurrentDate()) where Month(CurrentDate()) = " + currentMonth + " and id = " + document.getId(), Locale.US);
564         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), currentMonth);
565
566         // Week function
567 String currentWeek = String.valueOf(new GregorianCalendar (Locale.US).get(Calendar.WEEK_OF_YEAR));
568         result = queryManager.performQuery("select Week(CurrentDate()) where Week(CurrentDate()) = " + currentWeek + " and id = " + document.getId(), Locale.US);
569         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), currentWeek);
570
571         // DayOfWeek function
572 String currentDayOfWeek = String.valueOf(new GregorianCalendar (Locale.US).get(Calendar.DAY_OF_WEEK));
573         result = queryManager.performQuery("select DayOfWeek(CurrentDate()) where DayOfWeek(CurrentDate()) = " + currentDayOfWeek + " and id = " + document.getId(), Locale.US);
574         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), currentDayOfWeek);
575
576         // DayOfMonth function
577 String currentDayOfMonth = String.valueOf(new GregorianCalendar (Locale.US).get(Calendar.DAY_OF_MONTH));
578         result = queryManager.performQuery("select DayOfMonth(CurrentDate()) where DayOfMonth(CurrentDate()) = " + currentDayOfMonth + " and id = " + document.getId(), Locale.US);
579         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), currentDayOfMonth);
580
581         // DayOfYear function
582 String currentDayOfYear = String.valueOf(new GregorianCalendar (Locale.US).get(Calendar.DAY_OF_YEAR));
583         result = queryManager.performQuery("select DayOfYear(CurrentDate()) where DayOfYear(CurrentDate()) = " + currentDayOfYear + " and id = " + document.getId(), Locale.US);
584         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), currentDayOfYear);
585
586         // CurrentDate function with argument
587 Calendar tenYearsAgo = new GregorianCalendar ();
588         tenYearsAgo.set(Calendar.HOUR_OF_DAY, 0);
589         tenYearsAgo.set(Calendar.MINUTE, 0);
590         tenYearsAgo.set(Calendar.SECOND, 0);
591         tenYearsAgo.set(Calendar.MILLISECOND, 0);
592         tenYearsAgo.add(Calendar.YEAR, -10);
593         result = queryManager.performQuery("select CurrentDate('- 10 years') where CurrentDate('- 10 years') = " + QueryHelper.formatDate(tenYearsAgo.getTime()) + " and id = " + document.getId(), Locale.US);
594         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), dateFormat.format(tenYearsAgo.getTime()));
595
596         // CurrentDateTime function
597 result = queryManager.performQuery("select CurrentDateTime('+1hours') where CurrentDateTime('+ 1 hours') > " + QueryHelper.formatDateTime(new Date ()) + " and id = " + document.getId(), Locale.US);
598         assertTrue(dateTimeFormat.parse(result.getSearchResult().getRows().getRowArray(0).getValueArray(0)).compareTo(new Date ()) > 0);
599
600         result = queryManager.performQuery("select RelativeDate('start next week') where RelativeDate('start next week') > " + QueryHelper.formatDate(new Date ()) + " and id = " + document.getId(), Locale.US);
601         assertTrue(dateFormat.parse(result.getSearchResult().getRows().getRowArray(0).getValueArray(0)).compareTo(new Date ()) > 0);
602
603         result = queryManager.performQuery("select RelativeDateTime('start this week') where RelativeDateTime('start this week') < " + QueryHelper.formatDateTime(new Date ()) + " and id = " + document.getId(), Locale.US);
604         assertTrue(dateTimeFormat.parse(result.getSearchResult().getRows().getRowArray(0).getValueArray(0)).compareTo(new Date ()) < 0);
605
606         // Test Year function with datetime arguments
607 Calendar lastModCal = new GregorianCalendar ();
608         lastModCal.setTime(document.getLastModified());
609         String lastModYear = String.valueOf(lastModCal.get(Calendar.YEAR));
610         result = queryManager.performQuery("select Year(lastModified), Year(" + QueryHelper.formatDateTime(document.getLastModified()) + ") where Year(lastModified) = " + lastModYear + " and id = " + document.getId(), Locale.US);
611         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(0), lastModYear);
612         assertEquals(result.getSearchResult().getRows().getRowArray(0).getValueArray(1), lastModYear);
613
614         // UserId function
615 result = user1Repo.getQueryManager().performQuery("select UserId() where ownerId = UserId()", Locale.US);
616         assertEquals(String.valueOf(user1Repo.getUserId()), result.getSearchResult().getRows().getRowArray(0).getValueArray(0));
617         assertTrue(result.getSearchResult().getRows().getRowArray().length > 0);
618     }
619
620     private Date getDate() {
621         Calendar calendar = new GregorianCalendar ();
622         calendar.set(Calendar.HOUR_OF_DAY, 0);
623         calendar.set(Calendar.MINUTE, 0);
624         calendar.set(Calendar.SECOND, 0);
625         calendar.set(Calendar.MILLISECOND, 0);
626         return calendar.getTime();
627     }
628
629     private Date getDateTime() {
630         Calendar calendar = new GregorianCalendar ();
631         calendar.set(Calendar.MILLISECOND, 0);
632         return calendar.getTime();
633     }
634
635     protected abstract RepositoryManager getRepositoryManager() throws Exception ;
636
637     protected boolean resetDataStores() {
638         return true;
639     }
640 }
641

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags