OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 174 | 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
 
83 ilm 16
import org.openconcerto.sql.Configuration;
73 ilm 17
import org.openconcerto.sql.model.graph.SQLKey;
18
import org.openconcerto.sql.model.graph.TablesMap;
83 ilm 19
import org.openconcerto.sql.preferences.SQLPreferences;
73 ilm 20
import org.openconcerto.sql.utils.AlterTable;
21
import org.openconcerto.sql.utils.ChangeTable;
22
import org.openconcerto.sql.utils.SQLCreateTable;
23
import org.openconcerto.sql.view.list.SQLTableModelSourceOnline;
83 ilm 24
import org.openconcerto.utils.StringUtils;
73 ilm 25
import org.openconcerto.utils.cc.ITransformer;
17 ilm 26
 
27
import java.sql.SQLException;
177 ilm 28
import java.text.DateFormat;
29
import java.text.SimpleDateFormat;
17 ilm 30
import java.util.ArrayList;
73 ilm 31
import java.util.Arrays;
17 ilm 32
import java.util.HashMap;
177 ilm 33
import java.util.HashSet;
73 ilm 34
import java.util.List;
17 ilm 35
import java.util.Map;
73 ilm 36
import java.util.Set;
17 ilm 37
 
38
public class SQLInjector {
39
 
73 ilm 40
    private final SQLTable tableSrc, tableDest;
41
    private final ArrayList<SQLField> from = new ArrayList<SQLField>();
42
    private final ArrayList<SQLField> to = new ArrayList<SQLField>();
43
    private final Map<SQLField, Object> values = new HashMap<SQLField, Object>();
44
    private final static Map<DBRoot, Map<SQLTable, Map<SQLTable, SQLInjector>>> allRegisteredInjectors = new HashMap<DBRoot, Map<SQLTable, Map<SQLTable, SQLInjector>>>();
17 ilm 45
 
73 ilm 46
    private boolean storeTransfer;
47
    // maps of injectors that store transfer
48
    private static Map<DBRoot, Map<SQLTable, Map<SQLTable, SQLInjector>>> injectors = new HashMap<DBRoot, Map<SQLTable, Map<SQLTable, SQLInjector>>>();
49
 
50
    public SQLInjector(final DBRoot r, final String src, final String dest, boolean storeTransfer) {
51
        this(r.findTable(src), r.findTable(dest), storeTransfer);
17 ilm 52
    }
53
 
73 ilm 54
    public SQLInjector(SQLTable src, SQLTable dest, boolean storeTransfer) {
17 ilm 55
        this.tableDest = dest;
56
        this.tableSrc = src;
73 ilm 57
        this.storeTransfer = storeTransfer;
58
        final DBRoot dbRoot = src.getDBRoot();
59
        Map<SQLTable, Map<SQLTable, SQLInjector>> inj = allRegisteredInjectors.get(dbRoot);
60
        if (inj == null) {
61
            inj = new HashMap<SQLTable, Map<SQLTable, SQLInjector>>();
62
            allRegisteredInjectors.put(dbRoot, inj);
63
        }
64
        Map<SQLTable, SQLInjector> srcs = inj.get(src);
17 ilm 65
        if (srcs == null) {
66
            srcs = new HashMap<SQLTable, SQLInjector>();
73 ilm 67
            inj.put(src, srcs);
17 ilm 68
        }
69
        srcs.put(dest, this);
73 ilm 70
 
71
        if (storeTransfer) {
72
            // Register only SQLInjector that store transfer
73
            inj = injectors.get(dbRoot);
74
            if (inj == null) {
75
                inj = new HashMap<SQLTable, Map<SQLTable, SQLInjector>>();
76
                injectors.put(dbRoot, inj);
77
            }
78
            srcs = inj.get(src);
79
            if (srcs == null) {
80
                srcs = new HashMap<SQLTable, SQLInjector>();
81
                inj.put(src, srcs);
82
            }
83
            srcs.put(dest, this);
84
        }
17 ilm 85
    }
86
 
73 ilm 87
    public synchronized SQLRowValues createRowValuesFrom(int idSrc) {
88
        final List<SQLRowAccessor> srcRows = new ArrayList<SQLRowAccessor>(1);
93 ilm 89
        srcRows.add(getSource().getRow(idSrc));
73 ilm 90
        return createRowValuesFrom(srcRows);
17 ilm 91
    }
92
 
73 ilm 93
    public synchronized SQLRowValues createRowValuesFrom(final SQLRow srcRow) {
94
        final SQLRowValues rowVals = new SQLRowValues(getDestination());
17 ilm 95
        if (!srcRow.getTable().equals(getSource()))
96
            throw new IllegalArgumentException("Row not from source table : " + srcRow);
73 ilm 97
        merge(srcRow, rowVals);
98
        return rowVals;
99
    }
17 ilm 100
 
73 ilm 101
    public synchronized SQLRowValues createRowValuesFrom(final List<? extends SQLRowAccessor> srcRows) {
102
        final SQLRowValues rowVals = new SQLRowValues(getDestination());
103
        for (SQLRowAccessor srcRow : srcRows) {
104
            if (!srcRow.getTable().equals(getSource()))
105
                throw new IllegalArgumentException("Row not from source table : " + srcRow);
106
            merge(srcRow, rowVals);
107
        }
108
        return rowVals;
109
    }
110
 
111
    public void commitTransfert(final List<? extends SQLRowAccessor> srcRows, int destId) throws SQLException {
112
 
113
        if (storeTransfer) {
114
            System.err.println("SQLInjector.commitTransfert() : transfert from " + this.getSource().getName() + " to " + this.getDestination().getName());
115
            // Transfert
116
            final SQLTable tableTransfert = getSource().getDBRoot().getTable(getTableTranferName());
117
            if (tableTransfert == null) {
118
                throw new IllegalStateException("No table transfer for " + getSource().getName());
119
            }
120
 
121
            for (SQLRowAccessor srcRow : srcRows) {
122
 
123
                final SQLRowValues rowTransfer = new SQLRowValues(tableTransfert);
124
 
125
                final Set<SQLField> foreignKeysSrc = tableTransfert.getForeignKeys(getSource());
126
                final Set<SQLField> foreignKeysDest = tableTransfert.getForeignKeys(getDestination());
127
                if (foreignKeysSrc.isEmpty()) {
128
                    throw new IllegalStateException("No foreign (src) to " + getSource().getName() + " in " + tableTransfert.getName());
129
                }
130
                if (foreignKeysDest.isEmpty()) {
131
                    throw new IllegalStateException("No foreign (dest) to " + getDestination().getName() + " in " + tableTransfert.getName());
132
                }
133
                rowTransfer.put(foreignKeysSrc.iterator().next().getName(), srcRow.getIDNumber());
134
                rowTransfer.put(foreignKeysDest.iterator().next().getName(), destId);
135
                // TODO: commit in one shot
136
                rowTransfer.commit();
137
 
138
            }
139
        }
140
 
141
    }
142
 
143
    private String getTableTranferName() {
144
        return "TR_" + getSource().getName();
145
    }
146
 
147
    protected void merge(SQLRowAccessor srcRow, SQLRowValues rowVals) {
17 ilm 148
        for (SQLField field : this.values.keySet()) {
149
            rowVals.put(field.getName(), this.values.get(field));
150
        }
73 ilm 151
        final SQLSystem dbSystem = srcRow.getTable().getDBSystemRoot().getServer().getSQLSystem();
152
        final int size = getFrom().size();
153
        for (int i = 0; i < size; i++) {
17 ilm 154
 
155
            final SQLField sqlFieldFrom = getFrom().get(i);
156
            final SQLField sqlFieldTo = getTo().get(i);
157
            final Object o = srcRow.getObject(sqlFieldFrom.getName());
158
 
159
            // Probleme avec H2 Primary Key en Long et foreignKey en Int
160
            if (dbSystem == SQLSystem.H2 && sqlFieldFrom.getType().getJavaType() == Long.class && sqlFieldTo.getType().getJavaType() == Integer.class) {
73 ilm 161
                merge(sqlFieldTo, ((Long) o).intValue(), rowVals);
17 ilm 162
            } else {
73 ilm 163
                merge(sqlFieldTo, o, rowVals);
17 ilm 164
            }
165
        }
73 ilm 166
    }
17 ilm 167
 
73 ilm 168
    protected void merge(SQLField field, Object value, SQLRowValues rowVals) {
169
        rowVals.put(field.getName(), value);
17 ilm 170
    }
171
 
174 ilm 172
    protected void transfertReference(SQLRowAccessor srcRow, SQLRowValues rowVals, final SQLTable tableElementDestination, String refField, String from, String to) {
83 ilm 173
 
174
        String label = rowVals.getString(to);
174 ilm 175
        SQLPreferences prefs = SQLPreferences.getMemCached(srcRow.getTable().getDBRoot());
176
 
177
        if (prefs.getBoolean("TransfertRef", true) || !to.equals("NOM")) {
178
            if (label != null && label.trim().length() > 0) {
179
                rowVals.put(to, label + ", " + srcRow.getString(from));
180
            } else {
181
                rowVals.put(to, srcRow.getString(from));
182
            }
183
        } else if (prefs.getBoolean("TransfertMultiRef", false)) {
184
            SQLRowValues rowValsHeader = new SQLRowValues(UndefinedRowValuesCache.getInstance().getDefaultRowValues(tableElementDestination));
185
            // TODO taxe may be undefined set it to default
186
            rowValsHeader.put("NOM", srcRow.getString(from));
187
            rowValsHeader.put(refField, rowVals);
83 ilm 188
        }
174 ilm 189
 
83 ilm 190
    }
191
 
177 ilm 192
    DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
193
 
83 ilm 194
    protected void transfertNumberReference(SQLRowAccessor srcRow, SQLRowValues rowVals, final SQLTable tableElementDestination, String refField) {
174 ilm 195
        SQLPreferences prefs = SQLPreferences.getMemCached(srcRow.getTable().getDBRoot());
83 ilm 196
 
197
        if (prefs.getBoolean("TransfertRef", true)) {
198
            String label = rowVals.getString("NOM");
199
            if (label != null && label.trim().length() > 0) {
177 ilm 200
                final String value = label + ", " + srcRow.getString("NUMERO");
201
                List<String> l = StringUtils.fastSplit(value, ',');
202
                Set<String> s = new HashSet<>(l);
203
                String nom = "";
204
                if (s.size() > 1) {
205
                    for (String string : s) {
206
                        nom += string + ",";
207
                    }
208
                } else if (s.size() == 1) {
209
                    nom = s.iterator().next();
210
                }
211
                rowVals.put("NOM", nom);
83 ilm 212
            } else {
213
                rowVals.put("NOM", srcRow.getString("NUMERO"));
214
            }
215
        } else if (prefs.getBoolean("TransfertMultiRef", false)) {
216
            SQLRowValues rowValsHeader = new SQLRowValues(UndefinedRowValuesCache.getInstance().getDefaultRowValues(tableElementDestination));
177 ilm 217
            // FIXME Style forcé en titre 1 via l'ID
218
            rowValsHeader.put("ID_STYLE", 3);
83 ilm 219
            String elementName = StringUtils.firstUp(Configuration.getInstance().getDirectory().getElement(getSource()).getName().getVariant(org.openconcerto.utils.i18n.Grammar.SINGULAR));
177 ilm 220
            rowValsHeader.put("NOM", elementName + "\n N° " + srcRow.getString("NUMERO") + " du " + dateFormat.format(srcRow.getDate("DATE").getTime()));
83 ilm 221
            rowValsHeader.put(refField, rowVals);
222
        }
223
    }
224
 
73 ilm 225
    public synchronized SQLRow insertFrom(final SQLRowAccessor srcRow) throws SQLException {
226
        return createRowValuesFrom(Arrays.asList(srcRow)).insert();
17 ilm 227
    }
228
 
229
    // TODO gettable()..getName()..equalsIgnoreCase( by .getTable().equals(
230
    /**
231
     * mettre une valeur par défaut pour un champ donné
232
     *
233
     * @param fieldDest
234
     * @param defaultValue
235
     */
73 ilm 236
    protected synchronized final void mapDefaultValues(SQLField fieldDest, Object defaultValue) {
17 ilm 237
        if (fieldDest.getTable().getName().equalsIgnoreCase(this.tableDest.getName())) {
238
            this.values.put(fieldDest, defaultValue);
239
        } else {
240
            throw new IllegalArgumentException("SQLField " + fieldDest + " is not a field of table " + this.tableDest);
241
        }
242
    }
243
 
73 ilm 244
    protected synchronized final void map(SQLField from, SQLField to) throws IllegalArgumentException {
17 ilm 245
        // Verification de la validité des SQLField
246
        if (!from.getTable().getName().equalsIgnoreCase(this.tableSrc.getName())) {
247
            throw new IllegalArgumentException("SQLField " + from + " is not a field of table " + this.tableSrc);
248
        } else {
249
            if (!to.getTable().getName().equalsIgnoreCase(this.tableDest.getName())) {
250
                throw new IllegalArgumentException("SQLField " + to + " is not a field of table " + this.tableDest);
251
            }
252
        }
253
 
254
        int index = this.from.indexOf(from);
255
        if (index > 0) {
256
            this.to.set(index, to);
257
        } else {
258
            this.from.add(from);
259
            this.to.add(to);
260
        }
261
    }
262
 
73 ilm 263
    protected synchronized final void remove(SQLField from, SQLField to) throws IllegalArgumentException {
17 ilm 264
        // Verification de la validité des SQLField
265
        if (!from.getTable().getName().equalsIgnoreCase(this.tableSrc.getName())) {
266
            throw new IllegalArgumentException("SQLField " + from + " is not a field of table " + this.tableSrc);
267
        } else {
268
            if (!to.getTable().getName().equalsIgnoreCase(this.tableDest.getName())) {
269
                throw new IllegalArgumentException("SQLField " + to + " is not a field of table " + this.tableDest);
270
            }
271
        }
272
 
273
        int index = this.from.indexOf(from);
274
        if (this.to.get(index).getName().equalsIgnoreCase(to.getName())) {
275
            this.to.remove(to);
276
            this.from.remove(from);
277
        }
278
    }
279
 
280
    /**
281
     * Créer l'association entre les champs portant le nom dans les deux tables
282
     *
283
     */
73 ilm 284
    public synchronized void createDefaultMap() {
17 ilm 285
        for (SQLField field : this.tableSrc.getContentFields()) {
286
 
287
            if (this.tableDest.contains(field.getName())) {
288
                map(field, this.tableDest.getField(field.getName()));
289
            }
290
        }
291
    }
292
 
73 ilm 293
    public synchronized ArrayList<SQLField> getFrom() {
17 ilm 294
        return this.from;
295
    }
296
 
73 ilm 297
    public synchronized ArrayList<SQLField> getTo() {
17 ilm 298
        return this.to;
299
    }
300
 
301
    /**
302
     * Creer un SQLInjector par défaut si aucun n'est déja défini
303
     *
304
     * @param src
305
     * @param dest
306
     * @return un SQLInjector par défaut si aucun n'est déja défini
307
     */
73 ilm 308
    public static synchronized SQLInjector getInjector(SQLTable src, SQLTable dest) {
309
        SQLInjector injector = getRegistrereddInjector(src, dest);
310
        if (injector == null) {
311
            injector = createDefaultInjector(src, dest);
17 ilm 312
        }
73 ilm 313
        return injector;
17 ilm 314
    }
315
 
73 ilm 316
    public static synchronized SQLInjector getRegistrereddInjector(SQLTable src, SQLTable dest) {
317
        final Map<SQLTable, Map<SQLTable, SQLInjector>> map = allRegisteredInjectors.get(src.getDBRoot());
318
        if (map == null) {
319
            return null;
320
        }
321
        Map<SQLTable, SQLInjector> m = map.get(src);
322
        if (m != null) {
323
            return m.get(dest);
324
        }
325
        return null;
326
    }
327
 
328
    private static synchronized SQLInjector createDefaultInjector(SQLTable src, SQLTable dest) {
329
        System.err.println("No SQLInjector defined for " + src + " , " + dest + ". SQLInjector created automatically.");
330
        SQLInjector injector = new SQLInjector(src, dest, false);
331
        injector.createDefaultMap();
332
        return injector;
333
    }
334
 
335
    public synchronized SQLTable getDestination() {
17 ilm 336
        return this.tableDest;
337
    }
338
 
73 ilm 339
    public synchronized SQLTable getSource() {
17 ilm 340
        return this.tableSrc;
341
    }
73 ilm 342
 
343
    public synchronized static void createTransferTables(DBRoot root) throws SQLException {
344
        Map<SQLTable, Map<SQLTable, SQLInjector>> map = injectors.get(root);
345
        if (root == null) {
346
            System.err.println("No SQLInjector for root " + root);
347
            return;
348
        }
349
 
350
        final Set<SQLTable> srcTables = map.keySet();
351
        if (srcTables.isEmpty()) {
352
            System.err.println("No SQLInjector for root " + root);
353
            return;
354
        }
355
 
356
        final List<SQLCreateTable> createTablesQueries = new ArrayList<SQLCreateTable>();
357
        // Create table if needed
358
        for (SQLTable sqlTable : srcTables) {
359
            final String trTableName = "TR_" + sqlTable.getName();
360
            if (root.getTable(trTableName) == null) {
361
                final SQLCreateTable createTable = new SQLCreateTable(root, trTableName);
362
                createTable.setPlain(false);
363
                // createTable.addColumn(SQLSyntax.ID_NAME,
364
                // createTable.getSyntax().getPrimaryIDDefinition());
365
                createTable.addForeignColumn(SQLKey.PREFIX + sqlTable.getName(), sqlTable);
366
                createTablesQueries.add(createTable);
367
            }
368
        }
369
        if (createTablesQueries.size() > 0) {
370
            root.createTables(createTablesQueries);
371
        }
372
 
373
        // Create transfer fields if needed
374
        final List<AlterTable> alterTablesQueries = new ArrayList<AlterTable>();
375
        final TablesMap toRefresh = new TablesMap();
376
        for (SQLTable srcTable : srcTables) {
377
            final String trTableName = "TR_" + srcTable.getName();
378
            final SQLTable transfertTable = root.getTable(trTableName);
379
            final AlterTable alter = new AlterTable(transfertTable);
380
            final Set<SQLTable> destTables = map.get(srcTable).keySet();
381
            for (SQLTable destTable : destTables) {
382
                final String fk = SQLKey.PREFIX + destTable.getName();
383
                if (!transfertTable.contains(fk)) {
384
                    alter.addForeignColumn(fk, destTable);
385
                }
386
            }
387
            if (!alter.isEmpty()) {
388
                alterTablesQueries.add(alter);
389
                toRefresh.add(alter.getRootName(), alter.getName());
390
            }
391
        }
392
        for (final String q : ChangeTable.cat(alterTablesQueries)) {
393
            root.getDBSystemRoot().getDataSource().execute(q);
394
        }
395
        root.getSchema().updateVersion();
396
        root.getDBSystemRoot().refresh(toRefresh, false);
397
 
398
    }
399
 
400
    public void setOnlyTransfered(SQLTableModelSourceOnline tableSource) {
401
        // needed for distinct
402
        tableSource.getReq().setLockSelect(false);
403
 
404
        tableSource.getReq().setSelectTransf(new ITransformer<SQLSelect, SQLSelect>() {
405
 
406
            @Override
407
            public SQLSelect transformChecked(SQLSelect input) {
408
 
409
                final SQLTable tableTR = getSource().getTable(getTableTranferName());
410
                // FIXME: preprocess TR_ .. content before join : group by id_src
411
                final SQLSelectJoin j = input.addBackwardJoin("INNER", null, tableTR.getForeignKeys(getSource()).iterator().next(), null);
412
                j.setWhere(new Where(tableTR.getForeignKeys(getDestination()).iterator().next(), "!=", getDestination().getUndefinedID()));
413
                input.setDistinct(true);
414
 
415
                System.err.println(input.asString());
416
                return input;
417
            }
418
        });
419
    }
420
 
421
    public void setOnlyNotTransfered(SQLTableModelSourceOnline tableSource) {
422
        tableSource.getReq().setSelectTransf(new ITransformer<SQLSelect, SQLSelect>() {
423
 
424
            @Override
425
            public SQLSelect transformChecked(SQLSelect input) {
426
                final SQLTable tableTR = getSource().getTable(getTableTranferName());
427
 
428
                final Where w = new Where(tableTR.getForeignKeys(getSource()).iterator().next(), "=", input.getAlias(getSource().getKey()));
429
                input.addJoin("LEFT", tableTR, w);
430
                final Where w2 = new Where(tableTR.getForeignKeys(getDestination()).iterator().next(), "IS", (Object) null);
431
                input.setWhere(w2);
432
 
433
                System.err.println(input.asString());
434
                return input;
435
            }
436
        });
437
    }
438
 
439
    /**
440
     * register manually a transfer, use with caution
441
     *
442
     * @throws SQLException
174 ilm 443
     */
73 ilm 444
    public void addTransfert(int idFrom, int idTo) throws SQLException {
445
        final SQLTable tableTransfert = getSource().getTable(getTableTranferName());
446
        final SQLRowValues rowTransfer = new SQLRowValues(tableTransfert);
447
        final Set<SQLField> foreignKeysSrc = tableTransfert.getForeignKeys(getSource());
448
        final Set<SQLField> foreignKeysDest = tableTransfert.getForeignKeys(getDestination());
449
        rowTransfer.put(foreignKeysSrc.iterator().next().getName(), idFrom);
450
        rowTransfer.put(foreignKeysDest.iterator().next().getName(), idTo);
451
        rowTransfer.commit();
452
 
453
    }
454
 
17 ilm 455
}