sequel.md

   1 ---
   2 title: Sequel
   3 category: Ruby libraries
   4 layout: 2017/sheet
   5 ---
   6
   7 ### About
   8
   9 {: .-intro}
  10
  11 Sequel is a database toolkit for Ruby.
  12
  13 - <https://github.com/jeremyevans/sequel>
  14
  15 ### Open a database
  16
  17 ```
  18 require 'rubygems'
  19 require 'sequel'
  20
  21 DB = Sequel.sqlite('my_blog.db')
  22 DB = Sequel.connect('postgres://user:password@localhost/my_db')
  23 DB = Sequel.postgres('my_db', :user => 'user', :password => 'password', :host => 'localhost')
  24 DB = Sequel.ado('mydb')
  25 ```
  26
  27 ### Open an SQLite memory database
  28
  29 Without a filename argument, the sqlite adapter will setup a new sqlite database in memory.
  30
  31 ```
  32 DB = Sequel.sqlite
  33 ```
  34
  35 ### Logging SQL statements
  36
  37 ```
  38 require 'logger'
  39 DB = Sequel.sqlite '', :loggers => [Logger.new($stdout)]
  40 # or
  41 DB.loggers << Logger.new(...)
  42 ```
  43
  44 ### Using raw SQL
  45
  46 ```
  47 DB.run "CREATE TABLE users (name VARCHAR(255) NOT NULL, age INT(3) NOT NULL)"
  48 dataset = DB["SELECT age FROM users WHERE name = ?", name]
  49 dataset.map(:age)
  50 DB.fetch("SELECT name FROM users") do |row|
  51   p row[:name]
  52 end
  53 ```
  54
  55 ### Create a dataset
  56
  57 ```
  58 dataset = DB[:items]
  59 dataset = DB.from(:items)
  60 ```
  61
  62 ### Most dataset methods are chainable
  63
  64 ```
  65 dataset = DB[:managers].where(:salary => 5000..10000).order(:name, :department)
  66 ```
  67
  68 ### Insert rows
  69
  70 ```
  71 dataset.insert(:name => 'Sharon', :grade => 50)
  72 ```
  73
  74 ### Retrieve rows
  75
  76 ```
  77 dataset.each{|r| p r}
  78 dataset.all # => [{...}, {...}, ...]
  79 dataset.first # => {...}
  80 ```
  81
  82 ### Update/Delete rows
  83
  84 ```
  85 dataset.filter(~:active).delete
  86 dataset.filter('price < ?', 100).update(:active => true)
  87 ```
  88
  89 ### Datasets are Enumerable
  90
  91 ```
  92 dataset.map{|r| r[:name]}
  93 dataset.map(:name) # same as above
  94
  95 dataset.inject(0){|sum, r| sum + r[:value]}
  96 dataset.sum(:value) # same as above
  97 ```
  98
  99 ### Filtering (see also doc/dataset_filtering.rdoc)
 100
 101 #### Equality
 102
 103 ```
 104 dataset.filter(:name => 'abc')
 105 dataset.filter('name = ?', 'abc')
 106 ```
 107
 108 #### Inequality
 109
 110 ```
 111 dataset.filter{value > 100}
 112 dataset.exclude{value <= 100}
 113 ```
 114
 115 #### Inclusion
 116
 117 ```
 118 dataset.filter(:value => 50..100)
 119 dataset.where{(value >= 50) & (value <= 100)}
 120
 121 dataset.where('value IN ?', [50,75,100])
 122 dataset.where(:value=>[50,75,100])
 123
 124 dataset.where(:id=>other_dataset.select(:other_id))
 125 ```
 126
 127 #### Subselects as scalar values
 128
 129 ```
 130 dataset.where('price > (SELECT avg(price) + 100 FROM table)')
 131 dataset.filter{price > dataset.select(avg(price) + 100)}
 132 ```
 133
 134 #### LIKE/Regexp
 135
 136 ```
 137 DB[:items].filter(:name.like('AL%'))
 138 DB[:items].filter(:name => /^AL/)
 139 ```
 140
 141 #### AND/OR/NOT
 142
 143 ```
 144 DB[:items].filter{(x > 5) & (y > 10)}.sql
 145 # SELECT * FROM items WHERE ((x > 5) AND (y > 10))
 146
 147 DB[:items].filter({:x => 1, :y => 2}.sql_or & ~{:z => 3}).sql
 148 # SELECT * FROM items WHERE (((x = 1) OR (y = 2)) AND (z != 3))
 149 ```
 150
 151 #### Mathematical operators
 152
 153 ```
 154 DB[:items].filter((:x + :y) > :z).sql
 155 # SELECT * FROM items WHERE ((x + y) > z)
 156
 157 DB[:items].filter{price - 100 < avg(price)}.sql
 158 # SELECT * FROM items WHERE ((price - 100) < avg(price))
 159 ```
 160
 161 ### Ordering
 162
 163 ```
 164 dataset.order(:kind)
 165 dataset.reverse_order(:kind)
 166 dataset.order(:kind.desc, :name)
 167 ```
 168
 169 ### Limit/Offset
 170
 171 ```
 172 dataset.limit(30) # LIMIT 30
 173 dataset.limit(30, 10) # LIMIT 30 OFFSET 10
 174 ```
 175
 176 ### Joins
 177
 178 ```
 179 DB[:items].left_outer_join(:categories, :id => :category_id).sql
 180 # SELECT * FROM items LEFT OUTER JOIN categories ON categories.id = items.category_id
 181
 182 DB[:items].join(:categories, :id => :category_id).join(:groups, :id => :items__group_id)
 183 # SELECT * FROM items INNER JOIN categories ON categories.id = items.category_id INNER JOIN groups ON groups.id = items.group_id
 184 ```
 185
 186 ### Aggregate functions methods
 187
 188 ```
 189 dataset.count #=> record count
 190 dataset.max(:price)
 191 dataset.min(:price)
 192 dataset.avg(:price)
 193 dataset.sum(:stock)
 194
 195 dataset.group_and_count(:category)
 196 dataset.group(:category).select(:category, :AVG.sql_function(:price))
 197 ```
 198
 199 ### SQL Functions / Literals
 200
 201 ```
 202 dataset.update(:updated_at => :NOW.sql_function)
 203 dataset.update(:updated_at => 'NOW()'.lit)
 204
 205 dataset.update(:updated_at => "DateValue('1/1/2001')".lit)
 206 dataset.update(:updated_at => :DateValue.sql_function('1/1/2001'))
 207 ```
 208
 209 ### Schema Manipulation
 210
 211 ```
 212 DB.create_table :items do
 213   primary_key :id
 214   String :name, :unique => true, :null => false
 215   TrueClass :active, :default => true
 216   foreign_key :category_id, :categories
 217   DateTime :created_at
 218
 219   index :created_at
 220 end
 221
 222 DB.drop_table :items
 223
 224 DB.create_table :test do
 225   String :zipcode
 226   enum :system, :elements => ['mac', 'linux', 'windows']
 227 end
 228 ```
 229
 230 ### Aliasing
 231
 232 ```
 233 DB[:items].select(:name.as(:item_name))
 234 DB[:items].select(:name___item_name)
 235 DB[:items___items_table].select(:items_table__name___item_name)
 236 # SELECT items_table.name AS item_name FROM items AS items_table
 237 ```
 238
 239 ### Transactions
 240
 241 ```
 242 DB.transaction do
 243   dataset.insert(:first_name => 'Inigo', :last_name => 'Montoya')
 244   dataset.insert(:first_name => 'Farm', :last_name => 'Boy')
 245 end # Either both are inserted or neither are inserted
 246 ```
 247
 248 Database#transaction is re-entrant:
 249
 250 ```
 251 DB.transaction do # BEGIN issued only here
 252   DB.transaction
 253     dataset << {:first_name => 'Inigo', :last_name => 'Montoya'}
 254   end
 255 end # COMMIT issued only here
 256 ```
 257
 258 Transactions are aborted if an error is raised:
 259
 260 ```
 261 DB.transaction do
 262   raise "some error occurred"
 263 end # ROLLBACK issued and the error is re-raised
 264 ```
 265
 266 Transactions can also be aborted by raising Sequel::Rollback:
 267
 268 ```
 269 DB.transaction do
 270   raise(Sequel::Rollback) if something_bad_happened
 271 end # ROLLBACK issued and no error raised
 272 ```
 273
 274 Savepoints can be used if the database supports it:
 275
 276 ```
 277 DB.transaction do
 278   dataset << {:first_name => 'Farm', :last_name => 'Boy'} # Inserted
 279   DB.transaction(:savepoint=>true) # This savepoint is rolled back
 280     dataset << {:first_name => 'Inigo', :last_name => 'Montoya'} # Not inserted
 281     raise(Sequel::Rollback) if something_bad_happened
 282   end
 283   dataset << {:first_name => 'Prince', :last_name => 'Humperdink'} # Inserted
 284 end
 285 ```
 286
 287 ### Miscellaneous:
 288
 289 ```
 290 dataset.sql # "SELECT * FROM items"
 291 dataset.delete_sql # "DELETE FROM items"
 292 dataset.where(:name => 'sequel').exists # "EXISTS ( SELECT * FROM items WHERE name = 'sequel' )"
 293 dataset.columns #=> array of columns in the result set, does a SELECT
 294 DB.schema(:items) => [[:id, {:type=>:integer, ...}], [:name, {:type=>:string, ...}], ...]
 295 ```
 296
 297 ---
 298
 299 ### Documents
 300
 301 ```
 302 http://sequel.rubyforge.org/rdoc/files/doc/association_basics_rdoc.html
 303 http://sequel.rubyforge.org/rdoc/classes/Sequel/Schema/Generator.html
 304 http://sequel.rubyforge.org/rdoc/files/doc/validations_rdoc.html
 305 http://sequel.rubyforge.org/rdoc/classes/Sequel/Model.html
 306 ```
 307
 308 ### Alter table
 309
 310 ```
 311 database.alter_table :deals do
 312   add_column :name, String
 313   drop_column :column_name
 314   rename_column :from, :to
 315
 316   add_constraint :valid_name, :name.like('A%')
 317   drop_constraint :constraint
 318
 319   add_full_text_index :body
 320   add_spacial_index [columns]
 321
 322   add_index :price
 323   drop_index :index
 324
 325   add_foreign_key :artist_id, :table
 326   add_primary_key :id
 327   add_unique_constraint [columns]
 328   set_column_allow_null :foo, false
 329   set_column_default :title, ''
 330
 331   set_column_type :price, 'char(10)'
 332 end
 333 ```
 334
 335 ### Model associations
 336
 337 ```
 338 class Deal < Sequel::Model
 339
 340   # Us (left) <=> Them (right)
 341   many_to_many  :images,
 342     left_key:   :deal_id,
 343     right_key:  :image_id,
 344     join_table: :image_links
 345
 346   one_to_many   :files,
 347     key:        :deal_id,
 348     class:      :DataFile,
 349
 350   many_to_one   :parent, class: self
 351   one_to_many   :children, key: :parent_id, class: self
 352
 353   one_to_many :gold_albums, class: :Album do |ds|
 354     ds.filter { copies_sold > 50000 }
 355   end
 356 ```
 357
 358 Provided by many_to_many
 359
 360 ```
 361 Deal[1].images
 362 Deal[1].add_image
 363 Deal[1].remove_image
 364 Deal[1].remove_all_images
 365 ```
 366
 367 ### Validations
 368
 369 ```
 370 def validate
 371     super
 372     errors.add(:name, 'cannot be empty') if !name || name.empty?
 373
 374     validates_presence [:title, :site]
 375     validates_unique :name
 376     validates_format /\Ahttps?:\/\//, :website, :message=>'is not a valid URL'
 377     validates_includes %w(a b c), :type
 378     validates_integer :rating
 379     validates_numeric :number
 380     validates_type String, [:title, :description]
 381
 382     validates_integer :rating  if new?
 383
 384     # options: :message =>, :allow_nil =>, :allow_blank =>,
 385     #          :allow_missing =>,
 386
 387     validates_exact_length 17, :isbn
 388     validates_min_length 3, :name
 389     validates_max_length 100, :name
 390     validates_length_range 3..100, :name
 391
 392     # Setter override
 393     def filename=(name)
 394       @values[:filename] = name
 395     end
 396   end
 397 end
 398
 399 deal.errors
 400 ```
 401
 402 ### Model stuff
 403
 404 ```
 405 deal = Deal[1]
 406 deal.changed_columns
 407 deal.destroy  # Calls hooks
 408 deal.delete   # No hooks
 409 deal.exists?
 410 deal.new?
 411 deal.hash  # Only uniques
 412 deal.keys  #=> [:id, :name]
 413 deal.modified!
 414 deal.modified?
 415
 416 deal.lock!
 417 ```
 418
 419 ### Callbacks
 420
 421 ```
 422 before_create
 423 after_create
 424
 425 before_validation
 426 after_validation
 427 before_save
 428 before_update
 429 UPDATE QUERY
 430 after_update
 431 after_save
 432
 433 before_destroy
 434 DELETE QUERY
 435 after_destroy
 436 ```
 437
 438 ### Schema
 439
 440 ```
 441 class Deal < Sequel::Model
 442   set_schema do
 443     primary_key :id
 444     primary_key [:id, :title]
 445     String :name, primary_key: true
 446
 447     String  :title
 448     Numeric :price
 449     DateTime :expires
 450
 451     unique :whatever
 452     check(:price) { num > 0 }
 453
 454     foreign_key :artist_id
 455     String :artist_name, key: :id
 456
 457     index :title
 458     index [:artist_id, :name]
 459     full_text_index :title
 460
 461     # String, Integer, Fixnum, Bignum, Float, Numeric, BigDecimal,
 462     # Date, DateTime, Time, File, TrueClass, FalseClass
 463   end
 464 end
 465 ```
 466
 467 ### Unrestrict primary key
 468
 469 ```
 470 Category.create id: 'travel'   # error
 471 Category.unrestrict_primary_key
 472 Category.create id: 'travel'   # ok
 473 ```