1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537
|
= Sequel for SQL Users
One of the main benefits of Sequel is that it doesn't require the user to know SQL in order to use it, though SQL knowledge is certainly helpful. Unlike most other Sequel documentation, this guide assumes you know SQL, and provides an easy way to discover how to do something in Sequel given the knowledge of how to do so in SQL.
== You Can Just Use SQL
With Sequel, it's very easy to just use SQL for your queries. If learning Sequel's DSL seems like a waste of time, you are certainly free to write all your queries in SQL. Sequel uses a few different methods depending on the type of query you are doing.
=== SELECT
For SELECT queries, you should probably use <tt>Database#fetch</tt> with a string and a block:
DB.fetch("SELECT * FROM albums") do |row|
puts row[:name]
end
<tt>Database#fetch</tt> will take the query you give it, execute it on the database, and yield a hash with column symbol keys for each row returned. If you want to use some placeholder variables, you can set the placeholders with ? and add the arguments to fetch:
DB.fetch("SELECT * FROM albums WHERE name LIKE ?", 'A%') do |row|
puts row[:name]
end
You can also use named placeholders by starting the placeholder with a colon, and using a hash for the argument:
DB.fetch("SELECT * FROM albums WHERE name LIKE :pattern", :pattern=>'A%') do |row|
puts row[:name]
end
This can be helpful for long queries where it is difficult to match the ? with the arguments.
What Sequel actually does internally is two separate things. It first creates a dataset representing the query, and then it executes the dataset's SQL code to retrieve the objects. Often, you want to define a dataset at some point, but not execute it till later. You can do this by leaving off the block, and storing the dataset in a variable:
ds = DB.fetch("SELECT * FROM albums")
Then, when you want to retrieve the rows later, you can call +each+ on the dataset to retrieve the rows:
ds.each{|r| puts r[:name]}
You should note that <tt>Database#[]</tt> calls <tt>Database#fetch</tt> if a string is provided, so you can also do:
ds = DB["SELECT * FROM albums"]
ds.each{|r| puts r[:name]}
However, note that <tt>Database#[]</tt> cannot take a block directly, you have to call +each+ on the returned dataset. There are plenty of other methods besides +each+, one is +all+ which returns all records as an array:
DB["SELECT * FROM albums"].all # [{:id=>1, :name=>'RF', ...}, ...]
=== INSERT, UPDATE, DELETE
INSERT, UPDATE, and DELETE all work the same way. You first create the dataset with the SQL you want to execute using <tt>Database#[]</tt>:
insert_ds = DB["INSERT INTO albums (name) VALUES (?)", 'RF']
update_ds = DB["UPDATE albums SET name = ? WHERE name = ?", 'MO', 'RF']
delete_ds = DB["DELETE FROM albums WHERE name = ?", 'MO']
Then, you call the +insert+, +update+, or +delete+ method on the returned dataset:
insert_ds.insert
update_ds.update
delete_ds.delete
+update+ and +delete+ should return the number of rows affected, and +insert+ should return the autogenerated primary key integer for the row inserted (if any).
=== Other Queries
All other queries such as TRUNCATE, CREATE TABLE, and ALTER TABLE should be executed using <tt>Database#run</tt>:
DB.run "CREATE TABLE albums (id integer primary key, name varchar(255))"
You can also use <tt>Database#<<</tt>:
DB << "ALTER TABLE albums ADD COLUMN copies_sold INTEGER"
=== Other Places
Almost everywhere in Sequel, you can drop down to literal SQL by providing a literal string, which you can create with <tt>String#lit</tt>:
DB[:albums].select('name') # SELECT 'name' FROM albums
DB[:albums].select('name'.lit) # SELECT name FROM albums
So you can use Sequel's DSL everywhere you find it helpful, and fallback to literal SQL if the DSL can't do what you want or you just find literal SQL easier.
== Translating SQL Expressions into Sequel
The rest of this guide assumes you want to use Sequel's DSL to represent your query, that you know how to write the query in SQL, but you aren't sure how to write it in Sequel.
This section will describe how specific SQL expressions are handled in Sequel. The next section will discuss how to create queries by using method chaining on datasets.
=== <tt>Database#literal</tt>
Before we get started, I think it's important to get familiar with the <tt>Database#literal</tt> method, which will return the SQL that will be used for a given expression:
DB.literal(1)
# => "1"
DB.literal(:column)
# => "\"column\""
DB.literal('string')
# => "'string'"
I encourage you to just play around to see how different objects get literalized into SQL
=== Database Loggers
Some Sequel methods handle literalization slightly differently than <tt>Database#literal</tt>. If you want to see all SQL queries that Sequel is sending to the database, you should add a database logger:
DB.loggers << Logger.new($stdout)
Now that you know how to see what SQL is being used, let's jump in and see how to map SQL syntax to Sequel syntax:
=== Identifiers
In Sequel, SQL identifiers are usually specified as ruby symbols:
:column # "column"
As you can see, Sequel quotes identifiers by default. Depending on your database, it may uppercase them by default as well:
:column # "COLUMN" on some databases
A plain symbol is usually treated as an unqualified identifier. However, if you are using multiple tables in a query, and you want to reference a column in one of the tables that has the same name as a column in another one of the tables, you need to qualify that reference. There's two main ways in Sequel to do that. The first is implicit qualification inside the symbol, using the double underscore:
:table__column # "table"."column"
Note that you can't use a period to separate them:
:table.column # calls the column method on the symbol
Also note that specifying the period inside the symbol doesn't work if you are quoting identifiers:
:"table.column" # "table.column"
The other way to qualify an identifier is to use the +qualify+ method on the column symbol with the table symbol:
:column.qualify(:table) # "table"."column"
Another way to generate identifiers is to use Sequel's {virtual row support}[link:files/doc/virtual_rows_rdoc.html]:
DB[:albums].select{name} # SELECT "name" FROM "albums"
DB[:albums].select{albums__name} # SELECT "albums"."name" FROM "albums"
=== Numbers
In general, ruby numbers map directly to SQL numbers:
# Integers
1 # 1
-1 # -1
# Floats
1.5 # 1.5
# BigDecimals
BigDecimal.new('1000000.123091029') # 1000000.123091029
=== Strings
In general, ruby strings map directly to SQL strings:
'name' # 'name'
"name" # 'name'
=== Aliasing
Sequel allows for implicit aliasing in column symbols using the triple underscore:
:column___alias # "column" AS "alias"
You can combine this with implicit qualification:
:table__column___alias # "table"."column" AS "alias"
You can also use the +as+ method on symbols and most Sequel-specific expression objects:
:column.as(:alias) # "column" AS "alias"
:column.qualify(:table).as(:alias) # "table"."column" AS "alias"
=== Functions
The easiest way to use SQL functions is via a virtual row:
DB[:albums].select{function{}} # SELECT function() FROM "albums"
DB[:albums].select{function(col1, col2)} # SELECT function("col1", "col2") FROM "albums"
You can also use the +sql_function+ method on the symbol that contains the function name:
:function.sql_function # function()
:function.sql_function(:col1, :col2) # function("col1", "col2")
=== Aggregate Functions
Aggregate functions work the same way as normal functions, since they share the same syntax:
:sum.sql_function(:column) # sum(column)
However, if you want to use the DISTINCT modifier to an aggregate function, you either have to use literal SQL or a virtual row block:
:sum.sql_function('DISTINCT column'.lit) # sum(DISTINCT column)
DB[:albums].select{sum(:distinct, :column){}} # SELECT sum(DISTINCT column) FROM albums
If you want to use the wildcard as the sole argument of the aggregate function, you again have to use literal SQL or a virtual row block:
:count.sql_function('*'.lit) # count(*)
DB[:albums].select{count(:*){}} # SELECT count(*) FROM albums
Note that Sequel provides helper methods for aggregate functions such as +count+, +sum+, +min+, +max+, +avg+, and +group_and_count+, which handle common uses of aggregate functions.
=== Window Functions
If the database supports window functions, Sequel can handle them using a virtual row block:
DB[:albums].select{function(:over){}}
# SELECT function() OVER () FROM albums
DB[:albums].select{count(:over, :*=>true){}}
# SELECT count(*) OVER () FROM albums
DB[:albums].select{function(:over, :args=>col1, :partition=>col2, :order=>col3){}}
# SELECT function(col1) OVER (PARTITION BY col2 ORDER BY col3) FROM albums
DB[:albums].select{function(:over, :args=>[c1, c2], :partition=>[c3, c4], :order=>[c5, c6]){}}
# SELECT function(c1, c2) OVER (PARTITION BY c3, c4 ORDER BY c5, c6) FROM albums
=== Equality Operator (=)
Sequel uses hashes to specify equality:
{:column=>1} # ("column" = 1)
You can also specify this as an array of two element arrays:
[[:column, 1]] # ("column" = 1)
=== Not Equal Operator (!=)
You can specify a not equals condition by inverting the hash or array of two element arrays using +sql_negate+ or ~:
{:column => 1}.sql_negate # ("column" != 1)
[[:column, 1]].sql_negate # ("column" != 1)
~{:column => 1} # ("column" != 1)
~[[:column, 1]] # ("column" != 1)
The most common need for not equals is in filters, in which case you can use the +exclude+ method:
DB[:albums].exclude(:column=>1) # SELECT * FROM "albums" WHERE ("column" != 1)
=== Inclusion and Exclusion Operators (IN, NOT IN)
Sequel also uses hashes to specify inclusion, and inversions of those hashes to specify exclusion:
{:column=>[1, 2, 3]} # ("column" IN (1, 2, 3))
~{:column=>[1, 2, 3]} # ("column" NOT IN (1, 2, 3))
As you may have guessed, Sequel switches from an = to an IN when the hash value is an array. It also does this for datasets, which easily allows you to test for inclusion and exclusion in a subselect:
{:column=>DB[:albums].select(:id)} # ("column" IN (SELECT "id" FROM "albums"))
~{:column=>DB[:albums].select(:id)} # ("column" NOT IN (SELECT "id" FROM "albums"))
Sequel also supports the SQL EXISTS operator using <tt>Dataset#exists</tt>:
DB[:albums].exists # EXISTS (SELECT * FROM albums)
=== Identity Operators (IS, IS NOT)
Hashes in Sequel use IS if the value is true, false, or nil:
{:column=>nil) # ("column" IS NULL)
{:column=>true) # ("column" IS TRUE)
{:column=>false) # ("column" IS FALSE)
Negation works the same way as it does for equality and inclusion:
{:column=>nil).sql_negate # ("column" IS NOT NULL)
{:column=>true).sql_negate # ("column" IS NOT TRUE)
{:column=>false).sql_negate # ("column" IS NOT FALSE)
=== Inversion Operator (NOT)
Sequel's general inversion operator is ~, which works on symbols and most Sequel-specific expression objects:
~:column # NOT "column"
Note that ~ will actually apply the inversion operation to the underlying object, which is why
~{:column=>1}
produces <tt>(column != 1)</tt> instead of <tt>NOT (column = 1)</tt>.
=== Inequality Operators (< > <= >=)
Sequel defines the inequality operators directly on most Sequel-specific expression objects:
:column.qualify(:table) > 1 # ("table"."column" > 1)
:column.qualify(:table) < 1 # ("table"."column" < 1)
:function.sql_function >= 1 # (function() >= 1)
:function.sql_function(:column) <= 1 # (function("column") <= 1)
If you want to use them on a symbol, you should call +identifier+ on the symbol:
:column.identifier > 1 # ("column" > 1)
A common use of virtual rows is to handle inequality operators:
DB[:albums].filter{col1 > col2} # SELECT * FROM "albums" WHERE ("col1" > "col2")
=== Standard Mathematical Operators (+ - * /)
The standard mathematical operates are defined on symbol and most Sequel-specific expression objects:
:column + 1 # "column" + 1
:table__column - 1 # "table"."column" - 1
:column.qualify(:table) * 1 # "table"."column" * 1
:column / 1 # "column" / 1
Note that the following does not work:
1 + :column # raises TypeError
For commutative operates such as + and *, this isn't a problem as you can just reorder, but non-commutative operators such as - and / cannot be expressed directly. However, Sequel comes with an +sql_expr+ extension that adds an +sql_expr+ method to all objects, allowing you to do:
Sequel.extension :sql_expr
1.sql_expr / :column # (1 / "column")
=== Boolean Operators (AND OR)
Sequel defines the & and | methods on symbols, hashes, and most Sequel-specific expression objects to handle AND and OR:
:column1 & :column2 # ("column1" AND "column2")
{:column1=>1} | {:column2=>2} # (("column1" = 1) OR ("column2" = 2))
(:function.sql_function > 1) & :column3 # ((function() > 1) AND "column3")
Note the use of parentheses in the last statement. If you omit them, you won't get what you expect:
:function.sql_function > 1 & :column3 # (function() > 1)
This is because & has higher precedence than >, so it is parsed as:
:function.sql_function > (1 & :column3)
In this case, <tt>:column3.to_int</tt> returns an odd integer, so:
1 & :column3 # => 1
You can use hashes and arrays of two element arrays to specify AND and OR with equality conditions:
{:column1=>1, :column2=>2} # (("column1" = 1) AND ("column2" = 2))
[[:column1, 1], [:column2, 2]] # (("column1" = 1) AND ("column2" = 2))
As you can see, these literalize with ANDs by default. You can use the +sql_or+ method to use OR instead:
{:column1=>1, :column2=>2}.sql_or # (("column1" = 1) OR ("column2" = 2))
You've already seen the +sql_negate+ method, which will use ANDs if multiple entries are used:
{:column1=>1, :column2=>2}.sql_negate # (("column1" != 1) AND ("column2" != 2))
To negate while using ORs, the ~ operator can be used:
~{:column1=>1, :column2=>2} # (("column1" != 1) OR ("column2" != 2))
Note that <tt>Dataset#exclude</tt> uses ~, not +sql_negate+:
DB[:albums].exclude(:column1=>1, :column2=>2) # SELECT * FROM "albums" WHERE (("column" != 1) OR ("column2" != 2))
=== Casts
Casting in Sequel is done with the +cast+ method, which is available on strings, symbols, and most of the Sequel-specific expression objects:
:name.cast(:text) # CAST("name" AS text)
'1'.cast(:integer) # CAST('1' AS integer)
:column.qualify(:table).cast(:date) # CAST("table"."column" AS date)
=== Bitwise Mathematical Operators (& | ^ << >> ~)
Sequel allows the use of bitwise mathematical operators on Sequel::SQL::NumericExpression objects:
:number + 1 # => #<Sequel::SQL::NumericExpression ...>
(:number + 1) & 5 # (("number" + 1) & 5)
As you can see, when you use the + operator on a symbol, you get a NumericExpression. You can turn a symbol into a NumericExpression using +sql_number+:
:number.sql_number | 5 # ("number" | 5)
+sql_number+ also works on the many other Sequel-specific expression objects:
:function.sql_function.sql_number << 7 # (function() << 7)
:name.cast(:integer).sql_number >> 8 # (CAST("name" AS integer) >> 8)
Sequel allows you to do the cast and conversion at the same time via +cast_numeric+:
:name.cast_numeric ^ 9 # (CAST("name" AS integer) ^ 9)
Note that &, |, and ~ are already defined to do AND, OR, and NOT on most objects, so if you want to use the bitwise operators, you need to make sure that they are converted first:
~:name # NOT "name"
~:name.sql_number # ~"name"
=== String Operators (||, LIKE, Regexp)
Sequel allows the use of the string concatenation operator on Sequel::SQL::StringExpression objects, which can be created using the +sql_string+ method:
:name.sql_string + ' - Name' # ("name" || ' - Name')
Just like for the bitwise operators, Sequel allows you do do the cast and conversion at the same time via +cast_string+:
:number.cast_string + ' - Number' # (CAST(number AS varchar(255)) || ' - Number')
Note that similar to the mathematical operators, you cannot switch the order the expression and have it work:
'Name - ' + :name.sql_string # raises TypeError
Just like for the mathematical operators, you can use the +sql_expr+ extension to work around this:
Sequel.extension :sql_expr
'Name - '.sql_expr + :name # ('Name - ' || "name")
Sequel also adds an <tt>Array#sql_string_join</tt> method, which concatenates all of the elements in the array:
['Name', :name].sql_string_join # ('Name' || "name")
Just like ruby's <tt>String#join</tt>, you can provide an argument for a string used to join each element:
['Name', :name].sql_string_join(' - ') # ('Name' || ' - ' || "name")
For the LIKE operator, Sequel defines the +like+ and +ilike+ methods on symbol and most Sequel-specific expression objects:
:name.like('A%') # ("name" LIKE 'A%')
:name.qualify.ilike('A%') # ("name" ILIKE 'A%')
Note the above syntax, while Sequel's default, is specific to PostgreSQL. However, most other adapters override the behavior. For example, on MySQL, Sequel uses LIKE BINARY for +like+, and LIKE for +ilike+. If the database supports both case sensitive and case insensitive LIKE, then +like+ will use a case sensitive LIKE, and +ilike+ will use a case insensitive LIKE. Some databases only support case insensitive behavior, in which case +like+ and +ilike+ will act identically.
Inverting the LIKE operator works like other inversions:
~:name.like('A%') # ("name" NOT LIKE 'A%')
Sequel also supports SQL regular expressions on MySQL and PostgreSQL. You can use these by passing a ruby regular expression to the +like+ or +ilike+ method, or by making the regular expression a hash value:
:name.like(/^A/) # ("name" ~ '^A')
~:name.ilike(/^A/) # ("name" !~* '^A')
{:name=>/^A/i} # ("name" ~* '^A')
~{:name=>/^A/} # ("name" !~ '^A')
Note that using +ilike+ with a regular expression will always make the regexp case insensitive. If you use +like+ or the hash with regexp value, it will only be case insensitive if the Regexp itself is case insensitive.
=== Order Specifications (ASC, DESC)
Sequel supports specifying ascending or descending order using the +asc+ and +desc+ method on symbols and most Sequel-specific expression objects:
:column.asc # "column" ASC
:column.qualify(:table).desc # "table"."column" DESC
=== All Columns (.*)
To select all columns in a table, Sequel supports the * method on symbols without an argument:
:table.* # "table".*
=== CASE statements
Sequel allows the easy production of SQL CASE statements using the +case+ method of hashes and arrays of two element arrays. The argument to +case+ is the default value, the keys of the hash (or first element in each array) is the WHEN condition, and the values of the hash (or second element in each array) is the THEN result. Here are some examples:
{:column=>1}.case(0) # (CASE WHEN "column" THEN 1 ELSE 0 END)
[[column, 1]].case(0) # (CASE WHEN "column" THEN 1 ELSE 0 END)
{{:column=>nil}=>1}.case(0) # (CASE WHEN (column IS NULL) THEN 1 ELSE 0 END)
If the hash or array has multiple arguments, multiple WHEN clauses are used:
{:c=>1, :d=>2}.case(0) # (CASE WHEN "c" THEN 1 WHEN "d" THEN 2 ELSE 0 END)
[[:c, 1], [:d, 2]].case(0) # (CASE WHEN "c" THEN 1 WHEN "d" THEN 2 ELSE 0 END)
If you provide a 2nd argument to CASE, it goes between CASE and WHEN:
{2=>1, 3=>5}.case(0, :column) # (CASE column WHEN 2 THEN 1 WHEN 3 THEN 5 ELSE 0 END)
=== Subscripts/Array Access ([])
Sequel supports SQL subscripts using the +sql_subscript+ method on symbols and most Sequel-specific expression objects:
:column.sql_subscript(3) # column[3]
:column.qualify(:table).sql_subscript(3) # table.column[3]
Just like in SQL, you can use any expression as a subscript:
:column.sql_subscript(:function.sql_function) # column[function()]
== Building Queries in Sequel
In Sequel, the SQL queries are build with method chaining.
=== Creating Datasets
You generally start by creating a dataset by calling <tt>Dataset#[]</tt> with a symbol specifying the table name:
DB[:albums] # SELECT * FROM albums
If you want to select from multiple FROM tables, use multiple arguments:
DB[:albums, :artists] # SELECT * FROM albums, artists
If you don't want to select from any FROM tables, use no arguments:
DB[] # SELECT *
=== Chaining Methods
Once you have your dataset object, you build queries by chaining methods, usually with one method per clause in the query:
DB[:albums].select(:id, :name).where(:name.like('A%')).order(:name)
# SELECT id, name FROM albums WHERE (name LIKE 'A%') ORDER BY name
Note that the order of your method chain is not usually important unless you have multiple methods that affect the same clause:
DB[:albums].order(:name).where(:name.like('A%')).select(:id, :name)
# SELECT id, name FROM albums WHERE (name LIKE 'A%') ORDER BY name
=== Using the Same Dataset for SELECT, INSERT, UPDATE, and DELETE
Also note that while the SELECT clause is displayed when you look at a dataset, a Sequel dataset can be used for INSERT, UPDATE, and DELETE as well. Here's an example:
ds = DB[:albums]
ds.all # SELECT * FROM albums
ds.insert(:name=>'RF') # INSERT INTO albums (name) VALUES ('RF')
ds.update(:name=>'RF') # UPDATE albums SET name = 'RF'
ds.delete # DELETE FROM albums
In general, the +insert+, +update+, and +delete+ methods use the appropriate clauses you defined on the dataset:
ds = DB[:albums].filter(:id=>1)
ds.all # SELECT * FROM albums WHERE (id = 1)
ds.insert(:name=>'RF') # INSERT INTO albums (name) VALUES ('RF')
ds.update(:name=>'RF') # UPDATE albums SET name = 'RF' WHERE (id = 1)
ds.delete # DELETE FROM albums WHERE (id = 1)
Note how +update+ and +delete+ used the +filter+ argument, but that +insert+ did not, because INSERT doesn't use a WHERE clause.
=== Methods Used for Each SQL Clause
To see which methods exist that affect each SQL clause, see the {"Dataset Basics" guide}[link:files/doc/dataset_basics_rdoc.html].
|