Module: Sequel::MSSQL::DatabaseMethods
- Extended by:
- Database::ResetIdentifierMangling
- Includes:
- Database::SplitAlterTable
- Included in:
- ADO::MSSQL::DatabaseMethods, JDBC::MSSQL::DatabaseMethods, ODBC::MSSQL::DatabaseMethods, TinyTDS::Database
- Defined in:
- lib/sequel/adapters/shared/mssql.rb
Constant Summary collapse
- AUTO_INCREMENT =
'IDENTITY(1,1)'.freeze
- SERVER_VERSION_RE =
/^(\d+)\.(\d+)\.(\d+)/.freeze
- SERVER_VERSION_SQL =
"SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)".freeze
- SQL_BEGIN =
"BEGIN TRANSACTION".freeze
- SQL_COMMIT =
"COMMIT TRANSACTION".freeze
- SQL_ROLLBACK =
"IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION".freeze
- SQL_ROLLBACK_TO_SAVEPOINT =
'IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION autopoint_%d'.freeze
- SQL_SAVEPOINT =
'SAVE TRANSACTION autopoint_%d'.freeze
- MSSQL_DEFAULT_RE =
/\A(?:\(N?('.*')\)|\(\((-?\d+(?:\.\d+)?)\)\))\z/
- FOREIGN_KEY_ACTION_MAP =
{0 => :no_action, 1 => :cascade, 2 => :set_null, 3 => :set_default}.freeze
- DECIMAL_TYPE_RE =
The types to check for 0 scale to transform :decimal types to :integer.
/number|numeric|decimal/io
Instance Attribute Summary collapse
-
#mssql_unicode_strings ⇒ Object
Whether to use N” to quote strings, which allows unicode characters inside the strings.
Instance Method Summary collapse
-
#database_type ⇒ Object
Microsoft SQL Server uses the :mssql type.
-
#foreign_key_list(table, opts = OPTS) ⇒ Object
Return foreign key information using the system views, including :name, :on_delete, and :on_update entries in the hashes.
-
#global_index_namespace? ⇒ Boolean
Microsoft SQL Server namespaces indexes per table.
-
#indexes(table, opts = OPTS) ⇒ Object
Use the system tables to get index information.
-
#server_version(server = nil) ⇒ Object
The version of the MSSQL server, as an integer (e.g. 10001600 for SQL Server 2008 Express).
-
#supports_partial_indexes? ⇒ Boolean
MSSQL 2008+ supports partial indexes.
-
#supports_savepoints? ⇒ Boolean
MSSQL supports savepoints, though it doesn’t support committing/releasing them savepoint.
-
#supports_transaction_isolation_levels? ⇒ Boolean
MSSQL supports transaction isolation levels.
-
#supports_transactional_ddl? ⇒ Boolean
MSSQL supports transaction DDL statements.
-
#tables(opts = OPTS) ⇒ Object
Microsoft SQL Server supports using the INFORMATION_SCHEMA to get information on tables.
-
#views(opts = OPTS) ⇒ Object
Microsoft SQL Server supports using the INFORMATION_SCHEMA to get information on views.
Methods included from Database::ResetIdentifierMangling
Instance Attribute Details
#mssql_unicode_strings ⇒ Object
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.
26 27 28 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 26 def mssql_unicode_strings @mssql_unicode_strings end |
Instance Method Details
#database_type ⇒ Object
Microsoft SQL Server uses the :mssql type.
38 39 40 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 38 def database_type :mssql end |
#foreign_key_list(table, opts = OPTS) ⇒ Object
Return foreign key information using the system views, including :name, :on_delete, and :on_update entries in the hashes.
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 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 49 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 ds = .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 : 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 |
#global_index_namespace? ⇒ Boolean
Microsoft SQL Server namespaces indexes per table.
43 44 45 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 43 def global_index_namespace? false end |
#indexes(table, opts = OPTS) ⇒ Object
Use the system tables to get index information
89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 89 def indexes(table, opts=OPTS) m = output_identifier_meth im = input_identifier_meth indexes = {} ds = .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) 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 |
#server_version(server = nil) ⇒ Object
The version of the MSSQL server, as an integer (e.g. 10001600 for SQL Server 2008 Express).
115 116 117 118 119 120 121 122 123 124 125 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 115 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 |
#supports_partial_indexes? ⇒ Boolean
MSSQL 2008+ supports partial indexes.
128 129 130 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 128 def supports_partial_indexes? dataset.send(:is_2008_or_later?) end |
#supports_savepoints? ⇒ Boolean
MSSQL supports savepoints, though it doesn’t support committing/releasing them savepoint
133 134 135 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 133 def supports_savepoints? true end |
#supports_transaction_isolation_levels? ⇒ Boolean
MSSQL supports transaction isolation levels
138 139 140 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 138 def supports_transaction_isolation_levels? true end |
#supports_transactional_ddl? ⇒ Boolean
MSSQL supports transaction DDL statements.
143 144 145 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 143 def supports_transactional_ddl? true end |
#tables(opts = OPTS) ⇒ Object
Microsoft SQL Server supports using the INFORMATION_SCHEMA to get information on tables.
149 150 151 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 149 def tables(opts=OPTS) information_schema_tables('BASE TABLE', opts) end |
#views(opts = OPTS) ⇒ Object
Microsoft SQL Server supports using the INFORMATION_SCHEMA to get information on views.
155 156 157 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 155 def views(opts=OPTS) information_schema_tables('VIEW', opts) end |