Rake Script to Load Seed Data or Update Existing Data

In the previous blog we descussed loading of seed data using rake/active record.
What was missing was examples.

So First a example of our "rake" file.

namespace :project do
desc 'parts_master_load'
task :parts_master_load => :environment do
PartsMaster.uoc :pn => '11-1111-11',
:description => 'Part description for part 11-111-11',
:application_code => ApplicationCode.foc('appcode1'),
:pn_type_code => PnTypeCode.foc('REF')
PartsMaster.uoc :pn => '22-2222-22',
:description => 'Part Description for part 22-222-22',
:application_code => ApplicationCode.foc('apcode2'),
:pn_type_code => PnTypeCode.foc('REF')
end
end

Rake has the advantage in that it runs with all of rails environment, and for us that means activerecord, so we can use our "models". Another advantage is if there is a error, if you have the rake trace option, you get exactly the line of "data" that caused the issue.


In the above example, there are "three" models that are directly mentioned.
One is the PartsMaster, Two is the ApplicationCode, and the Third is the PnTypeCode.

The "method" foc is one of my own, that means "find" or "create". Very handly for those "simple" tables that are used for "catagories".

So as a example of "foc" as used in the "application" code module.

class ApplicationCode < ActiveRecord::Base
set_table_name "APPLICATION_CODES"

defaults :application_code => "",
:cannot_modify => "F",
:converted => "F",

public


def self.foc(thevalue) #find_or_create
if thevalue.nil?
thevalue = "_"
end
if thevalue == ""
thevalue = "_"
end
therecord = find(:first,:conditions => "application_code = '#{thevalue}'")
if therecord
return therecord
end
therecord = self.new
therecord.application_code = thevalue
therecord.description = thevalue
therecord.save
return therecord
end

end

Here you can see the power of "foc". We actually use the "application" key to do a "lookup", on existing data. We can "also" cleanup any "issues" with the "incoming" data, to make it more "consistent". If the record "does" not exist, then the record is created. This also is good illustration of "default" values to make usage of the model "easier".

Now lets look at a "stippet" of the "update or create" or uoc method. For the main
table in the application, we need to update existing records, since the file has constraints, so we cannot "truncate" the table, and aways do a "create".

def self.uoc(options = {}) # update or create
thevalue = options[:pn] #fixme - Need to also look at manufactuer
record = find(:first,:conditions => "pn = '#{thevalue}'")
if record.nil?
record = new
end
record.attributes = options
record.save!

record
end

This illustrates a lovely "concept" in ruby of allowing us to use a hash, allowing us to take variable arguments in, and pass those variable arguments down. Code looks simple. I could code the same in "C" and it would be rather ugly.

In this code, the "new" will create a record, and establish defaults (The record has a couple hundred fields, so defaults is "not" small.) and then "update" it with what we pass. That allows the "create" rake script to be very small.

So there you have it. From now own, I'm going to use active record to do my data load and migration. It works very nice.

Integrating Legacy Applications with Rails

I'm current involved in a large software project which is taking a "legacy" applicaton to a "new" generation application. Of course for me, ruby is my "main" tool. So I'm doing the migration with ruby, and looking to use rails to "expand" the canned application with rails functionality.

So the issues are many, and countless.

1. Schema - While the database is oracle, the schema of the new system is "huge", 400+ tables is nothing to sneeze at. So first we need some good documentation of it. Does it come with the system? Nope, no schema documentation, so lets create some documentation for easy reference.

http://schemaspy.sourceforge.net/

Schemaspy will let us "create" a map of existing schema, so we can explore it, and get familiar with internal standards.

2. Walking around the data. Next we need to "explore" the data of the new system. For this in a oracle environment, Oracle Sql Developer is free, and lets us look at the data easily.

3. Write a "script" in ruby to create models automatically. Typing any normal of models, getting plurization right, and creating controllers is not for the faint of heart. I create a "gen" script that generates "missing" models, and controllers. (I use activescaffold plugin for handling the controller side, and models can be started without relationships if you wish. (If I do this a second time, I'll have the relationships also done by the script. Not much fun doing tables that have 20 relationships, where the keyname does not match the tablename.) (Now you know why you need schemaspy).

4. For importing your "existing data, assuming its not terabytes, I find that it works best in a "two" step process. In previous projects I would write a "dbi" ruby script taking one table to the "new" rails table. That works fine, but now I have destination tables that have "rich" relationships, writing dbi is no fun. ActiveRecord can save the day.
a. Step 1 - dbi process - Read existing table, and generate a rake script, calling active record "models"

Reference: http://railspikes.com/2008/2/1/loading-seed-data

b. Step 2 - Run your rake script

Reference:
http://www.slashdotdash.net/articles/2007/01/18/using-activerecord-outside-rails-part-ii

I find that this gives you the easy ability to handle the multi-tier adds
while keeping things simple.

When your creating your models, I find that often in "large" applicatons the tables from tradiitional applications can be "huge" number of fields. Of course the reality is that the majority of fields are always" default" values. To keep your create and update clean, use a "defalts" plugin. This will allow you to write you "create" or "create-and-update" call in your rake script to be "very" small, while all the flags and other values get set to the right values.

Reference:
http://agilewebdevelopment.com/plugins/activerecord_defaults

This makes my "rake" task incrediably small.

Finally consider in your models adding a method of either:

1. uoc - Update or Create - This will let you take a hash, and find a existing record and update it, or create a new one based on the "primary" key that is "application" specific. While my target application does have a numeric primary key, the "real" key is two other fields. using uoc allows me to do a find on the record and update it. (Yes I could "truncate" the table and always do creates, but remmeber its a complex app with a spiderweb of connections, updates work better)

2. foc - Find or create - This is great for those "simple" tables like unit of measure, or other tables that typically reasly are for "catorgories". It simply "finds" the right record or "create" a new one and returns it for the relationship your building.

This also makes the rake script re-usable. And the code in your modeles can also be used in "expansion" applications.

Next week I'll add a few examples.

Rails - The Menu Problem - Handling Large Scale Applications moving to Rails

It was interesting. I'm doing a large progject, and there are 500+ tables to deal with. This is my first "legacy" as it is project. And of course the "same" old tricks dont always work.

One of the problems, to check my models, of course I generate activescaffold controllers.

If your've read my previous post, my converts/imports usually do the following:

1. Move the table.
a. grab the schema
b. add missing fields
c. fix field names and table names for destination database
d. create the sequence number
e. create the table in the new schema
2. Create a model
3. Create a controller
4. Update the menu

This strategy has worked great. You get a reasobly native rails app,
and cleanup some of the database/schmema issues in the process.

New project is a bit different. Its all in oracle all ready. And its a "off-the-shelf" application.

So now I need to not "touch" the database schema, but man, I need a extra table, and
how to do menus etc when the functionality is so "rich".

So I decided to create a separate sqlite3 database just to do menus and functions.
(and maybe some other things along the way).

So first, need a do a migration (001_system_menu.rb). This will create our new
sqlite3 database, not touching our oracle database that is already working.
I did a trick to get this to work, I created a "empty" sqlite3 database file,
so the schema_version table would already exist, otherwise you get a "nice" error message saying you "cannot" create it.

Next wanted to get some data "into" the tables, so I created a second migration
(002_add_base_menus.rb) that creates some data so the menu system can be tweaked further.

So now how to "fill" up the tables in the operating "application"?
So in the models, I added the ability to "walk" the "controller" directory,
and any associated namespaces, and create entries. This will allow you then
to "play" them into the "menu" tree and "treak" the names, and the roles.

The "update_functions" method does a few things:
1. Verify "functions(controllers)" actually exist,
then enable or disable as appropriate
(If we delete we loose the custimization)
2. Find any new controllers and "add" them into the system

For now thats what we got, next will be to automatically generation of
menu (tabnav) partials for each level of the menu, and arrange for "hooks" in
the application to "know" what his menu is.

The system_function model has a very good example of "walking" directories.


#-----------------------001_system_menu.rb ------------------------
class SystemMenu < ActiveRecord::Migration

class SystemMenu < ActiveRecord::Base
ActiveRecord::Base.establish_connection(
:adapter => "sqlite3",
:database => "db/system.sqlite3"
)
acts_as_tree
has_many :SystemFunction
end

class SystemFunction < ActiveRecord::Base
ActiveRecord::Base.establish_connection(
:adapter => "sqlite3",
:database => "db/system.sqlite3"
)
belongs_to :SystemMenu
end

def self.up
create_table :system_functions do |t|
t.column :system_menu_id, :number
t.column :present, :boolean
t.column :enable, :boolean
t.column :name, :string, :limit =>32, :null => false
t.column :title, :string, :limit =>64, :null => false
t.column :controller, :string, :limit =>128
t.column :role, :string
t.column :created_at, :datetime
t.column :updated_at, :datetime
end
create_table :system_menus do |t|
t.column :name, :string, :limit =>32, :null => false
t.column :title, :string, :limit =>64, :null => false
t.column :role, :string, :limit =>32
t.column :enable, :boolean
t.column :parent_id, :number
t.column :created_at, :datetime
t.column :updated_at, :datetime
end

end

def self.down
drop_table :system_menus
drop_table :system_functions
end
end

class AddBaseMenus < ActiveRecord::Migration
class SystemMenu < ActiveRecord::Base
establish_connection :adapter => "sqlite3", :database => "db/system.sqlite3"
acts_as_tree
has_many :system_function
end

#-------------------- 002_add_base_menus.rb ------------------------
class SystemFunction < ActiveRecord::Base
establish_connection :adapter => "sqlite3", :database => "db/system.sqlite3"
belongs_to :system_menu
end

def self.up
homemenu = SystemMenu.create :name => "home", :title => "Home"
adminmenu = SystemMenu.create :name => "admin", :title => "Admin", :role => "Admin", :parent_id => homemenu.id
SystemFunction.create :system_menu_id => adminmenu.id, :name => "Menus", :title => "System Menus", :controller => "adminspace/system_menu", :role => "admin", :present => TRUE, :enable => TRUE
SystemFunction.create :system_menu_id => adminmenu.id, :name => "Functions", :title => "System Functions", :controller => "adminspace/system_function", :role => "admin", :present => TRUE, :enable => TRUE
end

def self.down
end
end

#----------------------- system_menu.rb ----------------------
class SystemMenu < ActiveRecord::Base
establish_connection :adapter => "sqlite3", :database => "db/system.sqlite3"
acts_as_tree
has_many :system_function

end

#---------------------- system_function.rb -------------------
class SystemFunction < ActiveRecord::Base
establish_connection :adapter => "sqlite3", :database => "db/system.sqlite3"
belongs_to :system_menu


public
def self.update_functions
app_directory = "./app/controllers"
verify_functions(app_directory)
system_function_process_dir(app_directory,"")
end


def self.verify_functions(thedir)
SystemFunction.find(:all).each do |record|
thepath = thedir + "/" + record.controller + "_controller.rb"
if File.exists?(thepath)
if record.present == FALSE
record.present = TRUE
record.save
end
else
if record.present == TRUE
record.present = FALSE
record.save
end
end
end
end

def self.system_function_process_entry(theparent,thedir, theentry)
if theentry == "."
return
end
if theentry == ".."
return
end
if theentry == "archive"
return
end
fullpath = thedir + "/" + theentry
if File.directory?(fullpath)
system_function_process_dir(fullpath,theentry)
return
end
if theentry.include? "_controller.rb"
thelen = theentry.index('_controller.rb')
thename = theentry[0,thelen]
if theparent == ""
mycontroller = thename
else
mycontroller = theparent + "/" + thename
end
myfunction = SystemFunction.find(:first, :conditions => "controller = '#{mycontroller}'")
if myfunction == nil # We dont exist
myfunction = SystemFunction.new
myfunction.name = thename
myfunction.system_menu_id = nil
myfunction.title = thename
myfunction.controller = mycontroller
myfunction.enable = FALSE
myfunction.present = TRUE
myfunction.role = ''
myfunction.save
return
end
end
end

def self.system_function_process_dir(thedir,theparent)
Dir.foreach(thedir){ |theentry| system_function_process_entry(theparent,thedir,theentry)}
end

end