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