Changeset 3849
- Timestamp:
- 07/05/08 10:13:17 (13 years ago)
- Location:
- TI01-discovery/branches/ingestAutomation-upgrade/database
- Files:
-
- 5 edited
Legend:
- Unmodified
- Added
- Removed
-
TI01-discovery/branches/ingestAutomation-upgrade/database/original_document.sql
r3843 r3849 9 9 original_document_id SERIAL primary key, 10 10 original_document_filename varchar(255), 11 discovery_id varchar(255) unique ,11 discovery_id varchar(255) unique not null, 12 12 original_format text, 13 13 original_document text, 14 14 ts_vector tsvector, 15 create_date date,16 update_date date,15 create_date timestamp, 16 update_date timestamp, 17 17 harvest_count int, -- this is a counter to store the number of times a doc is repeatedly harvested, even though it hasn't changed 18 18 scn int … … 24 24 25 25 -- add a trigger to ensure the history table is populated by records when these change 26 DROP TRIGGER add_to_history_table ON original_document;27 26 CREATE TRIGGER add_to_history_table 28 27 BEFORE UPDATE ON original_document 29 28 FOR EACH ROW EXECUTE PROCEDURE add_document_to_history(); 30 29 31 INSERT INTO original_document VALUES (DEFAULT, 'tst.xml', 'test_dummy', 'DIF', null, null, current_date, null, 1, 1);32 INSERT INTO original_document VALUES (DEFAULT, 'tst1.xml', 'test_record', 'DIF', '<?xml version="1.0" encoding="utf-8"?>33 <kml xmlns="http://earth.google.com/kml/2.2">34 <Folder>35 <name>Aggregation of KML documents by NDG Portal</name>36 <visibility>0</visibility>37 <open>0</open>38 <description>The NetworkLink KML resources are those selected by the user when creating the document in the NDG portal (include URL?)</description>39 <NetworkLink>40 <name>ARGO Active float data; latitude range 45S to 90S; 18 March</name>41 <visibility>0</visibility>42 <open>0</open>43 <description>ARGO Active float data; latitude range 45S to 90S; 18 March - aggregated by the NDG Portal</description>44 <refreshVisibility>0</refreshVisibility>45 <flyToView>0</flyToView>46 <Link>47 <href>48 http://psdocean.nerc-bas.ac.uk/ARGO/web/Active45S-90S.kmz49 </href>50 </Link>51 </NetworkLink>52 </Folder>53 </kml>',54 null, current_date, null, 1, 1);55 56 INSERT INTO original_document VALUES (DEFAULT, 'tst2.xml', 'test_dummy1', 'DIF', 'freds freds bloo' , null, current_date, null, 1, 1);57 INSERT INTO original_document VALUES (DEFAULT, 'tst3.xml', 'test_dummy2', null, 'fred fred bloo fred', null, current_date, null, 1, 1);58 INSERT INTO original_document VALUES (DEFAULT, 'tst4.xml', 'test_dummy3', null, 'fred fred bloo, fred, fred, fred, fre, fred', null, current_date, null, 1, 1);59 60 UPDATE original_document SET ts_vector = to_tsvector('english', original_document);61 62 30 -- Create index on searchable column to speed up searches 63 31 CREATE INDEX textsearch_idx ON original_document USING gin(ts_vector); 64 65 select 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; -
TI01-discovery/branches/ingestAutomation-upgrade/database/spatial_data.sql
r3834 r3849 9 9 ( 10 10 spatial_data_id SERIAL primary key, 11 description character varying (255) 11 description character varying (255), 12 create_date timestamp 12 13 ); 13 ALTER TABLE spatial_data OWNER TO postgres;14 --ALTER TABLE spatial_data OWNER TO postgres; 14 15 15 16 -- add 2D geometry column, 'geometry', to table - with SRS val of 4326 -
TI01-discovery/branches/ingestAutomation-upgrade/database/spatial_temporal_data.sql
r3834 r3849 10 10 spatial_temporal_data_id SERIAL PRIMARY KEY, 11 11 original_record_id int references original_document(original_document_id), 12 spatial_data_id int references spatial_data(spatial_data_id), 13 temporal_data_id int references temporal_data(temporal_data_id) 12 spatial_data_id int references spatial_data(spatial_data_id) ON DELETE CASCADE, 13 temporal_data_id int references temporal_data(temporal_data_id) ON DELETE CASCADE, 14 create_date timestamp 14 15 ); 15 ALTER TABLE spatial_temporal_data OWNER TO postgres;16 --ALTER TABLE spatial_temporal_data OWNER TO postgres; -
TI01-discovery/branches/ingestAutomation-upgrade/database/temporal_data.sql
r3834 r3849 10 10 temporal_data_id SERIAL primary key, 11 11 start_time timestamp without time zone, 12 end_time timestamp without time zone 12 end_time timestamp without time zone, 13 create_date timestamp 13 14 ); 14 ALTER TABLE temporal_data OWNER TO postgres;15 --ALTER TABLE temporal_data OWNER TO postgres; -
TI01-discovery/branches/ingestAutomation-upgrade/database/transformed_document.sql
r3807 r3849 13 13 transformed_format text, 14 14 transformed_document text, 15 create_date date,16 update_date date,15 create_date timestamp, 16 update_date timestamp, 17 17 scn int 18 18 );
Note: See TracChangeset
for help on using the changeset viewer.