@Test public void testExceptionManagement() { // issue #5244 OLiveCommandExecutorSQLFactory.init(); ODatabaseDocumentTx db = new ODatabaseDocumentTx("memory:ONonBlockingQueryTest"); db.activateOnCurrentThread(); db.create(); db.getMetadata().getSchema().createClass("test"); MyResultListener listener = new MyResultListener(); try { db.command(new OCommandSQL("insert into test set name = 'foo', surname = 'bar'")).execute(); db.query(new OSQLNonBlockingQuery<Object>("select from test bla blu", listener)); try { Thread.sleep(3000); } catch (InterruptedException e) { e.printStackTrace(); } Assert.assertEquals(listener.finished, true); listener = new MyResultListener(); db.query(new OSQLNonBlockingQuery<Object>("select from test", listener)); } finally { db.close(); } try { Thread.sleep(3000); } catch (InterruptedException e) { e.printStackTrace(); } Assert.assertEquals(listener.numResults, 1); }
@Test public void testParamWithMatchesQuoteRegex() { // issue #5229 Map<String, Object> params = new HashMap<String, Object>(); params.put("param1", ".*admin[name].*"); // will not work OSQLSynchQuery sql = new OSQLSynchQuery("select from matchesstuff where name matches :param1"); List<ODocument> results = db.query(sql, params); assertEquals(results.size(), 0); params.put("param1", Pattern.quote("admin[name]") + ".*"); // should work results = db.query(sql, params); assertEquals(results.size(), 1); }
public static void query() { System.out.println("Querying docs..."); // List<ODocument> result = database.query(new ONativeSynchQuery<ODocument, // OQueryContextNativeSchema<ODocument>>(database, // "Account", new OQueryContextNativeSchema<ODocument>()) { // @Override // public boolean filter(OQueryContextNativeSchema<ODocument> iRecord) { // return iRecord.field("id").eq(1000l).field("name").go(); // } // }); long start = System.currentTimeMillis(); List<ODocument> result = database.query(new OSQLSynchQuery<ODocument>("SELECT FROM Account WHERE id = " + 100999)); System.out.println("Elapsed: " + (System.currentTimeMillis() - start)); System.out.println("Query done"); for (ODocument o : result) { System.out.println("id=" + o.field("id") + "\tname=" + o.field("name")); } }
@Test public void testParamWithMatchesAndNot() { // issue #5229 Map<String, Object> params = new HashMap<String, Object>(); params.put("param1", "adm.*"); params.put("param2", "foo.*"); OSQLSynchQuery sql = new OSQLSynchQuery( "select from OUser where (name matches :param1 and not (name matches :param2))"); List<ODocument> results = db.query(sql, params); assertEquals(results.size(), 1); params.put("param1", Pattern.quote("adm") + ".*"); results = db.query(sql, params); assertEquals(results.size(), 1); }
@Test public void testLetOrder() { OSQLSynchQuery sql = new OSQLSynchQuery( "SELECT" + " source," + " $maxYear as maxYear" + " FROM" + " (" + " SELECT expand( $union ) " + " LET" + " $a = (SELECT 'A' as source, 2013 as year)," + " $b = (SELECT 'B' as source, 2012 as year)," + " $union = unionAll($a,$b) " + " ) " + " LET " + " $maxYear = max(year)" + " GROUP BY" + " source"); try { List<ODocument> results = db.query(sql); fail( "Invalid query, usage of LET, aggregate functions and GROUP BY together is not supported"); } catch (OCommandSQLParsingException x) { } }
@Test public void testLiveInsert() { OLiveCommandExecutorSQLFactory.init(); ODatabaseDocumentTx db = new ODatabaseDocumentTx("memory:OLiveQueryTest"); db.activateOnCurrentThread(); db.create(); db.registerHook(new OLiveQueryHook(db)); try { db.getMetadata().getSchema().createClass("test"); db.getMetadata().getSchema().createClass("test2"); MyLiveQueryListener listener = new MyLiveQueryListener(); db.query(new OLiveQuery<ODocument>("live select from test", listener)); db.command(new OCommandSQL("insert into test set name = 'foo', surname = 'bar'")).execute(); db.command(new OCommandSQL("insert into test set name = 'foo', surname = 'baz'")).execute(); db.command(new OCommandSQL("insert into test2 set name = 'foo'")); try { Thread.sleep(3000); } catch (InterruptedException e) { e.printStackTrace(); } Assert.assertEquals(listener.ops.size(), 2); for (ORecordOperation doc : listener.ops) { Assert.assertEquals(doc.type, ORecordOperation.CREATED); Assert.assertEquals(((ODocument) doc.record).field("name"), "foo"); } } finally { db.drop(); } }
@Test public void testMatchesWithQuotes() { // issue #5229 String pattern = Pattern.quote("adm") + ".*"; OSQLSynchQuery sql = new OSQLSynchQuery("SELECT FROM matchesstuff WHERE (name matches ?)"); List<ODocument> results = db.query(sql, pattern); assertEquals(results.size(), 1); }
@Test public void testSelectFromClusterNumber() { OClass clazz = db.getMetadata().getSchema().getClass("DistinctLimit"); int firstCluster = clazz.getClusterIds()[0]; OSQLSynchQuery sql = new OSQLSynchQuery("select from cluster:" + firstCluster + " limit 1"); List<ODocument> results = db.query(sql); assertEquals(results.size(), 1); }
@Test public void testMatches() { List<?> result = db.query( new OSQLSynchQuery<Object>( "select from foo where name matches '(?i)(^\\\\Qa\\\\E$)|(^\\\\Qname2\\\\E$)|(^\\\\Qname3\\\\E$)' and bar = 1")); assertEquals(result.size(), 1); }
@Test public void testBacktick() { OSQLSynchQuery sql = new OSQLSynchQuery("SELECT `foo-bar` as r from TestBacktick"); List<ODocument> results = db.query(sql); assertEquals(results.size(), 1); ODocument doc = results.get(0); assertEquals(doc.field("r"), 10); }
@Test public void testMatchesWithQuotes3() { // issue #5229 OSQLSynchQuery sql = new OSQLSynchQuery( "SELECT FROM matchesstuff WHERE (name matches '\\\\Qadm\\\\E.*' and ( name matches '\\\\Qadmin\\\\E.*' ) )"); List<ODocument> results = db.query(sql); assertEquals(results.size(), 1); }
@Test public void testParamWithMatches() { // issue #5229 Map<String, Object> params = new HashMap<String, Object>(); params.put("param1", "adm.*"); OSQLSynchQuery sql = new OSQLSynchQuery("select from OUser where name matches :param1"); List<ODocument> results = db.query(sql, params); assertEquals(results.size(), 1); }
@Test public void testDistinctLimit() { OSQLSynchQuery sql = new OSQLSynchQuery("select distinct(name) from DistinctLimit limit 1"); List<ODocument> results = db.query(sql); assertEquals(results.size(), 1); sql = new OSQLSynchQuery("select distinct(name) from DistinctLimit limit 2"); results = db.query(sql); assertEquals(results.size(), 2); sql = new OSQLSynchQuery("select distinct(name) from DistinctLimit limit 3"); results = db.query(sql); assertEquals(results.size(), 2); sql = new OSQLSynchQuery("select distinct(name) from DistinctLimit limit -1"); results = db.query(sql); assertEquals(results.size(), 2); }
@Test public void testLetAsListAsString() { OSQLSynchQuery sql = new OSQLSynchQuery( "SELECT $ll as lll from unwindtest let $ll = coll.asList().asString() where name = 'bar'"); List<ODocument> results = db.query(sql); assertEquals(1, results.size()); assertNotNull(results.get(0).field("lll")); assertEquals("[bar1, bar2]", results.get(0).field("lll")); }
@Test public void testDatesListContainsString() { // issue #3526 OSQLSynchQuery sql = new OSQLSynchQuery( "select from OCommandExecutorSQLSelectTest_datesSet where foo contains '2015-10-21'"); List<ODocument> results = db.query(sql); assertEquals(results.size(), 1); }
@Test public void testUpdateMergeWithIndex() { final ODatabaseDocumentTx db = new ODatabaseDocumentTx("memory:OCommandExecutorSQLUpdateTestMergeWithIndex"); db.create(); try { db.command(new OCommandSQL("CREATE CLASS i_have_a_list ")).execute(); db.command(new OCommandSQL("CREATE PROPERTY i_have_a_list.id STRING")).execute(); db.command(new OCommandSQL("CREATE INDEX i_have_a_list.id ON i_have_a_list (id) UNIQUE")) .execute(); db.command(new OCommandSQL("CREATE PROPERTY i_have_a_list.types EMBEDDEDLIST STRING")) .execute(); db.command( new OCommandSQL( "CREATE INDEX i_have_a_list.types ON i_have_a_list (types) NOTUNIQUE")) .execute(); db.command( new OCommandSQL( "INSERT INTO i_have_a_list CONTENT {\"id\": \"the_id\", \"types\": [\"aaa\", \"bbb\"]}")) .execute(); Iterable result = db.query(new OSQLSynchQuery<Object>("SELECT * FROM i_have_a_list WHERE types = 'aaa'")); assertTrue(result.iterator().hasNext()); db.command( new OCommandSQL( "UPDATE i_have_a_list CONTENT {\"id\": \"the_id\", \"types\": [\"ccc\", \"bbb\"]} WHERE id = 'the_id'")) .execute(); result = db.query(new OSQLSynchQuery<Object>("SELECT * FROM i_have_a_list WHERE types = 'ccc'")); assertTrue(result.iterator().hasNext()); result = db.query(new OSQLSynchQuery<Object>("SELECT * FROM i_have_a_list WHERE types = 'aaa'")); assertFalse(result.iterator().hasNext()); } finally { db.close(); } }
@Test public void testExpandSkipLimit() { // issue #4985 OSQLSynchQuery sql = new OSQLSynchQuery( "SELECT expand(linked) from ExpandSkipLimit where parent = true order by nnum skip 1 limit 1"); List<ODocument> results = db.query(sql); assertEquals(results.size(), 1); ODocument doc = results.get(0); assertEquals(doc.field("nnum"), 1); }
public List<Comment> getCommentsByCaseId(String caseId) { String request = "SELECT FROM Comment WHERE case = ? ORDER BY createDate asc"; try (ODatabaseDocumentTx database = getODatabaseDocumentTx()) { List<ODocument> docs = database.query(new OSQLSynchQuery<Comment>(request), caseId); List<Comment> comments = new ArrayList<>(docs.size()); for (ODocument doc : docs) { Comment comment = oDocumentToComment(doc); comments.add(comment); } return comments; } }
@Test public void testUpdateContentParse() throws Exception { final ODatabaseDocumentTx db = new ODatabaseDocumentTx("memory:OCommandExecutorSQLUpdateTestContentParse"); db.create(); try { db.command(new OCommandSQL("CREATE class V")).execute(); db.command(new OCommandSQL("insert into V (name) values ('bar')")).execute(); db.command(new OCommandSQL("UPDATE V content {\"value\":\"foo\\\\\"}")).execute(); Iterable result = db.query(new OSQLSynchQuery<Object>("select from V")); ODocument doc = (ODocument) result.iterator().next(); assertEquals(doc.field("value"), "foo\\"); db.command(new OCommandSQL("UPDATE V content {\"value\":\"foo\\\\\\\\\"}")).execute(); result = db.query(new OSQLSynchQuery<Object>("select from V")); doc = (ODocument) result.iterator().next(); assertEquals(doc.field("value"), "foo\\\\"); } finally { db.close(); } }
@Test public void testStarPosition() { List<ODocument> result = db.query(new OSQLSynchQuery<Object>("select *, name as blabla from foo where name = 'a'")); assertEquals(result.size(), 1); assertEquals(result.get(0).field("blabla"), "a"); result = db.query(new OSQLSynchQuery<Object>("select name as blabla, * from foo where name = 'a'")); assertEquals(result.size(), 1); assertEquals(result.get(0).field("blabla"), "a"); result = db.query( new OSQLSynchQuery<Object>( "select name as blabla, *, fff as zzz from foo where name = 'a'")); assertEquals(result.size(), 1); assertEquals(result.get(0).field("blabla"), "a"); }
@Test public void testAggregations() { OSQLSynchQuery sql = new OSQLSynchQuery( "select data.size as collection_content, data.size() as collection_size, min(data.size) as collection_min, max(data.size) as collection_max, sum(data.size) as collection_sum, avg(data.size) as collection_avg from OCommandExecutorSQLSelectTest_aggregations"); List<ODocument> results = db.query(sql); assertEquals(1, results.size()); ODocument doc = results.get(0); assertEquals(5, doc.field("collection_size")); assertEquals(130, doc.field("collection_sum")); assertEquals(26, doc.field("collection_avg")); assertEquals(0, doc.field("collection_min")); assertEquals(50, doc.field("collection_max")); }
protected void updateDocument( final int threadId, final int iCycle, final String dbUrl, final String className, final int iSkip) { final ODatabaseDocumentTx db = getDatabase(dbUrl); for (int retry = 0; retry < MAX_RETRY; ++retry) { ODocument doc = null; try { List<OIdentifiable> result = db.query( new OSQLSynchQuery<Object>( "select from " + className + " skip " + iSkip + " limit 1")); if (result == null || result.isEmpty()) log(threadId, iCycle, dbUrl, " update no item " + iSkip + " because out of range"); else { doc = (ODocument) result.get(0); doc.field("updated", "" + (doc.getVersion() + 1)); doc.save(); log(threadId, iCycle, dbUrl, " updated item " + iSkip + " RID=" + result.get(0)); } // OK break; } catch (OConcurrentModificationException e) { log( threadId, iCycle, dbUrl, " concurrent update against record " + doc + ", reload it and retry " + retry + "/" + MAX_RETRY + "..."); if (doc != null) doc.reload(null, true); } catch (ORecordNotFoundException e) { log(threadId, iCycle, dbUrl, " update no item " + iSkip + " because not found"); break; } finally { db.close(); } } }
@Test public void testMassiveOrderAscSkipLimit() { long begin = System.currentTimeMillis(); int skip = 1000; OSQLSynchQuery sql = new OSQLSynchQuery( "SELECT from MassiveOrderSkipLimit order by nnum asc skip " + skip + " limit 5"); List<ODocument> results = db.query(sql); assertEquals(results.size(), 5); System.out.println("elapsed: " + (System.currentTimeMillis() - begin)); for (int i = 0; i < results.size(); i++) { ODocument doc = results.get(i); assertEquals(doc.field("nnum"), skip + i); } }
private ODocument loadRecord(ODatabaseDocumentTx database, int i) { final String uniqueId = serverId + "-" + threadId + "-" + i; List<ODocument> result = database.query( new OSQLSynchQuery<ODocument>( "select from Person where name = 'Billy" + uniqueId + "'")); if (result.size() == 0) Assert.assertTrue("No record found with name = 'Billy" + uniqueId + "'!", false); else if (result.size() > 1) Assert.assertTrue( result.size() + " records found with name = 'Billy" + uniqueId + "'!", false); return result.get(0); }
@Test public void testIntersectExpandLet() { // issue #5121 OSQLSynchQuery sql = new OSQLSynchQuery( "select expand(intersect($q1, $q2)) " + "let $q1 = (select from OUser where name ='admin')," + "$q2 = (select from OUser where name ='admin')"); List<ODocument> results = db.query(sql); assertEquals(results.size(), 1); for (int i = 0; i < results.size(); i++) { ODocument doc = results.get(i); assertEquals(doc.field("name"), "admin"); } }
@Test public void testUpsertSetPut() throws Exception { final ODatabaseDocumentTx db = new ODatabaseDocumentTx("memory:OCommandExecutorSQLUpdateUpsertSetPut"); db.create(); try { db.command(new OCommandSQL("CREATE CLASS test")).execute(); db.command(new OCommandSQL("CREATE PROPERTY test.id integer")).execute(); db.command(new OCommandSQL("CREATE PROPERTY test.addField EMBEDDEDSET string")).execute(); db.command( new OCommandSQL("UPDATE test SET id = 1 ADD addField=\"xxxx\" UPSERT WHERE id = 1")) .execute(); Iterable result = db.query(new OSQLSynchQuery<Object>("select from test")); ODocument doc = (ODocument) result.iterator().next(); Set<?> set = doc.field("addField"); assertEquals(set.size(), 1); } finally { db.close(); } }
@Test public void testNullProjection() { OSQLSynchQuery sql = new OSQLSynchQuery( "SELECT 1 AS integer, 'Test' AS string, NULL AS nothing, [] AS array, {} AS object"); List<ODocument> results = db.query(sql); assertEquals(results.size(), 1); ODocument doc = results.get(0); assertEquals(doc.field("integer"), 1); assertEquals(doc.field("string"), "Test"); assertNull(doc.field("nothing")); boolean nullFound = false; for (String s : doc.fieldNames()) { if (s.equals("nothing")) { nullFound = true; break; } } assertTrue(nullFound); }
protected void queryClass( final int threadId, final int iCycle, final String dbUrl, final String className, final int iMax) { final ODatabaseDocumentTx db = getDatabase(dbUrl); try { log(threadId, iCycle, dbUrl, " query class=" + className); List<OIdentifiable> result = db.query(new OSQLSynchQuery<Object>("select from " + className)); int browsed = 0; for (OIdentifiable r : result) { if (browsed++ > iMax) return; r.getRecord().toString(); } } finally { db.close(); } }
@Test public void testUpdateRemoveAll() throws Exception { final ODatabaseDocumentTx db = new ODatabaseDocumentTx("memory:OCommandExecutorSQLUpdateTest"); db.create(); db.command(new OCommandSQL("CREATE class company")).execute(); db.command(new OCommandSQL("CREATE property company.name STRING")).execute(); db.command(new OCommandSQL("CREATE class employee")).execute(); db.command(new OCommandSQL("CREATE property employee.name STRING")).execute(); db.command(new OCommandSQL("CREATE property company.employees LINKSET employee")).execute(); db.command(new OCommandSQL("INSERT INTO company SET name = 'MyCompany'")).execute(); final ODocument r = (ODocument) db.query(new OSQLSynchQuery<Object>("SELECT FROM company")).get(0); db.command(new OCommandSQL("INSERT INTO employee SET name = 'Philipp'")).execute(); db.command(new OCommandSQL("INSERT INTO employee SET name = 'Selma'")).execute(); db.command(new OCommandSQL("INSERT INTO employee SET name = 'Thierry'")).execute(); db.command(new OCommandSQL("INSERT INTO employee SET name = 'Linn'")).execute(); db.command(new OCommandSQL("UPDATE company ADD employees = (SELECT FROM employee)")).execute(); r.reload(); assertEquals(((Set) r.field("employees")).size(), 4); db.command( new OCommandSQL( "UPDATE company REMOVE employees = (SELECT FROM employee WHERE name = 'Linn') WHERE name = 'MyCompany'")) .execute(); r.reload(); assertEquals(((Set) r.field("employees")).size(), 3); db.close(); }
@Test public void testSkipAndLimitMinusOneOrdered() { OSQLSynchQuery sql = new OSQLSynchQuery("SELECT from alphabet ORDER BY letter SKIP 7 LIMIT -1"); List<ODocument> results = db.query(sql); assertEquals(19, results.size()); }