r867 - in trunk/wikitty-jdbc/src/main: java/org/nuiton/wikitty/jdbc resources
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
participants (1)
-
sletellier＠users.nuiton.org