[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sitemanager-devel
Subject:    Case-sensitivity problems with PostgreSQL
From:       David Osborne <david.osborne () nottingham ! ac ! uk>
Date:       2003-07-04 16:12:22
[Download RAW message or body]

Hi

I'm looking at siteManager (2.4.1) as a way of improving a
database-backed PHP web application I developed. The existing database
is in Postgres, so siteManager must work with that.

I started by looking at the testSite application, which I'm trying out
with RedHat 9, Apache 2.0.46, PHP 4.3.2 and PostgreSQL 7.3.2. It mostly
works, but the members test suite fails because some SQL statements
don't take into account the case-sensitivity of the field names in the
members table. (I think this is a similar problem to one I found in the
mailing list archives at
http://marc.theaimsgroup.com/?l=sitemanager-devel&m=100463957514422&w=2

The error I get is

dbselectentity:: : DB Error: no such field
SELECT idxNum AS _id, userName FROM members ORDER BY userName
[nativecode=ERROR: Attribute "idxnum" not found ]

I think this is because the fieldname idxNum here is unquoted --
Postgres is normally case-insensitive regarding names of tables and
fields, so treats the unquoted fieldname as if it were lowercase.
However, the members table was created with the fieldname quoted,
"idxNum", which preserves the mixed case. I dropped my test database and
hacked the test.db.sql file to create the tables using only lowercase
fieldnames but there are some instances in siteManager where the
fieldnames ARE quoted for pgsql, so that's not the solution.

I've grepped the testSite files and files in the installed siteManager
tree but I'm not sure about the SQL statement giving the error (I
grepped -i 'select idxnum')... could it be this, from
lib/sfInputEntities/dbSelectEntity.inc ?
        $SQL = "SELECT {$this->directive['dataField']} AS _id,
{$this->directive['viewField']} FROM {$this->directive['tableName']} $wC
$gB $oB";

If someone could give a pointer to help locate it, I can experiment with
quoting the relevant fieldnames to see if that fixes the problem. I read
Shannon's comment that MySQL is mostly used for development, so I'd be
happy to do some Postgres testing. As I'm not a MySQL user, can someone
say if it would have a problem using quoted fieldnames? If it would be
OK, I'll submit patches. Otherwise, something more elaborate, along the
lines of the switch statement starting at line 81 in
lib/memberSystems/default_memberSystem.inc, where the fieldnames are
quoted for pgsql, would be needed.

Thanks in advance for any help or suggestions.

David
-- 
David Osborne
Central Systems & Security Team, Information Services
The University of Nottingham
http://www.nottingham.ac.uk/~cczdao/

[prev in list] [next in list] [prev in thread] [next in thread]