DataCaster User API Guide

This section describes how to use the User API provided with DataCaster for direct access to internal database objects such as tables and indexes. Please see the JDBC section for information on using JDBC to access the database.

Java applications can use a JDBC connection and SQL statements to store and access data in DataCaster, much like they do with any other database. However, on occasion applications may want to directly access the internal tables and indexes of the database, in order to improve performance of key operations, or accomplish a task that cannot be done with the existing JDBC driver and SQL query processer. Note: This User API is a DataCaster-specific API, and using it would increase the effort needed to port your applications to other databases.

DataCaster provides a direct Java API into the internal tables, indexes and other objects in the database. Using this API, applications can more directly query and manipulate database objects, bypassing the need to rely on the DataCaster SQL and query processor normally used with JDBC. You can mix User API access with SQL statements to get the benefit of both in your applications.

Access Levels

There are two levels of access into the internal APIs.

  • User Level The first is a user API with authorized and controlled access to the internal database objects, .
  • Admin Level There is also an Administrator API for more complete access to the internal objects of the database. This should be used with caution as administrators, once authenticated, have full control of all internal objects in the database.

This section deals with the User API. Please see the Admin API section for information on the Admin level access. Use the User API Javadocs as a reference while using this guide.

Embedded And Remote Database Connections

With DataCaster, Java applications connect to the database in different ways. These include embedding the database in the application, or using a separate standalone database. In the latter case, the Java application may access the standalone database over the network.

In the embedded case, the database is co-resident with the application in the same JVM and provides the User API directly to the application. With the remote standalone database, the User API is provided over an RMI proxy, in a transparent manner.

While DataCaster supports both modes of access, the best performance for a given application is obtained by embedding the database with the Java application. In this case, the database provides very low-overhead connections and a number of performance benefits. However, in some cases multiple applications in separate servers or JVMs need to connect to the standalone database and the RMI connection has to be used.

There is only a small difference in using the two modes of operation as far as the User API is concerned. The following describes how to connect to the database in each case:

  • Embedded Case: To use the User API in the embedded case, the com.applibase.db.user.DatabaseImpl class is used to create a connection to the database. In this case only a simple database name is required to connect. For example, 
    Database db = new DatabaseImpl("productsdb", "myname", "mypasswd");
  • Remote Case: To use the User API in the remote database case, the com.applibase.db.rmi.client.DatabaseImpl class is used to create a connection to the database. In that case a URL is used to connect to a remote database. The URL has the form: 
    rmi://server_name:port_number/database_name

The code examples below are for the embedded case. To use the example code for the remote case, simply replace the database connection with 

Database db = new com.applibase.db.rmi.client.DatabaseImpl(url, "myname", "mypasswd")
where url is the RMI connection URL as described above. All other statements should need no change.

Database Objects

DataCaster is a relational database, it is built using a core set of objects representing the elements of a relational database. These include tables, indexes, tuples (rows), keys, schemas, views, etc. Each of these objects is described below. For more information on the organization of data within the database, please see the section on Database Organization.

With the User API, each relational database object has a defined Java Interface. Please see the API Javadocs for details on the interface for each of the objects. We will not attempt to provide a comprehensive tutorial on databases and database objects. Please consult other database texts for introductory material on databases. This is a brief description of the database objects to serve as a quick refresher before using the direct User API into the database.

  • Table A relational database table is a set of columns and rows (also called tuples). A Table consists of a set of Tuple instances (rows), or more generally a bag of tuples since duplicates are permitted for SQL compatibility.
  • Column A column in a relational table, which consists of a name and a type.
  • Tuple A Tuple is a row in a database Table, and is a map of Column instances to values.
  • View A View is a table, which may be virtual and is created from other tables using a table (relational algebra) expression.
  • Type A type defines a range of values, to help specify the values in table cells. A Type instance must be created for every data type, including user types like Java objects, before they can be used with the database.
  • Domain A domain defines a set of values that a table cell in a particular column may contain.
  • Index An index is a organization structure for tables, where table rows are organized by key values for efficient lookup of desired data.
  • Statement A statement, or SQL statement, is an SQL query or update command that users and applications create and use. Statements are executed by the database on behalf of the user to perform queries and operations on the database.
  • Key A key instance is a set of values for some subset of the columns of a table.
  • Schema A schema is the container for base tables, views and other useful database objects.
  • Transaction A Transaction is an atomic unit of work across multiple tables, and is tracked in this case on behalf of a single user thread.
  • Constraint A Constraint is an object used to ensure that changes made to the database conform to what the application permits.
  • Catalog A catalog is a container for schemas, and a database contains one or more catalogs.

You will work with these and other objects when using the direct database API to create, manipulate and query data in the database.

Simple Example

Let's go through a "Hello World" example to quickly learn how to use the User API.

First you need some imports from the User API. 

import com.applibase.db.Database;
import com.applibase.db.Table;
import com.applibase.db.Tuple;
import com.applibase.db.user.DatabaseImpl;
This is a simple Java application program to be run from the command line without arguments. Hence the class main method is where we do our work. 
public class SimpleExample {

    public void main(String args[]) {
The database needs to be opened and connected to, which is done by the following line. We use a database named products with specified username and password. 
        Database db = new DatabaseImpl("productsdb", "myname", "mypasswd");
Next we get an handle for the database table named products. 
        Table table = db.getTable("products");
Next we iterate the table, and get each tuple. For each tuple we print the product and price column values. 
        for (Iterator it = table.iterator();it.hasNext();) {
            Tuple tuple = (Tuple) it.next();
            System.out.println(tuple.value("product") + ": $" + tuple.value("price"));
        }
To end, we close the database connection. Closing the database is always a good idea, to mimize delays on restart, and avoid issues with saving data and database recovery. 
        db.disconnect();

The illustrates how to access an internal database table and it's tuples (rows), as well as values within the table rows.

User API Examples

We now look at examples of using the User API for many common functions performed on the database by applications. Many of the database operations with the User API described here can be accomplished with SQL statements. There is no need to go to a separate JDBC connection to run SQL statements. You can mix SQl statements with API operations as needed. Please see the section below on using SQL statements with the User API for more information.

For the following examples, we do not include the enclosing programs, or the imports required, to keep the presentation simple and more readable. And you will want to catch Runtime exceptions where appropriate, even though the examples do not show that.

Using Transactions
Transactions are important in working with databases, and you can use them with the User API. For many operations, the operation would work without using a transaction, and an internal auto-commit transaction would be performed on behalf of the user. In at least some cases you will want to create explicit transactions to acheive atomicity for a set of tasks or ensure rollback and other actions upon failure.

The following shows how to use transatcions with the User API. 

        Database db = new DatabaseImpl("productsdb", "myname", "mypasswd");

        Transaction tx = db.startTransaction();
        try {
            // Do the work here

            tx.commit();
        } catch (Exception ex) {
            tx.rollback();
        }
        db.disconnect();

Create Table
The following shows how to use the API to create a database table. 

        Database db = new DatabaseImpl("productsdb", "myname", "mypasswd");
        Schems schema = db.getDefaultSchema();
        
        String columns[] = { "id", "product", "price", "description" };
        String types[] = { "INTEGER", "VARCHAR(100)",  "FLOAT", "VARCHAR(500)" };
        Table table = schema.createTable("products", columns, types);
        db.disconnect();

A number of variations of the above are available, please see the User API Javadoc for more information. It may be easier in most cases to create the table with SQL, as shown in a later section.

Insert Rows
This example shows how to insert new rows (tuples) into a table. This shows how to create a new Tuple instance for a table from a set of columns and values. 

        Database db = new DatabaseImpl("productsdb", "myname", "mypasswd");
        Table table = db.getTable("products");
        
        String columns[] = { "id", "product", "price", "description" };
        String values[] = { "1", "widget",  "1.99", "Made in China" };
        
        Tuple tuple = table.tupleInstance(names, values);
        table.add(tuple);

        db.disconnect();

There is also an addAll method to add a set of tuples in one method.

Update Rows
This example shows how to update rows (tuples) in a table. In this example we create a Key instance and a modified Tuple instance, which are used in the update() call. 

        Database db = new DatabaseImpl("productsdb", "myname", "mypasswd");
        Table table = db.getTable("products");

        String keyColumns[] = { "id" };
        String keyValues[] = { "1" };
        Key key = table.keyInstance(keyColumns, keyValues);
        
        String columns[] = { "id", "product", "price", "description" };
        String values[] = { "1", "widget v 2",  "19.99", "Made in China circa 1999" };
        Tuple tuple = table.tupleInstance(names, values);
        
        table.update(key, tuple);

        db.disconnect();

Other update methods in table include an updateAll for a set of tuples, and an updateColumnValue() to specify a subset of columns to be changed. Please see the User API Javadoc for more information.

Delete Rows
This example shows how to delete rows (tuples) in a table. In this example we create a Key instance and delete all rows matching the key. 

        Database db = new DatabaseImpl("productsdb", "myname", "mypasswd");
        Table table = db.getTable("products");

        String keyColumns[] = { "id" };
        String keyValues[] = { "1" };
        Key key = table.keyInstance(keyColumns, keyValues);
        
        table.remove(key);

        db.disconnect();

Other delete methods in table include a removeAll for a set of tuples. Please see the User API Javadoc for more information.

Drop Table
This example drops the products table from the schema. The boolean flag in the dropTable method indicates whether drop should be cacaded to any dependent tables and views. If this is set to false, the drop will fail if there are any dependent views and foreign key references. 

        Database db = new DatabaseImpl("productsdb", "myname", "mypasswd");
        Schems schema = db.getDefaultSchema();

        schema.dropTable("products", false);

        db.disconnect();

Using SQL Statements Within the User API
One can mix SQL within the User API and use the best tool for each specific task. We use some arbitrary copmplex SQ<L statements here to emphasize that sometimes SQL can be used easily to achieve what may take more work with the API.

The first example shows how to do SQL queries and get results in teh API. The SQL query result tables are temporary tables that can be used like other tables in the API, and are cleaned up once it is no longer referenced by your code. 

        Database db = new DatabaseImpl("productsdb", "myname", "mypasswd");

        String str = "SELECT * FROM WORKS WORKSY "
         + "WHERE EMPNUM = 'E2' AND NOT EXISTS "
         + "(SELECT * FROM WORKS WORKSZ "
         + "WHERE WORKSZ.EMPNUM = WORKSX.EMPNUM "
         + "AND WORKSZ.PNUM = WORKSY.PNUM)";
        DbStatement stmt = db.createStatement(s);

        Table table = stmt.execute();
        table.printTable();
        db.disconnect();

In this next example, we call create table to show how you can fully use the SQL syntax as needed with the User API, so you pick the best way to do any given task. 

        Database db = new DatabaseImpl("productsdb", "myname", "mypasswd");
        String str = "CREATE TABLE RET_CATALOG (VENDOR_ID INT, "
            +"PRODUCT_ID INT, WHOLESALE NUMERIC (10,2), "
            +"RETAIL NUMERIC (10,2), "
            +"MARKUP NUMERIC (10,2), "
            +"EXPORT_CODE CHAR(2), EXPORT_LICNSE_DATE CHAR(20), "
            +"CHECK (EXPORT_LICNSE_DATE IS NULL OR ( "
            +"EXPORT_CODE = 'F1' OR  EXPORT_CODE = 'F2' OR "
            +"EXPORT_CODE = 'F3')), "
            +"CHECK (EXPORT_CODE <> 'F2' OR WHOLESALE > 10000.00), "
            +"CHECK (RETAIL >= WHOLESALE),  CHECK (RETAIL = WHOLESALE + MARKUP));";

        DbStatement stmt = db.createStatement(str);
        Table t = stmt.execute();
        db.disconnect();

The returned table for any update statement is a table with one row and column showing the number of rows changed by the statement.

Create Index
This example shows how to create an index named products_PRIMARY_KEY for table products. The index type is BTree and the last boolean value indicates it is a unique index. The null value for the isAscending array is used when the default index order (ascending order) is ok. 

        Database db = new DatabaseImpl("productsdb", "myname", "mypasswd");
        Table table = db.getTable("products");
        Schema schema = table.schema();

        String idxcolumns[] = { "id" };
        Index index = schema.addIndex("products", idxcolumns, "products_PRIMARY_KEY", "BTree", null, true);

        db.disconnect();

Iterate An Index
The following example shows how to iterate over the index we created above, and print values from each tuple. 
        Database db = new DatabaseImpl("productsdb", "myname", "mypasswd");
        Table table = db.getTable("products");
        Index index = table.getIndex("products_PRIMARY_KEY");

        for (Iterator it = index.iterator();it.hasNext();) {
            Tuple tuple = (Tuple) it.next();
            System.out.println(tuple.value("product") + ": $" + tuple.value("price"));
        }
        db.disconnect();

Iterate Index Range
Among the key benefits of the User API is getting direct access to table indexes and having the ability to extract the required data with minimal overhead. This example shows how to use an index to get to a range of tuples. 

        Database db = new DatabaseImpl("productsdb", "myname", "mypasswd");

        Table table = db.getTable("products");
        Index index = table.getIndex("products_PRIMARY_KEY");


        String keyColumns[] = { "id" };
        String skeyValues[] = { "5" };
        Key startKey = table.keyInstance(keyColumns, skeyValues);

        String ekeyValues[] = { "15" };
        Key endKey = table.keyInstance(keyColumns, ekeyValues);

        for (Iterator it = index.iterator(startKey, endKey);it.hasNext();) {
            Tuple tuple = (Tuple) it.next();
            System.out.println(tuple.value("product") + ": $" + tuple.value("price"));
        }
        db.disconnect();

With direct access to the index, applications can bypass the query processer and more easily control performance of specific operations to improve user response for tasks where response is critical or the task is frequently used.