39 lines
1.4 KiB
PL/PgSQL
39 lines
1.4 KiB
PL/PgSQL
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS subscription_account (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name VARCHAR(100) NOT NULL UNIQUE,
|
|
upstream_key VARCHAR(255) NOT NULL UNIQUE,
|
|
filter_high_multiplier INTEGER NOT NULL DEFAULT 1,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
last_success_at DATETIME,
|
|
last_error TEXT,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
INSERT INTO subscription_account (name, upstream_key, filter_high_multiplier, enabled)
|
|
SELECT '旧共享订阅', :legacy_key, 1, 1
|
|
WHERE NOT EXISTS (SELECT 1 FROM subscription_account WHERE name = '旧共享订阅')
|
|
AND NOT EXISTS (SELECT 1 FROM subscription_account WHERE upstream_key = :legacy_key);
|
|
|
|
CREATE TABLE sub_bind_new (
|
|
key VARCHAR(255) NOT NULL PRIMARY KEY,
|
|
user VARCHAR(255) NOT NULL UNIQUE,
|
|
subscription_account_id INTEGER NOT NULL,
|
|
FOREIGN KEY (subscription_account_id) REFERENCES subscription_account(id)
|
|
);
|
|
|
|
INSERT INTO sub_bind_new (key, user, subscription_account_id)
|
|
SELECT sb.key, sb.user, sa.id
|
|
FROM sub_bind sb
|
|
JOIN (SELECT id FROM subscription_account
|
|
WHERE name = '旧共享订阅' OR upstream_key = :legacy_key
|
|
ORDER BY id LIMIT 1) sa;
|
|
|
|
DROP TABLE sub_bind;
|
|
ALTER TABLE sub_bind_new RENAME TO sub_bind;
|
|
CREATE INDEX idx_sub_bind_account ON sub_bind(subscription_account_id);
|
|
|
|
COMMIT;
|