001
014
015 package com.liferay.portal.dao.db;
016
017 import com.liferay.portal.kernel.dao.db.Index;
018 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
019 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
020 import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
021 import com.liferay.portal.kernel.util.FileUtil;
022 import com.liferay.portal.kernel.util.GetterUtil;
023 import com.liferay.portal.kernel.util.StringBundler;
024 import com.liferay.portal.kernel.util.StringPool;
025 import com.liferay.portal.kernel.util.StringUtil;
026
027 import java.io.IOException;
028
029 import java.sql.Connection;
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 import java.util.regex.Matcher;
037 import java.util.regex.Pattern;
038
039
044 public class OracleDB extends BaseDB {
045
046 public OracleDB(int majorVersion, int minorVersion) {
047 super(TYPE_ORACLE, majorVersion, minorVersion);
048 }
049
050 @Override
051 public String buildSQL(String template) throws IOException {
052 template = _preBuildSQL(template);
053 template = _postBuildSQL(template);
054
055 return template;
056 }
057
058 @Override
059 public void buildSQLFile(String sqlDir, String fileName)
060 throws IOException {
061
062 String oracle = buildTemplate(sqlDir, fileName);
063
064 oracle = _preBuildSQL(oracle);
065
066 StringBundler imageSB = new StringBundler();
067 StringBundler journalArticleSB = new StringBundler();
068
069 try (UnsyncBufferedReader unsyncBufferedReader =
070 new UnsyncBufferedReader(new UnsyncStringReader(oracle))) {
071
072 String line = null;
073
074 while ((line = unsyncBufferedReader.readLine()) != null) {
075 if (line.startsWith("insert into Image")) {
076 _convertToOracleCSV(line, imageSB);
077 }
078 else if (line.startsWith("insert into JournalArticle (")) {
079 _convertToOracleCSV(line, journalArticleSB);
080 }
081 }
082 }
083
084 if (imageSB.length() > 0) {
085 FileUtil.write(
086 sqlDir + "/" + fileName + "/" + fileName + "-oracle-image.csv",
087 imageSB.toString());
088 }
089
090 if (journalArticleSB.length() > 0) {
091 FileUtil.write(
092 sqlDir + "/" + fileName + "/" + fileName +
093 "-oracle-journalarticle.csv",
094 journalArticleSB.toString());
095 }
096
097 oracle = _postBuildSQL(oracle);
098
099 FileUtil.write(
100 sqlDir + "/" + fileName + "/" + fileName + "-oracle.sql", oracle);
101 }
102
103 @Override
104 public List<Index> getIndexes(Connection con) throws SQLException {
105 List<Index> indexes = new ArrayList<>();
106
107 PreparedStatement ps = null;
108 ResultSet rs = null;
109
110 try {
111 StringBundler sb = new StringBundler(3);
112
113 sb.append("select index_name, table_name, uniqueness from ");
114 sb.append("user_indexes where index_name like 'LIFERAY_%' or ");
115 sb.append("index_name like 'IX_%'");
116
117 String sql = sb.toString();
118
119 ps = con.prepareStatement(sql);
120
121 rs = ps.executeQuery();
122
123 while (rs.next()) {
124 String indexName = rs.getString("index_name");
125 String tableName = rs.getString("table_name");
126 String uniqueness = rs.getString("uniqueness");
127
128 boolean unique = true;
129
130 if (StringUtil.equalsIgnoreCase(uniqueness, "NONUNIQUE")) {
131 unique = false;
132 }
133
134 indexes.add(new Index(indexName, tableName, unique));
135 }
136 }
137 finally {
138 DataAccess.cleanUp(null, ps, rs);
139 }
140
141 return indexes;
142 }
143
144 @Override
145 public boolean isSupportsInlineDistinct() {
146 return _SUPPORTS_INLINE_DISTINCT;
147 }
148
149 @Override
150 protected String[] buildColumnTypeTokens(String line) {
151 Matcher matcher = _varchar2CharPattern.matcher(line);
152
153 StringBuffer sb = new StringBuffer();
154
155 while (matcher.find()) {
156 matcher.appendReplacement(
157 sb, "VARCHAR2(" + matcher.group(1) + "%20CHAR)");
158 }
159
160 matcher.appendTail(sb);
161
162 String[] template = super.buildColumnTypeTokens(sb.toString());
163
164 template[3] = StringUtil.replace(template[3], "%20", StringPool.SPACE);
165
166 return template;
167 }
168
169 @Override
170 protected String buildCreateFileContent(
171 String sqlDir, String databaseName, int population)
172 throws IOException {
173
174 String suffix = getSuffix(population);
175
176 StringBundler sb = new StringBundler(13);
177
178 sb.append("drop user &1 cascade;\n");
179 sb.append("create user &1 identified by &2;\n");
180 sb.append("grant connect,resource to &1;\n");
181
182 if (population != BARE) {
183 sb.append("connect &1/&2;\n");
184 sb.append("set define off;\n");
185 sb.append("\n");
186 sb.append(getCreateTablesContent(sqlDir, suffix));
187 sb.append("\n\n");
188 sb.append(readFile(sqlDir + "/indexes/indexes-oracle.sql"));
189 sb.append("\n\n");
190 sb.append(readFile(sqlDir + "/sequences/sequences-oracle.sql"));
191 sb.append("\n");
192 }
193
194 sb.append("quit");
195
196 return sb.toString();
197 }
198
199 @Override
200 protected String getServerName() {
201 return "oracle";
202 }
203
204 @Override
205 protected String[] getTemplate() {
206 return _ORACLE;
207 }
208
209 @Override
210 protected String replaceTemplate(String template, String[] actual) {
211
212
213
214 Matcher matcher = _varcharPattern.matcher(template);
215
216 StringBuffer sb = new StringBuffer();
217
218 while (matcher.find()) {
219 int size = GetterUtil.getInteger(matcher.group(1));
220
221 if (size > 4000) {
222 size = 4000;
223 }
224
225 matcher.appendReplacement(sb, "VARCHAR2(" + size + " CHAR)");
226 }
227
228 matcher.appendTail(sb);
229
230 template = sb.toString();
231
232 return super.replaceTemplate(template, actual);
233 }
234
235 @Override
236 protected String reword(String data) throws IOException {
237 try (UnsyncBufferedReader unsyncBufferedReader =
238 new UnsyncBufferedReader(new UnsyncStringReader(data))) {
239
240 StringBundler sb = new StringBundler();
241
242 String line = null;
243
244 while ((line = unsyncBufferedReader.readLine()) != null) {
245 if (line.startsWith(ALTER_COLUMN_NAME)) {
246 String[] template = buildColumnNameTokens(line);
247
248 line = StringUtil.replace(
249 "alter table @table@ rename column @old-column@ to " +
250 "@new-column@;",
251 REWORD_TEMPLATE, template);
252 }
253 else if (line.startsWith(ALTER_COLUMN_TYPE)) {
254 String[] template = buildColumnTypeTokens(line);
255
256 line = StringUtil.replace(
257 "alter table @table@ modify @old-column@ @type@;",
258 REWORD_TEMPLATE, template);
259 }
260 else if (line.startsWith(ALTER_TABLE_NAME)) {
261 String[] template = buildTableNameTokens(line);
262
263 line = StringUtil.replace(
264 "alter table @old-table@ rename to @new-table@;",
265 RENAME_TABLE_TEMPLATE, template);
266 }
267 else if (line.contains(DROP_INDEX)) {
268 String[] tokens = StringUtil.split(line, ' ');
269
270 line = StringUtil.replace(
271 "drop index @index@;", "@index@", tokens[2]);
272 }
273
274 sb.append(line);
275 sb.append("\n");
276 }
277
278 return sb.toString();
279 }
280 }
281
282 private void _convertToOracleCSV(String line, StringBundler sb) {
283 int x = line.indexOf("values (");
284 int y = line.lastIndexOf(");");
285
286 line = line.substring(x + 8, y);
287
288 line = StringUtil.replace(line, "sysdate, ", "20050101, ");
289
290 sb.append(line);
291 sb.append("\n");
292 }
293
294 private String _postBuildSQL(String template) throws IOException {
295 template = removeLongInserts(template);
296 template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
297
298 return template;
299 }
300
301 private String _preBuildSQL(String template) throws IOException {
302 template = convertTimestamp(template);
303 template = replaceTemplate(template, getTemplate());
304
305 template = reword(template);
306 template = StringUtil.replace(
307 template,
308 new String[] {"\\\\", "\\'", "\\\""},
309 new String[] {"\\", "''", "\""});
310
311 return template;
312 }
313
314 private static final String[] _ORACLE = {
315 "--", "1", "0",
316 "to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')", "sysdate",
317 " blob", " blob", " number(1, 0)", " timestamp", " number(30,20)",
318 " number(30,0)", " number(30,0)", " varchar2(4000)", " clob",
319 " varchar2", "", "commit"
320 };
321
322 private static final boolean _SUPPORTS_INLINE_DISTINCT = false;
323
324 private static final Pattern _varchar2CharPattern = Pattern.compile(
325 "VARCHAR2\\((\\d+) CHAR\\)");
326 private static final Pattern _varcharPattern = Pattern.compile(
327 "VARCHAR\\((\\d+)\\)");
328
329 }