Source code

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.SqlGenerateCommandOptions;
022import io.stallion.boot.StallionRunAction;
023import io.stallion.exceptions.ConfigException;
024import io.stallion.exceptions.UsageException;
025import io.stallion.services.Log;
026import io.stallion.settings.Settings;
027import io.stallion.utils.GeneralUtils;
028import io.stallion.utils.Prompter;
029import org.apache.commons.lang3.StringUtils;
030import org.parboiled.common.FileUtils;
031
032import javax.script.ScriptEngine;
033import java.io.File;
034import java.nio.charset.Charset;
035import java.time.ZonedDateTime;
036import java.util.List;
037import java.util.Map;
038
039import static io.stallion.utils.Literals.*;
040
041
042public class SqlGenerationAction  implements StallionRunAction<SqlGenerateCommandOptions> {
043    private ScriptEngine scriptEngine;
044
045    @Override
046    public String getActionName() {
047        return "sql-generate";
048    }
049
050    @Override
051    public String getHelp() {
052        return "Generates new SQL scripts from the models in the project.";
053    }
054
055
056    @Override
057    public SqlGenerateCommandOptions newCommandOptions() {
058        return new SqlGenerateCommandOptions();
059    }
060
061    @Override
062    public void loadApp(SqlGenerateCommandOptions options) {
063        //AppContextLoader.loadWithSettingsOnly(options);
064        //DB.load();
065
066        DB.setUseDummyPersisterForSqlGenerationMode(true);
067        AppContextLoader.loadCompletely(options);
068        if (!DB.available()) {
069            if (Settings.instance().getDatabase() == null || empty(Settings.instance().getDatabase().getUrl())) {
070                throw new ConfigException("No database url defined in your settings");
071            } else {
072                throw new ConfigException("Database is not available.");
073            }
074        }
075    }
076
077    @Override
078    public void execute(SqlGenerateCommandOptions options) throws Exception {
079        //List<ClassLoader> loaders = list();
080        //ConfigurationBuilder builder = new ConfigurationBuilder();
081        //if (!empty(options.getPackageName())) {
082        //    builder = builder.forPackages("io.stallion");
083        //} else {
084        //    builder = builder.forPackages(options.getPackageName());
085        //}
086        //for(StallionJavaPlugin plugin: PluginRegistry.instance().getJavaPluginByName().values()) {
087        //    builder = builder.addClassLoader(plugin.getClass().getClassLoader());
088        //}
089
090        //Reflections reflections = new Reflections(builder);
091
092        //Reflections reflections = new Reflections("org.mycabal");
093
094        //Set<Class<? extends ModelBase>> classes = reflections.getSubTypesOf(ModelBase.class);
095        //Log.info("Model Count {0} {1}", classes.size(), classes);
096        //classes = set(User.class);
097        boolean hasNewMigrations = false;
098       // for (Class cls: classes) {
099        List<Schema> schemas = DB.instance().getSchemas();
100        Log.info("Schema count {0}", DB.instance().getSchemas().size());
101        Integer lastMigrationNumber = getLastMigrationNumber();
102        for (Schema schema: schemas)   {
103            Log.info("Try generate for schema ", schema.getName());
104            //GenerateResult result = generateForModel(cls);
105            GenerateResult result = generateForSchema(schema);
106            if (result == null) {
107                Log.info("No change needed for {0}", schema.getName());
108                continue;
109            }
110            Log.info("SQL GENERATED\n{0}", result.getSql());
111            boolean shouldWrite = new Prompter("Write this script to file? ").yesNo();
112            if (shouldWrite) {
113                lastMigrationNumber += 10;
114                String prefix = StringUtils.leftPad(lastMigrationNumber.toString(), 5, '0');
115                String file = Settings.instance().getTargetFolder() + "/sql/" + prefix + "-" + result.getChangePrefix()
116                        + ".mysql.js";
117                FileUtils.writeAllText(result.getSqlJs(), new File(file), Charset.forName("UTF-8"));
118                hasNewMigrations = true;
119            }
120        }
121        if (hasNewMigrations) {
122            boolean runMigrations = new Prompter("Execute all new SQL migrations? ").yesNo();
123            if (runMigrations) {
124                SqlMigrationAction action = new SqlMigrationAction();
125                action.execute(options);
126            }
127        }
128    }
129
130    public int getLastMigrationNumber() {
131        Integer max = 0;
132        for (SqlMigration migration: new SqlMigrationAction().getUserMigrations()) {
133            if (migration.getVersionNumber() > max) {
134                max = migration.getVersionNumber();
135            }
136        }
137        return max;
138    }
139
140    public GenerateResult generateForModel(Class cls) {
141        Schema schema = DB.instance().modelToSchema(cls);
142        return generateForSchema(schema);
143    }
144
145    public GenerateResult generateForSchema(Schema schema) {
146        if (!DB.instance().tableExists(schema.getName())) {
147            return createTableSqlForSchema(schema);
148        } else {
149            List<Col> missingColumns = list();
150            for(Col col: schema.getColumns()) {
151                if (!DB.instance().columnExists(schema.getName(), col.getName())) {
152                    missingColumns.add(col);
153                }
154            }
155            if (missingColumns.size() > 0) {
156                return createAlterTableForNewColumns(schema, missingColumns);
157            }
158        }
159        return null;
160    }
161
162    public GenerateResult createTableSqlForSchema(Schema schema) {
163        StringBuilder sql = new StringBuilder();
164        sql.append("CREATE TABLE IF NOT EXISTS `" + schema.getName() + "` (\n");
165        sql.append("`id` bigint(20) unsigned NOT NULL,\n");
166        for (Col col: schema.getColumns()) {
167            sql.append("    `" + col.getName() + "` ");
168            sql.append(" " + dbTypeForColumn(col) + " ");
169            if (col.getNullable()) {
170                sql.append(" NULL ");
171            } else {
172                sql.append(" NOT NULL ");
173            }
174            if (col.getDefaultValue() != null) {
175                Object defaultVal = col.getDefaultValue();
176                if (defaultVal instanceof Boolean) {
177                    if ((Boolean)defaultVal == true){
178                        defaultVal = 1;
179                    } else {
180                        defaultVal = 0;
181                    }
182                } else {
183                    defaultVal = "'" + defaultVal.toString().replace("'", "\'") + "'";
184                }
185                sql.append(" DEFAULT " + defaultVal + " ");
186            }
187            sql.append(",\n");
188        }
189        sql.append("  `row_updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n");
190        sql.append("  PRIMARY KEY (`id`),\n");
191        sql.append("  KEY `row_updated_at_key` (`row_updated_at`),\n");
192
193        for(Col col: schema.getColumns()) {
194            if (col.getUniqueKey()) {
195                sql.append("  UNIQUE KEY `" + col.getName() + "_key` (`" + col.getName() + "`),\n");
196
197            }
198            if (col.getAlternativeKey()) {
199                sql.append("  KEY `" + col.getName() + "_key` (`" + col.getName() + "`),\n");
200            }
201        }
202        for(String def: schema.getExtraKeyDefinitions()) {
203            sql.append("    " + def + ",\n");
204        }
205        String sqlString = sql.toString();
206        sqlString = StringUtils.strip(StringUtils.strip(sqlString, "\n"), ",") + "\n";
207        sqlString += ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
208
209        return new GenerateResult()
210                .setChangePrefix("create-table-" + schema.getName())
211                .setSql(sqlString)
212                .setSqlJs("db.execute('''\n" + sqlString + " \n''');")
213                .setTableName(schema.getName())
214                ;
215    }
216
217    public GenerateResult createAlterTableForNewColumns(Schema schema, List<Col> columns) {
218        StringBuilder sql = new StringBuilder();
219        sql.append("ALTER TABLE `" + schema.getName() + "` ");
220
221        int i = 0;
222        for (Col col: columns) {
223            sql.append("\n    ADD COLUMN `" + col.getName() + "` ");
224            sql.append(" " + dbTypeForColumn(col) + " ");
225            if (col.getNullable()) {
226                sql.append(" NULL");
227            } else {
228                sql.append(" NOT NULL");
229            }
230            if (col.getDefaultValue() != null) {
231                sql.append(" DEFAULT '" + col.getDefaultValue().toString().replace("'", "\'") + "'");
232            }
233            i++;
234            if (i != columns.size()) {
235                sql.append(",");
236            }
237        }
238        sql.append(";");
239        String sqlString = sql.toString();
240
241        GenerateResult generateResult = new GenerateResult()
242                .setChangePrefix(GeneralUtils.slugify("alter-" + schema.getName() + "-add-" + columns.size()))
243                .setSql(sqlString)
244                .setSqlJs("db.execute('''\n" + sqlString + "\n''');")
245                .setTableName(schema.getName())
246                ;
247        return generateResult;
248    }
249
250    protected String dbTypeForColumn(Col column) {
251        if (!empty(column.getDbType())) {
252            String dbType = column.getDbType();
253            if (column.getLength() > 0 && !dbType.contains("(") && !dbType.contains("text") && !dbType.contains("(")) {
254                dbType = dbType + "(" + column.getLength() + ")";
255            }
256            return dbType;
257        }
258        if (column.getjType() == String.class) {
259            int length = column.getLength();
260            if (length < 1) {
261                length = 250;
262            }
263            return "varchar(" + length + ")";
264        } else if (column.getjType() == Boolean.class) {
265            return "bit(1)";
266        } else if (column.getjType() == boolean.class) {
267            return "bit(1)";
268        } else if (column.getjType() == Long.class) {
269            return "bigint(20)";
270        } else if (column.getjType() == long.class) {
271            return "bigint(20)";
272        } else if (column.getjType() == Integer.class) {
273            return "int";
274        } else if (column.getjType() == int.class) {
275            return "int";
276        } else if (column.getjType() == float.class) {
277            return "float";
278        } else if (column.getjType() == Float.class) {
279            return "float";
280        } else if (column.getjType() == double.class) {
281            return "double";
282        } else if (column.getjType() == Double.class) {
283            return "double";
284        } else if (column.getjType() == ZonedDateTime.class) {
285            return "datetime";
286        } else if (List.class.isAssignableFrom(column.getjType())) {
287            return "longtext";
288        } else if (Map.class.isAssignableFrom(column.getjType())) {
289            return "longtext";
290        } else if (Enum.class.isAssignableFrom(column.getjType())) {
291            return "varchar(30)";
292        } else {
293            throw new UsageException("Could not guess database column type for column " + column.getName());
294        }
295    }
296
297    public static class GenerateResult {
298        private String sql = "";
299        private String sqlJs = "";
300        private String tableName = "";
301        private String changePrefix = "";
302
303        public String getSql() {
304            return sql;
305        }
306
307        public GenerateResult setSql(String sql) {
308            this.sql = sql;
309            return this;
310        }
311
312        public String getSqlJs() {
313            return sqlJs;
314        }
315
316        public GenerateResult setSqlJs(String sqlJs) {
317            this.sqlJs = sqlJs;
318            return this;
319        }
320
321        public String getTableName() {
322            return tableName;
323        }
324
325        public GenerateResult setTableName(String tableName) {
326            this.tableName = tableName;
327            return this;
328        }
329
330        public String getChangePrefix() {
331            return changePrefix;
332        }
333
334        public GenerateResult setChangePrefix(String changePrefix) {
335            this.changePrefix = changePrefix;
336            return this;
337        }
338    }
339
340}