# * George Moschovitis # (c) 2004-2005 Navel, all rights reserved. # $Id: sqlite.rb 263 2005-02-23 13:45:08Z gmosx $ require 'sqlite3' require 'fileutils' require 'og/adapter' require 'og/connection' require 'glue/attribute' module Og # The SQLite adapter. This adapter communicates with # an SQLite3 rdbms. For extra documentation see # lib/og/adapter.rb class SqliteAdapter < Adapter def drop_db(database, user = nil, password = nil) begin FileUtils.rm("#{database}.db") super rescue Logger.error "Cannot drop '#{database}'!" end end =begin def write_prop(p) if p.klass.ancestors.include?(Integer) return "@#{p.symbol}" elsif p.klass.ancestors.include?(Float) return "@#{p.symbol}" elsif p.klass.ancestors.include?(String) return "#{self.class}.escape(@#{p.symbol})" elsif p.klass.ancestors.include?(Time) return %|#\{@#{p.symbol} ? "'#\{#{self.class}.timestamp(@#{p.symbol})\}'" : 'NULL'\}| elsif p.klass.ancestors.include?(Date) return %|#\{@#{p.symbol} ? "'#\{#{self.class}.date(@#{p.symbol})\}'" : 'NULL'\}| elsif p.klass.ancestors.include?(TrueClass) return "#\{@#{p.symbol} ? \"'t'\" : 'NULL' \}" else return %|#\{@#{p.symbol} ? "'#\{#{self.class}.escape(@#{p.symbol}.to_yaml)\}'" : "''"\}| end end =end def insert_code(klass, db, pre_cb, post_cb) props = props_for_insert(klass) values = props.collect { |p| write_prop(p) }.join(',') sql = "INSERT INTO #{klass::DBTABLE} (#{props.collect {|p| p.name}.join(',')}) VALUES (#{values})" %{ #{pre_cb} conn.store.query("#{sql}").close @oid = conn.store.last_insert_row_id #{post_cb} } =begin props = props_for_insert(klass) placeholders = Array.new(props.size, '?').join(',') values = props.collect { |p| write_prop(p) }.join(',') sql = "INSERT INTO #{klass::DBTABLE} (#{props.collect {|p| p.name}.join(',')}) VALUES (#{placeholders})" klass.class_eval %{ cattr_accessor :og_insert_statement } klass.og_insert_statement = db.prepare(sql) %{ #{pre_cb} @@og_insert_statement.execute(#{values}) @oid = conn.store.last_insert_row_id #{post_cb} } =end end def new_connection(db) return SqliteConnection.new(db) end def calc_field_index(klass, db) res = db.query "SELECT * FROM #{klass::DBTABLE} LIMIT 1" meta = db.managed_classes[klass] columns = res.columns for idx in (0...columns.size) meta.field_index[columns[idx]] = idx end ensure res.close end def create_table(klass, db) conn = db.get_connection fields = create_fields(klass) sql = "CREATE TABLE #{klass::DBTABLE} (#{fields.join(', ')}" # Create table constrains if klass.__meta and constrains = klass.__meta[:sql_constrain] sql << ", #{constrains.join(', ')}" end sql << ");" # Create indices if klass.__meta and indices = klass.__meta[:sql_index] for data in indices idx, options = *data idx = idx.to_s pre_sql, post_sql = options[:pre], options[:post] idxname = idx.gsub(/ /, "").gsub(/,/, "_").gsub(/\(.*\)/, "") sql << " CREATE #{pre_sql} INDEX #{klass::DBTABLE}_#{idxname}_idx #{post_sql} ON #{klass::DBTABLE} (#{idx});" end end begin conn.store.query(sql).close Logger.info "Created table '#{klass::DBTABLE}'." rescue Exception => ex # gmosx: any idea how to better test this? if ex.to_s =~ /table .* already exists/i Logger.debug "Table already exists" if $DBG return else raise end end # Create join tables if needed. Join tables are used in # 'many_to_many' relations. if klass.__meta and joins = klass.__meta[:sql_join] for data in joins # the class to join to and some options. join_class, options = *data # gmosx: dont use DBTABLE here, perhaps the join class # is not managed yet. join_table = "#{self.class.join_table(klass, join_class)}" join_src = "#{self.class.encode(klass)}_oid" join_dst = "#{self.class.encode(join_class)}_oid" begin conn.store.query("CREATE TABLE #{join_table} ( key1 integer NOT NULL, key2 integer NOT NULL )").close conn.store.query("CREATE INDEX #{join_table}_key1_idx ON #{join_table} (key1)").close conn.store.query("CREATE INDEX #{join_table}_key2_idx ON #{join_table} (key2)").close rescue Exception => ex # gmosx: any idea how to better test this? if ex.to_s =~ /table .* already exists/i Logger.debug "Join table already exists" if $DBG else raise end end end end ensure db.put_connection end end # The SQLite connection. class SqliteConnection < Connection def initialize(db) @store = SQLite3::Database.new("#{db.config[:database]}.db") super end def close @store.close super end def prepare(sql) @store.prepare(sql) end def query(sql) Logger.debug sql if $DBG begin return @store.query(sql) rescue => ex Logger.error "DB error #{ex}, [#{sql}]" Logger.error ex.backtrace.join("\n") return nil end end def exec(sql) Logger.debug sql if $DBG begin @store.query(sql).close rescue => ex Logger.error "DB error #{ex}, [#{sql}]" Logger.error ex.backtrace.join("\n") end end def start @store.transaction end def commit @store.commit end def rollback @store.rollback end def valid_res?(res) return !(res.nil?) end def read_one(res, klass) return nil unless valid_res?(res) row = res.next return nil unless row obj = klass.new obj.og_read(row) res.close return obj end def read_all(res, klass) return [] unless valid_res?(res) objects = [] res.each do |row| obj = klass.new obj.og_read(row) objects << obj end res.close return objects end def read_int(res, idx = 0) val = res.next[idx].to_i res.close return val end end end