firebird-qa/tests/bugs/core_4470_test.py

#coding:utf-8

"""
ID:          issue-4790
ISSUE:       4790
TITLE:       gbak fails to restore database containing dependency between views and packaged functions
DESCRIPTION:
NOTES:
[28.10.2019]
  Replaced PSQL function name 'localtime()' with 'fn_local_time()': first of them became keyword in FB 4.0
JIRA:        CORE-4470
FBTEST:      bugs.core_4470
"""

import pytest
from pathlib import Path
from firebird.qa import *
from firebird.driver import SrvRestoreFlag

init_script = """
    set bail on;
    set autoddl on;

    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);
    commit;
    set autoddl off;

    set term ^;

    create package wf as
    begin
       function cest (t dtime) returns dtime;
       function cet (t dtime) returns dtime;
       function fn_local_time (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;

    create table constants (id did generated by default as identity not null,
            typ dvc255,
            val dvc255,
            keys dbigint,
    primary key (id));

    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));

    create table timezone (jahr dsmallint not null,
            gmt_from dtime,
            gmt_thru dtime,
    constraint pk_timezone primary key (jahr));

    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);

    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.fn_local_time(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;
    commit;

    set autoddl off;
    set term ^;

    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 fn_local_time (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 fur 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 fur die berucksichtigung 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;
"""

db = db_factory(init=init_script)

act = python_act('db')

expected_stdout = """
    METEO
    WF
"""

fbk_file = temp_file('test.fbk')

@pytest.mark.version('>=3.0')
def test_1(act: Action, fbk_file: Path):
    with act.connect_server() as srv:
        srv.database.backup(database=act.db.db_path, backup=fbk_file)
        srv.wait()
        srv.database.restore(backup=fbk_file, database=act.db.db_path,
                             flags=SrvRestoreFlag.REPLACE)
        srv.wait()
    act.expected_stdout = expected_stdout
    act.isql(switches=['-q'], input='show view; show package;')
    assert act.clean_stdout == act.clean_expected_stdout