Module: Export::Dwca::Occurrence::SqlFragments

Included in:
Data, MediaExporter
Defined in:
lib/export/dwca/occurrence/sql_fragments.rb

Overview

SQL fragment builders for DwC-A export queries. These are stateless helpers that generate SQL snippets for complex queries.

Instance Method Summary collapse

Instance Method Details

SQL fragment: Copyright label from temp attribution table. Generates copyright string from year and holder names.

Parameters:

  • attr_table_alias (String) (defaults to: 'attr') —

    SQL table alias for attribution temp table

Returns:

  • (String) —

    SQL CASE statement for building copyright label



84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/export/dwca/occurrence/sql_fragments.rb', line 84

def copyright_label_from_temp_sql(attr_table_alias = 'attr')
  <<-SQL
    CASE
      WHEN #{attr_table_alias}.copyright_holder_names_array IS NOT NULL
        AND array_length(#{attr_table_alias}.copyright_holder_names_array, 1) > 0
      THEN
        '©' ||
        CASE
          WHEN #{attr_table_alias}.copyright_year IS NOT NULL
            THEN #{attr_table_alias}.copyright_year::text || ' ' || pg_temp.authorship_sentence(#{attr_table_alias}.copyright_holder_names_array)
          ELSE
            pg_temp.authorship_sentence(#{attr_table_alias}.copyright_holder_names_array)
        END
      ELSE NULL
    END
  SQL
end

#image_occurrence_resolution_joins_sql(image_alias: 'img') ⇒ String

SQL fragment: Image occurrence resolution JOINs. Resolves images to dwc_occurrences via

depictions -> collection_objects/field_occurrences.

Includes filtering to only scoped occurrences.

Parameters:

  • image_alias (String) (defaults to: 'img') —

    SQL table alias for images table

Returns:

  • (String) —

    SQL JOIN clauses for resolving image to occurrence



36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
# File 'lib/export/dwca/occurrence/sql_fragments.rb', line 36

def image_occurrence_resolution_joins_sql(image_alias: 'img')
  <<~SQL
    -- Join to get coreid (occurrenceID) via depiction -> collection_object/field_occurrence -> dwc_occurrence
    LEFT JOIN depictions dep ON dep.image_id = #{image_alias}.id
    LEFT JOIN collection_objects co ON co.id = dep.depiction_object_id AND dep.depiction_object_type = 'CollectionObject'
    LEFT JOIN field_occurrences fo ON fo.id = dep.depiction_object_id AND dep.depiction_object_type = 'FieldOccurrence'
    LEFT JOIN observations obs ON obs.id = dep.depiction_object_id AND dep.depiction_object_type = 'Observation'
    LEFT JOIN collection_objects co_obs ON co_obs.id = obs.observation_object_id AND obs.observation_object_type = 'CollectionObject'
    LEFT JOIN field_occurrences fo_obs ON fo_obs.id = obs.observation_object_id AND obs.observation_object_type = 'FieldOccurrence'

    -- Filter to only scoped occurrences
    LEFT JOIN temp_scoped_occurrences scope_co ON scope_co.occurrence_id = co.id AND scope_co.occurrence_type = 'CollectionObject'
    LEFT JOIN temp_scoped_occurrences scope_fo ON scope_fo.occurrence_id = fo.id AND scope_fo.occurrence_type = 'FieldOccurrence'
    LEFT JOIN temp_scoped_occurrences scope_co_obs ON scope_co_obs.occurrence_id = co_obs.id AND scope_co_obs.occurrence_type = 'CollectionObject'
    LEFT JOIN temp_scoped_occurrences scope_fo_obs ON scope_fo_obs.occurrence_id = fo_obs.id AND scope_fo_obs.occurrence_type = 'FieldOccurrence'

    LEFT JOIN dwc_occurrences dwc ON (dwc.dwc_occurrence_object_id = co.id AND dwc.dwc_occurrence_object_type = 'CollectionObject' AND scope_co.occurrence_id IS NOT NULL)
      OR (dwc.dwc_occurrence_object_id = fo.id AND dwc.dwc_occurrence_object_type = 'FieldOccurrence' AND scope_fo.occurrence_id IS NOT NULL)
      OR (dwc.dwc_occurrence_object_id = co_obs.id AND dwc.dwc_occurrence_object_type = 'CollectionObject' AND scope_co_obs.occurrence_id IS NOT NULL)
      OR (dwc.dwc_occurrence_object_id = fo_obs.id AND dwc.dwc_occurrence_object_type = 'FieldOccurrence' AND scope_fo_obs.occurrence_id IS NOT NULL)
  SQL
end

#media_identifier_joins_sql(media_class, table_alias) ⇒ String

SQL fragment: Media identifier JOINs for UUID and URI. Provides LEFT JOINs to the identifiers table for UUID and URI lookups.

Parameters:

  • media_class (Class) —

    Media class (Image or Sound)

  • table_alias (String) —

    SQL table alias for the media table

Returns:

  • (String) —

    SQL JOIN clauses for UUID and URI identifiers



18
19
20
21
22
23
24
25
26
27
28
 
# File 'lib/export/dwca/occurrence/sql_fragments.rb', line 18

def media_identifier_joins_sql(media_class, table_alias)
  media_type = media_class.name
  <<~SQL.squish
    LEFT JOIN identifiers uuid_id ON uuid_id.identifier_object_id = #{table_alias}.id
      AND uuid_id.identifier_object_type = '#{media_type}'
      AND uuid_id.type LIKE  'Identifier::Global::Uuid%'
    LEFT JOIN identifiers uri_id ON uri_id.identifier_object_id = #{table_alias}.id
      AND uri_id.identifier_object_type = '#{media_type}'
      AND uri_id.type LIKE 'Identifier::Global::Uri%'
  SQL
end

#media_identifier_sql(media_class, media_table_alias) ⇒ String

SQL fragment: Media identifier with CSV sanitization

Parameters:

  • media_class (Class) —

    Media class (Image or Sound)

  • media_table_alias (String) —

    SQL table alias for the media table

Returns:

  • (String) —

    SQL expression for sanitized identifier



9
10
11
 
# File 'lib/export/dwca/occurrence/sql_fragments.rb', line 9

def media_identifier_sql(media_class, media_table_alias)
  media_class.dwc_media_identifier_sql(table_alias: media_table_alias)
end

#sound_occurrence_resolution_joins_sql(sound_alias: 'snd') ⇒ String

SQL fragment: Sound occurrence resolution JOINs Resolves sounds to dwc_occurrences via conveyances -> collection_objects/field_occurrences Includes filtering to only scoped occurrences

Parameters:

  • sound_alias (String) (defaults to: 'snd') —

    SQL table alias for sounds table

Returns:

  • (String) —

    SQL JOIN clauses for resolving sound to occurrence



64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
# File 'lib/export/dwca/occurrence/sql_fragments.rb', line 64

def sound_occurrence_resolution_joins_sql(sound_alias: 'snd')
  <<~SQL
    -- Join to get coreid (occurrenceID) via conveyance -> collection_object/field_occurrence -> dwc_occurrence
    LEFT JOIN conveyances conv ON conv.sound_id = #{sound_alias}.id
    LEFT JOIN collection_objects co ON co.id = conv.conveyance_object_id AND conv.conveyance_object_type = 'CollectionObject'
    LEFT JOIN field_occurrences fo ON fo.id = conv.conveyance_object_id AND conv.conveyance_object_type = 'FieldOccurrence'

    -- Filter to only scoped occurrences
    LEFT JOIN temp_scoped_occurrences scope_co ON scope_co.occurrence_id = co.id AND scope_co.occurrence_type = 'CollectionObject'
    LEFT JOIN temp_scoped_occurrences scope_fo ON scope_fo.occurrence_id = fo.id AND scope_fo.occurrence_type = 'FieldOccurrence'

    LEFT JOIN dwc_occurrences dwc ON (dwc.dwc_occurrence_object_id = co.id AND dwc.dwc_occurrence_object_type = 'CollectionObject' AND scope_co.occurrence_id IS NOT NULL)
      OR (dwc.dwc_occurrence_object_id = fo.id AND dwc.dwc_occurrence_object_type = 'FieldOccurrence' AND scope_fo.occurrence_id IS NOT NULL)
  SQL
end