<commit_before># name: discourse-data-explorer
# about: Interface for running analysis SQL queries on the live database
# version: 0.2
# authors: Riking
# url: https://github.com/discourse/discourse-data-explorer

enabled_site_setting :data_explorer_enabled
register_asset 'stylesheets/explorer.scss'

# route: /admin/plugins/explorer
add_admin_route 'explorer.title', 'explorer'

module ::DataExplorer
  def self.plugin_name
    'discourse-data-explorer'.freeze
  end

  def self.pstore_get(key)
    PluginStore.get(DataExplorer.plugin_name, key)
  end

  def self.pstore_set(key, value)
    PluginStore.set(DataExplorer.plugin_name, key, value)
  end

  def self.pstore_delete(key)
    PluginStore.remove(DataExplorer.plugin_name, key)
  end
end


after_initialize do

  module ::DataExplorer
    class Engine < ::Rails::Engine
      engine_name "data_explorer"
      isolate_namespace DataExplorer
    end

    class ValidationError < StandardError; end

    # Extract :colon-style parameters from the SQL query and replace them with
    # $1-style parameters.
    #
    # @return [Hash] :sql => [String] the new SQL query to run, :names =>
    #   [Array] the names of all parameters, in order by their $-style name.
    #   (The first name is $0.)
    def self.extract_params(sql)
      names = []
      new_sql = sql.gsub(/(:?):([a-z_]+)/) do |_|
        if $1 == ':' # skip casts
          $&
        else
          names << $2
          "$#{names.length - 1}"
        end
      end
      {sql: new_sql, names: names}
    end

    # Run a data explorer query on the currently connected database.
    #
    # @param [DataExplorer::Query] query the Query object to run
    # @param [Hash] params the colon-style query parameters to pass to AR
    # @param [Hash] opts hash of options
    #   explain - include a query plan in the result
    # @return [Hash]
    #   error - any exception that was raised in the execution. Check this
    #     first before looking at any other fields.
    #   pg_result - the PG::Result object
    #   duration_nanos - the query duration, in nanoseconds
    #   explain - the query
    def self.run_query(query, params={}, opts={})
      # Safety checks
      if query.sql =~ /;/
        err = DataExplorer::ValidationError.new(I18n.t('js.errors.explorer.no_semicolons'))
        return {error: err, duration_nanos: 0}
      end

      query_args = (query.qopts[:defaults] || {}).with_indifferent_access.merge(params)

      # Rudimentary types
      query_args.each do |k, arg|
        if arg =~ /\A\d+\z/
          query_args[k] = arg.to_i
        end
      end

      time_start, time_end, explain, err, result = nil
      begin
        ActiveRecord::Base.connection.transaction do
          # Setting transaction to read only prevents shoot-in-foot actions like SELECT FOR UPDATE
          ActiveRecord::Base.exec_sql "SET TRANSACTION READ ONLY"
          # SQL comments are for the benefits of the slow queries log
          sql = <<SQL

/*
 * DataExplorer Query
 * Query: /admin/plugins/explorer?id=#{query.id}
 * Started by: #{opts[:current_user]}
 */
WITH query AS (
#{query.sql}
) SELECT * FROM query
LIMIT #{opts[:limit] || 1000}
SQL

          time_start = Time.now
          result = ActiveRecord::Base.exec_sql(sql, query_args)
          time_end = Time.now

          if opts[:explain]
            explain = ActiveRecord::Base.exec_sql("EXPLAIN #{query.sql}", query_args)
                        .map { |row| row["QUERY PLAN"] }.join "\n"
          end

          # All done. Issue a rollback anyways, just in case
          raise ActiveRecord::Rollback
        end
      rescue Exception => ex
        err = ex
        time_end = Time.now
      end

      {
        error: err,
        pg_result: result,
        duration_nanos: time_end.nsec - time_start.nsec,
        explain: explain,
      }
    end

  end
<commit_msg>Prevent AR from using percent parameters<commit_after># name: discourse-data-explorer
# about: Interface for running analysis SQL queries on the live database
# version: 0.2
# authors: Riking
# url: https://github.com/discourse/discourse-data-explorer

enabled_site_setting :data_explorer_enabled
register_asset 'stylesheets/explorer.scss'

# route: /admin/plugins/explorer
add_admin_route 'explorer.title', 'explorer'

module ::DataExplorer
  def self.plugin_name
    'discourse-data-explorer'.freeze
  end

  def self.pstore_get(key)
    PluginStore.get(DataExplorer.plugin_name, key)
  end

  def self.pstore_set(key, value)
    PluginStore.set(DataExplorer.plugin_name, key, value)
  end

  def self.pstore_delete(key)
    PluginStore.remove(DataExplorer.plugin_name, key)
  end
end


after_initialize do

  module ::DataExplorer
    class Engine < ::Rails::Engine
      engine_name "data_explorer"
      isolate_namespace DataExplorer
    end

    class ValidationError < StandardError; end

    # Extract :colon-style parameters from the SQL query and replace them with
    # $1-style parameters.
    #
    # @return [Hash] :sql => [String] the new SQL query to run, :names =>
    #   [Array] the names of all parameters, in order by their $-style name.
    #   (The first name is $0.)
    def self.extract_params(sql)
      names = []
      new_sql = sql.gsub(/(:?):([a-z_]+)/) do |_|
        if $1 == ':' # skip casts
          $&
        else
          names << $2
          "$#{names.length - 1}"
        end
      end
      {sql: new_sql, names: names}
    end

    # Run a data explorer query on the currently connected database.
    #
    # @param [DataExplorer::Query] query the Query object to run
    # @param [Hash] params the colon-style query parameters to pass to AR
    # @param [Hash] opts hash of options
    #   explain - include a query plan in the result
    # @return [Hash]
    #   error - any exception that was raised in the execution. Check this
    #     first before looking at any other fields.
    #   pg_result - the PG::Result object
    #   duration_nanos - the query duration, in nanoseconds
    #   explain - the query
    def self.run_query(query, params={}, opts={})
      # Safety checks
      if query.sql =~ /;/
        err = DataExplorer::ValidationError.new(I18n.t('js.errors.explorer.no_semicolons'))
        return {error: err, duration_nanos: 0}
      end

      query_args = (query.qopts[:defaults] || {}).with_indifferent_access.merge(params)

      # Rudimentary types
      query_args.each do |k, arg|
        if arg =~ /\A\d+\z/
          query_args[k] = arg.to_i
        end
      end
      # If we don't include this, then queries with a % sign in them fail
      # because AR thinks we want percent-based parametes
      query_args[:xxdummy] = 1

      time_start, time_end, explain, err, result = nil
      begin
        ActiveRecord::Base.connection.transaction do
          # Setting transaction to read only prevents shoot-in-foot actions like SELECT FOR UPDATE
          ActiveRecord::Base.exec_sql "SET TRANSACTION READ ONLY"
          # SQL comments are for the benefits of the slow queries log
          sql = <<SQL

/*
 * DataExplorer Query
 * Query: /admin/plugins/explorer?id=#{query.id}
 * Started by: #{opts[:current_user]}
 * :xxdummy
 */
WITH query AS (
#{query.sql}
) SELECT * FROM query
LIMIT #{opts[:limit] || 1000}
SQL

          time_start = Time.now
          result = ActiveRecord::Base.exec_sql(sql, query_args)
          time_end = Time.now

          if opts[:explain]
            explain = ActiveRecord::Base.exec_sql("-- :xxdummy \nEXPLAIN #{query.sql}", query_args)
                        .map { |row| row["QUERY PLAN"] }.join "\n"
          end

          # All done. Issue a rollback anyways, just in case
          raise ActiveRecord::Rollback
        end
      rescue Exception => ex
        err = ex
        time_end = Time.now
      end

      {
        error: err,
        pg_result: result,
        duration_nanos: time_end.nsec - time_start.nsec,
        explain: explain,
      }
    end

  end

{'fraction_non_alphanumeric': 0.10190763052208836, 'fraction_numerical': 0.003639558232931727, 'mean_word_length': 2.472331154684096, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 9, 'https://': 2, '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': '1935156', 'n_tokens_mistral': 2503, 'n_tokens_neox': 2416, 'n_words': 726}