Skip to content
Nikolaos Konstantinou edited this page Jul 9, 2013 · 14 revisions

The following are examples of what you can do using the tool (more content to be added here)

  1. Allow arbitrary queries as logical views, using the rr:sqlQuery construct:

    <#dc-creator-view>
    rr:sqlQuery """
    SELECT i.item_id AS item_id, mv.text_value AS text_value
    FROM item AS i
    INNER JOIN metadatavalue AS mv
    ON i.item_id=mv.item_id
    INNER JOIN metadatafieldregistry AS mfr
    ON mfr.metadata_field_id=mv.metadata_field_id
    INNER JOIN metadataschemaregistry AS msr
    ON msr.metadata_schema_id=mfr.metadata_schema_id
    WHERE i.in_archive=TRUE AND
    mv.text_value IS NOT NULL AND
    msr.namespace='http://dublincore.org/documents/dcmi-terms/' AND
    mfr.element='creator'
    """.
    
  2. Allow functions in the SQL queries, for instance string manipulation functions (such as replace), which can be nested:

    <#dc-language-view>
    rr:sqlQuery """
    SELECT h.handle AS handle,
    replace(replace(replace(replace(replace(replace(mv.text_value,'Greek','ell'),'Spanish','spa'),'German','deu'),'Italian','ita'),'English','eng'),'French','fra') AS text_value
    FROM handle AS h, item AS i, metadatavalue AS mv, metadataschemaregistry AS msr, metadatafieldregistry AS mfr WHERE
    i.in_archive=TRUE AND
    h.resource_id=i.item_id AND
    h.resource_type_id=2 AND
    msr.metadata_schema_id=mfr.metadata_schema_id AND
    mfr.metadata_field_id=mv.metadata_field_id AND
    mv.text_value is not null AND
    i.item_id=mv.item_id AND
    msr.namespace='http://dublincore.org/documents/dcmi-terms/' AND
    mfr.element='language' AND
    mfr.qualifier IS NULL
    """.
    
Clone this wiki locally