<commit_before># coding: UTF-8

class Table < Sequel::Model(:user_tables)

  # App constants
  PRIVATE = 0
  PUBLIC  = 1
  CARTODB_COLUMNS = %W{ cartodb_id created_at updated_at the_geom }
  THE_GEOM_WEBMERCATOR = :the_geom_webmercator
  THE_GEOM = :the_geom
  RESERVED_COLUMN_NAMES = %W{ oid tableoid xmin cmin xmax cmax ctid }

  # Ignore mass-asigment on not allowed columns
  self.strict_param_setting = false

  # Allowed columns
  set_allowed_columns(:privacy, :tags)

  attr_accessor :force_schema, :import_from_file,:import_from_url, :import_from_table_copy,
                :importing_SRID, :importing_encoding, :temporal_the_geom_type


  ## Callbacks
  def validate
    super
    errors.add(:user_id, 'can\'t be blank') if user_id.blank?
    errors.add(:privacy, 'has an invalid value') if privacy != PRIVATE && privacy != PUBLIC
    validates_unique [:name, :user_id], :message => 'is already taken'
  end

  def before_validation
    self.privacy ||= PRIVATE
    super
  end

  def before_create    
    update_updated_at
    self.database_name = owner.database_name    

    #import from file
    if import_from_file.present? or import_from_url.present? or import_from_table_copy.present?
      
      if import_from_file.present?        
        hash_in = ::Rails::Sequel.configuration.environment_for(Rails.env).merge(
          "database" => database_name, 
          :logger => ::Rails.logger,
          "username" => owner.database_username, 
          "password" => <PASSWORD>.database_password,
          :import_from_file => import_from_file, 
          :debug => (Rails.env.development?), 
          :remaining_quota => owner.remaining_quota
        ).symbolize_keys

        importer = CartoDB::Importer.new hash_in
        importer_result_name = importer.import!.name
      end

      #import from URL
      if import_from_url.present?
        importer = CartoDB::Importer.new ::Rails::Sequel.configuration.environment_for(Rails.env).merge(
          "database" => database_name, 
          :logger => ::Rails.logger,
          "username" => owner.database_username, 
          "password" => owner.database_password,
          :import_from_url => import_from_url, 
          :debug => (Rails.env.development?), 
          :remaining_quota => owner.remaining_quota
        ).symbolize_keys
        
        importer_result_name = importer.import!.name
      end

      #Import from copying another table
      if import_from_table_copy.present?
                
        # ensure unique name
        uniname = get_valid_name(self.name)
        owner.in_database.run("CREATE TABLE #{uniname} AS SELECT * FROM #{import_from_table_copy}")
        owner.in_database.run("CREATE INDEX ON #{uniname} USING GIST(the_geom)")
        owner.in_database.run("CREATE INDEX ON #{uniname} USING GIST(#{THE_GEOM_WEBMERCATOR})")
        owner.in_database.run("UPDATE #{uniname} SET created_at = now()")
        owner.in_database.run("UPDATE #{uniname} SET updated_at = now()")
        owner.in_database.run("ALTER TABLE #{uniname} ALTER COLUMN created_at SET DEFAULT now()")
        set_trigger_the_geom_webmercator
        importer_result_name = uniname
      end

      self[:name] = importer_result_name
      schema = self.schema(:reload => true)

      owner.in_database do |user_database|
        # If we already have a cartodb_id column let's rename it to an auxiliary column
        aux_cartodb_id_column = nil
        if schema.present? && schema.flatten.include?(:cartodb_id)
           aux_cartodb_id_column = "cartodb_id_aux_#{Time.now.to_i}"
           user_database.run("ALTER TABLE #{self.name} RENAME COLUMN cartodb_id TO #{aux_cartodb_id_column}")
        end

        # When tables are created using ogr2ogr they are added a ogc_fid primary key
        # In that case:
        #  - If cartodb_id already exists, remove ogc_fid
        #  - If cartodb_id does not exist, remove the primary key constraint and treat ogc_fid as the auxiliary column
        if schema.present? && schema.flatten.include?(:ogc_fid)
          if aux_cartodb_id_column.nil?
            aux_cartodb_id_column = "ogc_fid"
          else
            user_database.run("ALTER TABLE #{self.name} DROP COLUMN ogc_fid")
          end
        end
        if schema.present? && schema.flatten.include?(:gid)
          if aux_cartodb_id_column.nil?
            aux_cartodb_id_column = "gid"
          else
            user_database.run("ALTER TABLE #{self.name} DROP COLUMN gid")
          end
        end

        user_database.run("ALTER TABLE #{self.name} ADD COLUMN cartodb_id SERIAL")

        # If there's an auxiliary column, copy and restart the sequence to the max(cartodb_id)+1
        # Do this before adding constraints cause otherwise we can have duplicate key errors
        if aux_cartodb_id_column.present?
          user_database.run("UPDATE #{self.name} SET cartodb_id = CAST(#{aux_cartodb_id_column} AS INTEGER)")
          user_database.run("ALTER TABLE #{self.name} DROP COLUMN #{aux_cartodb_id_column}")
          cartodb_id_sequence_name = user_database["SELECT pg_get_serial_sequence('#{self.name}', 'cartodb_id')"].first[:pg_get_serial_sequence]
          max_cartodb_id = user_database["SELECT max(cartodb_id) FROM #{self.name}"].first[:max] 
          
          # only reset the sequence on real imports. 
          # skip for duplicate tables as they have totaly new names, but have aux_cartodb_id columns         
          if max_cartodb_id
            user_database.run("ALTER SEQUENCE #{cartodb_id_sequence_name} RESTART WITH #{max_cartodb_id+1}") 
          end  
        end

        user_database.run("ALTER TABLE #{self.name} ADD PRIMARY KEY (cartodb_id)")

        if schema.nil? || !schema.flatten.include?(:created_at)
          user_database.run("ALTER TABLE #{self.name} ADD COLUMN created_at timestamp DEFAULT NOW()")
        end
        if schema.nil? || !schema.flatten.include?(:updated_at)
          user_database.run("ALTER TABLE #{self.name} ADD COLUMN updated_at timestamp DEFAULT NOW()")
        end
      end
      set_the_geom_column!
    else
      create_table_in_database!
      if !self.temporal_the_geom_type.blank?
        self.the_geom_type = self.temporal_the_geom_type
        self.temporal_the_geom_type = nil<commit_msg>when a table is imported with created_at and updated_at fields they are casted to timestamp field<commit_after># coding: UTF-8

class Table < Sequel::Model(:user_tables)

  # App constants
  PRIVATE = 0
  PUBLIC  = 1
  CARTODB_COLUMNS = %W{ cartodb_id created_at updated_at the_geom }
  THE_GEOM_WEBMERCATOR = :the_geom_webmercator
  THE_GEOM = :the_geom
  RESERVED_COLUMN_NAMES = %W{ oid tableoid xmin cmin xmax cmax ctid }

  # Ignore mass-asigment on not allowed columns
  self.strict_param_setting = false

  # Allowed columns
  set_allowed_columns(:privacy, :tags)

  attr_accessor :force_schema, :import_from_file,:import_from_url, :import_from_table_copy,
                :importing_SRID, :importing_encoding, :temporal_the_geom_type


  ## Callbacks
  def validate
    super
    errors.add(:user_id, 'can\'t be blank') if user_id.blank?
    errors.add(:privacy, 'has an invalid value') if privacy != PRIVATE && privacy != PUBLIC
    validates_unique [:name, :user_id], :message => 'is already taken'
  end

  def before_validation
    self.privacy ||= PRIVATE
    super
  end

  def before_create    
    update_updated_at
    self.database_name = owner.database_name    

    #import from file
    if import_from_file.present? or import_from_url.present? or import_from_table_copy.present?
      
      if import_from_file.present?        
        hash_in = ::Rails::Sequel.configuration.environment_for(Rails.env).merge(
          "database" => database_name, 
          :logger => ::Rails.logger,
          "username" => owner.database_username, 
          "password" => <PASSWORD>,
          :import_from_file => import_from_file, 
          :debug => (Rails.env.development?), 
          :remaining_quota => owner.remaining_quota
        ).symbolize_keys

        importer = CartoDB::Importer.new hash_in
        importer_result_name = importer.import!.name
      end

      #import from URL
      if import_from_url.present?
        importer = CartoDB::Importer.new ::Rails::Sequel.configuration.environment_for(Rails.env).merge(
          "database" => database_name, 
          :logger => ::Rails.logger,
          "username" => owner.database_username, 
          "password" => <PASSWORD>,
          :import_from_url => import_from_url, 
          :debug => (Rails.env.development?), 
          :remaining_quota => owner.remaining_quota
        ).symbolize_keys
        
        importer_result_name = importer.import!.name
      end

      #Import from copying another table
      if import_from_table_copy.present?
                
        # ensure unique name
        uniname = get_valid_name(self.name)
        owner.in_database.run("CREATE TABLE #{uniname} AS SELECT * FROM #{import_from_table_copy}")
        owner.in_database.run("CREATE INDEX ON #{uniname} USING GIST(the_geom)")
        owner.in_database.run("CREATE INDEX ON #{uniname} USING GIST(#{THE_GEOM_WEBMERCATOR})")
        owner.in_database.run("UPDATE #{uniname} SET created_at = now()")
        owner.in_database.run("UPDATE #{uniname} SET updated_at = now()")
        owner.in_database.run("ALTER TABLE #{uniname} ALTER COLUMN created_at SET DEFAULT now()")
        set_trigger_the_geom_webmercator
        importer_result_name = uniname
      end

      self[:name] = importer_result_name
      schema = self.schema(:reload => true)

      owner.in_database do |user_database|
        # If we already have a cartodb_id column let's rename it to an auxiliary column
        aux_cartodb_id_column = nil
        if schema.present? && schema.flatten.include?(:cartodb_id)
           aux_cartodb_id_column = "cartodb_id_aux_#{Time.now.to_i}"
           user_database.run("ALTER TABLE #{self.name} RENAME COLUMN cartodb_id TO #{aux_cartodb_id_column}")
        end

        # When tables are created using ogr2ogr they are added a ogc_fid primary key
        # In that case:
        #  - If cartodb_id already exists, remove ogc_fid
        #  - If cartodb_id does not exist, remove the primary key constraint and treat ogc_fid as the auxiliary column
        if schema.present? && schema.flatten.include?(:ogc_fid)
          if aux_cartodb_id_column.nil?
            aux_cartodb_id_column = "ogc_fid"
          else
            user_database.run("ALTER TABLE #{self.name} DROP COLUMN ogc_fid")
          end
        end
        if schema.present? && schema.flatten.include?(:gid)
          if aux_cartodb_id_column.nil?
            aux_cartodb_id_column = "gid"
          else
            user_database.run("ALTER TABLE #{self.name} DROP COLUMN gid")
          end
        end

        user_database.run("ALTER TABLE #{self.name} ADD COLUMN cartodb_id SERIAL")

        # If there's an auxiliary column, copy and restart the sequence to the max(cartodb_id)+1
        # Do this before adding constraints cause otherwise we can have duplicate key errors
        if aux_cartodb_id_column.present?
          user_database.run("UPDATE #{self.name} SET cartodb_id = CAST(#{aux_cartodb_id_column} AS INTEGER)")
          user_database.run("ALTER TABLE #{self.name} DROP COLUMN #{aux_cartodb_id_column}")
          cartodb_id_sequence_name = user_database["SELECT pg_get_serial_sequence('#{self.name}', 'cartodb_id')"].first[:pg_get_serial_sequence]
          max_cartodb_id = user_database["SELECT max(cartodb_id) FROM #{self.name}"].first[:max] 
          
          # only reset the sequence on real imports. 
          # skip for duplicate tables as they have totaly new names, but have aux_cartodb_id columns         
          if max_cartodb_id
            user_database.run("ALTER SEQUENCE #{cartodb_id_sequence_name} RESTART WITH #{max_cartodb_id+1}") 
          end  
        end

        user_database.run("ALTER TABLE #{self.name} ADD PRIMARY KEY (cartodb_id)")

        if schema.nil? || !schema.flatten.include?(:created_at)
            user_database.run("ALTER TABLE #{self.name} ADD COLUMN created_at timestamp DEFAULT NOW()")
        end

        # if column already exists, cast to timestamp value and set default
        if !schema.nil? && schema.flatten.include?(:created_at)
            user_database.run(<<-ALTERCREATEDAT
                ALTER TABLE #{self.name} ALTER COLUMN created_at TYPE timestamp without time zone
                USING to_timestamp(created_at, 'YYYY-MM-DD HH24:MI:SS.MS.US'),
                ALTER COLUMN created_at SET DEFAULT now();
            ALTERCREATEDAT
            )
        end

        if schema.nil? || !schema.flatten.include?(:updated_at)
          user_database.run("ALTER TABLE #{self.name} ADD COLUMN updated_at timestamp DEFAULT NOW()")
        end

        # if column already exists, cast to timestamp value and set default
        if !schema.nil? && schema.flatten.include?(:updated_at)
            user_database.run(<<-ALTERUPDATEDAT
                ALTER TABLE #{self.name} ALTER COLUMN updated_at TYPE timestamp without time zone
                USING to_timestamp(updated_at, 'YYYY-MM-DD HH24:MI:SS.MS.US'),
                ALTER COLUMN updated_at SET DEFAULT now();
            ALTERUPDATEDAT
            )
        end

      end
      set_the_geom_column!
    else
      create_table_in_database!
      if !self.temporal_the_geom_type.blank?
        self.the_geom_type = self.temporal_the_geom_type
        self.temporal_the_geom_type = nil
{'fraction_non_alphanumeric': 0.0912777409127774, 'fraction_numerical': 0.0011796800117968, 'mean_word_length': 2.778272980501393, 'pattern_counts': {'":': 0, '<': 12, '<?xml version=': 0, '>': 38, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1733498', 'n_tokens_mistral': 4355, 'n_tokens_neox': 4176, 'n_words': 976}