Revision 0f9d4e5a
Added by Andreas Müller almost 11 years ago
| cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/TableCreator.java | ||
|---|---|---|
| 17 | 17 |
import org.apache.commons.lang.StringUtils; |
| 18 | 18 |
import org.apache.log4j.Logger; |
| 19 | 19 |
|
| 20 |
import eu.etaxonomy.cdm.common.CdmUtils; |
|
| 20 | 21 |
import eu.etaxonomy.cdm.common.monitor.IProgressMonitor; |
| 21 | 22 |
import eu.etaxonomy.cdm.database.DatabaseTypeEnum; |
| 22 | 23 |
import eu.etaxonomy.cdm.database.ICdmDataSource; |
| 24 |
import eu.etaxonomy.cdm.model.agent.AgentBase; |
|
| 23 | 25 |
|
| 24 | 26 |
/** |
| 25 | 27 |
* @author a.mueller |
| ... | ... | |
| 89 | 91 |
} |
| 90 | 92 |
|
| 91 | 93 |
|
| 94 |
@Override |
|
| 95 |
public List<ISchemaUpdaterStep> getInnerSteps() {
|
|
| 96 |
return mnTablesStepList; |
|
| 97 |
} |
|
| 98 |
|
|
| 99 |
/** |
|
| 100 |
* Fills the {@link #columnAdders} list.
|
|
| 101 |
*/ |
|
| 92 | 102 |
private void makeColumnAdders() {
|
| 93 | 103 |
if (columnNames.size() != columnTypes.size()){
|
| 94 | 104 |
throw new RuntimeException ("ColumnNames and columnTypes must be of same size. Step: " + getStepName());
|
| ... | ... | |
| 117 | 127 |
} |
| 118 | 128 |
} |
| 119 | 129 |
} |
| 130 |
|
|
| 131 |
/** |
|
| 132 |
* fills the mnTablesStepList |
|
| 133 |
*/ |
|
| 134 |
private void makeMnTables() {
|
|
| 135 |
TableCreator tableCreator; |
|
| 120 | 136 |
|
| 137 |
if (this.includeAnnotatableEntity){
|
|
| 138 |
//annotations |
|
| 139 |
stepName= "Add @tableName annotations"; |
|
| 140 |
stepName = stepName.replace("@tableName", this.tableName);
|
|
| 141 |
tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, "Annotation", SchemaUpdaterBase.INCLUDE_AUDIT); |
|
| 142 |
mnTablesStepList.add(tableCreator); |
|
| 121 | 143 |
|
| 122 |
@Override |
|
| 123 |
public List<ISchemaUpdaterStep> getInnerSteps() {
|
|
| 124 |
return mnTablesStepList; |
|
| 125 |
} |
|
| 144 |
//marker |
|
| 145 |
stepName= "Add @tableName marker"; |
|
| 146 |
stepName = stepName.replace("@tableName", this.tableName);
|
|
| 147 |
tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, "Marker", SchemaUpdaterBase.INCLUDE_AUDIT); |
|
| 148 |
mnTablesStepList.add(tableCreator); |
|
| 149 |
|
|
| 150 |
} |
|
| 151 |
|
|
| 152 |
if (this.includeIdentifiableEntity){
|
|
| 126 | 153 |
|
| 127 |
private String getColumnsSql(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) throws DatabaseTypeNotSupportedException {
|
|
| 128 |
String result = ""; |
|
| 129 |
for (ColumnAdder adder : this.columnAdders){
|
|
| 130 |
String singleAdderSQL = adder.getUpdateQueryString(tableName, datasource, monitor) + ", "; |
|
| 154 |
//credits |
|
| 155 |
stepName= "Add @tableName credits"; |
|
| 156 |
stepName = stepName.replace("@tableName", this.tableName);
|
|
| 157 |
tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, null, "Credit", null, SchemaUpdaterBase.INCLUDE_AUDIT, SORT_INDEX, false); |
|
| 158 |
mnTablesStepList.add(tableCreator); |
|
| 131 | 159 |
|
| 132 |
String[] split = singleAdderSQL.split(ColumnAdder.getAddColumnSeperator(datasource)); |
|
| 133 |
result += split[1]; |
|
| 160 |
//extensions |
|
| 161 |
stepName= "Add @tableName extensions"; |
|
| 162 |
stepName = stepName.replace("@tableName", this.tableName);
|
|
| 163 |
tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, "Extension", SchemaUpdaterBase.INCLUDE_AUDIT); |
|
| 164 |
mnTablesStepList.add(tableCreator); |
|
| 165 |
|
|
| 166 |
//OriginalSourceBase |
|
| 167 |
stepName= "Add @tableName sources"; |
|
| 168 |
stepName = stepName.replace("@tableName", this.tableName);
|
|
| 169 |
tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, null, "OriginalSourceBase", "sources", SchemaUpdaterBase.INCLUDE_AUDIT, false, true); |
|
| 170 |
mnTablesStepList.add(tableCreator); |
|
| 171 |
|
|
| 172 |
//Rights |
|
| 173 |
stepName= "Add @tableName rights"; |
|
| 174 |
stepName = stepName.replace("@tableName", this.tableName);
|
|
| 175 |
tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, "Rights", SchemaUpdaterBase.INCLUDE_AUDIT); |
|
| 176 |
mnTablesStepList.add(tableCreator); |
|
| 134 | 177 |
} |
| 135 |
return result; |
|
| 136 | 178 |
} |
| 137 | 179 |
|
| 180 |
|
|
| 138 | 181 |
@Override |
| 139 | 182 |
protected boolean invokeOnTable(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) {
|
| 140 | 183 |
try {
|
| 141 | 184 |
boolean result = true; |
| 185 |
//CREATE |
|
| 142 | 186 |
String updateQuery = "CREATE TABLE @tableName (";
|
| 187 |
//AUDIT |
|
| 143 | 188 |
if (isAuditing){
|
| 144 | 189 |
updateQuery += " REV integer not null, revtype tinyint, "; |
| 145 | 190 |
} |
| 191 |
//CdmBase |
|
| 146 | 192 |
if (includeCdmBaseAttributes){
|
| 147 | 193 |
updateQuery += " id integer not null," |
| 148 | 194 |
+ " created datetime, " |
| ... | ... | |
| 150 | 196 |
+ " updated datetime, " |
| 151 | 197 |
+ " createdby_id integer," |
| 152 | 198 |
+ " updatedby_id integer, "; |
| 153 |
|
|
| 154 | 199 |
} |
| 200 |
//EventBase |
|
| 155 | 201 |
if (this.includeEventBase){
|
| 156 |
//TODO handle as column adder |
|
| 157 | 202 |
updateQuery += "timeperiod_start varchar(255), timeperiod_end varchar(255), timeperiod_freetext varchar(255), actor_id int, description varchar(255),"; |
| 158 |
logger.warn("ForeignKey for actor not yet handled");
|
|
| 159 | 203 |
} |
| 160 |
|
|
| 204 |
//Identifiable |
|
| 161 | 205 |
if (this.includeIdentifiableEntity){
|
| 162 | 206 |
updateQuery += "lsid_authority varchar(255), lsid_lsid varchar(255), lsid_namespace varchar(255), lsid_object varchar(255), lsid_revision varchar(255), protectedtitlecache bit not null, titleCache varchar(255),"; |
| 163 | 207 |
} |
| 164 |
|
|
| 208 |
//specific columns |
|
| 165 | 209 |
updateQuery += getColumnsSql(tableName, datasource, monitor); |
| 166 | 210 |
|
| 211 |
//primary and unique keys |
|
| 167 | 212 |
String primaryKeySql = primaryKey(isAuditing)==null ? "" : "primary key (" + primaryKey(isAuditing) + "),";
|
| 168 | 213 |
String uniqueSql = unique(isAuditing)== null ? "" : "unique(" + unique(isAuditing) + "),";
|
| 169 | 214 |
updateQuery += primaryKeySql + uniqueSql; |
| 170 | 215 |
|
| 171 |
updateQuery = StringUtils.chomp(updateQuery.trim(), ",");
|
|
| 172 |
updateQuery += ")";
|
|
| 216 |
//finalize
|
|
| 217 |
updateQuery = StringUtils.chomp(updateQuery.trim(), ",") + ")";
|
|
| 173 | 218 |
|
| 219 |
//replace |
|
| 174 | 220 |
updateQuery = updateQuery.replace("@tableName", tableName);
|
| 175 |
if (datasource.getDatabaseType().equals(DatabaseTypeEnum.MySQL)){
|
|
| 176 |
updateQuery += " ENGINE=MYISAM DEFAULT CHARSET=utf8 "; |
|
| 177 |
} |
|
| 178 |
logger.debug(updateQuery); |
|
| 221 |
|
|
| 222 |
//append datasource specific string |
|
| 223 |
updateQuery += datasource.getDatabaseType().getHibernateDialect().getTableTypeString(); |
|
| 224 |
logger.debug("UPDATE Query: " + updateQuery);
|
|
| 225 |
|
|
| 226 |
//execute |
|
| 179 | 227 |
datasource.executeUpdate(updateQuery); |
| 228 |
|
|
| 229 |
//Foreign Keys |
|
| 180 | 230 |
result &= createForeignKeys(tableName, isAuditing, datasource, monitor); |
| 231 |
|
|
| 181 | 232 |
return result; |
| 182 | 233 |
} catch (Exception e) {
|
| 183 | 234 |
monitor.warning(e.getMessage(), e); |
| ... | ... | |
| 187 | 238 |
} |
| 188 | 239 |
|
| 189 | 240 |
|
| 190 |
private void makeMnTables() {
|
|
| 191 |
TableCreator tableCreator; |
|
| 192 |
|
|
| 193 |
if (this.includeAnnotatableEntity){
|
|
| 194 |
//annotations |
|
| 195 |
stepName= "Add @tableName annotations"; |
|
| 196 |
stepName = stepName.replace("@tableName", this.tableName);
|
|
| 197 |
tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, "Annotation", SchemaUpdaterBase.INCLUDE_AUDIT); |
|
| 198 |
mnTablesStepList.add(tableCreator); |
|
| 199 |
|
|
| 200 |
//marker |
|
| 201 |
stepName= "Add @tableName marker"; |
|
| 202 |
stepName = stepName.replace("@tableName", this.tableName);
|
|
| 203 |
tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, "Marker", SchemaUpdaterBase.INCLUDE_AUDIT); |
|
| 204 |
mnTablesStepList.add(tableCreator); |
|
| 205 |
|
|
| 206 |
} |
|
| 207 |
|
|
| 208 |
if (this.includeIdentifiableEntity){
|
|
| 209 |
|
|
| 210 |
//credits |
|
| 211 |
stepName= "Add @tableName credits"; |
|
| 212 |
stepName = stepName.replace("@tableName", this.tableName);
|
|
| 213 |
tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, null, "Credit", null, SchemaUpdaterBase.INCLUDE_AUDIT, SORT_INDEX, false); |
|
| 214 |
mnTablesStepList.add(tableCreator); |
|
| 215 |
|
|
| 216 |
//extensions |
|
| 217 |
stepName= "Add @tableName extensions"; |
|
| 218 |
stepName = stepName.replace("@tableName", this.tableName);
|
|
| 219 |
tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, "Extension", SchemaUpdaterBase.INCLUDE_AUDIT); |
|
| 220 |
mnTablesStepList.add(tableCreator); |
|
| 221 |
|
|
| 222 |
//OriginalSourceBase |
|
| 223 |
stepName= "Add @tableName sources"; |
|
| 224 |
stepName = stepName.replace("@tableName", this.tableName);
|
|
| 225 |
tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, null, "OriginalSourceBase", "sources", SchemaUpdaterBase.INCLUDE_AUDIT, false, true); |
|
| 226 |
mnTablesStepList.add(tableCreator); |
|
| 227 |
|
|
| 228 |
//Rights |
|
| 229 |
stepName= "Add @tableName rights"; |
|
| 230 |
stepName = stepName.replace("@tableName", this.tableName);
|
|
| 231 |
tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, "Rights", SchemaUpdaterBase.INCLUDE_AUDIT); |
|
| 232 |
mnTablesStepList.add(tableCreator); |
|
| 241 |
/** |
|
| 242 |
* Returns the sql part for the {@link #columnAdders} columns.
|
|
| 243 |
* This is done by reusing the same method in the ColumnAdder class and removing all the prefixes like 'ADD COLUMN' |
|
| 244 |
*/ |
|
| 245 |
private String getColumnsSql(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) throws DatabaseTypeNotSupportedException {
|
|
| 246 |
String result = ""; |
|
| 247 |
for (ColumnAdder adder : this.columnAdders){
|
|
| 248 |
String singleAdderSQL = adder.getUpdateQueryString(tableName, datasource, monitor) + ", "; |
|
| 233 | 249 |
|
| 250 |
String[] split = singleAdderSQL.split(ColumnAdder.getAddColumnSeperator(datasource)); |
|
| 251 |
result += split[1]; |
|
| 234 | 252 |
} |
| 253 |
return result; |
|
| 235 | 254 |
} |
| 255 |
|
|
| 236 | 256 |
|
| 237 | 257 |
private boolean createForeignKeys(String tableName, boolean isAudit, ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException {
|
| 238 | 258 |
boolean result = true; |
| 239 | 259 |
if (includeCdmBaseAttributes){
|
| 240 | 260 |
String attribute = "updatedby"; |
| 241 | 261 |
String referencedTable = "UserAccount"; |
| 242 |
result &= makeForeignKey(tableName, datasource, attribute, referencedTable); |
|
| 262 |
result &= makeForeignKey(tableName, datasource, monitor, attribute, referencedTable);
|
|
| 243 | 263 |
|
| 244 | 264 |
attribute = "createdby"; |
| 245 | 265 |
referencedTable = "UserAccount"; |
| 246 |
result &= makeForeignKey(tableName, datasource, attribute, referencedTable); |
|
| 266 |
result &= makeForeignKey(tableName, datasource, monitor, attribute, referencedTable);
|
|
| 247 | 267 |
|
| 248 | 268 |
} |
| 249 | 269 |
if (isAudit){
|
| 250 | 270 |
String attribute = "REV"; |
| 251 | 271 |
String referencedTable = "AuditEvent"; |
| 252 |
result &= makeForeignKey(tableName, datasource, attribute, referencedTable); |
|
| 272 |
result &= makeForeignKey(tableName, datasource, monitor, attribute, referencedTable); |
|
| 273 |
} |
|
| 274 |
if (this.includeEventBase){
|
|
| 275 |
String attribute = "actor_id"; |
|
| 276 |
String referencedTable = "AgentBase"; |
|
| 277 |
result &= makeForeignKey(tableName, datasource, monitor, attribute, referencedTable); |
|
| 253 | 278 |
} |
| 254 | 279 |
for (ColumnAdder adder : this.columnAdders){
|
| 255 | 280 |
if (adder.getReferencedTable() != null){
|
| 256 |
result &= makeForeignKey(tableName, datasource, adder.getNewColumnName(), adder.getReferencedTable()); |
|
| 281 |
result &= makeForeignKey(tableName, datasource, monitor, adder.getNewColumnName(), adder.getReferencedTable());
|
|
| 257 | 282 |
} |
| 258 | 283 |
} |
| 259 | 284 |
return result; |
| 260 | 285 |
} |
| 261 | 286 |
|
| 262 |
public static boolean makeForeignKey(String tableName, ICdmDataSource datasource, String attribute, String referencedTable) throws SQLException {
|
|
| 287 |
public static boolean makeForeignKey(String tableName, ICdmDataSource datasource, IProgressMonitor monitor, String attribute, String referencedTable) throws SQLException {
|
|
| 263 | 288 |
boolean result = true; |
| 264 |
String index = "FK@tableName_@attribute"; |
|
| 265 |
index = index.replace("@tableName", tableName);
|
|
| 266 |
index = index.replace("@attribute", attribute);
|
|
| 267 | 289 |
|
| 268 |
String idSuffix = "_id"; |
|
| 269 |
if ("REV".equalsIgnoreCase(attribute) || attribute.endsWith(idSuffix)){
|
|
| 270 |
idSuffix = ""; |
|
| 290 |
if (supportsForeignKeys(datasource, monitor, tableName, referencedTable)){
|
|
| 291 |
String index = "FK@tableName_@attribute"; |
|
| 292 |
index = index.replace("@tableName", tableName);
|
|
| 293 |
index = index.replace("@attribute", attribute);
|
|
| 294 |
|
|
| 295 |
String idSuffix = "_id"; |
|
| 296 |
if (isRevAttribute(attribute) || attribute.endsWith(idSuffix)){
|
|
| 297 |
idSuffix = ""; |
|
| 298 |
} |
|
| 299 |
String updateQuery = "ALTER TABLE @tableName ADD INDEX @index (@attribute), ADD CONSTRAINT @index FOREIGN KEY (@attribute) REFERENCES @referencedTable (@id)"; |
|
| 300 |
updateQuery = updateQuery.replace("@tableName", tableName);
|
|
| 301 |
updateQuery = updateQuery.replace("@index", index);
|
|
| 302 |
updateQuery = updateQuery.replace("@attribute", attribute + idSuffix);
|
|
| 303 |
updateQuery = updateQuery.replace("@referencedTable", referencedTable);
|
|
| 304 |
if (isRevAttribute(attribute)){
|
|
| 305 |
updateQuery = updateQuery.replace("@id", "revisionnumber");
|
|
| 306 |
}else{
|
|
| 307 |
updateQuery = updateQuery.replace("@id", "id");
|
|
| 308 |
} |
|
| 309 |
logger.debug(updateQuery); |
|
| 310 |
try {
|
|
| 311 |
datasource.executeUpdate(updateQuery); |
|
| 312 |
} catch (Exception e) {
|
|
| 313 |
String message = "Problem when creating Foreign Key for " + tableName +"." + attribute +": " + e.getMessage(); |
|
| 314 |
monitor.warning(message); |
|
| 315 |
logger.warn(message, e); |
|
| 316 |
return true; |
|
| 317 |
} |
|
| 318 |
return result; |
|
| 319 |
}else{
|
|
| 320 |
return true; |
|
| 271 | 321 |
} |
| 272 |
String updateQuery = "ALTER TABLE @tableName ADD INDEX @index (@attribute), ADD CONSTRAINT @index FOREIGN KEY (@attribute) REFERENCES @referencedTable (id)"; |
|
| 273 |
updateQuery = updateQuery.replace("@tableName", tableName);
|
|
| 274 |
updateQuery = updateQuery.replace("@index", index);
|
|
| 275 |
updateQuery = updateQuery.replace("@attribute", attribute + idSuffix);
|
|
| 276 |
updateQuery = updateQuery.replace("@referencedTable", referencedTable);
|
|
| 277 |
|
|
| 278 |
logger.debug(updateQuery); |
|
| 279 |
try {
|
|
| 280 |
datasource.executeUpdate(updateQuery); |
|
| 281 |
} catch (SQLException e) {
|
|
| 282 |
throw e; |
|
| 322 |
|
|
| 323 |
} |
|
| 324 |
|
|
| 325 |
/** |
|
| 326 |
* Determines if the tables and the database support foreign keys. If determination is not possible true is returned as default. |
|
| 327 |
* @param datasource |
|
| 328 |
* @param monitor |
|
| 329 |
* @param tableName |
|
| 330 |
* @param referencedTable |
|
| 331 |
* @return |
|
| 332 |
*/ |
|
| 333 |
private static boolean supportsForeignKeys(ICdmDataSource datasource, IProgressMonitor monitor, String tableName, String referencedTable) {
|
|
| 334 |
boolean result = true; |
|
| 335 |
if (! datasource.getDatabaseType().equals(DatabaseTypeEnum.MySQL)){
|
|
| 336 |
return true; |
|
| 337 |
}else{
|
|
| 338 |
try {
|
|
| 339 |
String myIsamTables = ""; |
|
| 340 |
String format = "SELECT ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'"; |
|
| 341 |
String sql = String.format(format, datasource.getDatabase(), tableName); |
|
| 342 |
String engine = (String)datasource.getSingleValue(sql); |
|
| 343 |
if (engine.equals("MyISAM")){
|
|
| 344 |
result = false; |
|
| 345 |
myIsamTables = CdmUtils.concat(",", myIsamTables, tableName);
|
|
| 346 |
} |
|
| 347 |
sql = String.format(format, datasource.getDatabase(), referencedTable); |
|
| 348 |
engine = (String)datasource.getSingleValue(sql); |
|
| 349 |
if (engine.equals("MyISAM")){
|
|
| 350 |
result = false; |
|
| 351 |
myIsamTables = CdmUtils.concat(",", myIsamTables, referencedTable);
|
|
| 352 |
} |
|
| 353 |
if (result == false){
|
|
| 354 |
String message = "Tables (%s) use MyISAM engine. MyISAM does not support foreign keys."; |
|
| 355 |
message = String.format(message, myIsamTables); |
|
| 356 |
monitor.warning(message); |
|
| 357 |
} |
|
| 358 |
return result; |
|
| 359 |
} catch (Exception e) {
|
|
| 360 |
String message = "Problems to determine table engine for MySQL."; |
|
| 361 |
monitor.warning(message); |
|
| 362 |
return true; //default |
|
| 363 |
} |
|
| 364 |
|
|
| 283 | 365 |
} |
| 284 |
return result; |
|
| 366 |
|
|
| 367 |
|
|
| 368 |
} |
|
| 369 |
|
|
| 370 |
private static boolean isRevAttribute(String attribute) {
|
|
| 371 |
return "REV".equalsIgnoreCase(attribute); |
|
| 285 | 372 |
} |
| 286 | 373 |
|
| 287 | 374 |
|
| 375 |
/** |
|
| 376 |
* Constructs the primary key creation string |
|
| 377 |
* @param isAudit |
|
| 378 |
* @return |
|
| 379 |
*/ |
|
| 288 | 380 |
protected String primaryKey(boolean isAudit){
|
| 289 | 381 |
String result = null; |
| 290 | 382 |
if (! isAudit && this.primaryKeyParams != null){
|
| ... | ... | |
| 303 | 395 |
return result; |
| 304 | 396 |
} |
| 305 | 397 |
|
| 398 |
/** |
|
| 399 |
* Constructs the unique key creation string |
|
| 400 |
* @param isAudit |
|
| 401 |
* @return |
|
| 402 |
*/ |
|
| 306 | 403 |
protected String unique(boolean isAudit){
|
| 307 | 404 |
if (! isAudit){
|
| 308 | 405 |
if (this.uniqueParams != null){
|
Also available in: Unified diff
Allow access to hibernate dialect via DatabaseTypeEnum, use this for foreign key handling in TableCreator and ColumnAdder/Changer