source: TI01-discovery/branches/ingestAutomation-upgrade/database/metadata_record.sql @ 3800

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

Upgraded version of ingest codebranch - including major refactoring of the ingest
scripts to make more OO - allowing re-use and simplification of code + removal of reliance
on eXist DB to store data; this will now all be stored and looked up from the Postgres DB

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 metadata_record;
10CREATE TABLE metadata_record (
11        record_id               SERIAL,
12                record_name     varchar(80),
13                ts_vector       tsvector,
14                original_format text,
15                moles_format    text,
16                dif_format      text,
17                dc_format       text,
18                create_date     date,
19                update_date date,
20                scn                     int
21);
22
23INSERT INTO metadata_record VALUES (DEFAULT, 'test_dummy', null, null, null, null, null, sysdate, sysdate, 1);
24INSERT INTO metadata_record VALUES (DEFAULT, 'test_record', null, '<?xml version="1.0" encoding="utf-8"?>
25<kml xmlns="http://earth.google.com/kml/2.2">
26  <Folder>
27        <name>Aggregation of KML documents by NDG Portal</name>
28    <visibility>0</visibility>
29        <open>0</open>
30        <description>The NetworkLink KML resources are those selected by the user when creating the document in the NDG portal (include URL?)</description>
31                    <NetworkLink>
32                        <name>ARGO Active float data; latitude range 45S to 90S; 18 March</name>
33                        <visibility>0</visibility>
34                        <open>0</open>
35                        <description>ARGO Active float data; latitude range 45S to 90S; 18 March - aggregated by the NDG Portal</description>
36                        <refreshVisibility>0</refreshVisibility>
37                        <flyToView>0</flyToView>
38                        <Link>
39                                <href>
40                                        http://psdocean.nerc-bas.ac.uk/ARGO/web/Active45S-90S.kmz
41                                </href>
42                        </Link>
43                </NetworkLink>
44  </Folder>
45</kml>',
46null,
47null,
48null);
49
50INSERT INTO metadata_record VALUES (DEFAULT, 'test_dummy', null, 'freds freds bloo' , null, null, null);
51INSERT INTO metadata_record VALUES (DEFAULT, 'test_dummy', null, 'fred fred bloo fred', null, null, null);
52INSERT INTO metadata_record VALUES (DEFAULT, 'test_dummy', null, 'fred fred bloo, fred, fred, fred, fre, fred', null, null, null);
53INSERT INTO metadata_record VALUES (DEFAULT, 'test_dummy', null, 'fred fred bloo fred, fred', null, null, null);
54
55UPDATE metadata_record SET ts_vector = to_tsvector('english', original_format);
56
57-- Create index on searchable column to speed up searches
58CREATE INDEX textsearch_idx ON metadata_record USING gin(ts_vector);
59
60select record_id, ts_vector, ts_rank(ts_vector, query) as rank from metadata_record, to_tsquery('english', 'FRED') query WHERE query @@ coalesce(ts_vector,'') order by rank desc;
Note: See TracBrowser for help on using the repository browser.