Creating a PartiQL Database (Example)

If there are inaccuracies discovered with this documentation, please submit a GitHub issue.

Introduction

A PartiQL database implementation is the software that provides the necessary parsing, planning, compilation, and execution functionality to end-users to create, modify, and query data. From the perspective of database developers, the PartiQL library provides most, if not all, the functionality out-of-the-box.

A PartiQL database instance, on the other hand, contains both a database implementation and the physical data of a user(s). This physical data (whether it be tables, views, functions, or more) is represented as a collection of catalogs (see Implementing a Catalog).

Who is this for?

This usage guide is aimed at developers who want to understand how the different components of the PartiQL library interact to create a database instance. This basic guide will plug all the components together to create a fully-functional database instance, without going into all the possible functionality.

Prerequisites

This usage guide makes heavy use of Implementing a Catalog, Using the Parser, Using the Planner, and Using the Compiler. For a deep dive into how to use all the APIs relevant to each component, please refer to the usage guides mentioned.

To get the APIs discussed in this usage guide, please take a dependency on the following packages.

build.gradle.kts

dependencies {
    implementation("org.partiql:partiql-spi:1.+")
    implementation("org.partiql:partiql-plan:1.+")
    implementation("org.partiql:partiql-planner:1.+")
    implementation("org.partiql:partiql-parser:1.+")
    implementation("org.partiql:partiql-ast:1.+")
    implementation("org.partiql:partiql-eval:1.+")
}

Alternatively, you may take a dependency on partiql-lang, which consumes all the core packages.

build.gradle.kts

dependencies {
    implementation("org.partiql:partiql-lang:1.+")
}

Basic Implementation

The simple application expects a PartiQL query as the first argument to main. The query will be evaluated against the IonCatalog (discussed in Implementing a Catalog).

In the below example, we will create the parser, planner, and compiler (comprising the database implementation). Then, we will register the catalog and the session (comprising the database instance). With all pieces plugged together, this example compiles and executes a user-provided query and prints the result to standard output.

MyDB.java

import org.partiql.ast.Statement;
import org.partiql.eval.Mode;
import org.partiql.eval.compiler.PartiQLCompiler;
import org.partiql.parser.PartiQLParser;
import org.partiql.plan.Plan;
import org.partiql.planner.PartiQLPlanner;
import org.partiql.spi.catalog.Catalog;
import org.partiql.spi.catalog.Session;
import org.partiql.spi.types.PType;
import org.partiql.spi.value.Datum;

import java.util.List;

public class MyDB {

    public static void main(String[] args) {
        // Get the query
        assert args.length == 1;
        String query = args[0];

        // Create the database components (ideally this isn't done per-invocation, however, for simplicity
        // of this user-guide, we will do it this way)
        PartiQLParser parser = PartiQLParser.standard();
        PartiQLPlanner planner = PartiQLPlanner.standard();
        PartiQLCompiler compiler = PartiQLCompiler.standard();

        // Create your catalogs (where the data and functions reside)
        Catalog ionCatalog = new IonCatalog();
        String currentCatalog = ionCatalog.getName();

        // Create a session that registers the available catalogs and specifies the current catalog.
        // This Session builder will also register the system built-in functions and operators.
        Session session = Session.builder()
                .catalogs(ionCatalog)
                .catalog(currentCatalog)
                .build();

        // Parse Query (and only allow a single statement, for simplicity)
        PartiQLParser.Result parseResult = parser.parse(query);
        List<Statement> statements = parseResult.statements;
        assert statements.size() == 1;
        Statement statement = statements.get(0);

        // Plan the statement
        PartiQLPlanner.Result planResult = planner.plan(statement, session);
        Plan plan = planResult.getPlan();

        // Compile the plan (in strict mode, in this example)
        org.partiql.eval.Statement executable = compiler.prepare(plan, Mode.STRICT());
        Datum lazilyEvaluatedData = executable.execute();

        // Print out the result
        printResult(lazilyEvaluatedData);
    }

    /**
     * This recursively iterates over the data and prints it to System.out
     * @param d the data
     */
    private static void printResult(Datum d) {
        if (d.isNull()) {
            System.out.println("null");
            return;
        }
        if (d.isMissing()) {
            System.out.println("missing");
            return;
        }

        PType type = d.getType();
        int typeCode = type.code();
        switch (typeCode) {
            case PType.INTEGER -> {
                System.out.println(d.getInt());
            }
            case PType.BOOL -> {
                System.out.println(d.getBoolean());
            }
            case PType.ARRAY -> {
                System.out.println("[");
                d.forEach(MyDB::printResult);
                System.out.println("]");
            }
            // and more...
            default -> {
                System.out.println("UNHANDLED_TYPE: " + type);
            }
        }
    }
}

Note that this example does not handle `PRuntimeException`s, nor does it provide user-readable error messages to end-users. If you are interested in learning about how to take advantage of the PartiQL library’s error reporting mechanism, please read the Usage Guide: Error Handling usage guide.