rails 数据库操作

数据库元被影射成对象 
(object-relational mapping (ORM)层) 

table<=>class
row<=>object
column<=>object attribute

class和table的命名对应关系实例： 

Order<=>orders
TaxAgency<=>tax_agencies
Person<=>people

#复数形式关闭方法config/environment.rb：
ActiveRecord::Base.pluralize_table_names = false
#自定义表格名称方法：
class Sheep < ActiveRecord::Base
set_table_name "sheep"
end

Ruyb数据类型和SQL数据类型对应关系表： 

int,integer<=>Fixnum
float,double,decimal,numeric<=>Float
interval,date<=>Date
datetime,time<=>Time
char,varchar,string,clob,blob,text<=>String
boolean<=>see text...

访问属性（数据库列）： 

account[:balance] #=> 返回当前值
account[:balance] = 0.0 #=> 指定数值

#修正数据库列的取值范围的方法：
class Account < ActiveRecord::Base
def balance=(value)
   raise BalanceTooLow if value < MINIMUM_LEVEL
   self[:balance] = value
end
end

访问属性（数据库列）更方便的方法： 
account.balance #=> 返回当前值 
account.balance = 0.0 #=> 指定数值 

以上方式得到的数据库数据将是ruby按自身的数据类型格式化好的，如果要得到原始数据，可用以下形式代码： 
account.balance_before_type_cast #=> "123.4", 字符串 
account.release_date_before_type_cast #=> "20050301" 

是非属性 
在ruby中只有false或nil才被判断为false 
通常用以下代码判断: 

user = Users.find_by_name("Dave")
if user.superuser?
grant_privileges
end

superuser?将以下结果判断为false: 
1.数字0 
2.字符"0", "f", "false", 或""(空字符） 
3.nil 
4.常量false 

自定义判断原则的方法： 

class User < ActiveRecord::Base
def superuser?
   self.superuser == 'J'
end
# . . .
end

数据库主键(Primary Keys) 
Ruby on Rails默认以id为主键 

自定义主键的方法: 
class BadBook < ActiveRecord::Base 
set_primary_key "isbn" 
end 

数据创建,读取,更新和删除(CRUD:Create, Read, Update, Delete) 

创建新数据 

实例: 

an_order = Order.new
an_order.name ="Dave Thomas"
an_order.email = "[e-mail]"
an_order.address = "123 Main St"
an_order.pay_type = "check"
an_order.save #在save()之前所有数据只存在内存中

用以下方式可以减少产生一个an_order变量: 

Order.new do |o|
o.name = "Dave Thomas"
# . . .
o.save
end

当数据来自HTML表单时,可以考虑用以下方式: 

an_order = Order.new(
:name =>"Dave Thomas",
:email =>"[e-mail]",
:address => "123 Main St",
:pay_type =>"check")
an_order.save

使用create()代换new()可直接保存到数据库,省去an_order.save: 

an_order = Order.create(
:name => "Dave Thomas",
:email =>"[e-mail]",
:address =>"123 Main St",
:pay_type => "check")

可以使用hash同时保存多组数据: 

orders = Order.create(
   [ { :name =>"Dave Thomas",
     :email => "[e-mail]",
     :address =>"123 Main St",
     :pay_type =>"check"
    },
    { :name =>"Andy Hunt",
     :email =>"[e-mail]",
     :address =>"456 Gentle Drive",
     :pay_type => "po"
    } ] )

new()或create()也可以直接接参数: 
order = Order.create(params) 

读取数据 

an_order = Order.find(27) # 直接找出id = 27的数据
# 从一个表单读取product id列表,然后计算这些商品的总价:
product_list = params[:product_ids]
total = 0.0
Product.find(product_list).each {|prd| total += prd.total}

带条件的读取:
pos = Order.find(:all,
:conditions => "name = 'dave' and pay_type = 'po'")

不安全的表单参数传递读取数据库: 

name = params[:name]
# 此方法有被SQL注入方式入侵的风险!!!
pos = Order.find(:all,
:conditions =>"name = '#{name}' and pay_type = 'po'")
#注意上面单双引号的使用及变量的传递方法

更安全的方法: 

name = params[:name]
pos = Order.find(:all,
:conditions => ["name = ? and pay_type = 'po'", name])

你也可以这样: 

name = params[:name]
pay_type = params[:pay_type]
pos = Order.find(:all,
:conditions => ["name = :name and pay_type = :pay_type",
{:pay_type => pay_type, :name => name}])

终极简化版: 

pos = Order.find(:all,
:conditions => ["name = :name and pay_type = :pay_type", params])

排序和查找第3(?)至13(?)列的方法: 

orders = Order.find(:all,
:conditions =>"name = 'Dave'",
:order =>"pay_type, shipped_at DESC",
:limit => 10
:offset => 2)

联合数据表的查找方法(一般用不上): 

LineItem.find(:all,
:conditions => "pr.title = 'Programming Ruby'",
:joins =>"as li inner join products as pr on li.product_id = pr.id")

查找有序一列的方法: 

order = Order.find( :first,
:conditions =>"name = 'Dave Thomas'",
:order => "id DESC")

直接使用sql语句的查询方法: 

items = LineItem.find_by_sql("select *, quantity*unit_price as total_price,products.title as title from line_items, products where line_items.product_id = products.id ")
li = items[0]
puts "#{li.title}: #{li.quantity}x#{li.unit_price} => #{li.total_price}"
#你可以使用"as".

在这里你也可以传递参数: 

Order.find_by_sql(["select * from orders where amount > ?",
params[:amount]])

计算行数 

c1 = Order.count
c2 = Order.count(["name = ?", "Dave Thomas"])
c3 = LineItem.count_by_sql("select count(*) from line_items, orders   where line_items.order_id = orders.id and orders.name = 'Dave Thomas' ")
puts "Dave在#{c2}个定单里一共定了#{c3} 件商品 (目前定单总数:#{c1})"

动态查询 

order = Order.find_by_name("Dave Thomas")#只查一列
orders = Order.find_all_by_name("Dave Thomas")
order = Order.find_all_by_email(params['email'])

可同时查多个条件,如: 

user = User.find_by_name_and_password(name, pw)

重载数据库 

stock = Market.find_by_ticker("RUBY")
loop do
puts "Price = #{stock.price}"
sleep 60
stock.reload
end

更新数据 
使用save() 

order = Order.find(123)
order.name = "Fred"
order.save

orders = Order.find_by_sql("select id, name, pay_type from orders where id=123")
first = orders[0]
first.name ="Wilma"
first.save

使用update_attribute() 

order = Order.find(123)
order.update_attribute(:name,"Barney")
order = Order.find(321)
order.update_attributes(:name => "Barney",
:email =>"[e-mail]")

使用更快捷的update() 

order = Order.update(12, :name => "Barney", :email => "[e-mail]")

使用update_all() 

result = Product.update_all("price = 1.1*price", "title like '%ruby%'")

save()和save!()
save()
if order.save
# 成功
else
# 保存失败则...
end

save!()
begin
order.save!
rescue RecordInvalid => error
# 保存失败RecordInvalid exception
end

数据锁(防止数据保存撞车) 
加段:lock_version int default 0, 


删除数据 

delete()删除
Order.delete(123)
User.delete([2,3,4,5])
Product.delete_all(["price > ?", @expensive_price])

destroy()冻结(在model层面) 

order = Order.find_by_name("Dave")
order.destroy
# ... order将被冻结