Changeset 3863 for TI01-discovery


Ignore:
Timestamp:
08/05/08 11:20:55 (11 years ago)
Author:
cbyrom
Message:

Add new stored proc to cleanly delete an ingested document and all
its associated data.

Location:
TI01-discovery/branches/ingestAutomation-upgrade/database
Files:
4 edited

Legend:

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

    r3860 r3863  
    3838                RETURN db_id; 
    3939     END; 
     40$$ LANGUAGE 'plpgsql'; 
     41 
     42 
     43DROP FUNCTION delete_document(original_document_id_in int) CASCADE; 
     44CREATE FUNCTION delete_document(original_document_id_in int) RETURNS VOID AS  
     45$$ 
     46        DECLARE 
     47                db_id integer; 
     48    BEGIN 
     49        -- This deletes a document from the DB 
     50        DELETE FROM TRANSFORMED_DOCUMENT WHERE original_document_id = original_document_id_in; 
     51                DELETE FROM SPATIAL_TEMPORAL_DATA WHERE original_document_id = original_document_id_in; 
     52        DELETE FROM ORIGINAL_DOCUMENT WHERE original_document_id = original_document_id_in; 
     53        END; 
    4054$$ LANGUAGE 'plpgsql'; 
    4155 
     
    116130 
    117131                -- Now create row in link table 
    118                 INSERT INTO SPATIAL_TEMPORAL_DATA (spatial_temporal_data_id, original_record_id, 
     132                INSERT INTO SPATIAL_TEMPORAL_DATA (spatial_temporal_data_id, original_document_id, 
    119133                        spatial_data_id, temporal_data_id, create_date) VALUES 
    120134                        (DEFAULT, original_document_id_in, spatial_db_id, temporal_db_id, current_timestamp); 
  • TI01-discovery/branches/ingestAutomation-upgrade/database/spatial_temporal_data.sql

    r3849 r3863  
    99( 
    1010        spatial_temporal_data_id SERIAL PRIMARY KEY, 
    11         original_record_id      int references original_document(original_document_id), 
     11        original_document_id    int references original_document(original_document_id), 
    1212        spatial_data_id         int references spatial_data(spatial_data_id) ON DELETE CASCADE, 
    1313        temporal_data_id        int references temporal_data(temporal_data_id) ON DELETE CASCADE, 
  • TI01-discovery/branches/ingestAutomation-upgrade/database/test_data.sql

    r3850 r3863  
    3737 
    3838select original_document_id, ts_vector, ts_rank(ts_vector, query) as rank from original_document, to_tsquery('english', 'FRED') query WHERE query @@ coalesce(ts_vector,'') order by rank desc; 
     39 
     40 
     41INSERT INTO transformed_document VALUES (DEFAULT, 1,  'moles', null, current_date, null, 1); 
     42INSERT INTO transformed_document VALUES (DEFAULT, 1,  'mdip',  
     43'<mdip xmlns="http://earth.google.com/kml/2.2"> 
     44  <Folder> 
     45        <name>Aggregation of KML documents by NDG Portal</name> 
     46    <visibility>0</visibility> 
     47        <open>0</open> 
     48        <description>The NetworkLink KML resources are those selected by the user when creating the document in the NDG portal (include URL?)</description> 
     49                    <NetworkLink> 
     50                        <name>ARGO Active float data; latitude range 45S to 90S; 18 March</name> 
     51                        <visibility>0</visibility> 
     52                        <open>0</open> 
     53                        <description>ARGO Active float data; latitude range 45S to 90S; 18 March - aggregated by the NDG Portal</description> 
     54                        <refreshVisibility>0</refreshVisibility> 
     55                        <flyToView>0</flyToView> 
     56                        <Link> 
     57                                <href> 
     58                                        http://psdocean.nerc-bas.ac.uk/ARGO/web/Active45S-90S.kmz 
     59                                </href> 
     60                        </Link> 
     61                </NetworkLink> 
     62  </Folder> 
     63</mdip>', 
     64current_date, null, 1); 
     65 
  • TI01-discovery/branches/ingestAutomation-upgrade/database/transformed_document.sql

    r3849 r3863  
    99DROP TABLE transformed_document; 
    1010CREATE TABLE transformed_document ( 
    11         transformed_document_id         SERIAL primary key, 
    12                 original_record_id      int references original_document(original_document_id), 
     11        transformed_document_id SERIAL primary key, 
     12                original_document_id    int references original_document(original_document_id), 
    1313                transformed_format      text, 
    1414                transformed_document    text, 
     
    1717                scn                     int 
    1818); 
    19  
    20 INSERT INTO transformed_document VALUES (DEFAULT, 1,  'moles', null, current_date, null, 1); 
    21 INSERT INTO transformed_document VALUES (DEFAULT, 1,  'mdip',  
    22 '<mdip xmlns="http://earth.google.com/kml/2.2"> 
    23   <Folder> 
    24         <name>Aggregation of KML documents by NDG Portal</name> 
    25     <visibility>0</visibility> 
    26         <open>0</open> 
    27         <description>The NetworkLink KML resources are those selected by the user when creating the document in the NDG portal (include URL?)</description> 
    28                     <NetworkLink> 
    29                         <name>ARGO Active float data; latitude range 45S to 90S; 18 March</name> 
    30                         <visibility>0</visibility> 
    31                         <open>0</open> 
    32                         <description>ARGO Active float data; latitude range 45S to 90S; 18 March - aggregated by the NDG Portal</description> 
    33                         <refreshVisibility>0</refreshVisibility> 
    34                         <flyToView>0</flyToView> 
    35                         <Link> 
    36                                 <href> 
    37                                         http://psdocean.nerc-bas.ac.uk/ARGO/web/Active45S-90S.kmz 
    38                                 </href> 
    39                         </Link> 
    40                 </NetworkLink> 
    41   </Folder> 
    42 </mdip>', 
    43 current_date, null, 1); 
    44  
Note: See TracChangeset for help on using the changeset viewer.