import java.io.*;
import java.sql.*;
import java.text.NumberFormat;

import org.postgresql.PGConnection;
import org.postgresql.largeobject.*;

/** I am not planning to docuemnt this class. It is just a fast test
 * program to demonstrate how slow postgres is.
 * 
 * The command to run this class is:  
 *       java -Xmx256m -classpath ".;pg73jdbc3.jar" BLOBTest
 * 
 * 1024m is just enough to insert 16m to a bytea field
 * 
 * required tables
 * 
 * CREATE TABLE public.blob_bytea (
 *    blob_name varchar(255),
 *    blob_data bytea
 * );
 * 
 * CREATE TABLE public.blob_lo (
 *    blob_name varchar(255),
 *    blob_data oid
 * );
 * 
 * @author Nico Klasens (Finalist IT Group)
 *
 */
public class BLOBTest {

   private static String host = "localhost";
   private static String db = "blobtest";
   private static String user = "geusje";
   private static String passwd = "geusje";
   private static String port = "5432";

   public static void main( String args[] ) {
      if (args.length == 0) {
         System.out.println("Database settings could be passed to this program with arguments.\n"
         + "          java BLOBTest host database user password port");
      }
      if (args.length >= 1 && args[0] != null) {
         host = args[0];
      }
      if (args.length >= 2 && args[1] != null) {
         db = args[1];
      }
      if (args.length >= 3 && args[2] != null) {
         user = args[2];
      }
      if (args.length >= 4 && args[3] != null) {
         passwd = args[3];
      }
      if (args.length >= 5 && args[4] != null) {
         port = args[4];
      }
      System.out.println("database host = " + host);
      System.out.println("database port = " + port);
      System.out.println("database db   = " + db);
      System.out.println("database user = " + user);
      System.out.println("database pwd  = " + passwd);

      Runtime rt = Runtime.getRuntime();
      
       try {
           BLOBTest test = new BLOBTest();
           long time = 0;

           test.testData("bytea","bin0",256);
           test.testData("LO/oid","bin0",256);

           test.testData("bytea","bin1",1024);
           test.testData("LO/oid","bin1",1024);

           test.testData("bytea","bin512",512000);
           test.testData("LO/oid","bin512",512000);

           test.testData("bytea","bin1024",1024000);
           test.testData("LO/oid","bin1024",1024000);

//           test.testData("bytea","bin2048",2048000);
//           test.testData("LO/oid","bin2048",2048000);

           test.testData("bytea","bin4096",4096000);
           test.testData("LO/oid","bin4096",4096000);

//           test.testData("bytea","bin16384",16384000);
//           test.testData("LO/oid","bin16384",16384000);
//
//           test.testData("bytea","bin32768",32768000);
//           test.testData("LO/oid","bin32768",32768000);
//
//           test.testData("bytea","bin65535",65535000);
//           test.testData("LO/oid","bin65535",65535000);

       } catch ( Throwable e ) {
           System.err.println(e);
           e.printStackTrace();
          System.out.println("Total memory = " + NumberFormat.getInstance().format(rt.totalMemory()));
          System.out.println("Free memory  = " + NumberFormat.getInstance().format(rt.freeMemory()));      
       }
   }


    public BLOBTest()
        throws Exception {
            
        Class.forName("org.postgresql.Driver");

        createDummyDataFile("bin0",256);
        createDummyDataFile("bin1",1024);
        createDummyDataFile("bin512",512000);
        createDummyDataFile("bin1024",1024000);
//        createDummyDataFile("bin2048",2048000);
        createDummyDataFile("bin4096",4096000);
//        createDummyDataFile("bin16384",16384000);
//        createDummyDataFile("bin32768",32768000);
//        createDummyDataFile("bin65535",65535000);
    }

   private Connection getConnection() throws SQLException {
      return DriverManager.getConnection(
          "jdbc:postgresql://" + host + ":" + port + "/" + db,
          user, passwd);
   }

    private void createDummyDataFile( String fileName, int size )
        throws Exception {

        byte data[] = new byte[size];
        FileOutputStream fos = new FileOutputStream(fileName);
        fos.write(data);
        fos.close();
    }
        
    private void testData(String method, String fileName, int size )
        throws Exception {

        long time;

        // Garbage collect to clean up any garbage objects
        System.gc();

        if ( method.equals("bytea") ) {
            time = storeBlobAsBytea(fileName);
            System.err.println("["+method+"] storing    "+(size/1024)+"k of data -> "+time+"ms");
            time = retrieveBlobAsBytea(fileName);
            System.err.println("["+method+"] retrieving "+(size/1024)+"k of data -> "+time+"ms");
        } else {
            time = storeBlobAsLO(fileName);
            System.err.println("["+method+"] storing    "+(size/1024)+"k of data -> "+time+"ms");
            time = retrieveBlobAsLO(fileName);
            System.err.println("["+method+"] retrieving "+(size/1024)+"k of data -> "+time+"ms");
        }
    }

    private long storeBlobAsBytea(String fileName )
        throws Exception {
    
        long t1, t2;

        Connection conn = getConnection();

        t1 = System.currentTimeMillis();
        File file = new File(fileName);
        BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));
        PreparedStatement ps = conn.prepareStatement("INSERT INTO blob_bytea VALUES (?, ?)");
        ps.setString(1, file.getName());
        ps.setBinaryStream(2, bis, (int)file.length());
        ps.executeUpdate();
        ps.close();
        bis.close();
        t2 = System.currentTimeMillis();
        conn.close();
        return (t2 - t1);
    }
    
    private long retrieveBlobAsBytea(String fileName )
        throws Exception {
        
        long t1, t2;

        Connection conn = getConnection();
    
        t1 = System.currentTimeMillis();
        PreparedStatement ps = conn.prepareStatement(
            "SELECT blob_data FROM blob_bytea WHERE blob_name=?");
        ps.setString(1, fileName);
        ResultSet rs = ps.executeQuery();
        if ( rs.next() ) {
            byte[] imgBytes = rs.getBytes(1);
            // use the stream in some way here
        }
        rs.close();
        ps.close();
        t2 = System.currentTimeMillis();
        conn.close();
        return (t2 - t1);
    }
    
    private long storeBlobAsLO(String fileName )
        throws Exception {
    
        long t1, t2;
        Connection conn = getConnection();

        boolean oldState = conn.getAutoCommit();
        try {

            t1 = System.currentTimeMillis();

            // All LargeObject API calls must be within a transaction
            conn.setAutoCommit(false);

            // Get the Large Object Manager to perform operations with
            LargeObjectManager lobj = ((PGConnection)conn).getLargeObjectAPI();

            // create a new large object
            int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);

            // open the large object for write
            LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);

            // Now open the file
            File file = new File(fileName);
            BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));

            // copy the data from the file to the large object
            byte buf[] = new byte[2048];
            int s, tl = 0;
            while ((s = bis.read(buf, 0, 2048)) > 0) {
                obj.write(buf, 0, s);
                tl += s;
            }
            // Close the large object
            obj.close();
            // Now insert the row
            PreparedStatement ps = conn.prepareStatement("INSERT INTO blob_lo VALUES (?, ?)");
            ps.setString(1, file.getName());
            ps.setInt(2, oid);
            ps.executeUpdate();
            ps.close();
            bis.close();

            t2 = System.currentTimeMillis();
            return (t2 - t1);

        } finally {
            conn.setAutoCommit(oldState);
           conn.close();
        }
    }
    
    private long retrieveBlobAsLO(String fileName )
        throws Exception {
    
        long t1, t2;
        
        Connection conn = getConnection();
        
        boolean oldState = conn.getAutoCommit();

        try {

            t1 = System.currentTimeMillis();

            // All LargeObject API calls must be within a transaction
            conn.setAutoCommit(false);

            // Get the Large Object Manager to perform operations with
            LargeObjectManager lobj = ((PGConnection)conn).getLargeObjectAPI();
            PreparedStatement ps = conn.prepareStatement("SELECT blob_data FROM blob_lo WHERE blob_name=?");
            ps.setString(1, fileName);
            ResultSet rs = ps.executeQuery();
        
            while(rs.next()) {
                // open the large object for reading
                int oid = rs.getInt(1);
                LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
                // read the data
                byte buf[] = new byte[obj.size()];
                obj.read(buf, 0, obj.size());
                // do something with the data read here
                // Close the object
                obj.close();
            }
            rs.close();
            ps.close();

            t2 = System.currentTimeMillis();
            return (t2 - t1);
        } finally {
            conn.setAutoCommit(oldState);
            conn.close();
        }
    }
}