001
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
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 }