<commit_before>require_relative '../../lib/cartodb/stats/explore_api'
require_relative '../../lib/explore_api'

namespace :cartodb do

  namespace :explore_api do
    VISUALIZATIONS_TABLE = 'visualizations'

    PUBLIC_VISUALIZATIONS_VIEW = 'explore_api'
    CREATE_TABLE_SQL = %Q{
      CREATE TABLE #{VISUALIZATIONS_TABLE} (
        visualization_id UUID primary key,
        visualization_name text,
        visualization_description text,
        visualization_type text,
        visualization_synced boolean,
        visualization_table_names text[],
        visualization_table_rows integer,
        visualization_table_size integer,
        visualization_geometry_types text[],
        visualization_tags text[],
        visualization_bbox geometry,
        visualization_view_box geometry,
        visualization_view_box_center geometry,
        visualization_zoom integer,
        visualization_created_at timestamp with time zone,
        visualization_updated_at timestamp with time zone,
        visualization_map_id uuid,
        visualization_title text,
        visualization_likes integer,
        visualization_mapviews integer,
        user_id uuid,
        user_username text,
        user_organization_id uuid,
        user_twitter_username text,
        user_website text,
        user_avatar_url text,
        user_available_for_hire boolean,
        language regconfig default 'english'
      ) }
    CREATE_PUBLIC_VIEW = %Q{
      CREATE OR REPLACE VIEW #{PUBLIC_VISUALIZATIONS_VIEW} AS
        SELECT  visualization_id,
                visualization_name,
                visualization_description,
                visualization_type,
                visualization_table_rows,
                visualization_table_size,
                visualization_geometry_types,
                visualization_synced,
                visualization_tags,
                visualization_created_at,
                visualization_updated_at,
                visualization_map_id,
                visualization_title,
                visualization_likes,
                visualization_mapviews::numeric/(1.0 + (now()::date - visualization_created_at::date)::numeric)^2 AS popularity,
                user_id,
                user_username,
                user_organization_id,
                user_twitter_username,
                user_website,
                user_avatar_url,
                user_available_for_hire,
                language
        FROM visualizations
    }
    FULL_TEXT_SEARCHABLE_COLUMNS = %w{ visualization_name visualization_description visualization_title }
    INDEX_GEOMETRY_COLUMNS = %w{ visualization_bbox visualization_view_box }
    DROP_TABLE_SQL = %Q{ DROP TABLE IF EXISTS #{VISUALIZATIONS_TABLE} CASCADE}
    DROP_PUBLIC_VIEW_SQL = %Q{ DROP TABLE IF EXISTS #{PUBLIC_VISUALIZATIONS_VIEW} }
    MOST_RECENT_CREATED_SQL = %Q{ SELECT MAX(visualization_created_at) FROM #{VISUALIZATIONS_TABLE} }
    MOST_RECENT_UPDATED_SQL = %Q{ SELECT MAX(visualization_updated_at) FROM #{VISUALIZATIONS_TABLE} }
    BATCH_SIZE = 1000
    # TODO: "in" searches are limited to 300. To increase batch replace with date ranges
    UPDATE_BATCH_SIZE = 300
    DAYS_TO_CHECK_LIKES = 2

    desc "Creates #{VISUALIZATIONS_TABLE} at common-data user and loads the data for the very first time. This table contains an aggregated, desnormalized view of the public data at visualizations, and it's used by Explore API"
    task :setup => [:environment] do
      @explore_api = Helpers::ExploreAPI.new
      target_user.in_database.run CREATE_TABLE_SQL
      target_user.in_database.run CREATE_PUBLIC_VIEW

      update

      FULL_TEXT_SEARCHABLE_COLUMNS.each { |c|
        target_user.in_database.run "CREATE INDEX #{VISUALIZATIONS_TABLE}_#{c}_fts_idx ON #{VISUALIZATIONS_TABLE} USING gin(to_tsvector(language, #{c}))"
      }

      INDEX_GEOMETRY_COLUMNS.each { |c|
        target_user.in_database.run "CREATE INDEX #{VISUALIZATIONS_TABLE}_#{c}_geom_idx ON #{VISUALIZATIONS_TABLE} USING GIST(#{c})"
      }

      touch_metadata
    end

    task :setup_public_view => [:environment] do
      target_user.in_database.run CREATE_PUBLIC_VIEW
    end

    task :drop_public_view => [:environment] do
      target_user.in_database.run DROP_PUBLIC_VIEW_SQL
    end

    desc "Deletes the #{VISUALIZATIONS_TABLE} table"
    task :drop => [:environment] do
      target_user.in_database.run DROP_TABLE_SQL
      target_user.in_database.run DROP_PUBLIC_VIEW_SQL
    end

    desc "Updates the data at #{VISUALIZATIONS_TABLE}"
    task :update => [:environment] do
      @explore_api = ExploreAPI.new
      stats_aggregator.timing('visualizations.update.total') do
        update
        touch_metadata
      end
    end

    desc "Updates the all visualizations meta data at #{VISUALIZATIONS_TABLE}"
    task :update_metadata => [:environment] do
      @explore_api = ExploreAPI.new
      stats_aggregator.timing('visualizations.update_metadata.total') do
        update_visualizations_metadata
        touch_metadata
      end
    end

    def update_visualizations_metadata
      offset = 0
      while (explore_visualizations = get_explore_visualizations(offset)).length > 0
        updates = 0
        explore_visualization_ids = explore_visualizations.map { |ev| ev[:visualization_id] }
        visualizations = CartoDB::Visualization::Collection.new.fetch({ ids: explore_visualization_ids})
        bbox_values = get_visualizations_bbox(explore_visualization_ids)
        tables_data = @explore_api.get_visualizations_table_data(visualizations)
        visualizations.each do |v|
          update_visualization_metadata(v, tables_data, bbox_values)
          updates += 1
        end
        print "Batch size: #{explore_visualizations.length}.\tUpdated #{updates}\n"
        offset += explore_visualizations.length
      end
    end

    def update_visualization_metadata(visualization, tables_data, bbox_values)
      table_data = tables_data[visualization.user_id].nil? ? {} : tables_data[visualization.user_id][visualization.name]
      target_user.in_database.run update_mapviews_and_likes_query(visualization, bbox_values[visualization.id], table_data)
    end

    def update
      # We add one second because we have time fields with microseconds and this leads to
      # retrieve processed data crashing due constraint issues.
      # Ie. 2015-09-03 14:12:38+00 < 2015-09-03 14:12:38.294086+00 is true
      most_recent_created_date = target_user.in_database[MOST_RECENT_CREATED_SQL].first[:max]
      most_recent_created_date += 1 unless most_recent_created_date.nil?
      most_recent_updated_date = target_user.in_database[MOST_RECENT_UPDATED_SQL].first[:max]
      most_recent_updated_date += 1 unless most_recent_updated_date.nil?

      stats_aggregator.timing('visualizations.update.update_existing') do
        update_existing_visualizations_at_user
      end
      stats_aggregator.timing('visualizations.update.insert_new') do
        insert_new_visualizations_at_user(most_recent_created_date, most_recent_updated_date)
      end
    end

    def update_existing_visualizations_at_user
      deleted_visualization_ids = []
      privated_visualization_ids = []

      puts "UPDATING"

      # Get the last 2 days liked visualizations in order to use it as trigger to update likes, mapviews, etc
      date_to_check_likes = Time.now.beginning_of_day - DAYS_TO_CHECK_LIKES.days
      @liked_visualizations = @explore_api.visualization_likes_since(date_to_check_likes)<commit_msg>Added days to check for update as a parameter<commit_after>require_relative '../../lib/cartodb/stats/explore_api'
require_relative '../../lib/explore_api'

namespace :cartodb do

  namespace :explore_api do
    VISUALIZATIONS_TABLE = 'visualizations'

    PUBLIC_VISUALIZATIONS_VIEW = 'explore_api'
    CREATE_TABLE_SQL = %Q{
      CREATE TABLE #{VISUALIZATIONS_TABLE} (
        visualization_id UUID primary key,
        visualization_name text,
        visualization_description text,
        visualization_type text,
        visualization_synced boolean,
        visualization_table_names text[],
        visualization_table_rows integer,
        visualization_table_size integer,
        visualization_geometry_types text[],
        visualization_tags text[],
        visualization_bbox geometry,
        visualization_view_box geometry,
        visualization_view_box_center geometry,
        visualization_zoom integer,
        visualization_created_at timestamp with time zone,
        visualization_updated_at timestamp with time zone,
        visualization_map_id uuid,
        visualization_title text,
        visualization_likes integer,
        visualization_mapviews integer,
        user_id uuid,
        user_username text,
        user_organization_id uuid,
        user_twitter_username text,
        user_website text,
        user_avatar_url text,
        user_available_for_hire boolean,
        language regconfig default 'english'
      ) }
    CREATE_PUBLIC_VIEW = %Q{
      CREATE OR REPLACE VIEW #{PUBLIC_VISUALIZATIONS_VIEW} AS
        SELECT  visualization_id,
                visualization_name,
                visualization_description,
                visualization_type,
                visualization_table_rows,
                visualization_table_size,
                visualization_geometry_types,
                visualization_synced,
                visualization_tags,
                visualization_created_at,
                visualization_updated_at,
                visualization_map_id,
                visualization_title,
                visualization_likes,
                visualization_mapviews::numeric/(1.0 + (now()::date - visualization_created_at::date)::numeric)^2 AS popularity,
                user_id,
                user_username,
                user_organization_id,
                user_twitter_username,
                user_website,
                user_avatar_url,
                user_available_for_hire,
                language
        FROM visualizations
    }
    FULL_TEXT_SEARCHABLE_COLUMNS = %w{ visualization_name visualization_description visualization_title }
    INDEX_GEOMETRY_COLUMNS = %w{ visualization_bbox visualization_view_box }
    DROP_TABLE_SQL = %Q{ DROP TABLE IF EXISTS #{VISUALIZATIONS_TABLE} CASCADE}
    DROP_PUBLIC_VIEW_SQL = %Q{ DROP TABLE IF EXISTS #{PUBLIC_VISUALIZATIONS_VIEW} }
    MOST_RECENT_CREATED_SQL = %Q{ SELECT MAX(visualization_created_at) FROM #{VISUALIZATIONS_TABLE} }
    MOST_RECENT_UPDATED_SQL = %Q{ SELECT MAX(visualization_updated_at) FROM #{VISUALIZATIONS_TABLE} }
    BATCH_SIZE = 1000
    # TODO: "in" searches are limited to 300. To increase batch replace with date ranges
    UPDATE_BATCH_SIZE = 300
    DAYS_TO_CHECK_LIKES = 2

    desc "Creates #{VISUALIZATIONS_TABLE} at common-data user and loads the data for the very first time. This table contains an aggregated, desnormalized view of the public data at visualizations, and it's used by Explore API"
    task :setup => [:environment] do
      @explore_api = Helpers::ExploreAPI.new
      target_user.in_database.run CREATE_TABLE_SQL
      target_user.in_database.run CREATE_PUBLIC_VIEW

      update

      FULL_TEXT_SEARCHABLE_COLUMNS.each { |c|
        target_user.in_database.run "CREATE INDEX #{VISUALIZATIONS_TABLE}_#{c}_fts_idx ON #{VISUALIZATIONS_TABLE} USING gin(to_tsvector(language, #{c}))"
      }

      INDEX_GEOMETRY_COLUMNS.each { |c|
        target_user.in_database.run "CREATE INDEX #{VISUALIZATIONS_TABLE}_#{c}_geom_idx ON #{VISUALIZATIONS_TABLE} USING GIST(#{c})"
      }

      touch_metadata
    end

    task :setup_public_view => [:environment] do
      target_user.in_database.run CREATE_PUBLIC_VIEW
    end

    task :drop_public_view => [:environment] do
      target_user.in_database.run DROP_PUBLIC_VIEW_SQL
    end

    desc "Deletes the #{VISUALIZATIONS_TABLE} table"
    task :drop => [:environment] do
      target_user.in_database.run DROP_TABLE_SQL
      target_user.in_database.run DROP_PUBLIC_VIEW_SQL
    end

    desc "Updates the data at #{VISUALIZATIONS_TABLE}"
    task :update , [:days_since_to_update] => :environment do |t, args|
      days_since_to_check = args[:days_since_to_update].nil? ? DAYS_TO_CHECK_LIKES : args[:days_since_to_update].to_i
      @explore_api = ExploreAPI.new
      stats_aggregator.timing('visualizations.update.total') do
        update(days_since_to_check)
        touch_metadata
      end
    end

    desc "Updates the all visualizations meta data at #{VISUALIZATIONS_TABLE}"
    task :update_metadata => [:environment] do
      @explore_api = ExploreAPI.new
      stats_aggregator.timing('visualizations.update_metadata.total') do
        update_visualizations_metadata
        touch_metadata
      end
    end

    def update_visualizations_metadata
      offset = 0
      while (explore_visualizations = get_explore_visualizations(offset)).length > 0
        updates = 0
        explore_visualization_ids = explore_visualizations.map { |ev| ev[:visualization_id] }
        visualizations = CartoDB::Visualization::Collection.new.fetch({ ids: explore_visualization_ids})
        bbox_values = get_visualizations_bbox(explore_visualization_ids)
        tables_data = @explore_api.get_visualizations_table_data(visualizations)
        visualizations.each do |v|
          update_visualization_metadata(v, tables_data, bbox_values)
          updates += 1
        end
        print "Batch size: #{explore_visualizations.length}.\tUpdated #{updates}\n"
        offset += explore_visualizations.length
      end
    end

    def update_visualization_metadata(visualization, tables_data, bbox_values)
      table_data = tables_data[visualization.user_id].nil? ? {} : tables_data[visualization.user_id][visualization.name]
      target_user.in_database.run update_mapviews_and_likes_query(visualization, bbox_values[visualization.id], table_data)
    end

    def update(days_since_to_check)
      # We add one second because we have time fields with microseconds and this leads to
      # retrieve processed data crashing due constraint issues.
      # Ie. 2015-09-03 14:12:38+00 < 2015-09-03 14:12:38.294086+00 is true
      most_recent_created_date = target_user.in_database[MOST_RECENT_CREATED_SQL].first[:max]
      most_recent_created_date += 1 unless most_recent_created_date.nil?
      most_recent_updated_date = target_user.in_database[MOST_RECENT_UPDATED_SQL].first[:max]
      most_recent_updated_date += 1 unless most_recent_updated_date.nil?

      stats_aggregator.timing('visualizations.update.update_existing') do
        update_existing_visualizations_at_user(days_since_to_check)
      end
      stats_aggregator.timing('visualizations.update.insert_new') do
        insert_new_visualizations_at_user(most_recent_created_date, most_recent_updated_date)
      end
    end

    def update_existing_visualizations_at_user(days_since_to_check)
      deleted_visualization_ids = []
      privated_visualization_ids = []

      puts "UPDATING"

      # Get the last 2 days liked visualizations in order to use it as trigger to update likes, mapviews, etc
      date_to_check_likes = Time.now.beginning_of_day - days_since_to_check.days
      @liked_visualizations = @explore_api.visualization_likes_since(date_to_check_likes)
{'fraction_non_alphanumeric': 0.065729234793983, 'fraction_numerical': 0.007717462393721387, 'mean_word_length': 3.224033149171271, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 17, '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': '3485326', 'n_tokens_mistral': 4843, 'n_tokens_neox': 4457, 'n_words': 912}