-
Notifications
You must be signed in to change notification settings - Fork 2
/
DB_remove.go
359 lines (310 loc) · 9.89 KB
/
DB_remove.go
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
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
package drugdose
import (
"context"
"errors"
"fmt"
"strconv"
"strings"
"database/sql"
// MySQL driver needed for sql module
_ "github.com/go-sql-driver/mysql"
// SQLite driver needed for sql module
_ "modernc.org/sqlite"
)
// CleanDB deletes all tables in the database.
// Make sure you don't have any other tables related to other projects in
// the database! It's a good idea to create different databases for
// every project.
//
// db - open database connection
//
// ctx - context to be passed to sql queries
func (cfg *Config) CleanDB(db *sql.DB, ctx context.Context) error {
const printN string = "CleanDB()"
queryStr := cfg.getTableNamesQuery("")
rows, err := db.QueryContext(ctx, queryStr)
if err != nil {
return fmt.Errorf("%s%w", sprintName(printN), err)
}
defer rows.Close()
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return fmt.Errorf("%s%w", sprintName(printN, "db.BeginTx(): "), err)
}
if cfg.VerbosePrinting == true {
printNameNoNewline(printN, "Removing tables: ")
}
for rows.Next() {
var name string
err = rows.Scan(&name)
if err != nil {
return fmt.Errorf("%s%w", sprintName(printN), err)
}
if cfg.VerbosePrinting == true {
fmt.Print(name + ", ")
}
_, err = tx.Exec("drop table " + name)
err = handleErrRollbackSeq(err, tx, printN, "tx.Exec(): ")
if err != nil {
return err
}
}
err = tx.Commit()
err = handleErrRollbackSeq(err, tx, printN, "tx.Commit(): ")
if err != nil {
return err
}
if cfg.VerbosePrinting == true {
fmt.Println()
}
printName(printN, "All tables removed from DB.")
return nil
}
// CleanInfoTable removes the currently configured info table. For example if source
// is set to "psychonautwiki" it will delete the table with the same name as
// the source, containing all data like dosages and timings. All user dosages
// aren't touched since they're not apart of the drug general information.
//
// db - open database connection
//
// ctx - context to be passed to sql queries
func (cfg *Config) CleanInfoTable(db *sql.DB, ctx context.Context) error {
const printN string = "CleanInfoTable()"
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return fmt.Errorf("%s%w", sprintName(printN, "db.BeginTx(): "), err)
}
_, err = tx.Exec("drop table " + cfg.UseSource)
err = handleErrRollbackSeq(err, tx, printN, "tx.Exec(): ")
if err != nil {
return err
}
err = tx.Commit()
err = handleErrRollbackSeq(err, tx, printN, "tx.Commit(): ")
if err != nil {
return err
}
printName(printN, "The info table: "+cfg.UseSource+"; removed from DB.")
return nil
}
// CleanNamesTables removes the main names tables and the currently configured ones
// as well. Names are "alternative names" like "weed" for "cannabis" and etc.
// Main names are global, they apply to all sources. Currently configured ones
// are source specific and are chosen based on the currently used source.
// This means, that any old names generated for another source aren't removed.
//
// db - open database connection
//
// ctx - context to be passed to sql queries
//
// replaceOnly - if true, remove only replace tables (source specific),
// keep the global ones intact
func (cfg *Config) CleanNamesTables(db *sql.DB, ctx context.Context, replaceOnly bool) error {
const printN string = "CleanNamesTables()"
tableSuffix := "_" + cfg.UseSource
tableNames := [8]string{altNamesSubsTableName,
altNamesRouteTableName,
altNamesUnitsTableName,
altNamesConvUnitsTableName,
altNamesSubsTableName + tableSuffix,
altNamesRouteTableName + tableSuffix,
altNamesUnitsTableName + tableSuffix,
altNamesConvUnitsTableName + tableSuffix}
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return fmt.Errorf("%s%w", sprintName(printN), err)
}
startCount := 0
if replaceOnly == true {
startCount = 4
}
printNameNoNewline(printN, "Removing tables: ")
for i := startCount; i < len(tableNames); i++ {
fmt.Print(tableNames[i] + ", ")
_, err = tx.Exec("drop table " + tableNames[i])
err = handleErrRollbackSeq(err, tx, printN, "tx.Exec(): ")
if err != nil {
fmt.Println()
return err
}
}
fmt.Println()
err = tx.Commit()
err = handleErrRollbackSeq(err, tx, printN, "tx.Commit(): ")
if err != nil {
return err
}
printName(printN, "All tables removed from DB.")
return nil
}
// RemoveLogs removes logs from the dose log table.
//
// db - open database connection
//
// ctx - context to be passed to sql queries
//
// errChannel - the gorouting channel which returns the errors
// (set to nil if function doesn't need to be concurrent)
//
// username - the user's logs that will be removed, no other user's logs will
// be touched
//
// amount - how many logs to remove, if 0 it removes all
//
// reverse - from which direction to start removing logs, if true go from high
// values to low values, this should remove the newest logs first,
// false is the opposite direction
//
// remID - if not 0, remove a specific log using it's start timestamp (ID)
//
// search - remove logs only matching this string
func (cfg *Config) RemoveLogs(db *sql.DB, ctx context.Context,
errChannel chan<- ErrorInfo, username string, amount int, reverse bool,
remID int64, search string, getExact string) ErrorInfo {
const printN string = "RemoveLogs()"
tempErrInfo := ErrorInfo{
Err: nil,
Username: username,
Action: ActionRemoveLogs,
}
stmtStr := "delete from " + loggingTableName + " where username = ?"
if (amount != 0 && remID == 0) || (search != "none" && search != "") {
if search != "none" && search != "" {
amount = 0
}
gotLogs := cfg.GetLogs(db, ctx, nil, amount, 0, username,
reverse, search, getExact)
if gotLogs.Err != nil {
tempErrInfo.Err = fmt.Errorf("%s%w", sprintName(printN), gotLogs.Err)
if errChannel != nil {
errChannel <- tempErrInfo
}
return tempErrInfo
}
var gotTimeOfDose []int64
var tempTimes int64
for i := 0; i < len(gotLogs.UserLogs); i++ {
tempTimes = gotLogs.UserLogs[i].StartTime
gotTimeOfDose = append(gotTimeOfDose, tempTimes)
}
concatTimes := ""
for i := 0; i < len(gotTimeOfDose); i++ {
concatTimes = concatTimes + strconv.FormatInt(gotTimeOfDose[i], 10) + ","
}
concatTimes = strings.TrimSuffix(concatTimes, ",")
stmtStr = "delete from " + loggingTableName + " where timeOfDoseStart in (" + concatTimes + ") AND username = ?"
} else if remID != 0 && (search == "none" || search == "") {
xtrs := [1]string{xtrastmt("username", "and")}
ret := checkIfExistsDB(db, ctx,
"timeOfDoseStart", loggingTableName,
cfg.DBDriver, cfg.DBSettings[cfg.DBDriver].Path,
xtrs[:], remID, username)
if !ret {
tempErrInfo.Err = fmt.Errorf("%s%w: %s%q", sprintName(printN), LogDoesntExistError, "with ID: ", remID)
if errChannel != nil {
errChannel <- tempErrInfo
}
return tempErrInfo
}
stmtStr = "delete from " + loggingTableName + " where timeOfDoseStart = ? AND username = ?"
}
tx, err := db.BeginTx(ctx, nil)
if err != nil {
tempErrInfo.Err = fmt.Errorf("%s%s: %w", sprintName(printN), "db.BeginTx()", err)
if errChannel != nil {
errChannel <- tempErrInfo
}
return tempErrInfo
}
stmt, err := tx.Prepare(stmtStr)
if handleErrRollback(err, tx, errChannel, &tempErrInfo, printN, "tx.Prepare(): ") {
return tempErrInfo
}
defer stmt.Close()
if remID != 0 {
_, err = stmt.Exec(remID, username)
} else {
_, err = stmt.Exec(username)
}
if handleErrRollback(err, tx, errChannel, &tempErrInfo, printN, "stmt.Exec(): ") {
return tempErrInfo
}
err = tx.Commit()
if handleErrRollback(err, tx, errChannel, &tempErrInfo, printN, "tx.Commit(): ") {
return tempErrInfo
}
printNameVerbose(cfg.VerbosePrinting, printN, "Data removed from log table in DB successfully: user:",
username, "; amount:", amount, "; reverse:", reverse, "; remID:", remID,
"; search:", search)
if errChannel != nil {
errChannel <- tempErrInfo
}
return tempErrInfo
}
// RemoveSingleDrugInfo removes all entries of a single drug from the local
// info DB, instead of deleting the whole DB/table. For example if there's a need to
// clear all information about dosage and timing for a specific drug if it's
// old or incorrect.
//
// db - open database connection
//
// ctx - context to be passed to sql queries
//
// errChannel - go routine channel which returns any errors
// (set to nil if function doesn't need to be concurrent)
//
// drug - name of drug to be removed from source table
//
// username - the user which requested the drug removal
func (cfg *Config) RemoveSingleDrugInfo(db *sql.DB, ctx context.Context,
errChannel chan<- ErrorInfo, drug string, username string) ErrorInfo {
const printN string = "RemoveSingleDrugInfo()"
drug = cfg.MatchAndReplace(db, ctx, drug, NameTypeSubstance)
tempErrInfo := ErrorInfo{
Err: nil,
Username: username,
Action: ActionRemoveSingleDrugInfo,
}
ret := checkIfExistsDB(db, ctx,
"drugName",
cfg.UseSource,
cfg.DBDriver,
cfg.DBSettings[cfg.DBDriver].Path,
nil,
drug)
if !ret {
tempErrInfo.Err = fmt.Errorf("%s%w: %q", sprintName(printN), NoDrugInfoTableError, drug)
if errChannel != nil {
errChannel <- tempErrInfo
}
return tempErrInfo
}
tx, err := db.BeginTx(ctx, nil)
if err != nil {
tempErrInfo.Err = fmt.Errorf("%s%s: %w", sprintName(printN), "db.BeginTx()", err)
if errChannel != nil {
errChannel <- tempErrInfo
}
return tempErrInfo
}
stmt, err := tx.Prepare("delete from " + cfg.UseSource +
" where drugName = ?")
if handleErrRollback(err, tx, errChannel, &tempErrInfo, printN, "tx.Prepare(): ") {
return tempErrInfo
}
defer stmt.Close()
_, err = stmt.Exec(drug)
if handleErrRollback(err, tx, errChannel, &tempErrInfo, printN, "stmt.Exec(): ") {
return tempErrInfo
}
err = tx.Commit()
if handleErrRollback(err, tx, errChannel, &tempErrInfo, printN, "tx.Commit(): ") {
return tempErrInfo
}
if errChannel != nil {
errChannel <- tempErrInfo
}
return tempErrInfo
}
var LogDoesntExistError error = errors.New("log doesn't exist")
var NoDrugInfoTableError error = errors.New("no such drug in the info (source) table")