327 lines
7.2 KiB
Go
327 lines
7.2 KiB
Go
package db
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
_ "github.com/mattn/go-sqlite3"
|
|
"strconv"
|
|
"time"
|
|
)
|
|
|
|
type User struct {
|
|
Id string
|
|
Username string
|
|
IpAddress string
|
|
Timezone string
|
|
}
|
|
|
|
type Message struct {
|
|
Id string
|
|
SenderIp string
|
|
SenderUsername string
|
|
Content string
|
|
Timestamp string
|
|
Edited bool
|
|
}
|
|
|
|
type Database struct {
|
|
db *sql.DB
|
|
}
|
|
|
|
func OpenDatabase(filepath string) *Database {
|
|
if db, err := sql.Open("sqlite3", filepath); err != nil {
|
|
return nil
|
|
} else {
|
|
return &Database{
|
|
db: db,
|
|
}
|
|
}
|
|
}
|
|
|
|
func (db *Database) Close() {
|
|
db.db.Close()
|
|
}
|
|
|
|
func (db *Database) DbCreateTableMessages() {
|
|
stmt := `CREATE TABLE IF NOT EXISTS messages (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
ip_address TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
edited INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)`
|
|
db.db.Exec(stmt)
|
|
}
|
|
|
|
func (db *Database) DbCreateTableUsers() {
|
|
stmt := `CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
ip_address TEXT NOT NULL,
|
|
username TEXT NOT NULL UNIQUE,
|
|
timezone TEXT DEFAULT 'America/New_York',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)`
|
|
db.db.Exec(stmt)
|
|
}
|
|
|
|
func (db *Database) UserTimezoneSet(ip_address, timezone string) {
|
|
_, err := db.db.Exec("UPDATE users SET timezone = ? WHERE ip_address = ?", timezone, ip_address)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
}
|
|
|
|
func (db *Database) UserAdd(ip_address, username string) {
|
|
_, err := db.db.Exec("INSERT INTO users (username, ip_address) VALUES (?, ?)", username, ip_address)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
}
|
|
|
|
func (db *Database) MessageAdd(ip_address string, content string) {
|
|
_, err := db.db.Exec("INSERT INTO messages (ip_address, content) VALUES (?, ?)", ip_address, content)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
}
|
|
|
|
func (db *Database) UserNameGet(ip_address string) string {
|
|
rows, err := db.db.Query("SELECT username FROM users WHERE ip_address = ?", ip_address)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
defer rows.Close()
|
|
var username string
|
|
rows.Next()
|
|
rows.Scan(&username)
|
|
return username
|
|
}
|
|
|
|
func (db *Database) UserIpGet(username string) string {
|
|
rows, err := db.db.Query("SELECT ip_address FROM users WHERE username = ?", username)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
defer rows.Close()
|
|
var ip_address string
|
|
rows.Next()
|
|
rows.Scan(&ip_address)
|
|
return ip_address
|
|
}
|
|
|
|
func (db *Database) UserGetTimezone(ip_address string) string {
|
|
rows, err := db.db.Query("SELECT timezone FROM users WHERE ip_address = ?", ip_address)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
defer rows.Close()
|
|
var timezone string
|
|
rows.Next()
|
|
rows.Scan(&timezone)
|
|
return timezone
|
|
}
|
|
|
|
func (db *Database) UsersGet() []User {
|
|
rows, err := db.db.Query("SELECT * FROM users")
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
defer rows.Close()
|
|
var users []User
|
|
for rows.Next() {
|
|
var id string
|
|
var ip_address string
|
|
var username string
|
|
var created_at string
|
|
var timezone string
|
|
rows.Scan(&id, &ip_address, &username, &created_at, &timezone)
|
|
user := User{
|
|
Id: id,
|
|
Username: username,
|
|
IpAddress: ip_address,
|
|
Timezone: timezone,
|
|
}
|
|
users = append(users, user)
|
|
}
|
|
return users
|
|
}
|
|
|
|
func (db *Database) MessagesGet() []Message {
|
|
rows, err := db.db.Query(`
|
|
SELECT messages.id, messages.ip_address, messages.content,
|
|
strftime('%Y-%m-%d %H:%M:%S', messages.created_at) as created_at,
|
|
users.username, messages.edited
|
|
FROM messages
|
|
LEFT JOIN users ON messages.ip_address = users.ip_address;
|
|
`)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
var messages []Message
|
|
|
|
for rows.Next() {
|
|
var id string
|
|
var content string
|
|
var ip_address string
|
|
var created_at string
|
|
var username string
|
|
var edited int
|
|
rows.Scan(&id, &ip_address, &content, &created_at, &username, &edited)
|
|
|
|
editedBool := false
|
|
if edited == 1 {
|
|
editedBool = true
|
|
}
|
|
|
|
message := Message{
|
|
Id: id,
|
|
Content: content,
|
|
SenderIp: ip_address,
|
|
SenderUsername: username,
|
|
Edited: editedBool,
|
|
Timestamp: created_at,
|
|
}
|
|
|
|
messages = append(messages, message)
|
|
}
|
|
return messages
|
|
}
|
|
|
|
func (db *Database) UserNameExists(username string) bool {
|
|
rows, err := db.db.Query("SELECT * FROM users WHERE username = ?", username)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
defer rows.Close()
|
|
return rows.Next()
|
|
}
|
|
|
|
func (db *Database) UserExists(ip string) bool {
|
|
rows, err := db.db.Query("SELECT * FROM users WHERE ip_address = ?", ip)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
defer rows.Close()
|
|
return rows.Next()
|
|
}
|
|
|
|
func (db *Database) UserNameChange(ip, newUsername string) {
|
|
_, err := db.db.Exec("UPDATE users SET username = ? WHERE ip_address = ?", newUsername, ip)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
}
|
|
|
|
func (db *Database) UserMessagesDelete(ip string) {
|
|
_, err := db.db.Exec("DELETE FROM messages WHERE ip_address = ?", ip)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
}
|
|
|
|
func (db *Database) UserMessagesGet(ip string) []Message {
|
|
rows, err := db.db.Query(`
|
|
SELECT messages.*, users.username
|
|
FROM messages
|
|
LEFT JOIN users ON messages.ip_address = users.ip_address
|
|
WHERE messages.ip_address = ?
|
|
ORDER BY messages.created_at DESC;
|
|
`, ip)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
var messages []Message
|
|
|
|
for rows.Next() {
|
|
var id string
|
|
var content string
|
|
var ip_address string
|
|
var created_at string
|
|
var username string
|
|
var edited int
|
|
rows.Scan(&id, &ip_address, &content, &created_at, &username, &edited)
|
|
t, _ := time.Parse(created_at, created_at)
|
|
editedBool := false
|
|
if edited == 1 {
|
|
editedBool = true
|
|
}
|
|
message := Message{
|
|
Id: id,
|
|
Content: content,
|
|
SenderIp: ip_address,
|
|
SenderUsername: username,
|
|
Edited: editedBool,
|
|
Timestamp: t.Format(created_at),
|
|
}
|
|
messages = append(messages, message)
|
|
}
|
|
return messages
|
|
}
|
|
|
|
func (db *Database) MessageDeleteId(id string) {
|
|
_, err := db.db.Exec("DELETE FROM messages WHERE id = ?", id)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
}
|
|
func (db *Database) MessageDeleteIfOwner(id string, ip string) (int, error) {
|
|
res, err := db.db.Exec("DELETE FROM messages WHERE id = ? AND ip_address = ?", id, ip)
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
affected, err := res.RowsAffected()
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
return int(affected), nil
|
|
|
|
}
|
|
|
|
func (db *Database) MessageEditIfOwner(id string, content string, ip string) (int, error) {
|
|
res, err := db.db.Exec("UPDATE messages SET content = ?, edited = 1 WHERE id = ? AND ip_address = ?", content, id, ip)
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
affected, err := res.RowsAffected()
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
return int(affected), nil
|
|
}
|
|
|
|
func (db *Database) DeleteOldMessages(ageMinutes int) {
|
|
if ageMinutes <= 0 {
|
|
return
|
|
}
|
|
age := strconv.Itoa(ageMinutes)
|
|
_, err := db.db.Exec("DELETE FROM messages WHERE created_at < datetime('now', ? || ' minutes')", "-"+age)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
}
|
|
|
|
func (db *Database) UserDeleteIp(ip string) {
|
|
_, err := db.db.Exec("DELETE FROM users WHERE ip_address = ?", ip)
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
}
|
|
|
|
func (db *Database) UsersDelete() {
|
|
_, err := db.db.Exec("DELETE FROM users")
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
}
|
|
|
|
func (db *Database) MessagesDelete() {
|
|
_, err := db.db.Exec("DELETE FROM messages")
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
}
|