OpenConcerto

Dépôt officiel du code source de l'ERP OpenConcerto
sonarqube

svn://code.openconcerto.org/openconcerto

Rev

Rev 41 | Rev 63 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
17 ilm 1
/*
2
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3
 *
4
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5
 *
6
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9
 * language governing permissions and limitations under the License.
10
 *
11
 * When distributing the software, include this License Header Notice in each file.
12
 */
13
 
14
 package org.openconcerto.sql.model;
15
 
16
import org.openconcerto.sql.changer.correct.FixSerial;
17
import org.openconcerto.sql.model.SQLField.Properties;
18
import org.openconcerto.sql.model.SQLTable.Index;
19
import org.openconcerto.utils.CollectionUtils;
20
import org.openconcerto.utils.CompareUtils;
21
import org.openconcerto.utils.FileUtils;
22
import org.openconcerto.utils.Tuple2;
23
import org.openconcerto.utils.cc.IClosure;
24
import org.openconcerto.utils.cc.ITransformer;
25
 
26
import java.io.File;
27
import java.io.FileInputStream;
28
import java.io.FileOutputStream;
29
import java.io.IOException;
30
import java.math.BigDecimal;
31
import java.math.BigInteger;
32
import java.sql.Array;
33
import java.sql.Blob;
34
import java.sql.Clob;
35
import java.sql.Connection;
36
import java.sql.SQLException;
37
import java.sql.Timestamp;
38
import java.util.ArrayList;
39
import java.util.Arrays;
40
import java.util.Collections;
41
import java.util.Comparator;
42
import java.util.Date;
43
import java.util.List;
44
import java.util.Map;
45
import java.util.Map.Entry;
46
import java.util.Set;
47
import java.util.regex.Pattern;
48
 
49
import org.apache.commons.dbcp.DelegatingConnection;
50
import org.postgresql.PGConnection;
51
 
21 ilm 52
/**
53
 * To require SSL, set the "ssl" connection property to "true" (note: for now any value, including
54
 * "false" enables it), to disable server validation set "sslfactory" to
55
 * "org.postgresql.ssl.NonValidatingFactory". To check the connection status, install the
56
 * contrib/sslinfo extension and execute "select ssl_is_used();". SSL Compression might be supported
57
 * if we can find a good sslfactory : see this <a
58
 * href="http://archives.postgresql.org/pgsql-general/2010-08/thrd5.php#00003">thread</a>.
59
 * <p>
60
 * To enable SSL on the server see http://www.postgresql.org/docs/current/static/ssl-tcp.html
61
 * (already set up on Ubuntu).
62
 * <p>
63
 *
64
 * @author Sylvain CUAZ
65
 */
17 ilm 66
class SQLSyntaxPG extends SQLSyntax {
67
 
68
    SQLSyntaxPG() {
69
        super(SQLSystem.POSTGRESQL);
70
        this.typeNames.putAll(Boolean.class, "boolean", "bool", "bit");
71
        this.typeNames.putAll(Integer.class, "integer", "int", "int4");
72
        this.typeNames.putAll(Long.class, "bigint", "int8");
73
        this.typeNames.putAll(BigInteger.class, "bigint");
74
        this.typeNames.putAll(BigDecimal.class, "decimal", "numeric");
75
        this.typeNames.putAll(Float.class, "real", "float4");
76
        this.typeNames.putAll(Double.class, "double precision", "float8");
77
        // since 7.3 default is without timezone
78
        this.typeNames.putAll(Timestamp.class, "timestamp", "timestamp without time zone");
79
        this.typeNames.putAll(java.util.Date.class, "time", "time without time zone", "date");
80
        this.typeNames.putAll(Blob.class, "bytea");
81
        this.typeNames.putAll(Clob.class, "varchar", "char", "character varying", "character", "text");
82
        this.typeNames.putAll(String.class, "varchar", "char", "character varying", "character", "text");
83
    }
84
 
85
    public String getInitRoot(final String name) {
86
        final String sql;
87
        try {
88
            final String fileContent = FileUtils.read(this.getClass().getResourceAsStream("pgsql-functions.sql"), "UTF8");
89
            sql = fileContent.replace("${rootName}", SQLBase.quoteIdentifier(name));
90
        } catch (IOException e) {
91
            throw new IllegalStateException("cannot read functions", e);
92
        }
93
        return sql;
94
    }
95
 
96
    @Override
97
    protected Tuple2<Boolean, String> getCast() {
98
        return Tuple2.create(false, "::");
99
    }
100
 
101
    public String getIDType() {
102
        return " int";
103
    }
104
 
105
    @Override
106
    public boolean isAuto(SQLField f) {
107
        return f.getType().getTypeName().equalsIgnoreCase("serial");
108
    }
109
 
110
    public String getAuto() {
111
        return " serial";
112
    }
113
 
114
    private String changeFKChecks(DBRoot r, final String action) {
115
        String res = r.getBase().quote("select %i.getTables(%s, '.*', 'tables_changeFKChecks');", r.getName(), r.getName());
116
        res += r.getBase().quote("select %i.setTrigger('" + action + "', 'tables_changeFKChecks');", r.getName());
117
        res += "close \"tables_changeFKChecks\";";
118
        return res;
119
    }
120
 
121
    @Override
122
    public String disableFKChecks(DBRoot b) {
61 ilm 123
        // MAYBE return "SET CONSTRAINTS ALL DEFERRED";
124
        // NOTE: CASCADE, SET NULL, SET DEFAULT cannot be deferred (as of 9.1)
17 ilm 125
        return this.changeFKChecks(b, "DISABLE");
126
    }
127
 
128
    @Override
129
    public String enableFKChecks(DBRoot b) {
61 ilm 130
        // MAYBE return "SET CONSTRAINTS ALL IMMEDIATE";
17 ilm 131
        return this.changeFKChecks(b, "ENABLE");
132
    }
133
 
134
    @SuppressWarnings("unchecked")
135
    @Override
136
    // override since pg driver do not return FILTER_CONDITION
137
    public List<Map<String, Object>> getIndexInfo(SQLTable t) throws SQLException {
138
        final String query = "SELECT NULL AS \"TABLE_CAT\",  n.nspname as \"TABLE_SCHEM\",\n"
139
        //
140
                + "ct.relname as \"TABLE_NAME\", NOT i.indisunique AS \"NON_UNIQUE\",\n"
141
                //
142
                + "NULL AS \"INDEX_QUALIFIER\", ci.relname as \"INDEX_NAME\",\n"
143
                //
144
                + "NULL as \"TYPE\", col.attnum as \"ORDINAL_POSITION\",\n"
145
                //
146
                + "CASE WHEN i.indexprs IS NULL THEN col.attname ELSE pg_get_indexdef(ci.oid,col.attnum,false) END AS \"COLUMN_NAME\",\n"
147
                //
148
                + "NULL AS \"ASC_OR_DESC\", ci.reltuples as \"CARDINALITY\", ci.relpages as \"PAGES\",\n"
149
                //
150
                + "pg_get_expr(i.indpred,ct.oid) as \"FILTER_CONDITION\"\n"
151
                //
152
                + "FROM pg_catalog.pg_class ct\n"
153
                //
154
                + "     JOIN pg_catalog.pg_namespace n ON n.oid = ct.relnamespace\n"
155
                //
156
                + "     JOIN pg_catalog.pg_index i ON ct.oid=i.indrelid\n"
157
                //
158
                + "     JOIN pg_catalog.pg_class ci ON ci.oid=i.indexrelid\n"
159
                //
160
                + "     JOIN pg_catalog.pg_attribute col ON col.attrelid = ci.oid\n"
161
                //
162
                + "WHERE ci.relkind IN ('i','') AND n.nspname <> 'pg_catalog' AND n.nspname !~ '^pg_toast'\n"
163
                //
164
                + " AND n.nspname = '" + t.getSchema().getName() + "' AND ct.relname ~ '^(" + t.getName() + ")$'\n"
165
                //
166
                + "ORDER BY \"NON_UNIQUE\", \"TYPE\", \"INDEX_NAME\", \"ORDINAL_POSITION\";";
167
        // don't cache since we don't listen on system tables
168
        return (List<Map<String, Object>>) t.getDBSystemRoot().getDataSource().execute(query, new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false));
169
    }
170
 
171
    protected String setNullable(SQLField f, boolean b) {
172
        return SQLSelect.quote("ALTER COLUMN %n " + (b ? "DROP" : "SET") + " NOT NULL", f);
173
    }
174
 
175
    @Override
176
    public List<String> getAlterField(SQLField f, Set<Properties> toAlter, String type, String defaultVal, Boolean nullable) {
177
        final List<String> res = new ArrayList<String>();
178
        if (toAlter.contains(Properties.NULLABLE))
179
            res.add(this.setNullable(f, nullable));
180
        final String newType;
181
        if (toAlter.contains(Properties.TYPE)) {
182
            newType = type;
183
            res.add(SQLSelect.quote("ALTER COLUMN %n TYPE " + newType, f));
184
        } else
185
            newType = getType(f);
186
        if (toAlter.contains(Properties.DEFAULT))
187
            res.add(this.setDefault(f, defaultVal));
188
        return res;
189
    }
190
 
191
    @Override
192
    public String getDropRoot(String name) {
193
        return SQLSelect.quote("DROP SCHEMA IF EXISTS %i CASCADE ;", name);
194
    }
195
 
196
    @Override
197
    public String getCreateRoot(String name) {
198
        return SQLSelect.quote("CREATE SCHEMA %i ;", name);
199
    }
200
 
201
    @Override
41 ilm 202
    public String getDropPrimaryKey(SQLTable t) {
203
        return getDropConstraint() + SQLBase.quoteIdentifier(t.getConstraint(ConstraintType.PRIMARY_KEY, t.getPKsNames()).getName());
204
    }
205
 
206
    @Override
17 ilm 207
    public String getDropIndex(String name, SQLName tableName) {
208
        return "DROP INDEX IF EXISTS " + new SQLName(tableName.getItemLenient(-2), name).quote() + " ;";
209
    }
210
 
211
    @Override
212
    protected String getCreateIndex(final String cols, final SQLName tableName, Index i) {
213
        final String method = i.getMethod() != null ? " USING " + i.getMethod() : "";
214
        // TODO handle where
215
        return i.getTable().getBase().quote("ON %i " + method + cols, tableName);
216
    }
217
 
218
    @SuppressWarnings("unused")
219
    private final String getIndexesReq(String schema, String tablePattern) {
220
        return "SELECT pg_catalog.pg_get_indexdef(i.indexrelid), c2.relname, i.indisunique, i.indisclustered, i.indisvalid" +
221
        // FROM
222
                " FROM pg_catalog.pg_class c" +
223
                //
224
                " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace" +
225
                //
226
                " LEFT JOIN pg_catalog.pg_index i ON c.oid = i.indrelid" +
227
                //
228
                " LEFT JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid" +
229
                // WHERE
230
                " WHERE c.relname ~ '" + tablePattern + "' and n.nspname = '" + schema + "'" + " and i.indisprimary = FALSE;";
231
    }
232
 
233
    protected boolean supportsPGCast() {
234
        return true;
235
    }
236
 
237
    private static final Pattern NOW_PTRN = Pattern.compile("\\(?'now'::text\\)?(::timestamp)");
238
 
239
    @Override
240
    public String transfDefaultJDBC2SQL(SQLField f) {
241
        if (f.getDefaultValue() != null && Date.class.isAssignableFrom(f.getType().getJavaType())) {
242
            // pg returns ('now'::text)::timestamp without time zone for CURRENT_TIMESTAMP
243
            // replace() handles complex defaults, e.g. now + '00:00:10'::interval
244
            return NOW_PTRN.matcher(f.getDefaultValue().toString()).replaceAll("CURRENT_TIMESTAMP$1");
245
        } else {
246
            return super.transfDefaultJDBC2SQL(f);
247
        }
248
    }
249
 
250
    @Override
251
    public void _loadData(final File f, final SQLTable t) throws IOException, SQLException {
252
        final String copy = "COPY " + t.getSQLName().quote() + " FROM STDIN " + getDataOptions(t.getBase()) + ";";
253
        final Number count = t.getDBSystemRoot().getDataSource().useConnection(new ConnectionHandlerNoSetup<Number, IOException>() {
254
            @Override
255
            public Number handle(SQLDataSource ds) throws SQLException, IOException {
256
                FileInputStream in = null;
257
                try {
258
                    in = new FileInputStream(f);
259
                    final Connection conn = ((DelegatingConnection) ds.getConnection()).getInnermostDelegate();
260
                    return ((PGConnection) conn).getCopyAPI().copyIn(copy, in);
261
                } finally {
262
                    if (in != null)
263
                        in.close();
264
                }
265
            }
266
        });
267
 
268
        final String seq = FixSerial.getPrimaryKeySeq(t);
269
        // no need to alter sequence if nothing was inserted (can be -1 in old pg)
270
        // also avoid NULL for empty tables and thus arbitrary start constant
271
        if (count.intValue() != 0 && seq != null) {
272
            t.getDBSystemRoot().getDataSource().execute(t.getBase().quote("select %n.\"alterSeq\"( %s, 'select max(%n)+1 from %f');", t.getDBRoot(), seq, t.getKey(), t));
273
        }
274
    }
275
 
276
    private static String getDataOptions(final SQLBase b) {
277
        return " WITH NULL " + b.quoteString("\\N") + " CSV HEADER QUOTE " + b.quoteString("\"") + " ESCAPE AS " + b.quoteString("\\");
278
    }
279
 
280
    @Override
281
    protected void _storeData(final SQLTable t, final File f) {
282
        // if there's no fields, there's no data
283
        if (t.getFields().size() == 0)
284
            return;
285
 
286
        final String cols = CollectionUtils.join(t.getOrderedFields(), ",", new ITransformer<SQLField, String>() {
287
            @Override
288
            public String transformChecked(SQLField f) {
289
                return SQLBase.quoteIdentifier(f.getName());
290
            }
291
        });
292
        // you can't specify line separator to pg, so use STDOUT as it always use \n
293
        try {
294
            final String sql = "COPY (" + selectAll(t).asString() + ") to STDOUT " + getDataOptions(t.getBase()) + " FORCE QUOTE " + cols + " ;";
295
            final Connection conn = ((DelegatingConnection) t.getBase().getDataSource().getConnection()).getInnermostDelegate();
296
            final FileOutputStream out = new FileOutputStream(f);
297
            ((PGConnection) conn).getCopyAPI().copyOut(sql, out);
298
            out.close();
299
        } catch (Exception e) {
300
            throw new IllegalStateException("unable to store " + t + " into " + f, e);
301
        }
302
    }
303
 
304
    static SQLSelect selectAll(final SQLTable t) {
305
        final SQLSelect sel = new SQLSelect(t.getBase(), true);
306
        for (final SQLField field : t.getOrderedFields()) {
307
            // MySQL despite accepting 'boolean', 'true' and 'false' keywords doesn't really
308
            // support booleans
309
            if (field.getType().getJavaType() == Boolean.class)
310
                sel.addRawSelect("cast(" + field.getFieldRef() + " as integer)", field.getName());
311
            else
312
                sel.addSelect(field);
313
        }
314
        return sel;
315
    }
316
 
317
    @Override
318
    public String getChar(int asciiCode) {
319
        return "chr(" + asciiCode + ")";
320
    }
321
 
322
    @Override
26 ilm 323
    public String getRegexpOp(boolean negation) {
324
        return negation ? "!~" : "~";
325
    }
326
 
327
    @Override
17 ilm 328
    public SQLBase createBase(SQLServer server, String name, String login, String pass, IClosure<SQLDataSource> dsInit) {
329
        return new PGSQLBase(server, name, login, pass, dsInit);
330
    }
331
 
332
    @Override
333
    public final String getCreateSynonym(final SQLTable t, final SQLName newName) {
334
        String res = super.getCreateSynonym(t, newName);
335
 
336
        // in postgresql 8.3 views are not updatable, need to write rules
337
        final List<SQLField> fields = t.getOrderedFields();
338
        final List<String> setL = new ArrayList<String>(fields.size());
339
        final List<String> insFieldsL = new ArrayList<String>(fields.size());
340
        final List<String> insValuesL = new ArrayList<String>(fields.size());
341
        for (final SQLField f : fields) {
342
            final String name = t.getBase().quote("%n", f);
343
            final String newDotName = t.getBase().quote("NEW.%n", f, f);
344
            // don't add isAuto to ins
345
            if (!this.isAuto(f)) {
346
                insFieldsL.add(name);
347
                insValuesL.add(newDotName);
348
            }
349
            setL.add(name + " = " + newDotName);
350
        }
351
        final String set = "set " + CollectionUtils.join(setL, ", ");
352
        final String insFields = "(" + CollectionUtils.join(insFieldsL, ", ") + ") ";
353
        final String insValues = "VALUES(" + CollectionUtils.join(insValuesL, ", ") + ") ";
354
 
355
        // rule names are unique by table
356
        res += t.getBase().quote("CREATE or REPLACE RULE \"_updView_\" AS ON UPDATE TO %i\n" + "DO INSTEAD UPDATE %f \n" + set + "where %n=OLD.%n\n" + "RETURNING %f.*;", newName, t, t.getKey(),
357
                t.getKey(), t);
358
        res += t.getBase().quote("CREATE or REPLACE RULE \"_delView_\" AS ON DELETE TO %i\n" + "DO INSTEAD DELETE FROM %f \n where %n=OLD.%n\n" + "RETURNING %f.*;", newName, t, t.getKey(),
359
                t.getKey(), t);
360
        res += t.getBase().quote("CREATE or REPLACE RULE \"_insView_\" AS ON INSERT TO %i\n" + "DO INSTEAD INSERT INTO %f" + insFields + " " + insValues + "RETURNING %f.*;", newName, t, t);
361
 
362
        return res;
363
    }
364
 
365
    @Override
366
    public String getNullIsDataComparison(String x, boolean eq, String y) {
367
        return x + (eq ? " IS NOT DISTINCT FROM " : " IS DISTINCT FROM ") + y;
368
    }
369
 
370
    @Override
371
    public String getFunctionQuery(SQLBase b, Set<String> schemas) {
372
        return "SELECT ROUTINE_SCHEMA as \"schema\", ROUTINE_NAME as \"name\", ROUTINE_DEFINITION as \"src\" FROM \"information_schema\".ROUTINES where ROUTINE_CATALOG='" + b.getMDName()
373
                + "' and ROUTINE_SCHEMA in (" + quoteStrings(b, schemas) + ")";
374
    }
375
 
376
    @Override
377
    public String getTriggerQuery(SQLBase b, Set<String> schemas, Set<String> tables) throws SQLException {
378
        return "SELECT tgname as \"TRIGGER_NAME\", n.nspname as \"TABLE_SCHEMA\", c.relname as \"TABLE_NAME\", tgfoid as \"ACTION\", pg_get_triggerdef(t.oid) as \"SQL\" \n" +
379
        // from
380
                "FROM pg_catalog.pg_trigger t\n" +
381
                // table
382
                "LEFT join pg_catalog.pg_class c on t.tgrelid = c.oid\n" +
383
                // schema
384
                "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" +
385
                // where
386
                "where not t." + (b.getVersion()[0] >= 9 ? "tgisinternal" : "tgisconstraint") + " and " + getInfoSchemaWhere(b, "n.nspname", schemas, "c.relname", tables);
387
    }
388
 
389
    private final String getInfoSchemaWhere(SQLBase b, final String schemaCol, Set<String> schemas, final String tableCol, Set<String> tables) {
390
        final String tableWhere = tables == null ? "" : " and " + tableCol + " in (" + quoteStrings(b, tables) + ")";
391
        return schemaCol + " in ( " + quoteStrings(b, schemas) + ") " + tableWhere;
392
        // no need of base, since pg can only see its current base
393
    }
394
 
395
    @Override
396
    public String getColumnsQuery(SQLBase b, Set<String> schemas, Set<String> tables) {
397
        return "SELECT TABLE_SCHEMA as \"" + INFO_SCHEMA_NAMES_KEYS.get(0) + "\", TABLE_NAME as \"" + INFO_SCHEMA_NAMES_KEYS.get(1) + "\", COLUMN_NAME as \"" + INFO_SCHEMA_NAMES_KEYS.get(2)
398
                + "\" , CHARACTER_SET_NAME as \"CHARACTER_SET_NAME\", COLLATION_NAME as \"COLLATION_NAME\" from INFORMATION_SCHEMA.COLUMNS where "
399
                + getInfoSchemaWhere(b, "TABLE_SCHEMA", schemas, "TABLE_NAME", tables);
400
    }
401
 
402
    @Override
403
    @SuppressWarnings("unchecked")
404
    public List<Map<String, Object>> getConstraints(SQLBase b, Set<String> schemas, Set<String> tables) throws SQLException {
405
        final String sel = "select nsp.nspname as \"TABLE_SCHEMA\", rel.relname as \"TABLE_NAME\", c.conname as \"CONSTRAINT_NAME\", c.oid as cid, \n"
41 ilm 406
                + "case c.contype when 'u' then 'UNIQUE' when 'c' then 'CHECK' when 'f' then 'FOREIGN KEY' when 'p' then 'PRIMARY KEY' end as \"CONSTRAINT_TYPE\", att.attname as \"COLUMN_NAME\", c.conkey as \"colsNum\", att.attnum as \"colNum\"\n"
17 ilm 407
                // from
408
                + "from pg_catalog.pg_constraint c\n" + "join pg_namespace nsp on nsp.oid = c.connamespace\n" + "left join pg_class rel on rel.oid = c.conrelid\n"
409
                + "left join pg_attribute att on  att.attrelid = c.conrelid and att.attnum = ANY(c.conkey)\n"
410
                // where
41 ilm 411
                + "where " + getInfoSchemaWhere(b, "nsp.nspname", schemas, "rel.relname", tables)
17 ilm 412
                // order
413
                + "\norder by nsp.nspname, rel.relname, c.conname";
414
        // don't cache since we don't listen on system tables
415
        final List<Map<String, Object>> res = sort((List<Map<String, Object>>) b.getDBSystemRoot().getDataSource().execute(sel, new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false)));
416
        // ATTN c.conkey are not column indexes since dropped attribute are not deleted
417
        // so we must join pg_attribute to find out column names
418
        SQLSyntaxMySQL.mergeColumnNames(res);
419
        return res;
420
    }
421
 
422
    // pg has no ORDINAL_POSITION and no indexOf() function (except in contrib) so we can't ORDER
423
    // BY in SQL, we have to do it in java
424
    private List<Map<String, Object>> sort(final List<Map<String, Object>> sortedByConstraint) {
425
        final List<Map<String, Object>> res = new ArrayList<Map<String, Object>>(sortedByConstraint.size());
426
        final Comparator<Map<String, Object>> comp = new Comparator<Map<String, Object>>() {
427
            @Override
428
            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
429
                return CompareUtils.compareInt(getIndex(o1), getIndex(o2));
430
            }
431
 
432
            // index of the passed column in the constraint
433
            private final int getIndex(Map<String, Object> o) {
434
                final int colNum = ((Number) o.get("colNum")).intValue();
435
                try {
436
                    final Integer[] array = (Integer[]) ((Array) o.get("colsNum")).getArray();
437
                    for (int i = 0; i < array.length; i++) {
438
                        if (array[i].intValue() == colNum)
439
                            return i;
440
                    }
441
                    throw new IllegalStateException(colNum + " was not found in " + Arrays.toString(array));
442
                } catch (SQLException e) {
443
                    throw new RuntimeException(e);
444
                }
445
            }
446
        };
447
        // use the oid of pg to identify constraints (otherwise we'd have to compare the fully
448
        // qualified name of the constraint)
449
        int prevID = -1;
450
        final List<Map<String, Object>> currentConstr = new ArrayList<Map<String, Object>>();
451
        for (final Map<String, Object> m : sortedByConstraint) {
452
            final int currentID = ((Number) m.get("cid")).intValue();
453
            // at each change of constraint, sort its columns
454
            if (currentConstr.size() > 0 && currentID != prevID) {
455
                res.addAll(sort(currentConstr, comp));
456
                currentConstr.clear();
457
            }
458
            currentConstr.add(m);
459
            prevID = currentID;
460
        }
461
        res.addAll(sort(currentConstr, comp));
462
 
463
        return res;
464
    }
465
 
466
    private final List<Map<String, Object>> sort(List<Map<String, Object>> currentConstr, final Comparator<Map<String, Object>> comp) {
467
        Collections.sort(currentConstr, comp);
468
        for (int i = 0; i < currentConstr.size(); i++) {
469
            currentConstr.get(i).put("ORDINAL_POSITION", i + 1);
470
            // remove columns only needed to sort
471
            currentConstr.get(i).remove("cid");
472
            currentConstr.get(i).remove("colNum");
473
            currentConstr.get(i).remove("colsNum");
474
        }
475
        return currentConstr;
476
    }
477
 
478
    @Override
479
    public String getDropTrigger(Trigger t) {
480
        return SQLBase.quoteStd("DROP TRIGGER %i on %f", t.getName(), t.getTable());
481
    }
482
 
483
    @Override
484
    public String getUpdate(SQLTable t, List<String> tables, Map<String, String> setPart) {
485
        String res = t.getSQLName().quote() + " SET\n" + CollectionUtils.join(setPart.entrySet(), ",\n", new ITransformer<Entry<String, String>, String>() {
486
            @Override
487
            public String transformChecked(Entry<String, String> input) {
488
                // pg require that fields are unprefixed
489
                return SQLBase.quoteIdentifier(input.getKey()) + " = " + input.getValue();
490
            }
491
        });
492
        if (tables.size() > 0)
493
            res += " FROM " + CollectionUtils.join(tables, ", ");
494
        return res;
495
    }
496
}