1   /**
2    * Copyright (c) 2000-2009 Liferay, Inc. All rights reserved.
3    *
4    * The contents of this file are subject to the terms of the Liferay Enterprise
5    * Subscription License ("License"). You may not use this file except in
6    * compliance with the License. You can obtain a copy of the License by
7    * contacting Liferay, Inc. See the License for the specific language governing
8    * permissions and limitations under the License, including but not limited to
9    * distribution rights of the Software.
10   *
11   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
12   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
13   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
14   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
15   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
16   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
17   * SOFTWARE.
18   */
19  
20  package com.liferay.portal.tools.sql;
21  
22  import com.liferay.portal.kernel.util.FileUtil;
23  import com.liferay.portal.kernel.util.StringUtil;
24  
25  import java.io.BufferedReader;
26  import java.io.File;
27  import java.io.IOException;
28  import java.io.StringReader;
29  
30  /**
31   * <a href="OracleUtil.java.html"><b><i>View Source</i></b></a>
32   *
33   * @author Alexander Chow
34   * @author Sandeep Soni
35   * @author Ganesh Ram
36   *
37   */
38  public class OracleUtil extends DBUtil {
39  
40      public static DBUtil getInstance() {
41          return _instance;
42      }
43  
44      public String buildSQL(String template) throws IOException {
45          template = _preBuildSQL(template);
46          template = _postBuildSQL(template);
47  
48          return template;
49      }
50  
51      public void buildSQLFile(String fileName) throws IOException {
52          String oracle = buildTemplate(fileName);
53  
54          oracle = _preBuildSQL(oracle);
55  
56          BufferedReader br = new BufferedReader(new StringReader(oracle));
57  
58          StringBuilder imageSB = new StringBuilder();
59          StringBuilder journalArticleSB = new StringBuilder();
60          StringBuilder journalStructureSB = new StringBuilder();
61          StringBuilder journalTemplateSB = new StringBuilder();
62  
63          String line = null;
64  
65          while ((line = br.readLine()) != null) {
66              if (line.startsWith("insert into Image")) {
67                  _convertToOracleCSV(line, imageSB);
68              }
69              else if (line.startsWith("insert into JournalArticle (")) {
70                  _convertToOracleCSV(line, journalArticleSB);
71              }
72              else if (line.startsWith("insert into JournalStructure (")) {
73                  _convertToOracleCSV(line, journalStructureSB);
74              }
75              else if (line.startsWith("insert into JournalTemplate (")) {
76                  _convertToOracleCSV(line, journalTemplateSB);
77              }
78          }
79  
80          br.close();
81  
82          if (imageSB.length() > 0) {
83              FileUtil.write(
84                  "../sql/" + fileName + "/" + fileName + "-oracle-image.csv",
85                  imageSB.toString());
86          }
87  
88          if (journalArticleSB.length() > 0) {
89              FileUtil.write(
90                  "../sql/" + fileName + "/" + fileName +
91                      "-oracle-journalarticle.csv",
92                  journalArticleSB.toString());
93          }
94  
95          if (journalStructureSB.length() > 0) {
96              FileUtil.write(
97                  "../sql/" + fileName + "/" + fileName +
98                      "-oracle-journalstructure.csv",
99                  journalStructureSB.toString());
100         }
101 
102         if (journalTemplateSB.length() > 0) {
103             FileUtil.write(
104                 "../sql/" + fileName + "/" + fileName +
105                     "-oracle-journaltemplate.csv",
106                 journalTemplateSB.toString());
107         }
108 
109         oracle = _postBuildSQL(oracle);
110 
111         FileUtil.write(
112             "../sql/" + fileName + "/" + fileName + "-oracle.sql", oracle);
113     }
114 
115     protected OracleUtil() {
116         super(TYPE_ORACLE);
117     }
118 
119     protected void buildCreateFile(String databaseName, boolean minimal)
120         throws IOException {
121 
122         String minimalSuffix = getMinimalSuffix(minimal);
123 
124         File file = new File(
125             "../sql/create" + minimalSuffix + "/create" + minimalSuffix +
126                 "-oracle.sql");
127 
128         StringBuilder sb = new StringBuilder();
129 
130         sb.append("drop user &1 cascade;\n");
131         sb.append("create user &1 identified by &2;\n");
132         sb.append("grant connect,resource to &1;\n");
133         sb.append("connect &1/&2;\n");
134         sb.append("set define off;\n");
135         sb.append("\n");
136         sb.append(
137             FileUtil.read(
138                 "../sql/portal" + minimalSuffix + "/portal" + minimalSuffix +
139                     "-oracle.sql"));
140         sb.append("\n\n");
141         sb.append(FileUtil.read("../sql/indexes/indexes-oracle.sql"));
142         sb.append("\n\n");
143         sb.append(FileUtil.read("../sql/sequences/sequences-oracle.sql"));
144         sb.append("\n");
145         sb.append("quit");
146 
147         FileUtil.write(file, sb.toString());
148     }
149 
150     protected String getServerName() {
151         return "oracle";
152     }
153 
154     protected String[] getTemplate() {
155         return _ORACLE;
156     }
157 
158     protected String reword(String data) throws IOException {
159         BufferedReader br = new BufferedReader(new StringReader(data));
160 
161         StringBuilder sb = new StringBuilder();
162 
163         String line = null;
164 
165         while ((line = br.readLine()) != null) {
166             if (line.startsWith(ALTER_COLUMN_NAME)) {
167                 String[] template = buildColumnNameTokens(line);
168 
169                 line = StringUtil.replace(
170                     "alter table @table@ rename column @old-column@ to " +
171                         "@new-column@;",
172                     REWORD_TEMPLATE, template);
173             }
174             else if (line.startsWith(ALTER_COLUMN_TYPE)) {
175                 String[] template = buildColumnTypeTokens(line);
176 
177                 line = StringUtil.replace(
178                     "alter table @table@ modify @old-column@ @type@;",
179                     REWORD_TEMPLATE, template);
180             }
181 
182             sb.append(line);
183             sb.append("\n");
184         }
185 
186         br.close();
187 
188         return sb.toString();
189     }
190 
191     private void _convertToOracleCSV(String line, StringBuilder sb) {
192         int x = line.indexOf("values (");
193         int y = line.lastIndexOf(");");
194 
195         line = line.substring(x + 8, y);
196 
197         line = StringUtil.replace(line, "sysdate, ", "20050101, ");
198 
199         sb.append(line);
200         sb.append("\n");
201     }
202 
203     private String _preBuildSQL(String template) throws IOException {
204         template = convertTimestamp(template);
205         template = replaceTemplate(template, getTemplate());
206 
207         template = reword(template);
208         template = StringUtil.replace(
209             template,
210             new String[] {"\\\\", "\\'", "\\\""},
211             new String[] {"\\", "''", "\""});
212 
213         return template;
214     }
215 
216     private String _postBuildSQL(String template) throws IOException {
217         template = removeLongInserts(template);
218         template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
219 
220         return template;
221     }
222 
223     private static String[] _ORACLE = {
224         "--", "1", "0",
225         "to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')", "sysdate",
226         " blob", " number(1, 0)", " timestamp",
227         " number(30,20)", " number(30,0)", " number(30,0)",
228         " varchar2(4000)", " clob", " varchar2",
229         "", "commit"
230     };
231 
232     private static OracleUtil _instance = new OracleUtil();
233 
234 }