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.upgrade.v7_0_0;
016    
017    import com.liferay.portal.dao.db.PostgreSQLDB;
018    import com.liferay.portal.kernel.dao.db.DB;
019    import com.liferay.portal.kernel.dao.db.DBManagerUtil;
020    import com.liferay.portal.kernel.dao.db.DBType;
021    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
022    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
023    import com.liferay.portal.kernel.util.StringBundler;
024    
025    import java.sql.PreparedStatement;
026    import java.sql.ResultSet;
027    
028    import java.util.HashMap;
029    import java.util.Map;
030    
031    /**
032     * @author Michael Bowerman
033     */
034    public class UpgradePostgreSQL extends UpgradeProcess {
035    
036            @Override
037            protected void doUpgrade() throws Exception {
038                    DB db = DBManagerUtil.getDB();
039    
040                    if (db.getDBType() != DBType.POSTGRESQL) {
041                            return;
042                    }
043    
044                    Map<String, String> oidColumnNames = getOidColumnNames();
045    
046                    updatePostgreSQLRules(oidColumnNames);
047    
048                    updateOrphanedLargeObjects(oidColumnNames);
049            }
050    
051            protected HashMap<String, String> getOidColumnNames() throws Exception {
052                    HashMap<String, String> columnsWithOids = new HashMap<>();
053    
054                    PreparedStatement ps = null;
055                    ResultSet rs = null;
056    
057                    StringBundler sb = new StringBundler(3);
058    
059                    sb.append("select table_name, column_name from ");
060                    sb.append("information_schema.columns where table_schema='public' ");
061                    sb.append("and data_type='oid';");
062    
063                    try {
064                            ps = connection.prepareStatement(sb.toString());
065    
066                            rs = ps.executeQuery();
067    
068                            while (rs.next()) {
069                                    String tableName = (String)rs.getObject("table_name");
070                                    String columnName = (String)rs.getObject("column_name");
071    
072                                    columnsWithOids.put(tableName, columnName);
073                            }
074    
075                            return columnsWithOids;
076                    }
077    
078                    finally {
079                            DataAccess.cleanUp(ps, rs);
080                    }
081            }
082    
083            protected void updateOrphanedLargeObjects(
084                            Map<String, String> oidColumnNames)
085                    throws Exception {
086    
087                    PreparedStatement ps = null;
088    
089                    StringBundler sb = new StringBundler();
090    
091                    sb.append("select lo_unlink(l.oid) from pg_largeobject_metadata l ");
092                    sb.append("where ");
093    
094                    int i = 1;
095    
096                    for (Map.Entry<String, String> column : oidColumnNames.entrySet()) {
097                            String tableName = column.getKey();
098                            String columnName = column.getValue();
099    
100                            sb.append("(not exists (select 1 from ");
101                            sb.append(tableName);
102                            sb.append(" t where t.");
103                            sb.append(columnName);
104                            sb.append(" = l.oid))");
105    
106                            if (i < oidColumnNames.size()) {
107                                    sb.append(" and ");
108                            }
109    
110                            i++;
111                    }
112    
113                    try {
114                            ps = connection.prepareStatement(sb.toString());
115    
116                            ps.execute();
117                    }
118                    finally {
119                            DataAccess.cleanUp(ps);
120                    }
121            }
122    
123            protected void updatePostgreSQLRules(Map<String, String> oidColumnNames)
124                    throws Exception {
125    
126                    for (Map.Entry<String, String> entry : oidColumnNames.entrySet()) {
127                            PreparedStatement ps = null;
128    
129                            String tableName = entry.getKey();
130                            String columnName = entry.getValue();
131    
132                            try {
133                                    ps = connection.prepareStatement(
134                                            PostgreSQLDB.getCreateRulesSQL(tableName, columnName));
135    
136                                    ps.executeUpdate();
137                            }
138                            finally {
139                                    DataAccess.cleanUp(ps);
140                            }
141                    }
142            }
143    
144    }