RegisterSign In

Introduction to SQL in MckoiDDB

With the release of MckoiDDB 1.1 comes the introduction of an optional SQL package that includes a data model with tables, typed attributes and indexes, and an SQL query parser and optimizer. The MckoiDDB SQL package can be downloaded from the Downloads page. This guide details how to install the SQL data model on an MckoiDDB network and introduces the SQL API with some simple examples.

Please note that the SQL data model for MckoiDDB is a work in progress. The final section of this guide details where we are with it and the direction we plan to take in the future.

If you haven't already done so, it is suggested you familiarize yourself with the components of a MckoiDDB network before continuing with this guide. If you would like to run the sample code, it is assumed you have a MckoiDDB network up and running (the Quick Start Guide explains how).

The demo\sqldemo\ directory in the release distribution contains the code demonstrated in this article. It is assumed any commands described in this article are run from this directory.

Table of Contents

  1. Setting up an SQL Instance
  2. Connecting to the SQL instance from your application
  3. Capabilities of the SQL Data Model
  4. An example using the API
  5. The Database Viewer Tool
  6. Current Features and Future Plans

Setting up an SQL Instance

MckoiDDB is a software system that manages the distributed resources of machines over a network. MckoiDDB is designed such that it delegates the code that manages the format of data structures to separate data model packages, and the data model packages provide an API for developers to use to interact with the data. An example of a data model package is the Mckoi Simple Database API which is included in every MckoiDDB distribution (see the documentation for details about this package). Like the Simple Database API, the SQL module is also implemented as its own data model package and installed the same way as any other data model is installed.

To install a Mckoi SQL instance on your MckoiDDB network you must first make sure to include the mckoiddbsql.jar file in the classpath on the machine node assigned as the Root server. One easy way to manage this is to simply deploy the mckoiddbsql.jar file over all your Machine Node servers.

Once the .jar file has been deployed, you must use the administration console to create a new path instance and initialize your SQL database. The administration console can either be accessed using the 'console' script or by running the following Java command;

java -cp ..\..\lib\mckoiddb.jar com.mckoi.runtime.AdminConsole -netpassword [the network password]

To create an SQL path instance called 'testsqldb' the following command might be entered at the prompt;

add path com.mckoi.tabledb.TableDatabase testsqldb to 127.0.0.1:3500

The above example assumes you have set up a single machine installation on your local machine as described in the Quick Start Guide. If you are running MckoiDDB over a network, 127.0.0.1:3500 would instead be the IP address of the machine you have set as the root server. After successfully running this command the SQL database will be initialized and ready to accept queries. If this command reports an error, a likely problem is that the SQL .jar file (mckoiddbsql.jar) was not included in the classpath of the Machine Node assigned as your root server.

It's a good idea at this point to check the current paths you have set up on your network. You can do this by using the 'show paths' command from the console prompt which should now include the following report;

MckoiDDB> show paths
Root server: 127.0.0.1:3500

+Name:            testsqldb
 Consensus Class: com.mckoi.tabledb.TableDatabase
 Status:          2169 bytes (in 6 objects) commit stats: 0(0 ms) 0(0 ms)

Connecting to the SQL instance from your application

Note that you must include the mckoiddbsql.jar file in the JVM classpath for all the following code samples to work.

Once you have created the SQL database instance called testsqldb, you can connect to it from a client in the network by using the following Java code.

import com.mckoi.network.*;
import com.mckoi.sql.*;
import com.mckoi.type.*;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Properties;

public class MyExampleSQLApp {

  public static void main(String[] args) {

    // The following property defines the location of the machine node
    // assigned as the manager role in the network, and the network
    // password.
    Properties prop = new Properties();
    prop.setProperty("manager_address", "127.0.0.1:3500");
    prop.setProperty("network_password", [your network password]);

    // Connect to the network,
    MckoiDDBClient client;
    try {
      client = MckoiDDBClientUtils.connectTCP(prop);
    }
    catch (IOException e) {
      e.printStackTrace();
      return;
    }

    // Create a TDBMSSession object on our named path.
    TDBMSSession session = new TDBMSSession(client, "testsqldb");

    // Wrap the session with an SQL connection interface
    SQLConnectionInterface connection =
                           new LocalConnectionInterface(session);

    // ... Now we can start a transaction and interact with the
    // database ...


  }
}

The code sample above needs to be modified for your specific network environment. The manager_address property should be set to the location of the manager server in your network and the network_password set to the password assigned. If this code fails to connect then you should check that the network.conf file of all the machine nodes in the network includes the IP address of the client machine in the connect_whitelist property.

Capabilities of the SQL Data Model

Before we continue to some examples, I will briefly explain some of the capabilities of the SQL data model as implemented. This will include an overview of the data structures and their concurrency control model (how objects behave when updated concurrently).

The two primary objects implemented in the MckoiDDB SQL module are tables and indexes. A table has a set of columns each with a name and type that may be added to and removed at any time. A table also has a set of rows, each row contains a number of attribute values. Rows may be added, updated or removed freely. The rows stored in a table are traversed by means of a cursor.

The second type of structure is an index. An index stores a specific order of rows in a table. The index object contains a definition that describes the order, and the content of the index itself which is typically a set of references to rows in relevant order. An index object may be queried to quickly find which rows meet a criteria (for example, find a 'price between 3000 and 4000').

When a client wishes to access or update data objects it must do so by first starting a transaction which provides a consistent snapshot view of the database. MckoiDDB provides strongly consistent transactions meaning it is impossible for a transaction to see changes made in other concurrent transactions no matter what a client does in their own snapshot. This is called Multi-Version Concurrency Control. A transaction may be discarded at any time therefore throwing away (rolling back) any changes made to the snapshot. For a change to be made visible to transactions in the future, the client must successfully commit.

When an update is made and committed, the commit function may raise a com.mckoi.network.CommitFaultException if any one of the changes made is not possible or inconsistent because of a clash with a change committed by a concurrent transaction. A commit fault exception is raised in the following situations;

In the situations where one or more transactions make conflicting changes, the transaction that is the first able to successfully commit will be the one whose changes will be in future transactions. The other transactions will fail and rollback. Your application should catch the commit fault exception and handle this situation if your application is designed such that conflicting changes are likely to happen, and retry the changes.

An example using the API

With a general description of the capabilities behind us, I will now move on to some Java code to demonstrate how to use the API to put these objects to use.

In this example we will create a table called Books with the following columns, name, author, year published, has robots. Two indexes will also be defined for the name and has robots column. We will add some book information to the table and perform an SQL query on the data.

The following code demonstrates creating the table and indexes;


    // The SQL connection object (see the example in the
    // Connecting section)
    SQLConnectionInterface connection = ...

    // Open a transaction
    connection.openTransaction();

    // If the table doesn't exist, create the table and index.
    // (This is so we can run the example code multiple times)
    if (!connection.tableExists("APP.Books")) {

      // Create the table
      connection.createTable("APP.Books");

      // Add the columns
      connection.addColumn("APP.Books", "name", Type.STRING);
      connection.addColumn("APP.Books", "author", Type.STRING);
      connection.addColumn("APP.Books", "year published", Type.INTEGER);
      connection.addColumn("APP.Books", "has robots", Type.BOOLEAN);

      // Create an index 'idx_books_hasrobots' on the 'has robots' column.
      connection.createIndex("idx_books_hasrobots", "APP.Books", "has robots");
      // Create an index on the 'name' column.
      connection.createIndex("idx_books_name", "APP.Books", "name");
    }

    // Commit the operations.
    try {
      connection.commitTransaction();
    }
    catch (CommitFaultException e) {
      // Commit fault (this would happen if another client
      // concurrently created a table or index with the same name.
      e.printStackTrace();
      return;
    }


Note in the above example that the index definitions each need a unique name. This is so they can be referenced in schema queries or if one needs to be removed.

Now the table has been created, we need to populate it with some data. The following example adds some book information to the table.


    // Open a transaction
    connection.openTransaction();
    // Fetch the books table
    MutableTableDataSource table = connection.getTable("APP.Books");
    // Check if the table is empty.
    // (This is so we can run the example code multiple times)
    if (table.getRowCount() == 0) {
      // Insert a new row,
      table.insertRow();
      // Set the attributes of the row,
      table.setCellContents(TypedValue.stringVal("The Hitchkiter's Guide to the Galaxy"), 0);
      table.setCellContents(TypedValue.stringVal("Douglas Adams"), 1);
      table.setCellContents(TypedValue.integerVal(1979), 2);
      table.setCellContents(TypedValue.booleanVal(true), 3);
      // Complete the 'insertRow' operation
      table.completeOperation(true);

      // Insert some other data into the table. For brevity we'll use a loop
      // for the rest of the rows.

      ArrayList<Object[]> data = new ArrayList();
      data.add(new Object[] { "Destination: Void", "Frank Herbert", 1966, true });
      data.add(new Object[] { "I, Robot", "Isaac Asimov", 1950, true });
      data.add(new Object[] { "2001 : A Space Odyssey", "Author C. Clarke", 1968, true });
      data.add(new Object[] { "The Rest of the Robots", "Isaac Asimov", 1964, true });
      data.add(new Object[] { "Dune", "Frank Herbert", 1965, false });
      data.add(new Object[] { "Nineteen Eighty-Four", "George Orwell", 1949, false });
      data.add(new Object[] { "Fahrenheit 451", "Ray Bradbury", 1953, true });

      for (Object[] item : data) {
        table.insertRow();
        table.setCellContents(TypedValue.stringVal((String) item[0]), 0);
        table.setCellContents(TypedValue.stringVal((String) item[1]), 1);
        table.setCellContents(TypedValue.integerVal((Integer) item[2]), 2);
        table.setCellContents(TypedValue.booleanVal((Boolean) item[3]), 3);
        table.completeOperation(true);
      }

      // Commit the operations.
      try {
        connection.commitTransaction();
      }
      catch (CommitFaultException e) {
        e.printStackTrace();
        return;
      }

    }


The final example performs an SQL query that finds the books that have robots and orders the result by the name of the book.


    // Open a transaction
    connection.openTransaction();

    // A query,
    QueryStatement stmt = new QueryStatement(
           "SELECT * FROM APP.Books WHERE \"has robots\" = true ORDER BY \"name\"");

    // Perform a query and display the results,
    TableDataSource result = connection.executeQuery(stmt);

    // Use a cursor to traverse the result,
    RowIterator iterator = result.getRowIterator();
    while (iterator.hasNext()) {
      long rowid = iterator.next();
      // Get the book name and print it
      TypedValue book_name = result.getCellContents(0, rowid);
      System.out.println(ValueProcessor.castAsJavaString(book_name));
    }

The Database Viewer Tool

The SQL package includes a GUI tool that allows a user to browse through the database instances in a MckoiDDB network and a simple interface for inspecting and querying the contents. The Database viewer tool is started by using the following command;

java -cp ..\..\lib\mckoiddbsql.jar com.mckoi.tools.DBViewer

For convenience, the DBViewer tool supports an optional argument that specifies the location of a configuration file containing the address of the manager server and the network password. The configuration file must define two properties; manager_address which is set to the location of the manager Machine node, and network_password which is set to the network password defined for the MckoiDDB network. If this argument is not provided, a dialog appears for the user to enter this information manually.

If the DBViewer tool is unable to connect to the network then you should check that the network.conf file of all the machine nodes in the network includes the IP address of the machine you are running the DBViewer tool on, in the connect_whitelist property.

Assuming the example from the previous section has been run then you should be presented with a screen similar to the following;

The section on the left of the window displays a tree view of all the path instances in the network. Expanding the tree allows you to browse through all the data objects. The section on the right displays the content of the selected data object and the tab pane at the top allows you to change to a query panel on the selected path. Note also the Refresh Snapshot button at the top left, which updates the snapshot view of the currently selected database (you would use this after the data is changed so you can see the change).

To run an SQL query on the database, one of the Query tabs must be selected. The following screenshot demonstrates an SQL query run on the testsqldb path instance (the query is; find all the books by authors who haven't written a book that has robots).

The Database Viewer tool can also display the process plan for an SQL query. This is a useful feature for seeing exactly what the software does when it runs a query. The following screenshot demonstrates this feature.

The Database Viewer tool also supports inspecting the objects stored in a Simple Database data model. A Simple Database path instance on the network will show up as an additional entry in the left tree.

Current Features and Future Plans

The SQL data model for MckoiDDB is a work in progress. The goal for this package is to support the most useful features found in existing SQL management systems that are appropriate to be implemented in the distributed storage system implemented by MckoiDDB. An overview of the current list of features;

Features that are planned in the near future in order of priority;

What will not be implemented because the feature is not compatible with a distributed environment;

The text on this page is licensed under the Creative Commons Attribution 3.0 License. Java is a registered trademark of Oracle and/or its affiliates.
Mckoi is Copyright © 2000 - 2017 Diehl and Associates, Inc. All rights reserved.