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