OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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