ruby way之连接数据库

这边都只是个大概，具体的要自己去看文档了.

1 连接SQLite:

require 'sqlite'

db = SQLite::Database.new("library.db")

db.execute("select title,author from books") do |row|

  p row

end

db.close

2连接mysql:

require 'mysql'

m = Mysql.new("localhost","name","password","maillist")
r = m.query("SELECT * FROM people ORDER BY name")
r.each_hash do |f|
  print "#{f['name']} - #{f['email']}"
end

3连接到PostgreSQL：

require 'postgres'
conn = PGconn.connect("",5432, "", "", "testdb")

conn.exec("create table rtest ( number integer default 0 );")
conn.exec("insert into rtest values ( 99 )")
res = conn.query("select * from rtest")
# res id [["99"]]

4 连接到LDAP：

ruby中至少有3种LDAP库这里只介绍两种：

conn = LDAP::Conn.new("rsads02.foo.com")

conn.bind("CN=username,CN=Users,DC=foo,DC=com","password") do |bound|
  bound.search("DC=foo,DC=com", LDAP::LDAP_SCOPE_SUBTREE,
               "(&(name=*) (objectCategory=person))", ['name','ipPhone']) do |user|
    puts "#{user['name']} #{user['ipPhone']}"
  end
end

require 'net/ldap'

ldap = Net::LDAP.new :host => server_ip_address,

      :port => 389,

      :auth => {

                  :method => :simple,

                  :username => "cn=manager,dc=example,dc=com",

                 :password => "opensesame"

      }

filter = Net::LDAP::Filter.eq( "cn", "George*" )

treebase = "dc=example,dc=com"

ldap.search( :base => treebase, :filter => filter ) do |entry|

  puts "DN: #{entry.dn}"

  entry.each do |attribute, values|

    puts "   #{attribute}:"

    values.each do |value|

      puts "      --->#{value}"

    end

  end

end

p ldap.get_operation_result

5 连接到oracle

我们可以使用OCI8库，它可以支持oracle8之后的版本：

require 'oci8'

session = OCI8.new('user', 'password')

query = "SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL"

cursor = session.exec(query)

result = cursor.fetch         # Only one iteration in this case

cursor.close

session.logoff

下面是查询的例子：

session = OCI8.new("user","password")
query = "select * from people where name = :name"

# One way...
session.exec(query,'John Smith')

# Another...
cursor = session.parse(query)
cursor.exec('John Smith')

# And another...
cursor = session.parse(query)
cursor.bind_param(':name','John Smith')  # bind by name
cursor.exec

# And another.
cursor = session.parse(query)
cursor.bind_param(1,'John Smith')        # bind by position
cursor.exec

6 使用DBI包装器

理论上，DBI允许你以数据库无关的方式存取你的数据库.不管你的数据库是Oracle, MySQL, PostgreSQL 还是其他，访问的代码都是一样的。他有时是不能处理比较复杂的或则某个数据库独有的特性。

这里假设我们使用的是oracle数据库：

require "dbi"

db = DBI.connect("dbi:OCI8:mydb", "user", "password")
query = "select * from people"

stmt = db.prepare(query)
stmt.execute

while row = stmt.fetch do
  puts row.join(",")
end

stmt.finish
db.disconnect

7 ORMs

ruby中有两种流行的orm框架ActiveRecord和Og。
ActiveRecord是以Martin Fowler所定义的ActiveRecord设计模式所命名的.每一个数据库表，都是一个继承ActiveRecord::Base的一个类.

require 'active_record'

ActiveRecord::Base.establish_connection(:adapter => "oci8",
                                        :username => "username",
                                        :password => "password",
                                        :database => "mydb",
                                        :host => "myhost")

class SomeTable < ActiveRecord::Base
  set_table_name "test_table"
  set_primary_key "some_id"
end

SomeTable.find(:all).each do |rec|
  # process rec as needed...
end

item = SomeTable.new
item.id = 1001
item.some_column = "test"
item.save

og和ActiveRecord 的不同是，后者主要是针对数据库，而前者主要是以面向对象为核心.

当我们需要定义一个存储的类时，我们能够使用property 方法：

class SomeClass
  property :alpha, String
  property :beta, String
  property :gamma, String
end

连接数据库：

db = Og::Database.new(:destroy  => false,
                      :name => 'mydb',
                      :store  => :mysql,
                      :user     => 'hal9000',
                      :password => 'chandra')

每一个对象都有一个save方法来插入数据库数据：

obj = SomeClass.new
obj.alpha  = "Poole"
obj.beta   = "Whitehead"
obj.gamma  = "Kaminski"
obj.save

还有一些描述传统数据库中的对象关系的方法:

class Dog
  has_one :house
  belongs_to :owner
  has_many :fleas
end