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.FileUtil;
023 import com.liferay.portal.kernel.util.GetterUtil;
024 import com.liferay.portal.kernel.util.StringBundler;
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 static DB getInstance() {
047 return _instance;
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 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
067 new UnsyncStringReader(oracle));
068
069 StringBundler imageSB = new StringBundler();
070 StringBundler journalArticleSB = new StringBundler();
071 StringBundler journalStructureSB = new StringBundler();
072 StringBundler journalTemplateSB = new StringBundler();
073
074 String line = null;
075
076 while ((line = unsyncBufferedReader.readLine()) != null) {
077 if (line.startsWith("insert into Image")) {
078 _convertToOracleCSV(line, imageSB);
079 }
080 else if (line.startsWith("insert into JournalArticle (")) {
081 _convertToOracleCSV(line, journalArticleSB);
082 }
083 else if (line.startsWith("insert into JournalStructure (")) {
084 _convertToOracleCSV(line, journalStructureSB);
085 }
086 else if (line.startsWith("insert into JournalTemplate (")) {
087 _convertToOracleCSV(line, journalTemplateSB);
088 }
089 }
090
091 unsyncBufferedReader.close();
092
093 if (imageSB.length() > 0) {
094 FileUtil.write(
095 sqlDir + "/" + fileName + "/" + fileName + "-oracle-image.csv",
096 imageSB.toString());
097 }
098
099 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 @Override
127 public List<Index> getIndexes(Connection con) throws SQLException {
128 List<Index> indexes = new ArrayList<Index>();
129
130 PreparedStatement ps = null;
131 ResultSet rs = null;
132
133 try {
134 StringBundler sb = new StringBundler(3);
135
136 sb.append("select index_name, table_name, uniqueness from ");
137 sb.append("user_indexes where index_name like 'LIFERAY_%' or ");
138 sb.append("index_name like 'IX_%'");
139
140 String sql = sb.toString();
141
142 ps = con.prepareStatement(sql);
143
144 rs = ps.executeQuery();
145
146 while (rs.next()) {
147 String indexName = rs.getString("index_name");
148 String tableName = rs.getString("table_name");
149 String uniqueness = rs.getString("uniqueness");
150
151 boolean unique = true;
152
153 if (StringUtil.equalsIgnoreCase(uniqueness, "NONUNIQUE")) {
154 unique = false;
155 }
156
157 indexes.add(new Index(indexName, tableName, unique));
158 }
159 }
160 finally {
161 DataAccess.cleanUp(null, ps, rs);
162 }
163
164 return indexes;
165 }
166
167 @Override
168 public boolean isSupportsInlineDistinct() {
169 return _SUPPORTS_INLINE_DISTINCT;
170 }
171
172 protected OracleDB() {
173 super(TYPE_ORACLE);
174 }
175
176 @Override
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
189 if (population != BARE) {
190 sb.append("connect &1/&2;\n");
191 sb.append("set define off;\n");
192 sb.append("\n");
193 sb.append(getCreateTablesContent(sqlDir, suffix));
194 sb.append("\n\n");
195 sb.append(readFile(sqlDir + "/indexes/indexes-oracle.sql"));
196 sb.append("\n\n");
197 sb.append(readFile(sqlDir + "/sequences/sequences-oracle.sql"));
198 sb.append("\n");
199 }
200
201 sb.append("quit");
202
203 return sb.toString();
204 }
205
206 @Override
207 protected String getServerName() {
208 return "oracle";
209 }
210
211 @Override
212 protected String[] getTemplate() {
213 return _ORACLE;
214 }
215
216 @Override
217 protected String replaceTemplate(String template, String[] actual) {
218
219
220
221 Matcher matcher = _varcharPattern.matcher(template);
222
223 StringBuffer sb = new StringBuffer();
224
225 while (matcher.find()) {
226 int size = GetterUtil.getInteger(matcher.group(1));
227
228 if (size > 4000) {
229 size = 4000;
230 }
231
232 matcher.appendReplacement(sb, "VARCHAR2(" + size + " CHAR)");
233 }
234
235 matcher.appendTail(sb);
236
237 template = sb.toString();
238
239 return super.replaceTemplate(template, actual);
240 }
241
242 @Override
243 protected String reword(String data) throws IOException {
244 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
245 new UnsyncStringReader(data));
246
247 StringBundler sb = new StringBundler();
248
249 String line = null;
250
251 while ((line = unsyncBufferedReader.readLine()) != null) {
252 if (line.startsWith(ALTER_COLUMN_NAME)) {
253 String[] template = buildColumnNameTokens(line);
254
255 line = StringUtil.replace(
256 "alter table @table@ rename column @old-column@ to " +
257 "@new-column@;",
258 REWORD_TEMPLATE, template);
259 }
260 else if (line.startsWith(ALTER_COLUMN_TYPE)) {
261 String[] template = buildColumnTypeTokens(line);
262
263 line = StringUtil.replace(
264 "alter table @table@ modify @old-column@ @type@;",
265 REWORD_TEMPLATE, template);
266 }
267 else if (line.startsWith(ALTER_TABLE_NAME)) {
268 String[] template = buildTableNameTokens(line);
269
270 line = StringUtil.replace(
271 "alter table @old-table@ rename to @new-table@;",
272 RENAME_TABLE_TEMPLATE, template);
273 }
274 else if (line.contains(DROP_INDEX)) {
275 String[] tokens = StringUtil.split(line, ' ');
276
277 line = StringUtil.replace(
278 "drop index @index@;", "@index@", tokens[2]);
279 }
280
281 sb.append(line);
282 sb.append("\n");
283 }
284
285 unsyncBufferedReader.close();
286
287 return sb.toString();
288 }
289
290 private void _convertToOracleCSV(String line, StringBundler sb) {
291 int x = line.indexOf("values (");
292 int y = line.lastIndexOf(");");
293
294 line = line.substring(x + 8, y);
295
296 line = StringUtil.replace(line, "sysdate, ", "20050101, ");
297
298 sb.append(line);
299 sb.append("\n");
300 }
301
302 private String _postBuildSQL(String template) throws IOException {
303 template = removeLongInserts(template);
304 template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
305
306 return template;
307 }
308
309 private String _preBuildSQL(String template) throws IOException {
310 template = convertTimestamp(template);
311 template = replaceTemplate(template, getTemplate());
312
313 template = reword(template);
314 template = StringUtil.replace(
315 template,
316 new String[] {"\\\\", "\\'", "\\\""},
317 new String[] {"\\", "''", "\""});
318
319 return template;
320 }
321
322 private static final String[] _ORACLE = {
323 "--", "1", "0",
324 "to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')", "sysdate",
325 " blob", " blob", " number(1, 0)", " timestamp", " number(30,20)",
326 " number(30,0)", " number(30,0)", " varchar2(4000)", " clob",
327 " varchar2", "", "commit"
328 };
329
330 private static final boolean _SUPPORTS_INLINE_DISTINCT = false;
331
332 private static OracleDB _instance = new OracleDB();
333
334 private static Pattern _varcharPattern = Pattern.compile(
335 "VARCHAR\\((\\d+)\\)");
336
337 }