Databases and queries

This guide covers the life of a database directory and the two calls that talk to it. Prefer SQL or openCypher through db.command(…​) and db.query(…​) for schema, inserts and graph work. The bindings also expose record objects and a Schema API; they are on Values and records. A Python function that a statement calls, and a callback on record events, are on Python functions and record events. The wrappers for the rest of the Java API are not covered here.

Every script on this page ran against the published arcadedb-jvm wheel. Run each one in an empty directory.

The life of a database

A database is a directory on your filesystem. It is created, opened, used, closed and, if you want, dropped.

import arcadedb

# Create (raises if the path already holds a database)
with arcadedb.create_database("./mydb") as db:
    print(f"Created at: {db.get_database_path()}")
    db.command("sql", "CREATE VERTEX TYPE User")
    with db.transaction():
        db.command("sql", "INSERT INTO User SET name = ?", "Alice")

print("Exists:", arcadedb.database_exists("./mydb"))

# Creating it again fails
try:
    with arcadedb.create_database("./mydb"):
        pass
except Exception as e:
    print(type(e).__name__, e, sep=": ")

# Open, and read a value back
with arcadedb.open_database("./mydb") as db:
    print("Name:", db.get_name())
    for user in db.query("sql", "SELECT FROM User"):
        print(user.get("name"))

# Open an existing database or create it
path = "./myapp"
if arcadedb.database_exists(path):
    db = arcadedb.open_database(path)
else:
    db = arcadedb.create_database(path)
with db:
    print("Transaction active:", db.is_transaction_active())
    with db.transaction():
        print("Transaction active:", db.is_transaction_active())
    print("Transaction active:", db.is_transaction_active())

# Drop: deletes all files, irreversible
with arcadedb.open_database("./myapp") as db:
    db.drop()
print("Exists after drop:", arcadedb.database_exists("./myapp"))
Created at: ./mydb
Exists: True
ArcadeDBError: Failed to create database: com.arcadedb.exception.DatabaseOperationException: Database './mydb' already exists
Name: mydb
Alice
Transaction active: False
Transaction active: True
Transaction active: False
Exists after drop: False

Notes on the script:

  • create_database(path) creates the directory and opens the database for reading and writing. It raises ArcadeDBError if the path already holds a database, as the third output line shows.

  • open_database(path) opens an existing database. database_exists(path) tells the two cases apart.

  • with closes the database when the block ends, also when an exception leaves it. On close, active transactions roll back. You can call db.close() yourself, inside try/finally, but the context manager is the recommended way.

  • db.drop() deletes all files of the database. It cannot be undone.

  • db.is_transaction_active() is True only inside with db.transaction(): (or between begin() and commit() or rollback()). The other ways to end a transaction, and what an error does to it, are on Transactions.

Read-only open

open_database(path, read_only=True) opens an existing database for reading only; DatabaseFactory(path).open(read_only=True) does the same. Queries, counts and record reads work as usual and db.is_read_only() is True. Every write raises ArcadeDBError with "read only" in the message: an INSERT, a schema change such as CREATE DOCUMENT TYPE, or save() on a record. Reads inside with db.transaction(): work too; on ArcadeDB 26.11.1 and later the commit or rollback of such a transaction returns, while earlier engines raise the same read-only error there.

Use it when a process only reads a finished database, so that a stray write fails instead of changing the data. It is a safety setting, not a speed setting: no measurement here shows reads getting faster.

Locking

The open database holds an operating-system lock on database.lck in its directory. A second process cannot open the same directory while the first holds it; open it in one process and share the handle between that process’s threads.

After a crash the operating system releases the lock and leaves the file behind. A leftover file blocks nothing: the next open sees it and replays the write-ahead log to recover. Do not delete it by hand, because that skips the recovery.

Several databases, one JVM

Each Python process starts one JVM, on the first database or server it creates. All databases of the process share it. To set JVM options, call start_jvm(…​) before the first database; see JVM options.

Write and query

Which query language

Both languages run through the same two calls, so the choice depends on the question you ask. Use openCypher for graph patterns and traversals: who knows whom, friends of friends, paths of a given length. Use SQL for document and table queries, schema, inserts, filters, aggregates, and pagination. The older SQL graph syntax (MATCH {type: Person, as: p} patterns, out(), in(), outE(), inE(), and TRAVERSE) is supported but not recommended for new code: openCypher is a graph pattern language shared by many databases and close to the ISO GQL standard (ISO/IEC 39075), so queries and skills carry over. This page shows both languages on one database. The languages themselves are documented in the main ArcadeDB manual, see the SQL reference and the openCypher reference; this guide does not repeat them.

Write and read

import arcadedb

with arcadedb.create_database("./qdb") as db:
    db.command("sql", "CREATE VERTEX TYPE Person")
    db.command("sql", "CREATE PROPERTY Person.name STRING")
    db.command("sql", "CREATE PROPERTY Person.age INTEGER")
    db.command("sql", "CREATE EDGE TYPE Knows")

    with db.transaction():
        db.command("sql", "INSERT INTO Person SET name = ?, age = ?", "Alice", 30)
        db.command("sql", "INSERT INTO Person SET name = ?, age = ?", "Bob", 25)
        db.command("sql", "INSERT INTO Person SET name = ?, age = ?", "Charlie", 35)

    # Positional parameters
    for p in db.query("sql", "SELECT FROM Person WHERE name = ? AND age > ?", "Alice", 25):
        print("positional:", p.get("name"), p.get("age"))

    # Named parameters
    rows = db.query(
        "sql",
        "SELECT FROM Person WHERE name = :name AND age > :min_age",
        {"name": "Alice", "min_age": 25},
    )
    print("named:", [r.get("name") for r in rows])

    # Named collection parameter
    rows = db.query(
        "sql",
        "SELECT FROM Person WHERE age IN :ages ORDER BY age",
        {"ages": [25, 30]},
    )
    print("collection:", [r.get("name") for r in rows])

    # first() and to_list()
    first_person = db.query("sql", "SELECT FROM Person ORDER BY name").first()
    print("first:", first_person.get("name"))
    people = db.query("sql", "SELECT FROM Person ORDER BY name").to_list()
    print("to_list:", len(people), people[1]["name"])
    missing = db.query("sql", "SELECT FROM Person WHERE name = ?", "Unknown").first()
    print("no match:", missing)

    # Count a type
    print("count_type:", db.count_type("Person"))

    # Update and delete in a transaction
    with db.transaction():
        db.command("sql", "UPDATE Person SET age = ? WHERE name = ?", 26, "Bob")
        db.command("sql", "DELETE FROM Person WHERE name = ?", "Charlie")
    print("after write:", [(r.get("name"), r.get("age")) for r in db.query("sql", "SELECT FROM Person ORDER BY name")])

    # SQLScript: several statements in one call
    script = """
        CREATE VERTEX TYPE ScriptVertex;
        INSERT INTO ScriptVertex SET name = 'test';
        ALTER TYPE ScriptVertex ALIASES sv;
    """
    with db.transaction():
        result = db.command("sqlscript", script)
    last = result.first()
    print("sqlscript:", last.get("operation"), last.get("typeName"))

    # @rid-based pagination
    last_rid = "#-1:-1"
    batch_size = 1
    page_no = 0
    while True:
        chunk = db.query(
            "sql",
            "SELECT @rid AS rid, name FROM Person WHERE @rid > ? LIMIT ?",
            last_rid,
            batch_size,
        ).to_list()
        if not chunk:
            break
        page_no += 1
        print("page", page_no, [c["name"] for c in chunk])
        last_rid = str(chunk[-1]["rid"])

    # openCypher through the same two calls
    with db.transaction():
        db.command(
            "opencypher",
            "MATCH (a:Person {name: 'Alice'}), (b:Person {name: 'Bob'}) CREATE (a)-[:Knows]->(b)",
        )
    for row in db.query(
        "opencypher",
        "MATCH (a:Person)-[:Knows]->(b:Person) RETURN a.name AS a, b.name AS b",
    ):
        print("cypher:", row.get("a"), "knows", row.get("b"))

    # Aggregation
    total = db.query("sql", "SELECT count(*) AS total FROM Person").first().get("total")
    print("total:", total)
positional: Alice 30
named: ['Alice']
collection: ['Bob', 'Alice']
first: Alice
to_list: 3 Bob
no match: None
count_type: 3
after write: [('Alice', 30), ('Bob', 26)]
sqlscript: ALTER TYPE ScriptVertex
page 1 ['Alice']
page 2 ['Bob']
cypher: Alice knows Bob
total: 2

Parameters

Bind values as parameters instead of pasting them into the statement text. Identifiers such as type and property names cannot be bound and belong in the text.

  • Positional: one ? per value, from the extra arguments (db.query("sql", q, "Alice", 25)) or from one list or tuple (db.query("sql", q, ["Alice", 25])).

  • Named: :name in the statement and a dict as the one extra argument. A dict is always the named map, never one positional value. A named parameter can hold a collection, as in age IN :ages.

  • None binds as null.

A list or tuple on its own is the positional list, so it never binds as one parameter. db.query("sql", "SELECT FROM V WHERE @rid IN ?", [rid_a, rid_b]) has two values for one ?. The bindings raise TypeError before the statement runs when a SQL or SQLScript call has more positional values than the statement can bind, and since 26.11.1 the engine refuses a mismatched count too (ArcadeData/arcadedb#10721). Older engines bound rid_a, dropped rid_b without a word and returned one row where two were meant. To bind a list as one parameter, bind it by name or wrap it so it is the one positional value:

db.query("sql", "SELECT FROM V WHERE @rid IN :ids", {"ids": [rid_a, rid_b]})
db.query("sql", "SELECT FROM V WHERE @rid IN ?", [[rid_a, rid_b]])

A dict on its own (also [d] and (d,)) is the named map, so it cannot fill a ?. Engines before 26.11.1 bind a ? (or a :name) that has no key in the map to null and do not fail: db.command("sql", "INSERT INTO T SET v = ?", {"a": 1}) would store a record with no v. From 26.11.1 the engine refuses such a call and names the missing parameter (ArcadeData/arcadedb#11098: an ArcadeDBError caused by the engine’s CommandParameterMissingException); a key whose value is None still binds null. The bindings raise TypeError before the statement runs when a SQL or SQLScript call passes a lone dict and the statement has a ? and no :name or $name outside string literals. To fix the call, pass a list for a ? ([value]), or write :name in the statement and pass {"name": value}. A dict that you mean as one value goes in a list together with another positional value, or is bound by name. A dict with a digit-string key ({"0": value}) binds positional slot 0 by key, as the engine does, and is not refused.

The exact-count rule below is about positional values (a list, a tuple or separate arguments); it does not cover a dict.

The bindings' check counts the ?, $ and : characters of the SQL text, so its count is an upper bound: a ? inside a string literal counts as a placeholder, and a call that binds is never refused. The engine counts exactly: from 26.11.1 it also refuses fewer values than placeholders, with an error that names both counts (an ArcadeDBError wrapping the engine’s CommandSQLParsingException), where older engines bound null for the missing ones. Pass None for a placeholder you mean as null. db.query and db.command run the bindings' check; the async executor does not, so there the engine’s error reaches the error callback.

A query vector

A query vector for vectorNeighbors can be a numpy array or a list. This section is about statements: VectorIndex.find_nearest on the native package converts any numeric query to one float32 buffer itself, see find_nearest. The conversion before the statement runs is the cost to watch: a query takes about 0.3 to 0.75 ms, and a vector of 1536 numbers took 0.5 to 0.6 ms to convert when it was a list.

  • A float32 array crosses as one buffer on both builds: about 5 to 15 us. This is the fast path. np.asarray(v, dtype=np.float32) makes it from a list or from another array.

  • A list that holds only Python floats (no int, bool, None, str or numpy scalar) also crosses as one buffer. The statement reads the same list of doubles it always read: no value changes, and an int in a list stays an int. A list shorter than 32 numbers (JVM build) or 64 numbers (native build) is not worth a buffer and goes number by number. On the native build the list crosses as a buffer only when float32 holds every number exactly, which is true of a list from a float32 model (array.tolist()); a list of other doubles, NaN or an infinity is sent as text, because a buffer would round it.

  • A float64 array among the positional parameters (?) is a float32 array to the engine on both builds, so its numbers are rounded to float32 (a vector index stores float32). As a named parameter, inside CONTENT :row or as a property of a record you save, it keeps its doubles on both builds: 0.1 is stored as 0.1. Such a value goes as text, so give a float32 array when you want the buffer.

Results

db.query returns a result set you can iterate. What a row holds, how a Python value is converted and which rows are record objects is on Values and records. Two methods cover the usual needs:

  • first() returns the first row, or None when there is none, so if db.query(…​).first(): tests whether anything matched.

  • to_list() materialises the whole result as a list of dicts, one Python dict per row. Use it for a small result, or when you need the whole result in memory; iterate when you stop early. For a large result, see Reading large results.

  • iter_json_batches(batch_size=10_000) yields the rows as lists of JSON-native dicts, batch_size rows per list (the last may be shorter), with the same values as to_json_list(). On the native build batch_size bounds memory: the result set holds one reply of the cursor (2048 rows) and one batch at a time, and reads the next reply only when the next batch is asked for, so a large result is never held whole. On the JVM build a batch is serialised on the Java side one at a time. to_list(), to_json_list(), to_dataframe(), to_columns(), to_arrow() and to_polars() still hold the whole result, because they return it whole.

    One laptop study on the native build, 1,000,000 rows read with batch_size=10_000, 10 clean pairs of this change against the previous behaviour; each figure is the ratio of the new value to the old one, so below 1 is lower. Peak RSS: 0.286 (95% interval 0.284 to 0.289). Time to the first batch: 0.010 (95% interval 0.009 to 0.010). Wall time of the whole read: 0.958 (95% interval 0.952 to 0.966). This is one machine and one result shape, not a speed promise.

On the JVM build, Document.to_dict(), Result.to_dict() and get_property_names() cross into the JVM once per record or row, not once per property, and stepping through a result set takes one crossing per row, not two. The values and their Python types are the same as before. This was timed on the arcadedb-jvm package, built with the change and without it, one fresh process per run. The time is per record or row, in microseconds, and the table gives the ratio of the time with the change to the time without it (median over 12 clean pairs, 95% interval). Below 1 means less time.

What was timed Ratio with the change / without it

to_dict() of a Document, 1,000 records of 3 properties (INTEGER, STRING, DOUBLE)

0.307 [0.303, 0.311]

to_dict() of a Document, 1,000 records of 12 properties

0.133 [0.132, 0.135]

[row.to_dict() for row in db.query(…​)], 100 rows of 3 properties, the query start included

0.657 [0.649, 0.664]

[row.to_dict() for row in db.query(…​)], 10,000 rows of 3 properties, the query start included

0.474 [0.454, 0.480]

The control times json.dumps of rows already fetched, which uses no query and no JPype call. Its ratios on the four workloads are 0.996, 0.987, 0.993 and 1.000, and the same build measured twice gave ratios whose intervals contain 1. The figures come from one Linux x86_64 laptop with two cores for the process, Python 3.12, the median of 21 timed passes per run, and builds alternated in both orders. They cover the JVM build and these four workloads only, not wider rows, other value types, to_list(), or the native build.

On the JVM build, to_list() and iter_dicts() read the rows from the JVM as JSON text, in batches, and the C json module parses each batch. A value that JSON cannot carry exactly (a DECIMAL, a date or date-time, a set, a record id, an embedded document, a non-finite float) is tagged in the text, and a hook restores it after the parse. From 26.11.1.post3 a batch whose text holds no tag is parsed without the hook, and the bindings do not read the side array that the hook needs. The rows and their Python types are the same. A batch that holds a tagged value takes the hook as before; the study checked that its values are the same and claims no speed for it.

The table gives the time for each row of iter_dicts() over a table of LONG, DOUBLE and STRING properties, so that no value is tagged, for the 26.11.1.post3 wheel against the 26.11.1.post2 wheel. The query was issued before the timer started. The ratio is the median of the pair ratios (below 1 means less time) with its 95% interval. The setup was Python 3.12, one fresh process for each run, the two builds alternated in both orders (ABBA), the first round dropped, 20 clean pairs for each figure, and an A/A control (the post2 wheel twice) whose median ratio was between 0.97 and 1.03 in every row. The figures come from one Linux x86_64 laptop with two cores for the process. The post3 wheels were the ones that CI built from the tagged commit. The wheels on PyPI were checked for the same engine as 26.11.1 and were not timed again.

What was timed Ratio, post3 over post2 (95% interval) Clean pairs

iter_dicts() over a result of 1 row

0.749 [0.735, 0.777]

20

iter_dicts() over a result of 100 rows

0.763 [0.733, 0.782]

20

iter_dicts() over a result of 100,000 rows

0.755 [0.744, 0.770]

20

to_list() goes through the same parse, but its figures at 1 and 100 rows were too noisy to read at the round cap of the study, and the one at 100,000 rows did not resolve, so this page gives no figure for to_list(). The figures cover the JVM build and untagged rows only, not the native build. The study claims no gain in memory.

Reading large results

The row APIs build one Python dict per row: to_list(), to_json_list(), and a for loop over the result set. They suit small results. For a large result, read columns:

  • to_columns() returns a dict of column name to a numpy array, or to a Python list for text and for JSON-typed values.

  • to_arrow() returns a pyarrow.Table.

  • to_dataframe() returns a pandas.DataFrame.

  • to_polars() returns a polars.DataFrame, read through to_arrow().

These four build no dict per row. Both packages read the result as typed column frames and decode them with the same code, so one query gives the same column types and values on arcadedb (native) and on arcadedb-jvm. On the native package, the first rows of a query (up to 256) are the one exception: they arrive with the query as JSON, because the row APIs read them. A column read puts those rows into the same column rules as the later frames.

to_columns() needs numpy, to_arrow() needs numpy and pyarrow, to_dataframe() needs pandas, and to_polars() needs polars and pyarrow. to_columns() and to_arrow() return None when a library is missing. to_dataframe(convert_types=False) on arcadedb-jvm builds the frame from to_list() instead; on arcadedb the argument has no effect.

to_columns(), to_arrow() and to_polars() take batch_size, the most rows in one frame (default 25,000), and columns, a list of names to read as a projection would. to_dataframe() takes neither and uses 25,000. batch_size must be at least 1 (ValueError otherwise). A value above 2,147,483,647 (231 - 1) is clamped to 2,147,483,647. On the native package a frame also ends when the values it holds reach about 1 MiB.

Memory: these calls return the whole result, so it must fit in memory. The frames are decoded as they are read and kept until the end of the result. For a result of several frames, to_columns() and to_dataframe() then copy them into one array for each column, so the frames and the merged arrays are held together for a moment. to_arrow() keeps the frames as the chunks of one column. If the result does not fit, read it in pieces with iter_json_batches().

What the column calls return

  • The type of a column belongs to the whole result, not to where a frame ends. A frame that lacks a property, or holds only nulls for it, takes the type of the frames that have values. batch_size never changes a column’s type.

  • Nulls: in to_columns() and to_dataframe(), an integer column with a null is float64 with NaN (the pandas rule), a DATETIME column with a null holds NaT, and text and JSON-typed columns hold None. In to_arrow() and to_polars(), an integer column with a null stays int64 with a null. A float64 column cannot hold every integer beyond 253, so to_columns() and to_dataframe() round such an integer in a column that has a null. to_arrow() and to_polars() keep it exact.

  • An integer beyond 253 next to a floating value has no exact double, so the column is not made a float64 column. to_columns() gives a list of Python values, each an int or a float as stored. to_arrow() and to_polars() give a string column with the exact text of each value. to_dataframe() gives an object column of the same Python values, so the integer stays exact. This holds also when the integer and the floats are in different frames of a large result. Two reads still round the integer, because they build a float64 column: to_dataframe(convert_types=False) on arcadedb-jvm, and the fallback of to_dataframe() that reads through to_list() when the Java bridge classes are missing. Use to_columns() or to_arrow() when such a value must stay exact on those two reads.

  • A vector column is a column whose values are all float[], which is what a property declared ARRAY_OF_FLOATS holds. A LIST property of numbers is a list column, whatever its values.

  • Dates and decimals: DATETIME is datetime64[ms] in to_columns() and timestamp[ms] in to_arrow(), and datetime64[us] in to_dataframe(). A DECIMAL stays a Decimal (an object array in to_columns(), a decimal column in Arrow). A DATE in to_dataframe() is a datetime.date in an object column.

  • The arrays of to_columns() are writable.

  • A result with no rows gives {} from to_columns(). With columns=[…​], it gives those columns as empty lists. to_arrow() gives an empty table, or with columns=[…​] those columns empty and of the null type.

A vector cell has one form for each call:

Call Vector column (float[])

to_columns()

One (count, dim) float32 matrix. A null vector is a row of NaN.

to_dataframe()

One float32 numpy array in each cell, and None for a null.

to_arrow()

A fixed-size list of float32. A null vector is a null.

to_polars()

Array(Float32, n).

A double[] property gives float64 in each of these. If the vectors of a result do not all have one length, the column is a list of variable length: list<double> in Arrow, a list of lists in to_columns().

A result set is read once

A result set is a lazy cursor, and you read it once. The engine computes the rows as you read them, not in db.query(…​). Both packages behave the same way:

  • A second pass over a set that you read to its end returns nothing. The set closes itself at its end, and it reads as empty afterwards.

  • first() and one() close the set after they take their row. close() closes it too. A set closed before its end raises ArcadeDBError when you read it again, because the rows it had not returned are gone. Leaving a with block closes the set too.

  • count() reads and counts the remaining rows, so the set is empty after it.

  • An error in the statement can surface while you read the rows, not in db.query(…​). Every way of reading raises it as ArcadeDBError.

  • If you stop reading early, close the set, or use it in a with block, so the engine releases its scan. An unclosed set keeps the threads of the engine’s parallel scan parked for up to arcadedb.parallelScanAbandonedTimeout (10 minutes by default), and a few of them can stall the next query. Reading to the end also closes it.

Use to_list() when you need the rows twice. It reads the whole result into a list of dicts, which you can loop over as often as you like.

Record ids in rows

A record id inside a row value (SELECT @rid AS r, a LINK property, a list of links such as out('Knows').@rid) comes back as an RID object, not as text. str(rid) is the text ("#10:5"), so is an f-string, and get_rid() always returns text or None. Two RIDs of the same record are equal and hash alike, so they work in sets and as dict keys, and RIDs sort by bucket, then position. getBucketId(), getPosition(), toString() and isValid() work on the object. It goes back in as a query parameter (WHERE @rid = ?, or a list passed by name) and to db.lookup_by_rid(), as itself or as its text. to_json_list() is the exception: a record id in its rows is the text ("#10:5"), on both builds, and so is a map key that is not a string, because a JSON object has no other kind of key.

On the native build the record wrappers send their record id to the engine as a parameter, not in the statement text. The engine keeps a parsed statement in a cache keyed by its text (arcadedb.sqlStatementCache, 300 entries by default) and leaves a record id written in a FROM target as part of that text, so each record would be a cache entry of its own. get_out_edges(), get_in_edges(), get_both_edges(), new_edge(), Edge.get_in(), Edge.get_out(), save() and delete() send the id as a parameter, so every record shares one text. db.lookup_by_rid() keeps the id in the statement text: measured, the parameter path was 2 % slower for a lookup, so it stays as it is.

On the native build, save() of a new record and Vertex.new_edge read the record they create from the reply of the statement that creates it, so a create is one call into the library. save() of a saved record makes three calls: the transaction check, the SELECT of the stale-record check, and the UPDATE. With auto-transaction on (see Transactions) the transaction check cannot refuse, because the engine opens a transaction for a bare write, so from 26.11.1.post3 the check is left out and save() makes two calls. With auto-transaction off, the default, the check is still made, and a save() outside a transaction raises before it reads or writes anything. The records, their values and the errors are the same as before.

Vertex.new_edge() works on a vertex object that you read in an earlier transaction, on both builds. The first edge of a vertex rewrites its record, so an object read before that commit is out of date. The native build goes by record id. The JVM build looks up the current source vertex by its record id inside the active transaction and passes the target as its record id, so the engine loads that one too. The vertex object you hold is not refreshed: call db.lookup_by_rid() again to see its current properties.

row = db.query("sql", "SELECT @rid AS r, name FROM User LIMIT 1").first()
rid = row.get("r")
print(str(rid), rid.getBucketId(), rid.getPosition())   # #1:0 1 0
print(db.lookup_by_rid(rid).get("name"))                # the same record, from the object or from str(rid)

Compare record ids as str(rid). A row also has an identity of its own: result.get_rid() returns it for a record row, an expand() row and a projection of @rid that does not alias it, and None for any other projection.

JVM: reading a record id is one call

On the arcadedb-jvm package, from 26.11.1.post4, Result.get_rid(), Result.get_vertex(), Result.get_edge() and Result.get_element() each make one call into the JVM, through one method of a small Java helper in the bridge jar. Before, each of these made three or four JPype calls. When the bridge class is not available, the methods keep the plain path. Document.get_rid() of a record object is not changed: it keeps the plain path of 26.11.1.post3. The arcadedb (native) package does not change.

The table is one study of the arcadedb-jvm package on a laptop and on a second Linux machine, the mini: the time for each operation of the post4 wheel against the 26.11.1.post3 wheel. The ratio is the median of the pair ratios (below 1 means less time) with its 95% interval. Setup: Python 3.12, one fresh process for each run, two cores for the process, the two builds alternated in both orders (ABBA), the first round dropped, and an A/A control (the post3 wheel twice) whose median ratio was between 0.988 and 1.011 on the laptop and between 0.994 and 1.010 on the mini. The post4 wheels were built by CI from commit e83947e, the commit of the tag, and the post3 wheels were the ones that CI built from the post3 commit. The files on PyPI were checked for the same engine as 26.11.1 (see The engine of 26.11.1.post4) and were not timed again.

What was timed Ratio on the laptop, post4 over post3 (95% interval) Ratio on the mini (95% interval) Clean pairs (laptop, mini)

Result.get_rid() over 1 row of a document type

0.735 [0.699, 0.754]

0.680 [0.666, 0.688]

15, 13

Result.get_rid() over 100,000 rows of a document type

0.618 [0.605, 0.630]

0.596 [0.592, 0.605]

15, 13

Result.get_rid() over 100,000 rows of a vertex type

0.604 [0.592, 0.622]

0.599 [0.589, 0.605]

15, 13

Result.get_rid() over 100,000 rows of an edge type

0.616 [0.612, 0.626]

0.595 [0.591, 0.600]

15, 13

Result.get_vertex() over 100,000 rows

0.743 [0.728, 0.767]

0.738 [0.724, 0.756]

15, 13

Result.get_edge() over 100,000 rows

0.742 [0.727, 0.752]

0.739 [0.722, 0.763]

15, 13

Result.get_element() over 100,000 rows

0.762 [0.748, 0.764]

0.755 [0.740, 0.777]

15, 13

Memory did not improve: across the 172 figures of the whole post4 study the median of the peak resident size, post4 over post3, was 1.004, and this page makes no memory claim for these calls.

Pagination

Page a large type by @rid instead of SKIP: each page is WHERE @rid > ? LIMIT ?, and the cursor is the @rid of the last row of the previous page. Start with "#-1:-1". The script above runs the loop with a page size of one so that two short pages print.

SQLScript

db.command("sqlscript", script) runs several statements in one call, and the result set holds the last executed statement, which is why the script above reports the ALTER TYPE statement. The script syntax is in the SQLScript reference.

openCypher

The same two calls run openCypher: pass "opencypher" as the language. The script creates an edge with a CREATE pattern and reads it back with MATCH. The language is documented in the openCypher reference.

ArcadeDB SQL has no JOINs, so SQLAlchemy and other ORMs, and tools that generate JOIN queries, do not work with these two calls. Write the statements yourself, following links as in No JOINs, or use openCypher for relationships.

Database settings

An engine setting of scope DATABASE, which includes every arcadedb.vectorIndex.* setting, is changed on one open database with SQL, which db.command runs like any other statement:

db.command("sql", "ALTER DATABASE `arcadedb.vectorIndex.rebuildGraphRatio` 0.05")

The key is backquoted because it contains dots. The change applies to that database only. A setting of scope SERVER is refused there, with a message that names SET SERVER SETTING. For the vector settings, the index statistics show the value in force: index.get_stats()["effectiveMutationsThreshold"] is the number of changes after which the graph is rebuilt, computed from mutations_before_rebuild, rebuildGraphRatio and maxPendingMutations (see the create_vector_index docstring).