|
1
24 package org.ofbiz.entity.jdbc;
25
26 import java.io.Serializable  ;
27 import java.sql.Connection ;
28 import java.sql.DatabaseMetaData ;
29 import java.sql.ResultSet ;
30 import java.sql.SQLException ;
31 import java.sql.Statement ;
32 import java.util.ArrayList ;
33 import java.util.Collection ;
34 import java.util.Collections ;
35 import java.util.Iterator ;
36 import java.util.List ;
37 import java.util.Map ;
38 import java.util.Set ;
39 import java.util.TreeSet ;
40
41 import javolution.util.FastList;
42 import javolution.util.FastMap;
43 import org.w3c.dom.Document ;
44 import org.w3c.dom.Element ;
45
46 import org.ofbiz.base.util.Debug;
47 import org.ofbiz.base.util.UtilTimer;
48 import org.ofbiz.base.util.UtilValidate;
49 import org.ofbiz.base.util.UtilXml;
50 import org.ofbiz.entity.GenericEntityException;
51 import org.ofbiz.entity.config.DatasourceInfo;
52 import org.ofbiz.entity.config.EntityConfigUtil;
53 import org.ofbiz.entity.model.ModelEntity;
54 import org.ofbiz.entity.model.ModelField;
55 import org.ofbiz.entity.model.ModelFieldType;
56 import org.ofbiz.entity.model.ModelFieldTypeReader;
57 import org.ofbiz.entity.model.ModelIndex;
58 import org.ofbiz.entity.model.ModelKeyMap;
59 import org.ofbiz.entity.model.ModelRelation;
60 import org.ofbiz.entity.model.ModelViewEntity;
61
62
69 public class DatabaseUtil {
70
71 public static final String module = DatabaseUtil.class.getName();
72
73 protected ModelFieldTypeReader modelFieldTypeReader = null;
75 protected DatasourceInfo datasourceInfo = null;
76 protected String helperName = null;
77
78 protected String connectionUrl = null;
80 protected String driverName = null;
81 protected String userName = null;
82 protected String password = null;
83
84 boolean isLegacy = false;
85
86 public DatabaseUtil(String helperName) {
88 this.helperName = helperName;
89 this.modelFieldTypeReader = ModelFieldTypeReader.getModelFieldTypeReader(helperName);
90 this.datasourceInfo = EntityConfigUtil.getDatasourceInfo(helperName);
91 }
92
93 public DatabaseUtil(String driverName, String connectionUrl, String userName, String password) {
95 this.driverName = driverName;
96 this.connectionUrl = connectionUrl;
97 this.userName = userName;
98 this.password = password;
99 this.isLegacy = true;
100 }
101
102 protected Connection getConnection() throws SQLException , GenericEntityException {
103 Connection connection = null;
104 if (!isLegacy) {
105 connection = ConnectionFactory.getConnection(helperName);
106 } else {
107 connection = ConnectionFactory.getConnection(driverName, connectionUrl, null, userName, password);
108 }
109
110 if (connection == null) {
111 if (!isLegacy) {
112 throw new GenericEntityException("No connection available for helper named [" + helperName + "]");
113 } else {
114 throw new GenericEntityException("No connection avaialble for URL [" + connectionUrl + "]");
115 }
116 }
117 connection.setAutoCommit(true);
118 return connection;
119 }
120
121 public DatasourceInfo getDatasourceInfo() {
122 return this.datasourceInfo;
123 }
124
125
126
127
128
129 public void checkDb(Map modelEntities, List messages, boolean addMissing) {
130 checkDb(modelEntities, null, messages, datasourceInfo.checkPrimaryKeysOnStart, (datasourceInfo.useFks && datasourceInfo.checkForeignKeysOnStart), (datasourceInfo.useFkIndices && datasourceInfo.checkFkIndicesOnStart), addMissing);
131 }
132
133 public void checkDb(Map modelEntities, List colWrongSize, List messages, boolean checkPks, boolean checkFks, boolean checkFkIdx, boolean addMissing) {
134 if (isLegacy) {
135 throw new RuntimeException ("Cannot run checkDb on a legacy database connection; configure a database helper (entityengine.xml)");
136 }
137 UtilTimer timer = new UtilTimer();
138 timer.timerString("Start - Before Get Database Meta Data");
139
140 TreeSet tableNames = this.getTableNames(messages);
142 TreeSet fkTableNames = tableNames == null ? null : new TreeSet (tableNames);
143 TreeSet indexTableNames = tableNames == null ? null : new TreeSet (tableNames);
144
145 if (tableNames == null) {
146 String message = "Could not get table name information from the database, aborting.";
147 if (messages != null) messages.add(message);
148 Debug.logError(message, module);
149 return;
150 }
151 timer.timerString("After Get All Table Names");
152
153 Map colInfo = this.getColumnInfo(tableNames, checkPks, messages);
155 if (colInfo == null) {
156 String message = "Could not get column information from the database, aborting.";
157 if (messages != null) messages.add(message);
158 Debug.logError(message, module);
159 return;
160 }
161 timer.timerString("After Get All Column Info");
162
163
170 timer.timerString("Before Individual Table/Column Check");
171
172 ArrayList modelEntityList = new ArrayList (modelEntities.values());
173 Collections.sort(modelEntityList);
175 Iterator modelEntityIter = modelEntityList.iterator();
176 int curEnt = 0;
177 int totalEnt = modelEntityList.size();
178 List entitiesAdded = FastList.newInstance();
179 while (modelEntityIter.hasNext()) {
180 curEnt++;
181 ModelEntity entity = (ModelEntity) modelEntityIter.next();
182
183 if (entity instanceof ModelViewEntity) {
185 String entMessage = "(" + timer.timeSinceLast() + "ms) NOT Checking #" + curEnt + "/" + totalEnt + " View Entity " + entity.getEntityName();
186 Debug.logVerbose(entMessage, module);
187 if (messages != null) messages.add(entMessage);
188 continue;
189 }
190
191 String entMessage = "(" + timer.timeSinceLast() + "ms) Checking #" + curEnt + "/" + totalEnt +
192 " Entity " + entity.getEntityName() + " with table " + entity.getTableName(datasourceInfo);
193
194 Debug.logVerbose(entMessage, module);
195 if (messages != null) messages.add(entMessage);
196
197 if (tableNames.contains(entity.getTableName(datasourceInfo))) {
199 tableNames.remove(entity.getTableName(datasourceInfo));
200
201 if (colInfo != null) {
202 Map fieldColNames = FastMap.newInstance();
203 Iterator fieldIter = entity.getFieldsIterator();
204 while (fieldIter.hasNext()) {
205 ModelField field = (ModelField) fieldIter.next();
206 fieldColNames.put(field.getColName(), field);
207 }
208
209 Map colMap = (Map ) colInfo.get(entity.getTableName(datasourceInfo));
210 if (colMap != null) {
211 Iterator colEntryIter = colMap.entrySet().iterator();
212 while (colEntryIter.hasNext()) {
213 Map.Entry colEntry = (Map.Entry ) colEntryIter.next();
214 ColumnCheckInfo ccInfo = (ColumnCheckInfo) colEntry.getValue();
215
216 if (fieldColNames.containsKey(ccInfo.columnName)) {
218 ModelField field = null;
219
220 field = (ModelField) fieldColNames.remove(ccInfo.columnName);
221 ModelFieldType modelFieldType = modelFieldTypeReader.getModelFieldType(field.getType());
222
223 if (modelFieldType != null) {
224 String fullTypeStr = modelFieldType.getSqlType();
226 String typeName;
227 int columnSize = -1;
228 int decimalDigits = -1;
229
230 int openParen = fullTypeStr.indexOf('(');
231 int closeParen = fullTypeStr.indexOf(')');
232 int comma = fullTypeStr.indexOf(',');
233
234 if (openParen > 0 && closeParen > 0 && closeParen > openParen) {
235 typeName = fullTypeStr.substring(0, openParen);
236 if (comma > 0 && comma > openParen && comma < closeParen) {
237 String csStr = fullTypeStr.substring(openParen + 1, comma);
238 try {
239 columnSize = Integer.parseInt(csStr);
240 } catch (NumberFormatException e) {
241 Debug.logError(e, module);
242 }
243
244 String ddStr = fullTypeStr.substring(comma + 1, closeParen);
245 try {
246 decimalDigits = Integer.parseInt(ddStr);
247 } catch (NumberFormatException e) {
248 Debug.logError(e, module);
249 }
250 } else {
251 String csStr = fullTypeStr.substring(openParen + 1, closeParen);
252 try {
253 columnSize = Integer.parseInt(csStr);
254 } catch (NumberFormatException e) {
255 Debug.logError(e, module);
256 }
257 }
258 } else {
259 typeName = fullTypeStr;
260 }
261
262 if (UtilValidate.isNotEmpty(modelFieldType.getSqlTypeAlias())) {
264 typeName = modelFieldType.getSqlTypeAlias();
265 }
266
267 if (!ccInfo.typeName.equalsIgnoreCase(typeName)) {
269 String message = "WARNING: Column [" + ccInfo.columnName + "] of table [" + entity.getTableName(datasourceInfo) + "] of entity [" +
270 entity.getEntityName() + "] is of type [" + ccInfo.typeName + "] in the database, but is defined as type [" +
271 typeName + "] in the entity definition.";
272 Debug.logError(message, module);
273 if (messages != null) messages.add(message);
274 }
275 if (columnSize != -1 && ccInfo.columnSize != -1 && columnSize != ccInfo.columnSize && (columnSize * 3) != ccInfo.columnSize) {
276 String message = "WARNING: Column [" + ccInfo.columnName + "] of table [" + entity.getTableName(datasourceInfo) + "] of entity [" +
277 entity.getEntityName() + "] has a column size of [" + ccInfo.columnSize +
278 "] in the database, but is defined to have a column size of [" + columnSize + "] in the entity definition.";
279 Debug.logWarning(message, module);
280 if (messages != null) messages.add(message);
281 if (columnSize > ccInfo.columnSize && colWrongSize != null) {
282 colWrongSize.add(entity.getEntityName() + "." + field.getName());
284 }
285 }
286 if (decimalDigits != -1 && decimalDigits != ccInfo.decimalDigits) {
287 String message = "WARNING: Column [" + ccInfo.columnName + "] of table [" + entity.getTableName(datasourceInfo) + "] of entity [" +
288 entity.getEntityName() + "] has a decimalDigits of [" + ccInfo.decimalDigits +
289 "] in the database, but is defined to have a decimalDigits of [" + decimalDigits + "] in the entity definition.";
290 Debug.logWarning(message, module);
291 if (messages != null) messages.add(message);
292 }
293
294 if (checkPks && ccInfo.isPk && !field.getIsPk()) {
296 String message = "WARNING: Column [" + ccInfo.columnName + "] of table [" + entity.getTableName(datasourceInfo) + "] of entity [" +
297 entity.getEntityName() + "] IS a primary key in the database, but IS NOT a primary key in the entity definition. The primary key for this table needs to be re-created or modified so that this column is NOT party of the primary key.";
298 Debug.logError(message, module);
299 if (messages != null) messages.add(message);
300 }
301 if (checkPks && !ccInfo.isPk && field.getIsPk()) {
302 String message = "WARNING: Column [" + ccInfo.columnName + "] of table [" + entity.getTableName(datasourceInfo) + "] of entity [" +
303 entity.getEntityName() + "] IS NOT a primary key in the database, but IS a primary key in the entity definition. The primary key for this table needs to be re-created or modified to add this column to the primary key. Note that data may need to be added first as a primary key column cannot have an null values.";
304 Debug.logError(message, module);
305 if (messages != null) messages.add(message);
306 }
307 } else {
308 String message = "Column [" + ccInfo.columnName + "] of table [" + entity.getTableName(datasourceInfo) + "] of entity [" + entity.getEntityName() +
309 "] has a field type name of [" + field.getType() + "] which is not found in the field type definitions";
310 Debug.logError(message, module);
311 if (messages != null) messages.add(message);
312 }
313 } else {
314 String message = "Column [" + ccInfo.columnName + "] of table [" + entity.getTableName(datasourceInfo) + "] of entity [" + entity.getEntityName() + "] exists in the database but has no corresponding field" + ((checkPks && ccInfo.isPk) ? " (and it is a PRIMARY KEY COLUMN)" : "");
315 Debug.logWarning(message, module);
316 if (messages != null) messages.add(message);
317 }
318 }
319
320 if (colMap.size() != entity.getFieldsSize()) {
322 String message = "Entity [" + entity.getEntityName() + "] has " + entity.getFieldsSize() + " fields but table [" + entity.getTableName(datasourceInfo) + "] has " + colMap.size() + " columns.";
323 Debug.logWarning(message, module);
324 if (messages != null) messages.add(message);
325 }
326 }
327
328 Iterator fcnIter = fieldColNames.keySet().iterator();
330 while (fcnIter.hasNext()) {
331 String colName = (String ) fcnIter.next();
332 ModelField field = (ModelField) fieldColNames.get(colName);
333 String message = "Field [" + field.getName() + "] of entity [" + entity.getEntityName() + "] is missing its corresponding column [" + field.getColName() + "]" + (field.getIsPk() ? " (and it is a PRIMARY KEY FIELD)" : "");
334
335 Debug.logWarning(message, module);
336 if (messages != null) messages.add(message);
337
338 if (addMissing) {
339 String errMsg = addColumn(entity, field);
341
342 if (errMsg != null && errMsg.length() > 0) {
343 message = "Could not add column [" + field.getColName() + "] to table [" + entity.getTableName(datasourceInfo) + "]: " + errMsg;
344 Debug.logError(message, module);
345 if (messages != null) messages.add(message);
346 } else {
347 message = "Added column [" + field.getColName() + "] to table [" + entity.getTableName(datasourceInfo) + "]" + (field.getIsPk() ? " (NOTE: this is a PRIMARY KEY FIELD, but the primary key was not updated automatically (not considered a safe operation), be sure to fill in any needed data and re-create the primary key)" : "");
348 Debug.logImportant(message, module);
349 if (messages != null) messages.add(message);
350 }
351 }
352 }
353 }
354 } else {
355 String message = "Entity [" + entity.getEntityName() + "] has no table in the database";
356 Debug.logWarning(message, module);
357 if (messages != null) messages.add(message);
358
359 if (addMissing) {
360 String errMsg = createTable(entity, modelEntities, false);
362 if (errMsg != null && errMsg.length() > 0) {
363 message = "Could not create table [" + entity.getTableName(datasourceInfo) + "]: " + errMsg;
364 Debug.logError(message, module);
365 if (messages != null) messages.add(message);
366 } else {
367 entitiesAdded.add(entity);
368 message = "Created table [" + entity.getTableName(datasourceInfo) + "]";
369 Debug.logImportant(message, module);
370 if (messages != null) messages.add(message);
371 }
372 }
373 }
374 }
375
376 timer.timerString("After Individual Table/Column Check");
377
378 Iterator tableNamesIter = tableNames.iterator();
380 while (tableNamesIter != null && tableNamesIter.hasNext()) {
381 String tableName = (String ) tableNamesIter.next();
382 String message = "Table named [" + tableName + "] exists in the database but has no corresponding entity";
383 Debug.logWarning(message, module);
384 if (messages != null) messages.add(message);
385 }
386
387 if (datasourceInfo.useFkIndices) {
389 int totalFkIndices = 0;
390 Iterator eaIter = entitiesAdded.iterator();
391 while (eaIter.hasNext()) {
392 ModelEntity curEntity = (ModelEntity) eaIter.next();
393 if (curEntity.getRelationsOneSize() > 0) {
394 totalFkIndices += this.createForeignKeyIndices(curEntity, datasourceInfo.constraintNameClipLength, messages);
395 }
396 }
397 if (totalFkIndices > 0) Debug.logImportant("==== TOTAL Foreign Key Indices Created: " + totalFkIndices, module);
398 }
399
400 if (datasourceInfo.useFks) {
402 int totalFks = 0;
403 Iterator eaIter = entitiesAdded.iterator();
404 while (eaIter.hasNext()) {
405 ModelEntity curEntity = (ModelEntity) eaIter.next();
406 totalFks += this.createForeignKeys(curEntity, modelEntities, datasourceInfo.constraintNameClipLength, datasourceInfo.fkStyle, datasourceInfo.useFkInitiallyDeferred, messages);
407 }
408 if (totalFks > 0) Debug.logImportant("==== TOTAL Foreign Keys Created: " + totalFks, module);
409 }
410
411 if (datasourceInfo.useIndices) {
413 int totalDis = 0;
414 Iterator eaIter = entitiesAdded.iterator();
415 while (eaIter.hasNext()) {
416 ModelEntity curEntity = (ModelEntity) eaIter.next();
417 if (curEntity.getIndexesSize() > 0) {
418 totalDis += this.createDeclaredIndices(curEntity, messages);
419 }
420 }
421 if (totalDis > 0) Debug.logImportant("==== TOTAL Declared Indices Created: " + totalDis, module);
422 }
423
424 if (checkFks) {
426 int numFksCreated = 0;
429
431 Map refTableInfoMap = this.getReferenceInfo(fkTableNames, messages);
433
434
436 if (refTableInfoMap == null) {
437 if (Debug.verboseOn()) Debug.logVerbose("Ref Table Info Map is null", module);
439 } else {
440 Iterator refModelEntityIter = modelEntityList.iterator();
441 while (refModelEntityIter.hasNext()) {
442 ModelEntity entity = (ModelEntity) refModelEntityIter.next();
443 String entityName = entity.getEntityName();
444 if (entity instanceof ModelViewEntity) {
446 String entMessage = "NOT Checking View Entity " + entity.getEntityName();
447 Debug.logVerbose(entMessage, module);
448 if (messages != null) {
449 messages.add(entMessage);
450 }
451 continue;
452 }
453
454 Map rcInfoMap = (Map ) refTableInfoMap.get(entity.getTableName(datasourceInfo));
456
458 Iterator relations = entity.getRelationsIterator();
460 boolean createdConstraints = false;
461 while (relations.hasNext()) {
462 ModelRelation modelRelation = (ModelRelation) relations.next();
463 if (!"one".equals(modelRelation.getType())) {
464 continue;
465 }
466
467 ModelEntity relModelEntity = (ModelEntity) modelEntities.get(modelRelation.getRelEntityName());
468 if (relModelEntity == null) {
469 Debug.logError("No such relation: " + entity.getEntityName() + " -> " + modelRelation.getRelEntityName(), module);
470 continue;
471 }
472 String relConstraintName = makeFkConstraintName(modelRelation, datasourceInfo.constraintNameClipLength);
473 ReferenceCheckInfo rcInfo = null;
474
475 if (rcInfoMap != null) {
476 rcInfo = (ReferenceCheckInfo) rcInfoMap.get(relConstraintName);
477 }
478
479 if (rcInfo != null) {
480 rcInfoMap.remove(relConstraintName);
481 } else {
482 String noFkMessage = "No Foreign Key Constraint [" + relConstraintName + "] found for entity [" + entityName + "]";
484 if (messages != null) messages.add(noFkMessage);
485 if (Debug.infoOn()) Debug.logInfo(noFkMessage, module);
486
487 if (addMissing) {
488 String errMsg = createForeignKey(entity, modelRelation, relModelEntity, datasourceInfo.constraintNameClipLength, datasourceInfo.fkStyle, datasourceInfo.useFkInitiallyDeferred);
489 if (errMsg != null && errMsg.length() > 0) {
490 String message = "Could not create foreign key " + relConstraintName + " for entity [" + entity.getEntityName() + "]: " + errMsg;
491 Debug.logError(message, module);
492 if (messages != null) messages.add(message);
493 } else {
494 String message = "Created foreign key " + relConstraintName + " for entity [" + entity.getEntityName() + "]";
495 Debug.logVerbose(message, module);
496 if (messages != null) messages.add(message);
497 createdConstraints = true;
498 numFksCreated++;
499 }
500 }
501 }
502 }
503 if (createdConstraints) {
504 String message = "Created foreign key(s) for entity [" + entity.getEntityName() + "]";
505 Debug.logImportant(message, module);
506 if (messages != null) messages.add(message);
507 }
508
509 if (rcInfoMap != null) {
511 Iterator rcInfoKeysLeft = rcInfoMap.keySet().iterator();
512 while (rcInfoKeysLeft.hasNext()) {
513 String rcKeyLeft = (String ) rcInfoKeysLeft.next();
514 String message = "Unknown Foreign Key Constraint " + rcKeyLeft + " found in table " + entity.getTableName(datasourceInfo);
515 Debug.logImportant(message, module);
516 if (messages != null) messages.add(message);
517 }
518 }
519 }
520 }
521 if (Debug.infoOn()) Debug.logInfo("Created " + numFksCreated + " fk refs", module);
522 }
523
524 if (checkFkIdx) {
526 int numIndicesCreated = 0;
528
530
532 Map tableIndexListMap = this.getIndexInfo(indexTableNames, messages);
534
535
537 if (tableIndexListMap == null) {
538 if (Debug.verboseOn()) Debug.logVerbose("Ref Table Info Map is null", module);
540 } else {
541 Iterator refModelEntityIter = modelEntityList.iterator();
542 while (refModelEntityIter.hasNext()) {
543 ModelEntity entity = (ModelEntity) refModelEntityIter.next();
544 String entityName = entity.getEntityName();
545 if (entity instanceof ModelViewEntity) {
547 String entMessage = "NOT Checking View Entity " + entity.getEntityName();
548 Debug.logVerbose(entMessage, module);
549 if (messages != null) messages.add(entMessage);
550 continue;
551 }
552
553 TreeSet tableIndexList = (TreeSet ) tableIndexListMap.get(entity.getTableName(datasourceInfo));
555
556
558 if (tableIndexList == null) {
559 this.createForeignKeyIndices(entity, datasourceInfo.constraintNameClipLength, messages);
561 } else {
562 boolean createdConstraints = false;
564 Iterator relations = entity.getRelationsIterator();
565 while (relations.hasNext()) {
566 ModelRelation modelRelation = (ModelRelation) relations.next();
567 if (!"one".equals(modelRelation.getType())) {
568 continue;
569 }
570
571 String relConstraintName = makeFkConstraintName(modelRelation, datasourceInfo.constraintNameClipLength);
572 if (tableIndexList.contains(relConstraintName)) {
573 tableIndexList.remove(relConstraintName);
574 } else {
575 String noIdxMessage = "No Index [" + relConstraintName + "] found for entity [" + entityName + "]";
577 if (messages != null) messages.add(noIdxMessage);
578 if (Debug.infoOn()) Debug.logInfo(noIdxMessage, module);
579
580 if (addMissing) {
581 String errMsg = createForeignKeyIndex(entity, modelRelation, datasourceInfo.constraintNameClipLength);
582 if (errMsg != null && errMsg.length() > 0) {
583 String message = "Could not create foreign key index " + relConstraintName + " for entity [" + entity.getEntityName() + "]: " + errMsg;
584 Debug.logError(message, module);
585 if (messages != null) messages.add(message);
586 } else {
587 String message = "Created foreign key index " + relConstraintName + " for entity [" + entity.getEntityName() + "]";
588 Debug.logVerbose(message, module);
589 if (messages != null) messages.add(message);
590 createdConstraints = true;
591 numIndicesCreated++;
592 }
593 }
594 }
595 }
596 if (createdConstraints) {
597 String message = "Created foreign key index/indices for entity [" + entity.getEntityName() + "]";
598 Debug.logImportant(message, module);
599 if (messages != null) messages.add(message);
600 }
601 }
602
603 if (tableIndexList != null) {
605 Iterator tableIndexListIter = tableIndexList.iterator();
606 while (tableIndexListIter.hasNext()) {
607 String indexLeft = (String ) tableIndexListIter.next();
608 String message = "Unknown Index " + indexLeft + " found in table " + entity.getTableName(datasourceInfo);
609 Debug.logImportant(message, module);
610 if (messages != null) messages.add(message);
611 }
612 }
613 }
614 }
615 if (Debug.infoOn()) Debug.logInfo("Created " + numIndicesCreated + " indices", module);
616 }
617
618 timer.timerString("Finished Checking Entity Database");
619 }
620
621
622 public List induceModelFromDb(Collection messages) {
623 TreeSet tableNames = this.getTableNames(messages);
625
626 Map colInfo = this.getColumnInfo(tableNames, true, messages);
628
629 List newEntList = FastList.newInstance();
633
634 boolean isCaseSensitive = false;
635 DatabaseMetaData dbData = this.getDatabaseMetaData(null, messages);
636 if (dbData != null) {
637 try {
638 isCaseSensitive = dbData.supportsMixedCaseIdentifiers();
639 } catch (SQLException e) {
640 Debug.logError(e, "Error getting db meta data about case sensitive", module);
641 }
642 }
643
644 Iterator tableNamesIter = new TreeSet (colInfo.keySet()).iterator();
646 while (tableNamesIter.hasNext()) {
647 String tableName = (String ) tableNamesIter.next();
648 Map colMap = (Map ) colInfo.get(tableName);
649 ModelEntity newEntity = new ModelEntity(tableName, colMap, modelFieldTypeReader, isCaseSensitive);
650 newEntList.add(newEntity);
651 }
652
653 return newEntList;
654 }
655
656 public Document induceModelFromDb(String packageName) {
657 Document document = UtilXml.makeEmptyXmlDocument("entitymodel");
658 Element root = document.getDocumentElement();
659 root.appendChild(document.createElement("title"));
660 root.appendChild(document.createElement("description"));
661 root.appendChild(document.createElement("copyright"));
662 root.appendChild(document.createElement("author"));
663 root.appendChild(document.createElement("version"));
664
665 List messages = new ArrayList ();
667
668 TreeSet tableNames = this.getTableNames(messages);
670
671 Map colInfo = this.getColumnInfo(tableNames, true, messages);
673
674 boolean isCaseSensitive = false;
675 DatabaseMetaData dbData = this.getDatabaseMetaData(null, messages);
676 if (dbData != null) {
677 try {
678 isCaseSensitive = dbData.supportsMixedCaseIdentifiers();
679 } catch (SQLException e) {
680 Debug.logError(e, "Error getting db meta data about case sensitive", module);
681 }
682 }
683
684 if (UtilValidate.isNotEmpty(packageName)) {
685 String catalogName = null;
686 try {
687 catalogName = this.getConnection().getCatalog();
688 } catch (Exception e) {
689 }
691 packageName = "org.ofbiz.ext." + (catalogName != null ? catalogName : "unknown");
692 }
693
694
695 Iterator tableNamesIter = new TreeSet (colInfo.keySet()).iterator();
697 while (tableNamesIter.hasNext()) {
698 String tableName = (String ) tableNamesIter.next();
699 Map colMap = (Map ) colInfo.get(tableName);
700 ModelEntity newEntity = new ModelEntity(tableName, colMap, modelFieldTypeReader, isCaseSensitive);
701 root.appendChild(newEntity.toXmlElement(document, "org.ofbiz.ext." + packageName));
702 }
703
704 for (int i = 0; i < messages.size(); i++) {
706 Debug.logInfo((String ) messages.get(i), module);
707 }
708 return document;
709 }
710
711 public Document induceModelFromDb() {
712 return this.induceModelFromDb("");
713 }
714
715 public DatabaseMetaData getDatabaseMetaData(Connection connection, Collection messages) {
716 if (connection == null) {
717 try {
718 connection = getConnection();
719 } catch (SQLException sqle) {
720 String message = "Unable to esablish a connection with the database... Error was:" + sqle.toString();
721 Debug.logError(message, module);
722 if (messages != null) messages.add(message);
723 return null;
724 } catch (GenericEntityException e) {
725 String message = "Unable to esablish a connection with the database... Error was:" + e.toString();
726 Debug.logError(message, module);
727 if (messages != null) messages.add(message);
728 return null;
729 }
730 }
731
732 if (connection == null) {
733 String message = "Unable to esablish a connection with the database, no additional information available.";
734 Debug.logError(message, module);
735 if (messages != null) messages.add(message);
736 return null;
737 }
738
739 DatabaseMetaData dbData = null;
740 try {
741 dbData = connection.getMetaData();
742 } catch (SQLException sqle) {
743 String message = "Unable to get database meta data... Error was:" + sqle.toString();
744 Debug.logError(message, module);
745 if (messages != null) {
746 messages.add(message);
747 }
748 return null;
749 }
750
751 if (dbData == null) {
752 Debug.logWarning("Unable to get database meta data; method returned null", module);
753 }
754
755 return dbData;
756 }
757
758 public void printDbMiscData(DatabaseMetaData dbData, Connection con) {
759 if (dbData == null) {
760 return;
761 }
762 if (Debug.infoOn()) {
764 try {
765 Debug.logInfo("Database Product Name is " + dbData.getDatabaseProductName(), module);
766 Debug.logInfo("Database Product Version is " + dbData.getDatabaseProductVersion(), module);
767 } catch (SQLException sqle) {
768 Debug.logWarning("Unable to get Database name & version information", module);
769 }
770 }
771 if (Debug.infoOn()) {
773 try {
774 Debug.logInfo("Database Driver Name is " + dbData.getDriverName(), module);
775 Debug.logInfo("Database Driver Version is " + dbData.getDriverVersion(), module);
776 Debug.logInfo("Database Driver JDBC Version is " + dbData.getJDBCMajorVersion() + "." + dbData.getJDBCMinorVersion(), module);
777 } catch (SQLException sqle) {
778 Debug.logWarning("Unable to get Driver name & version information", module);
779 } catch (AbstractMethodError ame) {
780 Debug.logWarning("Unable to get Driver JDBC Version", module);
781 }
782 }
783 if (Debug.infoOn()) {
785 try {
786 Debug.logInfo("Database Setting/Support Information (those with a * should be true):", module);
787 Debug.logInfo("- supports transactions [" + dbData.supportsTransactions() + "]*", module);
788 Debug.logInfo("- isolation None [" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_NONE) + "]", module);
789 Debug.logInfo("- isolation ReadCommitted [" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED) + "]", module);
790 Debug.logInfo("- isolation ReadUncommitted[" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED) + "]", module);
791 Debug.logInfo("- isolation RepeatableRead [" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ) + "]", module);
792 Debug.logInfo("- isolation Serializable [" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE) + "]", module);
793 Debug.logInfo("- default fetchsize [" + con.createStatement().getFetchSize() + "]", module);
794 Debug.logInfo("- forward only type [" + dbData.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY) + "]", module);
795 Debug.logInfo("- scroll sensitive type [" + dbData.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE) + "]", module);
796 Debug.logInfo("- scroll insensitive type [" + dbData.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE) + "]", module);
797 Debug.logInfo("- is case sensitive [" + dbData.supportsMixedCaseIdentifiers() + "]", module);
798 Debug.logInfo("- stores LowerCase [" + dbData.storesLowerCaseIdentifiers() + "]", module);
799 Debug.logInfo("- stores MixedCase [" + dbData.storesMixedCaseIdentifiers() + "]", module);
800 Debug.logInfo("- stores UpperCase [" + dbData.storesUpperCaseIdentifiers() + "]", module);
801 Debug.logInfo("- max table name length [" + dbData.getMaxTableNameLength() + "]", module);
802 Debug.logInfo("- max column name length [" + dbData.getMaxColumnNameLength() + "]", module);
803 Debug.logInfo("- max schema name length [" + dbData.getMaxSchemaNameLength() + "]", module);
804 Debug.logInfo("- concurrent connections [" + dbData.getMaxConnections() + "]", module);
805 Debug.logInfo("- concurrent statements [" + dbData.getMaxStatements() + "]", module);
806 Debug.logInfo("- ANSI SQL92 Entry [" + dbData.supportsANSI92EntryLevelSQL() + "]", module);
807 Debug.logInfo("- ANSI SQL92 Itermediate [" + dbData.supportsANSI92IntermediateSQL() + "]", module);
808 Debug.logInfo("- ANSI SQL92 Full [" + dbData.supportsANSI92FullSQL() + "]", module);
809 Debug.logInfo("- ODBC SQL Grammar Core [" + dbData.supportsCoreSQLGrammar() + "]", module);
810 Debug.logInfo("- ODBC SQL Grammar Extended[" + dbData.supportsExtendedSQLGrammar() + "]", module);
811 Debug.logInfo("- ODBC SQL Grammar Minimum [" + dbData.supportsMinimumSQLGrammar() + "]", module);
812 Debug.logInfo("- outer joins [" + dbData.supportsOuterJoins() + "]*", module);
813 Debug.logInfo("- limited outer joins [" + dbData.supportsLimitedOuterJoins() + "]", module);
814 Debug.logInfo("- full outer joins [" + dbData.supportsFullOuterJoins() + "]", module);
815 Debug.logInfo("- group by [" + dbData.supportsGroupBy() + "]*", module);
816 Debug.logInfo("- group by not in select [" + dbData.supportsGroupByUnrelated() + "]", module);
817 Debug.logInfo("- column aliasing [" + dbData.supportsColumnAliasing() + "]", module);
818 Debug.logInfo("- order by not in select [" + dbData.supportsOrderByUnrelated() + "]", module);
819 Debug.logInfo("- alter table add column [" + dbData.supportsAlterTableWithAddColumn() + "]*", module);
821 Debug.logInfo("- non-nullable column [" + dbData.supportsNonNullableColumns() + "]*", module);
822 } catch (Exception e) {
823 Debug.logWarning(e, "Unable to get misc. support/setting information", module);
824 }
825 }
826 }
827
828 public TreeSet getTableNames(Collection messages) {
829 Connection connection = null;
830
831 try {
832 connection = getConnection();
833 } catch (SQLException sqle) {
834 String message = "Unable to esablish a connection with the database... Error was:" + sqle.toString();
835 Debug.logError(message, module);
836 if (messages != null) messages.add(message);
837 return null;
838 } catch (GenericEntityException e) {
839 String message = "Unable to esablish a connection with the database... Error was:" + e.toString();
840 Debug.logError(message, module);
841 if (messages != null) messages.add(message);
842 return null;
843 }
844
845 if (connection == null) {
846 String message = "Unable to esablish a connection with the database, no additional information available.";
847 Debug.logError(message, module);
848 if (messages != null) messages.add(message);
849 return null;
850 }
851
852 DatabaseMetaData dbData = this.getDatabaseMetaData(connection, messages);
853 if (dbData == null) {
854 return null;
855 }
856
857 printDbMiscData(dbData, connection);
858 if (Debug.infoOn()) Debug.logInfo("Getting Table Info From Database", module);
859
860 TreeSet tableNames = new TreeSet ();
862 ResultSet tableSet = null;
863
864 String lookupSchemaName = null;
865 try {
866 String [] types = {"TABLE", "VIEW", "ALIAS", "SYNONYM"};
867 lookupSchemaName = getSchemaName(dbData);
868 tableSet = dbData.getTables(null, lookupSchemaName, null, types);
869 if (tableSet == null) {
870 Debug.logWarning("getTables returned null set", module);
871 }
872 } catch (SQLException sqle) {
873 String message = "Unable to get list of table information, let's try the create anyway... Error was:" + sqle.toString();
874 Debug.logError(message, module);
875 if (messages != null) messages.add(message);
876
877 try {
878 connection.close();
879 } catch (SQLException sqle2) {
880 String message2 = "Unable to close database connection, continuing anyway... Error was:" + sqle2.toString();
881 Debug.logError(message2, module);
882 if (messages != null) messages.add(message2);
883 }
884 return tableNames;
886 }
887
888 try {
889 boolean needsUpperCase = false;
890 try {
891 needsUpperCase = dbData.storesLowerCaseIdentifiers() || dbData.storesMixedCaseIdentifiers();
892 } catch (SQLException sqle) {
893 String message = "Error getting identifier case information... Error was:" + sqle.toString();
894 Debug.logError(message, module);
895 if (messages != null) messages.add(message);
896 }
897 while (tableSet.next()) {
898 try {
899 String tableName = tableSet.getString("TABLE_NAME");
900 boolean appendSchemaName = false;
902 if (tableName != null && lookupSchemaName != null && !tableName.startsWith(lookupSchemaName)) {
903 appendSchemaName = true;
904 }
905 if (needsUpperCase && tableName != null) {
906 tableName = tableName.toUpperCase();
907 }
908 if (appendSchemaName) {
909 tableName = lookupSchemaName + "." + tableName;
910 }
911
912 String tableType = tableSet.getString("TABLE_TYPE");
914 if (tableType != null && !"TABLE".equalsIgnoreCase(tableType) && !"VIEW".equalsIgnoreCase(tableType) && !"ALIAS".equalsIgnoreCase(tableType) && !"SYNONYM".equalsIgnoreCase(tableType)) {
916 continue;
917 }
918
919 tableNames.add(tableName);
921 } catch (SQLException sqle) {
923 String message = "Error getting table information... Error was:" + sqle.toString();
924 Debug.logError(message, module);
925 if (messages != null) messages.add(message);
926 continue;
927 }
928 }
929 } catch (SQLException sqle) {
930 String message = "Error getting next table information... Error was:" + sqle.toString();
931 Debug.logError(message, module);
932 if (messages != null) messages.add(message);
933 } finally {
934 try {
935 tableSet.close();
936 } catch (SQLException sqle) {
937 String message = "Unable to close ResultSet for table list, continuing anyway... Error was:" + sqle.toString();
938 Debug.logError(message, module);
939 if (messages != null) messages.add(message);
940 }
941
942 try {
943 connection.close();
944 } catch (SQLException sqle) {
945 String message = "Unable to close database connection, continuing anyway... Error was:" + sqle.toString();
946 Debug.logError(message, module);
947 if (messages != null) messages.add(message);
948 }
949 }
950 return tableNames;
951 }
952
953 public Map getColumnInfo(Set tableNames, boolean getPks, Collection messages) {
954 if (tableNames.size() == 0) {
956 return FastMap.newInstance();
957 }
958
959 Connection connection = null;
960 try {
961 try {
962 connection = getConnection();
963 } catch (SQLException sqle) {
964 String message = "Unable to esablish a connection with the database... Error was:" + sqle.toString();
965 Debug.logError(message, module);
966 if (messages != null) messages.add(message);
967 return null;
968 } catch (GenericEntityException e) {
969 String message = "Unable to esablish a connection with the database... Error was:" + e.toString();
970 Debug.logError(message, module);
971 if (messages != null) messages.add(message);
972 return null;
973 }
974
975 DatabaseMetaData dbData = null;
976 try {
977 dbData = connection.getMetaData();
978 } catch (SQLException sqle) {
979 String message = "Unable to get database meta data... Error was:" + sqle.toString();
980 Debug.logError(message, module);
981 if (messages != null) messages.add(message);
982
983 try {
984 connection.close();
985 } catch (SQLException sqle2) {
986 String message2 = "Unable to close database connection, continuing anyway... Error was:" + sqle2.toString();
987 Debug.logError(message2, module);
988 if (messages != null) messages.add(message2);
989 }
990 return null;
991 }
992
993 if (Debug.infoOn()) Debug.logInfo("Getting Column Info From Database", module);
994
995 Map colInfo = FastMap.newInstance();
996 String lookupSchemaName = null;
997 try {
998 if (dbData.supportsSchemasInTableDefinitions()) {
999 if (this.datasourceInfo.schemaName != null && this.datasourceInfo.schemaName.length() > 0) {
1000 lookupSchemaName = this.datasourceInfo.schemaName;
1001 } else {
1002 lookupSchemaName = dbData.getUserName();
1003 }
1004 }
1005
1006 boolean needsUpperCase = false;
1007 try {
1008 needsUpperCase = dbData.storesLowerCaseIdentifiers() || dbData.storesMixedCaseIdentifiers();
1009 } catch (SQLException sqle) {
1010 String message = "Error getting identifier case information... Error was:" + sqle.toString();
1011 Debug.logError(message, module);
1012 if (messages != null) messages.add(message);
1013 }
1014
1015 boolean foundCols = false;
1016 ResultSet rsCols = dbData.getColumns(null, lookupSchemaName, null, null);
1017 if (!rsCols.next()) {
1018 try {
1019 rsCols.close();
1020 } catch (SQLException sqle) {
1021 String message = "Unable to close ResultSet for column list, continuing anyway... Error was:" + sqle.toString();
1022 Debug.logError(message, module);
1023 if (messages != null) messages.add(message);
1024 }
1025 rsCols = dbData.getColumns(null, lookupSchemaName, "%", "%");
1026 if (!rsCols.next()) {
1027 } else {
1029 foundCols = true;
1030 }
1031 } else {
1032 foundCols = true;
1033 }
1034 if (foundCols) {
1035 do {
1036 try {
1037 ColumnCheckInfo ccInfo = new ColumnCheckInfo();
1038
1039 ccInfo.tableName = ColumnCheckInfo.fixupTableName(rsCols.getString("TABLE_NAME"), lookupSchemaName, needsUpperCase);
1040 if (!tableNames.contains(ccInfo.tableName)) {
1042 continue;
1043 }
1044
1045 ccInfo.columnName = rsCols.getString("COLUMN_NAME");
1046 if (needsUpperCase && ccInfo.columnName != null) {
1047 ccInfo.columnName = ccInfo.columnName.toUpperCase();
1048 }
1049 ccInfo.typeName = rsCols.getString("TYPE_NAME");
1051 ccInfo.columnSize = rsCols.getInt("COLUMN_SIZE");
1052 ccInfo.decimalDigits = rsCols.getInt("DECIMAL_DIGITS");
1053 ccInfo.isNullable = rsCols.getString("IS_NULLABLE");
1055
1056 Map tableColInfo = (Map ) colInfo.get(ccInfo.tableName);
1057 if (tableColInfo == null) {
1058 tableColInfo = FastMap.newInstance();
1059 colInfo.put(ccInfo.tableName, tableColInfo);
1060 }
1061 tableColInfo.put(ccInfo.columnName, ccInfo);
1062 } catch (SQLException sqle) {
1063 String message = "Error getting column info for column. Error was:" + sqle.toString();
1064 Debug.logError(message, module);
1065 if (messages != null) messages.add(message);
1066 continue;
1067 }
1068 } while (rsCols.next());
1069 }
1070
1071 try {
1072 rsCols.close();
1073 } catch (SQLException sqle) {
1074 String message = "Unable to close ResultSet for column list, continuing anyway... Error was:" + sqle.toString();
1075 Debug.logError(message, module);
1076 if (messages != null) messages.add(message);
1077 }
1078
1079 if (getPks) {
1080 boolean foundPks = false;
1081 ResultSet rsPks = dbData.getPrimaryKeys(null, lookupSchemaName, null);
1082 if (!rsPks.next()) {
1083 try {
1084 rsPks.close();
1085 } catch (SQLException sqle) {
1086 String message = "Unable to close ResultSet for primary key list, continuing anyway... Error was:" + sqle.toString();
1087 Debug.logError(message, module);
1088 if (messages != null) messages.add(message);
1089 }
1090 rsPks = dbData.getPrimaryKeys(null, lookupSchemaName, "%");
1091 if (!rsPks.next()) {
1092 } else {
1094 foundPks = true;
1095 }
1096 } else {
1097 foundPks = true;
1098 }
1099 if (foundPks) {
1100 do {
1101 try {
1102 String tableName = ColumnCheckInfo.fixupTableName(rsPks.getString("TABLE_NAME"), lookupSchemaName, needsUpperCase);
1103 String columnName = rsPks.getString("COLUMN_NAME");
1104 if (needsUpperCase && columnName != null) {
1105 columnName = columnName.toUpperCase();
1106 }
1107 Map tableColInfo = (Map ) colInfo.get(tableName);
1108 if (tableColInfo == null) {
1109 continue;
1111 }
1112 ColumnCheckInfo ccInfo = (ColumnCheckInfo) tableColInfo.get(columnName);
1113 if (ccInfo == null) {
1114 Debug.logWarning("Got primary key information for a column that we didn't get column information for: tableName=[" + tableName + "], columnName=[" + columnName + "]", module);
1116 continue;
1117 }
1118
1119
1123 ccInfo.isPk = true;
1124 ccInfo.pkSeq = rsPks.getShort("KEY_SEQ");
1125 ccInfo.pkName = rsPks.getString("PK_NAME");
1126 } catch (SQLException sqle) {
1127 String message = "Error getting primary key info for column. Error was:" + sqle.toString();
1128 Debug.logError(message, module);
1129 if (messages != null) messages.add(message);
1130 continue;
1131 }
1132 } while (rsPks.next());
1133 }
1134
1135 try {
1136 rsPks.close();
1137 } catch (SQLException sqle) {
1138 String message = "Unable to close ResultSet for primary key list, continuing anyway... Error was:" + sqle.toString();
1139 Debug.logError(message, module);
1140 if (messages != null) messages.add(message);
1141 }
1142 }
1143 } catch (SQLException sqle) {
1144 String message = "Error getting column meta data for Error was:" + sqle.toString() + ". Not checking columns.";
1145 Debug.logError(message, module);
1146 if (messages != null) messages.add(message);
1147 }
1150 return colInfo;
1151 } finally {
1152 if (connection != null) {
1153 try {
1154 connection.close();
1155 } catch (SQLException sqle) {
1156 String message = "Unable to close database connection, continuing anyway... Error was:" + sqle.toString();
1157 Debug.logError(message, module);
1158 if (messages != null) messages.add(message);
1159 }
1160 }
1161 }
1162 }
1163
1164 public Map getReferenceInfo(Set tableNames, Collection messages) {
1165 Connection connection = null;
1166 try {
1167 connection = getConnection();
1168 } catch (SQLException sqle) {
1169 String message = "Unable to esablish a connection with the database... Error was:" + sqle.toString();
1170 Debug.logError(message, module);
1171 if (messages != null) messages.add(message);
1172 return null;
1173 } catch (GenericEntityException e) {
1174 String message = "Unable to esablish a connection with the database... Error was:" + e.toString();
1175 Debug.logError(message, module);
1176 if (messages != null) messages.add(message);
1177 return null;
1178 }
1179
1180 DatabaseMetaData dbData = null;
1181 try {
1182 dbData = connection.getMetaData();
1183 } catch (SQLException sqle) {
1184 String message = "Unable to get database meta data... Error was:" + sqle.toString();
1185 Debug.logError(message, module);
1186 if (messages != null) messages.add(message);
1187
1188 try {
1189 connection.close();
1190 } catch (SQLException sqle2) {
1191 String message2 = "Unable to close database connection, continuing anyway... Error was:" + sqle2.toString();
1192 Debug.logError(message2, module);
1193 if (messages != null) messages.add(message2);
1194 }
1195 return null;
1196 }
1197
1198
1212
1213 if (Debug.infoOn()) Debug.logInfo("Getting Foreign Key (Reference) Info From Database", module);
1214
1215 Map refInfo = FastMap.newInstance();
1216
1217 try {
1218 String lookupSchemaName = null;
1220 if (dbData.supportsSchemasInTableDefinitions()) {
1221 if (this.datasourceInfo.schemaName != null && this.datasourceInfo.schemaName.length() > 0) {
1222 lookupSchemaName = this.datasourceInfo.schemaName;
1223 } else {
1224 lookupSchemaName = dbData.getUserName();
1225 }
1226 }
1227
1228 boolean needsUpperCase = false;
1229 try {
1230 needsUpperCase = dbData.storesLowerCaseIdentifiers() || dbData.storesMixedCaseIdentifiers();
1231 } catch (SQLException sqle) {
1232 String message = "Error getting identifier case information... Error was:" + sqle.toString();
1233 Debug.logError(message, module);
1234 if (messages != null) messages.add(message);
1235 }
1236
1237 ResultSet rsCols = dbData.getImportedKeys(null, lookupSchemaName, null);
1238 int totalFkRefs = 0;
1239
1240
1246 while (rsCols.next()) {
1247 try {
1248 ReferenceCheckInfo rcInfo = new ReferenceCheckInfo();
1249
1250 rcInfo.pkTableName = rsCols.getString("PKTABLE_NAME");
1251 if (needsUpperCase && rcInfo.pkTableName != null) {
1252 rcInfo.pkTableName = rcInfo.pkTableName.toUpperCase();
1253 }
1254 rcInfo.pkColumnName = rsCols.getString("PKCOLUMN_NAME");
1255 if (needsUpperCase && rcInfo.pkColumnName != null) {
1256 rcInfo.pkColumnName = rcInfo.pkColumnName.toUpperCase();
1257 }
1258
1259 rcInfo.fkTableName = rsCols.getString("FKTABLE_NAME");
1260 if (needsUpperCase && rcInfo.fkTableName != null) {
1261 rcInfo.fkTableName = rcInfo.fkTableName.toUpperCase();
1262 }
1263 if (!tableNames.contains(rcInfo.fkTableName)) {
1265 continue;
1266 }
1267 rcInfo.fkColumnName = rsCols.getString("FKCOLUMN_NAME");
1268 if (needsUpperCase && rcInfo.fkColumnName != null) {
1269 rcInfo.fkColumnName = rcInfo.fkColumnName.toUpperCase();
1270 }
1271 rcInfo.fkName = rsCols.getString("FK_NAME");
1272 if (needsUpperCase && rcInfo.fkName != null) {
1273 rcInfo.fkName = rcInfo.fkName.toUpperCase();
1274 }
1275
1276 if (Debug.verboseOn()) Debug.logVerbose("Got: " + rcInfo.toString(), module);
1277
1278 Map tableRefInfo = (Map ) refInfo.get(rcInfo.fkTableName);
1279 if (tableRefInfo == null) {
1280 tableRefInfo = FastMap.newInstance();
1281 refInfo.put(rcInfo.fkTableName, tableRefInfo);
1282 if (Debug.verboseOn()) Debug.logVerbose("Adding new Map for table: " + rcInfo.fkTableName, module);
1283 }
1284 if (!tableRefInfo.containsKey(rcInfo.fkName)) totalFkRefs++;
1285 tableRefInfo.put(rcInfo.fkName, rcInfo);
1286 } catch (SQLException sqle) {
1287 String message = "Error getting fk reference info for table. Error was:" + sqle.toString();
1288 Debug.logError(message, module);
1289 if (messages != null) messages.add(message);
1290 continue;
1291 }
1292 }
1293
1294 try {
1296 rsCols.close();
1297 } catch (SQLException sqle) {
1298 String message = "Unable to close ResultSet for fk reference list, continuing anyway... Error was:" + sqle.toString();
1299 Debug.logError(message, module);
1300 if (messages != null) messages.add(message);
1301 }
1302 if (Debug.infoOn()) Debug.logInfo("There are " + totalFkRefs + " foreign key refs in the database", module);
1304
1305 } catch (SQLException sqle) {
1306 String message = "Error getting fk reference meta data Error was:" + sqle.toString() + ". Not checking fk refs.";
1307 Debug.logError(message, module);
1308 if (messages != null) messages.add(message);
1309 refInfo = null;
1310 } finally {
1311 try {
1312 connection.close();
1313 } catch (SQLException sqle) {
1314 String message = "Unable to close database connection, continuing anyway... Error was:" + sqle.toString();
1315 Debug.logError(message, module);
1316 if (messages != null) messages.add(message);
1317 }
1318 }
1319 return refInfo;
1320 }
1321
1322 public Map getIndexInfo(Set tableNames, Collection messages) {
1323 Connection connection = null;
1324
1325 try {
1326 connection = getConnection();
1327 } catch (SQLException sqle) {
1328 String message = "Unable to esablish a connection with the database... Error was:" + sqle.toString();
1329 Debug.logError(message, module);
1330 if (messages != null) messages.add(message);
1331 return null;
1332 } catch (GenericEntityException e) {
1333 String message = "Unable to esablish a connection with the database... Error was:" + e.toString();
1334 Debug.logError(message, module);
1335 if (messages != null) messages.add(message);
1336 return null;
1337 }
1338
1339 DatabaseMetaData dbData = null;
1340 try {
1341 dbData = connection.getMetaData();
1342 } catch (SQLException sqle) {
1343 String message = "Unable to get database meta data... Error was:" + sqle.toString();
1344 Debug.logError(message, module);
1345 if (messages != null) messages.add(message);
1346
1347 try {
1348 connection.close();
1349 } catch (SQLException sqle2) {
1350 String message2 = "Unable to close database connection, continuing anyway... Error was:" + sqle2.toString();
1351 Debug.logError(message2, module);
1352 if (messages != null) messages.add(message2);
1353 }
1354 return null;
1355 }
1356
1357 boolean needsUpperCase = false;
1358 try {
1359 needsUpperCase = dbData.storesLowerCaseIdentifiers() || dbData.storesMixedCaseIdentifiers();
1360 } catch (SQLException sqle) {
1361 String message = "Error getting identifier case information... Error was:" + sqle.toString();
1362 Debug.logError(message, module);
1363 if (messages != null) messages.add(message);
1364 }
1365
1366 if (Debug.infoOn()) Debug.logInfo("Getting Index Info From Database", module);
1367
1368 Map indexInfo = FastMap.newInstance();
1369 try {
1370 int totalIndices = 0;
1371 Iterator tableNamesIter = tableNames.iterator();
1372 while (tableNamesIter.hasNext()) {
1373 String curTableName = (String ) tableNamesIter.next();
1374
1375 String lookupSchemaName = null;
1376 if (dbData.supportsSchemasInTableDefinitions()) {
1377 if (this.datasourceInfo.schemaName != null && this.datasourceInfo.schemaName.length() > 0) {
1378 lookupSchemaName = this.datasourceInfo.schemaName;
1379 } else {
1380 lookupSchemaName = dbData.getUserName();
1381 }
1382 }
1383
1384 ResultSet rsCols = null;
1385 try {
1386 rsCols = dbData.getIndexInfo(null, lookupSchemaName, curTableName, false, true);
1389 } catch (Exception e) {
1390 Debug.logWarning(e, "Error getting index info for table: " + curTableName + " using lookupSchemaName " + lookupSchemaName, module);
1391 }
1392
1393 while (rsCols != null && rsCols.next()) {
1394 try {
1396 if (rsCols.getShort("TYPE") == DatabaseMetaData.tableIndexStatistic) continue;
1398
1399 if (!rsCols.getBoolean("NON_UNIQUE")) continue;
1401
1402 String tableName = rsCols.getString("TABLE_NAME");
1403 if (needsUpperCase && tableName != null) {
1404 tableName = tableName.toUpperCase();
1405 }
1406 if (!tableNames.contains(tableName)) continue;
1407
1408 String indexName = rsCols.getString("INDEX_NAME");
1409 if (needsUpperCase && indexName != null) {
1410 indexName = indexName.toUpperCase();
1411 }
1412
1413 TreeSet tableIndexList = (TreeSet ) indexInfo.get(tableName);
1414 if (tableIndexList == null) {
1415 tableIndexList = new TreeSet ();
1416 indexInfo.put(tableName, tableIndexList);
1417 if (Debug.verboseOn()) Debug.logVerbose("Adding new Map for table: " + tableName, module);
1418 }
1419 if (!tableIndexList.contains(indexName)) totalIndices++;
1420 tableIndexList.add(indexName);
1421 } catch (SQLException sqle) {
1422 String message = "Error getting fk reference info for table. Error was:" + sqle.toString();
1423 Debug.logError(message, module);
1424 if (messages != null) messages.add(message);
1425 continue;
1426 }
1427 }
1428
1429 if (rsCols != null) {
1431 try {
1432 rsCols.close();
1433 } catch (SQLException sqle) {
1434 String message = "Unable to close ResultSet for fk reference list, continuing anyway... Error was:" + sqle.toString();
1435 Debug.logError(message, module);
1436 if (messages != null) messages.add(message);
1437 }
1438 }
1439 }
1440 if (Debug.infoOn()) Debug.logInfo("There are " + totalIndices + " indices in the database", module);
1441
1442 } catch (SQLException sqle) {
1443 String message = "Error getting fk reference meta data Error was:" + sqle.toString() + ". Not checking fk refs.";
1444 Debug.logError(message, module);
1445 if (messages != null) messages.add(message);
1446 indexInfo = null;
1447 } finally {
1448 try {
1449 connection.close();
1450 } catch (SQLException sqle) {
1451 String message = "Unable to close database connection, continuing anyway... Error was:" + sqle.toString();
1452 Debug.logError(message, module);
1453 if (messages != null) messages.add(message);
1454 }
1455 }
1456 return indexInfo;
1457 }
1458
1459
1460
1461
1462
1463 public String createTable(ModelEntity entity, Map modelEntities, boolean addFks) {
1464 if (entity == null) {
1465 return "ModelEntity was null and is required to create a table";
1466 }
1467 if (entity instanceof ModelViewEntity) {
1468 return "ERROR: Cannot create table for a view entity";
1469 }
1470
1471 Connection connection = null;
1472 Statement stmt = null;
1473
1474 try {
1475 connection = getConnection();
1476 } catch (SQLException sqle) {
1477 return "Unable to esablish a connection with the database... Error was: " + sqle.toString();
1478 } catch (GenericEntityException e) {
1479 return "Unable to esablish a connection with the database... Error was: " + e.toString();
1480 }
1481
1482 StringBuffer sqlBuf = new StringBuffer ("CREATE TABLE ");
1483 sqlBuf.append(entity.getTableName(this.datasourceInfo));
1484 sqlBuf.append(" (");
1485 Iterator fieldIter = entity.getFieldsIterator();
1486 while (fieldIter.hasNext()) {
1487 ModelField field = (ModelField) fieldIter.next();
1488 ModelFieldType type = modelFieldTypeReader.getModelFieldType(field.getType());
1489 if (type == null) {
1490 return "Field type [" + type + "] not found for field [" + field.getName() + "] of entity [" + entity.getEntityName() + "], not creating table.";
1491 }
1492
1493 sqlBuf.append(field.getColName());
1494 sqlBuf.append(" ");
1495 sqlBuf.append(type.getSqlType());
1496
1497 if("String".equals(type.getJavaType()) || "java.lang.String".equals(type.getJavaType())) {
1498 if (UtilValidate.isNotEmpty(this.datasourceInfo.characterSet)) {
1500 sqlBuf.append(" CHARACTER SET ");
1501 sqlBuf.append(this.datasourceInfo.characterSet);
1502 }
1503 if (UtilValidate.isNotEmpty(this.datasourceInfo.collate)) {
1505 sqlBuf.append(" COLLATE ");
1506 sqlBuf.append(this.datasourceInfo.collate);
1507 }
1508 }
1509
1510 if (field.getIsPk()) {
1511 if (this.datasourceInfo.alwaysUseConstraintKeyword) {
1512 sqlBuf.append(" CONSTRAINT NOT NULL, ");
1513 } else {
1514 sqlBuf.append(" NOT NULL, ");
1515 }
1516 } else {
1517 sqlBuf.append(", ");
1518 }
1519 }
1520
1521 String pkName = makePkConstraintName(entity, this.datasourceInfo.constraintNameClipLength);
1522 if (this.datasourceInfo.usePkConstraintNames) {
1523 sqlBuf.append("CONSTRAINT ");
1524 sqlBuf.append(pkName);
1525 }
1526 sqlBuf.append(" PRIMARY KEY (");
1527 sqlBuf.append(entity.colNameString(entity.getPksCopy()));
1528 sqlBuf.append(")");
1529
1530 if (addFks) {
1531
1533 Iterator relationsIter = entity.getRelationsIterator();
1535 while (relationsIter.hasNext()) {
1536 ModelRelation modelRelation = (ModelRelation) relationsIter.next();
1537 if ("one".equals(modelRelation.getType())) {
1538 ModelEntity relModelEntity = (ModelEntity) modelEntities.get(modelRelation.getRelEntityName());
1539 if (relModelEntity == null) {
1540 Debug.logError("Error adding foreign key: ModelEntity was null for related entity name " + modelRelation.getRelEntityName(), module);
1541 continue;
1542 }
1543 if (relModelEntity instanceof ModelViewEntity) {
1544 Debug.logError("Error adding foreign key: related entity is a view entity for related entity name " + modelRelation.getRelEntityName(), module);
1545 continue;
1546 }
1547
1548 String fkConstraintClause = makeFkConstraintClause(entity, modelRelation, relModelEntity, this.datasourceInfo.constraintNameClipLength, this.datasourceInfo.fkStyle, this.datasourceInfo.useFkInitiallyDeferred);
1549 if (UtilValidate.isNotEmpty(fkConstraintClause)) {
1550 sqlBuf.append(", ");
1551 sqlBuf.append(fkConstraintClause);
1552 } else {
1553 continue;
1554 }
1555 }
1556 }
1557 }
1558
1559 sqlBuf.append(")");
1560
1561 if (UtilValidate.isNotEmpty(this.datasourceInfo.tableType)) {
1563 sqlBuf.append(" TYPE ");
1564 sqlBuf.append(this.datasourceInfo.tableType);
1565 }
1566
1567 if (UtilValidate.isNotEmpty(this.datasourceInfo.characterSet)) {
1569 sqlBuf.append(" CHARACTER SET ");
1570 sqlBuf.append(this.datasourceInfo.characterSet);
1571 }
1572
1573 if (UtilValidate.isNotEmpty(this.datasourceInfo.collate)) {
1575 sqlBuf.append(" COLLATE ");
1576 sqlBuf.append(this.datasourceInfo.collate);
1577 }
1578
1579 if (Debug.verboseOn()) Debug.logVerbose("[createTable] sql=" + sqlBuf.toString(), module);
1580 try {
1581 stmt = connection.createStatement();
1582 stmt.executeUpdate(sqlBuf.toString());
1583 } catch (SQLException sqle) {
1584 return "SQL Exception while executing the following:\n" + sqlBuf.toString() + "\nError was: " + sqle.toString();
1585 } finally {
1586 try {
1587 if (stmt != null) stmt.close();
1588 } catch (SQLException sqle) {
1589 Debug.logError(sqle, module);
1590 }
1591 try {
1592 if (connection != null) {
1593 connection.close();
1594 }
1595 } catch (SQLException sqle) {
1596 Debug.logError(sqle, module);
1597 }
1598 }
1599 return null;
1600 }
1601
1602 public void deleteTable(ModelEntity entity, List messages) {
1603 if (entity == null) {
1604 String errMsg = "ModelEntity was null and is required to delete a table";
1605 Debug.logError(errMsg, module);
1606 if (messages != null) messages.add(errMsg);
1607 return;
1608 }
1609 if (entity instanceof ModelViewEntity) {
1610 return;
1614 }
1615
1616 Connection connection = null;
1617 Statement stmt = null;
1618 try {
1619 connection = getConnection();
1620 } catch (SQLException sqle) {
1621 String errMsg = "Unable to esablish a connection with the database... Error was: " + sqle.toString();
1622 Debug.logError(errMsg, module);
1623 if (messages != null) messages.add(errMsg);
1624 return;
1625 } catch (GenericEntityException e) {
1626 String errMsg = "Unable to esablish a connection with the database... Error was: " + e.toString();
1627 Debug.logError(errMsg, module);
1628 if (messages != null) messages.add(errMsg);
1629 return;
1630 }
1631
1632 String message = "Deleting table for entity [" + entity.getEntityName() + "]";
1633 Debug.logImportant(message, module);
1634 if (messages != null) messages.add(message);
1635
1636 StringBuffer sqlBuf = new StringBuffer ("DROP TABLE ");
1637 sqlBuf.append(entity.getTableName(datasourceInfo));
1638 if (Debug.verboseOn()) Debug.logVerbose("[deleteTable] sql=" + sqlBuf.toString(), module);
1639 try {
1640 stmt = connection.createStatement();
1641 stmt.executeUpdate(sqlBuf.toString());
1642 } catch (SQLException sqle) {
1643 String errMsg = "SQL Exception while executing the following:\n" + sqlBuf.toString() + "\nError was: " + sqle.toString();
1644 Debug.logError(errMsg, module);
1645 if (messages != null) messages.add(errMsg);
1646 } finally {
1647 try {
1648 if (stmt != null) stmt.close();
1649 } catch (SQLException sqle) {
1650 Debug.logError(sqle, module);
1651 }
1652 try {
1653 if (connection != null) {
1654 connection.close();
1655 }
1656 } catch (SQLException sqle) {
1657 Debug.logError(sqle, module);
1658 }
1659 }
1660 }
1661
1662 public String addColumn(ModelEntity entity, ModelField field) {
1663 if (entity == null || field == null)
1664 return "ModelEntity or ModelField where null, cannot add column";
1665 if (entity instanceof ModelViewEntity) {
1666 return "ERROR: Cannot add column for a view entity";
1667 }
1668
1669 Connection connection = null;
1670 Statement stmt = null;
1671
1672 try {
1673 connection = getConnection();
1674 } catch (SQLException sqle) {
1675 return "Unable to esablish a connection with the database... Error was: " + sqle.toString();
1676 } catch (GenericEntityException e) {
1677 return "Unable to esablish a connection with the database... Error was: " + e.toString();
1678 }
1679
1680 ModelFieldType type = modelFieldTypeReader.getModelFieldType(field.getType());
1681
1682 if (type == null) {
1683 return "Field type [" + type + "] not found for field [" + field.getName() + "] of entity [" + entity.getEntityName() + "], not adding column.";
1684 }
1685
1686 StringBuffer sqlBuf = new StringBuffer ("ALTER TABLE ");
1687 sqlBuf.append(entity.getTableName(datasourceInfo));
1688 sqlBuf.append(" ADD ");
1689 sqlBuf.append(field.getColName());
1690 sqlBuf.append(" ");
1691 sqlBuf.append(type.getSqlType());
1692
1693 if("String".equals(type.getJavaType()) || "java.lang.String".equals(type.getJavaType())) {
1694 if (UtilValidate.isNotEmpty(this.datasourceInfo.characterSet)) {
1696 sqlBuf.append(" CHARACTER SET ");
1697 sqlBuf.append(this.datasourceInfo.characterSet);
1698 }
1699
1700 if (UtilValidate.isNotEmpty(this.datasourceInfo.collate)) {
1702 sqlBuf.append(" COLLATE ");
1703 sqlBuf.append(this.datasourceInfo.collate);
1704 }
1705 }
1706
1707 String sql = sqlBuf.toString();
1708 if (Debug.infoOn()) Debug.logInfo("[addColumn] sql=" + sql, module);
1709 try {
1710 stmt = connection.createStatement();
1711 stmt.executeUpdate(sql);
1712 } catch (SQLException sqle) {
1713 StringBuffer sql2Buf = new StringBuffer ("ALTER TABLE ");
1715 sql2Buf.append(entity.getTableName(datasourceInfo));
1716 sql2Buf.append(" ADD COLUMN ");
1717 sql2Buf.append(field.getColName());
1718 sql2Buf.append(" ");
1719 sql2Buf.append(type.getSqlType());
1720
1721 if("String".equals(type.getJavaType()) || "java.lang.String".equals(type.getJavaType())) {
1722 if (UtilValidate.isNotEmpty(this.datasourceInfo.characterSet)) {
1724 sql2Buf.append(" CHARACTER SET ");
1725 sql2Buf.append(this.datasourceInfo.characterSet);
1726 }
1727
1728 if (UtilValidate.isNotEmpty(this.datasourceInfo.collate)) {
1730 sql2Buf.append(" COLLATE ");
1731 sql2Buf.append(this.datasourceInfo.collate);
1732 }
1733 }
1734
1735 String sql2 = sql2Buf.toString();
1736 if (Debug.infoOn()) Debug.logInfo("[addColumn] sql failed, trying sql2=" + sql2, module);
1737 try {
1738 stmt = connection.createStatement();
1739 stmt.executeUpdate(sql2);
1740 } catch (SQLException sqle2) {
1741 return "SQL Exception while executing the following:\n" + sql + "\nError was: " + sqle.toString();
1743 }
1744 } finally {
1745 try {
1746 if (stmt != null) {
1747 stmt.close();
1748 }
1749 } catch (SQLException sqle) {
1750 Debug.logError(sqle, module);
1751 }
1752 try {
1753 if (connection != null) {
1754 connection.close();
1755 }
1756 } catch (SQLException sqle) {
1757 Debug.logError(sqle, module);
1758 }
1759 }
1760 return null;
1761 }
1762
1763 public String renameColumn(ModelEntity entity, ModelField field, String newName) {
1764 if (entity == null || field == null)
1765 return "ModelEntity or ModelField where null, cannot rename column";
1766 if (entity instanceof ModelViewEntity) {
1767 return "ERROR: Cannot rename column for a view entity";
1768 }
1769
1770 Connection connection = null;
1771 Statement stmt = null;
1772
1773 try {
1774 connection = getConnection();
1775 } catch (SQLException sqle) {
1776 return "Unable to esablish a connection with the database... Error was: " + sqle.toString();
1777 } catch (GenericEntityException e) {
1778 return "Unable to esablish a connection with the database... Error was: " + e.toString();
1779 }
1780
1781 ModelFieldType type = modelFieldTypeReader.getModelFieldType(field.getType());
1782
1783 if (type == null) {
1784 return "Field type [" + type + "] not found for field [" + field.getName() + "] of entity [" + entity.getEntityName() + "], not renaming column.";
1785 }
1786
1787 StringBuffer sqlBuf = new StringBuffer ("ALTER TABLE ");
1788 sqlBuf.append(entity.getTableName(datasourceInfo));
1789 sqlBuf.append(" RENAME ");
1790 sqlBuf.append(field.getColName());
1791 sqlBuf.append(" TO ");
1792 sqlBuf.append(newName);
1793
1794 String sql = sqlBuf.toString();
1795 if (Debug.infoOn()) Debug.logInfo("[renameColumn] sql=" + sql, module);
1796 try {
1797 stmt = connection.createStatement();
1798 stmt.executeUpdate(sql);
1799 } catch (SQLException sqle) {
1800 return "SQL Exception while executing the following:\n" + sql + "\nError was: " + sqle.toString();
1801 } finally {
1802 try {
1803 if (stmt != null) {
1804 stmt.close();
1805 }
1806 } catch (SQLException sqle) {
1807 Debug.logError(sqle, module);
1808 }
1809 try {
1810 if (connection != null) {
1811 connection.close();
1812 }
1813 } catch (SQLException sqle) {
1814 Debug.logError(sqle, module);
1815 }
1816 }
1817 return null;
1818 }
1819
1820 public void repairColumnSize(ModelEntity entity, ModelField field, List messages) {
1821 String tempName = makeTempFieldName(field);
1823 String renamedErr = renameColumn(entity, field, tempName);
1824 if (!UtilValidate.isEmpty(renamedErr)) {
1825 if (messages != null) messages.add(renamedErr);
1826 Debug.logError(renamedErr, module);
1827 return;
1828 }
1829
1830 String addedErr = addColumn(entity, field);
1832 if (!UtilValidate.isEmpty(addedErr)) {
1833 if (messages != null) messages.add(addedErr);
1834 Debug.logError(addedErr, module);
1835 return;
1836 }
1837
1838 Connection connection = null;
1840 Statement stmt = null;
1841
1842 try {
1843 connection = getConnection();
1844 } catch (SQLException sqle) {
1845 if (messages != null)
1846 messages.add("Unable to esablish a connection with the database... Error was: " + sqle.toString());
1847 return;
1848 } catch (GenericEntityException e) {
1849 if (messages != null)
1850 messages.add("Unable to esablish a connection with the database... Error was: " + e.toString());
1851 return;
1852 }
1853
1854 StringBuffer sqlBuf1 = new StringBuffer ("UPDATE ");
1856 sqlBuf1.append(entity.getTableName(datasourceInfo));
1857 sqlBuf1.append(" SET ");
1858 sqlBuf1.append(field.getColName());
1859 sqlBuf1.append(" = ");
1860 sqlBuf1.append(tempName);
1861
1862 String sql1 = sqlBuf1.toString();
1863 if (Debug.infoOn()) Debug.logInfo("[moveData] sql=" + sql1, module);
1864 try {
1865 stmt = connection.createStatement();
1866 int changed = stmt.executeUpdate(sql1);
1867 if (Debug.infoOn()) Debug.logInfo("[moveData] " + changed + " records updated", module);
1868 } catch (SQLException sqle) {
1869 String thisMsg = "SQL Exception while executing the following:\n" + sql1 + "\nError was: " + sqle.toString();
1870 if (messages != null)
1871 messages.add(thisMsg);
1872 Debug.logError(thisMsg, module);
1873 return;
1874 } finally {
1875 try {
1876 if (stmt != null) {
1877 stmt.close();
1878 }
1879 } catch (SQLException sqle) {
1880 Debug.logError(sqle, module);
1881 }
1882 try {
1883 if (connection != null) {
1884 connection.close();
1885 }
1886 } catch (SQLException sqle) {
1887 Debug.logError(sqle, module);
1888 }
1889 }
1890
1891 try {
1893 connection = getConnection();
1894 } catch (SQLException sqle) {
1895 if (messages != null)
1896 messages.add("Unable to esablish a connection with the database... Error was: " + sqle.toString());
1897 return;
1898 } catch (GenericEntityException e) {
1899 if (messages != null)
1900 messages.add("Unable to esablish a connection with the database... Error was: " + e.toString());
1901 return;
1902 }
1903
1904 StringBuffer sqlBuf2 = new StringBuffer ("ALTER TABLE ");
1906 sqlBuf2.append(entity.getTableName(datasourceInfo));
1907 sqlBuf2.append(" DROP COLUMN ");
1908 sqlBuf2.append(tempName);
1909
1910 String sql2 = sqlBuf2.toString();
1911 if (Debug.infoOn()) Debug.logInfo("[dropColumn] sql=" + sql2, module);
1912 try {
1913 stmt = connection.createStatement();
1914 stmt.executeUpdate(sql2);
1915 } catch (SQLException sqle) {
1916 String thisMsg = "SQL Exception while executing the following:\n" + sql2 + "\nError was: " + sqle.toString();
1917 if (messages != null)
1918 messages.add(thisMsg);
1919 Debug.logError(thisMsg, module);
1920 return;
1921 } finally {
1922 try {
1923 if (stmt != null) {
1924 stmt.close();
1925 }
1926 } catch (SQLException sqle) {
1927 Debug.logError(sqle, module);
1928 }
1929 try {
1930 if (connection != null) {
1931 connection.close();
1932 }
1933 } catch (SQLException sqle) {
1934 Debug.logError(sqle, module);
1935 }
1936 }
1937 }
1938
1939 public void repairColumnSizeChanges(Map modelEntities, List fieldsWrongSize, List messages) {
1940 if (modelEntities == null || fieldsWrongSize == null || fieldsWrongSize.size() == 0) {
1941 messages.add("No fields to repair");
1942 return;
1943 }
1944
1945 if (messages == null) messages = new ArrayList ();
1946
1947 Iterator i = fieldsWrongSize.iterator();
1948 while (i.hasNext()) {
1949 String fieldInfo = (String ) i.next();
1950 String entityName = fieldInfo.substring(0, fieldInfo.indexOf('.'));
1951 String fieldName = fieldInfo.substring(fieldInfo.indexOf('.') + 1);
1952
1953 ModelEntity modelEntity = (ModelEntity) modelEntities.get(entityName);
1954 ModelField modelField = modelEntity.getField(fieldName);
1955 repairColumnSize(modelEntity, modelField, messages);
1956 }
1957 }
1958
1959 private String makeTempFieldName(ModelField field) {
1960 String tempName = "tmp_" + field.getName();
1961 if (tempName.length() > 30) {
1962 tempName = tempName.substring(0, 30);
1963 }
1964 return tempName.toUpperCase();
1965 }
1966
1967
1968
1969
1970 public String makePkConstraintName(ModelEntity entity, int constraintNameClipLength) {
1971 String pkName = "PK_" + entity.getPlainTableName();
1972
1973 if (pkName.length() > constraintNameClipLength) {
1974 pkName = pkName.substring(0, constraintNameClipLength);
1975 }
1976
1977 return pkName;
1978 }
1979
1980 public String makeFkConstraintName(ModelRelation modelRelation, int constraintNameClipLength) {
1981 String relConstraintName = modelRelation.getFkName();
1982
1983 if (relConstraintName == null || relConstraintName.length() == 0) {
1984 relConstraintName = modelRelation.getTitle() + modelRelation.getRelEntityName();
1985 relConstraintName = relConstraintName.toUpperCase();
1986 }
1987
1988 if (relConstraintName.length() > constraintNameClipLength) {
1989 relConstraintName = relConstraintName.substring(0, constraintNameClipLength);
1990 }
1991
1992 return relConstraintName;
1993 }
1994
1995
1996 public String makeIndexName(ModelIndex modelIndex, int constraintNameClipLength) {
1997 String indexName = modelIndex.getName();
1998
1999 if (indexName.length() > constraintNameClipLength) {
2000 indexName = indexName.substring(0, constraintNameClipLength);
2001 }
2002
2003 return indexName;
2004 }
2005
2006
2007 public int createForeignKeys(ModelEntity entity, Map modelEntities, List messages) {
2008 return this.createForeignKeys(entity, modelEntities, datasourceInfo.constraintNameClipLength, datasourceInfo.fkStyle, datasourceInfo.useFkInitiallyDeferred, messages);
2009 }
2010 public int createForeignKeys(ModelEntity entity, Map modelEntities, int constraintNameClipLength, String fkStyle, boolean useFkInitiallyDeferred, List messages) {
2011 if (entity == null) {
2012 String errMsg = "ModelEntity was null and is required to create foreign keys for a table";
2013 Debug.logError(errMsg, module);
2014 if (messages != null) messages.add(errMsg);
2015 return 0;
2016 }
2017 if (entity instanceof ModelViewEntity) {
2018 return 0;
2022 }
2023
2024 int fksCreated = 0;
2025
2026 Iterator relationsIter = entity.getRelationsIterator();
2028 while (relationsIter.hasNext()) {
2029 ModelRelation modelRelation = (ModelRelation) relationsIter.next();
2030 if ("one".equals(modelRelation.getType())) {
2031 ModelEntity relModelEntity = (ModelEntity) modelEntities.get(modelRelation.getRelEntityName());
2032
2033 if (relModelEntity == null) {
2034 String errMsg = "Error adding foreign key: ModelEntity was null for related entity name " + modelRelation.getRelEntityName();
2035 Debug.logError(errMsg, module);
2036 if (messages != null) messages.add(errMsg);
2037 continue;
2038 }
2039 if (relModelEntity instanceof ModelViewEntity) {
2040 String errMsg = "Error adding foreign key: related entity is a view entity for related entity name " + modelRelation.getRelEntityName();
2041 Debug.logError(errMsg, module);
2042 if (messages != null) messages.add(errMsg);
2043 continue;
2044 }
2045
2046 String retMsg = createForeignKey(entity, modelRelation, relModelEntity, constraintNameClipLength, fkStyle, useFkInitiallyDeferred);
2047 if (retMsg != null && retMsg.length() > 0) {
2048 Debug.logError(retMsg, module);
2049 if (messages != null) messages.add(retMsg);
2050 continue;
2051 }
2052
2053 fksCreated++;
2054 }
2055 }
2056
2057 if (fksCreated > 0) {
2058 String message = "Created " + fksCreated + " foreign keys for entity [" + entity.getEntityName() + "]";
2059 Debug.logImportant(message, module);
2060 if (messages != null) messages.add(message);
2061 }
2062
2063 return fksCreated;
2064 }
2065
2066 public String createForeignKey(ModelEntity entity, ModelRelation modelRelation, ModelEntity relModelEntity, int constraintNameClipLength, String fkStyle, boolean useFkInitiallyDeferred) {
2067 Connection connection = null;
2068 Statement stmt = null;
2069
2070 try {
2071 connection = getConnection();
2072 } catch (SQLException sqle) {
2073 return "Unable to esablish a connection with the database... Error was: " + sqle.toString();
2074 } catch (GenericEntityException e) {
2075 return "Unable to esablish a connection with the database... Error was: " + e.toString();
2076 }
2077
2078 StringBuffer sqlBuf = new StringBuffer ("ALTER TABLE ");
2080 sqlBuf.append(entity.getTableName(datasourceInfo));
2081 sqlBuf.append(" ADD ");
2082 String fkConstraintClause = makeFkConstraintClause(entity, modelRelation, relModelEntity, constraintNameClipLength, fkStyle, useFkInitiallyDeferred);
2083 if (UtilValidate.isEmpty(fkConstraintClause)) {
2084 return "Error creating foreign key constraint clause, see log for details";
2085 }
2086 sqlBuf.append(fkConstraintClause);
2087
2088 if (Debug.verboseOn()) Debug.logVerbose("[createForeignKey] sql=" + sqlBuf.toString(), module);
2089 try {
2090 stmt = connection.createStatement();
2091 stmt.executeUpdate(sqlBuf.toString());
2092 } catch (SQLException sqle) {
2093 return "SQL Exception while executing the following:\n" + sqlBuf.toString() + "\nError was: " + sqle.toString();
2094 } finally {
2095 try {
2096 if (stmt != null) {
2097 stmt.close();
2098 }
2099 } catch (SQLException sqle) {
2100 Debug.logError(sqle, module);
2101 }
2102 try {
2103 if (connection != null) {
2104 connection.close();
2105 }
2106 } catch (SQLException sqle) {
2107 Debug.logError(sqle, module);
2108 }
2109 }
2110 return null;
2111 }
2112
2113 public String makeFkConstraintClause(ModelEntity entity, ModelRelation modelRelation, ModelEntity relModelEntity, int constraintNameClipLength, String fkStyle, boolean useFkInitiallyDeferred) {
2114 Iterator keyMapsIter = modelRelation.getKeyMapsIterator();
2116 StringBuffer mainCols = new StringBuffer ();
2117 StringBuffer relCols = new StringBuffer ();
2118
2119 while (keyMapsIter.hasNext()) {
2120 ModelKeyMap keyMap = (ModelKeyMap) keyMapsIter.next();
2121
2122 ModelField mainField = entity.getField(keyMap.getFieldName());
2123 if (mainField == null) {
2124 Debug.logError("Bad key-map in entity [" + entity.getEntityName() + "] relation to [" + modelRelation.getTitle() + modelRelation.getRelEntityName() + "] for field [" + keyMap.getFieldName() + "]", module);
2125 return null;
2126 }
2127
2128 if (mainCols.length() > 0) {
2129 mainCols.append(", ");
2130 }
2131 mainCols.append(mainField.getColName());
2132
2133 ModelField relField = relModelEntity.getField(keyMap.getRelFieldName());
2134
2135 if (relCols.length() > 0) {
2136 relCols.append(", ");
2137 }
2138 relCols.append(relField.getColName());
2139 }
2140
2141 StringBuffer sqlBuf = new StringBuffer ("");
2142
2143 if ("name_constraint".equals(fkStyle)) {
2144 sqlBuf.append("CONSTRAINT ");
2145 String relConstraintName = makeFkConstraintName(modelRelation, constraintNameClipLength);
2146
2147 sqlBuf.append(relConstraintName);
2148
2149 sqlBuf.append(" FOREIGN KEY (");
2150 sqlBuf.append(mainCols.toString());
2151 sqlBuf.append(") REFERENCES ");
2152 sqlBuf.append(relModelEntity.getTableName(datasourceInfo));
2153 sqlBuf.append(" (");
2154 sqlBuf.append(relCols.toString());
2155 sqlBuf.append(")");
2156 if (useFkInitiallyDeferred) {
2157 sqlBuf.append(" INITIALLY DEFERRED");
2158 }
2159 } else if ("name_fk".equals(fkStyle)) {
2160 sqlBuf.append(" FOREIGN KEY ");
2161 String relConstraintName = makeFkConstraintName(modelRelation, constraintNameClipLength);
2162
2163 sqlBuf.append(relConstraintName);
2164 sqlBuf.append(" (");
2165 sqlBuf.append(mainCols.toString());
2166 sqlBuf.append(") REFERENCES ");
2167 sqlBuf.append(relModelEntity.getTableName(datasourceInfo));
2168 sqlBuf.append(" (");
2169 sqlBuf.append(relCols.toString());
2170 sqlBuf.append(")");
2171 if (useFkInitiallyDeferred) {
2172 sqlBuf.append(" INITIALLY DEFERRED");
2173 }
2174 } else {
2175 String emsg = "ERROR: fk-style specified for this data-source is not valid: " + fkStyle;
2176
2177 Debug.logError(emsg, module);
2178 throw new IllegalArgumentException (emsg);
2179 }
2180
2181 return sqlBuf.toString();
2182 }
2183
2184 public void deleteForeignKeys(ModelEntity entity, Map modelEntities, List messages) {
2185 this.deleteForeignKeys(entity, modelEntities, datasourceInfo.constraintNameClipLength, messages);
2186 }
2187
2188 public void deleteForeignKeys(ModelEntity entity, Map modelEntities, int constraintNameClipLength, List messages) {
2189 if (entity == null) {
2190 String errMsg = "ModelEntity was null and is required to delete foreign keys for a table";
2191 if (messages != null) messages.add(errMsg);
2192 Debug.logError(errMsg, module);
2193 return;
2194 }
2195 if (entity instanceof ModelViewEntity) {
2196 return;
2200 }
2201
2202 String message = "Deleting foreign keys for entity [" + entity.getEntityName() + "]";
2203 Debug.logImportant(message, module);
2204 if (messages != null) messages.add(message);
2205
2206 Iterator relationsIter = entity.getRelationsIterator();
2208 while (relationsIter.hasNext()) {
2209 ModelRelation modelRelation = (ModelRelation) relationsIter.next();
2210
2211 if ("one".equals(modelRelation.getType())) {
2212 ModelEntity relModelEntity = (ModelEntity) modelEntities.get(modelRelation.getRelEntityName());
2213
2214 if (relModelEntity == null) {
2215 String errMsg = "Error removing foreign key: ModelEntity was null for related entity name " + modelRelation.getRelEntityName();
2216 if (messages != null) messages.add(errMsg);
2217 Debug.logError(errMsg, module);
2218 continue;
2219 }
2220 if (relModelEntity instanceof ModelViewEntity) {
2221 String errMsg = "Error removing foreign key: related entity is a view entity for related entity name " + modelRelation.getRelEntityName();
2222 if (messages != null) messages.add(errMsg);
2223 Debug.logError(errMsg, module);
2224 continue;
2225 }
2226
2227 String retMsg = deleteForeignKey(entity, modelRelation, relModelEntity, constraintNameClipLength);
2228 if (retMsg != null && retMsg.length() > 0) {
2229 if (messages != null) messages.add(retMsg);
2230 Debug.logError(retMsg, module);
2231 }
2232 }
2233 }
2234 }
2235
2236 public String deleteForeignKey(ModelEntity entity, ModelRelation modelRelation, ModelEntity relModelEntity, int constraintNameClipLength) {
2237 Connection connection = null;
2238 Statement stmt = null;
2239
2240 try {
2241 connection = getConnection();
2242 } catch (SQLException sqle) {
2243 return "Unable to esablish a connection with the database... Error was: " + sqle.toString();
2244 } catch (GenericEntityException e) {
2245 return "Unable to esablish a connection with the database... Error was: " + e.toString();
2246 }
2247
2248 String relConstraintName = makeFkConstraintName(modelRelation, constraintNameClipLength);
2249
2250 StringBuffer sqlBuf = new StringBuffer ("ALTER TABLE ");
2252 sqlBuf.append(entity.getTableName(datasourceInfo));
2253 if (datasourceInfo.dropFkUseForeignKeyKeyword) {
2254 sqlBuf.append(" DROP FOREIGN KEY ");
2255 } else {
2256 sqlBuf.append(" DROP CONSTRAINT ");
2257 }
2258 sqlBuf.append(relConstraintName);
2259
2260 if (Debug.verboseOn()) Debug.logVerbose("[deleteForeignKey] sql=" + sqlBuf.toString(), module);
2261 try {
2262 stmt = connection.createStatement();
2263 stmt.executeUpdate(sqlBuf.toString());
2264 } catch (SQLException sqle) {
2265 return "SQL Exception while executing the following:\n" + sqlBuf.toString() + "\nError was: " + sqle.toString();
2266 } finally {
2267 try {
2268 if (stmt != null)
2269 stmt.close();
2270 } catch (SQLException sqle) {
2271 Debug.logError(sqle, module);
2272 }
2273 try {
2274 if (connection != null) {
2275 connection.close();
2276 }
2277 } catch (SQLException sqle) {
2278 Debug.logError(sqle, module);
2279 }
2280 }
2281 return null;
2282 }
2283
2284
2285
2286 public void createPrimaryKey(ModelEntity entity, boolean usePkConstraintNames, int constraintNameClipLength, List messages) {
2287 if (messages == null) messages = new ArrayList ();
2288 String err = createPrimaryKey(entity, usePkConstraintNames, constraintNameClipLength);
2289 if (!UtilValidate.isEmpty(err)) {
2290 messages.add(err);
2291 }
2292 }
2293
2294 public void createPrimaryKey(ModelEntity entity, boolean usePkConstraintNames, List messages) {
2295 createPrimaryKey(entity, usePkConstraintNames, datasourceInfo.constraintNameClipLength, messages);
2296 }
2297
2298 public void createPrimaryKey(ModelEntity entity, List messages) {
2299 createPrimaryKey(entity, datasourceInfo.usePkConstraintNames, messages);
2300 }
2301
2302 public String createPrimaryKey(ModelEntity entity, boolean usePkConstraintNames, int constraintNameClipLength) {
2303 if (entity == null) {
2304 return "ModelEntity was null and is required to create the primary key for a table";
2305 }
2306 if (entity instanceof ModelViewEntity) {
2307 return "Ignoring view entity [" + entity.getEntityName() + "]";
2308 }
2309
2310 String message;
2311 if (entity.getPksSize() > 0) {
2312 message = "Creating primary key for entity [" + entity.getEntityName() + "]";
2313 Connection connection = null;
2314 Statement stmt = null;
2315
2316 try {
2317 connection = getConnection();
2318 } catch (SQLException sqle) {
2319 return "Unable to esablish a connection with the database... Error was: " + sqle.toString();
2320 } catch (GenericEntityException e) {
2321 return "Unable to esablish a connection with the database... Error was: " + e.toString();
2322 }
2323
2324 StringBuffer sqlBuf = new StringBuffer ("ALTER TABLE ");
2326 sqlBuf.append(entity.getTableName(datasourceInfo));
2327 sqlBuf.append(" ADD ");
2328
2329 String pkName = makePkConstraintName(entity, constraintNameClipLength);
2330
2331 if (usePkConstraintNames) {
2332 sqlBuf.append("CONSTRAINT ");
2333 sqlBuf.append(pkName);
2334 }
2335 sqlBuf.append(" PRIMARY KEY (");
2336 sqlBuf.append(entity.colNameString(entity.getPksCopy()));
2337 sqlBuf.append(")");
2338
2339 if (Debug.verboseOn()) Debug.logVerbose("[createPrimaryKey] sql=" + sqlBuf.toString(), module);
2340 try {
2341 stmt = connection.createStatement();
2342 stmt.executeUpdate(sqlBuf.toString());
2343 } catch (SQLException sqle) {
2344 return "SQL Exception while executing the following:\n" + sqlBuf.toString() + "\nError was: " + sqle.toString();
2345 } finally {
2346 try {
2347 if (stmt != null) {
2348 stmt.close();
2349 }
2350 } catch (SQLException sqle) {
2351 Debug.logError(sqle, module);
2352 }
2353 try {
2354 if (connection != null) {
2355 connection.close();
2356 }
2357 } catch (SQLException sqle) {
2358 Debug.logError(sqle, module);
2359 }
2360 }
2361 } else {
2362 message = "No primary-key defined for table [" + entity.getEntityName() + "]";
2363 }
2364
2365 Debug.logImportant(message, module);
2366 return message;
2367 }
2368
2369 public void deletePrimaryKey(ModelEntity entity, boolean usePkConstraintNames, int constraintNameClipLength, List messages) {
2370 if (messages == null) messages = new ArrayList ();
2371 String err = deletePrimaryKey(entity, usePkConstraintNames, constraintNameClipLength);
2372 if (!UtilValidate.isEmpty(err)) {
2373 messages.add(err);
2374 }
2375 }
2376
2377 public void deletePrimaryKey(ModelEntity entity, boolean usePkConstraintNames, List messages) {
2378 deletePrimaryKey(entity, usePkConstraintNames, datasourceInfo.constraintNameClipLength, messages);
2379 }
2380
2381 public void deletePrimaryKey(ModelEntity entity, List messages) {
2382 deletePrimaryKey(entity, datasourceInfo.usePkConstraintNames, messages);
2383 }
2384
2385 public String deletePrimaryKey(ModelEntity entity, boolean usePkConstraintNames, int constraintNameClipLength) {
2386 if (entity == null) {
2387 return "ModelEntity was null and is required to delete the primary key for a table";
2388 }
2389 if (entity instanceof ModelViewEntity) {
2390 return "Ignoring view entity [" + entity.getEntityName() + "]";
2391 }
2392
2393 String message;
2394 if (entity.getPksSize() > 0) {
2395 message = "Deleting primary key for entity [" + entity.getEntityName() + "]";
2396 Connection connection = null;
2397 Statement stmt = null;
2398 try {
2399 connection = getConnection();
2400 } catch (SQLException sqle) {
2401 return "Unable to esablish a connection with the database... Error was: " + sqle.toString();
2402 } catch (GenericEntityException e) {
2403 return "Unable to esablish a connection with the database... Error was: " + e.toString();
2404 }
2405
2406 StringBuffer sqlBuf = new StringBuffer ("ALTER TABLE ");
2408 sqlBuf.append(entity.getTableName(datasourceInfo));
2409 sqlBuf.append(" DROP ");
2410
2411 String pkName = makePkConstraintName(entity, constraintNameClipLength);
2412
2413 if (usePkConstraintNames) {
2414 sqlBuf.append("CONSTRAINT ");
2415 sqlBuf.append(pkName);
2416 sqlBuf.append(" CASCADE");
2417 } else {
2418 sqlBuf.append(" PRIMARY KEY");
2419 }
2421
2422 if (Debug.verboseOn()) Debug.logVerbose("[deletePrimaryKey] sql=" + sqlBuf.toString(), module);
2423 try {
2424 stmt = connection.createStatement();
2425 stmt.executeUpdate(sqlBuf.toString());
2426 } catch (SQLException sqle) {
2427 return "SQL Exception while executing the following:\n" + sqlBuf.toString() + "\nError was: " + sqle.toString();
2428 } finally {
2429 try {
2430 if (stmt != null)
2431 stmt.close();
2432 } catch (SQLException sqle) {
2433 Debug.logError(sqle, module);
2434 }
2435 try {
2436 if (connection != null) {
2437 connection.close();
2438 }
2439 } catch (SQLException sqle) {
2440 Debug.logError(sqle, module);
2441 }
2442 }
2443 } else {
2444 message = "No primary-key defined for table [" + entity.getEntityName() + "]";
2445 }
2446
2447 Debug.logImportant(message, module);
2448 return message;
2449 }
2450
2451
2452
2453 public int createDeclaredIndices(ModelEntity entity, List messages) {
2454 if (entity == null) {
2455 String message = "ERROR: ModelEntity was null and is required to create declared indices for a table";
2456 Debug.logError(message, module);
2457 if (messages != null) messages.add(message);
2458 return 0;
2459 }
2460 if (entity instanceof ModelViewEntity) {
2461 String message = "WARNING: Cannot create declared indices for a view entity";
2462 Debug.logWarning(message, module);
2463 if (messages != null) messages.add(message);
2464 return 0;
2465 }
2466
2467 int dinsCreated = 0;
2468
2469 Iterator indexesIter = entity.getIndexesIterator();
2471 while (indexesIter.hasNext()) {
2472 ModelIndex modelIndex = (ModelIndex) indexesIter.next();
2473
2474 String retMsg = createDeclaredIndex(entity, modelIndex);
2475 if (retMsg != null && retMsg.length() > 0) {
2476 String message = "Could not create declared indices for entity [" + entity.getEntityName() + "]: " + retMsg;
2477 Debug.logError(message, module);
2478 if (messages != null) messages.add(message);
2479 continue;
2480 }
2481 dinsCreated++;
2482 }
2483
2484 if (dinsCreated > 0) {
2485 String message = "Created " + dinsCreated + " declared indices for entity [" + entity.getEntityName() + "]";
2486 Debug.logImportant(message, module);
2487 if (messages != null) messages.add(message);
2488 }
2489 return dinsCreated;
2490 }
2491
2492 public String createDeclaredIndex(ModelEntity entity, ModelIndex modelIndex) {
2493 Connection connection = null;
2494 Statement stmt = null;
2495
2496 try {
2497 connection = getConnection();
2498 } catch (SQLException sqle) {
2499 return "Unable to esablish a connection with the database... Error was: " + sqle.toString();
2500 } catch (GenericEntityException e) {
2501 return "Unable to esablish a connection with the database... Error was: " + e.toString();
2502 }
2503
2504 String createIndexSql = makeIndexClause(entity, modelIndex);
2505 if (Debug.verboseOn()) Debug.logVerbose("[createForeignKeyIndex] index sql=" + createIndexSql, module);
2506
2507 try {
2508 stmt = connection.createStatement();
2509 stmt.executeUpdate(createIndexSql);
2510 } catch (SQLException sqle) {
2511 return "SQL Exception while executing the following:\n" + createIndexSql + "\nError was: " + sqle.toString();
2512 } finally {
2513 try {
2514 if (stmt != null) stmt.close();
2515 } catch (SQLException sqle) {
2516 Debug.logError(sqle, module);
2517 }
2518 try {
2519 if (connection != null) {
2520 connection.close();
2521 }
2522 } catch (SQLException sqle) {
2523 Debug.logError(sqle, module);
2524 }
2525 }
2526 return null;
2527 }
2528
2529 public String makeIndexClause(ModelEntity entity, ModelIndex modelIndex) {
2530 Iterator fieldNamesIter = modelIndex.getIndexFieldsIterator();
2531 StringBuffer mainCols = new StringBuffer ();
2532
2533 while (fieldNamesIter.hasNext()) {
2534 String fieldName = (String ) fieldNamesIter.next();
2535 ModelField mainField = entity.getField(fieldName);
2536 if (mainCols.length() > 0) {
2537 mainCols.append(", ");
2538 }
2539 mainCols.append(mainField.getColName());
2540 }
2541
2542 StringBuffer indexSqlBuf = new StringBuffer ("CREATE ");
2543 if (modelIndex.getUnique()) {
2544 indexSqlBuf.append("UNIQUE ");
2545 }
2546 indexSqlBuf.append("INDEX ");
2547 indexSqlBuf.append(makeIndexName(modelIndex, datasourceInfo.constraintNameClipLength));
2548 indexSqlBuf.append(" ON ");
2549 indexSqlBuf.append(entity.getTableName(datasourceInfo));
2550
2551 indexSqlBuf.append(" (");
2552 indexSqlBuf.append(mainCols.toString());
2553 indexSqlBuf.append(")");
2554
2555 return indexSqlBuf.toString();
2556 }
2557
2558 public void deleteDeclaredIndices(ModelEntity entity, List messages) {
2559 if (messages == null) messages = new ArrayList ();
2560 String err = deleteDeclaredIndices(entity);
2561 if (!UtilValidate.isEmpty(err)) {
2562 messages.add(err);
2563 }
2564 }
2565
2566 public String deleteDeclaredIndices(ModelEntity entity) {
2567 if (entity == null) {
2568 return "ModelEntity was null and is required to delete declared indices for a table";
2569 }
2570 if (entity instanceof ModelViewEntity) {
2571 return "ERROR: Cannot delete declared indices for a view entity";
2572 }
2573
2574 StringBuffer retMsgsBuffer = new StringBuffer ();
2575
2576 Iterator indexesIter = entity.getIndexesIterator();
2578 while (indexesIter.hasNext()) {
2579 ModelIndex modelIndex = (ModelIndex) indexesIter.next();
2580 String retMsg = deleteDeclaredIndex(entity, modelIndex);
2581 if (retMsg != null && retMsg.length() > 0) {
2582 if (retMsgsBuffer.length() > 0) {
2583 retMsgsBuffer.append("\n");
2584 }
2585 retMsgsBuffer.append(retMsg);
2586 if (Debug.infoOn()) Debug.logInfo(retMsg, module);
2587 }
2588 }
2589
2590 if (retMsgsBuffer.length() > 0) {
2591 return retMsgsBuffer.toString();
2592 } else {
2593 return null;
2594 }
2595 }
2596
2597 public String deleteDeclaredIndex(ModelEntity entity, ModelIndex modelIndex) {
2598 Connection connection = null;
2599 Statement stmt = null;
2600
2601 try {
2602 connection = getConnection();
2603 } catch (SQLException sqle) {
2604 return "Unable to esablish a connection with the database... Error was: " + sqle.toString();
2605 } catch (GenericEntityException e) {
2606 return "Unable to esablish a connection with the database... Error was: " + e.toString();
2607 }
2608
2609
2611 StringBuffer indexSqlBuf = new StringBuffer ("DROP INDEX ");
2612 String tableName = entity.getTableName(datasourceInfo);
2613 String schemaName = (tableName == null || tableName.length() == 0 || tableName.indexOf('.') == -1) ? "" :
2614 tableName.substring(0, tableName.indexOf('.'));
2615
2616 indexSqlBuf.append(schemaName);
2617 indexSqlBuf.append(".");
2618 indexSqlBuf.append(modelIndex.getName());
2619
2620 String deleteIndexSql = indexSqlBuf.toString();
2621 if (Debug.verboseOn()) Debug.logVerbose("[deleteDeclaredIndex] index sql=" + deleteIndexSql, module);
2622
2623 try {
2624 stmt = connection.createStatement();
2625 stmt.executeUpdate(deleteIndexSql);
2626 } catch (SQLException sqle) {
2627 return "SQL Exception while executing the following:\n" + deleteIndexSql + "\nError was: " + sqle.toString();
2628 } finally {
2629 try {
2630 if (stmt != null) stmt.close();
2631 } catch (SQLException sqle) {
2632 Debug.logError(sqle, module);
2633 }
2634 try {
2635 if (connection != null) {
2636 connection.close();
2637 }
2638 } catch (SQLException sqle) {
2639 Debug.logError(sqle, module);
2640 }
2641 }
2642 return null;
2643 }
2644
2645
2646
2647 public int createForeignKeyIndices(ModelEntity entity, List messages) {
2648 return createForeignKeyIndices(entity, datasourceInfo.constraintNameClipLength, messages);
2649 }
2650
2651 public int createForeignKeyIndices(ModelEntity entity, int constraintNameClipLength, List messages) {
2652 if (entity == null) {
2653 String message = "ERROR: ModelEntity was null and is required to create foreign keys indices for a table";
2654 Debug.logError(message, module);
2655 if (messages != null) messages.add(message);
2656 return 0;
2657 }
2658 if (entity instanceof ModelViewEntity) {
2659 String message = "WARNING: Cannot create foreign keys indices for a view entity";
2660 Debug.logWarning(message, module);
2661 if (messages != null) messages.add(message);
2662 return 0;
2663 }
2664
2665 int fkisCreated = 0;
2666
2667 Iterator relationsIter = entity.getRelationsIterator();
2669 while (relationsIter.hasNext()) {
2670 ModelRelation modelRelation = (ModelRelation) relationsIter.next();
2671 if ("one".equals(modelRelation.getType())) {
2672 String retMsg = createForeignKeyIndex(entity, modelRelation, constraintNameClipLength);
2673 if (retMsg != null && retMsg.length() > 0) {
2674 String message = "Could not create foreign key indices for entity [" + entity.getEntityName() + "]: " + retMsg;
2675 Debug.logError(message, module);
2676 if (messages != null) messages.add(message);
2677 continue;
2678 }
2679 fkisCreated++;
2680 }
2681 }
2682
2683 if (fkisCreated > 0) {
2684 String message = "Created " + fkisCreated + " foreign key indices for entity [" + entity.getEntityName() + "]";
2685 Debug.logImportant(message, module);
2686 if (messages != null) messages.add(message);
2687 }
2688 return fkisCreated;
2689 }
2690
2691 public String createForeignKeyIndex(ModelEntity entity, ModelRelation modelRelation, int constraintNameClipLength) {
2692 Connection connection = null;
2693 Statement stmt = null;
2694
2695 try {
2696 connection = getConnection();
2697 } catch (SQLException sqle) {
2698 return "Unable to esablish a connection with the database... Error was: " + sqle.toString();
2699 } catch (GenericEntityException e) {
2700 return "Unable to esablish a connection with the database... Error was: " + e.toString();
2701 }
2702
2703 String createIndexSql = makeFkIndexClause(entity, modelRelation, constraintNameClipLength);
2704 if (UtilValidate.isEmpty(createIndexSql)) {
2705 return "Error creating foreign key index clause, see log for details";
2706 }
2707
2708 if (Debug.verboseOn()) Debug.logVerbose("[createForeignKeyIndex] index sql=" + createIndexSql, module);
2709
2710 try {
2711 stmt = connection.createStatement();
2712 stmt.executeUpdate(createIndexSql);
2713 } catch (SQLException sqle) {
2714 return "SQL Exception while executing the following:\n" + createIndexSql + "\nError was: " + sqle.toString();
2715 } finally {
2716 try {
2717 if (stmt != null)
2718 stmt.close();
2719 } catch (SQLException sqle) {
2720 Debug.logError(sqle, module);
2721 }
2722 try {
2723 if (connection != null) {
2724 connection.close();
2725 }
2726 } catch (SQLException sqle) {
2727 Debug.logError(sqle, module);
2728 }
2729 }
2730 return null;
2731 }
2732
2733 public String makeFkIndexClause(ModelEntity entity, ModelRelation modelRelation, int constraintNameClipLength) {
2734 Iterator keyMapsIter = modelRelation.getKeyMapsIterator();
2735 StringBuffer mainCols = new StringBuffer ();
2736
2737 while (keyMapsIter.hasNext()) {
2738 ModelKeyMap keyMap = (ModelKeyMap) keyMapsIter.next();
2739 ModelField mainField = entity.getField(keyMap.getFieldName());
2740
2741 if (mainField == null) {
2742 Debug.logError("Bad key-map in entity [" + entity.getEntityName() + "] relation to [" + modelRelation.getTitle() + modelRelation.getRelEntityName() + "] for field [" + keyMap.getFieldName() + "]", module);
2743 return null;
2744 }
2745
2746 if (mainCols.length() > 0) {
2747 mainCols.append(", ");
2748 }
2749 mainCols.append(mainField.getColName());
2750 }
2751
2752 StringBuffer indexSqlBuf = new StringBuffer ("CREATE INDEX ");
2753 String relConstraintName = makeFkConstraintName(modelRelation, constraintNameClipLength);
2754
2755 indexSqlBuf.append(relConstraintName);
2756 indexSqlBuf.append(" ON ");
2757 indexSqlBuf.append(entity.getTableName(datasourceInfo));
2758
2759 indexSqlBuf.append(" (");
2760 indexSqlBuf.append(mainCols.toString());
2761 indexSqlBuf.append(")");
2762
2763 return indexSqlBuf.toString();
2764 }
2765
2766 public void deleteForeignKeyIndices(ModelEntity entity, List messages) {
2767 if (messages == null) messages = new ArrayList ();
2768 String err = deleteForeignKeyIndices(entity, datasourceInfo.constraintNameClipLength);
2769 if (!UtilValidate.isEmpty(err)) {
2770 messages.add(err);
2771 }
2772 }
2773
2774 public String deleteForeignKeyIndices(ModelEntity entity, int constraintNameClipLength) {
2775 if (entity == null) {
2776 return "ModelEntity was null and is required to delete foreign keys indices for a table";
2777 }
2778 if (entity instanceof ModelViewEntity) {
2779 return "ERROR: Cannot delete foreign keys indices for a view entity";
2780 }
2781
2782 StringBuffer retMsgsBuffer = new StringBuffer ();
2783
2784 Iterator relationsIter = entity.getRelationsIterator();
2786
2787 while (relationsIter.hasNext()) {
2788 ModelRelation modelRelation = (ModelRelation) relationsIter.next();
2789
2790 if ("one".equals(modelRelation.getType())) {
2791 String retMsg = deleteForeignKeyIndex(entity, modelRelation, constraintNameClipLength);
2792
2793 if (retMsg != null && retMsg.length() > 0) {
2794 if (retMsgsBuffer.length() > 0) {
2795 retMsgsBuffer.append("\n");
2796 }
2797 retMsgsBuffer.append(retMsg);
2798 }
2799 }
2800 }
2801 if (retMsgsBuffer.length() > 0) {
2802 return retMsgsBuffer.toString();
2803 } else {
2804 return null;
2805 }
2806 }
2807
2808 public String deleteForeignKeyIndex(ModelEntity entity, ModelRelation modelRelation, int constraintNameClipLength) {
2809 Connection connection = null;
2810 Statement stmt = null;
2811
2812 try {
2813 connection = getConnection();
2814 } catch (SQLException sqle) {
2815 return "Unable to esablish a connection with the database... Error was: " + sqle.toString();
2816 } catch (GenericEntityException e) {
2817 return "Unable to esablish a connection with the database... Error was: " + e.toString();
2818 }
2819
2820 StringBuffer indexSqlBuf = new StringBuffer ("DROP INDEX ");
2821 String relConstraintName = makeFkConstraintName(modelRelation, constraintNameClipLength);
2822
2823 String tableName = entity.getTableName(datasourceInfo);
2824 String schemaName = (tableName == null || tableName.length() == 0 || tableName.indexOf('.') == -1) ? "" :
2825 tableName.substring(0, tableName.indexOf('.'));
2826
2827 if (UtilValidate.isNotEmpty(schemaName)) {
2828 indexSqlBuf.append(schemaName);
2829 indexSqlBuf.append(".");
2830 }
2831 indexSqlBuf.append(relConstraintName);
2832
2833 String deleteIndexSql = indexSqlBuf.toString();
2834
2835 if (Debug.verboseOn()) Debug.logVerbose("[deleteForeignKeyIndex] index sql=" + deleteIndexSql, module);
2836
2837 try {
2838 stmt = connection.createStatement();
2839 stmt.executeUpdate(deleteIndexSql);
2840 } catch (SQLException sqle) {
2841 return "SQL Exception while executing the following:\n" + deleteIndexSql + "\nError was: " + sqle.toString();
2842 } finally {
2843 try {
2844 if (stmt != null) {
2845 stmt.close();
2846 }
2847 } catch (SQLException sqle) {
2848 Debug.logError(sqle, module);
2849 }
2850 try {
2851 if (connection != null) {
2852 connection.close();
2853 }
2854 } catch (SQLException sqle) {
2855 Debug.logError(sqle, module);
2856 }
2857 }
2858 return null;
2859 }
2860
2861 public String getSchemaName(DatabaseMetaData dbData) throws SQLException {
2862 if (!isLegacy && this.datasourceInfo.useSchemas && dbData.supportsSchemasInTableDefinitions()) {
2863 if (this.datasourceInfo.schemaName != null && this.datasourceInfo.schemaName.length() > 0) {
2864 return this.datasourceInfo.schemaName;
2865 } else {
2866 return dbData.getUserName();
2867 }
2868 }
2869 return null;
2870 }
2871
2872
2873
2874 public void updateCharacterSetAndCollation(ModelEntity entity, List messages) {
2875 if (entity instanceof ModelViewEntity) {
2876 return;
2877 }
2878 if (UtilValidate.isEmpty(this.datasourceInfo.characterSet) && UtilValidate.isEmpty(this.datasourceInfo.collate)) {
2879 messages.add("Not setting character-set and collate for entity [" + entity.getEntityName() + "], options not specified in the datasource definition in the entityengine.xml file.");
2880 return;
2881 }
2882
2883 Connection connection = null;
2884
2885 try {
2886 Statement stmt = null;
2887
2888 try {
2889 connection = getConnection();
2890 } catch (SQLException sqle) {
2891 messages.add("Unable to esablish a connection with the database... Error was: " + sqle.toString());
2892 } catch (GenericEntityException e) {
2893 messages.add("Unable to esablish a connection with the database... Error was: " + e.toString());
2894 }
2895 if (connection == null) {
2896 return;
2897 }
2898
2899 StringBuffer sqlTableBuf = new StringBuffer ("ALTER TABLE ");
2900 sqlTableBuf.append(entity.getTableName(this.datasourceInfo));
2901
2903 if (UtilValidate.isNotEmpty(this.datasourceInfo.characterSet)) {
2905 sqlTableBuf.append(" DEFAULT CHARACTER SET ");
2906 sqlTableBuf.append(this.datasourceInfo.characterSet);
2907 }
2908 if (UtilValidate.isNotEmpty(this.datasourceInfo.collate)) {
2910 sqlTableBuf.append(" COLLATE ");
2911 sqlTableBuf.append(this.datasourceInfo.collate);
2912 }
2913
2914 if (Debug.verboseOn()) Debug.logVerbose("[updateCharacterSetAndCollation] character-set and collate sql=" + sqlTableBuf, module);
2915
2916 try {
2917 stmt = connection.createStatement();
2918 stmt.executeUpdate(sqlTableBuf.toString());
2919 } catch (SQLException sqle) {
2920 String errMsg = "SQL Exception while executing the following:\n" + sqlTableBuf + "\nError was: " + sqle.toString();
2921 messages.add(errMsg);
2922 Debug.logError(errMsg, module);
2923 } finally {
2924 try {
2925 if (stmt != null)
2926 stmt.close();
2927 } catch (SQLException sqle) {
2928 Debug.logError(sqle, module);
2929 }
2930 }
2931
2932 Iterator fieldIter = entity.getFieldsIterator();
2933 while (fieldIter.hasNext()) {
2934 ModelField field = (ModelField) fieldIter.next();
2935 ModelFieldType type = modelFieldTypeReader.getModelFieldType(field.getType());
2936 if (type == null) {
2937 messages.add("Field type [" + type + "] not found for field [" + field.getName() + "] of entity [" + entity.getEntityName() + "], not creating table.");
2938 continue;
2939 }
2940 if(!"String".equals(type.getJavaType()) && !"java.lang.String".equals(type.getJavaType())) {
2941 continue;
2942 }
2943
2944 StringBuffer sqlBuf = new StringBuffer ("ALTER TABLE ");
2945 sqlBuf.append(entity.getTableName(this.datasourceInfo));
2946 sqlBuf.append(" MODIFY COLUMN ");
2947 sqlBuf.append(field.getColName());
2948 sqlBuf.append(" ");
2949 sqlBuf.append(type.getSqlType());
2950
2951 if (UtilValidate.isNotEmpty(this.datasourceInfo.characterSet)) {
2953 sqlBuf.append(" CHARACTER SET ");
2954 sqlBuf.append(this.datasourceInfo.characterSet);
2955 }
2956 if (UtilValidate.isNotEmpty(this.datasourceInfo.collate)) {
2958 sqlBuf.append(" COLLATE ");
2959 sqlBuf.append(this.datasourceInfo.collate);
2960 }
2961
2962 if (field.getIsPk()) {
2963 if (this.datasourceInfo.alwaysUseConstraintKeyword) {
2964 sqlBuf.append(" CONSTRAINT NOT NULL");
2965 } else {
2966 sqlBuf.append(" NOT NULL");
2967 }
2968 }
2969
2970 if (Debug.verboseOn()) Debug.logVerbose("[updateCharacterSetAndCollation] character-set and collate sql=" + sqlBuf, module);
2971 try {
2972 stmt = connection.createStatement();
2973 stmt.executeUpdate(sqlBuf.toString());
2974 } catch (SQLException sqle) {
2975 String errMsg = "SQL Exception while executing the following:\n" + sqlBuf + "\nError was: " + sqle.toString();
2976 messages.add(errMsg);
2977 Debug.logError(errMsg, module);
2978 } finally {
2979 try {
2980 if (stmt != null)
2981 stmt.close();
2982 } catch (SQLException sqle) {
2983 Debug.logError(sqle, module);
2984 }
2985 }
2986 }
2987 } finally {
2988 try {
2989 if (connection != null) {
2990 connection.close();
2991 }
2992 } catch (SQLException sqle) {
2993 Debug.logError(sqle, module);
2994 }
2995 }
2996 }
2997
2998
2999
3000 public static class ColumnCheckInfo implements Serializable {
3001 public String tableName;
3002 public String columnName;
3003 public String typeName;
3004 public int columnSize;
3005 public int decimalDigits;
3006 public String isNullable; public boolean isPk = false;
3008 public int pkSeq;
3009 public String pkName;
3010
3011 public static String fixupTableName(String rawTableName, String lookupSchemaName, boolean needsUpperCase) {
3012 String tableName = rawTableName;
3013 boolean appendSchemaName = false;
3015 if (tableName != null && lookupSchemaName != null && !tableName.startsWith(lookupSchemaName)) {
3016 appendSchemaName = true;
3017 }
3018 if (needsUpperCase && tableName != null) {
3019 tableName = tableName.toUpperCase();
3020 }
3021 if (appendSchemaName) {
3022 tableName = lookupSchemaName + "." + tableName;
3023 }
3024 return tableName;
3025 }
3026 }
3027
3028 public static class ReferenceCheckInfo implements Serializable {
3029 public String pkTableName;
3030
3031
3032 public String pkColumnName;
3033 public String fkName;
3034 public String fkTableName;
3035
3036
3037 public String fkColumnName;
3038
3039 public String toString() {
3040 return "FK Reference from table " + fkTableName + " called " + fkName + " to PK in table " + pkTableName;
3041 }
3042 }
3043}
3044 |
Popular Tags |
Java API By Example, From Geeks To Geeks.