Some convenience code for using Micah Alles’ Rails migration testing plugin

Lately I’ve been using Micah Alles’ migration testing plugin in my Ruby on Rails development. I very much like being able to use it to test migrations, and I’ve also been using it to compare schemas that should be mirrors of each other. The syntax is very nice, with assert_table statements basically mirroring the migration create_table syntax, performing an assertion that the table should be in the target database and should match the structure defined in the statement, such as:

    assert_table :groups do |t|
      t.column :group_id, :integer
      t.column :site_id, :integer, :limit => 10
      t.column :group_desc, :string, :limit => 100
      t.column :fam, :integer, :limit => 10
      t.index ["fam"], :name => :groups_fam_idx
    end

The problem, as you may notice from my syntax above, is that this is set up for the older t.column syntax that was current when Micah wrote the plugin, rather than the newer t.<datatype> syntax. Since I’m using this plugin to check mirrors of databases (often to compare production schemas against dev or test), I wanted a quick way to do something set up assert_table statements using a schema.rb file as a template. I initially looked at the plugin code thinking maybe I would write a version to work with the new syntax, but quickly figured out that I’m just too much of a Ruby newb to tackle that task. I ended up just writing some code which takes a schema.rb file (in the new syntax) as a commandline argument and creates the necessary assert_table statements in the old syntax, which can then be cut and pasted into the migration test file generated from Micah’s template.

Save the code below to a Ruby file (mine is named generate_migration_test_asserts.rb). Usage is simply

  ruby generate_migration_test_asserts.rb schema.rb

That is assuming that you want to read in a schema.rb file and are in the file’s directory. Adjust to suit if you need to read in another file. You can redirect the output to a text file if you’d rather do that than copy from the screen.

I mention in the TODO section of the file header that the code could also be enhanced to do more general translation between the current create_table syntax and the older syntax. Maybe I’ll tackle that when I get a few minutes to spare …

-William

################################################################################
#
# generate_migration_test_asserts.rb
#
# code to translate new-syntax table definitions into the older syntax for use
# in 'assert_table' statements with Micah Alles' migration testing plugin:
#   http://spin.atomicobject.com/2007/02/27/migration-testing-in-rails/
#
# With some modification or enhancement to add a choice between 'assert_table'
# and 'create_table' statements, it could also be used more generally just to
# translate from this:
#
#   create_table "foo", :primary_key => "foo_id", :force => true do |t|
#     t.string   "name", :limit => 40
#     t.string   "desc", :limit => 100
#     t.datetime "created_at"
#     t.string   "created_by",  :limit => 30
#   end
#
# to this:
#
#   create_table "foo", :primary_key => "foo_id", :force => true do |t|
#     t.column :name, :string, :limit => 40
#     t.column :desc, :string, :limit => 100
#     t.column :created_at, :datetime
#     t.column :created_by, :string, :limit => 30
#   end
#
#
# TODO:
#  to enhance for table creation, will need to support the specification of
#  column defaults and nullability
#
# William Graham, 10/22/2008
#
################################################################################

########################################################
#
# helper class for indices
#
########################################################
class Index
  attr_accessor :name, :columns, :unique

  def initialize(name)
    @name = name
  end
end

########################################################
#
# helper class for columns
#
########################################################
class Column
  attr_accessor :name, :type, :limit, :default, :null

  def initialize(name = nil, type = nil, null = nil, limit = nil, default = nil)
    @name, @type, @null, @limit, @default = name, type, null, limit, default
  end
end

########################################################
#
# helper class for tables
#
########################################################

class Table
  attr_accessor :name, :columns, :indices

  def initialize(name = nil, cols = Array.new, inds = Array.new)
    @name, @columns, @indices = name, cols, inds
  end

  # write out an assert_table statement
  def to_test_s
    s = "assert_table :", self.name, ' do |t|', "\n"
    self.columns.each do |c|
      s << "  t.column :" << c.name << ", :" << c.type
      if (c.limit)
        s << ', :limit => ' << c.limit
      end
      s << "\n"
    end

    self.indices.each do |i|
      s << "  t.index [" << i.columns << "], :name => :" << i.name
      if (i.unique)
        s << ', :unique => true'
      end
      s << "\n"
    end

    s << "end"

    return s
  end

  ########################################################
  #
  # parse a create_table line to get the table name plus
  # the name of the primary key column, if it's defined
  #
  ########################################################
  def parse_create(create_str)
    tabname_regex = /^\s*create_table\s+[\:\"\'](\w+)/
    pk_regex = /primary_key\s*\=\>\s*[\:\"\'](\w+)/

    tabname_regex =~ create_str
    data = Regexp.last_match
    self.name = data[1] if (data)

    pk_regex =~ create_str
    data = Regexp.last_match
    self.columns << Column.new(data[1], :integer) if (data)
  end

  ########################################################
  #
  # parse a column definition line to get column name,
  # type, limit, and default value
  #
  ########################################################
  def parse_col(col_str)
    main_regex  = /^\s*\w\.(\w+)\s+[\:\"\'](\w+)/
    limit_regex = /\:limit\s*\=\>\s*(\d+)/
    null_regex  = /\:null\s*\=\>\s*(\w+)/
    default_regex  = /\:default\s*\=\>\s*(\w+)/

    main_regex =~ col_str
    data = Regexp.last_match

    if (data)
      c = Column.new(data[2], data[1])
      limit_regex =~ col_str
      data = Regexp.last_match
      c.limit = data[1] if (data)

      null_regex =~ col_str
      data = Regexp.last_match
      c.null = data[1] if (data)

      default_regex =~ col_str
      data = Regexp.last_match
      c.default = data[1] if (data)

      self.columns << c
    end
  end

  ##########################################################
  #
  # parse an add_index line to get the index name, list of
  # columns, and whether or not it is a unique index
  #
  ##########################################################
  def parse_index(index_str)
    #note: capture the first \w+ if table name is needed in the future
    col_regex = /\[(.*)\]/
    name_regex = /\:name\s*\=\>\s*[\"\'](\w+)/
    unq_regex = /\:unique\s*\=\>\s*(\w+)/

    name_regex =~ index_str
    data = Regexp.last_match

    if (data)
      indx = Index.new(data[1])
      col_regex =~ index_str
      data = Regexp.last_match
      indx.columns = data[1] if (data)

      unq_regex =~ index_str
      data = Regexp.last_match
      indx.unique = data[1] if (data)

      self.indices << indx
    end
  end

  def print_match_data(data)
    if (!data)
      return
    end

    print "DataMatch object:\n"
    0.upto(data.size) do |i|
      print "\t",i,": ",data[i],"\n"
    end
  end
end

################################################################################

fname = ARGV[0]
f = File.open(fname, 'r')

t = nil
f.each do |line|
  if (line =~ /create_table/)
    print t.to_test_s,"\n\n" if (t)
    t = Table.new
    t.parse_create(line)
    next
  end

  if (line =~ /t\./)
    t.parse_col(line)
    next
  end

  if (line =~ /add_index/)
    t.parse_index(line)
    next
  end
end

print t.to_test_s, "\n"

################################################################################

Adding support for Oracle’s bitmap indexes to Rails migrations

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

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! 🙂

Ajax 101 tutorial updated for Zend Framework v1.5

I’ve updated the Ajax 101 tutorial for version 1.5 of Zend Framework. At some point after I originally wrote it, the Zend Framework team removed a loophole that I and apparently many others mistakenly relied on, relating to resolution of action names. In the original version of this tutorial, I had the javascript making an Ajax call to ‘index/getData’, which was resolving to the getDataAction method in the index controller. However, as of version 1.5 of the framework, the case is not getting preserved, so that my ‘getData‘ was getting converted to lowercase ‘getdata‘, resulting in the error that Ernesto commented about way back in February:

Fatal error: Uncaught exception ‘Zend_Controller_Action_Exception’ with message ‘IndexController::getdataAction() does not exist and was not trapped in __call()’

Basically, what needs to happen is that there needs to be a word separator in the action name in the javascript so that the framework really knows how to camelcase it, such as ‘index/get-data’. I have edited the blog post and the zipped code to correct this. For an in-depth discussion of this issue, and the team’s reasoning for blocking the loophole, see the manual here: http://framework.zend.com/manual/en/zend.controller.migration.html

Sorry for any problems people have been seeing!

-william

Ajax 101: A Simple Example of Using Ajax with the Zend Framework

NOTE: I’ve updated this tutorial for version 1.5 of Zend Framework. At some point after I originally wrote this, the Zend Framework team removed a loophole that I and apparently many others mistakenly relied on, relating to resolution of action names. In the original version of this tutorial, I had the javascript making an Ajax call to ‘index/getData’, which was resolving to the getDataAction method in the index controller. However, as of version 1.5 of the framework, the case is not getting preserved, so that my ‘getData‘ was getting converted to lowercase ‘getdata‘, resulting in the error that Ernesto commented about way back in February:

Fatal error: Uncaught exception ‘Zend_Controller_Action_Exception’ with message ‘IndexController::getdataAction() does not exist and was not trapped in __call()’

Basically, what needs to happen is that there needs to be a word separator in the action name in the javascript so that the framework really knows how to camelcase it, such as ‘index/get-data’. I have edited the blog post and the zipped code to correct this. For an in-depth discussion of this issue, and the team’s reasoning for blocking the loophole, see the manual here: http://framework.zend.com/manual/en/zend.controller.migration.html

Sorry for any problems people have been seeing!

-william

——————————————————————

I recently needed to port some existing php code which used Ajax over to the Zend Framework. While there are several tutorials out there with lots of details, I didn’t find a simple example that would just get me started with how to work Ajax calls into the MVC framework architecture. Eventually, I figured things out by referring to several different tutorials and doing a good amount of Googling. I decided it might be useful to have a very simple example of porting an existing Ajax app to the Zend Framework.

Warning: this app is very simplistic … it is solely designed to show minimum ‘hello world’ type of Ajax functionality, and how to fit that into the Zend Framework. There are plenty of more advanced tutorials out there, but this can get you started and show you how to make an Ajax call and respond to it with a Zend controller action. To simplify things even further, I’m using the prototype Javascript library, which makes life a lot easier by, among other things, providing a cross-browser means of creating and sending Ajax requests.

Versions: I wrote this against Prototype 1.5.1.1 and Zend Framework 1.0.1 (now updated for ZF v. 1.5)

Assumptions: I assume this is running on Apache, as I will include some .htaccess files to deal with url rewriting in the Zend version. I assume you’re familiar with Ajax … if not, there are tons of tutorials out there available via Google. I also assume you have some basic familiarity with the Zend Framework. If not, I recommend Rob Allen’s great introductory tutorial. And if you need to run the tutorial on Oracle instead of Mysql, well, of course, I recommend this blog post … 😉

If you want the code, download the zip file, which contains both the Zend app (ajax101_zend directory) and the non-Zend app (ajax101 directory). Both directories also contain the version of prototype that I used. Earlier versions of the download also contained Zend Framework v1.0.1, but I removed this at the same time I updated the code to work with ZF v1.5. I wrote the code on Windows, so be aware of that if you have any problems with the files on other platforms …

Okay, on to the code. First, the non-Zend version. I created three files (index.html, boxy.css, and server_script.php) and put them in the same directory on my webserver, along with the prototype.js file. Here’s the index.html, with a couple of Javascript functions to handle the Ajax call and response (we’ll factor those out to a separate file in the Zend version):

————————————————————————————

<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.0 Transitional//EN”>
<html>
<head>
<title>Ajax 101</title>
<link rel=”stylesheet” href=”boxy.css”>
<script type=”text/javascript” src=”prototype.js”></script>

<script language=’javascript’>

//handle the Ajax response …
function handleResponse(transport)
{
$(‘hello’).innerHTML = transport.responseText;
}

//creates a prototype Ajax object, sends a request, and registers the callback function ‘handleResponse’
function callAjax(s)
{
var myAjax = new Ajax.Request(‘server_script.php’,
{method: ‘get’, parameters: {state: s},
onComplete: handleResponse});
}

</script>

</head>
<body>
<p>
<span id=’hover_span’ onmouseover=”callAjax(‘do’)” onmouseout=”callAjax(‘undo’)”> <b>Hover here to trigger Ajax call:</b> </span>
</p>

<span id=’hello’> boring pre-Ajax-call text … </span>

</body>
</html>

————————————————————————————

Here’s the simple stylesheet used:

————————————————————————————

#hover_span
{
font-size: 12pt;
}

#hover_span:hover
{
cursor: crosshair;
background-color: yellow;
}

————————————————————————————

And here is the php server-side script which simply echoes some text back depending on the ‘state’ parameter passed in the request:

————————————————————————————

<?php

$state = $_REQUEST[‘state’];

if ($state == ‘do’)
{
echo ‘<h1>exciting text retrieved from server!</h1>’;
}
else if ($state == ‘undo’)
{
echo ‘reset to boring …’;
}
else
{
echo ‘unknown state parameter passed to server!!’;
}

————————————————————————————

So, if you trace through what happens, when you hover over the text at the top of the index.html page, Hover here to trigger Ajax call, the mouseover event fires, sending an Ajax request with a ‘state’ parameter of ‘do’ to the server. The server responds with the text exciting text retrieved from server! enclosed in h1 tags. When the mouse leaves the text at the top of the page, the mouseout event fires, sending an Ajax request with a ‘state’ parameter of ‘undo’ to the server, which responds with the text reset to boring …

Pretty simple, nothing exciting or difficult … just a super-basic example of doing something with Ajax. Okay, so how do we move that into the Zend Framework?

The first thing that is going to change is the directory structure. As I mentioned, for the simple ‘Ajax 101’ app, I just put all the files in the same directory on my webserver. For Zend, I’m going to need to separate things out, which is a good thing. I’m following a typical Zend Framework directory structure here, so it will look like this:

docroot
–app
—-controllers
—-views
——scripts
–library
–public
—-scripts
—-styles

You may have noticed the lack of a model directory. That’s because in this very simplistic example, there are no models.

Okay, so what next? Well, first we need to install the framework. Download the latest version of the framework (I used v1.5.1 in the latest work I did on this). Once you’ve extracted it, copy the library/Zend directory to your docroot/library directory.

Next we need a bootstrap file for the framework. This will be pretty simple for this example, since we’re not doing much, so we won’t be loading a bunch of Zend classes, setting up database connections, etc. All we’ll really be doing is some basic settings, like error reporting, include paths, and setting up the controller. This is in the index.php file in the docroot:

————————————————————————————

<?php

error_reporting(E_ALL | E_STRICT);
date_default_timezone_set(‘America/Chicago’);

set_include_path(‘.’ . PATH_SEPARATOR . ‘./library’ . PATH_SEPARATOR . get_include_path());

include “Zend/Loader.php”;

//setup controller
Zend_Loader::loadClass(‘Zend_Controller_Front’);
$frontController = Zend_Controller_Front::getInstance();
$frontController->throwExceptions(true);
$frontController->setControllerDirectory(‘./app/controllers’);

//run
$frontController->dispatch();

————————————————————————————

Next we’ll set up a simple controller, and put the code that was in the server_script.php file inside a controller action. This is the IndexController.php file in the docroot/app/controllers directory:

————————————————————————————

<?php

class IndexController extends Zend_Controller_Action
{
function init()
{
$this->initView();
$this->view->baseUrl = $this->_request->getBaseUrl();
Zend_Loader::loadClass(‘Zend_Debug’);
}

function indexAction()
{
//echo “<p>in IndexController::indexAction()</p>”;
$this->view->title = “Zend Ajax 101”;
}

function getDataAction()
{
$this->_helper->viewRenderer->setNoRender();

$state = $_REQUEST[‘state’];

if ($state == ‘do’)
{
echo ‘<h1>exciting text retrieved from server!</h1>’;
}
else if ($state == ‘undo’)
{
echo ‘reset to boring …’;
}
else
{
echo ‘unknown state parameter passed to server!!’;
}
}
}

————————————————————————————

The only difference between our initial php script and the code in the getDataAction() method is the crucial first line:

$this->_helper->viewRenderer->setNoRender();

This tells Zend that this particular action method is not going to result in the re-rendering of the view.

Next, here’s our index.phtml, which is just slightly altered from the index.html of the non-Zend app, and is saved in the docroot/app/views/scripts/index directory:

————————————————————————————

<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.0 Transitional//EN”>
<html>
<head>
<title><?php echo $this->title ?></title>
<link rel=”stylesheet” href=”public/styles/boxy.css”>
<script type=”text/javascript” src=”public/scripts/prototype.js”></script>
<script type=”text/javascript” src=”public/scripts/ajax_funcs.js”></script>

</head>
<body>
<p>
<span id=’hover_span’ onmouseover=”callAjax(‘do’)” onmouseout=”callAjax(‘undo’)”> <b>Hover here to trigger Ajax call:</b> </span>
</p>

<span id=’hello’> boring pre-Ajax-call text … </span>

</body>
</html>

————————————————————————————

You’ll notice that we’ve factored the javascript functions out to a separate file, as I said we would do. Here is that file, docroot/public/scripts/ajax_funcs.js:

————————————————————————————

//handle the Ajax response …
function handleResponse(transport)
{
$(‘hello’).innerHTML = transport.responseText;
}

//creates a prototype Ajax object, sends a request, and registers the callback function ‘handleResponse’
function callAjax(s)
{

//remember to put a word separator between elements of the camelcase action name, per the ZF manual:
var myAjax = new Ajax.Request(‘index/get-data’,
{method: ‘get’, parameters: {state: s},
onComplete: handleResponse});
}

————————————————————————————

The stylesheet is the same as the previous app, so I won’t repeat it here. Just note that it is saved in docroot/public/styles.

Finally, there are some .htaccess files we’ll need, since we need to handle rewrite rules and exceptions. In the docroot, here’s what you need in your .htaccess:

————————————————————————————

RewriteEngine on
RewriteCond %{REQUEST_URI} !/public.*

RewriteRule .* index.php

php_flag magic_quotes_gpc off
php_flag register_globals off

————————————————————————————

And for the docroot/public directory:

————————————————————————————

RewriteEngine off

————————————————————————————

Okay, that’s it! If all has gone well, you should see the same behavior in the Zend Framework version as you did in the non-Zend one. Hopefully, this will give you a jumping off point for more advanced Ajax work with the Zend Framework.

Oracle modifications for Rob Allen’s Zend Framework Tutorial

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

Typesafe arrays for PHP

PHP is not a strongly typed language. This makes for very convenient and quick coding, but it can also lead to bugs and confusion. I noticed recently that, most likely due to my own paranoia (and Java background), I was checking the types of array elements every time I accessed them, just to make sure I was dealing with ints when I thought I was dealing with ints, etc. I decided that I’d rather just find a way to make some typesafe array classes to use … so that’s what I did.

There is an ArrayObject class in PHP5 which can be subclassed (not sure about PHP4, since I’m new to PHP and jumped straight into v5.x). If you have an ArrayObject $myArray, there are two ways in which you can append a new element:

$myArray->append($foo);

and

$myArray[] = $foo;
//or, e.g., $myArray[3] = $foo, if you want to alter the 4th element of the array

Affecting the behavior of the first one is pretty obvious: override the append method in the subclass, adding type checking. The second one is a little more obscure, and I had to search for a while to find out how to affect the [] operations. I was looking for C++-style operator overloading, where I could somehow define what ‘[]’ meant in my class. PHP doesn’t do this, except for, I believe, in a PECL extension. Finally I found a bulletin board post mentioning that the offsetSet and offsetGet methods of ArrayObject actually control what happens with the square bracket method of array access, so I was set to go.

Initially I wrote four subclasses of ArrayObject: IntArray, FloatArray, StringArray, and BooleanArray, each of which overrode the append and offsetSet methods from the parent class. However, I then did some refactoring, realizing that there was code that could be factored to a base class. So I defined an abstract base class, TypeSafeArray, which looks like this:

————————————————————————-

/**
* @desc Abstract class extending ArrayObject. Used as base class for
* type-specific array subclasses. Each subclass must implement
* the abstract method typeCheck to define its appropriate type
* checking behavior
*
* @package php_wrappers.arrays
* @author Bill Graham, 20070514
*
*/

abstract class TypeSafeArray extends ArrayObject
{
/**
* @desc Overriding ArrayObject::append to add typechecking
* @param mixed $val – a value to append to the array
*/
public function append($val)
{
$this->typeCheck($val);
parent::append($val);
}

/**
* @desc Overriding ArrayObject::offsetSet to add typechecking; this method is called when values are set using the []
* @param int $idx – the index of the array element to set
* @param mixed $val – the value to set
*/
public function offsetSet($idx, $val)
{
$this->typeCheck($val);
parent::offsetSet($idx, $val);
}

/**
* @desc abstract method must be implemented by subclasses to define type-checking behavior
* @param mixed $val – the value to be type-checked
*/
abstract protected function typeCheck($val);
}

————————————————————————-
By doing this, the type-specific arrays can simply extend the TypeSafeArray class, and provide an implementation of the abstract typeCheck method. As an example, here’s my IntArray class:

————————————————————————-

/**
* @desc Array class to specifically hold integer values
*
* @package php_wrappers.arrays
* @author Bill Graham, 20070510
*/

require_once(‘TypeSafeArray.php’);

class IntArray extends TypeSafeArray
{
/**
* @desc implementation of abstract method from TypeSafeArray to define type-checking behavior
* @param mixed $val – the value to be type-checked
*/
protected function typeCheck($val)
{
if (!is_int($val))
{
throw new Exception(“Non-integer value ‘” . $val . “‘ supplied to IntArray”);
}

return true;
}
}

————————————————————————-

Any other type-specific array classes will be similar … simply provide an implementation of the typeCheck method which does whatever is needed to insure that only arguments of the correct type are appended to the array. Below is a simple test script showing the classes in action:

————————————————————————-

require_once(‘IntArray.php’);

$set = new IntArray();

try
{
//legal operations. these should succeed …
$set->append(1);
$set[] = 2;
$set[2] = 3;

//illegal operations. each will throw an exception. uncomment any line you want to try …
$set->append(1.2);
//$set[] = “foo”;
//$set[2] = 3.2;
}
catch (Exception $e)
{
print “Exception encountered appending to ” . get_class($set) . “:\n”;
print “\t” . $e->getMessage() . “\n”;
}

foreach($set as $val)
{
print $val . “\n”;
}

————————————————————————-

Okay, there we go. I find these classes to be very useful. I may be in the minority, but I really do want type safety as an option for my PHP development.

Finally, I certainly don’t claim to be the only person, or the first person who has thought of this. I didn’t run across any others in my searches, although I did find a few similar things more focused on ‘collection’ classes. I’d love to know of other solutions or implementations, so email me or post a comment. Thanks!

-William

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