[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Bacula-devel] bad sql??


On Thursday 18 September 2008 18:39:49 Bill Moran wrote:
> > > > [dan@ducky:~/src/bacula-trunk/bacula/src/dird] $ grep -i "select " *
> > > > autoprune.c:   const char *select = "SELECT DISTINCT
> > > > MediaId,LastWritten FROM Media WHERE "
> > > > backup.c:   Mmsg(buf, "SELECT sum(JobFiles) FROM Job WHERE JobId IN
> > > > (%s)",jobids);
> > > > catreq.c: *  attribute record, but we select out only the stat
> > > > packet, dird_conf.h:   char *db_driver;                   /* Select
> > > > appropriate driver */
> > > > getmsg.c:            dev->autoselect = dev_autoselect;
> > > > migrate.c:   "SELECT DISTINCT Job.Name from Job,Pool"
> > > > migrate.c:   "SELECT DISTINCT Job.JobId,Job.StartTime FROM Job,Pool"
> > > > migrate.c:   "SELECT DISTINCT Client.Name from Client,Pool,Job"
> > >
> > > Yes... the first query will become
> > >
> > > sqlite> SELECT DISTINCT MediaId,LastWritten FROM Media WHERE
> > >
> > > pgsql> SELECT DISTINCT mediaid,lastwritten FROM media WHERE
> >
> > I am not sure what you mean by the above.  In both cases, within the
> > Bacula code, the SQL is:
> >
> > SELECT DISTINCT MediaId,LastWritten FROM Media WHERE
> >
> > Bacula never converts table names or column names to lowercase.  It may
> > be done within the PostgreSQL database, but not within Bacula.
>
> It is done by PostgreSQL.  According to the SQL standard:
> 1) table and column names _are_ case sensitive. (i.e. Foo != foo)
> 2) unquoted column names are folded to uppercase by the server.
> 3) Case is maintained when names are quoted (with ")
>
> Robert's examples show how the server will rewrite the query before
> executing it.
>
> PostgreSQL isn't _quite_ compliant with the standard, as it folds the
> names to lowercase instead of uppercase.
>
> Effectively, if the use of names is consistent, it should never cause a
> problem.  I.e, if you quote the names, always quote them.  If you don't,
> then never do.
>
> I'm not sure why folks are so concerned about changing this.  It works
> fine on PostgreSQL and has for some time.

Yes, it works fine on Postgres, and it works fine on Sqlite. The problem is 
that it works differently between these two systems, so if you try to move 
from one database to another, it introduces many compatability issues. 

Now it is quite possible that no one cares about compatability across 
databases, but it certainly would make life easier for some people if bacula 
were implemented more consitently across db engines. And not just me :-), 
think 3rd party software developers who would have to write code in unquoted 
StudleyCaps to be bacula compliant across databases. That's a crappy thing to 
force on someone. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Bacula-devel mailing list
Bacula-devel@xxxxxxxxxxxxxxxxxxxxx
https://lists.sourceforge.net/lists/listinfo/bacula-devel


This mailing list archive is a service of Copilot Consulting.