MogileFS Oracle support

[Brad Fitzpatrick]

On Fri, 29 Sep 2006, Jay Buffington wrote:

> For places where I need an "if mysql do this, else if oracle do that"
> construct I'll use DBI::get_info:
>
> my $db_type = $dbh->get_info( $DBI::Const::GetInfoType{SQL_DBMS_NAME} );
> if ($db_type eq "Oracle" ) {
>    $MogileFS::Config::DATABASE_TYPE = "oracle";
> } elsif ( $db_type eq "MySQL" ) {
>    $MogileFS::Config::DATABASE_TYPE = "mysql";
> } else {
>     die "Sorry, database type '$db_type' isn't supported by mogilefs";
> }

Go ahead and do that for now, but don't be surprised if I refactor it into
a clean sort of hierarchy like:

  MogileFS::DatabaseBackend
    +
    +-- MogileFS::DatabaseBackend::MySQL
    +
    +-- MogileFS::DatabaseBackend::Oracle

etc.

> Mgd::validate_dbh uses CONNECTION_ID.  I believe using
> DBI::connect_cached() in Mgd::get_dbh() will allow us to get rid of
> validate_dbh() altogether.

There are weird issues with old MySQL versions.  For now, keep
validate_dbh and just make it a no-op in Oracle.

> MySQL locks (GET_LOCK and RELEASE_LOCK) are used in a couple of
> places.  Replace with a CPAN module like Proc::Lock?  I'm not sure if
> Proc::Lock does what we need.

I very much doubt it:  GET_LOCK isn't per-connection, it's global across
all callers.  If you have multiple mogilefsds, Proc::Lock wouldn't protect
you at all.

> Other MySQL-ism, and how to fix them:
> - AUTO_INCREMENT column types.
> I'm not sure what to do about this.  Suggestions?

Sequences?

> - LIMIT
> If oracle use ROW_NUM instead
>
> - UNIX_TIMESTAMP
> Use server time instead of database time or query like this
>     SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400)
> AS dt FROM dual;
> I could even write a stored procedure called UNIX_TIMESTAMP() that does this.
>
> - INSERT IGNORE
> I guess the idea behind this is to ignore duplicate row errors.  I'm
> not sure what to do about this in oracle.
>
> - REPLACE INTO
> If oracle use MERGE instead

Keep us updated.

> Jay
>
> p.s. would SQLite support be useful?

As a demo to how to write a new provider, or a warm-up, perhaps.  Normally
I'd say "for testing", but I got testing working with MySQL including
setup and tear-down.  So not that useful.  It's also pretty damn slow.

- Brad