CI_SESSIONS and ORACLE

[eluser]geekdad[/eluser]
Ummm, not exactly. In Oracle (I believe since version 9 days, but certainly in 10 & 11), it's like this:

Code:

SQL> create table "FOO" ( "UpperCase" date );

Table created.

SQL> create table "foo" ( "LowerCase" date );

Table created.

SQL> select table_name from user_tables;

TABLE_NAME
------------
foo
FOO

SQL> desc "FOO";

Name                 Null?    Type
-------------------- -------  -----
UpperCase                     DATE

SQL> desc "foo";
Name                 Null?    Type
-------------------- -------  -----
LowerCase                     DATE

SQL> desc foo
Name                 Null?    Type
-------------------- -------  -----
UpperCase                     DATE

The problem is that the Sessions class assumes the world works like MySQL and uses literal (lowercase) strings everywhere.

The general problem of Oracle case sensitivity in CI is solved either with:

a) quick hack by changing the assignment of $_escape_char in CI_DB_oci8_driver to: ''

or (better):

b) by overloading the oci8_driver with your own in /system/application/libraries thusly:

Code:

class MY_DB_oci8_driver extends CI_DB_oci8_driver {

    public function __construct($params){
        parent::__construct($params);
        log_message('debug', 'MY OCI8 Extended DB driver class instantiated');

    }

    # Overload to just return the table/column/object as given
    function _escape_identifiers( $item ) {
        return $item;
    }
}

But this still doesn't help with the default broken behavior in Sessions. There, you either need to construct an all-lower case table name and columns as laid out in the the CI User Guide, re-worked for Oracle:

Code:

CREATE TABLE "ci_sessions" (
  "session_id"    varchar(40) DEFAULT '0'
   CONSTRAINT ci_sessions_sess_id_nn  NOT NULL
, "ip_address"    varchar(16) DEFAULT '0'
   CONSTRAINT ci_sessions_ip_addr_nn  NOT NULL
, "user_agent"    varchar(50)
   CONSTRAINT ci_sessions_user_agt_nn NOT NULL
, "last_activity" int         DEFAULT  0
   CONSTRAINT ci_sessions_last_act_nn NOT NULL
, "user_data"     varchar(4000)  -- Note: NN breaks with autoloaded CI Sessions
, CONSTRAINT ci_sessions_pk
   PRIMARY KEY ( "session_id" ) USING INDEX -- Optional: TABLESPACE INDX
);

Or re-write Sessions.php with proper upper case. The "official" answer is to use the built mechanisms to extend the core built-in CI libraries, but unfortunately, in the case of DB anyway, there seems to be a subtle bug, where if both 'sessions' and 'database' are called in autoload, any custom overloaded methods to the $this->db are ignored for the autoconnect (they are honored for garden variety $this->db->query, etc. in your controller, but that doesn't help for Sessions).

But really, please don't use the above table build -- I'm only posting for the copy/paste folks who are desperate and have to get it to work right now. Doing this is very un-Oracley, and eventually, it will blow up in weird and unpredictable ways with a lot of 3rd party tools and import/export utilities/libraries that assume the (default) uppercase table and column identifiers.

Happy to share more if folks are interested (like true Oracle-native bind variable substitution instead of the silly MySQL-like quote escapes). Note to the Oracle newbies: not only will bind variables dramatically boost your scalability (see the Underground PHP Manual by Oracle if you don't believe me), but it has the side benefit of eliminating any chance of SQL Injection.

Cheers.