93 lines
2.8 KiB
PL/PgSQL
93 lines
2.8 KiB
PL/PgSQL
INSERT INTO "AspNetRoles"
|
|
VALUES ('5ba004fa-2e7a-42a7-b310-c72e719b3c19', 'ServerAdmin', 'SERVERADMIN', '');
|
|
|
|
CREATE TABLE plugins
|
|
(
|
|
slug TEXT NOT NULL PRIMARY KEY
|
|
);
|
|
|
|
CREATE TABLE users_plugins
|
|
(
|
|
user_id TEXT NOT NULL,
|
|
plugin_slug TEXT NOT NULL,
|
|
PRIMARY KEY (user_id, plugin_slug),
|
|
FOREIGN KEY (plugin_slug) REFERENCES plugins (slug) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
|
|
);
|
|
|
|
|
|
CREATE TABLE builds_ids
|
|
(
|
|
plugin_slug TEXT NOT NULL,
|
|
curr_id BIGINT NOT NULL,
|
|
PRIMARY KEY (plugin_slug),
|
|
FOREIGN KEY (plugin_slug) REFERENCES plugins (slug) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE builds
|
|
(
|
|
plugin_slug TEXT NOT NULL,
|
|
id BIGINT NOT NULL,
|
|
state TEXT NOT NULL,
|
|
manifest_info JSONB,
|
|
build_info JSONB,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
/*PRIMARY KEY (plugin_slug, id),*/
|
|
FOREIGN KEY (plugin_slug) REFERENCES plugins (slug) ON DELETE CASCADE
|
|
);
|
|
CREATE UNIQUE INDEX builds_pkey ON builds (plugin_slug, id DESC);
|
|
|
|
CREATE TABLE versions
|
|
(
|
|
plugin_slug TEXT NOT NULL,
|
|
ver INT[] NOT NULL,
|
|
build_id BIGINT NOT NULL,
|
|
btcpay_min_ver INT[] NOT NULL,
|
|
pre_release BOOLEAN NOT NULL,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (plugin_slug, ver),
|
|
FOREIGN KEY (plugin_slug) REFERENCES plugins (slug) ON DELETE CASCADE,
|
|
FOREIGN KEY (plugin_slug, build_id) REFERENCES builds (plugin_slug, id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX btcpay_min_ver_idx ON versions (btcpay_min_ver);
|
|
|
|
CREATE
|
|
OR REPLACE FUNCTION versions_updating() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
NEW.updated_at
|
|
= CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END
|
|
$$;
|
|
CREATE TRIGGER versions_update_trigger
|
|
AFTER UPDATE
|
|
ON versions
|
|
FOR EACH ROW EXECUTE PROCEDURE versions_updating();
|
|
|
|
CREATE TABLE builds_logs
|
|
(
|
|
plugin_slug TEXT NOT NULL,
|
|
build_id BIGINT NOT NULL,
|
|
logs TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (plugin_slug) REFERENCES plugins (slug) ON DELETE CASCADE,
|
|
FOREIGN KEY (plugin_slug, build_id) REFERENCES builds (plugin_slug, id) ON DELETE CASCADE
|
|
);
|
|
CREATE INDEX builds_logs_idx ON builds_logs (plugin_slug, build_id, created_at);
|
|
|
|
|
|
CREATE
|
|
OR REPLACE FUNCTION get_latest_versions (btcpayVersion INT[], includePreRelease BOOLEAN)
|
|
RETURNS TABLE(plugin_slug TEXT, ver INT[], build_id BIGINT)
|
|
AS $$
|
|
WITH latest_versions AS
|
|
(
|
|
SELECT plugin_slug, MAX(ver) ver FROM versions
|
|
WHERE btcpay_min_ver <= btcpayVersion AND (includePreRelease OR pre_release IS FALSE)
|
|
GROUP BY plugin_slug
|
|
)
|
|
SELECT v.plugin_slug, v.ver, v.build_id
|
|
FROM latest_versions lv
|
|
JOIN versions v USING (plugin_slug, ver) $$ LANGUAGE SQL STABLE;
|