1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * The contents of this file are subject to the terms of the Liferay Enterprise
5    * Subscription License ("License"). You may not use this file except in
6    * compliance with the License. You can obtain a copy of the License by
7    * contacting Liferay, Inc. See the License for the specific language governing
8    * permissions and limitations under the License, including but not limited to
9    * distribution rights of the Software.
10   *
11   *
12   *
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.StringBundler;
23  import com.liferay.portal.kernel.util.StringUtil;
24  
25  import java.io.IOException;
26  
27  import java.sql.Connection;
28  import java.sql.DatabaseMetaData;
29  import java.sql.PreparedStatement;
30  import java.sql.ResultSet;
31  import java.sql.SQLException;
32  
33  import java.util.ArrayList;
34  import java.util.List;
35  
36  /**
37   * <a href="SQLServerDB.java.html"><b><i>View Source</i></b></a>
38   *
39   * @author Alexander Chow
40   * @author Sandeep Soni
41   * @author Ganesh Ram
42   */
43  public class SQLServerDB extends BaseDB {
44  
45      public static DB getInstance() {
46          return _instance;
47      }
48  
49      public String buildSQL(String template) throws IOException {
50          template = convertTimestamp(template);
51          template = replaceTemplate(template, getTemplate());
52  
53          template = reword(template);
54          template = StringUtil.replace(template, "\ngo;\n", "\ngo\n");
55          template = StringUtil.replace(
56              template,
57              new String[] {"\\\\", "\\'", "\\\"", "\\n", "\\r"},
58              new String[] {"\\", "''", "\"", "\n", "\r"});
59  
60          return template;
61      }
62  
63      public List<Index> getIndexes() throws SQLException {
64          List<Index> indexes = new ArrayList<Index>();
65  
66          Connection con = null;
67          PreparedStatement ps = null;
68          ResultSet rs = null;
69  
70          try {
71              con = DataAccess.getConnection();
72  
73              DatabaseMetaData metaData = con.getMetaData();
74  
75              if (metaData.getDatabaseMajorVersion() <= _SQL_SERVER_2000) {
76                  return null;
77              }
78  
79              StringBundler sb = new StringBundler(6);
80  
81              sb.append("select sys.tables.name as table_name, ");
82              sb.append("sys.indexes.name as index_name, is_unique from ");
83              sb.append("sys.indexes inner join sys.tables on ");
84              sb.append("sys.tables.object_id = sys.indexes.object_id where ");
85              sb.append("sys.indexes.name like 'LIFERAY_%' or sys.indexes.name ");
86              sb.append("like 'IX_%'");
87  
88              String sql = sb.toString();
89  
90              ps = con.prepareStatement(sql);
91  
92              rs = ps.executeQuery();
93  
94              while (rs.next()) {
95                  String indexName = rs.getString("index_name");
96                  String tableName = rs.getString("table_name");
97                  boolean unique = !rs.getBoolean("is_unique");
98  
99                  indexes.add(new Index(indexName, tableName, unique));
100             }
101         }
102         finally {
103             DataAccess.cleanUp(con, ps, rs);
104         }
105 
106         return indexes;
107     }
108 
109     public boolean isSupportsAlterColumnType() {
110         return _SUPPORTS_ALTER_COLUMN_TYPE;
111     }
112 
113     protected SQLServerDB() {
114         super(TYPE_SQLSERVER);
115     }
116 
117     protected String buildCreateFileContent(
118             String sqlDir, String databaseName, int population)
119         throws IOException {
120 
121         String suffix = getSuffix(population);
122 
123         StringBundler sb = new StringBundler(17);
124 
125         sb.append("drop database ");
126         sb.append(databaseName);
127         sb.append(";\n");
128         sb.append("create database ");
129         sb.append(databaseName);
130         sb.append(";\n");
131         sb.append("\n");
132         sb.append("go\n");
133         sb.append("\n");
134         sb.append("use ");
135         sb.append(databaseName);
136         sb.append(";\n\n");
137         sb.append(
138             readFile(
139                 sqlDir + "/portal" + suffix + "/portal" + suffix +
140                     "-sql-server.sql"));
141         sb.append("\n\n");
142         sb.append(readFile(sqlDir + "/indexes/indexes-sql-server.sql"));
143         sb.append("\n\n");
144         sb.append(readFile(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 final boolean _SUPPORTS_ALTER_COLUMN_TYPE = false;
203 
204     private static SQLServerDB _instance = new SQLServerDB();
205 
206 }