Source Code Cross Referenced for DataStore.java in  » Database-Client » SQL-Workbench » workbench » storage » Java Source Code / Java DocumentationJava Source Code and Java Documentation

Java Source Code / Java Documentation
1. 6.0 JDK Core
2. 6.0 JDK Modules
3. 6.0 JDK Modules com.sun
4. 6.0 JDK Modules com.sun.java
5. 6.0 JDK Modules sun
6. 6.0 JDK Platform
7. Ajax
8. Apache Harmony Java SE
9. Aspect oriented
10. Authentication Authorization
11. Blogger System
12. Build
13. Byte Code
14. Cache
15. Chart
16. Chat
17. Code Analyzer
18. Collaboration
19. Content Management System
20. Database Client
21. Database DBMS
22. Database JDBC Connection Pool
23. Database ORM
24. Development
25. EJB Server geronimo
26. EJB Server GlassFish
27. EJB Server JBoss 4.2.1
28. EJB Server resin 3.1.5
29. ERP CRM Financial
30. ESB
31. Forum
32. GIS
33. Graphic Library
34. Groupware
35. HTML Parser
36. IDE
37. IDE Eclipse
38. IDE Netbeans
39. Installer
40. Internationalization Localization
41. Inversion of Control
42. Issue Tracking
43. J2EE
44. JBoss
45. JMS
46. JMX
47. Library
48. Mail Clients
49. Net
50. Parser
51. PDF
52. Portal
53. Profiler
54. Project Management
55. Report
56. RSS RDF
57. Rule Engine
58. Science
59. Scripting
60. Search Engine
61. Security
62. Sevlet Container
63. Source Control
64. Swing Library
65. Template Engine
66. Test Coverage
67. Testing
68. UML
69. Web Crawler
70. Web Framework
71. Web Mail
72. Web Server
73. Web Services
74. Web Services apache cxf 2.0.1
75. Web Services AXIS2
76. Wiki Engine
77. Workflow Engines
78. XML
79. XML UI
Java
Java Tutorial
Java Open Source
Jar File Download
Java Articles
Java Products
Java by API
Photoshop Tutorials
Maya Tutorials
Flash Tutorials
3ds-Max Tutorials
Illustrator Tutorials
GIMP Tutorials
C# / C Sharp
C# / CSharp Tutorial
C# / CSharp Open Source
ASP.Net
ASP.NET Tutorial
JavaScript DHTML
JavaScript Tutorial
JavaScript Reference
HTML / CSS
HTML CSS Reference
C / ANSI-C
C Tutorial
C++
C++ Tutorial
Ruby
PHP
Python
Python Tutorial
Python Open Source
SQL Server / T-SQL
SQL Server / T-SQL Tutorial
Oracle PL / SQL
Oracle PL/SQL Tutorial
PostgreSQL
SQL / MySQL
MySQL Tutorial
VB.Net
VB.Net Tutorial
Flash / Flex / ActionScript
VBA / Excel / Access / Word
XML
XML Tutorial
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
Java Source Code / Java Documentation » Database Client » SQL Workbench » workbench.storage 
Source Cross Referenced  Class Diagram Java Document (Java Doc) 


0001:        /*
0002:         * DataStore.java
0003:         *
0004:         * This file is part of SQL Workbench/J, http://www.sql-workbench.net
0005:         *
0006:         * Copyright 2002-2008, Thomas Kellerer
0007:         * No part of this code maybe reused without the permission of the author
0008:         *
0009:         * To contact the author please send an email to: support@sql-workbench.net
0010:         *
0011:         */
0012:        package workbench.storage;
0013:
0014:        import java.sql.Clob;
0015:        import java.sql.ResultSet;
0016:        import java.sql.ResultSetMetaData;
0017:        import java.sql.SQLException;
0018:        import java.sql.Statement;
0019:        import java.util.ArrayList;
0020:        import java.util.Collections;
0021:        import java.util.HashMap;
0022:        import java.util.Iterator;
0023:        import java.util.LinkedList;
0024:        import java.util.LinkedList;
0025:        import java.util.List;
0026:        import java.util.Map;
0027:        import workbench.db.ColumnIdentifier;
0028:        import workbench.db.ConnectionProfile;
0029:        import workbench.db.DbMetadata;
0030:        import workbench.db.DeleteScriptGenerator;
0031:        import workbench.db.TableIdentifier;
0032:        import workbench.db.WbConnection;
0033:        import workbench.gui.WbSwingUtilities;
0034:        import workbench.interfaces.JobErrorHandler;
0035:        import workbench.log.LogMgr;
0036:        import workbench.resource.ResourceMgr;
0037:        import workbench.resource.Settings;
0038:        import workbench.storage.filter.FilterExpression;
0039:        import workbench.util.ConverterException;
0040:        import workbench.util.ExceptionUtil;
0041:        import workbench.util.SqlUtil;
0042:        import workbench.util.StringUtil;
0043:        import workbench.util.ValueConverter;
0044:
0045:        /**
0046:         * A class to cache the result of a database query.
0047:         * If the underlying SELECT used only one table, then the
0048:         * DataStore can be updated and the changes can be saved back
0049:         * to the database.
0050:         *
0051:         * For updating or deleting rows, key columns are required
0052:         * For inserting new rows, no keys are required.
0053:         *
0054:         * @see workbench.storage.ResultInfo
0055:         *
0056:         * @author  support@sql-workbench.net
0057:         */
0058:        public class DataStore {
0059:            // Needed for the status display in the table model
0060:            // as RowData is only package visible. Thus we need to provide the objects here
0061:            public static final Integer ROW_MODIFIED = new Integer(
0062:                    RowData.MODIFIED);
0063:            public static final Integer ROW_NEW = new Integer(RowData.NEW);
0064:            public static final Integer ROW_ORIGINAL = new Integer(
0065:                    RowData.NOT_MODIFIED);
0066:
0067:            private RowActionMonitor rowActionMonitor;
0068:
0069:            private boolean modified;
0070:
0071:            private RowDataList data;
0072:            private RowDataList deletedRows;
0073:            private RowDataList filteredRows;
0074:
0075:            // The SQL statement that was used to generate this DataStore
0076:            private String sql;
0077:
0078:            private ResultInfo resultInfo;
0079:            private TableIdentifier updateTable;
0080:            private TableIdentifier updateTableToBeUsed;
0081:
0082:            private WbConnection originalConnection;
0083:
0084:            private boolean allowUpdates = false;
0085:            private boolean updateHadErrors = false;
0086:
0087:            private boolean cancelRetrieve = false;
0088:            private boolean cancelUpdate = false;
0089:
0090:            private List<ColumnIdentifier> missingPkcolumns;
0091:
0092:            /**
0093:             *	Create a DataStore which is not based on a result set
0094:             *	and contains the columns defined in the given array
0095:             *	The column types need to match the values from from java.sql.Types
0096:             *  @param aColNames the column names
0097:             *  @param colTypes data types for each column (matching java.sql.Types.XXXX)
0098:             */
0099:            public DataStore(String[] aColNames, int[] colTypes) {
0100:                this (aColNames, colTypes, null);
0101:            }
0102:
0103:            /**
0104:             *	Create a DataStore which is not based on a result set
0105:             *	and contains the columns defined in the given array
0106:             *	The column types need to match the values from from java.sql.Types
0107:             *  @param colNames the column names
0108:             *  @param colTypes data types for each column (matching java.sql.Types.XXXX)
0109:             *  @param colSizes display size for each column
0110:             *
0111:             * @see #getColumnDisplaySize(int)
0112:             * @see workbench.gui.components.DataStoreTableModel#getColumnWidth(int)
0113:             */
0114:            public DataStore(String[] colNames, int[] colTypes, int[] colSizes) {
0115:                this .data = createData();
0116:                this .resultInfo = new ResultInfo(colNames, colTypes, colSizes);
0117:            }
0118:
0119:            /**
0120:             * Create a DataStore based on the contents of the given ResultSet.
0121:             *
0122:             * The ResultSet has to be closed by the caller.
0123:             *
0124:             * @see #initData(ResultSet, int)
0125:             */
0126:            public DataStore(ResultSet aResultSet, WbConnection aConn)
0127:                    throws SQLException {
0128:                if (aResultSet == null)
0129:                    return;
0130:                this .originalConnection = aConn;
0131:                this .initData(aResultSet);
0132:            }
0133:
0134:            DataStore(ResultInfo metaData) {
0135:                this .resultInfo = metaData;
0136:                this .data = createData();
0137:            }
0138:
0139:            /**
0140:             * Initialize this DataStore based on the given ResultSet but without reading the data.
0141:             * This is equivalent to calling new DataStore(result, false)
0142:             *
0143:             * The ResultSet has to be closed by the caller.
0144:             *
0145:             * @param aResult the ResultSet to process
0146:             * @throws java.sql.SQLException
0147:             *
0148:             * @see #DataStore(ResultSet, boolean)
0149:             */
0150:            public DataStore(ResultSet aResult) throws SQLException {
0151:                this (aResult, false);
0152:            }
0153:
0154:            /**
0155:             * Initialize this DataStore based on the given ResultSet
0156:             *
0157:             * The ResultSet has to be closed by the caller.
0158:             *
0159:             * @param aResult the ResultSet to process
0160:             * @param readData if true, the ResultSet will be processed, otherwise only the MetaData will be read
0161:             * @throws java.sql.SQLException
0162:             *
0163:             * @see #initData(ResultSet, int)
0164:             */
0165:            public DataStore(ResultSet aResult, boolean readData)
0166:                    throws SQLException {
0167:                this (aResult, readData, null, -1, null);
0168:            }
0169:
0170:            public DataStore(ResultSet aResult, WbConnection conn,
0171:                    boolean readData) throws SQLException {
0172:                this (aResult, readData, null, -1, conn);
0173:            }
0174:
0175:            /**
0176:             *	Create a DataStore based on the given ResultSet.
0177:             *	@param aResult the result set to use
0178:             *  @param readData if true the data from the ResultSet should be read into memory, otherwise only MetaData information is read
0179:             *  @param maxRows limit number of rows to maxRows if the JDBC driver does not already limit them
0180:             */
0181:            public DataStore(ResultSet aResult, boolean readData, int maxRows)
0182:                    throws SQLException {
0183:                this (aResult, readData, null, maxRows, null);
0184:            }
0185:
0186:            /**
0187:             *	Create a DataStore based on the given ResultSet.
0188:             *	@param aResult the result set to use
0189:             *  @param readData if true the data from the ResultSet should be read into memory, otherwise only MetaData information is read
0190:             *  @param aMonitor if not null, the loading process is displayed through this monitor
0191:             */
0192:            public DataStore(ResultSet aResult, boolean readData,
0193:                    RowActionMonitor aMonitor) throws SQLException {
0194:                this (aResult, readData, aMonitor, -1, null);
0195:            }
0196:
0197:            /**
0198:             *	Create a DataStore based on the given ResultSet.
0199:             *	@param aResult the result set to use
0200:             *  @param readData if true the data from the ResultSet should be read into memory, otherwise only MetaData information is read
0201:             *  @param aMonitor if not null, the loading process is displayed through this monitor
0202:             *  @param maxRows limit number of rows to maxRows if the JDBC driver does not already limit them
0203:             *  @param conn the connection that was used to retrieve the result set
0204:             */
0205:            public DataStore(ResultSet aResult, boolean readData,
0206:                    RowActionMonitor aMonitor, int maxRows, WbConnection conn)
0207:                    throws SQLException {
0208:                this .rowActionMonitor = aMonitor;
0209:                this .originalConnection = conn;
0210:                if (readData) {
0211:                    this .initData(aResult, maxRows);
0212:                    this .cancelRetrieve = false;
0213:                } else {
0214:                    ResultSetMetaData metaData = aResult.getMetaData();
0215:                    this .initMetaData(metaData);
0216:                    this .data = createData();
0217:                }
0218:            }
0219:
0220:            /**
0221:             * Create an empty DataStore based on the information given in the MetaData
0222:             * object. The DataStore can be populated with the {@link #addRow(ResultSet)} method.
0223:             */
0224:            public DataStore(ResultSetMetaData metaData, WbConnection aConn)
0225:                    throws SQLException {
0226:                this .originalConnection = aConn;
0227:                this .initMetaData(metaData);
0228:                this .data = createData();
0229:            }
0230:
0231:            /**
0232:             * Return the connection that was used to retrieve the result.
0233:             * Can be null if the DataStore was not populated using a ResultSet
0234:             */
0235:            public WbConnection getOriginalConnection() {
0236:                return this .originalConnection;
0237:            }
0238:
0239:            public void setOriginalConnection(WbConnection aConn) {
0240:                this .originalConnection = aConn;
0241:            }
0242:
0243:            public void setColumnSizes(int[] sizes) {
0244:                if (this .resultInfo == null)
0245:                    return;
0246:                this .resultInfo.setColumnSizes(sizes);
0247:            }
0248:
0249:            public void setAllowUpdates(boolean aFlag) {
0250:                this .allowUpdates = aFlag;
0251:            }
0252:
0253:            private RowDataList createData(int size) {
0254:                return new RowDataList(size);
0255:            }
0256:
0257:            private RowDataList createData() {
0258:                return new RowDataList();
0259:            }
0260:
0261:            public int duplicateRow(int aRow) {
0262:                if (aRow < 0 || aRow >= this .getRowCount())
0263:                    return -1;
0264:                RowData oldRow = this .getRow(aRow);
0265:                RowData newRow = oldRow.createCopy();
0266:                int newIndex = aRow + 1;
0267:                if (newIndex >= this .getRowCount())
0268:                    newIndex = this .getRowCount();
0269:                this .data.add(newIndex, newRow);
0270:                this .modified = true;
0271:                return newIndex;
0272:            }
0273:
0274:            public int getRowCount() {
0275:                return this .data.size();
0276:            }
0277:
0278:            public int getColumnCount() {
0279:                return this .resultInfo.getColumnCount();
0280:            }
0281:
0282:            /**
0283:             *	Returns the total number of modified, new or deleted rows
0284:             */
0285:            public int getModifiedCount() {
0286:                if (!this .isModified())
0287:                    return 0;
0288:                int count = this .getRowCount();
0289:                int modifiedCount = 0;
0290:                for (int i = 0; i < count; i++) {
0291:                    if (this .isRowModified(i))
0292:                        modifiedCount++;
0293:                }
0294:                if (this .deletedRows != null) {
0295:                    count = this .deletedRows.size();
0296:                    for (int i = 0; i < count; i++) {
0297:                        RowData rowData = this .deletedRows.get(i);
0298:                        if (!rowData.isNew())
0299:                            modifiedCount++;
0300:                    }
0301:                }
0302:                return modifiedCount;
0303:            }
0304:
0305:            public int getColumnType(int aColumn)
0306:                    throws IndexOutOfBoundsException {
0307:                return this .resultInfo.getColumnType(aColumn);
0308:            }
0309:
0310:            public String getColumnClassName(int aColumn) {
0311:                return this .resultInfo.getColumnClassName(aColumn);
0312:            }
0313:
0314:            public Class getColumnClass(int aColumn) {
0315:                return this .resultInfo.getColumnClass(aColumn);
0316:            }
0317:
0318:            /**
0319:             * Applies a filter based on the given {@link workbench.storage.filter.FilterExpression}
0320:             * to this datastore. Each row that does not satisfy the {@link workbench.storage.filter.FilterExpression#evaluate(Map)}
0321:             * criteria is removed from the active data
0322:             *
0323:             * @param filterExpression the expression identifying the rows to be kept
0324:             *
0325:             * @see workbench.storage.filter.FilterExpression
0326:             * @see #clearFilter()
0327:             */
0328:            public void applyFilter(FilterExpression filterExpression) {
0329:                this .clearFilter();
0330:                int cols = getColumnCount();
0331:                Map<String, Object> valueMap = new HashMap<String, Object>(cols);
0332:                this .filteredRows = createData();
0333:                int count = this .getRowCount();
0334:                for (int i = (count - 1); i >= 0; i--) {
0335:                    RowData rowData = this .getRow(i);
0336:
0337:                    // Build the value map required for the FilterExpression
0338:                    for (int c = 0; c < cols; c++) {
0339:                        String col = getColumnName(c);
0340:                        Object value = rowData.getValue(c);
0341:                        valueMap.put(col.toLowerCase(), value);
0342:                    }
0343:
0344:                    if (!filterExpression.evaluate(valueMap)) {
0345:                        this .data.remove(i);
0346:                        this .filteredRows.add(rowData);
0347:                    }
0348:                }
0349:
0350:                if (this .filteredRows.size() == 0)
0351:                    this .filteredRows = null;
0352:            }
0353:
0354:            /**
0355:             * Restores all rows that were filtered.
0356:             */
0357:            public void clearFilter() {
0358:                if (this .filteredRows == null)
0359:                    return;
0360:                int count = this .filteredRows.size();
0361:                for (int i = 0; i < count; i++) {
0362:                    RowData row = this .filteredRows.get(i);
0363:                    this .data.add(row);
0364:                }
0365:                this .filteredRows.clear();
0366:                this .filteredRows = null;
0367:            }
0368:
0369:            /**
0370:             *	Deletes the given row and saves it in the delete buffer
0371:             *	in order to be able to generate a DELETE statement if
0372:             *	this DataStore needs updating
0373:             */
0374:            public void deleteRow(int aRow) throws IndexOutOfBoundsException {
0375:                RowData row = this .data.get(aRow);
0376:                // new rows (not read from the database)
0377:                // do not need to be put into the deleted buffer
0378:                if (row.isNew()) {
0379:                    this .data.remove(aRow);
0380:                } else {
0381:                    if (this .deletedRows == null)
0382:                        this .deletedRows = createData();
0383:                    this .deletedRows.add(row);
0384:                    this .data.remove(aRow);
0385:                    this .modified = true;
0386:                }
0387:            }
0388:
0389:            public void deleteRowWithDependencies(int aRow)
0390:                    throws IndexOutOfBoundsException, SQLException {
0391:                if (this .updateTable == null)
0392:                    this .checkUpdateTable(originalConnection);
0393:
0394:                RowData row = this .data.get(aRow);
0395:                if (row == null)
0396:                    return;
0397:
0398:                if (!row.isNew()) {
0399:                    List<ColumnData> pk = getPkValues(aRow, true);
0400:
0401:                    DeleteScriptGenerator generator = new DeleteScriptGenerator(
0402:                            originalConnection);
0403:                    generator.setTable(updateTable);
0404:                    List<String> statements = generator.getStatementsForValues(
0405:                            pk, false);
0406:                    row.setDependencyDeletes(statements);
0407:                }
0408:                this .deleteRow(aRow);
0409:            }
0410:
0411:            /**
0412:             *	Adds the next row from the result set
0413:             *	to the DataStore. No check will be done
0414:             *	if the ResultSet matches the current
0415:             *	column structure!!
0416:             *	@return int - the new row number
0417:             *	The new row will be marked as "Not modified".
0418:             */
0419:            public int addRow(ResultSet rs) throws SQLException {
0420:                int cols = this .resultInfo.getColumnCount();
0421:                RowData row = new RowData(cols);
0422:                row.read(rs, this .resultInfo);
0423:                this .data.add(row);
0424:                return this .getRowCount() - 1;
0425:            }
0426:
0427:            /**
0428:             *	Adds a new empty row to the DataStore.
0429:             *	The new row will be marked as Modified
0430:             *	@return int - the new row number
0431:             */
0432:            public int addRow() {
0433:                RowData row = new RowData(this .resultInfo);
0434:                this .data.add(row);
0435:                this .modified = true;
0436:                return this .getRowCount() - 1;
0437:            }
0438:
0439:            public int addRow(RowData row) {
0440:                this .data.add(row);
0441:                this .modified = true;
0442:                return this .getRowCount() - 1;
0443:            }
0444:
0445:            /**
0446:             *	Inserts a row after the given row number.
0447:             *	If the new Index is greater then the current
0448:             *	row count or the new index is < 0 the new
0449:             *	row will be added at the end.
0450:             *	@return int - the new row number
0451:             */
0452:            public int insertRowAfter(int anIndex) {
0453:                RowData row = new RowData(this .resultInfo);
0454:                anIndex++;
0455:                int newIndex = -1;
0456:
0457:                if (anIndex > this .data.size() || anIndex < 0) {
0458:                    this .data.add(row);
0459:                    newIndex = this .getRowCount();
0460:                } else {
0461:                    this .data.add(anIndex, row);
0462:                    newIndex = anIndex;
0463:                }
0464:                this .modified = true;
0465:                return newIndex;
0466:            }
0467:
0468:            /**
0469:             * Prepare the information which table should be updated. This
0470:             * will not trigger the retrieval of the columns.
0471:             *
0472:             * This table will be used the next time checkUpdateTable() will
0473:             * be called. checkUpdateTable() will not retrieve the
0474:             * table name from the original SQL then.
0475:             * @see #setUpdateTable(TableIdentifier)
0476:             */
0477:            public void setUpdateTableToBeUsed(TableIdentifier tbl) {
0478:                this .updateTableToBeUsed = (tbl == null ? null : tbl
0479:                        .createCopy());
0480:            }
0481:
0482:            public void setUpdateTable(String aTablename, WbConnection aConn) {
0483:                if (StringUtil.isEmptyString(aTablename)) {
0484:                    setUpdateTable((TableIdentifier) null, aConn);
0485:                } else {
0486:                    TableIdentifier tbl = new TableIdentifier(aTablename);
0487:                    tbl.setPreserveQuotes(true);
0488:                    setUpdateTable(tbl, aConn);
0489:                }
0490:            }
0491:
0492:            public List<ColumnIdentifier> getMissingPkColumns() {
0493:                return this .missingPkcolumns;
0494:            }
0495:
0496:            public boolean pkColumnsComplete() {
0497:                return (this .missingPkcolumns == null || this .missingPkcolumns
0498:                        .size() == 0);
0499:            }
0500:
0501:            public void setUpdateTable(TableIdentifier tbl) {
0502:                setUpdateTable(tbl, this .originalConnection);
0503:            }
0504:
0505:            /**
0506:             * Sets the table to be updated for this DataStore.
0507:             * Upon setting the table, the column definition for the table
0508:             * will be retrieved using {@link workbench.db.DbMetadata}
0509:             *
0510:             * To define the table that should be used for updates, but without
0511:             * retrieving its definition (for performance reasons) use
0512:             * {@link #setUpdateTableToBeUsed(TableIdentifier)}
0513:             *
0514:             * any PK column that is not found in the current ResultInfo
0515:             * will be stored and can be retrieved using getMissingPkColumns()
0516:             *
0517:             * @param tbl the table to be used as the update table
0518:             * @param conn the connection where this table exists
0519:             *
0520:             * @see #setUpdateTableToBeUsed(TableIdentifier)
0521:             * @see #getMissingPkColumns()
0522:             */
0523:            public void setUpdateTable(TableIdentifier tbl, WbConnection conn) {
0524:                if (tbl == null) {
0525:                    this .updateTable = null;
0526:                    this .resultInfo.setUpdateTable(null);
0527:                    return;
0528:                }
0529:
0530:                if (tbl.equals(this .updateTable) || conn == null)
0531:                    return;
0532:
0533:                this .updateTable = null;
0534:                this .resultInfo.setUpdateTable(null);
0535:                this .missingPkcolumns = null;
0536:
0537:                // check the columns which are in that table
0538:                // so that we can refuse any changes to columns
0539:                // which do not derive from that table
0540:                // note that this does not work, if the
0541:                // columns were renamed via an alias in the
0542:                // select statement
0543:                try {
0544:                    DbMetadata meta = conn.getMetadata();
0545:                    if (meta == null)
0546:                        return;
0547:
0548:                    this .updateTable = tbl.createCopy();
0549:
0550:                    TableIdentifier synCheck = tbl.createCopy();
0551:                    synCheck.setSchema(meta.getSchemaToUse());
0552:                    TableIdentifier toCheck = meta.resolveSynonym(synCheck);
0553:                    List<ColumnIdentifier> columns = meta
0554:                            .getTableColumns(toCheck);
0555:                    int realColumns = 0;
0556:
0557:                    if (columns != null) {
0558:                        this .missingPkcolumns = new ArrayList<ColumnIdentifier>(
0559:                                columns.size());
0560:
0561:                        for (ColumnIdentifier column : columns) {
0562:                            int index = this .findColumn(column.getColumnName());
0563:                            if (index > -1) {
0564:                                this .resultInfo.setUpdateable(index, true);
0565:                                this .resultInfo.setIsPkColumn(index, column
0566:                                        .isPkColumn());
0567:                                this .resultInfo.setIsNullable(index, column
0568:                                        .isNullable());
0569:                                realColumns++;
0570:                            } else if (column.isPkColumn()) {
0571:                                this .missingPkcolumns.add(column);
0572:                            }
0573:                        }
0574:                    }
0575:                    if (realColumns == 0) {
0576:                        LogMgr
0577:                                .logWarning(
0578:                                        "DataStore.setUpdateTable()",
0579:                                        "No columns from the table "
0580:                                                + this .updateTable
0581:                                                        .getTableExpression()
0582:                                                + " could be found in the current result set!");
0583:                    }
0584:                    this .resultInfo.setUpdateTable(updateTable);
0585:                } catch (Exception e) {
0586:                    this .updateTable = null;
0587:                    LogMgr.logError("DataStore.setUpdateTable()",
0588:                            "Could not read table definition", e);
0589:                }
0590:
0591:            }
0592:
0593:            /**
0594:             * Returns the current table to be updated if this DataStore is
0595:             * based on a SELECT query
0596:             *
0597:             * @return The current update table
0598:             *
0599:             * @see #setGeneratingSql(String)
0600:             */
0601:            public TableIdentifier getUpdateTable() {
0602:                if (this .updateTable == null)
0603:                    return null;
0604:                return this .updateTable.createCopy();
0605:            }
0606:
0607:            /**
0608:             * Return the name of the given column
0609:             * @param aColumn The index of the column in this DataStore. The first column index is 0
0610:             * @return The name of the column
0611:             */
0612:            public String getColumnName(int aColumn)
0613:                    throws IndexOutOfBoundsException {
0614:                return this .resultInfo.getColumnName(aColumn);
0615:            }
0616:
0617:            /**
0618:             * Return the suggested display size of the column. This is
0619:             * delegated to the instance of the {@link workbench.storage.ResultInfo} class
0620:             * that is used to store the column meta data
0621:             *
0622:             * @param aColumn the column index
0623:             * @return the suggested display size
0624:             *
0625:             * @see workbench.storage.ResultInfo#getColumnSize(int)
0626:             * @see workbench.gui.components.DataStoreTableModel#getColumnWidth(int)
0627:             */
0628:            public int getColumnDisplaySize(int aColumn)
0629:                    throws IndexOutOfBoundsException {
0630:                return this .resultInfo.getColumnSize(aColumn);
0631:            }
0632:
0633:            protected Object getOriginalValue(int aRow, int aColumn) {
0634:                RowData row = this .getRow(aRow);
0635:                return row.getOriginalValue(aColumn);
0636:            }
0637:
0638:            /**
0639:             * Returns the current value of the specified column in the specified row.
0640:             * @param aRow the row to get the data from (starts at 0)
0641:             * @param aColumn the column to get the data for (starts at 0)
0642:             *
0643:             * @return the current value of the column might be different to the value
0644:             * retrieved from the database!
0645:             *
0646:             * @see workbench.storage.RowData#getValue(int)
0647:             * @see #getRow(int)
0648:             */
0649:            public Object getValue(int aRow, int aColumn)
0650:                    throws IndexOutOfBoundsException {
0651:                RowData row = this .getRow(aRow);
0652:                return row.getValue(aColumn);
0653:            }
0654:
0655:            /**
0656:             * Returns the current value of the named column in the specified row.
0657:             * This is equivalent to calling <tt>getRow(row, findColumn(columnName))</tt>
0658:             * @param aRow the row to get the data from (starts at 0)
0659:             * @param columnName the column to get the data for
0660:             *
0661:             * @return the current value of the column might be different to the value
0662:             * retrieved from the database!
0663:             *
0664:             * @see workbench.storage.RowData#getValue(int)
0665:             * @see #getRow(int)
0666:             * @see #getColumnIndex(String)
0667:             * @see #getValue(int, int)
0668:             */
0669:            public Object getValue(int aRow, String columnName)
0670:                    throws IndexOutOfBoundsException {
0671:                int index = findColumn(columnName);
0672:                RowData row = this .getRow(aRow);
0673:                return row.getValue(index);
0674:            }
0675:
0676:            /**
0677:             * Returns the value of the given row/column as a String.
0678:             * The value's toString() method is used to convert the value to a String value.
0679:             * @return Null if the column is null, or the column's value as a String
0680:             */
0681:            public String getValueAsString(int aRow, String colName)
0682:                    throws IndexOutOfBoundsException {
0683:                return getValueAsString(aRow, findColumn(colName));
0684:            }
0685:
0686:            /**
0687:             * Returns the value of the given row/column as a String.
0688:             * The value's toString() method is used to convert the value to a String value.
0689:             * @return Null if the column is null, or the column's value as a String
0690:             */
0691:            public String getValueAsString(int aRow, int aColumn)
0692:                    throws IndexOutOfBoundsException {
0693:                Object value = getValue(aRow, aColumn);
0694:                if (value == null)
0695:                    return null;
0696:
0697:                if (value instanceof  Clob) {
0698:                    try {
0699:                        Clob lob = (Clob) value;
0700:                        long len = lob.length();
0701:                        return lob.getSubString(1, (int) len);
0702:                    } catch (Exception e) {
0703:                        LogMgr.logError("DataStore.getValueAsString()",
0704:                                "Error converting BLOB to String", e);
0705:                        return null;
0706:                    }
0707:                } else {
0708:                    return value.toString();
0709:                }
0710:
0711:            }
0712:
0713:            /**
0714:             * Return the value of a column as an int value.
0715:             * If the object stored in the DataStore is an instance of Number
0716:             * the intValue() of that object will be returned, otherwise the String value
0717:             * of the column will be converted to an integer.
0718:             * If it cannot be converted to an int, the default value will be returned
0719:             * @param aRow The row
0720:             * @param aColumn The column to be returned
0721:             * @param aDefault The default value that will be returned if the the column's value cannot be converted to an int
0722:             */
0723:            public int getValueAsInt(int aRow, int aColumn, int aDefault) {
0724:                Object value = getValue(aRow, aColumn);
0725:                if (value == null) {
0726:                    return aDefault;
0727:                } else if (value instanceof  Number) {
0728:                    return ((Number) value).intValue();
0729:                } else {
0730:                    return StringUtil.getIntValue(value.toString(), aDefault);
0731:                }
0732:            }
0733:
0734:            /**
0735:             * Return the value of a column as an long value.
0736:             * If the object stored in the DataStore is an instance of Number
0737:             * the longValue() of that object will be returned, otherwise the String value
0738:             * of the column will be converted to a long.
0739:             * If it cannot be converted to an long, the default value will be returned
0740:             * @param aRow The row
0741:             * @param aColumn The column to be returned
0742:             * @param aDefault The default value that will be returned if the the column's value cannot be converted to a long
0743:             */
0744:            public long getValueAsLong(int aRow, int aColumn, long aDefault) {
0745:                Object value = getValue(aRow, aColumn);
0746:                if (value == null) {
0747:                    return aDefault;
0748:                } else if (value instanceof  Number) {
0749:                    return ((Number) value).longValue();
0750:                } else {
0751:                    return StringUtil.getLongValue(value.toString(), aDefault);
0752:                }
0753:            }
0754:
0755:            /**
0756:             *	Set a value received from a user input. This
0757:             *  will convert the given value to an object of the
0758:             *  correct class
0759:             */
0760:            public void setInputValue(int row, int col, Object value)
0761:                    throws ConverterException {
0762:                Object realValue = this .convertCellValue(value, col);
0763:                this .setValue(row, col, realValue);
0764:            }
0765:
0766:            /**
0767:             * Set the value for the given column. This will change the internal state of the DataStore to modified.
0768:             * @param aRow
0769:             * @param aColumn
0770:             * @param aValue The value to be set
0771:             */
0772:            public void setValue(int aRow, int aColumn, Object aValue)
0773:                    throws IndexOutOfBoundsException {
0774:                // do not allow setting the value for columns
0775:                // which do not have a name. Those columns cannot
0776:                // be saved to the database (because most likely they
0777:                // are computed columns like count(*) etc)
0778:                if (this .resultInfo.getColumnName(aColumn) == null)
0779:                    return;
0780:
0781:                RowData row = this .getRow(aRow);
0782:                if (row == null) {
0783:                    LogMgr.logError("DataStore.setValue()",
0784:                            "Could not find specified row!", null);
0785:                    return;
0786:                }
0787:
0788:                row.setValue(aColumn, aValue);
0789:                this .modified = row.isModified();
0790:            }
0791:
0792:            /**
0793:             * Returns the index of the column with the given name.
0794:             * @param aName The column's name to search for
0795:             * @return The column's index (first column starts at 0)
0796:             */
0797:            public int getColumnIndex(String aName) {
0798:                return this .findColumn(aName);
0799:            }
0800:
0801:            /**
0802:             * Returns true if the given row has been modified.
0803:             * A new row is considered modified only if setValue() has been called at least once.
0804:             *
0805:             * @param aRow The row to check
0806:             */
0807:            public boolean isRowModified(int aRow) {
0808:                RowData row = this .getRow(aRow);
0809:                return row.isModified();
0810:            }
0811:
0812:            /**
0813:             * Restore the original values as retrieved from the database.
0814:             * This will have no effect if {@link #isModified()} returns <code>false</code>
0815:             * @see #setValue(int, int, Object)
0816:             */
0817:            public void restoreOriginalValues() {
0818:                RowData row = null;
0819:                if (this .deletedRows != null) {
0820:                    for (int i = 0; i < this .deletedRows.size(); i++) {
0821:                        row = this .deletedRows.get(i);
0822:                        this .data.add(row);
0823:                    }
0824:                    this .deletedRows = null;
0825:                }
0826:                for (int i = 0; i < this .data.size(); i++) {
0827:                    row = this .getRow(i);
0828:                    row.restoreOriginalValues();
0829:                }
0830:                this .resetStatus();
0831:            }
0832:
0833:            /**
0834:             *	Remove all data from the DataStore
0835:             */
0836:            public void reset() {
0837:                this .data.reset();
0838:                if (this .deletedRows != null) {
0839:                    this .deletedRows.clear();
0840:                    this .deletedRows = null;
0841:                }
0842:                if (this .filteredRows != null) {
0843:                    this .filteredRows.clear();
0844:                    this .filteredRows = null;
0845:                }
0846:                this .modified = false;
0847:            }
0848:
0849:            public boolean hasUpdateableColumns() {
0850:                return this .resultInfo.hasUpdateableColumns();
0851:            }
0852:
0853:            /**
0854:             *	Returns true if at least one row has been updated.
0855:             */
0856:            public boolean hasUpdatedRows() {
0857:                if (!this .isModified())
0858:                    return false;
0859:                int count = this .getRowCount();
0860:                for (int i = 0; i < count; i++) {
0861:                    RowData row = this .getRow(i);
0862:                    if (row.isModified() && !row.isNew())
0863:                        return true;
0864:                }
0865:                return false;
0866:            }
0867:
0868:            /**
0869:             *	Returns true if at least one row has been deleted
0870:             */
0871:            public boolean hasDeletedRows() {
0872:                return (this .deletedRows != null && this .deletedRows.size() > 0);
0873:            }
0874:
0875:            /**
0876:             * Returns true if key columns are needed to save the changes
0877:             * to the database. If only inserted rows are present, then no
0878:             * key is needed. For updated or deleted rows a key is needed
0879:             */
0880:            public boolean needPkForUpdate() {
0881:                if (!this .isModified())
0882:                    return false;
0883:                return (this .hasDeletedRows() || this .hasUpdatedRows());
0884:            }
0885:
0886:            public boolean isFiltered() {
0887:                return this .filteredRows != null;
0888:            }
0889:
0890:            public boolean isModified() {
0891:                return this .modified;
0892:            }
0893:
0894:            public boolean isUpdateable() {
0895:                if (this .allowUpdates)
0896:                    return true;
0897:                return (this .updateTable != null && this .hasUpdateableColumns());
0898:            }
0899:
0900:            private int findColumn(String name) {
0901:                return this .resultInfo.findColumn(name);
0902:            }
0903:
0904:            public RowData getRow(int aRow) throws IndexOutOfBoundsException {
0905:                return this .data.get(aRow);
0906:            }
0907:
0908:            private void initMetaData(ResultSetMetaData metaData)
0909:                    throws SQLException {
0910:                this .resultInfo = new ResultInfo(metaData,
0911:                        this .originalConnection);
0912:            }
0913:
0914:            /**
0915:             * Read the column definitions from the result set's meta data
0916:             * and store the data from the ResultSet in this DataStore with no maximum
0917:             *
0918:             * The ResultSet must be closed by the caller.
0919:             *
0920:             * @param aResultSet the ResultSet to read
0921:             * @see #initData(ResultSet,int)
0922:             */
0923:            public void initData(ResultSet aResultSet) throws SQLException {
0924:                this .initData(aResultSet, -1);
0925:            }
0926:
0927:            /**
0928:             * Read the column definitions from the result set's meta data
0929:             * and store the data from the ResultSet in this DataStore (up to maxRows)
0930:             *
0931:             * The ResultSet must be closed by the caller.
0932:             *
0933:             * @param aResultSet the ResultSet to read
0934:             * @param maxRows max. number of rows to read. Zero or lower to read all rows
0935:             * @see #initData(ResultSet)
0936:             */
0937:            public void initData(ResultSet aResultSet, int maxRows)
0938:                    throws SQLException {
0939:                if (this .resultInfo == null) {
0940:                    try {
0941:                        ResultSetMetaData metaData = aResultSet.getMetaData();
0942:                        this .initMetaData(metaData);
0943:                    } catch (SQLException e) {
0944:                        LogMgr.logError("DataStore.initData()",
0945:                                "Error while retrieving ResultSetMetaData", e);
0946:                        throw e;
0947:                    }
0948:                }
0949:
0950:                if (this .rowActionMonitor != null) {
0951:                    this .rowActionMonitor
0952:                            .setMonitorType(RowActionMonitor.MONITOR_LOAD);
0953:                }
0954:
0955:                boolean trimCharData = false;
0956:                if (this .originalConnection != null) {
0957:                    ConnectionProfile prof = this .originalConnection
0958:                            .getProfile();
0959:                    if (prof != null) {
0960:                        trimCharData = prof.getTrimCharData();
0961:                    }
0962:                }
0963:
0964:                this .cancelRetrieve = false;
0965:                final int reportInterval = Settings
0966:                        .getInstance()
0967:                        .getIntProperty("workbench.gui.data.reportinterval", 10);
0968:
0969:                try {
0970:                    int rowCount = 0;
0971:                    int cols = this .resultInfo.getColumnCount();
0972:                    if (this .data == null)
0973:                        this .data = createData();
0974:                    while (!this .cancelRetrieve && aResultSet.next()) {
0975:                        rowCount++;
0976:
0977:                        if (this .rowActionMonitor != null
0978:                                && rowCount % reportInterval == 0) {
0979:                            this .rowActionMonitor.setCurrentRow(rowCount, -1);
0980:                        }
0981:
0982:                        RowData row = new RowData(cols);
0983:                        row.setTrimCharData(trimCharData);
0984:                        row.read(aResultSet, this .resultInfo);
0985:                        this .data.add(row);
0986:                        if (this .cancelRetrieve)
0987:                            break;
0988:                        if (maxRows > 0 && rowCount > maxRows)
0989:                            break;
0990:                    }
0991:                    this .cancelRetrieve = false;
0992:                } catch (SQLException e) {
0993:                    if (this .cancelRetrieve) {
0994:                        // some JDBC drivers will throw an exception when cancel() is called
0995:                        // as we silently want to use the data that has been retrieved so far
0996:                        // the Exception should not be passed to the caller
0997:                        LogMgr.logInfo("DataStore.initData()",
0998:                                "Retrieve cancelled");
0999:                        // do not reset the cancelRetrieve flag, because this is checked
1000:                        // by the caller!
1001:                    } else {
1002:                        LogMgr.logError("DataStore.initData()",
1003:                                "SQL Error during retrieve", e);
1004:                        throw e;
1005:                    }
1006:                } catch (Exception e) {
1007:                    this .cancelRetrieve = false;
1008:                    LogMgr.logError("DataStore.initData()",
1009:                            "Error during retrieve", e);
1010:                    throw new SQLException(ExceptionUtil.getDisplay(e));
1011:                } finally {
1012:                    this .modified = false;
1013:                }
1014:            }
1015:
1016:            /**
1017:             * Define the (SELECT) statement that was used to produce this
1018:             * DataStore's result set. This is used to find the update table later
1019:             */
1020:            public void setGeneratingSql(String aSql) {
1021:                this .sql = aSql;
1022:            }
1023:
1024:            public String getGeneratingSql() {
1025:                return this .sql;
1026:            }
1027:
1028:            public boolean checkUpdateTable() {
1029:                return this .checkUpdateTable(this .originalConnection);
1030:            }
1031:
1032:            public boolean checkUpdateTable(WbConnection aConn) {
1033:                if (aConn == null)
1034:                    return false;
1035:
1036:                if (this .updateTableToBeUsed != null) {
1037:                    TableIdentifier ut = this .updateTableToBeUsed;
1038:                    this .updateTableToBeUsed = null;
1039:                    this .setUpdateTable(ut, aConn);
1040:                } else {
1041:                    if (this .sql == null)
1042:                        return false;
1043:                    List tables = SqlUtil.getTables(this .sql);
1044:                    if (tables.size() != 1)
1045:                        return false;
1046:                    String table = (String) tables.get(0);
1047:                    this .setUpdateTable(table, aConn);
1048:                }
1049:                return true;
1050:            }
1051:
1052:            /**
1053:             *	Return the table that should be used when generating INSERTs
1054:             *  ("copy as INSERT")
1055:             *	Normally this is the update table. If no update table
1056:             *	is defined, the table from the SQL statement will be used
1057:             *	but no checking for key columns takes place (which might take long)
1058:             */
1059:            public String getInsertTable() {
1060:                if (this .updateTable != null)
1061:                    return this .updateTable.getTableExpression();
1062:                if (this .updateTableToBeUsed != null)
1063:                    return this .updateTableToBeUsed.getTableExpression();
1064:                if (this .sql == null)
1065:                    return null;
1066:                if (!this .sqlHasUpdateTable())
1067:                    return null;
1068:                List tables = SqlUtil.getTables(this .sql);
1069:                if (tables.size() != 1)
1070:                    return null;
1071:                String table = (String) tables.get(0);
1072:                return table;
1073:            }
1074:
1075:            /**
1076:             *	Returns true if the current data can be converted to SQL INSERT statements.
1077:             *	The data can be saved as SQL INSERTs if an update table is defined.
1078:             *	If no update table is defined, then this method will call {@link #checkUpdateTable()}
1079:             *  and try to determine the table from the used SQL statement.
1080:             *
1081:             *  @return true if an update table is defined
1082:             */
1083:            public boolean canSaveAsSqlInsert() {
1084:                return (this .getInsertTable() != null);
1085:            }
1086:
1087:            private SqlLiteralFormatter createLiteralFormatter() {
1088:                return new SqlLiteralFormatter(this .originalConnection);
1089:            }
1090:
1091:            /**
1092:             * Checks if the underlying SQL statement references only one table.
1093:             * @return true if only one table is found in the SELECT statement
1094:             *
1095:             * @see workbench.util.SqlUtil#getTables(String)
1096:             */
1097:            public boolean sqlHasUpdateTable() {
1098:                if (this .updateTable != null)
1099:                    return true;
1100:                if (this .sql == null)
1101:                    return false;
1102:                List tables = SqlUtil.getTables(this .sql);
1103:                return (tables.size() == 1);
1104:            }
1105:
1106:            /**
1107:             * Set all values in the given row to NULL
1108:             */
1109:            public void setRowNull(int aRow) {
1110:                for (int i = 0; i < this .resultInfo.getColumnCount(); i++) {
1111:                    this .setValue(aRow, i, null);
1112:                }
1113:            }
1114:
1115:            /**
1116:             * Cancels a currently running update. This has to be called
1117:             * from a different thread than the one from which updatedb() was
1118:             * called
1119:             *
1120:             * @see #updateDb(workbench.db.WbConnection, workbench.interfaces.JobErrorHandler)
1121:             */
1122:            public void cancelUpdate() {
1123:                this .cancelUpdate = true;
1124:            }
1125:
1126:            /**
1127:             * If the DataStore is beeing initialized with a ResultSet, this
1128:             * cancels the processing of the ResultSet.
1129:             *
1130:             * @see #DataStore(java.sql.ResultSet)
1131:             * @see #initData(ResultSet)
1132:             */
1133:            public void cancelRetrieve() {
1134:                this .cancelRetrieve = true;
1135:            }
1136:
1137:            /**
1138:             * Checks if the last ResultSet processing was cancelled.
1139:             * This will only be correct if initData() was called previously
1140:             *
1141:             * @return true if retrieval was cancelled.
1142:             *
1143:             * @see #DataStore(java.sql.ResultSet)
1144:             * @see #initData(ResultSet)
1145:             */
1146:            public boolean isCancelled() {
1147:                return this .cancelRetrieve;
1148:            }
1149:
1150:            public void resetCancelStatus() {
1151:                this .cancelRetrieve = false;
1152:            }
1153:
1154:            private void updateProgressMonitor(int currentRow, int totalRows) {
1155:                if (this .rowActionMonitor != null) {
1156:                    this .rowActionMonitor.setCurrentRow(currentRow, totalRows);
1157:                }
1158:            }
1159:
1160:            /**
1161:             * Returns a List of {@link workbench.storage.DmlStatement}s which
1162:             * would be executed in order to store the current content
1163:             * of the DataStore.
1164:             * The returned list will be empty if no changes were made to the datastore
1165:             *
1166:             * @return a List of {@link workbench.storage.DmlStatement}s to be sent to the database
1167:             *
1168:             * @see workbench.storage.StatementFactory
1169:             * @see workbench.storage.DmlStatement#getExecutableStatement(SqlLiteralFormatter)
1170:             */
1171:            public List<DmlStatement> getUpdateStatements(
1172:                    WbConnection aConnection) throws SQLException {
1173:                if (this .updateTable == null)
1174:                    throw new NullPointerException("No update table defined!");
1175:                this .updatePkInformation(aConnection);
1176:
1177:                List<DmlStatement> stmtList = new LinkedList<DmlStatement>();
1178:                this .resetUpdateRowCounters();
1179:
1180:                DmlStatement dml = null;
1181:                RowData row = null;
1182:
1183:                StatementFactory factory = new StatementFactory(
1184:                        this .resultInfo, this .originalConnection);
1185:                factory.setIncludeTableOwner(aConnection.getMetadata()
1186:                        .needSchemaInDML(resultInfo.getUpdateTable()));
1187:
1188:                String le = Settings.getInstance()
1189:                        .getInternalEditorLineEnding();
1190:
1191:                row = this .getNextDeletedRow();
1192:                while (row != null) {
1193:                    List<String> deletes = row.getDependencyDeletes();
1194:                    if (deletes != null) {
1195:                        for (String delete : deletes) {
1196:                            if (delete != null)
1197:                                stmtList.add(new DmlStatement(delete, null));
1198:                        }
1199:                    }
1200:                    dml = factory.createDeleteStatement(row);
1201:                    stmtList.add(dml);
1202:                    row = this .getNextDeletedRow();
1203:                }
1204:
1205:                row = this .getNextChangedRow();
1206:                while (row != null) {
1207:                    dml = factory.createUpdateStatement(row, false, le);
1208:                    stmtList.add(dml);
1209:                    row = this .getNextChangedRow();
1210:                }
1211:
1212:                row = this .getNextInsertedRow();
1213:                while (row != null) {
1214:                    dml = factory.createInsertStatement(row, false, le);
1215:                    stmtList.add(dml);
1216:                    row = this .getNextInsertedRow();
1217:                }
1218:                this .resetUpdateRowCounters();
1219:                return stmtList;
1220:            }
1221:
1222:            private boolean ignoreAllUpdateErrors = false;
1223:
1224:            private int executeGuarded(WbConnection aConnection, RowData row,
1225:                    DmlStatement dml, JobErrorHandler errorHandler, int rowNum)
1226:                    throws SQLException {
1227:                int rowsUpdated = 0;
1228:                Statement stmt = null;
1229:                String delete = null;
1230:                try {
1231:                    List<String> dependent = row.getDependencyDeletes();
1232:                    if (dependent != null) {
1233:                        try {
1234:                            stmt = aConnection.createStatement();
1235:                            Iterator<String> itr = dependent.iterator();
1236:                            while (itr.hasNext()) {
1237:                                delete = itr.next();
1238:                                stmt.executeUpdate(delete);
1239:                            }
1240:                        } finally {
1241:                            SqlUtil.closeStatement(stmt);
1242:                        }
1243:                    }
1244:                    delete = null;
1245:                    rowsUpdated = dml.execute(aConnection);
1246:                    row.setDmlSent(true);
1247:                } catch (SQLException e) {
1248:                    this .updateHadErrors = true;
1249:
1250:                    String esql = (delete == null ? dml.getExecutableStatement(
1251:                            createLiteralFormatter()).toString() : delete);
1252:                    if (this .ignoreAllUpdateErrors) {
1253:                        LogMgr.logError("DataStore.executeGuarded()",
1254:                                "Error executing statement " + esql
1255:                                        + " for row = " + row + ", error: "
1256:                                        + e.getMessage(), null);
1257:                    } else {
1258:                        boolean abort = true;
1259:                        int choice = JobErrorHandler.JOB_ABORT;
1260:                        if (errorHandler != null) {
1261:                            choice = errorHandler.getActionOnError(rowNum,
1262:                                    null, esql, e.getMessage());
1263:                        }
1264:                        if (choice == JobErrorHandler.JOB_CONTINUE) {
1265:                            abort = false;
1266:                        } else if (choice == JobErrorHandler.JOB_IGNORE_ALL) {
1267:                            abort = false;
1268:                            this .ignoreAllUpdateErrors = true;
1269:                        }
1270:                        if (abort)
1271:                            throw e;
1272:                    }
1273:                }
1274:                return rowsUpdated;
1275:            }
1276:
1277:            /**
1278:             * Save the changes to this DataStore to the database.
1279:             * The changes are applied in the following order
1280:             * <ul>
1281:             * <li>Delete statements</li>
1282:             * <li>Insert statements</li>
1283:             * <li>Update statements</li>
1284:             * </ul>
1285:             * If everything was successful, the changes will be committed automatically
1286:             * If an error occurs a rollback will be sent to the database
1287:             *
1288:             * @param aConnection the connection where the database should be updated
1289:             * @param errorHandler callback for error handling
1290:             * @return the number of rows affected
1291:             *
1292:             * @see workbench.storage.StatementFactory
1293:             * @see workbench.storage.DmlStatement#getExecutableStatement(SqlLiteralFormatter)
1294:             */
1295:            public synchronized int updateDb(WbConnection aConnection,
1296:                    JobErrorHandler errorHandler) throws SQLException {
1297:                int rows = 0;
1298:                RowData row = null;
1299:                this .cancelUpdate = false;
1300:                this .updatePkInformation(aConnection);
1301:                int totalRows = this .getModifiedCount();
1302:                this .updateHadErrors = false;
1303:                int currentRow = 0;
1304:                if (this .rowActionMonitor != null) {
1305:                    this .rowActionMonitor
1306:                            .setMonitorType(RowActionMonitor.MONITOR_UPDATE);
1307:                }
1308:
1309:                this .ignoreAllUpdateErrors = false;
1310:
1311:                StatementFactory factory = new StatementFactory(
1312:                        this .resultInfo, aConnection);
1313:                factory.setIncludeTableOwner(aConnection.getMetadata()
1314:                        .needSchemaInDML(resultInfo.getUpdateTable()));
1315:                String le = Settings.getInstance()
1316:                        .getInternalEditorLineEnding();
1317:                boolean inCommit = false;
1318:
1319:                try {
1320:                    this .resetUpdateRowCounters();
1321:                    row = this .getNextDeletedRow();
1322:                    DmlStatement dml = null;
1323:                    while (row != null) {
1324:                        currentRow++;
1325:                        this .updateProgressMonitor(currentRow, totalRows);
1326:                        if (!row.isDmlSent()) {
1327:                            dml = factory.createDeleteStatement(row);
1328:                            rows += this .executeGuarded(aConnection, row, dml,
1329:                                    errorHandler, -1);
1330:                        }
1331:                        Thread.yield();
1332:                        if (this .cancelUpdate)
1333:                            return rows;
1334:                        row = this .getNextDeletedRow();
1335:                    }
1336:
1337:                    row = this .getNextChangedRow();
1338:                    while (row != null) {
1339:                        currentRow++;
1340:                        this .updateProgressMonitor(currentRow, totalRows);
1341:                        if (!row.isDmlSent()) {
1342:                            dml = factory.createUpdateStatement(row, false, le);
1343:                            rows += this .executeGuarded(aConnection, row, dml,
1344:                                    errorHandler, currentUpdateRow);
1345:                        }
1346:                        Thread.yield();
1347:                        if (this .cancelUpdate)
1348:                            return rows;
1349:                        row = this .getNextChangedRow();
1350:                    }
1351:
1352:                    row = this .getNextInsertedRow();
1353:                    while (row != null) {
1354:                        currentRow++;
1355:                        this .updateProgressMonitor(currentRow, totalRows);
1356:                        if (!row.isDmlSent()) {
1357:                            dml = factory.createInsertStatement(row, false, le);
1358:                            rows += this .executeGuarded(aConnection, row, dml,
1359:                                    errorHandler, currentInsertRow);
1360:                        }
1361:                        Thread.yield();
1362:                        if (this .cancelUpdate)
1363:                            return rows;
1364:                        row = this .getNextInsertedRow();
1365:                    }
1366:
1367:                    // If we got here, then either all errors were ignored
1368:                    // or no errors occured at all. Even if no rows were updated
1369:                    // we are sending a commit() to make sure the transaction
1370:                    // is ended. This is especially important for Postgres
1371:                    // in case an error occured during update (and the user chose to proceed)
1372:                    if (!aConnection.getAutoCommit()) {
1373:                        inCommit = true;
1374:                        aConnection.commit();
1375:                    }
1376:                    resetStatusForSentRows();
1377:                    resetStatus();
1378:                } catch (SQLException e) {
1379:                    if (inCommit) {
1380:                        String msg = ResourceMgr.getString("ErrCommit");
1381:                        msg = StringUtil.replace(msg, "%error%", ExceptionUtil
1382:                                .getDisplay(e));
1383:                        if (errorHandler != null) {
1384:                            errorHandler.fatalError(msg);
1385:                        } else {
1386:                            WbSwingUtilities.showErrorMessage(null, msg);
1387:                        }
1388:                    }
1389:
1390:                    if (!aConnection.getAutoCommit()) {
1391:                        // in case of an exception we have to reset the dmlSent flag for
1392:                        // all modified rows otherwise the next attempt to save the changes
1393:                        // will not re-send them (but as the transaction has been rolled back,
1394:                        // they are not stored in the database)
1395:                        resetDmlSentStatus();
1396:                        try {
1397:                            aConnection.rollback();
1398:                        } catch (Throwable th) {
1399:                        }
1400:                    }
1401:                    LogMgr.logError("DataStore.updateDb()",
1402:                            "Error when saving data for row=" + currentRow
1403:                                    + ", error: " + e.getMessage(), null);
1404:                    throw e;
1405:                }
1406:
1407:                return rows;
1408:            }
1409:
1410:            public boolean lastUpdateHadErrors() {
1411:                return updateHadErrors;
1412:            }
1413:
1414:            /**
1415:             * Clears the flag for all modified rows that indicates any pending update
1416:             * has already been sent to the database.
1417:             * This is necessary if an error occurs during update, to ensure the
1418:             * rows are re-send the next time.
1419:             */
1420:            public void resetDmlSentStatus() {
1421:                int rows = this .getRowCount();
1422:                for (int i = 0; i < rows; i++) {
1423:                    RowData row = this .getRow(i);
1424:                    row.setDmlSent(false);
1425:                }
1426:                if (this .deletedRows != null) {
1427:                    rows = this .deletedRows.size();
1428:                    for (int i = 0; i < rows; i++) {
1429:                        RowData row = this .deletedRows.get(i);
1430:                        row.setDmlSent(false);
1431:                    }
1432:                }
1433:            }
1434:
1435:            /**
1436:             * Clears the modified status for those rows where
1437:             * the update has been sent to the database
1438:             */
1439:            public void resetStatusForSentRows() {
1440:                int rows = this .getRowCount();
1441:                for (int i = 0; i < rows; i++) {
1442:                    RowData row = this .getRow(i);
1443:                    if (row.isDmlSent()) {
1444:                        row.resetStatus();
1445:                    }
1446:                }
1447:                if (this .deletedRows != null) {
1448:                    RowDataList newDeleted = createData(this .deletedRows.size());
1449:                    rows = this .deletedRows.size();
1450:                    for (int i = 0; i < rows; i++) {
1451:                        RowData row = this .deletedRows.get(i);
1452:                        if (!row.isDmlSent()) {
1453:                            newDeleted.add(row);
1454:                        }
1455:                    }
1456:                    this .deletedRows.clear();
1457:                    this .deletedRows = newDeleted;
1458:                }
1459:            }
1460:
1461:            /**
1462:             * Reset all rows to not modified. After this a call
1463:             * to #isModified() will return false.
1464:             */
1465:            public void resetStatus() {
1466:                this .deletedRows = null;
1467:                this .modified = false;
1468:                for (int i = 0; i < this .data.size(); i++) {
1469:                    RowData row = this .getRow(i);
1470:                    row.resetStatus();
1471:                }
1472:                this .resetUpdateRowCounters();
1473:            }
1474:
1475:            public void sort(SortDefinition sortDef) {
1476:                synchronized (this .data) {
1477:                    RowDataListSorter sorter = new RowDataListSorter(sortDef);
1478:                    sorter.sort(this .data);
1479:                }
1480:            }
1481:
1482:            public void sortByColumn(int col, boolean ascending) {
1483:                synchronized (this .data) {
1484:                    SortDefinition sort = new SortDefinition(col, ascending);
1485:                    RowDataListSorter sorter = new RowDataListSorter(sort);
1486:                    sorter.sort(this .data);
1487:                }
1488:            }
1489:
1490:            /**
1491:             * Convert the value to the approriate class instance
1492:             * for the given column
1493:             *
1494:             * @param aValue the value as entered by the user
1495:             * @param aColumn the column for which this value should be converted
1496:             * @return an Object of the needed class for the column
1497:             * @see ValueConverter#convertValue(Object, int)
1498:             */
1499:            public Object convertCellValue(Object aValue, int aColumn)
1500:                    throws ConverterException {
1501:                int type = this .getColumnType(aColumn);
1502:                if (aValue == null)
1503:                    return null;
1504:
1505:                ValueConverter converter = new ValueConverter();
1506:
1507:                return converter.convertValue(aValue, type);
1508:            }
1509:
1510:            /**
1511:             * Return the status object for the given row.
1512:             * The status is one of
1513:             * <ul>
1514:             * <li>{@link #ROW_ORIGINAL}</li>
1515:             * <li>{@link #ROW_MODIFIED}</li>
1516:             * <li>{@link #ROW_NEW}</li>
1517:             * </ul>
1518:             * The status object is used by the {@link workbench.gui.renderer.RowStatusRenderer}
1519:             * in the result table to display the approriate icon.
1520:             * @param aRow the row for which the status should be returned
1521:             * @return an Integer identifying the status
1522:             */
1523:            public Integer getRowStatus(int aRow)
1524:                    throws IndexOutOfBoundsException {
1525:                RowData row = this .getRow(aRow);
1526:                if (row.isOriginal()) {
1527:                    return ROW_ORIGINAL;
1528:                } else if (row.isNew()) {
1529:                    return ROW_NEW;
1530:                } else if (row.isModified()) {
1531:                    return ROW_MODIFIED;
1532:                } else {
1533:                    return ROW_ORIGINAL;
1534:                }
1535:            }
1536:
1537:            /**
1538:             * Returns a list with the value of all PK columns for the given
1539:             * row. The key to the map is the name of the column.
1540:             *
1541:             * @see workbench.storage.ResultInfo#isPkColumn(int)
1542:             * @see #getValue(int, int)
1543:             */
1544:            public List<ColumnData> getPkValues(int aRow) {
1545:                return getPkValues(aRow, false);
1546:            }
1547:
1548:            /**
1549:             * Returns a list with the value of all PK columns for the given
1550:             * row. The key to the map is the name of the column.
1551:             *
1552:             * @see workbench.storage.ResultInfo#isPkColumn(int)
1553:             * @see #getValue(int, int)
1554:             */
1555:            public List<ColumnData> getPkValues(int aRow, boolean originalValues) {
1556:                if (this .originalConnection == null)
1557:                    return Collections.emptyList();
1558:
1559:                try {
1560:                    this .updatePkInformation(this .originalConnection);
1561:                } catch (SQLException e) {
1562:                    return Collections.emptyList();
1563:                }
1564:
1565:                if (!this .resultInfo.hasPkColumns())
1566:                    return Collections.emptyList();
1567:
1568:                RowData rowdata = this .getRow(aRow);
1569:                if (rowdata == null)
1570:                    return Collections.emptyList();
1571:
1572:                int count = this .resultInfo.getColumnCount();
1573:                List<ColumnData> result = new LinkedList<ColumnData>();
1574:                for (int j = 0; j < count; j++) {
1575:                    if (this .resultInfo.isPkColumn(j)) {
1576:                        ColumnIdentifier col = this .resultInfo.getColumn(j);
1577:                        Object value = (originalValues ? rowdata
1578:                                .getOriginalValue(j) : rowdata.getValue(j));
1579:                        result.add(new ColumnData(value, col));
1580:                    }
1581:                }
1582:                return result;
1583:            }
1584:
1585:            private int currentUpdateRow = 0;
1586:            private int currentInsertRow = 0;
1587:            private int currentDeleteRow = 0;
1588:
1589:            protected void resetUpdateRowCounters() {
1590:                currentUpdateRow = 0;
1591:                currentInsertRow = 0;
1592:                currentDeleteRow = 0;
1593:            }
1594:
1595:            private RowData getNextChangedRow() {
1596:                if (this .currentUpdateRow >= this .getRowCount())
1597:                    return null;
1598:                RowData row = null;
1599:
1600:                int count = this .getRowCount();
1601:
1602:                while (this .currentUpdateRow < count) {
1603:                    row = this .getRow(this .currentUpdateRow);
1604:                    this .currentUpdateRow++;
1605:
1606:                    if (row.isModified() && !row.isNew())
1607:                        return row;
1608:                }
1609:                return null;
1610:            }
1611:
1612:            protected RowData getNextDeletedRow() {
1613:                if (this .deletedRows == null || this .deletedRows.size() == 0)
1614:                    return null;
1615:                int count = this .deletedRows.size();
1616:
1617:                if (this .currentDeleteRow > count)
1618:                    return null;
1619:
1620:                RowData row = null;
1621:
1622:                while (this .currentDeleteRow < count) {
1623:                    row = this .deletedRows.get(this .currentDeleteRow);
1624:                    this .currentDeleteRow++;
1625:                    return row;
1626:                }
1627:                return null;
1628:            }
1629:
1630:            private RowData getNextInsertedRow() {
1631:                int count = this .getRowCount();
1632:                if (this .currentInsertRow >= count)
1633:                    return null;
1634:
1635:                RowData row = null;
1636:
1637:                while (this .currentInsertRow < count) {
1638:                    row = this .getRow(this .currentInsertRow);
1639:                    this .currentInsertRow++;
1640:                    if (row.isNew() && row.isModified()) {
1641:                        return row;
1642:                    }
1643:                }
1644:                return null;
1645:            }
1646:
1647:            public void setPKColumns(ColumnIdentifier[] pkColumns) {
1648:                this .resultInfo.setPKColumns(pkColumns);
1649:                this .missingPkcolumns = null;
1650:            }
1651:
1652:            public ResultInfo getResultInfo() {
1653:                return this .resultInfo;
1654:            }
1655:
1656:            public ColumnIdentifier[] getColumns() {
1657:                return this .resultInfo.getColumns();
1658:            }
1659:
1660:            public boolean hasPkColumns() {
1661:                return this .resultInfo.hasPkColumns();
1662:            }
1663:
1664:            /**
1665:             *	Check if the currently defined updateTable
1666:             *	has any Primary keys defined in the database.
1667:             *
1668:             *	If it has, a subsequent call to hasPkColumns() returns true
1669:             */
1670:            public void updatePkInformation() throws SQLException {
1671:                updatePkInformation(this .originalConnection);
1672:            }
1673:
1674:            private void updatePkInformation(WbConnection aConnection)
1675:                    throws SQLException {
1676:                if (this .resultInfo.hasPkColumns())
1677:                    return;
1678:                if (this .updateTable == null) {
1679:                    this .checkUpdateTable();
1680:                }
1681:
1682:                if (this .updateTable == null) {
1683:                    LogMgr
1684:                            .logDebug("Datastore.updatePkInformation()",
1685:                                    "No update table found, PK information not available");
1686:                }
1687:
1688:                // If we have found a single update table, but no Primary Keys
1689:                // we try to find a user-defined PK mapping.
1690:                // there is no need to call readPkDefinition() as that
1691:                // will only try to find the PK columns of the update table
1692:                // first, which we have already tried in checkUpdateTable()
1693:                if (this .updateTable != null && !this .hasPkColumns()) {
1694:                    LogMgr
1695:                            .logInfo("Datastore.updatePkInformation()",
1696:                                    "Trying to retrieve PK information retrieved from pk mapping");
1697:                    this .resultInfo.readPkColumnsFromMapping(aConnection);
1698:                }
1699:            }
1700:
1701:            /** Getter for property progressMonitor.
1702:             * @return Value of property progressMonitor.
1703:             *
1704:             */
1705:            public RowActionMonitor getProgressMonitor() {
1706:                return this .rowActionMonitor;
1707:            }
1708:
1709:            /** Setter for property progressMonitor.
1710:             * @param aMonitor New value of property progressMonitor.
1711:             *
1712:             */
1713:            public void setProgressMonitor(RowActionMonitor aMonitor) {
1714:                this.rowActionMonitor = aMonitor;
1715:            }
1716:
1717:        }
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.