Skip to content
Drivine

Oracle

Oracle Database 23ai or later, over JDBC. Oracle does not speak Cypher: Drivine reads a graph with SQL/PGQ (GRAPH_TABLE) and writes it as rows, since Oracle refuses data manipulation through a property graph. Models, the query DSL and StatelessGraphObjectManager are the same as on the graph engines. Add the driver yourself: com.oracle.database.jdbc:ojdbc11.

database:
  datasources:
    graph:
      type: ORACLE
      host: localhost
      port: 1521
      database-name: FREEPDB1     # the service to connect to
      user-name: app
      password: secret
      default-graph-path: drivine # names the property graph and its tables; this is the default

user-name and database-name are required, and port is 1521 unless given. A cypher-dialect or a protocol means nothing here and is ignored with a warning. The graph’s name names tables, so it is letters, digits and underscores, begins with a letter, and is at most 114 characters.

Drivine makes a connection pool (HikariCP) for the datasource: pool-max connections at most (40 unless given), connection-timeout milliseconds to wait for one (5,000 unless given), and idle-timeout milliseconds before an idle one is closed (the pool’s own default unless given). The pool asks the database for a connection as it is made, so a service that cannot be reached or a user that is turned away fails at startup. Each connection keeps 64 statements prepared (oracle.jdbc.implicitStatementCacheSize): a save is the same block each time, and is neither sent nor parsed again.

To use a DataSource the application already has, register a provider in code: registry.register(JdbcConnectionProvider("graph", DatabaseType.ORACLE, dataSource, graphName = "drivine")). Drivine does not close a DataSource it was given, and does not configure it: turn the driver’s statement cache on there yourself. For a pool of Drivine’s own (HikariCP) without the YAML:

val provider = JdbcConnectionProvider.pooled(
    "graph", DatabaseType.ORACLE, JdbcConnectionProvider.oracleUrl("localhost", 1521, "FREEPDB1"), "app", "secret",
    graphName = "drivine",
)

How a graph is stored. Two tables and the property graph over them, made when a manager first speaks to the graph: <graph>_node (nid, labels, props, stamp) and <graph>_edge (eid, src, dst, type, props), with labels and props as JSON. Labels, property names and relationship types are data, so @NodeLabels, @PropertyBag and edges.relate work as they do elsewhere. To SQL written by hand the graph has one vertex label, node, and one edge label, edge; a Cypher label is a test on labels and a relationship type a test on type. A node’s stamp is the stamp column, and a load returns it as __drivine.stamp. Deleting a node’s row deletes its relationships with it. A third table, <graph>_schema, records the indexes and constraints Drivine made: see Schema Management.

Notes:

  • A statement written by hand is SQL. Parameters are named $name as in Cypher. A collection is bound as JSON, for JSON_TABLE to read. A column of the JSON type comes back as a map, so SELECT JSON_OBJECT(… RETURNING JSON) hydrates with .transform(MyView::class.java).
  • A statement that writes and returns rows is a PL/SQL block that hands them back with DBMS_SQL.RETURN_RESULT. A block is one statement: one that fails writes nothing.
  • A raw whereClause is a SQL predicate. A property of the root may be written as Cypher writes it (issue.state = 'open'); anything more is SQL over issue.props, issue.labels and issue.stamp.
  • A save is one statement and checks the stamp, as on the graph engines. Another writer that changes a node in SQL should give it a new stamp too: the first sixteen characters of stamp for its properties or labels, the last sixteen for its relationships. Stamps.setClause and Stamps.linksClause are Cypher and do not apply.
  • A save the database turns away because another writer held a row it wanted, a deadlock (ORA-00060) or a serialization failure (ORA-08177), is run again, as on Neo4j and Memgraph.
  • saveAll saves fragments 500 to a statement, and each view by a statement of its own. The batch is one transaction.
  • patchAll writes 500 nodes to a statement, and 100 where a @VectorIndex field is written, in one transaction.
  • patch is one block. Its predicate is run inside the block as a statement of its own, since Oracle Free 23.26 takes no parameter inside a GRAPH_TABLE of a block’s own SQL, which is how a predicate on a relationship is written.
  • A delete can cascade with DELETE_ALL and with DELETE_ORPHAN.
  • Index the id of each node type you save: constraints.ensure(UniquenessConstraintSpec("Person", "id")) makes a unique index and the index a node is found by. Without one a save reads the table.
  • A range index reads a property as text. A comparison or an order by a number or a moment is correct and is not served by that index, so an ordered or keyset-paged load is given no index advice.
  • A range index is two indexes on Oracle: one over the property whatever the label, which a save and a load by id find a node by, and one over the property of the label’s nodes alone. A loadAll, a count, a deleteAll or a groups whose where compares an indexed property with a text (eq, gt, gte, lt, lte) reads the second, and does not test the label on its own: the one index answers both. What else the where holds is tested on the nodes it gives. IN, neq, a test of part of a text, a comparison inside an or, and one with a number are read as before. A uniqueness constraint over one property already has an index over the label’s nodes, its unique index, which reads the same value: a filter on that property is read through it, and no second one is made. A range index made by an earlier version is found, listed and dropped as it is, and the next ensure of it adds the index over the label’s nodes. The backend reads which there are from <graph>_schema on a thread of its own, at most once a minute and again once this process has made or dropped an index or a constraint. A read never waits for it: until the first is done, and while another is under way, statements are written with what was read last.
  • Oracle reads an empty string as null, so a stamp of '' is no stamp. A comparison with "" in the query DSL is written to find a property stored empty all the same; SQL of yours that compares JSON_VALUE(…) with '' finds nothing.
  • matches takes a regular expression in Oracle’s POSIX dialect, matched against the whole value. A leading (?i) ignores case; what Java adds, such as (?:…), a look-around or \b, is not read.
  • A vector index (indexes.ensure(VectorIndexSpec(...)), or @VectorIndex through schema management) is a table of embeddings beside the nodes, kept in step by a trigger, with an Oracle vector index over it by neighbour partitions. loadNearest goes through it and is approximate, as on the graph engines. With no index made, it measures every node of the label and is exact.
  • A full-text index is a table of the indexed text beside the nodes, with an Oracle Text index over it. loadMatching reads words, a "phrase", +must, -not and a prefix*. A backslash before punctuation or a space makes that character text: prefix\* is the word and \-not is not taken away. A backslash before a letter or a digit is a gap between two words, where Lucene drops it: foo\bar is the phrase foo bar here and the word foobar on Neo4j. The index holds what has been committed, so a search does not see its own transaction’s writes. Oracle Text is in the regular Free image and not the slim one: without it, or with no index made, a search counts the words in the text of every node of the label, and does see them. An analyzer is recorded with the index and not applied.
  • A property over 4,000 characters is stored and loaded whole, and compares as absent in a predicate.
  • The deprecated GraphObjectManager.save and saveAll throw UnsupportedOperationException on Oracle; that manager’s loads, queries and deletes work. Cypher statements written by hand do not run, and neither do the migration tools RelationshipDirectionRepair and PathRelationshipReport, which are Cypher: they repair what saves before 0.1.0 wrote, and no graph on Oracle is that old.

What a read looks like. A load is one SQL/PGQ statement, written to be read beside the Cypher it stands for. A row is called what the model calls it, with an underscore so that a field may be called user or order, and the node of a MATCH has its row’s name. IS node and IS edge stand where Cypher has a label and a type, since both are data: a label is a test of labels, a type of type. doc is the node as the map a load returns. Where Cypher loads a view with

MATCH (claim:Claim) WHERE claim.id = $id
RETURN { claim: claim {.*}, people: [(claim)-[:MENTIONS]->(people:Human) | people {.*}] } AS result

Oracle is sent

SELECT JSON_OBJECT(
    'claim' VALUE claim_.doc,
    'people' VALUE COALESCE((
        SELECT JSON_ARRAYAGG(people_.doc ORDER BY people_.eid RETURNING JSON)
        FROM GRAPH_TABLE (drivine
            MATCH (claim_ IS node) -[e IS edge WHERE e.type = 'MENTIONS']-> (people_ IS node WHERE INSTR(JSON_SERIALIZE(people_.labels), '"Human"') > 0)
            COLUMNS (claim_.nid AS src, e.eid, people_.doc)) people_
        WHERE people_.src = claim_.nid), JSON('[]'))
    RETURNING JSON) AS result
FROM GRAPH_TABLE (drivine
    MATCH (claim_ IS node WHERE INSTR(JSON_SERIALIZE(claim_.labels), '"Claim"') > 0)
    COLUMNS (claim_.nid, claim_.props, claim_.doc)) claim_
WHERE JSON_VALUE(claim_.props, '$.id') = $id

A relationship field is a query of its own, under the field it fills: Oracle does not let a GRAPH_TABLE name the row it is nested in, so each returns the node it starts from as src and is joined to that row outside. COLUMNS names only what the statement reads of a row, and every column of the root once a whereClause written by hand is in it. A second row for one field is numbered (people2_), as when a condition on people is tested beside the field itself. A label is tested with JSON_EXISTS(… '$[*]?(@ == "Claim")') where every node of the label is read, which the index over labels answers, and with INSTR where the node is found by its id or at the end of a relationship. Where a range index on the label answers the where, the property is read as that index reads it and the label is not tested on its own:

SELECT n_.doc AS result
FROM GRAPH_TABLE (drivine
    MATCH (n_ IS node)
    COLUMNS (n_.labels, n_.props, n_.doc)) n_
WHERE CASE WHEN JSON_EXISTS(n_.labels, '$[*]?(@ == "Issue")') THEN JSON_VALUE(n_.props, '$.repoKey') END = $_p0
  AND JSON_VALUE(n_.props, '$.state') = $_p1

A @GraphPath field, and a list read to a depth, is the path’s own rows grouped by the node reached, so each node is there once however many ways lead to it:

'employers' VALUE COALESCE((
    SELECT JSON_ARRAYAGG(employers_.doc ORDER BY employers_.nid RETURNING JSON)
    FROM (
        SELECT reached_.src, reached_.nid, ANY_VALUE(reached_.doc) AS doc
        FROM GRAPH_TABLE (drivine
            MATCH (claim_ IS node)
                -[e1 IS edge WHERE e1.type = 'MENTIONS']-> (m1 IS node WHERE INSTR(JSON_SERIALIZE(m1.labels), '"Human"') > 0)
                -[e2 IS edge WHERE e2.type = 'WORKS_AT']-> (reached_ IS node WHERE INSTR(JSON_SERIALIZE(reached_.labels), '"Company"') > 0)
            COLUMNS (claim_.nid AS src, reached_.nid, reached_.doc)) reached_
        GROUP BY reached_.src, reached_.nid
    ) employers_
    WHERE employers_.src = claim_.nid), JSON('[]'))

Testing. OracleTestContainer starts Oracle Free (gvenzl/oracle-free:23-faststart; another with ORACLE_TEST_IMAGE or -Dtest.oracle.image) and gives a user, drivine, in the service FREEPDB1. @EnableDrivineTestConfig uses it for a datasource of type ORACLE, or a local instance with USE_LOCAL_ORACLE=true (see TEST_CONTAINERS.md). Add the JDBC driver to the test classpath. Without Spring:

val provider = JdbcConnectionProvider.pooled(
    "graph", DatabaseType.ORACLE, OracleTestContainer.getJdbcUrl(),
    OracleTestContainer.getConnectionUsername(), OracleTestContainer.getConnectionPassword(),
)

A test written for the graph engines usually seeds its graph and reads it back in Cypher, which Oracle does not run. OracleCypherFixtures works such a statement out over the graph instead, so the test’s fixtures stay as they are:

val fixtures = OracleCypherFixtures.over(persistenceManager)   // Cypher fixtures here, everything else to Oracle
fixtures.execute(QuerySpecification.withStatement("CREATE (:Person {id: 'ada'})-[:KNOWS]->(:Person {id: 'bob'})"))


OracleCypherFixtures.run(persistenceManager, "MATCH (p:Person) RETURN p.id ORDER BY p.id")   // ["ada", "bob"]
OracleCypherFixtures.clear(persistenceManager)                                               // every node and relationship
  • It reads the Cypher fixtures use and no more: CREATE, MATCH and MERGE patterns with literal or bound properties, WHERE (comparisons joined by AND), SET, REMOVE, DETACH DELETE, UNWIND, WITH, and a RETURN of one expression or a count, with ORDER BY. Anything else is refused: OR, ON CREATE SET, SKIP, LIMIT, a RETURN of several columns.
  • Keywords are upper case. A statement is taken for a fixture when it opens with MATCH (, CREATE (, MERGE ( or UNWIND; any other, one that opens with WITH included, goes to Oracle as SQL.
  • Each statement reads the whole graph into memory and writes back what it changed, a row at a time. It is for a test’s handful of nodes and nothing else.
  • The manager must name its graph (graphName), as one over a JdbcConnectionProvider does.
  • TestCleanup.beforeEach(persistenceManager, "my-test") deletes the nodes whose createdBy is the tag, in SQL on Oracle, as it does in Cypher elsewhere.

Drivine’s own Oracle tests are left out of a plain ./gradlew test: the image is large and the database wants two gigabytes. Run them with

Terminal window
ORACLE_TESTS=true ./gradlew :test --tests '*Oracle*'

CI runs the same command, in a job of its own (test (Oracle)).