· 8 years ago · Apr 23, 2018, 11:02 PM
1Open a database
2
3 require 'rubygems'
4 require 'sequel'
5
6 DB = Sequel.sqlite 'my_blog.db'
7 DB = Sequel.connect('postgres://user:password@localhost/my_db')
8 DB = Sequel.mysql 'my_db', :user => 'user', :password => 'password', :host =>
9 'localhost'
10 DB = Sequel.ado 'mydb'
11
12Open an SQLite memory database
13
14Without a filename argument, the sqlite adapter will setup a new sqlite database
15in RAM.
16
17 DB = Sequel.sqlite
18
19Logging SQL statements
20
21 require 'logger'
22 DB = Sequel.sqlite '', :loggers => [Logger.new($stdout)]
23 # or
24 DB.loggers << Logger.new(...)
25
26Using raw SQL
27
28 DB << "CREATE TABLE users (name VARCHAR(255) NOT NULL, age INT(3) NOT NULL)"
29 DB.fetch("SELECT name FROM users") do |row|
30 p r[:name]
31 end
32 dataset = DB["SELECT age FROM users"]
33 dataset.print
34 dataset.map(:age)
35
36Create a dataset
37
38 dataset = DB[:items]
39 dataset = DB.dataset.from(:items)
40
41Most dataset methods are chainable
42
43 dataset = DB[:managers].where(:salary => 5000..10000).order(:name,
44 :department)
45 # or
46 dataset = DB.query do
47 from :managers
48 where :salary => 5000..10000
49 order :name, :department
50 end
51
52Insert rows
53
54 dataset.insert(:name => 'Sharon', :grade => 50)
55 dataset << {:name => 'Sharon', :grade => 50} # same effect as above
56
57Retrieve rows
58
59 dataset.each {|r| p r}
60 dataset.all #=> [{...}, {...}, ...]
61 dataset.first
62 dataset.order(:name).last # works only for ordered datasets
63
64Retrieve single value
65 id= DB[:item].filter(:name => 'item1').select(:id).single_value
66
67Update/Delete rows
68
69 dataset.filter(:active => false).delete
70 dataset.filter('price < ?', 100).update(:active => true)
71 DB[:user].filter(:id => 1).update(:count => :count + 1) #=>
72 "UPDATE user SET count = (count + 1) WHERE (id = 1)"
73
74Datasets are Enumerable
75
76 dataset.map {|r| r[:name]}
77 dataset.map(:name) # same effect as above
78
79 dataset.inject {|sum, r| sum + r[:value]}
80
81Filtering (see also doc/dataset_filtering.rdoc)
82
83 dataset.filter(:name => 'abc')
84 dataset.filter('name = ?', 'abc')
85 dataset.filter(:value > 100)
86 dataset.exclude(:value <= 100)
87
88 dataset.filter(:value => 50..100)
89 dataset.where((:value >= 50) & (:value <= 100))
90
91 dataset.where('value IN ?', [50,75,100])
92
93 # Get the first record that matches a condition
94 dataset[:name => 'abc'] # Same as:
95 dataset.filter(:name => 'abc').first
96
97 # Filter using a subquery
98 dataset.filter('price > ?', dataset.select('AVG(price) + 100'))
99
100Advanced filtering using ruby expressions without blocks
101
102Available as of Sequel 2.0:
103
104 DB[:items].filter(:price < 100).sql
105 #=> "SELECT * FROM items WHERE (price < 100)"
106
107 DB[:items].filter(:name.like('AL%')).sql
108 #=> "SELECT * FROM items WHERE (name LIKE 'AL%')"
109
110There's support for nested expressions with AND, OR and NOT:
111
112 DB[:items].filter((:x > 5) & (:y > 10)).sql
113 #=> "SELECT * FROM items WHERE ((x > 5) AND (y > 10))"
114
115 DB[:items].filter({:x => 1, :y => 2}.sql_or & ~{:z => 3}).sql
116 #=> "SELECT * FROM items WHERE (((x = 1) OR (y = 2)) AND (z != 3))"
117
118You can use arithmetic operators and specify SQL functions:
119
120 DB[:items].filter((:x + :y) > :z).sql
121 #=> "SELECT * FROM items WHERE ((x + y) > z)"
122
123 DB[:items].filter(:price < :AVG[:price] + 100).sql
124 #=> "SELECT * FROM items WHERE (price < (AVG(price) + 100))"
125
126Ordering
127
128 dataset.order(:kind)
129 dataset.reverse_order(:kind)
130 dataset.order(:kind.desc, :name)
131
132Row ranges
133
134 dataset.limit(30) # LIMIT 30
135 dataset.limit(30, 10) # LIMIT 30 OFFSET 10
136
137Pagination
138
139 paginated = dataset.paginate(1, 10) # first page, 10 rows per page
140 paginated.page_count #=> number of pages in dataset
141 paginated.current_page #=> 1
142 paginated.next_page #=> next page number or nil
143 paginated.prev_page #=> previous page number or nil
144 paginated.first_page? #=> true if page number = 1
145 paginated.last_page? #=> true if page number = page_count
146
147Joins
148
149 DB[:items].left_outer_join(:categories, :id => :category_id).sql #=>
150 "SELECT * FROM items LEFT OUTER JOIN categories ON categories.id =
151 items.category_id"
152
153 DB[:items].join(:users, :id => :user_id).join(:resources, :id =>
154 :items__resource_id).sql #=>
155 "SELECT * FROM items INNER JOIN users ON (users.id = items.user_id) INNER
156 JOIN resources ON (resources.id = items.resource_id)"
157
158 DB.from(:items___t, :users___u, :resources___r).filter(:u__id=>:t__user_id,
159 :r__id=>:t__resource_id).sql #=>
160 "SELECT * FROM items AS t, users AS u, resources AS r WHERE ((u.id =
161 t.user_id) AND (r.id = t.resource_id))"
162
163Summarizing
164
165 dataset.count #=> record count
166 dataset.max(:price)
167 dataset.min(:price)
168 dataset.avg(:price)
169 dataset.sum(:stock)
170
171 dataset.group(:category).select(:category, :AVG[:price])
172
173SQL Functions / Literals
174
175 dataset.update(:updated_at => :NOW[])
176 dataset.update(:updated_at => 'NOW()'.lit)
177
178 dataset.update(:updated_at => "DateValue('1/1/2001')".lit)
179 dataset.update(:updated_at => :DateValue['1/1/2001'])
180
181 dataset.filter({:updated_at.extract(:year) => 3} &
182 {:updated_at.extract(:month) => 1})
183
184Schema Manipulation
185
186 DB.create_table :items do
187 primary_key :id
188 text :name, :unique => true, :null => false
189 boolean :active, :default => true
190 foreign_key :category_id, :categories
191 index :grade
192 constraint(:check_grade) { {:grade => 0} | {:active => false} }
193 end
194
195 DB.drop_table :items
196
197 DB.create_table :test do
198 varchar :zipcode, :size => 10
199 enum :system, :elements => ['mac', 'linux', 'windows']
200 end
201
202Migrations
203 class AddUser < Sequel::Migration
204 def up
205 create_table :users do
206 primary_key :id
207 text :username, :unique => true, :null => false
208 text :email, :unique => true, :null => false
209 varchar :password, :size => 40, :null => false
210 date :dob, :null => false
211 boolean :disabled, :default => false
212 timestamp :created_at
213 timestamp :updated_at
214 end
215 end
216
217 def down
218 drop_table :users
219 end
220 end
221
222 class ModifyProfiles < Sequel::Migration
223 def up
224 alter_table :profiles do
225 add_column :firstname, :text
226 add_column :lastname, :text
227 add_index :zip
228 add_constraint(:check_count) { :count > 0 }
229 rename_column :col1, :col2
230 set_column_default :age, 21
231 end
232 end
233
234 def down
235 alter_table :profiles do
236 drop_column :firstname
237 drop_column :lastname
238 drop_index :zip
239 rename_column :col2, :col1
240 end
241 end
242 end
243
244
245
246Aliasing
247
248 DB[:items].select(:name.as(:item_name))
249 DB[:items].select(:name => :item_name)
250 DB[:items].select(:name___item_name)
251 DB[:items___items_table].select(:items_table__name___item_name)
252 # => "SELECT items_table.name AS item_name FROM items AS items_table"
253
254Transactions
255
256 DB.transaction do
257 dataset << {:first_name => 'Inigo', :last_name => 'Montoya'}
258 dataset << {:first_name => 'Farm', :last_name => 'Boy'}
259 end # Either both are inserted or neither are inserted
260
261Database#transaction is re-entrant:
262
263 DB.transaction do # BEGIN issued only here
264 DB.transaction
265 dataset << {:first_name => 'Inigo', :last_name => 'Montoya'}
266 end
267 end # COMMIT issued only here
268
269Transactions are aborted if an error is raised:
270
271 DB.transaction do
272 raise "some error occurred"
273 end # ROLLBACK issued and the error is re-raised
274
275Transactions can also be aborted by raising Sequel::Error::Rollback:
276
277 DB.transaction do
278 raise(Sequel::Error::Rollback) if something_bad_happened
279 end # ROLLBACK issued and no error raised
280
281Miscellaneous:
282
283 dataset.sql #=> "SELECT * FROM items"
284 dataset.delete_sql #=> "DELETE FROM items"
285 dataset.where(:name => 'sequel').exists #=> "EXISTS ( SELECT 1 FROM items
286 WHERE name = 'sequel' )"
287 b= DB.fetch("SELECT #{DB[:item].filter(:name =>
288 'sequel').exists}").single_value #there maybe a better way
289 dataset.print #=> pretty table print to $stdout
290 dataset.columns #=> array of columns in the result set, does a SELECT
291 DB.schema_for_table(:items) => [[:id, {:type=>:integer, ...}], [:name,
292 {:type=>:string, ...}], ...]
293 # Works on PostgreSQL, MySQL, SQLite, and
294 possibly elsewhere
295
296 # indexing, and programatically creating updates
297 v= 123
298 periods= [:year, :month, :day]
299 idx= 10
300 DB[:items].update_sql(periods.inject({}){|h, p| h[p|idx] = (p|idx) + v; h})
301 #=>
302 "UPDATE items SET month[10] = (month[10] + 123), year[10] = (year[10] +
303 123), week[10] = (week[10] + 123)"