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