001
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
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 }