[development] Do not let postgresql hold back great patches

[Ivan Sergio Borgonovo]

On Sun, 11 Nov 2007 20:14:30 -0600
George Kappel <gkappel at herrspacific.com> wrote:

> I would think this is a slippery slope, making sure a patch work
> against at least 2 db backends in a reasonable way is an important
> indication of quality

Agree.

I think supporting one DB is not just a matter of losing support but
it is a matter of loosing freedom. Once drupal lose the
infrastructure for DB independence, it will be hard to put it back.

Support for 2 DB is actually too few to keep the code sane. The
second will just be a special case of the first rather than an
abstraction.

I am *ing scared of mono-cultures.

No one said DB abstraction had to be easy, actually it isn't and
there are plenty of DB abstraction layers out there to prove it.

I was actually looking at http://www.sqlalchemy.org/ (python) to
learn something.
Any chance we could introduce, get inspired, rely on an existent DB
abstraction layer?

OK, I'll stop to pretend to be a software architect and I'm going to
install D6 on pgsql now to see if I can help in anyway. Installation
completed...

BTW I've some older drupal sites on pgsql. The reasons I chose pgsql
were historical (more mature transaction, stored procedures...).
If I'd have to chose for the future I *may* reconsider to use mysql.
All my pgsql sites are running smoothly even if I had to patch some
modules. I could live with a mysql only D6. I'd just consider it
risky for the future of drupal itself.

Wasn't Microsoft interested in supporting drupal running on IIS/MSSQL?

http://hojtsy.hu/blog/2007-nov-04/adventures-redmond-microsoft-open-source-and-drupal

http://buytaert.net/microsoft-and-drupal


BTW as far as I know there is no way to introduce a unique constraint
in pgsql without eliminating the duplicates first.

For the case exposed here http://drupal.org/node/146466

mysql
ALTER IGNORE TABLE {search_index} ADD UNIQUE KEY sid_word_type (sid,
word, type)");
ALTER IGNORE TABLE {search_dataset} ADD UNIQUE KEY sid_type (sid,
type)

would turn to be

pgsql
delete from {search_index}
  where exists ( select 'x' 
    from {search_index} i
      where
        i.sid = {search_index}.sid
        and i.word = {search_index}.word
        and i.type = {search_index}.type
        and i.oid < {search_index}.oid
);

alter table {search_index}
  add constraint sid_word_type
  unique (sid, word, type);


delete from {search_dataset}
  where exists ( select 'x'
    from {search_dataset} i
      where
        i.sid={search_dataset}.sid
        and i.type = {search_dataset}.type
        and i.oid < {search_dataset}.oid
);

alter table {search_dataset}
  add constraint sid_type
  unique (sid, type);

untested on drupal... tested on a *test* table, going to test further
shortly.

(oid is a bit of pgmagic, oid is the object id, so you're not risking
to kill all the row and leave at least one instance).

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it