OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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