Revision 2230
Added by Matt Jones over 20 years ago
| test/test-queries/access.sql | ||
|---|---|---|
| 1 |
SELECT docid from xml_access |
|
| 2 |
WHERE |
|
| 3 |
( |
|
| 4 |
(lower(principal_name) = 'public' |
|
| 5 |
AND perm_type = 'allow' |
|
| 6 |
AND (permission='4' OR permission='7') |
|
| 7 |
) |
|
| 8 |
OR |
|
| 9 |
(lower(principal_name) = 'public' |
|
| 10 |
AND perm_type = 'allow' |
|
| 11 |
AND (permission='4' OR permission='7') |
|
| 12 |
) |
|
| 13 |
) |
|
| 14 |
/ |
|
| 0 | 15 | |
| test/test-queries/ext01.sql | ||
|---|---|---|
| 1 |
select xml_nodes.docid, 'originator/individualName/surName' as path, xml_nodes.nodedata, xml_nodes.parentnodeid |
|
| 2 |
from xml_nodes, xml_documents |
|
| 3 |
where parentnodeid IN |
|
| 4 |
( |
|
| 5 |
SELECT nodeid |
|
| 6 |
FROM xml_nodes |
|
| 7 |
WHERE nodename LIKE 'surName' |
|
| 8 |
AND parentnodeid IN |
|
| 9 |
( |
|
| 10 |
SELECT nodeid |
|
| 11 |
FROM xml_nodes |
|
| 12 |
WHERE nodename LIKE 'individualName' |
|
| 13 |
AND parentnodeid IN |
|
| 14 |
( |
|
| 15 |
SELECT nodeid |
|
| 16 |
FROM xml_nodes |
|
| 17 |
WHERE nodename LIKE 'originator' |
|
| 18 |
) |
|
| 19 |
) |
|
| 20 |
) |
|
| 21 |
AND xml_nodes.docid in ('knb-lter-gce.180')
|
|
| 22 |
AND xml_nodes.nodetype = 'TEXT' |
|
| 23 |
AND xml_nodes.rootnodeid = xml_documents.rootnodeid |
|
| 24 |
|
|
| 25 |
UNION select xml_nodes.docid, 'originator/individualName/givenName' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'givenName' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'individualName' AND parentnodeid IN |
|
| 26 |
(SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'originator'))) AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid
|
|
| 27 |
|
|
| 28 |
UNION select xml_nodes.docid, 'originator/organizationName' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'organizationName' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'originator')) AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid
|
|
| 29 |
|
|
| 30 |
UNION select xml_nodes.docid, 'creator/individualName/surName' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'surName' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'individualName' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'creator'))) AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid
|
|
| 31 |
|
|
| 32 |
UNION select xml_nodes.docid, 'creator/organizationName' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'organizationName' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'creator')) AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid
|
|
| 33 |
|
|
| 34 |
UNION select xml_nodes.docid, 'dataset/title' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'title' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'dataset')) AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid
|
|
| 35 |
|
|
| 36 |
UNION select xml_nodes.docid, 'keyword' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'keyword') AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid
|
|
| 37 |
|
|
| 38 |
UNION select xml_nodes.docid, 'dataset/dataTable/distribution/online/url' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'url' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'online' AND |
|
| 39 |
parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'distribution' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'dataTable' AND parentnodeid IN (SELECT nodeid |
|
| 40 |
FROM xml_nodes WHERE nodename LIKE 'dataset'))))) AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid
|
|
| 41 |
|
|
| 42 |
UNION select xml_nodes.docid, 'dataset/dataTable/distribution/inline' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'inline' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'distribution' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'dataTable' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'dataset')))) AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid
|
|
| 0 | 43 | |
| test/test-queries/ext02.sql | ||
|---|---|---|
| 1 |
select xml_nodes.docid, 'originator/individualName/surName' as path, xml_nodes.nodedata, xml_nodes.parentnodeid |
|
| 2 |
from xml_nodes, xml_documents |
|
| 3 |
where parentnodeid IN |
|
| 4 |
( |
|
| 5 |
SELECT nodeid |
|
| 6 |
FROM xml_nodes |
|
| 7 |
WHERE nodename LIKE 'surName' |
|
| 8 |
AND parentnodeid IN |
|
| 9 |
( |
|
| 10 |
SELECT nodeid |
|
| 11 |
FROM xml_nodes |
|
| 12 |
WHERE nodename LIKE 'individualName' |
|
| 13 |
AND parentnodeid IN |
|
| 14 |
( |
|
| 15 |
SELECT nodeid |
|
| 16 |
FROM xml_nodes |
|
| 17 |
WHERE nodename LIKE 'originator' |
|
| 18 |
) |
|
| 19 |
) |
|
| 20 |
) |
|
| 21 |
AND xml_nodes.docid in ('knb-lter-gce.180')
|
|
| 22 |
AND xml_nodes.nodetype = 'TEXT' |
|
| 23 |
AND xml_nodes.rootnodeid = xml_documents.rootnodeid |
|
| 0 | 24 | |
| test/test-queries/doc0.sql | ||
|---|---|---|
| 1 |
SELECT docid,docname,doctype,date_created, date_updated, rev |
|
| 2 |
FROM xml_documents |
|
| 3 |
WHERE docid IN ((SELECT DISTINCT docid FROM xml_nodes WHERE UPPER(nodedata) LIKE '%' )) |
|
| 4 |
AND |
|
| 5 |
(docid IN |
|
| 6 |
(SELECT docid FROM xml_documents WHERE lower(user_owner) ='public') |
|
| 7 |
OR |
|
| 8 |
(docid IN |
|
| 9 |
(SELECT docid from xml_access |
|
| 10 |
WHERE ( |
|
| 11 |
(lower(principal_name) = 'public' |
|
| 12 |
AND perm_type = 'allow' |
|
| 13 |
AND (permission='4' OR permission='7')) |
|
| 14 |
OR (lower(principal_name) = 'public' |
|
| 15 |
AND perm_type = 'allow' |
|
| 16 |
AND (permission='4' OR permission='7'))) |
|
| 17 |
AND subtreeid IS NULL) |
|
| 18 |
AND docid NOT IN |
|
| 19 |
(SELECT docid from xml_access |
|
| 20 |
WHERE( (lower(principal_name) = 'public' |
|
| 21 |
AND perm_type = 'deny' |
|
| 22 |
AND perm_order ='allowFirst' |
|
| 23 |
AND (permission='4' OR permission='7')) |
|
| 24 |
OR (lower(principal_name) = 'public' |
|
| 25 |
AND perm_type = 'deny' |
|
| 26 |
AND perm_order ='allowFirst' |
|
| 27 |
AND (permission='4' OR permission='7'))) |
|
| 28 |
AND subtreeid IS NULL ))) |
|
| 29 |
/ |
|
| 0 | 30 | |
| test/test-queries/doc1.sql | ||
|---|---|---|
| 1 |
SELECT docid,docname,doctype,date_created, date_updated, rev FROM xml_documents WHERE docid IN |
|
| 2 |
( |
|
| 3 |
(SELECT DISTINCT docid FROM xml_nodes WHERE UPPER(nodedata) LIKE '%') |
|
| 4 |
) |
|
| 5 |
AND (docid IN |
|
| 6 |
(SELECT docid FROM xml_documents WHERE lower(user_owner) ='public') |
|
| 7 |
OR (docid IN |
|
| 8 |
( |
|
| 9 |
SELECT docid from xml_access |
|
| 10 |
WHERE( |
|
| 11 |
(lower(principal_name) = 'public' AND perm_type = 'allow' |
|
| 12 |
AND (permission='4' OR permission='7') |
|
| 13 |
) OR |
|
| 14 |
(lower(principal_name) = 'public' AND perm_type = 'allow' |
|
| 15 |
AND (permission='4' OR permission='7') |
|
| 16 |
) |
|
| 17 |
) |
|
| 18 |
AND subtreeid IS NULL |
|
| 19 |
) |
|
| 20 |
AND docid NOT IN (SELECT docid from xml_access WHERE( (lower(principal_name) = 'public' AND perm_type = 'deny' AND perm_order ='allowFirst' AND (permission ='4' OR permission='7'))OR (lower(principal_name) = 'public' AND perm_type = 'deny' AND perm_order ='allowFirst' AND (permission='4' OR permission='7'))) AND subtreeid IS NULL ))) |
|
| 21 |
/ |
|
| 0 | 22 | |
| test/test-queries/doc2.sql | ||
|---|---|---|
| 1 |
SELECT DISTINCT xd.docid,xd.docname,xd.doctype,xd.date_created, xd.date_updated, xd.rev |
|
| 2 |
FROM xml_documents xd, xml_nodes xn |
|
| 3 |
WHERE xn.rootnodeid = xd.rootnodeid |
|
| 4 |
AND xn.nodedata LIKE '%' |
|
| 5 |
/ |
|
| 0 | 6 | |
| test/test-queries/doc3.sql | ||
|---|---|---|
| 1 |
SELECT DISTINCT xd.docid,xd.docname,xd.doctype,xd.date_created, xd.date_updated, xd.rev |
|
| 2 |
FROM xml_documents xd, xml_nodes xn |
|
| 3 |
WHERE xd.rootnodeid = xn.rootnodeid |
|
| 4 |
AND UPPER(xn.nodedata) LIKE '%' |
|
| 5 |
AND ( |
|
| 6 |
lower(xd.user_owner) LIKE 'public' |
|
| 7 |
OR |
|
| 8 |
( |
|
| 9 |
xd.docid IN |
|
| 10 |
( |
|
| 11 |
SELECT docid from xml_access |
|
| 12 |
WHERE ( |
|
| 13 |
( |
|
| 14 |
lower(principal_name) = 'public' |
|
| 15 |
AND perm_type = 'allow' |
|
| 16 |
AND (permission='4' OR permission='7') |
|
| 17 |
) |
|
| 18 |
OR |
|
| 19 |
( |
|
| 20 |
lower(principal_name) = 'public' |
|
| 21 |
AND perm_type = 'allow' |
|
| 22 |
AND (permission='4' OR permission='7') |
|
| 23 |
) |
|
| 24 |
) |
|
| 25 |
AND subtreeid IS NULL |
|
| 26 |
) |
|
| 27 |
AND xd.docid NOT IN |
|
| 28 |
( |
|
| 29 |
SELECT docid from xml_access |
|
| 30 |
WHERE ( |
|
| 31 |
( |
|
| 32 |
lower(principal_name) = 'public' |
|
| 33 |
AND perm_type = 'deny' |
|
| 34 |
AND perm_order ='allowFirst' |
|
| 35 |
AND (permission='4' OR permission='7') |
|
| 36 |
) |
|
| 37 |
OR |
|
| 38 |
( |
|
| 39 |
lower(principal_name) = 'public' |
|
| 40 |
AND perm_type = 'deny' |
|
| 41 |
AND perm_order ='allowFirst' |
|
| 42 |
AND (permission='4' OR permission='7') |
|
| 43 |
) |
|
| 44 |
) |
|
| 45 |
AND subtreeid IS NULL |
|
| 46 |
) |
|
| 47 |
) |
|
| 48 |
) |
|
| 49 |
/ |
|
| 0 | 50 | |
| test/test-queries/doc4.sql | ||
|---|---|---|
| 1 |
SELECT DISTINCT xd.docid,xd.docname,xd.doctype,xd.date_created, xd.date_updated, xd.rev |
|
| 2 |
FROM xml_documents xd, xml_nodes xn, |
|
| 3 |
( |
|
| 4 |
SELECT docid from xml_access |
|
| 5 |
WHERE ( |
|
| 6 |
( |
|
| 7 |
lower(principal_name) = 'public' |
|
| 8 |
AND perm_type = 'allow' |
|
| 9 |
AND (permission='4' OR permission='7') |
|
| 10 |
) |
|
| 11 |
OR |
|
| 12 |
( |
|
| 13 |
lower(principal_name) = 'public' |
|
| 14 |
AND perm_type = 'allow' |
|
| 15 |
AND (permission='4' OR permission='7') |
|
| 16 |
) |
|
| 17 |
) |
|
| 18 |
AND subtreeid IS NULL |
|
| 19 |
MINUS |
|
| 20 |
SELECT docid from xml_access |
|
| 21 |
WHERE ( |
|
| 22 |
( |
|
| 23 |
lower(principal_name) = 'public' |
|
| 24 |
AND perm_type = 'deny' |
|
| 25 |
AND perm_order ='allowFirst' |
|
| 26 |
AND (permission='4' OR permission='7') |
|
| 27 |
) |
|
| 28 |
OR |
|
| 29 |
( |
|
| 30 |
lower(principal_name) = 'public' |
|
| 31 |
AND perm_type = 'deny' |
|
| 32 |
AND perm_order ='allowFirst' |
|
| 33 |
AND (permission='4' OR permission='7') |
|
| 34 |
) |
|
| 35 |
) |
|
| 36 |
AND subtreeid IS NULL |
|
| 37 |
) xa |
|
| 38 |
WHERE xd.rootnodeid = xn.rootnodeid |
|
| 39 |
AND xd.docid = xa.docid |
|
| 40 |
AND UPPER(xn.nodedata) LIKE '%' |
|
| 41 |
/ |
|
| 0 | 42 | |
| test/test-queries/m_nodedata.sql | ||
|---|---|---|
| 1 |
/* This is a series of commands that could be used under the right |
|
| 2 |
env to modify the xml_nodes table to make the nodedata column |
|
| 3 |
smaller so that it can be indexed. How this would work without |
|
| 4 |
losing data for rows that are > 3000 chars is not yet fully worked out, |
|
| 5 |
so do NOT apply this indiscriminately. |
|
| 6 |
*/ |
|
| 7 |
ALTER TABLE xml_documents MODIFY CONSTRAINT xml_documents_root_fk DISABLE; |
|
| 8 |
ALTER TABLE xml_revisions MODIFY CONSTRAINT xml_revisions_root_fk DISABLE; |
|
| 9 |
ALTER TABLE xml_index MODIFY CONSTRAINT xml_index_nodeid_fk DISABLE; |
|
| 10 |
ALTER TABLE xml_nodes MODIFY CONSTRAINT xml_nodes_parent_fk DISABLE; |
|
| 11 |
ALTER TABLE xml_nodes MODIFY CONSTRAINT xml_nodes_root_fk DISABLE; |
|
| 12 |
CREATE TABLE xnodesback AS SELECT * FROM xml_nodes; |
|
| 13 |
DELETE FROM xml_nodes; |
|
| 14 |
ALTER TABLE xml_nodes MODIFY (nodedata VARCHAR2(3000)); |
|
| 15 |
INSERT INTO xml_nodes (NODEID, NODEINDEX, NODETYPE, NODENAME, NODEPREFIX, |
|
| 16 |
NODEDATA, PARENTNODEID, ROOTNODEID, DOCID, |
|
| 17 |
DATE_CREATED, DATE_UPDATED) |
|
| 18 |
SELECT * FROM xnodesback; |
|
| 19 |
ALTER TABLE xml_documents MODIFY CONSTRAINT xml_documents_root_fk ENABLE NOVALIDATE; |
|
| 20 |
ALTER TABLE xml_revisions MODIFY CONSTRAINT xml_revisions_root_fk ENABLE NOVALIDATE; |
|
| 21 |
ALTER TABLE xml_index MODIFY CONSTRAINT xml_index_nodeid_fk ENABLE NOVALIDATE; |
|
| 22 |
ALTER TABLE xml_nodes MODIFY CONSTRAINT xml_nodes_parent_fk ENABLE NOVALIDATE; |
|
| 23 |
ALTER TABLE xml_nodes MODIFY CONSTRAINT xml_nodes_root_fk ENABLE NOVALIDATE; |
|
| 0 | 24 | |
| test/test-queries/m_newindex.sql | ||
|---|---|---|
| 1 |
/* These are indices that are currently missing from metacat and should |
|
| 2 |
be created and maintained in order to speed up searches. |
|
| 3 |
*/ |
|
| 4 |
CREATE INDEX xml_access_principal_idx1 on xml_access (lower(principal_name)) |
|
| 5 |
/ |
|
| 6 |
CREATE INDEX xml_access_permtype_idx1 on xml_access (perm_type) |
|
| 7 |
/ |
|
| 8 |
CREATE INDEX xml_access_permission_idx1 on xml_access (permission) |
|
| 9 |
/ |
|
| 10 |
CREATE INDEX xml_nodes_idx4 ON xml_nodes (nodedata) |
|
| 11 |
/ |
|
| 12 |
ALTER TABLE xml_index MODIFY (path varchar2(1000)) |
|
| 13 |
/ |
|
| 0 | 14 | |
Also available in: Unified diff
A series of queries and commands being developed to improve metacat
performance. Very incomplete. WARNING: running some of these commmands
WILL DELETE DATA. Do not run these commands in a production environment.