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