1
14
15 package com.liferay.portal.dao.db;
16
17 import com.liferay.portal.kernel.dao.db.DB;
18 import com.liferay.portal.kernel.dao.db.Index;
19 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
20 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
21 import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
22 import com.liferay.portal.kernel.util.FileUtil;
23 import com.liferay.portal.kernel.util.GetterUtil;
24 import com.liferay.portal.kernel.util.StringBundler;
25 import com.liferay.portal.kernel.util.StringUtil;
26
27 import java.io.IOException;
28
29 import java.sql.Connection;
30 import java.sql.PreparedStatement;
31 import java.sql.ResultSet;
32 import java.sql.SQLException;
33
34 import java.util.ArrayList;
35 import java.util.List;
36 import java.util.regex.Matcher;
37 import java.util.regex.Pattern;
38
39
46 public class OracleDB extends BaseDB {
47
48 public static DB getInstance() {
49 return _instance;
50 }
51
52 public String buildSQL(String template) throws IOException {
53 template = _preBuildSQL(template);
54 template = _postBuildSQL(template);
55
56 return template;
57 }
58
59 public void buildSQLFile(String sqlDir, String fileName)
60 throws IOException {
61
62 String oracle = buildTemplate(sqlDir, fileName);
63
64 oracle = _preBuildSQL(oracle);
65
66 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
67 new UnsyncStringReader(oracle));
68
69 StringBundler imageSB = new StringBundler();
70 StringBundler journalArticleSB = new StringBundler();
71 StringBundler journalStructureSB = new StringBundler();
72 StringBundler journalTemplateSB = new StringBundler();
73
74 String line = null;
75
76 while ((line = unsyncBufferedReader.readLine()) != null) {
77 if (line.startsWith("insert into Image")) {
78 _convertToOracleCSV(line, imageSB);
79 }
80 else if (line.startsWith("insert into JournalArticle (")) {
81 _convertToOracleCSV(line, journalArticleSB);
82 }
83 else if (line.startsWith("insert into JournalStructure (")) {
84 _convertToOracleCSV(line, journalStructureSB);
85 }
86 else if (line.startsWith("insert into JournalTemplate (")) {
87 _convertToOracleCSV(line, journalTemplateSB);
88 }
89 }
90
91 unsyncBufferedReader.close();
92
93 if (imageSB.length() > 0) {
94 FileUtil.write(
95 sqlDir + "/" + fileName + "/" + fileName + "-oracle-image.csv",
96 imageSB.toString());
97 }
98
99 if (journalArticleSB.length() > 0) {
100 FileUtil.write(
101 sqlDir + "/" + fileName + "/" + fileName +
102 "-oracle-journalarticle.csv",
103 journalArticleSB.toString());
104 }
105
106 if (journalStructureSB.length() > 0) {
107 FileUtil.write(
108 sqlDir + "/" + fileName + "/" + fileName +
109 "-oracle-journalstructure.csv",
110 journalStructureSB.toString());
111 }
112
113 if (journalTemplateSB.length() > 0) {
114 FileUtil.write(
115 sqlDir + "/" + fileName + "/" + fileName +
116 "-oracle-journaltemplate.csv",
117 journalTemplateSB.toString());
118 }
119
120 oracle = _postBuildSQL(oracle);
121
122 FileUtil.write(
123 sqlDir + "/" + fileName + "/" + fileName + "-oracle.sql", oracle);
124 }
125
126 public List<Index> getIndexes() throws SQLException {
127 List<Index> indexes = new ArrayList<Index>();
128
129 Connection con = null;
130 PreparedStatement ps = null;
131 ResultSet rs = null;
132
133 try {
134 con = DataAccess.getConnection();
135
136 StringBundler sb = new StringBundler(3);
137
138 sb.append("select index_name, table_name, uniqueness from ");
139 sb.append("user_indexes where index_name like 'LIFERAY_%' or ");
140 sb.append("index_name like 'IX_%'");
141
142 String sql = sb.toString();
143
144 ps = con.prepareStatement(sql);
145
146 rs = ps.executeQuery();
147
148 while (rs.next()) {
149 String indexName = rs.getString("index_name");
150 String tableName = rs.getString("table_name");
151 String uniqueness = rs.getString("uniqueness");
152
153 boolean unique = true;
154
155 if (uniqueness.equalsIgnoreCase("NONUNIQUE")) {
156 unique = false;
157 }
158
159 indexes.add(new Index(indexName, tableName, unique));
160 }
161 }
162 finally {
163 DataAccess.cleanUp(con, ps, rs);
164 }
165
166 return indexes;
167 }
168
169 public boolean isSupportsInlineDistinct() {
170 return _SUPPORTS_INLINE_DISTINCT;
171 }
172
173 protected OracleDB() {
174 super(TYPE_ORACLE);
175 }
176
177 protected String buildCreateFileContent(
178 String sqlDir, String databaseName, int population)
179 throws IOException {
180
181 String suffix = getSuffix(population);
182
183 StringBundler sb = new StringBundler(13);
184
185 sb.append("drop user &1 cascade;\n");
186 sb.append("create user &1 identified by &2;\n");
187 sb.append("grant connect,resource to &1;\n");
188 sb.append("connect &1/&2;\n");
189 sb.append("set define off;\n");
190 sb.append("\n");
191 sb.append(
192 readFile(
193 sqlDir + "/portal" + suffix + "/portal" + suffix +
194 "-oracle.sql"));
195 sb.append("\n\n");
196 sb.append(readFile(sqlDir + "/indexes/indexes-oracle.sql"));
197 sb.append("\n\n");
198 sb.append(readFile(sqlDir + "/sequences/sequences-oracle.sql"));
199 sb.append("\n");
200 sb.append("quit");
201
202 return sb.toString();
203 }
204
205 protected String getServerName() {
206 return "oracle";
207 }
208
209 protected String[] getTemplate() {
210 return _ORACLE;
211 }
212
213 protected String replaceTemplate(String template, String[] actual) {
214
215
217 Matcher matcher = _varcharPattern.matcher(template);
218
219 StringBuffer sb = new StringBuffer();
220
221 while (matcher.find()) {
222 int size = GetterUtil.getInteger(matcher.group()) * 4;
223
224 if (size > 4000) {
225 size = 4000;
226 }
227
228 matcher.appendReplacement(sb, "VARCHAR(" + size + ")");
229 }
230
231 matcher.appendTail(sb);
232
233 template = sb.toString();
234
235 return super.replaceTemplate(template, actual);
236 }
237
238 protected String reword(String data) throws IOException {
239 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
240 new UnsyncStringReader(data));
241
242 StringBundler sb = new StringBundler();
243
244 String line = null;
245
246 while ((line = unsyncBufferedReader.readLine()) != null) {
247 if (line.startsWith(ALTER_COLUMN_NAME)) {
248 String[] template = buildColumnNameTokens(line);
249
250 line = StringUtil.replace(
251 "alter table @table@ rename column @old-column@ to " +
252 "@new-column@;",
253 REWORD_TEMPLATE, template);
254 }
255 else if (line.startsWith(ALTER_COLUMN_TYPE)) {
256 String[] template = buildColumnTypeTokens(line);
257
258 line = StringUtil.replace(
259 "alter table @table@ modify @old-column@ @type@;",
260 REWORD_TEMPLATE, template);
261 }
262 else if (line.indexOf(DROP_INDEX) != -1) {
263 String[] tokens = StringUtil.split(line, " ");
264
265 line = StringUtil.replace(
266 "drop index @index@;", "@index@", tokens[2]);
267 }
268
269 sb.append(line);
270 sb.append("\n");
271 }
272
273 unsyncBufferedReader.close();
274
275 return sb.toString();
276 }
277
278 private void _convertToOracleCSV(String line, StringBundler sb) {
279 int x = line.indexOf("values (");
280 int y = line.lastIndexOf(");");
281
282 line = line.substring(x + 8, y);
283
284 line = StringUtil.replace(line, "sysdate, ", "20050101, ");
285
286 sb.append(line);
287 sb.append("\n");
288 }
289
290 private String _preBuildSQL(String template) throws IOException {
291 template = convertTimestamp(template);
292 template = replaceTemplate(template, getTemplate());
293
294 template = reword(template);
295 template = StringUtil.replace(
296 template,
297 new String[] {"\\\\", "\\'", "\\\""},
298 new String[] {"\\", "''", "\""});
299
300 return template;
301 }
302
303 private String _postBuildSQL(String template) throws IOException {
304 template = removeLongInserts(template);
305 template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
306
307 return template;
308 }
309
310 private static String[] _ORACLE = {
311 "--", "1", "0",
312 "to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')", "sysdate",
313 " blob", " number(1, 0)", " timestamp",
314 " number(30,20)", " number(30,0)", " number(30,0)",
315 " varchar2(4000)", " clob", " varchar2",
316 "", "commit"
317 };
318
319 private static final boolean _SUPPORTS_INLINE_DISTINCT = false;
320
321 private static OracleDB _instance = new OracleDB();
322
323 private static Pattern _varcharPattern = Pattern.compile(
324 "VARCHAR(\\(\\d+\\))");
325
326 }