Atif Khan’s Blog

Archive for March, 2007

A simple file based Ruby Database - KirbyBase

By Atif Khan ( March 27, 2007 at 4:50 pm) · Filed under Programming, Ruby, Software

Have you ever been in a situation where you wanted to write a simple application for internal use that should be portable and completely self contained (including the database)? I am pretty sure you have. I had to do this recently for one of the simple reporting applications and I didn’t really want to create a dependency on external database like MySQL or PostgreSQL. This application needed only 4 tables and the data in these table is never going to be large enough to warrant an extensive database system. I thought about putting the data in flat files and write the code to access it. But, then I found this really neat gem called KirbyBase that does exactly the same and little more.

KirbyBase has some neat features that mirror the traditional RDBMS while still sticking to it’s simple roots:

Pure Ruby code
Data is kept in simple delimited files, allowing the modification in any text editor
It’s not an in-memory DB. The data is actually stored on the disk
Allows various data types like String, Integer, Float, Boolean, Time, Date, DateTime, Memo, Blob, and YAML
Allows creating indexes on fields
Query results can be sorted
Allows to define one-many links between tables similar to joins in RDBMS
The columns can be defined with a formula, so the value will be calculated during insertion
Automatic lookup fields can be defined to lookup the related values from another table

To give a simple example on how all this works, let’s consider a scenario of Department and Employees. The simple requirements can be laid out as each employee has personal information and belongs to a department. Also, each department has a name and identifier and can contain multiple employees. Following is how the code going to look using KirbyBase. Please keep in mind that I have tried to write a reusable DAO, hence the code may look a little verbose. You can very well short circuit the whole thing and write very concise code.

#!/usr/bin/ruby -w

require 'rubygems'
require 'kirbybase'

class DAO
  attr_accessor(:employee_tbl, :department_tbl)

#Initialize the KirbyBase engine
  def initialize
    db = KirbyBase.new(:local, nil, nil, '.')

#Get/Create the Department table
    begin
      self.department_tbl = db.get_table(:department)
    rescue
      self.department_tbl = db.create_table(:department, :dept_name, {:DataType=>:String, :Key=>true, :Index=>1}, :employee, {:DataType=>:ResultSet, :Link_many=>[:recno, :employee, :dept_id]}) do |t|
        t.record_class = Department
      end
    end

#Get/Create the Employee table
    begin
      self.employee_tbl = db.get_table(:employee)
    rescue
      self.employee_tbl = db.create_table(:employee, :dept_id, {:DataType=>:Integer, :Key=>true, :Index=>1}, :name, {:DataType=>:String, :Key=>true, :Index=>1}) do |t|
        t.record_class = Employee
      end
    end
  end

#Get the department by name or if it does not exist, insert it 
  def get_department(name)
    dept = self.department_tbl.select_by_dept_name_index { |r| r.dept_name == name  }.first
    unless dept
      dept = Department.new do |x|
        x.dept_name = name
      end
      self.department_tbl.insert(dept)
      dept = self.department_tbl.select_by_dept_name_index { |r| r.dept_name == name  }.first
    end
    return dept
  end

#Insert a new employee in table
  def insert_employee(dept, employee)
    employee_rs = self.employee_tbl.select_by_dept_id_name_index { |r| r.dept_id == dept.recno and r.name == employee.name }.first

if employee_rs
     self.employee_tbl.update(employee) { |r| r.dept_id == dept.recno and r.name == employee.name }
    else
      employee_obj = Employee.new do |x|
        x.dept_id = dept.recno
        x.name = employee.name
      end
      self.employee_tbl.insert(employee_obj)
    end
    
  end

#Get all employees for a department
  def get_all_employees(dept)
    employee_rs = self.employee_tbl.select_by_dept_id_name_index { |r| r.dept_id == dept.recno }.sort(:name)
  end
  
end

# The Department class
class Department
  attr_accessor(:recno, :dept_name, :employee)

def Department.kb_create(recno, dept_name, employee)
    Department.new do |x|
      x.recno = recno
      x.dept_name = dept_name
      x.employee = employee
    end
  end

def initialize(&block)
    instance_eval(&block)
  end
end

# The Employee class
class Employee
  attr_accessor(:recno, :dept_id, :name)

def Employee.kb_create(recno, dept_id, name)
    Employee.new do |x|
      x.recno = recno
      x.dept_id = dept_id
      x.name = name
    end
  end

def initialize(&block)
    instance_eval(&block)
  end
end

#Test everything now
dao = DAO.new
dept = dao.get_department("IT")

emp1 = Employee.new { |x| 
  x.name = "John"
}
dao.insert_employee(dept, emp1)

emp2 = Employee.new { |x|
  x.name = "Doe"
}
dao.insert_employee(dept, emp2)

#Get all empoyees in IT department and print
#Let's use the get_all_employees function first
puts "Employee in #{dept.dept_name} department"
dao.get_all_employees(dept).each { |x|
  puts x.name
}

#Alternatively we could use the dept.employee to get all employees
#Refresh the dept object
dept = dao.get_department("IT")
puts "Employee in #{dept.dept_name} department"
dept.employee.each { |x|
  puts x.name
}

The output after execution will look like this:

Employee in IT department
Doe
John

Employee in IT department
John
Doe

This should also generate 2 files in your current directory, department.tbl and employee.tbl. Let’s look at content of these files now.

department.tbl

000001|000000|Department|recno:Integer|dept_name:String:Key->true:Index->1|employee:ResultSet:Link_many->recno=employee.dept_id
1|IT|kb_nil

employee.tbl

000002|000000|Employee|recno:Integer|dept_id:Integer:Key->true:Index->1|name:String:Key->true:Index->1
1|1|John
2|1|Doe

That’s all there to it. It is very powerful and easy to work with library.

Share and Enjoy:

Permalink Comments (4)

Syndicate
Categories
- Software
  - Linux
    - Ubuntu
  - Programming
    - Java
      - Tools
      - Web
    - OpenLaszlo
    - Ruby
    - Web Service
Archives

Search for:
Recent Posts
Blogroll
- Explaining REST in most basic terms

Archive for March, 2007

A simple file based Ruby Database - KirbyBase

Syndicate

Categories

Archives

Recent Posts

Blogroll