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 public String buildSQL(String template) throws IOException {
051 template = _preBuildSQL(template);
052 template = _postBuildSQL(template);
053
054 return template;
055 }
056
057 public void buildSQLFile(String sqlDir, String fileName)
058 throws IOException {
059
060 String oracle = buildTemplate(sqlDir, fileName);
061
062 oracle = _preBuildSQL(oracle);
063
064 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
065 new UnsyncStringReader(oracle));
066
067 StringBundler imageSB = new StringBundler();
068 StringBundler journalArticleSB = new StringBundler();
069 StringBundler journalStructureSB = new StringBundler();
070 StringBundler journalTemplateSB = new StringBundler();
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 else if (line.startsWith("insert into JournalStructure (")) {
082 _convertToOracleCSV(line, journalStructureSB);
083 }
084 else if (line.startsWith("insert into JournalTemplate (")) {
085 _convertToOracleCSV(line, journalTemplateSB);
086 }
087 }
088
089 unsyncBufferedReader.close();
090
091 if (imageSB.length() > 0) {
092 FileUtil.write(
093 sqlDir + "/" + fileName + "/" + fileName + "-oracle-image.csv",
094 imageSB.toString());
095 }
096
097 if (journalArticleSB.length() > 0) {
098 FileUtil.write(
099 sqlDir + "/" + fileName + "/" + fileName +
100 "-oracle-journalarticle.csv",
101 journalArticleSB.toString());
102 }
103
104 if (journalStructureSB.length() > 0) {
105 FileUtil.write(
106 sqlDir + "/" + fileName + "/" + fileName +
107 "-oracle-journalstructure.csv",
108 journalStructureSB.toString());
109 }
110
111 if (journalTemplateSB.length() > 0) {
112 FileUtil.write(
113 sqlDir + "/" + fileName + "/" + fileName +
114 "-oracle-journaltemplate.csv",
115 journalTemplateSB.toString());
116 }
117
118 oracle = _postBuildSQL(oracle);
119
120 FileUtil.write(
121 sqlDir + "/" + fileName + "/" + fileName + "-oracle.sql", oracle);
122 }
123
124 public List<Index> getIndexes() throws SQLException {
125 List<Index> indexes = new ArrayList<Index>();
126
127 Connection con = null;
128 PreparedStatement ps = null;
129 ResultSet rs = null;
130
131 try {
132 con = DataAccess.getConnection();
133
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 (uniqueness.equalsIgnoreCase("NONUNIQUE")) {
154 unique = false;
155 }
156
157 indexes.add(new Index(indexName, tableName, unique));
158 }
159 }
160 finally {
161 DataAccess.cleanUp(con, ps, rs);
162 }
163
164 return indexes;
165 }
166
167 public boolean isSupportsInlineDistinct() {
168 return _SUPPORTS_INLINE_DISTINCT;
169 }
170
171 protected OracleDB() {
172 super(TYPE_ORACLE);
173 }
174
175 protected String buildCreateFileContent(
176 String sqlDir, String databaseName, int population)
177 throws IOException {
178
179 String suffix = getSuffix(population);
180
181 StringBundler sb = new StringBundler(13);
182
183 sb.append("drop user &1 cascade;\n");
184 sb.append("create user &1 identified by &2;\n");
185 sb.append("grant connect,resource to &1;\n");
186 sb.append("connect &1/&2;\n");
187 sb.append("set define off;\n");
188 sb.append("\n");
189 sb.append(
190 readFile(
191 sqlDir + "/portal" + suffix + "/portal" + suffix +
192 "-oracle.sql"));
193 sb.append("\n\n");
194 sb.append(readFile(sqlDir + "/indexes/indexes-oracle.sql"));
195 sb.append("\n\n");
196 sb.append(readFile(sqlDir + "/sequences/sequences-oracle.sql"));
197 sb.append("\n");
198 sb.append("quit");
199
200 return sb.toString();
201 }
202
203 protected String getServerName() {
204 return "oracle";
205 }
206
207 protected String[] getTemplate() {
208 return _ORACLE;
209 }
210
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()) * 4;
221
222 if (size > 4000) {
223 size = 4000;
224 }
225
226 matcher.appendReplacement(sb, "VARCHAR(" + size + ")");
227 }
228
229 matcher.appendTail(sb);
230
231 template = sb.toString();
232
233 return super.replaceTemplate(template, actual);
234 }
235
236 protected String reword(String data) throws IOException {
237 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
238 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.indexOf(DROP_INDEX) != -1) {
261 String[] tokens = StringUtil.split(line, " ");
262
263 line = StringUtil.replace(
264 "drop index @index@;", "@index@", tokens[2]);
265 }
266
267 sb.append(line);
268 sb.append("\n");
269 }
270
271 unsyncBufferedReader.close();
272
273 return sb.toString();
274 }
275
276 private void _convertToOracleCSV(String line, StringBundler sb) {
277 int x = line.indexOf("values (");
278 int y = line.lastIndexOf(");");
279
280 line = line.substring(x + 8, y);
281
282 line = StringUtil.replace(line, "sysdate, ", "20050101, ");
283
284 sb.append(line);
285 sb.append("\n");
286 }
287
288 private String _preBuildSQL(String template) throws IOException {
289 template = convertTimestamp(template);
290 template = replaceTemplate(template, getTemplate());
291
292 template = reword(template);
293 template = StringUtil.replace(
294 template,
295 new String[] {"\\\\", "\\'", "\\\""},
296 new String[] {"\\", "''", "\""});
297
298 return template;
299 }
300
301 private String _postBuildSQL(String template) throws IOException {
302 template = removeLongInserts(template);
303 template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
304
305 return template;
306 }
307
308 private static String[] _ORACLE = {
309 "--", "1", "0",
310 "to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')", "sysdate",
311 " blob", " number(1, 0)", " timestamp",
312 " number(30,20)", " number(30,0)", " number(30,0)",
313 " varchar2(4000)", " clob", " varchar2",
314 "", "commit"
315 };
316
317 private static final boolean _SUPPORTS_INLINE_DISTINCT = false;
318
319 private static OracleDB _instance = new OracleDB();
320
321 private static Pattern _varcharPattern = Pattern.compile(
322 "VARCHAR(\\(\\d+\\))");
323
324 }