gbak fails to restore database containing dependency between views and packaged
functions
-----------------------------------------------------------------------------------------
Key: CORE-4470
URL: http://tracker.firebirdsql.org/browse/CORE-4470
Project: Firebird Core
Issue Type: Bug
Components: GBAK
Affects Versions: 3.0 Alpha 2
Reporter: Frank Schlottmann-Goedde
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;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
Find What Matters Most in Your Big Data with HPCC Systems
Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
Leverages Graph Analysis for Fast Processing & Easy Data Exploration
http://p.sf.net/sfu/hpccsystems
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
