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

Re: [Bacula-devel] bad sql??


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.


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



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