Source code

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}