Last active
April 24, 2020 13:05
-
-
Save dlangille/9414787620447342daa4d78753f658b4 to your computer and use it in GitHub Desktop.
FreshPorts - packages
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION UpdatePackagesFromRawPackages( IN a_abi_name text, IN a_package_set package_sets) | |
RETURNS TABLE(inserts integer, updates integer, deletes integer) | |
LANGUAGE plpgsql VOLATILE | |
AS $$ | |
DECLARE | |
l_abi_id integer; | |
l_inserts integer; | |
l_deletes integer; | |
l_updates integer; | |
BEGIN | |
SELECT id | |
INTO l_abi_id | |
FROM abi | |
WHERE name = a_abi_name; | |
IF NOT FOUND THEN | |
RAISE EXCEPTION 'cannot find id for ABI %', a_abi_name; | |
END IF; | |
-- set abi_ic and port_id before updating the pacakges table. | |
UPDATE packages_raw PR | |
SET abi_id = abi.id, | |
port_id = PO.port_id | |
FROM abi, ports_origin PO | |
WHERE PR.abi = a_abi_name | |
AND PR.package_set = a_package_set | |
AND PR.abi = abi.name | |
AND PO.port_origin = PR.package_origin; | |
analyze packages_raw; | |
UPDATE packages p | |
SET package_version = PR.package_version | |
FROM packages_raw PR | |
WHERE P.abi_id = PR.abi_id | |
AND P.package_set = PR.package_set | |
AND P.package_name = PR.package_name | |
AND P.port_id = PR.port_id | |
AND P.package_version != PR.package_version | |
AND PR.abi = a_abi_name | |
AND PR.package_set = a_package_set; | |
GET DIAGNOSTICS l_updates = ROW_COUNT; | |
INSERT INTO packages (abi_id, port_id, package_version, package_name, package_set) | |
SELECT abi_id, port_id, package_version, package_name, package_set | |
FROM packages_raw PR | |
WHERE NOT EXISTS ( | |
SELECT * | |
FROM packages P | |
WHERE P.abi_id = PR.abi_id | |
AND P.package_set = PR.package_set | |
AND P.package_name = PR.package_name | |
AND P.port_id = PR.port_id | |
AND P.package_version = PR.package_version | |
AND PR.abi = a_abi_name | |
AND PR.package_set = a_package_set) | |
AND PR.abi_id IS NOT NULL | |
AND PR.port_id IS NOT NULL | |
AND PR.abi = a_abi_name | |
AND PR.package_set = a_package_set; | |
GET DIAGNOSTICS l_inserts = ROW_COUNT; | |
DELETE FROM packages P | |
WHERE P.abi_id = l_abi_id | |
AND P.package_set = a_package_set | |
AND NOT EXISTS ( | |
SELECT * | |
FROM packages_raw PR | |
WHERE P.abi_id = PR.abi_id | |
AND P.package_set = PR.package_set | |
AND P.package_name = PR.package_name | |
AND P.port_id = PR.port_id | |
AND PR.abi = a_abi_name | |
AND PR.package_set = a_package_set); | |
GET DIAGNOSTICS l_deletes = ROW_COUNT; | |
INSERT INTO package_imports (abi_id, package_set, date, inserts, updates, deletes) | |
values (l_abi_id, a_package_set, CURRENT_TIMESTAMP, l_inserts, l_updates, l_deletes); | |
RETURN QUERY (SELECT l_inserts, l_updates, l_deletes); | |
END; | |
$$; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment