lib/og/store/psql.rb in og-0.24.0 vs lib/og/store/psql.rb in og-0.25.0

@@ -3,10 +3,67 @@
 rescue Object => ex
   Logger.error 'Ruby-PostgreSQL bindings are not installed!'
   Logger.error ex
 end
 
+class PGconn
+  # Lists all the tables within the database.
+  
+  def list_tables 
+    r = self.exec "SELECT c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind='r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid)"
+    ret = r.result.flatten
+    r.clear
+    ret
+  end
+
+  # Returns true if a table exists within the database, false 
+  # otherwise.
+  
+  def table_exists?(table) #rp: this should be abstracted to the sql abstractor
+    r = self.exec "SELECT c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind='r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid) AND c.relname='#{self.class.escape(table.to_s)}'"
+    ret = r.result.size != 0
+    r.clear
+    ret
+  end
+
+  # Returns the PostgreSQL OID of a table within the database or 
+  # nil if it doesn't exist. Mostly for internal usage.
+  
+  def table_oid(table)
+    r = self.exec "SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind='r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid) AND c.relname='#{self.class.escape(table.to_s)}'"
+    ret = r.result.flatten.first
+    r.clear
+    ret
+  end
+
+  # Returns an array of arrays containing the list of fields within a
+  # table. Each element contains two elements, the first is the field
+  # name and the second is the field type. Returns nil if the table
+  # does not exist.
+  
+  def table_field_list(table)
+    return nil unless pg_oid = table_oid(table)
+    r = self.exec "SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) FROM pg_catalog.pg_attribute a WHERE a.attrelid = '#{pg_oid}' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum"
+    ret = r.result
+    r.clear
+    ret
+  end
+
+  # Returns an array of arrays containing the PostgreSQL foreign keys
+  # within a table. The first element is the constraint name and the
+  # second element is the constraint definition.
+  
+  def table_foreign_keys(table)
+    return nil unless pg_oid = table_oid(table)
+    r = self.exec "SELECT conname, pg_catalog.pg_get_constraintdef(oid, true) as condef FROM pg_catalog.pg_constraint r WHERE r.conrelid = '#{pg_oid}' AND r.contype = 'f'"
+    ret = r.result
+    r.clear
+    ret
+  end
+
+end
+
 require 'og/store/sql'
 
 # Customize the standard postgres resultset to make
 # more compatible with Og.
 
@@ -69,10 +126,12 @@
 end
 
 # A Store that persists objects into a PostgreSQL database.
 # To read documentation about the methods, consult the documentation
 # for SqlStore and Store.
+#
+# This is the reference Og store.
 # 
 # === Design
 #
 # The getvalue interface is used instead of each for extra
 # performance.
@@ -90,10 +149,32 @@
   def self.destroy(options)
     system 'dropdb', options[:name], '-U', options[:user]
     super
   end
 
+  # Purges all tables from the database.
+  
+  def self.destroy_tables(options)
+
+    conn = PGconn.connect(
+      options[:address],
+      options[:port], nil, nil,
+      options[:name],
+      options[:user].to_s,
+      options[:password].to_s
+    )
+
+    conn.list_tables.each do |table|
+      sql = "DROP TABLE #{table} CASCADE"
+      conn.exec sql
+      Logger.debug "Dropped database table #{table}"
+    end
+
+    conn.close
+  end
+
+
   def initialize(options)
     super
   
     @typemap.update(Og::Blob => 'bytea')
 
@@ -132,11 +213,11 @@
       klass.const_set 'OGSEQ', "#{table(klass.schema_inheritance_root_class)}_oid_seq"      
     else
       klass.const_set 'OGSEQ', "#{table(klass)}_oid_seq"
     end
 
-    if klass.ann.this.primary_key.symbol == :oid
+    if klass.ann.self.primary_key.symbol == :oid
       unless klass.properties.include? :oid
         klass.property :oid, Fixnum, :sql => 'serial PRIMARY KEY'
       end
     end
     super
@@ -163,92 +244,179 @@
     res.clear
     changed
   end
 
   # Start a new transaction.
-  
+
   def start  
     # neumann: works with earlier PSQL databases too.
     exec('BEGIN TRANSACTION') if @transaction_nesting < 1
     @transaction_nesting += 1
   end
 
 private
 
-  def create_table(klass)
-    fields = fields_for_class(klass)
+  # Adds foreign key constraints to a join table, replicating all
+  # modifications to OIDs to the join tables and also purging
+  # any left over data from deleted records (at the time of
+  # implementation, self-join cases left this data here).
 
-    sql = "CREATE TABLE #{klass::OGTABLE} (#{fields.join(', ')}"
-    
-    # Create table constraints.
-    
-    if constraints = klass.ann.this[:sql_constraint]
-      sql << ", #{constraints.join(', ')}"
+  def create_join_table_foreign_key_constraints(klass,info)
+
+    table_list = @conn.list_tables
+
+    needed_tables = [ info[:table], info[:first_table], info[:second_table] ]
+    missing_tables = Array.new
+    needed_tables.each do |table|
+      missing_tables << table unless table_list.include?(table)
     end
-    
-    sql << ") WITHOUT OIDS;"
-    
-    # Create indices.
-    
-    if indices = klass.ann.this[: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::OGTABLE}_#{idxname}_idx #{post_sql} ON #{klass::OGTABLE} (#{idx});"  
+    if missing_tables.size > 0
+      msg = "Join table #{info[:table]} needs PostgreSQL foreign key constraints but the following table"
+      msg << (missing_tables.size > 1 ? "s were " : " was ")
+      msg << "missing: "
+      missing_tables.each do |table|
+        msg << "#{table}, "
       end
+      Logger.debug msg[0..-3] + ". (Should be retried later)."
+      return false
     end
 
-    begin
-      @conn.exec(sql).clear
-      Logger.info "Created table '#{klass::OGTABLE}'."
-    rescue Object => ex
-      # gmosx: any idea how to better test this?
-      if ex.to_s =~ /relation .* already exists/i
-        Logger.debug 'Table already exists'
-        return
-      else
-        raise
+    #This info should maybe be in join metadata?
+    target_class = nil
+    klass.relations.each do |rel|
+      if rel.join_table == info[:table]
+        target_class =  rel.target_class
+        break
       end
     end
 
+    if target_class
+      Logger.debug "Adding PostgreSQL foreign key constraints to #{info[:table]}"
+
+      # TODO: This should also interrograte the constraint definition
+      # incase people meddle with the database in an insane fashion
+      # (very, very low priority)
+      
+      existing_constraints = @conn.table_foreign_keys(info[:table]).map {|fk| fk.first}
+
+      constraints = Array.new
+      constraints << { :table => info[:first_table], :join_table => info[:table], :primary_key => klass.primary_key.field || klass.primary_key.symbol, :foreign_key => info[:first_key] }
+      constraints << { :table => info[:second_table], :join_table => info[:table], :primary_key => target_class.primary_key.field || target_class.primary_key.symbol, :foreign_key => info[:second_key] }
+
+      constraints.each do |constraint|
+        constraint_name = "ogc_#{constraint[:table]}_#{constraint[:foreign_key]}"
+        if existing_constraints.include?(constraint_name)
+          Logger.debug "PostgreSQL foreign key constraint linking #{constraint[:foreign_key]} on #{constraint[:join_table]} to #{constraint[:primary_key]} on #{constraint[:table]} already exists (#{constraint_name})."
+          next
+        end
+        sql = "ALTER TABLE #{constraint[:join_table]} ADD CONSTRAINT #{constraint_name} FOREIGN KEY (#{constraint[:foreign_key]}) REFERENCES #{constraint[:table]} (#{constraint[:primary_key]}) ON UPDATE CASCADE ON DELETE CASCADE"
+        @conn.exec(sql).clear
+        Logger.debug "Added PostgreSQL foreign key constraint linking #{constraint[:foreign_key]} on #{constraint[:join_table]} to #{constraint[:primary_key]} on #{constraint[:table]} (#{constraint_name})."
+      end
+    end
+  end
+
+  def create_table(klass)
+    fields = fields_for_class(klass)
+
+    unless @conn.table_exists? klass::OGTABLE
+
+      sql = "CREATE TABLE #{klass::OGTABLE} (#{fields.join(', ')}"
+
+      # Create table constraints.
+
+      if constraints = klass.ann.self[:sql_constraint]
+        sql << ", #{constraints.join(', ')}"
+      end
+
+      sql << ") WITHOUT OIDS;"
+
+      # Create indices.
+
+      if indices = klass.ann.self[: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::OGTABLE}_#{idxname}_idx #{post_sql} ON #{klass::OGTABLE} (#{idx});"  
+        end
+      end
+
+        @conn.exec(sql).clear
+        Logger.info "Created table '#{klass::OGTABLE}'."
+    else
+      Logger.debug "Table #{klass::OGTABLE} already exists"
+      #rp: basic field interrogation
+      # TODO: Add type checking.
+
+      actual_fields = @conn.table_field_list(klass::OGTABLE).map {|pair| pair.first}
+
+      #Make new ones always - don't destroy by default because it might contain data you want back.
+      need_fields = fields.each do |needed_field|
+        field_name = needed_field[0..(needed_field.index(' ')-1)]
+        next if actual_fields.include?(field_name)
+
+        if @options[:evolve_schema] == true
+          Logger.debug "Adding field '#{needed_field}' to '#{klass::OGTABLE}'"
+          sql = "ALTER TABLE #{klass::OGTABLE} ADD COLUMN #{needed_field}"
+          @conn.exec(sql)
+        else
+          Logger.info "WARNING: Table '#{klass::OGTABLE}' is missing field '#{needed_field}' and :evolve_schema is not set to true!"
+        end
+      end
+
+      #Drop old ones
+      needed_fields = fields.map {|f| f =~ /^([^ ]+)/; $1}
+      actual_fields.each do |obsolete_field|
+        next if needed_fields.include?(obsolete_field)
+        if @options[:evolve_schema] == true and @options[:evolve_schema_cautious] == false
+          sql = "ALTER TABLE #{klass::OGTABLE} DROP COLUMN #{obsolete_field}"
+          Logger.debug "Removing obsolete field '#{obsolete_field}' from '#{klass::OGTABLE}'"
+          @conn.exec(sql)
+       else
+          Logger.info "WARNING: You have an obsolete field '#{obsolete_field}' on table '#{klass::OGTABLE}' and :evolve_schema is not set or is in cautious mode!"
+        end
+      end
+    end
+
     # Create join tables if needed. Join tables are used in
     # 'many_to_many' relations.
-    
-     if join_tables = klass.ann.this[:join_tables] 
-      for info in join_tables
-        begin
-          create_join_table_sql(info).each do |sql|
-            @conn.exec(sql).clear
-          end
-          Logger.debug "Created jointable '#{info[:table]}'."
-        rescue Object => ex
-          # gmosx: any idea how to better test this?
-          if ex.to_s =~ /relation .* already exists/i
-            Logger.debug 'Join table already exists' if $DBG
+      players = klass.resolve_remote_relations.map{|rel| rel.owner_class if rel.collection}.compact.uniq
+      players << klass
+      players.each do |player|
+      if join_tables = player.ann.self[:join_tables] 
+        for info in join_tables
+          unless @conn.table_exists? info[:table]
+            create_join_table_sql(info).each do |sql|
+              @conn.exec(sql).clear
+            end
+            Logger.debug "Created jointable '#{info[:table]}'."
           else
-            raise
+            Logger.debug "Join table '#{info[:table]}' already exists."
           end
+          create_join_table_foreign_key_constraints(player,info)
         end
       end
     end
+
   end
 
   def drop_table(klass)
-    super
-    exec "DROP SEQUENCE #{klass::OGSEQ}"    
+    # foreign key constraints will remove the need to do manual cleanup on
+    # postgresql join tables.
+    exec "DROP TABLE #{klass.table} CASCADE"
   end
 
   def create_field_map(klass)
     res = @conn.exec "SELECT * FROM #{klass::OGTABLE} LIMIT 1"
     map = {}
-  
+
     for field in res.fields
       map[field.intern] = res.fieldnum(field)
     end
-  
+
     return map
   ensure
     res.clear if res
   end
 
@@ -282,27 +450,27 @@
 
     if klass.schema_inheritance?
       props << Property.new(:symbol => :ogtype, :klass => String)
       values << ", '#{klass}'"
     end
-    
+
     sql = "INSERT INTO #{klass::OGTABLE} (#{props.collect {|p| field_for_property(p)}.join(',')}) VALUES (#{values})"
 
     klass.class_eval %{ 
       def og_insert(store)
-        #{Aspects.gen_advice_code(:og_insert, klass.advices, :pre) if klass.respond_to?(:advices)}
+        #{Glue::Aspects.gen_advice_code(:og_insert, klass.advices, :pre) if klass.respond_to?(:advices)}
         res = store.conn.exec "SELECT nextval('#{klass::OGSEQ}')"
         @#{klass.pk_symbol} = res.getvalue(0, 0).to_i
         res.clear
         store.conn.exec("#{sql}").clear
-        #{Aspects.gen_advice_code(:og_insert, klass.advices, :post) if klass.respond_to?(:advices)}
+        #{Glue::Aspects.gen_advice_code(:og_insert, klass.advices, :post) if klass.respond_to?(:advices)}
       end
     }
   end
 
   def eval_og_allocate(klass)  
-    if klass.ann.this[:subclasses]
+    if klass.ann.self[:subclasses]
       klass.module_eval %{
         def self.og_allocate(res, row = 0)
           Object.constant(res.getvalue(row, 0)).allocate
         end
       }
@@ -326,5 +494,6 @@
 end
 
 # * George Moschovitis <[e-mail]>
 # * Michael Neumann <[e-mail]>
 # * Ysabel <[e-mail]>
+# * Rob Pitt <[e-mail]>
\ No newline at end of file