mirror of https://github.com/allinssl/allinssl
410 lines
14 KiB
Go
410 lines
14 KiB
Go
package migrations
|
|
|
|
import (
|
|
"ALLinSSL/backend/public"
|
|
"ALLinSSL/backend/public/sqlite_migrate"
|
|
"database/sql"
|
|
"fmt"
|
|
_ "modernc.org/sqlite"
|
|
"os"
|
|
"path/filepath"
|
|
)
|
|
|
|
func init() {
|
|
// 指定运行目录为当前目录
|
|
exePath, err := os.Executable()
|
|
if err != nil {
|
|
fmt.Fprintf(os.Stderr, "获取可执行文件路径失败: %v\n", err)
|
|
os.Exit(1)
|
|
}
|
|
exePath, err = filepath.EvalSymlinks(exePath) // 解决 macOS/Linux 下软链接问题
|
|
if err != nil {
|
|
fmt.Fprintf(os.Stderr, "解析软链接失败: %v\n", err)
|
|
os.Exit(1)
|
|
}
|
|
exeDir := filepath.Dir(exePath)
|
|
err = os.Chdir(exeDir)
|
|
if err != nil {
|
|
fmt.Fprintf(os.Stderr, "切换目录失败: %v\n", err)
|
|
os.Exit(1)
|
|
}
|
|
|
|
os.MkdirAll("data", os.ModePerm)
|
|
|
|
dbPath := "data/data.db"
|
|
_, _ = filepath.Abs(dbPath)
|
|
// fmt.Println("数据库路径:", absPath)
|
|
db, err := sql.Open("sqlite", dbPath)
|
|
if err != nil {
|
|
// fmt.Println("创建数据库失败:", err)
|
|
return
|
|
}
|
|
defer db.Close()
|
|
// 创建表
|
|
_, err = db.Exec(`
|
|
PRAGMA journal_mode=WAL;
|
|
|
|
create table IF NOT EXISTS access
|
|
(
|
|
id integer not null
|
|
constraint access_pk
|
|
primary key autoincrement,
|
|
config TEXT not null,
|
|
type TEXT not null,
|
|
create_time TEXT,
|
|
update_time TEXT,
|
|
name TEXT not null
|
|
);
|
|
|
|
create table IF NOT EXISTS access_type
|
|
(
|
|
id integer not null
|
|
constraint access_type_pk
|
|
primary key autoincrement,
|
|
name TEXT,
|
|
type TEXT
|
|
);
|
|
|
|
create table IF NOT EXISTS cert
|
|
(
|
|
id integer not null
|
|
constraint cert_pk
|
|
primary key autoincrement,
|
|
source TEXT not null,
|
|
sha256 TEXT,
|
|
history_id TEXT,
|
|
key TEXT not null,
|
|
cert TEXT not null,
|
|
issuer_cert integer,
|
|
domains TEXT not null,
|
|
create_time TEXT,
|
|
update_time TEXT,
|
|
issuer TEXT not null,
|
|
start_time TEXT,
|
|
end_time TEXT,
|
|
end_day TEXT,
|
|
workflow_id TEXT
|
|
);
|
|
|
|
create table IF NOT EXISTS report
|
|
(
|
|
id integer not null
|
|
constraint report_pk
|
|
primary key autoincrement,
|
|
type TEXT not null,
|
|
config TEXT not null,
|
|
create_time TEXT,
|
|
update_time TEXT,
|
|
name TEXT
|
|
);
|
|
|
|
create table IF NOT EXISTS workflow
|
|
(
|
|
id integer not null
|
|
constraint workflow_pk
|
|
primary key autoincrement,
|
|
name TEXT not null,
|
|
content TEXT not null,
|
|
cron TEXT,
|
|
create_time TEXT,
|
|
update_time TEXT,
|
|
active integer,
|
|
exec_type TEXT,
|
|
last_run_status TEXT,
|
|
exec_time TEXT,
|
|
last_run_time TEXT
|
|
);
|
|
|
|
create table IF NOT EXISTS workflow_history
|
|
(
|
|
id TEXT not null
|
|
constraint work_flow_pk
|
|
primary key,
|
|
status TEXT,
|
|
exec_type TEXT,
|
|
create_time TEXT,
|
|
end_time TEXT,
|
|
workflow_id TEXT not null
|
|
);
|
|
|
|
create table IF NOT EXISTS workflow_deploy
|
|
(
|
|
id TEXT,
|
|
workflow_id TEXT,
|
|
cert_hash TEXT,
|
|
status TEXT,
|
|
constraint workflow_deploy_pk
|
|
primary key (id, workflow_id)
|
|
);
|
|
|
|
`)
|
|
insertDefaultData(db, "access_type", `
|
|
INSERT INTO access_type (name, type) VALUES ('aliyun', 'dns');
|
|
INSERT INTO access_type (name, type) VALUES ('tencentcloud', 'dns');
|
|
INSERT INTO access_type (name, type) VALUES ('aliyun', 'host');
|
|
INSERT INTO access_type (name, type) VALUES ('tencentcloud', 'host');
|
|
INSERT INTO access_type (name, type) VALUES ('ssh', 'host');
|
|
INSERT INTO access_type (name, type) VALUES ('btpanel', 'host');
|
|
INSERT INTO access_type (name, type) VALUES ('1panel', 'host');`)
|
|
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "cloudflare", "type": "host"}, []string{"name", "type"}, []any{"cloudflare", "host"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "cloudflare", "type": "dns"}, []string{"name", "type"}, []any{"cloudflare", "dns"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "huaweicloud", "type": "host"}, []string{"name", "type"}, []any{"huaweicloud", "host"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "huaweicloud", "type": "dns"}, []string{"name", "type"}, []any{"huaweicloud", "dns"})
|
|
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "baidu", "type": "host"}, []string{"name", "type"}, []any{"baidu", "host"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "baidu", "type": "dns"}, []string{"name", "type"}, []any{"baidu", "dns"})
|
|
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "btwaf", "type": "host"}, []string{"name", "type"}, []any{"btwaf", "host"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "doge", "type": "host"}, []string{"name", "type"}, []any{"doge", "host"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "plugin", "type": "host"}, []string{"name", "type"}, []any{"plugin", "host"})
|
|
|
|
// 雷池
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "safeline", "type": "host"}, []string{"name", "type"}, []any{"safeline", "host"})
|
|
// 七牛
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "qiniu", "type": "host"}, []string{"name", "type"}, []any{"qiniu", "host"})
|
|
// 西部数码
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "westcn", "type": "dns"}, []string{"name", "type"}, []any{"westcn", "dns"})
|
|
// 火山引擎
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "volcengine", "type": "dns"}, []string{"name", "type"}, []any{"volcengine", "dns"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "volcengine", "type": "host"}, []string{"name", "type"}, []any{"volcengine", "host"})
|
|
// godaddy
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "godaddy", "type": "dns"}, []string{"name", "type"}, []any{"godaddy", "dns"})
|
|
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "namecheap", "type": "dns"}, []string{"name", "type"}, []any{"namecheap", "dns"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "ns1", "type": "dns"}, []string{"name", "type"}, []any{"ns1", "dns"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "cloudns", "type": "dns"}, []string{"name", "type"}, []any{"cloudns", "dns"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "aws", "type": "dns"}, []string{"name", "type"}, []any{"aws", "dns"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "azure", "type": "dns"}, []string{"name", "type"}, []any{"azure", "dns"})
|
|
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "jdcloud", "type": "dns"}, []string{"name", "type"}, []any{"jdcloud", "dns"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "gcore", "type": "dns"}, []string{"name", "type"}, []any{"gcore", "dns"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "bunny", "type": "dns"}, []string{"name", "type"}, []any{"bunny", "dns"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "namedotcom", "type": "dns"}, []string{"name", "type"}, []any{"namedotcom", "dns"})
|
|
InsertIfNotExists(db, "access_type", map[string]any{"name": "namesilo", "type": "dns"}, []string{"name", "type"}, []any{"namesilo", "dns"})
|
|
|
|
err = sqlite_migrate.EnsureDatabaseWithTables(
|
|
"data/site_monitor.db",
|
|
"data/data.db",
|
|
[]string{"site_monitor"}, // 你要迁移的表
|
|
)
|
|
if err != nil {
|
|
fmt.Println("错误:", err)
|
|
}
|
|
|
|
db1, err := sql.Open("sqlite", "data/site_monitor.db")
|
|
if err != nil {
|
|
// fmt.Println("创建数据库失败:", err)
|
|
return
|
|
}
|
|
defer db1.Close()
|
|
// 创建表
|
|
_, err = db1.Exec(`
|
|
PRAGMA journal_mode=WAL;
|
|
|
|
create table IF NOT EXISTS site_monitor
|
|
(
|
|
id integer not null
|
|
constraint site_monitor_pk
|
|
primary key autoincrement,
|
|
name TEXT not null,
|
|
site_domain TEXT not null,
|
|
cycle integer not null,
|
|
report_type TEXT not null,
|
|
cert_domain TEXT,
|
|
ca TEXT,
|
|
active integer,
|
|
end_time TEXT,
|
|
end_day TEXT,
|
|
last_time TEXT,
|
|
except_end_time TEXT,
|
|
create_time TEXT,
|
|
state TEXT,
|
|
update_time TEXT,
|
|
repeat_send_gap INTEGER
|
|
);
|
|
`)
|
|
|
|
err = sqlite_migrate.EnsureDatabaseWithTables(
|
|
"data/settings.db",
|
|
"data/data.db",
|
|
[]string{"settings", "users"}, // 你要迁移的表
|
|
)
|
|
if err != nil {
|
|
fmt.Println("错误:", err)
|
|
}
|
|
dbSetting, err := sql.Open("sqlite", "data/settings.db")
|
|
if err != nil {
|
|
//fmt.Println("创建 settings 数据库失败:", err)
|
|
return
|
|
}
|
|
defer dbSetting.Close()
|
|
// 创建表
|
|
_, err = dbSetting.Exec(`
|
|
PRAGMA journal_mode=WAL;
|
|
|
|
create table IF NOT EXISTS settings
|
|
(
|
|
id integer
|
|
constraint settings_pk
|
|
primary key,
|
|
key TEXT,
|
|
value TEXT,
|
|
create_time TEXT not null,
|
|
update_time TEXT not null,
|
|
active integer not null,
|
|
type TEXT
|
|
);
|
|
|
|
create table IF NOT EXISTS users
|
|
(
|
|
id integer not null
|
|
constraint users_pk
|
|
primary key autoincrement,
|
|
username TEXT not null
|
|
constraint users_pk2
|
|
unique,
|
|
password TEXT not null,
|
|
salt TEXT default '' not null
|
|
);
|
|
`)
|
|
insertDefaultData(dbSetting, "users", "INSERT INTO users (id, username, password, salt) VALUES (1, 'admin', 'xxxxxxx', '&*ghs^&%dag');")
|
|
uuidStr := public.GenerateUUID()
|
|
randomStr := public.RandomString(8)
|
|
|
|
port, err := public.GetFreePort()
|
|
if err != nil {
|
|
port = 20773
|
|
}
|
|
|
|
Isql := fmt.Sprintf(
|
|
`INSERT INTO settings (key, value, create_time, update_time, active, type) VALUES ('log_path', 'logs/ALLinSSL.log', '2025-04-15 15:58', '2025-04-15 15:58', 1, null);
|
|
INSERT INTO settings (key, value, create_time, update_time, active, type) VALUES ( 'workflow_log_path', 'logs/workflows/', '2025-04-15 15:58', '2025-04-15 15:58', 1, null);
|
|
INSERT INTO settings (key, value, create_time, update_time, active, type) VALUES ( 'timeout', '86400', '2025-04-15 15:58', '2025-04-15 15:58', 1, null);
|
|
INSERT INTO settings (key, value, create_time, update_time, active, type) VALUES ( 'https', '0', '2025-04-15 15:58', '2025-04-15 15:58', 1, null);
|
|
INSERT INTO settings (key, value, create_time, update_time, active, type) VALUES ('session_key', '%s', '2025-04-15 15:58', '2025-04-15 15:58', 1, null);
|
|
INSERT INTO settings (key, value, create_time, update_time, active, type) VALUES ('secure', '/%s', '2025-04-15 15:58', '2025-04-15 15:58', 1, null);
|
|
INSERT INTO settings (key, value, create_time, update_time, active, type) VALUES ('port', '%d', '2025-04-15 15:58', '2025-04-15 15:58', 1, null);`, uuidStr, randomStr, port)
|
|
|
|
insertDefaultData(dbSetting, "settings", Isql)
|
|
InsertIfNotExists(dbSetting, "settings", map[string]any{"key": "plugin_dir"}, []string{"key", "value", "create_time", "update_time", "active"}, []any{"plugin_dir", "plugins", "2025-04-15 15:58", "2025-04-15 15:58", 1})
|
|
|
|
err = sqlite_migrate.EnsureDatabaseWithTables(
|
|
"data/accounts.db",
|
|
"data/data.db",
|
|
[]string{"_accounts", "_eab"}, // 你要迁移的表
|
|
)
|
|
if err != nil {
|
|
fmt.Println("错误:", err)
|
|
}
|
|
dbAcc, err := sql.Open("sqlite", "data/accounts.db")
|
|
if err != nil {
|
|
//fmt.Println("创建 settings 数据库失败:", err)
|
|
return
|
|
}
|
|
defer dbAcc.Close()
|
|
// 创建表
|
|
_, err = dbAcc.Exec(`
|
|
PRAGMA journal_mode=WAL;
|
|
|
|
create table if not exists accounts
|
|
(
|
|
id integer not null
|
|
constraint _accounts_pk
|
|
primary key autoincrement,
|
|
private_key TEXT ,
|
|
reg TEXT ,
|
|
email TEXT not null,
|
|
type TEXT not null,
|
|
Kid TEXT ,
|
|
HmacEncoded TEXT ,
|
|
CADirURL TEXT ,
|
|
create_time TEXT,
|
|
update_time TEXT
|
|
);
|
|
`)
|
|
insertSql := `
|
|
insert into accounts (id, private_key, reg, email, create_time, update_time, type, Kid, HmacEncoded)
|
|
select a.id, a.private_key, a.reg, a.email, a.create_time, a.update_time, case when a.type like 'sslcom%' then 'sslcom' else a.type end, b.Kid,b.HmacEncoded
|
|
from _accounts a
|
|
left join _eab b
|
|
on a.email = b.mail and a.type like b.ca||'%';
|
|
`
|
|
insertDefaultData(dbAcc, "accounts", insertSql)
|
|
}
|
|
|
|
func insertDefaultData(db *sql.DB, table, insertSQL string) {
|
|
// 查询用户表中现有的记录数
|
|
var count int
|
|
err := db.QueryRow("SELECT COUNT(*) FROM " + table).Scan(&count)
|
|
if err != nil {
|
|
// fmt.Println("检查数据行数失败:", err)
|
|
return
|
|
}
|
|
|
|
// 如果表为空，则插入默认数据
|
|
if count == 0 {
|
|
// fmt.Println("表为空，插入默认数据...")
|
|
_, err = db.Exec(insertSQL)
|
|
if err != nil {
|
|
// fmt.Println("插入数据失败:", err)
|
|
return
|
|
}
|
|
// fmt.Println("默认数据插入成功。")
|
|
// } else {
|
|
// fmt.Println("表已有数据，跳过插入。")
|
|
}
|
|
}
|
|
|
|
func InsertIfNotExists(
|
|
db *sql.DB,
|
|
table string,
|
|
whereFields map[string]any, // 用于 WHERE 判断的字段和值
|
|
insertColumns []string,
|
|
insertValues []any,
|
|
) error {
|
|
// 1. 构建 WHERE 子句
|
|
whereClause := ""
|
|
whereArgs := make([]any, 0, len(whereFields))
|
|
i := 0
|
|
for col, val := range whereFields {
|
|
if i > 0 {
|
|
whereClause += " AND "
|
|
}
|
|
whereClause += fmt.Sprintf("%s = ?", col)
|
|
whereArgs = append(whereArgs, val)
|
|
i++
|
|
}
|
|
|
|
// 2. 判断是否存在
|
|
query := fmt.Sprintf("SELECT EXISTS(SELECT 1 FROM %s WHERE %s)", table, whereClause)
|
|
var exists bool
|
|
err := db.QueryRow(query, whereArgs...).Scan(&exists)
|
|
if err != nil {
|
|
return fmt.Errorf("check exists failed: %w", err)
|
|
}
|
|
if exists {
|
|
return nil // 已存在
|
|
}
|
|
|
|
// 3. 构建 INSERT 语句
|
|
columnList := ""
|
|
placeholderList := ""
|
|
for i, col := range insertColumns {
|
|
if i > 0 {
|
|
columnList += ", "
|
|
placeholderList += ", "
|
|
}
|
|
columnList += col
|
|
placeholderList += "?"
|
|
}
|
|
insertSQL := fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s)", table, columnList, placeholderList)
|
|
|
|
_, err = db.Exec(insertSQL, insertValues...)
|
|
if err != nil {
|
|
return fmt.Errorf("insert failed: %w", err)
|
|
}
|
|
|
|
return nil
|
|
}
|