001
014
015 package com.liferay.portal.dao.db;
016
017 import com.liferay.portal.kernel.dao.db.DB;
018 import com.liferay.portal.kernel.dao.db.Index;
019 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
020 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
021 import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
022 import com.liferay.portal.kernel.util.StringBundler;
023 import com.liferay.portal.kernel.util.StringPool;
024 import com.liferay.portal.kernel.util.StringUtil;
025
026 import java.io.IOException;
027
028 import java.sql.Connection;
029 import java.sql.DatabaseMetaData;
030 import java.sql.PreparedStatement;
031 import java.sql.ResultSet;
032 import java.sql.SQLException;
033
034 import java.util.ArrayList;
035 import java.util.List;
036
037
042 public class SQLServerDB extends BaseDB {
043
044 public static DB getInstance() {
045 return _instance;
046 }
047
048 @Override
049 public String buildSQL(String template) throws IOException {
050 template = convertTimestamp(template);
051 template = replaceTemplate(template, getTemplate());
052
053 template = reword(template);
054 template = StringUtil.replace(template, "\ngo;\n", "\ngo\n");
055 template = StringUtil.replace(
056 template,
057 new String[] {"\\\\", "\\'", "\\\"", "\\n", "\\r"},
058 new String[] {"\\", "''", "\"", "\n", "\r"});
059
060 return template;
061 }
062
063 @Override
064 public List<Index> getIndexes() throws SQLException {
065 List<Index> indexes = new ArrayList<Index>();
066
067 Connection con = null;
068 PreparedStatement ps = null;
069 ResultSet rs = null;
070
071 try {
072 con = DataAccess.getConnection();
073
074 DatabaseMetaData databaseMetaData = con.getMetaData();
075
076 if (databaseMetaData.getDatabaseMajorVersion() <=
077 _SQL_SERVER_2000) {
078
079 return indexes;
080 }
081
082 StringBundler sb = new StringBundler(6);
083
084 sb.append("select sys.tables.name as table_name, ");
085 sb.append("sys.indexes.name as index_name, is_unique from ");
086 sb.append("sys.indexes inner join sys.tables on ");
087 sb.append("sys.tables.object_id = sys.indexes.object_id where ");
088 sb.append("sys.indexes.name like 'LIFERAY_%' or sys.indexes.name ");
089 sb.append("like 'IX_%'");
090
091 String sql = sb.toString();
092
093 ps = con.prepareStatement(sql);
094
095 rs = ps.executeQuery();
096
097 while (rs.next()) {
098 String indexName = rs.getString("index_name");
099 String tableName = rs.getString("table_name");
100 boolean unique = !rs.getBoolean("is_unique");
101
102 indexes.add(new Index(indexName, tableName, unique));
103 }
104 }
105 finally {
106 DataAccess.cleanUp(con, ps, rs);
107 }
108
109 return indexes;
110 }
111
112 @Override
113 public boolean isSupportsAlterColumnType() {
114 return _SUPPORTS_ALTER_COLUMN_TYPE;
115 }
116
117 @Override
118 public boolean isSupportsInlineDistinct() {
119 return _SUPPORTS_INLINE_DISTINCT;
120 }
121
122 protected SQLServerDB() {
123 super(TYPE_SQLSERVER);
124 }
125
126 @Override
127 protected String buildCreateFileContent(
128 String sqlDir, String databaseName, int population)
129 throws IOException {
130
131 String suffix = getSuffix(population);
132
133 StringBundler sb = new StringBundler(17);
134
135 sb.append("drop database ");
136 sb.append(databaseName);
137 sb.append(";\n");
138 sb.append("create database ");
139 sb.append(databaseName);
140 sb.append(";\n");
141 sb.append("\n");
142 sb.append("go\n");
143 sb.append("\n");
144 sb.append("use ");
145 sb.append(databaseName);
146 sb.append(";\n\n");
147 sb.append(
148 readFile(
149 sqlDir + "/portal" + suffix + "/portal" + suffix +
150 "-sql-server.sql"));
151 sb.append("\n\n");
152 sb.append(readFile(sqlDir + "/indexes/indexes-sql-server.sql"));
153 sb.append("\n\n");
154 sb.append(readFile(sqlDir + "/sequences/sequences-sql-server.sql"));
155
156 return sb.toString();
157 }
158
159 @Override
160 protected String getServerName() {
161 return "sql-server";
162 }
163
164 @Override
165 protected String[] getTemplate() {
166 return _SQL_SERVER;
167 }
168
169 @Override
170 protected String reword(String data) throws IOException {
171 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
172 new UnsyncStringReader(data));
173
174 StringBundler sb = new StringBundler();
175
176 String line = null;
177
178 while ((line = unsyncBufferedReader.readLine()) != null) {
179 if (line.startsWith(ALTER_COLUMN_NAME)) {
180 String[] template = buildColumnNameTokens(line);
181
182 line = StringUtil.replace(
183 "exec sp_rename '@table@.@old-column@', '@new-column@', " +
184 "'column';",
185 REWORD_TEMPLATE, template);
186 }
187 else if (line.startsWith(ALTER_COLUMN_TYPE)) {
188 String[] template = buildColumnTypeTokens(line);
189
190 line = StringUtil.replace(
191 "alter table @table@ alter column @old-column@ @type@;",
192 REWORD_TEMPLATE, template);
193 }
194 else if (line.indexOf(DROP_INDEX) != -1) {
195 String[] tokens = StringUtil.split(line, ' ');
196
197 String tableName = tokens[4];
198
199 if (tableName.endsWith(StringPool.SEMICOLON)) {
200 tableName = tableName.substring(0, tableName.length() - 1);
201 }
202
203 line = StringUtil.replace(
204 "drop index @table@.@index@;", "@table@", tableName);
205 line = StringUtil.replace(line, "@index@", tokens[2]);
206 }
207
208 sb.append(line);
209 sb.append("\n");
210 }
211
212 unsyncBufferedReader.close();
213
214 return sb.toString();
215 }
216
217 private static final String[] _SQL_SERVER = {
218 "--", "1", "0", "'19700101'", "GetDate()", " image", " image", " bit",
219 " datetime", " float", " int", " bigint", " nvarchar(2000)", " ntext",
220 " nvarchar", " identity(1,1)", "go"
221 };
222
223 private static final int _SQL_SERVER_2000 = 8;
224
225 private static final boolean _SUPPORTS_ALTER_COLUMN_TYPE = false;
226
227 private static final boolean _SUPPORTS_INLINE_DISTINCT = false;
228
229 private static SQLServerDB _instance = new SQLServerDB();
230
231 }