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