OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 73 | Rev 93 | 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
 
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);
86
        srcRows.add(new SQLImmutableRowValues(getSource().getRow(idSrc).asRowValues()));
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
 
83 ilm 169
    protected void transfertReference(SQLRowAccessor srcRow, SQLRowValues rowVals, String from, String to) {
170
 
171
        String label = rowVals.getString(to);
172
        if (label != null && label.trim().length() > 0) {
173
            rowVals.put(to, label + ", " + srcRow.getString(from));
174
        } else {
175
            rowVals.put(to, srcRow.getString(from));
176
        }
177
    }
178
 
179
    protected void transfertNumberReference(SQLRowAccessor srcRow, SQLRowValues rowVals, final SQLTable tableElementDestination, String refField) {
180
        SQLPreferences prefs = new SQLPreferences(srcRow.getTable().getDBRoot());
181
 
182
        if (prefs.getBoolean("TransfertRef", true)) {
183
            String label = rowVals.getString("NOM");
184
            if (label != null && label.trim().length() > 0) {
185
                rowVals.put("NOM", label + ", " + srcRow.getString("NUMERO"));
186
            } else {
187
                rowVals.put("NOM", srcRow.getString("NUMERO"));
188
            }
189
        } else if (prefs.getBoolean("TransfertMultiRef", false)) {
190
            SQLRowValues rowValsHeader = new SQLRowValues(UndefinedRowValuesCache.getInstance().getDefaultRowValues(tableElementDestination));
191
            String elementName = StringUtils.firstUp(Configuration.getInstance().getDirectory().getElement(getSource()).getName().getVariant(org.openconcerto.utils.i18n.Grammar.SINGULAR));
192
            rowValsHeader.put("NOM", elementName + " N° " + srcRow.getString("NUMERO"));
193
            rowValsHeader.put(refField, rowVals);
194
        }
195
    }
196
 
73 ilm 197
    public synchronized SQLRow insertFrom(final SQLRowAccessor srcRow) throws SQLException {
198
        return createRowValuesFrom(Arrays.asList(srcRow)).insert();
17 ilm 199
    }
200
 
201
    // TODO gettable()..getName()..equalsIgnoreCase( by .getTable().equals(
202
    /**
203
     * mettre une valeur par défaut pour un champ donné
204
     *
205
     * @param fieldDest
206
     * @param defaultValue
207
     */
73 ilm 208
    protected synchronized final void mapDefaultValues(SQLField fieldDest, Object defaultValue) {
17 ilm 209
        if (fieldDest.getTable().getName().equalsIgnoreCase(this.tableDest.getName())) {
210
            this.values.put(fieldDest, defaultValue);
211
        } else {
212
            throw new IllegalArgumentException("SQLField " + fieldDest + " is not a field of table " + this.tableDest);
213
        }
214
    }
215
 
73 ilm 216
    protected synchronized final void map(SQLField from, SQLField to) throws IllegalArgumentException {
17 ilm 217
        // Verification de la validité des SQLField
218
        if (!from.getTable().getName().equalsIgnoreCase(this.tableSrc.getName())) {
219
            throw new IllegalArgumentException("SQLField " + from + " is not a field of table " + this.tableSrc);
220
        } else {
221
            if (!to.getTable().getName().equalsIgnoreCase(this.tableDest.getName())) {
222
                throw new IllegalArgumentException("SQLField " + to + " is not a field of table " + this.tableDest);
223
            }
224
        }
225
 
226
        int index = this.from.indexOf(from);
227
        if (index > 0) {
228
            this.to.set(index, to);
229
        } else {
230
            this.from.add(from);
231
            this.to.add(to);
232
        }
233
    }
234
 
73 ilm 235
    protected synchronized final void remove(SQLField from, SQLField to) throws IllegalArgumentException {
17 ilm 236
        // Verification de la validité des SQLField
237
        if (!from.getTable().getName().equalsIgnoreCase(this.tableSrc.getName())) {
238
            throw new IllegalArgumentException("SQLField " + from + " is not a field of table " + this.tableSrc);
239
        } else {
240
            if (!to.getTable().getName().equalsIgnoreCase(this.tableDest.getName())) {
241
                throw new IllegalArgumentException("SQLField " + to + " is not a field of table " + this.tableDest);
242
            }
243
        }
244
 
245
        int index = this.from.indexOf(from);
246
        if (this.to.get(index).getName().equalsIgnoreCase(to.getName())) {
247
            this.to.remove(to);
248
            this.from.remove(from);
249
        }
250
    }
251
 
252
    /**
253
     * Créer l'association entre les champs portant le nom dans les deux tables
254
     *
255
     */
73 ilm 256
    public synchronized void createDefaultMap() {
17 ilm 257
        for (SQLField field : this.tableSrc.getContentFields()) {
258
 
259
            if (this.tableDest.contains(field.getName())) {
260
                map(field, this.tableDest.getField(field.getName()));
261
            }
262
        }
263
    }
264
 
73 ilm 265
    public synchronized ArrayList<SQLField> getFrom() {
17 ilm 266
        return this.from;
267
    }
268
 
73 ilm 269
    public synchronized ArrayList<SQLField> getTo() {
17 ilm 270
        return this.to;
271
    }
272
 
273
    /**
274
     * Creer un SQLInjector par défaut si aucun n'est déja défini
275
     *
276
     * @param src
277
     * @param dest
278
     * @return un SQLInjector par défaut si aucun n'est déja défini
279
     */
73 ilm 280
    public static synchronized SQLInjector getInjector(SQLTable src, SQLTable dest) {
281
        SQLInjector injector = getRegistrereddInjector(src, dest);
282
        if (injector == null) {
283
            injector = createDefaultInjector(src, dest);
17 ilm 284
        }
73 ilm 285
        return injector;
17 ilm 286
    }
287
 
73 ilm 288
    public static synchronized SQLInjector getRegistrereddInjector(SQLTable src, SQLTable dest) {
289
        final Map<SQLTable, Map<SQLTable, SQLInjector>> map = allRegisteredInjectors.get(src.getDBRoot());
290
        if (map == null) {
291
            return null;
292
        }
293
        Map<SQLTable, SQLInjector> m = map.get(src);
294
        if (m != null) {
295
            return m.get(dest);
296
        }
297
        return null;
298
    }
299
 
300
    private static synchronized SQLInjector createDefaultInjector(SQLTable src, SQLTable dest) {
301
        System.err.println("No SQLInjector defined for " + src + " , " + dest + ". SQLInjector created automatically.");
302
        SQLInjector injector = new SQLInjector(src, dest, false);
303
        injector.createDefaultMap();
304
        return injector;
305
    }
306
 
307
    public synchronized SQLTable getDestination() {
17 ilm 308
        return this.tableDest;
309
    }
310
 
73 ilm 311
    public synchronized SQLTable getSource() {
17 ilm 312
        return this.tableSrc;
313
    }
73 ilm 314
 
315
    public synchronized static void createTransferTables(DBRoot root) throws SQLException {
316
        Map<SQLTable, Map<SQLTable, SQLInjector>> map = injectors.get(root);
317
        if (root == null) {
318
            System.err.println("No SQLInjector for root " + root);
319
            return;
320
        }
321
 
322
        final Set<SQLTable> srcTables = map.keySet();
323
        if (srcTables.isEmpty()) {
324
            System.err.println("No SQLInjector for root " + root);
325
            return;
326
        }
327
 
328
        final List<SQLCreateTable> createTablesQueries = new ArrayList<SQLCreateTable>();
329
        // Create table if needed
330
        for (SQLTable sqlTable : srcTables) {
331
            final String trTableName = "TR_" + sqlTable.getName();
332
            if (root.getTable(trTableName) == null) {
333
                final SQLCreateTable createTable = new SQLCreateTable(root, trTableName);
334
                createTable.setPlain(false);
335
                // createTable.addColumn(SQLSyntax.ID_NAME,
336
                // createTable.getSyntax().getPrimaryIDDefinition());
337
                createTable.addForeignColumn(SQLKey.PREFIX + sqlTable.getName(), sqlTable);
338
                createTablesQueries.add(createTable);
339
            }
340
        }
341
        if (createTablesQueries.size() > 0) {
342
            root.createTables(createTablesQueries);
343
        }
344
 
345
        // Create transfer fields if needed
346
        final List<AlterTable> alterTablesQueries = new ArrayList<AlterTable>();
347
        final TablesMap toRefresh = new TablesMap();
348
        for (SQLTable srcTable : srcTables) {
349
            final String trTableName = "TR_" + srcTable.getName();
350
            final SQLTable transfertTable = root.getTable(trTableName);
351
            final AlterTable alter = new AlterTable(transfertTable);
352
            final Set<SQLTable> destTables = map.get(srcTable).keySet();
353
            for (SQLTable destTable : destTables) {
354
                final String fk = SQLKey.PREFIX + destTable.getName();
355
                if (!transfertTable.contains(fk)) {
356
                    alter.addForeignColumn(fk, destTable);
357
                }
358
            }
359
            if (!alter.isEmpty()) {
360
                alterTablesQueries.add(alter);
361
                toRefresh.add(alter.getRootName(), alter.getName());
362
            }
363
        }
364
        for (final String q : ChangeTable.cat(alterTablesQueries)) {
365
            root.getDBSystemRoot().getDataSource().execute(q);
366
        }
367
        root.getSchema().updateVersion();
368
        root.getDBSystemRoot().refresh(toRefresh, false);
369
 
370
    }
371
 
372
    public void setOnlyTransfered(SQLTableModelSourceOnline tableSource) {
373
        // needed for distinct
374
        tableSource.getReq().setLockSelect(false);
375
 
376
        tableSource.getReq().setSelectTransf(new ITransformer<SQLSelect, SQLSelect>() {
377
 
378
            @Override
379
            public SQLSelect transformChecked(SQLSelect input) {
380
 
381
                final SQLTable tableTR = getSource().getTable(getTableTranferName());
382
                // FIXME: preprocess TR_ .. content before join : group by id_src
383
                final SQLSelectJoin j = input.addBackwardJoin("INNER", null, tableTR.getForeignKeys(getSource()).iterator().next(), null);
384
                j.setWhere(new Where(tableTR.getForeignKeys(getDestination()).iterator().next(), "!=", getDestination().getUndefinedID()));
385
                input.setDistinct(true);
386
 
387
                System.err.println(input.asString());
388
                return input;
389
            }
390
        });
391
    }
392
 
393
    public void setOnlyNotTransfered(SQLTableModelSourceOnline tableSource) {
394
        tableSource.getReq().setSelectTransf(new ITransformer<SQLSelect, SQLSelect>() {
395
 
396
            @Override
397
            public SQLSelect transformChecked(SQLSelect input) {
398
                final SQLTable tableTR = getSource().getTable(getTableTranferName());
399
 
400
                final Where w = new Where(tableTR.getForeignKeys(getSource()).iterator().next(), "=", input.getAlias(getSource().getKey()));
401
                input.addJoin("LEFT", tableTR, w);
402
                final Where w2 = new Where(tableTR.getForeignKeys(getDestination()).iterator().next(), "IS", (Object) null);
403
                input.setWhere(w2);
404
 
405
                System.err.println(input.asString());
406
                return input;
407
            }
408
        });
409
    }
410
 
411
    /**
412
     * register manually a transfer, use with caution
413
     *
414
     * @throws SQLException
415
     * */
416
    public void addTransfert(int idFrom, int idTo) throws SQLException {
417
        final SQLTable tableTransfert = getSource().getTable(getTableTranferName());
418
        final SQLRowValues rowTransfer = new SQLRowValues(tableTransfert);
419
        final Set<SQLField> foreignKeysSrc = tableTransfert.getForeignKeys(getSource());
420
        final Set<SQLField> foreignKeysDest = tableTransfert.getForeignKeys(getDestination());
421
        rowTransfer.put(foreignKeysSrc.iterator().next().getName(), idFrom);
422
        rowTransfer.put(foreignKeysDest.iterator().next().getName(), idTo);
423
        rowTransfer.commit();
424
 
425
    }
426
 
17 ilm 427
}