summaryrefslogtreecommitdiffstats
path: root/lib/sqlite_helper.rb
blob: bfcc391ba75a4b2689033b7d09707c3c483f977e (plain) (blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
# frozen_string_literal: true

require 'sqlite3'
require 'yaml'

# This module contains methods interacting with the undelying sqlite
# database.
module SqliteHelper
  def init_db # rubocop:disable Metrics/MethodLength
    must_create = File.exist?('./data.db')
    @db = SQLite3::Database.new './data.db'
    if must_create
      @db.execute <<~SQL
        CREATE TABLE IF NOT EXISTS statuses (
          email TEXT(255) NOT NULL,
          name TEXT(100),
          token TEXT(30) NOT NULL,
          status TEXT(10) NOT NULL DEFAULT 'absent',
          eta TEXT(10),
          handle TEXT(20),
          details TEXT
        );
      SQL
      @db.execute(
        'CREATE UNIQUE INDEX IF NOT EXISTS statuses_email_idx ' \
        'ON statuses (email);'
      )
      @db.execute(
        'CREATE INDEX IF NOT EXISTS statuses_token_idx ' \
        'ON statuses (token);'
      )
      @db.execute(
        'CREATE UNIQUE INDEX IF NOT EXISTS statuses_handle_idx ' \
        'ON statuses (handle);'
      )
    end
    @db.results_as_hash = true
  end

  def create_status(email, token)
    @db.execute(
      'insert INTO statuses (email, token) VALUES (?, ?);',
      [email, token]
    )
  end

  def remove_status(email)
    @db.execute('delete FROM statuses WHERE email = ?;', [email])
  end

  def parse_status_details(status)
    status['details'] = YAML.safe_load(status['details'] || '') || {}
    status
  end

  def fetch_all_statuses(filter = nil)
    if filter
      results = @db.execute(
        'SELECT * FROM statuses ' \
        'WHERE name LIKE "%" || ? || "%" ' \
        'OR email LIKE "%" || ? || "%" ' \
        'OR handle LIKE "%" || ? || "%"' \
        'ORDER BY name;',
        [filter, filter, filter]
      )
    else
      results = @db.execute('SELECT * FROM statuses ORDER BY name;')
    end
    results.map { |status| parse_status_details(status) }
  end

  def fetch_status(crit, fields = '*', quick = false)
    return nil unless crit.is_a?(Hash) && crit.any?
    bindings = Array(crit)
    clauses = bindings.map { |k| "#{k[0]} = ?" }
    status = @db.execute(
      "SELECT #{fields} FROM statuses " \
      "WHERE #{clauses.join(' AND ')} LIMIT 1;",
      bindings.map(&:last)
    ).first
    return status if quick || !status
    parse_status_details(status)
  end

  def update_status(new_details, email)
    fields = []
    bindings = []
    new_details.each do |k, v|
      if ['status', 'name', 'eta', 'handle', 'token'].include?(k)
        fields << "#{k} = ?"
        bindings << v
        # Update @current_status if it exists
        @current_status[k] = v if @current_status
      else
        @details[k] = v
      end
    end
    @db.execute(
      "UPDATE statuses SET #{fields.join(', ')}, details = ? WHERE email = ?;",
      bindings + [YAML.dump(@details), email]
    )
  end
end