<commit_before><?xml version="1.0"?>
<!--
 * Copyright (c) 2009-2013, rultor.com
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met: 1) Redistributions of source code must retain the above
 * copyright notice, this list of conditions and the following
 * disclaimer. 2) Redistributions in binary form must reproduce the above
 * copyright notice, this list of conditions and the following
 * disclaimer in the documentation and/or other materials provided
 * with the distribution. 3) Neither the name of the rultor.com nor
 * the names of its contributors may be used to endorse or promote
 * products derived from this software without specific prior written
 * permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT
 * NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
 * FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL
 * THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
 * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
 * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
 * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT,
 * STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
 * OF THE POSSIBILITY OF SUCH DAMAGE.
 -->
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
    <changeSet author="<EMAIL>" id="153.2">
        <sql splitStatements="false"><![CDATA[
            CREATE OR REPLACE FUNCTION compress() RETURNS TRIGGER AS $$
            DECLARE
                date TIMESTAMP;
            BEGIN
                date := DATE_TRUNC('week', NOW()) - '1 s'::INTERVAL;
                CREATE TEMPORARY TABLE t AS
                    SELECT ct, ctrule, dt, dtrule, SUM(amount) AS amt, COUNT(id) AS cnt
                    FROM receipt
                    WHERE time < date AND @ amount > 10000
                    GROUP BY ct, ctrule, dt, dtrule;
                INSERT INTO receipt (ct, ctrule, dt, dtrule, amount, details)
                    SELECT t.ct, t.ctrule, t.dt, t.dtrule, t.amt, FORMAT('%s transaction(s)', t.cnt)
                    FROM t;
                DROP TABLE t;
                DELETE FROM receipt
                    WHERE time < date;
                RETURN NULL;
            END;
            $$ LANGUAGE plpgsql
        ]]></sql>
    </changeSet>
    <changeSet author="<EMAIL>" id="153.3" context="test" runAlways="true">
        <insert schemaName="public" tableName="receipt">
            <column name="dt" value="urn:test:1"/>
            <column name="dtrule" value="first"/>
            <column name="ct" value="urn:test:5"/>
            <column name="ctrule" value="second"/>
            <column name="amount" value="50"/>
            <column name="details" value="first test transaction"/>
        </insert>
        <insert schemaName="public" tableName="receipt">
            <column name="dt" value="urn:test:1"/>
            <column name="dtrule" value="first"/>
            <column name="ct" value="urn:test:6"/>
            <column name="ctrule" value="second"/>
            <column name="amount" value="500"/>
            <column name="details" value="second test transaction"/>
        </insert>
    </changeSet><commit_msg>#153 fixed, no more temporary table<commit_after><?xml version="1.0"?>
<!--
 * Copyright (c) 2009-2013, rultor.<EMAIL>
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met: 1) Redistributions of source code must retain the above
 * copyright notice, this list of conditions and the following
 * disclaimer. 2) Redistributions in binary form must reproduce the above
 * copyright notice, this list of conditions and the following
 * disclaimer in the documentation and/or other materials provided
 * with the distribution. 3) Neither the name of the rultor.com nor
 * the names of its contributors may be used to endorse or promote
 * products derived from this software without specific prior written
 * permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT
 * NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
 * FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL
 * THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
 * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
 * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
 * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT,
 * STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
 * OF THE POSSIBILITY OF SUCH DAMAGE.
 -->
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
    <changeSet author="<EMAIL>" id="153.2">
        <sql splitStatements="false"><![CDATA[
            DROP TRIGGER compress_on_insert ON receipt;
        ]]></sql>
    </changeSet>
    <changeSet author="<EMAIL>" id="153.3">
        <sql splitStatements="false"><![CDATA[
            CREATE TRIGGER compress_on_insert
            AFTER INSERT ON receipt
            FOR EACH ROW
            WHEN ( NEW.wrule IS NOT NULL )
            EXECUTE PROCEDURE compress();
        ]]></sql>
    </changeSet>
    <changeSet author="<EMAIL>" id="153.4">
        <sql splitStatements="false"><![CDATA[
            CREATE OR REPLACE FUNCTION compress() RETURNS TRIGGER AS $$
            DECLARE
                threshold TIMESTAMP;
            BEGIN
                threshold := DATE_TRUNC('week', NOW()) - '1 s'::INTERVAL;
                INSERT INTO receipt (time, ct, ctrule, dt, dtrule, amount, details)
                    SELECT threshold, ct, ctrule, dt, dtrule, SUM(amount), FORMAT('%s transaction(s)', COUNT(id))
                        FROM receipt
                        WHERE time < threshold AND @ amount > 10000
                        GROUP BY ct, ctrule, dt, dtrule;
                DELETE FROM receipt WHERE time < threshold;
                RETURN NULL;
            END;
            $$ LANGUAGE plpgsql
        ]]></sql>
    </changeSet>
    <changeSet author="<EMAIL>" id="153.5" context="test" runAlways="true">
        <insert schemaName="public" tableName="receipt">
            <column name="dt" value="urn:test:1"/>
            <column name="dtrule" value="first"/>
            <column name="ct" value="urn:test:5"/>
            <column name="ctrule" value="second"/>
            <column name="amount" value="50"/>
            <column name="details" value="first test transaction"/>
        </insert>
        <insert schemaName="public" tableName="receipt">
            <column name="dt" value="urn:test:1"/>
            <column name="dtrule" value="first"/>
            <column name="ct" value="urn:test:6"/>
            <column name="ctrule" value="second"/>
            <column name="amount" value="500"/>
            <column name="details" value="second test transaction"/>
        </insert>
    </changeSet>
{'fraction_non_alphanumeric': 0.1109093110546071, 'fraction_numerical': 0.011744763288533721, 'mean_word_length': 2.719045475011256, 'pattern_counts': {'":': 0, '<': 76, '<?xml version=': 2, '>': 74, 'https://': 0, 'lorem ipsum': 0, 'www.': 14, 'xml': 20}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5999263', 'n_tokens_mistral': 2488, 'n_tokens_neox': 2196, 'n_words': 770}