001    /**
002     * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
003     *
004     * The contents of this file are subject to the terms of the Liferay Enterprise
005     * Subscription License ("License"). You may not use this file except in
006     * compliance with the License. You can obtain a copy of the License by
007     * contacting Liferay, Inc. See the License for the specific language governing
008     * permissions and limitations under the License, including but not limited to
009     * distribution rights of the Software.
010     *
011     *
012     *
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            public String buildSQL(String template) throws IOException {
051                    template = _preBuildSQL(template);
052                    template = _postBuildSQL(template);
053    
054                    return template;
055            }
056    
057            public void buildSQLFile(String sqlDir, String fileName)
058                    throws IOException {
059    
060                    String oracle = buildTemplate(sqlDir, fileName);
061    
062                    oracle = _preBuildSQL(oracle);
063    
064                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
065                            new UnsyncStringReader(oracle));
066    
067                    StringBundler imageSB = new StringBundler();
068                    StringBundler journalArticleSB = new StringBundler();
069                    StringBundler journalStructureSB = new StringBundler();
070                    StringBundler journalTemplateSB = new StringBundler();
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                            else if (line.startsWith("insert into JournalStructure (")) {
082                                    _convertToOracleCSV(line, journalStructureSB);
083                            }
084                            else if (line.startsWith("insert into JournalTemplate (")) {
085                                    _convertToOracleCSV(line, journalTemplateSB);
086                            }
087                    }
088    
089                    unsyncBufferedReader.close();
090    
091                    if (imageSB.length() > 0) {
092                            FileUtil.write(
093                                    sqlDir + "/" + fileName + "/" + fileName + "-oracle-image.csv",
094                                    imageSB.toString());
095                    }
096    
097                    if (journalArticleSB.length() > 0) {
098                            FileUtil.write(
099                                    sqlDir + "/" + fileName + "/" + fileName +
100                                            "-oracle-journalarticle.csv",
101                                    journalArticleSB.toString());
102                    }
103    
104                    if (journalStructureSB.length() > 0) {
105                            FileUtil.write(
106                                    sqlDir + "/" + fileName + "/" + fileName +
107                                            "-oracle-journalstructure.csv",
108                                    journalStructureSB.toString());
109                    }
110    
111                    if (journalTemplateSB.length() > 0) {
112                            FileUtil.write(
113                                    sqlDir + "/" + fileName + "/" + fileName +
114                                            "-oracle-journaltemplate.csv",
115                                    journalTemplateSB.toString());
116                    }
117    
118                    oracle = _postBuildSQL(oracle);
119    
120                    FileUtil.write(
121                            sqlDir + "/" + fileName + "/" + fileName + "-oracle.sql", oracle);
122            }
123    
124            public List<Index> getIndexes() throws SQLException {
125                    List<Index> indexes = new ArrayList<Index>();
126    
127                    Connection con = null;
128                    PreparedStatement ps = null;
129                    ResultSet rs = null;
130    
131                    try {
132                            con = DataAccess.getConnection();
133    
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(con, ps, rs);
162                    }
163    
164                    return indexes;
165            }
166    
167            public boolean isSupportsInlineDistinct() {
168                    return _SUPPORTS_INLINE_DISTINCT;
169            }
170    
171            protected OracleDB() {
172                    super(TYPE_ORACLE);
173            }
174    
175            protected String buildCreateFileContent(
176                            String sqlDir, String databaseName, int population)
177                    throws IOException {
178    
179                    String suffix = getSuffix(population);
180    
181                    StringBundler sb = new StringBundler(13);
182    
183                    sb.append("drop user &1 cascade;\n");
184                    sb.append("create user &1 identified by &2;\n");
185                    sb.append("grant connect,resource to &1;\n");
186                    sb.append("connect &1/&2;\n");
187                    sb.append("set define off;\n");
188                    sb.append("\n");
189                    sb.append(
190                            readFile(
191                                    sqlDir + "/portal" + suffix + "/portal" + suffix +
192                                            "-oracle.sql"));
193                    sb.append("\n\n");
194                    sb.append(readFile(sqlDir + "/indexes/indexes-oracle.sql"));
195                    sb.append("\n\n");
196                    sb.append(readFile(sqlDir + "/sequences/sequences-oracle.sql"));
197                    sb.append("\n");
198                    sb.append("quit");
199    
200                    return sb.toString();
201            }
202    
203            protected String getServerName() {
204                    return "oracle";
205            }
206    
207            protected String[] getTemplate() {
208                    return _ORACLE;
209            }
210    
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()) * 4;
221    
222                            if (size > 4000) {
223                                    size = 4000;
224                            }
225    
226                            matcher.appendReplacement(sb, "VARCHAR(" + size + ")");
227                    }
228    
229                    matcher.appendTail(sb);
230    
231                    template = sb.toString();
232    
233                    return super.replaceTemplate(template, actual);
234            }
235    
236            protected String reword(String data) throws IOException {
237                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
238                            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.indexOf(DROP_INDEX) != -1) {
261                                    String[] tokens = StringUtil.split(line, " ");
262    
263                                    line = StringUtil.replace(
264                                            "drop index @index@;", "@index@", tokens[2]);
265                            }
266    
267                            sb.append(line);
268                            sb.append("\n");
269                    }
270    
271                    unsyncBufferedReader.close();
272    
273                    return sb.toString();
274            }
275    
276            private void _convertToOracleCSV(String line, StringBundler sb) {
277                    int x = line.indexOf("values (");
278                    int y = line.lastIndexOf(");");
279    
280                    line = line.substring(x + 8, y);
281    
282                    line = StringUtil.replace(line, "sysdate, ", "20050101, ");
283    
284                    sb.append(line);
285                    sb.append("\n");
286            }
287    
288            private String _preBuildSQL(String template) throws IOException {
289                    template = convertTimestamp(template);
290                    template = replaceTemplate(template, getTemplate());
291    
292                    template = reword(template);
293                    template = StringUtil.replace(
294                            template,
295                            new String[] {"\\\\", "\\'", "\\\""},
296                            new String[] {"\\", "''", "\""});
297    
298                    return template;
299            }
300    
301            private String _postBuildSQL(String template) throws IOException {
302                    template = removeLongInserts(template);
303                    template = StringUtil.replace(template, "\\n", "'||CHR(10)||'");
304    
305                    return template;
306            }
307    
308            private static String[] _ORACLE = {
309                    "--", "1", "0",
310                    "to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')", "sysdate",
311                    " blob", " number(1, 0)", " timestamp",
312                    " number(30,20)", " number(30,0)", " number(30,0)",
313                    " varchar2(4000)", " clob", " varchar2",
314                    "", "commit"
315            };
316    
317            private static final boolean _SUPPORTS_INLINE_DISTINCT = false;
318    
319            private static OracleDB _instance = new OracleDB();
320    
321            private static Pattern _varcharPattern = Pattern.compile(
322                    "VARCHAR(\\(\\d+\\))");
323    
324    }