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