imc-vibe/backend/internal/db/db.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()
}