Hi guys,

i have had enough trouble with PEAR DB abstraction Layer.

My problem is with capitalized Column / Table names in case of Postgres SQL.
MySQL supports both small as well as upper case letters .. where as postgres expects everything in small case.

I had already posted two messages in this forum. But haven't got any satisfactory reply. I have latest versions of both PHP (4.0.6) and Postgres(7.1.3) ..(This was suppose to solve my problem!! but it dind't)
..
And i am left with no other option than re-editing 5MB of PHP source files (Converting all column names to lower) ..

What if tomorrow i want to port my Database to Oracle .. should i have to re-edit all my code again (Convert again to UPPER CASE since Oracle supports only upper case column names.)

If this is the situation .. what is the point in having a abstraction layer.

Can somebody suggest me a better(?) solution for this please !!!

Danny Appaiah.

    • [deleted]

    • Post #2

    The point of having abstraction layers is that IT handles all that case-switching stuff.

    If your abstraction doesn't do that, you have a crap abstraction layer.

      You could edit the postgre pear file and write a function that converts table and column names to lowercase. That would have some drawbacks though. First, unless you can just strtolower() the whole thing which you probably shouldn't since it would make there where clauses act funny (if you need case sensitive where). You could do something like convert the string to lower from the beggining till you hit the "WHERE" clause and leave everything after that untouched.

      Secondly, it would be a performance hit. This might not be an issue, depending on server configuration and amount of queries. You would need to do some benchmarking to try that out.

      I can't believe the database does not have an option to ignore upper/lowercase problems like that though. Are you sure you have checked the config?

      Hopefully this will help.
      --Bryan

        Isn't Danny's real problem the fact that he used any upper case in his column names at all?

        I only use lower case column and table names and have never had a problem with changing databases, but should an abstraction layer really care about case? I don't think it should because correct SQL for case sensitive names is those names are enclosed in "s "SillyMixedCaseColumn" from "TaBle"

        I'm not criticizing you here, vincent, but do you really expect an abstraction layer to care about case?

          • [deleted]

          • Post #5

          Yes it is, and yes I do.

          In this case the abstraction layer should know that the database engine can only handle lowercase column-names, so it should not even try to use uppercase ones.

            In this case the abstraction layer should know that the database engine can only handle lowercase column-names

            Er, my quick test in pgsql:

            create table "TestIngCase" ("ColumnName" int);

            CREATE

            \d TestIngCase

            Did not find any relation named "testingcase".

            \d "TestIngCase"

            Table "TestIngCase"
            Attribute | Type | Modifier
            ------------+---------+----------
            ColumnName | integer |

            Quick test in Oracle:
            create table "TestIngCase" ("ColumnName" int);
            select from TestIngCase; = table or view does not exist
            select             from "TestIngCase"; = ok

            So this shows that postgresql and oracledo infact handle mixed case names so a db abstraction layer may want to put quotes around table names but in the case of correct sql, it shouldn't be necessary and would slow down any queries because of the prep work necessary.

            So, I think it's a matter of correct sql and the abstraction layer shouldn't care UNLESS, as you point out, database X doesn't support mixed case.

              • [deleted]

              • Post #7

              Thanks for clearing that up! :-)

                Hi,

                I even tried that ...
                But i have used all those fetched values at other places in my script. so i have to make changes not only in pqsql.php(part of PEAR) .. but also in all my scripts!!.

                e.g

                SELECT Name,Address from MyTable;

                i have used $row["Name"]; in many other places ... so i have to change $row["Name"] to $row["name"];

                  Hi Tom,

                  Forget about Mixed Case letters, what if i use all lower case letters instead, will it work when i port my application to ORACLE.
                  (ORCALE returns everything in UPPER CASE)

                  i really don't like to blame postgres here,
                  since postgres returns all results even if you have ur column names in MIXED or UPPER Cases, it's actually the problem with PEAR DBabstraction layer.

                  e.g : MyTable has 5 entries.

                  $query = SELECT Name,Address from MyTable;
                  $result = $db->query($query);
                  echo $result->numRows();
                  //(it's printing 5)
                  $row = $result->fetchrow();

                  echo $row["Name"]; //prints nothing ...
                  where as
                  echo $row["name"]; //prints the first row entry as Danny ...

                  Can't the abstraction layer take care of such a silly issue ...!!!

                  Danny ....

                    what if i use all lower case letters instead, will it work when i port my application to ORACLE.

                    YES, it will.

                    $query = SELECT Name,Address from MyTable; <sic>
                    is just bad sql. While non-standard databases like mysql eat this stuff with no problem, Postgresql handles it as all lowercase and I really doubt you'll run into a problem if you always use all lowercase table and column names.

                    I found this off here:
                    http://www.gac.edu/~max/pgsql/syntax.htm

                    SQL92 and SQL3 have reserved key words which are not allowed as identifiers and not allowed in any usage other than as fundamental tokens in SQL statements. Postgres has additional key words which have similar restrictions. In particular, these key words are not allowed as column or table names, though in some cases they are allowed to be column labels (i.e. in AS clauses).

                    Tip: Any string can be specified as an identifier if surrounded by double quotes (“like this!”). Some care is required since such an identifier will be case sensitive and will retain embedded whitespace other special characters.

                    So! you'll just have to bit the bullet and stop trying to blame an abstraction layer and realize your naive database design is the real cause of your problem.

                    I can see a database abstraction layer caring IF the database had a limitation to not allow anything but UPPER CASE COLUMN NAMES but because pgsql works properly and supports case per standard, please dont' blame PEAR or Postgresql...they're doing what that should be.

                      Write a Reply...