1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * This library is free software; you can redistribute it and/or modify it under
5    * the terms of the GNU Lesser General Public License as published by the Free
6    * Software Foundation; either version 2.1 of the License, or (at your option)
7    * any later version.
8    *
9    * This library is distributed in the hope that it will be useful, but WITHOUT
10   * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
11   * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
12   * details.
13   */
14  
15  package com.liferay.portal.dao.db;
16  
17  import com.liferay.portal.kernel.dao.db.DB;
18  import com.liferay.portal.kernel.dao.db.Index;
19  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
20  import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
21  import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
22  import com.liferay.portal.kernel.util.FileUtil;
23  import com.liferay.portal.kernel.util.StringBundler;
24  import com.liferay.portal.kernel.util.StringUtil;
25  
26  import java.io.IOException;
27  
28  import java.sql.Connection;
29  import java.sql.DatabaseMetaData;
30  import java.sql.PreparedStatement;
31  import java.sql.ResultSet;
32  import java.sql.SQLException;
33  
34  import java.util.ArrayList;
35  import java.util.List;
36  
37  /**
38   * <a href="SQLServerDB.java.html"><b><i>View Source</i></b></a>
39   *
40   * @author Alexander Chow
41   * @author Sandeep Soni
42   * @author Ganesh Ram
43   */
44  public class SQLServerDB extends BaseDB {
45  
46      public static DB getInstance() {
47          return _instance;
48      }
49  
50      public String buildSQL(String template) throws IOException {
51          template = convertTimestamp(template);
52          template = replaceTemplate(template, getTemplate());
53  
54          template = reword(template);
55          template = StringUtil.replace(template, "\ngo;\n", "\ngo\n");
56          template = StringUtil.replace(
57              template,
58              new String[] {"\\\\", "\\'", "\\\"", "\\n", "\\r"},
59              new String[] {"\\", "''", "\"", "\n", "\r"});
60  
61          return template;
62      }
63  
64      public List<Index> getIndexes() throws SQLException {
65          List<Index> indexes = new ArrayList<Index>();
66  
67          Connection con = null;
68          PreparedStatement ps = null;
69          ResultSet rs = null;
70  
71          try {
72              con = DataAccess.getConnection();
73  
74              DatabaseMetaData metaData = con.getMetaData();
75  
76              if (metaData.getDatabaseMajorVersion() <= _SQL_SERVER_2000) {
77                  return null;
78              }
79  
80              StringBundler sb = new StringBundler(6);
81  
82              sb.append("select sys.tables.name as table_name, ");
83              sb.append("sys.indexes.name as index_name, is_unique from ");
84              sb.append("sys.indexes inner join sys.tables on ");
85              sb.append("sys.tables.object_id = sys.indexes.object_id where ");
86              sb.append("sys.indexes.name like 'LIFERAY_%' or sys.indexes.name ");
87              sb.append("like 'IX_%'");
88  
89              String sql = sb.toString();
90  
91              ps = con.prepareStatement(sql);
92  
93              rs = ps.executeQuery();
94  
95              while (rs.next()) {
96                  String indexName = rs.getString("index_name");
97                  String tableName = rs.getString("table_name");
98                  boolean unique = !rs.getBoolean("is_unique");
99  
100                 indexes.add(new Index(indexName, tableName, unique));
101             }
102         }
103         finally {
104             DataAccess.cleanUp(con, ps, rs);
105         }
106 
107         return indexes;
108     }
109 
110     public boolean isSupportsAlterColumnType() {
111         return _SUPPORTS_ALTER_COLUMN_TYPE;
112     }
113 
114     protected SQLServerDB() {
115         super(TYPE_SQLSERVER);
116     }
117 
118     protected String buildCreateFileContent(
119             String sqlDir, String databaseName, int population)
120         throws IOException {
121 
122         String suffix = getSuffix(population);
123 
124         StringBundler sb = new StringBundler(17);
125 
126         sb.append("drop database ");
127         sb.append(databaseName);
128         sb.append(";\n");
129         sb.append("create database ");
130         sb.append(databaseName);
131         sb.append(";\n");
132         sb.append("\n");
133         sb.append("go\n");
134         sb.append("\n");
135         sb.append("use ");
136         sb.append(databaseName);
137         sb.append(";\n\n");
138         sb.append(
139             FileUtil.read(
140                 sqlDir + "/portal" + suffix + "/portal" + suffix +
141                     "-sql-server.sql"));
142         sb.append("\n\n");
143         sb.append(FileUtil.read(sqlDir + "/indexes/indexes-sql-server.sql"));
144         sb.append("\n\n");
145         sb.append(
146             FileUtil.read(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 boolean _SUPPORTS_ALTER_COLUMN_TYPE;
205 
206     private static SQLServerDB _instance = new SQLServerDB();
207 
208 }