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.verify;
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.DBFactoryUtil;
020    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
021    import com.liferay.portal.kernel.upgrade.UpgradeException;
022    import com.liferay.portal.kernel.util.StringBundler;
023    
024    import java.sql.Connection;
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 VerifyPostgreSQL extends VerifyProcess {
035    
036            protected void deleteOrphanedLargeObjects(
037                            Map<String, String> oidColumnNames)
038                    throws Exception {
039    
040                    Connection con = null;
041                    PreparedStatement ps = null;
042    
043                    try {
044                            con = DataAccess.getUpgradeOptimizedConnection();
045    
046                            StringBundler sb = new StringBundler();
047    
048                            sb.append("select lo_unlink(l.oid) from pg_largeobject_metadata ");
049                            sb.append("l where ");
050    
051                            int i = 1;
052    
053                            for (Map.Entry<String, String> column : oidColumnNames.entrySet()) {
054                                    String tableName = column.getKey();
055                                    String columnName = column.getValue();
056    
057                                    sb.append("(not exists (select 1 from ");
058                                    sb.append(tableName);
059                                    sb.append(" t where t.");
060                                    sb.append(columnName);
061                                    sb.append(" = l.oid))");
062    
063                                    if (i < oidColumnNames.size()) {
064                                            sb.append(" and ");
065                                    }
066    
067                                    i++;
068                            }
069    
070                            ps = con.prepareStatement(sb.toString());
071    
072                            ps.executeQuery();
073                    }
074                    finally {
075                            DataAccess.cleanUp(con, ps);
076                    }
077            }
078    
079            @Override
080            protected void doVerify() throws Exception {
081                    DB db = DBFactoryUtil.getDB();
082    
083                    String dbType = db.getType();
084    
085                    if (!dbType.equals(DB.TYPE_POSTGRESQL)) {
086                            return;
087                    }
088    
089                    Map<String, String> oidColumnNames = getOidColumnNames();
090    
091                    verifyRules(oidColumnNames);
092    
093                    deleteOrphanedLargeObjects(oidColumnNames);
094            }
095    
096            protected String getCurrentSchema() throws Exception {
097                    String schema = null;
098    
099                    Connection con = null;
100                    PreparedStatement ps = null;
101                    ResultSet rs = null;
102    
103                    try {
104                            con = DataAccess.getUpgradeOptimizedConnection();
105    
106                            ps = con.prepareStatement("select current_schema();");
107    
108                            rs = ps.executeQuery();
109    
110                            if (rs.next()) {
111                                    schema = (String)rs.getObject("current_schema");
112                            }
113                    }
114                    finally {
115                            DataAccess.cleanUp(con, ps, rs);
116                    }
117    
118                    return schema;
119            }
120    
121            protected Map<String, String> getOidColumnNames() throws Exception {
122                    Connection con = null;
123                    PreparedStatement ps = null;
124                    ResultSet rs = null;
125    
126                    Map<String, String> oidColumnNames = new HashMap<String, String>();
127    
128                    StringBundler sb = new StringBundler(4);
129    
130                    sb.append("select table_name, column_name from ");
131                    sb.append("information_schema.columns where table_schema='");
132    
133                    String schema = getCurrentSchema();
134    
135                    if (schema == null) {
136                            throw new UpgradeException("Unable to get current schema");
137                    }
138    
139                    try {
140                            con = DataAccess.getUpgradeOptimizedConnection();
141    
142                            sb.append(schema);
143                            sb.append("' and data_type='oid';");
144    
145                            ps = con.prepareStatement(sb.toString());
146    
147                            rs = ps.executeQuery();
148    
149                            while (rs.next()) {
150                                    String tableName = (String)rs.getObject("table_name");
151                                    String columnName = (String)rs.getObject("column_name");
152    
153                                    oidColumnNames.put(tableName, columnName);
154                            }
155                    }
156                    finally {
157                            DataAccess.cleanUp(con, ps, rs);
158                    }
159    
160                    return oidColumnNames;
161            }
162    
163            protected void verifyRules(Map<String, String> oidColumnNames)
164                    throws Exception {
165    
166                    Connection con = null;
167                    PreparedStatement ps = null;
168    
169                    for (Map.Entry<String, String> column : oidColumnNames.entrySet()) {
170                            String tableName = column.getKey();
171                            String columnName = column.getValue();
172    
173                            try {
174                                    con = DataAccess.getUpgradeOptimizedConnection();
175    
176                                    ps = con.prepareStatement(
177                                            PostgreSQLDB.getCreateRulesSQL(tableName, columnName));
178    
179                                    ps.executeUpdate();
180                            }
181                            finally {
182                                    DataAccess.cleanUp(con, ps);
183                            }
184                    }
185            }
186    
187    }