# File lib/sequel/adapters/shared/mssql.rb, line 451 def primary_key_constraint_sql_fragment(opts) add_clustered_sql_fragment(super, opts) end
module Sequel::MSSQL::DatabaseMethods
Constants
- DATABASE_ERROR_REGEXPS
- FOREIGN_KEY_ACTION_MAP
Attributes
Whether to use LIKE without COLLATE Latin1_General_CS_AS. Skipping the COLLATE can significantly increase performance in some cases.
Whether to use N'' to quote strings, which allows unicode characters inside the strings. True by default for compatibility, can be set to false for a possible performance increase. This sets the default for all datasets created from this Database object.
Public Instance Methods
Execute the given stored procedure with the given name.
Options:
- :args
-
Arguments to stored procedure. For named arguments, this should be a hash keyed by argument name. For unnamed arguments, this should be an array. Output parameters to the function are specified using :output. You can also name output parameters and provide a type by using an array containing :output, the type name, and the parameter name.
- :server
-
The server/shard on which to execute the procedure.
This method returns a single hash with the following keys:
- :result
-
The result code of the stored procedure
- :numrows
-
The number of rows affected by the stored procedure
- output params
-
Values for any output paramters, using the name given for the output parameter
Because Sequel datasets only support a single result set per query, and retrieving the result code and number of rows requires a query, this does not support stored procedures which also return result sets. To handle such stored procedures, you should drop down to the connection/driver level by using Sequel::Database#synchronize to get access to the underlying connection object.
Examples:
DB.call_mssql_sproc(:SequelTest, {args: ['input arg', :output]}) DB.call_mssql_sproc(:SequelTest, {args: ['input arg', [:output, 'int', 'varname']]}) named params: DB.call_mssql_sproc(:SequelTest, args: { 'input_arg1_name' => 'input arg1 value', 'input_arg2_name' => 'input arg2 value', 'output_arg_name' => [:output, 'int', 'varname'] })
# File lib/sequel/adapters/shared/mssql.rb, line 63 def call_mssql_sproc(name, opts=OPTS) args = opts[:args] || [] names = ['@RC AS RESULT', '@@ROWCOUNT AS NUMROWS'] declarations = ['@RC int'] values = [] if args.is_a?(Hash) named_args = true args = args.to_a method = :each else method = :each_with_index end args.public_send(method) do |v, i| if named_args k = v v, type, select = i raise Error, "must provide output parameter name when using output parameters with named arguments" if v == :output && !select else v, type, select = v end if v == :output type ||= "nvarchar(max)" if named_args varname = select else varname = "var#{i}" select ||= varname end names << "@#{varname} AS #{quote_identifier(select)}" declarations << "@#{varname} #{type}" value = "@#{varname} OUTPUT" else value = literal(v) end if named_args value = "@#{k}=#{value}" end values << value end sql = "DECLARE #{declarations.join(', ')}; EXECUTE @RC = #{name} #{values.join(', ')}; SELECT #{names.join(', ')}" ds = dataset.with_sql(sql) ds = ds.server(opts[:server]) if opts[:server] ds.first end
# File lib/sequel/adapters/shared/mssql.rb, line 115 def database_type :mssql end
Return foreign key information using the system views, including :name, :on_delete, and :on_update entries in the hashes.
# File lib/sequel/adapters/shared/mssql.rb, line 126 def foreign_key_list(table, opts=OPTS) m = output_identifier_meth im = input_identifier_meth schema, table = schema_and_table(table) current_schema = m.call(get(Sequel.function('schema_name'))) fk_action_map = FOREIGN_KEY_ACTION_MAP fk = Sequel[:fk] fkc = Sequel[:fkc] ds = metadata_dataset.from(Sequel.lit('[sys].[foreign_keys]').as(:fk)). join(Sequel.lit('[sys].[foreign_key_columns]').as(:fkc), :constraint_object_id => :object_id). join(Sequel.lit('[sys].[all_columns]').as(:pc), :object_id => fkc[:parent_object_id], :column_id => fkc[:parent_column_id]). join(Sequel.lit('[sys].[all_columns]').as(:rc), :object_id => fkc[:referenced_object_id], :column_id => fkc[:referenced_column_id]). where{{object_schema_name(fk[:parent_object_id]) => im.call(schema || current_schema)}}. where{{object_name(fk[:parent_object_id]) => im.call(table)}}. select{[fk[:name], fk[:delete_referential_action], fk[:update_referential_action], pc[:name].as(:column), rc[:name].as(:referenced_column), object_schema_name(fk[:referenced_object_id]).as(:schema), object_name(fk[:referenced_object_id]).as(:table)]}. order(fk[:name], fkc[:constraint_column_id]) h = {} ds.each do |row| if r = h[row[:name]] r[:columns] << m.call(row[:column]) r[:key] << m.call(row[:referenced_column]) else referenced_schema = m.call(row[:schema]) referenced_table = m.call(row[:table]) h[row[:name]] = { :name => m.call(row[:name]), :table => (referenced_schema == current_schema) ? referenced_table : Sequel.qualify(referenced_schema, referenced_table), :columns => [m.call(row[:column])], :key => [m.call(row[:referenced_column])], :on_update => fk_action_map[row[:update_referential_action]], :on_delete => fk_action_map[row[:delete_referential_action]] } end end h.values end
# File lib/sequel/adapters/shared/mssql.rb, line 167 def freeze server_version super end
Microsoft SQL Server namespaces indexes per table.
# File lib/sequel/adapters/shared/mssql.rb, line 120 def global_index_namespace? false end
Use the system tables to get index information
# File lib/sequel/adapters/shared/mssql.rb, line 173 def indexes(table, opts=OPTS) m = output_identifier_meth im = input_identifier_meth indexes = {} table = table.value if table.is_a?(Sequel::SQL::Identifier) i = Sequel[:i] ds = metadata_dataset.from(Sequel.lit('[sys].[tables]').as(:t)). join(Sequel.lit('[sys].[indexes]').as(:i), :object_id=>:object_id). join(Sequel.lit('[sys].[index_columns]').as(:ic), :object_id=>:object_id, :index_id=>:index_id). join(Sequel.lit('[sys].[columns]').as(:c), :object_id=>:object_id, :column_id=>:column_id). select(i[:name], i[:is_unique], Sequel[:c][:name].as(:column)). where{{t[:name]=>im.call(table)}}. where(i[:is_primary_key]=>0, i[:is_disabled]=>0). order(i[:name], Sequel[:ic][:index_column_id]) if supports_partial_indexes? ds = ds.where(i[:has_filter]=>0) end ds.each do |r| index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)} index[:columns] << m.call(r[:column]) end indexes end
The version of the MSSQL server, as an integer (e.g. 10001600 for SQL Server 2008 Express).
# File lib/sequel/adapters/shared/mssql.rb, line 201 def server_version(server=nil) return @server_version if @server_version if @opts[:server_version] return @server_version = Integer(@opts[:server_version]) end @server_version = synchronize(server) do |conn| (conn.server_version rescue nil) if conn.respond_to?(:server_version) end unless @server_version m = /^(\d+)\.(\d+)\.(\d+)/.match(fetch("SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)").single_value.to_s) @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i end @server_version end
MSSQL 2008+ supports partial indexes.
# File lib/sequel/adapters/shared/mssql.rb, line 217 def supports_partial_indexes? dataset.send(:is_2008_or_later?) end
MSSQL supports savepoints, though it doesn't support releasing them
# File lib/sequel/adapters/shared/mssql.rb, line 222 def supports_savepoints? true end
MSSQL supports transaction isolation levels
# File lib/sequel/adapters/shared/mssql.rb, line 227 def supports_transaction_isolation_levels? true end
MSSQL supports transaction DDL statements.
# File lib/sequel/adapters/shared/mssql.rb, line 232 def supports_transactional_ddl? true end
Microsoft SQL Server supports using the INFORMATION_SCHEMA to get information on tables.
# File lib/sequel/adapters/shared/mssql.rb, line 238 def tables(opts=OPTS) information_schema_tables('BASE TABLE', opts) end
Microsoft SQL Server supports using the INFORMATION_SCHEMA to get information on views.
# File lib/sequel/adapters/shared/mssql.rb, line 244 def views(opts=OPTS) information_schema_tables('VIEW', opts) end
Attempt to acquire an exclusive advisory lock with the given lock_id (which will be converted to a string). If successful, yield to the block, then release the advisory lock when the block exits. If unsuccessful, raise a Sequel::AdvisoryLockError.
Options:
- :wait
-
Do not raise an error, instead, wait until the advisory lock can be acquired.
# File lib/sequel/adapters/shared/mssql.rb, line 254 def with_advisory_lock(lock_id, opts=OPTS) lock_id = lock_id.to_s timeout = opts[:wait] ? -1 : 0 server = opts[:server] synchronize(server) do begin res = call_mssql_sproc(:sp_getapplock, :server=>server, :args=>{'Resource'=>lock_id, 'LockTimeout'=>timeout, 'LockMode'=>'Exclusive', 'LockOwner'=>'Session'}) unless locked = res[:result] >= 0 raise AdvisoryLockError, "unable to acquire advisory lock #{lock_id.inspect}" end yield ensure if locked call_mssql_sproc(:sp_releaseapplock, :server=>server, :args=>{'Resource'=>lock_id, 'LockOwner'=>'Session'}) end end end end
Private Instance Methods
Always quote identifiers in the metadata_dataset, so schema parsing works.
# File lib/sequel/adapters/shared/mssql.rb, line 446 def _metadata_dataset super.with_quote_identifiers(true) end
Add CLUSTERED or NONCLUSTERED as needed
# File lib/sequel/adapters/shared/mssql.rb, line 279 def add_clustered_sql_fragment(sql, opts) clustered = opts[:clustered] unless clustered.nil? sql += " #{'NON' unless clustered}CLUSTERED" end sql end
Add dropping of the default constraint to the list of SQL queries. This is necessary before dropping the column or changing its type.
# File lib/sequel/adapters/shared/mssql.rb, line 290 def add_drop_default_constraint_sql(sqls, table, column) if constraint = default_constraint_name(table, column) sqls << "ALTER TABLE #{quote_schema_table(table)} DROP CONSTRAINT #{constraint}" end end
# File lib/sequel/adapters/shared/mssql.rb, line 301 def alter_table_sql(table, op) case op[:op] when :add_column "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}" when :drop_column sqls = [] add_drop_default_constraint_sql(sqls, table, op[:name]) sqls << super when :rename_column "sp_rename #{literal("#{quote_schema_table(table)}.#{quote_identifier(op[:name])}")}, #{literal(metadata_dataset.with_quote_identifiers(false).quote_identifier(op[:new_name]))}, 'COLUMN'" when :set_column_type sqls = [] if sch = schema(table) if cs = sch.each{|k, v| break v if k == op[:name]; nil} cs = cs.dup add_drop_default_constraint_sql(sqls, table, op[:name]) cs[:default] = cs[:ruby_default] op = cs.merge!(op) default = op.delete(:default) end end sqls << "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{column_definition_sql(op)}" sqls << alter_table_sql(table, op.merge(:op=>:set_column_default, :default=>default, :skip_drop_default=>true)) if default sqls when :set_column_null sch = schema(table).find{|k,v| k.to_s == op[:name].to_s}.last type = sch[:db_type] if [:string, :decimal, :blob].include?(sch[:type]) && !["text", "ntext"].include?(type) && (size = (sch[:max_chars] || sch[:column_size])) size = "MAX" if size == -1 type += "(#{size}#{", #{sch[:scale]}" if sch[:scale] && sch[:scale].to_i > 0})" end "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} #{type_literal(:type=>type)} #{'NOT ' unless op[:null]}NULL" when :set_column_default sqls = [] add_drop_default_constraint_sql(sqls, table, op[:name]) unless op[:skip_drop_default] sqls << "ALTER TABLE #{quote_schema_table(table)} ADD CONSTRAINT #{quote_identifier("sequel_#{table}_#{op[:name]}_def")} DEFAULT #{literal(op[:default])} FOR #{quote_identifier(op[:name])}" else super(table, op) end end
MSSQL uses the IDENTITY(1,1) column for autoincrementing columns.
# File lib/sequel/adapters/shared/mssql.rb, line 297 def auto_increment_sql 'IDENTITY(1,1)' end
# File lib/sequel/adapters/shared/mssql.rb, line 342 def begin_savepoint_sql(depth) "SAVE TRANSACTION autopoint_#{depth}" end
# File lib/sequel/adapters/shared/mssql.rb, line 346 def begin_transaction_sql "BEGIN TRANSACTION" end
MSSQL does not allow adding primary key constraints to NULLable columns.
# File lib/sequel/adapters/shared/mssql.rb, line 351 def can_add_primary_key_constraint_on_nullable_columns? false end
Handle MSSQL specific default format.
# File lib/sequel/adapters/shared/mssql.rb, line 361 def column_schema_normalize_default(default, type) if m = /\A(?:\(N?('.*')\)|\(\((-?\d+(?:\.\d+)?)\)\))\z/.match(default) default = m[1] || m[2] end super(default, type) end
MSSQL tinyint types are unsigned.
# File lib/sequel/adapters/shared/mssql.rb, line 356 def column_schema_tinyint_type_is_unsigned? true end
Commit the active transaction on the connection, does not release savepoints.
# File lib/sequel/adapters/shared/mssql.rb, line 369 def commit_transaction(conn, opts=OPTS) log_connection_execute(conn, commit_transaction_sql) unless savepoint_level(conn) > 1 end
# File lib/sequel/adapters/shared/mssql.rb, line 373 def commit_transaction_sql "COMMIT TRANSACTION" end
MSSQL doesn't support CREATE TABLE AS, it only supports SELECT INTO. Emulating CREATE TABLE AS using SELECT INTO is only possible if a dataset is given as the argument, it can't work with a string, so raise an Error if a string is given.
# File lib/sequel/adapters/shared/mssql.rb, line 388 def create_table_as(name, ds, options) raise(Error, "must provide dataset instance as value of create_table :as option on MSSQL") unless ds.is_a?(Sequel::Dataset) run(ds.into(name).sql) end
MSSQL uses the name of the table to decide the difference between a regular and temporary table, with temporary table names starting with a #.
# File lib/sequel/adapters/shared/mssql.rb, line 380 def create_table_prefix_sql(name, options) "CREATE TABLE #{quote_schema_table(options[:temp] ? "##{name}" : name)}" end
# File lib/sequel/adapters/shared/mssql.rb, line 401 def database_error_regexps DATABASE_ERROR_REGEXPS end
The name of the constraint for setting the default value on the table and column. The SQL used to select default constraints utilizes MSSQL catalog views which were introduced in 2005. This method intentionally does not support MSSQL 2000.
# File lib/sequel/adapters/shared/mssql.rb, line 408 def default_constraint_name(table, column_name) if server_version >= 9000000 table_name = schema_and_table(table).compact.join('.') self[Sequel[:sys][:default_constraints]]. where{{:parent_object_id => Sequel::SQL::Function.new(:object_id, table_name), col_name(:parent_object_id, :parent_column_id) => column_name.to_s}}. get(:name) end end
# File lib/sequel/adapters/shared/mssql.rb, line 417 def drop_index_sql(table, op) "DROP INDEX #{quote_identifier(op[:name] || default_index_name(table, op[:columns]))} ON #{quote_schema_table(table)}" end
# File lib/sequel/adapters/shared/mssql.rb, line 421 def index_definition_sql(table_name, index) index_name = index[:name] || default_index_name(table_name, index[:columns]) raise Error, "Partial indexes are not supported for this database" if index[:where] && !supports_partial_indexes? if index[:type] == :full_text "CREATE FULLTEXT INDEX ON #{quote_schema_table(table_name)} #{literal(index[:columns])} KEY INDEX #{literal(index[:key_index])}" else "CREATE #{'UNIQUE ' if index[:unique]}#{'CLUSTERED ' if index[:type] == :clustered}INDEX #{quote_identifier(index_name)} ON #{quote_schema_table(table_name)} #{literal(index[:columns])}#{" INCLUDE #{literal(index[:include])}" if index[:include]}#{" WHERE #{filter_expr(index[:where])}" if index[:where]}" end end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/mssql.rb, line 432 def information_schema_tables(type, opts) m = output_identifier_meth schema = opts[:schema]||'dbo' tables = metadata_dataset.from(Sequel[:information_schema][:tables].as(:t)). select(:table_name). where(:table_type=>type, :table_schema=>schema.to_s). map{|x| m.call(x[:table_name])} tables.map!{|t| Sequel.qualify(m.call(schema).to_s, m.call(t).to_s)} if opts[:qualify] tables end
Handle clustered and nonclustered primary keys
Use sp_rename to rename the table
# File lib/sequel/adapters/shared/mssql.rb, line 456 def rename_table_sql(name, new_name) "sp_rename #{literal(quote_schema_table(name))}, #{quote_identifier(schema_and_table(new_name).pop)}" end
# File lib/sequel/adapters/shared/mssql.rb, line 460 def rollback_savepoint_sql(depth) "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION autopoint_#{depth}" end
# File lib/sequel/adapters/shared/mssql.rb, line 464 def rollback_transaction_sql "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION" end
# File lib/sequel/adapters/shared/mssql.rb, line 468 def schema_column_type(db_type) case db_type when /\A(?:bit)\z/io :boolean when /\A(?:(?:small)?money)\z/io :decimal when /\A(timestamp|rowversion)\z/io :blob else super end end
MSSQL uses the INFORMATION_SCHEMA to hold column information, and parses primary key information from the sysindexes, sysindexkeys, and syscolumns system tables.
# File lib/sequel/adapters/shared/mssql.rb, line 484 def schema_parse_table(table_name, opts) m = output_identifier_meth(opts[:dataset]) m2 = input_identifier_meth(opts[:dataset]) tn = m2.call(table_name.to_s) info_sch_sch = opts[:information_schema_schema] inf_sch_qual = lambda{|s| info_sch_sch ? Sequel.qualify(info_sch_sch, s) : Sequel[s]} table_id = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:objects])).where(:name => tn).select_map(:object_id).first identity_cols = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:columns])). where(:object_id=>table_id, :is_identity=>true). select_map(:name) pk_index_id = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:sysindexes])). where(:id=>table_id, :indid=>1..254){{(status & 2048)=>2048}}. get(:indid) pk_cols = metadata_dataset.from(inf_sch_qual.call(Sequel[:sys][:sysindexkeys]).as(:sik)). join(inf_sch_qual.call(Sequel[:sys][:syscolumns]).as(:sc), :id=>:id, :colid=>:colid). where{{sik[:id]=>table_id, sik[:indid]=>pk_index_id}}. select_order_map{sc[:name]} ds = metadata_dataset.from(inf_sch_qual.call(Sequel[:information_schema][:tables]).as(:t)). join(inf_sch_qual.call(Sequel[:information_schema][:columns]).as(:c), :table_catalog=>:table_catalog, :table_schema => :table_schema, :table_name => :table_name). select{[column_name.as(:column), data_type.as(:db_type), character_maximum_length.as(:max_chars), column_default.as(:default), is_nullable.as(:allow_null), numeric_precision.as(:column_size), numeric_scale.as(:scale)]}. where{{c[:table_name]=>tn}} if schema = opts[:schema] ds = ds.where{{c[:table_schema]=>schema}} end ds.map do |row| if row[:primary_key] = pk_cols.include?(row[:column]) row[:auto_increment] = identity_cols.include?(row[:column]) end row[:allow_null] = row[:allow_null] == 'YES' ? true : false row[:default] = nil if blank_object?(row[:default]) row[:type] = if row[:db_type] =~ /number|numeric|decimal/i && row[:scale] == 0 :integer else schema_column_type(row[:db_type]) end row[:max_length] = row[:max_chars] if row[:type] == :string && row[:max_chars] >= 0 [m.call(row.delete(:column)), row] end end
Set the #mssql_unicode_strings settings from the given options.
# File lib/sequel/adapters/shared/mssql.rb, line 531 def set_mssql_unicode_strings @mssql_unicode_strings = typecast_value_boolean(@opts.fetch(:mssql_unicode_strings, true)) end
MSSQL has both datetime and timestamp classes, most people are going to want datetime
# File lib/sequel/adapters/shared/mssql.rb, line 537 def type_literal_generic_datetime(column) :datetime end
MSSQL uses varbinary(max) type for blobs
# File lib/sequel/adapters/shared/mssql.rb, line 547 def type_literal_generic_file(column) :'varbinary(max)' end
MSSQL doesn't have a true boolean class, so it uses bit
# File lib/sequel/adapters/shared/mssql.rb, line 542 def type_literal_generic_trueclass(column) :bit end
Handle clustered and nonclustered unique constraints
# File lib/sequel/adapters/shared/mssql.rb, line 552 def unique_constraint_sql_fragment(opts) add_clustered_sql_fragment(super, opts) end
MSSQL supports views with check option, but not local.
# File lib/sequel/adapters/shared/mssql.rb, line 557 def view_with_check_option_support true end