<commit_before>module MetricSystem::Database
  def self.extended(other)
    require_relative "./sqlite3_extensions"
  end

  def exec(*args, &block);        @db.exec(*args, &block);          end
  def select(*args, &block);      @db.select(*args, &block);        end
  def transaction(*args, &block); @db.transaction(*args, &block);   end
  def rollback(*args, &block);    @db.rollback(*args, &block);      end
  def print(*args, &block);       @db.print(*args, &block);         end
  def run(*args, &block);         @db.run(*args, &block);           end
  def ask(*args, &block);         @db.ask(*args, &block);           end

  PERIODS = [
    [ :year,   31536000, "strftime('%Y-01-01',          starts_at, 'unixepoch')" ],
    [ :month,   2592000, "strftime('%Y-%m-01',          starts_at, 'unixepoch')" ],
    [ :week,     604800, "strftime('%Y-%m-%d',          starts_at, 'unixepoch',  'weekday 1', '-7 days')" ],
    [ :day,       86400, "strftime('%Y-%m-%d',          starts_at, 'unixepoch')" ],
    [ :hour,       3600, "strftime('%Y-%m-%d %H:00:00', starts_at, 'unixepoch')" ],
    [ :minute,       60, "strftime('%Y-%m-%d %H:%M:00', starts_at, 'unixepoch')" ],
    # [ :second,        1, "strftime('%Y-%m-%d %H:%M:%S', starts_at, 'unixepoch')" ],
  ]

  private

  def open(path)
    @db = SQLite3::Database.new(path)

    [ :counters, :gauges ].each do |name|
      exec <<-SQL
      CREATE TABLE IF NOT EXISTS #{name}(
        id INTEGER PRIMARY KEY,

        name NOT NULL,                                                -- the event name
        value NOT NULL,                                               -- the value
        starts_at TIMESTAMP NOT NULL DEFAULT (strftime('%s','now'))   -- the timestamp
      );

      CREATE INDEX IF NOT EXISTS #{name}_idx1 ON #{name}(name, starts_at);

      CREATE TABLE IF NOT EXISTS aggregated_#{name}(
          id INTEGER PRIMARY KEY,

          name NOT NULL,                                      -- the event name
          starts_at TIMESTAMP NOT NULL,                       -- the start-at timestamp
          duration NOT NULL,                                  -- the duration (estimate, in secs.)
          period,                                             -- the name of the period (year, day, etc.)
          sum,                                                -- the sum of event values
          count,                                              -- the count of events
          value                                               -- the aggregated value
      );

      CREATE UNIQUE INDEX IF NOT EXISTS aggregated_#{name}_uidx1 ON aggregated_#{name}(name, starts_at, duration);
      CREATE INDEX IF NOT EXISTS aggregated_#{name}_idx2 ON aggregated_#{name}(starts_at);
      CREATE INDEX IF NOT EXISTS aggregated_#{name}_idx3 ON aggregated_#{name}(duration);
      SQL
    end

    exec <<-SQL
    PRAGMA synchronous = NORMAL;
    PRAGMA journal_mode = WAL;

    CREATE VIEW IF NOT EXISTS aggregates AS
      SELECT * FROM aggregated_gauges
        UNION
      SELECT * FROM aggregated_counters
    SQL
  end

  public

  def add_event(table, name, value, starts_at)
    # get names of all related events. An event "a.b.c" is actually
    # 3 events: "a", "a.b", "a.b.c"
    names = begin
      parts = name.split(".")
      parts.length.downto(1).map do |cnt|
        parts[0,cnt].join(".")
      end
    end

    if starts_at
      starts_at = Time.parse(starts_at) if starts_at.is_a?(String)

      names.each do |name|
        run "INSERT INTO #{table}(name, value, starts_at) VALUES(?, ?, ?)", name, value, starts_at.to_i
      end
    else
      names.each do |name|
        run "INSERT INTO #{table}(name, value) VALUES(?, ?)", name, value
      end
    end
  end

  public

  PERIODS_BY_KEY = PERIODS.by(&:first)

  def aggregate(*keys)
    if keys.empty?
      keys = PERIODS.map(&:first)
    end

    transaction do
      keys.each do |period|
        aggregate_for_period :period => period, :source => :counters, :dest => :aggregated_counters, :aggregate => "sum"
      end

      @db.exec "DELETE FROM counters"
    end

    transaction do
      keys.each do |period|
        aggregate_for_period :period => period, :source => :gauges, :dest => :aggregated_gauges, :aggregate => "CAST(sum AS FLOAT) / count"
      end

      @db.exec "DELETE FROM gauges"
    end
  end

  private

  def aggregate_for_period(options)
    expect! options => {<commit_msg>sqlite3: don't choke on exceptions.<commit_after>module MetricSystem::Database
  def self.extended(other)
    require_relative "./sqlite3_extensions"
  end

  def exec(*args, &block);        @db.exec(*args, &block);          end
  def select(*args, &block);      @db.select(*args, &block);        end
  def transaction(*args, &block); @db.transaction(*args, &block);   end
  def rollback(*args, &block);    @db.rollback(*args, &block);      end
  def print(*args, &block);       @db.print(*args, &block);         end
  def run(*args, &block);         @db.run(*args, &block);           end
  def ask(*args, &block);         @db.ask(*args, &block);           end

  PERIODS = [
    [ :year,   31536000, "strftime('%Y-01-01',          starts_at, 'unixepoch')" ],
    [ :month,   2592000, "strftime('%Y-%m-01',          starts_at, 'unixepoch')" ],
    [ :week,     604800, "strftime('%Y-%m-%d',          starts_at, 'unixepoch',  'weekday 1', '-7 days')" ],
    [ :day,       86400, "strftime('%Y-%m-%d',          starts_at, 'unixepoch')" ],
    [ :hour,       3600, "strftime('%Y-%m-%d %H:00:00', starts_at, 'unixepoch')" ],
    [ :minute,       60, "strftime('%Y-%m-%d %H:%M:00', starts_at, 'unixepoch')" ],
    # [ :second,        1, "strftime('%Y-%m-%d %H:%M:%S', starts_at, 'unixepoch')" ],
  ]

  private

  def open(path)
    @db = SQLite3::Database.new(path)

    [ :counters, :gauges ].each do |name|
      exec <<-SQL
      CREATE TABLE IF NOT EXISTS #{name}(
        id INTEGER PRIMARY KEY,

        name NOT NULL,                                                -- the event name
        value NOT NULL,                                               -- the value
        starts_at TIMESTAMP NOT NULL DEFAULT (strftime('%s','now'))   -- the timestamp
      );

      CREATE INDEX IF NOT EXISTS #{name}_idx1 ON #{name}(name, starts_at);

      CREATE TABLE IF NOT EXISTS aggregated_#{name}(
          id INTEGER PRIMARY KEY,

          name NOT NULL,                                      -- the event name
          starts_at TIMESTAMP NOT NULL,                       -- the start-at timestamp
          duration NOT NULL,                                  -- the duration (estimate, in secs.)
          period,                                             -- the name of the period (year, day, etc.)
          sum,                                                -- the sum of event values
          count,                                              -- the count of events
          value                                               -- the aggregated value
      );

      CREATE UNIQUE INDEX IF NOT EXISTS aggregated_#{name}_uidx1 ON aggregated_#{name}(name, starts_at, duration);
      CREATE INDEX IF NOT EXISTS aggregated_#{name}_idx2 ON aggregated_#{name}(starts_at);
      CREATE INDEX IF NOT EXISTS aggregated_#{name}_idx3 ON aggregated_#{name}(duration);
      SQL
    end

    exec <<-SQL
    PRAGMA synchronous = NORMAL;
    PRAGMA journal_mode = WAL;

    CREATE VIEW IF NOT EXISTS aggregates AS
      SELECT * FROM aggregated_gauges
        UNION
      SELECT * FROM aggregated_counters
    SQL
  end

  public

  def add_event(table, name, value, starts_at)
    # get names of all related events. An event "a.b.c" is actually
    # 3 events: "a", "a.b", "a.b.c"
    names = begin
      parts = name.split(".")
      parts.length.downto(1).map do |cnt|
        parts[0,cnt].join(".")
      end
    end

    if starts_at
      starts_at = Time.parse(starts_at) if starts_at.is_a?(String)

      names.each do |name|
        run "INSERT INTO #{table}(name, value, starts_at) VALUES(?, ?, ?)", name, value, starts_at.to_i
      end
    else
      names.each do |name|
        run "INSERT INTO #{table}(name, value) VALUES(?, ?)", name, value
      end
    end
  rescue
    STDERR.puts "#{$!}: #{table} #{name.inspect}, #{value.inspect}"
  end

  PERIODS_BY_KEY = PERIODS.by(&:first)

  def aggregate(*keys)
    if keys.empty?
      keys = PERIODS.map(&:first)
    end

    transaction do
      keys.each do |period|
        aggregate_for_period :period => period, :source => :counters, :dest => :aggregated_counters, :aggregate => "sum"
      end

      @db.exec "DELETE FROM counters"
    end

    transaction do
      keys.each do |period|
        aggregate_for_period :period => period, :source => :gauges, :dest => :aggregated_gauges, :aggregate => "CAST(sum AS FLOAT) / count"
      end

      @db.exec "DELETE FROM gauges"
    end
  end

  private

  def aggregate_for_period(options)
    expect! options => {
{'fraction_non_alphanumeric': 0.1324267082822428, 'fraction_numerical': 0.012596143127856426, 'mean_word_length': 1.878408726339429, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 30, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1051586', 'n_tokens_mistral': 2937, 'n_tokens_neox': 2755, 'n_words': 760}