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