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