SET NAMES UTF8;

SET SQL DIALECT 3;

CONNECT ***;

SET AUTODDL ON;

/******************************************************************************/
/****                      Creating stored procedures                      ****/
/******************************************************************************/
SET TERM ^ ;

CREATE PROCEDURE SP(
  ID INTEGER)
RETURNS (
  F1 INTEGER,
  F2 BIGINT,
  F3 VARCHAR(50) /* COLLATE NONE - default */)
AS
BEGIN
  SUSPEND;
END
^


/******************************************************************************/
/****              Creating tables (without computed fields)               ****/
/******************************************************************************/
SET TERM ; ^

CREATE TABLE TBL (
    ID INTEGER,
    BG BIGINT,
    NM NUMERIC(15,2),
    VC VARCHAR(100) /* COLLATE NONE - default */);


/******************************************************************************/
/****                            Creating views                            ****/
/******************************************************************************/
CREATE VIEW V1 (
    ID,
    BG,
    NM,
    VC,
    F1,
    F2,
    F3)
AS
select t.id, t.bg, t.nm, t.vc, s.f1,  s.f2, s.f3
from tbl t left join sp(t.id) s on 1=1;


/******************************************************************************/
/****                      Altering stored procedures                      ****/
/******************************************************************************/
SET TERM ^ ;

ALTER PROCEDURE SP(
  ID INTEGER)
RETURNS (
  F1 INTEGER,
  F2 BIGINT,
  F3 VARCHAR(50) /* COLLATE NONE - default */)
AS
begin
  f1 = 1;
  f2 = 100500100500;
  f3 = 'blah-blah-blah';
  suspend;
end
^


/******************************************************************************/
/****                     Granting missing privileges                      ****/
/******************************************************************************/
SET TERM ; ^

GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON V1 TO SYSDBA WITH GRANT OPTION;

GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TBL TO SYSDBA WITH GRANT OPTION;

GRANT EXECUTE ON PROCEDURE SP TO SYSDBA WITH GRANT OPTION;

GRANT SELECT ON TBL TO VIEW V1;

GRANT EXECUTE ON PROCEDURE SP TO VIEW V1;

SET NAMES UTF8;

SET SQL DIALECT 3;

CONNECT ****;

SET AUTODDL ON;

/******************************************************************************/
/****                 Deactivating dependencies of objects                 ****/
/******************************************************************************/
DROP VIEW V1;


/******************************************************************************/
/****                            Creating views                            ****/
/******************************************************************************/
CREATE VIEW V1 (
    ID,
    BG,
    NM,
    VC,
    F1,
    F2,
    F3,
    F4)
AS
select t.id, t.bg, t.nm, t.vc, s.f1,  s.f2, s.f3, s.f4
from tbl t left join sp(t.id) s on 1=1;


/******************************************************************************/
/****            Altering stored procedures (with empty bodies)            ****/
/******************************************************************************/
SET TERM ^ ;

ALTER PROCEDURE SP(
  ID INTEGER)
RETURNS (
  F1 INTEGER,
  F2 BIGINT,
  F3 VARCHAR(50) /* COLLATE NONE - default */,
  F4 NUMERIC(15,2))
AS
BEGIN
  SUSPEND;
END
^


/******************************************************************************/
/****                      Altering stored procedures                      ****/
/******************************************************************************/
ALTER PROCEDURE SP(
  ID INTEGER)
RETURNS (
  F1 INTEGER,
  F2 BIGINT,
  F3 VARCHAR(50) /* COLLATE NONE - default */,
  F4 NUMERIC(15,2))
AS
begin
  f1 = 1;
  f2 = 100500100500;
  f3 = 'blah-blah-blah';
  f4 = -1;
  suspend;
end
^


/******************************************************************************/
/****                     Granting missing privileges                      ****/
/******************************************************************************/
SET TERM ; ^

GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON V1 TO SYSDBA WITH GRANT OPTION;

GRANT SELECT ON TBL TO VIEW V1;

GRANT EXECUTE ON PROCEDURE SP TO VIEW V1;

SET NAMES UTF8;

SET SQL DIALECT 3;

CONNECT ****;

SET AUTODDL ON;

/******************************************************************************/
/****                 Deactivating dependencies of objects                 ****/
/******************************************************************************/
ALTER VIEW V1 (
    ID,
    BG,
    NM,
    VC,
    F1,
    F2,
    F3)
AS
SELECT
    CAST(NULL AS INTEGER),
    CAST(NULL AS BIGINT),
    CAST(NULL AS NUMERIC(15,2)),
    CAST(NULL AS VARCHAR(100)),
    CAST(NULL AS INTEGER),
    CAST(NULL AS BIGINT),
    CAST(NULL AS VARCHAR(50))
FROM RDB$DATABASE;


/******************************************************************************/
/****                            Creating views                            ****/
/******************************************************************************/
ALTER VIEW V1 (
    ID,
    BG,
    NM,
    VC,
    F1,
    F2,
    F3,
    F4)
AS
select t.id, t.bg, t.nm, t.vc, s.f1,  s.f2, s.f3, s.f4
from tbl t left join sp(t.id) s on 1=1;


/******************************************************************************/
/****            Altering stored procedures (with empty bodies)            ****/
/******************************************************************************/
SET TERM ^ ;

ALTER PROCEDURE SP(
  ID INTEGER)
RETURNS (
  F1 INTEGER,
  F2 BIGINT,
  F3 VARCHAR(50) /* COLLATE NONE - default */,
  F4 NUMERIC(15,2))
AS
BEGIN
  SUSPEND;
END
^


/******************************************************************************/
/****                      Altering stored procedures                      ****/
/******************************************************************************/
ALTER PROCEDURE SP(
  ID INTEGER)
RETURNS (
  F1 INTEGER,
  F2 BIGINT,
  F3 VARCHAR(50) /* COLLATE NONE - default */,
  F4 NUMERIC(15,2))
AS
begin
  f1 = 1;
  f2 = 100500100500;
  f3 = 'blah-blah-blah';
  f4 = -1;
  suspend;
end
^

SET TERM ; ^

/* Server version: LI-V6.3.3.32794 Firebird 3.0 
   SQLDialect: 3. ODS: 12.0. Forced writes: On. Sweep interval: 20000.
   Page size: 8192. Cache pages: 2048 (16384 Kb). Read-only: False. */
SET CLIENTLIB 'C:\Program Files\Firebird\Firebird_3_0\fbclient.dll';
SET NAMES UTF8;

SET SQL DIALECT 3;

CONNECT '192.168.2.29:dst1.fdb' USER 'SYSDBA' PASSWORD '******';

SET AUTODDL ON;

/******************************************************************************/
/****                 Deactivating dependencies of objects                 ****/
/******************************************************************************/
SET TERM ^ ;

ALTER PROCEDURE SP2
RETURNS (
  F5 VARCHAR(10) /* COLLATE NONE - default */)
AS
BEGIN
  SUSPEND;
END
^

SET TERM ; ^

ALTER VIEW V2 (
    ID,
    BG,
    NM,
    VC,
    F1,
    F2,
    F3,
    F4,
    ID2,
    BG2,
    NM2,
    VC2)
AS
SELECT
    CAST(NULL AS INTEGER),
    CAST(NULL AS BIGINT),
    CAST(NULL AS NUMERIC(18,2)),
    CAST(NULL AS VARCHAR(100)),
    CAST(NULL AS INTEGER),
    CAST(NULL AS BIGINT),
    CAST(NULL AS VARCHAR(50)),
    CAST(NULL AS NUMERIC(18,2)),
    CAST(NULL AS INTEGER),
    CAST(NULL AS BIGINT),
    CAST(NULL AS NUMERIC(18,2)),
    CAST(NULL AS VARCHAR(200))
FROM RDB$DATABASE;

ALTER VIEW V2 (
    ID,
    BG,
    NM,
    VC,
    F1,
    F2,
    F3,
    F4,
    ID2,
    BG2,
    NM2,
    VC2)
AS
SELECT
    CAST(NULL AS INTEGER),
    CAST(NULL AS BIGINT),
    CAST(NULL AS NUMERIC(18,2)),
    CAST(NULL AS VARCHAR(100)),
    CAST(NULL AS INTEGER),
    CAST(NULL AS BIGINT),
    CAST(NULL AS VARCHAR(50)),
    CAST(NULL AS NUMERIC(18,2)),
    CAST(NULL AS INTEGER),
    CAST(NULL AS BIGINT),
    CAST(NULL AS NUMERIC(18,2)),
    CAST(NULL AS VARCHAR(200))
FROM RDB$DATABASE;


/******************************************************************************/
/****                       Creating/altering views                        ****/
/******************************************************************************/
ALTER VIEW V1 (
    ID,
    BG,
    NM,
    VC,
    F1,
    F2,
    F3,
    F4,
    ID2,
    BG2,
    NM2,
    VC2,
    F5,
    F6)
AS
select coalesce(t.id+1, 123), t.bg, t.nm, t.vc,
       s.f1, s.f2, s.f3, s.f4,
       cast (t.id  as integer), cast(t.bg as bigint), cast(t.nm as numeric(15,2)), cast(t.vc as varchar(200)),
       coalesce(s.f5,  '1111'), s.f6
from tbl t left join sp(t.id) s on 1=1;

ALTER VIEW V2 (
    ID,
    BG,
    NM,
    VC,
    F1,
    F2,
    F3,
    F4,
    ID2,
    BG2,
    NM2,
    VC2)
AS
select
    ID,
    BG,
    NM,
    VC,
    F1,
    F2,
    F3,
    F4,
    ID2,
    BG2,
    NM2,
    VC2 from v1;


/******************************************************************************/
/****                   Restoring deactivated procedures                   ****/
/******************************************************************************/
SET TERM ^ ;

ALTER PROCEDURE SP2
RETURNS (
  F5 VARCHAR(10) /* COLLATE NONE - default */)
AS
begin
  for select f5 from v1 into :f5 do
  suspend;
end
^


SET TERM ; ^