Module: JdbcSpec::MsSQL

Defined in:
lib/jdbc_adapter/jdbc_mssql.rb

Defined Under Namespace

Modules: Column

Instance Method Summary collapse

Instance Method Details

#add_limit_offset!(sql, options) ⇒ Object 



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
155
156
157
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 127

def add_limit_offset!(sql, options)
  if options[:limit] and options[:offset]
    total_rows = @connection.select_all("SELECT count(*) as TotalRows from (#{sql.gsub(/\bSELECT\b/i, "SELECT TOP 1000000000")}) tally")[0][:TotalRows].to_i
    if (options[:limit] + options[:offset]) >= total_rows
      options[:limit] = (total_rows - options[:offset] >= 0) ? (total_rows - options[:offset]) : 0
    end
    sql.sub!(/^\s*SELECT/i, "SELECT * FROM (SELECT TOP #{options[:limit]} * FROM (SELECT 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
        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:



180
181
182
183
184
185
186
187
188
189
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 180

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[:default]
    remove_default_constraint(table_name, column_name)
    sql_commands << "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{options[:default]} FOR #{column_name}"
  end
  sql_commands.each {|c|
    execute(c)
  }
end

#create_database(name) ⇒ Object 



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

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

#drop_database(name) ⇒ Object 



164
165
166
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 164

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

#modify_types(tp) ⇒ Object 



81
82
83
84
85
86
87
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 81

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 



89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 89

def quote(value, column = nil)
if column && column.type == :primary_key
  return value.to_s
end
  case value
    when String                
      if column && column.type == :binary && column.class.respond_to?(:string_to_binary)
        val = quote_string(column.class.string_to_binary(value))
        "'#{val}'"
      else
        "'#{quote_string(value)}'"
      end
    when NilClass              then "NULL"
    when TrueClass             then '1'
    when FalseClass            then '0'
    when Float, Fixnum, Bignum then value.to_s
    when Date                  then "'#{value.to_s}'" 
    when Time, DateTime        then "'#{value.strftime("%Y-%m-%d %H:%M:%S")}'"
    else                            "'#{quote_string(value.to_yaml)}'"
  end
end

#quote_column_name(name) ⇒ Object 



123
124
125
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 123

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

#quote_string(string) ⇒ Object 



111
112
113
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 111

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

#quoted_false ⇒ Object 



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

def quoted_false
  "0"
end

#quoted_true ⇒ Object 



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

def quoted_true
  "1"
end

#recreate_database(name) ⇒ Object 



159
160
161
162
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 159

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

#remove_column(table_name, column_name) ⇒ Object 



191
192
193
194
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 191

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 



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

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 



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

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 



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

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

#rename_table(name, new_name) ⇒ Object 



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

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