001/*
002 * (C) Copyright 2006-2016 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 */
019package org.nuxeo.ecm.core.storage.sql.jdbc;
020
021import java.io.IOException;
022import java.io.Serializable;
023import java.sql.Connection;
024import java.sql.ResultSet;
025import java.sql.SQLException;
026import java.util.ArrayList;
027import java.util.Arrays;
028import java.util.Collection;
029import java.util.Collections;
030import java.util.HashMap;
031import java.util.HashSet;
032import java.util.LinkedList;
033import java.util.List;
034import java.util.Map;
035import java.util.Map.Entry;
036import java.util.Set;
037import java.util.stream.Collectors;
038
039import org.nuxeo.ecm.core.api.NuxeoException;
040import org.nuxeo.ecm.core.api.model.Delta;
041import org.nuxeo.ecm.core.api.repository.FulltextConfiguration;
042import org.nuxeo.ecm.core.query.sql.NXQL;
043import org.nuxeo.ecm.core.schema.SchemaManager;
044import org.nuxeo.ecm.core.storage.sql.ColumnType;
045import org.nuxeo.ecm.core.storage.sql.Mapper;
046import org.nuxeo.ecm.core.storage.sql.Model;
047import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor;
048import org.nuxeo.ecm.core.storage.sql.RepositoryImpl;
049import org.nuxeo.ecm.core.storage.sql.RowMapper.RowUpdate;
050import org.nuxeo.ecm.core.storage.sql.Selection;
051import org.nuxeo.ecm.core.storage.sql.SelectionType;
052import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
053import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database;
054import org.nuxeo.ecm.core.storage.sql.jdbc.db.Delete;
055import org.nuxeo.ecm.core.storage.sql.jdbc.db.Insert;
056import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join;
057import org.nuxeo.ecm.core.storage.sql.jdbc.db.Select;
058import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
059import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table.IndexType;
060import org.nuxeo.ecm.core.storage.sql.jdbc.db.Update;
061import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect;
062import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.SQLStatement;
063import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.SQLStatement.ListCollector;
064import org.nuxeo.runtime.api.Framework;
065
066/**
067 * This singleton generates and holds the actual SQL DDL and DML statements for the operations needed by the
068 * {@link Mapper}, given a {@link Model}.
069 * <p>
070 * It is specific to one SQL dialect.
071 */
072public class SQLInfo {
073
074    private static final String ORDER_DESC = "DESC";
075
076    private static final String ORDER_ASC = "ASC";
077
078    public final Database database;
079
080    public final Dialect dialect;
081
082    public final boolean softDeleteEnabled;
083
084    public final boolean proxiesEnabled;
085
086    private final Model model;
087
088    private String selectRootIdSql;
089
090    private Column selectRootIdWhatColumn;
091
092    private final Map<String, String> insertSqlMap; // statement
093
094    private final Map<String, List<Column>> insertColumnsMap;
095
096    private final Map<String, String> deleteSqlMap; // statement
097
098    private Map<SelectionType, SQLInfoSelection> selections;
099
100    private String selectChildrenIdsAndTypesSql;
101
102    private String selectChildrenIdsAndTypesSqlExcludeSpecialChildren;
103
104    private String selectComplexChildrenIdsAndTypesSql;
105
106    private String selectComplexChildrenIdsAndTypesSqlIncludeSpecialChildren;
107
108    private List<Column> selectChildrenIdsAndTypesWhatColumns;
109
110    private String selectDescendantsInfoSql;
111
112    private List<Column> selectDescendantsInfoWhatColumns;
113
114    private final Map<String, String> copySqlMap;
115
116    private final Map<String, Column> copyIdColumnMap;
117
118    public final Map<String, SQLInfoSelect> selectFragmentById;
119
120    protected String createClusterNodeSql;
121
122    protected List<Column> createClusterNodeColumns;
123
124    protected String deleteClusterNodeSql;
125
126    protected Column deleteClusterNodeColumn;
127
128    protected String deleteClusterInvalsSql;
129
130    protected Column deleteClusterInvalsColumn;
131
132    protected List<Column> clusterInvalidationsColumns;
133
134    protected Map<String, List<SQLStatement>> sqlStatements;
135
136    public Map<String, Serializable> sqlStatementsProperties;
137
138    protected List<String> getBinariesSql;
139
140    protected List<Column> getBinariesColumns;
141
142    /**
143     * Generates and holds the needed SQL statements given a {@link Model} and a {@link Dialect}.
144     *
145     * @param model the model
146     * @param dialect the SQL dialect
147     */
148    public SQLInfo(Model model, Dialect dialect, boolean requiresClusterSQL) {
149        this.model = model;
150        this.dialect = dialect;
151        RepositoryDescriptor repositoryDescriptor = model.getRepositoryDescriptor();
152        softDeleteEnabled = repositoryDescriptor.getSoftDeleteEnabled();
153        proxiesEnabled = repositoryDescriptor.getProxiesEnabled();
154
155        database = new Database(dialect);
156
157        selectRootIdSql = null;
158        selectRootIdWhatColumn = null;
159
160        selectFragmentById = new HashMap<>();
161
162        selections = new HashMap<>();
163
164        selectChildrenIdsAndTypesSql = null;
165        selectChildrenIdsAndTypesSqlExcludeSpecialChildren = null;
166        selectChildrenIdsAndTypesWhatColumns = null;
167        selectComplexChildrenIdsAndTypesSql = null;
168        selectComplexChildrenIdsAndTypesSqlIncludeSpecialChildren = null;
169
170        insertSqlMap = new HashMap<>();
171        insertColumnsMap = new HashMap<>();
172
173        deleteSqlMap = new HashMap<>();
174
175        copySqlMap = new HashMap<>();
176        copyIdColumnMap = new HashMap<>();
177
178        getBinariesSql = new ArrayList<>(1);
179        getBinariesColumns = new ArrayList<>(1);
180
181        initSQL(requiresClusterSQL);
182        initSelections();
183
184        try {
185            initSQLStatements(RepositoryImpl.testProps, repositoryDescriptor.sqlInitFiles);
186        } catch (IOException e) {
187            throw new NuxeoException(e);
188        }
189    }
190
191    public Database getDatabase() {
192        return database;
193    }
194
195    // ----- select -----
196
197    public String getSelectRootIdSql() {
198        return selectRootIdSql;
199    }
200
201    public Column getSelectRootIdWhatColumn() {
202        return selectRootIdWhatColumn;
203    }
204
205    public String getInsertRootIdSql() {
206        return insertSqlMap.get(Model.REPOINFO_TABLE_NAME);
207    }
208
209    public List<Column> getInsertRootIdColumns() {
210        return insertColumnsMap.get(Model.REPOINFO_TABLE_NAME);
211    }
212
213    public SQLInfoSelection getSelection(SelectionType type) {
214        return selections.get(type);
215
216    }
217
218    public String getSelectChildrenIdsAndTypesSql(boolean excludeSpecialChildren, boolean excludeRegularChildren) {
219        if (excludeRegularChildren) {
220            return excludeSpecialChildren ? selectComplexChildrenIdsAndTypesSql
221                    : selectComplexChildrenIdsAndTypesSqlIncludeSpecialChildren;
222        } else {
223            return excludeSpecialChildren ? selectChildrenIdsAndTypesSqlExcludeSpecialChildren
224                    : selectChildrenIdsAndTypesSql;
225        }
226    }
227
228    public List<Column> getSelectChildrenIdsAndTypesWhatColumns() {
229        return selectChildrenIdsAndTypesWhatColumns;
230    }
231
232    public String getSelectDescendantsInfoSql() {
233        return selectDescendantsInfoSql;
234    }
235
236    public List<Column> getSelectDescendantsInfoWhatColumns() {
237        return selectDescendantsInfoWhatColumns;
238    }
239
240    // ----- cluster -----
241
242    public String getCreateClusterNodeSql() {
243        return createClusterNodeSql;
244    }
245
246    public List<Column> getCreateClusterNodeColumns() {
247        return createClusterNodeColumns;
248    }
249
250    public String getDeleteClusterNodeSql() {
251        return deleteClusterNodeSql;
252    }
253
254    public Column getDeleteClusterNodeColumn() {
255        return deleteClusterNodeColumn;
256    }
257
258    public String getDeleteClusterInvalsSql() {
259        return deleteClusterInvalsSql;
260    }
261
262    public Column getDeleteClusterInvalsColumn() {
263        return deleteClusterInvalsColumn;
264    }
265
266    public int getClusterNodeIdType() {
267        return dialect.getJDBCTypeAndString(ColumnType.CLUSTERNODE).jdbcType;
268    }
269
270    public List<Column> getClusterInvalidationsColumns() {
271        return clusterInvalidationsColumns;
272    }
273
274    // ----- insert -----
275
276    /**
277     * Returns the SQL {@code INSERT} to add a row. The columns that represent sequences that are implicitly
278     * auto-incremented aren't included.
279     *
280     * @param tableName the table name
281     * @return the SQL {@code INSERT} statement
282     */
283    public String getInsertSql(String tableName) {
284        return insertSqlMap.get(tableName);
285    }
286
287    /**
288     * Returns the list of columns to use for an {@code INSERT} statement {@link #getInsertSql}.
289     *
290     * @param tableName the table name
291     * @return the list of columns
292     */
293    public List<Column> getInsertColumns(String tableName) {
294        return insertColumnsMap.get(tableName);
295    }
296
297    // -----
298
299    /**
300     * Returns the clause used to match a given row by id in the given table.
301     * <p>
302     * Takes into account soft deletes.
303     *
304     * @param tableName the table name
305     * @return the clause, like {@code table.id = ?}
306     */
307    public String getIdEqualsClause(String tableName) {
308        return database.getTable(tableName).getColumn(Model.MAIN_KEY).getQuotedName() + " = ?"
309                + getSoftDeleteClause(tableName);
310    }
311
312    /**
313     * Returns {@code AND isdeleted IS NULL} if this is the hierarchy table and soft delete is activated.
314     *
315     * @param tableName the table name
316     * @return the clause
317     */
318    public String getSoftDeleteClause(String tableName) {
319        if (Model.HIER_TABLE_NAME.equals(tableName) && softDeleteEnabled) {
320            return " AND " + getSoftDeleteClause();
321        } else {
322            return "";
323        }
324    }
325
326    /**
327     * Returns null or {@code AND isdeleted IS NULL} if soft delete is activated.
328     *
329     * @return the clause, or null
330     */
331    public String getSoftDeleteClause() {
332        if (softDeleteEnabled) {
333            return database.getTable(Model.HIER_TABLE_NAME).getColumn(Model.MAIN_IS_DELETED_KEY).getFullQuotedName()
334                    + " IS NULL";
335        } else {
336            return null;
337        }
338    }
339
340    // ----- update -----
341
342    // TODO these two methods are redundant with one another
343
344    /**
345     * <pre>
346     * UPDATE tableName SET key1 = ?, key2 = ?, ... WHERE id = ? AND condition1 = ? AND condition2 IS NULL ...
347     * </pre>
348     */
349    public SQLInfoSelect getUpdateById(String tableName, RowUpdate rowu) {
350        Table table = database.getTable(tableName);
351        Update update = new Update(table);
352
353        List<Column> whatColumns = new ArrayList<>();
354        Set<String> deltas = new HashSet<>();
355        for (String key : rowu.keys) {
356            whatColumns.add(table.getColumn(key));
357            Serializable value = rowu.row.get(key);
358            if (value instanceof Delta && ((Delta) value).getBase() != null) {
359                deltas.add(key);
360            }
361        }
362        update.setUpdatedColumns(whatColumns, deltas);
363
364        List<Column> whereColumns = new ArrayList<>(2);
365        String where = getIdEqualsClause(tableName);
366        whereColumns.add(table.getColumn(Model.MAIN_KEY));
367        if (rowu.conditions != null) {
368            for (Entry<String, Serializable> es : rowu.conditions.entrySet()) {
369                String key = es.getKey();
370                boolean isNull = es.getValue() == null;
371                Column column = table.getColumn(key);
372                String columnName = column.getQuotedName();
373                if (isNull) {
374                    where += " AND " + columnName + " IS NULL";
375                } else {
376                    where += " AND " + columnName + " = ?";
377                    whereColumns.add(column);
378                }
379            }
380        }
381        update.setWhere(where);
382        return new SQLInfoSelect(update.getStatement(), whatColumns, whereColumns, null);
383    }
384
385    public Update getUpdateByIdForKeys(String tableName, List<String> keys) {
386        Table table = database.getTable(tableName);
387        List<Column> columns = new LinkedList<>();
388        for (String key : keys) {
389            columns.add(table.getColumn(key));
390        }
391        Update update = new Update(table);
392        update.setUpdatedColumns(columns);
393        update.setWhere(getIdEqualsClause(tableName));
394        return update;
395    }
396
397    /**
398     * Select by ids for all values of several fragments.
399     */
400    public SQLInfoSelect getSelectFragmentsByIds(String tableName, int nids) {
401        return getSelectFragmentsByIds(tableName, nids, null, null);
402    }
403
404    /**
405     * Select by ids for all values of several fragments (maybe ordered along columns -- for collection fragments
406     * retrieval).
407     */
408    public SQLInfoSelect getSelectFragmentsByIds(String tableName, int nids, String[] orderBys,
409            Set<String> skipColumns) {
410        Table table = database.getTable(tableName);
411        List<Column> whatColumns = new LinkedList<>();
412        List<String> whats = new LinkedList<>();
413        List<Column> opaqueColumns = new LinkedList<>();
414        for (Column column : table.getColumns()) {
415            if (column.isOpaque()) {
416                opaqueColumns.add(column);
417            } else if (skipColumns == null || !skipColumns.contains(column.getKey())) {
418                whatColumns.add(column);
419                whats.add(column.getQuotedName());
420            }
421        }
422        Column whereColumn = table.getColumn(Model.MAIN_KEY);
423        StringBuilder sb = new StringBuilder(whereColumn.getQuotedName());
424        sb.append(" IN (");
425        for (int i = 0; i < nids; i++) {
426            if (i != 0) {
427                sb.append(", ");
428            }
429            sb.append('?');
430        }
431        sb.append(')');
432        sb.append(getSoftDeleteClause(tableName));
433        Select select = new Select(table);
434        select.setWhat(String.join(", ", whats));
435        select.setFrom(table.getQuotedName());
436        select.setWhere(sb.toString());
437        if (orderBys != null) {
438            List<String> orders = new LinkedList<>();
439            for (String orderBy : orderBys) {
440                orders.add(table.getColumn(orderBy).getQuotedName());
441            }
442            select.setOrderBy(String.join(", ", orders));
443        }
444        return new SQLInfoSelect(select.getStatement(), whatColumns, Collections.singletonList(whereColumn),
445                opaqueColumns.isEmpty() ? null : opaqueColumns);
446    }
447
448    /**
449     * Select all ancestors ids for several fragments.
450     * <p>
451     * Fast alternative to the slowest iterative {@link #getSelectParentIds}.
452     *
453     * @return null if it's not possible in one call in this dialect
454     */
455    public SQLInfoSelect getSelectAncestorsIds() {
456        String sql = dialect.getAncestorsIdsSql();
457        if (sql == null) {
458            return null;
459        }
460        Table table = database.getTable(Model.HIER_TABLE_NAME);
461        Column mainColumn = table.getColumn(Model.MAIN_KEY);
462        // no soft-delete check needed, as ancestors of a non-deleted doc
463        // aren't deleted either
464        return new SQLInfoSelect(sql, Collections.singletonList(mainColumn), null, null);
465    }
466
467    /**
468     * Select parentid by ids for all values of several fragments.
469     */
470    public SQLInfoSelect getSelectParentIds(int nids) {
471        Table table = database.getTable(Model.HIER_TABLE_NAME);
472        Column whatColumn = table.getColumn(Model.HIER_PARENT_KEY);
473        Column whereColumn = table.getColumn(Model.MAIN_KEY);
474        StringBuilder sb = new StringBuilder(whereColumn.getQuotedName());
475        sb.append(" IN (");
476        for (int i = 0; i < nids; i++) {
477            if (i != 0) {
478                sb.append(", ");
479            }
480            sb.append('?');
481        }
482        sb.append(')');
483        sb.append(getSoftDeleteClause(Model.HIER_TABLE_NAME));
484        Select select = new Select(table);
485        select.setWhat("DISTINCT " + whatColumn.getQuotedName());
486        select.setFrom(table.getQuotedName());
487        select.setWhere(sb.toString());
488        return new SQLInfoSelect(select.getStatement(), Collections.singletonList(whatColumn),
489                Collections.singletonList(whereColumn), null);
490    }
491
492    /**
493     * Selects all children (not complex) for several parent ids.
494     */
495    public SQLInfoSelect getSelectChildrenNodeInfos(int nids) {
496        Table hierTable = database.getTable(Model.HIER_TABLE_NAME);
497        Column mainColumn = hierTable.getColumn(Model.MAIN_KEY);
498        List<Column> whatColumns = new ArrayList<>();
499        whatColumns.add(mainColumn);
500        whatColumns.add(hierTable.getColumn(Model.HIER_PARENT_KEY));
501        whatColumns.add(hierTable.getColumn(Model.MAIN_PRIMARY_TYPE_KEY));
502        whatColumns.add(hierTable.getColumn(Model.MAIN_RETAIN_UNTIL_KEY));
503        whatColumns.add(hierTable.getColumn(Model.MAIN_HAS_LEGAL_HOLD_KEY));
504        whatColumns.add(hierTable.getColumn(Model.MAIN_IS_RETENTION_ACTIVE_KEY));
505        Table proxyTable = null;
506        if (proxiesEnabled) {
507            proxyTable = database.getTable(Model.PROXY_TABLE_NAME);
508            whatColumns.add(proxyTable.getColumn(Model.PROXY_TARGET_KEY));
509            whatColumns.add(proxyTable.getColumn(Model.PROXY_VERSIONABLE_KEY));
510        }
511        String selectWhats = whatColumns.stream().map(Column::getFullQuotedName).collect(Collectors.joining(", "));
512        Select select = new Select(null);
513        select.setWhat(selectWhats);
514        String from = hierTable.getQuotedName();
515        if (proxiesEnabled) {
516            from += " LEFT JOIN " + proxyTable.getQuotedName() + " ON " + mainColumn.getFullQuotedName() + " = "
517                    + proxyTable.getColumn(Model.MAIN_KEY).getFullQuotedName();
518        }
519        select.setFrom(from);
520        Column whereColumn = hierTable.getColumn(Model.HIER_PARENT_KEY);
521        StringBuilder sb = new StringBuilder(whereColumn.getFullQuotedName());
522        if (nids == 1) {
523            sb.append(" = ?");
524        } else {
525            sb.append(" IN (");
526            for (int i = 0; i < nids; i++) {
527                if (i != 0) {
528                    sb.append(", ");
529                }
530                sb.append('?');
531            }
532            sb.append(')');
533        }
534        sb.append(" AND ");
535        sb.append(hierTable.getColumn(Model.HIER_CHILD_ISPROPERTY_KEY).getFullQuotedName());
536        sb.append(" = ").append(dialect.toBooleanValueString(false)); // not complex
537        sb.append(getSoftDeleteClause(Model.HIER_TABLE_NAME));
538        select.setWhere(sb.toString());
539        return new SQLInfoSelect(select.getStatement(), whatColumns, Collections.singletonList(whereColumn), null);
540    }
541
542    // ----- delete -----
543
544    /**
545     * Returns the SQL {@code DELETE} to delete a row. The primary key columns are free parameters.
546     *
547     * @param tableName the table name
548     * @return the SQL {@code DELETE} statement
549     */
550    public String getDeleteSql(String tableName) {
551        return deleteSqlMap.get(tableName);
552    }
553
554    /**
555     * Returns the SQL {@code DELETE} to delete several rows. The primary key columns are free parameters.
556     *
557     * @param tableName the table name
558     * @param n the number of rows to delete
559     * @return the SQL {@code DELETE} statement with a {@code IN} for the keys
560     */
561    public String getDeleteSql(String tableName, int n) {
562        Table table = database.getTable(tableName);
563        Delete delete = new Delete(table);
564        String where = null;
565        for (Column column : table.getColumns()) {
566            if (column.getKey().equals(Model.MAIN_KEY)) {
567                StringBuilder sb = new StringBuilder();
568                sb.append(column.getQuotedName());
569                if (n == 1) {
570                    sb.append(" = ?");
571                } else {
572                    sb.append(" IN (");
573                    for (int i = 0; i < n; i++) {
574                        if (i > 0) {
575                            sb.append(", ");
576                        }
577                        sb.append("?");
578                    }
579                    sb.append(")");
580                }
581                where = sb.toString();
582            }
583        }
584        delete.setWhere(where);
585        return delete.getStatement();
586    }
587
588    /**
589     * Returns the SQL to soft-delete several rows. The array of ids and the time are free parameters.
590     *
591     * @return the SQL statement
592     */
593    public String getSoftDeleteSql() {
594        return dialect.getSoftDeleteSql();
595    }
596
597    /**
598     * Returns the SQL to clean (hard-delete) soft-deleted rows. The max and beforeTime are free parameters.
599     *
600     * @return the SQL statement
601     */
602    public String getSoftDeleteCleanupSql() {
603        return dialect.getSoftDeleteCleanupSql();
604    }
605
606    // ----- copy -----
607
608    public SQLInfoSelect getCopyHier(boolean explicitName, boolean resetVersion) {
609        Table table = database.getTable(Model.HIER_TABLE_NAME);
610        Collection<Column> columns = table.getColumns();
611        List<String> selectWhats = new ArrayList<>(columns.size());
612        List<Column> selectWhatColumns = new ArrayList<>(5);
613        Insert insert = new Insert(table);
614        for (Column column : columns) {
615            if (column.isIdentity()) {
616                // identity column is never copied
617                continue;
618            }
619            insert.addColumn(column);
620            String quotedName = column.getQuotedName();
621            String key = column.getKey();
622            if (key.equals(Model.MAIN_KEY) //
623                    || key.equals(Model.HIER_PARENT_KEY) //
624                    || key.equals(Model.MAIN_BASE_VERSION_KEY) //
625                    || key.equals(Model.MAIN_CHECKED_IN_KEY) //
626                    || key.equals(Model.MAIN_IS_RECORD_KEY) //
627                    || (key.equals(Model.MAIN_MINOR_VERSION_KEY) && resetVersion) //
628                    || (key.equals(Model.MAIN_MAJOR_VERSION_KEY) && resetVersion) //
629                    || (key.equals(Model.HIER_CHILD_NAME_KEY) && explicitName)) {
630                // explicit value set
631                selectWhats.add("?");
632                selectWhatColumns.add(column);
633            } else {
634                // otherwise copy value
635                selectWhats.add(quotedName);
636            }
637        }
638        Column whereColumn = table.getColumn(Model.MAIN_KEY);
639        Select select = new Select(null);
640        select.setFrom(table.getQuotedName());
641        select.setWhat(String.join(", ", selectWhats));
642        select.setWhere(whereColumn.getQuotedName() + " = ?");
643        insert.setValues(select.getStatement());
644        String sql = insert.getStatement();
645        return new SQLInfoSelect(sql, selectWhatColumns, Collections.singletonList(whereColumn), null);
646    }
647
648    public String getCopySql(String tableName) {
649        return copySqlMap.get(tableName);
650    }
651
652    public Column getCopyIdColumn(String tableName) {
653        return copyIdColumnMap.get(tableName);
654    }
655
656    // ----- prepare everything -----
657
658    /**
659     * Creates all the sql from the models.
660     */
661    protected void initSQL(boolean requiresClusterSQL) {
662
663        // structural tables
664        if (requiresClusterSQL) {
665            if (!dialect.isClusteringSupported()) {
666                throw new NuxeoException("Clustering not supported for " + dialect.getClass().getSimpleName());
667            }
668            initClusterSQL();
669        }
670        initHierarchySQL();
671        initRepositorySQL();
672        if (dialect.supportsAncestorsTable()) {
673            initAncestorsSQL();
674        }
675
676        for (String tableName : model.getFragmentNames()) {
677            if (tableName.equals(Model.HIER_TABLE_NAME)) {
678                continue;
679            }
680            initFragmentSQL(tableName);
681        }
682
683        /*
684         * versions
685         */
686
687        Table hierTable = database.getTable(Model.HIER_TABLE_NAME);
688        Table versionTable = database.getTable(Model.VERSION_TABLE_NAME);
689        hierTable.addIndex(Model.MAIN_IS_VERSION_KEY);
690        versionTable.addIndex(Model.VERSION_VERSIONABLE_KEY);
691        // don't index series+label, a simple label scan will suffice
692
693        /*
694         * proxies
695         */
696
697        if (proxiesEnabled) {
698            Table proxyTable = database.getTable(Model.PROXY_TABLE_NAME);
699            proxyTable.addIndex(Model.PROXY_VERSIONABLE_KEY);
700            proxyTable.addIndex(Model.PROXY_TARGET_KEY);
701        }
702
703        initSelectDescendantsSQL();
704
705        /*
706         * fulltext
707         */
708        if (!model.getRepositoryDescriptor().getFulltextDescriptor().getFulltextSearchDisabled()) {
709            Table table = database.getTable(Model.FULLTEXT_TABLE_NAME);
710            FulltextConfiguration fulltextConfiguration = model.getFulltextConfiguration();
711            if (fulltextConfiguration.indexNames.size() > 1 && !dialect.supportsMultipleFulltextIndexes()) {
712                String msg = String.format("SQL database supports only one fulltext index, but %d are configured: %s",
713                        fulltextConfiguration.indexNames.size(), fulltextConfiguration.indexNames);
714                throw new NuxeoException(msg);
715            }
716            for (String indexName : fulltextConfiguration.indexNames) {
717                String suffix = model.getFulltextIndexSuffix(indexName);
718                int ftic = dialect.getFulltextIndexedColumns();
719                if (ftic == 1) {
720                    table.addIndex(indexName, IndexType.FULLTEXT, Model.FULLTEXT_FULLTEXT_KEY + suffix);
721                } else if (ftic == 2) {
722                    table.addIndex(indexName, IndexType.FULLTEXT, Model.FULLTEXT_SIMPLETEXT_KEY + suffix,
723                            Model.FULLTEXT_BINARYTEXT_KEY + suffix);
724                }
725            }
726        }
727
728        /*
729         * binary columns for GC
730         */
731        for (Entry<String, List<String>> e : model.getBinaryPropertyInfos().entrySet()) {
732            String tableName = e.getKey();
733            Table table = database.getTable(tableName);
734            for (String key : e.getValue()) {
735                Select select = new Select(table);
736                Column col = table.getColumn(key); // key = name for now
737                select.setWhat("DISTINCT " + col.getQuotedName());
738                select.setFrom(table.getQuotedName());
739                getBinariesSql.add(select.getStatement());
740                // in the result column we want the digest, not the binary
741                Column resCol = new Column(table, null, ColumnType.STRING, null);
742                getBinariesColumns.add(resCol);
743            }
744        }
745    }
746
747    protected void initClusterSQL() {
748        TableMaker maker = new TableMaker(Model.CLUSTER_NODES_TABLE_NAME);
749        maker.newColumn(Model.CLUSTER_NODES_NODEID_KEY, ColumnType.CLUSTERNODE);
750        maker.newColumn(Model.CLUSTER_NODES_CREATED_KEY, ColumnType.TIMESTAMP);
751        maker.table.addIndex(null, IndexType.UNIQUE, Model.CLUSTER_NODES_NODEID_KEY);
752        maker.postProcessClusterNodes();
753
754        maker = new TableMaker(Model.CLUSTER_INVALS_TABLE_NAME);
755        maker.newColumn(Model.CLUSTER_INVALS_NODEID_KEY, ColumnType.CLUSTERNODE);
756        maker.newColumn(Model.CLUSTER_INVALS_ID_KEY, ColumnType.NODEVAL);
757        maker.newColumn(Model.CLUSTER_INVALS_FRAGMENTS_KEY, ColumnType.CLUSTERFRAGS);
758        maker.newColumn(Model.CLUSTER_INVALS_KIND_KEY, ColumnType.TINYINT);
759        maker.table.addIndex(Model.CLUSTER_INVALS_NODEID_KEY);
760        maker.postProcessClusterInvalidations();
761    }
762
763    /**
764     * Creates the SQL for the table holding global repository information. This includes the id of the hierarchy root
765     * node.
766     */
767    protected void initRepositorySQL() {
768        TableMaker maker = new TableMaker(Model.REPOINFO_TABLE_NAME);
769        maker.newColumn(Model.MAIN_KEY, ColumnType.NODEIDFK);
770        maker.newColumn(Model.REPOINFO_REPONAME_KEY, ColumnType.SYSNAME);
771        maker.table.addIndex(null, IndexType.UNIQUE, Model.REPOINFO_REPONAME_KEY);
772        maker.postProcessRepository();
773    }
774
775    /**
776     * Creates the SQL for the table holding hierarchy information.
777     */
778    protected void initHierarchySQL() {
779        TableMaker maker = new TableMaker(Model.HIER_TABLE_NAME);
780        // if (separateMainTable)
781        // maker.newColumn(model.MAIN_KEY, ColumnType.NODEIDFK);
782        maker.newColumn(Model.MAIN_KEY, ColumnType.NODEID);
783        maker.newColumn(Model.HIER_PARENT_KEY, ColumnType.NODEIDFKNULL);
784        maker.newColumn(Model.HIER_CHILD_POS_KEY, ColumnType.INTEGER);
785        maker.newColumn(Model.HIER_CHILD_NAME_KEY, ColumnType.STRING);
786        maker.newColumn(Model.HIER_CHILD_ISPROPERTY_KEY, ColumnType.BOOLEAN); // notnull
787        // if (!separateMainTable)
788        maker.newFragmentFields();
789        maker.postProcess();
790        maker.postProcessHierarchy();
791        // if (!separateMainTable)
792        // maker.postProcessIdGeneration();
793
794        maker.table.addIndex(Model.HIER_PARENT_KEY);
795        maker.table.addIndex(Model.HIER_PARENT_KEY, Model.HIER_CHILD_NAME_KEY);
796        // don't index parent+name+isprop, a simple isprop scan will suffice
797        maker.table.addIndex(Model.MAIN_PRIMARY_TYPE_KEY);
798        maker.table.addIndex(Model.MAIN_RETAIN_UNTIL_KEY);
799
800        if (model.getRepositoryDescriptor().getSoftDeleteEnabled()) {
801            maker.table.addIndex(Model.MAIN_IS_DELETED_KEY);
802        }
803    }
804
805    protected void initSelectDescendantsSQL() {
806        Table hierTable = database.getTable(Model.HIER_TABLE_NAME);
807        Table proxyTable = null;
808        if (proxiesEnabled) {
809            proxyTable = database.getTable(Model.PROXY_TABLE_NAME);
810        }
811        Column mainColumn = hierTable.getColumn(Model.MAIN_KEY);
812        List<Column> whatCols = new ArrayList<>(Arrays.asList(mainColumn, //
813                hierTable.getColumn(Model.HIER_PARENT_KEY), //
814                hierTable.getColumn(Model.MAIN_PRIMARY_TYPE_KEY), //
815                hierTable.getColumn(Model.HIER_CHILD_ISPROPERTY_KEY), //
816                hierTable.getColumn(Model.MAIN_RETAIN_UNTIL_KEY), //
817                hierTable.getColumn(Model.MAIN_HAS_LEGAL_HOLD_KEY), //
818                hierTable.getColumn(Model.MAIN_IS_RETENTION_ACTIVE_KEY)));
819        if (proxiesEnabled) {
820            whatCols.add(proxyTable.getColumn(Model.PROXY_VERSIONABLE_KEY));
821            whatCols.add(proxyTable.getColumn(Model.PROXY_TARGET_KEY));
822        }
823        // no mixins, not used to decide if we have a version or proxy
824        String whats = whatCols.stream().map(Column::getFullQuotedName).collect(Collectors.joining(", "));
825        Select select = new Select(null);
826        select.setWhat(whats);
827        String from = hierTable.getQuotedName();
828        if (proxiesEnabled) {
829            from += " LEFT JOIN " + proxyTable.getQuotedName() + " ON " + mainColumn.getFullQuotedName() + " = "
830                    + proxyTable.getColumn(Model.MAIN_KEY).getFullQuotedName();
831        }
832        select.setFrom(from);
833        String where = dialect.getInTreeSql(mainColumn.getFullQuotedName(), null);
834        where += getSoftDeleteClause(Model.HIER_TABLE_NAME);
835        select.setWhere(where);
836        selectDescendantsInfoSql = select.getStatement();
837        selectDescendantsInfoWhatColumns = whatCols;
838    }
839
840    /**
841     * Creates the SQL for the table holding ancestors information.
842     * <p>
843     * This table holds trigger-updated information extracted from the recursive parent-child relationship in the
844     * hierarchy table.
845     */
846    protected void initAncestorsSQL() {
847        TableMaker maker = new TableMaker(Model.ANCESTORS_TABLE_NAME);
848        maker.newColumn(Model.MAIN_KEY, ColumnType.NODEIDFKMUL);
849        maker.newColumn(Model.ANCESTORS_ANCESTOR_KEY, ColumnType.NODEARRAY);
850    }
851
852    /**
853     * Creates the SQL for one fragment (simple or collection).
854     */
855    protected void initFragmentSQL(String tableName) {
856        TableMaker maker = new TableMaker(tableName);
857        ColumnType type;
858        if (tableName.equals(Model.HIER_TABLE_NAME)) {
859            type = ColumnType.NODEID;
860        } else if (tableName.equals(Model.LOCK_TABLE_NAME)) {
861            type = ColumnType.NODEIDPK; // no foreign key to hierarchy
862        } else if (model.isCollectionFragment(tableName)) {
863            type = ColumnType.NODEIDFKMUL;
864        } else {
865            type = ColumnType.NODEIDFK;
866        }
867        maker.newColumn(Model.MAIN_KEY, type);
868        maker.newFragmentFields();
869        maker.postProcess();
870        // if (isMain)
871        // maker.postProcessIdGeneration();
872    }
873
874    protected void initSelections() {
875        for (SelectionType selType : SelectionType.values()) {
876            if (!proxiesEnabled && selType.tableName.equals(Model.PROXY_TABLE_NAME)) {
877                continue;
878            }
879            selections.put(selType, new SQLInfoSelection(selType));
880        }
881    }
882
883    // ----- prepare one table -----
884
885    protected class TableMaker {
886
887        private final String tableName;
888
889        private final Table table;
890
891        private final String orderBy;
892
893        protected TableMaker(String tableName) {
894            this.tableName = tableName;
895            table = database.addTable(tableName);
896            orderBy = model.getCollectionOrderBy(tableName);
897        }
898
899        protected void newFragmentFields() {
900            Map<String, ColumnType> keysType = model.getFragmentKeysType(tableName);
901            for (Entry<String, ColumnType> entry : keysType.entrySet()) {
902                newColumn(entry.getKey(), entry.getValue());
903            }
904        }
905
906        protected Column newColumn(String columnName, ColumnType type) {
907            Column column = table.addColumn(columnName, type, columnName, model);
908            if (type == ColumnType.NODEID) {
909                // column.setIdentity(true); if idGenPolicy identity
910                column.setNullable(false);
911                column.setPrimary(true);
912            }
913            if (type == ColumnType.NODEIDFK || type == ColumnType.NODEIDPK) {
914                column.setNullable(false);
915                column.setPrimary(true);
916            }
917            if (type == ColumnType.NODEIDFKMUL) {
918                column.setNullable(false);
919                table.addIndex(columnName);
920            }
921            if (type == ColumnType.NODEIDFK || type == ColumnType.NODEIDFKNP || type == ColumnType.NODEIDFKNULL
922                    || type == ColumnType.NODEIDFKMUL) {
923                column.setReferences(database.getTable(Model.HIER_TABLE_NAME), Model.MAIN_KEY);
924            }
925            return column;
926        }
927
928        // ----------------------- post processing -----------------------
929
930        protected void postProcessClusterNodes() {
931            Collection<Column> columns = table.getColumns();
932            Insert insert = new Insert(table);
933            for (Column column : columns) {
934                insert.addColumn(column);
935            }
936            createClusterNodeSql = insert.getStatement();
937            createClusterNodeColumns = new ArrayList<>(columns);
938
939            Delete delete = new Delete(table);
940            Column column = table.getColumn(Model.CLUSTER_NODES_NODEID_KEY);
941            delete.setWhere(column.getQuotedName() + " = ?");
942            deleteClusterNodeSql = delete.getStatement();
943            deleteClusterNodeColumn = column;
944        }
945
946        protected void postProcessClusterInvalidations() {
947            clusterInvalidationsColumns = Arrays.asList(table.getColumn(Model.CLUSTER_INVALS_NODEID_KEY),
948                    table.getColumn(Model.CLUSTER_INVALS_ID_KEY), table.getColumn(Model.CLUSTER_INVALS_FRAGMENTS_KEY),
949                    table.getColumn(Model.CLUSTER_INVALS_KIND_KEY));
950
951            Delete delete = new Delete(table);
952            Column column = table.getColumn(Model.CLUSTER_INVALS_NODEID_KEY);
953            delete.setWhere(column.getQuotedName() + " = ?");
954            deleteClusterInvalsSql = delete.getStatement();
955            deleteClusterInvalsColumn = column;
956        }
957
958        protected void postProcessRepository() {
959            postProcessRootIdSelect();
960            postProcessInsert();
961        }
962
963        protected void postProcessRootIdSelect() {
964            String what = null;
965            String where = null;
966            for (Column column : table.getColumns()) {
967                String key = column.getKey();
968                String qname = column.getQuotedName();
969                if (key.equals(Model.MAIN_KEY)) {
970                    what = qname;
971                    selectRootIdWhatColumn = column;
972                } else if (key.equals(Model.REPOINFO_REPONAME_KEY)) {
973                    where = qname + " = ?";
974                } else {
975                    throw new RuntimeException(column.toString());
976                }
977            }
978            Select select = new Select(table);
979            select.setWhat(what);
980            select.setFrom(table.getQuotedName());
981            select.setWhere(where);
982            selectRootIdSql = select.getStatement();
983        }
984
985        /**
986         * Precompute what we can from the information available for a regular schema table, or a collection table.
987         */
988        protected void postProcess() {
989            postProcessSelectById();
990            postProcessInsert();
991            postProcessDelete();
992            postProcessCopy();
993        }
994
995        /**
996         * Additional SQL for the hierarchy table.
997         */
998        protected void postProcessHierarchy() {
999            postProcessSelectChildrenIdsAndTypes();
1000        }
1001
1002        protected void postProcessSelectById() {
1003            String[] orderBys = orderBy == null ? NO_ORDER_BY : new String[] { orderBy, ORDER_ASC };
1004            SQLInfoSelect select = makeSelect(table, orderBys, Model.MAIN_KEY);
1005            selectFragmentById.put(tableName, select);
1006        }
1007
1008        protected void postProcessSelectChildrenIdsAndTypes() {
1009            List<Column> whatColumns = new ArrayList<>(2);
1010            List<String> whats = new ArrayList<>(2);
1011            Column column = table.getColumn(Model.MAIN_KEY);
1012            whatColumns.add(column);
1013            whats.add(column.getQuotedName());
1014            column = table.getColumn(Model.MAIN_PRIMARY_TYPE_KEY);
1015            whatColumns.add(column);
1016            whats.add(column.getQuotedName());
1017            column = table.getColumn(Model.MAIN_MIXIN_TYPES_KEY);
1018            whatColumns.add(column);
1019            whats.add(column.getQuotedName());
1020            column = table.getColumn(Model.MAIN_IS_RECORD_KEY);
1021            whatColumns.add(column);
1022            whats.add(column.getQuotedName());
1023            Select select = new Select(table);
1024            select.setWhat(String.join(", ", whats));
1025            select.setFrom(table.getQuotedName());
1026            String where = table.getColumn(Model.HIER_PARENT_KEY).getQuotedName() + " = ?"
1027                    + getSoftDeleteClause(tableName);
1028            select.setWhere(where);
1029            selectChildrenIdsAndTypesSql = select.getStatement();
1030
1031            // Build the exclude from copy part of the where clause
1032            SchemaManager schemaManager = Framework.getService(SchemaManager.class);
1033            String excludedTypes = schemaManager.getSpecialDocumentTypes()
1034                                                .stream()
1035                                                .collect(Collectors.joining("', '"));
1036            if (!excludedTypes.isEmpty()) {
1037                String excludeSpecialChildrenClause = " AND "
1038                        + table.getColumn(Model.MAIN_PRIMARY_TYPE_KEY).getQuotedName() + " NOT IN ('" + excludedTypes
1039                        + "')";
1040                select.setWhere(where + excludeSpecialChildrenClause);
1041            }
1042            selectChildrenIdsAndTypesSqlExcludeSpecialChildren = select.getStatement();
1043
1044            selectChildrenIdsAndTypesWhatColumns = whatColumns;
1045            // now only complex properties
1046            String isComplexClause = table.getColumn(Model.HIER_CHILD_ISPROPERTY_KEY).getQuotedName() + " = "
1047                    + dialect.toBooleanValueString(true);
1048            String whereAnd = where + " AND " + isComplexClause;
1049            select.setWhere(whereAnd);
1050            selectComplexChildrenIdsAndTypesSql = select.getStatement();
1051            // now only complex properties include special children
1052            if (!excludedTypes.isEmpty()) {
1053                String whereAndOr = where + " AND ( " + isComplexClause + " OR "
1054                        + table.getColumn(Model.MAIN_PRIMARY_TYPE_KEY).getQuotedName() + " IN ('"
1055                        + excludedTypes + "') )";
1056                select.setWhere(whereAndOr);
1057            }
1058            selectComplexChildrenIdsAndTypesSqlIncludeSpecialChildren = select.getStatement();
1059        }
1060
1061        // TODO optimize multiple inserts into one statement for collections
1062        protected void postProcessInsert() {
1063            // insert (implicitly auto-generated sequences not included)
1064            Collection<Column> columns = table.getColumns();
1065            List<Column> insertColumns = new ArrayList<>(columns.size());
1066            Insert insert = new Insert(table);
1067            for (Column column : columns) {
1068                if (column.isIdentity()) {
1069                    // identity column is never inserted
1070                    continue;
1071                }
1072                insertColumns.add(column);
1073                insert.addColumn(column);
1074            }
1075            insertSqlMap.put(tableName, insert.getStatement());
1076            insertColumnsMap.put(tableName, insertColumns);
1077        }
1078
1079        protected void postProcessDelete() {
1080            Delete delete = new Delete(table);
1081            String wheres = table.getColumns()
1082                    .stream()
1083                    .filter(col -> Model.MAIN_KEY.equals(col.getKey()))
1084                    .map(col -> col.getQuotedName() + " = ?")
1085                    .collect(Collectors.joining(" AND "));
1086            delete.setWhere(wheres);
1087            deleteSqlMap.put(tableName, delete.getStatement());
1088        }
1089
1090        // copy of a fragment
1091        // INSERT INTO foo (id, x, y) SELECT ?, x, y FROM foo WHERE id = ?
1092        protected void postProcessCopy() {
1093            Collection<Column> columns = table.getColumns();
1094            List<String> selectWhats = new ArrayList<>(columns.size());
1095            Column copyIdColumn = table.getColumn(Model.MAIN_KEY);
1096            Insert insert = new Insert(table);
1097            for (Column column : columns) {
1098                if (column.isIdentity()) {
1099                    // identity column is never copied
1100                    continue;
1101                }
1102                insert.addColumn(column);
1103                if (column == copyIdColumn) {
1104                    // explicit value
1105                    selectWhats.add("?");
1106                } else {
1107                    // otherwise copy value
1108                    selectWhats.add(column.getQuotedName());
1109                }
1110            }
1111            Select select = new Select(table);
1112            select.setWhat(String.join(", ", selectWhats));
1113            select.setFrom(table.getQuotedName());
1114            select.setWhere(copyIdColumn.getQuotedName() + " = ?");
1115            insert.setValues(select.getStatement());
1116            copySqlMap.put(tableName, insert.getStatement());
1117            copyIdColumnMap.put(tableName, copyIdColumn);
1118        }
1119
1120    }
1121
1122    public static class SQLInfoSelect {
1123
1124        public final String sql;
1125
1126        public final List<Column> whatColumns;
1127
1128        public final MapMaker mapMaker;
1129
1130        public final List<Column> whereColumns;
1131
1132        public final List<Column> opaqueColumns;
1133
1134        /**
1135         * Standard select for given columns.
1136         */
1137        public SQLInfoSelect(String sql, List<Column> whatColumns, List<Column> whereColumns,
1138                List<Column> opaqueColumns) {
1139            this(sql, whatColumns, null, whereColumns, opaqueColumns);
1140        }
1141
1142        /**
1143         * Select where some column keys may be aliased, and some columns may be computed. The {@link MapMaker} is used
1144         * by the queryAndFetch() method.
1145         */
1146        public SQLInfoSelect(String sql, MapMaker mapMaker) {
1147            this(sql, null, mapMaker, null, null);
1148        }
1149
1150        public SQLInfoSelect(String sql, List<Column> whatColumns, MapMaker mapMaker, List<Column> whereColumns,
1151                List<Column> opaqueColumns) {
1152            this.sql = sql;
1153            this.whatColumns = whatColumns;
1154            this.mapMaker = mapMaker;
1155            this.whereColumns = whereColumns == null ? null : new ArrayList<>(whereColumns);
1156            this.opaqueColumns = opaqueColumns == null ? null : new ArrayList<>(opaqueColumns);
1157        }
1158    }
1159
1160    /**
1161     * Info about how to do the query to get a {@link Selection}.
1162     */
1163    public class SQLInfoSelection {
1164
1165        public final SelectionType type;
1166
1167        public final SQLInfoSelect selectAll;
1168
1169        public final SQLInfoSelect selectFiltered;
1170
1171        public SQLInfoSelection(SelectionType selType) {
1172            this.type = selType;
1173            Table table = database.getTable(selType.tableName);
1174            SQLInfoSelect selectAll;
1175            SQLInfoSelect selectFiltered;
1176            String from = table.getQuotedName();
1177            List<String> clauses;
1178            if (selType.tableName.equals(Model.HIER_TABLE_NAME)) {
1179                // clause already added by makeSelect
1180                clauses = null;
1181            } else {
1182                Table hierTable = database.getTable(Model.HIER_TABLE_NAME);
1183                Join join = new Join(Join.INNER, hierTable.getQuotedName(), null, null,
1184                        hierTable.getColumn(Model.MAIN_KEY), table.getColumn(Model.MAIN_KEY));
1185                from += join.toSql(dialect);
1186                String clause = getSoftDeleteClause();
1187                clauses = clause == null ? null : Collections.singletonList(clause);
1188            }
1189            if (selType.criterionKey == null) {
1190                selectAll = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey);
1191                selectFiltered = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey, selType.filterKey);
1192            } else {
1193                selectAll = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey, selType.criterionKey);
1194                selectFiltered = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey, selType.filterKey,
1195                        selType.criterionKey);
1196            }
1197            this.selectAll = selectAll;
1198            this.selectFiltered = selectFiltered;
1199        }
1200
1201        /**
1202         * Select selection ids for multiple values.
1203         */
1204        public SQLInfoSelect getSelectSelectionIds(int nids) {
1205            Table table = database.getTable(type.tableName);
1206            String from = table.getQuotedName();
1207            Table hierTable = database.getTable(Model.HIER_TABLE_NAME);
1208            Join join = new Join(Join.INNER, hierTable.getQuotedName(), null, null,
1209                    hierTable.getColumn(Model.MAIN_KEY), table.getColumn(Model.MAIN_KEY));
1210            from += join.toSql(dialect);
1211
1212            Column whatColumn = table.getColumn(Model.MAIN_KEY);
1213            Column whereColumn = table.getColumn(type.selKey);
1214            StringBuilder sb = new StringBuilder(whereColumn.getQuotedName());
1215            sb.append(" IN (");
1216            for (int i = 0; i < nids; i++) {
1217                if (i != 0) {
1218                    sb.append(", ");
1219                }
1220                sb.append('?');
1221            }
1222            sb.append(')');
1223            sb.append(getSoftDeleteClause(Model.HIER_TABLE_NAME));
1224            Select select = new Select(table);
1225            select.setWhat(whatColumn.getFullQuotedName());
1226            select.setFrom(from);
1227            select.setWhere(sb.toString());
1228            return new SQLInfoSelect(select.getStatement(), Collections.singletonList(whatColumn),
1229                    Collections.singletonList(whereColumn), null);
1230        }
1231    }
1232
1233    /**
1234     * Knows how to build a result map for a row given a {@link ResultSet}. This abstraction may be used to compute some
1235     * values on the fly.
1236     */
1237    public interface MapMaker {
1238        Map<String, Serializable> makeMap(ResultSet rs) throws SQLException;
1239    }
1240
1241    /**
1242     * Builds the map from a result set given a list of columns and column keys.
1243     */
1244    public static class ColumnMapMaker implements MapMaker {
1245        public final List<Column> columns;
1246
1247        public final List<String> keys;
1248
1249        public ColumnMapMaker(List<Column> columns) {
1250            this.columns = columns;
1251            this.keys = columns.stream().map(Column::getKey).collect(Collectors.toList());
1252        }
1253
1254        public ColumnMapMaker(List<Column> columns, List<String> keys) {
1255            this.columns = columns;
1256            this.keys = keys;
1257        }
1258
1259        @Override
1260        public Map<String, Serializable> makeMap(ResultSet rs) throws SQLException {
1261            Map<String, Serializable> map = new HashMap<>();
1262            int i = 1;
1263            for (Column column : columns) {
1264                String key = keys.get(i - 1);
1265                Serializable value = column.getFromResultSet(rs, i++);
1266                if (NXQL.ECM_UUID.equals(key) || NXQL.ECM_PARENTID.equals(key)) {
1267                    value = String.valueOf(value); // idToString
1268                }
1269                map.put(key, value);
1270            }
1271            return map;
1272        }
1273    }
1274
1275    private static String[] NO_ORDER_BY = new String[0];
1276
1277    /**
1278     * Basic SELECT x, y, z FROM table WHERE a = ? AND b = ?
1279     * <p>
1280     * with optional ORDER BY x, y DESC
1281     */
1282    public SQLInfoSelect makeSelect(Table table, String[] orderBys, String... freeColumns) {
1283        return makeSelect(table, null, null, orderBys, freeColumns);
1284    }
1285
1286    /**
1287     * Same as above but the FROM can be passed in, to allow JOINs.
1288     */
1289    public SQLInfoSelect makeSelect(Table table, String from, List<String> clauses, String[] orderBys,
1290            String... freeColumns) {
1291        boolean fullQuotedName = from != null;
1292        List<String> freeColumnsList = Arrays.asList(freeColumns);
1293        List<Column> whatColumns = new LinkedList<>();
1294        List<Column> whereColumns = new LinkedList<>();
1295        List<Column> opaqueColumns = new LinkedList<>();
1296        List<String> whats = new LinkedList<>();
1297        List<String> wheres = new LinkedList<>();
1298        for (Column column : table.getColumns()) {
1299            String qname = fullQuotedName ? column.getFullQuotedName() : column.getQuotedName();
1300            if (freeColumnsList.contains(column.getKey())) {
1301                whereColumns.add(column);
1302                wheres.add(qname + " = ?");
1303            } else if (column.isOpaque()) {
1304                opaqueColumns.add(column);
1305            } else {
1306                whatColumns.add(column);
1307                whats.add(qname);
1308            }
1309        }
1310        if (whats.isEmpty()) {
1311            // only opaque columns, don't generate an illegal SELECT
1312            whats.add(table.getColumn(Model.MAIN_KEY).getQuotedName());
1313        }
1314        if (clauses != null) {
1315            wheres.addAll(clauses);
1316        }
1317        Select select = new Select(table);
1318        select.setWhat(String.join(", ", whats));
1319        if (from == null) {
1320            from = table.getQuotedName();
1321        }
1322        select.setFrom(from);
1323        String where = String.join(" AND ", wheres) + getSoftDeleteClause(table.getKey());
1324        select.setWhere(where);
1325        List<String> orders = new LinkedList<>();
1326        for (int i = 0; i < orderBys.length; i++) {
1327            String name = orderBys[i++];
1328            String ascdesc = orderBys[i].equals(ORDER_DESC) ? " " + ORDER_DESC : "";
1329            Column col = table.getColumn(name);
1330            String qcol = fullQuotedName ? col.getFullQuotedName() : col.getQuotedName();
1331            orders.add(qcol + ascdesc);
1332        }
1333        select.setOrderBy(String.join(", ", orders));
1334        return new SQLInfoSelect(select.getStatement(), whatColumns, whereColumns,
1335                opaqueColumns.isEmpty() ? null : opaqueColumns);
1336    }
1337
1338    public void initSQLStatements(Map<String, Serializable> testProps, List<String> sqlInitFiles) throws IOException {
1339        sqlStatements = new HashMap<>();
1340        SQLStatement.read(dialect.getSQLStatementsFilename(), sqlStatements);
1341        if (sqlInitFiles != null) {
1342            for (String filename : sqlInitFiles) {
1343                SQLStatement.read(filename, sqlStatements);
1344            }
1345        }
1346        if (!testProps.isEmpty()) {
1347            SQLStatement.read(dialect.getTestSQLStatementsFilename(), sqlStatements, true); // DDL time
1348        }
1349        sqlStatementsProperties = dialect.getSQLStatementsProperties(model, database);
1350        if (!testProps.isEmpty()) {
1351            sqlStatementsProperties.putAll(testProps);
1352        }
1353    }
1354
1355    /**
1356     * Executes the SQL statements for the given category.
1357     */
1358    public void executeSQLStatements(String category, String ddlMode, Connection connection, JDBCLogger logger,
1359            ListCollector ddlCollector) throws SQLException {
1360        List<SQLStatement> statements = sqlStatements.get(category);
1361        if (statements != null) {
1362            SQLStatement.execute(statements, ddlMode, sqlStatementsProperties, dialect, connection, logger,
1363                    ddlCollector);
1364        }
1365    }
1366
1367    public int getMaximumArgsForIn() {
1368        return dialect.getMaximumArgsForIn();
1369    }
1370
1371}