Changeset 5328 for TI01-discovery


Ignore:
Timestamp:
21/05/09 12:07:00 (10 years ago)
Author:
sdonegan
Message:

Updated sql for database merged from rev5026

File:
1 edited

Legend:

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

    r3967 r5328  
    2121 
    2222DROP FUNCTION create_document(filename_in varchar(255), discovery_id_in varchar(255),  
    23         doc_type_in text, original_document_in text, authors text, parameters text, scope text) CASCADE; 
     23        doc_type_in text, original_document_in text, authors text, parameters text, scope text, dataset_name text, datacentre_name text, metadata_update_date timestamp) CASCADE; 
    2424CREATE FUNCTION create_document(filename_in varchar(255), discovery_id_in varchar(255),  
    25         doc_type_in text, original_document_in text, authors text, parameters text, scope text) RETURNS integer AS  
     25        doc_type_in text, original_document_in text, authors text, parameters text, scope text, dataset_name_in text, datacentre_name_in text, metadata_update_date_in timestamp) RETURNS integer AS  
    2626$$ 
    2727        DECLARE 
     
    3131        INSERT INTO ORIGINAL_DOCUMENT (original_document_id, original_document_filename,  
    3232            discovery_id, original_format, original_document, document_ts_vector, authors_ts_vector,  
    33             parameters_ts_vector, scope_ts_vector, create_date, harvest_count, scn) VALUES ( 
     33            parameters_ts_vector, scope_ts_vector, create_date, harvest_count, scn, dataset_name, datacentre_name, dataset_metadata_update_date) VALUES ( 
    3434            DEFAULT, filename_in, discovery_id_in, doc_type_in, original_document_in,  
    3535                        to_tsvector('english', original_document_in), to_tsvector('english', authors),  
    36                         to_tsvector('english', parameters), to_tsvector('english', scope), current_timestamp, 1, 1); 
     36                        to_tsvector('english', parameters), to_tsvector('english', scope), current_timestamp, 1, 1, dataset_name_in, datacentre_name_in, metadata_update_date_in); 
    3737 
    3838                SELECT original_document_id INTO db_id FROM ORIGINAL_DOCUMENT WHERE discovery_id = discovery_id_in; 
     
    4747        DECLARE 
    4848                db_id integer; 
     49                tdID integer; 
     50                sdID integer; 
    4951    BEGIN 
    5052        -- This deletes a document from the DB 
     53 
     54        -- get temporal_data_id from spatial_temporal_data (SJD 090209) --NOTE can't mix SELECT and DELETE bits here!! 
     55        SELECT INTO tdID temporal_data_id FROM SPATIAL_TEMPORAL_DATA WHERE original_document_id = original_document_id_in; 
     56        SELECT INTO sdID spatial_data_id FROM SPATIAL_TEMPORAL_DATA WHERE original_document_id = original_document_id_in; 
     57         
     58        DELETE FROM spatial_data WHERE spatial_data_id = sdID; 
     59        DELETE FROM temporal_data WHERE temporal_data_id = tdID; 
    5160        DELETE FROM TRANSFORMED_DOCUMENT WHERE original_document_id = original_document_id_in; 
    52                 DELETE FROM SPATIAL_TEMPORAL_DATA WHERE original_document_id = original_document_id_in; 
     61        DELETE FROM SPATIAL_TEMPORAL_DATA WHERE original_document_id = original_document_id_in; 
    5362        DELETE FROM ORIGINAL_DOCUMENT WHERE original_document_id = original_document_id_in; 
     63 
    5464        END; 
    5565$$ LANGUAGE 'plpgsql'; 
     
    5868DROP FUNCTION update_document(original_document_id_in int, filename_in varchar(255),  
    5969        discovery_id_in varchar(255), doc_type_in text, original_document_in text,  
    60         authors text, parameters text, scope text, scn_in int) CASCADE; 
     70        authors text, parameters text, scope text, scn_in int,dataset_name_in text, datacentre_name_in text, metadata_update_date_in timestamp) CASCADE; 
    6171CREATE FUNCTION update_document(original_document_id_in int, filename_in varchar(255),  
    6272        discovery_id_in varchar(255), doc_type_in text, original_document_in text,  
    63         authors text, parameters text, scope text, scn_in int)  
     73        authors text, parameters text, scope text, scn_in int,dataset_name_in text, datacentre_name_in text, metadata_update_date_in timestamp)  
    6474        RETURNS VOID AS  
    6575$$ 
     
    8898            update_date = current_timestamp,  
    8999                        harvest_count = 1, 
    90                         scn = scn_in + 1 
     100                        scn = scn_in + 1, 
     101                dataset_name = dataset_name_in, 
     102                datacentre_name = datacentre_name_in, 
     103                dataset_metadata_update_date = metadata_update_date_in 
    91104                        WHERE original_document_id = original_document_id_in; 
    92105     END; 
Note: See TracChangeset for help on using the changeset viewer.