OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 17 | Only display areas with differences | Regard whitespace | Details | Blame | Last modification | View Log | RSS feed

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