Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0036908Openbravo ERPY. DBSourceManagerpublic2017-09-20 18:572017-09-26 15:25
malsasua 
AugustoMauch 
immediatemajoralways
closedopen 
5
 
3.0PR17Q4 
alostale
Core
Production - Confirmed Stable
2017-01-11
3.0PR17Q2
http://code.openbravo.com/erp/devel/pi/rev/47c768943f70d1c674cb0a2701faf29edde3da84 [^]
No
0036908: It is not possible to change the size of columns if the column is used in a view
when a column is used in a view, and the column size is changed, the update.database process returns one error:
     [java] 113277 WARN - SQL Command failed with: ERROR: cannot alter type of a column used by a view or rule
     [java] Detail: rule _RETURN on view c_invoice_v2 depends on column "poreference"
     [java] 113285 WARN - -- END
     [java] ALTER TABLE C_INVOICE ALTER COLUMN POREFERENCE TYPE VARCHAR(255)
. in xml file (src-db/database/model/tables/C_INVOICE.xml), change the size of column "POREFERENCE" to 255 in c_invoice table
. execute:
ant update.database
No tags attached.
depends on backport 00369333.0PR17Q2.4 closed AugustoMauch It is not possible to change the size of columns if the column is used in a view 
depends on backport 00369343.0PR17Q3.1 closed AugustoMauch It is not possible to change the size of columns if the column is used in a view 
caused by feature request 00346543.0PR17Q2 closed alostale some performance improvements in DBSM tasks 
Issue History
2017-09-20 18:57malsasuaNew Issue
2017-09-20 18:57malsasuaAssigned To => platform
2017-09-20 18:57malsasuaModules => Core
2017-09-20 18:57malsasuaTriggers an Emergency Pack => No
2017-09-21 09:31inigosanchezStatusnew => acknowledged
2017-09-21 09:32inigosanchezAssigned Toplatform => inigosanchez
2017-09-21 09:35AugustoMauchAssigned Toinigosanchez => AugustoMauch
2017-09-21 09:35AugustoMauchStatusacknowledged => scheduled
2017-09-21 09:48alostaleRelationship addedcaused by 0034654
2017-09-21 09:49alostaleRegression level => Production - Confirmed Stable
2017-09-21 09:49alostaleRegression date => 2017-01-11
2017-09-21 09:49alostaleRegression introduced in release => 3.0PR17Q2
2017-09-21 09:49alostaleRegression introduced by commit => http://code.openbravo.com/erp/devel/pi/rev/47c768943f70d1c674cb0a2701faf29edde3da84 [^]
2017-09-21 09:49alostalePrioritynormal => immediate
2017-09-21 09:55alostaleNote Added: 0099201
2017-09-21 09:58alostaleNote Edited: 0099201bug_revision_view_page.php?bugnote_id=0099201#r15953
2017-09-22 13:22AugustoMauchReview Assigned To => alostale
2017-09-22 13:34hgbotCheckin
2017-09-22 13:34hgbotNote Added: 0099486
2017-09-22 13:34hgbotStatusscheduled => resolved
2017-09-22 13:34hgbotResolutionopen => fixed
2017-09-22 13:34hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/dbsm-main/rev/5b41c1ceb63e9e9e7e5dfe706093d38a81ff21c2 [^]
2017-09-22 13:34hgbotCheckin
2017-09-22 13:34hgbotNote Added: 0099487
2017-09-22 13:44hgbotCheckin
2017-09-22 13:44hgbotNote Added: 0099489
2017-09-22 13:44hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/dbsm-main/rev/5b41c1ceb63e9e9e7e5dfe706093d38a81ff21c2 [^] => http://code.openbravo.com/erp/devel/pi/rev/a945b83327cb5edf2ed4d8c3faf3936128b17d14 [^]
2017-09-25 09:28hgbotCheckin
2017-09-25 09:28hgbotNote Added: 0099507
2017-09-25 09:28hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/a945b83327cb5edf2ed4d8c3faf3936128b17d14 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/5dc3382f910dc2ad74b09ceac22fb83eba6b20e8 [^]
2017-09-25 09:28hgbotCheckin
2017-09-25 09:28hgbotNote Added: 0099508
2017-09-25 09:28hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/dbsm-main/rev/5dc3382f910dc2ad74b09ceac22fb83eba6b20e8 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/7cdd957ca8790f1c9bb971e66c40b9f38a842b7d [^]
2017-09-25 09:30AugustoMauchNote Deleted: 0099507
2017-09-25 09:30AugustoMauchNote Deleted: 0099508
2017-09-25 09:39hgbotCheckin
2017-09-25 09:39hgbotNote Added: 0099509
2017-09-25 09:40hgbotCheckin
2017-09-25 09:40hgbotNote Added: 0099510
2017-09-25 09:41AugustoMauchStatusresolved => new
2017-09-25 09:41AugustoMauchResolutionfixed => open
2017-09-25 09:41AugustoMauchStatusnew => scheduled
2017-09-25 09:41AugustoMauchStatusscheduled => resolved
2017-09-25 09:46hgbotCheckin
2017-09-25 09:46hgbotNote Added: 0099511
2017-09-25 12:36alostaleNote Added: 0099532
2017-09-25 12:36alostaleStatusresolved => closed
2017-09-25 12:36alostaleFixed in Version => 3.0PR17Q4
2017-09-25 14:30hudsonbotCheckin
2017-09-25 14:30hudsonbotNote Added: 0099552
2017-09-26 15:25hudsonbotCheckin
2017-09-26 15:25hudsonbotNote Added: 0099586

Notes
(0099201)
alostale   
2017-09-21 09:55   
(edited on: 2017-09-21 09:58)
Even views are dropped before any table modification, c_invoice_v2 fails to be dropped because it has some other views depending on it, then c_invoice table is tried to be modified but it can't be because of that view. Although failed dropping views are retried to be executed and finally it is performed, table alteration is not.

Proposal: split current script in 2 parts
1. drop views
2. the rest

in this manner 1st part is known to be executed before 2nd one starts.

The regression occurs because previous to 17q2, any column size change caused table recreation, dropping views querying them.

(0099486)
hgbot   
2017-09-22 13:34   
Repository: erp/devel/dbsm-main
Changeset: 5b41c1ceb63e9e9e7e5dfe706093d38a81ff21c2
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Fri Sep 22 12:56:42 2017 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/5b41c1ceb63e9e9e7e5dfe706093d38a81ff21c2 [^]

Fixes bug 36908:Updating the column length on table referenced by a view works

There was a problem when a the column length of a table (T) referenced by a view (V) was updated, if that view was referenced by another view (V2).

In the step to update the model, the following steps were done in single batch:
- The views are dropped (forced = true)
- The model is updated (forced = false)
- The views are recreated

What happened is that the view V1 could not be dropped because V2 referenced it (and drop on cascade was not used). Then the batch failed, and only those
commands with forced=true were recreated, so the model was not updated.

To fix this, now the views are dropped in a different batch than the one used to update the model. There is an API change, since before this change this step
was enough to do the whole update of the model:

getSqlBuilder().alterDatabase(currentModel, desiredModel, params);

And now this it is required to call and evalue the following command:

getSqlBuilder().prepareDatabaseForAlter(currentModel, desiredModel, params, changes);

This has been changed in all Openbravo occurrences, and no occurrences outside Openbravo have been found.

---
M src/org/apache/ddlutils/platform/PlatformImplBase.java
M src/org/apache/ddlutils/platform/SqlBuilder.java
M src/org/apache/ddlutils/task/WriteSchemaSqlToFileCommand.java
M src/org/openbravo/ddlutils/task/AlterXML2SQL.java
---
(0099487)
hgbot   
2017-09-22 13:34   
Repository: erp/devel/dbsm-main
Changeset: 4ff10f67a0ca7cdce6151dcfa55478100a95aad1
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Fri Sep 22 12:58:47 2017 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/4ff10f67a0ca7cdce6151dcfa55478100a95aad1 [^]

Related with issue 36908: Adds test

The test does the following:

A table T with a column with size 20 is created, along with three views with the following dependences:
- V1 -> T
- V2 -> V1
- V3 -> V2

Then the size of the column is updated from 20 to 40. Before issue 36908 was fixed the column size was not updated, and now it works properly.

Also, DbsmTest has been updated to ensure that continueOnError is true when alterTablesPostScript is invoked. This is needed because at that point the views are
recreated, and that step might need retries if some views depend on others.

---
M src-test/src/org/openbravo/dbsm/test/base/DbsmTest.java
A src-test/model/columnSizeChangesWithDependentViews/TEST.xml
A src-test/model/columnSizeChangesWithDependentViews/TEST2.xml
A src-test/src/org/openbravo/dbsm/test/model/ColumnSizeChangesWithDependentViews.java
---
(0099489)
hgbot   
2017-09-22 13:44   
Repository: erp/devel/pi
Changeset: a945b83327cb5edf2ed4d8c3faf3936128b17d14
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Fri Sep 22 13:24:11 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/a945b83327cb5edf2ed4d8c3faf3936128b17d14 [^]

Fixes bug 36908:Updating the column length on table referenced by a view works

There was a problem when a the column length of a table (T) referenced by a view (V) was updated, if that view was referenced by another view (V2).

In the step to update the model, the following steps were done in single batch:
- The views are dropped (forced = true)
- The model is updated (forced = false)
- The views are recreated

What happened is that the view V1 could not be dropped because V2 referenced it (and drop on cascade was not used). Then the batch failed, and only those
commands with forced=true were recreated, so the model was not updated.

To fix this, now the views are dropped in a different batch than the one used to update the model. There is an API change, since before this change this step
was enough to do the whole update of the model:

getSqlBuilder().alterDatabase(currentModel, desiredModel, params);

And now this it is required to call and evalue the following command:

getSqlBuilder().prepareDatabaseForAlter(currentModel, desiredModel, params, changes);

This has been changed in all Openbravo occurrences, and no occurrences outside Openbravo have been found.

---
M src-db/database/lib/dbsourcemanager.jar
---
(0099509)
hgbot   
2017-09-25 09:39   
Repository: erp/devel/dbsm-main
Changeset: 384498865151451324efff41cefcbfe8a276e1e4
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Mon Sep 25 09:16:28 2017 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/384498865151451324efff41cefcbfe8a276e1e4 [^]

Related with issue 36908: Applies code review to tests

- The test class ColumnSizeChangesWithDependentViews is now included in the ModelSuite
- updateDatabaseShouldApplyScaleChanges method -> has been renamed to updateDatabaseShouldApplyColumnSizeChanges

---
M src-test/src/org/openbravo/dbsm/test/model/ColumnSizeChangesWithDependentViews.java
M src-test/src/org/openbravo/dbsm/test/model/ModelSuite.java
---
(0099510)
hgbot   
2017-09-25 09:40   
Repository: erp/devel/dbsm-main
Changeset: 0d62eb28bb37e3d9867295344b61917d107cb547
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Mon Sep 25 09:21:11 2017 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/0d62eb28bb37e3d9867295344b61917d107cb547 [^]

Related with issue 36908: Code review changes

The following changes have been done:
- PlatformImplBase.doAlterTables has been refactored to remove duplication
- PlatformImplBase.doAlterTables and PlatformImplBase.prepareDatabaseForAlter are now private
- Adds missing Javadoc
- Uses foreach on db.getViews()
- Uses diamond operator

---
M src/org/apache/ddlutils/platform/PlatformImplBase.java
M src/org/apache/ddlutils/platform/SqlBuilder.java
---
(0099511)
hgbot   
2017-09-25 09:46   
Repository: erp/devel/pi
Changeset: 096ea1af55a7e5e9ca5954b27a091260c9a10679
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Mon Sep 25 09:27:56 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/096ea1af55a7e5e9ca5954b27a091260c9a10679 [^]

Related with issue 36908: Code review changes

The following changes have been done:
- PlatformImplBase.doAlterTables has been refactored to remove duplication
- PlatformImplBase.doAlterTables and PlatformImplBase.prepareDatabaseForAlter are now private
- Adds missing Javadoc
- Uses foreach on db.getViews()
- Uses diamond operator

---
M src-db/database/lib/dbsourcemanager.jar
---
(0099532)
alostale   
2017-09-25 12:36   
reviewed + tested
(0099552)
hudsonbot   
2017-09-25 14:30   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/d8fd6d1ecc14 [^]
Maturity status: Test
(0099586)
hudsonbot   
2017-09-26 15:25   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/8fa643b4437e [^]
Maturity status: Test