1   /**
2    * Copyright (c) 2000-2009 Liferay, Inc. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions 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.tools.sql;
24  
25  import com.liferay.portal.kernel.util.FileUtil;
26  import com.liferay.portal.kernel.util.StringUtil;
27  
28  import java.io.BufferedReader;
29  import java.io.File;
30  import java.io.IOException;
31  import java.io.StringReader;
32  
33  /**
34   * <a href="OracleUtil.java.html"><b><i>View Source</i></b></a>
35   *
36   * @author Alexander Chow
37   * @author Sandeep Soni
38   * @author Ganesh Ram
39   *
40   */
41  public class OracleUtil extends DBUtil {
42  
43      public static DBUtil getInstance() {
44          return _instance;
45      }
46  
47      public String buildSQL(String template) throws IOException {
48          template = _preBuildSQL(template);
49          template = _postBuildSQL(template);
50  
51          return template;
52      }
53  
54      public void buildSQLFile(String fileName) throws IOException {
55          String oracle = buildTemplate(fileName);
56  
57          oracle = _preBuildSQL(oracle);
58  
59          BufferedReader br = new BufferedReader(new StringReader(oracle));
60  
61          StringBuilder imageSB = new StringBuilder();
62          StringBuilder journalArticleSB = new StringBuilder();
63          StringBuilder journalStructureSB = new StringBuilder();
64          StringBuilder journalTemplateSB = new StringBuilder();
65  
66          String line = null;
67  
68          while ((line = br.readLine()) != null) {
69              if (line.startsWith("insert into Image")) {
70                  _convertToOracleCSV(line, imageSB);
71              }
72              else if (line.startsWith("insert into JournalArticle (")) {
73                  _convertToOracleCSV(line, journalArticleSB);
74              }
75              else if (line.startsWith("insert into JournalStructure (")) {
76                  _convertToOracleCSV(line, journalStructureSB);
77              }
78              else if (line.startsWith("insert into JournalTemplate (")) {
79                  _convertToOracleCSV(line, journalTemplateSB);
80              }
81          }
82  
83          br.close();
84  
85          if (imageSB.length() > 0) {
86              FileUtil.write(
87                  "../sql/" + fileName + "/" + fileName + "-oracle-image.csv",
88                  imageSB.toString());
89          }
90  
91          if (journalArticleSB.length() > 0) {
92              FileUtil.write(
93                  "../sql/" + fileName + "/" + fileName +
94                      "-oracle-journalarticle.csv",
95                  journalArticleSB.toString());
96          }
97  
98          if (journalStructureSB.length() > 0) {
99              FileUtil.write(
100                 "../sql/" + fileName + "/" + fileName +
101                     "-oracle-journalstructure.csv",
102                 journalStructureSB.toString());
103         }
104 
105         if (journalTemplateSB.length() > 0) {
106             FileUtil.write(
107                 "../sql/" + fileName + "/" + fileName +
108                     "-oracle-journaltemplate.csv",
109                 journalTemplateSB.toString());
110         }
111 
112         oracle = _postBuildSQL(oracle);
113 
114         FileUtil.write(
115             "../sql/" + fileName + "/" + fileName + "-oracle.sql", oracle);
116     }
117 
118     public boolean isSupportsAlterColumnType() {
119 
120         // LPS-3566
121 
122         return _SUPPORTS_ALTER_COLUMN_TYPE;
123     }
124 
125     protected OracleUtil() {
126         super(TYPE_ORACLE);
127     }
128 
129     protected void buildCreateFile(String databaseName, boolean minimal)
130         throws IOException {
131 
132         String minimalSuffix = getMinimalSuffix(minimal);
133 
134         File file = new File(
135             "../sql/create" + minimalSuffix + "/create" + minimalSuffix +
136                 "-oracle.sql");
137 
138         StringBuilder sb = new StringBuilder();
139 
140         sb.append("drop user &1 cascade;\n");
141         sb.append("create user &1 identified by &2;\n");
142         sb.append("grant connect,resource to &1;\n");
143         sb.append("connect &1/&2;\n");
144         sb.append("set define off;\n");
145         sb.append("\n");
146         sb.append(
147             FileUtil.read(
148                 "../sql/portal" + minimalSuffix + "/portal" + minimalSuffix +
149                     "-oracle.sql"));
150         sb.append("\n\n");
151         sb.append(FileUtil.read("../sql/indexes/indexes-oracle.sql"));
152         sb.append("\n\n");
153         sb.append(FileUtil.read("../sql/sequences/sequences-oracle.sql"));
154         sb.append("\n");
155         sb.append("quit");
156 
157         FileUtil.write(file, sb.toString());
158     }
159 
160     protected String getServerName() {
161         return "oracle";
162     }
163 
164     protected String[] getTemplate() {
165         return _ORACLE;
166     }
167 
168     protected String reword(String data) throws IOException {
169         BufferedReader br = new BufferedReader(new StringReader(data));
170 
171         StringBuilder sb = new StringBuilder();
172 
173         String line = null;
174 
175         while ((line = br.readLine()) != null) {
176             if (line.startsWith(ALTER_COLUMN_NAME)) {
177                 String[] template = buildColumnNameTokens(line);
178 
179                 line = StringUtil.replace(
180                     "alter table @table@ rename column @old-column@ to " +
181                         "@new-column@;",
182                     REWORD_TEMPLATE, template);
183             }
184             else if (line.startsWith(ALTER_COLUMN_TYPE)) {
185                 String[] template = buildColumnTypeTokens(line);
186 
187                 line = StringUtil.replace(
188                     "alter table @table@ modify @old-column@ @type@;",
189                     REWORD_TEMPLATE, template);
190             }
191 
192             sb.append(line);
193             sb.append("\n");
194         }
195 
196         br.close();
197 
198         return sb.toString();
199     }
200 
201     private void _convertToOracleCSV(String line, StringBuilder sb) {
202         int x = line.indexOf("values (");
203         int y = line.lastIndexOf(");");
204 
205         line = line.substring(x + 8, y);
206 
207         line = StringUtil.replace(line, "sysdate, ", "20050101, ");
208 
209         sb.append(line);
210         sb.append("\n");
211     }
212 
213     private String _preBuildSQL(String template) throws IOException {
214         template = convertTimestamp(template);
215         template = replaceTemplate(template, getTemplate());
216 
217         template = reword(template);
218         template = StringUtil.replace(
219             template,
220             new String[] {"\\\\", "\\'", "\\\""},
221             new String[] {"\\", "''", "\""});
222 
223         return template;
224     }
225 
226     private String _postBuildSQL(String template) throws IOException {
227         template = removeLongInserts(template);
228         template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
229 
230         return template;
231     }
232 
233     private static String[] _ORACLE = {
234         "--", "1", "0",
235         "to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')", "sysdate",
236         " blob", " number(1, 0)", " timestamp",
237         " number(30,20)", " number(30,0)", " number(30,0)",
238         " varchar2(4000)", " clob", " varchar2",
239         "", "commit"
240     };
241 
242     private static boolean _SUPPORTS_ALTER_COLUMN_TYPE;
243 
244     private static OracleUtil _instance = new OracleUtil();
245 
246 }