search

FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.24k stars 215 forks source link

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

Closed firebird-automations closed 9 years ago

firebird-automations commented 10 years ago

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: FirebirdSQL/firebird@c42c2b833f70786cfb0e6a55a4dd117ccadeab72 FirebirdSQL/fbt-repository@67722df5db17850ee62bf3cec0e798b90219338b

====== 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 commented 10 years ago
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 commented 9 years ago
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 commented 9 years ago
Modified by: @pavel-zotov status: Resolved \[ 5 \] =\> Closed \[ 6 \]