Source code

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