source: TI01-discovery/branches/ingestAutomation-upgrade/database/ingest_procedures.sql @ 3863

/* 
 * Procedures used by the DB code as part of the ingest process
 * 
 * C Byrom Apr 08
 * 
 */
DROP FUNCTION add_document_to_history() CASCADE;
CREATE FUNCTION add_document_to_history() RETURNS TRIGGER AS 
$$
    BEGIN
        -- This inserts the currently stored information 
        -- into the original_document_history table
                -- NB, only do this when we are not just incrementing the harvest count
                IF OLD.harvest_count=NEW.harvest_count THEN
                INSERT INTO original_document_history 
                SELECT * FROM original_document WHERE original_document_id=NEW.original_document_id;
                END IF;
                RETURN NEW;
     END;
$$ LANGUAGE 'plpgsql';

DROP FUNCTION create_document(filename_in varchar(255), discovery_id_in varchar(255), 
        doc_type_in text, original_document_in text) CASCADE;
CREATE FUNCTION create_document(filename_in varchar(255), discovery_id_in varchar(255), 
        doc_type_in text, original_document_in text) RETURNS integer AS 
$$
        DECLARE
                db_id integer;
    BEGIN
        -- This inserts a new document into the DB
        INSERT INTO ORIGINAL_DOCUMENT (original_document_id, original_document_filename, 
            discovery_id, original_format, original_document, ts_vector, create_date, 
                        harvest_count, scn) VALUES (
            DEFAULT, filename_in, discovery_id_in, doc_type_in, original_document_in, 
                        to_tsvector('english', original_document_in), current_timestamp, 1, 1);

                SELECT original_document_id INTO db_id FROM ORIGINAL_DOCUMENT WHERE discovery_id = discovery_id_in;
                RETURN db_id;
     END;
$$ LANGUAGE 'plpgsql';


DROP FUNCTION delete_document(original_document_id_in int) CASCADE;
CREATE FUNCTION delete_document(original_document_id_in int) RETURNS VOID AS 
$$
        DECLARE
                db_id integer;
    BEGIN
        -- This deletes a document from the DB
        DELETE FROM TRANSFORMED_DOCUMENT WHERE original_document_id = original_document_id_in;
                DELETE FROM SPATIAL_TEMPORAL_DATA WHERE original_document_id = original_document_id_in;
        DELETE FROM ORIGINAL_DOCUMENT WHERE original_document_id = original_document_id_in;
        END;
$$ LANGUAGE 'plpgsql';


DROP FUNCTION update_document(original_document_id_in int, filename_in varchar(255), 
        discovery_id_in varchar(255), doc_type_in text, original_document_in text, scn_in int) CASCADE;
CREATE FUNCTION update_document(original_document_id_in int, filename_in varchar(255), 
        discovery_id_in varchar(255), doc_type_in text, original_document_in text, scn_in int) 
        RETURNS VOID AS 
$$
        DECLARE
                scn_current integer;
    BEGIN
        -- This updates an existing document

                -- check the scn is valid before an update is done
                SELECT scn INTO scn_current FROM ORIGINAL_DOCUMENT WHERE 
                        original_document_id = original_document_id_in;
                IF scn_in <> scn_current THEN
                        RAISE EXCEPTION 'Invalid SCN specified (current scn=%, specified scn=%) - 
                                implies record has been changed during update - exiting...', scn_current, scn_in;
                END IF;

        UPDATE ORIGINAL_DOCUMENT SET
                        original_document_filename = filename_in,
            discovery_id = discovery_id_in,
                        original_format = doc_type_in,
                        original_document = original_document_in,
            ts_vector = to_tsvector('english', original_document_in),
            update_date = current_timestamp, 
                        harvest_count = 1,
                        scn = scn_in + 1
                        WHERE original_document_id = original_document_id_in;
     END;
$$ LANGUAGE 'plpgsql';


DROP FUNCTION add_spatiotemporal_row(original_document_id_in int, north_in real, 
        south_in real, west_in real, east_in real, start_time_in timestamp, 
        end_time_in timestamp) CASCADE;
CREATE FUNCTION add_spatiotemporal_row(original_document_id_in int, north_in real, 
        south_in real, west_in real, east_in real, start_time_in timestamp, 
        end_time_in timestamp) RETURNS void AS 
$$
        DECLARE
                spatial_db_id integer := null;
                temporal_db_id integer := null;
                cmdStr text;

    BEGIN
        -- This inserts a new row in the spatial data table, if required
                IF west_in is not null AND east_in is not null AND
                        north_in is not null AND south_in is not null THEN

                        cmdStr := 'INSERT INTO SPATIAL_DATA (spatial_data_id, geometry, create_date) 
                                VALUES (DEFAULT, 
                                SetSRID(''BOX3D(' || west_in || ' ' || south_in || ', ' || east_in || ' ' || 
                                        north_in || ')''::box3d,4326),
                                current_timestamp)'; 
        
                -- For debugging only
                        --raise notice 'cmdStr is: %', cmdStr;

                EXECUTE cmdStr;

                        SELECT MAX(spatial_data_id) INTO spatial_db_id FROM SPATIAL_DATA;
                        --RAISE NOTICE 'Spatial data added - with ID: %', spatial_db_id;
                END IF;

        -- This inserts a new row in the temporal data table, if required
        IF start_time_in is not null OR end_time_in is not null THEN
                INSERT INTO TEMPORAL_DATA (temporal_data_id, start_time, end_time, create_date) 
                                VALUES (DEFAULT, start_time_in, end_time_in, current_timestamp);

                        SELECT MAX(temporal_data_id) INTO temporal_db_id FROM TEMPORAL_DATA;
                        --RAISE NOTICE 'Temporal data added - with ID: %', temporal_db_id;
                END IF;

                -- Now create row in link table
                INSERT INTO SPATIAL_TEMPORAL_DATA (spatial_temporal_data_id, original_document_id,
                        spatial_data_id, temporal_data_id, create_date) VALUES
                        (DEFAULT, original_document_id_in, spatial_db_id, temporal_db_id, current_timestamp);
                
     END;
$$ LANGUAGE 'plpgsql';