Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Newer
Older
100644 642 lines (408 sloc) 20.546 kB
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
1 == Sequel: The Database Toolkit for Ruby
2
3 Sequel is a lightweight database access toolkit for Ruby.
4
5 * Sequel provides thread safety, connection pooling and a concise DSL
6 for constructing database queries and table schemas.
7 * Sequel also includes a lightweight but comprehensive ORM layer for
8 mapping records to Ruby objects and handling associated records.
189fe30 @jeremyevans Use boof's website design for sequel.rubyforge.org
authored
9 * Sequel supports advanced database features such as prepared statements,
10 bound variables, master/slave configurations, and database sharding.
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
11 * Sequel makes it easy to deal with multiple records without having
12 to break your teeth on SQL.
13 * Sequel currently has adapters for ADO, DB2, DBI, Informix, JDBC,
14 MySQL, ODBC, OpenBase, Oracle, PostgreSQL and SQLite3.
15
16 == Resources
17
189fe30 @jeremyevans Use boof's website design for sequel.rubyforge.org
authored
18 * {Website}[http://sequel.rubyforge.org]
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
19 * {Source code}[http://github.com/jeremyevans/sequel]
20 * {Bug tracking}[http://code.google.com/p/ruby-sequel/issues/list]
21 * {Google group}[http://groups.google.com/group/sequel-talk]
189fe30 @jeremyevans Use boof's website design for sequel.rubyforge.org
authored
22 * {RDoc}[http://sequel.rubyforge.org/rdoc]
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
23
24 To check out the source code:
25
26 git clone git://github.com/jeremyevans/sequel.git
27
28 === Contact
29
30 If you have any comments or suggestions please post to the Google group.
31
32 == Installation
33
34 sudo gem install sequel
35
36 == A Short Example
37
38 require 'rubygems'
39 require 'sequel'
40
41 DB = Sequel.sqlite # memory database
42
43 DB.create_table :items do # Create a new table
44 column :name, :text
45 column :price, :float
46 end
47
48 items = DB[:items] # Create a dataset
49
50 # Populate the table
51 items << {:name => 'abc', :price => rand * 100}
52 items << {:name => 'def', :price => rand * 100}
53 items << {:name => 'ghi', :price => rand * 100}
54
55 # Print out the number of records
56 puts "Item count: #{items.count}"
57
58 # Print out the records in descending order by price
59 items.reverse_order(:price).print
60
61 # Print out the average price
62 puts "The average price is: #{items.avg(:price)}"
63
64 == The Sequel Console
65
66 Sequel includes an IRB console for quick'n'dirty access to databases. You can use it like this:
67
68 sequel sqlite://test.db # test.db in current directory
69
70 You get an IRB session with the database object stored in DB.
71
72 == An Introduction
73
74 Sequel is designed to take the hassle away from connecting to databases and manipulating them. Sequel deals with all the boring stuff like maintaining connections, formatting SQL correctly and fetching records so you can concentrate on your application.
75
76 Sequel uses the concept of datasets to retrieve data. A Dataset object encapsulates an SQL query and supports chainability, letting you fetch data using a convenient Ruby DSL that is both concise and infinitely flexible.
77
78 For example, the following one-liner returns the average GDP for the five biggest countries in the middle east region:
79
80 DB[:countries].filter(:region => 'Middle East').reverse_order(:area).limit(5).avg(:GDP)
81
82 Which is equivalent to:
83
84 SELECT avg(GDP) FROM countries WHERE region = 'Middle East' ORDER BY area DESC LIMIT 5
85
86 Since datasets retrieve records only when needed, they can be stored and later reused. Records are fetched as hashes (they can also be fetched as custom model objects), and are accessed using an Enumerable interface:
87
88 middle_east = DB[:countries].filter(:region => 'Middle East')
89 middle_east.order(:name).each {|r| puts r[:name]}
90
91 Sequel also offers convenience methods for extracting data from Datasets, such as an extended map method:
92
93 middle_east.map(:name) #=> ['Egypt', 'Greece', 'Israel', ...]
94
95 Or getting results as a transposed hash, with one column as key and another as value:
96
97 middle_east.to_hash(:name, :area) #=> {'Israel' => 20000, 'Greece' => 120000, ...}
98
99 == Getting Started
100
101 === Connecting to a database
102
103 To connect to a database you simply provide Sequel with a URL:
104
105 require 'sequel'
106 DB = Sequel.connect('sqlite://blog.db')
107
108 The connection URL can also include such stuff as the user name and password:
109
110 DB = Sequel.connect('postgres://cico:12345@localhost:5432/mydb')
111
112 You can also specify optional parameters, such as the connection pool size, or loggers for logging SQL queries:
113
114 DB = Sequel.connect("postgres://postgres:postgres@localhost/my_db",
115 :max_connections => 10, :loggers => [Logger.new('log/db.log']))
116
117 You can specify a block to connect, which will disconnect from the database after it completes:
118
119 Sequel.connect('postgres://cico:12345@localhost:5432/mydb'){|db| db[:posts].delete}
120
121 === Arbitrary SQL queries
122
123 DB.execute("create table t (a text, b text)")
124 DB.execute("insert into t values ('a', 'b')")
125
126 Or more succinctly:
127
128 DB << "create table t (a text, b text)"
129 DB << "insert into t values ('a', 'b')"
130
131 You can also create datasets based on raw SQL:
132
133 dataset = DB['select * from items']
134 dataset.count # will return the number of records in the result set
135 dataset.map(:id) # will return an array containing all values of the id column in the result set
136
137 You can also fetch records with raw SQL through the dataset:
138
139 DB['select * from items'].each do |row|
140 p row
141 end
142
143 === Getting Dataset Instances
144
145 Dataset is the primary means through which records are retrieved and manipulated. You can create an blank dataset by using the dataset method:
146
147 dataset = DB.dataset
148
149 Or by using the from methods:
150
151 posts = DB.from(:posts)
152
153 The recommended way is the equivalent shorthand:
154
155 posts = DB[:posts]
156
157 Datasets will only fetch records when you explicitly ask for them. Datasets can be manipulated to filter through records, change record order, join tables, etc..
158
159 === Retrieving Records
160
161 You can retrieve records by using the all method:
162
163 posts.all
164
165 The all method returns an array of hashes, where each hash corresponds to a record.
166
167 You can also iterate through records one at a time:
168
169 posts.each{|row| p row}
170
171 Or perform more advanced stuff:
172
173 posts.map(:id)
174 posts.inject({}){|h, r| h[r[:id]] = r[:name]}
175
176 You can also retrieve the first record in a dataset:
177
178 posts.first
179
180 Or retrieve a single record with a specific value:
181
182 posts[:id => 1]
183
184 If the dataset is ordered, you can also ask for the last record:
185
186 posts.order(:stamp).last
187
188 === Filtering Records
189
190 The simplest way to filter records is to provide a hash of values to match:
191
192 my_posts = posts.filter(:category => 'ruby', :author => 'david')
193
194 You can also specify ranges:
195
196 my_posts = posts.filter(:stamp => (Date.today - 14)..(Date.today - 7))
197
198 Or lists of values:
199
200 my_posts = posts.filter(:category => ['ruby', 'postgres', 'linux'])
201
202 Sequel also accepts expressions:
203
204 my_posts = posts.filter(:stamp > Date.today << 1)
205
206 Some adapters (like postgresql) will also let you specify Regexps:
207
208 my_posts = posts.filter(:category => /ruby/i)
209
210 You can also use an inverse filter:
211
212 my_posts = posts.exclude(:category => /ruby/i)
213 my_posts = posts.filter(:category => /ruby/i).invert # same as above
214
215 You can also specify a custom WHERE clause using a string:
216
217 posts.filter('stamp IS NOT NULL')
218
219 You can use parameters in your string, as well (ActiveRecord style):
220
221 posts.filter('(stamp < ?) AND (author != ?)', Date.today - 3, author_name)
222 posts.filter((:stamp < Date.today - 3) & ~(:author => author_name)) # same as above
223
224 Datasets can also be used as subqueries:
225
226 DB[:items].filter('price > ?', DB[:items].select('AVG(price) + 100'))
227
228 After filtering you can retrieve the matching records by using any of the retrieval methods:
229
230 my_posts.each{|row| p row}
231
232 See the doc/dataset_filtering.rdoc file for more details.
233
234 === Summarizing Records
235
236 Counting records is easy:
237 posts.filter(:category => /ruby/i).count
238
239 And you can also query maximum/minimum values:
240 max_value = DB[:history].max(:value)
241
242 Or calculate a sum:
243 total = DB[:items].sum(:price)
244
245 === Ordering Records
246
247 Ordering datasets is simple:
248
249 posts.order(:stamp) # ORDER BY stamp
250 posts.order(:stamp, :name) # ORDER BY stamp, name
251
252 You can also specify descending order
253
254 posts.order(:stamp.desc) # ORDER BY stamp DESC
255
256 === Deleting Records
257
258 Deleting records from the table is done with delete:
259
260 posts.filter('stamp < ?', Date.today - 3).delete
261
262 === Inserting Records
263
264 Inserting records into the table is done with insert:
265
266 posts.insert(:category => 'ruby', :author => 'david')
267 posts << {:category => 'ruby', :author => 'david'} # same as above
268
269 === Updating Records
270
271 Updating records in the table is done with update:
272
273 posts.filter('stamp < ?', Date.today - 7).update(:state => 'archived')
274
275 === Joining Tables
276
277 Joining is very useful in a variety of scenarios, for example many-to-many relationships. With Sequel it's really easy:
278
279 order_items = DB[:items].join(:order_items, :item_id => :id).
280 filter(:order_items__order_id => 1234)
281
282 This is equivalent to the SQL:
283
284 SELECT * FROM items LEFT OUTER JOIN order_items
285 ON order_items.item_id = items.id
286 WHERE order_items.order_id = 1234
287
288 You can then do anything you like with the dataset:
289
290 order_total = order_items.sum(:price)
291
292 Which is equivalent to the SQL:
293
294 SELECT sum(price) FROM items LEFT OUTER JOIN order_items
295 ON order_items.item_id = items.id
296 WHERE order_items.order_id = 1234
297
298 === Graphing Datasets
299
300 When retrieving records from joined datasets, you get the results in a single hash, which is subject to clobbering:
301
302 DB[:items].join(:order_items, :item_id => :id).first
303 => {:id=>(could be items.id or order_items.id), :item_id=>order_items.order_id}
304
305 Using graph, you can split the result hashes into subhashes, one per join:
306
307 DB[:items].graph(:order_items, :item_id => :id).first
308 => {:items=>{:id=>items.id}, :order_items=>{:id=>order_items.id, :item_id=>order_items.item_id}}
309
a0181eb @jeremyevans Various doc fixes
authored
310 == Sequel Models
3ece811 @ciconia In preparation for 1.0.
ciconia authored
311
a0181eb @jeremyevans Various doc fixes
authored
312 Models in Sequel are based on the Active Record pattern described by Martin Fowler (http://www.martinfowler.com/eaaCatalog/activeRecord.html). A model class corresponds to a table or a dataset, and an instance of that class wraps a single record in the model's underlying dataset.
3ece811 @ciconia In preparation for 1.0.
ciconia authored
313
a0181eb @jeremyevans Various doc fixes
authored
314 Model classes are defined as regular Ruby classes:
315
316 DB = Sequel.connect('sqlite:/blog.db')
317 class Post < Sequel::Model
318 end
319
82d2593 @jeremyevans Some additions to sequel/README from the Google Code Wiki
authored
320 Just like in DataMapper or ActiveRecord, Sequel model classes assume that the table name is a plural of the class name:
a0181eb @jeremyevans Various doc fixes
authored
321
82d2593 @jeremyevans Some additions to sequel/README from the Google Code Wiki
authored
322 Post.table_name #=> :posts
323
324 You can, however, explicitly set the table name or even the dataset used:
325
326 class Post < Sequel::Model(:my_posts)
a0181eb @jeremyevans Various doc fixes
authored
327 end
82d2593 @jeremyevans Some additions to sequel/README from the Google Code Wiki
authored
328 # or:
329 Post.set_dataset :my_posts
330 # or:
331 Post.set_dataset DB[:my_posts].where(:category => 'ruby')
332
a0181eb @jeremyevans Various doc fixes
authored
333 === Model instances
3ece811 @ciconia In preparation for 1.0.
ciconia authored
334
a0181eb @jeremyevans Various doc fixes
authored
335 Model instance are identified by a primary key. By default, Sequel assumes the primary key column to be :id. The Model#[] method can be used to fetch records by their primary key:
3ece811 @ciconia In preparation for 1.0.
ciconia authored
336
a0181eb @jeremyevans Various doc fixes
authored
337 post = Post[123]
3ece811 @ciconia In preparation for 1.0.
ciconia authored
338
a0181eb @jeremyevans Various doc fixes
authored
339 The Model#pk method is used to retrieve the record's primary key value:
3ece811 @ciconia In preparation for 1.0.
ciconia authored
340
a0181eb @jeremyevans Various doc fixes
authored
341 post.pk #=> 123
3ece811 @ciconia In preparation for 1.0.
ciconia authored
342
a0181eb @jeremyevans Various doc fixes
authored
343 Sequel models allow you to use any column as a primary key, and even composite keys made from multiple columns:
3ece811 @ciconia In preparation for 1.0.
ciconia authored
344
a0181eb @jeremyevans Various doc fixes
authored
345 class Post < Sequel::Model
346 set_primary_key [:category, :title]
347 end
3ece811 @ciconia In preparation for 1.0.
ciconia authored
348
a0181eb @jeremyevans Various doc fixes
authored
349 post = Post['ruby', 'hello world']
350 post.pk #=> ['ruby', 'hello world']
3ece811 @ciconia In preparation for 1.0.
ciconia authored
351
a0181eb @jeremyevans Various doc fixes
authored
352 You can also define a model class that does not have a primary key, but then you lose the ability to update records.
3ece811 @ciconia In preparation for 1.0.
ciconia authored
353
a0181eb @jeremyevans Various doc fixes
authored
354 A model instance can also be fetched by specifying a condition:
3ece811 @ciconia In preparation for 1.0.
ciconia authored
355
a0181eb @jeremyevans Various doc fixes
authored
356 post = Post[:title => 'hello world']
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
357 post = Post.find(:num_comments < 10)
3ece811 @ciconia In preparation for 1.0.
ciconia authored
358
a0181eb @jeremyevans Various doc fixes
authored
359 === Iterating over records
3ece811 @ciconia In preparation for 1.0.
ciconia authored
360
a0181eb @jeremyevans Various doc fixes
authored
361 A model class lets you iterate over specific records by acting as a proxy to the underlying dataset. This means that you can use the entire Dataset API to create customized queries that return model instances, e.g.:
3ece811 @ciconia In preparation for 1.0.
ciconia authored
362
a0181eb @jeremyevans Various doc fixes
authored
363 Post.filter(:category => 'ruby').each{|post| p post}
3ece811 @ciconia In preparation for 1.0.
ciconia authored
364
a0181eb @jeremyevans Various doc fixes
authored
365 You can also manipulate the records in the dataset:
3ece811 @ciconia In preparation for 1.0.
ciconia authored
366
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
367 Post.filter(:num_comments < 7).delete
368 Post.filter(:title.like(/ruby/)).update(:category => 'ruby')
3ece811 @ciconia In preparation for 1.0.
ciconia authored
369
a0181eb @jeremyevans Various doc fixes
authored
370 === Accessing record values
3ece811 @ciconia In preparation for 1.0.
ciconia authored
371
a0181eb @jeremyevans Various doc fixes
authored
372 A model instances stores its values as a hash:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
373
a0181eb @jeremyevans Various doc fixes
authored
374 post.values #=> {:id => 123, :category => 'ruby', :title => 'hello world'}
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
375
a0181eb @jeremyevans Various doc fixes
authored
376 You can read the record values as object attributes (assuming the attribute names are valid columns in the model's dataset):
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
377
a0181eb @jeremyevans Various doc fixes
authored
378 post.id #=> 123
379 post.title #=> 'hello world'
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
380
a0181eb @jeremyevans Various doc fixes
authored
381 You can also change record values:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
382
a0181eb @jeremyevans Various doc fixes
authored
383 post.title = 'hey there'
384 post.save
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
385
a0181eb @jeremyevans Various doc fixes
authored
386 Another way to change values by using the #update_with_params method:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
387
a0181eb @jeremyevans Various doc fixes
authored
388 post.update_with_params(:title => 'hey there')
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
389
a0181eb @jeremyevans Various doc fixes
authored
390 === Creating new records
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
391
a0181eb @jeremyevans Various doc fixes
authored
392 New records can be created by calling Model.create:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
393
a0181eb @jeremyevans Various doc fixes
authored
394 post = Post.create(:title => 'hello world')
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
395
a0181eb @jeremyevans Various doc fixes
authored
396 Another way is to construct a new instance and save it:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
397
a0181eb @jeremyevans Various doc fixes
authored
398 post = Post.new
399 post.title = 'hello world'
400 post.save
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
401
a0181eb @jeremyevans Various doc fixes
authored
402 You can also supply a block to Model.new and Model.create:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
403
a0181eb @jeremyevans Various doc fixes
authored
404 post = Post.create {|p| p.title = 'hello world'}
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
405
a0181eb @jeremyevans Various doc fixes
authored
406 post = Post.new do |p|
407 p.title = 'hello world'
408 p.save
409 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
410
a0181eb @jeremyevans Various doc fixes
authored
411 === Hooks
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
412
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
413 You can execute custom code when creating, updating, or deleting records by using hooks. The before_create and after_create hooks wrap record creation. The before_update and after_update wrap record updating. The before_save and after_save wrap record creation and updating. The before_destroy and after_destroy wrap destruction. The before_validation and after_validation hooks wrap validation.
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
414
a0181eb @jeremyevans Various doc fixes
authored
415 Hooks are defined by supplying a block:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
416
a0181eb @jeremyevans Various doc fixes
authored
417 class Post < Sequel::Model
418 after_create do
1d2404f @jeremyevans Fix confusing code in after_create example in sequel/README
authored
419 author.increase_post_count
a0181eb @jeremyevans Various doc fixes
authored
420 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
421
a0181eb @jeremyevans Various doc fixes
authored
422 after_destroy do
1d2404f @jeremyevans Fix confusing code in after_create example in sequel/README
authored
423 author.decrease_post_count
a0181eb @jeremyevans Various doc fixes
authored
424 end
425 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
426
a0181eb @jeremyevans Various doc fixes
authored
427 === Deleting records
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
428
a0181eb @jeremyevans Various doc fixes
authored
429 You can delete individual records by calling #delete or #destroy. The only difference between the two methods is that #destroy invokes before_destroy and after_destroy hooks, while #delete does not:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
430
a0181eb @jeremyevans Various doc fixes
authored
431 post.delete #=> bypasses hooks
432 post.destroy #=> runs hooks
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
433
a0181eb @jeremyevans Various doc fixes
authored
434 Records can also be deleted en-masse by invoking Model.delete and Model.destroy. As stated above, you can specify filters for the deleted records:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
435
a0181eb @jeremyevans Various doc fixes
authored
436 Post.filter(:category => 32).delete #=> bypasses hooks
437 Post.filter(:category => 32).destroy #=> runs hooks
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
438
a0181eb @jeremyevans Various doc fixes
authored
439 Please note that if Model.destroy is called, each record is deleted
440 separately, but Model.delete deletes all relevant records with a single
441 SQL statement.
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
442
a0181eb @jeremyevans Various doc fixes
authored
443 === Associations
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
444
a0181eb @jeremyevans Various doc fixes
authored
445 Associations are used in order to specify relationships between model classes that reflect relations between tables in the database using foreign keys.
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
446
a0181eb @jeremyevans Various doc fixes
authored
447 class Post < Sequel::Model
448 many_to_one :author
449 one_to_many :comments
450 many_to_many :tags
451 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
452
a0181eb @jeremyevans Various doc fixes
authored
453 You can also use the ActiveRecord names for these associations:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
454
a0181eb @jeremyevans Various doc fixes
authored
455 class Post < Sequel::Model
456 belongs_to :author
457 has_many :comments
458 has_and_belongs_to_many :tags
459 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
460
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
461 many_to_one creates a getter and setter for each model object:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
462
a0181eb @jeremyevans Various doc fixes
authored
463 class Post < Sequel::Model
464 many_to_one :author
465 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
466
a0181eb @jeremyevans Various doc fixes
authored
467 post = Post.create(:name => 'hi!')
468 post.author = Author[:name => 'Sharon']
469 post.author
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
470
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
471 one_to_many and many_to_many create a getter method, a method for adding an object to the association, a method for removing an object from the association, and a method for removing all associated objected from the association:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
472
a0181eb @jeremyevans Various doc fixes
authored
473 class Post < Sequel::Model
474 one_to_many :comments
475 many_to_many :tags
476 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
477
a0181eb @jeremyevans Various doc fixes
authored
478 post = Post.create(:name => 'hi!')
479 post.comments
480 comment = Comment.create(:text=>'hi')
481 post.add_comment(comment)
482 post.remove_comment(comment)
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
483 post.remove_all_comments
a0181eb @jeremyevans Various doc fixes
authored
484 tag = Tag.create(:tag=>'interesting')
485 post.add_tag(tag)
486 post.remove_tag(tag)
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
487 post.remove_all_tags
a0181eb @jeremyevans Various doc fixes
authored
488
489 === Eager Loading
490
491 Associations can be eagerly loaded via .eager and the :eager association option. Eager loading is used when loading a group of objects. It loads all associated objects for all of the current objects in one query, instead of using a separate query to get the associated objects for each current object. Eager loading requires that you retrieve all model objects at once via .all (instead of individually by .each). Eager loading can be cascaded, loading association's associated objects.
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
492
a0181eb @jeremyevans Various doc fixes
authored
493 class Person < Sequel::Model
494 one_to_many :posts, :eager=>[:tags]
495 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
496
a0181eb @jeremyevans Various doc fixes
authored
497 class Post < Sequel::Model
498 many_to_one :person
499 one_to_many :replies
500 many_to_many :tags
501 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
502
a0181eb @jeremyevans Various doc fixes
authored
503 class Tag < Sequel::Model
504 many_to_many :posts
505 many_to_many :replies
506 end
507
508 class Reply < Sequel::Model
509 many_to_one :person
510 many_to_one :post
511 many_to_many :tags
512 end
513
514 # Eager loading via .eager
515 Post.eager(:person).all
2b572b9 @jeremyevans In the RDoc, give an example of .eager being used with filters, and d…
authored
516
517 # eager is a dataset method, so it works with filters/orders/limits/etc.
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
518 Post.filter(:topic > 'M').order(:date).limit(5).eager(:person).all
a0181eb @jeremyevans Various doc fixes
authored
519
520 person = Person.first
521 # Eager loading via :eager (will eagerly load the tags for this person's posts)
522 person.posts
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
523
a0181eb @jeremyevans Various doc fixes
authored
524 # These are equivalent
525 Post.eager(:person, :tags).all
526 Post.eager(:person).eager(:tags).all
527
528 # Cascading via .eager
529 Tag.eager(:posts=>:replies).all
530
531 # Will also grab all associated posts' tags (because of :eager)
532 Reply.eager(:person=>:posts).all
533
534 # No depth limit (other than memory/stack), and will also grab posts' tags
535 # Loads all people, their posts, their posts' tags, replies to those posts,
536 # the person for each reply, the tag for each reply, and all posts and
537 # replies that have that tag. Uses a total of 8 queries.
538 Person.eager(:posts=>{:replies=>[:person, {:tags=>{:posts, :replies}}]}).all
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
539
a0181eb @jeremyevans Various doc fixes
authored
540 In addition to using eager, you can also use eager_graph, which will use a single query to get the object and all associated objects. This may be necessary if you want to filter the result set based on columns in associated tables. It works with cascading as well, the syntax is exactly the same. Note that using eager_graph to eagerly load multiple *_to_many associations will cause the result set to be a cartesian product, so you should be very careful with your filters when using it in that case.
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
541
a0181eb @jeremyevans Various doc fixes
authored
542 === Caching model instances with memcached
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
543
a0181eb @jeremyevans Various doc fixes
authored
544 Sequel models can be cached using memcached based on their primary keys. The use of memcached can significantly reduce database load by keeping model instances in memory. The set_cache method is used to specify caching:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
545
a0181eb @jeremyevans Various doc fixes
authored
546 require 'memcache'
547 CACHE = MemCache.new 'localhost:11211', :namespace => 'blog'
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
548
a0181eb @jeremyevans Various doc fixes
authored
549 class Author < Sequel::Model
550 set_cache CACHE, :ttl => 3600
551 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
552
a0181eb @jeremyevans Various doc fixes
authored
553 Author[333] # database hit
554 Author[333] # cache hit
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
555
a0181eb @jeremyevans Various doc fixes
authored
556 === Extending the underlying dataset
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
557
a0181eb @jeremyevans Various doc fixes
authored
558 The obvious way to add table-wide logic is to define class methods to the model class definition. That way you can define subsets of the underlying dataset, change the ordering, or perform actions on multiple records:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
559
a0181eb @jeremyevans Various doc fixes
authored
560 class Post < Sequel::Model
561 def self.posts_with_few_comments
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
562 filter(:num_comments < 30)
a0181eb @jeremyevans Various doc fixes
authored
563 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
564
a0181eb @jeremyevans Various doc fixes
authored
565 def self.clean_posts_with_few_comments
566 posts_with_few_comments.delete
567 end
568 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
569
a0181eb @jeremyevans Various doc fixes
authored
570 You can also implement table-wide logic by defining methods on the dataset:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
571
a0181eb @jeremyevans Various doc fixes
authored
572 class Post < Sequel::Model
573 def_dataset_method(:posts_with_few_comments) do
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
574 filter(:num_comments < 30)
a0181eb @jeremyevans Various doc fixes
authored
575 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
576
a0181eb @jeremyevans Various doc fixes
authored
577 def_dataset_method(:clean_posts_with_few_comments) do
578 posts_with_few_comments.delete
579 end
580 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
581
a0181eb @jeremyevans Various doc fixes
authored
582 This is the recommended way of implementing table-wide operations, and allows you to have access to your model API from filtered datasets as well:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
583
a0181eb @jeremyevans Various doc fixes
authored
584 Post.filter(:category => 'ruby').clean_old_posts
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
585
a0181eb @jeremyevans Various doc fixes
authored
586 Sequel models also provide a short hand notation for filters:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
587
a0181eb @jeremyevans Various doc fixes
authored
588 class Post < Sequel::Model
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
589 subset(:posts_with_few_comments, :num_comments < 30)
a0181eb @jeremyevans Various doc fixes
authored
590 subset :invisible, :visible => false
591 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
592
a0181eb @jeremyevans Various doc fixes
authored
593 === Defining the underlying schema
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
594
a0181eb @jeremyevans Various doc fixes
authored
595 Model classes can also be used as a place to define your table schema and control it. The schema DSL is exactly the same provided by Sequel::Schema::Generator:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
596
a0181eb @jeremyevans Various doc fixes
authored
597 class Post < Sequel::Model
598 set_schema do
599 primary_key :id
600 text :title
601 text :category
602 foreign_key :author_id, :table => :authors
603 end
604 end
605
606 You can then create the underlying table, drop it, or recreate it:
607
608 Post.table_exists?
609 Post.create_table
610 Post.drop_table
611 Post.create_table! # drops the table if it exists and then recreates it
612
613 === Basic Model Validations
98169b7 @jeremyevans Various small doc fixes
authored
614
a0181eb @jeremyevans Various doc fixes
authored
615 To assign default validations to a sequel model:
98169b7 @jeremyevans Various small doc fixes
authored
616
a0181eb @jeremyevans Various doc fixes
authored
617 class MyModel < Sequel::Model
618 validates do
619 format_of...
620 presence_of...
621 acceptance_of...
622 confirmation_of...
623 length_of...
624 numericality_of...
625 format_of...
626 each...
627 end
628 end
98169b7 @jeremyevans Various small doc fixes
authored
629
a0181eb @jeremyevans Various doc fixes
authored
630 You may also perform the usual 'longhand' way to assign default model validates directly within the model class itself:
98169b7 @jeremyevans Various small doc fixes
authored
631
a0181eb @jeremyevans Various doc fixes
authored
632 class MyModel < Sequel::Model
633 validates_format_of...
634 validates_presence_of...
635 validates_acceptance_of...
636 validates_confirmation_of...
637 validates_length_of...
638 validates_numericality_of...
639 validates_format_of...
640 validates_each...
641 end
Something went wrong with that request. Please try again.