Ruby on Rails makes nice with PearDB

Another exciting day in the life of a PHP -- > ROR programmer..

Last week I wrote about how Ruby could access a PHP session and how php site structure could fit inside a Rails structure. This week, I share my findings on how to get ActiveRecord to work with our existing PearDB sequence tables. These are my random, scattered meanderings on a solution..

If you haven't used Pear's DB class before, it creates a table with the [tablename]_seq for each table and stores a single row with a single column "id" with the value of the last ID used. Example of use. This was designed so that your code could easily port to other databases that didn’t have autoincrement like MySQL. Whether this is a good practice is not up for debate, I have what I have :)

I went through a few different ideas, but knew there was probably a super elegant solution that I was missing. Also I was hoping I could make one change to ActiveRecord or something that I could remove later once I coverted all my tables to use autoincrement. I posted some ideas to the Chicago Ruby User Group mailing list and to the ROR mailing list.

Here are my iterations...

To start, I made a method "get_next_id" that just returned a number. When I was able to get this number in my id, then I was golden and then could tackle getting that method to return the right id from the table.

At first I thought I could just override the id set method, returning my next sequence id...didn't work. Erik Bryn, of Chicago RB list helped along with some ideas. We tried overriding the save method, that worked, but then I used the variable @new_record to check if this was a new record (not yet saved to DB) and if so, set the id. Then, Jim Weirich said, why not use before_create ?? ahh, that works. Nice.

Happy with that, I tackled (someone I know says that to me a lot, I want to say I don’t play football) the get_next_id method. First I had to figure out how to write sql without using the nifty model. I didn’t want to make models for all my seq tables.

I ended up using the attribute "sequence_name" although the docs say:

Sets the name of the sequence to use when generating ids to the given value, or (if the value is nil or false) to the value returned by the given block. This is required for Oracle and is useful for any database which relies on sequences for primary key generation.
If a sequence name is not explicitly set when using Oracle or Firebird, it will default to the commonly used pattern of: #{table_name}_seq
If a sequence name is not explicitly set when using PostgreSQL, it will discover the sequence corresponding to your primary key for you.

Link

I’m using MySQL, so I guess it probably doesn’t mean the exact same as if I were using Oracle/PostgreSQL, but I figure the attribute “sequence_name” is already there, why not use it?

So I had these methods in my Contact model

   def get_next_id
     seq_table = "contact_seq"
     ActiveRecord::Base.connection.execute("update #{seq_table} SET id = id+1") 
     ActiveRecord::Base.connection.select_value("SELECT * FROM #{seq_table}")
  end
  def before_create
       id = get_next_id
  end
 

The last line returns a single value of the last id in the table. Not too bad. ok, how do I get the name of the sequence automagically? I tried self.sequence_name but that didn't work, then asking on the ROR mailing list, I was told that it was a class variable. Umm ok, so I'm messing around doing class.sequence_name and that didn't work. So I ask for more clarification on the ROR list and one guy says self.class.sequence_name.

Actually I lie. I was trying to get the table name and append seq on it and then check to see if that table actually existed (some tables we have use auto increment still) blah blah but someone said something in response to my how to get the table name that it also works for "sequence_name". And I thought to my self, "Self, this requires you to explicitly state it in the model, thats not a bad idea. Then I coud see what tables had it and not" So this is what I had:

   def get_next_id
     seq_table = self.class.sequence_name
     ActiveRecord::Base.connection.execute("update #{seq_table} SET id = id+1") 
     ActiveRecord::Base.connection.select_value("SELECT * FROM #{seq_table}")
  end
  def before_create
       id = get_next_id
  end

Ok awesome. Now to get this to apply to every model.
Added this to environment.rb


# modify the base ActiveRecord to do pear db sequences
require 'PHP_Pear_Sequence'
class ActiveRecord::Base
    include PHP_Pear_Sequence
end


The threw this code into its own file in /lib.
PHP_Pear_Sequence.rb

   def self.append_features(base_class)
       base_class.before_create do |model|
         seq_table = model.class.sequence_name
         if !seq_table.nil?
           ActiveRecord::Base.connection.execute("update #{seq_table} SET id = id+1")
           model.id = ActiveRecord::Base.connection.select_value("SELECT * FROM #{seq_table}")
         end
       end
   end

end 

Now it will be applied to every model class.

Nifty, huh? Now I can continue on my merry way with the Pear DB sequence tables until such a time I convert all the PHP code to RoR and then I can change to autoincrement and remove this addition to the ActiveRecord. My RoR code will be none the wiser.