001/*
002 * Copyright (c) 2004-2019 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 *   Chapman Flack
012 */
013package org.postgresql.pljava.example;
014
015import java.sql.Connection;
016import java.sql.DriverManager;
017import java.sql.PreparedStatement;
018import java.sql.ResultSet;
019import java.sql.SQLException;
020import java.sql.Timestamp;
021import java.util.logging.Logger;
022
023import org.postgresql.pljava.SessionManager;
024import org.postgresql.pljava.TriggerData;
025import org.postgresql.pljava.TriggerException;
026
027/**
028 * This class contains some triggers that I found written in C under the
029 * contrib/spi directory of the postgres source distribution. Code to create the
030 * necessary tables, functions, triggers, and some code to actually execute them
031 * can be found in class {@code org.postgresql.pljava.test.Tester} (no longer
032 * part of the build, see under {@code src/java/test}).
033 * 
034 * @author Thomas Hallgren
035 */
036public class Triggers {
037    public static void afterUsernameInsert(TriggerData td) throws SQLException {
038        Logger log = Logger.getAnonymousLogger();
039        log.info("After username insert, username = "
040                + td.getNew().getInt("username"));
041    }
042
043    public static void afterUsernameUpdate(TriggerData td) throws SQLException {
044        Logger log = Logger.getAnonymousLogger();
045        if (td.isFiredForStatement())
046            throw new TriggerException(td, "can't process STATEMENT events");
047
048        if (td.isFiredBefore())
049            throw new TriggerException(td, "must be fired after event");
050
051        if (!td.isFiredByUpdate())
052            throw new TriggerException(td,
053                    "can't process DELETE or INSERT events");
054
055        ResultSet _new = td.getNew();
056        String[] args = td.getArguments();
057        if (args.length != 1)
058            throw new TriggerException(td, "one argument was expected");
059        String colName = args[0];
060
061        ResultSet _old = td.getOld();
062        log.info("Old name is \"" + _old.getString(colName) + '"');
063        log.info("New name is \"" + _new.getString(colName) + '"');
064    }
065
066    /**
067     * insert user name in response to a trigger.
068     */
069    public static void insertUsername(TriggerData td) throws SQLException {
070        if (td.isFiredForStatement())
071            throw new TriggerException(td, "can't process STATEMENT events");
072
073        if (td.isFiredAfter())
074            throw new TriggerException(td, "must be fired before event");
075
076        if (td.isFiredByDelete())
077            throw new TriggerException(td, "can't process DELETE events");
078
079        ResultSet _new = td.getNew();
080        String[] args = td.getArguments();
081        if (args.length != 1)
082            throw new TriggerException(td, "one argument was expected");
083
084        if (_new.getString(args[0]) == null)
085            _new.updateString(args[0], SessionManager.current().getUserName());
086    }
087
088    public static void leakStatements(TriggerData td) throws SQLException {
089        StringBuffer buf = new StringBuffer();
090
091        buf.append("Trigger ");
092        buf.append(td.getName());
093        buf.append(" declared on table ");
094        buf.append(td.getTableName());
095        buf.append(" was fired ");
096        if (td.isFiredAfter())
097            buf.append("after");
098        else
099            buf.append("before");
100
101        buf.append(' ');
102        if (td.isFiredByDelete())
103            buf.append("delete");
104        else if (td.isFiredByInsert())
105            buf.append("insert");
106        else
107            buf.append("update");
108
109        if (td.isFiredForEachRow())
110            buf.append(" on each row");
111
112        // DON'T DO LIKE THIS!!! Connection, PreparedStatement, and ResultSet
113        // instances
114        // should always be closed.
115        //
116        int max = Integer.MIN_VALUE;
117        Connection conn = DriverManager
118                .getConnection("jdbc:default:connection");
119        PreparedStatement stmt = conn
120                .prepareStatement("SELECT base FROM setReturnExample(?, ?)");
121        stmt.setInt(1, 5);
122        stmt.setInt(2, 8);
123        ResultSet rs = stmt.executeQuery();
124        while (rs.next()) {
125            int base = rs.getInt(1);
126            if (base > max)
127                max = base;
128        }
129        buf.append(" reports max = " + max);
130        stmt = conn
131                .prepareStatement("INSERT INTO javatest.mdt (idesc) VALUES (?)");
132        stmt.setString(1, buf.toString());
133        stmt.executeUpdate();
134    }
135
136    /**
137     * Update a modification time when the row is updated.
138     */
139    public static void moddatetime(TriggerData td) throws SQLException {
140        if (td.isFiredForStatement())
141            throw new TriggerException(td, "can't process STATEMENT events");
142
143        if (td.isFiredAfter())
144            throw new TriggerException(td, "must be fired before event");
145
146        if (!td.isFiredByUpdate())
147            throw new TriggerException(td, "can only process UPDATE events");
148
149        ResultSet _new = td.getNew();
150        String[] args = td.getArguments();
151        if (args.length != 1)
152            throw new TriggerException(td, "one argument was expected");
153        _new.updateTimestamp(args[0], new Timestamp(System.currentTimeMillis()));
154    }
155}