Re: [Lxr-dev] Status of this project?

[Jan-Benedict G. <jb...@lu...>]

On Thu, 2007-03-22 12:37:04 -0400, Paul Smith <ps...@ne...> wrote:
> On Thu, 2007-03-22 at 17:06 +0100, Jan-Benedict Glaw wrote:
> > > Critical to get in, IMO, are the enhancements to allow the code to wo=
rk
> > > with MySQL 5 (the quoting of `release` so it's not considered a
> > > keyword).  A number of the bugs and patches on the Savannah site all
> > > deal with this single issue.
> >=20
> > That may need a fix.  If the DB backend gets rewritten to make deeper
> > use of the DBI interface (eg. don't do too much database specific
> > stuff), that'd hopefully be dropped altogether.
> >=20
> > Renaming the field to avoid the issue could help, too.
> >=20
> > And a Perl guru could have a look at the documentation to tell us if
> > DBI has a generic interface for prepared statements.
>=20
> The only thing I understood completely here was the second paragraph :).

About the first paragraph:  I haven't had a look at the separate
backends for PostgreSQL, MySQL or Oracle.  But I guess there actually
was a reason to generate a whole class for implementing different
backends instead of just using plain DBI.

The third paragraph stresses an assumption I have. There could be
"prepared statements" inside the individual backends. When you submit
something as simple as "select foo, bar from baz where xay=3D123", the
DB server parses the request and tries to optimize it.

A "prepared statement" allows us to declare that we'll use a specific
query several times, but with different values. Even while the actual
values differ, the way the tables are searched doesn't. This may save
a lot of time.   As I guess the backends implement prepared statements
*as they are available* in the backends, we'd find a generic way to
come up with prepared statements, or we'll have to keep the different
backends.

> As far as I'm aware there's no way to "make deeper use of the DBI
> interface" that would allow us to do something like ignore column
> names... ?  And I'm not sure what you mean by a generic interface for
> prepared statements.

A column name must be given. The cruelity of MySQL is that it seems to
require '`' instead of '"', what all other RDBMS servers seem to
accept...

> Hm... maybe you mean using placeholders ("?") for column names as well
> as for values, as in:
>=20
> 	select ? from lxr_releases where ? =3D ?
>=20
> If so, that won't work.  DBI supports placeholders ONLY for values, not
> for table/column/database names or other keywords.

You can, in theory, use 'select * from ....', but if you're forced to
manually fiddle with the table at some given time, you cannot be sure
that the order of colums will be stable.  The where clause will of
course also give you grey hair with `release` ...

> > The interesting part here is most probably to correctly deal with
> > dropping the old tables...
>=20
> Which old tables would we want to drop?


The (current) initdb scripts try to first drop table foo, then CREATE
it.  Databases implemented different dialects to avoid errors (which
in turn could break a transaction), like the "drop table foo if exists"
stuff...

MfG, JBG

--=20
      Jan-Benedict Glaw      jb...@lu...              +49-172-7608481
Signature of:                   ...und wenn Du denkst, es geht nicht mehr,
the second  :                          kommt irgendwo ein Lichtlein her.