1   /**
2    * Copyright (c) 2000-2010 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   *
12   *
13   */
14  
15  package com.liferay.portal.dao.db;
16  
17  import com.liferay.portal.kernel.dao.db.DB;
18  import com.liferay.portal.kernel.dao.db.Index;
19  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
20  import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
21  import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
22  import com.liferay.portal.kernel.util.FileUtil;
23  import com.liferay.portal.kernel.util.GetterUtil;
24  import com.liferay.portal.kernel.util.StringBundler;
25  import com.liferay.portal.kernel.util.StringUtil;
26  
27  import java.io.IOException;
28  
29  import java.sql.Connection;
30  import java.sql.PreparedStatement;
31  import java.sql.ResultSet;
32  import java.sql.SQLException;
33  
34  import java.util.ArrayList;
35  import java.util.List;
36  import java.util.regex.Matcher;
37  import java.util.regex.Pattern;
38  
39  /**
40   * <a href="OracleDB.java.html"><b><i>View Source</i></b></a>
41   *
42   * @author Alexander Chow
43   * @author Sandeep Soni
44   * @author Ganesh Ram
45   */
46  public class OracleDB extends BaseDB {
47  
48      public static DB getInstance() {
49          return _instance;
50      }
51  
52      public String buildSQL(String template) throws IOException {
53          template = _preBuildSQL(template);
54          template = _postBuildSQL(template);
55  
56          return template;
57      }
58  
59      public void buildSQLFile(String sqlDir, String fileName)
60          throws IOException {
61  
62          String oracle = buildTemplate(sqlDir, fileName);
63  
64          oracle = _preBuildSQL(oracle);
65  
66          UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
67              new UnsyncStringReader(oracle));
68  
69          StringBundler imageSB = new StringBundler();
70          StringBundler journalArticleSB = new StringBundler();
71          StringBundler journalStructureSB = new StringBundler();
72          StringBundler journalTemplateSB = new StringBundler();
73  
74          String line = null;
75  
76          while ((line = unsyncBufferedReader.readLine()) != null) {
77              if (line.startsWith("insert into Image")) {
78                  _convertToOracleCSV(line, imageSB);
79              }
80              else if (line.startsWith("insert into JournalArticle (")) {
81                  _convertToOracleCSV(line, journalArticleSB);
82              }
83              else if (line.startsWith("insert into JournalStructure (")) {
84                  _convertToOracleCSV(line, journalStructureSB);
85              }
86              else if (line.startsWith("insert into JournalTemplate (")) {
87                  _convertToOracleCSV(line, journalTemplateSB);
88              }
89          }
90  
91          unsyncBufferedReader.close();
92  
93          if (imageSB.length() > 0) {
94              FileUtil.write(
95                  sqlDir + "/" + fileName + "/" + fileName + "-oracle-image.csv",
96                  imageSB.toString());
97          }
98  
99          if (journalArticleSB.length() > 0) {
100             FileUtil.write(
101                 sqlDir + "/" + fileName + "/" + fileName +
102                     "-oracle-journalarticle.csv",
103                 journalArticleSB.toString());
104         }
105 
106         if (journalStructureSB.length() > 0) {
107             FileUtil.write(
108                 sqlDir + "/" + fileName + "/" + fileName +
109                     "-oracle-journalstructure.csv",
110                 journalStructureSB.toString());
111         }
112 
113         if (journalTemplateSB.length() > 0) {
114             FileUtil.write(
115                 sqlDir + "/" + fileName + "/" + fileName +
116                     "-oracle-journaltemplate.csv",
117                 journalTemplateSB.toString());
118         }
119 
120         oracle = _postBuildSQL(oracle);
121 
122         FileUtil.write(
123             sqlDir + "/" + fileName + "/" + fileName + "-oracle.sql", oracle);
124     }
125 
126     public List<Index> getIndexes() throws SQLException {
127         List<Index> indexes = new ArrayList<Index>();
128 
129         Connection con = null;
130         PreparedStatement ps = null;
131         ResultSet rs = null;
132 
133         try {
134             con = DataAccess.getConnection();
135 
136             StringBundler sb = new StringBundler(3);
137 
138             sb.append("select index_name, table_name, uniqueness from ");
139             sb.append("user_indexes where index_name like 'LIFERAY_%' or ");
140             sb.append("index_name like 'IX_%'");
141 
142             String sql = sb.toString();
143 
144             ps = con.prepareStatement(sql);
145 
146             rs = ps.executeQuery();
147 
148             while (rs.next()) {
149                 String indexName = rs.getString("index_name");
150                 String tableName = rs.getString("table_name");
151                 String uniqueness = rs.getString("uniqueness");
152 
153                 boolean unique = true;
154 
155                 if (uniqueness.equalsIgnoreCase("NONUNIQUE")) {
156                     unique = false;
157                 }
158 
159                 indexes.add(new Index(indexName, tableName, unique));
160             }
161         }
162         finally {
163             DataAccess.cleanUp(con, ps, rs);
164         }
165 
166         return indexes;
167     }
168 
169     public boolean isSupportsInlineDistinct() {
170         return _SUPPORTS_INLINE_DISTINCT;
171     }
172 
173     protected OracleDB() {
174         super(TYPE_ORACLE);
175     }
176 
177     protected String buildCreateFileContent(
178             String sqlDir, String databaseName, int population)
179         throws IOException {
180 
181         String suffix = getSuffix(population);
182 
183         StringBundler sb = new StringBundler(13);
184 
185         sb.append("drop user &1 cascade;\n");
186         sb.append("create user &1 identified by &2;\n");
187         sb.append("grant connect,resource to &1;\n");
188         sb.append("connect &1/&2;\n");
189         sb.append("set define off;\n");
190         sb.append("\n");
191         sb.append(
192             readFile(
193                 sqlDir + "/portal" + suffix + "/portal" + suffix +
194                     "-oracle.sql"));
195         sb.append("\n\n");
196         sb.append(readFile(sqlDir + "/indexes/indexes-oracle.sql"));
197         sb.append("\n\n");
198         sb.append(readFile(sqlDir + "/sequences/sequences-oracle.sql"));
199         sb.append("\n");
200         sb.append("quit");
201 
202         return sb.toString();
203     }
204 
205     protected String getServerName() {
206         return "oracle";
207     }
208 
209     protected String[] getTemplate() {
210         return _ORACLE;
211     }
212 
213     protected String replaceTemplate(String template, String[] actual) {
214 
215         // LPS-12048
216 
217         Matcher matcher = _varcharPattern.matcher(template);
218 
219         StringBuffer sb = new StringBuffer();
220 
221         while (matcher.find()) {
222             int size = GetterUtil.getInteger(matcher.group()) * 4;
223 
224             if (size > 4000) {
225                 size = 4000;
226             }
227 
228             matcher.appendReplacement(sb, "VARCHAR(" + size + ")");
229         }
230 
231         matcher.appendTail(sb);
232 
233         template = sb.toString();
234 
235         return super.replaceTemplate(template, actual);
236     }
237 
238     protected String reword(String data) throws IOException {
239         UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
240             new UnsyncStringReader(data));
241 
242         StringBundler sb = new StringBundler();
243 
244         String line = null;
245 
246         while ((line = unsyncBufferedReader.readLine()) != null) {
247             if (line.startsWith(ALTER_COLUMN_NAME)) {
248                 String[] template = buildColumnNameTokens(line);
249 
250                 line = StringUtil.replace(
251                     "alter table @table@ rename column @old-column@ to " +
252                         "@new-column@;",
253                     REWORD_TEMPLATE, template);
254             }
255             else if (line.startsWith(ALTER_COLUMN_TYPE)) {
256                 String[] template = buildColumnTypeTokens(line);
257 
258                 line = StringUtil.replace(
259                     "alter table @table@ modify @old-column@ @type@;",
260                     REWORD_TEMPLATE, template);
261             }
262             else if (line.indexOf(DROP_INDEX) != -1) {
263                 String[] tokens = StringUtil.split(line, " ");
264 
265                 line = StringUtil.replace(
266                     "drop index @index@;", "@index@", tokens[2]);
267             }
268 
269             sb.append(line);
270             sb.append("\n");
271         }
272 
273         unsyncBufferedReader.close();
274 
275         return sb.toString();
276     }
277 
278     private void _convertToOracleCSV(String line, StringBundler sb) {
279         int x = line.indexOf("values (");
280         int y = line.lastIndexOf(");");
281 
282         line = line.substring(x + 8, y);
283 
284         line = StringUtil.replace(line, "sysdate, ", "20050101, ");
285 
286         sb.append(line);
287         sb.append("\n");
288     }
289 
290     private String _preBuildSQL(String template) throws IOException {
291         template = convertTimestamp(template);
292         template = replaceTemplate(template, getTemplate());
293 
294         template = reword(template);
295         template = StringUtil.replace(
296             template,
297             new String[] {"\\\\", "\\'", "\\\""},
298             new String[] {"\\", "''", "\""});
299 
300         return template;
301     }
302 
303     private String _postBuildSQL(String template) throws IOException {
304         template = removeLongInserts(template);
305         template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
306 
307         return template;
308     }
309 
310     private static String[] _ORACLE = {
311         "--", "1", "0",
312         "to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')", "sysdate",
313         " blob", " number(1, 0)", " timestamp",
314         " number(30,20)", " number(30,0)", " number(30,0)",
315         " varchar2(4000)", " clob", " varchar2",
316         "", "commit"
317     };
318 
319     private static final boolean _SUPPORTS_INLINE_DISTINCT = false;
320 
321     private static OracleDB _instance = new OracleDB();
322 
323     private static Pattern _varcharPattern = Pattern.compile(
324         "VARCHAR(\\(\\d+\\))");
325 
326 }