source: TI01-discovery/branches/ingestAutomation-upgrade/database/original_document.sql @ 3824

Subversion URL: http://proj.badc.rl.ac.uk/svn/ndg/TI01-discovery/branches/ingestAutomation-upgrade/database/original_document.sql@3824
Revision 3824, 2.4 KB checked in by cbyrom, 12 years ago (diff)

Adjust data model to use discovery id as a fieldname - and make this unique.

  • Property svn:executable set to *
Line 
1/*
2 * Basic DB structure to use in test of NERC Discovery upgrad
3 *
4 * C Byrom Apr 08
5 *
6 */
7--CREATE SCHEMA badc;
8--CREATE TABLE badc.records (
9DROP TABLE original_document;
10CREATE TABLE original_document (
11        original_document_id SERIAL primary key,
12                discovery_id    varchar(255) unique,
13                original_format text,
14                original_document text,
15                ts_vector       tsvector,
16                create_date     date,
17                update_date date,
18                harvest_count int,      -- this is a counter to store the number of times a doc is repeatedly harvested, even though it hasn't changed
19                scn                     int
20);
21
22INSERT INTO original_document VALUES (DEFAULT, 'test_dummy', 'DIF', null, null, current_date, null, 1, 1);
23INSERT INTO original_document VALUES (DEFAULT, 'test_record', 'DIF', '<?xml version="1.0" encoding="utf-8"?>
24<kml xmlns="http://earth.google.com/kml/2.2">
25  <Folder>
26        <name>Aggregation of KML documents by NDG Portal</name>
27    <visibility>0</visibility>
28        <open>0</open>
29        <description>The NetworkLink KML resources are those selected by the user when creating the document in the NDG portal (include URL?)</description>
30                    <NetworkLink>
31                        <name>ARGO Active float data; latitude range 45S to 90S; 18 March</name>
32                        <visibility>0</visibility>
33                        <open>0</open>
34                        <description>ARGO Active float data; latitude range 45S to 90S; 18 March - aggregated by the NDG Portal</description>
35                        <refreshVisibility>0</refreshVisibility>
36                        <flyToView>0</flyToView>
37                        <Link>
38                                <href>
39                                        http://psdocean.nerc-bas.ac.uk/ARGO/web/Active45S-90S.kmz
40                                </href>
41                        </Link>
42                </NetworkLink>
43  </Folder>
44</kml>',
45null, current_date, null, 1, 1);
46
47INSERT INTO original_document VALUES (DEFAULT, 'test_dummy', 'DIF', 'freds freds bloo' , null, current_date, null, 1, 1);
48INSERT INTO original_document VALUES (DEFAULT, 'test_dummy', null, 'fred fred bloo fred', null, current_date, null, 1, 1);
49INSERT INTO original_document VALUES (DEFAULT, 'test_dummy', null, 'fred fred bloo, fred, fred, fred, fre, fred', null, current_date, null, 1, 1);
50
51UPDATE original_document SET ts_vector = to_tsvector('english', original_document);
52
53-- Create index on searchable column to speed up searches
54CREATE INDEX textsearch_idx ON original_document USING gin(ts_vector);
55
56select 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;
Note: See TracBrowser for help on using the repository browser.