Project

General

Profile

« Previous | Next » 

Revision 866e99f0

Added by Andreas Müller almost 7 years ago

fix #6717 rename taxon name indexes

View differences:

cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/IndexRenamer.java
1
/**
2
* Copyright (C) 2017 EDIT
3
* European Distributed Institute of Taxonomy
4
* http://www.e-taxonomy.eu
5
*
6
* The contents of this file are subject to the Mozilla Public License Version 1.1
7
* See LICENSE.TXT at the top of this package for the full license terms.
8
*/
9
package eu.etaxonomy.cdm.database.update;
10

  
11
import java.sql.SQLException;
12

  
13
import org.apache.log4j.Logger;
14

  
15
import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
16
import eu.etaxonomy.cdm.database.DatabaseTypeEnum;
17
import eu.etaxonomy.cdm.database.ICdmDataSource;
18

  
19
/**
20
 * @author a.mueller
21
 * @date 15.06.2017
22
 *
23
 */
24
public class IndexRenamer extends SchemaUpdaterStepBase {
25
    private static final Logger logger = Logger.getLogger(IndexRenamer.class);
26

  
27
    private String tableName;
28

  
29
    private String oldIndexName;
30

  
31
    private String newIndexName;
32

  
33
    private String columnName;
34

  
35
    private Integer length;
36

  
37
// ********************** FACTORY ****************************************/
38

  
39
    public static final IndexRenamer NewStringInstance(String tableName, String oldIndexName,
40
            String newIndexName, String columnName, Integer length){
41
        return new IndexRenamer(tableName, oldIndexName,
42
                newIndexName, columnName, length == null ? 255 : length);
43
    }
44

  
45
    public static final IndexRenamer NewIntegerInstance(String tableName, String oldIndexName,
46
            String newIndexName, String columnName){
47
        return new IndexRenamer(tableName, oldIndexName, newIndexName, columnName, null);
48
    }
49
    /**
50
     * @param stepName
51
     */
52
    protected IndexRenamer(String tableName, String oldIndexName,
53
            String newIndexName, String columnName, Integer length) {
54
        super("Rename index " + oldIndexName + " to " + newIndexName);
55
        this.tableName = tableName;
56
        this.oldIndexName = oldIndexName;
57
        this.newIndexName = newIndexName;
58
        this.columnName = columnName;
59
        this.length = length;
60
    }
61

  
62
    /**
63
     * {@inheritDoc}
64
     */
65
    @Override
66
    public void invoke(ICdmDataSource datasource, IProgressMonitor monitor, CaseType caseType,
67
            SchemaUpdateResult result) throws SQLException {
68
        try {
69
            String[] updateQuery = getCreateQuery(datasource, caseType, tableName, oldIndexName, newIndexName, columnName, length);
70
            try {
71
                datasource.executeUpdate(updateQuery[0]);
72
            } catch (Exception e) {
73
                if (updateQuery.length > 1){
74
                    datasource.executeUpdate(updateQuery[1]);
75
                }else{
76
                    throw e;
77
                }
78
            }
79
            return;
80
        } catch (Exception e) {
81
            String message = "Index ("+tableName +"."+oldIndexName+") could not be renamed "
82
                    + "to ("+newIndexName+") or drop/add was not possible.\n"
83
                    + "Please ask your admin to rename manually.";
84
            logger.warn(message);
85
            result.addWarning(message, this, "invoke");
86
            return;
87
        }
88

  
89
    }
90

  
91
    private String[] getCreateQuery(ICdmDataSource datasource, CaseType caseType, String tableName, String oldIndexName, String newIndexName, String columnName, Integer length) {
92
        DatabaseTypeEnum type = datasource.getDatabaseType();
93
//      String indexName = "_UniqueKey";
94
        String[] updateQueries;
95
        if (type.equals(DatabaseTypeEnum.MySQL)){
96
            //https://stackoverflow.com/questions/1463363/how-do-i-rename-an-index-in-mysql
97
            //in future: https://dev.mysql.com/worklog/task/?id=6555
98
            String format = "ALTER TABLE @@%s@@ DROP INDEX %s, ADD INDEX %s (%s%s)";
99
            updateQueries = new String[]{String.format(format, tableName, oldIndexName, newIndexName, columnName, length!= null ? "("+length+")": "")};
100
        }else if (type.equals(DatabaseTypeEnum.H2) || type.equals(DatabaseTypeEnum.PostgreSQL)){
101
            //http://www.h2database.com/html/grammar.html#alter_index_rename
102
            //https://www.postgresql.org/docs/9.4/static/sql-alterindex.html (maybe IF EXISTS does not work prior to 9.x)
103
            String format = "ALTER INDEX %s %s RENAME TO %s";
104
            updateQueries = new String[]{String.format(format, " IF EXISTS ", oldIndexName, newIndexName),
105
                    String.format(format, "", oldIndexName, newIndexName)};
106

  
107
        }else if (type.equals(DatabaseTypeEnum.SqlServer2005)){
108
            //TODO Untested !!!!
109
            //https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql
110
            //https://www.mssqltips.com/sqlservertip/2709/script-to-rename-constraints-and-indexes-to-conform-to-a-sql-server-naming-convention/
111
            //https://stackoverflow.com/questions/40865886/rename-sql-server-index-in-ms-sql-server
112
            String format = "EXEC sp_rename N'%s.%s', N'%s', N'INDEX'";
113
            updateQueries = new String[]{String.format(format, tableName, oldIndexName, newIndexName)};
114
        }else{
115
            throw new IllegalArgumentException("Datasource type not supported yet: " + type.getName());
116
        }
117
//      updateQuery = updateQuery.replace("@indexName", indexName);
118
        for (int i = 0; i < updateQueries.length ; i++ ){
119
            updateQueries[i] = caseType.replaceTableNames(updateQueries[i]);
120
        }
121
        return updateQueries;
122
}
123

  
124
}
cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v41_47/SchemaUpdater_41_47.java
19 19
import eu.etaxonomy.cdm.database.update.ColumnRemover;
20 20
import eu.etaxonomy.cdm.database.update.ISchemaUpdater;
21 21
import eu.etaxonomy.cdm.database.update.ISchemaUpdaterStep;
22
import eu.etaxonomy.cdm.database.update.IndexRenamer;
22 23
import eu.etaxonomy.cdm.database.update.MnTableCreator;
23 24
import eu.etaxonomy.cdm.database.update.SchemaUpdaterBase;
24 25
import eu.etaxonomy.cdm.database.update.SimpleSchemaUpdaterStep;
......
415 416
        //#6368
416 417
        changeTaxonNameTableName(stepList);
417 418

  
419
        //#6717 update index names
420
        step = IndexRenamer.NewStringInstance("TaxonName",
421
                "taxonNameBaseNameCacheIndex", "taxonNameNameCacheIndex", "nameCache", 255);
422
        stepList.add(step);
423

  
424
        step = IndexRenamer.NewStringInstance("TaxonName",
425
                "taxonNameBaseTitleCacheIndex", "taxonNameTitleCacheIndex", "titleCache", 333);
426
        stepList.add(step);
427

  
418 428
    }
419 429

  
420 430
    /**

Also available in: Unified diff