001    /**
002     * Copyright (c) 2000-2012 Liferay, Inc. All rights reserved.
003     *
004     * The contents of this file are subject to the terms of the Liferay Enterprise
005     * Subscription License ("License"). You may not use this file except in
006     * compliance with the License. You can obtain a copy of the License by
007     * contacting Liferay, Inc. See the License for the specific language governing
008     * permissions and limitations under the License, including but not limited to
009     * distribution rights of the Software.
010     *
011     *
012     *
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(Connection con) throws SQLException {
060                    List<Index> indexes = new ArrayList<Index>();
061    
062                    PreparedStatement ps = null;
063                    ResultSet rs = null;
064    
065                    try {
066                            StringBundler sb = new StringBundler(4);
067    
068                            sb.append("select distinct(index_name), table_name, non_unique ");
069                            sb.append("from information_schema.statistics where ");
070                            sb.append("index_schema = database() and (index_name like ");
071                            sb.append("'LIFERAY_%' or index_name like 'IX_%')");
072    
073                            String sql = sb.toString();
074    
075                            ps = con.prepareStatement(sql);
076    
077                            rs = ps.executeQuery();
078    
079                            while (rs.next()) {
080                                    String indexName = rs.getString("index_name");
081                                    String tableName = rs.getString("table_name");
082                                    boolean unique = !rs.getBoolean("non_unique");
083    
084                                    indexes.add(new Index(indexName, tableName, unique));
085                            }
086                    }
087                    finally {
088                            DataAccess.cleanUp(null, ps, rs);
089                    }
090    
091                    return indexes;
092            }
093    
094            @Override
095            public boolean isSupportsDateMilliseconds() {
096                    return _SUPPORTS_DATE_MILLISECONDS;
097            }
098    
099            @Override
100            public boolean isSupportsUpdateWithInnerJoin() {
101                    return _SUPPORTS_UPDATE_WITH_INNER_JOIN;
102            }
103    
104            protected MySQLDB() {
105                    super(TYPE_MYSQL);
106            }
107    
108            @Override
109            protected String buildCreateFileContent(
110                            String sqlDir, String databaseName, int population)
111                    throws IOException {
112    
113                    String suffix = getSuffix(population);
114    
115                    StringBundler sb = new StringBundler(14);
116    
117                    sb.append("drop database if exists ");
118                    sb.append(databaseName);
119                    sb.append(";\n");
120                    sb.append("create database ");
121                    sb.append(databaseName);
122                    sb.append(" character set utf8;\n");
123                    sb.append("use ");
124                    sb.append(databaseName);
125                    sb.append(";\n\n");
126                    sb.append(
127                            readFile(
128                                    sqlDir + "/portal" + suffix + "/portal" + suffix +
129                                            "-mysql.sql"));
130                    sb.append("\n\n");
131                    sb.append(readFile(sqlDir + "/indexes/indexes-mysql.sql"));
132                    sb.append("\n\n");
133                    sb.append(readFile(sqlDir + "/sequences/sequences-mysql.sql"));
134    
135                    return sb.toString();
136            }
137    
138            @Override
139            protected String getServerName() {
140                    return "mysql";
141            }
142    
143            @Override
144            protected String[] getTemplate() {
145                    return _MYSQL;
146            }
147    
148            @Override
149            protected String reword(String data) throws IOException {
150                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
151                            new UnsyncStringReader(data));
152    
153                    boolean createTable = false;
154    
155                    StringBundler sb = new StringBundler();
156    
157                    String line = null;
158    
159                    while ((line = unsyncBufferedReader.readLine()) != null) {
160                            if (StringUtil.startsWith(line, "create table")) {
161                                    createTable = true;
162                            }
163                            else if (line.startsWith(ALTER_COLUMN_NAME)) {
164                                    String[] template = buildColumnNameTokens(line);
165    
166                                    line = StringUtil.replace(
167                                            "alter table @table@ change column @old-column@ " +
168                                                    "@new-column@ @type@;",
169                                            REWORD_TEMPLATE, template);
170                            }
171                            else if (line.startsWith(ALTER_COLUMN_TYPE)) {
172                                    String[] template = buildColumnTypeTokens(line);
173    
174                                    line = StringUtil.replace(
175                                            "alter table @table@ modify @old-column@ @type@;",
176                                            REWORD_TEMPLATE, template);
177                            }
178    
179                            int pos = line.indexOf(";");
180    
181                            if (createTable && (pos != -1)) {
182                                    createTable = false;
183    
184                                    line =
185                                            line.substring(0, pos) + " engine " +
186                                                    PropsValues.DATABASE_MYSQL_ENGINE + line.substring(pos);
187                            }
188    
189                            sb.append(line);
190                            sb.append("\n");
191                    }
192    
193                    unsyncBufferedReader.close();
194    
195                    return sb.toString();
196            }
197    
198            private static final String[] _MYSQL = {
199                    "##", "1", "0", "'1970-01-01'", "now()", " longblob", " longblob",
200                    " tinyint", " datetime", " double", " integer", " bigint", " longtext",
201                    " longtext", " varchar", "  auto_increment", "commit"
202            };
203    
204            private static final boolean _SUPPORTS_DATE_MILLISECONDS = false;
205    
206            private static final boolean _SUPPORTS_UPDATE_WITH_INNER_JOIN = true;
207    
208            private static MySQLDB _instance = new MySQLDB();
209    
210    }