Source code

001/*
002 * Copyright (c) 2004-2020 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.annotation;
014
015import java.sql.Connection;
016import java.sql.Date;
017import java.sql.DriverManager;
018import java.sql.PreparedStatement;
019import java.sql.ResultSet;
020import java.sql.SQLException;
021import java.sql.Savepoint;
022import java.sql.Statement;
023import java.sql.Time;
024import java.util.logging.Logger;
025
026import org.postgresql.pljava.SavepointListener;
027import org.postgresql.pljava.Session;
028import org.postgresql.pljava.SessionManager;
029import org.postgresql.pljava.TransactionListener;
030
031import org.postgresql.pljava.annotation.Function;
032import static org.postgresql.pljava.annotation.Function.Effects.*;
033import org.postgresql.pljava.annotation.SQLAction;
034
035/**
036 * Some methods used for testing the SPI JDBC driver.
037 *
038 * @author Thomas Hallgren
039 */
040@SQLAction(provides = "employees tables", install = {
041    "CREATE TABLE javatest.employees1" +
042    " (" +
043    " id     int PRIMARY KEY," +
044    " name   varchar(200)," +
045    " salary int" +
046    " )",
047
048    "CREATE TABLE javatest.employees2" +
049    " (" +
050    " id        int PRIMARY KEY," +
051    " name  varchar(200)," +
052    " salary    int," +
053    " transferDay date," +
054    " transferTime time" +
055    " )"
056    }, remove = {
057    "DROP TABLE javatest.employees2",
058    "DROP TABLE javatest.employees1"
059}
060)
061@SQLAction(requires = "issue228", install = "SELECT javatest.issue228()")
062public class SPIActions {
063    private static final String SP_CHECKSTATE = "sp.checkState";
064
065    private static final SavepointListener spListener = new SavepointListener() {
066        @Override
067        public void onAbort(Session session, Savepoint savepoint,
068                Savepoint parent) throws SQLException {
069            log("Abort of savepoint " + savepoint.getSavepointId());
070            nextState(session, 3, 0);
071        }
072
073        @Override
074        public void onCommit(Session session, Savepoint savepoint,
075                Savepoint parent) throws SQLException {
076            log("Commit of savepoint " + savepoint.getSavepointId());
077            nextState(session, 3, 4);
078        }
079
080        @Override
081        public void onStart(Session session, Savepoint savepoint,
082                Savepoint parent) throws SQLException {
083            log("Start of savepoint " + savepoint.getSavepointId());
084            nextState(session, 0, 1);
085        }
086    };
087
088    @Function(schema="javatest", effects=STABLE)
089    public static String getDateAsString() throws SQLException {
090        ResultSet rs = null;
091        Statement stmt = null;
092        Connection conn = DriverManager
093                .getConnection("jdbc:default:connection");
094        try {
095            stmt = conn.createStatement();
096            rs = stmt.executeQuery("SELECT CURRENT_DATE");
097            if (rs.next())
098                return rs.getDate(1).toString();
099            return "Date could not be retrieved";
100        } finally {
101            if (rs != null)
102                rs.close();
103            if (stmt != null)
104                stmt.close();
105            conn.close();
106        }
107    }
108
109    @Function(schema="javatest", effects=STABLE)
110    public static String getTimeAsString() throws SQLException {
111        ResultSet rs = null;
112        Statement stmt = null;
113        Connection conn = DriverManager
114                .getConnection("jdbc:default:connection");
115        try {
116            stmt = conn.createStatement();
117            rs = stmt.executeQuery("SELECT CURRENT_TIME");
118            if (rs.next())
119                return rs.getTime(1).toString();
120            return "Time could not be retrieved";
121        } finally {
122            if (rs != null)
123                rs.close();
124            if (stmt != null)
125                stmt.close();
126            conn.close();
127        }
128    }
129
130    static void log(String msg) {
131        // GCJ has a somewhat serious bug (reported)
132        //
133        if ("GNU libgcj".equals(System.getProperty("java.vm.name"))) {
134            System.out.print("INFO: ");
135            System.out.println(msg);
136        } else
137            Logger.getAnonymousLogger().info(msg);
138    }
139
140    static void warn(String msg) {
141        // GCJ has a somewhat serious bug (reported)
142        //
143        if ("GNU libgcj".equals(System.getProperty("java.vm.name"))) {
144            System.out.print("WARNING: ");
145            System.out.println(msg);
146        } else
147            Logger.getAnonymousLogger().warning(msg);
148    }
149
150    @Function(schema="javatest", effects=IMMUTABLE)
151    public static int maxFromSetReturnExample(int base, int increment)
152            throws SQLException {
153        int max = Integer.MIN_VALUE;
154        Connection conn = DriverManager
155                .getConnection("jdbc:default:connection");
156        PreparedStatement stmt = null;
157        ResultSet rs = null;
158
159        try {
160            stmt = conn
161                    .prepareStatement("SELECT base FROM setReturnExample(?, ?)");
162            stmt.setInt(1, base);
163            stmt.setInt(2, increment);
164            rs = stmt.executeQuery();
165            while (rs.next()) {
166                base = rs.getInt(1);
167                if (base > max)
168                    max = base;
169            }
170            return base;
171        } finally {
172            if (rs != null)
173                rs.close();
174            if (stmt != null)
175                stmt.close();
176            conn.close();
177        }
178    }
179
180    /**
181     * Test of bug #1556
182     */
183    @Function(schema="javatest")
184    public static void nestedStatements(int innerCount) throws SQLException {
185        Connection connection = DriverManager
186                .getConnection("jdbc:default:connection");
187        Statement statement = connection.createStatement();
188
189        // Create a set of ID's so that we can do somthing semi-useful during
190        // the long loop.
191        //
192        statement.execute("DELETE FROM javatest.employees1");
193        statement.execute("INSERT INTO javatest.employees1 VALUES("
194                + "1, 'Calvin Forrester', 10000)");
195        statement.execute("INSERT INTO javatest.employees1 VALUES("
196                + "2, 'Edwin Archer', 20000)");
197        statement.execute("INSERT INTO javatest.employees1 VALUES("
198                + "3, 'Rebecka Shawn', 30000)");
199        statement.execute("INSERT INTO javatest.employees1 VALUES("
200                + "4, 'Priscilla Johnson', 25000)");
201
202        int idx = 1;
203        ResultSet results = statement
204                .executeQuery("SELECT * FROM javatest.hugeResult(" + innerCount
205                        + ")");
206        while (results.next()) {
207            Statement innerStatement = connection.createStatement();
208            innerStatement
209                    .executeUpdate("UPDATE javatest.employees1 SET salary = salary + 1 WHERE id="
210                            + idx);
211            innerStatement.close();
212            if (++idx == 5)
213                idx = 0;
214        }
215        results.close();
216        statement.close();
217        connection.close();
218    }
219
220    @SuppressWarnings("removal") // getAttribute / setAttribute
221    private static void nextState(Session session, int expected, int next)
222            throws SQLException {
223        Integer state = (Integer) session.getAttribute(SP_CHECKSTATE);
224        if (state == null || state.intValue() != expected)
225            throw new SQLException(SP_CHECKSTATE + ": Expected " + expected
226                    + ", got " + state);
227        session.setAttribute(SP_CHECKSTATE, next);
228    }
229
230    @Function(schema="javatest", effects=IMMUTABLE)
231    @SuppressWarnings("removal") // setAttribute
232    public static int testSavepointSanity() throws SQLException {
233        Connection conn = DriverManager
234                .getConnection("jdbc:default:connection");
235
236        // Create an anonymous savepoint.
237        //
238        log("Attempting to set an anonymous savepoint");
239        Session currentSession = SessionManager.current();
240        currentSession.setAttribute(SP_CHECKSTATE, 0);
241        currentSession.addSavepointListener(spListener);
242
243        Savepoint sp = conn.setSavepoint();
244        nextState(currentSession, 1, 2);
245        try {
246            Statement stmt = conn.createStatement();
247            log("Attempting to set a SAVEPOINT using SQL (should fail)");
248            stmt.execute("SAVEPOINT foo");
249        } catch (SQLException e) {
250            log("It failed allright. Everything OK then");
251            log("Rolling back to anonymous savepoint");
252
253            nextState(currentSession, 2, 3);
254            conn.rollback(sp);
255            nextState(currentSession, 1, 5);
256            return 1;
257        } finally {
258            currentSession.removeSavepointListener(spListener);
259        }
260        throw new SQLException(
261                "SAVEPOINT through SQL succeeded. That's bad news!");
262    }
263
264    /**
265     * Confirm JDBC behavior of Savepoint, in particular that a Savepoint
266     * rolled back to still exists and can be rolled back to again or released.
267     */
268    @Function(schema="javatest", provides="issue228")
269    public static void issue228() throws SQLException
270    {
271        boolean ok = true;
272        Connection conn =
273            DriverManager.getConnection("jdbc:default:connection");
274        Statement s = conn.createStatement();
275        try
276        {
277            Savepoint alice = conn.setSavepoint("alice");
278            s.execute("SET LOCAL TIME ZONE 1");
279            Savepoint bob   = conn.setSavepoint("bob");
280            s.execute("SET LOCAL TIME ZONE 2");
281            conn.rollback(bob);
282            s.execute("SET LOCAL TIME ZONE 3");
283            conn.releaseSavepoint(bob);
284            try
285            {
286                conn.rollback(bob);
287                ok = false;
288                warn("Savepoint \"bob\" should be invalid after release");
289            }
290            catch ( SQLException e )
291            {
292                if ( ! "3B001".equals(e.getSQLState()) )
293                    throw e;
294            }
295            conn.rollback(alice);
296            bob = conn.setSavepoint("bob");
297            s.execute("SET LOCAL TIME ZONE 4");
298            conn.rollback(alice);
299            try
300            {
301                conn.releaseSavepoint(bob);
302                ok = false;
303                warn(
304                    "Savepoint \"bob\" should be invalid after outer rollback");
305            }
306            catch ( SQLException e )
307            {
308                if ( ! "3B001".equals(e.getSQLState()) )
309                    throw e;
310            }
311            conn.rollback(alice);
312            s.execute("SET LOCAL TIME ZONE 5");
313            conn.releaseSavepoint(alice);
314        }
315        finally
316        {
317            s.close();
318            if ( ok )
319                log("issue 228 tests ok");
320        }
321    }
322
323    @Function(schema="javatest", effects=IMMUTABLE)
324    @SuppressWarnings("removal") // setAttribute
325    public static int testTransactionRecovery() throws SQLException {
326        Connection conn = DriverManager
327                .getConnection("jdbc:default:connection");
328
329        // Create an anonymous savepoint.
330        //
331        log("Attempting to set an anonymous savepoint");
332        Session currentSession = SessionManager.current();
333        currentSession.setAttribute(SP_CHECKSTATE, 0);
334        currentSession.addSavepointListener(spListener);
335
336        Statement stmt = conn.createStatement();
337        Savepoint sp = conn.setSavepoint();
338        nextState(currentSession, 1, 2);
339
340        try {
341            log("Attempting to execute a statement with a syntax error");
342            stmt.execute("THIS MUST BE A SYNTAX ERROR");
343        } catch (SQLException e) {
344            log("It failed. Let's try to recover "
345                    + "by rolling back to anonymous savepoint");
346            nextState(currentSession, 2, 3);
347            conn.rollback(sp);
348            nextState(currentSession, 1, 5);
349            log("Rolled back.");
350            log("Now let's try to execute a correct statement.");
351
352            currentSession.setAttribute(SP_CHECKSTATE, 0);
353            sp = conn.setSavepoint();
354            nextState(currentSession, 1, 2);
355            ResultSet rs = stmt.executeQuery("SELECT 'OK'");
356            while (rs.next()) {
357                log("Expected: OK; Retrieved: " + rs.getString(1));
358            }
359            rs.close();
360            stmt.close();
361            nextState(currentSession, 2, 3);
362            conn.releaseSavepoint(sp);
363            nextState(currentSession, 4, 5);
364            return 1;
365        } finally {
366            currentSession.removeSavepointListener(spListener);
367        }
368
369        // Should never get here
370        return -1;
371    }
372
373    @Function(schema="javatest", name="transferPeople")
374    public static int transferPeopleWithSalary(int salary) throws SQLException {
375        Connection conn = DriverManager
376                .getConnection("jdbc:default:connection");
377        PreparedStatement select = null;
378        PreparedStatement insert = null;
379        PreparedStatement delete = null;
380        ResultSet rs = null;
381
382        String stmt;
383        try {
384            stmt = "SELECT id, name, salary FROM employees1 WHERE salary > ?";
385            log(stmt);
386            select = conn.prepareStatement(stmt);
387
388            stmt = "INSERT INTO employees2(id, name, salary, transferDay, transferTime) VALUES (?, ?, ?, ?, ?)";
389            log(stmt);
390            insert = conn.prepareStatement(stmt);
391
392            stmt = "DELETE FROM employees1 WHERE id = ?";
393            log(stmt);
394            delete = conn.prepareStatement(stmt);
395
396            log("assigning parameter value " + salary);
397            select.setInt(1, salary);
398            log("Executing query");
399            rs = select.executeQuery();
400            int rowNo = 0;
401            log("Doing next");
402            while (rs.next()) {
403                log("Processing row " + ++rowNo);
404                int id = rs.getInt(1);
405                String name = rs.getString(2);
406                int empSal = rs.getInt(3);
407
408                insert.setInt(1, id);
409                insert.setString(2, name);
410                insert.setInt(3, empSal);
411                long now = System.currentTimeMillis();
412                insert.setDate(4, new Date(now));
413                insert.setTime(5, new Time(now));
414                int nRows = insert.executeUpdate();
415                log("Insert processed " + nRows + " rows");
416
417                delete.setInt(1, id);
418                nRows = delete.executeUpdate();
419                log("Delete processed " + nRows + " rows");
420                log("Doing next");
421            }
422            if (rowNo == 0)
423                log("No row found");
424            return rowNo;
425        } finally {
426            if (select != null)
427                select.close();
428            if (insert != null)
429                insert.close();
430            if (delete != null)
431                delete.close();
432            conn.close();
433        }
434    }
435
436    static TransactionListener s_tlstnr;
437
438    public static void registerTransactionListener() throws SQLException
439    {
440        Session currentSession = SessionManager.current();
441        if ( null == s_tlstnr )
442        {
443            s_tlstnr = new XactListener();
444            currentSession.addTransactionListener(s_tlstnr);
445        }
446        else
447        {
448            currentSession.removeTransactionListener(s_tlstnr);
449            s_tlstnr = null;
450        }
451    }
452
453    static class XactListener implements TransactionListener
454    {
455        public void onAbort(Session s)
456        {
457            System.err.println("aborting a transaction");
458        }
459        public void onCommit(Session s)
460        {
461            System.err.println("committing a transaction");
462        }
463        public void onPrepare(Session s)
464        {
465            System.err.println("preparing a transaction");
466        }
467    }
468}