[Git][ultreiaio/ird-t3][develop] 2 commits: improve sql script loading

10 Apr 2017

Tony CHEMIT pushed to branch develop at ultreiaio / ird-t3


Commits:
3162de0a by Tony CHEMIT at 2017-04-10T15:02:50+02:00
improve sql script loading

- - - - -
b70063d4 by Tony CHEMIT at 2017-04-10T15:03:17+02:00
chmod

- - - - -


10 changed files:

- t3-domain/src/main/java/fr/ird/t3/T3SqlScriptsImporter.java
- t3-domain/src/main/java/fr/ird/t3/entities/T3JdbcHelper.java
- − t3-domain/src/main/java/fr/ird/t3/entities/T3ScriptHelper.java
- t3-domain/src/test/java/fr/ird/t3/entities/AbstractDatabaseTest.java
- t3-domain/src/test/java/fr/ird/t3/entities/conversion/WeightCategoryLogBookConvertorProviderTest.java
- t3-domain/src/test/java/fr/ird/t3/entities/reference/LengthWeightConversionHelperTest.java
- t3-domain/src/test/java/fr/ird/t3/entities/reference/WeightCategoryTreatmentImplTest.java
- t3-installer/src/main/assembly/dist/debug.sh
- t3-installer/src/main/java/fr/ird/t3/tools/T3ApplicationInstaller.java
- t3-installer/src/test/java/fr/ird/t3/tools/db/reference/AbstractReferenceImporterIT.java


Changes:

=====================================
t3-domain/src/main/java/fr/ird/t3/T3SqlScriptsImporter.java
=====================================

--- a/t3-domain/src/main/java/fr/ird/t3/T3SqlScriptsImporter.java
+++ b/t3-domain/src/main/java/fr/ird/t3/T3SqlScriptsImporter.java
@@ -24,7 +24,6 @@ package fr.ird.t3;
 import com.google.common.base.Preconditions;
 import com.google.common.collect.ImmutableSet;
 import com.google.common.collect.Lists;
-import fr.ird.t3.entities.T3ScriptHelper;
 import fr.ird.t3.services.T3ServiceContext;
 import java.io.File;
 import java.io.IOException;
@@ -51,13 +50,13 @@ public class T3SqlScriptsImporter {
     /** Logger. */
     private static final Log log = LogFactory.getLog(T3SqlScriptsImporter.class);
 
-    protected final File scriptsDirectory;
+    private final File scriptsDirectory;
 
-    protected ImmutableSet<File> scriptsFile;
+    private ImmutableSet<File> scriptsFile;
 
-    public static final Predicate<File> SKIP_SPECIES_LENGTH_STEP_FILE = input -> !input.getName().toLowerCase().contains("specieslengthstep.sql");
+    static final Predicate<File> SKIP_SPECIES_LENGTH_STEP_FILE = input -> !input.getName().toLowerCase().contains("specieslengthstep.sql");
 
-    public static void importReferential(File buildRootDirectory, T3ServiceContext serviceContext, Predicate<File> filePredicate) throws IOException, TopiaException {
+    static void importReferential(File buildRootDirectory, T3ServiceContext serviceContext, Predicate<File> filePredicate) throws IOException, TopiaException {
 
         Version t3DataVersion = serviceContext.getApplicationConfiguration().getT3DataVersion();
         File scriptsZip = Paths.get(buildRootDirectory.getAbsolutePath())
@@ -92,12 +91,14 @@ public class T3SqlScriptsImporter {
 
     public void prepare() {
 
-        List<String> scriptsName = Lists.newArrayList(scriptsDirectory.list((dir, name) -> name.endsWith(".sql")));
-        Collections.sort(scriptsName);
+        String[] list = scriptsDirectory.list((dir, name) -> name.endsWith(".sql"));
         ImmutableSet.Builder<File> filesBuilder = ImmutableSet.builder();
-
-        for (String ddlScriptName : scriptsName) {
-            filesBuilder.add(new File(scriptsDirectory, ddlScriptName));
+        if (list != null) {
+            List<String> scriptsName = Lists.newArrayList(list);
+            Collections.sort(scriptsName);
+            for (String ddlScriptName : scriptsName) {
+                filesBuilder.add(new File(scriptsDirectory, ddlScriptName));
+            }
         }
         scriptsFile = filesBuilder.build();
     }
@@ -113,15 +114,11 @@ public class T3SqlScriptsImporter {
                 log.info(" o Loading sql script ...(" + scriptFile.getName() + ")");
             }
             if (filePredicate.test(scriptFile)) {
-                loadScript(serviceContext, scriptFile);
+                serviceContext.getApplicationContext().newJdbcHelper().executeSql(scriptFile);
             }
         }
 
     }
 
-    protected void loadScript(T3ServiceContext serviceContext, File script) throws  IOException {
-
-        T3ScriptHelper.loadScript(serviceContext.getApplicationContext(), script);
-    }
 
 }


=====================================
t3-domain/src/main/java/fr/ird/t3/entities/T3JdbcHelper.java
=====================================
--- a/t3-domain/src/main/java/fr/ird/t3/entities/T3JdbcHelper.java
+++ b/t3-domain/src/main/java/fr/ird/t3/entities/T3JdbcHelper.java
@@ -21,12 +21,20 @@ package fr.ird.t3.entities;
  * #L%
  */
 
+import java.io.BufferedReader;
 import java.io.File;
+import java.io.IOException;
+import java.io.InputStream;
+import java.io.InputStreamReader;
+import java.io.Reader;
+import java.nio.charset.StandardCharsets;
+import java.nio.file.Files;
 import java.sql.Connection;
-import java.sql.PreparedStatement;
 import java.sql.SQLException;
+import java.sql.Statement;
 import org.nuiton.topia.persistence.jdbc.JdbcConfiguration;
 import org.nuiton.topia.persistence.jdbc.JdbcHelper;
+import org.nuiton.util.sql.SqlFileReader;
 
 /**
  * Created by tchemit on 03/04/17.
@@ -35,18 +43,63 @@ import org.nuiton.topia.persistence.jdbc.JdbcHelper;
  */
 public class T3JdbcHelper extends JdbcHelper {
 
-    public T3JdbcHelper(JdbcConfiguration jdbcConfiguration) {
+    public static final int BUZZER_SIZE = 100;
+
+    T3JdbcHelper(JdbcConfiguration jdbcConfiguration) {
         super(jdbcConfiguration);
     }
 
-    public void executeSql(String sql) {
+    public void executeSql(File file) {
+
+        try (BufferedReader bufferedReader = Files.newBufferedReader(file.toPath(), StandardCharsets.UTF_8)) {
+            executeSql(bufferedReader);
+        } catch (IOException e) {
+            throw new RuntimeException(e);
+        }
+
+    }
+
+    public void executeSql(InputStream file) {
+
+        try (InputStreamReader bufferedReader = new InputStreamReader(file, StandardCharsets.UTF_8)) {
+            executeSql(bufferedReader);
+        } catch (IOException e) {
+            throw new RuntimeException(e);
+        }
+
+    }
+
+    public void executeSql(Reader reader) {
+
         try (Connection connection = openConnection()) {
-            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
-                preparedStatement.execute();
+            try (Statement preparedStatement = connection.createStatement()) {
+
+                int currentBuffer = 0;
+                StringBuilder currentStatement = new StringBuilder();
+                for (String sqlStatement : new SqlFileReader(reader)) {
+                    if (sqlStatement.startsWith("--")) {
+                        continue;
+                    }
+                    currentStatement.append("\n").append(sqlStatement);
+                    if (sqlStatement.trim().endsWith(";")) {
+                        preparedStatement.addBatch(currentStatement.toString());
+                        currentBuffer++;
+                        currentStatement = new StringBuilder();
+                    }
+
+                    if (currentBuffer % BUZZER_SIZE == 0) {
+                        preparedStatement.executeBatch();
+                        preparedStatement.clearBatch();
+                    }
+                }
+                preparedStatement.executeBatch();
+                preparedStatement.clearBatch();
             }
         } catch (SQLException e) {
             throw new RuntimeException(e);
         }
+
+
     }
 
     public void backup(File file, boolean compress) {


=====================================
t3-domain/src/main/java/fr/ird/t3/entities/T3ScriptHelper.java deleted
=====================================
--- a/t3-domain/src/main/java/fr/ird/t3/entities/T3ScriptHelper.java
+++ /dev/null
@@ -1,138 +0,0 @@
-/*
- * #%L
- * T3 :: Domain
- * %%
- * Copyright (C) 2010 - 2017 IRD, Code Lutin, Ultreia.io
- * %%
- * This program is free software: you can redistribute it and/or modify
- * it under the terms of the GNU Affero 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 Public License for more details.
- * 
- * You should have received a copy of the GNU Affero General Public License
- * along with this program.  If not, see <http://www.gnu.org/licenses/>.
- * #L%
- */
-package fr.ird.t3.entities;
-
-import com.google.common.base.Charsets;
-import java.beans.PropertyChangeEvent;
-import java.beans.PropertyChangeListener;
-import java.io.BufferedReader;
-import java.io.File;
-import java.io.FileInputStream;
-import java.io.IOException;
-import java.io.InputStream;
-import java.io.InputStreamReader;
-import java.sql.Connection;
-import java.sql.SQLException;
-import java.sql.Statement;
-import org.apache.commons.io.IOUtils;
-import org.apache.commons.logging.Log;
-import org.apache.commons.logging.LogFactory;
-import org.nuiton.topia.persistence.TopiaException;
-import org.nuiton.topia.persistence.support.TopiaSqlWork;
-import org.nuiton.util.sql.SqlFileReader;
-
-/**
- * Usefull method to deal with sql scripts.
- *
- * @author Tony Chemit - dev@tchemit.fr
- * @since 1.0
- */
-public class T3ScriptHelper {
-
-    /** Logger. */
-    private static final Log log = LogFactory.getLog(T3ScriptHelper.class);
-
-    public static void exportDatabase(T3TopiaApplicationContext rootTx, File outputFile) throws TopiaException {
-
-        if (log.isInfoEnabled()) {
-            log.info("Export database to file " + outputFile);
-        }
-        rootTx.newJdbcHelper().backup(outputFile, false);
-    }
-
-    public static void loadReferentiel(T3TopiaApplicationContext tx, String location) throws IOException, TopiaException {
-        try (InputStream resourceAsStream = T3EntityHelper.class.getResourceAsStream(location)) {
-            loadScript(tx, resourceAsStream);
-        }
-    }
-
-    public static void loadScript(T3TopiaApplicationContext tx, InputStream resourceAsStream) throws IOException, TopiaException {
-
-        String script = IOUtils.toString(resourceAsStream, Charsets.UTF_8.name());
-
-        tx.newJdbcHelper().executeSql(script);
-
-    }
-
-    public static void loadScript(T3TopiaApplicationContext tx, File file) throws IOException, TopiaException {
-
-        try (InputStream resourceAsStream = new FileInputStream(file)) {
-            loadScript(tx, resourceAsStream);
-        }
-    }
-
-    public static void loadScriptLineByLine(T3TopiaApplicationContext tx, File script, PropertyChangeListener listener) throws TopiaException, IOException {
-
-        try (BufferedReader stream = new BufferedReader(new InputStreamReader(new FileInputStream(script), Charsets.UTF_8))) {
-
-            try (T3TopiaPersistenceContext persistenceContext = tx.newPersistenceContext()) {
-
-                persistenceContext.getSqlSupport().doSqlWork(new TopiaSqlWork() {
-
-                    final int batchSize = 100;
-                    int lineCount = 0;
-
-                    void flushStatement(Statement statement) throws SQLException {
-
-                        statement.executeBatch();
-                        statement.clearBatch();
-
-                    }
-
-                    @Override
-                    public void execute(Connection connection) throws SQLException {
-                        try (Statement statement = connection.createStatement()) {
-
-                            int batchSize = 0;
-
-                            for (String statementStr : new SqlFileReader(stream)) {
-
-                                String trimLine = statementStr.trim();
-
-                                if (trimLine.isEmpty() || trimLine.startsWith("--")) {
-                                    continue;
-                                }
-                                if (log.isDebugEnabled()) {
-                                    log.debug("Inject line : " + trimLine);
-                                }
-                                listener.propertyChange(new PropertyChangeEvent(script, "newLine", null, lineCount++));
-                                statement.addBatch(trimLine);
-                                batchSize++;
-
-                                if (batchSize % this.batchSize == 0) {
-                                    flushStatement(statement);
-                                }
-
-                            }
-
-                            flushStatement(statement);
-                        }
-                    }
-                });
-            }
-        }
-
-    }
-
-    protected T3ScriptHelper() {
-        // hide helper constructor
-    }
-}


=====================================
t3-domain/src/test/java/fr/ird/t3/entities/AbstractDatabaseTest.java
=====================================
--- a/t3-domain/src/test/java/fr/ird/t3/entities/AbstractDatabaseTest.java
+++ b/t3-domain/src/test/java/fr/ird/t3/entities/AbstractDatabaseTest.java
@@ -22,6 +22,8 @@ package fr.ird.t3.entities;
 
 import fr.ird.t3.T3IOUtil;
 import java.io.File;
+import java.io.IOException;
+import java.io.InputStream;
 import org.junit.Rule;
 import org.junit.rules.TestWatcher;
 import org.junit.runner.Description;
@@ -29,8 +31,6 @@ import org.nuiton.topia.persistence.TopiaException;
 
 public abstract class AbstractDatabaseTest {
 
-    public static final long TIMESTAMP = System.nanoTime();
-
     @Rule
     public T3Database db = new T3Database();
 
@@ -71,16 +71,16 @@ public abstract class AbstractDatabaseTest {
             T3EntityHelper.releaseRootContext(rootCtxt);
         }
 
-        public File getTestBasedir() {
-            return testBasedir;
-        }
-
-        public T3TopiaApplicationContext getRootCtxt() {
-            return rootCtxt;
-        }
-
         public T3TopiaPersistenceContext beginTransaction() throws TopiaException {
             return rootCtxt.newPersistenceContext();
         }
+
+        public void loadClassPathScript(String location) {
+            try (InputStream resourceAsStream = getClass().getResourceAsStream(location)) {
+                rootCtxt.newJdbcHelper().executeSql(resourceAsStream);
+            } catch (IOException e) {
+                throw new TopiaException(e);
+            }
+        }
     }
 }


=====================================
t3-domain/src/test/java/fr/ird/t3/entities/conversion/WeightCategoryLogBookConvertorProviderTest.java
=====================================
--- a/t3-domain/src/test/java/fr/ird/t3/entities/conversion/WeightCategoryLogBookConvertorProviderTest.java
+++ b/t3-domain/src/test/java/fr/ird/t3/entities/conversion/WeightCategoryLogBookConvertorProviderTest.java
@@ -21,7 +21,6 @@
 package fr.ird.t3.entities.conversion;
 
 import fr.ird.t3.entities.AbstractDatabaseTest;
-import fr.ird.t3.entities.T3ScriptHelper;
 import fr.ird.t3.entities.T3TopiaPersistenceContext;
 import org.junit.Assert;
 import org.junit.Test;
@@ -37,13 +36,10 @@ public class WeightCategoryLogBookConvertorProviderTest extends AbstractDatabase
     @Test
     public void newInstance() throws Exception {
 
-        T3ScriptHelper.loadReferentiel(
-                db.getRootCtxt(),
-                "/db/WeightCategoryLogBookConvertorProviderTest.sql");
+        db.loadClassPathScript("/db/WeightCategoryLogBookConvertorProviderTest.sql");
 
-        try (T3TopiaPersistenceContext tx= beginTransaction()) {
-            WeightCategoryLogBookConvertorProvider provider =
-                    WeightCategoryLogBookConvertorProvider.newInstance(tx);
+        try (T3TopiaPersistenceContext tx = beginTransaction()) {
+            WeightCategoryLogBookConvertorProvider provider = WeightCategoryLogBookConvertorProvider.newInstance(tx);
             Assert.assertNotNull(provider);
         }
 


=====================================
t3-domain/src/test/java/fr/ird/t3/entities/reference/LengthWeightConversionHelperTest.java
=====================================
--- a/t3-domain/src/test/java/fr/ird/t3/entities/reference/LengthWeightConversionHelperTest.java
+++ b/t3-domain/src/test/java/fr/ird/t3/entities/reference/LengthWeightConversionHelperTest.java
@@ -26,7 +26,6 @@ import com.google.common.collect.Lists;
 import com.google.common.collect.Range;
 import com.google.common.collect.Sets;
 import fr.ird.t3.entities.AbstractDatabaseTest;
-import fr.ird.t3.entities.T3ScriptHelper;
 import fr.ird.t3.entities.T3TopiaPersistenceContext;
 import fr.ird.t3.entities.type.T3Date;
 import java.util.Date;
@@ -49,8 +48,7 @@ public class LengthWeightConversionHelperTest extends AbstractDatabaseTest {
     @Before
     public void setUp() throws Exception {
 
-            T3ScriptHelper.loadReferentiel(
-                    db.getRootCtxt(), "/db/LengthWeightConversionHelperTest.sql");
+            db.loadClassPathScript("/db/LengthWeightConversionHelperTest.sql");
     }
 
     @Test


=====================================
t3-domain/src/test/java/fr/ird/t3/entities/reference/WeightCategoryTreatmentImplTest.java
=====================================
--- a/t3-domain/src/test/java/fr/ird/t3/entities/reference/WeightCategoryTreatmentImplTest.java
+++ b/t3-domain/src/test/java/fr/ird/t3/entities/reference/WeightCategoryTreatmentImplTest.java
@@ -23,7 +23,6 @@ package fr.ird.t3.entities.reference;
 import com.google.common.collect.Lists;
 import com.google.common.collect.Maps;
 import fr.ird.t3.entities.AbstractDatabaseTest;
-import fr.ird.t3.entities.T3ScriptHelper;
 import fr.ird.t3.entities.T3TopiaPersistenceContext;
 import java.util.Comparator;
 import java.util.List;
@@ -41,7 +40,7 @@ public class WeightCategoryTreatmentImplTest extends AbstractDatabaseTest {
 
     @Test
     public void testGetOldCategoryCode() throws Exception {
-        T3ScriptHelper.loadReferentiel(db.getRootCtxt(), "/db/WeightCategoryTreatmentImplTest.sql");
+        db.loadClassPathScript("/db/WeightCategoryTreatmentImplTest.sql");
 
         try (T3TopiaPersistenceContext tx = beginTransaction()) {
             WeightCategoryTreatmentTopiaDao dao = tx.getWeightCategoryTreatmentDao();


=====================================
t3-installer/src/main/assembly/dist/debug.sh
=====================================
--- a/t3-installer/src/main/assembly/dist/debug.sh
+++ b/t3-installer/src/main/assembly/dist/debug.sh


=====================================
t3-installer/src/main/java/fr/ird/t3/tools/T3ApplicationInstaller.java
=====================================
--- a/t3-installer/src/main/java/fr/ird/t3/tools/T3ApplicationInstaller.java
+++ b/t3-installer/src/main/java/fr/ird/t3/tools/T3ApplicationInstaller.java
@@ -27,7 +27,6 @@ import com.google.common.base.Preconditions;
 import fr.ird.t3.T3Config;
 import fr.ird.t3.T3SqlScriptsImporter;
 import fr.ird.t3.entities.T3EntityHelper;
-import fr.ird.t3.entities.T3ScriptHelper;
 import fr.ird.t3.entities.T3TopiaApplicationContext;
 import fr.ird.t3.entities.user.JdbcConfiguration;
 import fr.ird.t3.services.DefaultT3ServiceContext;
@@ -139,7 +138,7 @@ public class T3ApplicationInstaller {
         if (log.isInfoEnabled()) {
             log.info("3/6 - Create database schema...");
         }
-        boolean schemaFound = createDatabase();
+        createDatabase();
 
         T3SqlScriptsImporter dllScriptsImporter = new T3SqlScriptsImporter(ddlScriptsDirectory);
         dllScriptsImporter.prepare();
@@ -148,11 +147,7 @@ public class T3ApplicationInstaller {
             log.info("4/6 - Loading ddl from " + dllScriptsImporter.getScriptsFile().size() + " scripts.");
         }
 
-        if (schemaFound) {
-            log.info("4/6 - Loading ddl skip (schema already exists)");
-        } else {
-            dllScriptsImporter.importScripts(serviceContext, file -> true);
-        }
+        dllScriptsImporter.importScripts(serviceContext, file -> true);
 
         T3SqlScriptsImporter referentialScriptsImporter = new T3SqlScriptsImporter(referentialScriptsDirectory);
         referentialScriptsImporter.prepare();
@@ -178,7 +173,7 @@ public class T3ApplicationInstaller {
             if (log.isInfoEnabled()) {
                 log.info(" o Load postGis data... from " + unzipFile + " script.");
             }
-            loadScriptLineByLine(unzipFile);
+            serviceContext.getApplicationContext().newJdbcHelper().executeSql(unzipFile);
         }
     }
 
@@ -240,7 +235,7 @@ public class T3ApplicationInstaller {
         }
     }
 
-    private boolean createDatabase() {
+    private void createDatabase() {
 
 
         try {
@@ -256,7 +251,6 @@ public class T3ApplicationInstaller {
                 rootContext.createSchema();
 
             }
-            return schemaFound;
 
         } catch (TopiaException e) {
             throw new IllegalStateException("could not start db", e);
@@ -282,14 +276,6 @@ public class T3ApplicationInstaller {
         return unzupDirectory;
     }
 
-    private void loadScriptLineByLine(File script) throws TopiaException, IOException {
-
-
-        T3ScriptHelper.loadScriptLineByLine(serviceContext.getApplicationContext(), script,
-                                            evt -> log.debug("Sql line " + evt.getNewValue() + " done."));
-    }
-
-
     private void destroy() {
 
         T3EntityHelper.releaseRootContext(serviceContext.getApplicationContext());


=====================================
t3-installer/src/test/java/fr/ird/t3/tools/db/reference/AbstractReferenceImporterIT.java
=====================================
--- a/t3-installer/src/test/java/fr/ird/t3/tools/db/reference/AbstractReferenceImporterIT.java
+++ b/t3-installer/src/test/java/fr/ird/t3/tools/db/reference/AbstractReferenceImporterIT.java
@@ -24,7 +24,6 @@
 package fr.ird.t3.tools.db.reference;
 
 import fr.ird.t3.T3IOUtil;
-import fr.ird.t3.entities.T3ScriptHelper;
 import fr.ird.t3.entities.T3TopiaPersistenceContext;
 import fr.ird.t3.tools.AbstracToolTest;
 import java.io.File;
@@ -95,7 +94,13 @@ public abstract class AbstractReferenceImporterIT<E extends TopiaEntity, T exten
 
         // export referentiel
         File outputFile = new File(inputFile.getParentFile(), "newReferentiel.sql");
-        T3ScriptHelper.exportDatabase(serviceContext.getApplicationContext(), outputFile);
+
+        if (log.isInfoEnabled()) {
+            log.info("Export database to file " + outputFile);
+        }
+
+        serviceContext.getApplicationContext().newJdbcHelper().backup(outputFile, false);
+
     }
 
     public <T extends TopiaEntity> void deleteEntities(Class<T> entityType) {



View it on GitLab: https://gitlab.com/ultreiaio/ird-t3/compare/d10e8af8f4e1e0a3a090861f9952ff0f...

    

Tony CHEMIT

tags

participants (1)