OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 19 | Rev 65 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
18 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.erp.utils;
15
 
16
import org.openconcerto.erp.config.ComptaPropsConfiguration;
17
import org.openconcerto.erp.model.PrixTTC;
18
import org.openconcerto.sql.Configuration;
19
import org.openconcerto.sql.PropsConfiguration;
20
import org.openconcerto.sql.changer.correct.FixSerial;
19 ilm 21
import org.openconcerto.sql.model.DBRoot;
18 ilm 22
import org.openconcerto.sql.model.DBStructureItemDB;
19 ilm 23
import org.openconcerto.sql.model.DBSystemRoot;
18 ilm 24
import org.openconcerto.sql.model.SQLBase;
19 ilm 25
import org.openconcerto.sql.model.SQLDataSource;
18 ilm 26
import org.openconcerto.sql.model.SQLField;
27
import org.openconcerto.sql.model.SQLRowValues;
28
import org.openconcerto.sql.model.SQLSchema;
29
import org.openconcerto.sql.model.SQLSelect;
19 ilm 30
import org.openconcerto.sql.model.SQLSyntax;
31
import org.openconcerto.sql.model.SQLSystem;
18 ilm 32
import org.openconcerto.sql.model.SQLTable;
19 ilm 33
import org.openconcerto.sql.utils.SQLCreateRoot;
18 ilm 34
import org.openconcerto.utils.ExceptionHandler;
35
 
36
import java.sql.DatabaseMetaData;
37
import java.sql.ResultSet;
38
import java.sql.SQLException;
19 ilm 39
import java.util.Collections;
18 ilm 40
import java.util.HashMap;
41
import java.util.HashSet;
42
import java.util.Iterator;
43
import java.util.List;
44
import java.util.Map;
45
import java.util.Properties;
46
import java.util.Set;
47
 
48
import org.apache.commons.dbutils.handlers.ArrayListHandler;
49
 
50
public class ActionDB {
51
 
52
    static private final Properties props;
53
    static {
54
        props = new Properties();
55
        props.put(PropsConfiguration.JDBC_CONNECTION + "allowMultiQueries", "true");
56
    }
57
 
58
    /**
59
     * Création d'une société à partir de la base GestionDefault
60
     *
61
     * @param baseDefault nom de la base par défaut
62
     * @param newBase nom de la nouvelle base
63
     */
19 ilm 64
    public static void dupliqueDB(String baseDefault, String newBase, StatusListener l) {
65
        final DBSystemRoot sysRoot = Configuration.getInstance().getSystemRoot();
18 ilm 66
 
67
        // FIXME ADD TRIGGER TO UPDATE SOLDE COMPTE_PCE
68
        // ComptaPropsConfiguration instance = ComptaPropsConfiguration.create();
69
        // Configuration.setInstance(instance);
70
 
71
        try {
72
            log(l, "Création du schéma");
19 ilm 73
            if (!sysRoot.getRootsToMap().contains(baseDefault)) {
74
                sysRoot.getRootsToMap().add(baseDefault);
75
                sysRoot.refetch(Collections.singleton(baseDefault));
76
            }
77
            final DBRoot baseSQLDefault = sysRoot.getRoot(baseDefault);
78
            log(l, "Traitement des " + baseSQLDefault.getChildrenNames().size() + " tables");
18 ilm 79
 
19 ilm 80
            final SQLCreateRoot createRoot = baseSQLDefault.getDefinitionSQL(sysRoot.getServer().getSQLSystem());
81
            final SQLDataSource ds = sysRoot.getDataSource();
82
            // be safe don't add DROP SCHEMA
83
            ds.execute(createRoot.asString(newBase, false, true));
84
            sysRoot.getRootsToMap().add(newBase);
85
            // TODO find a more functional way
86
            final boolean origVal = Boolean.getBoolean(SQLSchema.NOAUTO_CREATE_METADATA);
87
            if (!origVal)
88
                System.setProperty(SQLSchema.NOAUTO_CREATE_METADATA, "true");
89
            sysRoot.refetch(Collections.singleton(newBase));
90
            if (!origVal)
91
                System.setProperty(SQLSchema.NOAUTO_CREATE_METADATA, "false");
92
            final DBRoot baseSQLNew = sysRoot.getRoot(newBase);
18 ilm 93
 
19 ilm 94
            final Set<SQLTable> newTables = baseSQLNew.getTables();
95
            int i = 0;
96
            final SQLSyntax syntax = sysRoot.getServer().getSQLSystem().getSyntax();
97
            // MAYBE SQLCreateRoot can avoid creating foreign constraints, then we insert data,
98
            // finally SQLCreateRoot adds just the constraints
99
            ds.execute(syntax.disableFKChecks(baseSQLNew));
100
            for (final SQLTable table : newTables) {
18 ilm 101
                String tableName = table.getName();
102
 
19 ilm 103
                log(l, "Copie de la table " + tableName + " " + (i + 1) + "/" + newTables.size());
18 ilm 104
                // Dump Table
19 ilm 105
                dumpTable(baseSQLDefault, table);
106
                log(l, "Table " + tableName + " " + (i + 1) + "/" + newTables.size() + " OK");
107
                i++;
18 ilm 108
            }
19 ilm 109
            ds.execute(syntax.enableFKChecks(baseSQLNew));
18 ilm 110
 
19 ilm 111
            if (syntax.getSystem() == SQLSystem.POSTGRESQL) {
112
                log(l, "Maj des séquences des tables");
113
                new FixSerial(sysRoot).changeAll(baseSQLNew);
114
            }
18 ilm 115
 
19 ilm 116
 
18 ilm 117
            log(l, "Duplication terminée");
118
 
20 ilm 119
        } catch (Throwable e) {
18 ilm 120
            e.printStackTrace();
121
            ExceptionHandler.handle("Erreur pendant la création de la base!", e);
122
            log(l, "Erreur pendant la duplication");
123
        }
124
 
125
    }
126
 
127
    private static void log(StatusListener l, String message) {
128
        if (l != null) {
129
            l.statusChanged(message);
130
        }
131
    }
132
 
133
    /**
134
     * copie l'integralite de la table "tableName" de la base "base" dans la nouvelle base "baseNew"
135
     *
136
     * @param base
137
     * @param baseNew
138
     * @param tableName
139
     */
19 ilm 140
    private static void dumpTable(DBRoot source, SQLTable newTable) {
18 ilm 141
        try {
19 ilm 142
            SQLRowValues.insertFromTable(newTable, source.getTable(newTable.getName()));
18 ilm 143
        } catch (SQLException e) {
19 ilm 144
            System.err.println("Unable to dump table " + newTable.getName());
18 ilm 145
            e.printStackTrace();
146
        }
147
    }
148
 
149
    /**
150
     * Affiche si il y a des différences entre les tables de base et baseDefault
151
     *
152
     * @param base
153
     * @param baseDefault
154
     */
155
    public static void compareDB(int base, int baseDefault) {
156
 
157
        try {
158
            if (Configuration.getInstance() == null) {
159
                Configuration.setInstance(ComptaPropsConfiguration.create());
160
            }
161
            Configuration instance = Configuration.getInstance();
162
            SQLTable tableSociete = Configuration.getInstance().getBase().getTable("SOCIETE_COMMON");
163
 
164
            String baseName = tableSociete.getRow(base).getString("DATABASE_NAME");
165
            String baseDefaultName = tableSociete.getRow(baseDefault).getString("DATABASE_NAME");
166
 
167
            instance.getBase().getDBSystemRoot().getRootsToMap().clear();
168
            try {
169
                Set<String> s = new HashSet<String>();
170
                s.add(baseName);
171
                s.add(baseDefaultName);
172
                instance.getBase().fetchTables(s);
173
            } catch (SQLException e) {
174
                throw new IllegalStateException("could not access societe base", e);
175
            }
176
            // instance.getBase().getDBRoot(baseName);
177
            // instance.getBase().getDBRoot(baseDefaultName);
178
            System.err.println("baseName" + baseName);
179
            System.err.println("baseDefault" + baseDefaultName);
180
            instance.getSystemRoot().prependToRootPath("Common");
181
            instance.getSystemRoot().prependToRootPath(baseName);
182
            instance.getSystemRoot().prependToRootPath(baseDefaultName);
183
 
184
            SQLSchema baseSQL = instance.getBase().getSchema(baseName);
185
            SQLSchema baseSQLDefault = instance.getBase().getSchema(baseDefaultName);
186
 
187
            DatabaseMetaData dbMetaDataSociete = baseSQL.getBase().getDataSource().getConnection().getMetaData();
188
            DatabaseMetaData dbMetaDataSocieteDefault = baseSQLDefault.getBase().getDataSource().getConnection().getMetaData();
189
 
190
            Map<String, Map<String, SQLField>> mapTableSociete = new HashMap<String, Map<String, SQLField>>();
191
            Map<String, Map<String, SQLField>> mapTableSocieteDefault = new HashMap<String, Map<String, SQLField>>();
192
 
193
            ResultSet rs = dbMetaDataSociete.getTables("", baseSQL.getName(), "%", null);
194
 
195
            System.err.println("Start");
196
 
197
            while (rs.next()) {
198
                // System.err.println(rs.getString("TABLE_NAME") + ", TYPE ::" +
199
                // rs.getString("TABLE_TYPE"));
200
 
201
                if (rs.getString("TABLE_TYPE") != null && rs.getString("TABLE_TYPE").equalsIgnoreCase("TABLE")) {
202
                    Map<String, SQLField> m = new HashMap<String, SQLField>();
203
                    baseSQL.getTableNames();
204
                    Set<SQLField> s = baseSQL.getTable(rs.getString("TABLE_NAME")).getFields();
205
                    for (SQLField field : s) {
206
                        m.put(field.getName(), field);
207
                    }
208
                    mapTableSociete.put(rs.getString("TABLE_NAME"), m);
209
                }
210
            }
211
            rs.close();
212
 
213
            rs = dbMetaDataSocieteDefault.getTables("", baseSQLDefault.getName(), "%", null);
214
 
215
            while (rs.next()) {
216
                // System.err.println(rs.getString("TABLE_NAME") + ", TYPE ::" +
217
                // rs.getString("TABLE_TYPE"));
218
                if (rs.getString("TABLE_TYPE") != null && rs.getString("TABLE_TYPE").equalsIgnoreCase("TABLE")) {
219
                    Map<String, SQLField> m = new HashMap<String, SQLField>();
220
                    Set<SQLField> s = baseSQLDefault.getTable(rs.getString("TABLE_NAME")).getFields();
221
                    for (SQLField field : s) {
222
                        m.put(field.getName(), field);
223
                    }
224
                    mapTableSocieteDefault.put(rs.getString("TABLE_NAME"), m);
225
                }
226
            }
227
            rs.close();
228
 
229
            System.err.println("Test 1 " + mapTableSociete.keySet().size());
230
            // On verifie que toutes les tables de la societe sont contenues dans la base default
231
            for (String tableName : mapTableSociete.keySet()) {
232
 
233
                if (!mapTableSocieteDefault.containsKey(tableName)) {
234
                    System.err.println("!! **** La table " + tableName + " n'est pas dans la base " + baseDefault);
235
 
236
                } else {
237
                    Map<String, SQLField> mSoc = mapTableSociete.get(tableName);
238
                    Map<String, SQLField> mDef = mapTableSocieteDefault.get(tableName);
239
                    if (mSoc.keySet().containsAll(mDef.keySet())) {
240
                        if (mSoc.keySet().size() == mDef.keySet().size()) {
241
                            System.err.println("Table " + tableName + " --- OK");
242
                            compareTypeField(mSoc, mDef);
243
                        } else {
244
                            if (mSoc.keySet().size() > mDef.keySet().size()) {
245
                                for (String fieldName : mDef.keySet()) {
246
                                    mSoc.remove(fieldName);
247
                                }
248
                                System.err.println("!! **** Difference Table " + tableName);
249
                                System.err.println(tableSociete.getRow(baseDefault).getString("DATABASE_NAME") + " Set Column " + mSoc);
250
                                System.err.println(getAlterTable(mSoc, tableSociete.getRow(baseDefault).getString("DATABASE_NAME"), tableName));
251
                            } else {
252
 
253
                            }
254
                        }
255
                    } else {
256
                        // System.err.println("!! **** Difference Table " + tableName);
257
                        // System.err.println(tableSociete.getRow(base).getString("DATABASE_NAME") +
258
                        // " Set Column " + mapTableSociete.get(tableName));
259
                        // System.err.println(tableSociete.getRow(baseDefault).getString("DATABASE_NAME")
260
                        // + " Set Column " + mapTableSocieteDefault.get(tableName));
261
                        for (String fieldName : mSoc.keySet()) {
262
                            mDef.remove(fieldName);
263
                        }
264
                        System.err.println("!! **** Difference Table " + tableName);
265
                        System.err.println(tableSociete.getRow(base).getString("DATABASE_NAME") + " Set Column " + mDef);
266
                        System.err.println(getAlterTable(mDef, tableSociete.getRow(base).getString("DATABASE_NAME"), tableName));
267
                    }
268
                }
269
            }
270
 
271
            System.err.println("Test 2 " + mapTableSocieteDefault.keySet().size());
272
            // On verifie que toutes les tables de la base default sont contenues dans la base
273
            // societe
274
            for (Iterator i = mapTableSocieteDefault.keySet().iterator(); i.hasNext();) {
275
                Object tableName = i.next();
276
                if (!mapTableSociete.containsKey(tableName)) {
277
                    System.err.println("!! **** La table " + tableName + " n'est pas dans la base " + baseDefault);
278
                }
279
            }
280
 
281
            SQLSchema schem = instance.getBase().getSchema("Common");
282
 
283
        } catch (SQLException e) {
284
            e.printStackTrace();
285
        }
286
    }
287
 
288
    private static String getAlterTable(Map<String, SQLField> m, String baseName, String tableName) {
289
        StringBuffer buf = new StringBuffer();
290
        for (String s : m.keySet()) {
291
            SQLField field = m.get(s);
292
            buf.append("ALTER TABLE \"" + baseName + "\".\"" + tableName + "\" ADD COLUMN ");
293
            buf.append("\"" + field.getName() + "\" ");
294
            buf.append(getType(field));
295
            buf.append(";\n");
296
        }
297
 
298
        return buf.toString();
299
    }
300
 
301
    private static String getType(SQLField field) {
302
        String columnName = field.getName();
303
        String columnType = field.getType().getTypeName();
304
        StringBuffer result = new StringBuffer();
305
        // NULL OR NOT NULL
306
        // field.getType().getSize();
307
        // String nullable = tableMetaData.getString("IS_NULLABLE");
308
        String nullString = "NULL";
309
        DBStructureItemDB db = field.getDB();
310
        // if ("NO".equalsIgnoreCase(nullable)) {
311
        // nullString = "NOT NULL";
312
        // }
313
 
314
        // DEFAULT
315
        Object defaultValueO = field.getDefaultValue();
316
        String defaultValue = (defaultValueO == null) ? null : defaultValueO.toString();
317
        String defaultValueString = "";
318
        if (defaultValue != null) {
319
            defaultValueString = " default " + defaultValue;
320
        }
321
 
322
        int columnSize = field.getType().getSize();
323
        Integer decimalDigit = (Integer) field.getMetadata("DECIMAL_DIGITS");
324
        String stringColumnSize = "";
325
        if (Integer.valueOf(columnSize).intValue() > 0) {
326
            stringColumnSize = " (" + columnSize;
327
 
328
            if (decimalDigit != null && Integer.valueOf(decimalDigit).intValue() > 0) {
329
                stringColumnSize += ", " + decimalDigit;
330
            }
331
 
332
            stringColumnSize += ")";
333
        }
334
 
335
        if ((columnType.trim().equalsIgnoreCase("character varying") || columnType.trim().equalsIgnoreCase("varchar") || columnType.trim().equalsIgnoreCase("numeric"))
336
                && stringColumnSize.length() > 0) {
337
            result.append(" " + columnType + stringColumnSize + " ");
338
            result.append(defaultValueString);
339
        } else {
340
            result.append(" " + columnType + " ");
341
            result.append(defaultValueString);
342
        }
343
        return result.toString();
344
    }
345
 
346
    private static void compareTypeField(Map fieldsDefault, Map fields) {
347
        for (Iterator i = fieldsDefault.keySet().iterator(); i.hasNext();) {
348
 
349
            Object o = i.next();
350
            SQLField field = (SQLField) fieldsDefault.get(o);
351
            SQLField fieldDefault = (SQLField) fields.get(o);
352
 
353
            if (field != null && fieldDefault != null && field.getType() != fieldDefault.getType()) {
354
                System.err.println("---------> Type different Table " + field.getTable() + " -- Field " + field.getName());
355
            }
356
        }
357
    }
358
 
359
    private static void updateMultiBase() {
360
        // Calcul automatique du ht des saisies de vente avec facture
361
 
362
        System.err.println("Start");
363
        // on recupere les differentes bases
364
        ComptaPropsConfiguration instance = ComptaPropsConfiguration.create();
365
        Configuration.setInstance(instance);
366
        SQLBase base = Configuration.getInstance().getBase();
367
        SQLTable tableBase = base.getTable("SOCIETE_COMMON");
368
 
369
        SQLSelect sel = new SQLSelect(base, false);
370
        sel.addSelect(tableBase.getField("DATABASE_NAME"));
371
 
372
        List listBasesNX = (List) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler());
373
 
374
        // for (int i = 0; i < listBasesNX.size(); i++) {
375
        // Object[] tmp = (Object[]) listBasesNX.get(i);
376
        //
377
        // setOrdreComptePCE(tmp[0].toString());
378
        // }
379
 
380
        reOrderCompteID("Default");
381
        System.err.println("End");
382
    }
383
 
384
    private static void updateSaisieVC(String databaseName) {
385
 
386
        System.err.println("Update " + databaseName);
387
        String select = "SELECT MONTANT_TTC, TAUX, ID FROM \"" + databaseName + "\".SAISIE_VENTE_COMPTOIR, \"" + databaseName + "\".TAXE";
388
        System.err.println("Request " + select);
389
        List listBaseNX = (List) Configuration.getInstance().getBase().getDataSource().execute(select, new ArrayListHandler());
390
 
391
        for (int i = 0; i < listBaseNX.size(); i++) {
392
            Object[] tmp = (Object[]) listBaseNX.get(i);
393
            PrixTTC p = new PrixTTC(Long.parseLong(tmp[0].toString()));
394
            long ht = p.calculLongHT(((Float) tmp[1]).doubleValue() / 100.0);
395
 
396
            // Update Value
397
            String updateVC = "UPDATE \"" + databaseName + "\".SAISIE_VENTE_COMPTOIR SET MONTANT_HT = " + ht + " WHERE ID=" + tmp[2];
398
            Configuration.getInstance().getBase().execute(updateVC);
399
        }
400
    }
401
 
402
    public static void setOrdreComptePCE(String databaseName) {
403
        String select = "SELECT ID, ORDRE FROM \"" + databaseName + "\".COMPTE_PCE ORDER BY NUMERO";
404
        List listBaseNX = (List) Configuration.getInstance().getBase().getDataSource().execute(select, new ArrayListHandler());
405
        for (int i = 0; i < listBaseNX.size(); i++) {
406
            Object[] tmp = (Object[]) listBaseNX.get(i);
407
 
408
            String update = "UPDATE \"" + databaseName + "\".COMPTE_PCE SET ORDRE=" + (i + 1) + " WHERE ID=" + tmp[0];
409
            Configuration.getInstance().getBase().execute(update);
410
        }
411
    }
412
 
413
    public static void reOrderCompteID(String databaseName) {
414
        String select = "SELECT ID, ORDRE FROM \"" + databaseName + "\".COMPTE_PCE WHERE ID > 1 ORDER BY NUMERO";
415
 
416
        List listBaseNX = (List) Configuration.getInstance().getBase().getDataSource().execute(select, new ArrayListHandler());
417
        for (int i = 0; i < listBaseNX.size(); i++) {
418
            Object[] tmp = (Object[]) listBaseNX.get(i);
419
            int id = Integer.valueOf(tmp[0].toString()).intValue();
420
            String update = "UPDATE \"" + databaseName + "\".COMPTE_PCE SET ID=" + (id + 1000) + " WHERE ID=" + id;
421
            Configuration.getInstance().getBase().execute(update);
422
        }
423
 
424
        select = "SELECT ID, ORDRE FROM \"" + databaseName + "\".COMPTE_PCE WHERE ID > 1 ORDER BY NUMERO";
425
 
426
        listBaseNX = (List) Configuration.getInstance().getBase().getDataSource().execute(select, new ArrayListHandler());
427
        for (int i = 0; i < listBaseNX.size(); i++) {
428
            Object[] tmp = (Object[]) listBaseNX.get(i);
429
            int id = Integer.valueOf(tmp[0].toString()).intValue();
430
            String update = "UPDATE \"" + databaseName + "\".COMPTE_PCE SET ID=" + (i + 2) + " WHERE ID=" + id;
431
            Configuration.getInstance().getBase().execute(update);
432
        }
433
 
434
    }
435
 
436
    public static void setOrder(SQLBase base) {
437
        Set<String> tableNames = base.getTableNames();
438
        for (String tableName : tableNames) {
439
            SQLTable table = base.getTable(tableName);
440
            // SQLField fieldPrimaryKey = table.getKey();
441
            SQLField field = table.getOrderField();
442
 
443
            if (field != null) {
444
                base.execute("ALTER TABLE \"" + tableName + "\" ALTER COLUMN \"" + field.getName() + "\" SET DEFAULT 0;");
445
                base.execute("ALTER TABLE \"" + tableName + "\" ALTER COLUMN \"" + field.getName() + "\" SET NOT NULL;");
446
            }
447
        }
448
    }
449
 
450
    /**
451
     * Check si la table posséde au moins une ligne avec un ordre different null le cas cas échéant
452
     * le crée
453
     *
454
     * @param base
455
     */
456
    public static void correct(SQLBase base) {
457
        Set<String> tableNames = base.getTableNames();
458
        for (String tableName : tableNames) {
459
 
460
            if (base.getTable(tableName).contains("ORDRE")) {
461
                SQLSelect select = new SQLSelect(base);
462
                select.addSelect("ORDRE");
463
                List l = base.getDataSource().execute(select.asString());
464
                if (l == null || l.size() == 0) {
465
                    SQLRowValues rowVals = new SQLRowValues(base.getTable(tableName));
466
                    rowVals.put("ORDRE", 0);
467
                    try {
468
                        rowVals.commit();
469
                    } catch (SQLException e) {
470
 
471
                        e.printStackTrace();
472
                    }
473
                }
474
            }
475
        }
476
        // TODO Checker que toutes les tables sont dans FWK_UNDEFINED_ID
477
    }
478
 
479
    public static void addUndefined(SQLBase base) {
480
        Set<String> tableNames = base.getTableNames();
481
        for (String tableName : tableNames) {
482
            SQLTable table = base.getTable(tableName);
483
            SQLField fieldPrimaryKey = table.getKey();
484
 
485
            if (fieldPrimaryKey != null && fieldPrimaryKey.getType().getJavaType().getSuperclass() != null && fieldPrimaryKey.getType().getJavaType().getSuperclass() == Number.class) {
486
 
487
                String patch = "INSERT INTO \"" + tableName + "\"(\"" + fieldPrimaryKey.getName() + "\") VALUES (1)";
488
                base.execute(patch);
489
            }
490
        }
491
 
492
    }
493
 
494
    public static void fixUserCommon(int base) {
495
 
496
        if (Configuration.getInstance() == null) {
497
            Configuration.setInstance(ComptaPropsConfiguration.create());
498
        }
499
        Configuration instance = Configuration.getInstance();
500
        SQLTable tableSociete = Configuration.getInstance().getBase().getTable("SOCIETE_COMMON");
501
 
502
        String baseName = tableSociete.getRow(base).getString("DATABASE_NAME");
503
 
504
        instance.getBase().getDBSystemRoot().getRootsToMap().clear();
505
        try {
506
            Set<String> s = new HashSet<String>();
507
            s.add(baseName);
508
            instance.getBase().fetchTables(s);
509
        } catch (SQLException e) {
510
            throw new IllegalStateException("could not access societe base", e);
511
        }
512
 
513
        System.err.println("baseName" + baseName);
514
        instance.getSystemRoot().prependToRootPath("Common");
515
        instance.getSystemRoot().prependToRootPath(baseName);
516
 
517
        SQLSchema baseSQL = instance.getBase().getSchema(baseName);
518
 
519
        DatabaseMetaData dbMetaDataSociete;
520
        try {
521
            dbMetaDataSociete = baseSQL.getBase().getDataSource().getConnection().getMetaData();
522
 
523
            String[] type = new String[1];
524
            type[0] = "TABLE";
525
            ResultSet rs = dbMetaDataSociete.getTables("", baseSQL.getName(), "%", null);
526
 
527
            System.err.println("Start " + rs.getFetchSize());
528
            int i = 0;
529
            while (rs.next()) {
530
 
531
                if (rs.getString("TABLE_TYPE") != null && rs.getString("TABLE_TYPE").equalsIgnoreCase("TABLE")) {
532
                    // System.err.println("FIND TABLE");
533
                    // baseSQL.getTableNames();
534
                    final SQLTable table = baseSQL.getTable(rs.getString("TABLE_NAME"));
535
                    Set<SQLField> s = table.getFields();
536
                    for (SQLField field : s) {
537
                        if (field.getName().equalsIgnoreCase("ID_USER_COMMON_CREATE") || field.getName().equalsIgnoreCase("ID_USER_COMMON_MODIFY")) {
538
                            Object o = field.getDefaultValue();
539
                            if (o == null || (o instanceof Integer && ((Integer) o) == 0)
540
 
541
                            ) {
542
                                System.err.println("Bad default on " + field);
543
                                baseSQL.getBase()
544
                                        .execute(
545
                                                "ALTER TABLE \"" + field.getTable().getSchema().getName() + "\".\"" + field.getTable().getName() + "\" ALTER COLUMN \"" + field.getName()
546
                                                        + "\" SET DEFAULT 1;");
547
 
548
                                baseSQL.getBase().execute(
549
                                        "UPDATE \"" + field.getTable().getSchema().getName() + "\".\"" + field.getTable().getName() + "\" SET \"" + field.getName() + "\"=1 WHERE \"" + field.getName()
550
                                                + "\"=0 OR \"" + field.getName() + "\" IS NULL;");
551
                            }
552
 
553
                        }
554
                    }
555
                }
556
                // System.err.println(i++ + " " + rs.getString("TABLE_TYPE"));
557
            }
558
            rs.close();
559
        } catch (SQLException e) {
560
            // TODO Auto-generated catch block
561
            e.printStackTrace();
562
        }
563
    }
564
 
565
    public static void main(String[] args) {
566
        // updateMultiBase();
567
        // compareDB(41, 1);
568
 
569
        fixUserCommon(41);
570
 
571
        // try {
572
        // patchSequences(new
573
        // PropsConfiguration(ActionDB.class.getResourceAsStream("changeBase.properties"),
574
        // props).getBase());
575
        // setOrder(new
576
        // PropsConfiguration(ActionDB.class.getResourceAsStream("changeBase.properties"),
577
        // props).getBase());
578
        // } catch (IOException e) {
579
        // TODO Auto-generated catch block
580
        // e.printStackTrace();
581
        // }
582
    }
583
}