接下來我們要做一些方法給 SQLite model
# rainbow/lib/rainbow/sqlite_model.rb (excerpt)
module Rulers
module Model
class SQLite
def initialize(data = nil)
@hash = data
end
def self.to_sql(val)
case val
when NilClass
'null'
when Numeric
val.to_s
when String
"'#{val}'"
else
raise "Can't change #{val.class} to SQL!"
end
end
def self.create(values)
values.delete "id"
keys = schema.keys - ["id"]
vals = keys.map do |key|
values[key] ? to_sql(values[key]) : "null"
end
DB.execute << SQL
INSERT INTO #{table} (#{keys.join ","})
VALUES (#{vals.join ","});
SQL
raw_vals = keys.map { |k| values[k] }
data = Hash[keys.zip raw_vals]
sql = "SELECT last_insert_rowid();"
data["id"] = DB.execute(sql)[0][0]
self.new data
end
def self.count
DB.execute(<<SQL)[0][0]
SELECT COUNT(*) FROM #{table}
SQL
end
end
end
end
現在我們在資料表中建立一個物件跟欄位,然後來數一下存了多少
SQLite 的主 key 叫 id,當欄位增加資料時, id 會自動增加
我們來改一下程式碼,新增新的欄位以及秀出有哪些欄位
# best_quotes/sqlite_test.rb
require "sqlite3"
require "rainbow/sqlite_model"
class MyTable < Rainbow::Model::SQLite; end
STDERR.puts MyTable.schema.inspect
# Create row
mt = MyTable.create "title" => "It happened!", "posted" => 1, "body" => "It did!"
mt = MyTable.create "title" => "I saw it!"
puts "Count: #{MyTable.count}"
> bundle exec ruby sqlite_test.rb
執行的時候,每次都會建立兩次
我們來增加 find 方法
# rainbow/lib/rainbow/sqlite_model.rb
module Rainbow
module Model
def self.find(id)
row = DB.execute <<SQL
select #{schema.keys.join ","} from #{table} where id = #{id};
SQL
data = Hash[schema.keys.zip row[0]]
self.new data
end
def [](name)
@hash[name.to_s]
end
def []=(name, value)
@hash[name.to_s] = value
end
end
end
改下列程式碼,並把所有欄位印出
# best_quotes/sqlite_test.rb
require "sqlite3"
require "rulers/sqlite_model"
class MyTable < Rainbow::Model::SQLite; end
STDERR.puts MyTable.schema.inspect
# Create row
mt = MyTable.create "title" => "I saw it again!"
puts "Count: #{MyTable.count}"
top_id = mt["id"].to_i
(1..top_id).each do |id|
mt_id = MyTable.find(id)
puts "Found title #{mt_id["title"]}."
end
跑這段程式碼,就會出現下列訊息
{"id"=>"INTEGER", "posted"=>"INTEGER", "title"=>"VARCHAR(30)", "body"=>"VARCHAR(32000)"}
Count: 6
Found title It happened!.
Found title I saw it!.
Found title It happened!.
Found title I saw it!.
Found title I saw it again!.
Found title I saw it again!.
Found title I saw it!.
這裡會根據 ID 創建出資料庫物件,並且把 title 印出,就像 ActiveRecord Lite 做的
因為要把資料從資料庫撈出來,但因為沒有自動轉換,我們必須手動 ID 轉換成字串
接下來我們要把物件存起來,我們來看一下這一段程式碼
我們都知道 Ruby 有 rescue
而 rescue false 是指 當 exception 發生時,他會直接回傳 false
ActiveRecord 裡有一個方法叫 save
當失敗的時候會呼叫一個 exception
# rainbow/lib/rainbow/sqlite_model.rb
module Rainbow
module Model
class SQLite
def save!
fields = @hash.map do |k, v|
"#{k}=#{self.class.to_sql(v)}" end.join ","
DB.execute << SQL
UPDATE #{self.class.table}
SET #{fields} WHERE id = #{@hash["id"]}
SQL
true
end
def save
self.save! rescue false
end
end
end
end
end
# best_quotes/sqlite_test.rb
require "sqlite3"
require "rainbow/sqlite_model"
class MyTable < Rainbow::Model::SQLite; end
# Create row
mt = MyTable.create "title" => "I saw it again!"
mt["title"] = "I really did!"
mt.save!
mt2 = MyTable.find mt["id"]
puts "Title: #{mt2["title"]}"
> bundle exec ruby sqlite_test.rb
當你看到 Title: I really did!
代表你已經成功修改了 title