001/*
002 * Copyright (c) 2004-2023 Tada AB and other contributors, as listed below.
003 *
004 * All rights reserved. This program and the accompanying materials
005 * are made available under the terms of the The BSD 3-Clause License
006 * which accompanies this distribution, and is available at
007 * http://opensource.org/licenses/BSD-3-Clause
008 *
009 * Contributors:
010 *   Tada AB
011 *   Purdue University
012 *   Chapman Flack
013 */
014package org.postgresql.pljava.example.annotation;
015
016import java.sql.Connection;
017import java.sql.DriverManager;
018import java.sql.ResultSet;
019import java.sql.SQLException;
020import java.sql.SQLIntegrityConstraintViolationException;
021import java.sql.Statement;
022
023import org.postgresql.pljava.TriggerData;
024import org.postgresql.pljava.annotation.Function;
025import org.postgresql.pljava.annotation.SQLAction;
026import org.postgresql.pljava.annotation.Trigger;
027import static org.postgresql.pljava.annotation.Trigger.Called.*;
028import static org.postgresql.pljava.annotation.Trigger.Constraint.*;
029import static org.postgresql.pljava.annotation.Trigger.Event.*;
030import static org.postgresql.pljava.annotation.Trigger.Scope.*;
031import static org.postgresql.pljava.annotation.Function.Security.*;
032
033import static org.postgresql.pljava.example.LoggerTest.logMessage;
034
035/**
036 * Example creating a couple of tables, and a function to be called when
037 * triggered by insertion into either table. In PostgreSQL 10 or later,
038 * also create a function and trigger that uses transition tables.
039 *<p>
040 * This example relies on {@code implementor} tags reflecting the PostgreSQL
041 * version, set up in the {@link ConditionalDDR} example. Transition tables
042 * appear in PG 10.
043 */
044@SQLAction(
045    provides = "foobar tables",
046    install = {
047        "CREATE TABLE javatest.foobar_1 ( username text, stuff text )",
048        "CREATE TABLE javatest.foobar_2 ( username text, value numeric )"
049    },
050    remove = {
051        "DROP TABLE javatest.foobar_2",
052        "DROP TABLE javatest.foobar_1"
053    }
054)
055@SQLAction(
056    requires = "constraint triggers",
057    install = "INSERT INTO javatest.foobar_2(value) VALUES (45)"
058)
059@SQLAction(
060    requires = "foobar triggers",
061    provides = "foobar2_42",
062    install = "INSERT INTO javatest.foobar_2(value) VALUES (42)"
063)
064@SQLAction(
065    requires = { "transition triggers", "foobar2_42" },
066    install = "UPDATE javatest.foobar_2 SET value = 43 WHERE value = 42"
067)
068/*
069 * Note for another day: this would seem an excellent place to add a
070 * regression test for github issue #134 (make sure invocations of a
071 * trigger do not fail with SPI_ERROR_UNCONNECTED). However, any test
072 * here that runs from the deployment descriptor will be running when
073 * SPI is already connected, so a regression would not be caught.
074 * A proper test for it will have to wait for a proper testing harness
075 * invoking tests from outside PL/Java itself.
076 */
077public class Triggers
078{
079    /**
080     * insert user name in response to a trigger.
081     */
082    @Function(
083        requires = "foobar tables",
084        provides = "foobar triggers",
085        schema = "javatest",
086        security = INVOKER,
087        triggers = {
088            @Trigger(called = BEFORE, table = "foobar_1", events = { INSERT } ),
089            @Trigger(called = BEFORE, scope = ROW, table = "foobar_2",
090                     events = { INSERT } )
091        })
092
093    public static void insertUsername(TriggerData td)
094    throws SQLException
095    {
096        ResultSet nrs = td.getNew(); // expect NPE in a DELETE/STATEMENT trigger
097        String col2asString = nrs.getString(2);
098        if ( "43".equals(col2asString) )
099            td.suppress();
100        nrs.updateString( "username", "bob");
101    }
102
103    /**
104     * Examine old and new rows in reponse to a trigger.
105     * Transition tables first became available in PostgreSQL 10.
106     */
107    @Function(
108        implementor = "postgresql_ge_100000",
109        requires = "foobar tables",
110        provides = "transition triggers",
111        schema = "javatest",
112        security = INVOKER,
113        triggers = {
114            @Trigger(called = AFTER, table = "foobar_2", events = { UPDATE },
115                     tableOld = "oldrows", tableNew = "newrows" )
116        })
117
118    public static void examineRows(TriggerData td)
119    throws SQLException
120    {
121        Connection co = DriverManager.getConnection("jdbc:default:connection");
122        Statement st = co.createStatement();
123        ResultSet rs = st.executeQuery(
124            "SELECT o.value, n.value" +
125            " FROM oldrows o FULL JOIN newrows n USING (username)");
126        rs.next();
127        int oval = rs.getInt(1);
128        int nval = rs.getInt(2);
129        if ( 42 == oval && 43 == nval )
130            logMessage( "INFO", "trigger transition table test ok");
131        else
132            logMessage( "WARNING", String.format(
133                "trigger transition table oval %d nval %d", oval, nval));
134    }
135
136    /**
137     * Throw exception if value to be inserted is 44.
138     */
139    @Function(
140        requires = "foobar tables",
141        provides = "constraint triggers",
142        schema = "javatest",
143        security = INVOKER,
144        triggers = {
145            @Trigger(called = AFTER, table = "foobar_2", events = { INSERT },
146                     scope = ROW, constraint = NOT_DEFERRABLE )
147        })
148
149    public static void disallow44(TriggerData td)
150    throws SQLException
151    {
152        ResultSet nrs = td.getNew();
153        if ( 44 == nrs.getInt( "value") )
154            throw new SQLIntegrityConstraintViolationException(
155                "44 shall not be inserted", "23000");
156    }
157}