001/* 002 * (C) Copyright 2006-2011 Nuxeo SA (http://nuxeo.com/) and others. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * http://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 * 016 * Contributors: 017 * Florent Guillaume 018 */ 019 020package org.nuxeo.ecm.core.storage.sql.db; 021 022import java.io.Serializable; 023import java.sql.Connection; 024import java.sql.DatabaseMetaData; 025import java.sql.PreparedStatement; 026import java.sql.ResultSet; 027import java.sql.SQLException; 028import java.sql.Types; 029import java.util.HashSet; 030import java.util.LinkedList; 031import java.util.List; 032import java.util.Set; 033 034import org.h2.tools.SimpleResultSet; 035 036/** 037 * Functions used as stored procedures for H2. 038 * 039 * @author Florent Guillaume 040 */ 041public class H2Functions extends EmbeddedFunctions { 042 043 // for debug 044 private static boolean isLogEnabled() { 045 return false; 046 // return log.isTraceEnabled(); 047 } 048 049 // for debug 050 private static void logDebug(String message, Object... args) { 051 // log.trace("SQL: " + String.format(message.replace("?", "%s"), args)); 052 } 053 054 public static boolean isInTreeString(Connection conn, String id, String baseId) throws SQLException { 055 return isInTree(conn, id, baseId); 056 } 057 058 /** 059 * @deprecated since 11.3, unused 060 */ 061 @Deprecated 062 public static boolean isInTreeLong(Connection conn, Long id, Long baseId) throws SQLException { 063 return isInTree(conn, id, baseId); 064 } 065 066 /** 067 * Compatibility signature without {@code disableVersionACL} or {@code disableReadVersionPermission} parameter 068 */ 069 public static boolean isAccessAllowedString(Connection conn, String id, String principals, String permissions) 070 throws SQLException { 071 return isAccessAllowed(conn, id, split(principals), split(permissions)); 072 } 073 074 /** 075 * @since 11.3 076 */ 077 public static boolean isAccessAllowed2(Connection conn, String id, String principals, String permissions, 078 boolean disableVersionACL, boolean disableReadVersionPermission) throws SQLException { 079 return isAccessAllowed(conn, id, split(principals), split(permissions), disableVersionACL, 080 disableReadVersionPermission); 081 } 082 083 /** 084 * @deprecated since 11.3, unused 085 */ 086 @Deprecated 087 public static boolean isAccessAllowedLong(Connection conn, Long id, String principals, String permissions) 088 throws SQLException { 089 return isAccessAllowed(conn, id, split(principals), split(permissions)); 090 } 091 092 /** 093 * Adds an invalidation from this cluster node to the invalidations list. 094 */ 095 @SuppressWarnings("boxing") 096 public static void clusterInvalidateString(Connection conn, long nodeId, String id, String fragments, int kind) 097 throws SQLException { 098 // find other node ids 099 String sql = "SELECT \"NODEID\" FROM \"CLUSTER_NODES\" WHERE \"NODEID\" <> ?"; 100 if (isLogEnabled()) { 101 logDebug(sql, nodeId); 102 } 103 List<Long> nodeIds = new LinkedList<>(); 104 try (PreparedStatement ps = conn.prepareStatement(sql)) { 105 ps.setLong(1, nodeId); 106 try (ResultSet rs = ps.executeQuery()) { 107 while (rs.next()) { 108 nodeIds.add(rs.getLong(1)); 109 } 110 } 111 } 112 if (isLogEnabled()) { 113 logDebug(" -> " + nodeIds); 114 } 115 // invalidate 116 sql = "INSERT INTO \"CLUSTER_INVALS\" " + "(\"NODEID\", \"ID\", \"FRAGMENTS\", \"KIND\") " 117 + "VALUES (?, ?, ?, ?)"; 118 try (PreparedStatement ps = conn.prepareStatement(sql)) { 119 for (Long nid : nodeIds) { 120 if (isLogEnabled()) { 121 logDebug(sql, nid, id, fragments, kind); 122 } 123 ps.setLong(1, nid); 124 ps.setObject(2, id); 125 ps.setString(3, fragments); 126 ps.setInt(4, kind); 127 ps.execute(); 128 } 129 } 130 } 131 132 /** 133 * Gets the invalidations for this cluster node. 134 * 135 * @return a result set with columns id, fragments, kind 136 */ 137 public static ResultSet getClusterInvalidationsString(Connection conn, long nodeId) throws SQLException { 138 DatabaseMetaData meta = conn.getMetaData(); 139 SimpleResultSet result = new SimpleResultSet(); 140 result.addColumn("ID", Types.VARCHAR, 0, 0); // String id 141 result.addColumn("FRAGMENTS", Types.VARCHAR, 0, 0); 142 result.addColumn("KIND", Types.INTEGER, 0, 0); 143 if (meta.getURL().startsWith("jdbc:columnlist:")) { 144 // this is just to query the result set columns 145 return result; 146 } 147 148 String sql = "SELECT \"ID\", \"FRAGMENTS\", \"KIND\" FROM \"CLUSTER_INVALS\" " 149 + "WHERE \"NODEID\" = ?"; 150 if (isLogEnabled()) { 151 logDebug(sql, nodeId); 152 } 153 try (PreparedStatement ps = conn.prepareStatement(sql)) { 154 ps.setLong(1, nodeId); 155 List<Serializable> debugValues = null; 156 if (isLogEnabled()) { 157 debugValues = new LinkedList<>(); 158 } 159 try (ResultSet rs = ps.executeQuery()) { 160 while (rs.next()) { 161 String id = rs.getString(1); 162 String fragments = rs.getString(2); 163 long kind = rs.getLong(3); 164 result.addRow(new Object[] { id, fragments, Long.valueOf(kind) }); 165 if (debugValues != null) { 166 debugValues.add(id + ',' + fragments + ',' + kind); 167 } 168 } 169 } 170 if (debugValues != null) { 171 logDebug(" -> " + debugValues); 172 } 173 174 // remove processed invalidations 175 sql = "DELETE FROM \"CLUSTER_INVALS\" WHERE \"NODEID\" = ?"; 176 if (isLogEnabled()) { 177 logDebug(sql); 178 } 179 } 180 try (PreparedStatement ps = conn.prepareStatement(sql)) { 181 ps.setLong(1, nodeId); 182 ps.execute(); 183 } 184 // return invalidations 185 return result; 186 } 187 188 public static ResultSet upgradeVersions(Connection conn) throws SQLException { 189 String sql = "SELECT v.id, v.versionableid, h.majorversion, h.minorversion" 190 + " FROM versions v JOIN hierarchy h ON v.id = h.id" 191 + " ORDER BY v.versionableid, v.created DESC"; 192 try (PreparedStatement ps1 = conn.prepareStatement(sql)) { 193 try (ResultSet rs = ps1.executeQuery()) { 194 String series = null; 195 boolean isLatest = false; 196 boolean isLatestMajor = false; 197 while (rs.next()) { 198 String id = rs.getString("id"); 199 String vid = rs.getString("versionableid"); 200 long maj = rs.getLong("majorversion"); 201 long min = rs.getLong("minorversion"); 202 if (vid == null || !vid.equals(series)) { 203 // restart 204 isLatest = true; 205 isLatestMajor = true; 206 series = vid; 207 } 208 boolean isMajor = min == 0; 209 try (PreparedStatement ps2 = conn.prepareStatement( 210 "UPDATE versions SET label = ?, islatest = ?, islatestmajor = ?" + " WHERE id = ?")) { 211 ps2.setString(1, maj + "." + min); 212 ps2.setBoolean(2, isLatest); 213 ps2.setBoolean(3, isMajor && isLatestMajor); 214 ps2.setString(4, id); 215 ps2.executeUpdate(); 216 } 217 // next 218 isLatest = false; 219 if (isMajor) { 220 isLatestMajor = false; 221 } 222 } 223 } 224 } 225 226 return new SimpleResultSet(); 227 } 228 229 public static ResultSet upgradeLastContributor(Connection conn) throws SQLException { 230 String sql = "SELECT dc_c.id, dc_c.item" 231 + " FROM dublincore dc" 232 + " JOIN (SELECT id, max(pos) AS pos FROM dc_contributors GROUP BY id) AS tmp ON (dc.id = tmp.id)" 233 + " JOIN dc_contributors dc_c ON (tmp.id = dc_c.id AND tmp.pos = dc_c.pos)" 234 + " WHERE dc.lastContributor IS NULL;"; 235 try (PreparedStatement ps1 = conn.prepareStatement(sql)) { 236 try (ResultSet rs = ps1.executeQuery()) { 237 while (rs.next()) { 238 String id = rs.getString("id"); 239 String lastContributor = rs.getString("item"); 240 try (PreparedStatement ps2 = conn.prepareStatement( 241 "UPDATE dublincore SET lastContributor = ? WHERE id = ?")) { 242 ps2.setString(1, lastContributor); 243 ps2.setString(2, id); 244 ps2.executeUpdate(); 245 } 246 } 247 } 248 } 249 250 return new SimpleResultSet(); 251 } 252 253 public static ResultSet getAncestorsIds(Connection conn, String idsString) throws SQLException { 254 Set<String> ids = split(idsString); 255 DatabaseMetaData meta = conn.getMetaData(); 256 SimpleResultSet result = new SimpleResultSet(); 257 result.addColumn("ID", Types.VARCHAR, 0, 0); // String id 258 if (meta.getURL().startsWith("jdbc:columnlist:")) { 259 // this is just to query the result set columns 260 return result; 261 } 262 263 LinkedList<String> todo = new LinkedList<>(ids); 264 Set<String> done = new HashSet<>(); 265 Set<String> res = new HashSet<>(); 266 List<String> debugIds = null; 267 if (isLogEnabled()) { 268 debugIds = new LinkedList<>(); 269 } 270 while (!todo.isEmpty()) { 271 done.addAll(todo); 272 String sql = getSelectParentIdsByIdsSql(todo.size()); 273 if (isLogEnabled()) { 274 logDebug(sql, todo); 275 } 276 try (PreparedStatement ps = conn.prepareStatement(sql)) { 277 int i = 1; 278 for (String id : todo) { 279 ps.setString(i++, id); 280 } 281 todo = new LinkedList<>(); 282 try (ResultSet rs = ps.executeQuery()) { 283 while (rs.next()) { 284 String id = rs.getString(1); 285 if (id != null) { 286 if (!res.contains(id)) { 287 res.add(id); 288 result.addRow(new Object[] { id }); 289 } 290 if (!done.contains(id)) { 291 todo.add(id); 292 } 293 if (debugIds != null) { 294 debugIds.add(id); 295 } 296 } 297 } 298 } 299 } 300 if (isLogEnabled()) { 301 logDebug(" -> " + debugIds); 302 } 303 } 304 return result; 305 } 306 307 protected static String getSelectParentIdsByIdsSql(int size) { 308 StringBuilder sb = new StringBuilder("SELECT DISTINCT \"PARENTID\" FROM \"HIERARCHY\" WHERE \"ID\" IN ("); 309 for (int i = 0; i < size; i++) { 310 if (i != 0) { 311 sb.append(", "); 312 } 313 sb.append('?'); 314 } 315 sb.append(')'); 316 return sb.toString(); 317 } 318 319}