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.DBFactoryUtil;
019    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
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.StringBundler;
024    import com.liferay.portal.kernel.util.StringUtil;
025    import com.liferay.portal.util.PropsValues;
026    
027    import java.sql.Connection;
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 = DBFactoryUtil.getDB();
043    
044                    String dbType = db.getType();
045    
046                    if (!dbType.equals(DB.TYPE_MYSQL)) {
047                            return;
048                    }
049    
050                    try (Connection connection = DataAccess.getUpgradeOptimizedConnection();
051                            Statement statement = connection.createStatement()) {
052    
053                            verifyTableEngine(statement);
054    
055                            if (GetterUtil.getFloat(db.getVersionString()) < 5.6F) {
056                                    return;
057                            }
058    
059                            verifyDatetimePrecision(connection.getMetaData(), statement);
060                    }
061            }
062    
063            protected String getActualColumnType(
064                            Statement statement, String tableName, String columnName)
065                    throws SQLException {
066    
067                    StringBundler sb = new StringBundler(5);
068    
069                    sb.append("show columns from ");
070                    sb.append(tableName);
071                    sb.append(" like \"");
072                    sb.append(columnName);
073                    sb.append("\"");
074    
075                    try (ResultSet rs = statement.executeQuery(sb.toString())) {
076                            if (!rs.next()) {
077                                    throw new IllegalStateException(
078                                            "Table " + tableName + " does not have column " +
079                                                    columnName);
080                            }
081    
082                            return rs.getString("Type");
083                    }
084            }
085    
086            protected void verifyDatetimePrecision(
087                            DatabaseMetaData databaseMetaData, Statement statement)
088                    throws Exception {
089    
090                    try (ResultSet rs = databaseMetaData.getTables(
091                            null, null, null, null)) {
092    
093                            while (rs.next()) {
094                                    verifyDatetimePrecisionForTable(
095                                            databaseMetaData, statement, rs.getString("TABLE_CAT"),
096                                            rs.getString("TABLE_SCHEM"), rs.getString("TABLE_NAME"));
097                            }
098                    }
099            }
100    
101            protected void verifyDatetimePrecisionForTable(
102                            DatabaseMetaData databaseMetaData, Statement statement,
103                            String catalog, String schemaPattern, String tableName)
104                    throws SQLException {
105    
106                    try (ResultSet rs = databaseMetaData.getColumns(
107                                    catalog, schemaPattern, tableName, null)) {
108    
109                            while (rs.next()) {
110                                    if (Types.TIMESTAMP != rs.getInt("DATA_TYPE")) {
111                                            continue;
112                                    }
113    
114                                    String columnName = rs.getString("COLUMN_NAME");
115    
116                                    String actualColumnType = getActualColumnType(
117                                            statement, tableName, columnName);
118    
119                                    if (actualColumnType.equals("datetime(6)")) {
120                                            continue;
121                                    }
122    
123                                    StringBundler sb = new StringBundler(5);
124    
125                                    sb.append("ALTER TABLE ");
126                                    sb.append(tableName);
127                                    sb.append(" MODIFY ");
128                                    sb.append(columnName);
129                                    sb.append(" datetime(6)");
130    
131                                    String sql = sb.toString();
132    
133                                    if (_log.isInfoEnabled()) {
134                                            _log.info(
135                                                    "Updating table " + tableName + " column " +
136                                                            columnName + " to datetime(6)");
137                                    }
138    
139                                    statement.executeUpdate(sql);
140                            }
141                    }
142            }
143    
144            protected void verifyTableEngine(Statement statement) throws Exception {
145                    try (ResultSet rs = statement.executeQuery("show table status")) {
146                            while (rs.next()) {
147                                    String tableName = rs.getString("Name");
148    
149                                    if (!isPortalTableName(tableName)) {
150                                            continue;
151                                    }
152    
153                                    String engine = GetterUtil.getString(rs.getString("Engine"));
154                                    String comment = GetterUtil.getString(rs.getString("Comment"));
155    
156                                    if (StringUtil.equalsIgnoreCase(comment, "VIEW")) {
157                                            continue;
158                                    }
159    
160                                    if (StringUtil.equalsIgnoreCase(
161                                                    engine, PropsValues.DATABASE_MYSQL_ENGINE)) {
162    
163                                            continue;
164                                    }
165    
166                                    if (_log.isInfoEnabled()) {
167                                            _log.info(
168                                                    "Updating table " + tableName + " to use engine " +
169                                                            PropsValues.DATABASE_MYSQL_ENGINE);
170                                    }
171    
172                                    statement.executeUpdate(
173                                            "alter table " + tableName + " engine " +
174                                                    PropsValues.DATABASE_MYSQL_ENGINE);
175                            }
176                    }
177            }
178    
179            private static final Log _log = LogFactoryUtil.getLog(VerifyMySQL.class);
180    
181    }