/
dbconn.rb
149 lines (130 loc) · 3.59 KB
/
dbconn.rb
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
142
143
144
145
146
147
148
149
require 'rubygems'
require 'sequel'
class Database
# connect with DB
def initialize
@db = Sequel.sqlite 'schrader.db'
createRcTable
createWLTable
createLogTable
end
# create a Whitelist Table if it doesn't exist
def createWLTable
@db.create_table? :wl do
primary_key :id
String :user
end
@wl = @db[:wl]
end
# create a Logger Table if it doesn't exist
def createLogTable
@db.create_table? :log do
primary_key :id
String :message
DateTime :created_at
end
@log = @db[:log]
end
# create an rcs table if it doesn't exist
def createRcTable
@db.create_table? :rcs do
primary_key :id
String :page
String :flags
String :diff
Integer :rcid
String :htmldiff
String :user
String :summary
DateTime :created_at
TrueClass :reviewed
end
@rcs = @db[:rcs]
end
# Drops the Rc table and creates it again
def resetRc
@db.drop_table(:rcs)
createRcTable
end
#clean Rc: set us up to date
def clearRc
@rcs.update(:reviewed => 1)
end
#clean WL: delete this list
def clearWL
@wl.delete
end
# Drops the Rc table and creates it again
def resetLog
@db.drop_table(:log)
createLogTable
end
# inserts an RC
def insertRc(page, flags, diff, rcid, user, summary, htmldiff)
if @wl.filter(:user => user).first
reviewed = 1
else
reviewed = 0
end
@rcs.insert(:page => page,
:flags => flags,
:diff => diff,
:user => user,
:summary => summary,
:created_at => Time.new.to_i,
:reviewed => reviewed,
:htmldiff => htmldiff,
:rcid => rcid
)
end
# Adds a message to the log
def insertLog(message)
@log.insert(:message => message,
:created_at => Time.new.to_i)
end
# Retrieve last log entries
def retrieveLog(num)
return @log.reverse_order(:created_at).limit(num)
end
# Adds an user to the Whitelist
# [_user_] user to be inserted
def addWL(user)
user.chomp!
if not @wl.filter(:user => user).first and not user =~ /^\s*$/
@wl.insert(:user => user)
#all his changes are reviewed
@rcs.filter(:user => user).update(:reviewed => 1)
end
end
# Removes an user from the whitelist
# [_id_] user to be inserted
def removeFromWL(id)
@wl.filter(:id => id).delete
end
# Retrieves the full whitelist
def getWL
return @wl.order(:user)
end
# Retrieves the first unreviewe rc
# [_id_] last reviewed id
def retrieveLastRc(id)
rc = @rcs.filter(:reviewed => 0).filter(:id < id).order(:created_at).last
return rc
end
# Retrieves the first unreviewe rc
# [_id_] last reviewed id
def retrieveRc(id)
rc = @rcs.filter(:reviewed => 0).filter(:id > id).order(:created_at).first
return rc
end
# Marks a diff as reviewed
# [_id_] id of the diff
def reviewedRc(id)
@rcs.filter(:id => id).update(:reviewed => 1)
end
# returns the number of Rcs
# [_id_] last reviewed id
def countRcs(id)
return @rcs.filter(:reviewed => 0).filter(:id > id).count
end
end