1 /*
2 * Copyright 2003 - 2013 The eFaps Team
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 * http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
15 *
16 * Revision: $Rev$
17 * Last Changed: $Date$
18 * Last Changed By: $Author$
19 */
20
21 package org.efaps.db.databases;
22
23 import java.sql.Connection;
24 import java.sql.DatabaseMetaData;
25 import java.sql.ResultSet;
26 import java.sql.SQLException;
27 import java.sql.Statement;
28 import java.util.Map;
29
30 import org.apache.commons.dbutils.BasicRowProcessor;
31 import org.apache.commons.dbutils.RowProcessor;
32 import org.efaps.db.databases.information.TableInformation;
33 import org.slf4j.Logger;
34 import org.slf4j.LoggerFactory;
35
36 import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
37
38 /**
39 * Database class for the MySQL database.
40 *
41 * @author The eFaps Team
42 * @version $Id$
43 */
44 public class MySQLDatabase
45 extends AbstractDatabase<MySQLDatabase>
46 {
47 /**
48 * Logging instance used in this class.
49 */
50 private static final Logger LOG = LoggerFactory.getLogger(PostgreSQLDatabase.class);
51
52 /**
53 * Prefix used for tables which simulates sequences.
54 *
55 * @see #createSequence(Connection, String, long)
56 * @see #deleteSequence(Connection, String)
57 * @see #existsSequence(Connection, String)
58 * @see #nextSequence(Connection, String)
59 * @see #setSequence(Connection, String, long)
60 */
61 private static final String PREFIX_SEQUENCE = "seq_";
62
63 /**
64 * Select statement to select all unique keys for current logged in
65 * MySQL database user.
66 *
67 * @see #initTableInfoUniqueKeys(Connection, String, Map)
68 */
69 private static final String SQL_UNIQUE_KEYS = "select "
70 + "a.constraint_name as INDEX_NAME, "
71 + "a.table_name as TABLE_NAME, "
72 + "b.column_name as COLUMN_NAME, "
73 + "b.ordinal_position as ORDINAL_POSITION "
74 + "from "
75 + "information_schema.table_constraints a,"
76 + "information_schema.key_column_usage b "
77 + "where "
78 + "a.constraint_type='UNIQUE' "
79 + "and a.table_schema=b.table_schema "
80 + "and a.table_name=b.table_name "
81 + "and a.constraint_name=b.constraint_name";
82
83 /**
84 * Select statement for all foreign keys for current logged in MySQL
85 * database user.
86 *
87 * @see #initTableInfoForeignKeys(Connection, String, Map)
88 */
89 private static final String SQL_FOREIGN_KEYS = "select "
90 + "a.TABLE_NAME as TABLE_NAME, "
91 + "a.CONSTRAINT_NAME as FK_NAME, "
92 + "b.COLUMN_NAME as FKCOLUMN_NAME, "
93 + "'' as DELETE_RULE, "
94 + "b.REFERENCED_TABLE_NAME as PKTABLE_NAME, "
95 + "b.REFERENCED_COLUMN_NAME as PKCOLUMN_NAME "
96 + "from "
97 + "information_schema.table_constraints a, "
98 + "information_schema.key_column_usage b "
99 + "where "
100 + "a.constraint_type='FOREIGN KEY' "
101 + "and a.CONSTRAINT_SCHEMA=b.CONSTRAINT_SCHEMA "
102 + "and a.CONSTRAINT_NAME=b.CONSTRAINT_NAME ";
103
104
105
106 /**
107 * Singleton processor instance that handlers share to save memory. Notice
108 * the default scoping to allow only classes in this package to use this
109 * instance.
110 */
111 private static final RowProcessor ROWPROCESSOR = new BasicRowProcessor();
112
113 /**
114 * Initializes the mapping between the eFaps column types and the MySQL
115 * specific column types.
116 */
117 public MySQLDatabase()
118 {
119 addMapping(ColumnType.INTEGER, "bigint", "null", "bigint", "integer", "int", "mediumint");
120 addMapping(ColumnType.DECIMAL, "decimal", "null", "decimal", "dec");
121 addMapping(ColumnType.REAL, "double", "null", "double", "float");
122 addMapping(ColumnType.STRING_SHORT, "varchar", "null", "text", "tinytext");
123 addMapping(ColumnType.STRING_LONG, "varchar", "null", "varchar");
124 addMapping(ColumnType.DATETIME, "datetime", "null", "datetime", "timestamp");
125 addMapping(ColumnType.BLOB, "longblob", "null", "longblob", "mediumblob", "blob", "tinyblob",
126 "varbinary", "binary");
127 addMapping(ColumnType.CLOB, "longtext", "null", "longtext");
128 addMapping(ColumnType.BOOLEAN, "boolean", "null", "boolean", "bool", "tinyint", "bit");
129 }
130
131 /**
132 * {@inheritDoc}
133 */
134 @Override
135 public boolean isConnected(final Connection _connection)
136 {
137 // FIXME must be implemented
138 return false;
139 }
140
141 /**
142 * @see org.efaps.db.databases.AbstractDatabase#getCurrentTimeStamp()
143 * @return "current_timestamp"
144 */
145 @Override
146 public String getCurrentTimeStamp()
147 {
148 return "current_timestamp";
149 }
150
151 /**
152 * {@inheritDoc}
153 */
154 @Override
155 public String getTimestampValue(final String _isoDateTime)
156 {
157 return "timestamp '" + _isoDateTime + "'";
158 }
159
160 /**
161 * {@inheritDoc}
162 */
163 @Override
164 public Object getBooleanValue(final Boolean _value)
165 {
166 return _value;
167 }
168
169 /**
170 * <p>This is the MySQL specific implementation of an all deletion.
171 * Following order is used to remove all eFaps specific information:
172 * <ul>
173 * <li>remove all views of the user</li>
174 * <li>remove all tables of the user</li>
175 * <li>remove all sequences of the user</li>
176 * </ul></p>
177 * <p>The table are dropped with cascade, so all depending sequences etc.
178 * are also dropped automatically. </p>
179 * <p>Attention! If application specific tables, views or constraints are
180 * defined, this database objects are also removed!</p>
181 *
182 * @param _con sql connection
183 * @throws SQLException on error while executing sql statements
184 */
185 @Override
186 @SuppressFBWarnings("SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE")
187 public void deleteAll(final Connection _con)
188 throws SQLException
189 {
190
191 final Statement stmtSel = _con.createStatement();
192 final Statement stmtExec = _con.createStatement();
193
194 try {
195 if (MySQLDatabase.LOG.isInfoEnabled()) {
196 MySQLDatabase.LOG.info("Remove all Tables");
197 }
198
199 final DatabaseMetaData metaData = _con.getMetaData();
200
201 // delete all views
202 final ResultSet rsViews = metaData.getTables(null, null, "%", new String[] { "VIEW" });
203 while (rsViews.next()) {
204 final String viewName = rsViews.getString("TABLE_NAME");
205 if (MySQLDatabase.LOG.isDebugEnabled()) {
206 MySQLDatabase.LOG.debug(" - View '" + viewName + "'");
207 }
208 stmtExec.execute("drop view " + viewName);
209 }
210 rsViews.close();
211
212 // delete all constraints
213 final ResultSet rsTables = metaData.getTables(null, null, "%", new String[] { "TABLE" });
214 while (rsTables.next()) {
215 final String tableName = rsTables.getString("TABLE_NAME");
216 final ResultSet rsf = _con.getMetaData().getImportedKeys(null, null, tableName);
217 while (rsf.next()) {
218 final String fkName = rsf.getString("FK_NAME").toUpperCase();
219 if (MySQLDatabase.LOG.isDebugEnabled()) {
220 MySQLDatabase.LOG.debug(" - Foreign Key '" + fkName + "'");
221 }
222 stmtExec.execute("alter table " + tableName + " drop foreign key " + fkName);
223 }
224 }
225
226 // delete all tables
227 rsTables.beforeFirst();
228 while (rsTables.next()) {
229 final String tableName = rsTables.getString("TABLE_NAME");
230 if (MySQLDatabase.LOG.isDebugEnabled()) {
231 MySQLDatabase.LOG.debug(" - Table '" + tableName + "'");
232 }
233 stmtExec.execute("drop table " + tableName + " cascade");
234 }
235 rsTables.close();
236
237 } finally {
238 stmtSel.close();
239 stmtExec.close();
240 }
241 }
242
243 /**
244 * {@inheritDoc}
245 */
246 @Override
247 public MySQLDatabase deleteView(final Connection _con,
248 final String _name)
249 throws SQLException
250 {
251 final Statement stmtExec = _con.createStatement();
252 stmtExec.execute("drop view " + _name);
253 return this;
254 }
255
256 /**
257 * For the MySQL database, an eFaps SQL table is created in this steps.
258 * <ul>
259 * <li>SQL table itself with column <code>ID</code> and unique key on the
260 * column is created</li>
261 * <li>if the table is an auto increment table (parent table is
262 * <code>null</code>, the column <code>ID</code> is set as auto increment
263 * column</li>
264 * <li>if no parent table is defined, the foreign key to the parent table is
265 * automatically set</li>
266 * </ul>
267 *
268 * @see org.efaps.db.databases.AbstractDatabase#createTable(java.sql.Connection, java.lang.String, java.lang.String)
269 * @param _con Connection to be used for the SQL statements
270 * @param _table name for the table
271 * @return this MySQL DB definition instance
272 * @throws SQLException if the table could not be created
273 */
274 @Override
275 public MySQLDatabase createTable(final Connection _con,
276 final String _table)
277 throws SQLException
278 {
279 final Statement stmt = _con.createStatement();
280 try {
281 stmt.executeUpdate(new StringBuilder()
282 .append("create table `").append(_table).append("` (")
283 .append("`ID` bigint ")
284 .append(",").append("constraint `").append(_table).append("_PK_ID` primary key (`ID`)")
285 .append(") engine InnoDB character set utf8;")
286 .toString());
287 } finally {
288 stmt.close();
289 }
290
291 return this;
292 }
293
294 /**
295 * {@inheritDoc}
296 */
297 @Override
298 public MySQLDatabase defineTableAutoIncrement(final Connection _con,
299 final String _table)
300 throws SQLException
301 {
302 final Statement stmt = _con.createStatement();
303 try {
304 // define for ID column the auto increment value
305 stmt.execute(new StringBuilder()
306 .append("alter table `").append(_table)
307 .append("` modify column `ID` bigint not null auto_increment")
308 .toString());
309 } finally {
310 stmt.close();
311 }
312 return this;
313 }
314
315 /**
316 * Overwrites original method because MySQL supports automatically
317 * generated keys.
318 *
319 * @return always <i>true</i> because generated keys are supported by MySQL
320 * database
321 * @see AbstractDatabase#supportsGetGeneratedKeys()
322 */
323 @Override
324 public boolean supportsGetGeneratedKeys()
325 {
326 return true;
327 }
328
329 /**
330 * Overwrites original method because MySQL supports binary input stream.
331 *
332 * @return always <i>true</i> because supported by MySQL database
333 * @see AbstractDatabase#supportsBinaryInputStream()
334 */
335 @Override
336 public boolean supportsBinaryInputStream()
337 {
338 return true;
339 }
340
341 /**
342 * Returns a single reversed apostrophe ` used to select tables within
343 * SQL statements for a MySQL database..
344 *
345 * @return always single reversed apostrophe
346 */
347 @Override
348 public String getTableQuote()
349 {
350 return "`";
351 }
352
353 /**
354 * Returns a single reversed apostrophe ` used to select columns within
355 * SQL statements for a MySQL database..
356 *
357 * @return always single reversed apostrophe
358 */
359 @Override
360 public String getColumnQuote()
361 {
362 return "`";
363 }
364
365 /**
366 * Creates a table with auto generated keys with table name as
367 * concatenation of the prefix {@link #PREFIX_SEQUENCE} and the lower case
368 * of <code>_name</code>. This table "simulates" the sequences (which are
369 * not supported by MySQL).
370 *
371 * @param _con SQL connection
372 * @param _name name of the sequence
373 * @param _startValue start value of the sequence number
374 * @return this instance
375 * @throws SQLException if SQL table could not be created; defined as auto
376 * increment table or if the sequence number could not
377 * be defined
378 * @see #createTable(Connection, String)
379 * @see #defineTableAutoIncrement(Connection, String)
380 * @see #setSequence(Connection, String, long)
381 * @see #PREFIX_SEQUENCE
382 */
383 @Override
384 public MySQLDatabase createSequence(final Connection _con,
385 final String _name,
386 final long _startValue)
387 throws SQLException
388 {
389 final String name = new StringBuilder()
390 .append(MySQLDatabase.PREFIX_SEQUENCE).append(_name.toLowerCase())
391 .toString();
392 createTable(_con, name);
393 defineTableAutoIncrement(_con, name);
394 setSequence(_con, _name, _startValue);
395 return this;
396 }
397
398 /**
399 * Deletes given sequence <code>_name</code> which is internally
400 * represented by this MySQL connector as normal SQL table. The name of the
401 * SQL table to delete is a concatenation of {@link #PREFIX_SEQUENCE} and
402 * <code>_name</code> in lower case.
403 *
404 * @param _con SQL connection
405 * @param _name name of the sequence
406 * @return this instance
407 * @throws SQLException if sequence (simulated by an auto increment SQL
408 * table) could not be deleted
409 * @see #PREFIX_SEQUENCE
410 */
411 @Override
412 public MySQLDatabase deleteSequence(final Connection _con,
413 final String _name)
414 throws SQLException
415 {
416 final String cmd = new StringBuilder()
417 .append("DROP TABLE `").append(MySQLDatabase.PREFIX_SEQUENCE).append(_name.toLowerCase()).append("`")
418 .toString();
419 final Statement stmt = _con.createStatement();
420 try {
421 stmt.executeUpdate(cmd);
422 } finally {
423 stmt.close();
424 }
425 return this;
426 }
427
428 /**
429 * Checks if the related table representing sequence <code>_name</code>
430 * exists.
431 *
432 * @param _con SQL connection
433 * @param _name name of the sequence
434 * @return <i>true</i> if a table with name as concatenation of
435 * {@link #PREFIX_SEQUENCE} and <code>_name</code> (in lower case)
436 * representing the sequence exists; otherwise <i>false</i>
437 * @throws SQLException if check for the existence of the table
438 * representing the sequence failed
439 * @see #existsTable(Connection, String)
440 * @see #PREFIX_SEQUENCE
441 */
442 @Override
443 public boolean existsSequence(final Connection _con,
444 final String _name)
445 throws SQLException
446 {
447 return existsTable(
448 _con,
449 new StringBuilder().append(MySQLDatabase.PREFIX_SEQUENCE).append(_name.toLowerCase()).toString());
450 }
451
452 /**
453 * Fetches next number for sequence <code>_name</code> by inserting new
454 * row into representing table. The new auto generated key is returned as
455 * next number of the sequence.
456 *
457 * @param _con SQL connection
458 * @param _name name of the sequence
459 * @return current inserted value of the table
460 * @throws SQLException if next number from the sequence could not be
461 * fetched
462 * @see #PREFIX_SEQUENCE
463 */
464 @Override
465 public long nextSequence(final Connection _con,
466 final String _name)
467 throws SQLException
468 {
469 final long ret;
470 final Statement stmt = _con.createStatement();
471 try {
472 // insert new line
473 final String insertCmd = new StringBuilder()
474 .append("INSERT INTO `").append(MySQLDatabase.PREFIX_SEQUENCE).append(_name.toLowerCase())
475 .append("` VALUES ()")
476 .toString();
477 final int row = stmt.executeUpdate(insertCmd, Statement.RETURN_GENERATED_KEYS);
478 if (row != 1) {
479 throw new SQLException("no sequence found for '" + _name + "'");
480 }
481
482 // fetch new number
483 final ResultSet resultset = stmt.getGeneratedKeys();
484 if (resultset.next()) {
485 ret = resultset.getLong(1);
486 } else {
487 throw new SQLException("no sequence found for '" + _name + "'");
488 }
489 } finally {
490 stmt.close();
491 }
492 return ret;
493 }
494
495 /**
496 * Defines new <code>_value</code> for sequence <code>_name</code>. Because
497 * in MySQL the sequences are simulated and the values from fetched
498 * sequence numbers are not deleted, all existing values in the table are
499 * first deleted (to be sure that the sequence could be reseted to already
500 * fetched numbers). After the new starting value is defined a first auto
501 * generated value is fetched from the database so that this value is also
502 * stored if the MySQL database is restarted.
503 *
504 * @param _con SQL connection
505 * @param _name name of the sequence
506 * @param _value new value of the sequence
507 * @return this instance
508 * @throws SQLException if new number of the sequence could not be defined
509 * for the table
510 * @see #PREFIX_SEQUENCE
511 */
512 @Override
513 public MySQLDatabase setSequence(final Connection _con,
514 final String _name,
515 final long _value)
516 throws SQLException
517 {
518 final String name = _name.toLowerCase();
519 final String lockCmd = new StringBuilder()
520 .append("LOCK TABLES `").append(MySQLDatabase.PREFIX_SEQUENCE).append(name)
521 .append("` WRITE")
522 .toString();
523 final String deleteCmd = new StringBuilder()
524 .append("DELETE FROM `").append(MySQLDatabase.PREFIX_SEQUENCE).append(name).append("`")
525 .toString();
526 final String alterCmd = new StringBuilder()
527 .append("ALTER TABLE `").append(MySQLDatabase.PREFIX_SEQUENCE).append(name)
528 .append("` AUTO_INCREMENT=").append(_value - 1)
529 .toString();
530 final String insertCmd = new StringBuilder()
531 .append("INSERT INTO `").append(MySQLDatabase.PREFIX_SEQUENCE).append(name)
532 .append("` VALUES ()")
533 .toString();
534 final String unlockCmd = new StringBuilder()
535 .append("UNLOCK TABLES")
536 .toString();
537
538 final Statement stmt = _con.createStatement();
539 try {
540 stmt.executeUpdate(lockCmd);
541 stmt.executeUpdate(deleteCmd);
542 stmt.executeUpdate(alterCmd);
543 stmt.executeUpdate(insertCmd);
544 stmt.executeUpdate(unlockCmd);
545 } finally {
546 stmt.close();
547 }
548
549 return this;
550 }
551
552 /**
553 * {@inheritDoc}
554 */
555 @Override
556 public String getHibernateDialect()
557 {
558 return "org.hibernate.dialect.MySQL5Dialect";
559 }
560
561 /**
562 * Overwrites the original method to specify SQL statement
563 * {@link #SQL_UNIQUE_KEYS} as replacement because the JDBC driver for
564 * MySQL does not handle matching table names.
565 *
566 * @param _con SQL connection
567 * @param _sql SQL statement (not used)
568 * @param _cache4Name map used to fetch depending on the table name the
569 * related table information
570 * @throws SQLException if unique keys could not be fetched
571 * @see #SQL_UNIQUE_KEYS
572 */
573 @Override
574 protected void initTableInfoUniqueKeys(final Connection _con,
575 final String _sql,
576 final Map<String, TableInformation> _cache4Name)
577 throws SQLException
578 {
579 super.initTableInfoUniqueKeys(_con, MySQLDatabase.SQL_UNIQUE_KEYS, _cache4Name);
580 }
581
582 /**
583 * Overwrites the original method to specify SQL statement
584 * {@link #SQL_FOREIGN_KEYS} as replacement because the JDBC driver for
585 * MySQL does not handle matching table names.
586 *
587 * @param _con SQL connection
588 * @param _sql SQL statement (not used)
589 * @param _cache4Name map used to fetch depending on the table name the
590 * related table information
591 * @throws SQLException if foreign keys could not be fetched
592 * @see #SQL_FOREIGN_KEYS
593 */
594 @Override
595 protected void initTableInfoForeignKeys(final Connection _con,
596 final String _sql,
597 final Map<String, TableInformation> _cache4Name)
598 throws SQLException
599 {
600 super.initTableInfoForeignKeys(_con, MySQLDatabase.SQL_FOREIGN_KEYS, _cache4Name);
601 }
602
603 /**
604 * {@inheritDoc}
605 */
606 @Override
607 protected StringBuilder getAlterColumn(final String _columnName,
608 final org.efaps.db.databases.AbstractDatabase.ColumnType _columnType)
609 {
610 final StringBuilder ret = new StringBuilder()
611 .append(" alter ").append(getColumnQuote()).append(_columnName).append(getColumnQuote())
612 .append(" ")
613 .append(getWriteSQLTypeName(_columnType));
614 return ret;
615 }
616
617 /**
618 * {@inheritDoc}
619 */
620 @Override
621 protected StringBuilder getAlterColumnIsNotNull(final String _columnName,
622 final boolean _isNotNull)
623 {
624 final StringBuilder ret = new StringBuilder()
625 .append(" alter column ").append(getColumnQuote()).append(_columnName).append(getColumnQuote());
626 if (_isNotNull) {
627 ret.append(" set ");
628 } else {
629 ret.append(" drop ");
630 }
631 ret.append(" not null");
632 return ret;
633 }
634
635 /**
636 * {@inheritDoc}
637 */
638 @Override
639 protected boolean check4NullValues(final Connection _con,
640 final String _tableName,
641 final String _columnName)
642 throws SQLException
643 {
644 boolean ret = true;
645 final StringBuilder cmd = new StringBuilder();
646 cmd.append("select count(*) from ").append(getTableQuote()).append(_tableName).append(getTableQuote())
647 .append(" where ").append(getColumnQuote()).append(_columnName).append(getColumnQuote())
648 .append(" is null");
649
650 MySQLDatabase.LOG.debug(" ..SQL> {}", cmd);
651
652 final Statement stmt = _con.createStatement();
653 ResultSet rs = null;
654 try {
655 rs = stmt.executeQuery(cmd.toString());
656 rs.next();
657 ret = rs.getInt(1) > 0;
658 } finally {
659 if (rs != null) {
660 rs.close();
661 }
662 stmt.close();
663 }
664 return ret;
665 }
666
667 /**
668 * {@inheritDoc}
669 */
670 @Override
671 public RowProcessor getRowProcessor()
672 {
673 return MySQLDatabase.ROWPROCESSOR;
674 }
675 }