db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From t.@apache.org
Subject svn commit: r985704 - in /db/torque/village/trunk: pom.xml src/java/com/workingdogs/village/Column.java src/java/com/workingdogs/village/QueryDataSet.java src/java/com/workingdogs/village/Schema.java src/test/com/workingdogs/village/TestMySQL.java
Date Sun, 15 Aug 2010 16:48:44 GMT
Author: tv
Date: Sun Aug 15 16:48:43 2010
New Revision: 985704

URL: http://svn.apache.org/viewvc?rev=985704&view=rev
Log:
Attempt to solve the meta-data caching issues. The current preliminary solution tries to use
a schema from the existing schemaCache but does not (yet) create new schema entries. Aliases
columns and aliases tables are not cached at all.

Modified:
    db/torque/village/trunk/pom.xml
    db/torque/village/trunk/src/java/com/workingdogs/village/Column.java
    db/torque/village/trunk/src/java/com/workingdogs/village/QueryDataSet.java
    db/torque/village/trunk/src/java/com/workingdogs/village/Schema.java
    db/torque/village/trunk/src/test/com/workingdogs/village/TestMySQL.java

Modified: db/torque/village/trunk/pom.xml
URL: http://svn.apache.org/viewvc/db/torque/village/trunk/pom.xml?rev=985704&r1=985703&r2=985704&view=diff
==============================================================================
--- db/torque/village/trunk/pom.xml (original)
+++ db/torque/village/trunk/pom.xml Sun Aug 15 16:48:43 2010
@@ -39,7 +39,14 @@
       <artifactId>junit</artifactId>
       <version>3.8.1</version>
       <scope>test</scope>
-    </dependency>
+    </dependency>
+    <dependency>
+      <groupId>mysql</groupId>
+      <artifactId>mysql-connector-java</artifactId>
+      <version>5.0.7</version>
+      <scope>test</scope>
+    </dependency>
+    
   </dependencies>
     
   <build>

Modified: db/torque/village/trunk/src/java/com/workingdogs/village/Column.java
URL: http://svn.apache.org/viewvc/db/torque/village/trunk/src/java/com/workingdogs/village/Column.java?rev=985704&r1=985703&r2=985704&view=diff
==============================================================================
--- db/torque/village/trunk/src/java/com/workingdogs/village/Column.java (original)
+++ db/torque/village/trunk/src/java/com/workingdogs/village/Column.java Sun Aug 15 16:48:43
2010
@@ -32,9 +32,6 @@ import java.sql.Types;
  */
 public class Column
 {
-    /** column number in a schema object */
-    private int columnNumber = -1;
-
     /** name of the column */
     private String name = "";
 
@@ -73,7 +70,6 @@ public class Column
      */
     public Column()
     {
-        this.columnNumber = -1;
         this.name = "";
         this.columnTypeName = "";
         this.tableName = "";
@@ -93,63 +89,32 @@ public class Column
      * @param rsmd TODO: DOCUMENT ME!
      * @param colNum TODO: DOCUMENT ME!
      * @param tableName TODO: DOCUMENT ME!
+     * @param columnName The name of the column
      *
      * @throws SQLException TODO: DOCUMENT ME!
      */
-    void populate(ResultSetMetaData rsmd, int colNum, String tableName)
+    void populate(ResultSetMetaData rsmd, int colNum, String tableName, String columnName)
             throws SQLException
     {
-        this.columnNumber = colNum;
-        this.name = rsmd.getColumnName(columnNumber);
-
-        // Workaround for Sybase jConnect 5.2 and older.
-        try
-        {
-            this.tableName = rsmd.getTableName(columnNumber);
+        this.name = columnName;
+        this.tableName = tableName;
 
-            // ResultSetMetaData may report table name as the empty
-            // string when a database-specific function has been
-            // called to generate a Column.
-            if ((this.tableName == null) || this.tableName.equals(""))
-            {
-                if (tableName != null)
-                {
-                    this.tableName = tableName;
-                }
-                else
-                {
-                    this.tableName = "";
-                }
-            }
-        }
-        catch (RuntimeException e)
-        {
-            if (tableName != null)
-            {
-                this.tableName = tableName;
-            }
-            else
-            {
-                this.tableName = "";
-            }
-        }
-
-        this.columnTypeName = rsmd.getColumnTypeName(columnNumber);
-        this.columnType = rsmd.getColumnType(columnNumber);
-        this.nullAllowed = rsmd.isNullable(columnNumber) == 1;
-        this.autoIncrement = rsmd.isAutoIncrement(columnNumber);
+        this.columnTypeName = rsmd.getColumnTypeName(colNum);
+        this.columnType = rsmd.getColumnType(colNum);
+        this.nullAllowed = rsmd.isNullable(colNum) == 1;
+        this.autoIncrement = rsmd.isAutoIncrement(colNum);
 
         // The JDBC spec is VERY unclear about what this means and as 
         // such, it should be ignored.  Derby returns true all the time.
         // Sybase and Informix say it's unsupported (and false).
-        this.readOnly = false; // rsmd.isReadOnly (columnNumber);
+        this.readOnly = false; // rsmd.isReadOnly (colNum);
         
-        this.searchable = rsmd.isSearchable(columnNumber);
-        this.scale = rsmd.getScale(columnNumber);
+        this.searchable = rsmd.isSearchable(colNum);
+        this.scale = rsmd.getScale(colNum);
 
         try
         {
-            this.precision = rsmd.getPrecision(columnNumber);
+            this.precision = rsmd.getPrecision(colNum);
         }
         catch (NumberFormatException assumedTooLarge)
         {
@@ -159,7 +124,7 @@ public class Column
             this.precision = Integer.MAX_VALUE;
         }
 
-        this.length = rsmd.getColumnDisplaySize(columnNumber);
+        this.length = rsmd.getColumnDisplaySize(colNum);
     }
 
     /**

Modified: db/torque/village/trunk/src/java/com/workingdogs/village/QueryDataSet.java
URL: http://svn.apache.org/viewvc/db/torque/village/trunk/src/java/com/workingdogs/village/QueryDataSet.java?rev=985704&r1=985703&r2=985704&view=diff
==============================================================================
--- db/torque/village/trunk/src/java/com/workingdogs/village/QueryDataSet.java (original)
+++ db/torque/village/trunk/src/java/com/workingdogs/village/QueryDataSet.java Sun Aug 15
16:48:43 2010
@@ -83,7 +83,7 @@ public class QueryDataSet
             stmt = conn.createStatement();
             resultSet = stmt.executeQuery(selectStmt);
             schema = new Schema();
-            schema.populate(resultSet.getMetaData(), null);
+            schema.populate(resultSet.getMetaData(), null, conn);
             ok = true;
         } 
         finally
@@ -115,7 +115,7 @@ public class QueryDataSet
     {
         this.resultSet = resultSet;
         schema = new Schema();
-        schema.populate(resultSet.getMetaData(), null);
+        schema.populate(resultSet.getMetaData(), null, resultSet.getStatement().getConnection());
     }
 
     /**

Modified: db/torque/village/trunk/src/java/com/workingdogs/village/Schema.java
URL: http://svn.apache.org/viewvc/db/torque/village/trunk/src/java/com/workingdogs/village/Schema.java?rev=985704&r1=985703&r2=985704&view=diff
==============================================================================
--- db/torque/village/trunk/src/java/com/workingdogs/village/Schema.java (original)
+++ db/torque/village/trunk/src/java/com/workingdogs/village/Schema.java Sun Aug 15 16:48:43
2010
@@ -21,13 +21,10 @@ package com.workingdogs.village;
 
 import java.io.ByteArrayOutputStream;
 import java.io.PrintWriter;
-
 import java.sql.Connection;
-import java.sql.ResultSet;
+import java.sql.PreparedStatement;
 import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
-import java.sql.Statement;
-
 import java.util.Enumeration;
 import java.util.Hashtable;
 
@@ -52,9 +49,10 @@ public final class Schema
 
     /** TODO: DOCUMENT ME! */
     private Column [] columns;
+    private Hashtable columnNumberByName;
 
     /** TODO: DOCUMENT ME! */
-    private static Hashtable schemaCache = new Hashtable();
+    private static final Hashtable schemaCache = new Hashtable();
 
     /**
      * This attribute is used to complement columns in the event that this schema represents
more than one table.  Its keys are
@@ -104,7 +102,7 @@ public final class Schema
      * @exception SQLException
      * @exception DataSetException
      */
-    public synchronized Schema schema(Connection conn, String tableName, String columnsAttribute)
+    public Schema schema(Connection conn, String tableName, String columnsAttribute)
             throws SQLException, DataSetException
     {
         if (columnsAttribute == null)
@@ -112,50 +110,47 @@ public final class Schema
             columnsAttribute = "*";
         }
 
-        Statement stmt = null;
-        ResultSet rs = null;
+        PreparedStatement stmt = null;
 
         try
         {
+        	Schema tableSchema = null;
             String keyValue = conn.getMetaData().getURL() + tableName;
-            Schema tableSchema = (Schema) schemaCache.get(keyValue);
-
-            if (tableSchema == null)
-            {
-                String sql = "SELECT " + columnsAttribute + " FROM " + tableName + " WHERE
1 = -1";
-                stmt = conn.createStatement();
-
-                rs = stmt.executeQuery(sql);
-
-                if (rs != null)
-                {
-                    tableSchema = new Schema();
-                    tableSchema.setTableName(tableName);
-                    tableSchema.setAttributes(columnsAttribute);
-                    tableSchema.populate(rs.getMetaData(), tableName);
-                    schemaCache.put(keyValue, tableSchema);
-                }
-                else
-                {
-                    throw new DataSetException("Couldn't retrieve schema for " + tableName);
-                }
-            }
+        	
+        	synchronized (schemaCache)
+        	{
+	            tableSchema = (Schema) schemaCache.get(keyValue);
+	
+	            if (tableSchema == null)
+	            {
+	            	String sql = "SELECT " + columnsAttribute + " FROM " + tableName + " WHERE
1 = -1";
+	
+	            	/*
+	            	 *  prepare dummy query as recommended by John Goodson in
+	            	 *  http://www.theserverside.com/news/1365579/Using-Database-MetaData-methods-appropriately

+	            	 */
+	            	stmt = conn.prepareStatement(sql);
+	
+	            	// query is never executed on the server - only prepared
+	                if (stmt != null)
+	                {
+	                    tableSchema = new Schema();
+	                    tableSchema.setTableName(tableName);
+	                    tableSchema.setAttributes(columnsAttribute);
+	                    tableSchema.populate(stmt.getMetaData(), tableName, null);
+	                    schemaCache.put(keyValue, tableSchema);
+	                }
+	                else
+	                {
+	                    throw new DataSetException("Couldn't retrieve schema for " + tableName);
+	                }
+	            }
+        	}
 
             return tableSchema;
         }
         finally
         {
-        	if (rs != null)
-        	{
-        		try
-        		{
-        			rs.close();
-        		}
-        		catch (SQLException e)
-        		{
-        			//Do nothing
-        		}
-        	}
             if (stmt != null)
             {
             	try
@@ -342,25 +337,16 @@ public final class Schema
     public int index(String colName)
             throws DataSetException
     {
-        int dot = colName.indexOf('.');
-
-        if (dot > 0)
+    	Integer position = (Integer)columnNumberByName.get(colName);
+    	
+    	if (position != null)
         {
-            String table = colName.substring(0, dot);
-            String col = colName.substring(dot + 1);
-
-            return index(table, col);
+        	return position.intValue();
         }
-
-        for (int i = 1; i <= numberOfColumns(); i++)
+        else
         {
-            if (columns[i].name().equalsIgnoreCase(colName))
-            {
-                return i;
-            }
+        	throw new DataSetException("Column name: " + colName + " does not exist!");
         }
-
-        throw new DataSetException("Column name: " + colName + " does not exist!");
     }
 
     /**
@@ -376,15 +362,7 @@ public final class Schema
     public int index(String tableName, String colName)
             throws DataSetException
     {
-        for (int i = 1; i <= numberOfColumns(); i++)
-        {
-            if (columns[i].name().equalsIgnoreCase(colName) && columns[i].getTableName().equalsIgnoreCase(tableName))
-            {
-                return i;
-            }
-        }
-
-        throw new DataSetException("Column name: " + colName + " does not exist!");
+    	return index(tableName + "." + colName);
     }
 
     /**
@@ -412,21 +390,92 @@ public final class Schema
      *
      * @param meta The meta data of the ResultSet used to build this Schema.
      * @param tableName The name of the table referenced in this schema, or null if unknown
or multiple tables are involved.
+     * @param conn The connection whose URL serves as a cache key prefix
      *
      * @exception SQLException
      * @exception DataSetException
      */
-    void populate(ResultSetMetaData meta, String tableName)
+    void populate(ResultSetMetaData meta, String tableName, Connection conn)
             throws SQLException, DataSetException
     {
         this.numberOfColumns = meta.getColumnCount();
         columns = new Column[numberOfColumns() + 1];
-
+        columnNumberByName = new Hashtable((int) ((1.25 * numberOfColumns) + 1));
+     
+        String connURL = (conn != null) ? conn.getMetaData().getURL() : null;
+        
         for (int i = 1; i <= numberOfColumns(); i++)
         {
-            Column col = new Column();
-            col.populate(meta, i, tableName);
+        	String metaColumnName = meta.getColumnName(i);
+        	String metaTableName = null;
+        	
+            // Workaround for Sybase jConnect 5.2 and older.
+            try
+            {
+            	metaTableName = meta.getTableName(i);
+
+                // ResultSetMetaData may report table name as the empty
+                // string when a database-specific function has been
+                // called to generate a Column.
+                if ((metaTableName == null) || metaTableName.equals(""))
+                {
+                    if (tableName != null)
+                    {
+                    	metaTableName = tableName;
+                    }
+                    else
+                    {
+                    	metaTableName = "";
+                    }
+                }
+            }
+            catch (RuntimeException e)
+            {
+                if (tableName != null)
+                {
+                	metaTableName = tableName;
+                }
+                else
+                {
+                	metaTableName = "";
+                }
+            }
+
+            Column col = null;
+        	
+            if (metaTableName.length() > 0 && connURL != null)
+            {
+            	Schema tableSchema = null; // schema(conn, metaTableName);
+            
+            	synchronized (schemaCache)
+            	{
+            		tableSchema = (Schema) schemaCache.get(connURL + metaTableName);
+            	}
+            	
+            	if (tableSchema != null)
+            	{
+        			try 
+        			{
+						col = tableSchema.column(metaColumnName);
+					} 
+        			catch (DataSetException e) 
+        			{
+        				// column does not exist, ignore
+					}
+            	}
+            }
+            
+            // Not found in cache
+            if (col == null)
+            {
+	            col = new Column();
+	            col.populate(meta, i, metaTableName, metaColumnName);
+            }
+            
             columns[i] = col;
+            Integer position = new Integer(i);
+            columnNumberByName.put(metaColumnName, position);
+            columnNumberByName.put(metaTableName + "." + metaColumnName, position);
 
             if ((i > 1) && !col.getTableName().equalsIgnoreCase(columns[i - 1].getTableName()))
             {
@@ -451,7 +500,7 @@ public final class Schema
                 // 5.2 and older will fail, in which case we are screwed.
                 try
                 {
-                    setTableName(meta.getTableName(1));
+                    setTableName(columns[1].getTableName());
                 }
                 catch (Exception e)
                 {
@@ -461,21 +510,24 @@ public final class Schema
         }
         else
         {
-            tableHash = new Hashtable((int) ((1.25 * numberOfColumns) + 1));
-
-            for (int i = 1; i <= numberOfColumns(); i++)
-            {
-                if (tableHash.containsKey(columns[i].getTableName()))
-                {
-                    ((Hashtable) tableHash.get(columns[i].getTableName())).put(columns[i].name(),
columns[i]);
-                }
-                else
-                {
-                    Hashtable columnHash = new Hashtable((int) ((1.25 * numberOfColumns)
+ 1));
-                    columnHash.put(columns[i].name(), columns[i]);
-                    tableHash.put(columns[i].getTableName(), columnHash);
-                }
-            }
+	        tableHash = new Hashtable((int) ((1.25 * numberOfColumns) + 1));
+	
+	        for (int i = 1; i <= numberOfColumns(); i++)
+	        {
+	        	Hashtable columnHash;
+	
+	        	if (tableHash.containsKey(columns[i].getTableName()))
+	            {
+	                columnHash = (Hashtable) tableHash.get(columns[i].getTableName());
+	            }
+	            else
+	            {
+	                columnHash = new Hashtable((int) ((1.25 * numberOfColumns) + 1));
+	                tableHash.put(columns[i].getTableName(), columnHash);
+	            }
+	            
+	            columnHash.put(columns[i].name(), columns[i]);
+	        }
         }
     }
 

Modified: db/torque/village/trunk/src/test/com/workingdogs/village/TestMySQL.java
URL: http://svn.apache.org/viewvc/db/torque/village/trunk/src/test/com/workingdogs/village/TestMySQL.java?rev=985704&r1=985703&r2=985704&view=diff
==============================================================================
--- db/torque/village/trunk/src/test/com/workingdogs/village/TestMySQL.java (original)
+++ db/torque/village/trunk/src/test/com/workingdogs/village/TestMySQL.java Sun Aug 15 16:48:43
2010
@@ -92,7 +92,7 @@ import junit.framework.TestCase;
 public class TestMySQL extends TestCase
 {
     /** The database connection */
-    static Connection conn;
+    private Connection conn;
 
     /** This is the name of the database. Created with mysqladmin create */
     private static String DB_NAME = "village";
@@ -113,11 +113,11 @@ public class TestMySQL extends TestCase
     private static String DB_PASS = "village";
 
     /** mm MySQL Driver setup */
-    private static String DB_DRIVER = "org.gjt.mm.mysql.Driver";
-    //private static String DB_DRIVER = "com.mysql.jdbc.Driver";
+    //private static String DB_DRIVER = "org.gjt.mm.mysql.Driver";
+    private String DB_DRIVER = "com.mysql.jdbc.Driver";
 
     /** mm MySQL Driver setup */
-    private static String DB_CONNECTION = "jdbc:mysql://" + DB_HOST + "/"
+    private String DB_CONNECTION = "jdbc:mysql://" + DB_HOST + "/"
             + DB_NAME + "?user=" + DB_USER + "&password=" + DB_PASS;
 
     /** used for debugging */
@@ -216,7 +216,7 @@ public class TestMySQL extends TestCase
      * This test verifies that deleting multiple records actually works. after
      * execution, there should be no more records in the database.
      */
-    public static void testDeleteSomeRecords()
+    public void testDeleteSomeRecords()
             throws SQLException, DataSetException
     {
         KeyDef kd = new KeyDef().addAttrib("e");
@@ -250,7 +250,7 @@ public class TestMySQL extends TestCase
     /**
      * This test checks that a DataSetException is thrown when appropriate.
      */
-    public static void testRemoveRecord()
+    public void testRemoveRecord()
             throws SQLException, DataSetException
     {
         TableDataSet tds = new TableDataSet(conn, DB_TABLE);
@@ -270,7 +270,7 @@ public class TestMySQL extends TestCase
         tds.close();
     }
 
-    public static void testTableDataSet2()
+    public void testTableDataSet2()
             throws SQLException, DataSetException
     {
         TableDataSet tds = new TableDataSet(conn, DB_TABLE);
@@ -280,7 +280,7 @@ public class TestMySQL extends TestCase
         tds.close();
     }
 
-    public static void testTableDataSet3()
+    public void testTableDataSet3()
             throws SQLException, DataSetException
     {
         TableDataSet tds = new TableDataSet(conn, DB_TABLE);
@@ -290,7 +290,7 @@ public class TestMySQL extends TestCase
         tds.close();
     }
 
-    public static void testTableDataSet4()
+    public void testTableDataSet4()
             throws SQLException, DataSetException
     {
         KeyDef kd = new KeyDef().addAttrib("b");
@@ -305,7 +305,7 @@ public class TestMySQL extends TestCase
         tds.close();
     }
 
-    public static void testTableDataSet()
+    public void testTableDataSet()
             throws SQLException, DataSetException
     {
         KeyDef kd = new KeyDef().addAttrib("a");
@@ -461,7 +461,7 @@ public class TestMySQL extends TestCase
      * @throws DataSetException 
      * @throws SQLException 
      */
-    public static void testQueryDataSet()
+    public void testQueryDataSet()
             throws SQLException, DataSetException
     {
         KeyDef kd = new KeyDef().addAttrib("a");
@@ -487,9 +487,11 @@ public class TestMySQL extends TestCase
         assertEquals("SELECT * FROM test", qds.getSelectString());
 
         debug(QDS, "qds.size()", qds.size()); // should be 1
+        assertEquals("Query data set size should be 1", 1, qds.size());
 
         Record rec = qds.getRecord(0);
         debug(QDS, "rec.size()", rec.size()); // should be 24
+        assertEquals("Record size should be 24", 24, rec.size());
 
         debug(QDS, "rec.getValue(\"a\").asString()", rec.getValue("a").asString());
         debug(QDS, "rec.getValue(\"b\").asString()", rec.getValue("b").asString());
@@ -503,7 +505,35 @@ public class TestMySQL extends TestCase
         //assertEquals("0", rec.getValue("d").asString());
         assertNull(rec.getValue("d").asString());
         qds.close();
+        
+        // Test column aliases
+        qds = new QueryDataSet(conn, "SELECT a as aa FROM " + DB_TABLE);
+        qds.fetchRecords();
+
+        debug(QDS, "qds.size()", qds.size()); // should be 1
+        assertEquals("Query data set size should be 1", 1, qds.size());
+
+        rec = qds.getRecord(0);
+        debug(QDS, "rec.size()", rec.size()); // should be 1
+        assertEquals("Record size should be 1", 1, rec.size());
 
+        debug(QDS, "rec.getValue(\"a\").asString()", rec.getValue("aa").asString());
+        qds.close();
+
+        // Test table aliases
+        qds = new QueryDataSet(conn, "SELECT a FROM " + DB_TABLE + " AS t");
+        qds.fetchRecords();
+
+        debug(QDS, "qds.size()", qds.size()); // should be 1
+        assertEquals("Query data set size should be 1", 1, qds.size());
+
+        rec = qds.getRecord(0);
+        debug(QDS, "rec.size()", rec.size()); // should be 1
+        assertEquals("Record size should be 1", 1, rec.size());
+
+        debug(QDS, "rec.getValue(\"a\").asString()", rec.getValue("a").asString());
+        qds.close();
+        
         // delete the record
         kd = new KeyDef().addAttrib("a");
         tds = new TableDataSet(conn, DB_TABLE, kd);
@@ -521,7 +551,7 @@ public class TestMySQL extends TestCase
      * @throws DataSetException 
      * @throws SQLException 
      */
-    public static void testSchemaResultSet()
+    public void testSchemaResultSet()
             throws SQLException, DataSetException
     {
         for (int i = 0; i < SCHEMA_LOOPS; i++)
@@ -535,7 +565,7 @@ public class TestMySQL extends TestCase
     /**
      * Get a connection. 
      */
-    public static void getConnection()
+    public void getConnection()
             throws ClassNotFoundException, SQLException
     {
         Class.forName(DB_DRIVER);
@@ -548,7 +578,7 @@ public class TestMySQL extends TestCase
      * @param type
      * @param e
      */
-    public static void debug(int type, Exception e)
+    public void debug(int type, Exception e)
     {
         debug(TDS, e.getMessage());
         e.printStackTrace();
@@ -561,7 +591,7 @@ public class TestMySQL extends TestCase
      * @param type
      * @param method
      */
-    public static void debug(int type, String method)
+    public void debug(int type, String method)
     {
         debug(type, method, null);
     }
@@ -573,7 +603,7 @@ public class TestMySQL extends TestCase
      * @param method
      * @param value
      */
-    public static void debug(int type, String method, int value)
+    public void debug(int type, String method, int value)
     {
         debug(type, method, String.valueOf(value));
     }
@@ -585,7 +615,7 @@ public class TestMySQL extends TestCase
      * @param method
      * @param value
      */
-    public static void debug(int type, String method, String value)
+    public void debug(int type, String method, String value)
     {
         if (debugging)
         {



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message