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
|