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