Search in sources :

Example 1 with RowResult

use of com.mysql.cj.xdevapi.RowResult in project aws-mysql-jdbc by awslabs.

the class CollectionFindTest method testCollectionFindInDelete.

@SuppressWarnings("deprecation")
@Test
public void testCollectionFindInDelete() throws Exception {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
    int i = 0, maxrec = 10;
    DocResult docs = null;
    Result res = null;
    /* add(DbDoc[] docs) */
    DbDoc[] jsonlist = new DbDocImpl[maxrec];
    for (i = 0; i < maxrec; i++) {
        DbDoc newDoc2 = new DbDocImpl();
        newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
        newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
        newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
        newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
        newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 + i)));
        jsonlist[i] = newDoc2;
        newDoc2 = null;
    }
    this.collection.add(jsonlist).execute();
    assertEquals((maxrec), this.collection.count());
    /* find without Condition */
    docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
    i = 0;
    while (docs.hasNext()) {
        docs.next();
        i++;
    }
    assertEquals((maxrec), i);
    /* remove with single IN */
    res = this.collection.remove("'1001' in $._id").execute();
    assertEquals(1, res.getAffectedItemsCount());
    docs = this.collection.find("'1001' in $._id").execute();
    assertFalse(docs.hasNext());
    /* remove with mulltiple IN */
    String findCond = "";
    for (i = 1; i < maxrec; i++) {
        findCond = findCond + "'";
        findCond = findCond + String.valueOf(i + 1000) + "' not in $._id";
        if (i != maxrec - 1) {
            findCond = findCond + " and ";
        }
    }
    res = this.collection.remove(findCond).execute();
    assertEquals(1, res.getAffectedItemsCount());
    docs = this.collection.find(findCond).execute();
    assertFalse(docs.hasNext());
    /* remove with single IN */
    res = this.collection.remove("10004 in $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
    assertEquals(1, res.getAffectedItemsCount());
    docs = this.collection.find("10004 in $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
    assertFalse(docs.hasNext());
    /* remove with single IN for float */
    res = this.collection.remove("30.1234 in $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
    assertEquals(1, res.getAffectedItemsCount());
    docs = this.collection.find("30.1234 in $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
    assertFalse(docs.hasNext());
    res = this.collection.remove("true").execute();
    jsonlist = new DbDocImpl[maxrec];
    for (i = 0; i < maxrec; i++) {
        DbDoc newDoc2 = new DbDocImpl();
        newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
        newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
        newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
        newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
        newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 + i)));
        jsonlist[i] = newDoc2;
        newDoc2 = null;
    }
    this.collection.add(jsonlist).execute();
    assertEquals((maxrec), this.collection.count());
    Table table = this.schema.getCollectionAsTable(this.collectionName);
    /* delete with single IN */
    res = table.delete().where("'1001' in doc->$._id").execute();
    assertEquals(res.getAffectedItemsCount(), 1);
    RowResult rows = table.select("doc->$.F1 as _id").where("'1001' in doc->$._id").execute();
    assertFalse(rows.hasNext());
    /* delete with multiple IN */
    findCond = "";
    for (i = 1; i < maxrec; i++) {
        findCond = findCond + "'";
        findCond = findCond + String.valueOf(i + 1000) + "' not in doc->$._id";
        if (i != maxrec - 1) {
            findCond = findCond + " and ";
        }
    }
    res = table.delete().where(findCond).execute();
    assertEquals(res.getAffectedItemsCount(), 1);
    rows = table.select("doc->$.F1 as _id").where(findCond).execute();
    assertFalse(rows.hasNext());
    /* delete with single IN for float */
    res = table.delete().where("30.1234 in doc->$.F2").execute();
    assertEquals(res.getAffectedItemsCount(), 1);
    rows = table.select("doc->$.F1 as F1").where("30.1234 in doc->$.F2").execute();
    assertFalse(rows.hasNext());
    /* delete with single IN for int */
    res = table.delete().where("10004 in doc->$.F4").execute();
    assertEquals(res.getAffectedItemsCount(), 1);
    rows = table.select("doc->$.F1 as F1").where("10004 in doc->$.F4").execute();
    assertFalse(rows.hasNext());
}
Also used : DbDoc(com.mysql.cj.xdevapi.DbDoc) RowResult(com.mysql.cj.xdevapi.RowResult) DbDocImpl(com.mysql.cj.xdevapi.DbDocImpl) Table(com.mysql.cj.xdevapi.Table) JsonNumber(com.mysql.cj.xdevapi.JsonNumber) JsonString(com.mysql.cj.xdevapi.JsonString) JsonString(com.mysql.cj.xdevapi.JsonString) DocResult(com.mysql.cj.xdevapi.DocResult) RowResult(com.mysql.cj.xdevapi.RowResult) Result(com.mysql.cj.xdevapi.Result) AddResult(com.mysql.cj.xdevapi.AddResult) DocResult(com.mysql.cj.xdevapi.DocResult) SqlResult(com.mysql.cj.xdevapi.SqlResult) Test(org.junit.jupiter.api.Test)

Example 2 with RowResult

use of com.mysql.cj.xdevapi.RowResult in project aws-mysql-jdbc by awslabs.

the class CollectionFindTest method testCollectionFindOverlaps.

@Test
public void testCollectionFindOverlaps() throws Exception {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
    try {
        int i = 0, j = 0, maxrec = 8, minArraySize = 3;
        DbDoc doc = null;
        DocResult docs = null;
        long l3 = 2147483647;
        double d1 = 1000.1234;
        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));
            JsonArray jarray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                jarray.addValue(new JsonNumber().setValue(String.valueOf((l3 + j + i))));
            }
            newDoc2.add("ARR1", jarray);
            JsonArray karray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                karray.addValue(new JsonNumber().setValue(String.valueOf((d1 + j + i))));
            }
            newDoc2.add("ARR2", karray);
            JsonArray larray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                larray.addValue(new JsonString().setValue("St_" + i + "_" + j));
            }
            newDoc2.add("ARR3", larray);
            this.collection.add(newDoc2).execute();
            newDoc2 = null;
            jarray = null;
        }
        assertEquals((maxrec), this.collection.count());
        /* find with single OVERLAPS in array */
        docs = this.collection.find("2147483647 overlaps $.ARR1").execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());
        Table table = this.schema.getCollectionAsTable(this.collectionName);
        RowResult rows = table.select("doc->$._id as _id").where("2147483647 overlaps $.ARR1").execute();
        Row r = rows.next();
        assertEquals("\"1000\"", r.getString("_id"));
        assertFalse(rows.hasNext());
        /* find with array OVERLAPS array */
        docs = this.collection.find("[2147483647, 2147483648, 2147483649] overlaps $.ARR1").execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());
        rows = table.select("doc->$._id as _id").where("[2147483647, 2147483648, 2147483649] overlaps $.ARR1").execute();
        r = rows.next();
        assertEquals("\"1000\"", r.getString("_id"));
        r = rows.next();
        assertEquals("\"1001\"", r.getString("_id"));
        r = rows.next();
        assertEquals("\"1002\"", r.getString("_id"));
        assertFalse(rows.hasNext());
        /* find with array OVERLAPS array with orderBy */
        docs = this.collection.find("[2147483648, 2147483648, 2147483649] overlaps $.ARR1").orderBy("_id").execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());
        /* */
        docs = this.collection.find("[!false && true] OVERLAPS [true]").execute();
        assertEquals(maxrec, docs.count());
        /* Overlaps with NULL */
        docs = this.collection.find("NULL overlaps $.ARR1").execute();
        assertFalse(docs.hasNext());
        rows = table.select("doc->$._id as _id").where("NULL overlaps $.ARR1").execute();
        assertFalse(rows.hasNext());
        docs = this.collection.find("$.ARR1 overlaps null").execute();
        assertFalse(docs.hasNext());
        rows = table.select("doc->$._id as _id").where("$.ARR1 overlaps NULL").execute();
        assertFalse(rows.hasNext());
        /* Not Overlaps with NULL */
        docs = this.collection.find("NULL not overlaps $.ARR1").execute();
        assertTrue(docs.hasNext());
        assertEquals(maxrec, docs.count());
        rows = table.select("doc->$._id as _id").where("NULL not overlaps $.ARR1").execute();
        assertTrue(rows.hasNext());
        assertEquals(maxrec, docs.count());
        docs = this.collection.find("$.ARR1 not overlaps null").execute();
        assertTrue(docs.hasNext());
        assertEquals(maxrec, docs.count());
        rows = table.select("doc->$._id as _id").where("$.ARR1 not overlaps null").execute();
        assertTrue(rows.hasNext());
        assertEquals(maxrec, docs.count());
        /* Test OVERLAPS/NOT OVERLAPS with empty array - Expected to pass, though the array is empty but still valid */
        // checking the case insensitivity as well
        docs = this.collection.find("[] Overlaps $.ARR1").execute();
        assertFalse(docs.hasNext());
        rows = table.select().where("[] ovErlaps $.ARR1").execute();
        assertFalse(rows.hasNext());
        // checking the case insensitivity as well
        docs = this.collection.find("$.ARR1 overlapS []").execute();
        assertFalse(docs.hasNext());
        rows = table.select().where("$.ARR1 ovErlaps []").execute();
        assertFalse(rows.hasNext());
        docs = this.collection.find("[] not overlaps $.ARR1").execute();
        assertTrue(docs.hasNext());
        assertEquals(maxrec, docs.count());
        rows = table.select().where("[] not overlaps $.ARR1").execute();
        assertTrue(rows.hasNext());
        assertEquals(maxrec, docs.count());
        // checking the case insensitivity as well
        docs = this.collection.find("$.ARR1 not oveRlaps []").execute();
        assertTrue(docs.hasNext());
        assertEquals(maxrec, docs.count());
        rows = table.select().where("$.ARR1 not overlaps []").execute();
        assertTrue(rows.hasNext());
        assertEquals(maxrec, docs.count());
        /* When the right number of operands are not provided - error should be thrown */
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> this.collection.find("overlaps $.ARR1").execute());
        assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 4", () -> this.collection.find("$.ARR1 OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> this.collection.find("OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> this.collection.find("not overlaps $.ARR1").execute());
        assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 5", () -> this.collection.find("$.ARR1 NOT OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> this.collection.find("not OVERLAPS").execute());
        final Table table1 = this.schema.getCollectionAsTable(this.collectionName);
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> table1.select().where("overlaps $.ARR1").execute());
        assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 4", () -> table1.select().where("$.ARR1 OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> table1.select().where("OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> table1.select().where("not overlaps $.ARR1").execute());
        assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 5", () -> table1.select().where("$.ARR1 NOT OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> table1.select().where("not OVERLAPS").execute());
        /* invalid criteria, e.g. .find("[1, 2, 3] OVERLAPS $.age") . where $.age is atomic value */
        dropCollection("coll2");
        Collection coll2 = this.schema.createCollection("coll2", true);
        coll2.add("{ \"_id\": \"1\", \"name\": \"nonjson\", \"age\": \"50\",\"arrayField\":[1,[7]]}").execute();
        // The below command should give exception, but X-plugin doesn't return any error
        docs = coll2.find("[1,2,3] overlaps $.age").execute();
        assertEquals(0, docs.count());
        docs = coll2.find("arrayField OVERLAPS [7]").execute();
        assertEquals(0, docs.count());
        docs = coll2.find("arrayField[1] OVERLAPS [7]").execute();
        assertEquals(1, docs.count());
        table = this.schema.getCollectionAsTable("coll2");
        rows = table.select().where("[1,2,3] overlaps $.age").execute();
        assertEquals(0, rows.count());
        /* Test with empty spaces */
        dropCollection("coll3");
        Collection coll3 = this.schema.createCollection("coll3", true);
        // List contains an array without any space
        coll3.add("{ \"_id\":1, \"name\": \"Record1\",\"list\":[\"\"], \"age\":15, \"intList\":[1,2,3] }").execute();
        // List contains an array with space
        coll3.add("{ \"_id\":2, \"name\": \"overlaps\",\"list\":[\" \"],\"age\":24}").execute();
        coll3.add("{ \"_id\":3, \"overlaps\": \"overlaps\",\"age\":30}").execute();
        docs = coll3.find("[''] OVERLAPS $.list").execute();
        assertEquals(1, docs.count());
        assertEquals(new Integer(1), ((JsonNumber) docs.next().get("_id")).getInteger());
        table = this.schema.getCollectionAsTable("coll3");
        rows = table.select("doc->$._id as _id").where("[''] overlaps $.list").execute();
        r = rows.next();
        assertEquals("1", r.getString("_id"));
        docs = coll3.find("[' '] OVERLAPS $.list").execute();
        assertEquals(1, docs.count());
        assertEquals(new Integer(2), ((JsonNumber) docs.next().get("_id")).getInteger());
        rows = table.select("doc->$._id as _id").where("[' '] overlaps $.list").execute();
        r = rows.next();
        assertEquals("2", r.getString("_id"));
        docs = coll3.find("'overlaps' OVERLAPS $.name").execute();
        assertEquals(1, docs.count());
        assertEquals(new Integer(2), ((JsonNumber) docs.next().get("_id")).getInteger());
        rows = table.select("doc->$._id as _id").where("'overlaps' overlaps $.name").execute();
        r = rows.next();
        assertEquals(1, docs.count());
        assertEquals("2", r.getString("_id"));
        docs = coll3.find("[3] OVERLAPS $.intList").execute();
        assertEquals(1, docs.count());
        rows = table.select().where("[3] overlaps $.intList").execute();
        assertEquals(1, rows.count());
        /* Escape the keyword, to use it as identifier */
        docs = coll3.find("`overlaps` OVERLAPS $.`overlaps`").execute();
        assertEquals(1, docs.count());
        rows = table.select().where("'overlaps' overlaps $.`overlaps`").execute();
        assertEquals(1, rows.count());
        docs = coll3.find("$.`overlaps` OVERLAPS `overlaps`").execute();
        assertEquals(1, docs.count());
        rows = table.select().where("$.`overlaps` overlaps 'overlaps'").execute();
        assertEquals(1, rows.count());
        dropCollection("coll4");
        Collection coll4 = this.schema.createCollection("coll4", true);
        coll4.add("{\"overlaps\":{\"one\":1, \"two\":2, \"three\":3},\"list\":{\"one\":1, \"two\":2, \"three\":3},\"name\":\"one\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":1, \"two\":2, \"three\":3},\"list\":{\"four\":4, \"five\":5, \"six\":6},\"name\":\"two\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"two\":2, \"four\":4, \"six\":6},\"name\":\"three\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"three\":3, \"six\":9, \"nine\":9},\"name\":\"four\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"three\":6, \"six\":12, \"nine\":18},\"name\":\"five\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":[1,2,3]}, \"list\":{\"one\":[3,4,5]}, \"name\":\"six\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":[1,2,3]}, \"list\":{\"one\":[1,2,3]}, \"name\":\"seven\"}").execute();
        docs = coll4.find("`overlaps` OVERLAPS `list`").execute();
        assertEquals(3, docs.count());
        doc = docs.fetchOne();
        assertEquals("one", (((JsonString) doc.get("name")).getString()));
        doc = docs.fetchOne();
        assertEquals("four", (((JsonString) doc.get("name")).getString()));
        doc = docs.fetchOne();
        assertEquals("seven", (((JsonString) doc.get("name")).getString()));
        table = this.schema.getCollectionAsTable("coll4");
        rows = table.select("doc->$.name as name").where("$.`overlaps` OVERLAPS $.`list`").execute();
        assertEquals(3, rows.count());
        r = rows.next();
        assertEquals("\"one\"", r.getString("name"));
    } finally {
        dropCollection("coll4");
        dropCollection("coll3");
        dropCollection("coll2");
    }
}
Also used : Table(com.mysql.cj.xdevapi.Table) JsonArray(com.mysql.cj.xdevapi.JsonArray) DbDoc(com.mysql.cj.xdevapi.DbDoc) RowResult(com.mysql.cj.xdevapi.RowResult) DbDocImpl(com.mysql.cj.xdevapi.DbDocImpl) JsonNumber(com.mysql.cj.xdevapi.JsonNumber) Collection(com.mysql.cj.xdevapi.Collection) JsonString(com.mysql.cj.xdevapi.JsonString) Row(com.mysql.cj.xdevapi.Row) DocResult(com.mysql.cj.xdevapi.DocResult) Test(org.junit.jupiter.api.Test)

Example 3 with RowResult

use of com.mysql.cj.xdevapi.RowResult in project aws-mysql-jdbc by awslabs.

the class CollectionFindTest method testCollRemoveTabDeleteWithOverlaps.

@SuppressWarnings("deprecation")
@Test
public void testCollRemoveTabDeleteWithOverlaps() throws Exception {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
    int i = 0, maxrec = 10;
    DocResult docs = null;
    Result res = null;
    /* add(DbDoc[] docs) */
    DbDoc[] jsonlist = new DbDocImpl[maxrec];
    for (i = 0; i < maxrec; i++) {
        DbDoc newDoc2 = new DbDocImpl();
        newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
        newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
        newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
        newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
        newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 + i)));
        jsonlist[i] = newDoc2;
        newDoc2 = null;
    }
    this.collection.add(jsonlist).execute();
    assertEquals((maxrec), this.collection.count());
    /* find without Condition */
    docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
    i = 0;
    while (docs.hasNext()) {
        docs.next();
        i++;
    }
    assertEquals((maxrec), i);
    /* remove with single OVERLAPS */
    res = this.collection.remove("'1001' overlaps $._id").execute();
    assertEquals(1, res.getAffectedItemsCount());
    docs = this.collection.find("'1001' overlaps $._id").execute();
    assertFalse(docs.hasNext());
    /* remove with mulltiple OVERLAPS */
    String findCond = "";
    for (i = 1; i < maxrec; i++) {
        findCond = findCond + "'";
        findCond = findCond + String.valueOf(i + 1000) + "' not overlaps $._id";
        if (i != maxrec - 1) {
            findCond = findCond + " and ";
        }
    }
    res = this.collection.remove(findCond).execute();
    assertEquals(1, res.getAffectedItemsCount());
    docs = this.collection.find(findCond).execute();
    assertFalse(docs.hasNext());
    /* remove with single OVERLAPS */
    res = this.collection.remove("10004 overlaps $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
    assertEquals(1, res.getAffectedItemsCount());
    docs = this.collection.find("10004 overlaps $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
    assertFalse(docs.hasNext());
    /* remove with single OVERLAPS for float */
    res = this.collection.remove("30.1234 overlaps $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
    assertEquals(1, res.getAffectedItemsCount());
    docs = this.collection.find("30.1234 overlaps $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
    assertFalse(docs.hasNext());
    res = this.collection.remove("true").execute();
    jsonlist = new DbDocImpl[maxrec];
    for (i = 0; i < maxrec; i++) {
        DbDoc newDoc2 = new DbDocImpl();
        newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
        newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
        newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
        newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
        newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 + i)));
        jsonlist[i] = newDoc2;
        newDoc2 = null;
    }
    this.collection.add(jsonlist).execute();
    assertEquals((maxrec), this.collection.count());
    Table table = this.schema.getCollectionAsTable(this.collectionName);
    /* delete with single OVERLAPS */
    res = table.delete().where("'1001' overlaps doc->$._id").execute();
    assertEquals(res.getAffectedItemsCount(), 1);
    RowResult rows = table.select("doc->$.F1 as _id").where("'1001' overlaps doc->$._id").execute();
    assertFalse(rows.hasNext());
    /* delete with multiple OVERLAPS */
    findCond = "";
    for (i = 1; i < maxrec; i++) {
        findCond = findCond + "'";
        findCond = findCond + String.valueOf(i + 1000) + "' not overlaps doc->$._id";
        if (i != maxrec - 1) {
            findCond = findCond + " and ";
        }
    }
    res = table.delete().where(findCond).execute();
    assertEquals(res.getAffectedItemsCount(), 1);
    rows = table.select("doc->$.F1 as _id").where(findCond).execute();
    assertFalse(rows.hasNext());
    /* delete with single OVERLAPS for float */
    res = table.delete().where("30.1234 overlaps doc->$.F2").execute();
    assertEquals(res.getAffectedItemsCount(), 1);
    rows = table.select("doc->$.F1 as F1").where("30.1234 overlaps doc->$.F2").execute();
    assertFalse(rows.hasNext());
    /* delete with single OVERLAPS for int */
    res = table.delete().where("10004 overlaps doc->$.F4").execute();
    assertEquals(res.getAffectedItemsCount(), 1);
    rows = table.select("doc->$.F1 as F1").where("10004 overlaps doc->$.F4").execute();
    assertFalse(rows.hasNext());
}
Also used : DbDoc(com.mysql.cj.xdevapi.DbDoc) RowResult(com.mysql.cj.xdevapi.RowResult) DbDocImpl(com.mysql.cj.xdevapi.DbDocImpl) Table(com.mysql.cj.xdevapi.Table) JsonNumber(com.mysql.cj.xdevapi.JsonNumber) JsonString(com.mysql.cj.xdevapi.JsonString) JsonString(com.mysql.cj.xdevapi.JsonString) DocResult(com.mysql.cj.xdevapi.DocResult) RowResult(com.mysql.cj.xdevapi.RowResult) Result(com.mysql.cj.xdevapi.Result) AddResult(com.mysql.cj.xdevapi.AddResult) DocResult(com.mysql.cj.xdevapi.DocResult) SqlResult(com.mysql.cj.xdevapi.SqlResult) Test(org.junit.jupiter.api.Test)

Example 4 with RowResult

use of com.mysql.cj.xdevapi.RowResult in project aws-mysql-jdbc by awslabs.

the class CollectionFindTest method testCollectionFindInValidFunction.

@Test
public void testCollectionFindInValidFunction() throws Exception {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
    DbDoc doc = null;
    DocResult docs = null;
    this.collection.add("{\"_id\": \"1001\", \"ARR\":[1,1,2], \"ARR1\":[\"name1\", \"name2\", \"name3\"]}").execute();
    this.collection.add("{\"_id\": \"1002\", \"ARR\":[1,2,3], \"ARR1\":[\"name4\", \"name5\", \"name6\"]}").execute();
    this.collection.add("{\"_id\": \"1003\", \"ARR\":[1,4,5], \"ARR1\":[\"name1\", \"name1\", \"name5\"]}").execute();
    docs = this.collection.find("[1,1,3] in $.ARR").execute();
    doc = docs.next();
    assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    docs = this.collection.find("[2,5] in $.ARR").execute();
    assertFalse(docs.hasNext());
    docs = this.collection.find("(1+2) in (1, 2, 3)").execute();
    doc = docs.next();
    docs = this.collection.find("concat('name', '6') in ('name1', 'name2', 'name6')").execute();
    doc = docs.next();
    Table tabNew = this.schema.getCollectionAsTable(this.collectionName);
    RowResult rows = tabNew.select("doc->$._id as _id").where("(1+2) in (1, 2, 3)").execute();
    rows.next();
    assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires operator that produce a JSON value\\.", () -> tabNew.select("doc->$._id as _id").where("(1+2) in [1, 2, 3]").execute());
    assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires operator that produce a JSON value\\.", () -> tabNew.select("doc->$._id as _id").where("(1+2) in doc->$.ARR").execute());
    assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires function that produce a JSON value\\.", () -> this.collection.find("concat('name', '6') in ['name1', 'name2', 'name6']").execute());
    assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires operator that produce a JSON value\\.", () -> this.collection.find("(1+2) in $.ARR").execute());
    assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires function that produce a JSON value\\.", () -> this.collection.find("concat('name', '6') in $.ARR1").execute());
}
Also used : DbDoc(com.mysql.cj.xdevapi.DbDoc) RowResult(com.mysql.cj.xdevapi.RowResult) Table(com.mysql.cj.xdevapi.Table) JsonString(com.mysql.cj.xdevapi.JsonString) DocResult(com.mysql.cj.xdevapi.DocResult) Test(org.junit.jupiter.api.Test)

Example 5 with RowResult

use of com.mysql.cj.xdevapi.RowResult in project aws-mysql-jdbc by awslabs.

the class CollectionFindTest method testCollectionFindInSanity.

@Test
public void testCollectionFindInSanity() throws Exception {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
    int i = 0, maxrec = 10;
    DbDoc doc = null;
    DocResult docs = null;
    DbDoc[] jsonlist = new DbDocImpl[maxrec];
    for (i = 0; i < maxrec; i++) {
        DbDoc newDoc2 = new DbDocImpl();
        newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
        newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
        newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
        newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
        newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 - i)));
        jsonlist[i] = newDoc2;
        newDoc2 = null;
    }
    this.collection.add(jsonlist).execute();
    assertEquals((maxrec), this.collection.count());
    /* find without Condition */
    docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
    i = 0;
    while (docs.hasNext()) {
        doc = docs.next();
        i++;
    }
    assertEquals((maxrec), i);
    /* find with single element IN which uses json_contains */
    docs = this.collection.find("'1001' in $._id").execute();
    doc = docs.next();
    assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* find with multiple IN which uses json_contains */
    String findCond = "";
    for (i = 1; i < maxrec; i++) {
        findCond = findCond + "'";
        findCond = findCond + String.valueOf(i + 1000) + "' not in $._id";
        if (i != maxrec - 1) {
            findCond = findCond + " and ";
        }
    }
    docs = this.collection.find(findCond).execute();
    doc = docs.next();
    assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* find with single IN for string with orderBy */
    docs = this.collection.find("'Field-1-Data-2' in $.F1").orderBy("CAST($.F4 as SIGNED)").execute();
    doc = docs.next();
    assertEquals(String.valueOf(1002), (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* find with single IN for numeric with orderBy */
    docs = this.collection.find("10000 in $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
    doc = docs.next();
    assertEquals(String.valueOf(1000), (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* find with single IN for float with orderBy */
    docs = this.collection.find("20.1234 in $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
    doc = docs.next();
    assertEquals(String.valueOf(1001), (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* Testing with table */
    Table table = this.schema.getCollectionAsTable(this.collectionName);
    /* find with single IN for string */
    RowResult rows = table.select("doc->$._id as _id").where("'1001' in doc->$._id").execute();
    Row r = rows.next();
    assertEquals(r.getString("_id"), "\"1001\"");
    assertFalse(rows.hasNext());
    /* find with multiple IN in single select */
    findCond = "";
    for (i = 1; i < maxrec; i++) {
        findCond = findCond + "'";
        findCond = findCond + String.valueOf(i + 1000) + "' not in doc->$._id";
        if (i != maxrec - 1) {
            findCond = findCond + " and ";
        }
    }
    /* find with single IN for string */
    rows = table.select("doc->$._id as _id").where(findCond).execute();
    r = rows.next();
    assertEquals(r.getString("_id"), "\"1000\"");
    assertFalse(rows.hasNext());
    /* find with single IN for float */
    rows = table.select("doc->$._id as _id").where("20.1234 in doc->$.F2").execute();
    r = rows.next();
    assertEquals(r.getString("_id"), "\"1001\"");
    assertFalse(rows.hasNext());
    /* find with single IN for string */
    rows = table.select("doc->$._id as _id").where("'Field-1-Data-2' in doc->$.F1").execute();
    r = rows.next();
    assertEquals(r.getString("_id"), "\"1002\"");
    assertFalse(rows.hasNext());
    /* find with single IN for numeric */
    rows = table.select("doc->$._id as _id").where("10000 in doc->$.F4").execute();
    r = rows.next();
    assertEquals(r.getString("_id"), "\"1000\"");
    assertFalse(rows.hasNext());
}
Also used : DbDoc(com.mysql.cj.xdevapi.DbDoc) RowResult(com.mysql.cj.xdevapi.RowResult) DbDocImpl(com.mysql.cj.xdevapi.DbDocImpl) Table(com.mysql.cj.xdevapi.Table) JsonNumber(com.mysql.cj.xdevapi.JsonNumber) JsonString(com.mysql.cj.xdevapi.JsonString) JsonString(com.mysql.cj.xdevapi.JsonString) Row(com.mysql.cj.xdevapi.Row) DocResult(com.mysql.cj.xdevapi.DocResult) Test(org.junit.jupiter.api.Test)

Aggregations

RowResult (com.mysql.cj.xdevapi.RowResult)54 Test (org.junit.jupiter.api.Test)51 Table (com.mysql.cj.xdevapi.Table)48 Column (com.mysql.cj.xdevapi.Column)23 Row (com.mysql.cj.xdevapi.Row)22 JsonString (com.mysql.cj.xdevapi.JsonString)13 DbDoc (com.mysql.cj.xdevapi.DbDoc)12 DocResult (com.mysql.cj.xdevapi.DocResult)10 DbDocImpl (com.mysql.cj.xdevapi.DbDocImpl)9 JsonNumber (com.mysql.cj.xdevapi.JsonNumber)7 SqlResult (com.mysql.cj.xdevapi.SqlResult)7 CoreSession (com.mysql.cj.CoreSession)5 Session (com.mysql.cj.xdevapi.Session)5 HashMap (java.util.HashMap)5 ExecutionException (java.util.concurrent.ExecutionException)5 AddResult (com.mysql.cj.xdevapi.AddResult)4 Result (com.mysql.cj.xdevapi.Result)4 DataConversionException (com.mysql.cj.exceptions.DataConversionException)3 SessionFactory (com.mysql.cj.xdevapi.SessionFactory)3 BigDecimal (java.math.BigDecimal)3