[development] Do not let postgresql hold back great patches

[Trevor Mckeown]

Alright let me throw my 2 cents in here .... Again many may not like
what i have to say.

I have experience with both mysql and postgresql (along with SQL
server and oracle).  They are both pretty cool databases (though I
tend to lean towards postgres, its much more mature, the command line
client is a lot better, and its had a PL/SQL language for much longer
then mysql)

Everything I read in the previous messages about keeping DB
independance I think is dead on.  Any application that only supports
one database shuts the door on potential users and more then likely is
using some boofed up, non-standard SQL. Neither Mysql or Postgres is
evil, they are just tools. Its all about how you use those tools.
Same as the argument, guns don't kill people, people kill people.

>From my limited experience with Drupal, its bad queries that are
breaking Postgres support.   ... '1' is not an int ... its a string.
Don't user '%s' for every parameter you pass to db_query ...Realize
there is a difference between storing a string, storing an int,
storing a timestamp, etc ... and please stay away from functions or
constructs that only work for one db platform.

On a side note,  I've been away from the community for a little while
(close to a year), and I really would like to jump back in.  My jedi
skill of programming have gotten a lot better (lots of jquery and lots
of php), and I would like to try my hand at designing architecture or
even opening up og2list to qmail, or maybe even making a second
attempt at porting mantis to drupal.  Thanks for you time

-Trevor (tmck)

On Nov 16, 2007 3:50 AM, Brenda Wallace <brenda at wallace.net.nz> wrote:
> I'm here, and willing to test on postgresql.. I'm not full time but
> rather a weekend hacker. Please send me stuff.
> (Shiny on #drupal)
>
>
> On Mon, 2007-11-12 at 12:34 +0100, Ivan Sergio Borgonovo wrote:
> > 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).
> >
>
>