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

[Paul S. <ps...@ne...>]

On Thu, 2007-03-22 at 17:52 +0100, Maximilian Wilhelm wrote:
> > Critical to get in, IMO, are the enhancements to allow the code to
> work
> > with MySQL 5 (the quoting of `release` so it's not considered a
> > keyword).
>=20
> Would it be problem to quote it every time?

It's not a problem to quote it, EXCEPT THAT we then cannot combine the
backends because (as Jan-Benedict points out below) MySQL doesn't by
default accept standard SQL quoting chars.  This can be addressed, but
it might be simpler to just rename the column instead.

> I guess these scripts would need the 'baseurl' as parameter to get the
> correct data from lxr.conf?

Ooh, good point.

> The use case would be to have several project databases indexed by lxr
> side by side in the same database but with different table
> prefixes/names?

> I=B4m not that sure if this is really needed, maybe others have some
> experiences about that?

It's helpful for testing, at the very least.  Also I'm sure there are
sites that have multiple completely different codebases they'd like to
use with a single instance of LXR.

The package does give people the option of using an alternate database
names and/or table prefixes; it just seems odd that this configurability
is there in the code but not available at the table creation time.

Jan-Beneict Glaw <jb...@lu...> wrote:
> 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.

I'm not so sure there was a reason, other than maybe an unfamiliarity
with Perl class inheritance and/or DBI itself.  Looking at the Index
backends I can't see any _material_ difference between Postgres and
MySQL (I haven't looked at Oracle).  They are pretty different in some
ways but that difference feels to me more like someone just updated one
backend but didn't bother with the other one (e.g., the MySQL backend
keeps the prepared statements inside the object, while Postgres uses
class variables).  If I had to guess based on the code I'd guess that
one of the backends was written, then when someone wanted a different
database the split was created in a more or less ad hoc way.

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

Yes, unquestionably we should keep the prepared statement implementation
as we have it today.

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

The DBI interface takes care of all this for us, though: it supports the
prepare() method and does the best it can with whatever the underlying
database supports.  At the very least it saves the template in the
statement object and binds the values when you run execute().

In other words, almost all those prepare statements are identical across
all the backends, because they're all pure SQL using the same DBI
interface; they can all be pushed up into a superclass, along with most
of the methods.

The only real difference I see is in handling the auto-increment fields:
I don't know how Oracle does this but MySQL and Postgress handle this
very differently.  So, that bit would need to be kept local.

> The cruelity of MySQL is that it seems to require '`' instead of '"',
> what all other RDBMS servers seem to accept...

True :-(.  This could be the death of the commonality of the prepare()
statements (although we could still share at least some of the methods
since they just invoke execute()).

Actually there is an "ANSI_QUOTES" mode that we could set, that lets you
(among other things) use standard "-quoting in MySQL.  That might be a
valid thing to do.

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

True.  Note I wasn't suggesting that we would try to unify the initdb-*
scripts into one script: there's too much variation in the schema
creation operations to make that worthwhile IMO.

I just wanted a way to create databases and tables based on the
information in lxr.conf, without having to edit the appropriate initdb-*
script by hand.

--=20
---------------------------------------------------------------------------=
--
 Paul D. Smith <ps...@ne...>                       http://netezza.co=
m
 "Please remain calm--I may be mad, but I am a professional."--Mad Scientis=
t
---------------------------------------------------------------------------=
--
      These are my opinions--Netezza takes no responsibility for them.