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.dao.db;
016    
017    import com.liferay.portal.kernel.dao.db.DBType;
018    import com.liferay.portal.kernel.dao.db.Index;
019    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
020    import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
021    import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
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.io.IOException;
028    
029    import java.sql.Connection;
030    import java.sql.PreparedStatement;
031    import java.sql.ResultSet;
032    import java.sql.SQLException;
033    
034    import java.util.ArrayList;
035    import java.util.List;
036    
037    /**
038     * @author Alexander Chow
039     * @author Sandeep Soni
040     * @author Ganesh Ram
041     */
042    public class MySQLDB extends BaseDB {
043    
044            public MySQLDB(int majorVersion, int minorVersion) {
045                    super(DBType.MYSQL, majorVersion, minorVersion);
046            }
047    
048            @Override
049            public String buildSQL(String template) throws IOException {
050                    template = convertTimestamp(template);
051                    template = replaceTemplate(template, getTemplate());
052    
053                    template = reword(template);
054                    template = StringUtil.replace(template, "\\'", "''");
055    
056                    return template;
057            }
058    
059            @Override
060            public List<Index> getIndexes(Connection con) throws SQLException {
061                    List<Index> indexes = new ArrayList<>();
062    
063                    PreparedStatement ps = null;
064                    ResultSet rs = null;
065    
066                    try {
067                            StringBundler sb = new StringBundler(4);
068    
069                            sb.append("select distinct(index_name), table_name, non_unique ");
070                            sb.append("from information_schema.statistics where ");
071                            sb.append("index_schema = database() and (index_name like ");
072                            sb.append("'LIFERAY_%' or index_name like 'IX_%')");
073    
074                            String sql = sb.toString();
075    
076                            ps = con.prepareStatement(sql);
077    
078                            rs = ps.executeQuery();
079    
080                            while (rs.next()) {
081                                    String indexName = rs.getString("index_name");
082                                    String tableName = rs.getString("table_name");
083                                    boolean unique = !rs.getBoolean("non_unique");
084    
085                                    indexes.add(new Index(indexName, tableName, unique));
086                            }
087                    }
088                    finally {
089                            DataAccess.cleanUp(ps, rs);
090                    }
091    
092                    return indexes;
093            }
094    
095            @Override
096            public boolean isSupportsUpdateWithInnerJoin() {
097                    return _SUPPORTS_UPDATE_WITH_INNER_JOIN;
098            }
099    
100            @Override
101            protected String buildCreateFileContent(
102                            String sqlDir, String databaseName, int population)
103                    throws IOException {
104    
105                    StringBundler sb = new StringBundler(14);
106    
107                    sb.append("drop database if exists ");
108                    sb.append(databaseName);
109                    sb.append(";\n");
110                    sb.append("create database ");
111                    sb.append(databaseName);
112                    sb.append(" character set utf8;\n");
113    
114                    if (population != BARE) {
115                            sb.append("use ");
116                            sb.append(databaseName);
117                            sb.append(";\n\n");
118    
119                            String suffix = getSuffix(population);
120    
121                            sb.append(getCreateTablesContent(sqlDir, suffix));
122                            sb.append("\n\n");
123                            sb.append(readFile(sqlDir + "/indexes/indexes-mysql.sql"));
124                            sb.append("\n\n");
125                            sb.append(readFile(sqlDir + "/sequences/sequences-mysql.sql"));
126                    }
127    
128                    return sb.toString();
129            }
130    
131            @Override
132            protected String getServerName() {
133                    return "mysql";
134            }
135    
136            @Override
137            protected String[] getTemplate() {
138                    if (GetterUtil.getFloat(getVersionString()) >= 5.6F) {
139                            _MYSQL[8] = " datetime(6)";
140                    }
141    
142                    return _MYSQL;
143            }
144    
145            @Override
146            protected String reword(String data) throws IOException {
147                    try (UnsyncBufferedReader unsyncBufferedReader =
148                                    new UnsyncBufferedReader(new UnsyncStringReader(data))) {
149    
150                            StringBundler sb = new StringBundler();
151    
152                            boolean createTable = false;
153    
154                            String line = null;
155    
156                            while ((line = unsyncBufferedReader.readLine()) != null) {
157                                    if (StringUtil.startsWith(line, "create table")) {
158                                            createTable = true;
159                                    }
160                                    else if (line.startsWith(ALTER_COLUMN_NAME)) {
161                                            String[] template = buildColumnNameTokens(line);
162    
163                                            line = StringUtil.replace(
164                                                    "alter table @table@ change column @old-column@ " +
165                                                            "@new-column@ @type@;",
166                                                    REWORD_TEMPLATE, template);
167                                    }
168                                    else if (line.startsWith(ALTER_COLUMN_TYPE)) {
169                                            String[] template = buildColumnTypeTokens(line);
170    
171                                            line = StringUtil.replace(
172                                                    "alter table @table@ modify @old-column@ @type@;",
173                                                    REWORD_TEMPLATE, template);
174                                    }
175                                    else if (line.startsWith(ALTER_TABLE_NAME)) {
176                                            String[] template = buildTableNameTokens(line);
177    
178                                            line = StringUtil.replace(
179                                                    "rename table @old-table@ to @new-table@;",
180                                                    RENAME_TABLE_TEMPLATE, template);
181                                    }
182    
183                                    int pos = line.indexOf(";");
184    
185                                    if (createTable && (pos != -1)) {
186                                            createTable = false;
187    
188                                            line =
189                                                    line.substring(0, pos) + " engine " +
190                                                            PropsValues.DATABASE_MYSQL_ENGINE +
191                                                                    line.substring(pos);
192                                    }
193    
194                                    sb.append(line);
195                                    sb.append("\n");
196                            }
197    
198                            return sb.toString();
199                    }
200            }
201    
202            private static final String[] _MYSQL = {
203                    "##", "1", "0", "'1970-01-01'", "now()", " longblob", " longblob",
204                    " tinyint", " datetime", " double", " integer", " bigint", " longtext",
205                    " longtext", " varchar", "  auto_increment", "commit"
206            };
207    
208            private static final boolean _SUPPORTS_UPDATE_WITH_INNER_JOIN = true;
209    
210    }