mirror of
https://github.com/v2rayA/v2rayA.git
synced 2026-11-20 23:02:25 +03:00
233 lines
7.3 KiB
Go
233 lines
7.3 KiB
Go
package db
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
jsoniter "github.com/json-iterator/go"
|
|
|
|
"github.com/v2rayA/v2rayA/pkg/util/log"
|
|
)
|
|
|
|
// SQL statements for creating all tables
|
|
const schemaSQL = `
|
|
CREATE TABLE IF NOT EXISTS system_config (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS servers (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
type TEXT NOT NULL DEFAULT 'server',
|
|
sub_id INTEGER DEFAULT NULL,
|
|
address TEXT NOT NULL DEFAULT '',
|
|
port INTEGER NOT NULL DEFAULT 0,
|
|
protocol TEXT NOT NULL DEFAULT '',
|
|
config_json TEXT NOT NULL DEFAULT '{}',
|
|
intel TEXT DEFAULT '',
|
|
latency TEXT DEFAULT '',
|
|
link TEXT DEFAULT '',
|
|
url TEXT DEFAULT '',
|
|
sort INTEGER NOT NULL DEFAULT 0,
|
|
group_id TEXT DEFAULT '',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS subscriptions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
address TEXT NOT NULL DEFAULT '',
|
|
remarks TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT '',
|
|
info TEXT DEFAULT '',
|
|
auto_select INTEGER NOT NULL DEFAULT 0,
|
|
node_dns TEXT NOT NULL DEFAULT 'auto',
|
|
update_mode TEXT NOT NULL DEFAULT 'disabled',
|
|
update_interval_minutes INTEGER NOT NULL DEFAULT 0,
|
|
failure_interval_minutes INTEGER NOT NULL DEFAULT 1,
|
|
allow_direct_recovery INTEGER NOT NULL DEFAULT 0,
|
|
filter TEXT DEFAULT '',
|
|
group_id TEXT DEFAULT '',
|
|
sort INTEGER NOT NULL DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS accounts (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS outbound_names (
|
|
name TEXT PRIMARY KEY,
|
|
sort INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
`
|
|
|
|
// InitSchema creates all tables if they don't exist
|
|
func InitSchema(db *sql.DB) error {
|
|
log.Info("Initializing database schema")
|
|
_, err := db.Exec(schemaSQL)
|
|
if err != nil {
|
|
log.Fatal("Failed to initialize database schema: %v", err)
|
|
return err
|
|
}
|
|
log.Info("Database schema initialized successfully")
|
|
return nil
|
|
}
|
|
|
|
// MigrateSchema applies incremental schema migrations for existing databases.
|
|
// Unlike InitSchema (which uses CREATE TABLE IF NOT EXISTS), this handles
|
|
// ALTER TABLE additions for columns added after the initial schema version.
|
|
func MigrateSchema(db *sql.DB) error {
|
|
// Check if remarks column exists (added after initial schema)
|
|
var count int
|
|
err := db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('subscriptions') WHERE name = 'remarks'").Scan(&count)
|
|
if err != nil {
|
|
return fmt.Errorf("failed to check for remarks column: %w", err)
|
|
}
|
|
if count == 0 {
|
|
log.Info("Adding remarks column to subscriptions table")
|
|
if _, err := db.Exec("ALTER TABLE subscriptions ADD COLUMN remarks TEXT NOT NULL DEFAULT ''"); err != nil {
|
|
return fmt.Errorf("failed to add remarks column: %w", err)
|
|
}
|
|
}
|
|
|
|
columns := []struct{ name, definition string }{
|
|
{"node_dns", "TEXT NOT NULL DEFAULT 'auto'"},
|
|
{"auto_select", "INTEGER NOT NULL DEFAULT 0"},
|
|
{"update_mode", "TEXT NOT NULL DEFAULT 'disabled'"},
|
|
{"update_interval_minutes", "INTEGER NOT NULL DEFAULT 0"},
|
|
{"failure_interval_minutes", "INTEGER NOT NULL DEFAULT 1"},
|
|
{"allow_direct_recovery", "INTEGER NOT NULL DEFAULT 0"},
|
|
}
|
|
for _, column := range columns {
|
|
if err := db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('subscriptions') WHERE name = ?", column.name).Scan(&count); err != nil {
|
|
return err
|
|
}
|
|
if count == 0 {
|
|
if _, err := db.Exec("ALTER TABLE subscriptions ADD COLUMN " + column.name + " " + column.definition); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
}
|
|
if err := migrateLegacyOutboundTables(db); err != nil {
|
|
return err
|
|
}
|
|
tx, err := db.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer tx.Rollback()
|
|
if err := migrateSubscriptionUpdatePolicy(tx); err != nil {
|
|
return err
|
|
}
|
|
return tx.Commit()
|
|
}
|
|
|
|
// migrateLegacyOutboundTables copies what the first SQLite migration wrote
|
|
// into outbound_connections and outbound_settings — tables the runtime
|
|
// never read — into the system_config keys it does read, for outbounds
|
|
// that have no key yet. A database that never had the tables, or whose
|
|
// keys were written since, is left alone.
|
|
func migrateLegacyOutboundTables(db *sql.DB) error {
|
|
var tables int
|
|
if err := db.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name IN ('outbound_connections', 'outbound_settings')").Scan(&tables); err != nil {
|
|
return err
|
|
}
|
|
if tables == 0 {
|
|
return nil
|
|
}
|
|
hasKey := func(key string) (bool, error) {
|
|
var n int
|
|
err := db.QueryRow("SELECT COUNT(*) FROM system_config WHERE key = ?", key).Scan(&n)
|
|
return n > 0, err
|
|
}
|
|
rows, err := db.Query(`SELECT c.outbound_name, s.type, s.sort, COALESCE(sub.sort, -1)
|
|
FROM outbound_connections c
|
|
JOIN servers s ON s.id = c.server_id
|
|
LEFT JOIN subscriptions sub ON sub.id = s.sub_id
|
|
ORDER BY c.outbound_name, c.sort`)
|
|
if err != nil {
|
|
// outbound_settings may exist without outbound_connections
|
|
rows = nil
|
|
}
|
|
type which struct {
|
|
TYPE string `json:"_type"`
|
|
ID int `json:"id"`
|
|
Sub int `json:"sub"`
|
|
Outbound string `json:"outbound"`
|
|
}
|
|
groups := map[string][]which{}
|
|
var order []string
|
|
if rows != nil {
|
|
for rows.Next() {
|
|
var name, typ string
|
|
var sort, subSort int
|
|
if err := rows.Scan(&name, &typ, &sort, &subSort); err != nil {
|
|
rows.Close()
|
|
return err
|
|
}
|
|
w := which{ID: sort + 1, Outbound: name}
|
|
if typ == "subscription_server" {
|
|
w.TYPE, w.Sub = "subscriptionServer", subSort
|
|
} else {
|
|
w.TYPE = "server"
|
|
}
|
|
if _, seen := groups[name]; !seen {
|
|
order = append(order, name)
|
|
}
|
|
groups[name] = append(groups[name], w)
|
|
}
|
|
rows.Close()
|
|
}
|
|
for _, name := range order {
|
|
key := makeKey("outbound."+name, "connectedServers")
|
|
if ok, err := hasKey(key); err != nil || ok {
|
|
if err != nil {
|
|
return err
|
|
}
|
|
continue
|
|
}
|
|
b, err := jsoniter.Marshal(map[string]interface{}{"touches": groups[name]})
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if _, err := db.Exec("INSERT INTO system_config (key, value) VALUES (?, ?)", key, string(b)); err != nil {
|
|
return err
|
|
}
|
|
log.Warn("Recovered the members of outbound %q from the first SQLite migration (%d)", name, len(groups[name]))
|
|
}
|
|
settings, err := db.Query("SELECT outbound_name, setting_json FROM outbound_settings")
|
|
if err != nil {
|
|
return nil
|
|
}
|
|
defer settings.Close()
|
|
for settings.Next() {
|
|
var name, setting string
|
|
if err := settings.Scan(&name, &setting); err != nil {
|
|
return err
|
|
}
|
|
key := makeKey("outbound."+name, "setting")
|
|
if ok, err := hasKey(key); err != nil || ok || setting == "" || setting == "{}" {
|
|
if err != nil {
|
|
return err
|
|
}
|
|
continue
|
|
}
|
|
if _, err := db.Exec("INSERT INTO system_config (key, value) VALUES (?, ?)", key, setting); err != nil {
|
|
return err
|
|
}
|
|
log.Warn("Recovered the setting of outbound %q from the first SQLite migration", name)
|
|
}
|
|
|
|
// Drop obsolete legacy tables so stale foreign keys (e.g. outbound_connections.server_id -> servers.id)
|
|
// do not cause "FOREIGN KEY constraint failed (787)" when deleting servers.
|
|
if _, err := db.Exec("DROP TABLE IF EXISTS outbound_connections; DROP TABLE IF EXISTS outbound_settings;"); err != nil {
|
|
return fmt.Errorf("failed to drop legacy outbound tables: %w", err)
|
|
}
|
|
|
|
return nil
|
|
}
|