108 lines
2.8 KiB
Go
108 lines
2.8 KiB
Go
// Package db provides database access for the IMC application.
|
|
// It uses sqlc for type-safe SQL queries.
|
|
package db
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
"time"
|
|
|
|
"git.workaround.org/chaas/imc/backend/internal/config"
|
|
imcdb "git.workaround.org/chaas/imc/backend/internal/db/sqlc"
|
|
_ "github.com/go-sql-driver/mysql"
|
|
)
|
|
|
|
type DB struct {
|
|
*imcdb.Queries
|
|
db *sql.DB
|
|
}
|
|
|
|
type DomainStats struct {
|
|
ID uint32 `json:"id"`
|
|
Name string `json:"name"`
|
|
UserCount int64 `json:"userCount"`
|
|
AliasCount int64 `json:"aliasCount"`
|
|
}
|
|
|
|
type AliasWithDomain struct {
|
|
imcdb.VirtualAlias
|
|
DomainName string `json:"domainName"`
|
|
}
|
|
|
|
func Connect(cfg *config.Config) (*DB, error) {
|
|
dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true&charset=utf8mb4",
|
|
cfg.DBUser, cfg.DBPassword, cfg.DBHost, cfg.DBPort, cfg.DBName)
|
|
|
|
mysqlDB, err := sql.Open("mysql", dsn)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to open database: %w", err)
|
|
}
|
|
|
|
mysqlDB.SetMaxOpenConns(25)
|
|
mysqlDB.SetMaxIdleConns(5)
|
|
mysqlDB.SetConnMaxLifetime(5 * time.Minute)
|
|
|
|
if err := mysqlDB.Ping(); err != nil {
|
|
return nil, fmt.Errorf("failed to ping database: %w", err)
|
|
}
|
|
|
|
return &DB{
|
|
Queries: imcdb.New(mysqlDB),
|
|
db: mysqlDB,
|
|
}, nil
|
|
}
|
|
|
|
func (d *DB) InitSchema() error {
|
|
imcUsersSQL := `
|
|
CREATE TABLE IF NOT EXISTS imc_users (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(100) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role ENUM('admin','user') DEFAULT 'user',
|
|
domain_id INT UNSIGNED NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_username (username),
|
|
INDEX idx_role (role),
|
|
INDEX idx_domain_id (domain_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
`
|
|
if _, err := d.db.Exec(imcUsersSQL); err != nil {
|
|
return err
|
|
}
|
|
|
|
loginAttemptsSQL := `
|
|
CREATE TABLE IF NOT EXISTS imc_login_attempts (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(100) NOT NULL,
|
|
ip_address VARCHAR(45) NOT NULL,
|
|
attempted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
successful BOOLEAN DEFAULT FALSE,
|
|
INDEX idx_username_time (username, attempted_at),
|
|
INDEX idx_ip_time (ip_address, attempted_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
`
|
|
if _, err := d.db.Exec(loginAttemptsSQL); err != nil {
|
|
return err
|
|
}
|
|
|
|
users2DomainsSQL := `
|
|
CREATE TABLE IF NOT EXISTS imc_users2domains (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT UNSIGNED NOT NULL,
|
|
domain_id INT UNSIGNED NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY unique_user_domain (user_id, domain_id),
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_domain_id (domain_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
`
|
|
if _, err := d.db.Exec(users2DomainsSQL); err != nil {
|
|
return err
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func (d *DB) Close() error {
|
|
return d.db.Close()
|
|
}
|