-- oracle equivalent source sha1 df6188637d4b993e47fc2105624d515e452f18a5
-- Temporary procedure
--
-- For each minion server associated with a configuration channel but not
-- associated with any state revision create a revision and associate the
-- server with it.
--
-- This is needed as we need to upgrade the association of server's state
-- revision to configuration channel (based on rhnServerConfigChannel) in the
-- following steps. For this, the revision and it's association to the server
-- must exist.
--

CREATE OR REPLACE FUNCTION suse_create_revision_for_servers_with_conf_channel()
RETURNS VOID AS
$$
DECLARE
    new_rev_id numeric := 0;
    sid numeric := 0;
BEGIN
    FOR sid IN SELECT DISTINCT serverChannel.server_id
               FROM rhnServerConfigChannel serverChannel INNER JOIN suseMinionInfo minionInfo
               ON serverChannel.server_id = minionInfo.server_id
               WHERE NOT EXISTS (SELECT 1 FROM suseServerStateRevision WHERE server_id = serverChannel.server_id)
    LOOP
        SELECT sequence_nextval('suse_state_revision_id_seq') INTO new_rev_id FROM DUAL;
        raise notice 'INSERTING NEW STATE REVISION: %', new_rev_id;
        EXECUTE 'INSERT INTO suseStateRevision(id) VALUES (' || new_rev_id || ');';
        raise notice 'LINKING IT TO SERVER: %', sid;
        EXECUTE 'INSERT INTO suseServerStateRevision(server_id, state_revision_id) VALUES (' || sid || ', ' || new_rev_id || ');';
    END LOOP;
END
$$
LANGUAGE plpgsql;
SELECT 1 FROM suse_create_revision_for_servers_with_conf_channel();
DROP FUNCTION IF EXISTS suse_create_revision_for_servers_with_conf_channel();
