001    /**
002     * Copyright (c) 2000-present Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.portal.verify;
016    
017    import com.liferay.portal.kernel.dao.db.DB;
018    import com.liferay.portal.kernel.dao.db.DBManagerUtil;
019    import com.liferay.portal.kernel.dao.db.DBType;
020    import com.liferay.portal.kernel.log.Log;
021    import com.liferay.portal.kernel.log.LogFactoryUtil;
022    import com.liferay.portal.kernel.util.GetterUtil;
023    import com.liferay.portal.kernel.util.LoggingTimer;
024    import com.liferay.portal.kernel.util.StringBundler;
025    import com.liferay.portal.kernel.util.StringUtil;
026    import com.liferay.portal.util.PropsValues;
027    
028    import java.sql.DatabaseMetaData;
029    import java.sql.ResultSet;
030    import java.sql.SQLException;
031    import java.sql.Statement;
032    import java.sql.Types;
033    
034    /**
035     * @author Brian Wing Shun Chan
036     * @author Shuyang Zhou
037     */
038    public class VerifyMySQL extends VerifyProcess {
039    
040            @Override
041            protected void doVerify() throws Exception {
042                    DB db = DBManagerUtil.getDB();
043    
044                    if (db.getDBType() != DBType.MYSQL) {
045                            return;
046                    }
047    
048                    verifyTableEngine();
049                    verifyDatetimePrecision();
050            }
051    
052            protected String getActualColumnType(
053                            Statement statement, String tableName, String columnName)
054                    throws SQLException {
055    
056                    StringBundler sb = new StringBundler(5);
057    
058                    sb.append("show columns from ");
059                    sb.append(tableName);
060                    sb.append(" like \"");
061                    sb.append(columnName);
062                    sb.append("\"");
063    
064                    try (ResultSet rs = statement.executeQuery(sb.toString())) {
065                            if (!rs.next()) {
066                                    throw new IllegalStateException(
067                                            "Table " + tableName + " does not have column " +
068                                                    columnName);
069                            }
070    
071                            return rs.getString("Type");
072                    }
073            }
074    
075            protected void verifyDatetimePrecision() throws Exception {
076                    DatabaseMetaData databaseMetaData = connection.getMetaData();
077    
078                    try (LoggingTimer loggingTimer = new LoggingTimer();
079                            Statement statement = connection.createStatement();
080                            ResultSet rs = databaseMetaData.getTables(
081                                    null, null, null, null)) {
082    
083                            while (rs.next()) {
084                                    verifyDatetimePrecisionForTable(
085                                            databaseMetaData, statement, rs.getString("TABLE_CAT"),
086                                            rs.getString("TABLE_SCHEM"), rs.getString("TABLE_NAME"));
087                            }
088                    }
089            }
090    
091            protected void verifyDatetimePrecisionForTable(
092                            DatabaseMetaData databaseMetaData, Statement statement,
093                            String catalog, String schemaPattern, String tableName)
094                    throws SQLException {
095    
096                    try (ResultSet rs = databaseMetaData.getColumns(
097                                    catalog, schemaPattern, tableName, null)) {
098    
099                            while (rs.next()) {
100                                    if (Types.TIMESTAMP != rs.getInt("DATA_TYPE")) {
101                                            continue;
102                                    }
103    
104                                    String columnName = rs.getString("COLUMN_NAME");
105    
106                                    String actualColumnType = getActualColumnType(
107                                            statement, tableName, columnName);
108    
109                                    if (actualColumnType.equals("datetime(6)")) {
110                                            continue;
111                                    }
112    
113                                    StringBundler sb = new StringBundler(5);
114    
115                                    sb.append("ALTER TABLE ");
116                                    sb.append(tableName);
117                                    sb.append(" MODIFY ");
118                                    sb.append(columnName);
119                                    sb.append(" datetime(6)");
120    
121                                    String sql = sb.toString();
122    
123                                    if (_log.isInfoEnabled()) {
124                                            _log.info(
125                                                    "Updating table " + tableName + " column " +
126                                                            columnName + " to datetime(6)");
127                                    }
128    
129                                    statement.executeUpdate(sql);
130                            }
131                    }
132            }
133    
134            protected void verifyTableEngine() throws Exception {
135                    try (LoggingTimer loggingTimer = new LoggingTimer();
136                            Statement statement = connection.createStatement();
137                            ResultSet rs = statement.executeQuery("show table status")) {
138    
139                            while (rs.next()) {
140                                    String tableName = rs.getString("Name");
141    
142                                    if (!isPortalTableName(tableName)) {
143                                            continue;
144                                    }
145    
146                                    String engine = GetterUtil.getString(rs.getString("Engine"));
147                                    String comment = GetterUtil.getString(rs.getString("Comment"));
148    
149                                    if (StringUtil.equalsIgnoreCase(comment, "VIEW")) {
150                                            continue;
151                                    }
152    
153                                    if (StringUtil.equalsIgnoreCase(
154                                                    engine, PropsValues.DATABASE_MYSQL_ENGINE)) {
155    
156                                            continue;
157                                    }
158    
159                                    if (_log.isInfoEnabled()) {
160                                            _log.info(
161                                                    "Updating table " + tableName + " to use engine " +
162                                                            PropsValues.DATABASE_MYSQL_ENGINE);
163                                    }
164    
165                                    statement.executeUpdate(
166                                            "alter table " + tableName + " engine " +
167                                                    PropsValues.DATABASE_MYSQL_ENGINE);
168                            }
169                    }
170            }
171    
172            private static final Log _log = LogFactoryUtil.getLog(VerifyMySQL.class);
173    
174    }