Using Ruby Active Record with CUBRID

This tutorial will explain how to use Ruby Active Record Adapter to work with CUBRID Database. Active Record is compatible with most database systems and has same syntax. So if you had prior experience working with Active Records with other databases, it will be easy for you to get started with programming for CUBRID.

Required Software

To successfully replicate the following examples, it is necessary to install the following prerequisites:

Ruby 1.8.7
CUBRID gem
CUBRID ActiverRecord gem (with all its dependencies)

To download the above items and learn more about Ruby Programming, visit Ruby Programming with CUBRID.
Detailed explanations for Ruby functions can be found in Ruby API for CUBRID.

Downloads

You can download the files with examples used in this tutorial below:

tutorial_ruby.zip

Examples

Scenario

Let's start by creating a simple scenario. Let's suppose we have a database with two tables with countries and cities. Open Cubrid Manager application and create the tables by executing the following SQL script:

CREATE TABLE "countries"(
	"id" integer AUTO_INCREMENT,
	"code" character varying(3) NOT NULL UNIQUE,
	"name" character varying(40) NOT NULL UNIQUE,
	"record_date" datetime DEFAULT sysdatetime NOT NULL,
	CONSTRAINT pk_countries_id PRIMARY KEY("id")
);
CREATE TABLE "cities"(
	"id" integer AUTO_INCREMENT NOT NULL UNIQUE,
	"name" character varying(40) NOT NULL,
	"country_id" integer NOT NULL,
	"record_date" datetime DEFAULT sysdatetime NOT NULL,
	FOREIGN KEY ("country_id") REFERENCES "countries"("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
	CONSTRAINT pk_cities_id PRIMARY KEY("id")
);

Load Libraries

Now that the database is ready, let's start by creating a new ruby file named tutorial.rb and add basic configuration.

require 'rubygems'
require 'active_record'
require 'pp'

Establish Database Connection

Now we will define the connection parameters to the database:

ActiveRecord::Base.establish_connection(
 :adapter => "cubrid",
 :host => "localhost",
 :database => "demodb" ,
 :user => "dba"
)

Insert Objects into a Database

Before starting to operate on tables we must declare the two tables as mappings in our database as Active Record classes:

class Country < ActiveRecord::Base
end

class City < ActiveRecord::Base
end

Country.create(:code => 'ROU', :name => 'Romania')
Country.create(:code => 'HUN', :name => 'Hungary')
Country.create(:code => 'DEU', :name => 'Germany')
Country.create(:code => 'FRA', :name => 'France')
Country.create(:code => 'ITA', :name => 'Italy', :record_date => Time.now)
Country.create(:code => 'SPN', :name => 'Spain')

Select Records from a Database

romania = Country.find(1)
pp(romania)

romania = Country.where(:code => 'ROU')
pp(romania)

Country.find_each do |country|
 pp(country)
end

Update Database Records

Let's change the Spain code from 'SPN' to to ESP.

Country.transaction do
 spain = Country.where(:code => 'SPN')[0]
 spain.code = 'ESP'
 spain.save
end

Delete Database Records

Country.transaction do
 spain = Country.where(:code => 'ESP')[0]
 spain.destroy
end

Working with Associations

One method to add cities to a country would be to select the Country and assign the country code to a new City object:

romania = Country.where(:code => 'ROU')[0]
City.create(:country_id => romania.id, :name => 'Bucharest');

A more elegant solution would be to let ActiveRecord know about this relationship and declare it in the Country class. Let's modify our two initial mappings:

class Country < ActiveRecord::Base  
 has_many :cities, :dependent => :destroy
end

class City < ActiveRecord::Base
end

With the above update, we declared that one country can have many cities. Now, it will be very easy to add new city to a country:

italy = Country.where(:code => 'ITA')[0]
italy.cities.create(:name => 'Milano');
italy.cities.create(:name => 'Napoli');

pp (romania.cities)
pp (italy.cities)

This would be very helpful because when we access cities we get all the cities recorded for the referenced country. Another use is that when we delete the country, all its cities are removed. All is done in one statement:

romania.destroy

ActiveRecord also supports other relationship including one-to-one, many-to-many etc.

Working with metadata

ActiveRecord allows programmers to interact with their database in portable manner. This ensures the code will work on different database backends without modifying the code.

Defining a Database Structure

A new table can be defined programatically using ActiveRecord::Schema.define. Let's create two tables: books and authors with a one-to-many relation between authors and books:

ActiveRecord::Schema.define do
 create_table :books do |table|
  table.column :title, :string, :null => false
  table.column :price, :float, :null => false
  table.column :author_id, :integer, :null => false
 end
 
 create_table :authors do |table|
  table.column :name, :string, :null => false
  table.column :address, :string
  table.column :phone, :string
 end

 add_index :books, :author_id
end

With Cubrid, supported column types are: :string, :text, :integer, :float, :decimal, :datetime, :timestamp., :time, :boolean, :bit, :monetary, :smallint, :bigint, :char. At the moment, :binary is not supported.

Managing Table Columns

We can add, update, delete columns to existing tables. This can be accomplished using features from ActiveRecord::Migration.

ActiveRecord::Schema.define do
 create_table :todos do |table|
  table.column :title, :string
  table.column :description, :string
 end
 
 change_column :todos, :description, :string, :null => false
 add_column :todos, :created, :datetime, :default => Time.now
 rename_column :todos, :created, :record_date
 remove_column :todos, :record_date

end

Dumping Database Schema

We can use ActiveRecord::SchemaDumper.dump to dump information for the currently used schema. This is done into a platform independent format that is understood by Ruby ActiveRecord.

Note: If you are using custom column types database specific (:bigint, :bit, :monetary), this may not work.

Obtaining Server Capabilities

Database information can be extracted from the current connection as in the example below:

puts "Maximum column length        : " + ActiveRecord::Base.connection.column_name_length.to_s
puts "SQL statement maximum length : " + ActiveRecord::Base.connection.sql_query_length.to_s
puts "Quoting : '''test'''         : " + ActiveRecord::Base.connection.quote("'''test'''")

Creating a Schema

Due to the way Cubrid is functioning, you cannot programatically create a schema as in the following example:

ActiveRecord::Schema.define do
 create_database('not_supported')
end

You will have to manually create a schema using tools such as CubridManager or csql.

Getting Help

If you have any difficulties, you can post your questions to Ruby forum for CUBRID at http://forum.cubrid.org/.