Getting Started Embedded

Creating a New Database

Using the Code

The simplest way to create a database is by calling the Database.New() static method: this is intended to be used in a context of a single database.

In its easiest form, the method requires only the name of the database, the administrator name and password: at the end of the call you will have an object that represents the database.

var db = Database.New("testdb", "SA", "abc12345");

A more advanced version of this method requires a IConfiguration object instance, that contains the configurations needed by the database: when not passed, or if null, the database will use the default configurations.

var config = new Configuration();
config.SetValue("database.storageType", "jounaled");

var db = Database.New(config, "testdb", "SA", "abc12345");

A list of the built-in configuration keys is available at this link.

An even more advanced way to create a database is using a SystemBuilder, that allows the specification of custom services, replace object managers, attach systematic event managers, modules for the definition of custom types, functions, stored procedures and many other features. This getting started guide is not covering the advanced aspects of building a database, but only give a kick-``start.

Opening an Existing Database

When creating a database using the Database.New call without specifying any custom configuration, this uses the In-Memory storage system for it and at its disposal all the data get lost, as removed from the memory block that holds the database.

To re-open a physical database from a disk or a medium, it is necessary to specify a set of custom configurations to tell the system the kind of physical storage to use and the path to the database (some storage types use folders, other are one-file only).

var config = new Configuration();
config.SetValue("database.path", "./testdb.db");
config.SetValue("storage.type", "single-file");

var db = Database.Open("testdb", config);

Authenticating

Before any operation can be executed, the user must be authenticated: creating or opening a database is not enough for interacting with it. In fact, any operation is executed in an isolated context (transaction) that is wrapped into an authenticated session, that checks user rights (eg. select from a certain table, delete data from a certain table, create objects into a given schema, etc.).

A user can be created as the moment the database is created (the administrator) or in a later moment.

var session = db.CreateSession("SA", "12345");

If the user for which to open the transaction does not exist, an error will occur.

A session is backed by Transaction, that is an isolated execution context for the commands (queries) passed to the database. When none isolation level is specified (or when unspecified level is set), the system selects a default level (serializable). The database coordinates the access (for read or write) to resources through concurrent transactions, persisting the work done during the transaction at Commit command. Any work not committed, or on explicit call to Rollback, will be discarded at the end of the transaction.

Executing Statements

Once the user obtains an authenticated session it is possible to execute statements to interact with the database, modeling data (Data Modeling Language - DML), defining data (Data Definition Language - DDL), controlling access to data.

Statements can be executed on several levels of a query context: a IQuery object is the root of a query context and can be obtained only from an authenticated session.

var query = session.CreateQuery();

Data Modeling commands and security commands can be executed only at the top level, while data definition statements can be executed at any level of a context (eg. in a LOOP context, or in a PL/SQL block).

// This can be executed only at IQuery level
query.CreateTable("test", new [] { 
     new SqlTableColumn("a", PrimitiveTypes.Integer()),
     new SqlTableColumn("b", PrimitiveTypes.VarChar(), true)
});
query.AddPrimaryKey("test", "a");

// Since IQuery is a IRequest like Block objects the following will 
// be possible to executed also at IQuery level, as much as any level
var values = new List<SqlExpression[]> {
    new[] {
       SqlExpression.Constant("Antonello"),
       SqlExpression.Constant("Provenzano"),
       SqlExpression.Constant(true)
    },
    new [] {
       SqlExpression.Constant("Mart"),
       SqlExpression.Constant("Roosmaa"),
       SqlExpression.Constant(false)
    }
};

var count = Query.Insert(tableName, columns, values.ToArray());

Programmatic Statements

The concept of statement is generic and can be defined as a single instruction to a system, that is executed within a context. DeveelDB implements a set of code statements to support the SQL model (eg. CREATE TABLE, SELECT, INSERT, etc.), which are derived from SqlStatement class.

Implementations of SQL statements in code are composed of three main components:

1. Metadata - properties required to instruct the system about the statement to execute
2. Preparation - routines executed to prepare a statement for its execution
3. Execution - the execution plan of the prepared statement within the system

DeveelDB supports the definition and execution of custom defined statement

// Create a table statement object
var statement = new CreateStatement("test_table", new[] {
                                            new SqlTableColumn("id", PrimitiveTypes.Integer()) {
                                                IsNotNull = true,
                                                IsIdentity = true
                                            },
                                            new SqlTableColumn("name", PrimitiveTypes.Integer())
                                        });

A major advantage of working with a statement object can be stored as binary or to build a statement plan dynamically from code, instead of using strings.

To execute a statement all that one has to do is simply

// execute the statement within the query

query.ExecuteStatement(statement);

... or alternatively ...

// or from the statement itself

statement.Execute(query);

Preparation

Invoking the statement execution will trigger the preparation of the statement, if this is preparable: this means that the expressions contained in the statement will be reduced (eg. variables resolved, query parameters replaced, etc.), and the statement metadata will be prepared (eg. partial object names will be resolved against current schema, type references will be resolved, etc.).

In some cases, a statement is prepared into another instance of statement (eg. SelectStatement is prepared into a SelectStatement.Prepared, that includes the evaluated query plan): because of this reason, if you want to save the statement in its prepared form, it is never safe to cast the result of the preparation to determined type.

SQL Compiled Statements

Being a system based on a language model well defined (ANSI SQL-99), DeveelDB supports the execution of statements expressed in string format, that follow the SQL standard.

// this will create a cursor that can be iterated
var result = query.ExecuteStatement("SELECT a.id, b.* FROM table1 a, table2 b WHERE a.date > '2015-04-07'");

A Working Example

To sum it all up, a possible usage scenario would be the following

var db = Database.New("testdb", "SA", "12345");

using(var session = db.CreateSession("SA", "12345")) {
    using(var query = session.CreateQuery()) {
        query.CreateTable("test", new [] { 
            new SqlTableColumn("a", PrimitiveTypes.Integer()),
            new SqlTableColumn("b", PrimitiveTypes.VarChar(), true)
        });
        query.AddPrimaryKey("test", "a");

        var values = new List<SqlExpression[]> {
            new[] {
                SqlExpression.Constant("Antonello"),
                SqlExpression.Constant("Provenzano"),
                SqlExpression.Constant(true)
            },
            new [] {
                SqlExpression.Constant("Mart"),
                SqlExpression.Constant("Roosmaa"),
                SqlExpression.Constant(false)
            }
        };
        Query.Insert(tableName, columns, values.ToArray());
        Query.Commit();
    }
}