velocity-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From cbris...@apache.org
Subject svn commit: r1298906 [9/14] - in /velocity/sandbox/velosurf: ./ docs/ examples/ examples/ant-vpp/ examples/ant-vpp/lib/ examples/auth-l10n/ examples/auth-l10n/WEB-INF/ examples/auth-l10n/WEB-INF/lib/ examples/auth-l10n/WEB-INF/src/ examples/auth-l10n/e...
Date Fri, 09 Mar 2012 16:23:32 GMT
Added: velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/DriverInfo.java
URL: http://svn.apache.org/viewvc/velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/DriverInfo.java?rev=1298906&view=auto
==============================================================================
--- velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/DriverInfo.java (added)
+++ velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/DriverInfo.java Fri Mar  9 16:23:25 2012
@@ -0,0 +1,380 @@
+package org.apache.velocity.velosurf.sql;
+
+import java.lang.reflect.Method;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Calendar;
+import java.util.HashMap;
+import java.util.Map;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+import java.util.regex.PatternSyntaxException;
+import org.apache.velocity.velosurf.util.Logger;
+
+/**
+ * <p>Contains specific description and behaviour of jdbc drivers.</p>
+ *
+ * <p>Main sources:
+ * <ul><li>http://www.schemaresearch.com/products/srtransport/doc/modules/jdbcconf.html
+ * <li>http://db.apache.org/torque/ and org.apache.torque.adapter classes
+ * </ul></p>
+ *
+ *  @author <a href="mailto:claude.brisson@gmail.com">Claude Brisson</a>
+ */
+public class DriverInfo
+{
+    /**
+     * Get a driver info by url and driver class.
+     * @param url database url
+     * @param driverClass driver class
+     * @return driver infos
+     */
+    static public DriverInfo getDriverInfo(String url, String driverClass)
+    {
+        /* always try to use both infos to check for validity */
+        String vendor = null;
+
+        try
+        {
+            Matcher matcher = Pattern.compile("^jdbc:([^:]+):").matcher(url);
+
+            if(matcher.find())
+            {
+                vendor = matcher.group(1);
+            }
+            else
+            {
+                Logger.warn("Could not guess JDBC vendor from URL " + url);
+                Logger.warn("Please report this issue on Velosurf bug tracker.");
+            }
+        }
+        catch(PatternSyntaxException pse)
+        {
+            Logger.log(pse);
+        }
+
+        DriverInfo ret = null, ret1 = null, ret2 = null;
+
+        if(vendor != null)
+        {
+            ret1 = driverByVendor.get(vendor);
+            if(ret1 == null)
+            {
+                Logger.warn("Velosurf doesn't know JDBC vendor '" + vendor + "'. Please contribute!");
+            }
+        }
+        if(driverClass != null)
+        {
+            ret2 = driverByClass.get(driverClass);
+        }
+        if(ret1 == null && ret2 == null)
+        {
+            String msg = "No driver infos found for: ";
+
+            if(driverClass != null)
+            {
+                msg += "class " + driverClass + ", ";
+            }
+            if(vendor != null)
+            {
+                msg += "vendor " + vendor;
+            }
+            Logger.warn(msg);
+            Logger.warn("Please contribute! See http://velosurf.sf.net/velosurf/docs/drivers.html");
+        }
+        else if(ret1 != null && ret2 != null)
+        {
+            if(ret1.equals(ret2))
+            {
+                ret = ret1;
+            }
+            else
+            {
+                Logger.warn("Driver class '" + driverClass + "' and driver vendor '" + vendor + "' do not match!");
+                Logger.warn("Please report this issue on Velosurf bug tracker.");
+                ret = ret2;
+            }
+        }
+        else if(ret1 != null)
+        {
+            if(driverClass != null)
+            {
+                Logger.warn("Driver class '" + driverClass
+                            + "' is not referenced in Velosurf as a known driver for vendor '" + vendor + "'");
+                Logger.warn("Please report this issue on Velosurf bug tracker.");
+
+                /* not even sure this new driver will have the same behaviour... */
+                ret1.drivers = new String[] { driverClass };
+            }
+            ret = ret1;
+        }
+        else if(ret2 != null)
+        {
+            ret = ret2;    /* already warned */
+        }
+        if(ret == null)
+        {
+            Logger.warn("Using default driver behaviour...");
+            ret = (DriverInfo)driverByVendor.get("unknown");
+        }
+        return ret;
+    }
+
+    /**
+     * Driver info constructor.
+     * @param name name
+     * @param jdbcTag jdbc tag
+     * @param drivers array of driver class names
+     * @param pingQuery ping query (e.g. "select 1")
+     * @param caseSensivity default case sensivity policy
+     * @param schemaQuery query to change schema
+     * @param IDGenerationMethod preferred ID generation method
+     * @param lastInsertIDQuery query to get last inserted ID value
+     * @param ignorePattern ignore tables whose name matches this pattern
+     */
+    private DriverInfo(String name, String jdbcTag, String drivers[], String pingQuery, String caseSensivity,
+                       String schemaQuery, String IDGenerationMethod, String lastInsertIDQuery, String ignorePattern)
+    {
+        this.name = name;
+        this.jdbcTag = jdbcTag;
+        this.drivers = drivers;
+        this.pingQuery = pingQuery;
+        this.caseSensivity = caseSensivity;
+        this.schemaQuery = schemaQuery;
+        this.IDGenerationMethod = IDGenerationMethod;
+        this.lastInsertIDQuery = lastInsertIDQuery;
+        this.ignorePattern = (ignorePattern == null ? null : Pattern.compile(ignorePattern));
+
+//      this.IDGenerationQuery = IDGenerationQuery;
+    }
+
+    /** name of the database vendor */
+    private String name;
+
+    /** jdbc tag of the database vendor */
+    private String jdbcTag;
+
+    /** list of driver classes */
+    private String[] drivers;
+
+    /** ping SQL query */
+    private String pingQuery;
+
+    /** case-sensivity */
+    private String caseSensivity;
+
+    /** SQL query to set the current schema */
+    private String schemaQuery;
+
+    /** ID generation method */
+    private String IDGenerationMethod;
+
+    /** query used to retrieve the last inserted id */
+    private String lastInsertIDQuery;
+
+    /** ignore tables matchoing this pattern */
+    private Pattern ignorePattern;
+
+//  not yet implemented (TODO)
+//    public String IDGenerationQuery;   // ID generation query
+
+    /**
+     *  Add a new driver.
+     * @param name name
+     * @param jdbcTag jdbc tag
+     * @param drivers array of driver class names
+     * @param pingQuery ping query (e.g. "select 1")
+     * @param caseSensivity default case sensivity policy
+     * @param schemaQuery query to change schema
+     * @param IDGenerationMethod preferred ID generation method
+     * @param lastInsertIDQuery query to get last inserted ID value
+     * @param ignorePrefix ignore tables whose name matches this pattern
+     */
+    public static void addDriver(String name, String jdbcTag, String drivers[], String pingQuery, String caseSensivity,
+                                 String schemaQuery, String IDGenerationMethod, String lastInsertIDQuery,
+                                 String ignorePrefix /* ,String IDGenerationQuery */)
+    {
+        DriverInfo infos = new DriverInfo(name, jdbcTag, drivers, pingQuery, caseSensivity, schemaQuery,
+                                          IDGenerationMethod, lastInsertIDQuery, ignorePrefix /* ,IDGenerationQuery */);
+
+        driverByVendor.put(jdbcTag, infos);
+        for(String clazz : drivers)
+        {
+            driverByClass.put(clazz, infos);
+        }
+    }
+
+    /** map jdbctag -> driver infos. */
+    static private Map<String, DriverInfo> driverByVendor = new HashMap<String, DriverInfo>();
+
+    /** map driver class -> driver infos. */
+    static private Map<String, DriverInfo> driverByClass = new HashMap<String, DriverInfo>();
+
+    /**
+     * Get the jdbc tag.
+     * @return jdbc tag
+     */
+    public String getJdbcTag()
+    {
+        return jdbcTag;
+    }
+
+    /**
+     * Get the list of driver class names.
+     * @return array of driver class names
+     */
+    public String[] getDrivers()
+    {
+        return drivers;
+    }
+
+    /**
+     * Get the ping query.
+     * @return ping query
+     */
+    public String getPingQuery()
+    {
+        return pingQuery;
+    }
+
+    /**
+     * Get case sensivity default policy.
+     * @return case sensivity default policy
+     */
+    public String getCaseSensivity()
+    {
+        return caseSensivity;
+    }
+
+    /**
+     * Get the schema setter query.
+     * @return schema setter query
+     */
+    public String getSchemaQuery()
+    {
+        return schemaQuery;
+    }
+
+    /**
+     * Get the last inserted id.
+     * @param statement source statement
+     * @return last inserted id (or -1)
+     * @throws SQLException
+     */
+    public long getLastInsertId(Statement statement) throws SQLException
+    {
+        long ret = -1;
+
+        if("mysql".equalsIgnoreCase(getJdbcTag()))
+        {    /* MySql */
+            try
+            {
+                Method lastInsertId = statement.getClass().getMethod("getLastInsertID", new Class[0]);
+
+                ret = ((Long)lastInsertId.invoke(statement, new Object[0])).longValue();
+            }
+            catch(Throwable e)
+            {
+                Logger.log("Could not find last insert id: ", e);
+            }
+        }
+        else
+        {
+            if(lastInsertIDQuery == null)
+            {
+                Logger.error("getLastInsertID is not [yet] implemented for your dbms... Contribute!");
+            }
+            else
+            {
+                ResultSet rs = statement.getConnection().createStatement().executeQuery(lastInsertIDQuery);
+
+                rs.next();
+                ret = rs.getLong(1);
+            }
+        }
+        return ret;
+    }
+
+    /**
+     * Check whether to ignore or not this table.
+     *
+     * @param name table name
+     * @return whether to ignore this table
+     */
+    public boolean ignoreTable(String name)
+    {
+        return ignorePattern != null && ignorePattern.matcher(name).matches();
+    }
+
+    // sources :
+    // http://www.schemaresearch.com/products/srtransport/doc/modules/jdbcconf.html
+    // http://db.apache.org/torque/ and org.apache.torque.adapter classes
+    // and Google of course
+    static
+    {
+        addDriver("Axion", "axiondb", new String[] { "org.axiondb.jdbc.AxionDriver" }, "select 1", "TODO", "TODO",
+                  "none", null, null);
+        addDriver("Cloudscape", "cloudscape", new String[] { "COM.cloudscape.core.JDBCDriver" }, "select 1", "TODO",
+                  "TODO", "autoincrement", "VALUES IDENTITY_VAL_LOCAL()", null);
+        addDriver("DB2", "db2", new String[] { "COM.ibm.db2.jdbc.app.DB2Driver", "COM.ibm.db2.jdbc.net.DB2Driver" },
+                  "select 1", "TODO", "TODO", "none", "VALUES IDENTITY_VAL_LOCAL()", null);
+        addDriver("Derby", "derby", new String[] { "org.apache.derby.jdbc.EmbeddedDriver" }, "values 1", "uppercase",
+                  "set schema $schema", "autoincrement", null, null);
+        addDriver("Easysoft", "easysoft", new String[] { "easysoft.sql.jobDriver" }, "select 1", "TODO", "TODO",
+                  "TODO", null, null);
+        addDriver("Firebird", "firebirdsql", new String[] { "org.firebirdsql.jdbc.FBDriver" }, "TODO", "TODO", "TODO",
+                  "TODO", null, null);
+        addDriver("Frontbase", "frontbase", new String[] { "jdbc.FrontBase.FBJDriver" }, "select 1", "TODO", "TODO",
+                  "TODO", null, null);
+        addDriver("HSQLDB", "hsqldb", new String[] { "org.hsqldb.jdbcDriver", "org.hsql.jdbcDriver" }, "call 1",
+                  "uppercase", "set schema $schema", "autoincrement", "CALL IDENTITY()", "SYSTEM_.*");
+        addDriver("Hypersonic", "hypersonic", new String[] { "org.hsql.jdbcDriver" }, "select 1", "TODO", "TODO",
+                  "autoincrement", null, null);
+        addDriver("OpenBase", "openbase", new String[] { "com.openbase.jdbc.ObDriver" }, "select 1", "TODO", "TODO",
+                  "TODO", null, null);
+        addDriver("Informix", "informix", new String[] { "com.informix.jdbc.IfxDriver" }, "select 1", "TODO", "TODO",
+                  "none", null, null);
+        addDriver("InstantDB", "instantdb", new String[] { "org.enhydra.instantdb.jdbc.idbDriver" }, "select 1",
+                  "TODO", "TODO", "none", null, null);
+        addDriver("Interbase", "interbase", new String[] { "interbase.interclient.Driver" }, "select 1", "TODO",
+                  "TODO", "none", null, null);
+        addDriver("ODBC", "odbc", new String[] { "sun.jdbc.odbc.JdbcOdbcDriver" }, "select 1", "TODO", "TODO", "TODO",
+                  null, null);
+        addDriver("Sql Server", "sqlserver", new String[] { "com.microsoft.jdbc.sqlserver.SQLServerDriver",
+            "com.jnetdirect.jsql.JSQLDriver", "com.merant.datadirect.jdbc.sqlserver.SQLServerDriver" }, "select 1",
+            "TODO", "TODO", "autoincrement", null, null);
+        addDriver("MySql", "mysql", new String[] { "com.mysql.jdbc.Driver", "org.gjt.mm.mysql.Driver" }, "select 1",
+                  "sensitive", null, "autoincrement", null, null);
+        addDriver("OpenBase", "", new String[] { "com.openbase.jdbc.ObDriver" }, "select 1", "TODO", "TODO", "TODO",
+                  null, null);
+        addDriver("Oracle", "oracle", new String[] { "oracle.jdbc.driver.OracleDriver" }, "select 1 from dual",
+                  "uppercase", "alter session set current_schema = $schema", "sequence", null, ".*\\/.*");
+        addDriver("PostgreSQL", "postgresql", new String[] { "org.postgresql.Driver" }, "select 1", "lowercase", null,
+                  "autoincrement", null, null);    // also sequences, but support for autoincrement is better
+        addDriver("SapDB", "sapdb", new String[] { "com.sap.dbtech.jdbc.DriverSapDB" }, "select 1 from dual",
+                  "uppercase", "TODO", "sequence", null, null);
+        addDriver("Sybase", "sybase", new String[] { "com.sybase.jdbc2.jdbc.SybDriver" }, "select 1", "TODO", "TODO",
+                  "autoincrement", "SELECT @@IDENTITY", null);
+        addDriver("Weblogic", "weblogic", new String[] { "weblogic.jdbc.pool.Driver" }, "select 1", "TODO", "TODO",
+                  "none", null, null);
+
+        // unknwon driver
+        addDriver("Unknown driver", "unknown", new String[] {}, "select 1", "sensitive", null, "none", null, null);
+    }
+
+    /**
+     * Driver-specofic value filtering
+     *
+     * @param value value to be filtered
+     * @return filtered value
+     */
+    public Object filterValue(Object value)
+    {
+        if(value instanceof Calendar && "mysql".equals(jdbcTag))
+        {
+            value = ((Calendar)value).getTime();
+        }
+        return value;
+    }
+}

Added: velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/Pool.java
URL: http://svn.apache.org/viewvc/velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/Pool.java?rev=1298906&view=auto
==============================================================================
--- velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/Pool.java (added)
+++ velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/Pool.java Fri Mar  9 16:23:25 2012
@@ -0,0 +1,27 @@
+/*
+ * Copyright 2003 The Apache Software Foundation.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+
+
+package org.apache.velocity.velosurf.sql;
+
+/**
+ * This interface represents a generic pool of objects - no real need for now.
+ *
+ *  @author <a href='mailto:claude.brisson@gmail.com'>Claude Brisson</a>
+ *
+ */
+public interface Pool{}

Added: velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PooledPreparedStatement.java
URL: http://svn.apache.org/viewvc/velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PooledPreparedStatement.java?rev=1298906&view=auto
==============================================================================
--- velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PooledPreparedStatement.java (added)
+++ velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PooledPreparedStatement.java Fri Mar  9 16:23:25 2012
@@ -0,0 +1,377 @@
+/*
+ * Copyright 2003 The Apache Software Foundation.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+
+
+package org.apache.velocity.velosurf.sql;
+
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.HashSet;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.TreeMap;
+import org.apache.velocity.velosurf.context.RowIterator;
+import org.apache.velocity.velosurf.model.Entity;
+import org.apache.velocity.velosurf.util.Logger;
+import org.apache.velocity.velosurf.util.StringLists;
+
+/**
+ * this class encapsulates a jdbc PreparedStatement.
+ *
+ *  @author <a href=mailto:claude.brisson@gmail.com>Claude Brisson</a>
+ *
+ */
+public class PooledPreparedStatement extends PooledStatement implements RowHandler
+{
+    /** org.apache.velocity.tools.generic.ValueParser$ValueParserSub class, if found in the classpath. */
+    private static Class valueParserSubClass = null;
+
+    static
+    {
+        try
+        {
+            valueParserSubClass = Class.forName("org.apache.velocity.tools.generic.ValueParser$ValueParserSub");
+        }
+        catch(ClassNotFoundException cnfe) {}
+    }
+
+    /**
+     * build a new PooledPreparedStatement.
+     *
+     * @param connection database connection
+     * @param preparedStatement wrapped prepared statement
+     */
+    public PooledPreparedStatement(ConnectionWrapper connection, PreparedStatement preparedStatement)
+    {
+        this.connection = connection;
+        this.preparedStatement = preparedStatement;
+    }
+
+    /**
+     * get a unique object by id.
+     *
+     * @param params parameter values
+     * @exception SQLException thrown bu the database engine
+     * @return fetched Instance
+     */
+    public synchronized Object fetch(List params) throws SQLException
+    {
+        return fetch(params, null);
+    }
+
+    /**
+     * get a unique object by id and specify the Entity this object is an Instance of.
+     *
+     * @param params parameter values
+     * @param resultEntity resulting entity
+     * @exception SQLException thrown by the database engine
+     * @return the fetched Instance
+     */
+    public synchronized Object fetch(List params, Entity resultEntity) throws SQLException
+    {
+        Map<String, Object> row = null;
+
+        try
+        {
+            Logger.trace("fetch-params=" + StringLists.join(params, ","));
+            setParams(params);
+
+            boolean hasNext = false;
+
+            try
+            {
+                connection.enterBusyState();
+                resultSet = preparedStatement.executeQuery();
+                hasNext = resultSet.next();
+            }
+            finally
+            {
+                connection.leaveBusyState();
+            }
+            entity = resultEntity;
+            if(hasNext)
+            {
+                if(resultEntity != null)
+                {
+                    row = resultEntity.newInstance(new ReadOnlyMap(this), true);
+                }
+                else
+                {
+                    row = new TreeMap<String, Object>();
+                    if(columnNames == null)
+                    {
+                        columnNames = SqlUtil.getColumnNames(resultSet);
+                    }
+                    for(Iterator it = columnNames.iterator(); it.hasNext(); )
+                    {
+                        String column = (String)it.next();
+                        Object value = resultSet.getObject(column);
+
+                        if(value != null &&!resultSet.wasNull())
+                        {
+                            row.put(column, value);
+                        }
+                    }
+                }
+            }
+        }
+        finally
+        {
+            notifyOver();
+        }
+        return row;
+    }
+
+    /**
+     * get a unique object by id and specify the Entity this object is an Instance of.
+     *
+     * @param params parameter values
+     * @param resultEntity resulting entity
+     * @exception SQLException thrown by the database engine
+     * @return the fetched Instance
+     */
+    public synchronized Object fetch(Map<String, Object> params, Entity resultEntity) throws SQLException
+    {
+        List<Object> values = new ArrayList<Object>();
+
+        for(Iterator i = resultEntity.getPKCols().iterator(); i.hasNext(); )
+        {
+            String key = (String)i.next();
+            String value = (String)params.get(key);
+
+            if(value == null)
+            {
+                throw new SQLException("Error: key column '" + key + "' is not specified!");
+            }
+            values.add(value);
+        }
+        return fetch(values, resultEntity);
+    }
+
+    /**
+     * get the rowset.
+     *
+     * @param params parameter values
+     * @exception SQLException thrown by the database engine
+     * @return the resulting row iterator
+     */
+    public synchronized RowIterator query(List params) throws SQLException
+    {
+        return query(params, null);
+    }
+
+    /**
+     * get the rowset.
+     *
+     * @param params parameter values
+     * @param resultEntity resulting entity
+     * @exception SQLException thrown by the database engine
+     * @return resulting RowIterator
+     */
+    public synchronized RowIterator query(List params, Entity resultEntity) throws SQLException
+    {
+        RowIterator result = null;
+
+        try
+        {
+            Logger.trace("query-params=" + StringLists.join(params, ","));
+            if(params != null)
+            {
+                setParams(params);
+            }
+            connection.enterBusyState();
+            result = new RowIterator(this, preparedStatement.executeQuery(), resultEntity);
+            return result;
+        }
+        finally
+        {
+            connection.leaveBusyState();
+            if(result == null)
+            {
+                notifyOver();
+            }
+        }
+    }
+
+    /**
+     * get a scalar result from this statement.
+     *
+     * @param params parameter values
+     * @exception SQLException thrown bu the database engine
+     * @return scalar result
+     */
+    public synchronized Object evaluate(List params) throws SQLException
+    {
+        Object value = null;
+
+        try
+        {
+            Logger.trace("evaluate-params=" + StringLists.join(params, ","));
+            if(params != null)
+            {
+                setParams(params);
+            }
+            connection.enterBusyState();
+            resultSet = preparedStatement.executeQuery();
+
+            boolean hasNext = resultSet.next();
+
+            if(hasNext)
+            {
+                value = resultSet.getObject(1);
+                if(resultSet.wasNull())
+                {
+                    value = null;
+                }
+            }
+        }
+        finally
+        {
+            connection.leaveBusyState();
+            notifyOver();
+        }
+        return value;
+    }
+
+    /**
+     * issue the modification query of this prepared statement.
+     *
+     * @param params parameter values
+     * @exception SQLException thrown by the database engine
+     * @return the numer of affected rows
+     */
+    public synchronized int update(List params) throws SQLException
+    {
+        try
+        {
+            Logger.trace("update-params=" + StringLists.join(params, ","));
+            setParams(params);
+            connection.enterBusyState();
+
+            int rows = preparedStatement.executeUpdate();
+
+            return rows;
+        }
+        finally
+        {
+            connection.leaveBusyState();
+            notifyOver();
+        }
+    }
+
+    /**
+     * get the object value of the specified resultset column.
+     *
+     * @param key the name of the resultset column
+     * @exception SQLException thrown by the database engine
+     * @return the object value returned by jdbc
+     */
+    public synchronized Object get(Object key) throws SQLException
+    {
+        if(!(key instanceof String))
+        {
+            return null;
+        }
+
+        Object ret = resultSet.getObject((String)key);
+
+        if(entity != null && entity.isObfuscated((String)key))
+        {
+            ret = entity.obfuscate((ret));
+        }
+        return ret;
+    }
+
+    public Set<String> keySet() throws SQLException
+    {
+        return new HashSet<String>(SqlUtil.getColumnNames(resultSet));
+    }
+
+    /**
+     * get the last insert id.
+     *
+     * @exception SQLException thrown by the database engine
+     * @return the last insert id
+     */
+    public synchronized long getLastInsertID() throws SQLException
+    {
+        return((ConnectionWrapper)connection).getLastInsertId(preparedStatement);
+    }
+
+    /**
+     * close this statement.
+     *
+     * @exception SQLException thrown by the database engine
+     */
+    public synchronized void close() throws SQLException
+    {
+        if(preparedStatement != null)
+        {
+            preparedStatement.close();
+        }
+    }
+
+    /**
+     * get statement Connection.
+     *
+     *  @return the Connection object (usually a ConnectionWrapper object)
+     */
+    public ConnectionWrapper getConnection()
+    {
+        return connection;
+    }
+
+    /**
+     * set prepared parameter values.
+     *
+     * @param params parameter values
+     * @exception SQLException thrown by the database engine
+     */
+    private void setParams(List params) throws SQLException
+    {
+        for(int i = 0; i < params.size(); i++)
+        {
+            Object param = params.get(i);
+
+            if(valueParserSubClass != null && valueParserSubClass.isAssignableFrom(param.getClass()))
+            {
+                param = param.toString();
+            }
+            preparedStatement.setObject(i + 1, param);
+        }
+    }
+
+    private List columnNames = null;
+
+    /**
+     * wrapped prepared statement.
+     */
+    private PreparedStatement preparedStatement = null;
+
+    /**
+     * the resulting entity.
+     */
+    private Entity entity = null;
+
+    /**
+     * has meta information been fetched?
+     */
+}

Added: velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PooledSimpleStatement.java
URL: http://svn.apache.org/viewvc/velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PooledSimpleStatement.java?rev=1298906&view=auto
==============================================================================
--- velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PooledSimpleStatement.java (added)
+++ velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PooledSimpleStatement.java Fri Mar  9 16:23:25 2012
@@ -0,0 +1,289 @@
+/*
+ * Copyright 2003 The Apache Software Foundation.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+
+
+package org.apache.velocity.velosurf.sql;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.TreeMap;
+import org.apache.velocity.velosurf.context.RowIterator;
+import org.apache.velocity.velosurf.model.Entity;
+import org.apache.velocity.velosurf.util.Logger;
+
+/**
+ * this class encapsulates a jdbc Statement.
+ *
+ *  @author <a href=mailto:claude.brisson@gmail.com>Claude Brisson</a>
+ *
+ */
+public class PooledSimpleStatement extends PooledStatement
+{
+    /**
+     * build a new PooledStatement.
+     *
+     * @param connection database connection
+     * @param statement wrapped Statement
+     */
+    public PooledSimpleStatement(ConnectionWrapper connection, Statement statement)
+    {
+        this.connection = connection;
+        this.statement = statement;
+    }
+
+    /**
+     * get the resultset for this statement.
+     *
+     * @param query SQL query
+     * @exception SQLException thrown by the database engine
+     * @return resulting RowIterator
+     */
+    public synchronized RowIterator query(String query) throws SQLException
+    {
+        return query(query, null);
+    }
+
+    /**
+     * get the resultset for this statement, specifying the entity the results belong to.
+     *
+     * @param query SQL query
+     * @param resultEntity entity
+     * @exception SQLException thrown by the database engine
+     * @return the resulting RowIterator
+     */
+    public synchronized RowIterator query(String query, Entity resultEntity) throws SQLException
+    {
+        RowIterator result = null;
+
+        try
+        {
+            Logger.trace("query-" + query);
+            connection.enterBusyState();
+            result = new RowIterator(this, statement.executeQuery(query), resultEntity);
+            return result;
+        }
+        finally
+        {
+            connection.leaveBusyState();
+            if(result == null)
+            {
+                notifyOver();
+            }
+        }
+    }
+
+    /**
+     * fetch a single row.
+     *
+     * @param query SQL query
+     * @exception SQLException thrown by the database engine
+     * @return fetched row
+     */
+    public synchronized Object fetch(String query) throws SQLException
+    {
+        return fetch(query, null);
+    }
+
+    /**
+     * fetch a single row, specyfing the entity it belongs to.
+     *
+     * @param query SQL query
+     * @param resultEntity entity
+     * @exception SQLException thrown by the database engine
+     * @return the fetched Instance
+     */
+    public synchronized Object fetch(String query, Entity resultEntity) throws SQLException
+    {
+        try
+        {
+            Logger.trace("fetch-" + query);
+            connection.enterBusyState();
+
+            boolean hasNext = false;
+
+            try
+            {
+                resultSet = statement.executeQuery(query);
+                hasNext = resultSet.next();
+            }
+            finally
+            {
+                connection.leaveBusyState();
+            }
+
+            Map<String, Object> row = null;
+
+            if(hasNext)
+            {
+                if(resultEntity != null)
+                {
+                    row = resultEntity.newInstance(new ReadOnlyMap(this), true);
+                }
+                else
+                {
+                    row = new TreeMap<String, Object>();
+                    if(columnNames == null)
+                    {
+                        columnNames = SqlUtil.getColumnNames(resultSet);
+                    }
+                    for(String column : columnNames)
+                    {
+                        Object value = resultSet.getObject(column);
+
+                        row.put(Database.adaptContextCase(column), value);
+                    }
+                }
+            }
+            return row;
+        }
+        finally
+        {
+            notifyOver();
+        }
+    }
+
+    /**
+     * get specified column as an object.
+     *
+     * @param key column
+     * @exception SQLException thrown by the database engine
+     * @return object value
+     */
+    public Object get(Object key) throws SQLException
+    {
+        if(!(key instanceof String))
+        {
+            return null;
+        }
+        return resultSet.getObject((String)key);
+    }
+
+    public Set<String> keySet() throws SQLException
+    {
+        return new HashSet<String>(SqlUtil.getColumnNames(resultSet));
+    }
+
+    /**
+     * evaluates the SQL query as  a scalar.
+     *
+     * @param query SQL query
+     * @exception SQLException thrown by the database engine
+     * @return found scalar
+     */
+    public synchronized Object evaluate(String query) throws SQLException
+    {
+        Logger.trace("evaluate-" + query);
+
+        Object result = null;
+
+        try
+        {
+            connection.enterBusyState();
+            resultSet = statement.executeQuery(query);
+
+            boolean hasNext = resultSet.next();
+
+            if(hasNext)
+            {
+                result = resultSet.getObject(1);
+            }
+        }
+        finally
+        {
+            connection.leaveBusyState();
+            notifyOver();
+        }
+        return result;
+    }
+
+    /**
+     * issue the update contained in the query.
+     *
+     * @param query SQL query
+     * @exception SQLException thrown by the database engine
+     * @return number of affected rows
+     */
+    public synchronized int update(String query) throws SQLException
+    {
+        try
+        {
+            Logger.trace("update-" + query);
+            connection.enterBusyState();
+
+            int result = statement.executeUpdate(query);
+
+            return result;
+        }
+        finally
+        {
+            connection.leaveBusyState();
+            notifyOver();
+        }
+    }
+
+    /**
+     * close this statement.
+     *
+     * @exception SQLException thrown by the database engine
+     */
+    public void close() throws SQLException
+    {
+        if(statement != null)
+        {
+            statement.close();
+        }
+    }
+
+    /**
+     * notify this statement is no more used and can be recycled.
+     */
+    public void notifyOver()
+    {
+        super.notifyOver();
+    }
+
+    /**
+     * gets the last insert id.
+     *
+     * @exception SQLException thrown by the database engine
+     * @return last insert id
+     */
+    public long getLastInsertID() throws SQLException
+    {
+        return((ConnectionWrapper)connection).getLastInsertId(statement);
+    }
+
+    /**
+     * get statement's Connection.
+     *
+     *  @return the Connection object (usually a ConnectionWrapper object)
+     */
+    public ConnectionWrapper getConnection()
+    {
+        return connection;
+    }
+
+    /**
+     * wrapped statement.
+     */
+    private Statement statement = null;
+}

Added: velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PooledStatement.java
URL: http://svn.apache.org/viewvc/velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PooledStatement.java?rev=1298906&view=auto
==============================================================================
--- velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PooledStatement.java (added)
+++ velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PooledStatement.java Fri Mar  9 16:23:25 2012
@@ -0,0 +1,165 @@
+/*
+ * Copyright 2003 The Apache Software Foundation.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+
+
+package org.apache.velocity.velosurf.sql;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.List;
+
+//CB TODO useOver is deprecated - update doc
+
+/**
+ * This abstract class represents a pooled object.<p>
+ * It has two booleans : inUse and useOver (understand : usageOver).<p>
+ * The cycle of those two booleans is the following :<p>
+ * states (inUse - useOver) : (false-false) -> (true-false) -> (true-true) -> [delay] (false-false)
+ *
+ *  @author <a href=mailto:claude.brisson@gmail.com>Claude Brisson</a>
+ *
+ */
+public abstract class PooledStatement implements RowHandler
+{
+    /**
+     * build a new pooled object.
+     */
+    public PooledStatement()
+    {
+        tagTime = System.currentTimeMillis();
+    }
+
+    /**
+     * get the time tag of this pooled object.
+     *
+     * @return the time tag
+     */
+    public long getTagTime()
+    {
+        return tagTime;
+    }
+
+    /**
+     * reset the time tag.
+     */
+    public void resetTagTime()
+    {
+        tagTime = System.currentTimeMillis();
+    }
+
+    /**
+     * notify this object that it is in use.
+     */
+    public void notifyInUse()
+    {
+        inUse = true;
+        resetTagTime();
+    }
+
+    /**
+     * notify this object that it is no more in use.
+     */
+    public void notifyOver()
+    {
+        try
+        {
+            if(resultSet != null &&!resultSet.isClosed())
+            {
+                resultSet.close();
+                resultSet = null;
+            }
+        }
+        catch(SQLException sqle) {}    // ignore
+        resultSet = null;
+        inUse = false;
+    }
+
+    /**
+     * check whether this pooled object is in use.
+     *
+     * @return whether this object is in use
+     */
+    public boolean isInUse()
+    {
+        return inUse;
+    }
+
+    /**
+     * check whether this pooled object is marked as valid or invalid.
+     * (used in the recovery process)
+     *
+     * @return whether this object is in use
+     */
+    public boolean isValid()
+    {
+        return valid;
+    }
+
+    /**
+     * definitely mark this statement as meant to be deleted.
+     */
+    public void setInvalid()
+    {
+        valid = false;
+    }
+
+    /**
+     * get the connection used by this statement.
+     *
+     * @return the connection used by this statement
+     */
+    public abstract ConnectionWrapper getConnection();
+
+    /**
+     * close this pooled object.
+     *
+     * @exception SQLException when thrown by the database engine
+     */
+    public abstract void close() throws SQLException;
+
+    /**
+     * time tag.
+     */
+    private long tagTime = 0;
+
+    // states (inUse - useOver) : (false-false) -> (true-false) -> (true-true) -> [delay] (false-false)
+
+    /**
+     * valid statement?
+     */
+    private boolean valid = true;
+
+    /**
+     * is this object in use?
+     */
+    private boolean inUse = false;
+
+    /**
+     * database connection.
+     */
+    protected ConnectionWrapper connection = null;
+
+    /**
+     * result set.
+     */
+    protected ResultSet resultSet = null;
+
+    /**
+     * column names in natural order.
+     */
+    protected List<String> columnNames = null;
+}

Added: velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PreparedStatementPool.java
URL: http://svn.apache.org/viewvc/velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PreparedStatementPool.java?rev=1298906&view=auto
==============================================================================
--- velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PreparedStatementPool.java (added)
+++ velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/PreparedStatementPool.java Fri Mar  9 16:23:25 2012
@@ -0,0 +1,256 @@
+/*
+ * Copyright 2003 The Apache Software Foundation.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+
+
+package org.apache.velocity.velosurf.sql;
+
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Iterator;
+import java.util.List;
+import org.apache.velocity.velosurf.util.HashMultiMap;
+import org.apache.velocity.velosurf.util.Logger;
+
+/**
+ * This class is a pool of PooledPreparedStatements.
+ *
+ *  @author <a href=mailto:claude.brisson@gmail.com>Claude Brisson</a>
+ *
+ */
+public class PreparedStatementPool implements /* Runnable, */ Pool
+{
+    /**
+     * build a new pool.
+     *
+     * @param connectionPool connection pool
+     */
+    protected PreparedStatementPool(ConnectionPool connectionPool, boolean checkConnections, long checkInterval)
+    {
+        this.connectionPool = connectionPool;
+        this.checkConnections = checkConnections;
+        this.checkInterval = checkInterval;
+
+//      if(checkConnections) checkTimeoutThread = new Thread(this);
+//      checkTimeoutThread.start();
+    }
+
+    /**
+     * get a PooledPreparedStatement associated with this query.
+     *
+     * @param query an SQL query
+     * @exception SQLException thrown by the database engine
+     * @return a valid statement
+     */
+    public synchronized PooledPreparedStatement getPreparedStatement(String query) throws SQLException
+    {
+        Logger.trace("prepare-" + query);
+
+        PooledPreparedStatement statement = null;
+        ConnectionWrapper connection = null;
+        List available = statementsMap.get(query);
+
+        for(Iterator it = available.iterator(); it.hasNext(); )
+        {
+            statement = (PooledPreparedStatement)it.next();
+            if(statement.isValid())
+            {
+                if(!statement.isInUse() &&!(connection = (ConnectionWrapper)statement.getConnection()).isBusy())
+                {
+                    // check connection
+                    if(!checkConnections || System.currentTimeMillis() - connection.getLastUse() < checkInterval
+                        || connection.check())
+                    {
+                        statement.notifyInUse();
+                        return statement;
+                    }
+                    else
+                    {
+                        dropConnection(connection);
+                        it.remove();
+                    }
+                }
+            }
+            else
+            {
+                it.remove();
+            }
+        }
+        if(count == maxStatements)
+        {
+            throw new SQLException("Error: Too many opened prepared statements!");
+        }
+        connection = connectionPool.getConnection();
+        statement = new PooledPreparedStatement(connection,
+            connection.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
+        statementsMap.put(query, statement);
+        statement.notifyInUse();
+        return statement;
+    }
+
+    /**
+     * cycle through statements to check and recycle them.
+     * 
+     * public void run() {
+     *   while (running) {
+     *       try {
+     *           Thread.sleep(checkDelay);
+     *       } catch (InterruptedException e) {}
+     *       long now = System.currentTimeMillis();
+     *       PooledPreparedStatement statement = null;
+     *       for (Iterator it=statementsMap.keySet().iterator();it.hasNext();)
+     *           for (Iterator jt=statementsMap.get(it.next()).iterator();jt.hasNext();) {
+     *               statement = (PooledPreparedStatement)jt.next();
+     *               if (statement.isInUse() && now-statement.getTagTime() > timeout)
+     *                   statement.notifyOver();
+     *           }
+     *   }
+     * }
+     */
+
+    /**
+     * close all statements.
+     */
+    public void clear()
+    {
+        // close all statements
+        for(Iterator it = statementsMap.keySet().iterator(); it.hasNext(); )
+        {
+            for(Iterator jt = statementsMap.get(it.next()).iterator(); jt.hasNext(); )
+            {
+                try
+                {
+                    ((PooledPreparedStatement)jt.next()).close();
+                }
+                catch(SQLException e)
+                {    // don't care now...
+                    Logger.log(e);
+                }
+            }
+        }
+        statementsMap.clear();
+    }
+
+    /*
+     *  drop all statements relative to a specific connection
+     * @param connection the connection
+     */
+    private void dropConnection(Connection connection)
+    {
+        for(Iterator it = statementsMap.keySet().iterator(); it.hasNext(); )
+        {
+            for(Iterator jt = statementsMap.get(it.next()).iterator(); jt.hasNext(); )
+            {
+                PooledPreparedStatement statement = (PooledPreparedStatement)jt.next();
+
+                try
+                {
+                    statement.close();
+                }
+                catch(SQLException sqle) {}
+                statement.setInvalid();
+            }
+        }
+        try
+        {
+            connection.close();
+        }
+        catch(SQLException sqle) {}
+    }
+
+    /**
+     * clear statements on exit.
+     */
+    protected void finalize()
+    {
+        clear();
+    }
+
+    /**
+     * debug - get usage statistics.
+     *
+     * @return an int array : [nb of statements in use , total nb of statements]
+     */
+    public int[] getUsageStats()
+    {
+        int[] stats = new int[] { 0, 0 };
+
+        for(Iterator it = statementsMap.keySet().iterator(); it.hasNext(); )
+        {
+            for(Iterator jt = statementsMap.get(it.next()).iterator(); jt.hasNext(); )
+            {
+                if(!((PooledPreparedStatement)jt.next()).isInUse())
+                {
+                    stats[0]++;
+                }
+            }
+        }
+        stats[1] = statementsMap.size();
+        return stats;
+    }
+
+    /**
+     * connection pool.
+     */
+    private ConnectionPool connectionPool;
+
+    /**
+     * statements count.
+     */
+    private int count = 0;
+
+    /**
+     * map queries -> statements.
+     */
+    private HashMultiMap statementsMap = new HashMultiMap();    // query -> PooledPreparedStatement
+
+    /**
+     * running thread.
+     */
+    private Thread checkTimeoutThread = null;
+
+    /**
+     * true if running.
+     */
+    private boolean running = true;
+
+    /**
+     * do we need to check connections?
+     */
+    private boolean checkConnections = true;
+
+    /**
+     * minimal check interval
+     */
+    private long checkInterval;
+
+    /**
+     * check delay.
+     */
+
+//  private static final long checkDelay = 30*1000;
+
+    /**
+     * after this timeout, statements are recycled even if not closed.
+     */
+//  private static final long timeout = 60*60*1000;
+
+    /**
+     * max number of statements.
+     */
+    private static final int maxStatements = 50;
+}

Added: velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/ReadOnlyMap.java
URL: http://svn.apache.org/viewvc/velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/ReadOnlyMap.java?rev=1298906&view=auto
==============================================================================
--- velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/ReadOnlyMap.java (added)
+++ velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/ReadOnlyMap.java Fri Mar  9 16:23:25 2012
@@ -0,0 +1,170 @@
+/*
+ * Copyright 2003 The Apache Software Foundation.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+
+
+package org.apache.velocity.velosurf.sql;
+
+import java.sql.SQLException;
+import java.util.Collection;
+import java.util.Map;
+import java.util.Set;
+import org.apache.velocity.velosurf.util.Logger;
+
+/**
+ * A wrapper implementing the Map interface for some objets having only getters.
+ * Only <code>get(key)</code> and <code>keySet()</code> are implemented.
+ *
+ *  @author <a href=mailto:claude.brisson@gmail.com>Claude Brisson</a>
+ */
+public class ReadOnlyMap implements Map<String, Object>
+{
+    /**
+     * Constructor.
+     * @param source the wrapped row handler
+     */
+    public ReadOnlyMap(RowHandler source)
+    {
+        this.source = source;
+    }
+
+    /**
+     * Not implemented.
+     * @return 0
+     */
+    public int size()
+    {
+        return 0;
+    }
+
+    /**
+     * Not implemented.
+     * @return false
+     */
+    public boolean isEmpty()
+    {
+        return false;
+    }
+
+    /**
+     * Not implemented.
+     * @param key
+     * @return false
+     */
+    public boolean containsKey(Object key)
+    {
+        return false;
+    }
+
+    /**
+     * Not implemented.
+     * @param value
+     * @return false
+     */
+    public boolean containsValue(Object value)
+    {
+        return false;
+    }
+
+    /**
+     * Get a value by key.
+     * @param key value key
+     * @return value
+     */
+    public Object get(Object key)
+    {
+        try
+        {
+            return source.get((String)key);
+        }
+        catch(SQLException sqle)
+        {
+            Logger.error("Could not retrieve value of key " + key + " on a " + source.getClass().getName());
+            Logger.log(sqle);
+            return null;
+        }
+    }
+
+    /**
+     * Not implemented.
+     * @param query
+     * @param key
+     * @return null
+     */
+    public Object put(String query, Object key)
+    {
+        return null;
+    }
+
+    /**
+     * Not implemented.
+     * @param key
+     * @return null
+     */
+    public Object remove(Object key)
+    {
+        return null;
+    }
+
+    /**
+     * Not implemented.
+     * @param map
+     */
+    public void putAll(Map<? extends String, ? extends Object> map){}
+
+    /**
+     * Not implemented.
+     */
+    public void clear(){}
+
+    /**
+     * Returns the set of keys.
+     * @return the set of keys
+     */
+    public Set<String> keySet()
+    {
+        try
+        {
+            return source.keySet();
+        }
+        catch(SQLException sqle)
+        {
+            Logger.error("Could not retrieve keyset of a " + source.getClass().getName());
+            Logger.log(sqle);
+            return null;
+        }
+    }
+
+    /**
+     * Not implemented.
+     * @return null
+     */
+    public Collection<Object> values()
+    {
+        return null;
+    }
+
+    /**
+     * Not implemented.
+     * @return null
+     */
+    public Set<Entry<String, Object>> entrySet()
+    {
+        return null;
+    }
+
+    private RowHandler source;
+}

Added: velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/ReverseEngineer.java
URL: http://svn.apache.org/viewvc/velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/ReverseEngineer.java?rev=1298906&view=auto
==============================================================================
--- velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/ReverseEngineer.java (added)
+++ velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/ReverseEngineer.java Fri Mar  9 16:23:25 2012
@@ -0,0 +1,591 @@
+/*
+ * Copyright 2003 The Apache Software Foundation.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+
+
+package org.apache.velocity.velosurf.sql;
+
+import java.sql.DatabaseMetaData;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import org.apache.velocity.velosurf.model.Entity;
+import org.apache.velocity.velosurf.model.ExportedKey;
+import org.apache.velocity.velosurf.model.ImportedKey;
+import org.apache.velocity.velosurf.util.Logger;
+import org.apache.velocity.velosurf.util.StringLists;
+
+/**
+ * Class used to reverse engine a database.
+ */
+public class ReverseEngineer
+{
+    /**
+     * map table->entity, valid only between readConfigFile and readMetaData.
+     */
+    private Map<String, String> entityByTableName = new HashMap<String, String>();
+
+    /** Database. */
+    private Database db;
+
+    /** Driver infos. */
+    private DriverInfo driverInfo;
+
+    /** Reverse mode. */
+    private int reverseMode = DEFAULT_REVERSE_MODE;
+
+    /** No reverse engineering mode. */
+    public static final int REVERSE_NONE = 0;
+
+    /** Partial reverse engineering mode. */
+    public static final int REVERSE_PARTIAL = 1;
+
+    /** Tables reverse engineering mode. */
+    public static final int REVERSE_TABLES = 2;
+
+    /** Full reverse engineering mode. */
+    public static final int REVERSE_FULL = 3;
+
+    /** Default reverse engineering mode. */
+    public static final int DEFAULT_REVERSE_MODE = REVERSE_FULL;
+
+    /** Reverse engineering mode names. */
+    public static String[] reverseModeName = { "none", "partial", "tables", "full" };
+
+    /**
+     * constructor.
+     *
+     * @param database database
+     */
+    public ReverseEngineer(Database database)
+    {
+        db = database;
+    }
+
+    /**
+     * Driver infos setter.
+     *
+     * @param di driver infos
+     */
+    protected void setDriverInfo(DriverInfo di)
+    {
+        driverInfo = di;
+    }
+
+    /**
+     * Set reverse mode.
+     * @param reverseMethod reverse mode
+     */
+    protected void setReverseMode(int reverseMethod)
+    {
+        reverseMode = reverseMethod;
+    }
+
+    /**
+     * add a table &lt;-&gt; entity matching.
+     * @param tableName
+     * @param entity
+     */
+    protected void addTableMatching(String tableName, Entity entity)
+    {
+        if(db.getSchema() != null)
+        {
+            tableName = db.getSchema() + "." + tableName;
+        }
+        entityByTableName.put(tableName, entity.getName());
+    }
+
+    /**
+     * read the meta data from the database.
+     *
+     * @exception java.sql.SQLException thrown by the database engine
+     */
+    protected void readMetaData() throws SQLException
+    {
+        DatabaseMetaData meta = db.getConnection().getMetaData();
+        ResultSet tables = null;
+
+        // extra debug information about which jdbc driver we are using
+        Logger.info("Database Product Name:    " + meta.getDatabaseProductName());
+        Logger.info("Database Product Version: " + meta.getDatabaseProductVersion());
+        Logger.info("JDBC Driver Name:         " + meta.getDriverName());
+        Logger.info("JDBC Driver Version:      " + meta.getDriverVersion());
+
+        /* columns and primary keys */
+        try
+        {
+            Logger.debug("reverse enginering: mode = " + reverseModeName[reverseMode]);
+            if(reverseMode == REVERSE_NONE)
+            {
+                return;
+            }
+            switch(reverseMode)
+            {
+                case REVERSE_TABLES :
+                case REVERSE_FULL :
+                    tables = meta.getTables(null, db.getSchema(), null, null);
+                    while(tables.next())
+                    {
+                        String tableName = adaptCase(tables.getString("TABLE_NAME"));
+
+                        if(driverInfo.ignoreTable(tableName) || "SYSTEM TABLE".equals(tables.getString("TABLE_TYPE")))
+                        {
+                            continue;
+                        }
+                        if(db.getSchema() != null)
+                        {
+                            tableName = db.getSchema() + tableName;
+                        }
+
+                        String entityName = entityByTableName.get(tableName);
+                        Entity entity = null;
+
+                        if(entityName != null)
+                        {
+                            entity = db.getEntity(entityName);
+                        }
+                        if(entity == null)
+                        {
+                            entity = db.getEntityCreate(tableName);
+                            entityByTableName.put(tableName, entity.getName());
+                        }
+                        readTableMetaData(meta, entity, tableName);
+                    }
+
+/*                  not any more valid since entityByTableName does now keep mappings TODO alternative
+                                     for(Iterator e = entityByTableName.keySet().iterator();e.hasNext();)
+                                     {
+                                         Logger.warn("table '"+(String)e.next()+"' not found!");
+                                     }
+*/
+                    break;
+                case REVERSE_PARTIAL :
+                    for(Entity entity : db.getEntities().values())
+                    {
+                        String tableName = entity.getTableName();
+                        if(!tableName.equals("velosurf.root"))
+                          readTableMetaData(meta, entity, tableName);
+                    }
+                    break;
+                case REVERSE_NONE :
+                    break;
+            }
+        }
+        finally
+        {
+            if(tables != null)
+            {
+                tables.close();
+            }
+        }
+
+        /* imported and exported keys */
+        if(reverseMode == REVERSE_FULL)
+        {
+            try
+            {
+                tables = meta.getTables(null, db.getSchema(), null, null);
+                while(tables.next())
+                {
+                    String tableName = adaptCase(tables.getString("TABLE_NAME"));
+
+                    if(driverInfo.ignoreTable(tableName))
+                    {
+                        continue;
+                    }
+                    if(db.getSchema() != null)
+                    {
+                        tableName = db.getSchema() + tableName;
+                    }
+
+                    String entityName = entityByTableName.get(tableName);
+                    Entity entity = db.getEntity(entityName);
+
+                    readForeignKeys(meta, entity, tableName);
+                }
+            }
+            finally
+            {
+                if(tables != null)
+                {
+                    tables.close();
+                }
+            }
+        }
+    }
+
+    /**
+     * Read table meta data.
+     *
+     * @param meta database meta data
+     * @param entity corresponding entity
+     * @param tableName table name
+     * @throws SQLException
+     */
+    private void readTableMetaData(DatabaseMetaData meta, Entity entity, String tableName) throws SQLException
+    {
+        List<String> keylist = StringLists.getPopulatedArrayList(10);    // ever seen a primary key with more than 10 columns ?!
+
+        /* read columns */
+        ResultSet cols = null;
+
+        try
+        {
+            cols = meta.getColumns(null, db.getSchema(), tableName, null);
+            while(cols.next())
+            {
+                String column = adaptCase(cols.getString("COLUMN_NAME"));
+
+                entity.addColumn(column, cols.getInt("DATA_TYPE"));
+            }
+        }
+        finally
+        {
+            if(cols != null)
+            {
+                cols.close();
+            }
+        }
+
+        /* read primary keys */
+        ResultSet pks = null;
+
+        try
+        {
+            pks = meta.getPrimaryKeys(null, db.getSchema(), tableName);
+
+            int keysize = 0;
+
+            while(pks.next())
+            {
+                short ord = pks.getShort("KEY_SEQ");
+                String column = adaptCase(pks.getString("COLUMN_NAME"));
+
+                keylist.set(ord - 1, column);
+                keysize++;
+            }
+            for(int k = 0; k < keysize; k++)
+            {
+                entity.addPKColumn(keylist.get(k));
+            }
+        }
+        finally
+        {
+            if(pks != null)
+            {
+                pks.close();
+            }
+        }
+        entity.reverseEnginered();
+    }
+
+    /**
+     * Read foreign keys.
+     * @param meta database meta data
+     * @param entity entity
+     * @param tableName table name
+     * @throws SQLException
+     */
+    private void readForeignKeys(DatabaseMetaData meta, Entity entity, String tableName) throws SQLException
+    {
+        /* read imported keys */
+        if(reverseMode == REVERSE_FULL)
+        {
+            ResultSet iks = null;
+
+            try
+            {
+                iks = meta.getImportedKeys(null, db.getSchema(), tableName);
+
+                short ord;
+                String pkSchema = null, pkTable = null;
+                List<String> fkCols = new ArrayList<String>();
+                List<String> pkCols = new ArrayList<String>();
+
+                while(iks.next())
+                {
+                    ord = iks.getShort("KEY_SEQ");
+                    if(ord == 1)
+                    {
+                        /* save previous key */
+                        if(fkCols.size() > 0)
+                        {
+                            addImportedKey(entity, pkSchema, pkTable, pkCols, fkCols);
+                            fkCols.clear();
+                            pkCols.clear();
+                        }
+                        pkSchema = adaptCase(iks.getString("PKTABLE_SCHEM"));
+                        pkTable = adaptCase(iks.getString("PKTABLE_NAME"));
+                    }
+                    pkCols.add(adaptCase(iks.getString("PKCOLUMN_NAME")));
+                    fkCols.add(adaptCase(iks.getString("FKCOLUMN_NAME")));
+                }
+
+                /* save last key */
+                if(pkCols.size() > 0)
+                {
+                    addImportedKey(entity, pkSchema, pkTable, pkCols, fkCols);
+                }
+            }
+            finally
+            {
+                if(iks != null)
+                {
+                    iks.close();
+                }
+            }
+        }
+
+        /* read exported keys */
+        if(reverseMode == REVERSE_FULL)
+        {
+            ResultSet eks = null;
+
+            try
+            {
+                eks = meta.getExportedKeys(null, db.getSchema(), tableName);
+
+                short ord;
+                String fkSchema = null, fkTable = null;
+                List<String> fkCols = new ArrayList<String>();
+                List<String> pkCols = new ArrayList<String>();
+
+                if(eks != null)
+                {
+                    while(eks.next())
+                    {
+                        ord = eks.getShort("KEY_SEQ");
+                        if(ord == 1)
+                        {
+                            /* save previous key */
+                            if(fkCols.size() > 0)
+                            {
+                                addExportedKey(entity, fkSchema, fkTable, fkCols, pkCols);
+                                fkCols.clear();
+                                pkCols.clear();
+                            }
+                            fkSchema = adaptCase(eks.getString("FKTABLE_SCHEM"));
+                            fkTable = adaptCase(eks.getString("FKTABLE_NAME"));
+                        }
+                        pkCols.add(adaptCase(eks.getString("PKCOLUMN_NAME")));
+                        fkCols.add(adaptCase(eks.getString("FKCOLUMN_NAME")));
+                    }
+
+                    /* save last key */
+                    if(pkCols.size() > 0)
+                    {
+                        addExportedKey(entity, fkSchema, fkTable, fkCols, pkCols);
+                    }
+                }
+            }
+            finally
+            {
+                if(eks != null)
+                {
+                    eks.close();
+                }
+            }
+        }
+    }
+
+    /**
+     * Add a new exported key.
+     * @param entity target entity
+     * @param fkSchema foreign key schema
+     * @param fkTable foreign key table
+     * @param fkCols foreign key columns
+     * @param pkCols primary key columns
+     */
+    private void addExportedKey(Entity entity, String fkSchema, String fkTable, List<String> fkCols,
+                                List<String> pkCols)
+    {
+        String fkEntityName = getEntityByTable(fkSchema, fkTable);
+
+        if(fkEntityName == null)
+        {
+            Logger.warn("reverse: ignoring exported key " + fkSchema + "." + fkTable
+                        + ": corresponding entity not found.");
+        }
+        else
+        {
+            if(fkSchema != null)
+            {
+                fkTable = fkSchema + "." + fkTable;
+            }
+
+            Entity fkEntity = db.getEntity(fkEntityName);
+
+            fkCols = sortColumns(entity.getPKCols(), pkCols, fkCols);
+
+//          Logger.trace("reverse: found exported key: "+entity.getName()+"("+StringLists.join(pkCols,",")+") <- "+fkTable+"("+StringLists.join(fkCols,",")+")");
+            ExportedKey definedKey = entity.findExportedKey(fkEntity, fkCols);
+
+            if(definedKey == null)
+            {
+                entity.addAttribute(new ExportedKey(getExportedKeyName(fkEntityName), entity, fkTable,
+                    new ArrayList<String>(fkCols)));
+            }
+            else if(definedKey.getFKCols() == null)
+            {
+                definedKey.setFKCols(fkCols);
+            }
+        }
+    }
+
+    /**
+     * Add a new imported key.
+     * @param entity target entity
+     * @param pkSchema primary key schema
+     * @param pkTable primary key table
+     * @param pkCols primary key columns
+     * @param fkCols foreign key columns
+     */
+    private void addImportedKey(Entity entity, String pkSchema, String pkTable, List<String> pkCols,
+                                List<String> fkCols)
+    {
+        String pkEntityName = getEntityByTable(pkSchema, pkTable);
+
+        if(pkEntityName == null)
+        {
+            Logger.warn("reverse: ignoring imported key " + pkSchema + "." + pkTable
+                        + ": corresponding entity not found.");
+        }
+        else
+        {
+/*            if(pkSchema != null) {
+                pkTable = pkSchema+"."+pkTable;
+            }
+*/
+            Entity pkEntity = db.getEntity(pkEntityName);
+
+            fkCols = sortColumns(pkEntity.getPKCols(), pkCols, fkCols);
+
+            // Logger.trace("reverse: found imported key: "+entity.getName()+"("+StringLists.join(fkCols,",")+") -> "+pkTable+"("+StringLists.join(pkCols,",")+")");
+            ImportedKey definedKey = entity.findImportedKey(pkEntity, fkCols);
+
+            if(definedKey == null)
+            {
+                entity.addAttribute(new ImportedKey(pkEntityName, entity, pkEntityName, new ArrayList<String>(fkCols)));
+            }
+            else if(definedKey.getFKCols() == null)
+            {
+                definedKey.setFKCols(fkCols);
+            }
+        }
+    }
+
+    /**
+     * Get an entity by its table name.
+     *
+     * @param schema schema name
+     * @param table table name
+     * @return entity name
+     */
+    private String getEntityByTable(String schema, String table)
+    {
+        String entityName;
+
+        if(schema == null)
+        {
+            if(db.getSchema() == null)
+            {
+                entityName = entityByTableName.get(table);
+            }
+            else
+            {
+                /* buggy jdbc driver */
+                entityName = entityByTableName.get(db.getSchema() + "." + table);
+            }
+        }
+        else
+        {
+            if(db.getSchema() == null)
+            {
+                /*
+                 *  means the jdbc driver is using a default schema name,
+                 * like PUBLIC for hsqldb (OR that we want an instance in another database connexion. TODO)
+                 */
+                entityName = entityByTableName.get(schema + "." + table);
+                if(entityName == null)
+                {
+                    entityName = entityByTableName.get(table);
+                }
+            }
+            else
+            {
+                /* what if not equal? rather strange... */
+                if(schema.equals(db.getSchema()))
+                {
+                    Logger.error("reverse: was expecting schema '" + db.getSchema() + "' and got schema '" + schema
+                                 + "'! Please report this error.");
+                }
+                entityName = entityByTableName.get(schema + "." + table);
+            }
+        }
+        return entityName;
+    }
+
+    /**
+     * Sort columns in <code>target</code> the same way <code>unordered</code> would have to
+     * be sorted to be like <code>ordered</code>.
+     * @param ordered ordered list reference
+     * @param unordered unordered list reference
+     * @param target target list
+     * @return sorted target list
+     */
+    private List<String> sortColumns(List<String> ordered, List<String> unordered, List<String> target)
+    {
+        if(ordered.size() == 1)
+        {
+            return target;
+        }
+
+        List<String> sorted = new ArrayList<String>();
+
+        for(String col : ordered)
+        {
+            int i = unordered.indexOf(col);
+
+            assert(i != -1);
+            sorted.add(target.get(i));
+        }
+        return sorted;
+    }
+
+    /**
+     * adapt case
+     *
+     * @param name name to adapt
+     * @return adapted name
+     */
+    private String adaptCase(String name)
+    {
+        return db.adaptCase(name);
+    }
+
+    /**
+     * rough plural calculation.
+     * @param name singular
+     * @return plural
+     */
+    private String getExportedKeyName(String name)
+    {
+        return adaptCase(name.endsWith("s") ? name + "es" : name + "s");
+    }
+}

Added: velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/RowHandler.java
URL: http://svn.apache.org/viewvc/velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/RowHandler.java?rev=1298906&view=auto
==============================================================================
--- velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/RowHandler.java (added)
+++ velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/RowHandler.java Fri Mar  9 16:23:25 2012
@@ -0,0 +1,45 @@
+/*
+ * Copyright 2003 The Apache Software Foundation.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+
+
+package org.apache.velocity.velosurf.sql;
+
+import java.sql.SQLException;
+import java.util.Set;
+
+/**
+ * This interface represents objects having read-only properties
+ *
+ *  @author <a href=mailto:claude.brisson@gmail.com>Claude Brisson</a>
+ *
+ */
+public interface RowHandler
+{
+    /**
+     * get the property named key.
+     *
+     * @param key the name of the property to return
+     * @return the value of the property, or null if not found
+     */
+    public Object get(Object key) throws SQLException;
+
+    /**
+     * Get keys set.
+     * @return keys set
+     */
+    public Set<String> keySet() throws SQLException;
+}

Added: velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/SqlUtil.java
URL: http://svn.apache.org/viewvc/velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/SqlUtil.java?rev=1298906&view=auto
==============================================================================
--- velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/SqlUtil.java (added)
+++ velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/SqlUtil.java Fri Mar  9 16:23:25 2012
@@ -0,0 +1,221 @@
+/*
+ * Copyright 2003 The Apache Software Foundation.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+
+
+package org.apache.velocity.velosurf.sql;
+
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+import org.apache.velocity.velosurf.util.Logger;
+import org.apache.velocity.velosurf.util.StringLists;
+
+/**
+ * various SQL-related helpers.
+ *
+ *  @author <a href=mailto:claude.brisson@gmail.com>Claude Brisson</a>
+ *
+ */
+public class SqlUtil
+{
+    // that's crazy to have to code such a method...
+    // in Ruby for instance, it's :
+    // '*' * n
+    private static String stars(int length)
+    {
+        StringBuffer ret = new StringBuffer(length);
+
+        for(int i = 0; i < length; i++)
+        {
+            ret.append('*');
+        }
+        return ret.toString();
+    }
+
+    /**
+     *  add seach criteria to a query
+     * @param query query
+     * @param criteriaList list of criteria
+     * @return new query
+     */
+    public static String refineQuery(String query, List criteriaList)
+    {
+        if(criteriaList == null || criteriaList.size() == 0)
+        {
+            return query;
+        }
+        try
+        {
+            Logger.trace("refining query: " + query);
+
+            /*
+             *  issue all searches on a string where all constant strings
+             * (inside quotes) and subqueries (inside parenthesis) have been filtered
+             */
+
+            StringBuffer buffer = new StringBuffer(query.toLowerCase());
+            Matcher matcher = Pattern.compile("'[^']+'").matcher(buffer);
+
+            while(matcher.find())
+            {
+                int start = matcher.start();
+                int end = matcher.end();
+
+                buffer.replace(start, end, stars(end - start));
+            }
+
+            Pattern pattern = Pattern.compile("\\([^()]+\\)");
+
+            while(true)
+            {
+                matcher = pattern.matcher(buffer);
+                if(matcher.find())
+                {
+                    int start = matcher.start();
+                    int end = matcher.end();
+
+                    buffer.replace(start, end, stars(end - start));
+                }
+                else
+                {
+                    break;
+                }
+            }
+
+            Matcher where = Pattern.compile("\\Wwhere\\W").matcher(buffer);
+            Matcher groupby = Pattern.compile("\\Wgroup\\W+by\\W").matcher(buffer);
+            Matcher orderby = Pattern.compile("\\Worder\\W+by\\W").matcher(buffer);
+            int after = query.length();
+
+            if(groupby.find())
+            {
+                after = groupby.start();
+            }
+            if(orderby.find())
+            {
+                after = Math.min(after, orderby.start());
+            }
+
+            String criteria = " (" + StringLists.join(criteriaList, " ) and ( ") + ") ";
+
+            if(where.find())
+            {
+                // a little check
+                if(where.start() > after)
+                {
+                    throw new Exception("Error: 'where' clause found after 'order by' or 'group by' clause");
+                }
+                query = query.substring(0, where.end()) + " ( " + query.substring(where.end(), after) + ") and "
+                        + criteria + query.substring(after);
+            }
+            else
+            {
+                query = query.substring(0, after) + " where " + criteria + query.substring(after);
+            }
+            Logger.trace("refined query: " + query);
+            return query;
+        }
+        catch(Exception ree)
+        {
+            Logger.warn("Could not refine query: " + query);
+            Logger.log(ree);
+            return query;
+        }
+    }
+
+    /**
+     * add an ordering clause to a query
+     *
+     * @param query initial query
+     * @param order order clause
+     * @return ordered query
+     */
+    public static String orderQuery(String query, String order)
+    {
+        if(order == null || order.length() == 0)
+        {
+            return query;
+        }
+        try
+        {
+            /*
+             *  issue all searches on a string where all constant strings
+             * (inside quotes) and subqueries (inside parenthesis) have been filtered
+             */
+            StringBuffer buffer = new StringBuffer(query.toLowerCase());
+            Matcher matcher = Pattern.compile("'[^']+'").matcher(buffer);
+
+            while(matcher.find())
+            {
+                int start = matcher.start();
+                int end = matcher.end();
+
+                buffer.replace(start, end, stars(end - start));
+            }
+            matcher = Pattern.compile("\\([^()]+\\)").matcher(buffer);
+            while(matcher.find())
+            {
+                int start = matcher.start();
+                int end = matcher.end();
+
+                buffer.replace(start, end, stars(end - start));
+            }
+
+            Matcher orderby = Pattern.compile("\\Worder\\W+by\\W").matcher(buffer);
+
+            if(orderby.find())
+            {
+                Logger.warn("Query has already an 'order by' clause: " + query);
+            }
+            else
+            {
+                query = query + " order by " + order;
+            }
+            return query;
+        }
+        catch(Exception e)
+        {
+            Logger.log(e);
+            return null;    // or query ?
+        }
+    }
+
+    /**
+     * get the column nams of a result set
+     * @param resultSet result set
+     * @return list of columns
+     * @throws SQLException
+     */
+    public static List<String> getColumnNames(ResultSet resultSet) throws SQLException
+    {
+        List<String> columnNames = new ArrayList<String>();
+        ResultSetMetaData meta = resultSet.getMetaData();
+        int count = meta.getColumnCount();
+
+        for(int c = 1; c <= count; c++)
+        {
+            // see http://jira.springframework.org/browse/SPR-3541
+            // columnNames.add(meta.getColumnName(c));
+            columnNames.add(meta.getColumnLabel(c));
+        }
+        return columnNames;
+    }
+}

Added: velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/StatementPool.java
URL: http://svn.apache.org/viewvc/velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/StatementPool.java?rev=1298906&view=auto
==============================================================================
--- velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/StatementPool.java (added)
+++ velocity/sandbox/velosurf/src/java/org/apache/velocity/velosurf/sql/StatementPool.java Fri Mar  9 16:23:25 2012
@@ -0,0 +1,242 @@
+/*
+ * Copyright 2003 The Apache Software Foundation.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+
+
+package org.apache.velocity.velosurf.sql;
+
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.*;
+import org.apache.velocity.velosurf.util.Logger;
+
+/**
+ * This class is a pool of PooledStatements.
+ *
+ *  @author <a href=mailto:claude.brisson@gmail.com>Claude Brisson</a>
+ */
+public class StatementPool implements /* Runnable, */ Pool
+{
+    /**
+     * build a new pool.
+     *
+     * @param connectionPool connection pool
+     */
+    protected StatementPool(ConnectionPool connectionPool, boolean checkConnections, long checkInterval)
+    {
+        this.connectionPool = connectionPool;
+        this.checkConnections = checkConnections;
+        this.checkInterval = checkInterval;
+
+//      if(checkConnections) checkTimeoutThread = new Thread(this);
+//      checkTimeoutThread.start();
+    }
+
+    /**
+     * get a valid statement.
+     *
+     * @exception SQLException thrown by the database engine
+     * @return a valid statement
+     */
+    public synchronized PooledSimpleStatement getStatement() throws SQLException
+    {
+        PooledSimpleStatement statement = null;
+        ConnectionWrapper connection = null;
+
+        for(Iterator<PooledSimpleStatement> it = statements.iterator(); it.hasNext(); )
+        {
+            statement = it.next();
+            if(statement.isValid())
+            {
+                if(!statement.isInUse() &&!(connection = (ConnectionWrapper)statement.getConnection()).isBusy())
+                {
+                    // check connection
+                    if(!checkConnections || System.currentTimeMillis() - connection.getLastUse() < checkInterval
+                        || connection.check())
+                    {
+                        statement.notifyInUse();
+                        return statement;
+                    }
+                    else
+                    {
+                        dropConnection(connection);
+                        it.remove();
+                    }
+                }
+            }
+            else
+            {
+                it.remove();
+            }
+        }
+        if(count == maxStatements)
+        {
+            throw new SQLException("Error: Too many opened statements!");
+        }
+        connection = connectionPool.getConnection();
+        statement = new PooledSimpleStatement(connection,
+            connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
+        statements.add(statement);
+        statement.notifyInUse();
+        return statement;
+    }
+
+    // timeout loop
+
+    /**
+     * run the loop of statements checking and recycling.
+     * 
+     * public void run() {
+     *   while (running) {
+     *       try {
+     *           Thread.sleep(checkDelay);
+     *       } catch (InterruptedException e) {}
+     *       long now = System.currentTimeMillis();
+     *       for(PooledSimpleStatement statement:statements) {
+     *           if (statement.isInUse() && now-statement.getTagTime() > timeout)
+     *               statement.notifyOver();
+     *       }
+     *   }
+     * }
+     */
+
+    /**
+     * debug - two ints long array containing nb of statements in use and total nb of statements.
+     *
+     * @return 2 integers long array
+     */
+    public int[] getUsageStats()
+    {
+        int[] stats = new int[] { 0, 0 };
+
+        for(PooledSimpleStatement statement : statements)
+        {
+            if(!statement.isInUse())
+            {
+                stats[0]++;
+            }
+        }
+        stats[1] = statements.size();
+        return stats;
+    }
+
+    /**
+     * close all statements.
+     */
+    public void clear()
+    {
+        // close all statements
+        for(PooledSimpleStatement statement : statements)
+        {
+            try
+            {
+                statement.close();
+            }
+            catch(SQLException sqle)
+            {    // don't care now...
+                Logger.log(sqle);
+            }
+        }
+        for(Iterator it = statements.iterator(); it.hasNext(); )
+        {
+            statements.clear();
+        }
+    }
+
+    /*
+     *  drop all statements relative to a specific connection
+     * @param connection the connection
+     */
+    private void dropConnection(Connection connection)
+    {
+        for(Iterator it = statements.iterator(); it.hasNext(); )
+        {
+            PooledSimpleStatement statement = (PooledSimpleStatement)it.next();
+
+            try
+            {
+                statement.close();
+            }
+            catch(SQLException sqle) {}
+            statement.setInvalid();
+        }
+        try
+        {
+            connection.close();
+        }
+        catch(SQLException sqle) {}
+    }
+
+    /**
+     * close statements on exit.
+     */
+    protected void finalize()
+    {
+        clear();
+    }
+
+    /**
+     * Connection pool.
+     */
+    private ConnectionPool connectionPool = null;
+
+    /**
+     * number of statements.
+     */
+    private int count = 0;
+
+    /**
+     * statements.
+     */
+    private List<PooledSimpleStatement> statements = new ArrayList<PooledSimpleStatement>();
+
+    /**
+     * timeout checking thread.
+     */
+    private Thread checkTimeoutThread = null;
+
+    /**
+     * is the thread running?
+     */
+    private boolean running = true;
+
+    /**
+     * do we need to check connections?
+     */
+    private boolean checkConnections = true;
+
+    /**
+     * minimal check interval
+     */
+    private long checkInterval;
+
+    /**
+     * delay between checks.
+     */
+
+//  private static final long checkDelay = 30*1000;
+
+    /**
+     * timeout on which statements are automatically recycled if not used.
+     */
+//  private static final long timeout = 10*60*1000;
+
+    /**
+     * maximum number of statements.
+     */
+    private static final int maxStatements = 50;
+}



Mime
View raw message