OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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