1 |
4104
|
daigle
|
/**
|
2 |
|
|
* '$RCSfile$'
|
3 |
|
|
* Copyright: 2004 Regents of the University of California and the
|
4 |
|
|
* National Center for Ecological Analysis and Synthesis
|
5 |
|
|
*
|
6 |
|
|
* '$Author$'
|
7 |
|
|
* '$Date$'
|
8 |
|
|
* '$Revision$'
|
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 = '/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 = '/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 = '/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 = '/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 = '/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 = '/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 = '/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 = '/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 = '/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 = '/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 = '/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 = '/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 = '/schema/eml-2.0.0/eml.xsd'
|
70 |
|
|
WHERE public_id = '@eml2_0_0namespace@';
|
71 |
|
|
UPDATE xml_catalog
|
72 |
|
|
SET system_id = '/schema/eml-2.0.1/eml.xsd'
|
73 |
|
|
WHERE public_id = '@eml2_0_1namespace@';
|
74 |
|
|
UPDATE xml_catalog
|
75 |
|
|
SET system_id = '/schema/eml-2.0.0/stmml.xsd'
|
76 |
|
|
WHERE public_id = '@stmmlnamespace@';
|
77 |
|
|
|
78 |
|
|
|
79 |
|
|
/*
|
80 |
|
|
* In Metacat 1.4.0, if user insert a eml201 document and has record in xml_relation
|
81 |
|
|
* table. The package type in xml_relation table will be eml200 rather than eml201.
|
82 |
|
|
* The bug was fixed and we need a sql command to fix exsited records
|
83 |
|
|
*/
|
84 |
|
|
UPDATE xml_relation SET packagetype='eml://ecoinformatics.org/eml-2.0.1'
|
85 |
|
|
WHERE docid IN (SELECT docid from xml_documents WHERE doctype LIKE 'eml://ecoinformatics.org/eml-2.0.1');
|
86 |
|
|
|
87 |
|
|
|
88 |
|
|
/*
|
89 |
|
|
* Returnfields -- table to store combinations of returnfields requested
|
90 |
|
|
* and the number of times this table is accessed
|
91 |
|
|
*/
|
92 |
|
|
CREATE TABLE xml_returnfield (
|
93 |
|
|
returnfield_id NUMBER(20), -- the id for this returnfield entry
|
94 |
|
|
returnfield_string VARCHAR2(2000), -- the returnfield string
|
95 |
|
|
usage_count NUMBER(20), -- the number of times this string
|
96 |
|
|
-- 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 |
|
|
/*
|
105 |
|
|
* Queryresults -- table to store queryresults for a given docid
|
106 |
|
|
* and returnfield_id
|
107 |
|
|
*/
|
108 |
|
|
CREATE TABLE xml_queryresult(
|
109 |
|
|
queryresult_id NUMBER(20), -- id for this entry
|
110 |
|
|
returnfield_id NUMBER(20), -- id for the returnfield corresponding to this entry
|
111 |
|
|
docid VARCHAR2(250), -- docid of the document
|
112 |
|
|
queryresult_string VARCHAR2(4000), -- resultant text generated for this docid and given
|
113 |
|
|
-- returnfield
|
114 |
|
|
CONSTRAINT xml_queryresult_pk PRIMARY KEY (queryresult_id),
|
115 |
|
|
CONSTRAINT xml_queryresult_searchid_fk
|
116 |
|
|
FOREIGN KEY (returnfield_id) REFERENCES xml_returnfield
|
117 |
|
|
);
|
118 |
|
|
|
119 |
|
|
CREATE INDEX xml_queryresult_idx1 ON xml_queryresult (returnfield_id, docid);
|
120 |
|
|
|
121 |
|
|
CREATE SEQUENCE xml_queryresult_id_seq;
|
122 |
|
|
|
123 |
4413
|
daigle
|
ALTER TABLE xml_nodes ADD nodedatanumerical NUMBER(20);
|
124 |
|
|
|
125 |
|
|
UPDATE xml_nodes SET nodedatanumerical = to_number(nodedata, '999999999999999999999999999D9999999999999999999999999999')
|
126 |
|
|
WHERE nodedata IS NOT NULL
|
127 |
|
|
AND UPPER(nodedata) = LOWER(nodedata)
|
128 |
|
|
AND (REGEXP_LIKE(TRIM(REPLACE(nodedata,'\n')),'(^(-|[+]|[0-9])[0-9]*[.][0-9]*)')
|
129 |
|
|
OR REGEXP_LIKE(TRIM(REPLACE(nodedata,'\n')),'^(.)[0-9]+')
|
130 |
|
|
OR REGEXP_LIKE(TRIM(REPLACE(nodedata,'\n')),'^(-|[+]|[0-9])[0-9]*'))
|
131 |
|
|
AND NOT REGEXP_LIKE(TRIM(REPLACE(nodedata,'\n')),'(-|[+])');
|