/*

* Copyright (c) 2004-2019 Tada AB and other contributors, as listed below.

*

* All rights reserved. This program and the accompanying materials

* are made available under the terms of the The BSD 3-Clause License

* which accompanies this distribution, and is available at

* http://opensource.org/licenses/BSD-3-Clause

*

* Contributors:

* Tada AB

* Chapman Flack

*/

package org.postgresql.pljava.example;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Timestamp;

import java.util.logging.Logger;

import org.postgresql.pljava.SessionManager;

import org.postgresql.pljava.TriggerData;

import org.postgresql.pljava.TriggerException;

/**

* This class contains some triggers that I found written in C under the

* contrib/spi directory of the postgres source distribution. Code to create the

* necessary tables, functions, triggers, and some code to actually execute them

* can be found in class {@code org.postgresql.pljava.test.Tester} (no longer

* part of the build, see under {@code src/java/test}).

*

* @author Thomas Hallgren

*/

public class Triggers {

public static void afterUsernameInsert(TriggerData td) throws SQLException {

Logger log = Logger.getAnonymousLogger();

log.info("After username insert, username = "

+ td.getNew().getInt("username"));

}

public static void afterUsernameUpdate(TriggerData td) throws SQLException {

Logger log = Logger.getAnonymousLogger();

if (td.isFiredForStatement())

throw new TriggerException(td, "can't process STATEMENT events");

if (td.isFiredBefore())

throw new TriggerException(td, "must be fired after event");

if (!td.isFiredByUpdate())

throw new TriggerException(td,

"can't process DELETE or INSERT events");

ResultSet _new = td.getNew();

String[] args = td.getArguments();

if (args.length != 1)

throw new TriggerException(td, "one argument was expected");

String colName = args[0];

ResultSet _old = td.getOld();

log.info("Old name is \"" + _old.getString(colName) + '"');

log.info("New name is \"" + _new.getString(colName) + '"');

}

/**

* insert user name in response to a trigger.

*/

public static void insertUsername(TriggerData td) throws SQLException {

if (td.isFiredForStatement())

throw new TriggerException(td, "can't process STATEMENT events");

if (td.isFiredAfter())

throw new TriggerException(td, "must be fired before event");

if (td.isFiredByDelete())

throw new TriggerException(td, "can't process DELETE events");

ResultSet _new = td.getNew();

String[] args = td.getArguments();

if (args.length != 1)

throw new TriggerException(td, "one argument was expected");

if (_new.getString(args[0]) == null)

_new.updateString(args[0], SessionManager.current().getUserName());

}

public static void leakStatements(TriggerData td) throws SQLException {

StringBuffer buf = new StringBuffer();

buf.append("Trigger ");

buf.append(td.getName());

buf.append(" declared on table ");

buf.append(td.getTableName());

buf.append(" was fired ");

if (td.isFiredAfter())

buf.append("after");

else

buf.append("before");

buf.append(' ');

if (td.isFiredByDelete())

buf.append("delete");

else if (td.isFiredByInsert())

buf.append("insert");

else

buf.append("update");

if (td.isFiredForEachRow())

buf.append(" on each row");

// DON'T DO LIKE THIS!!! Connection, PreparedStatement, and ResultSet

// instances

// should always be closed.

//

int max = Integer.MIN_VALUE;

Connection conn = DriverManager

.getConnection("jdbc:default:connection");

PreparedStatement stmt = conn

.prepareStatement("SELECT base FROM setReturnExample(?, ?)");

stmt.setInt(1, 5);

stmt.setInt(2, 8);

ResultSet rs = stmt.executeQuery();

while (rs.next()) {

int base = rs.getInt(1);

if (base > max)

max = base;

}

buf.append(" reports max = " + max);

stmt = conn

.prepareStatement("INSERT INTO javatest.mdt (idesc) VALUES (?)");

stmt.setString(1, buf.toString());

stmt.executeUpdate();

}

/**

* Update a modification time when the row is updated.

*/

public static void moddatetime(TriggerData td) throws SQLException {

if (td.isFiredForStatement())

throw new TriggerException(td, "can't process STATEMENT events");

if (td.isFiredAfter())

throw new TriggerException(td, "must be fired before event");

if (!td.isFiredByUpdate())

throw new TriggerException(td, "can only process UPDATE events");

ResultSet _new = td.getNew();

String[] args = td.getArguments();

if (args.length != 1)

throw new TriggerException(td, "one argument was expected");

_new.updateTimestamp(args[0], new Timestamp(System.currentTimeMillis()));

}

}