CMSgov/dpc-app

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <changeSet id="create-rootkey-table" author="nickrobison-usds">
        <createTable tableName="ROOT_KEYS">
            <column name="id" type="VARCHAR">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="key" type="VARCHAR">
                <constraints nullable="false"/>
            </column>
            <column name="created" type="DATETIME">
                <constraints nullable="false"/>
            </column>
            <column name="expires" type="DATETIME">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <createIndex tableName="ROOT_KEYS" indexName="root_key_expire_idx">
            <column name="expires"/>
        </createIndex>

        <!--        Add the expiration trigger-->
        <createProcedure>
            <![CDATA[
            CREATE OR REPLACE FUNCTION expire_root_keys() RETURNS trigger
            LANGUAGE plpgsql
            AS $$
            BEGIN
            DELETE FROM ROOT_KEYS WHERE expires < NOW();
            RETURN NEW;
            END;
            $$;
            ]]>
        </createProcedure>

        <createProcedure>
            CREATE TRIGGER root_key_expiration_trigger
            BEFORE INSERT ON ROOT_KEYS
            EXECUTE PROCEDURE expire_root_keys();
        </createProcedure>
    </changeSet>

    <!--    This file is deprecated and no longer in use-->
</databaseChangeLog>