Changeset 3851


Ignore:
Timestamp:
07/05/08 10:15:25 (11 years ago)
Author:
cbyrom
Message:

Add various stored procs to create/update records - including proc,
add_spatiotemporal_row, to handle the time range and coords input -
building the BOX3D command dynamically to avoid problems with
interpretation.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • TI01-discovery/branches/ingestAutomation-upgrade/database/ingest_procedures.sql

    r3843 r3851  
    66 */ 
    77DROP FUNCTION add_document_to_history() CASCADE; 
    8 CREATE FUNCTION add_document_to_history() RETURNS OPAQUE AS ' 
     8CREATE FUNCTION add_document_to_history() RETURNS TRIGGER AS  
     9$$ 
    910    BEGIN 
    1011        -- This inserts the currently stored information  
     
    1718                RETURN NEW; 
    1819     END; 
    19 ' LANGUAGE 'plpgsql'; 
     20$$ LANGUAGE 'plpgsql'; 
     21 
     22DROP FUNCTION create_document(filename_in varchar(255), discovery_id_in varchar(255),  
     23        doc_type_in text, original_document_in text) CASCADE; 
     24CREATE FUNCTION create_document(filename_in varchar(255), discovery_id_in varchar(255),  
     25        doc_type_in text, original_document_in text) RETURNS integer AS  
     26$$ 
     27        DECLARE 
     28                db_id integer := -1; 
     29    BEGIN 
     30        -- This inserts a new document into the DB 
     31        INSERT INTO ORIGINAL_DOCUMENT (original_document_id, original_document_filename,  
     32            discovery_id, original_format, original_document, ts_vector, create_date,  
     33                        harvest_count, scn) VALUES ( 
     34            DEFAULT, filename_in, discovery_id_in, doc_type_in, original_document_in,  
     35                        to_tsvector('english', original_document_in), current_timestamp, 1, 1); 
     36 
     37                SELECT original_document_id INTO db_id FROM ORIGINAL_DOCUMENT WHERE discovery_id = discovery_id_in; 
     38                RETURN db_id; 
     39     END; 
     40$$ LANGUAGE 'plpgsql'; 
     41 
     42 
     43DROP FUNCTION update_document(original_document_id_in int, filename_in varchar(255),  
     44        discovery_id_in varchar(255), doc_type_in text, original_document_in text, scn_int int) CASCADE; 
     45CREATE FUNCTION update_document(original_document_id_in int, filename_in varchar(255),  
     46        discovery_id_in varchar(255), doc_type_in text, original_document_in text, scn_int int)  
     47        RETURNS integer AS  
     48$$ 
     49        DECLARE 
     50                scn_current integer := 1; 
     51    BEGIN 
     52        -- This updates an existing document 
     53         
     54                -- check the scn is valid before an update is done 
     55                SELECT scn INTO scn_current FROM ORIGINAL_DOCUMENT WHERE  
     56                        original_document_id = original_document_id_in; 
     57                IF scn_in <> scn_current THEN 
     58                        RAISE EXCEPTION 'Invalid SCN specified (current scn=%, specified scn=%) - implies record has been changed during update - exiting...'; 
     59                END IF; 
     60 
     61        UPDATE ORIGINAL_DOCUMENT SET 
     62                        original_document_filename = filename_in, 
     63            discovery_id = discovery_id_in, 
     64                        original_format = doc_type_in, 
     65                        original_document = original_document_in, 
     66            ts_vector = to_tsvector('english', original_document_in), 
     67            update_date = current_timestamp,  
     68                        harvest_count = 1, 
     69                        scn = scn_in + 1 
     70                        WHERE original_document_id = original_document_id_in; 
     71     END; 
     72$$ LANGUAGE 'plpgsql'; 
     73 
     74 
     75DROP FUNCTION add_spatiotemporal_row(original_document_id_in int, north_in real,  
     76        south_in real, west_in real, east_in real, start_time_in timestamp,  
     77        end_time_in timestamp) CASCADE; 
     78CREATE FUNCTION add_spatiotemporal_row(original_document_id_in int, north_in real,  
     79        south_in real, west_in real, east_in real, start_time_in timestamp,  
     80        end_time_in timestamp) RETURNS void AS  
     81$$ 
     82        DECLARE 
     83                spatial_db_id integer := null; 
     84                temporal_db_id integer := null; 
     85                cmdStr text; 
     86 
     87    BEGIN 
     88        -- This inserts a new row in the spatial data table, if required 
     89                IF west_in is not null AND east_in is not null AND 
     90                        north_in is not null AND south_in is not null THEN 
     91 
     92                        cmdStr := 'INSERT INTO SPATIAL_DATA (spatial_data_id, geometry, create_date)  
     93                                VALUES (DEFAULT,  
     94                                SetSRID(''BOX3D(' || west_in || ' ' || south_in || ', ' || east_in || ' ' ||  
     95                                        north_in || ')''::box3d,4326), 
     96                                current_timestamp)';  
     97         
     98                -- For debugging only 
     99                        --raise notice 'cmdStr is: %', cmdStr; 
     100 
     101                EXECUTE cmdStr; 
     102 
     103                        SELECT MAX(spatial_data_id) INTO spatial_db_id FROM SPATIAL_DATA; 
     104                        --RAISE NOTICE 'Spatial data added - with ID: %', spatial_db_id; 
     105                END IF; 
     106 
     107        -- This inserts a new row in the temporal data table, if required 
     108        IF start_time_in is not null OR end_time_in is not null THEN 
     109                INSERT INTO TEMPORAL_DATA (temporal_data_id, start_time, end_time, create_date)  
     110                                VALUES (DEFAULT, start_time_in, end_time_in, current_timestamp); 
     111 
     112                        SELECT MAX(temporal_data_id) INTO temporal_db_id FROM TEMPORAL_DATA; 
     113                        --RAISE NOTICE 'Temporal data added - with ID: %', temporal_db_id; 
     114                END IF; 
     115 
     116                -- Now create row in link table 
     117                INSERT INTO SPATIAL_TEMPORAL_DATA (spatial_temporal_data_id, original_record_id, 
     118                        spatial_data_id, temporal_data_id, create_date) VALUES 
     119                        (DEFAULT, original_document_id_in, spatial_db_id, temporal_db_id, current_timestamp); 
     120                 
     121     END; 
     122$$ LANGUAGE 'plpgsql'; 
Note: See TracChangeset for help on using the changeset viewer.