1 require 'active_record/connection_adapters/abstract_adapter'
5 # Establishes a connection to the database that's used by all Active Record objects
6 def self.openbase_connection(config) # :nodoc:
7 require_library_or_gem 'openbase' unless self.class.const_defined?(:OpenBase)
9 config = config.symbolize_keys
11 username = config[:username].to_s
12 password = config[:password].to_s
15 if config.has_key?(:database)
16 database = config[:database]
18 raise ArgumentError, "No database specified. Missing argument: database."
21 oba = ConnectionAdapters::OpenBaseAdapter.new(
22 OpenBase.new(database, host, username, password), logger
30 module ConnectionAdapters
31 class OpenBaseColumn < Column #:nodoc:
33 def simplified_type(field_type)
34 return :integer if field_type.downcase =~ /long/
35 return :decimal if field_type.downcase == "money"
36 return :binary if field_type.downcase == "object"
40 # The OpenBase adapter works with the Ruby/Openbase driver by Tetsuya Suzuki.
41 # http://www.spice-of-life.net/ruby-openbase/ (needs version 0.7.3+)
45 # * <tt>:host</tt> -- Defaults to localhost
46 # * <tt>:username</tt> -- Defaults to nothing
47 # * <tt>:password</tt> -- Defaults to nothing
48 # * <tt>:database</tt> -- The name of the database. No default, must be provided.
50 # The OpenBase adapter will make use of OpenBase's ability to generate unique ids
51 # for any column with an unique index applied. Thus, if the value of a primary
52 # key is not specified at the time an INSERT is performed, the adapter will prefetch
53 # a unique id for the primary key. This prefetching is also necessary in order
54 # to return the id after an insert.
56 # Caveat: Operations involving LIMIT and OFFSET do not yet work!
58 # Maintainer: derrick.spell@gmail.com
59 class OpenBaseAdapter < AbstractAdapter
64 def native_database_types
66 :primary_key => "integer UNIQUE INDEX DEFAULT _rowid",
67 :string => { :name => "char", :limit => 4096 },
68 :text => { :name => "text" },
69 :integer => { :name => "integer" },
70 :float => { :name => "float" },
71 :decimal => { :name => "decimal" },
72 :datetime => { :name => "datetime" },
73 :timestamp => { :name => "timestamp" },
74 :time => { :name => "time" },
75 :date => { :name => "date" },
76 :binary => { :name => "object" },
77 :boolean => { :name => "boolean" }
81 def supports_migrations?
85 def prefetch_primary_key?(table_name = nil)
89 def default_sequence_name(table_name, primary_key) # :nodoc:
90 "#{table_name} #{primary_key}"
93 def next_sequence_value(sequence_name)
94 ary = sequence_name.split(' ')
96 ary[0] =~ /(\w+)_nonstd_seq/
99 @connection.unique_row_id(ary[0], ary[1])
103 # QUOTING ==================================================
105 def quote(value, column = nil)
106 if value.kind_of?(String) && column && column.type == :binary
107 "'#{@connection.insert_binary(value)}'"
123 # DATABASE STATEMENTS ======================================
125 def add_limit_offset!(sql, options) #:nodoc:
126 if limit = options[:limit]
127 unless offset = options[:offset]
128 sql << " RETURN RESULTS #{limit}"
130 limit = limit + offset
131 sql << " RETURN RESULTS #{offset} TO #{limit}"
136 def select_all(sql, name = nil) #:nodoc:
140 def select_one(sql, name = nil) #:nodoc:
141 add_limit_offset!(sql,{:limit => 1})
142 results = select(sql, name)
143 results.first if results
146 def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc:
148 update_nulls_after_insert(sql, name, pk, id_value, sequence_name)
152 def execute(sql, name = nil) #:nodoc:
153 log(sql, name) { @connection.execute(sql) }
156 def update(sql, name = nil) #:nodoc:
157 execute(sql, name).rows_affected
160 alias_method :delete, :update #:nodoc:
162 def begin_db_transaction #:nodoc:
163 execute "START TRANSACTION"
165 # Transactions aren't supported
168 def commit_db_transaction #:nodoc:
171 # Transactions aren't supported
174 def rollback_db_transaction #:nodoc:
177 # Transactions aren't supported
181 # SCHEMA STATEMENTS ========================================
183 # Return the list of all tables in the schema search path.
184 def tables(name = nil) #:nodoc:
185 tables = @connection.tables
186 tables.reject! { |t| /\A_SYS_/ === t }
190 def columns(table_name, name = nil) #:nodoc:
191 sql = "SELECT * FROM _sys_tables "
192 sql << "WHERE tablename='#{table_name}' AND INDEXOF(fieldname,'_')<>0 "
193 sql << "ORDER BY columnNumber"
195 select_all(sql, name).each do |row|
196 columns << OpenBaseColumn.new(row["fieldname"],
197 default_value(row["defaultvalue"]),
198 sql_type_name(row["typename"],row["length"]),
205 def indexes(table_name, name = nil)#:nodoc:
206 sql = "SELECT fieldname, notnull, searchindex, uniqueindex, clusteredindex FROM _sys_tables "
207 sql << "WHERE tablename='#{table_name}' AND INDEXOF(fieldname,'_')<>0 "
208 sql << "AND primarykey=0 "
209 sql << "AND (searchindex=1 OR uniqueindex=1 OR clusteredindex=1) "
210 sql << "ORDER BY columnNumber"
212 execute(sql, name).each do |row|
213 indexes << IndexDefinition.new(table_name,index_name(row),row[3]==1,[row[0]])
220 def select(sql, name = nil)
221 sql = translate_sql(sql)
222 results = execute(sql, name)
226 results.column_infos.each do |info|
227 col_names << info.name
228 date_cols << info.name if info.type == "date"
232 if ( results.rows_affected )
233 results.each do |row| # loop through result rows
235 row.each_index do |index|
236 hashed_row["#{col_names[index]}"] = row[index] unless col_names[index] == "_rowid"
238 date_cols.each do |name|
239 unless hashed_row["#{name}"].nil? or hashed_row["#{name}"].empty?
240 hashed_row["#{name}"] = Date.parse(hashed_row["#{name}"],false).to_s
249 def default_value(value)
250 # Boolean type values
251 return true if value =~ /true/
252 return false if value =~ /false/
254 # Date / Time magic values
255 return Time.now.to_s if value =~ /^now\(\)/i
257 # Empty strings should be set to null
258 return nil if value.empty?
260 # Otherwise return what we got from OpenBase
261 # and hope for the best...
265 def sql_type_name(type_name, length)
266 return "#{type_name}(#{length})" if ( type_name =~ /char/ )
270 def index_name(row = [])
272 name << "UNIQUE " if row[3]
273 name << "CLUSTERED " if row[4]
278 def translate_sql(sql)
280 # Change table.* to list of columns in table
281 while (sql =~ /SELECT.*\s(\w+)\.\*/)
283 cols = columns(table)
284 if ( cols.size == 0 ) then
285 # Maybe this is a table alias
286 sql =~ /FROM(.+?)(?:LEFT|OUTER|JOIN|WHERE|GROUP|HAVING|ORDER|RETURN|$)/
287 $1 =~ /[\s|,](\w+)\s+#{table}[\s|,]/ # get the tablename for this alias
292 select_columns << table + '.' + col.name
294 sql.gsub!(table + '.*',select_columns.join(", ")) if select_columns
297 # Change JOIN clause to table list and WHERE condition
298 while (sql =~ /JOIN/)
299 sql =~ /((LEFT )?(OUTER )?JOIN (\w+) ON )(.+?)(?:LEFT|OUTER|JOIN|WHERE|GROUP|HAVING|ORDER|RETURN|$)/
300 join_clause = $1 + $5
304 join_condition.gsub!(/=/,"*") if is_outer_join
306 sql.gsub!(/WHERE/,"WHERE (#{join_condition}) AND")
308 sql.gsub!(join_clause,"#{join_clause} WHERE #{join_condition}")
310 sql =~ /(FROM .+?)(?:LEFT|OUTER|JOIN|WHERE|$)/
312 sql.gsub!(from_clause,"#{from_clause}, #{join_table} ")
313 sql.gsub!(join_clause,"")
316 # ORDER BY _rowid if no explicit ORDER BY
317 # This will ensure that find(:first) returns the first inserted row
318 if (sql !~ /(ORDER BY)|(GROUP BY)/)
319 if (sql =~ /RETURN RESULTS/)
320 sql.sub!(/RETURN RESULTS/,"ORDER BY _rowid RETURN RESULTS")
322 sql << " ORDER BY _rowid"
329 def update_nulls_after_insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
330 sql =~ /INSERT INTO (\w+) \((.*)\) VALUES\s*\((.*)\)/m
334 cols = cols.split(',')
335 values.gsub!(/'[^']*'/,"''")
336 values.gsub!(/"[^"]*"/,"\"\"")
337 values = values.split(',')
339 values.each_index { |index| update_cols << cols[index] if values[index] =~ /\s*NULL\s*/ }
340 update_sql = "UPDATE #{table} SET"
341 update_cols.each { |col| update_sql << " #{col}=NULL," unless col.empty? }
343 update_sql << " WHERE #{pk}=#{quote(id_value)}"
344 execute(update_sql, name + " NULL Correction") if update_cols.size > 0