forked from wbbaddons/Tims-Chatbot
-
Notifications
You must be signed in to change notification settings - Fork 0
/
db.coffee
141 lines (124 loc) · 5.28 KB
/
db.coffee
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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
###
# Chatbot for Tim's Chat 3
# Copyright (C) 2011 - 2014 Tim Düsterhus
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
###
config = require './config'
winston = require 'winston'
debug = (require 'debug')('Chatbot:db')
api = require './api'
sqlite = (require 'sqlite3').verbose()
db = new sqlite.Database config.database
# creates default tables
db.serialize ->
db.run "CREATE TABLE IF NOT EXISTS bot (
key VARCHAR(255),
value MEDIUMTEXT,
PRIMARY KEY(key)
);"
db.run "CREATE TABLE IF NOT EXISTS users (
userID INT(10),
lastUsername VARCHAR(255) DEFAULT '',
lastSeen INT(10),
password CHAR(20) DEFAULT NULL,
PRIMARY KEY(userID)
);"
db.run "CREATE TABLE IF NOT EXISTS user_to_permission (
userID INT(10),
permission VARCHAR(255),
PRIMARY KEY(userID, permission),
FOREIGN KEY(userID) REFERENCES users(userID)
);"
db.run "INSERT OR IGNORE INTO bot (key, value) VALUES (?, ?);", 'firstStart', Date.now()
process.on 'exit', ->
debug 'Closing database'
do db.close
# retrieves a user by the given username and calls the callback with the retrieved row
do ->
query = db.prepare "SELECT * FROM users WHERE lastUsername = ?"
db.getUserByUsername = (username, callback) -> query.get username, callback
# retrieves all permissions the given user has
do ->
query = db.prepare "SELECT permission FROM user_to_permission WHERE userID = ?"
db.getPermissionsByUserID = (userID, callback) ->
query.all userID, (err, rows) ->
if err?
winston.error "Error while checking permissions", err
else
callback rows
# checks whether the user with the given userID has the given permission
# and calls the callback with a boolean as the first parameter
do ->
query = db.prepare "SELECT COUNT(*) AS count FROM user_to_permission WHERE userID = ? AND permission = ?"
db.hasPermissionByUserID = (userID, permission, callback) ->
query.get userID, permission, (err, row) ->
if err?
winston.error "Error while checking permissions", err
else
callback row.count > 0
# Gives the permission to the user with the given userID
do ->
query = db.prepare "INSERT OR IGNORE INTO user_to_permission (userID, permission) VALUES (?, ?)"
db.givePermissionToUserID = (userID, permission, callback) ->
query.run userID, permission, (err, row) ->
if err?
winston.error "Error while giving permission", err
else
callback?()
# Removes the permission from the user with the given userID
do ->
query = db.prepare "DELETE FROM user_to_permission WHERE userID = ? AND permission = ?"
db.removePermissionFromUserID = (userID, permission, callback) ->
query.run userID, permission, (err, row) ->
if err?
winston.error "Error while giving permission", err
else
callback?()
# See `hasPermissionByUserID`, additionally whispers the user if he lacks permissions
db.checkPermissionByMessage = (message, permission, callback) ->
db.hasPermissionByUserID message.sender, permission, (result) ->
if result
callback yes
else
api.replyTo message, "Permission denied. You lack the required permission: #{permission}", no, -> callback no
# Check whether the user with the given userID has all the given permissions
db.hasAllPermissionsByUserID = (userID, permissions, callback) ->
db.get "SELECT COUNT(*) AS count FROM user_to_permission WHERE userID = ? AND permission IN (#{('?' for [0...permissions.length]).join(',')})", userID, permissions..., (err, row) ->
if err?
winston.error "Error while checking permissions", err
else
callback row.count is permissions.length
# Check whether the user with the given userID has any of the given permissions
db.hasAnyPermissionByUserID = (userID, permissions, callback) ->
db.get "SELECT COUNT(*) AS count FROM user_to_permission WHERE userID = ? AND permission IN (#{('?' for [0...permissions.length]).join(',')})", userID, permissions..., (err, row) ->
if err?
winston.error "Error while checking permissions", err
else
callback row.count > 0
# See `hasAllPermissionsByUserID`, additionally whispers the user if he lacks permissions
db.checkAllPermissionsByMessage = (message, permissions, callback) ->
db.hasAllPermissionsByUserID message.sender, permissions, (result) ->
if result
callback yes
else
api.replyTo message, "Permission denied. You lack some of the required permissions: #{permissions.join ', '}", no, -> callback no
# See `hasAnyPermissionsByUserID`, additionally whispers the user if he lacks permissions
db.checkAnyPermissionByMessage = (message, permissions, callback) ->
db.hasAnyPermissionByUserID message.sender, permissions, (result) ->
if result
callback yes
else
api.replyTo message, "Permission denied. You lack all the required permissions: #{permissions.join ', '}", no, -> callback no
module.exports = db