One key feature is the ability to very cheaply snapshot the database (both metadata and data) and restore from that point. This is further described at net.sourceforge.mayfly.Database#dataStore().
The file mayfly-version-src-ide.zip within the main download is designed to make it easy to browse Mayfly source (for example, using the "attach sources" feature found in Java development tools). Either the sources or the Mayfly documentation can help you distinguish between methods you would ordinarily call and methods which are public but would generally only be called from within Mayfly.
The source download mayfly-version-src.zip contains everything you need to rebuild Mayfly and run the Mayfly tests. Because it contains jar files for non-Mayfly databases, it is rather large.
Alternately, Mayfly supplies a JDBC Driver, described in net.sourceforge.mayfly.JdbcDriver.
The most commonly used parts of JDBC are implemented, including PreparedStatement and parameters. As usual, if you get an net.sourceforge.mayfly.UnimplementedException, we'd love to hear what features you'd like to see us add (via the mayfly mailing list).
WHERE a = NULL (instead of WHERE a IS NULL), Mayfly complains. For another example, if you specify a CREATE TABLE with duplicate column names, Mayfly tells you which column was duplicated. And so on.If you find a problem with your SQL, and Mayfly gives an unclear or less than helpful message, we consider that to be a bug. Please let us know, on the mayfly mailing list, what you were doing and what would have helped you find your problem faster.
This section describes the syntax supported in Mayfly. In this description, Square brackets indicate optional elements, vertical bars indicate choices, curly braces simply group elements, and , ... indicates that the previous element can be repeated. Parentheses stand for themselves.
You may want to read these descriptions with reference to a more comprehensive SQL guide, such as those found at sqlzoo.net. Here we emphasis conciseness and identifying our SQL subset, instead of a full description of the semantics and syntax.
CREATE TABLE name (
{
{column-name data-type
[ DEFAULT default-value ]
[ AUTO_INCREMENT ]
{[ NOT NULL | UNIQUE | PRIMARY KEY ]}...
} |
UNIQUE(column, ...) |
PRIMARY KEY(column, ...)
FOREIGN KEY(column) REFERENCES table(column)
[ ON DELETE action [ ON UPDATE action ] |
ON UPDATE action [ ON DELETE action ]
]
}, ...
)
At the moment the data type is mostly ignored (what matters is what data you actually put into the database). Future versions of Mayfly are expected to make more use of the data type.
Supported data types for numbers are TINYINT, SMALLINT, INTEGER and BIGINT (8, 16, 32, and 64 bit integers, respectively). DECIMAL(x,y) is supported. For strings, there is VARCHAR(size) (with TEXT as a non-standard synonym). There is partial support for DATE.
An exception to the rule that Mayfly ignores data types are auto-increment columns. Either IDENTITY or SERIAL as datatypes indicate an integer column which causes the default value to start at one and increment after every row inserted.
Foreign key actions for ON DELETE are NO ACTION, CASCADE, SET NULL, and SET DEFAULT. Foreign key actions for ON UPDATE are NO ACTION only.
DROP TABLE name DROP TABLE name IF EXISTS DROP TABLE IF EXISTS name
Remove the table name and all its contents. Without IF EXISTS, there must be a table by that name. With the IF EXISTS (in either position), if there is no table by that name, the command does nothing, without an error.
INSERT INTO table [ ( column-name, ... ) ] VALUES ( expression, ... )
If expression is DEFAULT, the default value that was specified when creating the table is used.
SELECT { * | alias.* | [alias . ] column}, ...
FROM table-reference, ...
[ WHERE condition ]
[ ORDER BY { [alias .] column }, ... ]
[ LIMIT count [ OFFSET start ] ]
A table-reference is:
tablename [ alias ] |
table-reference
{ INNER | LEFT OUTER } JOIN table-reference]
ON condition
A condition is:
condition OR condition |
condition AND condition |
NOT condition |
expression = expression |
expression { != | <> } expression |
expression < expression |
expression > expression |
expression IS [ NOT ] NULL |
expression IN ( expression, ... )
An expression is:
0-9... |
'character...' |
[ alias . ] column |
MAX ( [ ALL | DISTINCT ] expression )
MIN ( [ ALL | DISTINCT ] expression )
SUM ( [ ALL | DISTINCT ] expression )
AVG ( [ ALL | DISTINCT ] expression )
COUNT ( { [ ALL | DISTINCT ] expression } | * )
NULL
There is also some limited support for GROUP BY and HAVING.
If expression is DEFAULT, the default value that was specified when creating the table is used.
The syntax of the schema commands is:
CREATE SCHEMA name [AUTHORIZATION DBA] [ { create-table-command } ... ]
SET SCHEMA name
Furthermore, even those aspects of transactions which are visible from within a single thread (for example, rollback), and not yet implemented.
A more introductory book is The Practical SQL Handbook: Using Structured Query Language, by Judith S. Bowman, Sandra L. Emerson, and Marcy Darnovsky (third edition is from 1996).
One good SQL reference/tutorial web site is SQLzoo.