source: TI01-discovery/branches/ingestAutomation-upgrade/database/test_data.sql @ 3850

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

Add some test data for the DB to allow quick testing of data model.

  • Property svn:executable set to *
Line 
1/*
2 * Test data to populate and exercise tables with
3 *
4 * C Byrom May 08
5 *
6 */
7INSERT INTO original_document VALUES (DEFAULT, 'tst.xml', 'test_dummy', 'DIF', null, null, current_timestamp, null, 1, 1);
8INSERT INTO original_document VALUES (DEFAULT, 'tst1.xml', 'test_record', 'DIF', '<?xml version="1.0" encoding="utf-8"?>
9<kml xmlns="http://earth.google.com/kml/2.2">
10  <Folder>
11        <name>Aggregation of KML documents by NDG Portal</name>
12    <visibility>0</visibility>
13        <open>0</open>
14        <description>The NetworkLink KML resources are those selected by the user when creating the document in the NDG portal (include URL?)</description>
15                    <NetworkLink>
16                        <name>ARGO Active float data; latitude range 45S to 90S; 18 March</name>
17                        <visibility>0</visibility>
18                        <open>0</open>
19                        <description>ARGO Active float data; latitude range 45S to 90S; 18 March - aggregated by the NDG Portal</description>
20                        <refreshVisibility>0</refreshVisibility>
21                        <flyToView>0</flyToView>
22                        <Link>
23                                <href>
24                                        http://psdocean.nerc-bas.ac.uk/ARGO/web/Active45S-90S.kmz
25                                </href>
26                        </Link>
27                </NetworkLink>
28  </Folder>
29</kml>',
30null, current_timestamp, null, 1, 1);
31
32INSERT INTO original_document VALUES (DEFAULT, 'tst2.xml', 'test_dummy1', 'DIF', 'freds freds bloo' , null, current_timestamp, null, 1, 1);
33INSERT INTO original_document VALUES (DEFAULT, 'tst3.xml', 'test_dummy2', null, 'fred fred bloo fred', null, current_timestamp, null, 1, 1);
34INSERT INTO original_document VALUES (DEFAULT, 'tst4.xml', 'test_dummy3', null, 'fred fred bloo, fred, fred, fred, fre, fred', null, current_timestamp, null, 1, 1);
35
36UPDATE original_document SET ts_vector = to_tsvector('english', original_document);
37
38select 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.