module Sequel::SqlAnywhere::DatabaseMethods
Constants
- DATABASE_ERROR_REGEXPS
Attributes
Set whether to convert smallint type to boolean for this Database
instance
Public Instance Methods
# File lib/sequel/adapters/shared/sqlanywhere.rb 15 def database_type 16 :sqlanywhere 17 end
# File lib/sequel/adapters/shared/sqlanywhere.rb 74 def foreign_key_list(table, opts=OPTS) 75 m = output_identifier_meth 76 im = input_identifier_meth 77 fk_indexes = {} 78 metadata_dataset. 79 from{sys[:sysforeignkey].as(:fk)}. 80 select{[ 81 fk[:role].as(:name), 82 fks[:columns].as(:column_map), 83 si[:indextype].as(:type), 84 si[:colnames].as(:columns), 85 fks[:primary_tname].as(:table_name)]}. 86 join(Sequel[:sys][:sysforeignkeys].as(:fks), :role => :role). 87 join(Sequel[:sys][:sysindexes].as(:si), {:iname => Sequel[:fk][:role]}, {:implicit_qualifier => :fk}). 88 where{{fks[:foreign_tname]=>im.call(table)}}. 89 each do |r| 90 unless r[:type].downcase == 'primary key' 91 fk_indexes[r[:name]] = 92 {:name=>m.call(r[:name]), 93 :columns=>r[:columns].split(',').map{|v| m.call(v.split(' ').first)}, 94 :table=>m.call(r[:table_name]), 95 :key=>r[:column_map].split(',').map{|v| m.call(v.split(' IS ').last)}} 96 end 97 end 98 fk_indexes.values 99 end
# File lib/sequel/adapters/shared/sqlanywhere.rb 19 def freeze 20 @conversion_procs.freeze 21 super 22 end
# File lib/sequel/adapters/shared/sqlanywhere.rb 51 def indexes(table, opts = OPTS) 52 m = output_identifier_meth 53 im = input_identifier_meth 54 table = table.value if table.is_a?(Sequel::SQL::Identifier) 55 indexes = {} 56 metadata_dataset. 57 from(Sequel[:dbo][:sysobjects].as(:z)). 58 select{[ 59 z[:name].as(:table_name), 60 i[:name].as(:index_name), 61 si[:indextype].as(:type), 62 si[:colnames].as(:columns)]}. 63 join(Sequel[:dbo][:sysindexes].as(:i), :id=>:id). 64 join(Sequel[:sys][:sysindexes].as(:si), :iname=> :name). 65 where{{z[:type] => 'U', :table_name=>im.call(table)}}. 66 each do |r| 67 indexes[m.call(r[:index_name])] = 68 {:unique=>(r[:type].downcase=='unique'), 69 :columns=>r[:columns].split(',').map{|v| m.call(v.split(' ').first)}} unless r[:type].downcase == 'primary key' 70 end 71 indexes 72 end
# File lib/sequel/adapters/shared/sqlanywhere.rb 28 def schema_parse_table(table, opts) 29 m = output_identifier_meth(opts[:dataset]) 30 im = input_identifier_meth(opts[:dataset]) 31 metadata_dataset. 32 from{sa_describe_query("select * from #{im.call(table)}").as(:a)}. 33 join(Sequel[:syscolumn].as(:b), :table_id=>:base_table_id, :column_id=>:base_column_id). 34 order{a[:column_number]}. 35 map do |row| 36 auto_increment = row.delete(:is_autoincrement) 37 row[:auto_increment] = auto_increment == 1 || auto_increment == true 38 row[:primary_key] = row.delete(:pkey) == 'Y' 39 row[:allow_null] = row[:nulls_allowed].is_a?(Integer) ? row.delete(:nulls_allowed) == 1 : row.delete(:nulls_allowed) 40 row[:db_type] = row.delete(:domain_name_with_size) 41 row[:type] = if row[:db_type] =~ /numeric/i and (row[:scale].is_a?(Integer) ? row[:scale] == 0 : !row[:scale]) 42 :integer 43 else 44 schema_column_type(row[:db_type]) 45 end 46 row[:max_length] = row[:width] if row[:type] == :string 47 [m.call(row.delete(:name)), row] 48 end 49 end
# File lib/sequel/adapters/shared/sqlanywhere.rb 101 def tables(opts=OPTS) 102 tables_and_views('U', opts) 103 end
# File lib/sequel/adapters/shared/sqlanywhere.rb 24 def to_application_timestamp_sa(v) 25 to_application_timestamp(v.to_s) if v 26 end
# File lib/sequel/adapters/shared/sqlanywhere.rb 105 def views(opts=OPTS) 106 tables_and_views('V', opts) 107 end
Private Instance Methods
# File lib/sequel/adapters/shared/sqlanywhere.rb 165 def alter_table_sql(table, op) 166 case op[:op] 167 when :add_column 168 "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}" 169 when :drop_column 170 "ALTER TABLE #{quote_schema_table(table)} DROP #{column_definition_sql(op)}" 171 when :drop_constraint 172 case op[:type] 173 when :primary_key 174 "ALTER TABLE #{quote_schema_table(table)} DROP PRIMARY KEY" 175 when :foreign_key 176 if op[:name] || op[:columns] 177 name = op[:name] || foreign_key_name(table, op[:columns]) 178 if name 179 "ALTER TABLE #{quote_schema_table(table)} DROP FOREIGN KEY #{quote_identifier(name)}" 180 end 181 end 182 else 183 super 184 end 185 when :rename_column 186 "ALTER TABLE #{quote_schema_table(table)} RENAME #{quote_identifier(op[:name])} TO #{quote_identifier(op[:new_name].to_s)}" 187 when :set_column_type 188 "ALTER TABLE #{quote_schema_table(table)} ALTER #{quote_identifier(op[:name])} #{type_literal(op)}" 189 when :set_column_null 190 "ALTER TABLE #{quote_schema_table(table)} ALTER #{quote_identifier(op[:name])} #{'NOT ' unless op[:null]}NULL" 191 when :set_column_default 192 "ALTER TABLE #{quote_schema_table(table)} ALTER #{quote_identifier(op[:name])} DEFAULT #{literal(op[:default])}" 193 else 194 super(table, op) 195 end 196 end
Sybase uses the IDENTITY column for autoincrementing columns.
# File lib/sequel/adapters/shared/sqlanywhere.rb 124 def auto_increment_sql 125 'IDENTITY' 126 end
# File lib/sequel/adapters/shared/sqlanywhere.rb 137 def begin_transaction_sql 138 "BEGIN TRANSACTION" 139 end
Sybase does not allow adding primary key constraints to NULLable columns.
# File lib/sequel/adapters/shared/sqlanywhere.rb 129 def can_add_primary_key_constraint_on_nullable_columns? 130 false 131 end
SQLAnywhere tinyint types are unsigned.
# File lib/sequel/adapters/shared/sqlanywhere.rb 199 def column_schema_tinyint_type_is_unsigned? 200 true 201 end
# File lib/sequel/adapters/shared/sqlanywhere.rb 145 def commit_transaction_sql 146 "COMMIT TRANSACTION" 147 end
SqlAnywhere
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/sqlanywhere.rb 207 def create_table_as(name, ds, options) 208 raise(Error, "must provide dataset instance as value of create_table :as option on SqlAnywhere") unless ds.is_a?(Sequel::Dataset) 209 run(ds.into(name).sql) 210 end
# File lib/sequel/adapters/shared/sqlanywhere.rb 119 def database_error_regexps 120 DATABASE_ERROR_REGEXPS 121 end
Use SP_RENAME to rename the table
# File lib/sequel/adapters/shared/sqlanywhere.rb 213 def rename_table_sql(name, new_name) 214 "ALTER TABLE #{quote_schema_table(name)} RENAME #{quote_schema_table(new_name)}" 215 end
# File lib/sequel/adapters/shared/sqlanywhere.rb 141 def rollback_transaction_sql 142 "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION" 143 end
Convert smallint type to boolean if convert_smallint_to_bool
is true
# File lib/sequel/adapters/shared/sqlanywhere.rb 218 def schema_column_type(db_type) 219 if convert_smallint_to_bool && db_type =~ /smallint/i 220 :boolean 221 elsif db_type =~ /unsigned (big)?int/i 222 :integer 223 else 224 super 225 end 226 end
# File lib/sequel/adapters/shared/sqlanywhere.rb 228 def tables_and_views(type, opts=OPTS) 229 m = output_identifier_meth 230 metadata_dataset. 231 from{sysobjects.as(:a)}. 232 where{{a[:type]=>type}}. 233 select_map{a[:name]}. 234 map{|n| m.call(n)} 235 end
# File lib/sequel/adapters/shared/sqlanywhere.rb 133 def temporary_table_sql 134 "GLOBAL TEMPORARY " 135 end
Sybase has both datetime and timestamp classes, most people are going to want datetime
# File lib/sequel/adapters/shared/sqlanywhere.rb 151 def type_literal_generic_datetime(column) 152 :datetime 153 end
SQLAnywhere uses image type for blobs
# File lib/sequel/adapters/shared/sqlanywhere.rb 161 def type_literal_generic_file(column) 162 :image 163 end
Sybase doesn’t have a true boolean class, so it uses integer
# File lib/sequel/adapters/shared/sqlanywhere.rb 156 def type_literal_generic_trueclass(column) 157 :smallint 158 end
SQLAnywhere supports views with check option, but not local.
# File lib/sequel/adapters/shared/sqlanywhere.rb 238 def view_with_check_option_support 239 true 240 end