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? > > > > > > > > [EMAIL PROTECTED]:~/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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
