The types to check for 0 scale to transform :decimal types to :integer.
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.
Microsoft SQL Server uses the :mssql type.
# File lib/sequel/adapters/shared/mssql.rb, line 31 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 42 def foreign_key_list(table, 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 ds = metadata_dataset.from(:sys__foreign_keys___fk). join(:sys__foreign_key_columns___fkc, :constraint_object_id => :object_id). join(:sys__all_columns___pc, :object_id => :fkc__parent_object_id, :column_id => :fkc__parent_column_id). join(:sys__all_columns___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___column, :rc__name___referenced_column, object_schema_name(:fk__referenced_object_id).as(:schema), object_name(:fk__referenced_object_id).as(:table)]}. order(: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 : :"#{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
Microsoft SQL Server namespaces indexes per table.
# File lib/sequel/adapters/shared/mssql.rb, line 36 def global_index_namespace? false end
Use the system tables to get index information
# File lib/sequel/adapters/shared/mssql.rb, line 82 def indexes(table, opts={}) m = output_identifier_meth im = input_identifier_meth indexes = {} metadata_dataset.from(:sys__tables___t). join(:sys__indexes___i, :object_id=>:object_id). join(:sys__index_columns___ic, :object_id=>:object_id, :index_id=>:index_id). join(:sys__columns___c, :object_id=>:object_id, :column_id=>:column_id). select(:i__name, :i__is_unique, :c__name___column). where{{t__name=>im.call(table)}}. where(:i__is_primary_key=>0, :i__is_disabled=>0). order(:i__name, :ic__index_column_id). 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 103 def server_version(server=nil) return @server_version if @server_version @server_version = synchronize(server) do |conn| (conn.server_version rescue nil) if conn.respond_to?(:server_version) end unless @server_version m = SERVER_VERSION_RE.match(fetch(SERVER_VERSION_SQL).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 supports savepoints, though it doesn't support committing/releasing them savepoint
# File lib/sequel/adapters/shared/mssql.rb, line 116 def supports_savepoints? true end
MSSQL supports transaction isolation levels
# File lib/sequel/adapters/shared/mssql.rb, line 121 def supports_transaction_isolation_levels? true end
MSSQL supports transaction DDL statements.
# File lib/sequel/adapters/shared/mssql.rb, line 126 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 132 def tables(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 138 def views(opts={}) information_schema_tables('VIEW', opts) 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 146 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
MSSQL specific syntax for altering tables.
# File lib/sequel/adapters/shared/mssql.rb, line 158 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(op[:new_name].to_s)}, '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)) 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].include?(sch[:type]) and size = (sch[:max_chars] || sch[:column_size]) 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 "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 153 def auto_increment_sql AUTO_INCREMENT end
SQL to start a new savepoint
# File lib/sequel/adapters/shared/mssql.rb, line 197 def begin_savepoint_sql(depth) SQL_SAVEPOINT % depth end
SQL to BEGIN a transaction.
# File lib/sequel/adapters/shared/mssql.rb, line 202 def begin_transaction_sql SQL_BEGIN end
Handle MSSQL specific default format.
# File lib/sequel/adapters/shared/mssql.rb, line 207 def column_schema_normalize_default(default, type) if m = MSSQL_DEFAULT_RE.match(default) default = m[1] || m[2] end super(default, type) end
Commit the active transaction on the connection, does not commit/release savepoints.
# File lib/sequel/adapters/shared/mssql.rb, line 216 def commit_transaction(conn, opts={}) log_connection_execute(conn, commit_transaction_sql) unless _trans(conn)[:savepoint_level] > 1 end
SQL to COMMIT a transaction.
# File lib/sequel/adapters/shared/mssql.rb, line 221 def commit_transaction_sql SQL_COMMIT 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 236 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 228 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 248 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 255 def default_constraint_name(table, column_name) if server_version >= 9000000 table_name = schema_and_table(table).compact.join('.') self[: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
The SQL to drop an index for the table.
# File lib/sequel/adapters/shared/mssql.rb, line 265 def drop_index_sql(table, op) "DROP INDEX #{quote_identifier(op[:name] || default_index_name(table, op[:columns]))} ON #{quote_schema_table(table)}" end
support for clustered index type
# File lib/sequel/adapters/shared/mssql.rb, line 270 def index_definition_sql(table_name, index) index_name = index[:name] || default_index_name(table_name, index[:columns]) 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 280 def information_schema_tables(type, opts) m = output_identifier_meth metadata_dataset.from(:information_schema__tables___t). select(:table_name). filter(:table_type=>type, :table_schema=>(opts[:schema]||default_schema||'dbo').to_s). map{|x| m.call(x[:table_name])} end
Always quote identifiers in the #metadata_dataset, so schema parsing works.
# File lib/sequel/adapters/shared/mssql.rb, line 289 def metadata_dataset ds = super ds.quote_identifiers = true ds end
Use sp_rename to rename the table
# File lib/sequel/adapters/shared/mssql.rb, line 296 def rename_table_sql(name, new_name) "sp_rename #{literal(quote_schema_table(name))}, #{quote_identifier(schema_and_table(new_name).pop)}" end
SQL to rollback to a savepoint
# File lib/sequel/adapters/shared/mssql.rb, line 301 def rollback_savepoint_sql(depth) SQL_ROLLBACK_TO_SAVEPOINT % depth end
SQL to ROLLBACK a transaction.
# File lib/sequel/adapters/shared/mssql.rb, line 306 def rollback_transaction_sql SQL_ROLLBACK end
The closest MSSQL equivalent of a boolean datatype is the bit type.
# File lib/sequel/adapters/shared/mssql.rb, line 311 def schema_column_type(db_type) case db_type when /\A(?:bit)\z/o :boolean when /\A(?:(?:small)?money)\z/o :decimal 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 325 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) table_id = get{object_id(tn)} info_sch_sch = opts[:information_schema_schema] inf_sch_qual = lambda{|s| info_sch_sch ? Sequel.qualify(info_sch_sch, s) : Sequel.expr(s)} sys_qual = lambda{|s| info_sch_sch ? Sequel.qualify(info_sch_sch, Sequel.qualify(Sequel.lit(''), s)) : Sequel.expr(s)} pk_index_id = metadata_dataset.from(sys_qual.call(:sysindexes)). where(:id=>table_id, :indid=>1..254){{(status & 2048)=>2048}}. get(:indid) pk_cols = metadata_dataset.from(sys_qual.call(:sysindexkeys).as(:sik)). join(sys_qual.call(: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(:information_schema__tables).as(:t)). join(inf_sch_qual.call(:information_schema__columns).as(:c), :table_catalog=>:table_catalog, :table_schema => :table_schema, :table_name => :table_name). select(:column_name___column, :data_type___db_type, :character_maximum_length___max_chars, :column_default___default, :is_nullable___allow_null, :numeric_precision___column_size, :numeric_scale___scale). filter(:c__table_name=>tn) if schema = opts[:schema] || default_schema ds.filter!(:c__table_schema=>schema) end ds.map do |row| row[:primary_key] = pk_cols.include?(row[:column]) row[:allow_null] = row[:allow_null] == 'YES' ? true : false row[:default] = nil if blank_object?(row[:default]) row[:type] = if row[:db_type] =~ DECIMAL_TYPE_RE && row[:scale] == 0 :integer else schema_column_type(row[:db_type]) end [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 363 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 369 def type_literal_generic_datetime(column) :datetime end
MSSQL uses varbinary(max) type for blobs
# File lib/sequel/adapters/shared/mssql.rb, line 385 def type_literal_generic_file(column) :'varbinary(max)' end
MSSQL has both datetime and timestamp classes, most people are going to want datetime
# File lib/sequel/adapters/shared/mssql.rb, line 375 def type_literal_generic_time(column) column[:only_time] ? :time : :datetime end
MSSQL doesn't have a true boolean class, so it uses bit
# File lib/sequel/adapters/shared/mssql.rb, line 380 def type_literal_generic_trueclass(column) :bit end