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