Path: | doc/release_notes/3.34.0.txt |
Last Update: | Wed May 15 17:48:08 +0000 2013 |
If you are using arrays in model objects, you need to load support for that:
DB.extend Sequel::Postgres::PGArray::DatabaseMethods
This makes schema parsing and typecasting of array columns work correctly.
This extension also allows you to use PGArray objects and arrays in bound variables when using the postgres adapter with pg.
If you are using hstores in model objects, you need to load support for that:
DB.extend Sequel::Postgres::HStore::DatabaseMethods
This makes schema parsing and typecasting of hstore columns work correctly.
This extension also allows you to use HStore objects and hashes in bound variables when using the postgres adapter with pg.
a = :array_column.pg_array a[1] # array_column[1] a[1][2] # array_column[1][2] a.push(1) # array_column || 1 a.unshift(1) # 1 || array_column a.any # ANY(array_column) a.join # array_to_string(array_column, '', NULL)
If you are also using the pg_array extension, you can turn a PGArray object into a query object, which allows you to run operations on array literals:
a = [1, 2].pg_array.op a.push(3) # ARRAY[1,2] || 3
h = :hstore_column.hstore h['a'] # hstore_column -> 'a' h.has_key?('a') # hstore_column ? 'a' h.keys # akeys(hstore_column) h.to_array # hstore_to_array(hstore_column)
If you are also using the pg_hstore extension, you can turn an HStore object into a query object, which allows you to run operations on hstore literals:
h = {'a' => 'b'}.hstore.op h[a] # '"a"=>"b"'::hstore -> 'a'
DB[:table].where(:column=>1)
and do:
SELECT * FROM table WHERE column = $1; -- [1]
Note that automatically parameterizing queries is not generally faster unless the bound variables are large (i.e. long text/bytea values). Also, there are multiple corner cases when automatically parameterizing queries, though most can be worked around by adding explicit casts.
Note that these two extensions do not necessarily improve performance. For simple queries, they actually hurt performance. They do help for complex queries, but in all cases, it‘s faster to use Sequel‘s prepared statements API manually.
DB[:table].select('a, b, ?' 2).group('a, b').order('c') # Without query_literals: # SELECT 'a, b, ?', 2 FROM table GROUP BY 'a, b' ORDER BY 'c' # With query_literals: # SELECT a, b, 2 FROM table GROUP BY a, b ORDER BY c
Sequel‘s default handling in this case is to use literal strings, which is generally not desired and on some databases not even valid syntax. In general, you‘ll probably want to use this extension for all of a database‘s datasets, which you can do via:
Sequel.extension :query_literals DB.extend_datasets(Sequel::QueryLiterals)
The next major version of Sequel (4.0.0) will probably integrate this extension into the core library.
ds = DB[:table] # Assume table has columns a, b, and c ds.select_remove(:c) # SELECT a, b FROM table # Removal by column alias ds.select(:a, :b___c, :c___b).select_remove(:c) # SELECT a, c AS b FROM table # Removal by expression ds.select(:a, :b___c, :c___b).select_remove(:c___b) # SELECT a, b AS c FROM table
This method makes it easier to select all columns except for the columns given. This is common in cases where a table has a few large columns that are expensive to retrieve. This method does have some corner cases, so read the documentation before using it.
bin/sequel -S has been added to dump the schema for the given database to a file, and DB.load_schema_cache(filename) can be used to populate the schema cache inside your application. This should be done after creating the Database object but before loading your model files.
Note that Sequel does no checking to ensure that the cached schema currently reflects the state of the database. That is up to the application.
The statically cached model instances are frozen so they are not accidently modified.
:column.as(:alias) -> Sequel.as(:column, :alias) :column.asc -> Sequel.asc(:column) :column.desc -> Sequel.desc(:column) :column.cast(Integer) -> Sequel.cast(:column, Integer) :column.cast_numeric -> Sequel.cast_numeric(:column) :column.cast_string -> Sequel.cast_string(:column) :column.extract(:year) -> Sequel.extract(:year, :column) :column.identifier -> Sequel.identifier(:column) :column.ilike('A%') -> Sequel.ilike(:column, 'A%') :column.like('A%') -> Sequel.like(:column, 'A%') :column.qualify(:table) -> Sequel.qualify(:table, :column) :column.sql_subscript(1) -> Sequel.subscript(:column, 1) :function.sql_function(1) -> Sequel.function(:function, 1) 'some SQL'.lit -> Sequel.lit('some SQL') 'string'.to_sequel_blob -> Sequel.blob('string') {:a=>1}.case(0) -> Sequel.case({:a=>1}, 0) {:a=>1}.sql_negate -> Sequel.negate(:a=>1) {:a=>1}.sql_or -> Sequel.or(:a=>1) [[1, 2]].sql_value_list -> Sequel.value_list([[1, 2]]) [:a, :b].sql_string_join -> Sequel.join([:a, :b]) ~{:a=>1} -> Sequel.~(:a=>1) :a + 1 -> Sequel.+(:a, 1) :a - 1 -> Sequel.-(:a, 1) :a * 1 -> Sequel.*(:a, 1) :a / 1 -> Sequel./(:a, 1) :a & 1 -> Sequel.&(:a, 1) :a | 1 -> Sequel.|(:a, 1)
1.sql_expr -> Sequel.expr(1)
The sql_expr extension now just has Object#sql_expr call Sequel.expr.
select{|o| o.+(1, :a)} # SELECT (1 + a)
the standard inequality operators:
where{|o| o.>(2, :a)} # WHERE (2 > a)
and the standard boolean operators:
where{|o| o.&({:a=>1}, o.~(:b=>1))} # WHERE ((a = 1) AND (b != 1))
Additionally, there is now direct support for creating literal strings in instance_evaled virtual row blocks using `:
where{a > `some crazy SQL`} # WHERE (a > some crazy SQL)
This doesn‘t override Kernel.`, since virtual rows use a BasicObject subclass. Previously, using ` would result in calling the SQL function named ` with the given string, which probably isn‘t valid syntax on most databases.
:columns : | An array of columns in the given table |
:table : | The table referenced by the columns |
:key : | An array of columns referenced (in the table specified by :table), but can be nil on certain adapters if the primary key is referenced. |
The hash may also contain entries for:
:deferrable : | Whether the constraint is deferrable |
:name : | The name of the constraint |
:on_delete : | The action to take ON DELETE |
:on_update : | The action to take ON UPDATE |
In case there is a circular dependency, Sequel breaks the dependency and adds separate foreign key constraints at the end of the migration. However, when a circular dependency is broken, the migration can probably not be migrated down.
Foreign key constraints can also be dumped as a separate migration using Database#dump_foreign_key_migration, similar to how Database#dump_indexes_migration works.
DB[:table].all # => [{:a=>1, :b=>2}, {:a=>1, :b=>3}, {:a=>2, :b=>4}] DB[:table].to_hash(:a, :b) # => {1=>3, 2=>4} DB[:table].to_hash_groups(:a, :b) # => {1=>[2, 3], 2=>[4]}
DB.create_join_table(:album_id=>:albums, :artist_id=>:artists)
This uses real foreign keys for both of the columns, uses a composite primary key of both of the columns, and adds an additional composite index of the columns in reverse order. The primary key and additional index should ensure that almost all operations on the join table can benefit from an index.
In terms of customization, the values in the hash can be hashes themselves for column specific options, and an additional options hash can also be given to override some of the default settings.
Database#drop_join_table also exists and takes the same options as create_join_table. It mostly exists to make it easy to reverse migrations that use create_join_table.
DB.type_supported?(:hstore)
Sequel.lit(["(", " ? ", ")"], 1, 2) # (1 ? 2)
Sequel.subscript(:column, 1)[2][3] # column[1][2][3]
PG_NAMED_TYPES = {} unless defined?(PG_NAMED_TYPES) PG_TYPES = {} unless defined?(PG_TYPES)
That way they work whether they are loaded before or after the postgres adapter.
Database::CASCADE Database::NO_ACTION Database::SET_DEFAULTS Database::SET_NULL Database::RESTRICT Dataset::COLUMN_ALL
or moved:
MySQL::Dataset::AFFECTED_ROWS_RE -> MySQL::Database MySQL::Dataset#affected_rows -> MySQL::Database