You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by John English <jo...@gmail.com> on 2012/02/07 11:28:00 UTC
Views dropped automatically when altering tables?
Hi all,
When altering a table the other day I discovered that any views that
reference the table get dropped automatically (and silently), as do
any views that depend on those views, and so on. This came as a nasty
surprise to me when I tried to access one of those views!
If the table has associated triggers I get an error when I try to alter
it, so I know which triggers are involved and I can then drop them and
re-create them after the alteration. Shouldn't the same be true for
views to avoid surprises? Or does the standard mandate this bizarre
behaviour somewhere?
I'd be glad to hear any informed opinions on this...
------------------------------------------------------------------------
John English | My old University of Brighton home page is still here:
| http://www.cem.brighton.ac.uk/staff/je/
------------------------------------------------------------------------
Re: Views dropped automatically when altering tables?
Posted by John English <jo...@gmail.com>.
On 07/02/2012 16:43, Rick Hillegas wrote:
> Hi John,
>
> This is one of many areas where Derby's dependency tracking could be
> improved. I think that Derby is over-aggressive in dropping views when
> you drop a column in the underlying table.As I read the SQL Standard
> (part 2, section 11.19 <drop column definition>), a RESTRICTed column
> drop does not need to affect views which don't reference the column.
My copy of SQL-92 has this as section 11.15, and according to General
Rules (2) in this section:
"Let VN be the name of any view that contains a reference to
column C of table T. The following <drop view statement> is
effectively executed with a current <authorization identifier>
of "_SYSTEM" and without further Access Rule checking:
DROP VIEW VN CASCADE".
This seems a bit peculiar to me, as silent unasked-for behaviour
generally leads to trouble in my experience. But it seems that this
is indeed mandated by the standard... The over-aggressive behaviour
of Derby just means you're even less sure than usual what gets hosed
automatically when you drop a column!
Thanks for pointing me to the appropriate section of a document I find
rather dense at times... :-)
------------------------------------------------------------------------
John English | My old University of Brighton home page is still here:
| http://www.cem.brighton.ac.uk/staff/je/
------------------------------------------------------------------------
Re: Views dropped automatically when altering tables?
Posted by Rick Hillegas <ri...@oracle.com>.
Hi John,
This is one of many areas where Derby's dependency tracking could be
improved. I think that Derby is over-aggressive in dropping views when
you drop a column in the underlying table. As I read the SQL Standard
(part 2, section 11.19 <drop column definition>), a RESTRICTed column
drop does not need to affect views which don't reference the column.
However, in this situation, Derby sees all views on the table as
blocking dependencies. The following script shows this:
connect 'jdbc:derby:memory:db;create=true';
create table t( a varchar( 10 ), b varchar( 10 ) );
create view v1 as select a from t;
select * from v1;
-- column alteration is ok, the view is not dropped
alter table t alter column a set data type varchar( 20 );
select * from v1;
-- adding a column is ok, the view is not dropped
alter table t add column c int;
select * from v1;
-- dropping a column behaves over-aggressively. the view does not need
to prevent this action.
alter table t drop column b restrict;
select * from v1;
-- similarly, the view does not need to be dropped here. derby does
issue a warning that v1 is dropped.
alter table t drop column b;
select * from v1;
Thanks,
-Rick
On 2/7/12 2:28 AM, John English wrote:
> Hi all,
> When altering a table the other day I discovered that any views that
> reference the table get dropped automatically (and silently), as do
> any views that depend on those views, and so on. This came as a nasty
> surprise to me when I tried to access one of those views!
>
> If the table has associated triggers I get an error when I try to alter
> it, so I know which triggers are involved and I can then drop them and
> re-create them after the alteration. Shouldn't the same be true for
> views to avoid surprises? Or does the standard mandate this bizarre
> behaviour somewhere?
>
> I'd be glad to hear any informed opinions on this...
>
> ------------------------------------------------------------------------
> John English | My old University of Brighton home page is still here:
> | http://www.cem.brighton.ac.uk/staff/je/
> ------------------------------------------------------------------------
>
RE: About db file sizing
Posted by Carlos de Luna Saenz <CL...@mail.scjn.gob.mx>.
Great article the one about derby security... just what I needed to
know, thanks.
-----Mensaje original-----
De: Rick Hillegas [mailto:rick.hillegas@oracle.com]
Enviado el: jueves, 09 de febrero de 2012 07:47 a.m.
Para: derby-user@db.apache.org
Asunto: Re: About db file sizing
Hi Carlos,
Some responses inline...
On 2/8/12 3:00 PM, Carlos de Luna Saenz wrote:
> I really don't know if this is a good couple questions or not...
> We are developing an embedded DB app that has a 1.74GB of files for
the
> database
> I wonder if there is something like a shrink or compact command that
> will reduce the amount of space for the app DB.
The SYSCS_UTIL.SYSCS_COMPRESS_TABLE and
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedures can be used to
reclaim unused space. Don't know how effective they will be for your
needs though. See
http://db.apache.org/derby/docs/10.8/ref/ref-single.html#rrefaltertablec
ompress
> Is there a way to make it user/password protected? We don't want to
> "start" a server on the program side but password protection would be
> appreciated.
Derby currently supports 3 user/password authentication schemes,
discussed here:
http://db.apache.org/derby/docs/10.8/devguide/devguide-single.html#cdevc
secure42374
. In the next major feature release (10.9), we hope to introduce another
authentication scheme which should be easier to configure and
administer; it is described by the functional spec attached to
https://issues.apache.org/jira/browse/DERBY-866
Hope this helps,
-Rick
> Greetings
>
Re: About db file sizing
Posted by Rick Hillegas <ri...@oracle.com>.
Hi Carlos,
Some responses inline...
On 2/8/12 3:00 PM, Carlos de Luna Saenz wrote:
> I really don't know if this is a good couple questions or not...
> We are developing an embedded DB app that has a 1.74GB of files for the
> database
> I wonder if there is something like a shrink or compact command that
> will reduce the amount of space for the app DB.
The SYSCS_UTIL.SYSCS_COMPRESS_TABLE and
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedures can be used to
reclaim unused space. Don't know how effective they will be for your
needs though. See
http://db.apache.org/derby/docs/10.8/ref/ref-single.html#rrefaltertablecompress
> Is there a way to make it user/password protected? We don't want to
> "start" a server on the program side but password protection would be
> appreciated.
Derby currently supports 3 user/password authentication schemes,
discussed here:
http://db.apache.org/derby/docs/10.8/devguide/devguide-single.html#cdevcsecure42374
. In the next major feature release (10.9), we hope to introduce another
authentication scheme which should be easier to configure and
administer; it is described by the functional spec attached to
https://issues.apache.org/jira/browse/DERBY-866
Hope this helps,
-Rick
> Greetings
>
About db file sizing
Posted by Carlos de Luna Saenz <CL...@mail.scjn.gob.mx>.
I really don't know if this is a good couple questions or not...
We are developing an embedded DB app that has a 1.74GB of files for the
database
I wonder if there is something like a shrink or compact command that
will reduce the amount of space for the app DB.
Is there a way to make it user/password protected? We don't want to
"start" a server on the program side but password protection would be
appreciated.
Greetings
Re: Views dropped automatically when altering tables?
Posted by John English <jo...@gmail.com>.
On 07/02/2012 18:38, Dag H. Wanvik wrote:
> At least for the DROP COLUMN feature, the syntax looks like this:
>
> DROP [ COLUMN ] column-name [ CASCADE | RESTRICT ]
OK, thanks. I'm over my surprise now and have read and understood, so I
won't be taken unawares again in future!
------------------------------------------------------------------------
John English | My old University of Brighton home page is still here:
| http://www.cem.brighton.ac.uk/staff/je/
------------------------------------------------------------------------
Re: Views dropped automatically when altering tables?
Posted by "Dag H. Wanvik" <da...@oracle.com>.
John English <jo...@gmail.com> writes:
> Hi all,
> When altering a table the other day I discovered that any views that
> reference the table get dropped automatically (and silently), as do
> any views that depend on those views, and so on. This came as a nasty
> surprise to me when I tried to access one of those views!
>
> If the table has associated triggers I get an error when I try to alter
> it, so I know which triggers are involved and I can then drop them and
> re-create them after the alteration. Shouldn't the same be true for
> views to avoid surprises? Or does the standard mandate this bizarre
> behaviour somewhere?
How did you alter the table?
At least for the DROP COLUMN feature, the syntax looks like this:
DROP [ COLUMN ] column-name [ CASCADE | RESTRICT ]
The CASCADE gives the behavior you see, the RESTRICT should balk.
In the documentation, we see that CASCADE is the default:
http://db.apache.org/derby/docs/10.8/ref/rrefsqlj81859.html
"The keywords CASCADE and RESTRICT are also optional. If you specify
neither CASCADE nor RESTRICT, the default is CASCADE."
The SQL standard makes the <drop behavior> specification mandatory (no
default), see ISO/IEC 9075-2:2003 (E) section 11.18 <drop column
definition>.
Thanks,
Dag
>
> I'd be glad to hear any informed opinions on this...
>
> ------------------------------------------------------------------------
> John English | My old University of Brighton home page is still here:
> | http://www.cem.brighton.ac.uk/staff/je/
> ------------------------------------------------------------------------