Module: JdbcSpec::MsSQL

Defined in:
lib/jdbc_adapter/jdbc_mssql.rb

Defined Under Namespace

Modules: Column

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.adapter_selector ⇒ Object 



7
8
9
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 7

def self.adapter_selector
  [/sqlserver|tds/i, lambda {|cfg,adapt| adapt.extend(::JdbcSpec::MsSQL)}]
end

.column_selector ⇒ Object 



3
4
5
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 3

def self.column_selector
  [/sqlserver|tds/i, lambda {|cfg,col| col.extend(::JdbcSpec::MsSQL::Column)}]
end

Instance Method Details

#_execute(sql, name = nil) ⇒ Object 



227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 227

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
  @connection.execute_query(sql)
  else
  @connection.execute_update(sql)
  end
end

#add_limit_offset!(sql, options) ⇒ Object 



124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 124

def add_limit_offset!(sql, options)
  if options[:limit] and options[:offset]
    total_rows = select_all("SELECT count(*) as TotalRows from (#{sql.gsub(/\bSELECT(\s+DISTINCT)?\b/i, "SELECT#{$1} TOP 1000000000")}) tally")[0][:TotalRows].to_i

    sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i, "SELECT * FROM (SELECT TOP #{options[:limit]} * FROM (SELECT#{$1} TOP #{options[:limit] + options[:offset]} ")
    sql << ") AS tmp1"
    if options[:order]
      options[:order] = options[:order].split(',').map do |field|
        parts = field.split(" ")
        tc = parts[0]
        if sql =~ /\.\[/ and tc =~ /\./ # if column quoting used in query
          tc.gsub!(/\./, '\\.\\[')
          tc << '\\]'
        end
        if sql =~ /#{tc} AS (t\d_r\d\d?)/
          parts[0] = $1
        elsif parts[0] =~ /\w+\.(\w+)/
          parts[0] = $1
        end
        parts.join(' ')
      end.join(', ')
      sql << " ORDER BY #{change_order_direction(options[:order])}) AS tmp2 ORDER BY #{options[:order]}"
    else
      sql << " ) AS tmp2"
    end
  elsif sql !~ /^\s*SELECT (@@|COUNT\()/i
    sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i) do
      "SELECT#{$1} TOP #{options[:limit]}"
    end unless options[:limit].nil?
  end
end

#change_column(table_name, column_name, type, options = {}) ⇒ Object

:nodoc:



188
189
190
191
192
193
194
195
196
197
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 188

def change_column(table_name, column_name, type, options = {}) #:nodoc:
  sql_commands = ["ALTER TABLE #{table_name} ALTER COLUMN #{column_name} #{type_to_sql(type, options[:limit])}"]
  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

:nodoc:



198
199
200
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 198

def change_column_default(table_name, column_name, default) #:nodoc:
  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 



157
158
159
160
161
162
163
164
165
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 157

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 



218
219
220
221
222
223
224
225
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 218

def columns(table_name, name = nil)
  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 



176
177
178
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 176

def create_database(name)
  execute "CREATE DATABASE #{name}"
end

#drop_database(name) ⇒ Object 



172
173
174
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 172

def drop_database(name)
  execute "DROP DATABASE #{name}"
end

#modify_types(tp) ⇒ Object 



96
97
98
99
100
101
102
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 96

def modify_types(tp)
  tp[:primary_key] = "int NOT NULL IDENTITY(1, 1) PRIMARY KEY"
  tp[:integer][:limit] = nil
  tp[:boolean][:limit] = nil
  tp[:binary] = { :name => "image"}
  tp
end

#quote(value, column = nil) ⇒ Object 



104
105
106
107
108
109
110
111
112
113
114
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 104

def quote(value, column = nil)
  return value.quoted_id if value.respond_to?(:quoted_id)

  case value
    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 



120
121
122
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 120

def quote_column_name(name)
  "[#{name}]"
end

#quote_string(string) ⇒ Object 



116
117
118
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 116

def quote_string(string)
  string.gsub(/\'/, "''")
end

#recreate_database(name) ⇒ Object 



167
168
169
170
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 167

def recreate_database(name)
  drop_database(name)
  create_database(name)
end

#remove_column(table_name, column_name) ⇒ Object 



201
202
203
204
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 201

def remove_column(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 



206
207
208
209
210
211
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 206

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 



213
214
215
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 213

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 



184
185
186
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 184

def rename_column(table, column, new_column_name)
  execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'"
end

#rename_table(name, new_name) ⇒ Object 



180
181
182
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 180

def rename_table(name, new_name)
  execute "EXEC sp_rename '#{name}', '#{new_name}'"
end