Serialize and object-oriented features - Mailing list pgsql-jdbc

I've been trying to use some object oriented features from java, like:

CREATE TABLE myurl (
    -- this table gets created automatically
    -- org.postgresql.util.Serialize (after some hacking)
    url    TEXT
);

CREATE TABLE website (
    -- created manually
    owner    TEXT,
    url    myurl
)

Then, I've tried SELECTs like:

setgetobject=# select url.url from website;
ERROR:  Relation 'url' does not exist

setgetobject=# select website.url.url from website;
ERROR:  fmgr_info: function 24434: cache lookup failed

24434 is the oid/myurl-type row id of an entry in the myurl table.

Can these object capabilities work?

I'm interested in this because I've been playing around with the JDBC
Serialize/PreparedStatment classes.  I've been hacking Serialize and
PreparedStatement.java in the distribution and got it to store java classes
in the database, but with a little more object support in the database, it
would work more smoothly. This all has to do a little bit with J2EE stuff.
I'd like PostgreSQL to work with J2EE enterprise beans.  The container
managed stuff probably needs the database to be more object oriented.

The following is a patch to the jdbc directory in the 7.1.3 dist that I've
played with. After that, there is a small test program.  I'd like to know if
anyone else has worked on these capabilities for postgres and what is
available.

Apply with with:
    cd postgresql-7.1.3/src/interfaces/jdbc
    patch -p1 < thispatchfile

---------------- Patch follows this line -------------------
Only in jdbc: build
Only in jdbc: jars
Only in jdbc/org/postgresql: Driver.java
diff -cr jdbc-orig/org/postgresql/jdbc2/PreparedStatement.java
jdbc/org/postgresql/jdbc2/PreparedStatement.java
*** jdbc-orig/org/postgresql/jdbc2/PreparedStatement.java    Fri Feb 16 11:45:00
2001
--- jdbc/org/postgresql/jdbc2/PreparedStatement.java    Tue Aug 21 10:45:02 2001
***************
*** 223,228 ****
--- 223,250 ----
      }

      /**
+      * Set a parameter to a Java long value.  The driver converts this to
+      * a SQL BIGINT value when it sends it to the database.
+      *
+      * @param parameterIndex the first parameter is 1...
+      * @param x the parameter value
+      * @exception SQLException if a database access error occurs
+      */
+     public void setSerializedObject(int parameterIndex, long x, String
classname) throws SQLException
+     {
+         // When setting oid reference to a tablerow type in an sql command, it
must be explicitly cast to ::<rowtype>
+         // PostgreSQLize the java class name
+         String tablename = classname.toLowerCase();
+         if(tablename.indexOf("_")>-1)
+             throw new PSQLException("postgresql.serial.underscore");
+         if(tablename.length()>32)
+             throw new PSQLException("postgresql.serial.namelength",tablename,new
Integer(tablename.length()));
+
+         set(parameterIndex, Long.toString(x) + "::" + tablename);
+     }
+
+
+     /**
       * Set a parameter to a Java float value.  The driver converts this
       * to a SQL FLOAT value when it sends it to the database.
       *
***************
*** 593,599 ****
          else if (x instanceof PGobject)
              setString(parameterIndex, ((PGobject)x).getValue());
          else
!             setLong(parameterIndex, connection.putObject(x));
      }

      /**
--- 615,622 ----
          else if (x instanceof PGobject)
              setString(parameterIndex, ((PGobject)x).getValue());
          else
!             setSerializedObject(parameterIndex, connection.putObject(x),
x.getClass().getName() );
!             //setLong(parameterIndex, connection.putObject(x));
      }

      /**
diff -cr jdbc-orig/org/postgresql/util/Serialize.java
jdbc/org/postgresql/util/Serialize.java
*** jdbc-orig/org/postgresql/util/Serialize.java    Mon Oct  9 12:48:19 2000
--- jdbc/org/postgresql/util/Serialize.java    Tue Aug 21 10:45:02 2001
***************
*** 41,64 ****
    {
      try {
        conn = c;
!       tableName = type.toLowerCase();
        className = toClassName(type);
        ourClass = Class.forName(className);
      } catch(ClassNotFoundException cnfe) {
        throw new PSQLException("postgresql.serial.noclass",type);
      }

      // Second check, the type must be a table
      boolean status = false;
!     ResultSet rs = conn.ExecSQL("select typname from pg_type,pg_class where
typname=relname and typname='"+type+"'");
      if(rs!=null) {
        if(rs.next())
!     status=true;
        rs.close();
      }
      // This should never occur, as org.postgresql has it's own internal
checks
!     if(!status)
        throw new PSQLException("postgresql.serial.table",type);

      // Finally cache the fields within the table
    }
--- 41,69 ----
    {
      try {
        conn = c;
!         DriverManager.println("Serialize: initializing instance for type: " +
type);
!       tableName = toPostgreSQL(type);
        className = toClassName(type);
        ourClass = Class.forName(className);
      } catch(ClassNotFoundException cnfe) {
+         DriverManager.println("Serialize: " + className + " java class not
found");
        throw new PSQLException("postgresql.serial.noclass",type);
      }

      // Second check, the type must be a table
      boolean status = false;
!     ResultSet rs = conn.ExecSQL("select typname from pg_type,pg_class where
typname=relname and typname='"+tableName+"'");
      if(rs!=null) {
        if(rs.next())
!         status=true;
!         DriverManager.println("Serialize: " + tableName + " table found");
        rs.close();
      }
      // This should never occur, as org.postgresql has it's own internal
checks
!     if(!status) {
!         DriverManager.println("Serialize: " + tableName + " table not found");
        throw new PSQLException("postgresql.serial.table",type);
+       }

      // Finally cache the fields within the table
    }
***************
*** 72,78 ****
--- 77,85 ----
    public Object fetch(int oid) throws SQLException
    {
      try {
+       DriverManager.println("Serialize.fetch: " + "attempting to instantiate
object of type: " + ourClass.getName() );
        Object obj = ourClass.newInstance();
+       DriverManager.println("Serialize.fetch: " + "instantiated object of
type: " + ourClass.getName() );

        // NB: we use java.lang.reflect here to prevent confusion with
        // the org.postgresql.Field
***************
*** 96,102 ****
        sb.append(" where oid=");
        sb.append(oid);

!       DriverManager.println("store: "+sb.toString());
        ResultSet rs = conn.ExecSQL(sb.toString());
        if(rs!=null) {
      if(rs.next()) {
--- 103,109 ----
        sb.append(" where oid=");
        sb.append(oid);

!       DriverManager.println("Serialize.fetch: " + sb.toString());
        ResultSet rs = conn.ExecSQL(sb.toString());
        if(rs!=null) {
      if(rs.next()) {
***************
*** 133,211 ****
     * @return oid of stored object
     * @exception SQLException on error
     */
!   public int store(Object o) throws SQLException
!   {
!     try {
!       // NB: we use java.lang.reflect here to prevent confusion with
!       // the org.postgresql.Field
!       java.lang.reflect.Field f[] = ourClass.getDeclaredFields();
!       boolean hasOID=false;
!       int oidFIELD=-1;
!       boolean update=false;
!
!       // Find out if we have an oid value
!       for(int i=0;i<f.length;i++) {
!     String n = f[i].getName();
!     if(n.equals("oid")) {
!       hasOID=true;
!       oidFIELD=i;
!
!       // We are an update if oid != 0
!       update = f[i].getInt(o)>0;
!     }
!       }
!
!       StringBuffer sb = new StringBuffer(update?"update "+tableName+"
set":"insert into "+tableName+" values ");
!       char sep=update?' ':'(';
!       for(int i=0;i<f.length;i++) {
!     String n = f[i].getName();
!     sb.append(sep);
!     sb.append(n);
!     sep=',';
!     if(update) {
!       sb.append('=');
!       if(f[i].getType().getName().equals("java.lang.String")) {
!         sb.append('\'');
!         sb.append(f[i].get(o).toString());
!         sb.append('\'');
!       } else
!         sb.append(f[i].get(o).toString());
!     }
!       }
!
!       if(!update) {
!     sb.append(") values ");
!     sep='(';
!     for(int i=0;i<f.length;i++) {
!       String n = f[i].getName();
!       if(f[i].getType().getName().equals("java.lang.String")) {
!         sb.append('\'');
!         sb.append(f[i].get(o).toString());
!         sb.append('\'');
!       } else
!         sb.append(f[i].get(o).toString());
      }
-     sb.append(')');
-       }
-
-       DriverManager.println("store: "+sb.toString());
-       ResultSet rs = conn.ExecSQL(sb.toString());
-       if(rs!=null) {
-     rs.close();
-       }
-
-       // fetch the OID for returning
-       int oid=0;
-       if(hasOID) {
-     // set the oid in the object
-     f[oidFIELD].setInt(o,oid);
-       }
-       return oid;
-
-     } catch(IllegalAccessException iae) {
-       throw new SQLException(iae.toString());
-     }
-   }

    /**
     * This method is not used by the driver, but it creates a table, given
--- 140,230 ----
     * @return oid of stored object
     * @exception SQLException on error
     */
!     public int store(Object o) throws SQLException
!     {
!         try {
!             // NB: we use java.lang.reflect here to prevent confusion with
!             // the org.postgresql.Field
!             java.lang.reflect.Field f[] = ourClass.getDeclaredFields();
!             boolean hasOID=false;
!             int oidFIELD=-1;
!             boolean update=false;
!
!               // Find out if we have an oid value
!             for(int i=0;i<f.length;i++) {
!                 String n = f[i].getName();
!                 if(n.equals("oid")) {
!                     hasOID=true;
!                     oidFIELD=i;
!                     // We are an update if oid != 0
!                     update = f[i].getInt(o)>0;
!                 }
!             }
!
!             StringBuffer sb = new StringBuffer(update? "update "+tableName+"
set":"insert into "+tableName+" ");
!             char sep = update?' ':'(';
!             for(int i=0;i<f.length;i++) {
!                 String n = f[i].getName();
!                 if( n.equals("oid") ) continue;
!                 else {
!                     sb.append(sep);
!                     sep=',';
!                 }
!                 sb.append(n);
!                 if(update) {
!                     sb.append('=');
!                     if(f[i].getType().getName().equals("java.lang.String")) {
!                         sb.append('\'');
!                         sb.append(f[i].get(o).toString());
!                         sb.append('\'');
!                     } else sb.append(f[i].get(o).toString());
!                 }
!             }
!
!             if(update) sb.append(" where oid = " + f[oidFIELD].getInt(o) );
!
!             if(!update) {
!                 sb.append(") values ");
!                 sep='(';
!                 for(int i=0;i<f.length;i++) {
!                     String n = f[i].getName();
!                     if( n.equals("oid") ) continue;
!                     else {
!                         sb.append(sep);
!                         sep=',';
!                     }
!                     if(f[i].getType().getName().equals("java.lang.String")) {
!                         sb.append('\'');
!                         sb.append(f[i].get(o).toString());
!                         sb.append('\'');
!                     } else
!                     sb.append(f[i].get(o).toString());
!                 }
!                 sb.append(')');
!             }
!
!             DriverManager.println("Serialize.store: " + sb.toString());
!             ResultSet rs = conn.ExecSQL(sb.toString());
!
!               // fetch the OID for returning
!             if(update) {
!                 // object has oid already, so return it
!                 if(rs!=null) rs.close();
!                 return f[oidFIELD].getInt(o);
!             } else {
!                 // new record inserted has new oid; rs should be not null
!                 int newOID = ((org.postgresql.ResultSet)rs).getInsertedOID();
!                 rs.close();
!                 // update the java object's oid field if it has the oid field
!                 if(hasOID) f[oidFIELD].setInt(o,newOID);
!                 // new object stored, return newly inserted oid
!                 return newOID;
!             }
!
!         } catch(IllegalAccessException iae) {
!             throw new SQLException(iae.toString());
!         }
      }

    /**
     * This method is not used by the driver, but it creates a table, given
***************
*** 238,244 ****

      ResultSet rs = con.ExecSQL("select relname from pg_class where relname
= '"+tableName+"'");
      if(!rs.next()) {
!       DriverManager.println("found "+rs.getString(1));
        // No entries returned, so the table doesn't exist

        StringBuffer sb = new StringBuffer("create table ");
--- 257,263 ----

      ResultSet rs = con.ExecSQL("select relname from pg_class where relname
= '"+tableName+"'");
      if(!rs.next()) {
!       DriverManager.println("Serialize.create: table " + tableName + "not
found, creating");
        // No entries returned, so the table doesn't exist

        StringBuffer sb = new StringBuffer("create table ");
***************
*** 335,341 ****
     */
    public static String toClassName(String name) throws SQLException
    {
!     name = name.toLowerCase();
      return name.replace('_','.');
    }

--- 354,360 ----
     */
    public static String toClassName(String name) throws SQLException
    {
!     //name = name.toLowerCase();
      return name.replace('_','.');
    }


------------- end of patch ----------------------

---------------
Test prog:
------------------------------------------------------------------

import java.util.*;
import java.net.*;
import java.sql.*;
import java.io.*;
import org.postgresql.util.*;
//import org.postgresql.*;

/*
    Run this as "java SetGetObject init"
    then, create a table:
        CREATE TABLE website (owner text, url myurl);

    Then run it again.
*/

public class SetGetObject
{
    public static void main( String[] args ) {
        try {
            Class.forName("org.postgresql.Driver");
        } catch( ClassNotFoundException e ) {
            System.out.println("ClassNotFoundException loading pg driver: " +
e.getMessage() );
            return;
        }

        try {
            myurl url = new myurl("http://www.comptechnews.com/");
            System.out.println("URL to be stored: ");
            printURL( url );

            DriverManager.setLogWriter( new PrintWriter(System.out) );
            Connection db =
DriverManager.getConnection("jdbc:postgresql:setgetobject", "reaster",
"reaster");
            System.out.println("Loaded PostgreSQL JDBC Driver.");

            Serialize.create( (org.postgresql.Connection) db, myurl.class );
            System.out.println("Serialize.create() called Ok.");
            if( args.length > 0 ) return;

            PreparedStatement ps = db.prepareStatement("INSERT INTO website (owner,
url) VALUES ( ?, ? )");
            ps.setString( 1, "Robert Easter" );
            ps.setObject( 2, url );
            System.out.println( ps.toString() );
            System.out.println("URLs INSERTed: " + ps.executeUpdate() );

            BufferedReader in = new BufferedReader( new InputStreamReader(System.in) );
            in.readLine();

            PreparedStatement ps2 = db.prepareStatement("SELECT * FROM website WHERE
url = ?");
            ps2.setObject( 1, url );
            System.out.println( ps2.toString() );
            ResultSet rs = ps2.executeQuery();
            myurl gotURL;
            if( rs.next() ) {
                gotURL = (myurl) rs.getObject("url");
                System.out.println("URL retrieved: ");
                printURL( gotURL );
            }
            else System.out.println("URL was not retrieved.");

            rs.close();
            ps2.close();
            ps.close();
            db.close();

           } catch( SQLException sqle ) {
            System.out.println("SQLException: " + sqle.getMessage() );
            return;

        } catch( IOException ioe ) {
            System.out.println("IOException: " + ioe.getMessage() );
            return;
        }

    }

    public static void printURL( myurl u ) {
        try {
            URL t = new URL( u.url );
            System.out.println("URL: " + t.toString() );
            System.out.println("\tProto: " + t.getProtocol() );
            System.out.println("\tHost: " + t.getHost() );
            System.out.println("\tPath: " + t.getPath() );
            System.out.println("\toid: " + u.oid );
        } catch( MalformedURLException mfe ) {}

    }
}


----------------------------------------------------------------

import java.net.*;
import java.io.*;

/*
    Requirements for ojbects to be serialized into postgres:

    1) Must have a no-arg constructor
    2) Must have class name that is all lowercase since database
        table names are case-insensitive but stored as lowercase.
    3) Should probably only contain only simple fields like String and the
        java primitive types since the recursive stuff might not work.
    4) Must implement the Serializable interface even though I
        do not know how it is used here.
    5) Should have a "public int oid;" field that is initialized
        to 0.  The oid field should be for read-only and is
        used for identifying the row to be updated during updates.
        If the oid field is absent, then all calls of
        setObject() on it in prepared statements cause the
        insertion of the object again and again.
*/

public class myurl implements Serializable {
    public int oid;
    public String url;

    // this no-arg constructor is required in order for
    // Class.newInstance() to work in Serialize.getObject()
    public myurl() { }

    public myurl( String url ) {
        this.url = url; oid = 0;
    }
}