1
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
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 }