Issue Details (XML | Word | Printable)

Key: CORE-4470
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Frank Schlottmann-Goedde
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

gbak fails to restore database containing dependency between views and packaged functions

Created: 21/Jun/14 02:14 PM   Updated: 14/Jun/15 06:51 AM
Component/s: GBAK
Affects Version/s: 3.0 Alpha 2
Fix Version/s: 3.0 Beta 1

QA Status: Done successfully
Test Details:
Confirmed on WI-T3.0.0.30809 Firebird 3.0 Alpha 2:
gbak: ERROR:action cancelled by trigger (0) to preserve data integrity
gbak: ERROR: could not find object for GRANT
gbak:Exiting before completion due to errors


 Description  « Hide
The following isql script creates a database that can't be restored by gbak:


SET SQL DIALECT 3;

/* CREATE DATABASE 'localhost:C:\Users\fsg\src\weather\weather.fdb' PAGE_SIZE 16384 DEFAULT CHARACTER SET ISO8859_1; */


/* Domain definitions */
CREATE DOMAIN DBIGINT AS BIGINT;
CREATE DOMAIN DBLOB AS BLOB SUB_TYPE 0 SEGMENT SIZE 80;
CREATE DOMAIN DID AS BIGINT NOT NULL;
CREATE DOMAIN DINTEGER AS INTEGER;
CREATE DOMAIN DN31 AS NUMERIC(3, 1);
CREATE DOMAIN DN41 AS NUMERIC(4, 1);
CREATE DOMAIN DN51 AS NUMERIC(5, 1);
CREATE DOMAIN DREAL AS DOUBLE PRECISION;
CREATE DOMAIN DSMALLINT AS SMALLINT;
CREATE DOMAIN DTIME AS TIMESTAMP;
CREATE DOMAIN DVC255 AS VARCHAR(255);
CREATE DOMAIN DVC512 AS VARCHAR(512);
SET AUTODDL OFF;
SET TERM ^ ;

/* Package headers */

/* Package header: WF, Owner: SYSDBA */
CREATE PACKAGE WF AS
begin
   function CEST (T dtime) returns dtime;
   function CET (T dtime) returns dtime;
   function LOCALTIME (T dtime) returns dtime;
   function DEWPOINT (TEMP dreal, HUM dsmallint) returns dreal;
   function YESTERDAY returns dtime;
   function altitude returns dreal;
   function CURRENT_XML returns dvc512;
   function relpressure(AirPressureAbsolute dreal,Temperature dreal,Altitude dreal, Humidity dreal) returns dreal;
end^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;

/* Table: CONSTANTS, Owner: SYSDBA */
CREATE TABLE CONSTANTS (ID DID GENERATED BY DEFAULT AS IDENTITY NOT NULL,
        TYP DVC255,
        VAL DVC255,
        KEYS DBIGINT,
PRIMARY KEY (ID));

/* Table: RAW, Owner: SYSDBA */
CREATE TABLE RAW (ID DID GENERATED BY DEFAULT AS IDENTITY NOT NULL,
        READTIME DTIME NOT NULL,
        DELAY DSMALLINT,
        HUM_IN DSMALLINT,
        TEMP_IN DREAL,
        HUM_OUT DSMALLINT,
        TEMP_OUT DREAL,
        ABS_PRESSURE DREAL,
        WIND_AVE DREAL,
        WIND_GUST DREAL,
        WIND_DIR DSMALLINT,
        RAIN DREAL,
        STATUS DSMALLINT,
        ILLUMINANCE DREAL,
        UV DSMALLINT,
PRIMARY KEY (ID),
CONSTRAINT UNQ1_RAW UNIQUE (READTIME));

/* Table: TIMEZONE, Owner: SYSDBA */
CREATE TABLE TIMEZONE (JAHR DSMALLINT NOT NULL,
        GMT_FROM DTIME,
        GMT_THRU DTIME,
CONSTRAINT PK_TIMEZONE PRIMARY KEY (JAHR));

/* Index definitions for all user tables */
CREATE INDEX RAW_IDX1 ON RAW (HUM_IN);
CREATE DESCENDING INDEX RAW_IDX10 ON RAW (ABS_PRESSURE);
CREATE DESCENDING INDEX RAW_IDX11 ON RAW (READTIME);
CREATE DESCENDING INDEX RAW_IDX12 ON RAW (RAIN);
CREATE INDEX RAW_IDX2 ON RAW (TEMP_IN);
CREATE INDEX RAW_IDX3 ON RAW (HUM_OUT);
CREATE INDEX RAW_IDX4 ON RAW (TEMP_OUT);
CREATE INDEX RAW_IDX5 ON RAW (ABS_PRESSURE);
CREATE INDEX RAW_IDX6 ON RAW (WIND_AVE);
CREATE INDEX RAW_IDX7 ON RAW (WIND_GUST);
CREATE INDEX RAW_IDX8 ON RAW (WIND_DIR);
CREATE INDEX RAW_IDX9 ON RAW (RAIN);

/* View: METEO, Owner: SYSDBA */
CREATE VIEW METEO (TIMESTAMP_UTC, TIMESTAMP_LOCAL, TEMPINT, HUMINT, TEMP, HUM, WIND, WIND_DIR, WIND_GUST, WIND_GUST_DIR, DEW_POINT, RAIN, RAIN_RATE, PRESSURE, UV_INDEX, SOLAR_RAD) AS
select READTIME, WF.LOCALTIME(READTIME), TEMP_IN, HUM_IN, TEMP_OUT, HUM_OUT, WIND_AVE / 3.6 , 22.5 * WIND_DIR, WIND_GUST / 3.6 ,
       22.5 * WIND_DIR, WF.DEWPOINT(TEMP_OUT, HUM_OUT), CAST(RAIN - lead(RAIN) over(order by READTIME desc) as numeric (6,3)) , 0,
       2.8+WF.RELPRESSURE(ABS_PRESSURE, TEMP_OUT, WF.ALTITUDE(), HUM_OUT), 0, 0
  from RAW;
SET AUTODDL OFF;
SET TERM ^ ;

/* Package bodies */

/* Package body: WF, Owner: SYSDBA */
CREATE PACKAGE BODY WF AS
begin

function CEST (T dtime)returns dtime
AS
begin
  return dateadd (2 hour to t);
end

function CET (T dtime)returns dtime
AS
begin
  return dateadd (1 hour to t);
end

function altitude returns dreal
as
begin
   return (select c.val from constants c where c.typ='Altitude');
end

function LOCALTIME (T dtime)returns dtime
AS
declare variable jahr dsmallint;
declare variable gmt_from dtime;
declare variable gmt_thru dtime;
begin
  select TZ.GMT_FROM, TZ.GMT_THRU
             from TIMEZONE TZ where TZ.jahr=extract(year from :T)
           into :GMT_FROM, :GMT_THRU;
  if (T between :GMt_FROM and :GMT_THRU) then
  begin
     return dateadd (2 hour to t);
  end
  else
    return dateadd (1 hour to t);
end

function RELPRESSURE (AIRPRESSUREABSOLUTE DREAL, TEMPERATURE DREAL, ALTITUDE DREAL, HUMIDITY DREAL) returns DREAL
as
declare variable G_N DREAL;
declare variable GAM DREAL;
declare variable R DREAL;
declare variable M DREAL;
declare variable R_0 DREAL;
declare variable T_0 DREAL;
declare variable C DREAL;
declare variable E_0 DREAL;
declare variable F_REL DREAL;
declare variable E_D DREAL;

begin
  G_N = 9.80665;-- Erdbeschleunigung (m/s^2)
  GAM = 0.0065;--Temperaturabnahme in K pro geopotentiellen Metern (K/gpm)
  R = 287.06;--Gaskonstante für trockene Luft (R = R_0 / M)
  M = 0.0289644;--Molare Masse trockener Luft (J/kgK)
  R_0 = 8.314472;--allgemeine Gaskonstante (J/molK)
  T_0 = 273.15;--Umrechnung von C in K
  C = 0.11;--DWD-Beiwert für die Berücksichtigung der Luftfeuchte
  E_0 = 6.11213;-- (hPa)
  F_REL = HUMIDITY / 100;--relative Luftfeuchte (0-1.0)
  E_D = F_REL * E_0 * EXP((17.5043 * TEMPERATURE) / (241.2 + TEMPERATURE));--momentaner Stationsdampfdruck (hPa)
  return AIRPRESSUREABSOLUTE * EXP((G_N * ALTITUDE) / (R * (TEMPERATURE + T_0 + C * E_D + ((GAM * ALTITUDE) / 2))));
end

function YESTERDAY returns dtime
AS
begin
  return dateadd (-1 day to current_date);
end

function DEWPOINT (TEMP dreal, HUM dsmallint)
returns dreal
AS
declare variable gamma dreal;
declare variable a dreal;
declare variable b dreal;
begin
    if ((coalesce(temp,0)=0) or (coalesce(hum,0)=0)) then
      return 0;
    else
    begin
       return TEMP - ((100 - HUM) / 5.0);
    end
end

function CURRENT_XML returns dvc512
as
declare variable timestamp_utc type of column meteo.timestamp_utc;
declare variable timestamp_local type of column meteo.timestamp_local;
declare variable tempint type of column meteo.tempint;
declare variable humint type of column meteo.humint;
declare variable temp type of column meteo.temp;
declare variable hum type of column meteo.hum;
declare variable wind type of column meteo.wind;
declare variable wind_dir type of column meteo.wind_dir;
declare variable wind_gust type of column meteo.wind_gust;
declare variable wind_gust_dir type of column meteo.wind_gust_dir;
declare variable dew_point type of column meteo.dew_point;
declare variable rain type of column meteo.rain;
declare variable rain_rate type of column meteo.rain_rate;
declare variable pressure type of column meteo.pressure;
declare variable uv_index type of column meteo.uv_index;
declare variable solar_rad type of column meteo.solar_rad;

begin

select first 1 TIMESTAMP_UTC,TIMESTAMP_LOCAL, TEMPINT, HUMINT, TEMP, HUM, WIND, WIND_DIR, WIND_GUST, WIND_GUST_DIR, DEW_POINT,
       RAIN, RAIN_RATE, PRESSURE, UV_INDEX, SOLAR_RAD
  from METEO order by timestamp_UTC desc
into :TIMESTAMP_UTC, :TIMESTAMP_LOCAL, :TEMPINT, :HUMINT, :TEMP, :HUM, :WIND, :WIND_DIR, :WIND_GUST, :WIND_GUST_DIR,
     :DEW_POINT, :RAIN, :RAIN_RATE, :PRESSURE, :UV_INDEX, :SOLAR_RAD;


 return '<current><thInt><temp>'||
 tempint||
 '</temp><humidity>'||
 humint||
 '</humidity></thInt><th1><temp>'||
 temp||
 '</temp><humidity>'||
 hum||
 '</humidity></th1><rain><rate>'||
 rain_rate||
 '</rate></rain><wind><avgSpeed>'||
 wind||
 '</avgSpeed><dirDeg>'||
 wind_dir||
 '</dirDeg><gustSpeed>'||
 wind_gust||
 '</gustSpeed></wind><barometer><pressure>'||
 pressure||
 '</pressure></barometer><time>'||
 substring (timestamp_local from 1 for 19)||
 '</time></current>';
end

end^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;


 All   Comments   Change History   Subversion Commits      Sort Order: Descending order - Click to sort in ascending order
There are no comments yet on this issue.