Greetings!

I'm looking to add a stored procedure that will ALTER an existing
view. Strangely, it isn't working.. and I could swear it was before,
as I remember testing this step by step in developing it.

DROP PROCEDURE IF EXISTS bankIdent_ChangeView;
DELIMITER |
CREATE DEFINER=CURRENT_USER PROCEDURE bankIdent_ChangeView(
IN sNewView ENUM('offline','production')
)
SQL SECURITY INVOKER
MODIFIES SQL DATA
BEGIN
IF sNewView = 'offline' THEN
ALTER VIEW bank_ident_popular_v AS SELECT * FROM
bank_ident_offline_t WHERE is_popular = 'Y';
ELSEIF sNewView = 'production' THEN
ALTER VIEW bank_ident_popular_v AS SELECT * FROM bank_ident_t
WHERE is_popular = 'Y';
END IF;
END; |
DELIMITER ;

It's a simple procedure that allows me to update a view to access an
offline version of a table while weekly maintenance is performed on
the primary table.

What's strange, is that if I instead do:

DROP VIEW IF EXISTS bank_ident_popular_v;
CREATE VIEW bank_ident_popular_v AS SELECT ...;

Then it works. However, I'd prefer not to leave any "gaps". This is
based around an engine type using MyISAM, so transactions aren't an
option. Is anyone aware of a tweak to allow the ALTER to be performed?

Alternatively, is anyone very familiar with the internal locking
mechanisms with the calls? For example, when this maintenance routine
is called.. when this procedure drops the existing view, is there an
opportunity for other calls to fail on their selection since the view
doesn't exist? My understanding is that there is an opportunity for
this, and that is what I'm trying to avoid. Although the routine
shouldn't take long to execute, and certain the view should only take
at most a few milliseconds to create, I'd prefer to minimize as many
functional gaps as possible.

Thanks!
Michael

On Sat, 29 Dec 2007 03:16:49 -0800 (PST), Michael Martinek
<michael.martinek@gmail.com> wrote:

>Greetings!
>
>I'm looking to add a stored procedure that will ALTER an existing
>view. Strangely, it isn't working.. and I could swear it was before,
>as I remember testing this step by step in developing it.
>
>DROP PROCEDURE IF EXISTS bankIdent_ChangeView;
>DELIMITER |
>CREATE DEFINER=CURRENT_USER PROCEDURE bankIdent_ChangeView(
> IN sNewView ENUM('offline','production')
>)
>SQL SECURITY INVOKER
>MODIFIES SQL DATA
>BEGIN
> IF sNewView = 'offline' THEN
> ALTER VIEW bank_ident_popular_v AS SELECT * FROM
>bank_ident_offline_t WHERE is_popular = 'Y';
> ELSEIF sNewView = 'production' THEN
> ALTER VIEW bank_ident_popular_v AS SELECT * FROM bank_ident_t
>WHERE is_popular = 'Y';
> END IF;
>END; |
>DELIMITER ;
>
>It's a simple procedure that allows me to update a view to access an
>offline version of a table while weekly maintenance is performed on
>the primary table.
>
>What's strange, is that if I instead do:
>
>DROP VIEW IF EXISTS bank_ident_popular_v;
>CREATE VIEW bank_ident_popular_v AS SELECT ...;
>
>Then it works. However, I'd prefer not to leave any "gaps". This is
>based around an engine type using MyISAM, so transactions aren't an
>option. Is anyone aware of a tweak to allow the ALTER to be performed?
>
>Alternatively, is anyone very familiar with the internal locking
>mechanisms with the calls? For example, when this maintenance routine
>is called.. when this procedure drops the existing view, is there an
>opportunity for other calls to fail on their selection since the view
>doesn't exist? My understanding is that there is an opportunity for
>this, and that is what I'm trying to avoid. Although the routine
>shouldn't take long to execute, and certain the view should only take
>at most a few milliseconds to create, I'd prefer to minimize as many
>functional gaps as possible.

Perhaps
"CREATE OR REPLACE VIEW"
is a better strategy. You could try it.

From the manual:
This statement creates a new view, or replaces an existing
one if the OR REPLACE clause is given. If the view does
not exist, CREATE OR REPLACE VIEW is the same as CREATE
VIEW. If the view does exist, CREATE OR REPLACE VIEW is
the same as ALTER VIEW.

>Thanks!
>Michael

HTH
--
( Kees
)
c[_] It is difficult to say what is impossible, for the dream of yesterday
is the hope of today and the reality of tomorrow. (Robert Goddard) (#39)

> From the manual:
> This statement creates a new view, or replaces an existing
> one if the OR REPLACE clause is given. If the view does
> not exist, CREATE OR REPLACE VIEW is the same as CREATE
> VIEW. If the view does exist, CREATE OR REPLACE VIEW is
> the same as ALTER VIEW.
> ( Kees

Nice. That does the trick, actually. It's rather odd that we aren't
able to use ALTER VIEW, and instead need to just do "CREATE OR
REPLACE" which has the same effect as an ALTER VIEW. But certainly,
thanks for the ! That was a good catch.


-Michael

On Sat, 29 Dec 2007 16:38:13 -0800 (PST), Michael Martinek
<michael.martinek@gmail.com> wrote:

>> From the manual:
>> This statement creates a new view, or replaces an existing
>> one if the OR REPLACE clause is given. If the view does
>> not exist, CREATE OR REPLACE VIEW is the same as CREATE
>> VIEW. If the view does exist, CREATE OR REPLACE VIEW is
>> the same as ALTER VIEW.
>> ( Kees
>
>Nice. That does the trick, actually. It's rather odd that we aren't
>able to use ALTER VIEW, and instead need to just do "CREATE OR
>REPLACE" which has the same effect as an ALTER VIEW.

Yes, it is peculiar, and I wasn't very certain it would
work. Thanks for testing and feedback.

>But certainly,
>thanks for the ! That was a good catch.

I'm glad it worked.

>-Michael
--
( Kees
)
c[_] To retain respect for sausages and laws, one must
not watch them in the making. (Otto von Bismarck) (#390)