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

Re: [Bacula-devel] bad sql??


On Thu, 2008-09-18 at 21:00 -0400, Robert Treat wrote:
> 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. 

I think that you pointing the finger at the wrong place.   The database
work differently with case conversion,  this isn't Bacula's fault.   You
can also setup mysql to force case.  Moving data to a mysql server that
doesn't have case set to lower will cause issues too.


I would suggest creating a quick perl script to suck the data out of
sqlite and stuff it into Postgres with the correct table cases.  If you
would like to gripe I would suggest moving this to a Postgres mailing
list.

				-Jason


> 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. 
> 
-- 
----------------------------------------------------------------------------
Jason A. Kates (jason@xxxxxxxxx) 
Fax:    208-975-1514
Phone:  212-400-1670 x2
============================================================================



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