001    /**
002     * Copyright (c) 2000-2013 Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.portal.dao.db;
016    
017    import com.liferay.portal.kernel.dao.db.DB;
018    import com.liferay.portal.kernel.dao.db.Index;
019    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
020    import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
021    import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
022    import com.liferay.portal.kernel.util.FileUtil;
023    import com.liferay.portal.kernel.util.GetterUtil;
024    import com.liferay.portal.kernel.util.StringBundler;
025    import com.liferay.portal.kernel.util.StringUtil;
026    
027    import java.io.IOException;
028    
029    import java.sql.Connection;
030    import java.sql.PreparedStatement;
031    import java.sql.ResultSet;
032    import java.sql.SQLException;
033    
034    import java.util.ArrayList;
035    import java.util.List;
036    import java.util.regex.Matcher;
037    import java.util.regex.Pattern;
038    
039    /**
040     * @author Alexander Chow
041     * @author Sandeep Soni
042     * @author Ganesh Ram
043     */
044    public class OracleDB extends BaseDB {
045    
046            public static DB getInstance() {
047                    return _instance;
048            }
049    
050            @Override
051            public String buildSQL(String template) throws IOException {
052                    template = _preBuildSQL(template);
053                    template = _postBuildSQL(template);
054    
055                    return template;
056            }
057    
058            @Override
059            public void buildSQLFile(String sqlDir, String fileName)
060                    throws IOException {
061    
062                    String oracle = buildTemplate(sqlDir, fileName);
063    
064                    oracle = _preBuildSQL(oracle);
065    
066                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
067                            new UnsyncStringReader(oracle));
068    
069                    StringBundler imageSB = new StringBundler();
070                    StringBundler journalArticleSB = new StringBundler();
071                    StringBundler journalStructureSB = new StringBundler();
072                    StringBundler journalTemplateSB = new StringBundler();
073    
074                    String line = null;
075    
076                    while ((line = unsyncBufferedReader.readLine()) != null) {
077                            if (line.startsWith("insert into Image")) {
078                                    _convertToOracleCSV(line, imageSB);
079                            }
080                            else if (line.startsWith("insert into JournalArticle (")) {
081                                    _convertToOracleCSV(line, journalArticleSB);
082                            }
083                            else if (line.startsWith("insert into JournalStructure (")) {
084                                    _convertToOracleCSV(line, journalStructureSB);
085                            }
086                            else if (line.startsWith("insert into JournalTemplate (")) {
087                                    _convertToOracleCSV(line, journalTemplateSB);
088                            }
089                    }
090    
091                    unsyncBufferedReader.close();
092    
093                    if (imageSB.length() > 0) {
094                            FileUtil.write(
095                                    sqlDir + "/" + fileName + "/" + fileName + "-oracle-image.csv",
096                                    imageSB.toString());
097                    }
098    
099                    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            @Override
127            public List<Index> getIndexes(Connection con) throws SQLException {
128                    List<Index> indexes = new ArrayList<Index>();
129    
130                    PreparedStatement ps = null;
131                    ResultSet rs = null;
132    
133                    try {
134                            StringBundler sb = new StringBundler(3);
135    
136                            sb.append("select index_name, table_name, uniqueness from ");
137                            sb.append("user_indexes where index_name like 'LIFERAY_%' or ");
138                            sb.append("index_name like 'IX_%'");
139    
140                            String sql = sb.toString();
141    
142                            ps = con.prepareStatement(sql);
143    
144                            rs = ps.executeQuery();
145    
146                            while (rs.next()) {
147                                    String indexName = rs.getString("index_name");
148                                    String tableName = rs.getString("table_name");
149                                    String uniqueness = rs.getString("uniqueness");
150    
151                                    boolean unique = true;
152    
153                                    if (uniqueness.equalsIgnoreCase("NONUNIQUE")) {
154                                            unique = false;
155                                    }
156    
157                                    indexes.add(new Index(indexName, tableName, unique));
158                            }
159                    }
160                    finally {
161                            DataAccess.cleanUp(null, ps, rs);
162                    }
163    
164                    return indexes;
165            }
166    
167            @Override
168            public boolean isSupportsInlineDistinct() {
169                    return _SUPPORTS_INLINE_DISTINCT;
170            }
171    
172            protected OracleDB() {
173                    super(TYPE_ORACLE);
174            }
175    
176            @Override
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(getCreateTablesContent(sqlDir, suffix));
192                    sb.append("\n\n");
193                    sb.append(readFile(sqlDir + "/indexes/indexes-oracle.sql"));
194                    sb.append("\n\n");
195                    sb.append(readFile(sqlDir + "/sequences/sequences-oracle.sql"));
196                    sb.append("\n");
197                    sb.append("quit");
198    
199                    return sb.toString();
200            }
201    
202            @Override
203            protected String getServerName() {
204                    return "oracle";
205            }
206    
207            @Override
208            protected String[] getTemplate() {
209                    return _ORACLE;
210            }
211    
212            @Override
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());
223    
224                            if (size > 4000) {
225                                    size = 4000;
226                            }
227    
228                            matcher.appendReplacement(sb, "VARCHAR2(" + size + " CHAR)");
229                    }
230    
231                    matcher.appendTail(sb);
232    
233                    template = sb.toString();
234    
235                    return super.replaceTemplate(template, actual);
236            }
237    
238            @Override
239            protected String reword(String data) throws IOException {
240                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
241                            new UnsyncStringReader(data));
242    
243                    StringBundler sb = new StringBundler();
244    
245                    String line = null;
246    
247                    while ((line = unsyncBufferedReader.readLine()) != null) {
248                            if (line.startsWith(ALTER_COLUMN_NAME)) {
249                                    String[] template = buildColumnNameTokens(line);
250    
251                                    line = StringUtil.replace(
252                                            "alter table @table@ rename column @old-column@ to " +
253                                                    "@new-column@;",
254                                            REWORD_TEMPLATE, template);
255                            }
256                            else if (line.startsWith(ALTER_COLUMN_TYPE)) {
257                                    String[] template = buildColumnTypeTokens(line);
258    
259                                    line = StringUtil.replace(
260                                            "alter table @table@ modify @old-column@ @type@;",
261                                            REWORD_TEMPLATE, template);
262                            }
263                            else if (line.startsWith(ALTER_TABLE_NAME)) {
264                                    String[] template = buildTableNameTokens(line);
265    
266                                    line = StringUtil.replace(
267                                            "alter table @old-table@ rename to @new-table@;",
268                                            RENAME_TABLE_TEMPLATE, template);
269                            }
270                            else if (line.contains(DROP_INDEX)) {
271                                    String[] tokens = StringUtil.split(line, ' ');
272    
273                                    line = StringUtil.replace(
274                                            "drop index @index@;", "@index@", tokens[2]);
275                            }
276    
277                            sb.append(line);
278                            sb.append("\n");
279                    }
280    
281                    unsyncBufferedReader.close();
282    
283                    return sb.toString();
284            }
285    
286            private void _convertToOracleCSV(String line, StringBundler sb) {
287                    int x = line.indexOf("values (");
288                    int y = line.lastIndexOf(");");
289    
290                    line = line.substring(x + 8, y);
291    
292                    line = StringUtil.replace(line, "sysdate, ", "20050101, ");
293    
294                    sb.append(line);
295                    sb.append("\n");
296            }
297    
298            private String _postBuildSQL(String template) throws IOException {
299                    template = removeLongInserts(template);
300                    template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
301    
302                    return template;
303            }
304    
305            private String _preBuildSQL(String template) throws IOException {
306                    template = convertTimestamp(template);
307                    template = replaceTemplate(template, getTemplate());
308    
309                    template = reword(template);
310                    template = StringUtil.replace(
311                            template,
312                            new String[] {"\\\\", "\\'", "\\\""},
313                            new String[] {"\\", "''", "\""});
314    
315                    return template;
316            }
317    
318            private static final String[] _ORACLE = {
319                    "--", "1", "0",
320                    "to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')", "sysdate",
321                    " blob", " blob", " number(1, 0)", " timestamp", " number(30,20)",
322                    " number(30,0)", " number(30,0)", " varchar2(4000)", " clob",
323                    " varchar2", "", "commit"
324            };
325    
326            private static final boolean _SUPPORTS_INLINE_DISTINCT = false;
327    
328            private static OracleDB _instance = new OracleDB();
329    
330            private static Pattern _varcharPattern = Pattern.compile(
331                    "VARCHAR(\\(\\d+\\))");
332    
333    }