module ActiveFacts::Generators::Traits::SQL::Postgres
Public Instance Methods
# 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
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
# 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
ActiveFacts::Generators::Traits::SQL#choose_sql_type
# 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
# File lib/activefacts/generator/traits/sql/postgres.rb, line 287 def close_escape '"' end
# File lib/activefacts/generator/traits/sql/postgres.rb, line 45 def column_name_max 63 end
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
# File lib/activefacts/generator/traits/sql/postgres.rb, line 37 def data_type_context_class PostgresDataTypeContext end
# 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
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
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
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
# File lib/activefacts/generator/traits/sql/postgres.rb, line 291 def index_kind(index) '' end
# File lib/activefacts/generator/traits/sql/postgres.rb, line 49 def index_name_max 63 end
ActiveFacts::Generators::Traits::SQL#key_words
# 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
# 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
# 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
# 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
# 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
# File lib/activefacts/generator/traits/sql/postgres.rb, line 283 def open_escape '"' end
ActiveFacts::Generators::Traits::SQL#options
# File lib/activefacts/generator/traits/sql/postgres.rb, line 21 def options super.merge({ # no: [String, "no new options defined here"] }) end
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
# 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
ActiveFacts::Generators::Traits::SQL#process_options
# File lib/activefacts/generator/traits/sql/postgres.rb, line 32 def process_options options # No extra options to process super end
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
ActiveFacts::Generators::Traits::SQL#reserved_words
# 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
# File lib/activefacts/generator/traits/sql/postgres.rb, line 53 def schema_name_max 63 end
# 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
# 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
# File lib/activefacts/generator/traits/sql/postgres.rb, line 41 def table_name_max 63 end
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
# 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
# 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
# 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