firebird-qa/tests/bugs/core_5545_test.py

#coding:utf-8

"""
ID:          issue-5813
ISSUE:       5813
TITLE:       Wrong syntax with CREATE TRIGGER ... ON <table> used with POSITION
DESCRIPTION:
  Test checks that:
  1. Triggers with POSITION can be accepted only when this clause is at the END of declaration
     (but before SQL SECURITY clause because it belongs to '<routine body>' rather than to the trigger header.
  2. Any kind of trigger which has POSITION not in the end of header section will not compile.
     Failed DDL statement, gdscode and SQLSTATE are written into special table, DDL_AUDIT (in autonomous Tx, of course).

  Finally, we make output of triggers list which present in the database (i.e. which passed) and list of failed DDLs
  from the table DDL_AUDIT, with filtering out any details of their declarations (we need only trigger names from failed DDL).

  ::: NB ::: Section 'expected_stdout' was changed.
  Trigger 'trg_test_bi_sql2003_02' violates SQL:2003 but such form is maintained for backward compatibility.
  This trigger is compiled successfully since build 4.0.0.1767.
JIRA:        CORE-5545
FBTEST:      bugs.core_5545
"""

import pytest
from firebird.qa import *

db = db_factory()

test_script = """
    create or alter procedure sp_try2run as begin end;
    recreate table ddl_audit(
        id int generated by default as identity constraint pk_ddl primary key
        ,failed_ddl varchar(1024)
        ,gds_code bigint
        ,sql_state char(5)
    );

    set term ^;
    create or alter procedure sp_try2run(ddl_sttm type of column ddl_audit.failed_ddl) as
    begin
        execute statement (ddl_sttm);
        when any do
        begin
            in autonomous transaction do
            insert into ddl_audit(failed_ddl, gds_code, sql_state) values(:ddl_sttm, gdscode, sqlstate);
        end
    end
    ^
    set term ;^
    commit;


    recreate table test(id int);
    commit;

    set term ^;
    -- must be accepted because POSITION is at the end of decl.
    create or alter trigger trg_test_bi_legacy_01
        for test
        inactive
        before insert
        position 1
        sql security definer
    as begin
    end
    ^

    -- must be accepted because POSITION is at the end of decl.
    create or alter trigger trg_test_bi_sql2003_01
        inactive
        before insert
        on test
        position 1
        sql security definer
    as begin
    end
    ^


    -- must be accepted because POSITION is at the end of decl.
    create or alter trigger trg_dbevent_01
        inactive
        on disconnect
        position 1
        sql security definer
    as begin
    end
    ^


    -- must be accepted because POSITION is at the end of decl.
    create or alter trigger trg_ddl_01
        inactive
        before any ddl statement
        position 1
        sql security definer
    as begin
    end
    ^
    commit -- otherwise concurrent update raises... why ?..
    ^
    -- #####################################################
    -- ### All following DDL statements must FAILED with ###
    -- ### SQLSTATE = 42000 / -Token unknown / -position ###
    -- #####################################################

    execute block as
        declare ddl_sttm type of column ddl_audit.failed_ddl;
    begin

        --  ===========================
        --  1. LEGACY-STYLE DECLARATION
        --  ===========================
        ddl_sttm =
            'create or alter trigger trg_test_bi_legacy_02
                for test
                inactive
                position 2
                before insert
            as begin
            end'
        ;
        execute procedure sp_try2run(ddl_sttm);


        ddl_sttm =
            'create or alter trigger trg_test_bi_legacy_03
                for test
                position 3
                inactive
                before insert
            as begin
            end'
        ;
        execute procedure sp_try2run(ddl_sttm);

        ddl_sttm =
            'create or alter trigger trg_test_bi_legacy_04
                position 4
                for test
                inactive
                before insert
            as begin
            end'
        ;
        execute procedure sp_try2run(ddl_sttm);


        --  =============================
        --  2. SQL-2003 STYLE DECLARATION
        --  =============================

        -- 24.04.2020: this form must PASS though it is violates requirements of SQL:2003.
        -- This behaviour is kept for backward compatibility - see explanation by Adriano,
        -- letter 24.04.2020 17:20.
        -- ::: NB ::: documentation for FB 4.x need to be changed because of this.
        ddl_sttm =
            'create or alter trigger trg_test_bi_sql2003_02
                inactive
                before insert
                position 2
                on test
            as begin
            end'
        ;
        execute procedure sp_try2run(ddl_sttm);


        ddl_sttm =
            'create or alter trigger trg_test_bi_sql2003_03
                inactive
                position 3
                before insert
                on test
            as begin
            end'
        ;
        execute procedure sp_try2run(ddl_sttm);


        ddl_sttm =
            'create or alter trigger trg_test_bi_sql2003_04
                position 4
                inactive
                before insert
                on test
            as begin
            end'
        ;
        execute procedure sp_try2run(ddl_sttm);


        --  =======================
        --  3. DB-LEVEL DECLARATION
        --  =======================

        ddl_sttm =
            'create or alter trigger trg_dbevent_02
                position 2
                inactive
                on disconnect
            as begin
            end'
        ;
        execute procedure sp_try2run(ddl_sttm);


        ddl_sttm =
            'create or alter trigger trg_dbevent_03
                inactive
                position 3
                on disconnect
            as begin
            end'
        ;
        execute procedure sp_try2run(ddl_sttm);


        --  ========================
        --  4. DDL-LEVEL DECLARATION
        --  ========================

        ddl_sttm =
            'create or alter trigger trg_ddl_02
                inactive
                position 2
                before any ddl statement
            as begin
            end'
        ;
        execute procedure sp_try2run(ddl_sttm);


        ddl_sttm =
            'create or alter trigger trg_ddl_03
                position 3
                inactive
                before any ddl statement
            as begin
            end'
        ;
        execute procedure sp_try2run(ddl_sttm);


    end
    ^
    set term ;^
    commit;
    set list on;
    set count on;
    select rdb$trigger_name as accepted_ddl from rdb$triggers where rdb$system_flag is distinct from 1;
    select * from ddl_audit;
"""

act = isql_act('db', test_script,
               substitutions=[('^((?!(ACCEPTED_DDL|Records affected|FAILED_DDL|GDS_CODE|SQL_STATE)).)*$', '')])

expected_stdout = """
    ACCEPTED_DDL                    TRG_TEST_BI_LEGACY_01

    ACCEPTED_DDL                    TRG_TEST_BI_SQL2003_01

    ACCEPTED_DDL                    TRG_DBEVENT_01

    ACCEPTED_DDL                    TRG_DDL_01

    ACCEPTED_DDL                    TRG_TEST_BI_SQL2003_02


    Records affected: 5

    ID                              1
    FAILED_DDL                      create or alter trigger trg_test_bi_legacy_02
                    for test
                    inactive
                    position 2
                    before insert
                as begin
                end
    GDS_CODE                        335544569
    SQL_STATE                       42000

    ID                              2
    FAILED_DDL                      create or alter trigger trg_test_bi_legacy_03
                    for test
                    position 3
                    inactive
                    before insert
                as begin
                end
    GDS_CODE                        335544569
    SQL_STATE                       42000

    ID                              3
    FAILED_DDL                      create or alter trigger trg_test_bi_legacy_04
                    position 4
                    for test
                    inactive
                    before insert
                as begin
                end
    GDS_CODE                        335544569
    SQL_STATE                       42000

    ID                              4
    FAILED_DDL                      create or alter trigger trg_test_bi_sql2003_03
                    inactive
                    position 3
                    before insert
                    on test
                as begin
                end
    GDS_CODE                        335544569
    SQL_STATE                       42000

    ID                              5
    FAILED_DDL                      create or alter trigger trg_test_bi_sql2003_04
                    position 4
                    inactive
                    before insert
                    on test
                as begin
                end
    GDS_CODE                        335544569
    SQL_STATE                       42000

    ID                              6
    FAILED_DDL                      create or alter trigger trg_dbevent_02
                    position 2
                    inactive
                    on disconnect
                as begin
                end
    GDS_CODE                        335544569
    SQL_STATE                       42000

    ID                              7
    FAILED_DDL                      create or alter trigger trg_dbevent_03
                    inactive
                    position 3
                    on disconnect
                as begin
                end
    GDS_CODE                        335544569
    SQL_STATE                       42000

    ID                              8
    FAILED_DDL                      create or alter trigger trg_ddl_02
                    inactive
                    position 2
                    before any ddl statement
                as begin
                end
    GDS_CODE                        335544569
    SQL_STATE                       42000

    ID                              9
    FAILED_DDL                      create or alter trigger trg_ddl_03
                    position 3
                    inactive
                    before any ddl statement
                as begin
                end
    GDS_CODE                        335544569
    SQL_STATE                       42000


    Records affected: 9
"""

@pytest.mark.version('>=4.0')
def test_1(act: Action):
    act.expected_stdout = expected_stdout
    act.execute()
    assert act.clean_stdout == act.clean_expected_stdout