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