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