William Graham’s blog

July 1, 2008

Adding support for Oracle’s bitmap indexes to Rails migrations

Filed under: Oracle,Programming,Ruby on Rails,Web Development — liamgraham @ 9:29 pm

My coworker Greg Donald helped me find a way to override the way Rails migrations create Oracle sequences by putting code in the environment.rb file. I mentioned at the bottom of that post that I would like to find a way to make it also support Oracle’s bitmap indexes. This turned out to be trivial, simply a matter of adding another section to the override code I already had. The method which creates the indexes is ActiveRecord::ConnectionAdapters::SchemaStatements.add_index. It surprises me that there isn’t an Oracle-specific override in the OracleAdapter. Anyway, my code from the last blog post already had the ActiveRecord::ConnectionAdapters in scope, so I added another section as below:

module SchemaStatements
  def add_index(table_name, column_name, options = })
    column_names = Array(column_name)
    index_name   = index_name(table_name, :column => column_names)

    if Hash === options # legacy support, since this param was a string
      index_type = options[:unique] ? "UNIQUE" : options[:bitmap] ? "BITMAP" : ""
      index_name = options[:name] || index_name
    else
      index_type = options
    end
    quoted_column_names = column_names.map { |e| quote_column_name(e) }.join(", ")
    execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{quoted_column_names})"
  end
end

Only one change was made to the original:

index_type = options[:unique] ? "UNIQUE" : ""

became

index_type = options[:unique] ? "UNIQUE" : options[:bitmap] ? "BITMAP" : ""

This now allows the specification of a bitmap option to add_index in the migration file:

add_index "mytable", ["mycolumn"], :name => "mytable_bitmap_idx", :bitmap => true

I’m pretty happy with the ability Ruby on Rails gives you to customize things like this. Being able to override class methods in this way is really pretty special.

Altering the way Rails migrations create Oracle sequences

Filed under: Oracle,Programming,Ruby on Rails,Web Development — liamgraham @ 7:04 pm

Lately I’ve been using Ruby on Rails for a project at work, and am really enjoying it. Several things bug me about managing an Oracle database with migrations, though. The main one that just struck me as bizarre is how each table’s sequence is created with a starting value of 10,000. Why? Where did that number come from, who came up with it, and why is there not a way to override it within the migration, short of using execute to drop the sequence after the fact and recreate it (since you can’t change an Oracle sequence’s starting value with an alter statement).

I dug into the code for the OracleAdapter, and sure enough, there it is, right there in the create_table method:

def create_table(name, options = {}) #:nodoc:
  super(name, options)
  seq_name = options[:sequence_name] || "#{name}_seq"
  execute "CREATE SEQUENCE #{seq_name} START WITH 10000" unless options[:id] == false
end

I was all set to just modify the CREATE SEQUENCE code above when my coworker Greg Donald, who knows much more about Ruby and RoR than I do, knocked me out with yet another in the long line of things about Ruby on Rails that make me yell “Sweet!” Thanks, Greg!

Within your Rails app’s environment.rb file you can override code. Simply by putting this snippet at the bottom of the file, I was able to change the way migrate creates my Oracle sequences:

module ActiveRecord
  module ConnectionAdapters
    class OracleAdapter
      def create_table(name, options = {}) #:nodoc:
        super(name, options)
        seq_name = options[:sequence_name] || "#{name}_seq"
        execute "CREATE SEQUENCE #{seq_name} START WITH 1 NOCACHE" unless options[:id] == false
      end
    end
  end
end

Notice that I also added in NOCACHE while I was at it, to prevent Oracle from grabbing 20 sequence values at a time.

This is just cool. Without having to keep track of custom changes made to the adapter, which could be lost with future upgrades, and without having to resort to a bunch of execute statements within the migration file to drop and recreate the sequences, I’m able to get the custom behavior that I need. Honestly, of course, I think that the starting value should default to 1, not 10000, and I wish that something like NOCACHE was an option that could be passed. But this gets me what I need, and I can see this sort of override capability coming in very handy for other Rails customizations.

Now on to adding something to support bitmap indexes! 🙂

June 5, 2007

Oracle modifications for Rob Allen’s Zend Framework Tutorial

Filed under: Oracle,PHP,Programming,Zend Framework — liamgraham @ 9:08 pm

After working with CakePHP, I also decided to give the Zend Framework a try. I initially had some problems with it back at beta 0.9.1, but was told by the DB team lead that many Oracle issues would be fixed by the first release. Well, Zend Framework 1.0.0 Release Candidate 1 is out now, so I revisited it.

I had chosen the tutorial written by Rob Allen (Akra), available here . This is a very neat and well-maintained tutorial on creating a simple Album / CD tracking system. Below is the DDL to create the database objects, converted to Oracle syntax. Note that, as in the CakePHP tutorial work I did earlier, I’m mimicing an auto-increment ID by using a before-insert trigger to assign a value from the sequence:

—————————–
CREATE TABLE album (
id number(11,0) NOT NULL,
artist varchar2(100) NOT NULL,
title varchar2(100) NOT NULL,
PRIMARY KEY (id)
);

create sequence album_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
INCREMENT BY 1
START WITH 1
NOCACHE
NOORDER
NOCYCLE ;

CREATE OR REPLACE TRIGGER ALBUM_BEF_UPDATE_TRG
BEFORE INSERT ON ALBUM
FOR EACH ROW
BEGIN
select album_seq.nextval into :new.id from dual;
END;
/

ALTER TRIGGER ALBUM_BEF_UPDATE_TRG ENABLE;

insert into album (artist, title) values (‘James Morrison’, ‘Undiscovered’);
insert into album (artist, title) values (‘Snow Patrol’, ‘Eyes Open’);
commit;

—————————–

Here are the settings to use in your config.ini (Rob, thanks for pointing out that I had initially forgotten to include this!):

—————————–

[general]
db.adapter = Oracle
db.config.host = myDatabaseHostName
db.config.username = myUserName
db.config.password = notGonnaTellYou
db.config.dbname = mySID

—————————–

In addition to the DDL changes, there is some strange behavior due to the fact that Oracle converts all non-quoted database object names to uppercase in the data dictionary. This means that attribute names in your Zend Framework PHP code that refer to column names need to be in uppercase as well. For example, here is an excerpt from _form.phtml from the tutorial:

————–
<label for=”artist”>Artist</label>
escape(trim($this->album->artist));?>”/>
————–

In order for this to work in Oracle, it needs to be modified so that the ‘artist’ attribute is uppercased:

————–
<label for=”artist”>Artist</label>
escape(trim($this->album->ARTIST));?>”/>
————–

You will need to make this change everywhere that there is a reference to an attribute which maps to an Oracle column name. Just go through the code in the tutorial and uppercase artist, title, and id when they appear as attributes of album. It makes for rather strange-looking code, but it is the only way that I know of to get the Zend Framework and Oracle to work together. Actually, I take that back … it would probably work if you used quoted identifiers to force Oracle to store lowercase object names, but I hate doing that. It means that anytime you write raw SQL you have to embed database object names in quotes, and it just gets nasty, imho.

One note:
1) DON’T uppercase album in the above, i.e., don’t do this: $this->ALBUM->ARTIST. In this case, ‘album’ is a variable name, not something that needs to map to the Oracle data dictionary. That’s probably pretty obvious, but I thought I’d throw it out there anyway, just in case …

I hope this helps anyone who is trying to get going with Oracle on the Zend Framework. Please contact me with any questions, or post comments. Cheers!
–William

April 19, 2007

Using Oracle with CakePHP: 15 Minute Blog Tutorial

Filed under: CakePHP,Oracle,PHP,Programming — liamgraham @ 9:39 pm

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

Blog at WordPress.com.