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.Statement; 029import java.sql.Types; 030import java.util.HashSet; 031import java.util.LinkedList; 032import java.util.List; 033import java.util.Set; 034 035import org.h2.tools.SimpleResultSet; 036 037/** 038 * Functions used as stored procedures for H2. 039 * 040 * @author Florent Guillaume 041 */ 042public class H2Functions extends EmbeddedFunctions { 043 044 // for debug 045 private static boolean isLogEnabled() { 046 return false; 047 // return log.isTraceEnabled(); 048 } 049 050 // for debug 051 private static void logDebug(String message, Object... args) { 052 // log.trace("SQL: " + String.format(message.replace("?", "%s"), args)); 053 } 054 055 public static boolean isInTreeString(Connection conn, String id, String baseId) throws SQLException { 056 return isInTree(conn, id, baseId); 057 } 058 059 public static boolean isInTreeLong(Connection conn, Long id, Long baseId) throws SQLException { 060 return isInTree(conn, id, baseId); 061 } 062 063 public static boolean isAccessAllowedString(Connection conn, String id, String principals, String permissions) 064 throws SQLException { 065 return isAccessAllowed(conn, id, split(principals), split(permissions)); 066 } 067 068 public static boolean isAccessAllowedLong(Connection conn, Long id, String principals, String permissions) 069 throws SQLException { 070 return isAccessAllowed(conn, id, split(principals), split(permissions)); 071 } 072 073 /** 074 * Adds an invalidation from this cluster node to the invalidations list. 075 */ 076 @SuppressWarnings("boxing") 077 public static void clusterInvalidateString(Connection conn, long nodeId, String id, String fragments, int kind) 078 throws SQLException { 079 // find other node ids 080 String sql = "SELECT \"NODEID\" FROM \"CLUSTER_NODES\" WHERE \"NODEID\" <> ?"; 081 if (isLogEnabled()) { 082 logDebug(sql, nodeId); 083 } 084 List<Long> nodeIds = new LinkedList<>(); 085 try (PreparedStatement ps = conn.prepareStatement(sql)) { 086 ps.setLong(1, nodeId); 087 try (ResultSet rs = ps.executeQuery()) { 088 while (rs.next()) { 089 nodeIds.add(rs.getLong(1)); 090 } 091 } 092 } 093 if (isLogEnabled()) { 094 logDebug(" -> " + nodeIds); 095 } 096 // invalidate 097 sql = "INSERT INTO \"CLUSTER_INVALS\" " + "(\"NODEID\", \"ID\", \"FRAGMENTS\", \"KIND\") " 098 + "VALUES (?, ?, ?, ?)"; 099 try (PreparedStatement ps = conn.prepareStatement(sql)) { 100 for (Long nid : nodeIds) { 101 if (isLogEnabled()) { 102 logDebug(sql, nid, id, fragments, kind); 103 } 104 ps.setLong(1, nid); 105 ps.setObject(2, id); 106 ps.setString(3, fragments); 107 ps.setInt(4, kind); 108 ps.execute(); 109 } 110 } 111 } 112 113 /** 114 * Gets the invalidations for this cluster node. 115 * 116 * @return a result set with columns id, fragments, kind 117 */ 118 public static ResultSet getClusterInvalidationsString(Connection conn, long nodeId) throws SQLException { 119 DatabaseMetaData meta = conn.getMetaData(); 120 SimpleResultSet result = new SimpleResultSet(); 121 result.addColumn("ID", Types.VARCHAR, 0, 0); // String id 122 result.addColumn("FRAGMENTS", Types.VARCHAR, 0, 0); 123 result.addColumn("KIND", Types.INTEGER, 0, 0); 124 if (meta.getURL().startsWith("jdbc:columnlist:")) { 125 // this is just to query the result set columns 126 return result; 127 } 128 129 String sql = "SELECT \"ID\", \"FRAGMENTS\", \"KIND\" FROM \"CLUSTER_INVALS\" " 130 + "WHERE \"NODEID\" = ?"; 131 if (isLogEnabled()) { 132 logDebug(sql, nodeId); 133 } 134 try (PreparedStatement ps = conn.prepareStatement(sql)) { 135 ps.setLong(1, nodeId); 136 List<Serializable> debugValues = null; 137 if (isLogEnabled()) { 138 debugValues = new LinkedList<>(); 139 } 140 try (ResultSet rs = ps.executeQuery()) { 141 while (rs.next()) { 142 String id = rs.getString(1); 143 String fragments = rs.getString(2); 144 long kind = rs.getLong(3); 145 result.addRow(new Object[] { id, fragments, Long.valueOf(kind) }); 146 if (debugValues != null) { 147 debugValues.add(id + ',' + fragments + ',' + kind); 148 } 149 } 150 } 151 if (debugValues != null) { 152 logDebug(" -> " + debugValues); 153 } 154 155 // remove processed invalidations 156 sql = "DELETE FROM \"CLUSTER_INVALS\" WHERE \"NODEID\" = ?"; 157 if (isLogEnabled()) { 158 logDebug(sql); 159 } 160 } 161 try (PreparedStatement ps = conn.prepareStatement(sql)) { 162 ps.setLong(1, nodeId); 163 ps.execute(); 164 } 165 // return invalidations 166 return result; 167 } 168 169 public static ResultSet upgradeVersions(Connection conn) throws SQLException { 170 String sql = "SELECT v.id, v.versionableid, h.majorversion, h.minorversion" 171 + " FROM versions v JOIN hierarchy h ON v.id = h.id" 172 + " ORDER BY v.versionableid, v.created DESC"; 173 try (PreparedStatement ps1 = conn.prepareStatement(sql)) { 174 try (ResultSet rs = ps1.executeQuery()) { 175 String series = null; 176 boolean isLatest = false; 177 boolean isLatestMajor = false; 178 while (rs.next()) { 179 String id = rs.getString("id"); 180 String vid = rs.getString("versionableid"); 181 long maj = rs.getLong("majorversion"); 182 long min = rs.getLong("minorversion"); 183 if (vid == null || !vid.equals(series)) { 184 // restart 185 isLatest = true; 186 isLatestMajor = true; 187 series = vid; 188 } 189 boolean isMajor = min == 0; 190 try (PreparedStatement ps2 = conn.prepareStatement( 191 "UPDATE versions SET label = ?, islatest = ?, islatestmajor = ?" + " WHERE id = ?")) { 192 ps2.setString(1, maj + "." + min); 193 ps2.setBoolean(2, isLatest); 194 ps2.setBoolean(3, isMajor && isLatestMajor); 195 ps2.setString(4, id); 196 ps2.executeUpdate(); 197 } 198 // next 199 isLatest = false; 200 if (isMajor) { 201 isLatestMajor = false; 202 } 203 } 204 } 205 } 206 207 return new SimpleResultSet(); 208 } 209 210 public static ResultSet upgradeLastContributor(Connection conn) throws SQLException { 211 String sql = "SELECT dc_c.id, dc_c.item" 212 + " FROM dublincore dc" 213 + " JOIN (SELECT id, max(pos) AS pos FROM dc_contributors GROUP BY id) AS tmp ON (dc.id = tmp.id)" 214 + " JOIN dc_contributors dc_c ON (tmp.id = dc_c.id AND tmp.pos = dc_c.pos)" 215 + " WHERE dc.lastContributor IS NULL;"; 216 try (PreparedStatement ps1 = conn.prepareStatement(sql)) { 217 try (ResultSet rs = ps1.executeQuery()) { 218 while (rs.next()) { 219 String id = rs.getString("id"); 220 String lastContributor = rs.getString("item"); 221 try (PreparedStatement ps2 = conn.prepareStatement( 222 "UPDATE dublincore SET lastContributor = ? WHERE id = ?")) { 223 ps2.setString(1, lastContributor); 224 ps2.setString(2, id); 225 ps2.executeUpdate(); 226 } 227 } 228 } 229 } 230 231 return new SimpleResultSet(); 232 } 233 234 public static ResultSet getAncestorsIds(Connection conn, String idsString) throws SQLException { 235 Set<String> ids = split(idsString); 236 DatabaseMetaData meta = conn.getMetaData(); 237 SimpleResultSet result = new SimpleResultSet(); 238 result.addColumn("ID", Types.VARCHAR, 0, 0); // String id 239 if (meta.getURL().startsWith("jdbc:columnlist:")) { 240 // this is just to query the result set columns 241 return result; 242 } 243 244 LinkedList<String> todo = new LinkedList<String>(ids); 245 Set<String> done = new HashSet<String>(); 246 Set<String> res = new HashSet<String>(); 247 List<String> debugIds = null; 248 if (isLogEnabled()) { 249 debugIds = new LinkedList<String>(); 250 } 251 while (!todo.isEmpty()) { 252 done.addAll(todo); 253 String sql = getSelectParentIdsByIdsSql(todo.size()); 254 if (isLogEnabled()) { 255 logDebug(sql, todo); 256 } 257 try (PreparedStatement ps = conn.prepareStatement(sql)) { 258 int i = 1; 259 for (String id : todo) { 260 ps.setString(i++, id); 261 } 262 todo = new LinkedList<String>(); 263 try (ResultSet rs = ps.executeQuery()) { 264 while (rs.next()) { 265 String id = rs.getString(1); 266 if (id != null) { 267 if (!res.contains(id)) { 268 res.add(id); 269 result.addRow(new Object[] { id }); 270 } 271 if (!done.contains(id)) { 272 todo.add(id); 273 } 274 if (debugIds != null) { 275 debugIds.add(id); 276 } 277 } 278 } 279 } 280 } 281 if (isLogEnabled()) { 282 logDebug(" -> " + debugIds); 283 } 284 } 285 return result; 286 } 287 288 protected static String getSelectParentIdsByIdsSql(int size) { 289 StringBuilder buf = new StringBuilder("SELECT DISTINCT \"PARENTID\" FROM \"HIERARCHY\" WHERE \"ID\" IN ("); 290 for (int i = 0; i < size; i++) { 291 if (i != 0) { 292 buf.append(", "); 293 } 294 buf.append('?'); 295 } 296 buf.append(')'); 297 return buf.toString(); 298 } 299 300}