60 lines
2.3 KiB
MySQL
60 lines
2.3 KiB
MySQL
|
ALTER TABLE vendors
|
||
|
|
ADD COLUMN IF NOT EXISTS is_internet_provider BOOLEAN NOT NULL DEFAULT FALSE;
|
||
|
|
|
||
|
|
ALTER TABLE internet_connections_connections
|
||
|
|
ADD COLUMN IF NOT EXISTS vendor_id INTEGER REFERENCES vendors(id) ON DELETE SET NULL;
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_vendors_internet_provider
|
||
|
|
ON vendors(is_internet_provider, is_active);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_internet_connections_vendor
|
||
|
|
ON internet_connections_connections(vendor_id)
|
||
|
|
WHERE deleted_at IS NULL;
|
||
|
|
|
||
|
|
-- Link existing provider text only when it identifies exactly one vendor.
|
||
|
|
UPDATE internet_connections_connections connection
|
||
|
|
SET vendor_id = candidate.vendor_id
|
||
|
|
FROM (
|
||
|
|
SELECT LOWER(BTRIM(connection.provider)) AS provider_key, MIN(vendor.id) AS vendor_id
|
||
|
|
FROM internet_connections_connections connection
|
||
|
|
JOIN vendors vendor ON LOWER(BTRIM(vendor.name)) = LOWER(BTRIM(connection.provider))
|
||
|
|
WHERE connection.deleted_at IS NULL AND NULLIF(BTRIM(connection.provider), '') IS NOT NULL
|
||
|
|
GROUP BY LOWER(BTRIM(connection.provider))
|
||
|
|
HAVING COUNT(DISTINCT vendor.id) = 1
|
||
|
|
) candidate
|
||
|
|
WHERE connection.deleted_at IS NULL
|
||
|
|
AND connection.vendor_id IS NULL
|
||
|
|
AND LOWER(BTRIM(connection.provider)) = candidate.provider_key;
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION assign_internet_connection_vendor()
|
||
|
|
RETURNS TRIGGER AS $$
|
||
|
|
DECLARE
|
||
|
|
matched_vendor_id INTEGER;
|
||
|
|
BEGIN
|
||
|
|
IF NEW.vendor_id IS NULL AND NULLIF(BTRIM(NEW.provider), '') IS NOT NULL THEN
|
||
|
|
SELECT id INTO matched_vendor_id
|
||
|
|
FROM vendors
|
||
|
|
WHERE is_active = TRUE
|
||
|
|
AND is_internet_provider = TRUE
|
||
|
|
AND regexp_replace(
|
||
|
|
regexp_replace(LOWER(name), '(denmark|danmark|a/s|as)', '', 'g'),
|
||
|
|
'[^a-z0-9]', '', 'g'
|
||
|
|
) = regexp_replace(
|
||
|
|
regexp_replace(LOWER(NEW.provider), '(denmark|danmark|a/s|as)', '', 'g'),
|
||
|
|
'[^a-z0-9]', '', 'g'
|
||
|
|
)
|
||
|
|
ORDER BY id
|
||
|
|
LIMIT 1;
|
||
|
|
NEW.vendor_id := matched_vendor_id;
|
||
|
|
END IF;
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
DROP TRIGGER IF EXISTS internet_connections_assign_vendor ON internet_connections_connections;
|
||
|
|
CREATE TRIGGER internet_connections_assign_vendor
|
||
|
|
BEFORE INSERT OR UPDATE OF provider, vendor_id
|
||
|
|
ON internet_connections_connections
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION assign_internet_connection_vendor();
|