1   /**
2    * Copyright (c) 2000-2009 Liferay, Inc. All rights reserved.
3    *
4    *
5    *
6    *
7    * The contents of this file are subject to the terms of the Liferay Enterprise
8    * Subscription License ("License"). You may not use this file except in
9    * compliance with the License. You can obtain a copy of the License by
10   * contacting Liferay, Inc. See the License for the specific language governing
11   * permissions and limitations under the License, including but not limited to
12   * distribution rights of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
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  /**
44   * <a href="OracleDB.java.html"><b><i>View Source</i></b></a>
45   *
46   * @author Alexander Chow
47   * @author Sandeep Soni
48   * @author Ganesh Ram
49   */
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 }