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        whatColumns.add(hierTable.getColumn(Model.MAIN_IS_RETENTION_ACTIVE_KEY));
489        Table proxyTable = null;
490        if (proxiesEnabled) {
491            proxyTable = database.getTable(Model.PROXY_TABLE_NAME);
492            whatColumns.add(proxyTable.getColumn(Model.PROXY_TARGET_KEY));
493            whatColumns.add(proxyTable.getColumn(Model.PROXY_VERSIONABLE_KEY));
494        }
495        String selectWhats = whatColumns.stream().map(Column::getFullQuotedName).collect(Collectors.joining(", "));
496        Select select = new Select(null);
497        select.setWhat(selectWhats);
498        String from = hierTable.getQuotedName();
499        if (proxiesEnabled) {
500            from += " LEFT JOIN " + proxyTable.getQuotedName() + " ON " + mainColumn.getFullQuotedName() + " = "
501                    + proxyTable.getColumn(Model.MAIN_KEY).getFullQuotedName();
502        }
503        select.setFrom(from);
504        Column whereColumn = hierTable.getColumn(Model.HIER_PARENT_KEY);
505        StringBuilder wherebuf = new StringBuilder(whereColumn.getFullQuotedName());
506        if (nids == 1) {
507            wherebuf.append(" = ?");
508        } else {
509            wherebuf.append(" IN (");
510            for (int i = 0; i < nids; i++) {
511                if (i != 0) {
512                    wherebuf.append(", ");
513                }
514                wherebuf.append('?');
515            }
516            wherebuf.append(')');
517        }
518        wherebuf.append(" AND ");
519        wherebuf.append(hierTable.getColumn(Model.HIER_CHILD_ISPROPERTY_KEY).getFullQuotedName());
520        wherebuf.append(" = ").append(dialect.toBooleanValueString(false)); // not complex
521        wherebuf.append(getSoftDeleteClause(Model.HIER_TABLE_NAME));
522        select.setWhere(wherebuf.toString());
523        return new SQLInfoSelect(select.getStatement(), whatColumns, Collections.singletonList(whereColumn), null);
524    }
525
526    // ----- delete -----
527
528    /**
529     * Returns the SQL {@code DELETE} to delete a row. The primary key columns are free parameters.
530     *
531     * @param tableName the table name
532     * @return the SQL {@code DELETE} statement
533     */
534    public String getDeleteSql(String tableName) {
535        return deleteSqlMap.get(tableName);
536    }
537
538    /**
539     * Returns the SQL {@code DELETE} to delete several rows. The primary key columns are free parameters.
540     *
541     * @param tableName the table name
542     * @param n the number of rows to delete
543     * @return the SQL {@code DELETE} statement with a {@code IN} for the keys
544     */
545    public String getDeleteSql(String tableName, int n) {
546        Table table = database.getTable(tableName);
547        Delete delete = new Delete(table);
548        String where = null;
549        for (Column column : table.getColumns()) {
550            if (column.getKey().equals(Model.MAIN_KEY)) {
551                StringBuilder buf = new StringBuilder();
552                buf.append(column.getQuotedName());
553                if (n == 1) {
554                    buf.append(" = ?");
555                } else {
556                    buf.append(" IN (");
557                    for (int i = 0; i < n; i++) {
558                        if (i > 0) {
559                            buf.append(", ");
560                        }
561                        buf.append("?");
562                    }
563                    buf.append(")");
564                }
565                where = buf.toString();
566            }
567        }
568        delete.setWhere(where);
569        return delete.getStatement();
570    }
571
572    /**
573     * Returns the SQL to soft-delete several rows. The array of ids and the time are free parameters.
574     *
575     * @return the SQL statement
576     */
577    public String getSoftDeleteSql() {
578        return dialect.getSoftDeleteSql();
579    }
580
581    /**
582     * Returns the SQL to clean (hard-delete) soft-deleted rows. The max and beforeTime are free parameters.
583     *
584     * @return the SQL statement
585     */
586    public String getSoftDeleteCleanupSql() {
587        return dialect.getSoftDeleteCleanupSql();
588    }
589
590    // ----- copy -----
591
592    public SQLInfoSelect getCopyHier(boolean explicitName, boolean resetVersion) {
593        Table table = database.getTable(Model.HIER_TABLE_NAME);
594        Collection<Column> columns = table.getColumns();
595        List<String> selectWhats = new ArrayList<>(columns.size());
596        List<Column> selectWhatColumns = new ArrayList<>(5);
597        Insert insert = new Insert(table);
598        for (Column column : columns) {
599            if (column.isIdentity()) {
600                // identity column is never copied
601                continue;
602            }
603            insert.addColumn(column);
604            String quotedName = column.getQuotedName();
605            String key = column.getKey();
606            if (key.equals(Model.MAIN_KEY) //
607                    || key.equals(Model.HIER_PARENT_KEY) //
608                    || key.equals(Model.MAIN_BASE_VERSION_KEY) //
609                    || key.equals(Model.MAIN_CHECKED_IN_KEY) //
610                    || (key.equals(Model.MAIN_MINOR_VERSION_KEY) && resetVersion) //
611                    || (key.equals(Model.MAIN_MAJOR_VERSION_KEY) && resetVersion) //
612                    || (key.equals(Model.HIER_CHILD_NAME_KEY) && explicitName)) {
613                // explicit value set
614                selectWhats.add("?");
615                selectWhatColumns.add(column);
616            } else {
617                // otherwise copy value
618                selectWhats.add(quotedName);
619            }
620        }
621        Column whereColumn = table.getColumn(Model.MAIN_KEY);
622        Select select = new Select(null);
623        select.setFrom(table.getQuotedName());
624        select.setWhat(String.join(", ", selectWhats));
625        select.setWhere(whereColumn.getQuotedName() + " = ?");
626        insert.setValues(select.getStatement());
627        String sql = insert.getStatement();
628        return new SQLInfoSelect(sql, selectWhatColumns, Collections.singletonList(whereColumn), null);
629    }
630
631    public String getCopySql(String tableName) {
632        return copySqlMap.get(tableName);
633    }
634
635    public Column getCopyIdColumn(String tableName) {
636        return copyIdColumnMap.get(tableName);
637    }
638
639    // ----- prepare everything -----
640
641    /**
642     * Creates all the sql from the models.
643     */
644    protected void initSQL() {
645
646        // structural tables
647        if (model.getRepositoryDescriptor().getClusteringEnabled()) {
648            if (!dialect.isClusteringSupported()) {
649                throw new NuxeoException("Clustering not supported for " + dialect.getClass().getSimpleName());
650            }
651            initClusterSQL();
652        }
653        initHierarchySQL();
654        initRepositorySQL();
655        if (dialect.supportsAncestorsTable()) {
656            initAncestorsSQL();
657        }
658
659        for (String tableName : model.getFragmentNames()) {
660            if (tableName.equals(Model.HIER_TABLE_NAME)) {
661                continue;
662            }
663            initFragmentSQL(tableName);
664        }
665
666        /*
667         * versions
668         */
669
670        Table hierTable = database.getTable(Model.HIER_TABLE_NAME);
671        Table versionTable = database.getTable(Model.VERSION_TABLE_NAME);
672        hierTable.addIndex(Model.MAIN_IS_VERSION_KEY);
673        versionTable.addIndex(Model.VERSION_VERSIONABLE_KEY);
674        // don't index series+label, a simple label scan will suffice
675
676        /*
677         * proxies
678         */
679
680        if (proxiesEnabled) {
681            Table proxyTable = database.getTable(Model.PROXY_TABLE_NAME);
682            proxyTable.addIndex(Model.PROXY_VERSIONABLE_KEY);
683            proxyTable.addIndex(Model.PROXY_TARGET_KEY);
684        }
685
686        initSelectDescendantsSQL();
687
688        /*
689         * fulltext
690         */
691        if (!model.getRepositoryDescriptor().getFulltextDescriptor().getFulltextSearchDisabled()) {
692            Table table = database.getTable(Model.FULLTEXT_TABLE_NAME);
693            FulltextConfiguration fulltextConfiguration = model.getFulltextConfiguration();
694            if (fulltextConfiguration.indexNames.size() > 1 && !dialect.supportsMultipleFulltextIndexes()) {
695                String msg = String.format("SQL database supports only one fulltext index, but %d are configured: %s",
696                        fulltextConfiguration.indexNames.size(), fulltextConfiguration.indexNames);
697                throw new NuxeoException(msg);
698            }
699            for (String indexName : fulltextConfiguration.indexNames) {
700                String suffix = model.getFulltextIndexSuffix(indexName);
701                int ftic = dialect.getFulltextIndexedColumns();
702                if (ftic == 1) {
703                    table.addIndex(indexName, IndexType.FULLTEXT, Model.FULLTEXT_FULLTEXT_KEY + suffix);
704                } else if (ftic == 2) {
705                    table.addIndex(indexName, IndexType.FULLTEXT, Model.FULLTEXT_SIMPLETEXT_KEY + suffix,
706                            Model.FULLTEXT_BINARYTEXT_KEY + suffix);
707                }
708            }
709        }
710
711        /*
712         * binary columns for GC
713         */
714        for (Entry<String, List<String>> e : model.getBinaryPropertyInfos().entrySet()) {
715            String tableName = e.getKey();
716            Table table = database.getTable(tableName);
717            for (String key : e.getValue()) {
718                Select select = new Select(table);
719                Column col = table.getColumn(key); // key = name for now
720                select.setWhat("DISTINCT " + col.getQuotedName());
721                select.setFrom(table.getQuotedName());
722                getBinariesSql.add(select.getStatement());
723                // in the result column we want the digest, not the binary
724                Column resCol = new Column(table, null, ColumnType.STRING, null);
725                getBinariesColumns.add(resCol);
726            }
727        }
728    }
729
730    protected void initClusterSQL() {
731        TableMaker maker = new TableMaker(Model.CLUSTER_NODES_TABLE_NAME);
732        maker.newColumn(Model.CLUSTER_NODES_NODEID_KEY, ColumnType.CLUSTERNODE);
733        maker.newColumn(Model.CLUSTER_NODES_CREATED_KEY, ColumnType.TIMESTAMP);
734        maker.table.addIndex(null, IndexType.UNIQUE, Model.CLUSTER_NODES_NODEID_KEY);
735        maker.postProcessClusterNodes();
736
737        maker = new TableMaker(Model.CLUSTER_INVALS_TABLE_NAME);
738        maker.newColumn(Model.CLUSTER_INVALS_NODEID_KEY, ColumnType.CLUSTERNODE);
739        maker.newColumn(Model.CLUSTER_INVALS_ID_KEY, ColumnType.NODEVAL);
740        maker.newColumn(Model.CLUSTER_INVALS_FRAGMENTS_KEY, ColumnType.CLUSTERFRAGS);
741        maker.newColumn(Model.CLUSTER_INVALS_KIND_KEY, ColumnType.TINYINT);
742        maker.table.addIndex(Model.CLUSTER_INVALS_NODEID_KEY);
743        maker.postProcessClusterInvalidations();
744    }
745
746    /**
747     * Creates the SQL for the table holding global repository information. This includes the id of the hierarchy root
748     * node.
749     */
750    protected void initRepositorySQL() {
751        TableMaker maker = new TableMaker(Model.REPOINFO_TABLE_NAME);
752        maker.newColumn(Model.MAIN_KEY, ColumnType.NODEIDFK);
753        maker.newColumn(Model.REPOINFO_REPONAME_KEY, ColumnType.SYSNAME);
754        maker.table.addIndex(null, IndexType.UNIQUE, Model.REPOINFO_REPONAME_KEY);
755        maker.postProcessRepository();
756    }
757
758    /**
759     * Creates the SQL for the table holding hierarchy information.
760     */
761    protected void initHierarchySQL() {
762        TableMaker maker = new TableMaker(Model.HIER_TABLE_NAME);
763        // if (separateMainTable)
764        // maker.newColumn(model.MAIN_KEY, ColumnType.NODEIDFK);
765        maker.newColumn(Model.MAIN_KEY, ColumnType.NODEID);
766        Column column = maker.newColumn(Model.HIER_PARENT_KEY, ColumnType.NODEIDFKNULL);
767        maker.newColumn(Model.HIER_CHILD_POS_KEY, ColumnType.INTEGER);
768        maker.newColumn(Model.HIER_CHILD_NAME_KEY, ColumnType.STRING);
769        maker.newColumn(Model.HIER_CHILD_ISPROPERTY_KEY, ColumnType.BOOLEAN); // notnull
770        // if (!separateMainTable)
771        maker.newFragmentFields();
772        maker.postProcess();
773        maker.postProcessHierarchy();
774        // if (!separateMainTable)
775        // maker.postProcessIdGeneration();
776
777        maker.table.addIndex(Model.HIER_PARENT_KEY);
778        maker.table.addIndex(Model.HIER_PARENT_KEY, Model.HIER_CHILD_NAME_KEY);
779        // don't index parent+name+isprop, a simple isprop scan will suffice
780        maker.table.addIndex(Model.MAIN_PRIMARY_TYPE_KEY);
781
782        if (model.getRepositoryDescriptor().getSoftDeleteEnabled()) {
783            maker.table.addIndex(Model.MAIN_IS_DELETED_KEY);
784        }
785    }
786
787    protected void initSelectDescendantsSQL() {
788        Table hierTable = database.getTable(Model.HIER_TABLE_NAME);
789        Table proxyTable = null;
790        if (proxiesEnabled) {
791            proxyTable = database.getTable(Model.PROXY_TABLE_NAME);
792        }
793        Column mainColumn = hierTable.getColumn(Model.MAIN_KEY);
794        List<Column> whatCols = new ArrayList<>(Arrays.asList(mainColumn, //
795                hierTable.getColumn(Model.HIER_PARENT_KEY), //
796                hierTable.getColumn(Model.MAIN_PRIMARY_TYPE_KEY), //
797                hierTable.getColumn(Model.HIER_CHILD_ISPROPERTY_KEY), //
798                hierTable.getColumn(Model.MAIN_IS_RETENTION_ACTIVE_KEY)));
799        if (proxiesEnabled) {
800            whatCols.add(proxyTable.getColumn(Model.PROXY_VERSIONABLE_KEY));
801            whatCols.add(proxyTable.getColumn(Model.PROXY_TARGET_KEY));
802        }
803        // no mixins, not used to decide if we have a version or proxy
804        String whats = whatCols.stream().map(Column::getFullQuotedName).collect(Collectors.joining(", "));
805        Select select = new Select(null);
806        select.setWhat(whats);
807        String from = hierTable.getQuotedName();
808        if (proxiesEnabled) {
809            from += " LEFT JOIN " + proxyTable.getQuotedName() + " ON " + mainColumn.getFullQuotedName() + " = "
810                    + proxyTable.getColumn(Model.MAIN_KEY).getFullQuotedName();
811        }
812        select.setFrom(from);
813        String where = dialect.getInTreeSql(mainColumn.getFullQuotedName(), null);
814        where += getSoftDeleteClause(Model.HIER_TABLE_NAME);
815        select.setWhere(where);
816        selectDescendantsInfoSql = select.getStatement();
817        selectDescendantsInfoWhatColumns = whatCols;
818    }
819
820    /**
821     * Creates the SQL for the table holding ancestors information.
822     * <p>
823     * This table holds trigger-updated information extracted from the recursive parent-child relationship in the
824     * hierarchy table.
825     */
826    protected void initAncestorsSQL() {
827        TableMaker maker = new TableMaker(Model.ANCESTORS_TABLE_NAME);
828        maker.newColumn(Model.MAIN_KEY, ColumnType.NODEIDFKMUL);
829        maker.newColumn(Model.ANCESTORS_ANCESTOR_KEY, ColumnType.NODEARRAY);
830    }
831
832    /**
833     * Creates the SQL for one fragment (simple or collection).
834     */
835    protected void initFragmentSQL(String tableName) {
836        TableMaker maker = new TableMaker(tableName);
837        ColumnType type;
838        if (tableName.equals(Model.HIER_TABLE_NAME)) {
839            type = ColumnType.NODEID;
840        } else if (tableName.equals(Model.LOCK_TABLE_NAME)) {
841            type = ColumnType.NODEIDPK; // no foreign key to hierarchy
842        } else if (model.isCollectionFragment(tableName)) {
843            type = ColumnType.NODEIDFKMUL;
844        } else {
845            type = ColumnType.NODEIDFK;
846        }
847        maker.newColumn(Model.MAIN_KEY, type);
848        maker.newFragmentFields();
849        maker.postProcess();
850        // if (isMain)
851        // maker.postProcessIdGeneration();
852    }
853
854    protected void initSelections() {
855        for (SelectionType selType : SelectionType.values()) {
856            if (!proxiesEnabled && selType.tableName.equals(Model.PROXY_TABLE_NAME)) {
857                continue;
858            }
859            selections.put(selType, new SQLInfoSelection(selType));
860        }
861    }
862
863    // ----- prepare one table -----
864
865    protected class TableMaker {
866
867        private final String tableName;
868
869        private final Table table;
870
871        private final String orderBy;
872
873        protected TableMaker(String tableName) {
874            this.tableName = tableName;
875            table = database.addTable(tableName);
876            orderBy = model.getCollectionOrderBy(tableName);
877        }
878
879        protected void newFragmentFields() {
880            Map<String, ColumnType> keysType = model.getFragmentKeysType(tableName);
881            for (Entry<String, ColumnType> entry : keysType.entrySet()) {
882                newColumn(entry.getKey(), entry.getValue());
883            }
884        }
885
886        protected Column newColumn(String columnName, ColumnType type) {
887            Column column = table.addColumn(columnName, type, columnName, model);
888            if (type == ColumnType.NODEID) {
889                // column.setIdentity(true); if idGenPolicy identity
890                column.setNullable(false);
891                column.setPrimary(true);
892            }
893            if (type == ColumnType.NODEIDFK || type == ColumnType.NODEIDPK) {
894                column.setNullable(false);
895                column.setPrimary(true);
896            }
897            if (type == ColumnType.NODEIDFKMUL) {
898                column.setNullable(false);
899                table.addIndex(columnName);
900            }
901            if (type == ColumnType.NODEIDFK || type == ColumnType.NODEIDFKNP || type == ColumnType.NODEIDFKNULL
902                    || type == ColumnType.NODEIDFKMUL) {
903                column.setReferences(database.getTable(Model.HIER_TABLE_NAME), Model.MAIN_KEY);
904            }
905            return column;
906        }
907
908        // ----------------------- post processing -----------------------
909
910        protected void postProcessClusterNodes() {
911            Collection<Column> columns = table.getColumns();
912            Insert insert = new Insert(table);
913            for (Column column : columns) {
914                insert.addColumn(column);
915            }
916            createClusterNodeSql = insert.getStatement();
917            createClusterNodeColumns = new ArrayList<>(columns);
918
919            Delete delete = new Delete(table);
920            Column column = table.getColumn(Model.CLUSTER_NODES_NODEID_KEY);
921            delete.setWhere(column.getQuotedName() + " = ?");
922            deleteClusterNodeSql = delete.getStatement();
923            deleteClusterNodeColumn = column;
924        }
925
926        protected void postProcessClusterInvalidations() {
927            clusterInvalidationsColumns = Arrays.asList(table.getColumn(Model.CLUSTER_INVALS_NODEID_KEY),
928                    table.getColumn(Model.CLUSTER_INVALS_ID_KEY), table.getColumn(Model.CLUSTER_INVALS_FRAGMENTS_KEY),
929                    table.getColumn(Model.CLUSTER_INVALS_KIND_KEY));
930
931            Delete delete = new Delete(table);
932            Column column = table.getColumn(Model.CLUSTER_INVALS_NODEID_KEY);
933            delete.setWhere(column.getQuotedName() + " = ?");
934            deleteClusterInvalsSql = delete.getStatement();
935            deleteClusterInvalsColumn = column;
936        }
937
938        protected void postProcessRepository() {
939            postProcessRootIdSelect();
940            postProcessInsert();
941        }
942
943        protected void postProcessRootIdSelect() {
944            String what = null;
945            String where = null;
946            for (Column column : table.getColumns()) {
947                String key = column.getKey();
948                String qname = column.getQuotedName();
949                if (key.equals(Model.MAIN_KEY)) {
950                    what = qname;
951                    selectRootIdWhatColumn = column;
952                } else if (key.equals(Model.REPOINFO_REPONAME_KEY)) {
953                    where = qname + " = ?";
954                } else {
955                    throw new RuntimeException(column.toString());
956                }
957            }
958            Select select = new Select(table);
959            select.setWhat(what);
960            select.setFrom(table.getQuotedName());
961            select.setWhere(where);
962            selectRootIdSql = select.getStatement();
963        }
964
965        /**
966         * Precompute what we can from the information available for a regular schema table, or a collection table.
967         */
968        protected void postProcess() {
969            postProcessSelectById();
970            postProcessInsert();
971            postProcessDelete();
972            postProcessCopy();
973        }
974
975        /**
976         * Additional SQL for the hierarchy table.
977         */
978        protected void postProcessHierarchy() {
979            postProcessSelectChildrenIdsAndTypes();
980        }
981
982        protected void postProcessSelectById() {
983            String[] orderBys = orderBy == null ? NO_ORDER_BY : new String[] { orderBy, ORDER_ASC };
984            SQLInfoSelect select = makeSelect(table, orderBys, Model.MAIN_KEY);
985            selectFragmentById.put(tableName, select);
986        }
987
988        protected void postProcessSelectChildrenIdsAndTypes() {
989            List<Column> whatColumns = new ArrayList<>(2);
990            List<String> whats = new ArrayList<>(2);
991            Column column = table.getColumn(Model.MAIN_KEY);
992            whatColumns.add(column);
993            whats.add(column.getQuotedName());
994            column = table.getColumn(Model.MAIN_PRIMARY_TYPE_KEY);
995            whatColumns.add(column);
996            whats.add(column.getQuotedName());
997            column = table.getColumn(Model.MAIN_MIXIN_TYPES_KEY);
998            whatColumns.add(column);
999            whats.add(column.getQuotedName());
1000            Select select = new Select(table);
1001            select.setWhat(String.join(", ", whats));
1002            select.setFrom(table.getQuotedName());
1003            String where = table.getColumn(Model.HIER_PARENT_KEY).getQuotedName() + " = ?"
1004                    + getSoftDeleteClause(tableName);
1005            select.setWhere(where);
1006            selectChildrenIdsAndTypesSql = select.getStatement();
1007            selectChildrenIdsAndTypesWhatColumns = whatColumns;
1008            // now only complex properties
1009            where += " AND " + table.getColumn(Model.HIER_CHILD_ISPROPERTY_KEY).getQuotedName() + " = "
1010                    + dialect.toBooleanValueString(true);
1011            select.setWhere(where);
1012            selectComplexChildrenIdsAndTypesSql = select.getStatement();
1013        }
1014
1015        // TODO optimize multiple inserts into one statement for collections
1016        protected void postProcessInsert() {
1017            // insert (implicitly auto-generated sequences not included)
1018            Collection<Column> columns = table.getColumns();
1019            List<Column> insertColumns = new ArrayList<>(columns.size());
1020            Insert insert = new Insert(table);
1021            for (Column column : columns) {
1022                if (column.isIdentity()) {
1023                    // identity column is never inserted
1024                    continue;
1025                }
1026                insertColumns.add(column);
1027                insert.addColumn(column);
1028            }
1029            insertSqlMap.put(tableName, insert.getStatement());
1030            insertColumnsMap.put(tableName, insertColumns);
1031        }
1032
1033        protected void postProcessDelete() {
1034            Delete delete = new Delete(table);
1035            String wheres = table.getColumns()
1036                                 .stream()
1037                                 .filter(col -> Model.MAIN_KEY.equals(col.getKey()))
1038                                 .map(col -> col.getQuotedName() + " = ?")
1039                                 .collect(Collectors.joining(" AND "));
1040            delete.setWhere(wheres);
1041            deleteSqlMap.put(tableName, delete.getStatement());
1042        }
1043
1044        // copy of a fragment
1045        // INSERT INTO foo (id, x, y) SELECT ?, x, y FROM foo WHERE id = ?
1046        protected void postProcessCopy() {
1047            Collection<Column> columns = table.getColumns();
1048            List<String> selectWhats = new ArrayList<>(columns.size());
1049            Column copyIdColumn = table.getColumn(Model.MAIN_KEY);
1050            Insert insert = new Insert(table);
1051            for (Column column : columns) {
1052                if (column.isIdentity()) {
1053                    // identity column is never copied
1054                    continue;
1055                }
1056                insert.addColumn(column);
1057                if (column == copyIdColumn) {
1058                    // explicit value
1059                    selectWhats.add("?");
1060                } else {
1061                    // otherwise copy value
1062                    selectWhats.add(column.getQuotedName());
1063                }
1064            }
1065            Select select = new Select(table);
1066            select.setWhat(String.join(", ", selectWhats));
1067            select.setFrom(table.getQuotedName());
1068            select.setWhere(copyIdColumn.getQuotedName() + " = ?");
1069            insert.setValues(select.getStatement());
1070            copySqlMap.put(tableName, insert.getStatement());
1071            copyIdColumnMap.put(tableName, copyIdColumn);
1072        }
1073
1074    }
1075
1076    public static class SQLInfoSelect {
1077
1078        public final String sql;
1079
1080        public final List<Column> whatColumns;
1081
1082        public final MapMaker mapMaker;
1083
1084        public final List<Column> whereColumns;
1085
1086        public final List<Column> opaqueColumns;
1087
1088        /**
1089         * Standard select for given columns.
1090         */
1091        public SQLInfoSelect(String sql, List<Column> whatColumns, List<Column> whereColumns,
1092                List<Column> opaqueColumns) {
1093            this(sql, whatColumns, null, whereColumns, opaqueColumns);
1094        }
1095
1096        /**
1097         * Select where some column keys may be aliased, and some columns may be computed. The {@link MapMaker} is used
1098         * by the queryAndFetch() method.
1099         */
1100        public SQLInfoSelect(String sql, MapMaker mapMaker) {
1101            this(sql, null, mapMaker, null, null);
1102        }
1103
1104        public SQLInfoSelect(String sql, List<Column> whatColumns, MapMaker mapMaker, List<Column> whereColumns,
1105                List<Column> opaqueColumns) {
1106            this.sql = sql;
1107            this.whatColumns = whatColumns;
1108            this.mapMaker = mapMaker;
1109            this.whereColumns = whereColumns == null ? null : new ArrayList<>(whereColumns);
1110            this.opaqueColumns = opaqueColumns == null ? null : new ArrayList<>(opaqueColumns);
1111        }
1112    }
1113
1114    /**
1115     * Info about how to do the query to get a {@link Selection}.
1116     */
1117    public class SQLInfoSelection {
1118
1119        public final SelectionType type;
1120
1121        public final SQLInfoSelect selectAll;
1122
1123        public final SQLInfoSelect selectFiltered;
1124
1125        public SQLInfoSelection(SelectionType selType) {
1126            this.type = selType;
1127            Table table = database.getTable(selType.tableName);
1128            SQLInfoSelect selectAll;
1129            SQLInfoSelect selectFiltered;
1130            String from = table.getQuotedName();
1131            List<String> clauses;
1132            if (selType.tableName.equals(Model.HIER_TABLE_NAME)) {
1133                // clause already added by makeSelect
1134                clauses = null;
1135            } else {
1136                Table hierTable = database.getTable(Model.HIER_TABLE_NAME);
1137                Join join = new Join(Join.INNER, hierTable.getQuotedName(), null, null,
1138                        hierTable.getColumn(Model.MAIN_KEY), table.getColumn(Model.MAIN_KEY));
1139                from += join.toSql(dialect);
1140                String clause = getSoftDeleteClause();
1141                clauses = clause == null ? null : Collections.singletonList(clause);
1142            }
1143            if (selType.criterionKey == null) {
1144                selectAll = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey);
1145                selectFiltered = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey, selType.filterKey);
1146            } else {
1147                selectAll = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey, selType.criterionKey);
1148                selectFiltered = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey, selType.filterKey,
1149                        selType.criterionKey);
1150            }
1151            this.selectAll = selectAll;
1152            this.selectFiltered = selectFiltered;
1153        }
1154
1155        /**
1156         * Select selection ids for multiple values.
1157         */
1158        public SQLInfoSelect getSelectSelectionIds(int nids) {
1159            Table table = database.getTable(type.tableName);
1160            String from = table.getQuotedName();
1161            Table hierTable = database.getTable(Model.HIER_TABLE_NAME);
1162            Join join = new Join(Join.INNER, hierTable.getQuotedName(), null, null,
1163                    hierTable.getColumn(Model.MAIN_KEY), table.getColumn(Model.MAIN_KEY));
1164            from += join.toSql(dialect);
1165
1166            Column whatColumn = table.getColumn(Model.MAIN_KEY);
1167            Column whereColumn = table.getColumn(type.selKey);
1168            StringBuilder wherebuf = new StringBuilder(whereColumn.getQuotedName());
1169            wherebuf.append(" IN (");
1170            for (int i = 0; i < nids; i++) {
1171                if (i != 0) {
1172                    wherebuf.append(", ");
1173                }
1174                wherebuf.append('?');
1175            }
1176            wherebuf.append(')');
1177            wherebuf.append(getSoftDeleteClause(Model.HIER_TABLE_NAME));
1178            Select select = new Select(table);
1179            select.setWhat(whatColumn.getFullQuotedName());
1180            select.setFrom(from);
1181            select.setWhere(wherebuf.toString());
1182            return new SQLInfoSelect(select.getStatement(), Collections.singletonList(whatColumn),
1183                    Collections.singletonList(whereColumn), null);
1184        }
1185    }
1186
1187    /**
1188     * Knows how to build a result map for a row given a {@link ResultSet}. This abstraction may be used to compute some
1189     * values on the fly.
1190     */
1191    public interface MapMaker {
1192        Map<String, Serializable> makeMap(ResultSet rs) throws SQLException;
1193    }
1194
1195    /**
1196     * Builds the map from a result set given a list of columns and column keys.
1197     */
1198    public static class ColumnMapMaker implements MapMaker {
1199        public final List<Column> columns;
1200
1201        public final List<String> keys;
1202
1203        public ColumnMapMaker(List<Column> columns) {
1204            this.columns = columns;
1205            this.keys = columns.stream().map(Column::getKey).collect(Collectors.toList());
1206        }
1207
1208        public ColumnMapMaker(List<Column> columns, List<String> keys) {
1209            this.columns = columns;
1210            this.keys = keys;
1211        }
1212
1213        @Override
1214        public Map<String, Serializable> makeMap(ResultSet rs) throws SQLException {
1215            Map<String, Serializable> map = new HashMap<>();
1216            int i = 1;
1217            for (Column column : columns) {
1218                String key = keys.get(i - 1);
1219                Serializable value = column.getFromResultSet(rs, i++);
1220                if (NXQL.ECM_UUID.equals(key) || NXQL.ECM_PARENTID.equals(key)) {
1221                    value = String.valueOf(value); // idToString
1222                }
1223                map.put(key, value);
1224            }
1225            return map;
1226        }
1227    }
1228
1229    private static String[] NO_ORDER_BY = new String[0];
1230
1231    /**
1232     * Basic SELECT x, y, z FROM table WHERE a = ? AND b = ?
1233     * <p>
1234     * with optional ORDER BY x, y DESC
1235     */
1236    public SQLInfoSelect makeSelect(Table table, String[] orderBys, String... freeColumns) {
1237        return makeSelect(table, null, null, orderBys, freeColumns);
1238    }
1239
1240    /**
1241     * Same as above but the FROM can be passed in, to allow JOINs.
1242     */
1243    public SQLInfoSelect makeSelect(Table table, String from, List<String> clauses, String[] orderBys,
1244            String... freeColumns) {
1245        boolean fullQuotedName = from != null;
1246        List<String> freeColumnsList = Arrays.asList(freeColumns);
1247        List<Column> whatColumns = new LinkedList<>();
1248        List<Column> whereColumns = new LinkedList<>();
1249        List<Column> opaqueColumns = new LinkedList<>();
1250        List<String> whats = new LinkedList<>();
1251        List<String> wheres = new LinkedList<>();
1252        for (Column column : table.getColumns()) {
1253            String qname = fullQuotedName ? column.getFullQuotedName() : column.getQuotedName();
1254            if (freeColumnsList.contains(column.getKey())) {
1255                whereColumns.add(column);
1256                wheres.add(qname + " = ?");
1257            } else if (column.isOpaque()) {
1258                opaqueColumns.add(column);
1259            } else {
1260                whatColumns.add(column);
1261                whats.add(qname);
1262            }
1263        }
1264        if (whats.isEmpty()) {
1265            // only opaque columns, don't generate an illegal SELECT
1266            whats.add(table.getColumn(Model.MAIN_KEY).getQuotedName());
1267        }
1268        if (clauses != null) {
1269            wheres.addAll(clauses);
1270        }
1271        Select select = new Select(table);
1272        select.setWhat(String.join(", ", whats));
1273        if (from == null) {
1274            from = table.getQuotedName();
1275        }
1276        select.setFrom(from);
1277        String where = String.join(" AND ", wheres) + getSoftDeleteClause(table.getKey());
1278        select.setWhere(where);
1279        List<String> orders = new LinkedList<>();
1280        for (int i = 0; i < orderBys.length; i++) {
1281            String name = orderBys[i++];
1282            String ascdesc = orderBys[i].equals(ORDER_DESC) ? " " + ORDER_DESC : "";
1283            Column col = table.getColumn(name);
1284            String qcol = fullQuotedName ? col.getFullQuotedName() : col.getQuotedName();
1285            orders.add(qcol + ascdesc);
1286        }
1287        select.setOrderBy(String.join(", ", orders));
1288        return new SQLInfoSelect(select.getStatement(), whatColumns, whereColumns,
1289                opaqueColumns.isEmpty() ? null : opaqueColumns);
1290    }
1291
1292    public void initSQLStatements(Map<String, Serializable> testProps, List<String> sqlInitFiles) throws IOException {
1293        sqlStatements = new HashMap<>();
1294        SQLStatement.read(dialect.getSQLStatementsFilename(), sqlStatements);
1295        if (sqlInitFiles != null) {
1296            for (String filename : sqlInitFiles) {
1297                SQLStatement.read(filename, sqlStatements);
1298            }
1299        }
1300        if (!testProps.isEmpty()) {
1301            SQLStatement.read(dialect.getTestSQLStatementsFilename(), sqlStatements, true); // DDL time
1302        }
1303        sqlStatementsProperties = dialect.getSQLStatementsProperties(model, database);
1304        if (!testProps.isEmpty()) {
1305            sqlStatementsProperties.putAll(testProps);
1306        }
1307    }
1308
1309    /**
1310     * Executes the SQL statements for the given category.
1311     */
1312    public void executeSQLStatements(String category, String ddlMode, Connection connection, JDBCLogger logger,
1313            ListCollector ddlCollector) throws SQLException {
1314        List<SQLStatement> statements = sqlStatements.get(category);
1315        if (statements != null) {
1316            SQLStatement.execute(statements, ddlMode, sqlStatementsProperties, dialect, connection, logger,
1317                    ddlCollector);
1318        }
1319    }
1320
1321    public int getMaximumArgsForIn() {
1322        return dialect.getMaximumArgsForIn();
1323    }
1324
1325}