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