------------------------------------------------------------------------------
--          Copyright: (C) Copyright David Goodwin, 2014
--            License: GNU General Public License
------------------------------------------------------------------------------
--
--   This is free software; you can redistribute it and/or modify
--   it under the terms of the GNU General Public License as published by
--   the Free Software Foundation; either version 2 of the License, or
--   (at your option) any later version.
--
--   This software is distributed in the hope that it will be useful,
--   but WITHOUT ANY WARRANTY; without even the implied warranty of
--   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--   GNU General Public License for more details.
--
--   You should have received a copy of the GNU General Public License
--   along with this software; if not, write to the Free Software
--   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
--
------------------------------------------------------------------------------

-----------------------------------------------------------------------------
-- TempView v1 schema for PostgreSQL ----------------------------------------
-----------------------------------------------------------------------------
--
-- Create a new blank database and run this script against it to create the
-- required database structure.
--

begin;

------------------
-- sample table
create table sample (
  sample_id serial not null,
  time_stamp timestamp with time zone not null,
  temperature real
);

comment on table sample is 'Archived readings';
comment on column sample.sample_id is 'Primary key';
comment on column sample.time_stamp is 'Time the sample was taken';
comment on column sample.temperature is 'Temperature reading';

------------------
-- db_info table
create table db_info
(
  k character varying primary key not null, -- Data key
  v character varying -- Data value
);

comment on table db_info is 'Database metadata';
comment on column db_info.k is 'Key';
comment on column db_info.v is 'Value';

-- This is schema revision 1
insert into db_info(k,v) VALUES('DB_VERSION','1');

-- And it its not compatible with anything older than TempView 1.0.0
insert into db_info(k,v) VALUES('MIN_VER_MAJ', '1');
insert into db_info(k,v) VALUES('MIN_VER_MIN', '0');
insert into db_info(k,v) VALUES('MIN_VER_REV', '0');

-- This is a tempview database
insert into db_info(k,v) values('TEMPVIEW', 'true');

------------------
-- Functions

-- Checks to see if the application is too old for this database
CREATE OR REPLACE FUNCTION version_check(application character varying, major integer, minor integer, revision integer)
  RETURNS boolean AS $BODY$
DECLARE
  app_key_maj character varying;
  app_key_min character varying;
  app_key_rev character varying;
  key_count integer;

  v_maj integer;
  v_min integer;
  v_rev integer;

  compatible boolean := FALSE;
BEGIN
  -- We'll check for application-specific minimum versions first.
  app_key_maj := upper(application) || '_MIN_VER_MAJ';
  app_key_min := upper(application) || '_MIN_VER_MIN';
  app_key_rev := upper(application) || '_MIN_VER_REV';

  select count(*) into key_count
  from db_info
  where k = app_key_maj or k = app_key_min or k = app_key_rev;

  IF (key_count = 3) THEN
    -- Looks like all three app keys are there. We'll use its versions
    select v::integer into v_maj from db_info where k = app_key_maj;
    select v::integer into v_min from db_info where k = app_key_min;
    select v::integer into v_rev from db_info where k = app_key_rev;
  ELSE
    -- No valid application-specific minimum version. Use the global minimum
    -- instead.
    select v::integer into v_maj from db_info where k = 'MIN_VER_MAJ';
    select v::integer into v_min from db_info where k = 'MIN_VER_MIN';
    select v::integer into v_rev from db_info where k = 'MIN_VER_REV';
  END IF;

  -- We've got the minimum versions. Now lets see if the supplied version
  -- number is newer.

  IF (v_maj < major) THEN
    -- Minimum version is an older major release. We're OK.
    compatible := TRUE;
  ELSEIF (v_maj = major) THEN
    -- Major versions match. Need to check the minor version.
    IF (v_min < minor) THEN
      -- Minimum version is an older release. We're OK.
      compatible := TRUE;
    ELSEIF (v_min = minor) THEN
      -- Major and minor versions match. The revision number will decide if
      -- we're compatible.
      if (v_rev <= revision) THEN
        compatible := TRUE;
      END IF;
    END IF;
  END IF;

  return compatible;
END;
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION version_check(character varying, integer, integer, integer)
IS 'Checks to see if a particular version of an application is too old for this database.';

-- Gets the minimum version of the application needed for this database
CREATE OR REPLACE FUNCTION minimum_version_string(application character varying)
  RETURNS varchar AS $BODY$
DECLARE
  key_maj character varying;
  key_min character varying;
  key_rev character varying;
  key_count integer;

  version_string character varying;
BEGIN
  -- We'll check for application-specific minimum versions first.
  key_maj := upper(application) || '_MIN_VER_MAJ';
  key_min := upper(application) || '_MIN_VER_MIN';
  key_rev := upper(application) || '_MIN_VER_REV';

  select count(*) into key_count
  from db_info
  where k = key_maj or k = key_min or k = key_rev;

  IF (key_count <> 3) THEN
    key_maj := 'MIN_VER_MAJ';
    key_min := 'MIN_VER_MIN';
    key_rev := 'MIN_VER_REV';
  END IF;

  select max(version.major) || '.' || max(version.minor) || '.' || max(version.revision) as version_string
         into version_string
  from (
  select 42 as grp,
         case when k = key_maj then v else NULL end as major,
         case when k = key_min then v else NULL end as minor,
         case when K = key_rev then v else NULL end as revision
  from db_info
  where k = key_maj or k = key_min or k = key_rev
  ) version
  group by version.grp;

  return version_string;
END;
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION minimum_version_string(character varying)
IS 'Gets minimum version of the application needed by this database.';

commit;
