|
|||||||||
PREV CLASS NEXT CLASS | FRAMES NO FRAMES | ||||||||
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD |
java.lang.Objectcom.holub.database.Database
public final class Database
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. |
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.
|
This code may be used freely by yourself with the following restrictions:
|
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 |
---|
public Database()
useDatabase(java.io.File)
.
public Database(URI directory) throws IOException
IOException
public Database(File path) throws IOException
IOException
public Database(String path) throws IOException
IOException
public Database(File path, Table[] database) throws IOException
Table t = execute( "SELECT * from " + tableName );
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.
IOException
Method Detail |
---|
public void useDatabase(File path) throws IOException
path
- A File
object that specifies directory
that represents the database.
IOException
- if the directory that represents the
database can't be found.public void createDatabase(String name) throws IOException
IOException
- if the named directory can't be opened.public void createTable(String name, List columns)
public void dropTable(String name)
public void dump() throws IOException
The present implemenation flushes to a .csv file whose name is the table name with a ".csv" extension added.
IOException
public int affectedRows()
execute(java.lang.String)
call. Zero is returned for all operations except
for INSERT, DELETE, or UPDATE.
public void begin()
public void commit() throws ParseFailure
NoSuchElementException
- if no begin()
was issued.
ParseFailure
public void rollback() throws ParseFailure
NoSuchElementException
- if no begin()
was issued.
ParseFailure
public Table execute(String expression) throws IOException, ParseFailure
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.
Table
holding the result of a SELECT,
or null for statements other than SELECT.
ParseFailure
- if the SQL is corrupt.
IOException
- Database files couldn't be accessed or created.affectedRows()
|
|||||||||
PREV CLASS NEXT CLASS | FRAMES NO FRAMES | ||||||||
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD |