IDEAS.txt

* What to compare speed on:
  - create a bunch of tables and a few rows, then close database.
  - joins.  This is where the naive approach to queries might break down.

* Transaction isolation.  Fully isolated (probably by making the
database data structure immutable) should be doable.  How to best
simulate the behavior of whatever database you are going to use in
production?  Is there a way to be especially anal (are there cases we
can detect and complain on, with respect to applications assuming too
much/too little isolation)?

Possible features (beyond those in DESCRIPTION.txt):

* Stored procedures, perhaps compatible with hypersonic stored procedures.
  This is probably the most graceful way to provide the Oracle TO_DATE
  function, for example (either as a full implementation, or just
  as a project-specific implementation which only supports a single
  date format).
  
* Be anal about insisting that close() is called (on result sets, statements,
   etc).  Does this mean that Database tracks all of those things? (which
   means they can't be garbage collected, which I guess is OK provided that
   a .close() lets them, or at least the data which they point to, be
   garbage collected).
    * also, keep a stack trace around for each creation...
      of result sets, statements, connections, etc.,
      for auditing, debugging, etc.
      * if something's not closed, print the stack trace of its creation
      * when do we assert this?  provide an external method?  or,
        register a shutdown hook (implications?)? (shutdown hook is wrong
        unless we can somehow avoid memory growing without bounds).
        On the finalizer of Database is perhaps one good place but
        of course we can't assume it would get called.
   (Also check that we don't do anything after calling close() on a statement,
    result set, etc).
    
   * Have a limit (typically one if this feature is in use) on the number of result
     sets per statement, the number of statements per connection, and anything else.
   
* Enforce "For maximum portability, result set columns within each row should
  be read in left-to-right order, and each column should be read only once."
  in a ResultSet.

* VARCHAR(7) -> enforce the length (exception on attempt to store anything longer)
  (SQLite ignores the length.  Truncation seems like a mistake).
  
* Can we use mayfly with Ruby on Rails?
  (How does this work without mayfly?  There's a test database mentioned in config/database.yml
  and there are fixtures which can load data from YAML or CSV files.  But how does it clear
  things out between tests?  How well is one test isolated from the next?)

  - Running ruby in jruby is one choice.  Then it can just call mayfly.
  - Some kind of remote protocol so they can communicate over a socket?
    Does MySQL, Postgres, etc, have a protocol we can adopt?  


Open source projects to serve as acceptance tests (criteria: must use SQL
and have automated tests which can be run against Mayfly):

* Hibernate

* others?

* Or on a lighter note, we could always target the SQL implementation of the
  "99 bottles of beer" song at 
  http://www.99-bottles-of-beer.net/language-sql-865.html

* An example schema is at
  http://cvs.open-bio.org/cgi-bin/viewcvs/viewcvs.cgi/biosql-schema/sql/?cvsroot=biosql
  but they just seem to have libraries which use it, not tests or 
  even a full fledged application
  
* JForum (not sure the tests are very good, but the author wants to improve
  testability, we're told).


Things to document somewhere:

* Associating types with values rather than columns.  This is what SQLite does.
  For better compatibility we may want to implement what SQLite calls "type
  affinity" (in which the column type gives a preferred type for the value).