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.StringBundler;
024 import com.liferay.portal.kernel.util.StringUtil;
025
026 import java.io.IOException;
027
028 import java.sql.Connection;
029 import java.sql.PreparedStatement;
030 import java.sql.ResultSet;
031 import java.sql.SQLException;
032
033 import java.util.ArrayList;
034 import java.util.List;
035
036
041 public class OracleDB extends BaseDB {
042
043 public static DB getInstance() {
044 return _instance;
045 }
046
047 public String buildSQL(String template) throws IOException {
048 template = _preBuildSQL(template);
049 template = _postBuildSQL(template);
050
051 return template;
052 }
053
054 public void buildSQLFile(String sqlDir, String fileName)
055 throws IOException {
056
057 String oracle = buildTemplate(sqlDir, fileName);
058
059 oracle = _preBuildSQL(oracle);
060
061 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
062 new UnsyncStringReader(oracle));
063
064 StringBundler imageSB = new StringBundler();
065 StringBundler journalArticleSB = new StringBundler();
066 StringBundler journalStructureSB = new StringBundler();
067 StringBundler journalTemplateSB = new StringBundler();
068
069 String line = null;
070
071 while ((line = unsyncBufferedReader.readLine()) != null) {
072 if (line.startsWith("insert into Image")) {
073 _convertToOracleCSV(line, imageSB);
074 }
075 else if (line.startsWith("insert into JournalArticle (")) {
076 _convertToOracleCSV(line, journalArticleSB);
077 }
078 else if (line.startsWith("insert into JournalStructure (")) {
079 _convertToOracleCSV(line, journalStructureSB);
080 }
081 else if (line.startsWith("insert into JournalTemplate (")) {
082 _convertToOracleCSV(line, journalTemplateSB);
083 }
084 }
085
086 unsyncBufferedReader.close();
087
088 if (imageSB.length() > 0) {
089 FileUtil.write(
090 sqlDir + "/" + fileName + "/" + fileName + "-oracle-image.csv",
091 imageSB.toString());
092 }
093
094 if (journalArticleSB.length() > 0) {
095 FileUtil.write(
096 sqlDir + "/" + fileName + "/" + fileName +
097 "-oracle-journalarticle.csv",
098 journalArticleSB.toString());
099 }
100
101 if (journalStructureSB.length() > 0) {
102 FileUtil.write(
103 sqlDir + "/" + fileName + "/" + fileName +
104 "-oracle-journalstructure.csv",
105 journalStructureSB.toString());
106 }
107
108 if (journalTemplateSB.length() > 0) {
109 FileUtil.write(
110 sqlDir + "/" + fileName + "/" + fileName +
111 "-oracle-journaltemplate.csv",
112 journalTemplateSB.toString());
113 }
114
115 oracle = _postBuildSQL(oracle);
116
117 FileUtil.write(
118 sqlDir + "/" + fileName + "/" + fileName + "-oracle.sql", oracle);
119 }
120
121 public List<Index> getIndexes() throws SQLException {
122 List<Index> indexes = new ArrayList<Index>();
123
124 Connection con = null;
125 PreparedStatement ps = null;
126 ResultSet rs = null;
127
128 try {
129 con = DataAccess.getConnection();
130
131 StringBundler sb = new StringBundler(3);
132
133 sb.append("select index_name, table_name, uniqueness from ");
134 sb.append("user_indexes where index_name like 'LIFERAY_%' or ");
135 sb.append("index_name like 'IX_%'");
136
137 String sql = sb.toString();
138
139 ps = con.prepareStatement(sql);
140
141 rs = ps.executeQuery();
142
143 while (rs.next()) {
144 String indexName = rs.getString("index_name");
145 String tableName = rs.getString("table_name");
146 String uniqueness = rs.getString("uniqueness");
147
148 boolean unique = true;
149
150 if (uniqueness.equalsIgnoreCase("NONUNIQUE")) {
151 unique = false;
152 }
153
154 indexes.add(new Index(indexName, tableName, unique));
155 }
156 }
157 finally {
158 DataAccess.cleanUp(con, ps, rs);
159 }
160
161 return indexes;
162 }
163
164 protected OracleDB() {
165 super(TYPE_ORACLE);
166 }
167
168 protected String buildCreateFileContent(
169 String sqlDir, String databaseName, int population)
170 throws IOException {
171
172 String suffix = getSuffix(population);
173
174 StringBundler sb = new StringBundler(13);
175
176 sb.append("drop user &1 cascade;\n");
177 sb.append("create user &1 identified by &2;\n");
178 sb.append("grant connect,resource to &1;\n");
179 sb.append("connect &1/&2;\n");
180 sb.append("set define off;\n");
181 sb.append("\n");
182 sb.append(
183 readFile(
184 sqlDir + "/portal" + suffix + "/portal" + suffix +
185 "-oracle.sql"));
186 sb.append("\n\n");
187 sb.append(readFile(sqlDir + "/indexes/indexes-oracle.sql"));
188 sb.append("\n\n");
189 sb.append(readFile(sqlDir + "/sequences/sequences-oracle.sql"));
190 sb.append("\n");
191 sb.append("quit");
192
193 return sb.toString();
194 }
195
196 protected String getServerName() {
197 return "oracle";
198 }
199
200 protected String[] getTemplate() {
201 return _ORACLE;
202 }
203
204 protected String reword(String data) throws IOException {
205 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
206 new UnsyncStringReader(data));
207
208 StringBundler sb = new StringBundler();
209
210 String line = null;
211
212 while ((line = unsyncBufferedReader.readLine()) != null) {
213 if (line.startsWith(ALTER_COLUMN_NAME)) {
214 String[] template = buildColumnNameTokens(line);
215
216 line = StringUtil.replace(
217 "alter table @table@ rename column @old-column@ to " +
218 "@new-column@;",
219 REWORD_TEMPLATE, template);
220 }
221 else if (line.startsWith(ALTER_COLUMN_TYPE)) {
222 String[] template = buildColumnTypeTokens(line);
223
224 line = StringUtil.replace(
225 "alter table @table@ modify @old-column@ @type@;",
226 REWORD_TEMPLATE, template);
227 }
228 else if (line.indexOf(DROP_INDEX) != -1) {
229 String[] tokens = StringUtil.split(line, " ");
230
231 line = StringUtil.replace(
232 "drop index @index@;", "@index@", tokens[2]);
233 }
234
235 sb.append(line);
236 sb.append("\n");
237 }
238
239 unsyncBufferedReader.close();
240
241 return sb.toString();
242 }
243
244 private void _convertToOracleCSV(String line, StringBundler sb) {
245 int x = line.indexOf("values (");
246 int y = line.lastIndexOf(");");
247
248 line = line.substring(x + 8, y);
249
250 line = StringUtil.replace(line, "sysdate, ", "20050101, ");
251
252 sb.append(line);
253 sb.append("\n");
254 }
255
256 private String _preBuildSQL(String template) throws IOException {
257 template = convertTimestamp(template);
258 template = replaceTemplate(template, getTemplate());
259
260 template = reword(template);
261 template = StringUtil.replace(
262 template,
263 new String[] {"\\\\", "\\'", "\\\""},
264 new String[] {"\\", "''", "\""});
265
266 return template;
267 }
268
269 private String _postBuildSQL(String template) throws IOException {
270 template = removeLongInserts(template);
271 template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
272
273 return template;
274 }
275
276 private static String[] _ORACLE = {
277 "--", "1", "0",
278 "to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')", "sysdate",
279 " blob", " number(1, 0)", " timestamp",
280 " number(30,20)", " number(30,0)", " number(30,0)",
281 " varchar2(4000)", " clob", " varchar2",
282 "", "commit"
283 };
284
285 private static OracleDB _instance = new OracleDB();
286
287 }