MogileFS Oracle support

[Hill, Greg]

Accidentally sent this to Jay only; meant to send it to the list:

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

You'll likely have to create a separate Oracle schema with sequences and
triggers.

> - LIMIT
> If oracle use ROW_NUM instead

One caveat.  You may already know this, but a lot of people don't, so I
thought I'd offer.  If you're using an offset, you need to do something
like this:

SELECT * FROM (SELECT ROW_NUM AS myrow, **some sql**) WHERE myrow >=
offset AND myrow < offset + limit;

If you say WHERE ROW_NUM >= offset, you'll get no results because the
ROW_NUM is basically just a running counter on the results.  So, every
row will be zero because each previous row failed that condition.
Contrary to what seems logical, it doesn't add much overhead.  Oracle's
planner is pretty smart that way.
 
> - 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.

Man, I don't envy the person having to write database-agnostic date/time
code.

> - 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.

You can do it with a trigger ON BEFORE INSERT, if the record exists do
nothing.  There might be a better way.

> - REPLACE INTO
> If oracle use MERGE instead
> 
> Jay
> 
> p.s. would SQLite support be useful?

Never used it myself, but I know people who swear by it.  Also, anywhere
you say IF oracle, you should say IF (oracle or postgres).  Most, if not
all, Oracle SQL will work in Postgres.  If someone uses postgres and
finds bugs, they can fix those specific queries as needed.

Greg