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