[R-sig-finance] MySQL and other RDBMSs

[Andrew Piskorski]

On Sat, Nov 26, 2005 at 12:11:27AM -0500, Rob Steele wrote:

> MySQL is ideal for the write-once-read-thereafter scenario 
> that research implies.

True (that's what MySQL was originally designed for), except that it's
SQL dialect is pretty limited, and the database has various
misfeatures, e.g.:

  http://sql-info.de/mysql/gotchas.html

The limited SQL support might or might not matter to you, depending on
just what sort of data you're loading in and how complicated your data
model and queries are, but it's certainly something you should keep in
mind when picking a RDBMS.  Maybe that's been improved in MySQL 5.0
(which was just released a month or so ago), I don't know.

If I didn't want to use PostgreSQL or Oracle, I'd be tempted to try a
lightweight embedded database like SQLite or Metakit:

  http://www.sqlite.org/
  http://www.equi4.com/metakit.html

PostgreSQL's SQL tends to be more powerful than MySQL's, but even
PostgreSQL doesn't yet support the (very useful) SQL:2003 windowing
functions (aka, the "OLAP" features T611 and T612 in the spec).
Oracle and DB2 both do.

  http://www.wintercorp.com/rwintercolumns/SQL_99snewolapfunctions.html
  http://www.ncb.ernet.in/education/modules/dbms/SQL99/OLAP-99-154r2.pdf
  http://www.wiscorp.com/sql/SQL2003Features.pdf
  http://troels.arvin.dk/db/rdbms/#select-limit-offset
  http://www.postgresql.org/docs/8.0/interactive/features.html
  http://en.wikipedia.org/wiki/SQL
  http://www.sigmod.org/sigmod/record/issues/0403/E.JimAndrew-standard.pdf
  http://www.oracle.com/oramag/oracle/01-jul/o41industry.html

Of course, with PostgreSQL you can also run R (or a whole bunch of
other languages) inside the RDBMS if you want:

  http://www.joeconway.com/plr/
  http://gborg.postgresql.org/project/plr/projdisplay.php
  http://archives.postgresql.org/pgsql-hackers/2003-02/msg00142.php
  http://www.omegahat.org/RSPostgres/

-- 
Andrew Piskorski <atp at piskorski.com>
http://www.piskorski.com/