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.
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.
You can download the files with examples used in this tutorial below:
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") );
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') spain.code = 'ESP' spain.save end
Delete Database Records
Country.transaction do spain = Country.where(:code => 'ESP') 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') 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') 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:
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.
If you have any difficulties, you can post your questions to Ruby forum for CUBRID at http://forum.cubrid.org/.
This article will guide you through the steps you need to follow to build Windows x64 CUBRID PHP Driver with PHP 5.3 and VC9. If you are looki...