001    /**
002     * Copyright (c) 2000-2013 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.upgrade.util;
016    
017    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
019    import com.liferay.portal.kernel.io.unsync.UnsyncBufferedWriter;
020    import com.liferay.portal.kernel.log.Log;
021    import com.liferay.portal.kernel.log.LogFactoryUtil;
022    import com.liferay.portal.kernel.upgrade.StagnantRowException;
023    import com.liferay.portal.kernel.upgrade.UpgradeException;
024    import com.liferay.portal.kernel.util.Base64;
025    import com.liferay.portal.kernel.util.DateUtil;
026    import com.liferay.portal.kernel.util.FileUtil;
027    import com.liferay.portal.kernel.util.GetterUtil;
028    import com.liferay.portal.kernel.util.StringBundler;
029    import com.liferay.portal.kernel.util.StringPool;
030    import com.liferay.portal.kernel.util.StringUtil;
031    import com.liferay.portal.kernel.util.SystemProperties;
032    import com.liferay.portal.kernel.util.Validator;
033    import com.liferay.portal.kernel.uuid.PortalUUIDUtil;
034    import com.liferay.portal.upgrade.AutoBatchPreparedStatementUtil;
035    
036    import java.io.FileReader;
037    import java.io.FileWriter;
038    
039    import java.sql.Clob;
040    import java.sql.Connection;
041    import java.sql.PreparedStatement;
042    import java.sql.ResultSet;
043    import java.sql.SQLException;
044    import java.sql.Timestamp;
045    import java.sql.Types;
046    
047    import java.text.DateFormat;
048    
049    import java.util.Date;
050    
051    import org.apache.commons.lang.time.StopWatch;
052    
053    /**
054     * @author Alexander Chow
055     * @author Brian Wing Shun Chan
056     * @author Raymond Aug??
057     */
058    public class Table {
059    
060            public Table(String tableName) {
061                    _tableName = tableName;
062            }
063    
064            public Table(String tableName, Object[][] columns) {
065                    _tableName = tableName;
066    
067                    setColumns(columns);
068            }
069    
070            public void appendColumn(StringBuilder sb, Object value, boolean last)
071                    throws Exception {
072    
073                    if (value == null) {
074                            throw new UpgradeException(
075                                    "Nulls should never be inserted into the database. " +
076                                            "Attempted to append column to " + sb.toString() + ".");
077                    }
078                    else if (value instanceof byte[]) {
079                            sb.append(Base64.encode((byte[])value));
080                    }
081                    else if (value instanceof Clob || value instanceof String) {
082                            value = StringUtil.replace(
083                                    (String)value, _SAFE_TABLE_CHARS[0], _SAFE_TABLE_CHARS[1]);
084    
085                            sb.append(value);
086                    }
087                    else if (value instanceof Date) {
088                            DateFormat df = DateUtil.getISOFormat();
089    
090                            sb.append(df.format(value));
091                    }
092                    else {
093                            sb.append(value);
094                    }
095    
096                    sb.append(StringPool.COMMA);
097    
098                    if (last) {
099                            sb.append(StringPool.NEW_LINE);
100                    }
101            }
102    
103            public void appendColumn(
104                            StringBuilder sb, ResultSet rs, String name, Integer type,
105                            boolean last)
106                    throws Exception {
107    
108                    Object value = null;
109    
110                    try {
111                            value = getValue(rs, name, type);
112                    }
113                    catch (SQLException sqle) {
114                            if (name.equals("uuid_")) {
115                                    sb.append(PortalUUIDUtil.generate());
116                            }
117    
118                            sb.append(StringPool.COMMA);
119    
120                            if (last) {
121                                    sb.append(StringPool.NEW_LINE);
122                            }
123    
124                            return;
125                    }
126    
127                    appendColumn(sb, value, last);
128            }
129    
130            public String generateTempFile() throws Exception {
131                    Connection con = DataAccess.getUpgradeOptimizedConnection();
132    
133                    try {
134                            return generateTempFile(con);
135                    }
136                    finally {
137                            DataAccess.cleanUp(con);
138                    }
139            }
140    
141            public String generateTempFile(Connection con) throws Exception {
142                    PreparedStatement ps = null;
143                    ResultSet rs = null;
144    
145                    boolean empty = true;
146    
147                    String tempFileName =
148                            SystemProperties.get(SystemProperties.TMP_DIR) + "/temp-db-" +
149                                    _tableName + "-" + System.currentTimeMillis();
150    
151                    StopWatch stopWatch = new StopWatch();
152    
153                    stopWatch.start();
154    
155                    if (_log.isInfoEnabled()) {
156                            _log.info(
157                                    "Starting backup of " + _tableName + " to " + tempFileName);
158                    }
159    
160                    UnsyncBufferedWriter unsyncBufferedWriter = new UnsyncBufferedWriter(
161                            new FileWriter(tempFileName));
162    
163                    try {
164                            ps = getSelectPreparedStatement(con);
165    
166                            rs = ps.executeQuery();
167    
168                            while (rs.next()) {
169                                    String data = null;
170    
171                                    try {
172                                            data = getExportedData(rs);
173    
174                                            unsyncBufferedWriter.write(data);
175    
176                                            _totalRows++;
177    
178                                            empty = false;
179                                    }
180                                    catch (StagnantRowException sre) {
181                                            if (_log.isWarnEnabled()) {
182                                                    _log.warn(
183                                                            "Skipping stagnant data in " + _tableName + ": " +
184                                                                    sre.getMessage());
185                                            }
186                                    }
187                            }
188    
189                            if (_log.isInfoEnabled()) {
190                                    _log.info(
191                                            "Finished backup of " + _tableName + " to " +
192                                                    tempFileName + " in " + stopWatch.getTime() + " ms");
193                            }
194                    }
195                    catch (Exception e) {
196                            FileUtil.delete(tempFileName);
197    
198                            throw e;
199                    }
200                    finally {
201                            DataAccess.cleanUp(null, ps, rs);
202    
203                            unsyncBufferedWriter.close();
204                    }
205    
206                    if (!empty) {
207                            return tempFileName;
208                    }
209    
210                    FileUtil.delete(tempFileName);
211    
212                    return null;
213            }
214    
215            public Object[][] getColumns() {
216                    return _columns;
217            }
218    
219            public String getCreateSQL() throws Exception {
220                    return _createSQL;
221            }
222    
223            public String getDeleteSQL() throws Exception {
224                    return "DELETE FROM " + _tableName;
225            }
226    
227            public String getExportedData(ResultSet rs) throws Exception {
228                    StringBuilder sb = new StringBuilder();
229    
230                    Object[][] columns = getColumns();
231    
232                    for (int i = 0; i < columns.length; i++) {
233                            boolean last = false;
234    
235                            if ((i + 1) == columns.length) {
236                                    last = true;
237                            }
238    
239                            appendColumn(
240                                    sb, rs, (String)columns[i][0], (Integer)columns[i][1], last);
241                    }
242    
243                    return sb.toString();
244            }
245    
246            public String getInsertSQL() throws Exception {
247                    String sql = "INSERT INTO " + getInsertTableName() + " (";
248    
249                    for (int i = 0; i < _order.length; i++) {
250                            int pos = _order[i];
251    
252                            sql += _columns[pos][0];
253    
254                            if ((i + 1) < _columns.length) {
255                                    sql += ", ";
256                            }
257                            else {
258                                    sql += ") VALUES (";
259                            }
260                    }
261    
262                    for (int i = 0; i < _columns.length; i++) {
263                            sql += "?";
264    
265                            if ((i + 1) < _columns.length) {
266                                    sql += ", ";
267                            }
268                            else {
269                                    sql += ")";
270                            }
271                    }
272    
273                    return sql;
274            }
275    
276            public String getInsertTableName() throws Exception {
277                    String createSQL = getCreateSQL();
278    
279                    if (Validator.isNotNull(createSQL)) {
280                            String createSQLLowerCase = StringUtil.toLowerCase(createSQL);
281    
282                            int x = createSQLLowerCase.indexOf("create table ");
283    
284                            if (x == -1) {
285                                    return _tableName;
286                            }
287    
288                            x += 13;
289    
290                            int y = createSQL.indexOf(" ", x);
291    
292                            return createSQL.substring(x, y).trim();
293                    }
294                    else {
295                            return _tableName;
296                    }
297            }
298    
299            public int[] getOrder() {
300                    return _order;
301            }
302    
303            public PreparedStatement getSelectPreparedStatement(Connection con)
304                    throws Exception {
305    
306                    return con.prepareStatement(getSelectSQL());
307            }
308    
309            public String getSelectSQL() throws Exception {
310                    if (_selectSQL == null) {
311                            /*String sql = "select ";
312    
313                            for (int i = 0; i < _columns.length; i++) {
314                                    sql += _columns[i][0];
315    
316                                    if ((i + 1) < _columns.length) {
317                                            sql += ", ";
318                                    }
319                                    else {
320                                            sql += " from " + _tableName;
321                                    }
322                            }
323    
324                            return sql;*/
325    
326                            return "select * from " + _tableName;
327                    }
328                    else {
329                            return _selectSQL;
330                    }
331            }
332    
333            public String getTableName() {
334                    return _tableName;
335            }
336    
337            public long getTotalRows() {
338                    return _totalRows;
339            }
340    
341            public Object getValue(ResultSet rs, String name, Integer type)
342                    throws Exception {
343    
344                    Object value = null;
345    
346                    int t = type.intValue();
347    
348                    if (t == Types.BIGINT) {
349                            try {
350                                    value = GetterUtil.getLong(rs.getLong(name));
351                            }
352                            catch (SQLException sqle) {
353                                    value = GetterUtil.getLong(rs.getString(name));
354                            }
355                    }
356                    else if (t == Types.BIT) {
357                            value = GetterUtil.getBoolean(rs.getBoolean(name));
358                    }
359                    else if (t == Types.BLOB) {
360                            value = rs.getBytes(name);
361    
362                            if (value == null) {
363                                    value = new byte[0];
364                            }
365                    }
366                    else if (t == Types.BOOLEAN) {
367                            value = GetterUtil.getBoolean(rs.getBoolean(name));
368                    }
369                    else if (t == Types.CLOB) {
370                            try {
371                                    Clob clob = rs.getClob(name);
372    
373                                    if (clob == null) {
374                                            value = StringPool.BLANK;
375                                    }
376                                    else {
377                                            UnsyncBufferedReader unsyncBufferedReader =
378                                                    new UnsyncBufferedReader(clob.getCharacterStream());
379    
380                                            StringBundler sb = new StringBundler();
381    
382                                            String line = null;
383    
384                                            while ((line = unsyncBufferedReader.readLine()) != null) {
385                                                    if (sb.length() != 0) {
386                                                            sb.append(_SAFE_TABLE_NEWLINE_CHARACTER);
387                                                    }
388    
389                                                    sb.append(line);
390                                            }
391    
392                                            value = sb.toString();
393                                    }
394                            }
395                            catch (Exception e) {
396    
397                                    // If the database doesn't allow CLOB types for the column
398                                    // value, then try retrieving it as a String
399    
400                                    value = GetterUtil.getString(rs.getString(name));
401                            }
402                    }
403                    else if (t == Types.DOUBLE) {
404                            value = GetterUtil.getDouble(rs.getDouble(name));
405                    }
406                    else if (t == Types.FLOAT) {
407                            value = GetterUtil.getFloat(rs.getFloat(name));
408                    }
409                    else if (t == Types.INTEGER) {
410                            value = GetterUtil.getInteger(rs.getInt(name));
411                    }
412                    else if (t == Types.NUMERIC) {
413                            value = GetterUtil.getLong(rs.getLong(name));
414                    }
415                    else if (t == Types.SMALLINT) {
416                            value = GetterUtil.getShort(rs.getShort(name));
417                    }
418                    else if (t == Types.TIMESTAMP) {
419                            try {
420                                    value = rs.getTimestamp(name);
421                            }
422                            catch (Exception e) {
423                            }
424    
425                            if (value == null) {
426                                    value = StringPool.NULL;
427                            }
428                    }
429                    else if (t == Types.VARCHAR) {
430                            value = GetterUtil.getString(rs.getString(name));
431                    }
432                    else {
433                            throw new UpgradeException(
434                                    "Upgrade code using unsupported class type " + type);
435                    }
436    
437                    return value;
438            }
439    
440            public void populateTable(String tempFileName) throws Exception {
441                    Connection con = DataAccess.getUpgradeOptimizedConnection();
442    
443                    try {
444                            populateTable(tempFileName, con);
445                    }
446                    finally {
447                            DataAccess.cleanUp(con);
448                    }
449            }
450    
451            public void populateTable(String tempFileName, Connection con)
452                    throws Exception {
453    
454                    PreparedStatement ps = AutoBatchPreparedStatementUtil.autoBatch(
455                            con.prepareStatement(getInsertSQL()));
456    
457                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
458                            new FileReader(tempFileName));
459    
460                    String line = null;
461    
462                    try {
463                            while ((line = unsyncBufferedReader.readLine()) != null) {
464                                    String[] values = StringUtil.split(line);
465    
466                                    Object[][] columns = getColumns();
467    
468                                    if (values.length != columns.length) {
469                                            throw new UpgradeException(
470                                                    "Column lengths differ between temp file and schema. " +
471                                                            "Attempted to insert row " + line + ".");
472                                    }
473    
474                                    int[] order = getOrder();
475    
476                                    for (int i = 0; i < order.length; i++) {
477                                            int pos = order[i];
478    
479                                            setColumn(ps, i, (Integer)columns[pos][1], values[pos]);
480                                    }
481    
482                                    ps.addBatch();
483                            }
484    
485                            ps.executeBatch();
486                    }
487                    finally {
488                            DataAccess.cleanUp(null, ps);
489    
490                            unsyncBufferedReader.close();
491                    }
492    
493                    if (_log.isDebugEnabled()) {
494                            _log.debug(getTableName() + " table populated with data");
495                    }
496            }
497    
498            public void populateTableRows(PreparedStatement ps, boolean batch)
499                    throws Exception {
500    
501                    if (_log.isDebugEnabled()) {
502                            _log.debug("Updating rows for " + getTableName());
503                    }
504    
505                    if (batch) {
506                            ps.executeBatch();
507                    }
508                    else {
509                            ps.executeUpdate();
510                    }
511    
512                    ps.close();
513            }
514    
515            public void setColumn(
516                            PreparedStatement ps, int index, Integer type, String value)
517                    throws Exception {
518    
519                    int t = type.intValue();
520    
521                    int paramIndex = index + 1;
522    
523                    if (t == Types.BIGINT) {
524                            ps.setLong(paramIndex, GetterUtil.getLong(value));
525                    }
526                    else if (t == Types.BLOB) {
527                            ps.setBytes(paramIndex, Base64.decode(value));
528                    }
529                    else if (t == Types.BOOLEAN) {
530                            ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
531                    }
532                    else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
533                            value = StringUtil.replace(
534                                    value, _SAFE_TABLE_CHARS[1], _SAFE_TABLE_CHARS[0]);
535    
536                            ps.setString(paramIndex, value);
537                    }
538                    else if (t == Types.DOUBLE) {
539                            ps.setDouble(paramIndex, GetterUtil.getDouble(value));
540                    }
541                    else if (t == Types.FLOAT) {
542                            ps.setFloat(paramIndex, GetterUtil.getFloat(value));
543                    }
544                    else if (t == Types.INTEGER) {
545                            ps.setInt(paramIndex, GetterUtil.getInteger(value));
546                    }
547                    else if (t == Types.SMALLINT) {
548                            ps.setShort(paramIndex, GetterUtil.getShort(value));
549                    }
550                    else if (t == Types.TIMESTAMP) {
551                            if (StringPool.NULL.equals(value)) {
552                                    ps.setTimestamp(paramIndex, null);
553                            }
554                            else {
555                                    DateFormat df = DateUtil.getISOFormat();
556    
557                                    ps.setTimestamp(
558                                            paramIndex, new Timestamp(df.parse(value).getTime()));
559                            }
560                    }
561                    else {
562                            throw new UpgradeException(
563                                    "Upgrade code using unsupported class type " + type);
564                    }
565            }
566    
567            public void setColumns(Object[][] columns) {
568                    _columns = columns;
569    
570                    // LEP-7331
571    
572                    _order = new int[_columns.length];
573    
574                    int clobCount = 0;
575    
576                    for (int i = 0; i < _columns.length; ++i) {
577                            Integer type = (Integer)columns[i][1];
578    
579                            if (type.intValue() == Types.CLOB) {
580                                    clobCount++;
581    
582                                    int pos = _columns.length - clobCount;
583    
584                                    _order[pos] = i;
585                            }
586                            else {
587                                    int pos = i - clobCount;
588    
589                                    _order[pos] = i;
590                            }
591                    }
592            }
593    
594            public void setCreateSQL(String createSQL) throws Exception {
595                    _createSQL = createSQL;
596            }
597    
598            public void setSelectSQL(String selectSQL) throws Exception {
599                    _selectSQL = selectSQL;
600            }
601    
602            private static final String[][] _SAFE_TABLE_CHARS = {
603                    {StringPool.COMMA, StringPool.NEW_LINE, StringPool.RETURN},
604                    {
605                            Table._SAFE_TABLE_COMMA_CHARACTER,
606                            Table._SAFE_TABLE_NEWLINE_CHARACTER,
607                            Table._SAFE_TABLE_RETURN_CHARACTER
608                    }
609            };
610    
611            private static final String _SAFE_TABLE_COMMA_CHARACTER =
612                    "_SAFE_TABLE_COMMA_CHARACTER_";
613    
614            private static final String _SAFE_TABLE_NEWLINE_CHARACTER =
615                    "_SAFE_TABLE_NEWLINE_CHARACTER_";
616    
617            private static final String _SAFE_TABLE_RETURN_CHARACTER =
618                    "_SAFE_TABLE_RETURN_CHARACTER_";
619    
620            private static Log _log = LogFactoryUtil.getLog(Table.class);
621    
622            private Object[][] _columns;
623            private String _createSQL;
624            private int[] _order;
625            private String _selectSQL;
626            private String _tableName;
627            private long _totalRows;
628    
629    }