migration

gistfile1.txt

task duplicate_imei: :environment do
  Company.active.each do |company|
    begin
      SwitchTenant.switch!(company.tenant)
      duplicate_imei_ids = []
      GpsLoggerMetadataItem.select(:id, :imei).each do |l|  
        duplicate_imei_ids << GpsLogger.find_by(imei: l.imei).id if GpsLogger.where(imei: l.imei).size > 1
      end

      unless duplicate_imei_ids.blank?
        puts ' '
        puts '############################################'
        p "#{company.tenant} has duplication GpsLogger ids: #{duplicate_imei_ids}"
        puts '############################################'
        puts ' '
      end
    rescue => e
      puts e
    end
  end
end


task gps_logger_ids_replacement: :environment do
  Company.active.each do |company|
    begin
      ActiveRecord::Base.transaction do
        SwitchTenant.switch!(company.tenant)

        puts ' '
        puts "#{company.tenant} - !!!!!Start migration!!!!!"
        puts ' '

        metadata_items = GpsServerBase.connection.execute('
            SELECT id, imei FROM gps_logger_metadata_items
        ').to_a

        ds = [0]
        metadata_items.each do |item|
          req = ActiveRecord::Base.connection.execute("
            UPDATE data_source_gps_loggers ds
            SET gps_logger_id = #{item['id']}
            FROM (SELECT id, imei FROM gps_loggers) as gp
            WHERE gps_logger_id = gp.id
            AND gp.imei = '#{item['imei']}'
          AND ds.id NOT IN (#{ds.join(",")})
          RETURNING ds.id;
          ")
          ds = ds + req.to_a.map{|x| x['id']}
        end

        gl = [0]
        metadata_items.each do |item|
          req = ActiveRecord::Base.connection.execute("
            UPDATE gps_logger_mapping_items gl
            SET gps_logger_id = #{item['id']}
            FROM (SELECT id, imei FROM gps_loggers) as gp
            WHERE gps_logger_id = gp.id
            AND gp.imei = '#{item['imei']}'
            AND gl.id NOT IN (#{gl.join(",")})
            RETURNING gl.id;
          ")
          gl = gl + req.to_a.map{|x| x['id']}
        end 

        # !!!! rewrite gps loggers ids !!!!!
        ActiveRecord::Base.connection.execute('
          ALTER TABLE gps_loggers DROP CONSTRAINT gps_loggers_pkey
        ')

        # find gps logger with the same metadata imei key:
        gps_with_history_ids = []
        GpsLoggerMetadataItem.select(:id, :imei).each do |l| 
          gps_with_history_ids << GpsLogger.find_by(imei: l.imei).id if GpsLogger.where(imei: l.imei).size > 0 
        end 

        # change ids for logger without imei identention:
        gps_null = GpsLogger.pluck(:id) - gps_with_history_ids
        
        ActiveRecord::Base.transaction do
          gps_null.each do |g| 
            num = (g.to_s + 9999.to_s).to_i
            ActiveRecord::Base.connection.execute("
              UPDATE gps_loggers SET id = #{num} WHERE id = #{g};
              UPDATE data_source_gps_loggers SET gps_logger_id = #{num} WHERE id = #{g};
              UPDATE gps_logger_mapping_items SET gps_logger_id = #{num} WHERE id = #{g};
            ")
          end
        end

        # rewrite gps loggers ids
        gps = [0]
        metadata_items.each do |item|
          req = ActiveRecord::Base.connection.execute("
            UPDATE gps_loggers gps
            SET id = #{item['id']}
            FROM (SELECT id, imei FROM gps_loggers) as gp
            WHERE gps.imei = '#{item['imei']}'
            AND gps.id NOT IN (#{gps.join(",")});
          ")
        end

        ActiveRecord::Base.connection.execute('
          ALTER TABLE gps_loggers ADD PRIMARY KEY (id)
        ')

        puts ' '
        puts "#{company.tenant} - !!!!!Finish. Done.!!!!!"
        puts ' '
      end
    rescue => e
      puts e
    end
  end
end