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