OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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