mirror of
https://github.com/gl-inet/glkvm-cloud.git
synced 2026-09-12 05:48:57 +00:00
GL.iNet-Yongping.Xie
333 lines
9.7 KiB
Go
Executable File
333 lines
9.7 KiB
Go
Executable File
package sqlite
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"fmt"
|
|
"os"
|
|
"strings"
|
|
|
|
gormsqlite "github.com/glebarez/sqlite"
|
|
"gorm.io/gorm"
|
|
"gorm.io/gorm/logger"
|
|
)
|
|
|
|
type AppDB struct {
|
|
gorm *gorm.DB
|
|
sql *sql.DB
|
|
dsn string
|
|
}
|
|
|
|
func (a *AppDB) Gorm() *gorm.DB { return a.gorm }
|
|
func (a *AppDB) SQL() *sql.DB { return a.sql }
|
|
func (a *AppDB) Close() error {
|
|
if a.sql == nil {
|
|
return nil
|
|
}
|
|
return a.sql.Close()
|
|
}
|
|
|
|
type Options struct {
|
|
DSN string // e.g. "/home/database/glkvm-cloud.db"
|
|
MaxOpenConns int
|
|
MaxIdleConns int
|
|
LogSQL bool
|
|
}
|
|
|
|
// withPragmas appends connection pragmas to the DSN so every pooled connection
|
|
// opens in WAL mode (readers never block on the single writer), waits instead
|
|
// of erroring on contention (busy_timeout), and uses the faster-but-safe
|
|
// synchronous=NORMAL. Without this, a device reconnect storm serializes all DB
|
|
// access on one connection and blocks user-facing API reads for seconds.
|
|
func withPragmas(dsn string) string {
|
|
const pragmas = "_pragma=busy_timeout(5000)" +
|
|
"&_pragma=journal_mode(WAL)" +
|
|
"&_pragma=synchronous(NORMAL)" +
|
|
"&_pragma=foreign_keys(ON)"
|
|
if strings.HasPrefix(dsn, "file:") {
|
|
if strings.Contains(dsn, "?") {
|
|
return dsn + "&" + pragmas
|
|
}
|
|
return dsn + "?" + pragmas
|
|
}
|
|
return "file:" + dsn + "?" + pragmas
|
|
}
|
|
|
|
// Open opens sqlite via GORM (glebarez/sqlite) and exposes both *gorm.DB and *sql.DB.
|
|
func Open(ctx context.Context, opt Options) (*AppDB, error) {
|
|
if opt.DSN == "" {
|
|
return nil, fmt.Errorf("sqlite: DSN is empty")
|
|
}
|
|
if opt.MaxOpenConns == 0 {
|
|
opt.MaxOpenConns = 1
|
|
}
|
|
if opt.MaxIdleConns == 0 {
|
|
opt.MaxIdleConns = 1
|
|
}
|
|
|
|
gormCfg := &gorm.Config{}
|
|
if opt.LogSQL {
|
|
gormCfg.Logger = logger.Default.LogMode(logger.Info)
|
|
}
|
|
|
|
gdb, err := gorm.Open(gormsqlite.Open(withPragmas(opt.DSN)), gormCfg)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
raw, err := gdb.DB()
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
raw.SetMaxOpenConns(opt.MaxOpenConns)
|
|
raw.SetMaxIdleConns(opt.MaxIdleConns)
|
|
|
|
return &AppDB{
|
|
gorm: gdb,
|
|
sql: raw,
|
|
dsn: opt.DSN,
|
|
}, nil
|
|
}
|
|
|
|
func InitSchema(ctx context.Context, db *sql.DB, schemaPath string) error {
|
|
b, err := os.ReadFile(schemaPath)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if _, err = db.ExecContext(ctx, string(b)); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureDeviceClientColumn(ctx, db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureUserIsSystemColumn(ctx, db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureAuthProviderColumn(ctx, db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureExternalSubColumn(ctx, db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureExternalIdentityIndex(ctx, db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureUserLastLoginAtColumn(ctx, db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureUserTotpSecretColumn(ctx, db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureUserTotpEnabledColumn(ctx, db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureTrustedDevicesTable(ctx, db); err != nil {
|
|
return err
|
|
}
|
|
if err := ensureDeviceEventLogsTable(ctx, db); err != nil {
|
|
return err
|
|
}
|
|
return ensureNotificationTables(ctx, db)
|
|
}
|
|
|
|
func ensureNotificationTables(ctx context.Context, db *sql.DB) error {
|
|
if db == nil {
|
|
return nil
|
|
}
|
|
if _, err := db.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS notification_smtp_config (
|
|
id INTEGER PRIMARY KEY CHECK (id = 1),
|
|
host TEXT NOT NULL DEFAULT '',
|
|
port INTEGER NOT NULL DEFAULT 587,
|
|
username TEXT NOT NULL DEFAULT '',
|
|
password TEXT NOT NULL DEFAULT '',
|
|
from_email TEXT NOT NULL DEFAULT '',
|
|
encryption TEXT NOT NULL DEFAULT 'starttls',
|
|
enabled INTEGER NOT NULL DEFAULT 0,
|
|
updated_at INTEGER NOT NULL DEFAULT 0
|
|
)`); err != nil {
|
|
return err
|
|
}
|
|
if _, err := db.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS notification_rules (
|
|
id INTEGER PRIMARY KEY CHECK (id = 1),
|
|
device_online INTEGER NOT NULL DEFAULT 0,
|
|
device_offline INTEGER NOT NULL DEFAULT 0,
|
|
remote_access INTEGER NOT NULL DEFAULT 0,
|
|
updated_at INTEGER NOT NULL DEFAULT 0
|
|
)`); err != nil {
|
|
return err
|
|
}
|
|
if _, err := db.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS notification_recipients (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
email TEXT NOT NULL UNIQUE,
|
|
created_at INTEGER NOT NULL DEFAULT (unixepoch())
|
|
)`); err != nil {
|
|
return err
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func ensureDeviceClientColumn(ctx context.Context, db *sql.DB) error {
|
|
if db == nil {
|
|
return nil
|
|
}
|
|
_, err := db.ExecContext(ctx, `ALTER TABLE devices ADD COLUMN client TEXT NOT NULL DEFAULT ''`)
|
|
if err == nil {
|
|
return nil
|
|
}
|
|
if strings.Contains(err.Error(), "duplicate column name") {
|
|
return nil
|
|
}
|
|
return err
|
|
}
|
|
|
|
func ensureUserIsSystemColumn(ctx context.Context, db *sql.DB) error {
|
|
if db == nil {
|
|
return nil
|
|
}
|
|
_, err := db.ExecContext(ctx, `ALTER TABLE users ADD COLUMN is_system INTEGER NOT NULL DEFAULT 0`)
|
|
if err == nil {
|
|
return nil
|
|
}
|
|
if strings.Contains(err.Error(), "duplicate column name") {
|
|
return nil
|
|
}
|
|
return err
|
|
}
|
|
|
|
func ensureAuthProviderColumn(ctx context.Context, db *sql.DB) error {
|
|
if db == nil {
|
|
return nil
|
|
}
|
|
_, err := db.ExecContext(ctx, `ALTER TABLE users ADD COLUMN auth_provider TEXT NOT NULL DEFAULT 'local'`)
|
|
if err == nil {
|
|
return nil
|
|
}
|
|
if strings.Contains(err.Error(), "duplicate column name") {
|
|
return nil
|
|
}
|
|
return err
|
|
}
|
|
|
|
func ensureExternalSubColumn(ctx context.Context, db *sql.DB) error {
|
|
if db == nil {
|
|
return nil
|
|
}
|
|
_, err := db.ExecContext(ctx, `ALTER TABLE users ADD COLUMN external_sub TEXT NOT NULL DEFAULT ''`)
|
|
if err == nil {
|
|
return nil
|
|
}
|
|
if strings.Contains(err.Error(), "duplicate column name") {
|
|
return nil
|
|
}
|
|
return err
|
|
}
|
|
|
|
func ensureExternalIdentityIndex(ctx context.Context, db *sql.DB) error {
|
|
if db == nil {
|
|
return nil
|
|
}
|
|
_, err := db.ExecContext(ctx,
|
|
`CREATE UNIQUE INDEX IF NOT EXISTS idx_users_external_identity
|
|
ON users(auth_provider, external_sub)
|
|
WHERE external_sub != ''`)
|
|
return err
|
|
}
|
|
|
|
func ensureUserLastLoginAtColumn(ctx context.Context, db *sql.DB) error {
|
|
if db == nil {
|
|
return nil
|
|
}
|
|
_, err := db.ExecContext(ctx, `ALTER TABLE users ADD COLUMN last_login_at INTEGER`)
|
|
if err == nil {
|
|
return nil
|
|
}
|
|
if strings.Contains(err.Error(), "duplicate column name") {
|
|
return nil
|
|
}
|
|
return err
|
|
}
|
|
|
|
func ensureUserTotpSecretColumn(ctx context.Context, db *sql.DB) error {
|
|
if db == nil {
|
|
return nil
|
|
}
|
|
_, err := db.ExecContext(ctx, `ALTER TABLE users ADD COLUMN totp_secret TEXT NOT NULL DEFAULT ''`)
|
|
if err == nil {
|
|
return nil
|
|
}
|
|
if strings.Contains(err.Error(), "duplicate column name") {
|
|
return nil
|
|
}
|
|
return err
|
|
}
|
|
|
|
func ensureUserTotpEnabledColumn(ctx context.Context, db *sql.DB) error {
|
|
if db == nil {
|
|
return nil
|
|
}
|
|
_, err := db.ExecContext(ctx, `ALTER TABLE users ADD COLUMN totp_enabled INTEGER NOT NULL DEFAULT 0`)
|
|
if err == nil {
|
|
return nil
|
|
}
|
|
if strings.Contains(err.Error(), "duplicate column name") {
|
|
return nil
|
|
}
|
|
return err
|
|
}
|
|
|
|
func ensureTrustedDevicesTable(ctx context.Context, db *sql.DB) error {
|
|
if db == nil {
|
|
return nil
|
|
}
|
|
if _, err := db.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS user_trusted_devices (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
token TEXT NOT NULL UNIQUE,
|
|
device_name TEXT NOT NULL DEFAULT '',
|
|
ip TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
|
|
last_used_at INTEGER NOT NULL DEFAULT (unixepoch()),
|
|
expires_at INTEGER NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
)`); err != nil {
|
|
return err
|
|
}
|
|
if _, err := db.ExecContext(ctx, `CREATE INDEX IF NOT EXISTS idx_trusted_devices_user_id ON user_trusted_devices(user_id)`); err != nil {
|
|
return err
|
|
}
|
|
if _, err := db.ExecContext(ctx, `CREATE INDEX IF NOT EXISTS idx_trusted_devices_token ON user_trusted_devices(token)`); err != nil {
|
|
return err
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func ensureDeviceEventLogsTable(ctx context.Context, db *sql.DB) error {
|
|
if db == nil {
|
|
return nil
|
|
}
|
|
if _, err := db.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS device_event_logs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
device_id TEXT NOT NULL DEFAULT '',
|
|
device_mac TEXT NOT NULL DEFAULT '',
|
|
event_type TEXT NOT NULL,
|
|
actor_user_id INTEGER NOT NULL DEFAULT 0,
|
|
actor_name TEXT NOT NULL DEFAULT '',
|
|
client_ip TEXT NOT NULL DEFAULT '',
|
|
detail TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
|
|
ended_at INTEGER NOT NULL DEFAULT 0
|
|
)`); err != nil {
|
|
return err
|
|
}
|
|
if _, err := db.ExecContext(ctx, `CREATE INDEX IF NOT EXISTS idx_device_event_logs_mac ON device_event_logs(device_mac)`); err != nil {
|
|
return err
|
|
}
|
|
if _, err := db.ExecContext(ctx, `CREATE INDEX IF NOT EXISTS idx_device_event_logs_type ON device_event_logs(event_type)`); err != nil {
|
|
return err
|
|
}
|
|
if _, err := db.ExecContext(ctx, `CREATE INDEX IF NOT EXISTS idx_device_event_logs_created_at ON device_event_logs(created_at)`); err != nil {
|
|
return err
|
|
}
|
|
return nil
|
|
}
|