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

Re: [Bacula-devel] bad sql??


On Friday 19 September 2008 02:50:48 Robert Treat wrote:
> 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?/archive
> > > > >>>>>>>s/ 36 0- Ba cu la
> > > > >>>>>>> ,-Sqlite,-Postgres...-when-good-tools-go-horribly-horribly-wr
> > > > >>>>>>>on 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.

SQLite and SQLite3 databases created by the Bacula scripts have worked for a 
long time.  To my knowledge there has never been a bug reported against the 
Bacula SQL.


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