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}