CI_SESSIONS and ORACLE

[eluser]neofactor[/eluser]
We leverage Oracle 10g here


Two things:

1) What is the proper Create Table for Oracle CI_SESIONS Table?


2) Also... I noticed that all of my Column names need to be UPPERCASE. No biggie... I just keep an eye on that... BUT... CI sends the CI_SESSIONS names in lower case. That is an issue. How can that be changed?

[eluser]bretticus[/eluser]
1) Should be easy to convert if you know your Oracle SQL syntax I'd assume.

2) Extend the session class. See Extending Native Libraries.

[eluser]neofactor[/eluser]
Your fix for #2...
I guess I am looking to see specifically how to do this. I do not even know where to intercept it. And if I do create my own... would it run for CI calls as well or just mine?

[eluser]bretticus[/eluser]
[quote author="neofactor" date="1253667342"]Your fix for #2...
I guess I am looking to see specifically how to do this. I do not even know where to intercept it. And if I do create my own... would it run for CI calls as well or just mine?[/quote]

Copy the portions of the CI_Session class that have database entity names (that are lower case.) Then duplicate them, changing the database entity names in your MY_Session class in your library folder of your application folder. Again, it's all in that manual link that I submitted earlier (the manual assumes you know how to extend classes in PHP.)

[eluser]kgill[/eluser]
Have you read the user guide documentation on the session class? The SQL for creating the table is right there, all you need to is change the column types (varchar2, number, clob).

Column names do not need to be uppercase, Oracle isn't case sensitive when it comes to table and column names used in SQL, only text used in where clauses. e.g.

Code:

where name = 'Smith' is not the same as where name = 'smith'

Oracle however does return column names in uppercase when you do a select, so when dealing with your result sets you need to remember things will be uppercase.

[eluser]neofactor[/eluser]
Yes.. that is how I set things up...

Code:

Name           Null     Type
SESSION_ID     NOT NULL VARCHAR2(40)    
IP_ADDRESS     NOT NULL VARCHAR2(16)
USER_DATA               CLOB()
USER_AGENT     NOT NULL VARCHAR2(50)
LAST_ACTIVITY  NOT NULL NUMBER(10)

I get an error on insert:
INSERT INTO "CI_SESSIONS" ("session_id", "ip_address", "user_agent", "last_activity") VALUES ('620cf53c17ee5ea29b769bb6091c6261', '111.111.111.111', 'Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en', 1253684735)

[eluser]kgill[/eluser]
My guess is the quotes, "CI_SESSIONS" will throw a ORA-00942 - table or view does not exist error. Oracle will let you get away with quoting the column names (it's not necessary though) but it will throw a fit if you try to quote the table name.

For future reference, anytime you run into an error with your SQL like that, paste it into SQLPlus/Toad/whatever interface you use to run queries and run the query there. CI doesn't pass Oracle's error codes back to you so the easiest way to track stuff down is to run it directly unless you feel like hacking the driver.

[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.

[eluser]neofactor[/eluser]
Thanks for the info. Where are you associated/work? Curious if you are a University like us.

[eluser]geekdad[/eluser]
I'm in biotech/pharma in the Research Triangle area (Raleigh, Durham, Chapel Hill), but have been using Oracle and PHP since 1999. Big believer in FOSS.