OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 142 | 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
 
142 ilm 16
import static org.openconcerto.xml.JDOM2Utils.OUTPUTTER;
17
 
67 ilm 18
import org.openconcerto.sql.model.graph.TablesMap;
19
import org.openconcerto.sql.utils.ChangeTable;
20
import org.openconcerto.sql.utils.ChangeTable.ClauseType;
21
import org.openconcerto.sql.utils.ChangeTable.DeferredClause;
65 ilm 22
import org.openconcerto.sql.utils.SQLCreateMoveableTable;
17 ilm 23
import org.openconcerto.sql.utils.SQLUtils;
67 ilm 24
import org.openconcerto.utils.Tuple2;
61 ilm 25
import org.openconcerto.utils.cc.CopyOnWriteMap;
17 ilm 26
import org.openconcerto.utils.change.CollectionChangeEventCreator;
27
 
28
import java.io.IOException;
29
import java.sql.DatabaseMetaData;
30
import java.sql.ResultSet;
31
import java.sql.SQLException;
67 ilm 32
import java.util.ArrayList;
33
import java.util.Arrays;
17 ilm 34
import java.util.Collections;
35
import java.util.HashMap;
36
import java.util.HashSet;
37
import java.util.List;
38
import java.util.Map;
39
import java.util.Map.Entry;
40
import java.util.Set;
41
 
67 ilm 42
import org.apache.commons.dbutils.ResultSetHandler;
83 ilm 43
import org.jdom2.Element;
17 ilm 44
 
142 ilm 45
import net.jcip.annotations.GuardedBy;
46
 
17 ilm 47
public final class SQLSchema extends SQLIdentifier {
48
 
49
    /**
50
     * set this system property to avoid writing to the db (be it CREATE TABLE or
51
     * INSERT/UPDATE/DELETE)
52
     */
53
    public static final String NOAUTO_CREATE_METADATA = "org.openconcerto.sql.noautoCreateMetadata";
54
 
41 ilm 55
    public static final String FWK_TABLENAME_PREFIX = "FWK_";
151 ilm 56
    public static final String METADATA_TABLENAME = FWK_TABLENAME_PREFIX + "SCHEMA_METADATA";
17 ilm 57
    private static final String VERSION_MDKEY = "VERSION";
58
    private static final String VERSION_XMLATTR = "schemaVersion";
59
 
60
    public static final void getVersionAttr(final SQLSchema schema, final Appendable sb) {
67 ilm 61
        final String version = schema.getFullyRefreshedVersion();
62
        try {
63
            appendVersionAttr(version, sb);
64
        } catch (IOException e) {
65
            throw new IllegalStateException("Couldn't append version of " + schema, e);
66
        }
67
    }
68
 
69
    public static final void appendVersionAttr(final String version, final StringBuilder sb) {
70
        try {
71
            appendVersionAttr(version, (Appendable) sb);
72
        } catch (IOException e) {
73
            throw new IllegalStateException("Couldn't append version" + version, e);
74
        }
75
    }
76
 
77
    public static final void appendVersionAttr(final String version, final Appendable sb) throws IOException {
17 ilm 78
        if (version != null) {
67 ilm 79
            sb.append(' ');
80
            sb.append(VERSION_XMLATTR);
81
            sb.append("=\"");
142 ilm 82
            sb.append(OUTPUTTER.escapeAttributeEntities(version));
67 ilm 83
            sb.append('"');
17 ilm 84
        }
85
    }
86
 
87
    public static final String getVersion(final Element schemaElem) {
88
        return schemaElem.getAttributeValue(VERSION_XMLATTR);
89
    }
90
 
83 ilm 91
    public static final Map<String, String> getVersions(final SQLBase base, final Set<String> schemaNames) {
67 ilm 92
        // since we haven't an instance of SQLSchema, we can't know if the table exists
83 ilm 93
        return base.getFwkMetadata(schemaNames, VERSION_MDKEY);
17 ilm 94
    }
95
 
67 ilm 96
    static private String getVersionSQL(final SQLSyntax syntax) {
83 ilm 97
        return syntax.getFormatTimestamp("CURRENT_TIMESTAMP", true);
67 ilm 98
    }
99
 
100
    static SQLCreateMoveableTable getCreateMetadata(final SQLSyntax syntax) throws SQLException {
65 ilm 101
        if (Boolean.getBoolean(NOAUTO_CREATE_METADATA))
102
            return null;
103
        final SQLCreateMoveableTable create = new SQLCreateMoveableTable(syntax, METADATA_TABLENAME);
104
        create.addVarCharColumn("NAME", 100).addVarCharColumn("VALUE", 250);
105
        create.setPrimaryKey("NAME");
67 ilm 106
        create.addOutsideClause(new DeferredClause() {
107
            @Override
108
            public String asString(ChangeTable<?> ct, SQLName tableName) {
109
                return syntax.getInsertOne(tableName, Arrays.asList("NAME", "VALUE"), SQLBase.quoteStringStd(VERSION_MDKEY), getVersionSQL(syntax));
110
            }
111
 
112
            @Override
113
            public ClauseType getType() {
114
                return ClauseType.OTHER;
115
            }
116
        });
65 ilm 117
        return create;
118
    }
119
 
67 ilm 120
    // last DB structure version when we were fully refreshed
121
    @GuardedBy("this")
122
    private String version;
61 ilm 123
    private final CopyOnWriteMap<String, SQLTable> tables;
17 ilm 124
    // name -> src
125
    private final Map<String, String> procedures;
61 ilm 126
    @GuardedBy("this")
17 ilm 127
    private boolean fetchAllUndefIDs = true;
128
 
129
    SQLSchema(SQLBase base, String name) {
130
        super(base, name);
61 ilm 131
        this.tables = new CopyOnWriteMap<String, SQLTable>();
132
        this.procedures = new CopyOnWriteMap<String, String>();
17 ilm 133
    }
134
 
135
    public final SQLBase getBase() {
136
        return (SQLBase) this.getParent();
137
    }
138
 
83 ilm 139
    @Override
140
    protected void onDrop() {
141
        SQLTable.removeUndefID(this);
142
        super.onDrop();
143
    }
144
 
73 ilm 145
    /**
146
     * The version when this instance was last fully refreshed. In other words, if we refresh tables
147
     * by names (even if we name them all) this version isn't updated.
148
     *
149
     * @return the version.
150
     */
67 ilm 151
    synchronized final String getFullyRefreshedVersion() {
152
        return this.version;
153
    }
154
 
155
    synchronized final void setFullyRefreshedVersion(final String vers) {
156
        this.version = vers;
157
    }
158
 
17 ilm 159
    // ** procedures
160
 
161
    /**
162
     * Return the procedures names and if possible their source.
163
     *
164
     * @return the procedures in this schema.
165
     */
166
    public final Map<String, String> getProcedures() {
167
        return Collections.unmodifiableMap(this.procedures);
168
    }
169
 
61 ilm 170
    final void putProcedures(final Map<String, String> m) {
171
        this.procedures.putAll(m);
17 ilm 172
    }
173
 
174
    // clear the attributes that are not preserved (ie SQLTable) but recreated each time (ie
175
    // procedure)
176
    void clearNonPersistent() {
177
        this.procedures.clear();
178
    }
179
 
61 ilm 180
    // XMLStructureSource always pre-verify so we don't need the system root lock
67 ilm 181
    void load(Element schemaElem, Set<String> tableNames) {
182
        this.setFullyRefreshedVersion(getVersion(schemaElem));
83 ilm 183
        for (final Element elementTable : schemaElem.getChildren("table")) {
67 ilm 184
            this.refreshTable(elementTable, tableNames);
17 ilm 185
        }
61 ilm 186
        final Map<String, String> procMap = new HashMap<String, String>();
83 ilm 187
        for (final Element procElem : schemaElem.getChild("procedures").getChildren("proc")) {
17 ilm 188
            final Element src = procElem.getChild("src");
61 ilm 189
            procMap.put(procElem.getAttributeValue("name"), src == null ? null : src.getText());
17 ilm 190
        }
61 ilm 191
        this.putProcedures(procMap);
17 ilm 192
    }
193
 
61 ilm 194
    /**
195
     * Fetch table from the DB.
196
     *
197
     * @param tableName the name of the table to fetch.
198
     * @return the up to date table, <code>null</code> if not found
199
     * @throws SQLException if an error occurs.
200
     */
65 ilm 201
    final SQLTable fetchTable(final String tableName) throws SQLException {
61 ilm 202
        synchronized (getTreeMutex()) {
83 ilm 203
            this.getBase().fetchTables(TablesMap.createFromTables(getName(), Collections.singleton(tableName)));
204
            return this.getTable(tableName);
61 ilm 205
        }
206
    }
207
 
17 ilm 208
    void mutateTo(SQLSchema newSchema) {
61 ilm 209
        assert Thread.holdsLock(this.getDBSystemRoot().getTreeMutex());
210
        synchronized (this) {
67 ilm 211
            this.version = newSchema.version;
61 ilm 212
            this.clearNonPersistent();
213
            this.putProcedures(newSchema.procedures);
67 ilm 214
            // since one can refresh only some tables, newSchema is a subset of this
215
            for (final SQLTable t : newSchema.getTables()) {
216
                this.getTable(t.getName()).mutateTo(t);
61 ilm 217
            }
17 ilm 218
        }
219
    }
220
 
221
    // ** tables
222
 
61 ilm 223
    final SQLTable addTable(String tableName) {
224
        synchronized (getTreeMutex()) {
225
            return this.addTableWithoutSysRootLock(tableName);
226
        }
227
    }
228
 
229
    final SQLTable addTableWithoutSysRootLock(String tableName) {
17 ilm 230
        if (this.contains(tableName))
231
            throw new IllegalStateException(tableName + " already in " + this);
232
        final CollectionChangeEventCreator c = this.createChildrenCreator();
61 ilm 233
        final SQLTable res = new SQLTable(this, tableName);
234
        this.tables.put(tableName, res);
17 ilm 235
        this.fireChildrenChanged(c);
61 ilm 236
        return res;
17 ilm 237
    }
238
 
67 ilm 239
    private final void refreshTable(Element tableElem, Set<String> tableNames) {
17 ilm 240
        final String tableName = tableElem.getAttributeValue("name");
67 ilm 241
        if (tableNames.contains(tableName))
242
            this.getTable(tableName).loadFields(tableElem);
17 ilm 243
    }
244
 
245
    /**
246
     * Refresh the table of the current row of rs.
247
     *
248
     * @param metaData the metadata.
249
     * @param rs the resultSet from getColumns().
67 ilm 250
     * @param version the version of the schema.
17 ilm 251
     * @return whether <code>rs</code> has a next row, <code>null</code> if the current row is not
252
     *         part of this, and thus rs hasn't moved.
253
     * @throws SQLException
254
     */
67 ilm 255
    final Boolean refreshTable(DatabaseMetaData metaData, ResultSet rs, final String version) throws SQLException {
61 ilm 256
        synchronized (getTreeMutex()) {
257
            synchronized (this) {
258
                final String tableName = rs.getString("TABLE_NAME");
259
                if (this.contains(tableName)) {
67 ilm 260
                    return this.getTable(tableName).fetchFields(metaData, rs, version);
61 ilm 261
                } else {
262
                    // eg in pg getColumns() return columns of BATIMENT_ID_seq
263
                    return null;
264
                }
265
            }
17 ilm 266
        }
267
    }
268
 
269
    final void rmTable(String tableName) {
61 ilm 270
        synchronized (getTreeMutex()) {
271
            this.rmTableWithoutSysRootLock(tableName);
272
        }
273
    }
274
 
67 ilm 275
    private final void rmTableWithoutSysRootLock(String tableName) {
17 ilm 276
        final CollectionChangeEventCreator c = this.createChildrenCreator();
277
        final SQLTable tableToDrop = this.tables.remove(tableName);
278
        this.fireChildrenChanged(c);
279
        if (tableToDrop != null)
280
            tableToDrop.dropped();
281
    }
282
 
283
    public final SQLTable getTable(String tablename) {
284
        return this.tables.get(tablename);
285
    }
286
 
287
    /**
288
     * Return the tables in this schema.
289
     *
290
     * @return an unmodifiable Set of the tables' names.
291
     */
292
    public Set<String> getTableNames() {
293
        return Collections.unmodifiableSet(this.tables.keySet());
294
    }
295
 
296
    /**
297
     * Return all the tables in this schema.
298
     *
299
     * @return a Set of SQLTable.
300
     */
301
    public Set<SQLTable> getTables() {
302
        return new HashSet<SQLTable>(this.tables.values());
303
    }
304
 
305
    @Override
61 ilm 306
    public Map<String, SQLTable> getChildrenMap() {
307
        return this.tables.getImmutable();
17 ilm 308
    }
309
 
310
    @Override
311
    public String toString() {
312
        return this.getClass().getSimpleName() + " " + this.getName();
313
    }
314
 
315
    public String toXML() {
67 ilm 316
        // always save even without version, as some tables might still be up to date
317
 
17 ilm 318
        // a table is about 16000 characters
319
        final StringBuilder sb = new StringBuilder(16000 * 16);
320
        sb.append("<schema ");
321
        if (this.getName() != null) {
322
            sb.append(" name=\"");
142 ilm 323
            sb.append(OUTPUTTER.escapeAttributeEntities(this.getName()));
17 ilm 324
            sb.append('"');
325
        }
61 ilm 326
        synchronized (getTreeMutex()) {
327
            synchronized (this) {
328
                getVersionAttr(this, sb);
329
                sb.append(" >\n");
17 ilm 330
 
61 ilm 331
                sb.append("<procedures>\n");
332
                for (final Entry<String, String> e : this.procedures.entrySet()) {
333
                    sb.append("<proc name=\"");
142 ilm 334
                    sb.append(OUTPUTTER.escapeAttributeEntities(e.getKey()));
61 ilm 335
                    sb.append("\" ");
336
                    if (e.getValue() == null) {
337
                        sb.append("/>");
338
                    } else {
339
                        sb.append("><src>");
142 ilm 340
                        sb.append(OUTPUTTER.escapeElementEntities(e.getValue()));
61 ilm 341
                        sb.append("</src></proc>\n");
342
                    }
343
                }
344
                sb.append("</procedures>\n");
345
                for (final SQLTable table : this.getTables()) {
346
                    // passing our sb to table don't go faster
347
                    sb.append(table.toXML());
348
                    sb.append("\n");
349
                }
350
                sb.append("</schema>");
17 ilm 351
            }
352
        }
353
 
354
        return sb.toString();
355
    }
356
 
357
    String getFwkMetadata(String name) {
358
        if (!this.contains(METADATA_TABLENAME))
359
            return null;
360
 
67 ilm 361
        // we just tested for table existence
83 ilm 362
        return this.getBase().getFwkMetadata(this.getName(), name);
17 ilm 363
    }
364
 
365
    boolean setFwkMetadata(String name, String value) throws SQLException {
67 ilm 366
        return this.setFwkMetadata(name, value, true).get0();
17 ilm 367
    }
368
 
369
    /**
370
     * Set the value of a metadata.
371
     *
67 ilm 372
     * @param name name of the metadata, e.g. "Customer".
373
     * @param sqlExpr SQL value of the metadata, e.g. "'ACME, inc'".
17 ilm 374
     * @param createTable whether the metadata table should be automatically created if necessary.
67 ilm 375
     * @return <code>true</code> if the value was set, <code>false</code> otherwise ; the new value
376
     *         (<code>null</code> if the value wasn't set, i.e. if the value cannot be
377
     *         <code>null</code> the boolean isn't needed).
17 ilm 378
     * @throws SQLException if an error occurs while setting the value.
379
     */
67 ilm 380
    Tuple2<Boolean, String> setFwkMetadata(String name, String sqlExpr, boolean createTable) throws SQLException {
17 ilm 381
        if (Boolean.getBoolean(NOAUTO_CREATE_METADATA))
67 ilm 382
            return Tuple2.create(false, null);
17 ilm 383
 
67 ilm 384
        final SQLSystem sys = getServer().getSQLSystem();
142 ilm 385
        final SQLSyntax syntax = this.getDBSystemRoot().getSyntax();
67 ilm 386
        final SQLDataSource ds = this.getDBSystemRoot().getDataSource();
61 ilm 387
        synchronized (this.getTreeMutex()) {
388
            // don't refresh until after the insert, that way if the refresh triggers an access to
389
            // the metadata name will already be set to value.
390
            final boolean shouldRefresh;
391
            if (createTable && !this.contains(METADATA_TABLENAME)) {
67 ilm 392
                final SQLCreateMoveableTable create = getCreateMetadata(syntax);
393
                ds.execute(create.asString(getDBRoot().getName()));
61 ilm 394
                shouldRefresh = true;
67 ilm 395
            } else {
61 ilm 396
                shouldRefresh = false;
67 ilm 397
            }
61 ilm 398
 
67 ilm 399
            final Tuple2<Boolean, String> res;
61 ilm 400
            if (createTable || this.contains(METADATA_TABLENAME)) {
401
                // don't use SQLRowValues, cause it means getting the SQLTable and thus calling
402
                // fetchTables(), but setFwkMetadata() might itself be called by fetchTables()
403
                // furthermore SQLRowValues support only rowable tables
67 ilm 404
 
405
                final List<String> queries = new ArrayList<String>();
406
 
61 ilm 407
                final SQLName tableName = new SQLName(this.getBase().getName(), this.getName(), METADATA_TABLENAME);
67 ilm 408
                final String where = " WHERE " + SQLBase.quoteIdentifier("NAME") + " = " + getBase().quoteString(name);
409
                queries.add("DELETE FROM " + tableName.quote() + where);
410
 
411
                final String returning = sys == SQLSystem.POSTGRESQL ? " RETURNING " + SQLBase.quoteIdentifier("VALUE") : "";
412
                final String ins = syntax.getInsertOne(tableName, Arrays.asList("NAME", "VALUE"), getBase().quoteString(name), sqlExpr) + returning;
413
                queries.add(ins);
414
 
415
                final List<? extends ResultSetHandler> handlers;
416
                if (returning.length() == 0) {
417
                    queries.add("SELECT " + SQLBase.quoteIdentifier("VALUE") + " FROM " + tableName.quote() + where);
418
                    handlers = Arrays.asList(null, null, SQLDataSource.SCALAR_HANDLER);
419
                } else {
420
                    handlers = Arrays.asList(null, SQLDataSource.SCALAR_HANDLER);
421
                }
422
 
423
                final List<?> ress = SQLUtils.executeMultiple(getDBSystemRoot(), queries, handlers);
424
                res = Tuple2.create(true, (String) ress.get(ress.size() - 1));
425
            } else {
426
                res = Tuple2.create(false, null);
427
            }
61 ilm 428
            if (shouldRefresh)
65 ilm 429
                this.fetchTable(METADATA_TABLENAME);
61 ilm 430
            return res;
431
        }
17 ilm 432
    }
433
 
73 ilm 434
    /**
435
     * The current version in the database.
436
     *
437
     * @return current version in the database.
438
     */
17 ilm 439
    public final String getVersion() {
440
        return this.getFwkMetadata(VERSION_MDKEY);
441
    }
442
 
19 ilm 443
    // TODO assure that the updated version is different that current one and unique
67 ilm 444
    // If we have a fast in-memory DB, some additions might get lost
19 ilm 445
    // Perhaps something like VERSION = date seconds || '_' || (select cast( substring(indexof '_')
446
    // as int ) + 1 from VERSION) ; e.g. 20110701-1021_01352
17 ilm 447
    public final String updateVersion() throws SQLException {
65 ilm 448
        return this.updateVersion(true);
449
    }
450
 
451
    final String updateVersion(boolean createTable) throws SQLException {
142 ilm 452
        // don't use the VM time as it can vary between clients, use the server time
67 ilm 453
        return this.setFwkMetadata(SQLSchema.VERSION_MDKEY, getVersionSQL(SQLSyntax.get(this)), createTable).get1();
17 ilm 454
    }
455
 
61 ilm 456
    public synchronized final void setFetchAllUndefinedIDs(final boolean b) {
17 ilm 457
        this.fetchAllUndefIDs = b;
458
    }
459
 
460
    /**
461
     * A boolean indicating if one {@link SQLTable#getUndefinedID()} should fetch IDs for the whole
462
     * schema or just that table. The default is true which is faster but requires that all tables
463
     * are coherent.
464
     *
465
     * @return <code>true</code> if all undefined IDs are fetched together.
466
     */
61 ilm 467
    public synchronized final boolean isFetchAllUndefinedIDs() {
17 ilm 468
        return this.fetchAllUndefIDs;
469
    }
470
}