Re: [Bacula-devel] bad sql??

[Dan Langille <dan@xxxxxxxxxxxx>]

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/360-Ba
>>>>>>> cu la
>>>>>>> ,-Sqlite,-Postgres...-when-good-tools-go-horribly-horribly-wrong.html
>>>>>> 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"

-- 
Dan Langille

BSDCan - The Technical BSD Conference : http://www.bsdcan.org/
PGCon  - The PostgreSQL Conference:     http://www.pgcon.org/

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