<commit_before>namespace :cartodb do
  namespace :db do
    desc "Add the_geom_webmercator column to every table which needs it"
    task :add_the_geom_webmercator => :environment do
      User.all.each do |user|
        tables = Table.filter(:user_id => user.id).all
        next if tables.empty?
        puts "Updating tables from #{user.username}"
        tables.each do |table|
          has_the_geom = false
          user.in_database do |user_database|
            flatten_schema = user_database.schema(table.name.to_sym).flatten
            if flatten_schema.include?(:the_geom) && !flatten_schema.include?(Table::THE_GEOM_WEBMERCATOR.to_sym)
              puts "Updating table #{table.name}"
              has_the_geom = true
              geometry_type = user_database["select GeometryType(the_geom) FROM #{table.name} limit 1"].first.try(:geometrytype) || "point"
              user_database.run("SELECT AddGeometryColumn ('#{table.name}','#{Table::THE_GEOM_WEBMERCATOR}',#{CartoDB::GOOGLE_SRID},'#{geometrytype}',2)")
              user_database.run("CREATE INDEX #{table.name}_#{Table::THE_GEOM_WEBMERCATOR}_idx ON #{table.name} USING GIST(#{Table::THE_GEOM_WEBMERCATOR})")                      
              user_database.run("VACUUM ANALYZE #{table.name}")
              table.update_stored_schema(user_database)
              table.save_changes
            end
          end
          if has_the_geom
            owner.in_database(:as => :superuser) do |user_database|
              user_database.run(<<-TRIGGER     
                DROP TRIGGER IF EXISTS update_the_geom_webmercator_trigger ON #{table.name};  
                CREATE OR REPLACE FUNCTION update_the_geom_webmercator() RETURNS trigger AS $update_the_geom_webmercator_trigger$
                  BEGIN
                       NEW.#{Table::THE_GEOM_WEBMERCATOR} := ST_Transform(NEW.the_geom,#{CartoDB::GOOGLE_SRID});
                       RETURN NEW;
                  END;
                $update_the_geom_webmercator_trigger$ LANGUAGE plpgsql VOLATILE COST 100;

                CREATE TRIGGER update_the_geom_webmercator_trigger 
                BEFORE INSERT OR UPDATE OF the_geom ON #{table.name} 
                  FOR EACH ROW EXECUTE PROCEDURE update_the_geom_webmercator();    
        TRIGGER
              )
            end
          end
        end
      end
    end
  end
end<commit_msg>Fixed maintenance task<commit_after>namespace :cartodb do
  namespace :db do
    desc "Permissions"
    task :give_permissions => :environment do
      User.all.each do |user|
        next if user.database_name.blank?
        user.in_database(:as => :superuser) do |user_database|
          user_database.run("REVOKE ALL ON DATABASE #{user.database_name} FROM public")
          user_database.run("REVOKE ALL ON SCHEMA public FROM public")
          user_database.run("GRANT ALL ON DATABASE #{user.database_name} TO #{user.database_username}")
          user_database.run("GRANT ALL ON SCHEMA public TO #{user.database_username}")
          user_database.run("GRANT ALL ON ALL TABLES IN SCHEMA public TO #{user.database_username}")
          user_database.run("GRANT CONNECT ON DATABASE #{user.database_name} TO #{CartoDB::PUBLIC_DB_USER}")
          user_database.run("GRANT USAGE ON SCHEMA public TO #{CartoDB::PUBLIC_DB_USER}")
          user_database.run("GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO #{CartoDB::PUBLIC_DB_USER}")
          user_database.run("GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO #{CartoDB::PUBLIC_DB_USER}")
        end
      end
    end
    desc "Add the_geom_webmercator column to every table which needs it"
    task :add_the_geom_webmercator => :environment do
      User.all.each do |user|
        tables = Table.filter(:user_id => user.id).all
        next if tables.empty?
        puts "Updating tables from #{user.username}"
        tables.each do |table|
          has_the_geom = false
          user.in_database do |user_database|
            flatten_schema = user_database.schema(table.name.to_sym).flatten
            if flatten_schema.include?(:the_geom) && !flatten_schema.include?(Table::THE_GEOM_WEBMERCATOR.to_sym)
              puts "Updating table #{table.name}"
              has_the_geom = true
              geometry_type = if col = user_database["select GeometryType(the_geom) FROM #{table.name} limit 1"].first
                col[:geometrytype]
              end
              geometry_type ||= "POINT"
              user_database.run("SELECT AddGeometryColumn ('#{table.name}','#{Table::THE_GEOM_WEBMERCATOR}',#{CartoDB::GOOGLE_SRID},'#{geometry_type}',2)")
              user_database.run("CREATE INDEX #{table.name}_#{Table::THE_GEOM_WEBMERCATOR}_idx ON #{table.name} USING GIST(#{Table::THE_GEOM_WEBMERCATOR})")                      
              user_database.run("VACUUM ANALYZE #{table.name}")
              table.update_stored_schema(user_database)
              table.save_changes
            end
          end
          if has_the_geom
            user.in_database(:as => :superuser) do |user_database|
              user_database.run(<<-TRIGGER     
                DROP TRIGGER IF EXISTS update_the_geom_webmercator_trigger ON #{table.name};  
                CREATE OR REPLACE FUNCTION update_the_geom_webmercator() RETURNS trigger AS $update_the_geom_webmercator_trigger$
                  BEGIN
                       NEW.#{Table::THE_GEOM_WEBMERCATOR} := ST_Transform(NEW.the_geom,#{CartoDB::GOOGLE_SRID});
                       RETURN NEW;
                  END;
                $update_the_geom_webmercator_trigger$ LANGUAGE plpgsql VOLATILE COST 100;

                CREATE TRIGGER update_the_geom_webmercator_trigger 
                BEFORE INSERT OR UPDATE OF the_geom ON #{table.name} 
                  FOR EACH ROW EXECUTE PROCEDURE update_the_geom_webmercator();    
        TRIGGER
              )
            end
          end
        end
      end
    end
  end
end
{'fraction_non_alphanumeric': 0.09101634923310298, 'fraction_numerical': 0.0016854879487611663, 'mean_word_length': 2.3773477518497437, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 11, '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': '2203572', 'n_tokens_mistral': 1870, 'n_tokens_neox': 1814, 'n_words': 373}