001    /**
002     * Copyright (c) 2000-2010 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.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            protected SQLServerDB() {
112                    super(TYPE_SQLSERVER);
113            }
114    
115            protected String buildCreateFileContent(
116                            String sqlDir, String databaseName, int population)
117                    throws IOException {
118    
119                    String suffix = getSuffix(population);
120    
121                    StringBundler sb = new StringBundler(17);
122    
123                    sb.append("drop database ");
124                    sb.append(databaseName);
125                    sb.append(";\n");
126                    sb.append("create database ");
127                    sb.append(databaseName);
128                    sb.append(";\n");
129                    sb.append("\n");
130                    sb.append("go\n");
131                    sb.append("\n");
132                    sb.append("use ");
133                    sb.append(databaseName);
134                    sb.append(";\n\n");
135                    sb.append(
136                            readFile(
137                                    sqlDir + "/portal" + suffix + "/portal" + suffix +
138                                            "-sql-server.sql"));
139                    sb.append("\n\n");
140                    sb.append(readFile(sqlDir + "/indexes/indexes-sql-server.sql"));
141                    sb.append("\n\n");
142                    sb.append(readFile(sqlDir + "/sequences/sequences-sql-server.sql"));
143    
144                    return sb.toString();
145            }
146    
147            protected String getServerName() {
148                    return "sql-server";
149            }
150    
151            protected String[] getTemplate() {
152                    return _SQL_SERVER;
153            }
154    
155            protected String reword(String data) throws IOException {
156                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
157                            new UnsyncStringReader(data));
158    
159                    StringBundler sb = new StringBundler();
160    
161                    String line = null;
162    
163                    while ((line = unsyncBufferedReader.readLine()) != null) {
164                            if (line.startsWith(ALTER_COLUMN_NAME)) {
165                                    String[] template = buildColumnNameTokens(line);
166    
167                                    line = StringUtil.replace(
168                                            "exec sp_rename '@table@.@old-column@', '@new-column@', " +
169                                                    "'column';",
170                                            REWORD_TEMPLATE, template);
171                            }
172                            else if (line.startsWith(ALTER_COLUMN_TYPE)) {
173                                    String[] template = buildColumnTypeTokens(line);
174    
175                                    line = StringUtil.replace(
176                                            "alter table @table@ alter column @old-column@ @type@;",
177                                            REWORD_TEMPLATE, template);
178                            }
179    
180                            sb.append(line);
181                            sb.append("\n");
182                    }
183    
184                    unsyncBufferedReader.close();
185    
186                    return sb.toString();
187            }
188    
189            private static String[] _SQL_SERVER = {
190                    "--", "1", "0",
191                    "'19700101'", "GetDate()",
192                    " image", " bit", " datetime",
193                    " float", " int", " bigint",
194                    " nvarchar(2000)", " ntext", " nvarchar",
195                    "  identity(1,1)", "go"
196            };
197    
198            private static final int _SQL_SERVER_2000 = 8;
199    
200            private static final boolean _SUPPORTS_ALTER_COLUMN_TYPE = false;
201    
202            private static SQLServerDB _instance = new SQLServerDB();
203    
204    }