package example;

import java.io.*;
import java.sql.*;

// rare in user code, but we use the LargeObject API in this test
import org.postgresql.largeobject.*;

/*
 * This example tests the thread safety of the driver.
 *
 * It does this by performing several queries, in different threads. Each
 * thread has it's own Statement object, which is (in my understanding of the
 * jdbc specification) the minimum requirement.
 *
 */

public class threadsafe
{
    Connection db; // The connection to the database
    Statement st; // Our statement to run queries with

    public threadsafe(String args[]) throws ClassNotFoundException, FileNotFoundException, IOException, SQLException
    {
        String url = args[0];
        String usr = args[1];
        String pwd = args[2];

        // Load the driver
        Class.forName("org.postgresql.Driver");

        // Connect to database
        System.out.println("Connecting to Database URL = " + url);
        db = DriverManager.getConnection(url, usr, pwd);

        System.out.println("Connected...Now creating a statement");
        st = db.createStatement();

        // Clean up the database (in case we failed earlier) then initialise
        cleanup();

        // Because we use LargeObjects, we must use Transactions
        db.setAutoCommit(false);

        // Now run tests using JDBC methods, then LargeObjects
        doexample();

        // Clean up the database
        cleanup();

        // Finally close the database
        System.out.println("Now closing the connection");
        st.close();
        db.close();
    }

    /*
     * This drops the table (if it existed). No errors are reported.
     */
    public void cleanup()
    {
        try
        {
            st.executeUpdate("drop table basic1");
        }
        catch (Exception ex)
        {
            // We ignore any errors here
        }

        try
        {
            st.executeUpdate("drop table basic2");
        }
        catch (Exception ex)
        {
            // We ignore any errors here
        }
    }

    /*
     * This performs the example
     */
    public void doexample() throws SQLException
    {
        System.out.println("\nThis test runs three Threads. Two simply insert data into a table, then\nthey perform a query. While they are running, a third thread is running,\nand it load data into, then reads from a Large Object.\n\nIf alls well, this should run without any errors. If so, we are Thread Safe.\nWhy test JDBC & LargeObject's? Because both will run over the network\nconnection, and if locking on the stream isn't done correctly, the backend\nwill get pretty confused!\n");

        thread3 thread3 = null;

        try
        {

            // create the two threads
            Thread thread0 = Thread.currentThread();
            Thread thread1 = new thread1(db);
            Thread thread2 = new thread2(db);
            thread3 = new thread3(db);

            // now run, and wait for them
            thread1.start();
            thread2.start();
            thread3.start();

            // ok, I know this is bad, but it does the trick here as our main thread
            // will yield as long as either of the children are still running
            System.out.println("Waiting for threads to run");
            while (thread1.isAlive() || thread2.isAlive() || thread3.isAlive())
                Thread.yield();
        }
        finally
        {
            // clean up after thread3 (the finally ensures this is run even
            // if an exception is thrown inside the try { } construct)
            if (thread3 != null)
                thread3.cleanup();
        }

        System.out.println("No Exceptions have been thrown. This is a good omen, as it means that we are\npretty much thread safe as we can get.");
    }

    // This is the first thread. It's the same as the basic test
    class thread1 extends Thread
    {
        Connection c;
        Statement st;

        public thread1(Connection c) throws SQLException
        {
            this.c = c;
            st = c.createStatement();
        }

        public void run()
        {
            try
            {
                System.out.println("Thread 1 running...");

                // First we need a table to store data in
                st.executeUpdate("create table basic1 (a int2, b int2)");

                // Now insert some data, using the Statement
                st.executeUpdate("insert into basic1 values (1,1)");
                st.executeUpdate("insert into basic1 values (2,1)");
                st.executeUpdate("insert into basic1 values (3,1)");

                // For large inserts, a PreparedStatement is more efficient, because it
                // supports the idea of precompiling the SQL statement, and to store
                // directly, a Java object into any column. PostgreSQL doesnt support
                // precompiling, but does support setting a column to the value of a
                // Java object (like Date, String, etc).
                //
                // Also, this is the only way of writing dates in a datestyle independent
                // manner. (DateStyles are PostgreSQL's way of handling different methods
                // of representing dates in the Date data type.)
                PreparedStatement ps = db.prepareStatement("insert into basic1 values (?,?)");
                for (int i = 2;i < 2000;i++)
                {
                    ps.setInt(1, 4);  // "column a" = 5
                    ps.setInt(2, i);  // "column b" = i
                    ps.executeUpdate(); // executeUpdate because insert returns no data
                    //   c.commit();
                    if ((i % 50) == 0)
                        DriverManager.println("Thread 1 done " + i + " inserts");
                }
                ps.close();   // Always close when we are done with it

                // Finally perform a query on the table
                DriverManager.println("Thread 1 performing a query");
                ResultSet rs = st.executeQuery("select a, b from basic1");
                int cnt = 0;
                if (rs != null)
                {
                    // Now we run through the result set, printing out the result.
                    // Note, we must call .next() before attempting to read any results
                    while (rs.next())
                    {
                        int a = rs.getInt("a"); // This shows how to get the value by name
                        int b = rs.getInt(2); // This shows how to get the value by column
                        //System.out.println("  a="+a+" b="+b);
                        cnt++;
                    }
                    rs.close(); // again, you must close the result when done
                }
                DriverManager.println("Thread 1 read " + cnt + " rows");

                // The last thing to do is to drop the table. This is done in the
                // cleanup() method.
                System.out.println("Thread 1 finished");
            }
            catch (SQLException se)
            {
                System.err.println("Thread 1: " + se.toString());
                se.printStackTrace();
                System.exit(1);
            }
        }
    }

    // This is the second thread. It's the similar to the basic test, and thread1
    // except it works on another table.
    class thread2 extends Thread
    {
        Connection c;
        Statement st;

        public thread2(Connection c) throws SQLException
        {
            this.c = c;
            st = c.createStatement();
        }

        public void run()
        {
            try
            {
                System.out.println("Thread 2 running...");

                // First we need a table to store data in
                st.executeUpdate("create table basic2 (a int2, b int2)");

                // For large inserts, a PreparedStatement is more efficient, because it
                // supports the idea of precompiling the SQL statement, and to store
                // directly, a Java object into any column. PostgreSQL doesnt support
                // precompiling, but does support setting a column to the value of a
                // Java object (like Date, String, etc).
                //
                // Also, this is the only way of writing dates in a datestyle independent
                // manner. (DateStyles are PostgreSQL's way of handling different methods
                // of representing dates in the Date data type.)
                PreparedStatement ps = db.prepareStatement("insert into basic2 values (?,?)");
                for (int i = 2;i < 2000;i++)
                {
                    ps.setInt(1, 4);  // "column a" = 5
                    ps.setInt(2, i);  // "column b" = i
                    ps.executeUpdate(); // executeUpdate because insert returns no data
                    //   c.commit();
                    if ((i % 50) == 0)
                        DriverManager.println("Thread 2 done " + i + " inserts");
                }
                ps.close();   // Always close when we are done with it

                // Finally perform a query on the table
                DriverManager.println("Thread 2 performing a query");
                ResultSet rs = st.executeQuery("select * from basic2 where b>1");
                int cnt = 0;
                if (rs != null)
                {
                    // First find out the column numbers.
                    //
                    // It's best to do this here, as calling the methods with the column
                    // numbers actually performs this call each time they are called. This
                    // really speeds things up on large queries.
                    //
                    int col_a = rs.findColumn("a");
                    int col_b = rs.findColumn("b");

                    // Now we run through the result set, printing out the result.
                    // Again, we must call .next() before attempting to read any results
                    while (rs.next())
                    {
                        int a = rs.getInt(col_a); // This shows how to get the value by name
                        int b = rs.getInt(col_b); // This shows how to get the value by column
                        //System.out.println("  a="+a+" b="+b);
                        cnt++;
                    }
                    rs.close(); // again, you must close the result when done
                }
                DriverManager.println("Thread 2 read " + cnt + " rows");

                // The last thing to do is to drop the table. This is done in the
                // cleanup() method.
                System.out.println("Thread 2 finished");
            }
            catch (SQLException se)
            {
                System.err.println("Thread 2: " + se.toString());
                se.printStackTrace();
                System.exit(1);
            }
        }
    }

    // This is the third thread. It loads, then reads from a LargeObject, using
    // our LargeObject api.
    //
    // The purpose of this is to test that FastPath will work in between normal
    // JDBC queries.
    class thread3 extends Thread
    {
        Connection c;
        Statement st;
        LargeObjectManager lom;
        LargeObject lo;
        int oid;

        public thread3(Connection c) throws SQLException
        {
            this.c = c;
            //st = c.createStatement();

            // create a blob
            lom = ((org.postgresql.PGConnection)c).getLargeObjectAPI();
            oid = lom.create();
            System.out.println("Thread 3 has created a blob of oid " + oid);
        }

        public void run()
        {
            try
            {
                System.out.println("Thread 3 running...");

                DriverManager.println("Thread 3: Loading data into blob " + oid);
                lo = lom.open(oid);
                FileInputStream fis = new FileInputStream("example/threadsafe.java");
                // keep the buffer size small, to allow the other thread a chance
                byte buf[] = new byte[128];
                int rc, bc = 1, bs = 0;
                while ((rc = fis.read(buf)) > 0)
                {
                    DriverManager.println("Thread 3 read block " + bc + " " + bs + " bytes");
                    lo.write(buf, 0, rc);
                    bc++;
                    bs += rc;
                }
                lo.close();
                fis.close();

                DriverManager.println("Thread 3: Reading blob " + oid);
                lo = lom.open(oid);
                bc = 0;
                while (buf.length > 0)
                {
                    buf = lo.read(buf.length);
                    if (buf.length > 0)
                    {
                        String s = new String(buf);
                        bc++;
                        DriverManager.println("Thread 3 block " + bc);
                        DriverManager.println("Block " + bc + " got " + s);
                    }
                }
                lo.close();

                System.out.println("Thread 3 finished");
            }
            catch (Exception se)
            {
                System.err.println("Thread 3: " + se.toString());
                se.printStackTrace();
                System.exit(1);
            }
        }

        public void cleanup() throws SQLException
        {
            if (lom != null && oid != 0)
            {
                System.out.println("Thread 3: Removing blob oid=" + oid);
                lom.delete(oid);
            }
        }
    }

    /*
     * Display some instructions on how to run the example
     */
    public static void instructions()
    {
        System.out.println("\nThis tests the thread safety of the driver.\n\nThis is done in two parts, the first with standard JDBC calls, and the\nsecond mixing FastPath and LargeObject calls with queries.\n");
        System.out.println("Useage:\n java example.threadsafe jdbc:postgresql:database user password [debug]\n\nThe debug field can be anything. It's presence will enable DriverManager's\ndebug trace. Unless you want to see screens of items, don't put anything in\nhere.");
        System.exit(1);
    }

    /*
     * This little lot starts the test
     */
    public static void main(String args[])
    {
        System.out.println("PostgreSQL Thread Safety test v6.4 rev 1\n");

        if (args.length < 3)
            instructions();

        // This line outputs debug information to stderr. To enable this, simply
        // add an extra parameter to the command line
        if (args.length > 3)
            DriverManager.setLogStream(System.err);

        // Now run the tests
        try
        {
            threadsafe test = new threadsafe(args);
        }
        catch (Exception ex)
        {
            System.err.println("Exception caught.\n" + ex);
            ex.printStackTrace();
        }
    }
}