1   /**
2    * Copyright (c) 2000-2009 Liferay, Inc. All rights reserved.
3    *
4    *
5    *
6    *
7    * The contents of this file are subject to the terms of the Liferay Enterprise
8    * Subscription License ("License"). You may not use this file except in
9    * compliance with the License. You can obtain a copy of the License by
10   * contacting Liferay, Inc. See the License for the specific language governing
11   * permissions and limitations under the License, including but not limited to
12   * distribution rights of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portal.dao.db;
24  
25  import com.liferay.portal.kernel.dao.db.DB;
26  import com.liferay.portal.kernel.dao.db.Index;
27  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
28  import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
29  import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
30  import com.liferay.portal.kernel.util.FileUtil;
31  import com.liferay.portal.kernel.util.StringUtil;
32  
33  import java.io.IOException;
34  
35  import java.sql.Connection;
36  import java.sql.DatabaseMetaData;
37  import java.sql.PreparedStatement;
38  import java.sql.ResultSet;
39  import java.sql.SQLException;
40  
41  import java.util.ArrayList;
42  import java.util.List;
43  
44  /**
45   * <a href="SQLServerDB.java.html"><b><i>View Source</i></b></a>
46   *
47   * @author Alexander Chow
48   * @author Sandeep Soni
49   * @author Ganesh Ram
50   */
51  public class SQLServerDB extends BaseDB {
52  
53      public static DB getInstance() {
54          return _instance;
55      }
56  
57      public String buildSQL(String template) throws IOException {
58          template = convertTimestamp(template);
59          template = replaceTemplate(template, getTemplate());
60  
61          template = reword(template);
62          template = StringUtil.replace(template, "\ngo;\n", "\ngo\n");
63          template = StringUtil.replace(
64              template,
65              new String[] {"\\\\", "\\'", "\\\"", "\\n", "\\r"},
66              new String[] {"\\", "''", "\"", "\n", "\r"});
67  
68          return template;
69      }
70  
71      public List<Index> getIndexes() throws SQLException {
72          List<Index> indexes = new ArrayList<Index>();
73  
74          Connection con = null;
75          PreparedStatement ps = null;
76          ResultSet rs = null;
77  
78          try {
79              con = DataAccess.getConnection();
80  
81              DatabaseMetaData metaData = con.getMetaData();
82  
83              if (metaData.getDatabaseMajorVersion() <= _SQL_SERVER_2000) {
84                  return null;
85              }
86  
87              StringBuilder sb = new StringBuilder();
88  
89              sb.append("select sys.tables.name as table_name, ");
90              sb.append("sys.indexes.name as index_name, is_unique from ");
91              sb.append("sys.indexes inner join sys.tables on ");
92              sb.append("sys.tables.object_id = sys.indexes.object_id where ");
93              sb.append("sys.indexes.name like 'LIFERAY_%' or sys.indexes.name ");
94              sb.append("like 'IX_%'");
95  
96              String sql = sb.toString();
97  
98              ps = con.prepareStatement(sql);
99  
100             rs = ps.executeQuery();
101 
102             while (rs.next()) {
103                 String indexName = rs.getString("index_name");
104                 String tableName = rs.getString("table_name");
105                 boolean unique = !rs.getBoolean("is_unique");
106 
107                 indexes.add(new Index(indexName, tableName, unique));
108             }
109         }
110         finally {
111             DataAccess.cleanUp(con, ps, rs);
112         }
113 
114         return indexes;
115     }
116 
117     public boolean isSupportsAlterColumnType() {
118         return _SUPPORTS_ALTER_COLUMN_TYPE;
119     }
120 
121     protected SQLServerDB() {
122         super(TYPE_SQLSERVER);
123     }
124 
125     protected String buildCreateFileContent(String databaseName, int population)
126         throws IOException {
127 
128         String suffix = getSuffix(population);
129 
130         StringBuilder sb = new StringBuilder();
131 
132         sb.append("drop database " + databaseName + ";\n");
133         sb.append("create database " + databaseName + ";\n");
134         sb.append("\n");
135         sb.append("go\n");
136         sb.append("\n");
137         sb.append("use " + databaseName + ";\n\n");
138         sb.append(
139             FileUtil.read(
140                 "../sql/portal" + suffix + "/portal" + suffix +
141                     "-sql-server.sql"));
142         sb.append("\n\n");
143         sb.append(FileUtil.read("../sql/indexes/indexes-sql-server.sql"));
144         sb.append("\n\n");
145         sb.append(FileUtil.read("../sql/sequences/sequences-sql-server.sql"));
146 
147         return sb.toString();
148     }
149 
150     protected String getServerName() {
151         return "sql-server";
152     }
153 
154     protected String[] getTemplate() {
155         return _SQL_SERVER;
156     }
157 
158     protected String reword(String data) throws IOException {
159         UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
160             new UnsyncStringReader(data));
161 
162         StringBuilder sb = new StringBuilder();
163 
164         String line = null;
165 
166         while ((line = unsyncBufferedReader.readLine()) != null) {
167             if (line.startsWith(ALTER_COLUMN_NAME)) {
168                 String[] template = buildColumnNameTokens(line);
169 
170                 line = StringUtil.replace(
171                     "exec sp_rename '@table@.@old-column@', '@new-column@', " +
172                         "'column';",
173                     REWORD_TEMPLATE, template);
174             }
175             else if (line.startsWith(ALTER_COLUMN_TYPE)) {
176                 String[] template = buildColumnTypeTokens(line);
177 
178                 line = StringUtil.replace(
179                     "alter table @table@ alter column @old-column@ @type@;",
180                     REWORD_TEMPLATE, template);
181             }
182 
183             sb.append(line);
184             sb.append("\n");
185         }
186 
187         unsyncBufferedReader.close();
188 
189         return sb.toString();
190     }
191 
192     private static String[] _SQL_SERVER = {
193         "--", "1", "0",
194         "'19700101'", "GetDate()",
195         " image", " bit", " datetime",
196         " float", " int", " bigint",
197         " nvarchar(2000)", " ntext", " nvarchar",
198         "  identity(1,1)", "go"
199     };
200 
201     private static final int _SQL_SERVER_2000 = 8;
202 
203     private static boolean _SUPPORTS_ALTER_COLUMN_TYPE;
204 
205     private static SQLServerDB _instance = new SQLServerDB();
206 
207 }