[Wikitty-commits] r867 - in trunk/wikitty-jdbc/src/main: java/org/nuiton/wikitty/jdbc resources

6 May 2011

Author: sletellier
Date: 2011-05-06 18:37:24 +0200 (Fri, 06 May 2011)
New Revision: 867

Url: http://nuiton.org/repositories/revision/wikitty/867

Log:
#1510 Automatique binary type support by bd on table creation



Removed:
   trunk/wikitty-jdbc/src/main/resources/wikitty-jdbc-query-mysql.properties
Modified:
   trunk/wikitty-jdbc/src/main/java/org/nuiton/wikitty/jdbc/WikittyExtensionStorageJDBC.java
   trunk/wikitty-jdbc/src/main/java/org/nuiton/wikitty/jdbc/WikittyJDBCUtil.java
   trunk/wikitty-jdbc/src/main/java/org/nuiton/wikitty/jdbc/WikittyStorageJDBC.java
   trunk/wikitty-jdbc/src/main/resources/wikitty-jdbc-query.properties

Modified: trunk/wikitty-jdbc/src/main/java/org/nuiton/wikitty/jdbc/WikittyExtensionStorageJDBC.java
===================================================================

--- trunk/wikitty-jdbc/src/main/java/org/nuiton/wikitty/jdbc/WikittyExtensionStorageJDBC.java	2011-05-06 15:59:36 UTC (rev 866)
+++ trunk/wikitty-jdbc/src/main/java/org/nuiton/wikitty/jdbc/WikittyExtensionStorageJDBC.java	2011-05-06 16:37:24 UTC (rev 867)
@@ -33,9 +33,7 @@
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_CLEAR_EXTENSION;
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_DELETE_EXTENSION_ADMIN;
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_DELETE_EXTENSION_DATA;
-import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_CREATION_EXTENSION_ADMIN_TEST;
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_CREATION_EXTENSION_ADMIN;
-import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_CREATION_EXTENSION_DATA_TEST;
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_CREATION_EXTENSION_DATA;
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_INSERT_EXTENSION_ADMIN;
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_INSERT_EXTENSION_DATA;
@@ -119,32 +117,25 @@
     }
 
     protected void createDatabase(WikittyTransaction tx) {
-        Connection connectionTest = WikittyJDBCUtil.getConnection(tx, config);
+        Connection connection  = WikittyJDBCUtil.getConnection(tx, config);
         try {
-            // If test of existance work, no exception and do nothing
-            // if exception try to create databse
-            Statement statementTest = connectionTest.createStatement();
-            statementTest.execute(jdbcQuery.getProperty(QUERY_CREATION_EXTENSION_ADMIN_TEST));
-            statementTest.execute(jdbcQuery.getProperty(QUERY_CREATION_EXTENSION_DATA_TEST));
-        } catch(Exception silentError) {
-            if (log.isInfoEnabled()) {
-                log.info("try to create extension database");
-            }
-            Connection connection = WikittyJDBCUtil.getConnection(tx, config);
-            try {
-                Statement statement = connection.createStatement();
+            Statement statement = connection.createStatement();
+            if (!WikittyJDBCUtil.tableExist(connection, TABLE_EXTENSION_ADMIN)) {
+                if (log.isInfoEnabled()) {
+                    log.info("try to create extension database");
+                }
                 statement.execute(jdbcQuery.getProperty(QUERY_CREATION_EXTENSION_ADMIN));
-                statement.execute(jdbcQuery.getProperty(QUERY_CREATION_EXTENSION_DATA));                
-            } catch (Exception eee) {
-                throw new WikittyException("Can't create table for extension storage", eee);
-            } finally {
-                WikittyJDBCUtil.closeQuietly(connection);
             }
+            if (!WikittyJDBCUtil.tableExist(connection, TABLE_EXTENSION_DATA)) {
+                statement.execute(jdbcQuery.getProperty(QUERY_CREATION_EXTENSION_DATA));
+            }
+        } catch (Exception eee) {
+            throw new WikittyException("Can't create table for extension storage", eee);
         } finally {
-            WikittyJDBCUtil.closeQuietly(connectionTest);
+            WikittyJDBCUtil.closeQuietly(connection);
         }
     }
-    
+
     @Override
     public WikittyEvent store(WikittyTransaction tx,
             Collection<WikittyExtension> extensions)

Modified: trunk/wikitty-jdbc/src/main/java/org/nuiton/wikitty/jdbc/WikittyJDBCUtil.java
===================================================================
--- trunk/wikitty-jdbc/src/main/java/org/nuiton/wikitty/jdbc/WikittyJDBCUtil.java	2011-05-06 15:59:36 UTC (rev 866)
+++ trunk/wikitty-jdbc/src/main/java/org/nuiton/wikitty/jdbc/WikittyJDBCUtil.java	2011-05-06 16:37:24 UTC (rev 867)
@@ -29,10 +29,14 @@
 import java.io.InputStream;
 import java.net.URL;
 import java.sql.Connection;
+import java.sql.DatabaseMetaData;
 import java.sql.PreparedStatement;
+import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Types;
+import java.util.ArrayList;
 import java.util.Arrays;
+import java.util.Collections;
 import java.util.Date;
 import java.util.HashMap;
 import java.util.List;
@@ -47,6 +51,7 @@
 import org.apache.commons.beanutils.BeanUtils;
 
 import org.apache.commons.io.IOUtils;
+import org.apache.commons.lang.ArrayUtils;
 import org.apache.commons.lang.StringUtils;
 import org.apache.commons.logging.Log;
 import org.apache.commons.logging.LogFactory;
@@ -68,6 +73,10 @@
     /** to use log facility, just put in your code: log.info(\"...\"); */
     static private Log log = LogFactory.getLog(WikittyJDBCUtil.class);
 
+    /** TODO sletellier 20110506 : reference all binary types existing */
+    /** reference all binary type known for bd, wikitty will search if type is supported for using it */
+    static final public String[] BINARY_TYPES = new String[]{"bytea", "blob"};
+
     /** extension list column in the wikitty_admin table */
     static final public String COL_EXTENSION  = "extension_list";
     /** version column in the admin tables */
@@ -113,8 +122,6 @@
     static final public String QUERY_SELECT_WHERE_NOTDELETED = "jdbc.queries.select.where.notdeleted";
 
     /** wikitty_admin table creation query property name */
-    static final public String QUERY_CREATION_WIKITTY_ADMIN_TEST =
-            "jdbc.queries.creation.wikitty.admin.test";
     static final public String QUERY_CREATION_WIKITTY_ADMIN =
             "jdbc.queries.creation.wikitty.admin";
     /** wikitty_data column binary test exits query property name */
@@ -123,12 +130,11 @@
     /** wikitty_data column binary creation with alter query property name */
     static final public String QUERY_CREATION_WIKITTY_DATA_ALTER_BINARY =
             "jdbc.queries.creation.wikitty.data.alter.binary";
-    /** wikitty_data table test exists query property name */
-    static final public String QUERY_CREATION_WIKITTY_DATA_TEST =
-            "jdbc.queries.creation.wikitty.data.test";
     /** wikitty_data table creation query property name */
     static final public String QUERY_CREATION_WIKITTY_DATA =
             "jdbc.queries.creation.wikitty.data";
+    static final public String QUERY_CREATION_WIKITTY_DATA_NO_BINARY =
+            "jdbc.queries.creation.wikitty.data.no.binary";
     /** insertion in the admin table query property name */
     static final public String QUERY_INSERT_WIKITTY_ADMIN = "jdbc.queries.insert.wikitty.admin";
     /** update in the admin table query property name */
@@ -151,13 +157,9 @@
     static final public String QUERY_DELETE_EXTENSION_DATA = "jdbc.queries.delete.extension.data";
 
     /** extension_admin table creation query property name */
-    static final public String QUERY_CREATION_EXTENSION_ADMIN_TEST =
-            "jdbc.queries.creation.extension.admin.test";
     static final public String QUERY_CREATION_EXTENSION_ADMIN =
             "jdbc.queries.creation.extension.admin";
     /** extension_data table creation query property name */
-    static final public String QUERY_CREATION_EXTENSION_DATA_TEST =
-            "jdbc.queries.creation.extension.data.test";
     static final public String QUERY_CREATION_EXTENSION_DATA =
             "jdbc.queries.creation.extension.data";
     /** insertion in the admin table query property name */
@@ -360,6 +362,26 @@
         }
     }
 
+    public static boolean tableExist(Connection connection, String tableName) throws SQLException {
+        DatabaseMetaData metaData = connection.getMetaData();
+
+        ResultSet tables = metaData.getTables(null, null, tableName, null);
+
+        return tables.next();
+    }
+
+    public static String getSupportedBinaryType(Connection connection) throws SQLException {
+
+        ResultSet typeInfo = connection.getMetaData().getTypeInfo();
+        while (typeInfo.next()) {
+            String type = typeInfo.getString("TYPE_NAME");
+            if (ArrayUtils.contains(BINARY_TYPES, type)) {
+                return type;
+            }
+        }
+        return null;
+    }
+
     // REMOVED because, we must used WikittyTransaction with jta management
 //    /**
 //     * Closes a connection (i.e. transaction) and commit data.
@@ -401,7 +423,7 @@
 
     /**
      * Execute query.
-     * 
+     *
      * @param connection connection to use
      * @param query sql query to do
      * @param args arguments for the query
@@ -419,5 +441,4 @@
         }
         sta.execute();
     }
-
 }

Modified: trunk/wikitty-jdbc/src/main/java/org/nuiton/wikitty/jdbc/WikittyStorageJDBC.java
===================================================================
--- trunk/wikitty-jdbc/src/main/java/org/nuiton/wikitty/jdbc/WikittyStorageJDBC.java	2011-05-06 15:59:36 UTC (rev 866)
+++ trunk/wikitty-jdbc/src/main/java/org/nuiton/wikitty/jdbc/WikittyStorageJDBC.java	2011-05-06 16:37:24 UTC (rev 867)
@@ -38,11 +38,10 @@
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.COL_VERSION;
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_CLEAR_WIKITTY;
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_CREATION_WIKITTY_ADMIN;
-import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_CREATION_WIKITTY_ADMIN_TEST;
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_CREATION_WIKITTY_DATA_ALTER_BINARY;
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_CREATION_WIKITTY_DATA_TEST_BINARY;
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_CREATION_WIKITTY_DATA;
-import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_CREATION_WIKITTY_DATA_TEST;
+import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_CREATION_WIKITTY_DATA_NO_BINARY;
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_DELETE_WIKITTY_ADMIN;
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_DELETE_WIKITTY_DATA;
 import static org.nuiton.wikitty.jdbc.WikittyJDBCUtil.QUERY_INSERT_WIKITTY_ADMIN;
@@ -62,6 +61,7 @@
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
+import java.sql.Types;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Collection;
@@ -129,13 +129,10 @@
 
             checkTableOrCreation(tx);
 
-            // all time use alter after creation for binaryValue column because
-            // this datatype is not portable
-            checkColumnBinaryOrAlter(tx);
-
             if (txBeginHere) {
                 tx.commit();
             }
+
         } catch (WikittyException eee) {
             if (tx != null && tx.isStarted()) {
                 tx.rollback();
@@ -148,61 +145,34 @@
      * test table existance or create them if necessary
      */
     protected void checkTableOrCreation(WikittyTransaction tx) {
-        Connection connectionTest = WikittyJDBCUtil.getConnection(tx, config);
+        Connection connection = WikittyJDBCUtil.getConnection(tx, config);
         try {
-            // If test of existance work, no exception and do nothing
-            // if exception try to create databse
-            Statement statementTest = connectionTest.createStatement();
-            statementTest.execute(jdbcQuery.getProperty(QUERY_CREATION_WIKITTY_ADMIN_TEST));
-            statementTest.execute(jdbcQuery.getProperty(QUERY_CREATION_WIKITTY_DATA_TEST));
-        } catch (Exception silentError) {
-            if (log.isInfoEnabled()) {
-                log.info("try to create wikitty database");
-            }
-            Connection connection = WikittyJDBCUtil.getConnection(tx, config);
-            try {
-                Statement statement = connection.createStatement();
+            Statement statement = connection.createStatement();
+            if (!WikittyJDBCUtil.tableExist(connection, WikittyJDBCUtil.TABLE_WIKITTY_ADMIN)) {
+                if (log.isInfoEnabled()) {
+                    log.info("try to create wikitty database");
+                }
                 statement.execute(jdbcQuery.getProperty(QUERY_CREATION_WIKITTY_ADMIN));
-                statement.execute(jdbcQuery.getProperty(QUERY_CREATION_WIKITTY_DATA));
-            } catch (Exception eee) {
-                throw new WikittyException("Can't create table for wikitty storage", eee);
-            } finally {
-                WikittyJDBCUtil.closeQuietly(connection);
             }
-        } finally {
-            WikittyJDBCUtil.closeQuietly(connectionTest);
-        }
-    }
+            if (!WikittyJDBCUtil.tableExist(connection, WikittyJDBCUtil.TABLE_WIKITTY_DATA)) {
 
-    /**
-     * Add binary column if necessary
-     * If add can be done, wikitty work for all, except binary type
-     */
-    protected void checkColumnBinaryOrAlter(WikittyTransaction tx) {
-        Connection connectionTest = WikittyJDBCUtil.getConnection(tx, config);
-        try {
-            // If test of existance work, no exception and do nothing
-            // if exception try to create binary column
-            Statement statementTest = connectionTest.createStatement();
-            statementTest.execute(jdbcQuery.getProperty(QUERY_CREATION_WIKITTY_DATA_TEST_BINARY));
-        } catch (Exception silentError) {
-            if (log.isInfoEnabled()) {
-                log.info("try to alter wikitty database to add binary column");
+                // Check if database support blob
+                String supportedBinaryType = WikittyJDBCUtil.getSupportedBinaryType(connection);
+                if (supportedBinaryType == null) {
+                    log.fatal("Can add column to store binary field. You can't use binary");
+                    statement.execute(jdbcQuery.getProperty(QUERY_CREATION_WIKITTY_DATA_NO_BINARY));
+                } else {
+                    if (log.isDebugEnabled()) {
+                        log.debug("Creating wikitty data with '" + supportedBinaryType + "' type for binary");
+                    }
+                    String request = String.format(jdbcQuery.getProperty(QUERY_CREATION_WIKITTY_DATA), supportedBinaryType);
+                    statement.execute(request);
+                }
             }
-            Connection connection = WikittyJDBCUtil.getConnection(tx, config);
-            try {
-                Statement statement = connection.createStatement();
-                statement.execute(jdbcQuery.getProperty(QUERY_CREATION_WIKITTY_DATA_ALTER_BINARY));
-            } catch (Exception eee) {
-                // no exception just log fatal, wikitty can work without this
-                // column but can't store binary. If binary is not used there is
-                // no probleme
-                log.fatal("Can add column to store binary field. You can't use binary", eee);
-            } finally {
-                WikittyJDBCUtil.closeQuietly(connection);
-            }
+        } catch (Exception eee) {
+            throw new WikittyException("Can't create table for wikitty storage", eee);
         } finally {
-            WikittyJDBCUtil.closeQuietly(connectionTest);
+            WikittyJDBCUtil.closeQuietly(connection);
         }
     }
 

Deleted: trunk/wikitty-jdbc/src/main/resources/wikitty-jdbc-query-mysql.properties
===================================================================
--- trunk/wikitty-jdbc/src/main/resources/wikitty-jdbc-query-mysql.properties	2011-05-06 15:59:36 UTC (rev 866)
+++ trunk/wikitty-jdbc/src/main/resources/wikitty-jdbc-query-mysql.properties	2011-05-06 16:37:24 UTC (rev 867)
@@ -1,31 +0,0 @@
-###
-# #%L
-# Wikitty :: wikitty-jdbc-impl
-# 
-# $Id$
-# $HeadURL$
-# %%
-# Copyright (C) 2010 CodeLutin, Benjamin Poussin
-# %%
-# This program is free software: you can redistribute it and/or modify
-# it under the terms of the GNU Lesser General Public License as 
-# published by the Free Software Foundation, either version 3 of the 
-# License, or (at your option) any later version.
-# 
-# This program is distributed in the hope that it will be useful,
-# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-# GNU General Lesser Public License for more details.
-# 
-# You should have received a copy of the GNU General Lesser Public 
-# License along with this program.  If not, see
-# <http://www.gnu.org/licenses/lgpl-3.0.html>.
-# #L%
-###
-#
-# ce fichier surcharge la requete d'ajout de la column binaryValue pour mysql
-# il doit etre charge apres le fichier par defaut dans la sequence de fichier
-#
-
-#table wikitty_data creation query
-jdbc.queries.creation.wikitty.data.alter.binary=ALTER TABLE wikitty_data ADD COLUMN binaryValue blob;

Modified: trunk/wikitty-jdbc/src/main/resources/wikitty-jdbc-query.properties
===================================================================
--- trunk/wikitty-jdbc/src/main/resources/wikitty-jdbc-query.properties	2011-05-06 15:59:36 UTC (rev 866)
+++ trunk/wikitty-jdbc/src/main/resources/wikitty-jdbc-query.properties	2011-05-06 16:37:24 UTC (rev 867)
@@ -31,7 +31,6 @@
 #
 
 #table extension_admin creation query
-jdbc.queries.creation.extension.admin.test=SELECT * FROM extension_admin LIMIT 1;
 jdbc.queries.creation.extension.admin=CREATE TABLE extension_admin (\
 id text NOT NULL,\
 name text NOT NULL,\
@@ -41,7 +40,6 @@
 PRIMARY KEY (id));
 
 #table extension_data creation query
-jdbc.queries.creation.extension.data.test=SELECT * FROM extension_data LIMIT 1;
 jdbc.queries.creation.extension.data=CREATE TABLE extension_data (\
 id text NOT NULL,\
 fieldName text NOT NULL,\
@@ -50,7 +48,6 @@
 FOREIGN KEY (id) REFERENCES extension_admin (id));
 
 #table wikitty_admin creation query
-jdbc.queries.creation.wikitty.admin.test=SELECT * FROM wikitty_admin LIMIT 1;
 jdbc.queries.creation.wikitty.admin=CREATE TABLE  wikitty_admin (\
 id varchar(64) NOT NULL,\
 version varchar(8) NOT NULL,\
@@ -60,9 +57,6 @@
 
 #table wikitty_data creation query
 # all time use alter after creation for binaryValue column because this datatype is not portable
-jdbc.queries.creation.wikitty.data.test.binary=SELECT binaryValue FROM wikitty_data LIMIT 1;
-jdbc.queries.creation.wikitty.data.alter.binary=ALTER TABLE wikitty_data ADD COLUMN binaryValue bytea;
-jdbc.queries.creation.wikitty.data.test=SELECT * FROM wikitty_data LIMIT 1;
 jdbc.queries.creation.wikitty.data=CREATE TABLE wikitty_data (\
 id varchar(64) NOT NULL,\
 fieldName text NOT NULL,\
@@ -70,9 +64,20 @@
 dateValue timestamp,\
 textValue text,\
 booleanValue boolean,\
+binaryValue %s,\
 PRIMARY KEY (id,fieldName),\
 FOREIGN KEY (id) REFERENCES wikitty_admin (id));
 
+jdbc.queries.creation.wikitty.data.no.binary=CREATE TABLE wikitty_data (\
+id varchar(64) NOT NULL,\
+fieldName text NOT NULL,\
+numberValue numeric,\
+dateValue timestamp,\
+textValue text,\
+booleanValue boolean,\
+PRIMARY KEY (id,fieldName),\
+FOREIGN KEY (id) REFERENCES wikitty_admin (id));
+
 #basic selection query without where clause
 jdbc.queries.select=SELECT %s FROM %s;
 #not deleted data selection query without where clause

    

[Wikitty-commits] r867 - in trunk/wikitty-jdbc/src/main: java/org/nuiton/wikitty/jdbc resources

sletellier＠users.nuiton.org