Databases and queries

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

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

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