Module: JdbcSpec::MsSQL
- Includes:
- TSqlMethods
- Defined in:
- lib/jdbc_adapter/jdbc_mssql.rb
Defined Under Namespace
Modules: Column
Class Method Summary
collapse
Instance Method Summary
collapse
-
#_execute(sql, name = nil) ⇒ Object
-
#add_column(table_name, column_name, type, options = {}) ⇒ Object
Adds a new column to the named table.
-
#change_column(table_name, column_name, type, options = {}) ⇒ Object
-
#change_column_default(table_name, column_name, default) ⇒ Object
-
#change_order_direction(order) ⇒ Object
-
#columns(table_name, name = nil) ⇒ Object
-
#create_database(name) ⇒ Object
-
#drop_database(name) ⇒ Object
-
#quote(value, column = nil) ⇒ Object
-
#quote_column_name(name) ⇒ Object
-
#quote_string(string) ⇒ Object
-
#quote_table_name(name) ⇒ Object
-
#recreate_database(name) ⇒ Object
-
#remove_check_constraints(table_name, column_name) ⇒ Object
-
#remove_column(table_name, column_name) ⇒ Object
-
#remove_default_constraint(table_name, column_name) ⇒ Object
-
#remove_index(table_name, options = {}) ⇒ Object
-
#rename_column(table, column, new_column_name) ⇒ Object
-
#rename_table(name, new_name) ⇒ Object
#add_limit_offset!, #modify_types, #type_to_sql
Class Method Details
.adapter_selector ⇒ Object
30
31
32
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 30
def self.adapter_selector
[/sqlserver|tds/i, lambda {|cfg,adapt| adapt.extend(::JdbcSpec::MsSQL)}]
end
|
.column_selector ⇒ Object
26
27
28
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 26
def self.column_selector
[/sqlserver|tds/i, lambda {|cfg,col| col.extend(::JdbcSpec::MsSQL::Column)}]
end
|
Instance Method Details
#_execute(sql, name = nil) ⇒ Object
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 232
def _execute(sql, name = nil)
if sql.lstrip =~ /^insert/i
if query_requires_identity_insert?(sql)
table_name = get_table_name(sql)
with_identity_insert_enabled(table_name) do
id = @connection.execute_insert(sql)
end
else
@connection.execute_insert(sql)
end
elsif sql.lstrip =~ /^\(?\s*(select|show)/i
repair_special_columns(sql)
@connection.execute_query(sql)
else
@connection.execute_update(sql)
end
end
|
#add_column(table_name, column_name, type, options = {}) ⇒ Object
Adds a new column to the named table. See TableDefinition#column for details of the options you can use.
170
171
172
173
174
175
176
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 170
def add_column(table_name, column_name, type, options = {})
add_column_sql = "ALTER TABLE #{table_name} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
add_column_options!(add_column_sql, options)
execute(add_column_sql)
end
|
#change_column(table_name, column_name, type, options = {}) ⇒ Object
182
183
184
185
186
187
188
189
190
191
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 182
def change_column(table_name, column_name, type, options = {}) sql_commands = ["ALTER TABLE #{table_name} ALTER COLUMN #{column_name} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"]
if options_include_default?(options)
remove_default_constraint(table_name, column_name)
sql_commands << "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote(options[:default], options[:column])} FOR #{column_name}"
end
sql_commands.each {|c|
execute(c)
}
end
|
#change_column_default(table_name, column_name, default) ⇒ Object
192
193
194
195
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 192
def change_column_default(table_name, column_name, default) remove_default_constraint(table_name, column_name)
execute "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote(default, column_name)} FOR #{column_name}"
end
|
#change_order_direction(order) ⇒ Object
141
142
143
144
145
146
147
148
149
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 141
def change_order_direction(order)
order.split(",").collect {|fragment|
case fragment
when /\bDESC\b/i then fragment.gsub(/\bDESC\b/i, "ASC")
when /\bASC\b/i then fragment.gsub(/\bASC\b/i, "DESC")
else String.new(fragment).split(',').join(' DESC,') + ' DESC'
end
}.join(",")
end
|
#columns(table_name, name = nil) ⇒ Object
222
223
224
225
226
227
228
229
230
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 222
def columns(table_name, name = nil)
return [] if table_name =~ /^information_schema\./i
cc = super
cc.each do |col|
col.identity = true if col.sql_type =~ /identity/i
col.is_special = true if col.sql_type =~ /text|ntext|image/i
end
cc
end
|
#create_database(name) ⇒ Object
160
161
162
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 160
def create_database(name)
execute "CREATE DATABASE #{name}"
end
|
#drop_database(name) ⇒ Object
156
157
158
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 156
def drop_database(name)
execute "DROP DATABASE #{name}"
end
|
#quote(value, column = nil) ⇒ Object
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 107
def quote(value, column = nil)
return value.quoted_id if value.respond_to?(:quoted_id)
case value
when String, ActiveSupport::Multibyte::Chars
value = value.to_s
if column && column.type == :binary
"'#{quote_string(JdbcSpec::MsSQL::Column.string_to_binary(value))}'" elsif column && [:integer, :float].include?(column.type)
value = column.type == :integer ? value.to_i : value.to_f
value.to_s
else
"'#{quote_string(value)}'" end
when TrueClass then '1'
when FalseClass then '0'
when Time, DateTime then "'#{value.strftime("%Y%m%d %H:%M:%S")}'"
when Date then "'#{value.strftime("%Y%m%d")}'"
else super
end
end
|
#quote_column_name(name) ⇒ Object
137
138
139
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 137
def quote_column_name(name)
"[#{name}]"
end
|
#quote_string(string) ⇒ Object
129
130
131
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 129
def quote_string(string)
string.gsub(/\'/, "''")
end
|
#quote_table_name(name) ⇒ Object
133
134
135
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 133
def quote_table_name(name)
name
end
|
#recreate_database(name) ⇒ Object
151
152
153
154
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 151
def recreate_database(name)
drop_database(name)
create_database(name)
end
|
#remove_check_constraints(table_name, column_name) ⇒ Object
209
210
211
212
213
214
215
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 209
def remove_check_constraints(table_name, column_name)
constraints = select "SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '#{table_name}' and COLUMN_NAME = '#{column_name}'"
constraints.each do |constraint|
execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["CONSTRAINT_NAME"]}"
end
end
|
#remove_column(table_name, column_name) ⇒ Object
196
197
198
199
200
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 196
def remove_column(table_name, column_name)
remove_check_constraints(table_name, column_name)
remove_default_constraint(table_name, column_name)
execute "ALTER TABLE #{table_name} DROP COLUMN [#{column_name}]"
end
|
#remove_default_constraint(table_name, column_name) ⇒ Object
202
203
204
205
206
207
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 202
def remove_default_constraint(table_name, column_name)
defaults = select "select def.name from sysobjects def, syscolumns col, sysobjects tab where col.cdefault = def.id and col.name = '#{column_name}' and tab.name = '#{table_name}' and col.id = tab.id"
defaults.each {|constraint|
execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["name"]}"
}
end
|
#remove_index(table_name, options = {}) ⇒ Object
217
218
219
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 217
def remove_index(table_name, options = {})
execute "DROP INDEX #{table_name}.#{index_name(table_name, options)}"
end
|
#rename_column(table, column, new_column_name) ⇒ Object
178
179
180
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 178
def rename_column(table, column, new_column_name)
execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'"
end
|
#rename_table(name, new_name) ⇒ Object
164
165
166
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 164
def rename_table(name, new_name)
execute "EXEC sp_rename '#{name}', '#{new_name}'"
end
|