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

Re: [Bacula-devel] bad sql??


Kern Sibbald <kern@xxxxxxxxxxx> 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-wrong.
> > > >>>>>>>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.

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.

-- 
Bill Moran
Collaborative Fusion Inc.

wmoran@xxxxxxxxxxxxxxxxxxxxxxx
Phone: 412-422-3463x4023

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