How to set user supplied id

[indrajra]

I read a note in the documentation and quote the same here. Am not sure if I find this in the documentation or is missing from my hasty eyes. Can you please help me lead to that section? Any example would be additionally helpful.

Sqlg supports user supplied ids but not quite as defined by TinkerPop. This is explained below.

[pietermartin]

Sorry, looks like I never added it to the docs. Here is an example,

    @Test
    public void testMultipleIDs() {
        Schema aSchema = this.sqlgGraph.getTopology().ensureSchemaExist("A");
        aSchema.ensureVertexLabelExist(
                "A",
                new HashMap<String, PropertyType>(){{
                    put("uid", PropertyType.varChar(100));
                    put("country", PropertyType.varChar(100));
                }},
                ListOrderedSet.listOrderedSet(Arrays.asList("uid", "country")));
        this.sqlgGraph.tx().commit();
        this.sqlgGraph.tx().normalBatchModeOn();
        int count = 2;
        List<Vertex> ids = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            Vertex v = this.sqlgGraph.addVertex(T.label, "A.A", "uid", UUID.randomUUID().toString(), "country", "SA");
            ids.add(v);
        }
        this.sqlgGraph.tx().commit();
        List<Vertex> vertices = this.sqlgGraph.traversal().V(ids.toArray()).toList();
        Assert.assertEquals(count, vertices.size());
    }

The 3rd parameter of the ensureVertexLabelExist method specifies which attributes will make up the identifier/uid. It will create a composite id in the database.

If you look in org.umlg.sqlg.test.usersuppliedpk.topology package you will see more tests.

If you want to use partitioning at all you have to use user supplied ids.

[indrajra]

Thanks @pietermartin . I see a property named "uid" is added and thereafter .traversal() could use these uids and fetch vertices. I've a couple of questions around this:

1. Could the 'uid' attribute name be alterable? For example, can I call this "osid" ?
2. Any comments if get graph.vertices() would work rather than of .traversal().V()? We prefer .vertices() way and hence asking.

[pietermartin]

1) Yes the user supplied id names can be anything. 2) graph.vertices() will also work. I'd recommend using the other way as its the more gremliny way of doing things.

[indrajra]

Thanks @pietermartin. I'll try and come back if I face any issues. Would you want to reference this to update the documentation? I'll keep it open if so, otherwise happy to close.

[pietermartin]

I have added a user supplied id section to the doc, just need to finalize the next release to publish it. You can close this issue, thanks.

[indrajra]

@pietermartin - looks like I wasn't all that clear in the reading here. Given that 'uid' has been inserted as a column in the database, can one query back using this custom id? For example, can one get the vertex by supplying the 'uid' (not the record id)? I would prefer a simple direct query than doing any client side filtering.

Iterator<Vertex> itrV = graph.traversal().V(uid); // Custom id being passed here if (!itrV.hasNext()) { throw new Exception("Invalid id"); }

I encounter invalid id exception here with a custom id (trace below).

org.umlg.sqlg.structure.SqlgExceptions$InvalidIdException: Sqlg ids must be a String with the format 'label:::id' The id must be a long. The given id '253ecd75-9445-4fc9-8cef-e2116712eada' is invalid. at org.umlg.sqlg.structure.SqlgExceptions.invalidId(SqlgExceptions.java:23) at org.umlg.sqlg.structure.RecordId.from(RecordId.java:78) at org.umlg.sqlg.sql.parse.ReplacedStep.addIdHasContainer(ReplacedStep.java:669) at org.umlg.sqlg.strategy.BaseStrategy.addHasContainerForIds(BaseStrategy.java:716) at org.umlg.sqlg.strategy.GraphStrategy.doFirst(GraphStrategy.java:107) at org.umlg.sqlg.strategy.BaseStrategy.handleStep(BaseStrategy.java:115) at org.umlg.sqlg.strategy.GraphStrategy.combineSteps(GraphStrategy.java:74) at org.umlg.sqlg.strategy.GraphStrategy.apply(GraphStrategy.java:63) at org.umlg.sqlg.strategy.SqlgGraphStepStrategy.apply(SqlgGraphStepStrategy.java:29) at org.apache.tinkerpop.gremlin.process.traversal.util.DefaultTraversalStrategies.applyStrategies(DefaultTraversalStrategies.java:86) at org.apache.tinkerpop.gremlin.process.traversal.util.DefaultTraversal.applyStrategies(DefaultTraversal.java:119) at org.apache.tinkerpop.gremlin.process.traversal.util.DefaultTraversal.hasNext(DefaultTraversal.java:191)

[pietermartin]

Here is an example of how to query with the id.

    @Test
    public void testIdQuery() {
        Schema aSchema = this.sqlgGraph.getTopology().ensureSchemaExist("A");
        aSchema.ensureVertexLabelExist(
                "A",
                new HashMap<String, PropertyType>(){{
                    put("uid", PropertyType.varChar(100));
                    put("country", PropertyType.varChar(100));
                }},
                ListOrderedSet.listOrderedSet(Collections.singletonList("uid")));
        this.sqlgGraph.tx().commit();

        String uid = UUID.randomUUID().toString();
        this.sqlgGraph.addVertex(T.label, "A.A", "uid", uid, "country", "Moon");
        this.sqlgGraph.tx().commit();

        Traversal<Vertex, Vertex> traversal =  this.sqlgGraph.traversal().
            V(RecordId.from(SchemaTable.of("A", "A"), ListOrderedSet.listOrderedSet(Collections.singletonList(uid))));
        Assert.assertTrue(traversal.hasNext());
        Vertex v = traversal.next();
        Assert.assertTrue(v.property("country").isPresent());
        Assert.assertEquals("Moon", v.property("country").value());
    }

You have to create the RecordId, indicating the schema, table and user supplied identifiers.

[indrajra]

Ok, great - thanks; closing this once and for all :-)