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 PreparedStatement ps = null; 080 try { 081 // find other node ids 082 String sql = "SELECT \"NODEID\" FROM \"CLUSTER_NODES\" WHERE \"NODEID\" <> ?"; 083 if (isLogEnabled()) { 084 logDebug(sql, nodeId); 085 } 086 ps = conn.prepareStatement(sql); 087 ps.setLong(1, nodeId); 088 List<Long> nodeIds = new LinkedList<Long>(); 089 ResultSet rs = ps.executeQuery(); 090 while (rs.next()) { 091 nodeIds.add(rs.getLong(1)); 092 } 093 if (isLogEnabled()) { 094 logDebug(" -> " + nodeIds); 095 } 096 // invalidate 097 sql = "INSERT INTO \"CLUSTER_INVALS\" " + "(\"NODEID\", \"ID\", \"FRAGMENTS\", \"KIND\") " 098 + "VALUES (?, ?, ?, ?)"; 099 for (Long nid : nodeIds) { 100 if (isLogEnabled()) { 101 logDebug(sql, nid, id, fragments, kind); 102 } 103 ps = conn.prepareStatement(sql); 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 } finally { 111 if (ps != null) { 112 ps.close(); 113 } 114 } 115 } 116 117 /** 118 * Gets the invalidations for this cluster node. 119 * 120 * @return a result set with columns id, fragments, kind 121 */ 122 public static ResultSet getClusterInvalidationsString(Connection conn, long nodeId) throws SQLException { 123 DatabaseMetaData meta = conn.getMetaData(); 124 SimpleResultSet result = new SimpleResultSet(); 125 result.addColumn("ID", Types.VARCHAR, 0, 0); // String id 126 result.addColumn("FRAGMENTS", Types.VARCHAR, 0, 0); 127 result.addColumn("KIND", Types.INTEGER, 0, 0); 128 if (meta.getURL().startsWith("jdbc:columnlist:")) { 129 // this is just to query the result set columns 130 return result; 131 } 132 133 PreparedStatement ps = null; 134 try { 135 String sql = "SELECT \"ID\", \"FRAGMENTS\", \"KIND\" FROM \"CLUSTER_INVALS\" " 136 + "WHERE \"NODEID\" = ?"; 137 if (isLogEnabled()) { 138 logDebug(sql, nodeId); 139 } 140 ps = conn.prepareStatement(sql); 141 ps.setLong(1, nodeId); 142 ResultSet rs = ps.executeQuery(); 143 List<Serializable> debugValues = null; 144 if (isLogEnabled()) { 145 debugValues = new LinkedList<Serializable>(); 146 } 147 while (rs.next()) { 148 String id = rs.getString(1); 149 String fragments = rs.getString(2); 150 long kind = rs.getLong(3); 151 result.addRow(new Object[] { id, fragments, Long.valueOf(kind) }); 152 if (debugValues != null) { 153 debugValues.add(id + ',' + fragments + ',' + kind); 154 } 155 } 156 if (debugValues != null) { 157 logDebug(" -> " + debugValues); 158 } 159 160 // remove processed invalidations 161 sql = "DELETE FROM \"CLUSTER_INVALS\" WHERE \"NODEID\" = ?"; 162 if (isLogEnabled()) { 163 logDebug(sql); 164 } 165 ps.close(); 166 ps = conn.prepareStatement(sql); 167 ps.setLong(1, nodeId); 168 ps.execute(); 169 170 // return invalidations 171 return result; 172 } finally { 173 if (ps != null) { 174 ps.close(); 175 } 176 } 177 } 178 179 public static ResultSet upgradeVersions(Connection conn) throws SQLException { 180 PreparedStatement ps1 = null; 181 PreparedStatement ps2 = null; 182 try { 183 String sql = "SELECT v.id, v.versionableid, h.majorversion, h.minorversion" 184 + " FROM versions v JOIN hierarchy h ON v.id = h.id" 185 + " ORDER BY v.versionableid, v.created DESC"; 186 ps1 = conn.prepareStatement(sql); 187 ResultSet rs = ps1.executeQuery(); 188 String series = null; 189 boolean isLatest = false; 190 boolean isLatestMajor = false; 191 while (rs.next()) { 192 String id = rs.getString("id"); 193 String vid = rs.getString("versionableid"); 194 long maj = rs.getLong("majorversion"); 195 long min = rs.getLong("minorversion"); 196 if (vid == null || !vid.equals(series)) { 197 // restart 198 isLatest = true; 199 isLatestMajor = true; 200 series = vid; 201 } 202 boolean isMajor = min == 0; 203 ps2 = conn.prepareStatement("UPDATE versions SET label = ?, islatest = ?, islatestmajor = ?" 204 + " WHERE id = ?"); 205 ps2.setString(1, maj + "." + min); 206 ps2.setBoolean(2, isLatest); 207 ps2.setBoolean(3, isMajor && isLatestMajor); 208 ps2.setString(4, id); 209 ps2.executeUpdate(); 210 // next 211 isLatest = false; 212 if (isMajor) { 213 isLatestMajor = false; 214 } 215 } 216 } finally { 217 if (ps1 != null) { 218 ps1.close(); 219 } 220 if (ps2 != null) { 221 ps2.close(); 222 } 223 } 224 225 return new SimpleResultSet(); 226 } 227 228 public static ResultSet upgradeLastContributor(Connection conn) throws SQLException { 229 PreparedStatement ps1 = null; 230 PreparedStatement ps2 = null; 231 try { 232 String sql = "SELECT dc_c.id, dc_c.item" 233 + " FROM dublincore dc" 234 + " JOIN (SELECT id, max(pos) AS pos FROM dc_contributors GROUP BY id) AS tmp ON (dc.id = tmp.id)" 235 + " JOIN dc_contributors dc_c ON (tmp.id = dc_c.id AND tmp.pos = dc_c.pos)" 236 + " WHERE dc.lastContributor IS NULL;"; 237 ps1 = conn.prepareStatement(sql); 238 ResultSet rs = ps1.executeQuery(); 239 String series = null; 240 while (rs.next()) { 241 String id = rs.getString("id"); 242 String lastContributor = rs.getString("item"); 243 244 ps2 = conn.prepareStatement("UPDATE dublincore SET lastContributor = ? WHERE id = ?"); 245 ps2.setString(1, lastContributor); 246 ps2.setString(2, id); 247 248 ps2.executeUpdate(); 249 } 250 } finally { 251 if (ps1 != null) { 252 ps1.close(); 253 } 254 if (ps2 != null) { 255 ps2.close(); 256 } 257 } 258 259 return new SimpleResultSet(); 260 } 261 262 public static ResultSet getAncestorsIds(Connection conn, String idsString) throws SQLException { 263 Set<String> ids = split(idsString); 264 DatabaseMetaData meta = conn.getMetaData(); 265 SimpleResultSet result = new SimpleResultSet(); 266 result.addColumn("ID", Types.VARCHAR, 0, 0); // String id 267 if (meta.getURL().startsWith("jdbc:columnlist:")) { 268 // this is just to query the result set columns 269 return result; 270 } 271 272 PreparedStatement ps = null; 273 try { 274 LinkedList<String> todo = new LinkedList<String>(ids); 275 Set<String> done = new HashSet<String>(); 276 Set<String> res = new HashSet<String>(); 277 while (!todo.isEmpty()) { 278 done.addAll(todo); 279 String sql = getSelectParentIdsByIdsSql(todo.size()); 280 if (isLogEnabled()) { 281 logDebug(sql, todo); 282 } 283 ps = conn.prepareStatement(sql); 284 int i = 1; 285 for (String id : todo) { 286 ps.setString(i++, id); 287 } 288 todo = new LinkedList<String>(); 289 List<String> debugIds = null; 290 if (isLogEnabled()) { 291 debugIds = new LinkedList<String>(); 292 } 293 ResultSet rs = ps.executeQuery(); 294 while (rs.next()) { 295 String id = rs.getString(1); 296 if (id != null) { 297 if (!res.contains(id)) { 298 res.add(id); 299 result.addRow(new Object[] { id }); 300 } 301 if (!done.contains(id)) { 302 todo.add(id); 303 } 304 if (isLogEnabled()) { 305 debugIds.add(id); 306 } 307 } 308 } 309 if (isLogEnabled()) { 310 logDebug(" -> " + debugIds); 311 } 312 ps.close(); 313 ps = null; 314 } 315 return result; 316 } finally { 317 if (ps != null) { 318 ps.close(); 319 } 320 } 321 } 322 323 protected static String getSelectParentIdsByIdsSql(int size) { 324 StringBuilder buf = new StringBuilder("SELECT DISTINCT \"PARENTID\" FROM \"HIERARCHY\" WHERE \"ID\" IN ("); 325 for (int i = 0; i < size; i++) { 326 if (i != 0) { 327 buf.append(", "); 328 } 329 buf.append('?'); 330 } 331 buf.append(')'); 332 return buf.toString(); 333 } 334 335}