[OGo-Developer] OGo, PostgreSQL 8.3, & INT casting

[Adam Tauno Williams]

PostgreSQL 8.3, and I think some builds of 8.2.x, not longer performing
automatic casting of INT to TEXT when INTs are compared to character
types. <http://www.postgresql.org/docs/8.3/static/release-8-3.html>
This causes at least ACL processing in OGo to die with -

ERROR:  IN types character varying and integer cannot be matched

 - because a query like -

SELECT DISTINCT B.company_id  FROM person AS B WHERE (( ( B.firstname
ILIKE '%%yser%%' ) OR ( B.name ILIKE '%%yser%%' ) ))  AND (( B.owner_id
= 10100 ) OR ( ( B.is_private = 0 OR B.is_private IS NULL ) AND (  ( 0 =
( SELECT COUNT(*) FROM object_acl WHERE object_id = B.company_id ) OR 0
< ( SELECT COUNT(*) FROM object_acl WHERE object_id = B.company_id AND
action = 'allowed' AND permissions LIKE '%r%' AND ( auth_id = 10100 OR
auth_id IN ( 9981, 9991, 10003, 11530, 55000, 77210, 142730, 142780,
142850, 393280, 423400, 445160, 949410, 949540, 955770, 970730, 970990,
1096320, 9153790, 9153880, 9154000, 10597021, 10735169 ) ) ) )  ) )) AND
(B.is_template_user IS NULL OR B.is_template_user = 0) AND (B.db_status
<> 'archived') LIMIT 50

 - is generated where object_acl.auth_id [a CHAR(255)] is IN'd with an
integer list.   Altering this query to -

  ... auth_id::INT IN ( 9981, 9991, ....)

- fixes this.  On the other hand I think a better fix is just to make
auth_id an integer column.  I've never seen this column be anything but
an integer [object id] and integer comparison and integer set comparison
is faster than processing text types.

I've done -
OGo=# ALTER TABLE object_acl ADD auth_id_int INT;
OGo=# UPDATE object_acl SET auth_id_int = auth_id::INT;
OGo=# ALTER TABLE object_acl DROP auth_id;
OGo=# ALTER TABLE object_acl ADD auth_id INT;
OGo=# UPDATE object_acl SET auth_id = auth_id_int;
OGo=# ALTER TABLE object_acl DROP auth_id_int;
OGo=# VACUUM ANALYZE object_acl;

This fixes the issue and everything still seems to work fine.

The "original" object_acl table is -

CREATE TABLE object_acl (
  object_acl_id int not null,
  sort_key      int not null,
  action        varchar(10) not null,
  object_id     varchar(255) not null,
  auth_id       varchar(255),
  permissions   varchar(50));
 
To me it just seems like having object_id and auth_id as VARCHAR(255) is
pointless.

I've also proceeded to change object_id to an INT -

OGo=# ALTER TABLE object_acl ADD object_id_int INT;
OGo=# UPDATE object_acl SET object_id_int = object_id::INT;
OGo=# ALTER TABLE object_acl DROP object_id;
OGo=# ALTER TABLE object_acl ADD object_id INT;
OGo=# UPDATE object_acl SET object_id = object_id_int;
OGo=# ALTER TABLE object_acl DROP object_id_int;
OGo=# VACUUM ANALYZE object_acl;

 - leaving me with a table of - 

    Column     |         Type          | Modifiers 
 object_acl_id | integer               | not null
 sort_key      | integer               | not null
 action        | character varying(10) | not null
 permissions   | character varying(50) | 
 auth_id       | integer               | 
 object_id     | integer               | 

Everything still works.  

However, the OGoModel represents these data-types as VARCHAR(255), so
maybe my fix isn't appropriate.

<snip>
        {
          name           = "authId";
          columnName     = "auth_id";
          externalType   = "VARCHAR(255)";
          valueClassName = "NSString";
          allowsNull     = Y;
          width          = 255;
        },
        {
          name           = "objectAclId";
          columnName     = "object_acl_id";
          externalType   = "INT";
          valueClassName = "NSNumber";
          valueType      = i;
        },
        {
          name           = "objectId";
          columnName     = "object_id";
          externalType   = "VARCHAR(255)";
          valueClassName = "NSString";
          width          = 255;
        },
</snap>

I haven't tried modifying the model to use INT/NSNumber yet.  Personally
I think just storing INTs is better [and faster],  but maybe the
object_acl has some [edge / undocumented] functionality I'm not aware
of?