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.StringPool;
024    import com.liferay.portal.kernel.util.StringUtil;
025    
026    import java.io.IOException;
027    
028    import java.sql.Connection;
029    import java.sql.DatabaseMetaData;
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 SQLServerDB extends BaseDB {
043    
044            public static DB getInstance() {
045                    return _instance;
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, "\ngo;\n", "\ngo\n");
055                    template = StringUtil.replace(
056                            template,
057                            new String[] {"\\\\", "\\'", "\\\"", "\\n", "\\r"},
058                            new String[] {"\\", "''", "\"", "\n", "\r"});
059    
060                    return template;
061            }
062    
063            @Override
064            public List<Index> getIndexes() throws SQLException {
065                    List<Index> indexes = new ArrayList<Index>();
066    
067                    Connection con = null;
068                    PreparedStatement ps = null;
069                    ResultSet rs = null;
070    
071                    try {
072                            con = DataAccess.getConnection();
073    
074                            DatabaseMetaData databaseMetaData = con.getMetaData();
075    
076                            if (databaseMetaData.getDatabaseMajorVersion() <=
077                                            _SQL_SERVER_2000) {
078    
079                                    return indexes;
080                            }
081    
082                            StringBundler sb = new StringBundler(6);
083    
084                            sb.append("select sys.tables.name as table_name, ");
085                            sb.append("sys.indexes.name as index_name, is_unique from ");
086                            sb.append("sys.indexes inner join sys.tables on ");
087                            sb.append("sys.tables.object_id = sys.indexes.object_id where ");
088                            sb.append("sys.indexes.name like 'LIFERAY_%' or sys.indexes.name ");
089                            sb.append("like 'IX_%'");
090    
091                            String sql = sb.toString();
092    
093                            ps = con.prepareStatement(sql);
094    
095                            rs = ps.executeQuery();
096    
097                            while (rs.next()) {
098                                    String indexName = rs.getString("index_name");
099                                    String tableName = rs.getString("table_name");
100                                    boolean unique = !rs.getBoolean("is_unique");
101    
102                                    indexes.add(new Index(indexName, tableName, unique));
103                            }
104                    }
105                    finally {
106                            DataAccess.cleanUp(con, ps, rs);
107                    }
108    
109                    return indexes;
110            }
111    
112            @Override
113            public boolean isSupportsAlterColumnType() {
114                    return _SUPPORTS_ALTER_COLUMN_TYPE;
115            }
116    
117            @Override
118            public boolean isSupportsInlineDistinct() {
119                    return _SUPPORTS_INLINE_DISTINCT;
120            }
121    
122            protected SQLServerDB() {
123                    super(TYPE_SQLSERVER);
124            }
125    
126            @Override
127            protected String buildCreateFileContent(
128                            String sqlDir, String databaseName, int population)
129                    throws IOException {
130    
131                    String suffix = getSuffix(population);
132    
133                    StringBundler sb = new StringBundler(17);
134    
135                    sb.append("drop database ");
136                    sb.append(databaseName);
137                    sb.append(";\n");
138                    sb.append("create database ");
139                    sb.append(databaseName);
140                    sb.append(";\n");
141                    sb.append("\n");
142                    sb.append("go\n");
143                    sb.append("\n");
144                    sb.append("use ");
145                    sb.append(databaseName);
146                    sb.append(";\n\n");
147                    sb.append(
148                            readFile(
149                                    sqlDir + "/portal" + suffix + "/portal" + suffix +
150                                            "-sql-server.sql"));
151                    sb.append("\n\n");
152                    sb.append(readFile(sqlDir + "/indexes/indexes-sql-server.sql"));
153                    sb.append("\n\n");
154                    sb.append(readFile(sqlDir + "/sequences/sequences-sql-server.sql"));
155    
156                    return sb.toString();
157            }
158    
159            @Override
160            protected String getServerName() {
161                    return "sql-server";
162            }
163    
164            @Override
165            protected String[] getTemplate() {
166                    return _SQL_SERVER;
167            }
168    
169            @Override
170            protected String reword(String data) throws IOException {
171                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
172                            new UnsyncStringReader(data));
173    
174                    StringBundler sb = new StringBundler();
175    
176                    String line = null;
177    
178                    while ((line = unsyncBufferedReader.readLine()) != null) {
179                            if (line.startsWith(ALTER_COLUMN_NAME)) {
180                                    String[] template = buildColumnNameTokens(line);
181    
182                                    line = StringUtil.replace(
183                                            "exec sp_rename '@table@.@old-column@', '@new-column@', " +
184                                                    "'column';",
185                                            REWORD_TEMPLATE, template);
186                            }
187                            else if (line.startsWith(ALTER_COLUMN_TYPE)) {
188                                    String[] template = buildColumnTypeTokens(line);
189    
190                                    line = StringUtil.replace(
191                                            "alter table @table@ alter column @old-column@ @type@;",
192                                            REWORD_TEMPLATE, template);
193                            }
194                            else if (line.indexOf(DROP_INDEX) != -1) {
195                                    String[] tokens = StringUtil.split(line, ' ');
196    
197                                    String tableName = tokens[4];
198    
199                                    if (tableName.endsWith(StringPool.SEMICOLON)) {
200                                            tableName = tableName.substring(0, tableName.length() - 1);
201                                    }
202    
203                                    line = StringUtil.replace(
204                                            "drop index @table@.@index@;", "@table@", tableName);
205                                    line = StringUtil.replace(line, "@index@", tokens[2]);
206                            }
207    
208                            sb.append(line);
209                            sb.append("\n");
210                    }
211    
212                    unsyncBufferedReader.close();
213    
214                    return sb.toString();
215            }
216    
217            private static final String[] _SQL_SERVER = {
218                    "--", "1", "0", "'19700101'", "GetDate()", " image", " image", " bit",
219                    " datetime", " float", " int", " bigint", " nvarchar(2000)", " ntext",
220                    " nvarchar", "  identity(1,1)", "go"
221            };
222    
223            private static final int _SQL_SERVER_2000 = 8;
224    
225            private static final boolean _SUPPORTS_ALTER_COLUMN_TYPE = false;
226    
227            private static final boolean _SUPPORTS_INLINE_DISTINCT = false;
228    
229            private static SQLServerDB _instance = new SQLServerDB();
230    
231    }