데이터베이스 열기
require 'sequel'
db = Sequel.sqlite('my_blog.db')
db = Sequel.connect('postgres://user:password@localhost/my_db')
db = Sequel.postgres('my_db', user: 'user', password: 'password', host: 'localhost')
db = Sequel.ado('mydb')
SQLite 메모리 데이터베이스 열기
파일명 인자를 생략하면 SQLite 어댑터가 메모리 내에 새 SQLite 데이터베이스를 생성합니다.
db = Sequel.sqlite
SQL 문 로깅
require 'logger'
db = Sequel.sqlite('', loggers: [Logger.new($stdout)])
# 또는
db.loggers << Logger.new($stdout)
원시 SQL 사용
db.run "CREATE TABLE users (name VARCHAR(255) NOT NULL, age INT(3) NOT NULL)"
ages = db["SELECT age FROM users WHERE name = ?", user_name]
ages.map(:age)
db.fetch("SELECT name FROM users") do |row|
p row[:name]
end
데이터셋 생성
items = db[:items]
items = db.from(:items)
대부분의 데이터셋 메서드는 체이닝 가능
managers = db[:managers].where(salary: 5000..10000).order(:name, :department)
행 삽입
items.insert(name: 'Sharon', grade: 50)
행 조회
items.each { |row| p row }
items.all # => [{...}, {...}, ...]
items.first # => {...}
행 갱신/삭제
items.where(active: false).delete
items.where('price < ?', 100).update(active: true)
데이터셋은 Enumerable
items.map { |row| row[:name] }
items.map(:name)
items.inject(0) { |sum, row| sum + row[:value] }
items.sum(:value)
필터링
동등 비교
items.where(name: 'abc')
items.where('name = ?', 'abc')
부등 비교
items.where { value > 100 }
items.exclude { value <= 100 }
포함 여부
items.where(value: 50..100)
items.where { (value >= 50) & (value <= 100) }
items.where('value IN ?', [50, 75, 100])
items.where(value: [50, 75, 100])
items.where(id: other_ds.select(:other_id))
스칼라 값으로서의 서브셀렉트
items.where('price > (SELECT avg(price) + 100 FROM table)')
items.where { price > items.select(avg(price) + 100) }
LIKE/정규식
db[:items].where(:name.like('AL%'))
db[:items].where(name: /^AL/)
AND/OR/NOT
db[:items].where { (x > 5) & (y > 10) }.sql
# SELECT * FROM items WHERE ((x > 5) AND (y > 10))
db[:items].where({ x: 1, y: 2 }.sql_or & ~{ z: 3 }).sql
# SELECT * FROM items WHERE (((x = 1) OR (y = 2)) AND (z != 3))
수학 연산자
db[:items].where((:x + :y) > :z).sql
# SELECT * FROM items WHERE ((x + y) > z)
db[:items].where { price - 100 < avg(price) }.sql
# SELECT * FROM items WHERE ((price - 100) < avg(price))
정렬
items.order(:kind)
items.reverse_order(:kind)
items.order(:kind.desc, :name)
제한/오프셋
items.limit(30) # LIMIT 30
items.limit(30, 10) # LIMIT 30 OFFSET 10
조인
db[:items].left_outer_join(:categories, id: :category_id).sql
# SELECT * FROM items LEFT OUTER JOIN categories ON categories.id = items.category_id
db[:items].join(:categories, id: :category_id).join(:groups, id: :items__group_id)
# SELECT * FROM items INNER JOIN categories ON categories.id = items.category_id INNER JOIN groups ON groups.id = items.group_id
집계 함수 메서드
items.count
items.max(:price)
items.min(:price)
items.avg(:price)
items.sum(:stock)
items.group_and_count(:category)
items.group(:category).select(:category, :AVG.sql_function(:price))
SQL 함수/리터럴
items.update(updated_at: :NOW.sql_function)
items.update(updated_at: 'NOW()'.lit)
items.update(updated_at: "DateValue('1/1/2001')".lit)
items.update(updated_at: :DateValue.sql_function('1/1/2001'))
스키마 조작
db.create_table :items do
primary_key :id
String :name, unique: true, null: false
TrueClass :active, default: true
foreign_key :category_id, :categories
DateTime :created_at
index :created_at
end
db.drop_table :items
db.create_table :test do
String :zipcode
enum :system, elements: ['mac', 'linux', 'windows']
end
별칭
db[:items].select(:name.as(:item_name))
db[:items].select(:name___item_name)
db[:items___items_table].select(:items_table__name___item_name)
# SELECT items_table.name AS item_name FROM items AS items_table
트랜잭션
db.transaction do
items.insert(first_name: 'Inigo', last_name: 'Montoya')
items.insert(first_name: 'Farm', last_name: 'Boy')
end # 둘 다 삽입되거나 둘 다 삽입되지 않음
Database#transaction은 재진입 가능합니다.
db.transaction do # BEGIN은 여기서만 발행됨
db.transaction do
items << { first_name: 'Inigo', last_name: 'Montoya' }
end
end # COMMIT은 여기서만 발행됨
오류가 발생하면 트랜잭션이 중단됩니다.
db.transaction do
raise "some error occurred"
end # ROLLBACK이 발행되고 오류가 다시 발생함
Sequel::Rollback을 발생시켜 트랜잭션을 중단할 수도 있습니다.
db.transaction do
raise(Sequel::Rollback) if something_bad_happened
end # ROLLBACK이 발행되고 오류는 발생하지 않음
데이터베이스가 지원하면 세이브포인트를 사용할 수 있습니다.
db.transaction do
items << { first_name: 'Farm', last_name: 'Boy' } # 삽입됨
db.transaction(savepoint: true) do # 이 세이브포인트는 롤백됨
items << { first_name: 'Inigo', last_name: 'Montoya' } # 삽입되지 않음
raise(Sequel::Rollback) if something_bad_happened
end
items << { first_name: 'Prince', last_name: 'Humperdink' } # 삽입됨
end
기타
items.sql # "SELECT * FROM items"
items.delete_sql # "DELETE FROM items"
items.where(name: 'sequel').exists # "EXISTS ( SELECT * FROM items WHERE name = 'sequel' )"
items.columns # => 결과 집합의 컬럼 배열, SELECT 수행
db.schema(:items) # => [[:id, { type: :integer, ... }], [:name, { type: :string, ... }], ...]
문서
http://sequel.rubyforge.org/rdoc/files/doc/association_basics_rdoc.html
http://sequel.rubyforge.org/rdoc/classes/Sequel/Schema/Generator.html
http://sequel.rubyforge.org/rdoc/files/doc/validations_rdoc.html
http://sequel.rubyforge.org/rdoc/classes/Sequel/Model.html
테이블 변경
db.alter_table :deals do
add_column :name, String
drop_column :column_name
rename_column :from, :to
add_constraint :valid_name, :name.like('A%')
drop_constraint :constraint
add_full_text_index :body
add_spatial_index [columns]
add_index :price
drop_index :index
add_foreign_key :artist_id, :table
add_primary_key :id
add_unique_constraint [columns]
set_column_allow_null :foo, false
set_column_default :title, ''
set_column_type :price, 'char(10)'
end
모델 연관 관계
class Deal < Sequel::Model
many_to_many :images,
left_id: :deal_id,
right_id: :image_id,
join_table: :image_links
one_to_many :files,
key: :deal_id,
class: :DataFile
many_to_one :parent, class: self
one_to_many :children, key: :parent_id, class: self
one_to_many :gold_albums, class: :Album do |ds|
ds.where { copies_sold > 50000 }
end
end
many_to_many에서 제공하는 메서드
Deal[1].images
Deal[1].add_image(image)
Deal[1].remove_image(image)
Deal[1].remove_all_images
유효성 검사
class Deal < Sequel::Model
plugin :validation_helpers
def validate
super
errors.add(:name, '비어 있을 수 없습니다') if !name || name.empty?
validates_presence [:title, :site]
validates_unique :name
validates_format /\Ahttps?:\/\//, :website, message: '유효한 URL이 아닙니다'
validates_includes %w(a b c), :type
validates_integer :rating
validates_numeric :number
validates_type String, [:title, :description]
validates_integer :rating if new?
# 옵션: :message, :allow_nil, :allow_blank, :allow_missing
validates_exact_length 17, :isbn
validates_min_length 3, :name
validates_max_length 100, :name
validates_length_range 3..100, :name
end
def filename=(value)
@values[:filename] = value
end
end
deal.errors
모델 관련 기능
deal = Deal[1]
deal.changed_columns
deal.destroy # 훅 호출
deal.delete # 훅 호출 안 함
deal.exists?
deal.new?
deal.hash # 유니크 값만
deal.keys # => [:id, :name]
deal.modified!
deal.modified?
deal.lock!
콜백
before_create
after_create
before_validation
after_validation
before_save
before_update
UPDATE 쿼리
after_update
after_save
before_destroy
DELETE 쿼리
after_destroy
스키마
class Deal < Sequel::Model
set_schema do
primary_key :id
primary_key [:id, :title]
String :name, primary_key: true
String :title
Numeric :price
DateTime :expires
unique :whatever
check(:price) { num > 0 }
foreign_key :artist_id
String :artist_name, key: :id
index :title
index [:artist_id, :name]
full_text_index :title
# String, Integer, Fixnum, Bignum, Float, Numeric, BigDecimal,
# Date, DateTime, Time, File, TrueClass, FalseClass
end
end
기본 키 제한 해제
Category.create id: 'travel' # 오류
Category.unrestrict_primary_key
Category.create id: 'travel' # 정상