radchat/db/db.go
2025-01-22 20:04:47 -06:00

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)
}
}