Using Oracle with CakePHP: 15 Minute Blog Tutorial

As I mentioned in my ‘About Me’ page, I’m fairly new to PHP, having been primarily a Java guy for the last 10 years. Having used or investigated several of the webapp frameworks from the Java world (Struts, Stripes, Spring), when I found myself needing to program in PHP, I naturally looked around for a good MVC framework. There are several out there, as most of you no doubt know. The one that has really got me interested right now is CakePHP. I think the thing I’m most impressed with about it is the ‘convention over configuration’ philosophy that I believe it takes from Rails. Having seen how configuration-heavy Struts and Spring are in the Java world, this really was a breath of fresh air.

One thing that initially concerned me, though, is that there isn’t an Oracle driver currently released for Cake. This led me to find Jeff Loiselle’s dbo_oracle driver. While it’s not in the current release, it seems to be pretty mature, and I’ve started to use it as I learn the framework. Actually, I’ve ‘translated’ the DDL for some of the easy tutorials from MySQL syntax to Oracle syntax. If anyone is interested below is the Oracle DDL for the 15 Minute Blog Tutorial. My apologies if someone has already done this! Please let me know, and I’ll give proper credit to whoever did it first. 😉

First, You’ll need to get the dbo_oracle driver from the link to Jeff’s blog that I posted above, where he gives the url to his svn trunk. Save it to your cake/libs/model/dbo directory along with all the other database drivers. Next, you’ll need to configure Cake to connect to Oracle. Here’s a sample entry for Oracle in the /app/config/database.php file:

----------------------------------------------------------
var $default = array('driver' => 'oracle',
 			'connect' => 'oci_connect',
			'host' => 'mypcname.Vanderbilt.edu',
			'login' => 'mylogin',
			'password' => 'notgonnatellyou',
			'database' => 'mySID',
			'prefix' => '');
----------------------------------------------------------

Now here’s the DDL in Oracle syntax for creating the POSTS table and inserting the three initial rows. Also notice that I’m mimicing the MySQL autoincrement by using an Oracle sequence and a before insert trigger to populate the ‘id’ value:

----------------------------------------------------------
CREATE TABLE POSTS
( ID NUMBER,
  TITLE VARCHAR2(50),
  BODY VARCHAR2(4000),
  CREATED DATE,
  MODIFIED DATE,
   PRIMARY KEY (ID)
);

CREATE SEQUENCE POSTS_SEQ
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  INCREMENT BY 1
  START WITH 1
  NOCACHE
  NOORDER
  NOCYCLE ;

CREATE OR REPLACE TRIGGER POSTS_BEF_UPDATE_TRG
BEFORE INSERT ON POSTS
FOR EACH ROW
BEGIN
      select posts_seq.nextval into :new.id from dual;
END;
/
ALTER TRIGGER POSTS_BEF_UPDATE_TRG ENABLE;

INSERT INTO POSTS (TITLE, BODY, CREATED) VALUES ('The title', 'This is the post body.', SYSDATE);
INSERT INTO POSTS (TITLE, BODY, CREATED) VALUES ('A title once again', 'And the post body follows.', SYSDATE);
INSERT INTO POSTS (TITLE, BODY, CREATED) VALUES ('Title strikes back', 'This is really exciting! Not.', SYSDATE);

commit;
----------------------------------------------------------

Okay, the above info basically corresponds to sections 3 and 4 of the tutorial. With that, you should be set up to do the rest of the tutorial on Oracle instead of MySQL. Enjoy, and please let me know if you have any problems with this. It works for me, but I know that doesn’t guarantee anything for you … 😉

-William