db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Karl San Gabriel (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-4681) Dropping a column in the table drops the views that use this table
Date Thu, 25 Oct 2012 18:29:12 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4681?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13484354#comment-13484354
] 

Karl San Gabriel commented on DERBY-4681:
-----------------------------------------

Hello Rick, 

I looked into the codes and at the stack trace. Based on my investigation, I would say the
application needs to store the table fields that go with any views during CREATE VIEW.

Currently, the application keeps track of which tables a view uses or which views depend on
a particular table. One fix that I am 
inclined to do includes adding a new table field in DEPENDENTFINDER to store which table-field
a view uses during CREATE VIEW and 
using that information in processing the ALTER TABLE...DROP COLUMN...RESTRICT or CASCADE.


select * from sys.sysdepends where DEPENDENTFINDER='View';
==================================================================================================
DEPENDENTID                         |DEPENDENTFINDER|PROVIDERID                          |PROVIDERFINDER
==================================================================================================
437c8055-013a-9605-32d1-000000bf1590|View           |48d7402a-013a-9605-32d1-000000bf1590|ColumnsInTable
     
--> providerid points to T3 table in systables, no way to know which fields the view actually
uses

437c8055-013a-9605-32d1-000000bf1590|View           |f55fc0a6-013a-7ea4-0336-000000bf1430|ColumnsInTable
     
--> providerid points to T2 table in systables, no way to know which fields the view actually
uses

The other fix that I am thinking of is to reuse the codes that check if the fields and tables
are valid entities for example during a CREATE VIEW
operation.

Your thoughts? 

Thanks,
Karl
                
> Dropping a column in the table drops the views that use this table
> ------------------------------------------------------------------
>
>                 Key: DERBY-4681
>                 URL: https://issues.apache.org/jira/browse/DERBY-4681
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0,
10.5.3.0, 10.6.1.0
>         Environment: Windows 7 Enterprise, JDK SE 1.6 u20
>            Reporter: Grzegorz Łyp
>            Priority: Critical
>              Labels: derby_triage10_8
>         Attachments: DERBY-4681-LEADS.txt
>
>
> If i have a table and a view that uses this table, after dropping a column from this
table the whole view is also dropped.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message