1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * This library is free software; you can redistribute it and/or modify it under
5    * the terms of the GNU Lesser General Public License as published by the Free
6    * Software Foundation; either version 2.1 of the License, or (at your option)
7    * any later version.
8    *
9    * This library is distributed in the hope that it will be useful, but WITHOUT
10   * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
11   * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
12   * details.
13   */
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  /**
37   * <a href="OracleDB.java.html"><b><i>View Source</i></b></a>
38   *
39   * @author Alexander Chow
40   * @author Sandeep Soni
41   * @author Ganesh Ram
42   */
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 }