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

Re: [Bacula-devel] bad sql??


On Thursday 18 September 2008 18:10:35 Kern Sibbald wrote:
> On Thursday 18 September 2008 23:42:52 Robert Treat wrote:
> > On Thursday 18 September 2008 15:58:18 Dan Langille wrote:
> > > Robert Treat wrote:
> > > >> Kern Sibbald wrote:
> > > >>> On Thursday 18 September 2008 17:23:06 Dan Langille wrote:
> > > >>>> Kern Sibbald wrote:
> > > >>>>> On Thursday 18 September 2008 15:40:04 Dan Langille wrote:
> > > >>>>>> resending, with CC to list.
> > > >>>>>>
> > > >>>>>> John Huttley wrote:
> > > >>>>>>> https://people.planetpostgresql.org/xzilla/index.php?/archives/
> > > >>>>>>>36 0- Ba cu la
> > > >>>>>>> ,-Sqlite,-Postgres...-when-good-tools-go-horribly-horribly-wron
> > > >>>>>>>g. ht ml
> > > >>>>>>
> > > >>>>>> John: whenever people post a URL without commenting up on it, I
> > > >>>>>> always ask them for their comments upon it.
> > > >>>>>>
> > > >>>>>> John: What do you think?
> > > >>>>>>
> > > >>>>>> and FYI, no, it is not bad SQL.
> > > >>>>>
> > > >>>>> I agree, and SQLite is doing the correct thing not to quote the
> > > >>>>> table names.
> > > >>>>>
> > > >>>>> Dan: do you think we could ease database conversion problems such
> > > >>>>> as this one by changing the make_postgresql_tables script to
> > > >>>>> define the table names using Bacula standard capitalization?
> > > >>>>
> > > >>>> I think the best thing to do is to stop using capitalization
> > > >>>> altogether. Go lower case.
> > > >>>
> > > >>> That is not a solution will work for Bacula. First it would require
> > > >>> *massive* changes in Bacula source code. Second, a good number of
> > > >>> studies show that using either _ in variable names or using
> > > >>> capitalization of the parts significantly improves the readability.
> > > >>
> > > >> I hope there is a solution that will work everywhere.
> > > >>
> > > >> The SQL standard says that "SELECT * FROM FOO" should be the same as
> > > >> "SELECT * FROM foo"
> > > >>
> > > >> In short,  FOO == Foo == foo <> "Foo"
> > > >>
> > > >>  From what I understand, we're doing a lot of 'SELECT * FROM "Foo"'.
> > > >>
> > > >> If we stop putting quotes around the table and field names, that
> > > >> might solve a lot of the issues.
> > > >
> > > > Actually I don't think your assesment here is correct. I have a
> > > > feeling that your actually doing a lot of  SELECT * FROM Foo, which
> > > > in postgres becomes SELECT * FROM foo and in Sqlite becomes SELECT *
> > > > FROM Foo (which might look nice but is completly broken from a sql
> > > > standard point of view).
> > > >
> > > > So... someone should look at the code and confirm that, because if
> > > > you are doing SELECT * FROM Foo, then there is no way to fix this
> > > > without modifiying the queries inside of bacula, which does up the
> > > > bar considerably.
> > >
> > > Is this what you mean?
> > >
> > > [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.
>

This is exactly what I am getting at. Sqlite does not case fold unquoted 
identifiers (table names, column names, etc...), while every other database 
does fold them (since that is sql standard behavior).  So the bacula code 
looks the same going in, but produces different results when executed by each 
database. 

> > If you change sqlites schema to lower case, you will have to lower case
> > all of the sql statements to work with that. If you quote postgres'
> > schema, you'll need to quote all of the identifiers in all the sql. 
> > Neither of these jobs is hard, just tedious.

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