NEWS

Changes since 0.3:

Now have the standard SQL DROP INDEX syntax:
  DROP INDEX index-name

For MySQL compatibility, accept engine and character set in CREATE TABLE
and ALTER TABLE (the CREATE TABLE form had existed in Mayfly for a while
but had not been documented; the ALTER TABLE form is new).
Also add ALTER SCHEMA schema CHARACTER SET character-set.

For better compatibility with Hypersonic, the data type IDENTITY
now indicates an auto-increment column (that is, newly inserted
values are chosen relative to what is already in the table,
rather than as a sequence which isn't affected by table contents).

Add 
  INSERT INTO table(columns...) select-statement
syntax (used, for example, for copying data from an old table 
to a rearranged table).

Add ALTER TABLE table DROP CONSTRAINT constraint-name
  (in addition to ALTER TABLE table DROP FOREIGN KEY constraint-name
  which has been there longer).

Now support CONCAT(expression, ...) syntax for MySQL compatibility in
addition to the SQL standard expression || expression.

Add DROP INDEX. For now the syntax is a MySQL-specific one:
    DROP INDEX index-name ON table
although a future version of Mayfly might not require the "ON table".

When adding a foreign key without giving it a name, Mayfly now assigns
a name using the same scheme as MySQL: referringTable_ibfk_sequence,
where the first foreign key in this table is sequence 1, the second is 2, 
and so on.  This can be kind of ugly in dumps (although not as problematic
as if the name were set in an unpredictable way, such as based on times
or database internal state), but seems to be the best way to make
ALTER TABLE DROP FOREIGN KEY usable where a constraint name was not
initially assigned.

Add MySQL syntax for CHANGE COLUMN (which is like MODIFY COLUMN but
also allows renaming the column):
  ALTER TABLE table CHANGE COLUMN old-name column-definition

Add CREATE INDEX (with a syntax which seems compatible with most other
databases I tried):
    CREATE [UNIQUE] INDEX index-name ON table(column[(width)], ...)
As with the MySQL INDEX syntax
    CREATE TABLE(a INTEGER, INDEX(a))
the index is a no-op, but in this case UNIQUE is not a no-op - it
has the same effect as any other unique constraint.  The optional
width is for MySQL compatibility and is also a no-op.

ALTER TABLE can now control the position of the added column,
using the same syntax as MySQL:
  ALTER TABLE ADD COLUMN B INTEGER AFTER A
  ALTER TABLE ADD COLUMN A INTEGER FIRST
  
The dumper can now optionally omit the next value for auto-increment
or identity columns.

Accept the SQL 2003 syntax for an identity column:
    a INTEGER GENERATED BY DEFAULT AS IDENTITY[(STARTS WITH value)]
Support two variants of this functionality:
* An identity column is enabled by the SQL 2003 syntax.  It is based on
a sequence (that is, the value to be inserted by default is not affected
by other insert statements into the table).
* An auto-increment column is enabled by the AUTO_INCREMENT syntax.
It is based on the existing values (that is, inserting another value
into the table will change the value to be inserted).
Older versions of Mayfly had only identity columns.

Changes from 0.2 to 0.3:

Accept the MySQL syntax:
    INSERT INTO table SET col = value, col2 = value2, ...
Although this syntax is supported by no/few databases other than
MySQL, and it is merely a convenience, Mayfly does provide it,
simply because the standard syntax
    INSERT INTO table(col, col2) VALUES(value, value2)
can be really hard to read if there are more than a few columns.

New method in Database to set table names to case sensitive.

Accept the MySQL index syntax:
    CREATE TABLE(a INTEGER, INDEX(a))
as a noop.

Mayfly now supports many subselects, for example:
    select name from foo where x = (select max(y) from bar)
    select name from foo where x in (select y from bar)

The new SqlDumper class can dump a database as an SQL script.

Now can insert hex literals into BLOB columns, for example:
INSERT INTO foo(a) VALUES(x'aa65fe')

Some CASE statements now work.

Added LIKE, <=, and >= conditions.

Column aliases (SELECT foo as bar FROM) now work for the simple
cases.

Most methods in Database now throw runtime exceptions instead
of checked exceptions.  If you are of the checked exception
persuasion, or you have some piece of code which expects 
an SQLException, just call MayflyException#asSqlException. 
JDBC methods, of course, continue to throw SQLException

Many forms of ALTER TABLE now work.

Some limited query optimization.  It is now possible to,
for example, implicitly join 3 tables of 1000 rows each, 
in some cases.  The query optimizer is still dead simple -
it proceeds from left to right and can decompose a WHERE
clause made up of AND.  For example, 
"SELECT * from foo, bar, baz where foo.x = bar.x"
will get optimized to
inner join foo and bar on foo.x = bar.x
inner join that with baz
whereas
"SELECT * from foo, bar, baz where bar.x = baz.x"
will perform all the joins, and only
then apply the WHERE (requiring temporary storage
of the number of rows in foo times the number of rows
in bar times the number of rows in baz).

Changes from 0.1 to 0.2:

Adds schemas (and SET SCHEMA), GROUP BY, auto-increment columns, 
and primary and foreign keys.