OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Go to most recent revision | Details | 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.changer.convert;
15
 
16
import org.openconcerto.sql.changer.Changer;
17
import org.openconcerto.sql.model.ConnectionHandlerNoSetup;
18
import org.openconcerto.sql.model.DBStructureItem;
19
import org.openconcerto.sql.model.DBSystemRoot;
20
import org.openconcerto.sql.model.SQLBase;
21
import org.openconcerto.sql.model.SQLDataSource;
22
import org.openconcerto.sql.model.SQLRow;
23
import org.openconcerto.sql.model.SQLSelect;
24
import org.openconcerto.sql.model.SQLSyntax;
25
import org.openconcerto.sql.model.SQLTable;
26
import org.openconcerto.sql.model.SQLSyntax.ConstraintType;
27
import org.openconcerto.sql.utils.AlterTable;
28
import org.openconcerto.sql.utils.SQLUtils;
29
import org.openconcerto.sql.utils.ChangeTable.ClauseType;
30
 
31
import java.sql.SQLException;
32
import java.util.ArrayList;
33
import java.util.List;
34
 
35
/**
36
 * Remove duplicate rows of a table and a constraint so that it can't happen again. If the table has
37
 * no primary key then the constraint will be a primary key otherwise it will be a unique
38
 * constraint.
39
 *
40
 * @author Sylvain CUAZ
41
 */
42
public class RemoveDuplicates extends Changer<SQLTable> {
43
 
44
    private final List<String> fields;
45
 
46
    public RemoveDuplicates(DBSystemRoot b) {
47
        super(b);
48
        this.fields = new ArrayList<String>();
49
    }
50
 
51
    @Override
52
    protected Class<? extends DBStructureItem> getMaxLevel() {
53
        return SQLTable.class;
54
    }
55
 
56
    public final List<String> getFields() {
57
        return this.fields;
58
    }
59
 
60
    public final void setFields(List<String> fields) {
61
        this.fields.clear();
62
        this.fields.addAll(fields);
63
    }
64
 
65
    @Override
66
    public void setUpFromSystemProperties() {
67
        super.setUpFromSystemProperties();
68
        this.setFields(SQLRow.toList(System.getProperty("org.openconcerto.sql.pks", "")));
69
    }
70
 
71
    protected void changeImpl(final SQLTable t) throws SQLException {
72
        this.getStream().print(t.getName() + "... ");
73
        if (this.getFields().size() == 0)
74
            throw new IllegalStateException("no fields defined");
75
        this.getStream().print(getFields() + " ");
76
 
77
        // test if duplicates can happen
78
        if (t.getPKsNames().equals(this.getFields()) || t.getConstraint(ConstraintType.UNIQUE, getFields()) != null)
79
            this.getStream().println("already");
80
        else {
81
            this.getStream().println(SQLUtils.executeAtomic(getDS(), new ConnectionHandlerNoSetup<String, SQLException>() {
82
                @Override
83
                public String handle(SQLDataSource ds) throws SQLException {
84
                    // cannot just select * group by into a tmp table, delete from t, and insert
85
                    // since when "grouping by" each selected column must be an aggregate
86
 
87
                    // need a primary key to differentiate duplicates
88
                    final String addedPK;
89
                    if (t.getKey() == null) {
90
                        addedPK = SQLSyntax.ID_NAME;
91
                        ds.execute(new AlterTable(t).addColumn(addedPK, getSyntax().getPrimaryIDDefinition()).asString());
92
                        t.fetchFields();
93
                    } else
94
                        addedPK = null;
95
                    assert t.getKey() != null;
96
 
97
                    // find out one ID per unique row
98
                    final SQLSelect sel = new SQLSelect(t.getBase());
99
                    sel.addSelect(t.getKey(), "min");
100
                    for (final String f : getFields())
101
                        sel.addGroupBy(t.getField(f));
102
                    ds.execute("CREATE TEMPORARY TABLE " + SQLBase.quoteIdentifier("ID_TO_KEEP") + " as " + sel.asString());
103
 
104
                    // delete the rest
105
                    ds.execute(t.getBase().quote("DELETE FROM %f where %n not in (SELECT * from " + SQLBase.quoteIdentifier("ID_TO_KEEP") + ")", t, t.getKey()));
106
 
107
                    // add constraint
108
                    if (addedPK != null) {
109
                        ds.execute(new AlterTable(t).dropColumn(addedPK).asString());
110
                        ds.execute(new AlterTable(t).addClause("ADD PRIMARY KEY(" + SQLSyntax.quoteIdentifiers(getFields()) + ")", ClauseType.ADD_CONSTRAINT).asString());
111
                        return "added primary key";
112
                    } else {
113
                        ds.execute(new AlterTable(t).addUniqueConstraint("uniq", getFields()).asString());
114
                        return "added unique constraint";
115
                    }
116
                }
117
            }));
118
        }
119
    }
120
}