001/*
002 * HA-JDBC: High-Availability JDBC
003 * Copyright (C) 2012  Paul Ferraro
004 *
005 * This program is free software: you can redistribute it and/or modify
006 * it under the terms of the GNU Lesser General Public License as published by
007 * the Free Software Foundation, either version 3 of the License, or
008 * (at your option) any later version.
009 *
010 * This program is distributed in the hope that it will be useful,
011 * but WITHOUT ANY WARRANTY; without even the implied warranty of
012 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
013 * GNU Lesser General Public License for more details.
014 *
015 * You should have received a copy of the GNU Lesser General Public License
016 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
017 */
018package net.sf.hajdbc.dialect.mysql;
019
020import java.io.File;
021import java.sql.DatabaseMetaData;
022import java.sql.SQLException;
023import java.util.Arrays;
024import java.util.Collections;
025import java.util.HashSet;
026import java.util.List;
027import java.util.Set;
028
029import net.sf.hajdbc.Database;
030import net.sf.hajdbc.DumpRestoreSupport;
031import net.sf.hajdbc.codec.Decoder;
032import net.sf.hajdbc.dialect.ConnectionProperties;
033import net.sf.hajdbc.dialect.StandardDialect;
034import net.sf.hajdbc.util.Processes;
035import net.sf.hajdbc.util.Strings;
036
037/**
038 * Dialect for <a href="http://www.mysql.com/products/database/mysql/">MySQL</a>
039 * @author Paul Ferraro
040 */
041@SuppressWarnings("nls")
042public class MySQLDialect extends StandardDialect implements DumpRestoreSupport
043{
044        // Taken from: http://dev.mysql.com/doc/refman/5.7/en/reserved-words.html
045        protected static final String[] RESERVED_KEY_WORDS = new String[] {
046                "ACCESSIBLE", "ADD", "ALL", "ALTER", "ANALYZE", "AND", "AS", "ASC", "ASENSITIVE",
047                "BEFORE", "BETWEEN", "BIGINT", "BINARY", "BLOB", "BOTH", "BY",
048                "CALL", "CASCADE", "CASE", "CHANGE", "CHAR", "CHARACTER", "CHECK", "COLLATE", "COLUMN", "CONDITION", "CONSTRAINT", "CONTINUE", "CONVERT", "CREATE", "CROSS", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "CURSOR",
049                "DATABASE", "DATABASES", "DAY_HOUR", "DAY_MICROSECOND", "DAY_MINUTE", "DAY_SECOND", "DEC", "DECIMAL", "DECLARE", "DEFAULT", "DELAYED", "DELETE", "DESC", "DESCRIBE", "DETERMINISTIC", "DISTINCT", "DISTINCTROW", "DIV", "DOUBLE", "DROP", "DUAL",
050                "EACH", "ELSE", "ELSEIF", "ENCLOSED", "ESCAPED", "EXISTS", "EXIT", "EXPLAIN",
051                "FALSE", "FETCH", "FLOAT", "FLOAT4", "FLOAT8", "FOR", "FORCE", "FOREIGN", "FROM", "FULLTEXT",
052                "GET", "GRANT", "GROUP",
053                "HAVING", "HIGH_PRIORITY", "HOUR_MICROSECOND", "HOUR_MINUTE", "HOUR_SECOND",
054                "IF", "IGNORE", "IN", "INDEX", "INFILE", "INNER", "INOUT", "INSENSITIVE", "INSERT", "INT", "INT1", "INT2", "INT3", "INT4", "INT8", "INTEGER", "INTERVAL", "INTO", "IO_AFTER_GTIDS", "IO_BEFORE_GTIDS", "IS", "ITERATE",
055                "JOIN",
056                "KEY", "KEYS", "KILL",
057                "LEADING", "LEAVE", "LEFT", "LIKE", "LIMIT", "LINEAR", "LINES", "LOAD", "LOCALTIME", "LOCALTIMESTAMP", "LOCK", "LONG", "LONGBLOB", "LONGTEXT", "LOOP", "LOW_PRIORITY",
058                "MASTER_BIND", "MASTER_SSL_VERIFY_SERVER_CERT", "MATCH", "MAXVALUE", "MEDIUMBLOB", "MEDIUMINT", "MEDIUMTEXT", "MIDDLEINT", "MINUTE_MICROSECOND", "MINUTE_SECOND", "MOD", "MODIFIES",
059                "NATURAL", "NONBLOCKING", "NOT", "NO_WRITE_TO_BINLOG", "NULL", "NUMERIC",
060                "ON", "OPTIMIZE", "OPTION", "OPTIONALLY", "OR", "ORDER", "OUT", "OUTER", "OUTFILE",
061                "PARTITION", "PRECISION", "PRIMARY", "PROCEDURE", "PURGE",
062                "RANGE", "READ", "READS", "READ_WRITE", "REAL", "REFERENCES", "REGEXP", "RELEASE", "RENAME", "REPEAT", "REPLACE", "REQUIRE", "RESIGNAL", "RESTRICT", "RETURN", "REVOKE", "RIGHT", "RLIKE",
063                "SCHEMA", "SCHEMAS", "SECOND_MICROSECOND", "SELECT", "SENSITIVE", "SEPARATOR", "SET", "SHOW", "SIGNAL", "SMALLINT", "SPATIAL", "SPECIFIC", "SQL", "SQLEXCEPTION", "SQLSTATE", "SQLWARNING", "SQL_BIG_RESULT", "SQL_CALC_FOUND_ROWS", "SQL_SMALL_RESULT", "SSL", "STARTING", "STRAIGHT_JOIN",
064                "TABLE", "TERMINATED", "THEN", "TINYBLOB", "TINYINT", "TINYTEXT", "TO", "TRAILING", "TRIGGER", "TRUE",
065                "UNDO", "UNION", "UNIQUE", "UNLOCK", "UNSIGNED", "UPDATE", "USAGE", "USE", "USING", "UTC_DATE", "UTC_TIME", "UTC_TIMESTAMP",
066                "VALUES", "VARBINARY", "VARCHAR", "VARCHARACTER", "VARYING",
067                "WHEN", "WHERE", "WHILE", "WITH", "WRITE",
068                "XOR",
069                "YEAR_MONTH",
070                "ZEROFILL",
071        };
072        private static final File PASSWORD_FILE = new File(String.format("%s%s.my.cnf", Strings.USER_HOME, Strings.FILE_SEPARATOR));
073        
074        /**
075         * {@inheritDoc}
076         * @see net.sf.hajdbc.dialect.StandardDialect#vendorPattern()
077         */
078        @Override
079        protected String vendorPattern()
080        {
081                return "mysql";
082        }
083
084        @Override
085        protected Set<String> reservedIdentifiers(DatabaseMetaData metaData)
086        {
087                return new HashSet<String>(Arrays.asList(RESERVED_KEY_WORDS));
088        }
089
090        /**
091         * @see net.sf.hajdbc.dialect.StandardDialect#getDefaultSchemas(java.sql.DatabaseMetaData)
092         */
093        @Override
094        public List<String> getDefaultSchemas(DatabaseMetaData metaData) throws SQLException
095        {
096                return Collections.singletonList(this.executeFunction(metaData.getConnection(), "DATABASE()"));
097        }
098
099        /**
100         * Deferrability clause is not supported.
101         * @see net.sf.hajdbc.dialect.StandardDialect#createForeignKeyConstraintFormat()
102         */
103        @Override
104        protected String createForeignKeyConstraintFormat()
105        {
106                return "ALTER TABLE {1} ADD CONSTRAINT {0} FOREIGN KEY ({2}) REFERENCES {3} ({4}) ON DELETE {5,choice,0#CASCADE|1#RESTRICT|2#SET NULL|3#NO ACTION|4#SET DEFAULT} ON UPDATE {6,choice,0#CASCADE|1#RESTRICT|2#SET NULL|3#NO ACTION|4#SET DEFAULT}";
107        }
108
109        /**
110         * @see net.sf.hajdbc.dialect.StandardDialect#createUniqueConstraintFormat()
111         */
112        @Override
113        protected String createUniqueConstraintFormat()
114        {
115                return "ALTER TABLE {1} ADD UNIQUE {0} ({2})";
116        }
117
118        /**
119         * @see net.sf.hajdbc.dialect.StandardDialect#dropForeignKeyConstraintFormat()
120         */
121        @Override
122        protected String dropForeignKeyConstraintFormat()
123        {
124                return "ALTER TABLE {1} DROP FOREIGN KEY {0}";
125        }
126
127        /**
128         * @see net.sf.hajdbc.dialect.StandardDialect#dropUniqueConstraintFormat()
129         */
130        @Override
131        protected String dropUniqueConstraintFormat()
132        {
133                return "ALTER TABLE {1} DROP INDEX {0}";
134        }
135
136        /**
137         * @see net.sf.hajdbc.dialect.StandardDialect#alterIdentityColumnFormat()
138         */
139        @Override
140        protected String alterIdentityColumnFormat()
141        {
142                return "ALTER TABLE {0} AUTO_INCREMENT = {2}";
143        }
144
145        /**
146         * @see net.sf.hajdbc.dialect.StandardDialect#currentDatePattern()
147         */
148        @Override
149        protected String currentDatePattern()
150        {
151                return super.currentDatePattern() + "|(?<=\\W)CURDATE\\s*\\(\\s*\\)";
152        }
153
154        /**
155         * @see net.sf.hajdbc.dialect.StandardDialect#currentTimePattern()
156         */
157        @Override
158        protected String currentTimePattern()
159        {
160                return super.currentTimePattern() + "|(?<=\\W)CURTIME\\s*\\(\\s*\\)";
161        }
162
163        /**
164         * @see net.sf.hajdbc.dialect.StandardDialect#currentTimestampPattern()
165         */
166        @Override
167        protected String currentTimestampPattern()
168        {
169                return super.currentTimestampPattern() + "|(?<=\\W)NOW\\s*\\(\\s*\\)|(?<=\\W)SYSDATE\\s*\\(\\s*\\)";
170        }
171        
172        /**
173         * @see net.sf.hajdbc.dialect.StandardDialect#dateLiteralFormat()
174         */
175        @Override
176        protected String dateLiteralFormat()
177        {
178                return this.timestampLiteralFormat();
179        }
180
181        /**
182         * @see net.sf.hajdbc.dialect.StandardDialect#timeLiteralFormat()
183         */
184        @Override
185        protected String timeLiteralFormat()
186        {
187                return this.timestampLiteralFormat();
188        }
189
190        /**
191         * @see net.sf.hajdbc.dialect.StandardDialect#timestampLiteralFormat()
192         */
193        @Override
194        protected String timestampLiteralFormat()
195        {
196                return "''{0}''";
197        }
198
199        /**
200         * {@inheritDoc}
201         * @see net.sf.hajdbc.dialect.StandardDialect#getDumpRestoreSupport()
202         */
203        @Override
204        public DumpRestoreSupport getDumpRestoreSupport()
205        {
206                return this;
207        }
208
209        @Override
210        public <Z, D extends Database<Z>> void dump(D database, Decoder decoder, File file, boolean dataOnly) throws Exception
211        {
212                ConnectionProperties properties = this.getConnectionProperties(database, decoder);
213                ProcessBuilder builder = new ProcessBuilder("mysqldump");
214                List<String> args = builder.command();
215                args.add("--host=" + properties.getHost());
216                args.add("--port=" + properties.getPort());
217                args.add("--user=" + properties.getUser());
218                args.add("--result-file=" + file.getPath());
219                args.add("--compress");
220                if (dataOnly)
221                {
222                        args.add("--no-create-info");
223                        args.add("--skip-triggers");
224                }
225                args.add(properties.getDatabase());
226                Processes.run(setPassword(builder, properties));
227        }
228
229        @Override
230        public <Z, D extends Database<Z>> void restore(D database, Decoder decoder, File file, boolean dataOnly) throws Exception
231        {
232                ConnectionProperties properties = this.getConnectionProperties(database, decoder);
233                ProcessBuilder builder = new ProcessBuilder("mysql");
234                List<String> args = builder.command();
235                args.add("--host=" + properties.getHost());
236                args.add("--port=" + properties.getPort());
237                args.add("--user=" + properties.getUser());
238                args.add(properties.getDatabase());
239                Processes.run(setPassword(builder, properties), file);
240        }
241        
242        private static ProcessBuilder setPassword(final ProcessBuilder builder, final ConnectionProperties properties)
243        {
244                String password = properties.getPassword();
245                if ((password != null) && !PASSWORD_FILE.exists())
246                {
247                        Processes.environment(builder).put("MYSQL_PWD", properties.getPassword());
248                }
249                return builder;
250        }
251}