You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
154 lines
4.5 KiB
154 lines
4.5 KiB
3 years ago
|
package main
|
||
|
|
||
|
import (
|
||
|
"database/sql"
|
||
|
"fmt"
|
||
|
"time"
|
||
|
|
||
|
_ "github.com/mattn/go-sqlite3"
|
||
|
)
|
||
|
|
||
|
// Inital func to ensure database is properly setup
|
||
|
func dbSetup() {
|
||
|
database, _ := sql.Open("sqlite3", config.DbFile)
|
||
|
defer database.Close()
|
||
|
statement, _ := database.Prepare("CREATE TABLE IF NOT EXISTS users (email TEXT PRIMARY KEY, discordID TEXT, mcUser TEXT, lastSeen TEXT, lastReported TEXT)")
|
||
|
statement.Exec()
|
||
|
statement, _ = database.Prepare("CREATE TABLE IF NOT EXISTS reports (email TEXT PRIMARY KEY, reportedBy TEXT, reportMsg TEXT, reportTime TEXT)")
|
||
|
statement.Exec()
|
||
|
}
|
||
|
|
||
|
// Write a user to database, also update last seen to time.Now()
|
||
|
func (u User) Write() error {
|
||
|
database, _ := sql.Open("sqlite3", config.DbFile)
|
||
|
defer database.Close()
|
||
|
if u.Email == "" || u.DiscordID == "" || u.McUser == "" {
|
||
|
log.LogDebug(fmt.Sprintf("Attempted to insert invalid user. DENIED.\n%+v", u))
|
||
|
return nil
|
||
|
}
|
||
|
statement, err := database.Prepare("INSERT INTO users (email, discordID, mcUser, lastSeen, lastReported) VALUES (?, ?, ?, ?, ?)")
|
||
|
if err != nil {
|
||
|
return err
|
||
|
}
|
||
|
log.LogDebug(fmt.Sprintf("Running insert on %+v", u))
|
||
|
statement.Exec(u.Email, u.DiscordID, u.McUser, time.Now(), u.LastReported)
|
||
|
return nil
|
||
|
}
|
||
|
|
||
|
// Delete a user from the database.
|
||
|
func (u User) Delete() error {
|
||
|
database, err := sql.Open("sqlite3", config.DbFile)
|
||
|
if err != nil {
|
||
|
return err
|
||
|
}
|
||
|
defer database.Close()
|
||
|
statement, err := database.Prepare("DELETE FROM users WHERE email=? mcUser=? discordID=?")
|
||
|
if err != nil {
|
||
|
return err
|
||
|
}
|
||
|
_, err = statement.Exec(u.Email, u.McUser, u.DiscordID)
|
||
|
return err
|
||
|
}
|
||
|
|
||
|
// Get a user by a field (deprecated?)
|
||
|
func (u User) GetUserBy(userfield string) {
|
||
|
database, _ := sql.Open("sqlite3", config.DbFile)
|
||
|
defer database.Close()
|
||
|
rows, _ := database.Query("SELECT * FROM users WHERE ? = ?", userfield, userGetField(&u, userfield))
|
||
|
for rows.Next() {
|
||
|
rows.Scan(&u.Email, &u.DiscordID, &u.McUser, &u.LastSeen, &u.LastReported)
|
||
|
}
|
||
|
}
|
||
|
|
||
|
// Retrieve a user and fill in missing values
|
||
|
func (u *User) Retrieve() error {
|
||
|
database, _ := sql.Open("sqlite3", config.DbFile)
|
||
|
defer database.Close()
|
||
|
var parms []interface{}
|
||
|
statement := "SELECT * FROM users WHERE"
|
||
|
first := true
|
||
|
if u.DiscordID != "" {
|
||
|
parms = append(parms, u.DiscordID)
|
||
|
statement += " discordID = ? "
|
||
|
first = false
|
||
|
}
|
||
|
if u.Email != "" {
|
||
|
parms = append(parms, u.Email)
|
||
|
if !first {
|
||
|
statement += " AND "
|
||
|
} else {
|
||
|
first = false
|
||
|
}
|
||
|
statement += " email = ?"
|
||
|
}
|
||
|
if u.McUser != "" {
|
||
|
parms = append(parms, u.McUser)
|
||
|
if !first {
|
||
|
statement += " AND "
|
||
|
} else {
|
||
|
first = false
|
||
|
}
|
||
|
statement += " mcUser = ?"
|
||
|
}
|
||
|
log.LogDebug(fmt.Sprintf("Attempting to query using the following statement: %+v\nParams: %+v", statement, parms))
|
||
|
rows, err := database.Query(statement, parms...)
|
||
|
if err != nil {
|
||
|
return err
|
||
|
}
|
||
|
for rows.Next() {
|
||
|
rows.Scan(&u.Email, &u.DiscordID, &u.McUser, &u.LastSeen, &u.LastReported)
|
||
|
}
|
||
|
go u.Write()
|
||
|
log.LogDebug(fmt.Sprintf("DB Returned: %+v", u))
|
||
|
return nil
|
||
|
|
||
|
}
|
||
|
|
||
|
// Get a slice of all users within the database.
|
||
|
func GetAllUsers() []User {
|
||
|
database, _ := sql.Open("sqlite3", config.DbFile)
|
||
|
defer database.Close()
|
||
|
rows, _ := database.Query("SELECT * FROM users")
|
||
|
var users []User
|
||
|
for rows.Next() {
|
||
|
var u User // Don't need anything else for this
|
||
|
rows.Scan(&u.Email, &u.DiscordID, &u.McUser, &u.LastSeen, &u.LastReported)
|
||
|
users = append(users, u) // You wanna get all users, not just the last one right?
|
||
|
}
|
||
|
|
||
|
return users
|
||
|
}
|
||
|
|
||
|
// Get Reports per user via SITNET
|
||
|
func (u User) GetReports() []Report {
|
||
|
database, _ := sql.Open("sqlite3", config.DbFile)
|
||
|
defer database.Close()
|
||
|
rows, _ := database.Query("SELECT * FROM reports WHERE email = ?", u.Email)
|
||
|
var reports []Report
|
||
|
for rows.Next() {
|
||
|
var r Report
|
||
|
rows.Scan(&r.Abuser, &r.Reporter, &r.Msg, &r.Time)
|
||
|
reports = append(reports, r)
|
||
|
}
|
||
|
return reports
|
||
|
}
|
||
|
|
||
|
// Write a report to the database
|
||
|
func (r Report) Write() error {
|
||
|
//(email TEXT PRIMARY KEY, reportedBy TEXT, reportMsg TEXT, reportTime TEXT)
|
||
|
log.LogDebug(fmt.Sprintf("Attempting Adding report of %+v", r.Abuser))
|
||
|
database, _ := sql.Open("sqlite3", config.DbFile)
|
||
|
defer database.Close()
|
||
|
statement, err := database.Prepare("INSERT INTO reports (email, reportedBy, reportMsg, reportTime) VALUES (?, ?, ?, ?)")
|
||
|
if err != nil {
|
||
|
log.LogDebug(fmt.Sprintf("Adding report of %+v failed with error %+v", r.Abuser, err))
|
||
|
return err
|
||
|
}
|
||
|
_, err = statement.Exec(r.Abuser.Email, r.Reporter.Email, r.Msg, r.Time)
|
||
|
if err != nil {
|
||
|
log.LogDebug(fmt.Sprintf("Execution failed with error %+v", err))
|
||
|
return err
|
||
|
}
|
||
|
return nil
|
||
|
}
|