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