Module: Export::Dwca::Occurrence::PostgresqlFunctions

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

Overview

PostgreSQL temporary function creators for DwC-A export These functions exist only for the current database session

Instance Method Summary collapse

Instance Method Details

Creates a temporary PostgreSQL function that builds API links for models, matching Shared::Api.api_link_for_model_id Handles all current dwc_occurrence_object_type values:

- AssertedDistribution
- CollectionObject
- FieldOccurrence

The function exists only for the current database session.

Examples:

api_link_for_model_id('CollectionObject', 123) → 'http://host/api/v1/collection_objects/123'
api_link_for_model_id('FieldOccurrence', 456) → 'http://host/api/v1/field_occurrences/456'


50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
# File 'lib/export/dwca/occurrence/postgresql_functions.rb', line 50

def create_api_link_for_model_id_function
  conn = ActiveRecord::Base.connection
  host = conn.quote(Shared::Api.host)
  conn.execute(<<~SQL)
    CREATE OR REPLACE FUNCTION pg_temp.api_link_for_model_id(model_type text, model_id integer)
    RETURNS text AS $$
      SELECT CASE
        WHEN model_type IS NULL OR model_id IS NULL THEN NULL
        WHEN model_type = 'AssertedDistribution' THEN #{host} || '/api/v1/asserted_distributions/' || model_id
        WHEN model_type = 'CollectionObject' THEN #{host} || '/api/v1/collection_objects/' || model_id
        WHEN model_type = 'FieldOccurrence' THEN #{host} || '/api/v1/field_occurrences/' || model_id
        ELSE NULL
      END
    $$ LANGUAGE SQL IMMUTABLE;
  SQL
end

#create_authorship_sentence_function ⇒ Object

Creates a temporary PostgreSQL function that formats an array of names into a grammatically correct sentence, matching Utilities::Strings.authorship_sentence Examples:

1 name: "Smith"
2 names: "Smith & Jones"
3+ names: "Smith, Jones & Brown"

The function exists only for the current database session.



25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
# File 'lib/export/dwca/occurrence/postgresql_functions.rb', line 25

def create_authorship_sentence_function
  conn = ActiveRecord::Base.connection
  conn.execute(<<~SQL)
    CREATE OR REPLACE FUNCTION pg_temp.authorship_sentence(names text[])
    RETURNS text AS $$
      SELECT CASE
        WHEN array_length(names, 1) IS NULL THEN NULL
        WHEN array_length(names, 1) = 1 THEN names[1]
        WHEN array_length(names, 1) = 2 THEN names[1] || ' & ' || names[2]
        ELSE array_to_string(names[1:array_length(names,1)-1], ', ') || ' & ' || names[array_length(names,1)]
      END
    $$ LANGUAGE SQL IMMUTABLE;
  SQL
end

#create_csv_sanitize_function ⇒ Object

Creates a temporary PostgreSQL function that sanitizes CSV values by replacing newlines and tabs with spaces The function exists only for the current database session.



8
9
10
11
12
13
14
15
16
 
# File 'lib/export/dwca/occurrence/postgresql_functions.rb', line 8

def create_csv_sanitize_function
  conn = ActiveRecord::Base.connection
  conn.execute(<<~SQL)
    CREATE OR REPLACE FUNCTION pg_temp.sanitize_csv(text)
    RETURNS text AS $$
      SELECT REGEXP_REPLACE($1, E'[\\n\\t]', ' ', 'g')
    $$ LANGUAGE SQL IMMUTABLE;
  SQL
end

#create_image_file_url_function(conn, image_file_prefix) ⇒ Object

Regular image file URL (fingerprint-based)



81
82
83
84
85
86
87
88
 
# File 'lib/export/dwca/occurrence/postgresql_functions.rb', line 81

def create_image_file_url_function(conn, image_file_prefix)
  conn.execute(<<~SQL)
    CREATE OR REPLACE FUNCTION pg_temp.image_file_url(fingerprint text, token text)
    RETURNS text AS $$
      SELECT #{image_file_prefix} || fingerprint || '?project_token=' || token
    $$ LANGUAGE SQL IMMUTABLE;
  SQL
end

#create_image_metadata_url_function(conn, image_metadata_prefix) ⇒ Object

Image metadata URL



91
92
93
94
95
96
97
98
 
# File 'lib/export/dwca/occurrence/postgresql_functions.rb', line 91

def create_image_metadata_url_function(conn, image_metadata_prefix)
  conn.execute(<<~SQL)
    CREATE OR REPLACE FUNCTION pg_temp.image_metadata_url(image_id integer, token text)
    RETURNS text AS $$
      SELECT #{image_metadata_prefix} || image_id::text || '?project_token=' || token
    $$ LANGUAGE SQL IMMUTABLE;
  SQL
end

#create_image_url_functions ⇒ Object

Creates temporary PostgreSQL functions for generating image URLs matching Shared::Api methods. The functions exist only for the current database session.



70
71
72
73
74
75
76
77
78
 
# File 'lib/export/dwca/occurrence/postgresql_functions.rb', line 70

def create_image_url_functions
  conn = ActiveRecord::Base.connection
  image_file_prefix = conn.quote(Shared::Api.image_file_url_prefix)
  image_metadata_prefix = conn.quote(Shared::Api.image_metadata_url_prefix)

  create_image_file_url_function(conn, image_file_prefix)
  create_image_metadata_url_function(conn, image_metadata_prefix)
  create_sled_image_file_url_function(conn, image_file_prefix)
end

#create_sled_image_file_url_function(conn, image_file_prefix) ⇒ Object

Sled image file URL (fingerprint-based with crop coordinates)



101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
 
# File 'lib/export/dwca/occurrence/postgresql_functions.rb', line 101

def create_sled_image_file_url_function(conn, image_file_prefix)
  conn.execute(<<~SQL)
    CREATE OR REPLACE FUNCTION pg_temp.sled_image_file_url(fingerprint text, svg_view_box text, token text)
    RETURNS text AS $$
      SELECT #{image_file_prefix} || fingerprint || '/scale_to_box/'
        || SPLIT_PART(svg_view_box, ' ', 1)::numeric::integer::text || '/'
        || SPLIT_PART(svg_view_box, ' ', 2)::numeric::integer::text || '/'
        || SPLIT_PART(svg_view_box, ' ', 3)::numeric::integer::text || '/'
        || SPLIT_PART(svg_view_box, ' ', 4)::numeric::integer::text || '/'
        || SPLIT_PART(svg_view_box, ' ', 3)::numeric::integer::text || '/'
        || SPLIT_PART(svg_view_box, ' ', 4)::numeric::integer::text
        || '?project_token=' || token
    $$ LANGUAGE SQL IMMUTABLE;
  SQL
end