mysql updater

3 files changed, 158 insertions, 0 deletions

diff --git a/juick-server/src/main/java/com/juick/configuration/DataConfiguration.java b/juick-server/src/main/java/com/juick/configuration/DataConfiguration.java
index 535755c2..d9ef7d38 100644
--- a/juick-server/src/main/java/com/juick/configuration/DataConfiguration.java
+++ b/juick-server/src/main/java/com/juick/configuration/DataConfiguration.java
@@ -1,5 +1,6 @@
 package com.juick.configuration;
 
+import com.juick.database.MySqlUpdater;
 import com.juick.service.search.SearchService;
 import org.apache.commons.dbcp2.BasicDataSource;
 import org.springframework.context.annotation.*;
@@ -58,6 +59,14 @@ public class DataConfiguration implements TransactionManagementConfigurer {
     }
 
     @Bean
+    public MySqlUpdater updater() {
+        return new MySqlUpdater(
+                jdbcTemplate(),
+                transactionManager(),
+                "update.sql");
+    }
+
+    @Bean
     public SearchService emptySearchService() {
         return new SearchService() {
             @Override
diff --git a/juick-server/src/main/java/com/juick/database/MySqlUpdater.java b/juick-server/src/main/java/com/juick/database/MySqlUpdater.java
new file mode 100644
index 00000000..469dad10
--- /dev/null
+++ b/juick-server/src/main/java/com/juick/database/MySqlUpdater.java
@@ -0,0 +1,137 @@
+package com.juick.database;
+
+import org.apache.commons.io.IOUtils;
+import org.apache.commons.lang3.StringUtils;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.transaction.PlatformTransactionManager;
+import org.springframework.transaction.support.TransactionTemplate;
+import org.springframework.util.Assert;
+
+import javax.annotation.PostConstruct;
+import java.io.IOException;
+import java.io.InputStream;
+import java.nio.charset.StandardCharsets;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.function.Supplier;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+import static org.springframework.transaction.TransactionDefinition.PROPAGATION_REQUIRED;
+
+/**
+ * Created by aalexeev on 12/13/16.
+ */
+public class MySqlUpdater {
+    private static final Pattern UPDATE_PATTERN = Pattern.compile(
+            "update\\s+(version|`version`)\\s+set\\s+(version|`version`)\\s+=\\s+(\\d+)",
+            Pattern.CASE_INSENSITIVE);
+
+    private final Logger logger = LoggerFactory.getLogger(getClass());
+
+    private final JdbcTemplate jdbcTemplate;
+    private final TransactionTemplate transactionTemplate;
+    private final String updateSqlResource;
+
+
+    public MySqlUpdater(JdbcTemplate jdbcTemplate, PlatformTransactionManager transactionManager, String updateSqlResource) {
+        Assert.notNull(jdbcTemplate);
+        Assert.notNull(transactionManager);
+        Assert.notNull(updateSqlResource);
+
+        this.jdbcTemplate = jdbcTemplate;
+        this.transactionTemplate = new TransactionTemplate(transactionManager);
+        this.updateSqlResource = updateSqlResource;
+    }
+
+    @PostConstruct
+    public void init() {
+        try (
+                InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(updateSqlResource);
+        ) {
+            if (is != null) {
+                String content = IOUtils.toString(is, StandardCharsets.UTF_8);
+                if (StringUtils.isNotEmpty(content)) {
+                    String[] sqlArray = content.split(";");
+                    if (sqlArray.length > 0) {
+                        List<String> sqlList = new ArrayList<>(sqlArray.length);
+                        for (String sql : sqlArray)
+                            if (!sql.isEmpty()) {
+                                String sqlTrimmed = sql.trim();
+                                if (!sqlTrimmed.isEmpty())
+                                    sqlList.add(sqlTrimmed);
+                            }
+                        if (!sqlList.isEmpty())
+                            processingSql(sqlList);
+                    }
+                }
+            }
+        } catch (Exception e) {
+            logger.error("MySqlUpdater initialization exception", e);
+        }
+    }
+
+    private void processingSql(final List<String> sqls) {
+        long currentDbVersion = getSingleResult(this::getVersionRaw);
+        long actualVersion;
+
+        List<String> changesSql = new ArrayList<>();
+
+        for (String sql : sqls) {
+            changesSql.add(sql);
+
+            Matcher m = UPDATE_PATTERN.matcher(sql);
+            if (m.matches()) {
+                String actual = m.group(3);
+                actualVersion = Long.valueOf(actual);
+
+                if (actualVersion > currentDbVersion) {
+                    updateInTransaction(changesSql);
+                    currentDbVersion = actualVersion;
+                }
+                changesSql.clear();
+            }
+        }
+    }
+
+    private void updateInTransaction(final List<String> sqls) {
+        transactionTemplate.setReadOnly(false);
+        transactionTemplate.setPropagationBehavior(PROPAGATION_REQUIRED);
+        transactionTemplate.execute(status -> {
+            for (String sql : sqls)
+                jdbcTemplate.execute(sql);
+            return 0;
+        });
+    }
+
+    private <T> T getSingleResult(Supplier<T> supplier) {
+        transactionTemplate.setReadOnly(true);
+        transactionTemplate.setPropagationBehavior(PROPAGATION_REQUIRED);
+
+        return transactionTemplate.execute(status -> supplier.get());
+    }
+
+    private long getVersionRaw() {
+        int cnt = jdbcTemplate.query(
+                "SELECT count(*) FROM information_schema.tables WHERE table_schema = ? AND table_name = ?",
+                rs -> {
+                    int result = 0;
+                    if (rs.next())
+                        result = rs.getInt(1);
+                    return result;
+                },
+                "juick", "version");
+
+        long version = 0l;
+
+        if (cnt == 1) {
+            List<Long> list = jdbcTemplate.queryForList("select version from version", Long.class);
+            if (!list.isEmpty())
+                version = list.get(0);
+        }
+
+        return version;
+    }
+}
diff --git a/juick-server/src/main/resources/update.sql b/juick-server/src/main/resources/update.sql
new file mode 100644
index 00000000..13a62c3d
--- /dev/null
+++ b/juick-server/src/main/resources/update.sql
@@ -0,0 +1,12 @@
+-- if version table not exists set up version = 0;
+update version set version = 0;
+
+DROP TABLE IF EXISTS `version`;
+
+CREATE TABLE `version` (
+  `version`  bigint NOT NULL
+);
+
+insert into version values (0);
+
+update version set version = 1;
\ No newline at end of file