--#############################################################################
-- This SQL script generates the raster metadata table to store 
-- and metadata for SQL queries and temporal GIS support. Additionally two views
-- are created to access all map specific tables
--
-- Author: Soeren Gebbert soerengebbert <at> googlemail <dot> com
--#############################################################################

--PRAGMA foreign_keys = ON;

-- The metadata table reflects most of the raster metadata available in grass

CREATE TABLE  raster_metadata (
  id VARCHAR NOT NULL,               -- The id (PFK) is the unique identifier for all tables, it is based on name and mapset (name@mapset) and is used as primary foreign key
  strds_register VARCHAR,            -- The name of the table storing all space-time raster datasets in which this map is registered
  datatype VARCHAR NOT NULL,
  cols INTEGER NOT NULL,
  rows INTEGER NOT NULL,
  number_of_cells INTEGER NOT NULL,
  nsres DOUBLE PRECISION NOT NULL,
  ewres DOUBLE PRECISION NOT NULL,
  min DOUBLE PRECISION,
  max DOUBLE PRECISION,
  PRIMARY KEY (id),
  FOREIGN KEY (id) REFERENCES  raster_base (id) ON DELETE CASCADE
);

-- Create the views to access all cols for the absolute and relative time

CREATE VIEW raster_view_abs_time AS SELECT 
            A1.id, A1.mapset,
            A1.name, A1.temporal_type,
            A1.creation_time, 
-- Uncommented due to performance issues
--            A1.modification_time, A1.revision, 
            A1.creator, 
	    A2.start_time, A2.end_time, A2.timezone,
            A3.north, A3.south, A3.east, A3.west, A3.proj,
	    A4.datatype, A4.cols, A4.rows,
            A4.nsres, A4.ewres, A4.min, A4.max,
	    A4.strds_register,
            A4.number_of_cells
	    FROM raster_base A1, raster_absolute_time A2, 
            raster_spatial_extent A3, raster_metadata A4 
	    WHERE A1.id = A2.id AND A1.id = A3.id AND A1.id = A4.id;

CREATE VIEW raster_view_rel_time AS SELECT 
            A1.id, A1.mapset,
            A1.name, A1.temporal_type,
            A1.creation_time, 
-- Uncommented due to performance issues
--            A1.modification_time, A1.revision, 
            A1.creator, 
	    A2.start_time, A2.end_time,
            A3.north, A3.south, A3.east, A3.west, A3.proj,
	    A4.datatype, A4.cols, A4.rows,
            A4.nsres, A4.ewres, A4.min, A4.max,
	    A4.strds_register,
            A4.number_of_cells
	    FROM raster_base A1, raster_relative_time A2, 
            raster_spatial_extent A3, raster_metadata A4 
	    WHERE A1.id = A2.id AND A1.id = A3.id AND A1.id = A4.id;

-- Create a trigger to update the modification time and revision number in case the metadata have been updated 
-- Uncommented due to performance issues
--CREATE TRIGGER update_raster_metadata AFTER UPDATE ON raster_metadata 
--  BEGIN
--    UPDATE raster_base SET modification_time = datetime("NOW") WHERE id = old.id;
--    UPDATE raster_base SET revision = (revision + 1) WHERE id = old.id;
--  END;