OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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