|
1
|
/**
|
|
2
|
* '$RCSfile$'
|
|
3
|
* Copyright: 2004 Regents of the University of California and the
|
|
4
|
* National Center for Ecological Analysis and Synthesis
|
|
5
|
*
|
|
6
|
* '$Author: sgarg $'
|
|
7
|
* '$Date: 2005-04-22 16:05:47 -0700 (Fri, 22 Apr 2005) $'
|
|
8
|
* '$Revision: 2504 $'
|
|
9
|
*
|
|
10
|
* This program is free software; you can redistribute it and/or modify
|
|
11
|
* it under the terms of the GNU General Public License as published by
|
|
12
|
* the Free Software Foundation; either version 2 of the License, or
|
|
13
|
* (at your option) any later version.
|
|
14
|
*
|
|
15
|
* This program is distributed in the hope that it will be useful,
|
|
16
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
17
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
18
|
* GNU General Public License for more details.
|
|
19
|
*
|
|
20
|
* You should have received a copy of the GNU General Public License
|
|
21
|
* along with this program; if not, write to the Free Software
|
|
22
|
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
|
|
23
|
*/
|
|
24
|
|
|
25
|
|
|
26
|
|
|
27
|
/*
|
|
28
|
* Update the XML_CATALOG table. In Metacat 1.4.0, the system_id in xml_catalog
|
|
29
|
* pointed to knb metacat no matter where you install it. We need change it
|
|
30
|
* to local schema or dtd file.
|
|
31
|
*/
|
|
32
|
UPDATE xml_catalog
|
|
33
|
SET system_id = '@systemidserver@@html-path@/dtd/eml-access-@eml-version@.dtd'
|
|
34
|
WHERE public_id = '-//ecoinformatics.org//eml-access-@eml-version@//EN';
|
|
35
|
UPDATE xml_catalog
|
|
36
|
SET system_id = '@systemidserver@@html-path@/dtd/eml-attribute-@eml-version@.dtd'
|
|
37
|
WHERE public_id = '-//ecoinformatics.org//eml-attribute-@eml-version@//EN';
|
|
38
|
UPDATE xml_catalog
|
|
39
|
SET system_id = '@systemidserver@@html-path@/dtd/eml-constraint-@eml-version@.dtd'
|
|
40
|
WHERE public_id = '-//ecoinformatics.org//eml-constraint-@eml-version@//EN';
|
|
41
|
UPDATE xml_catalog
|
|
42
|
SET system_id = '@systemidserver@@html-path@/dtd/eml-coverage-@eml-version@.dtd'
|
|
43
|
WHERE public_id = '-//ecoinformatics.org//eml-coverage-@eml-version@//EN';
|
|
44
|
UPDATE xml_catalog
|
|
45
|
SET system_id = '@systemidserver@@html-path@/dtd/eml-coverage-@eml-version@.dtd'
|
|
46
|
WHERE public_id = '-//ecoinformatics.org//eml-coverage-@eml-version@//EN';
|
|
47
|
UPDATE xml_catalog
|
|
48
|
SET system_id = '@systemidserver@@html-path@/dtd/eml-dataset-@eml-version@.dtd'
|
|
49
|
WHERE public_id = '-//ecoinformatics.org//eml-dataset-@eml-version@//EN';
|
|
50
|
UPDATE xml_catalog
|
|
51
|
SET system_id = '@systemidserver@@html-path@/dtd/eml-entity-@eml-version@.dtd'
|
|
52
|
WHERE public_id = '-//ecoinformatics.org//eml-entity-@eml-version@//EN';
|
|
53
|
UPDATE xml_catalog
|
|
54
|
SET system_id = '@systemidserver@@html-path@/dtd/eml-literature-@eml-version@.dtd'
|
|
55
|
WHERE public_id = '-//ecoinformatics.org//eml-literature-@eml-version@//EN';
|
|
56
|
UPDATE xml_catalog
|
|
57
|
SET system_id = '@systemidserver@@html-path@/dtd/eml-physical-@eml-version@.dtd'
|
|
58
|
WHERE public_id = '-//ecoinformatics.org//eml-physical-@eml-version@//EN';
|
|
59
|
UPDATE xml_catalog
|
|
60
|
SET system_id = '@systemidserver@@html-path@/dtd/eml-project-@eml-version@.dtd'
|
|
61
|
WHERE public_id = '-//ecoinformatics.org//eml-project-@eml-version@//EN';
|
|
62
|
UPDATE xml_catalog
|
|
63
|
SET system_id = '@systemidserver@@html-path@/dtd/eml-protocol-@eml-version@.dtd'
|
|
64
|
WHERE public_id = '-//ecoinformatics.org//eml-protocol-@eml-version@//EN';
|
|
65
|
UPDATE xml_catalog
|
|
66
|
SET system_id = '@systemidserver@@html-path@/dtd/eml-software-@eml-version@.dtd'
|
|
67
|
WHERE public_id = '-//ecoinformatics.org//eml-software-@eml-version@//EN';
|
|
68
|
UPDATE xml_catalog
|
|
69
|
SET system_id = '@systemidserver@@html-path@/schema/eml-2.0.0/eml.xsd'
|
|
70
|
WHERE public_id = '@eml2_0_0namespace@';
|
|
71
|
UPDATE xml_catalog
|
|
72
|
SET system_id = '@systemidserver@@html-path@/schema/eml-2.0.1/eml.xsd'
|
|
73
|
WHERE public_id = '@eml2_0_1namespace@';
|
|
74
|
UPDATE xml_catalog
|
|
75
|
SET system_id = '@systemidserver@@html-path@/schema/eml-2.0.0/stmml.xsd'
|
|
76
|
WHERE public_id = '@stmmlnamespace@';
|
|
77
|
|
|
78
|
/*
|
|
79
|
* In Metacat 1.4.0, if user insert a eml201 document and has record in xml_relation
|
|
80
|
* table. The package type in xml_relation table will be eml200 rather than eml201.
|
|
81
|
* The bug was fixed and we need a sql command to fix exsited records
|
|
82
|
*/
|
|
83
|
UPDATE xml_relation SET packagetype='eml://ecoinformatics.org/eml-2.0.1'
|
|
84
|
WHERE docid IN (SELECT docid from xml_documents WHERE doctype LIKE 'eml://ecoinformatics.org/eml-2.0.1');
|
|
85
|
|
|
86
|
|
|
87
|
|
|
88
|
|
|
89
|
/*
|
|
90
|
* Returnfields -- table to store combinations of returnfields requested
|
|
91
|
* and the number of times this table is accessed
|
|
92
|
*/
|
|
93
|
CREATE TABLE xml_returnfield (
|
|
94
|
returnfield_id INT8 default nextval('xml_returnfield_id_seq'), -- the id for this returnfield entry
|
|
95
|
returnfield_string VARCHAR(2000), -- the returnfield string
|
|
96
|
usage_count INT8, -- the number of times this string has been requested
|
|
97
|
CONSTRAINT xml_returnfield_pk PRIMARY KEY (returnfield_id)
|
|
98
|
);
|
|
99
|
CREATE INDEX xml_returnfield_idx1 ON xml_returnfield(returnfield_string);
|
|
100
|
|
|
101
|
CREATE SEQUENCE xml_returnfield_id_seq;
|
|
102
|
|
|
103
|
/*
|
|
104
|
* Queryresults -- table to store queryresults for a given docid
|
|
105
|
* and returnfield_id
|
|
106
|
*/
|
|
107
|
CREATE TABLE xml_queryresult(
|
|
108
|
queryresult_id INT8 default nextval('xml_queryresult_id_seq'), -- id for this entry
|
|
109
|
returnfield_id INT8, -- id for the returnfield corresponding to this entry
|
|
110
|
docid VARCHAR(250), -- docid of the document
|
|
111
|
queryresult_string VARCHAR(4000), -- resultant text generated for this docid and given
|
|
112
|
-- returnfield
|
|
113
|
CONSTRAINT xml_queryresult_pk PRIMARY KEY (queryresult_id),
|
|
114
|
CONSTRAINT xml_queryresult_searchid_fk
|
|
115
|
FOREIGN KEY (returnfield_id) REFERENCES xml_returnfield
|
|
116
|
);
|
|
117
|
|
|
118
|
CREATE SEQUENCE xml_queryresult_id_seq;
|
|
119
|
|
|
120
|
ALTER TABLE xml_nodes ADD nodedatanumerical FLOAT8;
|
|
121
|
|
|
122
|
UPDATE xml_nodes SET nodedatanumerical = to_number(nodedata, '999999999999999999999999999D9999999999999999999999999999')
|
|
123
|
WHERE nodedata IS NOT NULL AND UPPER(nodedata) = LOWER(nodedata)
|
|
124
|
AND (TRIM(REPLACE(nodedata,'\n','')) SIMILAR TO '(^(-|[+]|[0-9])[0-9]*[.][0-9]*)'
|
|
125
|
OR TRIM(REPLACE(nodedata,'\n','')) SIMILAR TO '^(.)[0-9]+'
|
|
126
|
OR TRIM(REPLACE(nodedata,'\n','')) SIMILAR TO '^(-|[+]|[0-9])[0-9]*')
|
|
127
|
AND TRIM(REPLACE(nodedata,'\n','')) not SIMILAR TO '(-|[+])';
|
|
128
|
|