Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 653 lines (404 sloc) 21.891 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.
645073f @jeremyevans Add DataObjects adapter, with PostgreSQL, MySQL, and SQLite subadapters
authored
9 * Sequel supports advanced database features such as prepared
10 statements, bound variables, stored procedures, master/slave
11 configurations, and database sharding.
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
12 * Sequel makes it easy to deal with multiple records without having
13 to break your teeth on SQL.
8398b5b @jeremyevans Add Amalgalite adapter
authored
14 * Sequel currently has adapters for ADO, Amalgalite, DataObjects,
15 DB2, DBI, Firebird, Informix, JDBC, MySQL, ODBC, OpenBase, Oracle,
16 PostgreSQL and SQLite3.
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
17
18 == Resources
19
189fe30 @jeremyevans Use boof's website design for sequel.rubyforge.org
authored
20 * {Website}[http://sequel.rubyforge.org]
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
21 * {Source code}[http://github.com/jeremyevans/sequel]
22 * {Bug tracking}[http://code.google.com/p/ruby-sequel/issues/list]
23 * {Google group}[http://groups.google.com/group/sequel-talk]
189fe30 @jeremyevans Use boof's website design for sequel.rubyforge.org
authored
24 * {RDoc}[http://sequel.rubyforge.org/rdoc]
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
25
26 To check out the source code:
27
28 git clone git://github.com/jeremyevans/sequel.git
29
30 === Contact
31
32 If you have any comments or suggestions please post to the Google group.
33
34 == Installation
35
36 sudo gem install sequel
37
38 == A Short Example
39
40 require 'rubygems'
41 require 'sequel'
42
43 DB = Sequel.sqlite # memory database
44
76613bc @jeremyevans Update README.rdoc
authored
45 DB.create_table :items do
7c0583f @jackdempsey add in demonstration of primary_key method
jackdempsey authored
46 primary_key :id
5193867 @jeremyevans README fixes from jinguoli, thanks!
authored
47 String :name
48 Float :price
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
49 end
50
51 items = DB[:items] # Create a dataset
52
53 # Populate the table
76613bc @jeremyevans Update README.rdoc
authored
54 items.insert(:name => 'abc', :price => rand * 100)
5193867 @jeremyevans README fixes from jinguoli, thanks!
authored
55 items.insert(:name => 'def', :price => rand * 100)
76613bc @jeremyevans Update README.rdoc
authored
56 items.insert(:name => 'ghi', :price => rand * 100)
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
57
58 # Print out the number of records
59 puts "Item count: #{items.count}"
60
61 # Print out the average price
62 puts "The average price is: #{items.avg(:price)}"
63
64 == The Sequel Console
65
76613bc @jeremyevans Update README.rdoc
authored
66 Sequel includes an IRB console for quick access to databases. You can use it like this:
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
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
ce1c70f @jeremyevans Documentation cleanup
authored
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 flexible.
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
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
76613bc @jeremyevans Update README.rdoc
authored
86 Since datasets retrieve records only when needed, they can be stored and later reused. Records are fetched as hashes (or custom model objects), and are accessed using an Enumerable interface:
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
87
88 middle_east = DB[:countries].filter(:region => 'Middle East')
ce1c70f @jeremyevans Documentation cleanup
authored
89 middle_east.order(:name).each{|r| puts r[:name]}
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
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 << "create table t (a text, b text)"
124 DB << "insert into t values ('a', 'b')"
125
126 You can also create datasets based on raw SQL:
127
76613bc @jeremyevans Update README.rdoc
authored
128 dataset = DB['select id from items']
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
129 dataset.count # will return the number of records in the result set
130 dataset.map(:id) # will return an array containing all values of the id column in the result set
131
132 You can also fetch records with raw SQL through the dataset:
133
134 DB['select * from items'].each do |row|
135 p row
136 end
137
ce1c70f @jeremyevans Documentation cleanup
authored
138 You can use placeholders in your SQL string as well:
139
140 DB['select * from items where name = ?', name].each do |row|
141 p row
142 end
143
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
144 === Getting Dataset Instances
145
76613bc @jeremyevans Update README.rdoc
authored
146 Datasets are the primary way records are retrieved and manipulated. They are generally created via the Database#from or Database#[] methods:
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
147
148 posts = DB.from(:posts)
76613bc @jeremyevans Update README.rdoc
authored
149 posts = DB[:posts] # same
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
150
76613bc @jeremyevans Update README.rdoc
authored
151 Datasets will only fetch records when you tell them to. They can be manipulated to filter records, change ordering, join tables, etc..
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
152
153 === Retrieving Records
154
76613bc @jeremyevans Update README.rdoc
authored
155 You can retrieve all records by using the all method:
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
156
157 posts.all
158
159 The all method returns an array of hashes, where each hash corresponds to a record.
160
161 You can also iterate through records one at a time:
162
163 posts.each{|row| p row}
164
165 Or perform more advanced stuff:
166
76613bc @jeremyevans Update README.rdoc
authored
167 names_and_dates = posts.map{|r| [r[:name], r[:date]]}
168 old_posts, recent_posts = posts.partition{|r| r[:date] < Date.today - 7}
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
169
170 You can also retrieve the first record in a dataset:
171
172 posts.first
173
174 Or retrieve a single record with a specific value:
175
176 posts[:id => 1]
177
178 If the dataset is ordered, you can also ask for the last record:
179
180 posts.order(:stamp).last
181
182 === Filtering Records
183
76613bc @jeremyevans Update README.rdoc
authored
184 An easy way to filter records is to provide a hash of values to match:
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
185
186 my_posts = posts.filter(:category => 'ruby', :author => 'david')
187
188 You can also specify ranges:
189
190 my_posts = posts.filter(:stamp => (Date.today - 14)..(Date.today - 7))
191
ce1c70f @jeremyevans Documentation cleanup
authored
192 Or arrays of values:
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
193
194 my_posts = posts.filter(:category => ['ruby', 'postgres', 'linux'])
195
196 Sequel also accepts expressions:
197
ce1c70f @jeremyevans Documentation cleanup
authored
198 my_posts = posts.filter{|o| o.stamp > Date.today << 1}
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
199
76613bc @jeremyevans Update README.rdoc
authored
200 Some adapters will also let you specify Regexps:
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
201
202 my_posts = posts.filter(:category => /ruby/i)
203
204 You can also use an inverse filter:
205
206 my_posts = posts.exclude(:category => /ruby/i)
207
208 You can also specify a custom WHERE clause using a string:
209
210 posts.filter('stamp IS NOT NULL')
211
76613bc @jeremyevans Update README.rdoc
authored
212 You can use parameters in your string, as well:
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
213
214 posts.filter('(stamp < ?) AND (author != ?)', Date.today - 3, author_name)
c4ab71e @jeremyevans Another minor README fix
authored
215 posts.filter{|o| (o.stamp < Date.today - 3) & ~{:author => author_name}} # same as above
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
216
217 Datasets can also be used as subqueries:
218
76613bc @jeremyevans Update README.rdoc
authored
219 DB[:items].filter('price > ?', DB[:items].select{|o| o.avg(:price) + 100})
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
220
221 After filtering you can retrieve the matching records by using any of the retrieval methods:
222
223 my_posts.each{|row| p row}
224
225 See the doc/dataset_filtering.rdoc file for more details.
226
227 === Summarizing Records
228
229 Counting records is easy:
230 posts.filter(:category => /ruby/i).count
231
232 And you can also query maximum/minimum values:
76613bc @jeremyevans Update README.rdoc
authored
233 max = DB[:history].max(:value)
234 min = DB[:history].min(:value)
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
235
76613bc @jeremyevans Update README.rdoc
authored
236 Or calculate a sum or average:
237 sum = DB[:items].sum(:price)
238 avg = DB[:items].avg(:price)
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
239
240 === Ordering Records
241
242 Ordering datasets is simple:
243
244 posts.order(:stamp) # ORDER BY stamp
245 posts.order(:stamp, :name) # ORDER BY stamp, name
ce1c70f @jeremyevans Documentation cleanup
authored
246
247 Chaining order doesn't work the same as filter:
248
249 posts.order(:stamp).order(:name) # ORDER BY name
250
251 The order_more method chains this way, though:
252
253 posts.order(:stamp).order_more(:name) # ORDER BY stamp, name
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
254
76613bc @jeremyevans Update README.rdoc
authored
255 You can also specify descending order:
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
256
257 posts.order(:stamp.desc) # ORDER BY stamp DESC
258
ce1c70f @jeremyevans Documentation cleanup
authored
259 === Selecting Columns
260
261 Selecting specific columns to be returned is also simple:
262
263 posts.select(:stamp) # SELECT stamp FROM posts
264 posts.select(:stamp, :name) # SELECT stamp, name FROM posts
265
266 Chaining select works like order, not filter:
267
268 posts.select(:stamp).select(:name) # SELECT name FROM posts
269
270 As you might expect, there is an order_more equivalent for select:
271
272 posts.select(:stamp).select_more(:name) # SELECT stamp, name FROM posts
273
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
274 === Deleting Records
275
276 Deleting records from the table is done with delete:
277
278 posts.filter('stamp < ?', Date.today - 3).delete
279
76613bc @jeremyevans Update README.rdoc
authored
280 Be very careful when deleting, as delete affects all rows in the dataset.
281 Filter first, delete second, unless you want to empty the table.
282
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
283 === Inserting Records
284
285 Inserting records into the table is done with insert:
286
287 posts.insert(:category => 'ruby', :author => 'david')
288
289 === Updating Records
290
291 Updating records in the table is done with update:
292
293 posts.filter('stamp < ?', Date.today - 7).update(:state => 'archived')
294
76613bc @jeremyevans Update README.rdoc
authored
295 You can reference table columns when choosing what values to set:
296
297 posts.filter{|o| o.stamp < Date.today - 7}.update(:backup_number => :backup_number + 1)
298
299 As with delete, this affects all rows in the dataset, so filter first,
300 update second, unless you want to update all rows.
301
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
302 === Joining Tables
303
76613bc @jeremyevans Update README.rdoc
authored
304 Sequel makes it easy to join tables:
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
305
306 order_items = DB[:items].join(:order_items, :item_id => :id).
307 filter(:order_items__order_id => 1234)
308
309 This is equivalent to the SQL:
310
43f43ba @jeremyevans Fix incorrect SQL in the README
authored
311 SELECT * FROM items INNER JOIN order_items
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
312 ON order_items.item_id = items.id
313 WHERE order_items.order_id = 1234
314
315 You can then do anything you like with the dataset:
316
317 order_total = order_items.sum(:price)
318
319 Which is equivalent to the SQL:
320
43f43ba @jeremyevans Fix incorrect SQL in the README
authored
321 SELECT sum(price) FROM items INNER JOIN order_items
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
322 ON order_items.item_id = items.id
323 WHERE order_items.order_id = 1234
324
325 === Graphing Datasets
326
ce1c70f @jeremyevans Documentation cleanup
authored
327 When retrieving records from joined datasets, you get the results in a single hash, which is subject to clobbering if you have columns with the same name in multiple tables:
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
328
329 DB[:items].join(:order_items, :item_id => :id).first
76613bc @jeremyevans Update README.rdoc
authored
330 => {:id=>order_items.id), :item_id=>order_items.item_id}
778a032 @jeremyevans Combine sequel and sequel_core into one gem
authored
331
332 Using graph, you can split the result hashes into subhashes, one per join:
333
334 DB[:items].graph(:order_items, :item_id => :id).first
335 => {:items=>{:id=>items.id}, :order_items=>{:id=>order_items.id, :item_id=>order_items.item_id}}
336
ce1c70f @jeremyevans Documentation cleanup
authored
337 == An aside: column references in Sequel
338
339 Sequel expects column names to be specified using symbols. In addition, returned hashes always use symbols as their keys. This allows you to freely mix literal values and column references. For example, the two following lines produce equivalent SQL:
340
341 items.filter(:x => 1) #=> "SELECT * FROM items WHERE (x = 1)"
342 items.filter(1 => :x) #=> "SELECT * FROM items WHERE (1 = x)"
343
76613bc @jeremyevans Update README.rdoc
authored
344 Ruby strings are generally treated as SQL strings:
345
346 items.filter(:x => 'x') #=> "SELECT * FROM items WHERE (x = 'x')"
347
ce1c70f @jeremyevans Documentation cleanup
authored
348 === Qualifying column names
349
350 Column references can be qualified by using the double underscore special notation :table__column:
351
352 items.literal(:items__price) #=> "items.price"
353
354 === Column aliases
355
356 You can also alias columns by using the triple undersecore special notation :column___alias or :table__column___alias:
357
358 items.literal(:price___p) #=> "price AS p"
359 items.literal(:items__price___p) #=> "items.price AS p"
360
76613bc @jeremyevans Update README.rdoc
authored
361 Another way to alias columns is to use the #as method:
ce1c70f @jeremyevans Documentation cleanup
authored
362
363 items.literal(:price.as(:p)) #=> "price AS p"
364
a0181eb @jeremyevans Various doc fixes
authored
365 == Sequel Models
3ece811 @ciconia In preparation for 1.0.
ciconia authored
366
76613bc @jeremyevans Update README.rdoc
authored
367 A model class wraps a dataset, and an instance of that class wraps a single record in the dataset.
3ece811 @ciconia In preparation for 1.0.
ciconia authored
368
a0181eb @jeremyevans Various doc fixes
authored
369 Model classes are defined as regular Ruby classes:
370
01a6887 @jeremyevans Fix typo in README, thanks mwlang
authored
371 DB = Sequel.connect('sqlite://blog.db')
a0181eb @jeremyevans Various doc fixes
authored
372 class Post < Sequel::Model
373 end
374
82d2593 @jeremyevans Some additions to sequel/README from the Google Code Wiki
authored
375 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
376
82d2593 @jeremyevans Some additions to sequel/README from the Google Code Wiki
authored
377 Post.table_name #=> :posts
378
379 You can, however, explicitly set the table name or even the dataset used:
380
381 class Post < Sequel::Model(:my_posts)
a0181eb @jeremyevans Various doc fixes
authored
382 end
82d2593 @jeremyevans Some additions to sequel/README from the Google Code Wiki
authored
383 # or:
384 Post.set_dataset :my_posts
76613bc @jeremyevans Update README.rdoc
authored
385
386 If you use a symbol, it assumes you are referring to the table with the same name. You can also give it a dataset:
387
388 Post.set_dataset DB[:my_posts].filter(:category => 'ruby')
389 Post.set_dataset DB[:my_posts].select(:id, :name).order(:date)
82d2593 @jeremyevans Some additions to sequel/README from the Google Code Wiki
authored
390
a0181eb @jeremyevans Various doc fixes
authored
391 === Model instances
3ece811 @ciconia In preparation for 1.0.
ciconia authored
392
76613bc @jeremyevans Update README.rdoc
authored
393 Model instance are identified by a primary key. By default, Sequel assumes the primary key column to be :id, unless it can get the primary key information from the database. The Model.[] method can be used to fetch records by their primary key:
3ece811 @ciconia In preparation for 1.0.
ciconia authored
394
a0181eb @jeremyevans Various doc fixes
authored
395 post = Post[123]
3ece811 @ciconia In preparation for 1.0.
ciconia authored
396
a0181eb @jeremyevans Various doc fixes
authored
397 The Model#pk method is used to retrieve the record's primary key value:
3ece811 @ciconia In preparation for 1.0.
ciconia authored
398
a0181eb @jeremyevans Various doc fixes
authored
399 post.pk #=> 123
3ece811 @ciconia In preparation for 1.0.
ciconia authored
400
a0181eb @jeremyevans Various doc fixes
authored
401 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
402
a0181eb @jeremyevans Various doc fixes
authored
403 class Post < Sequel::Model
404 set_primary_key [:category, :title]
405 end
3ece811 @ciconia In preparation for 1.0.
ciconia authored
406
a0181eb @jeremyevans Various doc fixes
authored
407 post = Post['ruby', 'hello world']
408 post.pk #=> ['ruby', 'hello world']
3ece811 @ciconia In preparation for 1.0.
ciconia authored
409
a0181eb @jeremyevans Various doc fixes
authored
410 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
411
a0181eb @jeremyevans Various doc fixes
authored
412 A model instance can also be fetched by specifying a condition:
3ece811 @ciconia In preparation for 1.0.
ciconia authored
413
a0181eb @jeremyevans Various doc fixes
authored
414 post = Post[:title => 'hello world']
ce1c70f @jeremyevans Documentation cleanup
authored
415 post = Post.find{|o| o.num_comments < 10}
3ece811 @ciconia In preparation for 1.0.
ciconia authored
416
a0181eb @jeremyevans Various doc fixes
authored
417 === Iterating over records
3ece811 @ciconia In preparation for 1.0.
ciconia authored
418
ce1c70f @jeremyevans Documentation cleanup
authored
419 A model class lets you iterate over subsets of records by proxying many methods to the underlying dataset. This means that you can use most of the Dataset API to create customized queries that return model instances, e.g.:
3ece811 @ciconia In preparation for 1.0.
ciconia authored
420
a0181eb @jeremyevans Various doc fixes
authored
421 Post.filter(:category => 'ruby').each{|post| p post}
3ece811 @ciconia In preparation for 1.0.
ciconia authored
422
a0181eb @jeremyevans Various doc fixes
authored
423 You can also manipulate the records in the dataset:
3ece811 @ciconia In preparation for 1.0.
ciconia authored
424
ce1c70f @jeremyevans Documentation cleanup
authored
425 Post.filter{|o| o.num_comments < 7}.delete
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
426 Post.filter(:title.like(/ruby/)).update(:category => 'ruby')
3ece811 @ciconia In preparation for 1.0.
ciconia authored
427
a0181eb @jeremyevans Various doc fixes
authored
428 === Accessing record values
3ece811 @ciconia In preparation for 1.0.
ciconia authored
429
a0181eb @jeremyevans Various doc fixes
authored
430 A model instances stores its values as a hash:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
431
a0181eb @jeremyevans Various doc fixes
authored
432 post.values #=> {:id => 123, :category => 'ruby', :title => 'hello world'}
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
433
a0181eb @jeremyevans Various doc fixes
authored
434 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
435
a0181eb @jeremyevans Various doc fixes
authored
436 post.id #=> 123
437 post.title #=> 'hello world'
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
438
a0181eb @jeremyevans Various doc fixes
authored
439 You can also change record values:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
440
a0181eb @jeremyevans Various doc fixes
authored
441 post.title = 'hey there'
442 post.save
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
443
ce1c70f @jeremyevans Documentation cleanup
authored
444 Another way to change values by using the #update method:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
445
ce1c70f @jeremyevans Documentation cleanup
authored
446 post.update(:title => 'hey there')
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
447
a0181eb @jeremyevans Various doc fixes
authored
448 === Creating new records
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
449
a0181eb @jeremyevans Various doc fixes
authored
450 New records can be created by calling Model.create:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
451
a0181eb @jeremyevans Various doc fixes
authored
452 post = Post.create(:title => 'hello world')
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
453
a0181eb @jeremyevans Various doc fixes
authored
454 Another way is to construct a new instance and save it:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
455
a0181eb @jeremyevans Various doc fixes
authored
456 post = Post.new
457 post.title = 'hello world'
458 post.save
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
459
a0181eb @jeremyevans Various doc fixes
authored
460 You can also supply a block to Model.new and Model.create:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
461
ce1c70f @jeremyevans Documentation cleanup
authored
462 post = Post.create{|p| p.title = 'hello world'}
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
463
a0181eb @jeremyevans Various doc fixes
authored
464 post = Post.new do |p|
465 p.title = 'hello world'
466 p.save
467 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
468
a0181eb @jeremyevans Various doc fixes
authored
469 === Hooks
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
470
76613bc @jeremyevans Update README.rdoc
authored
471 You can execute custom code when creating, updating, or deleting records by defining hook methods. The before_create and after_create hook methods wrap record creation. The before_update and after_update hook methods wrap record updating. The before_save and after_save hook methods wrap record creation and updating. The before_destroy and after_destroy hook methods wrap destruction. The before_validation and after_validation hook methods wrap validation. Example:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
472
a0181eb @jeremyevans Various doc fixes
authored
473 class Post < Sequel::Model
76613bc @jeremyevans Update README.rdoc
authored
474 def after_create
1d2404f @jeremyevans Fix confusing code in after_create example in sequel/README
authored
475 author.increase_post_count
a0181eb @jeremyevans Various doc fixes
authored
476 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
477
76613bc @jeremyevans Update README.rdoc
authored
478 def after_destroy
1d2404f @jeremyevans Fix confusing code in after_create example in sequel/README
authored
479 author.decrease_post_count
a0181eb @jeremyevans Various doc fixes
authored
480 end
481 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
482
76613bc @jeremyevans Update README.rdoc
authored
483 For the example above, you should probably use a database trigger if you can. Hooks can be used for data integrity, but they will only enforce that integrity when you are using the model. If you plan on allowing any other access to the database, it's best to use database triggers for data integrity.
484
a0181eb @jeremyevans Various doc fixes
authored
485 === Deleting records
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
486
76613bc @jeremyevans Update README.rdoc
authored
487 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 hook methods, while #delete does not:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
488
a0181eb @jeremyevans Various doc fixes
authored
489 post.delete #=> bypasses hooks
490 post.destroy #=> runs hooks
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
491
a0181eb @jeremyevans Various doc fixes
authored
492 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
493
a0181eb @jeremyevans Various doc fixes
authored
494 Post.filter(:category => 32).delete #=> bypasses hooks
495 Post.filter(:category => 32).destroy #=> runs hooks
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
496
a0181eb @jeremyevans Various doc fixes
authored
497 Please note that if Model.destroy is called, each record is deleted
498 separately, but Model.delete deletes all relevant records with a single
499 SQL statement.
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
500
a0181eb @jeremyevans Various doc fixes
authored
501 === Associations
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
502
a0181eb @jeremyevans Various doc fixes
authored
503 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
504
a0181eb @jeremyevans Various doc fixes
authored
505 class Post < Sequel::Model
506 many_to_one :author
507 one_to_many :comments
508 many_to_many :tags
509 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
510
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
511 many_to_one creates a getter and setter for each model object:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
512
a0181eb @jeremyevans Various doc fixes
authored
513 class Post < Sequel::Model
514 many_to_one :author
515 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
516
a0181eb @jeremyevans Various doc fixes
authored
517 post = Post.create(:name => 'hi!')
518 post.author = Author[:name => 'Sharon']
519 post.author
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
520
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
521 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
522
a0181eb @jeremyevans Various doc fixes
authored
523 class Post < Sequel::Model
524 one_to_many :comments
525 many_to_many :tags
526 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
527
a0181eb @jeremyevans Various doc fixes
authored
528 post = Post.create(:name => 'hi!')
529 post.comments
530 comment = Comment.create(:text=>'hi')
531 post.add_comment(comment)
532 post.remove_comment(comment)
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
533 post.remove_all_comments
a0181eb @jeremyevans Various doc fixes
authored
534 tag = Tag.create(:tag=>'interesting')
535 post.add_tag(tag)
536 post.remove_tag(tag)
17c0143 @jeremyevans Small documentation updates to the READMEs
authored
537 post.remove_all_tags
a0181eb @jeremyevans Various doc fixes
authored
538
76613bc @jeremyevans Update README.rdoc
authored
539 All associations add a dataset method that can be used to further filter or reorder the returned objects, or modify all of them:
540
541 # Delete all of this post's comments from the database
542 Post.comments_dataset.destroy
543
544 # Return all tags related to this post with no subscribers, ordered by the tag's name
545 Post.tags_dataset.filter(:subscribers=>0).order(:name).all
546
a0181eb @jeremyevans Various doc fixes
authored
547 === Eager Loading
548
549 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
550
a0181eb @jeremyevans Various doc fixes
authored
551 class Person < Sequel::Model
552 one_to_many :posts, :eager=>[:tags]
553 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
554
a0181eb @jeremyevans Various doc fixes
authored
555 class Post < Sequel::Model
556 many_to_one :person
557 one_to_many :replies
558 many_to_many :tags
559 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
560
a0181eb @jeremyevans Various doc fixes
authored
561 class Tag < Sequel::Model
562 many_to_many :posts
563 many_to_many :replies
564 end
565
566 class Reply < Sequel::Model
567 many_to_one :person
568 many_to_one :post
569 many_to_many :tags
570 end
571
572 # Eager loading via .eager
573 Post.eager(:person).all
2b572b9 @jeremyevans In the RDoc, give an example of .eager being used with filters, and d…
authored
574
575 # eager is a dataset method, so it works with filters/orders/limits/etc.
ce1c70f @jeremyevans Documentation cleanup
authored
576 Post.filter{|o| o.topic > 'M'}.order(:date).limit(5).eager(:person).all
a0181eb @jeremyevans Various doc fixes
authored
577
578 person = Person.first
579 # Eager loading via :eager (will eagerly load the tags for this person's posts)
580 person.posts
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
581
a0181eb @jeremyevans Various doc fixes
authored
582 # These are equivalent
583 Post.eager(:person, :tags).all
584 Post.eager(:person).eager(:tags).all
585
586 # Cascading via .eager
587 Tag.eager(:posts=>:replies).all
588
589 # Will also grab all associated posts' tags (because of :eager)
590 Reply.eager(:person=>:posts).all
591
592 # No depth limit (other than memory/stack), and will also grab posts' tags
593 # Loads all people, their posts, their posts' tags, replies to those posts,
594 # the person for each reply, the tag for each reply, and all posts and
595 # replies that have that tag. Uses a total of 8 queries.
596 Person.eager(:posts=>{:replies=>[:person, {:tags=>{:posts, :replies}}]}).all
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
597
76613bc @jeremyevans Update README.rdoc
authored
598 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 or order 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
599
a0181eb @jeremyevans Various doc fixes
authored
600 === Extending the underlying dataset
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
601
a0181eb @jeremyevans Various doc fixes
authored
602 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
603
a0181eb @jeremyevans Various doc fixes
authored
604 class Post < Sequel::Model
605 def self.posts_with_few_comments
ce1c70f @jeremyevans Documentation cleanup
authored
606 filter{|o| o.num_comments < 30}
a0181eb @jeremyevans Various doc fixes
authored
607 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
608
a0181eb @jeremyevans Various doc fixes
authored
609 def self.clean_posts_with_few_comments
610 posts_with_few_comments.delete
611 end
612 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
613
a0181eb @jeremyevans Various doc fixes
authored
614 You can also implement table-wide logic by defining methods on the dataset:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
615
a0181eb @jeremyevans Various doc fixes
authored
616 class Post < Sequel::Model
617 def_dataset_method(:posts_with_few_comments) do
ce1c70f @jeremyevans Documentation cleanup
authored
618 filter{|o| o.num_comments < 30}
a0181eb @jeremyevans Various doc fixes
authored
619 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
620
a0181eb @jeremyevans Various doc fixes
authored
621 def_dataset_method(:clean_posts_with_few_comments) do
622 posts_with_few_comments.delete
623 end
624 end
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
625
a0181eb @jeremyevans Various doc fixes
authored
626 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
627
ce1c70f @jeremyevans Documentation cleanup
authored
628 Post.filter(:category => 'ruby').clean_posts_with_few_comments
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
629
a0181eb @jeremyevans Various doc fixes
authored
630 Sequel models also provide a short hand notation for filters:
de172d9 @ciconia Merged new associations branch into trunk.
ciconia authored
631
a0181eb @jeremyevans Various doc fixes
authored
632 class Post < Sequel::Model
ce1c70f @jeremyevans Documentation cleanup
authored
633 subset(:posts_with_few_comments){|o| o.num_comments < 30}
634 subset :invisible, ~:visible
a0181eb @jeremyevans Various doc fixes
authored
635 end
636
76613bc @jeremyevans Update README.rdoc
authored
637 === Model Validations
638
639 You can define a validate method for your model, which save
640 will check before attempting to save the model in the database.
641 If an attribute of the model isn't valid, you should add a error
642 message for that attribute to the model object's errors. If an
643 object has any errors added by the validate method, save will
644 raise an error or return false depending on how it is configured.
645
646 class Post < Sequel::Model
647 def validate
648 errors[:name] << "can't be empty" if name.empty?
649 errors[:written_on] << "should be in the past" if written_on >= Time.now
a0181eb @jeremyevans Various doc fixes
authored
650 end
651 end
98169b7 @jeremyevans Various small doc fixes
authored
652
Something went wrong with that request. Please try again.