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