@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());
}
