端端舝

? Ruby午犯奈正矛奈旦午及窣觓撮胍

Ruby午犯奈正矛奈旦午及窣觓撮胍
Integration Technology of Ruby and Database

繒�憷湮悝﹛悝胍ロ�價屜本件正奈
悝胍ロ��I燴旃噶窒嬡
嘉挌﹛悵
furuya@cc.kagoshima-u.ac.jp
K-Ruby, July 28, 2011 ? Tamotsu FURUYA, All rights reserved. 1


囀⺍
♂ 1. 犯奈正矛奈旦窣觓衙猁
♂ 2. MySQL田奶件犯奴件弘卞方月窣觓
♂ 3. ActiveRecord卞方月窣觓



1. 犯奈正矛奈旦窣觓衙猁
♂ Ruby午MySQL犯奈正矛奈旦午及窣觓
每犯奈正矛奈旦喲砓伊奶乩毛妏蚚六內ㄛ眻諉田奶件犯奴件弘毛妏
蚚仄化心月ㄝㄗSQL毛�扴允月斛猁互丐月ㄘ
物 ☆mysql2★＃MySQL蚚及田奶件犯奴件弘
Gemㄗhttps://github.com/brianmario/mysql2ㄘ

#MySQL及田奶件犯奴件弘毛奶件旦玄奈伙允月
$ gem install mysql2



1. 犯奈正矛奈旦窣觓衙猁
♂ Ruby午犯奈正矛奈旦午及窣觓
每幻午氏升及DBMS午窣觓褫夔
物 MySQLㄛPostgreSQLㄛOracleㄛMicrosoft SQL
ServerㄛDB2ㄛSQLite
物跪犯奈正矛奈旦API尺及田奶件犯奴件弘毛Gem匹枑鼎
每犯奈正矛奈旦喲砓伊奶乩ㄗActiveRecordㄘ
物 DB及綃中毛砩舑允月仇午卅仁戊奈犯奴件弘褫夔ㄝ



2. MySQL田奶件犯奴件弘卞方月窣觓
♂
窣觓允月犯奈正矛奈旦ㄗ氾奈皮伙ㄘ
每 ☆students★氾奈皮伙ㄗ軑戶mysql汁奈伙匹釬傖ㄘ
mysql> use test;
mysql> CREATE TABLE students (
-> id CHAR(11) NOT NULL,
-> name VARCHAR(40),
-> e_mail VARCHAR(80),
-> PRIMARY KEY (id)
->);



♂
皿伕弘仿丞瞰
# -*- coding: utf-8 -*-
require 'mysql2'

# 交奈扒 dbuser 匹 MySQL 及 test 犯奈正矛奈旦卞諉適允月ㄝ
client = Mysql2::Client.new(
:host => "localhost",
:database => "test",
:username => "dbuser",
:password => "pass"
)
# SQL毛灍俴仄磐彆本永玄毛腕月ㄝ
results = client.query("SELECT * FROM students")



♂
皿伕弘仿丞瞰
# 磐彆本永玄井日�伊戊奈玉及市仿丞及�毛奶氾伊奈正匹龰腕允月ㄝ
results.each do |row|
printf "%st%st%sn", row["id"],
row["name"], row["e_mail"]
end

# 犯奈正矛奈旦午諉適毛弁伕奈朮允月ㄝ
client.close

$ ruby dbaccess.rb
1234567891 繒�憷怮檔 taro@kago.jp
1234567892 灺藻豪赽 hana@kago.jp
1234567893 昹趌癒呏 taka@satsuma.jp



3. ActiveRecord卞方月窣觓
♂ ActiveRecord
每犯奈正矛奈旦喲砓伊奶乩及乒斥亙奈伙
每 Ruby on Rails毛�傖允月仿奶皮仿伉�及1勾
每 O/R穴永疋件弘ㄗObject / Relation Mappingㄘ仿奶皮仿伉



♂ O/R穴永疋件弘
每犯奈正矛奈旦及伊戊奈玉午Ruby及左皮斥尼弁玄毛穴永皿允月
每氾奈皮伙及白奴奈伙玉互﹜左皮斥尼弁玄及扽俶午卅曰ㄛ白奴奈
伙玉靡互公及引引失弁本扔午仄化隅膽今木月ㄝ



♂ 皿伕弘仿丞瞰ㄗActiveRecord�ㄘ
# -*- coding: utf-8 -*-
require 'active_record'
# 1.8炵
# require 'rubygems'
# require 'activerecord'

# 犯奈正矛奈旦午及諉適
ActiveRecord::Base.establish_connection(
:adapter => 'mysql2', # 妏蚚允月DB反MySQL犯奈正矛奈旦
:host => 'localhost',
:database => 'test',
:username => 'dbuser',
:password => 'pass'
)

ㄗ棒及旦仿奶玉尺適仁ㄝㄝㄝㄘ


♂ 皿伕弘仿丞瞰ㄗActiveRecord�ㄘ
# O/R穴永疋件弘
idㄛnameㄛe_mail脹及
idㄛnameㄛe_mail脹及
class Student < ActiveRecord::Base
扽俶丟末永玉反隅膽仄化卅中
扽俶丟末永玉反隅膽仄化卅中
end 及卞手憝歹日內ㄝㄝㄝ
及卞手憝歹日內ㄝㄝㄝ

students = Student.find(:all)
students.each do |res|
printf "%st%st%sn", res.id, res.name, res.e_mail
end

$ ruby dbaccess.rb
1234567891 繒�憷怮檔 taro@kago.jp
1234567892 灺藻豪赽 hana@kago.jp
1234567893 昹趌癒呏 taka@satsuma.jp



♂ Conversion over ConfigurationㄗCoCㄘ
每 ☆偞隅方曰手�廣★ㄗRails及殍悝ㄘ
每 XML白央奶伙卅升及戊件白奴弘反中日卅中
每允屯化反韜靡�廣卞𣶹丹仇午



♂ YAML白央奶伙及瞳蚚
每 YAML (YAML Ain't Markup Language)
每 �婖趙今木凶氾平旦玄白巧奈穴永玄
每 Rails匹反 config/database.yml 匹瞳蚚

YAML白央奶伙及瞰: database.ymlㄗMySQL蚚ㄘ
development_test:
adapter: mysql2
host: localhost
database: test
username: dbuser
password: pass
socket: /opt/local/var/run/mysql5/mysqld.sock
encoding: utf8
pool: 5



♂ 皿伕弘仿丞瞰ㄗActiveRecordㄛYAML瞳蚚ㄘ
# -*- coding:utf-8 -*-
require 'active_record'

# YAML毛瞳蚚仄凶犯奈正矛奈旦及諉適
ActiveRecord::Base.configurations
= YAML.load_file('database.yml')
ActiveRecord::Base.establish_connection('development_test')

end

students = Student.find(:all)
students.each do |res|
printf "%st%st%sn", res.id, res.name, res.e_mail
end



♂ find丟末永玉及妏蚚楊
瞰尹壬ㄝㄝㄝ
result = Student.find(2)

仇木反棒及SQL恅午肮脹匹丐月
SELECT * FROM students WHERE id = 2

↗皿仿奶穴伉平奈白奴奈伙玉反☆id★午中丹白奴奈伙玉靡匹卅仃木壬NG

賤𢜪習
set_table_name 'gakusei' # 氾奈皮伙靡反￤砩匹手OK
set_primary_key 's_code' # 皿仿奶穴伉平奈反￤砩匹手OK
end



♂ 手丹SQL反𤩸井卅仁化手謎中ˋ
今日卞沭璃毛勾仃化喲堤仄凶中�磁反ㄝㄝㄝ
result = Student.find(:all,
:conditions => [ 'name = ?', '繒�憷怮檔' ])

SELECT * FROM students WHERE name = '繒�憷怮檔'

find眕俋卞手棒及方丹卅丟末永玉手𨃨�今木化中月ㄝㄝㄝ
result = Student.find_by_name("繒�憷怮檔")

今日卞伊戊奈玉及陔�袚樓手ㄝㄝㄝ

student = Student.new(:id => '1234567894',
:name =>"捶囀棒檔", :e_mail => 'jiro@kago.jp')
student.save



♂ 石件玄卞SQL反𤩸井卅仁化手謎中ˋ

恚螝卅弁巨伉奈反ㄛ眻諉SQL匹𤩸仁仇午手褫夔ㄝㄝㄝ

result = Student.find_by_sql("SELECT * FROM students
WHERE name LIKE '%怮檔%' AND e_mail LIKE '%jp%'")

引午戶ㄝㄝㄝ
♂ �廣卞𣶹尹壬五切氏午O/R穴永疋件弘仄化仁木月ㄝ

♂
穴永疋件弘匹五木壬蚚砩今木凶丟末永玉匹磐彆本永玄毛腕日木月ㄝ
♂
磐彆本永玄井日扽俶丟末永玉匹白奴奈伙玉犯奈正毛腕日木月ㄝ
♂ 婡薯SQL毛𤩸井卅仁化𦤦戈

♂
犯奈正矛奈旦毛中勾匹手蝠𡥼匹五月
公及坻ㄝㄝㄝ
♂ DML分仃匹卅仁DDL手褫夔ㄗMigration弁仿旦ㄘ



4. 云引仃
♂ 氾奈皮伙及JOIN

studentsㄗ悝汜ㄘ subjectsㄗ褪醴ㄘ
id name e_mail id name teacher
1234567891 繒�憷怮檔 taro@kago.jp S01 ロ�褪悝⻌嬡刓泬怮檔
1234567892 灺藻豪赽 hana@kago.jp S02 巨件玉交奈扒灍� �躂珨檔
1234567893 昹趌癒呏 taka@kago.jp S03 𡛟蚚杅悝侂凝凅炰

1 1

coursesㄗ薩党ㄘ
student_id subject_id rating
1234567891 S02 A
1234567891 S03 B
嗣 1234567892 S01 C 嗣
1234567893 S02 B



4. 云引仃
♂
磐磁申亙奈

student_id name subject_id name rating
1234567891 繒�憷怮檔 S02 巨件玉交奈扒灍� A
1234567891 繒�憷怮檔 S03 𡛟蚚杅悝 B
1234567892 灺藻豪赽 S01 ロ�褪悝⻌嬡 C
1234567893 昹趌癒呏 S02 巨件玉交奈扒灍� B

mysql> SELECT student_id,﹛students.name,﹛subject_id,﹛
-> subjects.name, rating
-> FROM students JOIN (courses JOIN subjects
-> ON courses.subject_id = subjects.id )
-> ON students.id = courses.student_id;



4. 云引仃
♂ 氾奈皮伙及JOIN

studentsㄗ悝汜ㄘ subjectsㄗ褪醴ㄘ
id name e_mail id name teacher
1234567891 繒�憷怮檔 taro@kago.jp S01 ロ�褪悝⻌嬡刓泬怮檔
1234567892 灺藻豪赽 hana@kago.jp S02 巨件玉交奈扒灍� �躂珨檔
1234567893 昹趌癒呏 taka@kago.jp S03 𡛟蚚杅悝侂凝凅炰

has_many :course has_many :course
coursesㄗ薩党ㄘ
student_id subject_id rating
1234567891 S02 A
1234567891 S03 B
belongs_to :student 1234567892 S01 C belongs_to :subject
1234567893 S02 B



4. 云引仃
♂
氾奈皮伙嶲伉伊奈扑亦件毛砩舑仄凶隅膽

has_many :course
end

class Subject < ActiveRecord::Base
has_many :course
End

class Course < ActiveRecord::Base
belongs_to :student
belongs_to :subject
end



4. 云引仃
♂
磐磁磐彆及堤薯

courses = Course.find(:all, :include => [:student, :subject])
courses.each do |res|
printf "%st%st%st%st%sn",
res.student_id, res.student.name,
res.subject_id, res.subject.name, res.rating
end

$ ruby dbaccess.rb
1234567891 繒�憷怮檔 S02 巨件玉交奈扒灍� A
1234567891 繒�憷怮檔 S03 𡛟蚚杅悝 B
1234567892 灺藻豪赽 S01 ロ�褪悝⻌嬡 C
1234567893 昹趌癒呏 S02 巨件玉交奈扒灍� B


端端舝

Integration Technology of Ruby and DB.

Recommended

More Related Content

What's hot (8)

Similar to Integration Technology of Ruby and DB. (20)

Integration Technology of Ruby and DB.