SentiQL

A minimalistic Ruby wrapper for MySQL. I love using SQL for data selection, except for basic selects (e.g. finding something by id), I love using OM interface to create, update and delete records in the database and I love when lib is not “overcoded”. When you combine all this love, you get SentiQL.

Why SentiQL

I wanted to have a lightweight OM for my Sinatras’ apps, that wouldn’t add a thick abstraction layer and would allow me to be flexible.

SentiQL Philosophy

1. “Make everything as simple as possible, but not simpler” - Albert Einstein.

2. Rule of Clarity. Clarity is better than cleverness. (Unix Philosophy)

3. Rule of Simplicity: Design for simplicity; add complexity only where you must. (Unix Philosophy)

Notice

This is REALY a work in progress, I wrote this lib during one evening after reading “Metaprogramming Ruby: Program Like a Ruby Pros”. It is less then 140 LOCs, so even if it is missing one or other thing, just open the sentiql.rb file and happy hacking.

Instalation

gem install sentiql

Features

• OM to create, update and find by attribute

• Prepared SQL statements, which automatically escapes input

• Filters: before_save, before_create, after_save and after_create

• Validations expressed in boolean logic

• Automatic timestamps

Configuration

DB = Mysql2::Client.new( 
      :host=>'localhost', 
      :username=>'username', 
      :password=>'password', 
      :database=>'db'
    )
DB.query_options.merge!(:symbolize_keys=>true)
SentiQL::Base.connection = DB

Usage examples

1. Create

   u = User.create :name=>'tdurden', :password=>'project_mayhem'
   

2. Update

   # single attribute update
   u = User.find_by :name=>'tdurden'
   u.full_name = 'Tyler Durden'
   u.save
   
   # multiple update
   put '/users' do
     @user = User.find_by :id=>params[:user][:id]
     @user.updated_attributes params[:user]
     @user.save
   end
   

3. Select

   # find by attribute
   u = User.find_by :name=>'tdurden'
   
   # select with SQL
   
   # "?" is replaced with escaped adequate value from params array
   sql = 'SELECT * FROM users WHERE name=?'
   u = User.first sql, ['tdurden'] # -> [ {:name=>'tdurden', :password=>'project_mayhem'} ]
   
   users = User.all 'SELECT * FROM users' # => Mysql2::Result
   users.map {|u| u[:name] } #=> ['username1', 'username2']
   

Model

1. Basic

   class User < SentiQL::Base
   
     set_table :users
     set_schema :name, :full_name, :email, :crypted_password, :salt, :created_at, :updated_at
   
   end
   

2. Using filters

   before_save :create_salt, :encrypt_password, 
   after_create :download_profile_image

Plus. I think it is wrong to put a thick abstraction layer on one of the biggest web application’s performance bottlenecks.

protected

def create_salt
  self[:salt] = 'salt'
end

def encrypt_password
  self[:crypted_password] = "#{self[:salt]}#{self[:password]}"
end

def download_profile_image
  # ...
end

1. Validations

   ...
   
   def validate
     name_not_blank? && name_not_longer_then?(20)
   end
   
   def name_not_blank?
     if self[:name] == "" || self[:name].nil?
       @errors << "Name can't be blank"
       return false
     end
     return true
   end
   
   def name_not_longer_then? max
     if self[:name].length > max
       @errors << "Name can't be longer then #{max} characters"
       return false
     end
     return true
   end
   

Licence

I have no idea about this sort of things.