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.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.IOException;
30  import java.io.StringReader;
31  
32  /**
33   * <a href="OracleUtil.java.html"><b><i>View Source</i></b></a>
34   *
35   * @author Alexander Chow
36   * @author Sandeep Soni
37   * @author Ganesh Ram
38   */
39  public class OracleUtil extends DBUtil {
40  
41      public static DBUtil getInstance() {
42          return _instance;
43      }
44  
45      public String buildSQL(String template) throws IOException {
46          template = _preBuildSQL(template);
47          template = _postBuildSQL(template);
48  
49          return template;
50      }
51  
52      public void buildSQLFile(String fileName) throws IOException {
53          String oracle = buildTemplate(fileName);
54  
55          oracle = _preBuildSQL(oracle);
56  
57          BufferedReader br = new BufferedReader(new StringReader(oracle));
58  
59          StringBuilder imageSB = new StringBuilder();
60          StringBuilder journalArticleSB = new StringBuilder();
61          StringBuilder journalStructureSB = new StringBuilder();
62          StringBuilder journalTemplateSB = new StringBuilder();
63  
64          String line = null;
65  
66          while ((line = br.readLine()) != null) {
67              if (line.startsWith("insert into Image")) {
68                  _convertToOracleCSV(line, imageSB);
69              }
70              else if (line.startsWith("insert into JournalArticle (")) {
71                  _convertToOracleCSV(line, journalArticleSB);
72              }
73              else if (line.startsWith("insert into JournalStructure (")) {
74                  _convertToOracleCSV(line, journalStructureSB);
75              }
76              else if (line.startsWith("insert into JournalTemplate (")) {
77                  _convertToOracleCSV(line, journalTemplateSB);
78              }
79          }
80  
81          br.close();
82  
83          if (imageSB.length() > 0) {
84              FileUtil.write(
85                  "../sql/" + fileName + "/" + fileName + "-oracle-image.csv",
86                  imageSB.toString());
87          }
88  
89          if (journalArticleSB.length() > 0) {
90              FileUtil.write(
91                  "../sql/" + fileName + "/" + fileName +
92                      "-oracle-journalarticle.csv",
93                  journalArticleSB.toString());
94          }
95  
96          if (journalStructureSB.length() > 0) {
97              FileUtil.write(
98                  "../sql/" + fileName + "/" + fileName +
99                      "-oracle-journalstructure.csv",
100                 journalStructureSB.toString());
101         }
102 
103         if (journalTemplateSB.length() > 0) {
104             FileUtil.write(
105                 "../sql/" + fileName + "/" + fileName +
106                     "-oracle-journaltemplate.csv",
107                 journalTemplateSB.toString());
108         }
109 
110         oracle = _postBuildSQL(oracle);
111 
112         FileUtil.write(
113             "../sql/" + fileName + "/" + fileName + "-oracle.sql", oracle);
114     }
115 
116     protected OracleUtil() {
117         super(TYPE_ORACLE);
118     }
119 
120     protected String buildCreateFileContent(String databaseName, int population)
121         throws IOException {
122 
123         String suffix = getSuffix(population);
124 
125         StringBuilder sb = new StringBuilder();
126 
127         sb.append("drop user &1 cascade;\n");
128         sb.append("create user &1 identified by &2;\n");
129         sb.append("grant connect,resource to &1;\n");
130         sb.append("connect &1/&2;\n");
131         sb.append("set define off;\n");
132         sb.append("\n");
133         sb.append(
134             FileUtil.read(
135                 "../sql/portal" + suffix + "/portal" + suffix + "-oracle.sql"));
136         sb.append("\n\n");
137         sb.append(FileUtil.read("../sql/indexes/indexes-oracle.sql"));
138         sb.append("\n\n");
139         sb.append(FileUtil.read("../sql/sequences/sequences-oracle.sql"));
140         sb.append("\n");
141         sb.append("quit");
142 
143         return sb.toString();
144     }
145 
146     protected String getServerName() {
147         return "oracle";
148     }
149 
150     protected String[] getTemplate() {
151         return _ORACLE;
152     }
153 
154     protected String reword(String data) throws IOException {
155         BufferedReader br = new BufferedReader(new StringReader(data));
156 
157         StringBuilder sb = new StringBuilder();
158 
159         String line = null;
160 
161         while ((line = br.readLine()) != null) {
162             if (line.startsWith(ALTER_COLUMN_NAME)) {
163                 String[] template = buildColumnNameTokens(line);
164 
165                 line = StringUtil.replace(
166                     "alter table @table@ rename column @old-column@ to " +
167                         "@new-column@;",
168                     REWORD_TEMPLATE, template);
169             }
170             else if (line.startsWith(ALTER_COLUMN_TYPE)) {
171                 String[] template = buildColumnTypeTokens(line);
172 
173                 line = StringUtil.replace(
174                     "alter table @table@ modify @old-column@ @type@;",
175                     REWORD_TEMPLATE, template);
176             }
177 
178             sb.append(line);
179             sb.append("\n");
180         }
181 
182         br.close();
183 
184         return sb.toString();
185     }
186 
187     private void _convertToOracleCSV(String line, StringBuilder sb) {
188         int x = line.indexOf("values (");
189         int y = line.lastIndexOf(");");
190 
191         line = line.substring(x + 8, y);
192 
193         line = StringUtil.replace(line, "sysdate, ", "20050101, ");
194 
195         sb.append(line);
196         sb.append("\n");
197     }
198 
199     private String _preBuildSQL(String template) throws IOException {
200         template = convertTimestamp(template);
201         template = replaceTemplate(template, getTemplate());
202 
203         template = reword(template);
204         template = StringUtil.replace(
205             template,
206             new String[] {"\\\\", "\\'", "\\\""},
207             new String[] {"\\", "''", "\""});
208 
209         return template;
210     }
211 
212     private String _postBuildSQL(String template) throws IOException {
213         template = removeLongInserts(template);
214         template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
215 
216         return template;
217     }
218 
219     private static String[] _ORACLE = {
220         "--", "1", "0",
221         "to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')", "sysdate",
222         " blob", " number(1, 0)", " timestamp",
223         " number(30,20)", " number(30,0)", " number(30,0)",
224         " varchar2(4000)", " clob", " varchar2",
225         "", "commit"
226     };
227 
228     private static OracleUtil _instance = new OracleUtil();
229 
230 }