001    /**
002     * Copyright (c) 2000-present 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.Index;
018    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
019    import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
020    import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
021    import com.liferay.portal.kernel.util.FileUtil;
022    import com.liferay.portal.kernel.util.GetterUtil;
023    import com.liferay.portal.kernel.util.StringBundler;
024    import com.liferay.portal.kernel.util.StringPool;
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 OracleDB(int majorVersion, int minorVersion) {
047                    super(TYPE_ORACLE, majorVersion, minorVersion);
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                    StringBundler imageSB = new StringBundler();
067                    StringBundler journalArticleSB = new StringBundler();
068    
069                    try (UnsyncBufferedReader unsyncBufferedReader =
070                                    new UnsyncBufferedReader(new UnsyncStringReader(oracle))) {
071    
072                            String line = null;
073    
074                            while ((line = unsyncBufferedReader.readLine()) != null) {
075                                    if (line.startsWith("insert into Image")) {
076                                            _convertToOracleCSV(line, imageSB);
077                                    }
078                                    else if (line.startsWith("insert into JournalArticle (")) {
079                                            _convertToOracleCSV(line, journalArticleSB);
080                                    }
081                            }
082                    }
083    
084                    if (imageSB.length() > 0) {
085                            FileUtil.write(
086                                    sqlDir + "/" + fileName + "/" + fileName + "-oracle-image.csv",
087                                    imageSB.toString());
088                    }
089    
090                    if (journalArticleSB.length() > 0) {
091                            FileUtil.write(
092                                    sqlDir + "/" + fileName + "/" + fileName +
093                                            "-oracle-journalarticle.csv",
094                                    journalArticleSB.toString());
095                    }
096    
097                    oracle = _postBuildSQL(oracle);
098    
099                    FileUtil.write(
100                            sqlDir + "/" + fileName + "/" + fileName + "-oracle.sql", oracle);
101            }
102    
103            @Override
104            public List<Index> getIndexes(Connection con) throws SQLException {
105                    List<Index> indexes = new ArrayList<>();
106    
107                    PreparedStatement ps = null;
108                    ResultSet rs = null;
109    
110                    try {
111                            StringBundler sb = new StringBundler(3);
112    
113                            sb.append("select index_name, table_name, uniqueness from ");
114                            sb.append("user_indexes where index_name like 'LIFERAY_%' or ");
115                            sb.append("index_name like 'IX_%'");
116    
117                            String sql = sb.toString();
118    
119                            ps = con.prepareStatement(sql);
120    
121                            rs = ps.executeQuery();
122    
123                            while (rs.next()) {
124                                    String indexName = rs.getString("index_name");
125                                    String tableName = rs.getString("table_name");
126                                    String uniqueness = rs.getString("uniqueness");
127    
128                                    boolean unique = true;
129    
130                                    if (StringUtil.equalsIgnoreCase(uniqueness, "NONUNIQUE")) {
131                                            unique = false;
132                                    }
133    
134                                    indexes.add(new Index(indexName, tableName, unique));
135                            }
136                    }
137                    finally {
138                            DataAccess.cleanUp(null, ps, rs);
139                    }
140    
141                    return indexes;
142            }
143    
144            @Override
145            public boolean isSupportsInlineDistinct() {
146                    return _SUPPORTS_INLINE_DISTINCT;
147            }
148    
149            @Override
150            protected String[] buildColumnTypeTokens(String line) {
151                    Matcher matcher = _varchar2CharPattern.matcher(line);
152    
153                    StringBuffer sb = new StringBuffer();
154    
155                    while (matcher.find()) {
156                            matcher.appendReplacement(
157                                    sb, "VARCHAR2(" + matcher.group(1) + "%20CHAR)");
158                    }
159    
160                    matcher.appendTail(sb);
161    
162                    String[] template = super.buildColumnTypeTokens(sb.toString());
163    
164                    template[3] = StringUtil.replace(template[3], "%20", StringPool.SPACE);
165    
166                    return template;
167            }
168    
169            @Override
170            protected String buildCreateFileContent(
171                            String sqlDir, String databaseName, int population)
172                    throws IOException {
173    
174                    String suffix = getSuffix(population);
175    
176                    StringBundler sb = new StringBundler(13);
177    
178                    sb.append("drop user &1 cascade;\n");
179                    sb.append("create user &1 identified by &2;\n");
180                    sb.append("grant connect,resource to &1;\n");
181    
182                    if (population != BARE) {
183                            sb.append("connect &1/&2;\n");
184                            sb.append("set define off;\n");
185                            sb.append("\n");
186                            sb.append(getCreateTablesContent(sqlDir, suffix));
187                            sb.append("\n\n");
188                            sb.append(readFile(sqlDir + "/indexes/indexes-oracle.sql"));
189                            sb.append("\n\n");
190                            sb.append(readFile(sqlDir + "/sequences/sequences-oracle.sql"));
191                            sb.append("\n");
192                    }
193    
194                    sb.append("quit");
195    
196                    return sb.toString();
197            }
198    
199            @Override
200            protected String getServerName() {
201                    return "oracle";
202            }
203    
204            @Override
205            protected String[] getTemplate() {
206                    return _ORACLE;
207            }
208    
209            @Override
210            protected String replaceTemplate(String template, String[] actual) {
211    
212                    // LPS-12048
213    
214                    Matcher matcher = _varcharPattern.matcher(template);
215    
216                    StringBuffer sb = new StringBuffer();
217    
218                    while (matcher.find()) {
219                            int size = GetterUtil.getInteger(matcher.group(1));
220    
221                            if (size > 4000) {
222                                    size = 4000;
223                            }
224    
225                            matcher.appendReplacement(sb, "VARCHAR2(" + size + " CHAR)");
226                    }
227    
228                    matcher.appendTail(sb);
229    
230                    template = sb.toString();
231    
232                    return super.replaceTemplate(template, actual);
233            }
234    
235            @Override
236            protected String reword(String data) throws IOException {
237                    try (UnsyncBufferedReader unsyncBufferedReader =
238                                    new UnsyncBufferedReader(new UnsyncStringReader(data))) {
239    
240                            StringBundler sb = new StringBundler();
241    
242                            String line = null;
243    
244                            while ((line = unsyncBufferedReader.readLine()) != null) {
245                                    if (line.startsWith(ALTER_COLUMN_NAME)) {
246                                            String[] template = buildColumnNameTokens(line);
247    
248                                            line = StringUtil.replace(
249                                                    "alter table @table@ rename column @old-column@ to " +
250                                                            "@new-column@;",
251                                                    REWORD_TEMPLATE, template);
252                                    }
253                                    else if (line.startsWith(ALTER_COLUMN_TYPE)) {
254                                            String[] template = buildColumnTypeTokens(line);
255    
256                                            line = StringUtil.replace(
257                                                    "alter table @table@ modify @old-column@ @type@;",
258                                                    REWORD_TEMPLATE, template);
259                                    }
260                                    else if (line.startsWith(ALTER_TABLE_NAME)) {
261                                            String[] template = buildTableNameTokens(line);
262    
263                                            line = StringUtil.replace(
264                                                    "alter table @old-table@ rename to @new-table@;",
265                                                    RENAME_TABLE_TEMPLATE, template);
266                                    }
267                                    else if (line.contains(DROP_INDEX)) {
268                                            String[] tokens = StringUtil.split(line, ' ');
269    
270                                            line = StringUtil.replace(
271                                                    "drop index @index@;", "@index@", tokens[2]);
272                                    }
273    
274                                    sb.append(line);
275                                    sb.append("\n");
276                            }
277    
278                            return sb.toString();
279                    }
280            }
281    
282            private void _convertToOracleCSV(String line, StringBundler sb) {
283                    int x = line.indexOf("values (");
284                    int y = line.lastIndexOf(");");
285    
286                    line = line.substring(x + 8, y);
287    
288                    line = StringUtil.replace(line, "sysdate, ", "20050101, ");
289    
290                    sb.append(line);
291                    sb.append("\n");
292            }
293    
294            private String _postBuildSQL(String template) throws IOException {
295                    template = removeLongInserts(template);
296                    template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
297    
298                    return template;
299            }
300    
301            private String _preBuildSQL(String template) throws IOException {
302                    template = convertTimestamp(template);
303                    template = replaceTemplate(template, getTemplate());
304    
305                    template = reword(template);
306                    template = StringUtil.replace(
307                            template,
308                            new String[] {"\\\\", "\\'", "\\\""},
309                            new String[] {"\\", "''", "\""});
310    
311                    return template;
312            }
313    
314            private static final String[] _ORACLE = {
315                    "--", "1", "0",
316                    "to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')", "sysdate",
317                    " blob", " blob", " number(1, 0)", " timestamp", " number(30,20)",
318                    " number(30,0)", " number(30,0)", " varchar2(4000)", " clob",
319                    " varchar2", "", "commit"
320            };
321    
322            private static final boolean _SUPPORTS_INLINE_DISTINCT = false;
323    
324            private static final Pattern _varchar2CharPattern = Pattern.compile(
325                    "VARCHAR2\\((\\d+) CHAR\\)");
326            private static final Pattern _varcharPattern = Pattern.compile(
327                    "VARCHAR\\((\\d+)\\)");
328    
329    }