001
014
015 package com.liferay.portal.dao.db;
016
017 import com.liferay.portal.kernel.dao.db.DBType;
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 SQLServerDB(int majorVersion, int minorVersion) {
045 super(DBType.SQLSERVER, majorVersion, minorVersion);
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(Connection con) throws SQLException {
065 List<Index> indexes = new ArrayList<>();
066
067 PreparedStatement ps = null;
068 ResultSet rs = null;
069
070 try {
071 DatabaseMetaData databaseMetaData = con.getMetaData();
072
073 if (databaseMetaData.getDatabaseMajorVersion() <=
074 _SQL_SERVER_2000) {
075
076 return indexes;
077 }
078
079 StringBundler sb = new StringBundler(6);
080
081 sb.append("select sys.tables.name as table_name, ");
082 sb.append("sys.indexes.name as index_name, is_unique from ");
083 sb.append("sys.indexes inner join sys.tables on ");
084 sb.append("sys.tables.object_id = sys.indexes.object_id where ");
085 sb.append("sys.indexes.name like 'LIFERAY_%' or sys.indexes.name ");
086 sb.append("like 'IX_%'");
087
088 String sql = sb.toString();
089
090 ps = con.prepareStatement(sql);
091
092 rs = ps.executeQuery();
093
094 while (rs.next()) {
095 String indexName = rs.getString("index_name");
096 String tableName = rs.getString("table_name");
097 boolean unique = !rs.getBoolean("is_unique");
098
099 indexes.add(new Index(indexName, tableName, unique));
100 }
101 }
102 finally {
103 DataAccess.cleanUp(ps, rs);
104 }
105
106 return indexes;
107 }
108
109 @Override
110 public boolean isSupportsAlterColumnType() {
111 return _SUPPORTS_ALTER_COLUMN_TYPE;
112 }
113
114 @Override
115 protected String buildCreateFileContent(
116 String sqlDir, String databaseName, int population)
117 throws IOException {
118
119 String suffix = getSuffix(population);
120
121 StringBundler sb = new StringBundler(17);
122
123 sb.append("drop database ");
124 sb.append(databaseName);
125 sb.append(";\n");
126 sb.append("create database ");
127 sb.append(databaseName);
128 sb.append(";\n");
129 sb.append("\n");
130 sb.append("go\n");
131 sb.append("\n");
132
133 if (population != BARE) {
134 sb.append("use ");
135 sb.append(databaseName);
136 sb.append(";\n\n");
137 sb.append(getCreateTablesContent(sqlDir, suffix));
138 sb.append("\n\n");
139 sb.append(readFile(sqlDir + "/indexes/indexes-sql-server.sql"));
140 sb.append("\n\n");
141 sb.append(readFile(sqlDir + "/sequences/sequences-sql-server.sql"));
142 }
143
144 return sb.toString();
145 }
146
147 @Override
148 protected String getServerName() {
149 return "sql-server";
150 }
151
152 @Override
153 protected String[] getTemplate() {
154 return _SQL_SERVER;
155 }
156
157 @Override
158 protected String reword(String data) throws IOException {
159 try (UnsyncBufferedReader unsyncBufferedReader =
160 new UnsyncBufferedReader(new UnsyncStringReader(data))) {
161
162 StringBundler sb = new StringBundler();
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@', " +
172 "'@new-column@', '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 else if (line.startsWith(ALTER_TABLE_NAME)) {
183 String[] template = buildTableNameTokens(line);
184
185 line = StringUtil.replace(
186 "exec sp_rename '@old-table@', '@new-table@';",
187 RENAME_TABLE_TEMPLATE, template);
188 }
189 else if (line.contains(DROP_INDEX)) {
190 String[] tokens = StringUtil.split(line, ' ');
191
192 String tableName = tokens[4];
193
194 if (tableName.endsWith(StringPool.SEMICOLON)) {
195 tableName = tableName.substring(
196 0, tableName.length() - 1);
197 }
198
199 line = StringUtil.replace(
200 "drop index @table@.@index@;", "@table@", tableName);
201 line = StringUtil.replace(line, "@index@", tokens[2]);
202 }
203
204 sb.append(line);
205 sb.append("\n");
206 }
207
208 return sb.toString();
209 }
210 }
211
212 private static final String[] _SQL_SERVER = {
213 "--", "1", "0", "'19700101'", "GetDate()", " image", " image", " bit",
214 " datetime", " float", " int", " bigint", " nvarchar(2000)",
215 " nvarchar(max)", " nvarchar", " identity(1,1)", "go"
216 };
217
218 private static final int _SQL_SERVER_2000 = 8;
219
220 private static final boolean _SUPPORTS_ALTER_COLUMN_TYPE = false;
221
222 }