sequel.md

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