001/*
002 * Stallion Core: A Modern Web Framework
003 *
004 * Copyright (C) 2015 - 2016 Stallion Software LLC.
005 *
006 * This program is free software: you can redistribute it and/or modify it under the terms of the
007 * GNU General Public License as published by the Free Software Foundation, either version 2 of
008 * the License, or (at your option) any later version. This program is distributed in the hope that
009 * it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
010 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public
011 * License for more details. You should have received a copy of the GNU General Public License
012 * along with this program.  If not, see <http://www.gnu.org/licenses/gpl-2.0.html>.
013 *
014 *
015 *
016 */
017
018package io.stallion.dataAccess.db;
019
020import io.stallion.boot.AppContextLoader;
021import io.stallion.boot.StallionRunAction;
022import io.stallion.boot.SqlMigrateCommandOptions;
023import io.stallion.exceptions.ConfigException;
024import io.stallion.plugins.PluginRegistry;
025import io.stallion.plugins.StallionJavaPlugin;
026import io.stallion.reflection.PropertyComparator;
027import io.stallion.services.Log;
028import io.stallion.utils.ResourceHelpers;
029import io.stallion.utils.json.JSON;
030import jdk.nashorn.api.scripting.NashornScriptEngineFactory;
031import org.apache.commons.io.FilenameUtils;
032import org.apache.commons.lang3.StringUtils;
033import org.parboiled.common.FileUtils;
034
035import javax.script.ScriptEngine;
036import java.io.File;
037import java.util.List;
038import java.util.Map;
039
040import static io.stallion.utils.Literals.*;
041import static io.stallion.Context.*;
042
043/**
044 * Runs all unexecuted database migrations.
045 */
046public class SqlMigrationAction  implements StallionRunAction<SqlMigrateCommandOptions> {
047    private ScriptEngine scriptEngine;
048
049    @Override
050    public String getActionName() {
051        return "sql-migrate";
052    }
053
054    @Override
055    public String getHelp() {
056        return "Executes all SQL scripts in the sql folder that are above the current migration number";
057    }
058
059
060    @Override
061    public SqlMigrateCommandOptions newCommandOptions() {
062        return new SqlMigrateCommandOptions();
063    }
064
065    @Override
066    public void loadApp(SqlMigrateCommandOptions options) {
067        AppContextLoader.loadWithSettingsOnly(options);
068        DB.load();
069    }
070
071    @Override
072    public void execute(SqlMigrateCommandOptions options) throws Exception {
073        createMigrationTrackingTableIfNotExists();
074        // Get a ticket to make sure the tickets table is operational
075        Long nonce = DB.instance().getTickets().nextId();
076        List<SqlMigration> migrations = getDefaultMigrations();
077        migrations.addAll(getUserMigrations());
078        DB db = DB.instance();
079        for (SqlMigration migration: migrations) {
080            Long currentVersion = or(db.queryScalar("SELECT MAX(versionNumber) FROM stallion_sql_migrations WHERE appName=?", migration.getAppName()), 0L);
081            if (currentVersion >= migration.getVersionNumber()) {
082                Log.finer("File {0} is below current version of {1} for app {2}", migration.getFilename(), currentVersion, migration.getAppName());
083                continue;
084            }
085            Log.info("Run migration app:" + migration.getAppName() + " file:" + migration.getFilename());
086            if (migration.isJavascript()) {
087                ScriptEngine engine = getOrCreateScriptEngine();;
088                engine.put("db", db);
089                engine.eval("load(" + JSON.stringify(map(
090                        val("script", transformJavascript(migration.getSource())),
091                        val("name", migration.getFilename())
092                )) + ");");
093            } else {
094                db.execute(migration.getSource());
095
096            }
097            db.execute("INSERT INTO stallion_sql_migrations (versionNumber, appName, fileName, executedAt) VALUES(?, ?, ?, UTC_TIMESTAMP())",
098                    migration.getVersionNumber(), migration.getAppName(), migration.getFilename()
099            );
100        }
101
102
103    }
104
105    public String transformJavascript(String source) {
106        StringBuilder builder = new StringBuilder();
107        int current = 0;
108        int end = source.length() - 1;
109        for(int i: safeLoop(1000)) {
110            int start = source.indexOf("'''", current);
111            if (start == -1) {
112                break;
113            }
114            int last = source.indexOf("'''", start + 3);
115            if (last == -1) {
116                break;
117            }
118            builder.append(source.substring(current, start));
119            String inner = source.substring(start + 3, last);
120            builder.append(JSON.stringify(inner));
121            current = last + 3;
122        }
123        builder.append(source.substring(current, end));
124        return builder.toString();
125    }
126
127    public ScriptEngine getOrCreateScriptEngine() {
128        if (scriptEngine == null) {
129            scriptEngine = new NashornScriptEngineFactory().getScriptEngine();
130        }
131        return scriptEngine;
132    }
133
134    public List<SqlMigration> getUserMigrations() {
135        List<SqlMigration> migrations = list();
136        File sqlDirectory = new File(settings().getTargetFolder() + "/sql");
137        if (!sqlDirectory.isDirectory()) {
138            Log.finer("Sql directory does not exist {0}", sqlDirectory.getAbsoluteFile());
139            return migrations;
140        }
141        Log.finer("Find sql files in {0}", sqlDirectory.getAbsolutePath());
142        for (File file: sqlDirectory.listFiles()) {
143            Log.finer("Scan file" + file.getAbsolutePath());
144            if (!set("js", "sql").contains(FilenameUtils.getExtension(file.getAbsolutePath()))) {
145                Log.finer("Extension is not .js or .sql {0}", file.getAbsolutePath());
146                continue;
147            }
148            if (file.getName().startsWith(".") || file.getName().startsWith("#")) {
149                Log.finer("File name starts with invalid character {0}", file.getName());
150                continue;
151            }
152            if (!file.getName().contains("." + DB.instance().getDbImplementation().getName().toLowerCase() + ".")) {
153                Log.finer("File name does not contain the name of the current database engine: \".{0}.\"", DB.instance().getDbImplementation().getName().toLowerCase());
154                continue;
155            }
156            if (!file.getName().contains("-")) {
157                Log.finer("File name does not have version part {0}", file.getName());
158                continue;
159            }
160            String versionString = StringUtils.stripStart(StringUtils.split(file.getName(), "-")[0], "0");
161            if (!StringUtils.isNumeric(versionString)) {
162                Log.finer("File name does not have numeric version part {0}", file.getName());
163                continue;
164            }
165            Log.info("Load SQL file for migration: {0}", file.getName());
166
167            migrations.add(
168                    new SqlMigration()
169                    .setVersionNumber(Integer.parseInt(StringUtils.stripStart(versionString, "0")))
170                    .setAppName("")
171                    .setFilename(file.getName())
172                            .setSource(FileUtils.readAllText(file))
173            );
174        }
175        migrations.sort(new PropertyComparator<>("versionNumber"));
176        return migrations;
177    }
178
179    public List<SqlMigration> getDefaultMigrations() {
180        List<SqlMigration> migrations = list();
181        Map<String, List<String>> pluginMigrations = map();
182        pluginMigrations.put("stallion", list(
183                "00004-users",
184                "00006-async_tasks",
185                "00010-job_status",
186                "00011-temp_tokens",
187                "00020-create-audit-trail",
188                "00025-create-transaction-log",
189                "00030-job-status-new-columns"
190        ));
191        if (PluginRegistry.instance() != null) {
192            for (StallionJavaPlugin plugin : PluginRegistry.instance().getJavaPluginByName().values()) {
193                pluginMigrations.put(plugin.getPluginName(), plugin.getSqlMigrations());
194            }
195        }
196        for (Map.Entry<String, List<String>> entry: pluginMigrations.entrySet()) {
197            for (String fileStub: entry.getValue()) {
198                Log.info("fileStub: {0}", fileStub);
199                int version = Integer.parseInt(fileStub.split("\\-")[0]);
200                String fileName = fileStub + "." + DB.instance().getDbImplementation().getName().toLowerCase() + ".sql";
201                if (!ResourceHelpers.resourceExists(entry.getKey(), "/sql/" + fileName)) {
202                    fileName = fileStub + "." + DB.instance().getDbImplementation().getName().toLowerCase() + ".js";
203                }
204                if (!ResourceHelpers.resourceExists(entry.getKey(), "/sql/" + fileName)) {
205                    throw new ConfigException("The sql migration file /sql/" + fileName + " does not exist in plugin " + entry.getKey());
206                }
207                String source = ResourceHelpers.loadResource(entry.getKey(), "/sql/" + fileName);
208                SqlMigration migration = new SqlMigration()
209                        .setAppName(entry.getKey())
210                        .setFilename(fileName)
211                        .setSource(source)
212                        .setVersionNumber(version);
213                migrations.add(migration);
214            }
215        }
216
217        return migrations;
218    }
219
220    void createMigrationTrackingTableIfNotExists() {
221        String sql = ResourceHelpers.loadResource("stallion", "/sql/migrations-table." + DB.instance().getDbImplementation().getName() + ".sql");
222        DB.instance().execute(sql);
223
224    }
225}