com.holub.database
Class Database

java.lang.Object
  extended by com.holub.database.Database

public final class Database
extends Object

This class implements a small SQL-subset database. that provides a front end to the Table classes. Find the grammar for the supported language below. The remainder of the documentation of this class assumes that you know a little SQL (see the HolubSQL web page for a few SQL references).

My intent is to do simple things, only. None of the niceties of SQL (like aliases, outer and inner joins, permissions, views, etc.) are supported. The file src/com/holub/database/Database.test.sql in the original distribution .jar file demonstrates the SQL subset that's supported.

A database is effectively a directory, and a table is effectively a file in the directory. The argument to USE DATABASE specifies the full path to that directory. A table name is a file name with the ".csv" extension added. Note that a simple name (as in "USE DATABASE foo" will create a subdirectory called "foo" in the current directory. Use a full path name to get something else: "USE DATABASE c:/tmp/foo" See CSVExporter for a description of the file format.

Because database names are path names, identifier names in general can contain characters that would normally go in a path (/ \ : ~ _) but they cannot contain a dot or dash (so your database name can't have a dot or dash in it either). Identifiers can't contain spaces, and they cannot start with digits.

SELECT statements support FROM and WHERE clauses, but nothing else. (DISTINCT, ORDEREDBY, etc., aren't supported; neither are subqueries.) You can join an arbitrary number of tables in a SELECT, but outer and inner joins (ans subqueries) aren't supported. A few operators (BETWEEN, IN) aren't supported---check the grammar, below. Any Java/Perl regular expression can be used as an argument to LIKE, and for SQL compatibility, a % wild card is automatically mapped to ".*". Selecting "into" another table works, but bear in mind that the actual data is shared between tables. Since everything in the table is a String, this strategy works fine unless you use the Table object that's returned from execute(java.lang.String) to modify the table directly. Don't do that.

Though the following types are recognized by the parser (so you can use them in the SQL), but they are ignored. Everything's stored in the underlying database as a String. Strings that represent numbers (can be parsed successfully by NumberFormat) can be used in arithmetic expressions, however.

integer(maxDigits)
int(maxDigits)
smallint(maxDigits)
bigint(maxDigits)
tinyint(size)
integers
decimal(l,r)
real(l,r)
double(l,r)
numeric(l,r)
floating point, l and r specify the maximum number of digits to the left and right of the decimal.
char(length)Fixed length string.
varchar(maximum_length)Variable length string.
date(format)Date in the Gregorian calendar with optional format.
You may specify a "PRIMARY KEY(identifier)" in the list of columns, but it's ignored, too.

Numbers in the input must begin with a digit (.10 doesn't work. 0.10 does), and decimal fractions less than 1.0E-20 are assumed to be 0. (That is 1.000000000000000000001 is rounded down to 1.0, and will be put into the table as the integer 1.

You can't store a Boolean value as such, but if you decide on some string like "true" and "false" as meaningful, and use it consistently, then comparisons and assignments of boolean values will work fine. Null is supported.

Simple transactions (in the sense of a group of SQL statements that execute atomically, which can be rolled back) are supported. Initially, no transaction is active, and all SQL requests are effectively committed immediately on execution. This auto-commit mode is superceded once you issue a BEGIN, but is reinstated as soon as the matching COMMIT or ROLLBACK is encountered. All requests that occur between the BEGIN and COMMIT are treated as a single unit. If you close (or DUMP) the database without a formal COMMIT or ROLLBACK, then any open transactions are effectively committed. The begin(), commit(), and rollback() methods have the same effect as issuing the equivalent SQL requests.

Transactions affect only modifications of tables. Tables that are created or dumped during a transaction are not destroyed (or put back in their original state on the disk) if that transaction is rolled back.

An exception-toss that occurs when processing a SQL expression submitted to execute(java.lang.String) causes an automatic rollback before the exception is tossed out to your code. This automatic-rollback behavior is not implemented by the methods that mimic SQL statements (useDatabase(...), createDatabase(...), createTable(...), dropTable(...), and dump(...)). If you use these methods, you'll have to catch any exceptions manually and call rollback() or commit() explicitly.

The modified database is not stored to disk until a DUMP is issued. (In the JDBC wrapper, an automatic DUMP occurs when you close the Connection).

This class wraps various Table derivatives, but this class also relies on the fact that the table is made up entirely of String objects. You can use this class to access Table objects that were created directly by yourself, but problems can arise if those manually created tables have anything other than Strings in them. In particular, Object.toString() method is used to get the value of a cell, and if the value is modified through an UPDATE, the new value is stored as a String, without regard to the original field type.)

Here's the grammar I've implemented ("expr"=expression, "id"=identifier, "opt"=optional, "e"=epsilon. "[...]" is an optional subproduction.

statement       ::= 
                    INSERT  INTO IDENTIFIER [LP idList RP]
                                      VALUES LP exprList RP
                |   CREATE  DATABASE IDENTIFIER
                |   CREATE  TABLE    IDENTIFIER LP declarations RP
                |   DROP    TABLE    IDENTIFIER
                |   BEGIN    [WORK|TRAN[SACTION]]
                |   COMMIT   [WORK|TRAN[SACTION]]
                |   ROLLBACK [WORK|TRAN[SACTION]]
                |   DUMP
                |   USE     DATABASE IDENTIFIER
                |   UPDATE  IDENTIFIER SET IDENTIFIER
                                            EQUAL expr WHERE expr
                |   DELETE  FROM IDENTIFIER WHERE expr
                |   SELECT  [INTO identifier] idList
                                        FROM idList [WHERE expr]

idList          ::= IDENTIFIER idList' | STAR
idList'         ::= COMMA IDENTIFIER idList'
                |   e

declarations    ::= IDENTIFIER [type] [NOT [NULL]] declaration'
declarations'   ::= COMMA IDENTIFIER [type] declarations'
                |   COMMA PRIMARY KEY LP IDENTIFIER RP
                |   e

type            ::=  INTEGER [ LP expr RP               ]
                |    CHAR    [ LP expr RP               ]
                |    NUMERIC [ LP expr COMMA expr RP    ]
                |    DATE           // format spec is part of token

exprList        ::=       expr exprList'
exprList'       ::= COMMA expr exprList'
                |   e

expr            ::=     andExpr expr'
expr'           ::= OR  andExpr expr'
                |   e

andExpr         ::=     relationalExpr andExpr'
andExpr'        ::= AND relationalExpr andExpr'
                |   e

relationalExpr ::=          additiveExpr relationalExpr'
relationalExpr'::=    RELOP additiveExpr relationalExpr'
                    | EQUAL additiveExpr relationalExpr'
                    | LIKE  additiveExpr relationalExpr'
                    | e

additiveExpr        ::=          multiplicativeExpr additiveExpr'
additiveExpr'       ::= ADDITIVE multiplicativeExpr additiveExpr'
                    |   e

multiplicativeExpr  ::=     term multiplicativeExpr'
multiplicativeExpr' ::= STAR  term multiplicativeExpr'
                    |   SLASH term multiplicativeExpr'
                    |   e

term                ::= NOT factor
                    |   LP expr RP
                    |   factor

factor              ::= compoundId | STRING | NUMBER | NULL

compoundId          ::= IDENTIFIER compoundId'
compoundId'         ::= DOT IDENTIFIER
                    |   e

Most runtime errors (including inappropriate use of nulls) cause
a exception toss.
Most of the methods of this class throw a ParseFailure (a checked Exception) if something goes wrong.

Modifications Since Publication of Holub on Patterns:

9/24/02 Added a few methods to the Cursor interface (and local implemenation) to make it possible to get column-related metadata in the ResultSetMetaData class.

©2004 Allen I. Holub. All rights reserved.

This code may be used freely by yourself with the following restrictions:

  1. Your splash screen, about box, or equivalent, must include Allen Holub's name, copyright, and URL. For example:

    This program contains Allen Holub's SQL package.
    (c) 2005 Allen I. Holub. All Rights Reserved.
    http://www.holub.com


    If your program does not run interactively, then the foregoing notice must appear in your documentation.
  2. You may not redistribute (or mirror) the source code.
  3. You must report any bugs that you find to me. Use the form at http://www.holub.com/company/contact.html or send email.
  4. The software is supplied as is. Neither Allen Holub nor Holub Associates are responsible for any bugs (or any problems caused by bugs, including lost productivity or data) in any of this code.

Nested Class Summary
static class Database.Test
           
 
Constructor Summary
Database()
          Create a database object attached to the current directory.
Database(File path)
          Use the indicated directory for the database
Database(File path, Table[] database)
          Use this constructor to wrap one or more Table objects so that you can access them using SQL.
Database(String path)
          Use the indicated directory for the database
Database(URI directory)
          Use the indicated directory for the database
 
Method Summary
 int affectedRows()
          Return the number of rows that were affected by the most recent execute(java.lang.String) call.
 void begin()
          Begin a transaction
 void commit()
          Commit transactions at the current level.
 void createDatabase(String name)
          Create a database by opening the indicated directory.
 void createTable(String name, List columns)
          Create a new table.
 void dropTable(String name)
          Destroy both internal and external (on the disk) versions of the specified table.
 void dump()
          Flush to the persistent store (e.g. disk) all tables that are "dirty" (which have been modified since the database was last committed).
 Table execute(String expression)
          Execute a SQL statement.
 void rollback()
          Roll back transactions at the current level
 void useDatabase(File path)
          Use an existing "database."
 
Methods inherited from class java.lang.Object
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Constructor Detail

Database

public Database()
Create a database object attached to the current directory. You can specify a different directory after the object is created by calling useDatabase(java.io.File).


Database

public Database(URI directory)
         throws IOException
Use the indicated directory for the database

Throws:
IOException

Database

public Database(File path)
         throws IOException
Use the indicated directory for the database

Throws:
IOException

Database

public Database(String path)
         throws IOException
Use the indicated directory for the database

Throws:
IOException

Database

public Database(File path,
                Table[] database)
         throws IOException
Use this constructor to wrap one or more Table objects so that you can access them using SQL. You may add tables to this database using SQL "CREATE TABLE" statements, and you may safely extract a snapshot of a table that you create in this way using:
        Table t = execute( "SELECT * from " + tableName );
  

Parameters:
database - an array of tables to use as the database.
path - The default directory to search for tables, and the directory to which tables are dumped. Tables specified in the database argument are used in place of any table on the disk that has the same name.
Throws:
IOException
Method Detail

useDatabase

public void useDatabase(File path)
                 throws IOException
Use an existing "database." In the current implementation, a "database" is a directory and tables are files within the directory. An active database (opened by a constructor, a USE DATABASE directive, or a prior call to the current method) is closed and committed before the new database is opened.

Parameters:
path - A File object that specifies directory that represents the database.
Throws:
IOException - if the directory that represents the database can't be found.

createDatabase

public void createDatabase(String name)
                    throws IOException
Create a database by opening the indicated directory. All tables must be files in that directory. If you don't call this method (or issue a SQL CREATE DATABASE directive), then the current directory is used.

Throws:
IOException - if the named directory can't be opened.

createTable

public void createTable(String name,
                        List columns)
Create a new table. If a table by this name exists, it's overwritten.


dropTable

public void dropTable(String name)
Destroy both internal and external (on the disk) versions of the specified table.


dump

public void dump()
          throws IOException
Flush to the persistent store (e.g. disk) all tables that are "dirty" (which have been modified since the database was last committed). These tables will not be flushed again unless they are modified after the current dump() call. Nothing happens if no tables are dirty.

The present implemenation flushes to a .csv file whose name is the table name with a ".csv" extension added.

Throws:
IOException

affectedRows

public int affectedRows()
Return the number of rows that were affected by the most recent execute(java.lang.String) call. Zero is returned for all operations except for INSERT, DELETE, or UPDATE.


begin

public void begin()
Begin a transaction


commit

public void commit()
            throws ParseFailure
Commit transactions at the current level.

Throws:
NoSuchElementException - if no begin() was issued.
ParseFailure

rollback

public void rollback()
              throws ParseFailure
Roll back transactions at the current level

Throws:
NoSuchElementException - if no begin() was issued.
ParseFailure

execute

public Table execute(String expression)
              throws IOException,
                     ParseFailure
Execute a SQL statement. If an exception is tossed and we are in the middle of a transaction (a begin has been issued but no matching commit has been seen), the transaction is rolled back.

Parameters:
expression - a String holding a single SQL statement. The complete statement must be present (you cannot break a long statement into multiple calls), and text following the SQL statement is ignored.
Returns:
a Table holding the result of a SELECT, or null for statements other than SELECT.
Throws:
ParseFailure - if the SQL is corrupt.
IOException - Database files couldn't be accessed or created.
See Also:
affectedRows()