module ActiveFacts::Generators::Traits::SQL::Postgres

Public Instance Methods

as_alpha(expr) click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 223
def as_alpha expr
  Expression.new("btrim(lower(regexp_replace(#{expr}, '[^[:alnum:]]+', ' ', 'g')))", MM::DataType::TYPE_String, expr.is_mandatory)
end
as_text(exprs) click to toggle source

Some or all of the SQL expressions may have non-text values. Return an SQL expression that coerces them to text.

# File lib/activefacts/generator/traits/sql/postgres.rb, line 137
def as_text exprs
  return exprs.map{|e| as_text(e)} if Array === exprs

  Expression.new("#{exprs}::text", MM::DataType::TYPE_String, exprs.is_mandatory)
end
as_words(expr, extra_word_chars = '') click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 227
def as_words expr, extra_word_chars = ''
  Expression.new(
    "regexp_split_to_array(lower(#{expr}), E'[^[:alnum:]#{extra_word_chars}]+')",
    MM::DataType::TYPE_String, expr.is_mandatory, true
  )
end
choose_sql_type(type_name, value_constraint, component, options) click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 63
def choose_sql_type(type_name, value_constraint, component, options)
  type = MM::DataType.intrinsic_type(type_name)
  case type
  when MM::DataType::TYPE_Integer
    # The :auto_assign key is set for auto-assigned types, but with a nil value in foreign keys
    if options.has_key?(:auto_assign)
      if options[:auto_assign]
        'BIGSERIAL' # This doesn't need an auto_increment default
      else
        'BIGINT'
      end
    else
      super
    end

  when MM::DataType::TYPE_Money
    'MONEY'

  when MM::DataType::TYPE_DateTime
    'TIMESTAMP'

  when MM::DataType::TYPE_Timestamp
    'TIMESTAMP'

  when MM::DataType::TYPE_Binary
    case binary_surrogate(type_name, value_constraint, options)
    when :guid_fk             # A surrogate that's auto-assigned elsewhere
      options[:length] = nil
      'UUID'
    when :guid                # A GUID
      # This requires the pgcrypto extension
      options[:length] = nil
      options[:default] = " DEFAULT gen_random_uuid()"
      'UUID'
    when :hash                # A hash of the natural key
      options.delete(:length) # 20 bytes, assuming SHA-1, but we don't need to specify it. SHA-256 would need 32 bytes
      options[:delayed] = trigger_hash_assignment(component, component.root.natural_index.all_index_field.map(&:component))
      'BYTEA'
    else                      # Not a surrogate
      options.delete(:length)
      'BYTEA'
    end

  else
    super
  end
end
close_escape() click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 287
def close_escape
  '"'
end
column_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 45
def column_name_max
  63
end
concatenate(expressions) click to toggle source

Return an SQL expression that concatenates the given expressions (which must yield a string type)

# File lib/activefacts/generator/traits/sql/postgres.rb, line 144
def concatenate expressions
  Expression.new(
    "'|'::text || " +
    expressions.map(&:to_s) * " || '|'::text || " +
    " || '|'::text",
    MM::DataType::TYPE_String
  )
end
data_type_context_class() click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 37
def data_type_context_class
  PostgresDataTypeContext
end
date_or_null(expr) click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 178
def date_or_null expr
  Expression.new(
    %Q{CASE WHEN #{col_expr} ~ '^ *[0-9]+[-/]?[0-9]+[-/][0-9]+ *$' THEN (#{col_expr}::date):text ELSE NULL END},
    MM::DataType::TYPE_Date,
    false
  )
end
defaults_and_options(options) click to toggle source

The options parameter overrides any default options set by sub-traits

# File lib/activefacts/generator/traits/sql/postgres.rb, line 28
def defaults_and_options options
  {'tables' => 'snake', 'columns' => 'snake'}.merge(options)
end
email_addresses(expr) click to toggle source

Extract separated numbers, remove non-digits, take the last 8 (removing area codes etc)

# File lib/activefacts/generator/traits/sql/postgres.rb, line 203
def email_addresses expr
  Expression.new(
    %Q{unnest(regexp_matches(#{expr}, E'[-_.[:alnum:]]+@[-_.[:alnum:]]+'))},
    MM::DataType::TYPE_String,
    true
  )
end
hash(expr, algo = 'sha1') click to toggle source

Return an expression that yields a hash of the given expression

# File lib/activefacts/generator/traits/sql/postgres.rb, line 154
def hash expr, algo = 'sha1'
  Expression.new("digest(#{expr}, '#{algo}')", MM::DataType::TYPE_Binary, expr.is_mandatory, expr.is_array)
end
index_kind(index) click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 291
def index_kind(index)
  ''
end
index_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 49
def index_name_max
  63
end
key_words() click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 256
def key_words
  # These keywords should not be used for columns or tables:
  @postgres_key_words ||= %w{
    ABORT ACCESS AGGREGATE ALSO BACKWARD CACHE CHECKPOINT
    CLASS CLUSTER COMMENT COMMENTS CONFIGURATION CONFLICT
    CONVERSION COPY COST CSV DATABASE DELIMITER DELIMITERS
    DICTIONARY DISABLE DISCARD ENABLE ENCRYPTED ENUM EVENT
    EXCLUSIVE EXPLAIN EXTENSION FAMILY FORCE FORWARD FUNCTIONS
    HEADER IMMUTABLE IMPLICIT INDEX INDEXES INHERIT INHERITS
    INLINE LABEL LEAKPROOF LISTEN LOAD LOCK LOCKED LOGGED
    MATERIALIZED MODE MOVE NOTHING NOTIFY NOWAIT OIDS
    OPERATOR OWNED OWNER PARSER PASSWORD PLANS POLICY
    PREPARED PROCEDURAL PROGRAM QUOTE REASSIGN RECHECK
    REFRESH REINDEX RENAME REPLACE REPLICA RESET RULE
    SEQUENCES SHARE SHOW SKIP SNAPSHOT STABLE STATISTICS
    STDIN STDOUT STORAGE STRICT SYSID TABLES TABLESPACE
    TEMP TEMPLATE TEXT TRUSTED TYPES UNENCRYPTED UNLISTEN
    UNLOGGED VACUUM VALIDATE VALIDATOR VIEWS VOLATILE
  }

  # These keywords cannot be used for type or functions (and should not for columns or tables)
  @postgres_key_words_func_type ||= %w{
    GREATEST LEAST SETOF XMLROOT
  }
  super + @postgres_key_words + @postgres_key_words_func_type
end
lexical_date(expr) click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 211
def lexical_date expr
  Expression.new("to_char(#{expr}, 'YYYY-MM-DD')", MM::DataType::TYPE_String, expr.is_mandatory)
end
lexical_datetime(expr) click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 215
def lexical_datetime expr
  Expression.new("to_char(#{expr}, 'YYYY-MM-DD HH24:MI:SS.US')", MM::DataType::TYPE_String, expr.is_mandatory)
end
lexical_time(expr) click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 219
def lexical_time expr
  Expression.new("to_char(#{expr}, 'HH24:MI:SS.US')", MM::DataType::TYPE_String, expr.is_mandatory)
end
number_or_null(expr) click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 169
def number_or_null expr
  # This doesn't handle all valid Postgres numeric literals (e.g. 2.3e-4)
  Expression.new(
    %Q{CASE WHEN #{expr} ~ '^ *[-+]?([0-9]+[.]?[0-9]*|[.][0-9]+) *$' THEN (#{expr}::numeric)::text ELSE NULL END},
    MM::DataType::TYPE_Real,
    false
  )
end
open_escape() click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 283
def open_escape
  '"'
end
options() click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 21
def options
  super.merge({
    # no: [String, "no new options defined here"]
  })
end
phone_numbers(expr) click to toggle source

Extract separated numbers, remove non-digits, take the last 8 (removing area codes etc)

# File lib/activefacts/generator/traits/sql/postgres.rb, line 194
def phone_numbers expr
  Expression.new(
    %Q{right(regexp_replace(#{split_on_separators(expr)}, '[^0-9]+', '', 'g'), 8)},
    MM::DataType::TYPE_String,
    true
  )
end
phonetics(expr) click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 238
def phonetics expr
  Expression.new(
    %Q{unnest(ARRAY[dmetaphone(#{expr}), dmetaphone_alt(#{expr})])},
    MM::DataType::TYPE_String,
    expr.is_mandatory
  )
end
process_options(options) click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 32
def process_options options
  # No extra options to process
  super
end
reserved_words() click to toggle source

Reserved words cannot be used anywhere without quoting. Keywords have existing definitions, so should not be used without quoting. Both lists here are added to the supertype's lists

# File lib/activefacts/generator/traits/sql/postgres.rb, line 249
def reserved_words
  @postgres_reserved_words ||= %w{
    ANALYSE ANALYZE LIMIT PLACING RETURNING SYMMETRIC VARIADIC
  }
  super + @postgres_reserved_words
end
schema_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 53
def schema_name_max
  63
end
schema_prefix() click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 57
def schema_prefix
  go('CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public') +
  go('CREATE EXTENSION IF NOT EXISTS fuzzystrmatch WITH SCHEMA public') +
  "\n"
end
split_on_separators(expr, seps = ',|') click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 186
def split_on_separators expr, seps = ',|'
  Expression.new(
    %Q{regexp_split_to_table(#{expr}, E'[#{seps}]')},
    MM::DataType::TYPE_String, true, true
  )
end
table_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 41
def table_name_max
  63
end
trigger_hash_assignment(hash_field, leaves) click to toggle source

Return an array of SQL statements that arrange for the hash_field to be populated with a hash of the values of the leaves.

# File lib/activefacts/generator/traits/sql/postgres.rb, line 113
def trigger_hash_assignment hash_field, leaves
  table_name = safe_table_name(hash_field.root)
  trigger_function = escape('assign_'+column_name(hash_field), 128)
  [
    %Q{
      CREATE OR REPLACE FUNCTION #{trigger_function}() RETURNS TRIGGER AS $$
      BEGIN
              NEW.#{safe_column_name(hash_field)} = #{
                hash(concatenate(coalesce(as_text(safe_column_exprs(leaves, 'NEW')))))
              };
              RETURN NEW;
      END
      $$ language 'plpgsql'}.
    unindent,
    %Q{
      CREATE TRIGGER trig_#{trigger_function}
              BEFORE INSERT OR UPDATE ON #{table_name}
              FOR EACH ROW EXECUTE PROCEDURE #{trigger_function}()}.
    unindent
  ]
end
trigram(expr) click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 162
def trigram expr
  # This is not a useful way to handle trigrams. Instead, create a trigram index
  # over an ordinary text index value, and use a similarity search over that.
  # Expression.new("show_trgm(#{expr})", MM::DataType::TYPE_String, expr.is_mandatory, true)
  expr
end
truncate(expr, length) click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 158
def truncate expr, length
  Expression.new("left(#{expr}, #{length})", MM::DataType::TYPE_String, expr.is_mandatory, expr.is_array)
end
unnest(expr) click to toggle source
# File lib/activefacts/generator/traits/sql/postgres.rb, line 234
def unnest expr
  Expression.new("unnest(#{expr})", MM::DataType::TYPE_String, expr.is_mandatory, true)
end