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.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}