From f478692552c58009c733e4b0434c17fa8a551224 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sun, 26 Aug 2001 00:54:42 +0000 Subject: [PATCH] The attached file: SerializePatch2.tgz, contains a patch for org.postgresql.util.Serialize and org.postgresql.jdbc2.PreparedStatement that fixes the ability to "serialize" a simple java class into a postgres table. The current cvs seems completely broken in this support, so the patch puts it into working condition, granted that there are many limitations with serializing java classes into Postgres. The code to do serialize appears to have been in the driver since Postgres 6.4, according to some comments in the source. My code is not adding any totally new ability to the driver, rather just fixing what is there so that it actually is usable. I do not think that it should affect any existing functions of the driver that people regularly depend on. The code is activated if you use jdbc2.PreparedStatement and try to setObject some java class type that is unrecognized, like not String or not some other primitive type. This will cause a sequence of function calls that results in an instance of Serialize being instantiated for the class type passed. The Serialize constructor will query pg_class to see if it can find an existing table that matches the name of the java class. If found, it will continue and try to use the table to store the object, otherwise an SQL exception is thrown and no harm is done. Serialize.create() has to be used to setup the table for a java class before anything can really happen with this code other than an SQLException (unless by some freak chance a table exists that it thinks it can use). I saw a difference in Serialize.java between 7.1.3 and 7.2devel that I didn't notice before, so I had to redo my changes from the 7.2devel version (why I had to resend this patch now). I was missing the fixString stuff, which is nice and is imporant to ensure the inserts will not fail due to embedded single quote or unescaped backslashes. I changed that fixString function in Serialize just a little since there is no need to muddle with escaping newlines: only escaping single quote and literal backslashes is needed. Postgres appears to insert newlines within strings without trouble. --- .../postgresql/jdbc2/PreparedStatement.java | 27 +- .../jdbc/org/postgresql/util/Serialize.java | 585 ++++++++++-------- 2 files changed, 360 insertions(+), 252 deletions(-) diff --git a/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java b/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java index 8532fe777f..aabb492309 100644 --- a/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java +++ b/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java @@ -613,7 +613,8 @@ public class PreparedStatement extends Statement implements java.sql.PreparedSta else if (x instanceof PGobject) setString(parameterIndex, ((PGobject)x).getValue()); else - setLong(parameterIndex, connection.putObject(x)); + // Try to store java object in database + setSerialize(parameterIndex, connection.putObject(x), x.getClass().getName() ); } /** @@ -674,6 +675,29 @@ public class PreparedStatement extends Statement implements java.sql.PreparedSta inStrings[paramIndex - 1] = s; } + /** + * Set a parameter to a tablerow-type oid reference. + * + * @param parameterIndex the first parameter is 1... + * @param x the oid of the object from org.postgresql.util.Serialize.store + * @param classname the classname of the java object x + * @exception SQLException if a database access error occurs + */ + private void setSerialize(int parameterIndex, long x, String classname) throws SQLException + { + // converts . to _, toLowerCase, and ensures length<32 + String tablename = Serialize.toPostgreSQL( classname ); + DriverManager.println("setSerialize: setting " + x + "::" + tablename ); + + // OID reference to tablerow-type must be cast like: :: + // Note that postgres support for tablerow data types is incomplete/broken. + // This cannot be just a plain OID because then there would be ambiguity + // between when you want the oid itself and when you want the object + // an oid references. + set(parameterIndex, Long.toString(x) + "::" + tablename ); + } + + // ** JDBC 2 Extensions ** /** @@ -803,3 +827,4 @@ public class PreparedStatement extends Statement implements java.sql.PreparedSta } } + diff --git a/src/interfaces/jdbc/org/postgresql/util/Serialize.java b/src/interfaces/jdbc/org/postgresql/util/Serialize.java index a89fba5baf..2e80102ecb 100644 --- a/src/interfaces/jdbc/org/postgresql/util/Serialize.java +++ b/src/interfaces/jdbc/org/postgresql/util/Serialize.java @@ -8,16 +8,102 @@ import java.util.*; import java.sql.*; /** - * This class uses PostgreSQL's object oriented features to store Java Objects. + * This class uses PostgreSQL's object oriented features to store Java Objects.

* * It does this by mapping a Java Class name to a table in the database. Each * entry in this new table then represents a Serialized instance of this * class. As each entry has an OID (Object IDentifier), this OID can be - * included in another table. + * included in another table.

* - * This is too complex to show here, and will be documented in the main - * documents in more detail. + * Serialize depends on a feature of Postgres that allows + * a table to be used as a data type. However, Postgres support of + * this feature is incomplete. The basic ability to create and use + * a table as a field type in another table exists:
+ * CREATE TABLE myclass( var1 TEXT, var2 INTEGER );
+ * CREATE TABLE othertable( field1 TEXT, field2 myclass );
+ * INSERT INTO myclass VALUES ('Hello', 1);
+ * INSERT INTO othertable VALUES ('World', xxxx::myclass);
+ * where xxxx is the OID of a row in myclass
+ * This lets othertable reference a myclass instance but + * the support to actually make any use of the myclass data type + * is not there. For instance, you cannot compare the myclass field + * with ANY other data type, not even with other myclass values. + * Casting to and from the myclass type will also not work. + * From the limited testing done, only the literal xxxx::myclass + * syntax appears to work.

* + * Queries like:
+ * SELECT othertable.field2.var1 FROM othertable;
+ * will not work but were suggested in the original Postgres + * design documents.

+ * Because support is incomplete for table data types, tables + * such as othertable that hold java instances should also + * hold an oid field for the same java instance:
+ * CREATE othertable( field1 TEXT, field2 myclass, myclassOID oid);
+ * This oid-type field would be set with setInt() immediately after + * setting the myclass-type field with setObject(). The order of these + * set calls matters since the oid is not available until after + * setting the object when inserting a new object. With the oid, + * queries and comparisons etc. can be done to locate a myclass. + * Read below about how to include an int oid field in your java class + * that is updated by setObject() when it is inserted.

+ * + * The myclass table represents a java class. This table is created + * by Serialize.create(). Serialize.create() must be called before + * the first use of the myclass java class in PreparedStatement.setObject() + * calls. This is a one-time initialization step.

+ * + * There are a number of limitations placed on the java class to be + * used by Serialize: + *

    + *
  • The class name must be less than 32 chars long and must be all lowercase. + * This is due to limitations in Postgres about the size of table names. + * The name must be all lowercase since table names in Postgres are + * case insensitive and the relname is stored in lowercase. Unless some + * additional table were to be maintained about the names of java classes, + * there is no way to know how to go from a Postgres table name back to + * a java class name with knowledge of case of the letters in the name. + *
  • The class name must not contain the underscore '_' character since + * any dots in a java class name are converted to an underscore in + * its table name and vice versa going back. + *
  • The class should only contain java primitive types and String. + * Support for recursively "serializing" a class is not tested but + * some code for this does exist and you may wish to take a look at it. + *
  • Only the public fields of the class will be stored in and fetched from + * the database. Protected and private fields are ignored. + *
  • Must have a no-arg constructor so that Class.newInstance() may + * instantiate the class in fetch(). + *
  • Should implement the Serializable interface. This interface + * may be used more in future releases or in providing an alternative + * method of storing the java classes in the database. The Serializable + * interface allows a class instance to be written out as a binary + * stream of data and is a standard java feature. + *
  • The class should contain a field defined as:
    + * int oid = 0;
    + * This field is actually optional and its use by jdbc2.PreparedStatement.setObject() + * is as follows:
    + * If oid does not exist in the class, the class instance is stored in a new table row + * everytime setObject() is called on it. If oid field exists and is 0, then the class + * instance is stored into a new row in the table and that row's oid is set in the class by setObject(). + * If oid field exists and is > 0, then the existing table row for the class instance is + * updated. The oid field should be thought of as read-only unless you want to set it to 0 + * so that a new instance is created in the database rather than doing an update.

    + *

+ * + * Suggested usage: + *
    + *
  1. Create your javaclass and include an int oid = 0; field. + *
  2. Run Serialize.create( conn, javaclass ) to create the table for javaclass (once). + *
  3. Create mytable in the database with fields like: jclassoid INTEGER, jclass JAVACLASS
    + *
  4. Use a jdbc2.PreparedStatement to insert, update, or select from mytable. + * Use setObject(2, jclass), followed by setInt(1, jclass.oid) to setup an insert. + *
  5. Use jclass.oid and jclassoid to do queries since the jclass field cannot be used + * for anything but fetching the javaclass instance with getObject("jclass"). + *
+ * Finally, Serialize is a work in progress and being a utility class, it is not supported. + * You are "on your own" if you use it. If you use it and make any enhancements, + * please consider joining the email lists pgsql-jdbc@postgresql.org and pgsql-patches@postgresql.org + * and contributing your additions. */ public class Serialize { @@ -41,10 +127,12 @@ public class Serialize { try { conn = c; + DriverManager.println("Serialize: initializing instance for type: " + type); tableName = toPostgreSQL(type); className = type; ourClass = Class.forName(className); } catch(ClassNotFoundException cnfe) { + DriverManager.println("Serialize: " + className + " java class not found"); throw new PSQLException("postgresql.serial.noclass",type); } @@ -52,14 +140,17 @@ public class Serialize 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; + 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) + if(!status) { + DriverManager.println("Serialize: " + tableName + " table not found"); throw new PSQLException("postgresql.serial.table",type); - + } // Finally cache the fields within the table } @@ -85,81 +176,72 @@ public class Serialize * @return Object relating to oid * @exception SQLException on error */ - public Object fetch(int oid) throws SQLException - { - try { - Object obj = ourClass.newInstance(); - - // NB: we use java.lang.reflect here to prevent confusion with - // the org.postgresql.Field - - // used getFields to get only public fields. We have no way to set values - // for other declarations. Maybe look for setFieldName() methods? - java.lang.reflect.Field f[] = ourClass.getFields(); - - boolean hasOID=false; - int oidFIELD=-1; - StringBuffer sb = new StringBuffer("select"); - char sep=' '; - - // build a select for the fields. Look for the oid field to use in the where - for(int i=0;i @@ -179,114 +261,116 @@ public class Serialize * @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 - - // don't save private fields since we would not be able to fetch them - java.lang.reflect.Field f[] = ourClass.getFields(); - - boolean hasOID=false; - int oidFIELD=-1; - boolean update=false; - - // Find out if we have an oid value - for(int i=0;i0; - } - } - - StringBuffer sb = new StringBuffer(update?"update "+tableName+" set":"insert into " + tableName); - char sep=update?' ':'('; - for(int i=0;i 0; + } + } + + StringBuffer sb = new StringBuffer(update?"update "+tableName+" set":"insert into "+tableName+" "); + char sep=update?' ':'('; + for(int i=0;i -1) { StringBuffer buf = new StringBuffer(); StringTokenizer tok = new StringTokenizer(s, "'"); @@ -294,25 +378,24 @@ public class Serialize if (idx > 0) buf.append(tok.nextToken()); while(tok.hasMoreTokens()) - buf.append("\\'").append(tok.nextToken()); + buf.append("''").append(tok.nextToken()); s = buf.toString(); } - // if the string has newlines in it convert them to \n - if ((idx = s.indexOf("\n")) > -1) { + // if the string has backslashes in it escape them them as \\ + if ((idx = s.indexOf("\\")) > -1) { StringBuffer buf = new StringBuffer(); - StringTokenizer tok = new StringTokenizer(s, "\n"); + StringTokenizer tok = new StringTokenizer(s, "\\"); if (idx > 0) buf.append(tok.nextToken()); while(tok.hasMoreTokens()) - buf.append("\\n").append(tok.nextToken()); + buf.append("\\\\").append(tok.nextToken()); s = buf.toString(); } return s; - } /** @@ -336,62 +419,62 @@ public class Serialize * @param o Class to base table on * @exception SQLException on error */ - public static void create(org.postgresql.Connection con,Class c) throws SQLException - { - if(c.isInterface()) - throw new PSQLException("postgresql.serial.interface"); - - // See if the table exists - String tableName = toPostgreSQL(c.getName()); - - 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 "); - sb.append(tableName); - char sep='('; - -// java.lang.reflect.Field[] fields = c.getDeclaredFields(); - java.lang.reflect.Field[] fields = c.getFields(); - for(int i=0;i-1) throw new PSQLException("postgresql.serial.underscore"); - // Postgres table names can only be 32 character long + // Postgres table names can only be 32 character long. + // Reserve 1 char, so allow only up to 31 chars. // If the full class name with package is too long // then just use the class name. If the class name is // too long throw an exception. - if(name.length() > 32) { + // + if( name.length() > 31 ) { name = name.substring(name.lastIndexOf(".") + 1); - - if(name.length()>32) + if( name.length() >31 ) throw new PSQLException("postgresql.serial.namelength",name,new Integer(name.length())); } - return name.replace('.','_'); } -- 2.40.0