From f7c371dff8021de8e2389580bb96b0cfdca3c9ec Mon Sep 17 00:00:00 2001 From: Jeremy Kemper Date: Thu, 13 Sep 2007 23:21:14 +0000 Subject: OpenBase: update for new lib and latest Rails. Support migrations. Closes #8748. git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@7472 5ecf4fe2-1ee6-0310-87b1-e25e094e27de --- .../connection_adapters/openbase_adapter.rb | 348 ++++++++++++++------- 1 file changed, 229 insertions(+), 119 deletions(-) (limited to 'activerecord/lib') diff --git a/activerecord/lib/active_record/connection_adapters/openbase_adapter.rb b/activerecord/lib/active_record/connection_adapters/openbase_adapter.rb index 57d16d47b4..43c7dc1ed8 100644 --- a/activerecord/lib/active_record/connection_adapters/openbase_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/openbase_adapter.rb @@ -10,7 +10,6 @@ module ActiveRecord host = config[:host] username = config[:username].to_s password = config[:password].to_s - if config.has_key?(:database) database = config[:database] @@ -21,10 +20,20 @@ module ActiveRecord oba = ConnectionAdapters::OpenBaseAdapter.new( OpenBase.new(database, host, username, password), logger ) - + + if oba.raw_connection.connected? + unless oba.tables.include?(ConnectionAdapters::OpenBaseAdapter::COLUMN_SUPPORT_TABLE) + oba.execute(<<-SQL,"Creating OpenBase Column Support Table") + CREATE TABLE #{ConnectionAdapters::OpenBaseAdapter::COLUMN_SUPPORT_TABLE} (name char, type char, precision int, scale int) + SQL + end + oba.select_all("SELECT * FROM #{ConnectionAdapters::OpenBaseAdapter::COLUMN_SUPPORT_TABLE}").each do |col| + ConnectionAdapters::OpenBaseAdapter::DECIMAL_COLUMNS.store(col["name"],[col["precision"],col["scale"]]) + end + end + oba end - end module ConnectionAdapters @@ -37,8 +46,10 @@ module ActiveRecord super end end - # The OpenBase adapter works with the Ruby/Openbase driver by Tetsuya Suzuki. - # http://www.spice-of-life.net/ruby-openbase/ (needs version 0.7.3+) + + # The OpenBase adapter works with the Ruby/Openbase driver by Derrick Spell, + # provided with the distribution of OpenBase 10.0.6 and later + # http://www.openbase.com # # Options: # @@ -50,20 +61,21 @@ module ActiveRecord # The OpenBase adapter will make use of OpenBase's ability to generate unique ids # for any column with an unique index applied. Thus, if the value of a primary # key is not specified at the time an INSERT is performed, the adapter will prefetch - # a unique id for the primary key. This prefetching is also necessary in order + # a unique id for the primary key. This prefetching is also necessary in order # to return the id after an insert. # - # Caveat: Operations involving LIMIT and OFFSET do not yet work! # # Maintainer: derrick.spell@gmail.com class OpenBaseAdapter < AbstractAdapter + DECIMAL_COLUMNS = {} + COLUMN_SUPPORT_TABLE = "rails_openbase_column_support" def adapter_name 'OpenBase' end - + def native_database_types { - :primary_key => "integer UNIQUE INDEX DEFAULT _rowid", + :primary_key => "integer NOT NULL UNIQUE INDEX DEFAULT _rowid", :string => { :name => "char", :limit => 4096 }, :text => { :name => "text" }, :integer => { :name => "integer" }, @@ -77,19 +89,19 @@ module ActiveRecord :boolean => { :name => "boolean" } } end - + def supports_migrations? - false - end - + true + end + def prefetch_primary_key?(table_name = nil) true end - + def default_sequence_name(table_name, primary_key) # :nodoc: "#{table_name} #{primary_key}" end - + def next_sequence_value(sequence_name) ary = sequence_name.split(' ') if (!ary[1]) then @@ -99,66 +111,75 @@ module ActiveRecord @connection.unique_row_id(ary[0], ary[1]) end - + # QUOTING ================================================== - + def quote(value, column = nil) if value.kind_of?(String) && column && column.type == :binary "'#{@connection.insert_binary(value)}'" + elsif value.kind_of?(BigDecimal) + return "'#{value.to_s}'" + elsif column && column.type == :integer && column.sql_type =~ /decimal/ + return "'#{value.to_s}'" + elsif [Float,Fixnum,Bignum].include?(value.class) && column && column.type == :string + return "'#{value.to_s}'" else super end end - + def quoted_true "1" end - + def quoted_false "0" end - + # DATABASE STATEMENTS ====================================== def add_limit_offset!(sql, options) #:nodoc: - if limit = options[:limit] - unless offset = options[:offset] - sql << " RETURN RESULTS #{limit}" + return if options[:limit].nil? + limit = options[:limit] + offset = options[:offset] + if limit == 0 + # Mess with the where clause to ensure we get no results + if sql =~ /WHERE/i + sql.sub!(/WHERE/i, 'WHERE 1 = 2 AND ') + elsif sql =~ /ORDER\s+BY/i + sql.sub!(/ORDER\s+BY/i, 'WHERE 1 = 2 ORDER BY') else - limit = limit + offset - sql << " RETURN RESULTS #{offset} TO #{limit}" + sql << 'WHERE 1 = 2' end + elsif offset.nil? + sql << " RETURN RESULTS #{limit}" + else + sql << " RETURN RESULTS #{offset} TO #{limit + offset}" end end - - def select_all(sql, name = nil) #:nodoc: - select(sql, name) - end - - def select_one(sql, name = nil) #:nodoc: - add_limit_offset!(sql,{:limit => 1}) - results = select(sql, name) - results.first if results - end def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc: execute(sql, name) update_nulls_after_insert(sql, name, pk, id_value, sequence_name) id_value end - + def execute(sql, name = nil) #:nodoc: log(sql, name) { @connection.execute(sql) } end + def direct_execute(sql, name = nil) #:nodoc: + log(sql, name) { @connection.execute(sql) } + end + def update(sql, name = nil) #:nodoc: execute(sql, name).rows_affected end alias_method :delete, :update #:nodoc: -#=begin + def begin_db_transaction #:nodoc: execute "START TRANSACTION" rescue Exception @@ -176,15 +197,13 @@ module ActiveRecord rescue Exception # Transactions aren't supported end -#=end - # SCHEMA STATEMENTS ======================================== + # SCHEMA STATEMENTS ======================================== # Return the list of all tables in the schema search path. def tables(name = nil) #:nodoc: tables = @connection.tables tables.reject! { |t| /\A_SYS_/ === t } - tables end def columns(table_name, name = nil) #:nodoc: @@ -192,16 +211,23 @@ module ActiveRecord sql << "WHERE tablename='#{table_name}' AND INDEXOF(fieldname,'_')<>0 " sql << "ORDER BY columnNumber" columns = [] - select_all(sql, name).each do |row| + direct_execute(sql, name).each_hash do |row| columns << OpenBaseColumn.new(row["fieldname"], - default_value(row["defaultvalue"]), - sql_type_name(row["typename"],row["length"]), - row["notnull"] - ) + default_value(row["defaultvalue"],row["typename"]), + sql_type_name(table_name,row["fieldname"],row["typename"],row["length"]), + row["notnull"] == 1 ? false : true) end columns end + def column_names(table_name) #:nodoc: + sql = "SELECT fieldname FROM _sys_tables " + sql << "WHERE tablename='#{table_name}' AND INDEXOF(fieldname,'_')<>0 " + sql << "ORDER BY columnNumber" + names = direct_execute(sql).fetch_all + names.flatten! || names + end + def indexes(table_name, name = nil)#:nodoc: sql = "SELECT fieldname, notnull, searchindex, uniqueindex, clusteredindex FROM _sys_tables " sql << "WHERE tablename='#{table_name}' AND INDEXOF(fieldname,'_')<>0 " @@ -210,109 +236,189 @@ module ActiveRecord sql << "ORDER BY columnNumber" indexes = [] execute(sql, name).each do |row| - indexes << IndexDefinition.new(table_name,index_name(row),row[3]==1,[row[0]]) + indexes << IndexDefinition.new(table_name,ob_index_name(row),row[3]==1,[row[0]]) end indexes end + def create_table(name, options = {}) #:nodoc: + return_value = super + + # Get my own copy of TableDefinition so that i can detect decimal columns + table_definition = TableDefinition.new(self) + yield table_definition + + table_definition.columns.each do |col| + if col.type == :decimal + record_decimal(name, col.name, col.precision, col.scale) + end + end + + unless options[:id] == false + primary_key = (options[:primary_key] || "id") + direct_execute("CREATE PRIMARY KEY #{name} (#{primary_key})") + end + return_value + end + + def rename_table(name, new_name) + execute "RENAME #{name} #{new_name}" + end + + def add_column(table_name, column_name, type, options = {}) + return_value = super(table_name, "COLUMN " + column_name.to_s, type, options) + if type == :decimal + record_decimal(table_name, column_name, options[:precision], options[:scale]) + end + end + + def remove_column(table_name, column_name) + execute "ALTER TABLE #{table_name} REMOVE COLUMN #{quote_column_name(column_name)}" + end + + def rename_column(table_name, column_name, new_column_name) + execute "ALTER TABLE #{table_name} RENAME #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}" + end + + def add_column_options!(sql, options) #:nodoc: + sql << " NOT NULL" if options[:null] == false + sql << " DEFAULT #{quote(options[:default], options[:column])}" if options_include_default?(options) + end + + def change_column(table_name, column_name, type, options = {}) #:nodoc: + unless options_include_default?(options) + options[:default] = select_one("SELECT * FROM _sys_tables WHERE tablename='#{table_name}' AND fieldname='#{column_name}'")["defaultvalue"] + end + + change_column_sql = "ALTER TABLE #{table_name} ADD COLUMN #{column_name} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" + add_column_options!(change_column_sql, options) + execute(change_column_sql) + end + + def change_column_default(table_name, column_name, default) + execute "ALTER TABLE #{table_name} COLUMN #{column_name} SET DEFAULT #{quote(default)}" + end + + def add_index(table_name, column_name, options = {}) + if Hash === options # legacy support, since this param was a string + index_type = options[:unique] ? "UNIQUE" : "" + else + index_type = options + end + execute "CREATE #{index_type} INDEX #{table_name} #{column_name}" + end + + def remove_index(table_name, options = {}) + execute "DROP INDEX #{table_name} #{options === Hash ? options[:column] : options}" + end + + def type_to_sql(type, limit = nil, precision = nil, scale = nil) #:nodoc: + return super unless type.to_s == 'decimal' + + if (scale.to_i == 2) + return 'money' + elsif (scale.to_i == 0) + return 'longlong' + else + return "char(#{precision.to_i + 1})" + end + end + private def select(sql, name = nil) - sql = translate_sql(sql) - results = execute(sql, name) - - date_cols = [] - col_names = [] - results.column_infos.each do |info| - col_names << info.name - date_cols << info.name if info.type == "date" + decimals = detect_decimals(sql) || [] + sql = add_order_by_rowid(sql) + + # OpenBase ignores the return results when there is a group by + # so limit the result set that we return to rails if need be + if (sql =~ /GROUP BY/i) + sql.sub!(/RETURN RESULTS (\d+)( TO (\d+))?/i,"") + + results = execute(sql, name) + if ($2) + results.fetch_offset = $1.to_i + results.fetch_limit = $3.to_i - $1.to_i + elsif ($1) + results.fetch_limit = $1.to_i + end + else + results = execute(sql, name) end - + rows = [] if ( results.rows_affected ) - results.each do |row| # loop through result rows - hashed_row = {} - row.each_index do |index| - hashed_row["#{col_names[index]}"] = row[index] unless col_names[index] == "_rowid" - end - date_cols.each do |name| - unless hashed_row["#{name}"].nil? or hashed_row["#{name}"].empty? - hashed_row["#{name}"] = Date.parse(hashed_row["#{name}"],false).to_s - end + results.each_hash do |row| # loop through result rows + row.delete("_rowid") if row.key?("_rowid") + decimals.each do |name, precision, scale| + row[name] = BigDecimal.new(row[name]) if row[name] === String end - rows << hashed_row + rows << row end end rows end - - def default_value(value) + + def default_value(value,type=nil) + return value if value.nil? + # Boolean type values return true if value =~ /true/ return false if value =~ /false/ - + # Alternative boolean default declarations + return true if (value == 1 && type == "boolean") + return false if (value == 0 && type == "boolean") + # Date / Time magic values return Time.now.to_s if value =~ /^now\(\)/i - - # Empty strings should be set to null + + # Empty strings should be set to nil return nil if value.empty? - + # Otherwise return what we got from OpenBase # and hope for the best... + # Take off the leading space and unquote + value.lstrip! + value = value[1,value.length-2] if value.first.eql?("'") && value.last.eql?("'") + return nil if value.eql?("NULL") return value - end - - def sql_type_name(type_name, length) - return "#{type_name}(#{length})" if ( type_name =~ /char/ ) - type_name end - - def index_name(row = []) + + def sql_type_name(table_name, col_name, type, length) + full_name = table_name.to_s + "." + col_name.to_s + if DECIMAL_COLUMNS.include?(full_name) && type != "longlong" + return "decimal(#{DECIMAL_COLUMNS[full_name][0]},#{DECIMAL_COLUMNS[full_name][1]})" + end + return "#{type}(#{length})" if ( type =~ /char/ ) + type + end + + def ob_index_name(row = []) name = "" name << "UNIQUE " if row[3] name << "CLUSTERED " if row[4] name << "INDEX" name end - - def translate_sql(sql) - - # Change table.* to list of columns in table - while (sql =~ /SELECT.*\s(\w+)\.\*/) - table = $1 - cols = columns(table) - if ( cols.size == 0 ) then - # Maybe this is a table alias - sql =~ /FROM(.+?)(?:LEFT|OUTER|JOIN|WHERE|GROUP|HAVING|ORDER|RETURN|$)/ - $1 =~ /[\s|,](\w+)\s+#{table}[\s|,]/ # get the tablename for this alias - cols = columns($1) - end - select_columns = [] - cols.each do |col| - select_columns << table + '.' + col.name - end - sql.gsub!(table + '.*',select_columns.join(", ")) if select_columns - end - - # Change JOIN clause to table list and WHERE condition - while (sql =~ /JOIN/) - sql =~ /((LEFT )?(OUTER )?JOIN (\w+) ON )(.+?)(?:LEFT|OUTER|JOIN|WHERE|GROUP|HAVING|ORDER|RETURN|$)/ - join_clause = $1 + $5 - is_outer_join = $3 - join_table = $4 - join_condition = $5 - join_condition.gsub!(/=/,"*") if is_outer_join - if (sql =~ /WHERE/) - sql.gsub!(/WHERE/,"WHERE (#{join_condition}) AND") - else - sql.gsub!(join_clause,"#{join_clause} WHERE #{join_condition}") + + def detect_decimals(sql) + # Detect any decimal columns that will need to be cast when fetched + decimals = [] + sql =~ /SELECT\s+(.*)\s+FROM\s+(\w+)/i + select_clause = $1 + main_table = $2 + if select_clause == "*" + column_names(main_table).each do |col| + full_name = main_table + "." + col + if DECIMAL_COLUMNS.include?(full_name) + decimals << [col,DECIMAL_COLUMNS[full_name][0].to_i,DECIMAL_COLUMNS[full_name][1].to_i] + end end - sql =~ /(FROM .+?)(?:LEFT|OUTER|JOIN|WHERE|$)/ - from_clause = $1 - sql.gsub!(from_clause,"#{from_clause}, #{join_table} ") - sql.gsub!(join_clause,"") end - + return decimals + end + + def add_order_by_rowid(sql) # ORDER BY _rowid if no explicit ORDER BY # This will ensure that find(:first) returns the first inserted row if (sql !~ /(ORDER BY)|(GROUP BY)/) @@ -322,10 +428,15 @@ module ActiveRecord sql << " ORDER BY _rowid" end end - sql end - + + def record_decimal(table_name, column_name, precision, scale) + full_name = table_name.to_s + "." + column_name.to_s + DECIMAL_COLUMNS.store(full_name, [precision.to_i,scale.to_i]) + direct_execute("INSERT INTO #{COLUMN_SUPPORT_TABLE} (name,type,precision,scale) VALUES ('#{full_name}','decimal',#{precision.to_i},#{scale.to_i})") + end + def update_nulls_after_insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) sql =~ /INSERT INTO (\w+) \((.*)\) VALUES\s*\((.*)\)/m table = $1 @@ -341,9 +452,8 @@ module ActiveRecord update_cols.each { |col| update_sql << " #{col}=NULL," unless col.empty? } update_sql.chop!() update_sql << " WHERE #{pk}=#{quote(id_value)}" - execute(update_sql, name + " NULL Correction") if update_cols.size > 0 + direct_execute(update_sql,"Null Correction") if update_cols.size > 0 end - end end end -- cgit v1.2.3