Re: Common Last Index Method

On Tue, Aug 31, 2004 at 10:56:58AM -0400, T. Onoma wrote:
> But I wonder how mcuh trouble it would be to actually have a common superset 
> of SQL. Could "INSERT ... RETURNING ..." be done if we had a common "hook" 
> method for filtering/adapting the SQL?
> 
>    module DBD
>      class Mysql
>        def sql_adapt(super_sql)
>          # ... create SQL suitabe to backend
>        end
>      end
>      class Postgres
>        def sql_adapt(super_sql)
>          # ... create SQL suitabe to backend
>        end
>      end
>    end

Would that mean DBI actually parsing the SQL, working out what it meant, and
then re-assembling one or more SQL statements to do the job?

If so, I don't like that idea. I would prefer a procedural interface, in
particular one which doesn't involve de-quoting and re-quoting data.

A simple example of where it could be useful:

  def insert(table, cols, rows)
    qm = "?," * cols.size-1 + "?"
    rows.each do |r|
      execute("insert into ? (#{qm}) values (#{qm})", table, *(cols + r))
    end
  end

Mysql allows multiple row inserts in a single statement, so this could be
optimised to generate SQL to insert all the rows in one go (or to handle the
rows in batches of ten, say, to avoid having a stupidly long command line)

I think I can see where you're coming from: emulate the "perfect" SQL
database by using one of many imperfect SQL databases which sit under it.
But SQL is pretty poor as a programmatic API; about the only advantage I can
see for it is that you can hand-craft SQL statements in a text editor.

Now, a clean programmatic database interface - *that* would be cool... but
pretty hard to design, too.

Regards,

Brian.