Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

gbak fails to restore database containing dependency between views and packaged functions [CORE4470] #4790

Closed
firebird-automations opened this issue Jun 21, 2014 · 3 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Frank Schlottmann-Goedde (fsg)

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;

Commits: c42c2b8 FirebirdSQL/fbt-repository@67722df

====== 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

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 1 [ 10332 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants