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