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

Re: [Bacula-devel] MySQL, PostgreSQL the names of the fields and case sensitive


Hello,

The "correct" table and column names are defined in MySQL.  I call 
them "correct" for several reasons: 1. They are the original names I gave to 
the tables in both MySQL and SQLite; 2. They have a systematic capitalization 
convention; 3. They are easier to read (by a number of studies); 4. It is the 
way I would like to keep the names :-)  5. All the SQL code of Bacula and Bat 
uses that convention; 6. Up to now, even though the original PostgreSQL table 
and column names do not follow that convention, PostgreSQL has been case 
insensitive so there have been few if any problems.

I would be pleased if you would like to undertake the work necessary to bring 
the PostgreSQL make...tables and possibly the driver itself into conformance 
with the Bacula convention.  I am not sure exactly what it would take though 
and there may be some ramifications on other programs like bweb and baculaweb 
that we should be aware of.

By keeping the MySQL standard and modifying the PostgreSQL names, there should 
be no issues with backward compatibility that do not already exist.

A patch would be welcome, and I would be happy to answer any questions.  If 
you are planning to submit a patch, please see   www.bacula.org->FSFE License 
and send in the two copies requested.  That will make it possible to apply 
your patch. 

Please be aware that the current development code is in the trunk of the svn 
and it is significantly different from the Branch-2.4 code.  In fact, for the 
next major release we do expect to have several important changes in the 
database tables (not yet implemented).  So please be advised that we need a 
patch against the trunk.

Thanks for the analysis and offer of help.

Best regards,

Kern

On Monday 21 July 2008 10:18:56 Yuri Timofeev wrote:
> Hi!
>
> I am developing web interface for PHP5 (specifically on the Zend
> Framework) to bacula.
> I use PHP PDO module.
> And in the process of work, I was confronted with some difficulties.
>
> For example, when writing SQL queries, which will be equally performed
> for MySQL and PostgreSQL.
> I am referring to DB Catalog.
>
> The fact is that the names of tables and fields in different registers
> for MySQL and PostgreSQL:
> src/cats/make_mysql_tables.in
> src/cats/make_postgresql_tables.in
>
> An example may clarify this:
>
> Example 1. PHP PDO is not used.
> http://webacula.svn.sourceforge.net/viewvc/webacula/devel/pgsql_tests/noPDO
>_test.php?view=markup
>
> --- noPDO_test.php
>
> #!/usr/bin/php
> <?php
>
> echo "\n----------------- MySQL test\n\n";
>
> $db  = mysql_connect("localhost", "root", "");
> mysql_select_db("bacula");
> $res = mysql_query("SELECT * FROM Filename LIMIT 3;");
> while ( $row = mysql_fetch_assoc($res) ) {
>    echo $row['FilenameId'], "\t", $row['Name'], "\n";
> }
>
> echo "\n----------------- PostgreSQL test\n\n";
>
> $db = pg_connect("host='localhost' dbname='bacula' user='root'
> password=''"); $res = mysql_query("SELECT * FROM Filename LIMIT 3;");
> //$res = mysql_query("SELECT * FROM filename LIMIT 3;");
> while ( $row = mysql_fetch_assoc($res) ) {
>    echo $row['FilenameId'], "\t", $row['Name'], "\n";
> //   echo $row['filenameid'], "\t", $row['name'], "\n";
> }
>
> ?>
>
> Both test (for MySQL and PostgreSQL) will be successful.
> It does not matter what request will be for PostgreSQL:
>
> SELECT * FROM filename
>
> or
>
> SELECT * FROM Filename
>
> Similarly, no matter how the results will then be processed:
>
> $row['FilenameId']
>
> or
>
> $row['filenameid']
>
> As we can see the names of fields case insensitive.
>
>
> The situation will change if we use the PHP PDO.
>
> Example 2. Using PDO.
> http://webacula.svn.sourceforge.net/viewvc/webacula/devel/pgsql_tests/PDO_t
>est.php?view=markup
>
> --- PDO_test.php
>
> #!/usr/bin/php
> <?php
>
> echo "\n----------------- MySQL test\n\n";
>
> // connect to  DB
> $database = new PDO("mysql:dbname=bacula;host=localhost");
> $stmt = $database->prepare("select * from Filename limit 3;");
>
> if ( $stmt->execute() )  {
>    while ($row = $stmt->fetch())    {
>       $filenameid = $row['FilenameId'];
>       $name       = $row['Name'];
>
>       echo $filenameid, "\t", $name, "\n";
>    }
> }
>
> echo "\n----------------- PostgreSQL test\n\n";
>
> // connect to  DB
> $database = new PDO("pgsql:dbname=bacula;host=localhost");
> $stmt = $database->prepare("select * from filename limit 3;"); // is OK
> //$stmt = $database->prepare('select * from "Filename" limit 3;'); // ok
>
> if ( $stmt->execute() )  {
>    while ($row = $stmt->fetch())    {
>       $filenameid = $row['filenameid']; // ok
> //      $filenameid = $row['FilenameId']; // Error! Field name is case
> sensitive !
>       $name       = $row['name']; // ok
> //      $name       = $row['Name']; // Error! Field name is case sensitive
> !
>
>       echo $filenameid, "\t", $name, "\n";
>    }
> }
>
> ?>
>
> If we reference to the field as $row['FilenameId'] then a error occured.
>
> I think (and not only I) that in the future, it makes sense to do the
> same field names, tables.
>
> src/cats/make_mysql_tables.in
> src/cats/make_postgresql_tables.in
>
> However, such changes will not be back compatible with earlier versions
> bacula.
>
> PS. I can help carry out this work.
>
> Thanks.
>
>
>
>
>
> --
> with best regards
>
> -------------------------------------------------------------------------
> 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 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 Copilotco.