Commits

Takumi IINO  committed 8fd7b21

initial commit

  • Participants
  • Tags v0.1

Comments (0)

Files changed (16)

+syntax: regexp
+^\.metadata
+/\.(project|classpath|settings|gradle)
+/(bin|target|repo|build)/

File schema-update/build.gradle

+// -*- coding:utf-8 -*-
+
+apply plugin: 'eclipse'
+apply plugin: 'groovy'
+apply plugin: 'maven'
+
+archivesBaseName = 'schema-update'
+group = 'jp.troter.tools'
+version = '0.1'
+
+def defaultEncoding = 'UTF-8'
+
+repositories {
+    mavenCentral()
+}
+
+dependencies {
+    groovy group: 'org.codehaus.groovy', name: 'groovy', version: '1.8.1'
+    testCompile group: 'junit', name: 'junit', version: '4.8.2'
+    testCompile group: 'com.h2database', name: 'h2', version: '1.3.158'
+}
+
+compileGroovy {
+    groovyOptions.encoding = defaultEncoding
+    options.encoding = defaultEncoding
+}
+compileTestGroovy {
+    groovyOptions.encoding = defaultEncoding
+    options.encoding = defaultEncoding
+}
+
+mavenRepository = "repo"
+
+uploadArchives {
+    repositories {
+        mavenDeployer {
+            repository(url: uri(mavenRepository))
+        }
+    }
+}

File schema-update/src/main/groovy/jp/troter/tools/schema_update/ChangeSet.groovy

+// -*- coding:utf-8 -*-
+package jp.troter.tools.schema_update;
+
+import groovy.sql.Sql
+import groovy.util.logging.Log
+
+import java.util.regex.Pattern
+
+
+/**
+ * SQLのチェンジセットを表すクラス
+ */
+@Log
+class ChangeSet implements Comparable<ChangeSet> {
+
+    File changeSetFile
+    Integer version
+    List queries
+
+    ChangeSet(Map data) {
+        changeSetFile = data.changeSetFile
+        version = data.version
+        queries = data.queries
+    }
+
+    ChangeSet(String changeSetFile) {
+        this(new File(changeSetFile))
+    }
+
+    ChangeSet(File changeSetFile) {
+        assert changeSetFile.isFile()
+        this.changeSetFile = changeSetFile
+        version = parseVersion(changeSetFile)
+        queries = parseQueries(changeSetFile)
+    }
+
+    public static Integer parseVersion(File changeSetFile) {
+        def m = getChangeSetFilePattern().matcher(changeSetFile.name)
+        Integer.parseInt(m[0][1])
+    }
+
+    public static List<String> parseQueries(File changeSetFile) {
+        parseQueries(changeSetFile.getText())
+    }
+
+    public static List<String> parseQueries(String changeSetContent) {
+        List queries = []
+        changeSetContent.eachMatch(getQuerySeparatorPattern()) {
+            if (! isEmptyLineOnly(it)) {
+                queries << it.trim()
+            }
+        }
+        queries
+    }
+
+    public static boolean isEmptyLineOnly(String query) {
+        query.readLines().every { isEmptyLine(it) }
+    }
+
+    public static boolean isEmptyLine(String line) {
+        line ==~ /\s*--\s.*|\s*/
+    }
+
+    public static boolean isChangeSetFile(File changeSetFile) {
+        changeSetFile.name in getChangeSetFilePattern()
+    }
+
+    /**
+     * 差分SQLの適応
+     * @param sql
+     */
+    public void apply(Sql sql) {
+        def size = queries.size();
+        log.info("- ${changeSetFile.getName()} has ${size} query(s).")
+        queries.eachWithIndex { query, index ->
+            log.info("  execute ${index + 1}/${size} query.")
+            log.fine("  -- execute query: ${query}")
+            sql.execute(query); 
+        }
+    }
+
+    public static Pattern getChangeSetFilePattern() {
+        ~/(\d+)_.*\.sql/
+    }
+
+    public static Pattern getQuerySeparatorPattern() {
+        ~/(?s).*?;\n/
+    }
+
+    @Override public int compareTo(ChangeSet o) {
+        version.compareTo(o.version)
+    }
+}

File schema-update/src/main/groovy/jp/troter/tools/schema_update/ConfigurationHolder.groovy

+package jp.troter.tools.schema_update
+
+import groovy.sql.Sql
+
+class ConfigurationHolder {
+
+    static ConfigObject config;
+
+    public static void load(File config) {
+        this.config = new ConfigSlurper().parse(config.toURI().toURL())
+    }
+
+    public static void load(File config, String env) {
+        this.config = new ConfigSlurper(env).parse(config.toURI().toURL())
+    }
+
+    public static Sql getSql() {
+        Sql.newInstance(config.jdbc.url, config.jdbc.user, config.jdbc.password, config.jdbc.driverClassName)
+    }
+}

File schema-update/src/main/groovy/jp/troter/tools/schema_update/Runner.groovy

+// -*- coding:utf-8 -*-
+package jp.troter.tools.schema_update;
+
+import groovy.sql.*
+import groovy.util.logging.Log
+
+@Log
+class Runner {
+
+    List schemaUpdates
+
+    Runner(File changeSetDir) {
+        assert changeSetDir.isDirectory()
+        schemaUpdates = [new SchemaUpdate(changeSetDir)]
+    }
+
+    Runner(File basedir, Comparator<SchemaUpdate> schemaUpdateOrder) {
+        assert basedir.isDirectory()
+        schemaUpdates = basedir.listFiles().findAll {
+            it.isDirectory()
+        }.collect {
+            new SchemaUpdate(basedir, it.name)
+        }.sort(schemaUpdateOrder)
+    }
+
+    public Sql getSql() {
+        ConfigurationHolder.sql
+    }
+
+    public void run() {
+        run(sql)
+    }
+
+    public void run(Sql sql) {
+        schemaUpdates.each { it.update(sql) }
+    }
+
+    public void version() {
+        version(sql)
+    }
+
+    public void version(Sql sql) {
+        schemaUpdates.each {
+            log.info("phase:[${it.phase}] version:[${it.currentVersion(sql)}]")
+        }
+    }
+
+    static public class DefaultComparator implements Comparator<SchemaUpdate> {
+        int compare(SchemaUpdate o1, SchemaUpdate o2) {
+            o1.phase.compareTo(o2.phase)
+        }
+    }
+}
+
+

File schema-update/src/main/groovy/jp/troter/tools/schema_update/SchemaUpdate.groovy

+// -*- coding:utf-8 -*-
+package jp.troter.tools.schema_update;
+
+import groovy.sql.*
+import groovy.util.logging.Log
+
+@Log
+class SchemaUpdate {
+
+    File changeSetDir
+
+    String phase = ''
+
+    List changeSets
+
+    SchemaVersion version;
+
+    SchemaUpdate(File changeSetDir) {
+        this(changeSetDir, '')
+    }
+
+    SchemaUpdate(File basedir, String phase) {
+        this.changeSetDir = new File(basedir, phase)
+        assert this.changeSetDir.isDirectory()
+        this.changeSets = getChangeSets(this.changeSetDir)
+        this.phase = phase
+        this.version = new SchemaVersion(phase)
+    }
+
+    protected static List getChangeSets(File changeSetDir) {
+        getChangeSetFiles(changeSetDir).collect { new ChangeSet(it) }.sort()
+    }
+
+    protected static List getChangeSetFiles(File basedir) {
+        basedir.listFiles().findAll { ChangeSet.isChangeSetFile(it) }
+    }
+
+    public int currentVersion(Sql sql) {
+        version.init(sql)
+        version.version(sql)
+    }
+
+    public void update(Sql sql) {
+        version.init(sql)
+        log.info("update phase:[${phase}]")
+        changeSets.each { changeSet ->
+            sql.withTransaction {
+                if (version.isApplied(sql, changeSet.version)) {
+                    return
+                }
+                changeSet.apply(sql)
+                version.update(sql, changeSet)
+            }
+        }
+    }
+}
+

File schema-update/src/main/groovy/jp/troter/tools/schema_update/SchemaVersion.groovy

+// -*- coding:utf-8 -*-
+package jp.troter.tools.schema_update
+
+import groovy.sql.Sql
+import groovy.util.logging.Log
+
+@Log
+class SchemaVersion {
+
+    String phase
+
+    SchemaVersion(String phase) {
+        this.phase = phase
+    }
+    
+    public String getTableName() {
+        ConfigurationHolder.config.schemaUpdate.tableName
+    }
+
+    public String getCreateTable() {
+        ConfigurationHolder.config.schemaUpdate.createTable
+    }
+
+    public void init(Sql sql) {
+        sql.execute(createTable);
+        if (! isExists(sql)) {
+            log.info("initialize phase: [${phase}].")
+            sql.execute("INSERT INTO ${tableName}(phase, version) VALUES(?, ?)", [phase, 0]);
+        }
+    }
+
+    public boolean isExists(Sql sql) {
+        sql.firstRow("SELECT version FROM ${tableName} WHERE phase = ?", [phase]);
+    }
+
+    public boolean isApplied(Sql sql, int version) {
+        sql.firstRow("SELECT version FROM ${tableName} WHERE phase = ? AND version >= ?", [phase, version]);
+    }
+
+    public int version(Sql sql) {
+        sql.firstRow("SELECT version FROM ${tableName} WHERE phase = ?", [phase]).version as int;
+    }
+
+    public update(Sql sql, ChangeSet changeSet) {
+        sql.execute("UPDATE ${tableName} SET version = ? WHERE phase = ?", [changeSet.version, phase])
+        assert sql.updateCount == 1;
+    }
+}

File schema-update/src/test/groovy/jp/troter/tools/schema_update/ChangeSetTest.groovy

+// -*- coding:utf-8 -*-
+package jp.troter.tools.schema_update;
+
+import static org.junit.Assert.*
+import groovy.sql.Sql
+
+import org.junit.After
+import org.junit.Before
+import org.junit.Test
+
+class ChangeSetTest {
+
+    Sql sql
+
+    @Before
+    void setUp() {
+        ConfigurationHolder.load(new File("src/test/resources/config.groovy"))
+        sql = ConfigurationHolder.sql
+    }
+
+    @After
+    void tearDown() {
+        sql.execute("DROP ALL OBJECTS DELETE FILES;")
+    }
+
+    @Test public void isChangeSetFile() {
+        assertTrue( ChangeSet.isChangeSetFile(new File('0001_changeset.sql')))
+        assertFalse(ChangeSet.isChangeSetFile(new File('0001changeset.sql')))
+        assertFalse(ChangeSet.isChangeSetFile(new File('0001_changeset.sql.disable')))
+        assertFalse(ChangeSet.isChangeSetFile(new File('000A_changeset.sql.disable')))
+    }
+
+    @Test public void parseVersion() {
+        assertEquals(1, ChangeSet.parseVersion(new File('0001_changeset.sql')))
+        assertEquals(2011, ChangeSet.parseVersion(new File('2011_changeset.sql')))
+    }
+
+    @Test public void parseQueries_String() {
+        List<String> queries = ChangeSet.parseQueries(changeSetContent)
+        assertEquals(2, queries.size())
+        assertEquals(first, queries[0])
+        assertEquals(second, queries[1])
+    }
+
+    @Test public void isEmptyLineOnly() {
+        assertTrue( ChangeSet.isEmptyLineOnly("  --  \n\n-- \n    "))
+        assertFalse(ChangeSet.isEmptyLineOnly("insert into a (id) values (1);"))
+        assertFalse(ChangeSet.isEmptyLineOnly("-- \n    insert into a (id) values (1);"))
+        assertFalse(ChangeSet.isEmptyLineOnly("--- \n"))
+    }
+
+    @Test public void compare() {
+        List sets = [new ChangeSet([version:2]), new ChangeSet([version:1])]
+        assertEquals(1, sets.sort()[0].version)
+        assertEquals(2, sets.sort()[1].version)
+    }
+
+    @Test public void smoke() {
+        def c = new ChangeSet("src/test/resources/migration/phase_1/0001_create_sample_table.sql")
+        assertEquals(1, c.version)
+        assertEquals(first, c.queries[0])
+        assertEquals(second, c.queries[1])
+        c.apply(sql)
+    }
+
+    @Test public void apply() {
+        new ChangeSet("src/test/resources/migration/phase_1/0001_create_sample_table.sql").apply(sql)
+        new ChangeSet("src/test/resources/migration/phase_1/0002_add_column_description_sample_table.sql").apply(sql)
+        // TODO version 0003の用意
+    }
+
+    // test data --------------------------------------------------
+
+        def changeSetContent = """
+-- -*- coding:utf-8; mode:sql; -*-
+-- r1 create table
+CREATE TABLE sample_table (
+    id INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
+    display_order INT DEFAULT 0 NOT NULL COMMENT '表示順',
+    created_date DATETIME NOT NULL COMMENT '作成日時',
+    updated_date DATETIME NOT NULL COMMENT '更新日時',
+    version INT DEFAULT 0 NOT NULL COMMENT 'オプティミスティックロック用',
+);
+
+CREATE INDEX ix_sample_table_display_order ON sample_table (display_order);
+-- END OF SQL
+"""
+
+    def first = """
+-- -*- coding:utf-8; mode:sql; -*-
+-- r1 create table
+CREATE TABLE sample_table (
+    id INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
+    display_order INT DEFAULT 0 NOT NULL COMMENT '表示順',
+    created_date DATETIME NOT NULL COMMENT '作成日時',
+    updated_date DATETIME NOT NULL COMMENT '更新日時',
+    version INT DEFAULT 0 NOT NULL COMMENT 'オプティミスティックロック用',
+);
+""".trim()
+
+    def second = """
+CREATE INDEX ix_sample_table_display_order ON sample_table (display_order);
+""".trim()
+
+}

File schema-update/src/test/groovy/jp/troter/tools/schema_update/RunnerTest.groovy

+package jp.troter.tools.schema_update;
+
+import static org.junit.Assert.*
+import groovy.sql.Sql
+
+import org.junit.After
+import org.junit.Before
+import org.junit.Test
+
+class RunnerTest {
+
+    Sql sql
+
+    @Before
+    void setUp() {
+        ConfigurationHolder.load(new File("src/test/resources/config.groovy"))
+        sql = ConfigurationHolder.sql
+    }
+
+    @After
+    void tearDown() {
+        sql.execute("DROP ALL OBJECTS DELETE FILES;")
+    }
+
+    @Test public void run() {
+        def runner = new Runner(new File('src/test/resources/migration/phase_1'))
+        runner.run()
+        assert runner.schemaUpdates[0].currentVersion(sql) == 2
+    }
+
+    @Test public void runWithPhase() {
+        def runner = new Runner(new File('src/test/resources/migration'), new Runner.DefaultComparator())
+        runner.run()
+        assert runner.schemaUpdates[0].currentVersion(sql) == 2
+        assert runner.schemaUpdates[1].currentVersion(sql) == 1
+    }
+
+    @Test public void version() {
+        def runner = new Runner(new File('src/test/resources/migration'), new Runner.DefaultComparator())
+        runner.run()
+        assert runner.schemaUpdates[0].currentVersion(sql) == 2
+        assert runner.schemaUpdates[1].currentVersion(sql) == 1
+        runner.version();
+    }
+}

File schema-update/src/test/groovy/jp/troter/tools/schema_update/SchemaUpdateTest.groovy

+// -*- coding:utf-8 -*-
+package jp.troter.tools.schema_update;
+
+import static org.junit.Assert.*
+import groovy.sql.Sql
+
+import org.junit.After
+import org.junit.Before
+import org.junit.Test
+
+class SchemaUpdateTest {
+
+    Sql sql
+
+    @Before
+    void setUp() {
+        ConfigurationHolder.load(new File("src/test/resources/config.groovy"))
+        sql = ConfigurationHolder.sql
+    }
+
+    @After
+    void tearDown() {
+        sql.execute("DROP ALL OBJECTS DELETE FILES;")
+    }
+
+    @Test public void getChangeSetFiles() {
+        assert SchemaUpdate.getChangeSetFiles(new File('src/test/resources/migration/phase_1')).size() == 2
+    }
+
+    @Test public void allChangeSet() {
+        List<ChangeSet> changeSets = SchemaUpdate.getChangeSets(new File('src/test/resources/migration/phase_1'))
+        assert changeSets.size() == 2
+        assert changeSets[0].changeSetFile.name == '0001_create_sample_table.sql'
+        assert changeSets[1].changeSetFile.name == '0002_add_column_description_sample_table.sql'
+    }
+
+    @Test public void construct() {
+        assert new SchemaUpdate(new File('src/test/resources/migration/phase_1')).changeSets.size() == 2
+        assert new SchemaUpdate(new File('src/test/resources/migration'), 'phase_1').changeSets.size() == 2
+    }
+
+    @Test public void currentVersion() {
+        assert new SchemaUpdate(new File('src/test/resources/migration/phase_1')).currentVersion(sql) == 0
+    }
+
+    @Test public void update_phase_1() {
+        def s = new SchemaUpdate(new File('src/test/resources/migration'), 'phase_1')
+        assert s.currentVersion(sql) == 0
+        s.update(sql)
+        assert s.currentVersion(sql) == 2
+        s.update(sql)
+        assert s.currentVersion(sql) == 2
+    }
+
+    @Test public void update_phase_2() {
+        def p1 = new SchemaUpdate(new File('src/test/resources/migration'), 'phase_1')
+        assert p1.currentVersion(sql) == 0
+        p1.update(sql)
+        assert p1.currentVersion(sql) == 2
+
+        def p2 = new SchemaUpdate(new File('src/test/resources/migration'), 'phase_2')
+        assert p2.currentVersion(sql) == 0
+        p2.update(sql)
+        assert p2.currentVersion(sql) == 1
+
+        p1.update(sql)
+        assert p1.currentVersion(sql) == 2
+        p2.update(sql)
+        assert p2.currentVersion(sql) == 1
+    }
+}

File schema-update/src/test/groovy/jp/troter/tools/schema_update/SchemaVersionTest.groovy

+package jp.troter.tools.schema_update;
+
+import static org.junit.Assert.*
+import groovy.sql.Sql
+
+import org.junit.After
+import org.junit.Before
+import org.junit.Test
+
+class SchemaVersionTest {
+
+    Sql sql
+
+    @Before
+    void setUp() {
+        ConfigurationHolder.load(new File("src/test/resources/config.groovy"))
+        sql = ConfigurationHolder.sql
+    }
+
+    @After
+    void tearDown() {
+        sql.execute("DROP ALL OBJECTS DELETE FILES;")
+    }
+
+    public void assertInitialStatus(SchemaVersion schemaVersion, Sql sql) {
+        assert schemaVersion.isExists(sql), "フェースが存在する"
+        assert schemaVersion.version(sql) == 0, "適用済みバーションは0である"
+        (1..10).step(1) {
+            assert ! schemaVersion.isApplied(sql, it), "バーション${it}は適応されていない"
+        }
+    }
+
+    public ChangeSet newChangeSet(int version) {
+        new ChangeSet([version:version])
+    }
+
+    @Test void phase_1() {
+        def s = new SchemaVersion("phase_1")
+        s.init(sql)
+        assertInitialStatus(s, sql)
+
+        (1..10).step(1) {
+            s.update(sql, newChangeSet(it))
+            assert s.isApplied(sql, it), "バーション${it}は適応されています"
+        }
+    }
+
+    @Test void phase_2() {
+        def p1 = new SchemaVersion("phase_1")
+        p1.init(sql)
+        assertInitialStatus(p1, sql)
+
+        p1.update(sql, newChangeSet(1))
+        assert p1.isApplied(sql, 1)
+
+        def p2 = new SchemaVersion("phase_2")
+        assert ! p2.isExists(sql)
+        p2.init(sql)
+        assertInitialStatus(p2, sql)
+
+        p2.update(sql, newChangeSet(3))
+
+        assert p2.isApplied(sql, 1)
+        assert p2.isApplied(sql, 2)
+        assert p2.isApplied(sql, 3)
+
+        assert p1.isApplied(sql, 1)
+    }
+}

File schema-update/src/test/resources/config.groovy

+schemaUpdate {
+    tableName = "schema_version"
+    createTable = """
+CREATE TABLE IF NOT EXISTS schema_version (
+    id INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
+    phase TEXT DEFAULT 0 NOT NULL COMMENT 'pharse',
+    version INT DEFAULT 0 NOT NULL COMMENT 'version'
+);
+"""
+}
+
+jdbc {
+    url = "jdbc:h2:MyDB"
+    user = "sa"
+    password = ""
+    driverClassName = "org.h2.Driver"
+}

File schema-update/src/test/resources/logging.properties

+handlers= java.util.logging.ConsoleHandler
+.level= FINE
+ 
+java.util.logging.ConsoleHandler.level = FINE
+java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter

File schema-update/src/test/resources/migration/phase_1/0001_create_sample_table.sql

+-- -*- coding:utf-8; mode:sql; -*-
+-- r1 create table
+CREATE TABLE sample_table (
+    id INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
+    display_order INT DEFAULT 0 NOT NULL COMMENT '表示順',
+    created_date DATETIME NOT NULL COMMENT '作成日時',
+    updated_date DATETIME NOT NULL COMMENT '更新日時',
+    version INT DEFAULT 0 NOT NULL COMMENT 'オプティミスティックロック用',
+);
+
+CREATE INDEX ix_sample_table_display_order ON sample_table (display_order);
+-- END OF SQL

File schema-update/src/test/resources/migration/phase_1/0002_add_column_description_sample_table.sql

+-- -*- coding:utf-8; mode:sql; -*-
+-- r1 alter table
+
+ALTER TABLE sample_table
+  ADD COLUMN description TEXT NOT NULL COMMENT '説明' BEFORE display_order
+;
+
+-- END OF SQL

File schema-update/src/test/resources/migration/phase_2/0001_create_another_table.sql

+-- -*- coding:utf-8; mode:sql; -*-
+-- r1 create table
+CREATE TABLE another_table (
+    id INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
+    display_order INT DEFAULT 0 NOT NULL COMMENT '表示順',
+    created_date DATETIME NOT NULL COMMENT '作成日時',
+    updated_date DATETIME NOT NULL COMMENT '更新日時',
+    version INT DEFAULT 0 NOT NULL COMMENT 'オプティミスティックロック用',
+);
+
+CREATE INDEX ix_another_table_display_order ON another_table (display_order);
+-- END OF SQL