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