module ActiveFacts::Generators::Traits::SQL

Options are comma or space separated:

Options are comma or space separated:

Constants

MM

Public Instance Methods

binary_surrogate(type_name, value_constraint, options) click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 125
def binary_surrogate(type_name, value_constraint, options)
  if options[:auto_assign] == 'hash'
    :hash
  elsif type_name =~ /^(guid|uuid)$/i
    options[:length] ||= 16
    if ![nil, ''].include?(options[:auto_assign])
      options.delete(:auto_assign)  # Don't auto-assign foreign keys
      :guid
    else
      :guid_fk
    end
  else
    false
  end
end
check_clause(column_name, value_constraint) click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 430
def check_clause column_name, value_constraint
  " CHECK(" +
    value_constraint.all_allowed_range_sorted.map do |ar|
      vr = ar.value_range
      min = vr.minimum_bound
      max = vr.maximum_bound
      if (min && max && max.value.literal == min.value.literal)
        "#{column_name} = #{sql_value(min.value)}"
      else
        inequalities = [
          min && "#{column_name} >#{min.is_inclusive ? "=" : ""} #{sql_value(min.value)}",
          max && "#{column_name} <#{max.is_inclusive ? "=" : ""} #{sql_value(max.value)}"
        ].compact
        inequalities.size > 1 ? "(" + inequalities*" AND " + ")" : inequalities[0]
      end
    end*" OR " +
  ")"
end
choose_sql_type(type_name, value_constraint, component, options) click to toggle source

Return SQL type and (modified?) length for the passed base type

# File lib/activefacts/generator/traits/sql.rb, line 142
def choose_sql_type(type_name, value_constraint, component, options)
  case MM::DataType.intrinsic_type(type_name)
  when MM::DataType::TYPE_Boolean
    data_type_context.boolean_type

  when MM::DataType::TYPE_Integer
    # The :auto_assign key is set for auto-assigned types, but with a nil value in foreign keys
    length = options[:length]
    if options.has_key?(:auto_assign)
      options[:default] ||= ' GENERATED ALWAYS AS IDENTITY' if options[:auto_assign]
      length = data_type_context.default_autoincrement_length
      type_name = 'int'
    end
    if chosen = MM::DataType.choose_integer(type_name, length, value_constraint, data_type_context)
      options.delete(:length)
      chosen
    else  # No available integer seems to suit. Use the defined type and length
      type_name
    end

  when MM::DataType::TYPE_Real
    'FLOAT'

  when MM::DataType::TYPE_Decimal
    'DECIMAL'

  when MM::DataType::TYPE_Money
    'DECIMAL'

  when MM::DataType::TYPE_Char
    data_type_context.default_char_type

  when MM::DataType::TYPE_String
    data_type_context.default_varchar_type

  when MM::DataType::TYPE_Text
    options[:length] ||= 'MAX'
    data_type_context.default_text_type

  when MM::DataType::TYPE_Date
    'DATE'

  when MM::DataType::TYPE_Time
    'TIME'

  when MM::DataType::TYPE_DateTime
    'TIMESTAMP'

  when MM::DataType::TYPE_Timestamp
    'TIMESTAMP'

  when MM::DataType::TYPE_Binary
    # If it's a surrogate, that might change the length we use
    binary_surrogate(type_name, value_constraint, options)
    if options[:length]
      'BINARY'          # Fixed length
    else
      'VARBINARY'
    end
  else
    type_name
  end
end
close_escape() click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 390
def close_escape
  '"'
end
coalesce(exprs, na = "'NA'") click to toggle source

For an (array of) Expression, return expressions that have value “na” if NULL

# File lib/activefacts/generator/traits/sql.rb, line 243
def coalesce exprs, na = "'NA'"
  return exprs.map{|expr| coalesce(expr)} if Array === exprs
  return exprs if exprs.is_mandatory
  Expression.new("COALESCE(#{exprs}, #{na})", exprs.type_num, true)
end
column_name(component) click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 425
def column_name component
  words = component.column_name.send(@column_case)
  words*@column_joiner
end
column_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 104
def column_name_max
  1024
end
compatibility() click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 39
def compatibility
  [1, %i{relational}]
end
create_or_replace(name, kind) click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 237
def create_or_replace(name, kind)
  # There's no standard SQL way to do this. Do it anyway.
  "CREATE OR REPLACE #{kind} #{name}"
end
data_type_context() click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 92
def data_type_context
  @data_type_context ||= data_type_context_class.new(surrogate_method: @surrogate_method)
end
data_type_context_class() click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 96
def data_type_context_class
  SQLDataTypeContext
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.rb, line 44
def defaults_and_options options
  options
end
escape(s, max = table_name_max) click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 394
def escape s, max = table_name_max
  # Escape SQL keywords and non-identifiers
  if s.size > max
    excess = s[max..-1]
    s = s[0...max-(excess.size/8)] +
      Digest::SHA1.hexdigest(excess)[0...excess.size/8]
  end

  if s =~ /[^A-Za-z0-9_]/ || is_reserved_word(s)
    "#{open_escape}#{s}#{close_escape}"
  else
    s
  end
end
go(s = '') click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 374
def go s = ''
  "#{s.sub(/\A\n+/,'')};\n"
end
index_kind(index) click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 121
def index_kind(index)
  ''
end
index_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 108
def index_name_max
  1024
end
is_reserved_word(w) click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 363
def is_reserved_word w
  @reserved_word_hash ||=
    ( reserved_words +
      (@quote_keywords ? key_words : [])).
    inject({}) do |h,w|
      h[w] = true
      h
    end
  @reserved_word_hash[w.upcase]
end
key_words() click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 318
def key_words
  @key_words ||= %w{
    A ABSENT ACCORDING ADA ADMIN AFTER ALWAYS ASSIGNMENT
    ATTRIBUTE ATTRIBUTES BASE64 BEFORE BERNOULLI BLOCKED BOM
    BREADTH C CATALOG_NAME CHAIN CHARACTERISTICS CHARACTERS
    CHARACTER_SET_CATALOG CHARACTER_SET_NAME CHARACTER_SET_SCHEMA
    CLASS_ORIGIN COBOL COLLATION_CATALOG COLLATION_NAME
    COLLATION_SCHEMA COLUMNS COLUMN_NAME COMMAND_FUNCTION
    COMMAND_FUNCTION_CODE COMMITTED CONDITION_NUMBER
    CONNECTION_NAME CONSTRAINT_CATALOG CONSTRAINT_NAME
    CONSTRAINT_SCHEMA CONSTRUCTOR CONTENT CONTROL CURSOR_NAME
    DATA DATETIME_INTERVAL_CODE DATETIME_INTERVAL_PRECISION
    DB DEFAULTS DEFINED DEFINER DEGREE DEPTH DERIVED DISPATCH
    DOCUMENT DYNAMIC_FUNCTION DYNAMIC_FUNCTION_CODE EMPTY
    ENCODING ENFORCED EXCLUDE EXCLUDING EXPRESSION FILE FINAL
    FLAG FOLLOWING FORTRAN FS G GENERAL GENERATED GRANTED HEX
    HIERARCHY ID IGNORE IMMEDIATELY IMPLEMENTATION INCLUDING
    INCREMENT INDENT INSTANCE INSTANTIABLE INSTEAD INTEGRITY
    INVOKER K KEY_MEMBER KEY_TYPE LENGTH LIBRARY LIMIT LINK
    LOCATION LOCATOR M MAP MAPPING MATCHED MAXVALUE MESSAGE_LENGTH
    MESSAGE_OCTET_LENGTH MESSAGE_TEXT MINVALUE MORE MUMPS
    NAME NAMESPACE NESTING NFC NFD NFKC NFKD NIL NORMALIZED
    NULLABLE NULLS NUMBER OBJECT OCTETS OFF OPTIONS ORDERING
    ORDINALITY OTHERS OVERRIDING P PARAMETER_MODE PARAMETER_NAME
    PARAMETER_ORDINAL_POSITION PARAMETER_SPECIFIC_CATALOG
    PARAMETER_SPECIFIC_NAME PARAMETER_SPECIFIC_SCHEMA PASCAL
    PASSING PASSTHROUGH PATH PERMISSION PLACING PLI PRECEDING
    RECOVERY REPEATABLE REQUIRING RESPECT RESTART RESTORE
    RETURNED_CARDINALITY RETURNED_LENGTH RETURNED_OCTET_LENGTH
    RETURNED_SQLSTATE RETURNING ROLE ROUTINE ROUTINE_CATALOG
    ROUTINE_NAME ROUTINE_SCHEMA ROW_COUNT SCALE SCHEMA_NAME
    SCOPE_CATALOG SCOPE_NAME SCOPE_SCHEMA SECURITY SELECTIVE
    SELF SEQUENCE SERIALIZABLE SERVER SERVER_NAME SETS SIMPLE
    SOURCE SPECIFIC_NAME STANDALONE STATE STATEMENT STRIP
    STRUCTURE STYLE SUBCLASS_ORIGIN T TABLE_NAME TIES TOKEN
    TOP_LEVEL_COUNT TRANSACTIONS_COMMITTED TRANSACTIONS_ROLLED_BACK
    TRANSACTION_ACTIVE TRANSFORM TRANSFORMS TRIGGER_CATALOG
    TRIGGER_NAME TRIGGER_SCHEMA TYPE UNBOUNDED UNCOMMITTED
    UNDER UNLINK UNNAMED UNTYPED URI USER_DEFINED_TYPE_CATALOG
    USER_DEFINED_TYPE_CODE USER_DEFINED_TYPE_NAME
    USER_DEFINED_TYPE_SCHEMA VALID VERSION WHITESPACE WRAPPER
    XMLDECLARATION XMLSCHEMA YES
  }
end
open_escape() click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 386
def open_escape
  '"'
end
options() click to toggle source

Options available in this flavour of SQL

# File lib/activefacts/generator/traits/sql.rb, line 25
def options
  {
    keywords: ['Boolean', "Quote all keywords, not just reserved words"],
    restrict: ['String', "Restrict generation to tables in the specified group (e.g. bdv, rdv)"],
    joiner: ['String', "Use 'str' instead of the default joiner between words in table and column names"],
    unicode: ['Boolean', "Use Unicode for all text fields by default"],
    tables: [%w{cap title camel snake shout}, "Case to use for table names"],
    columns: [%w{cap title camel snake shout}, "Case to use for table names"],
    surrogates: [%w{counter guid hash}, "Method to use for assigning surrogate keys"],
    fks: [%w{no yes delay}, "Emit foreign keys, delay them to the end, or omit them"],
    # Legacy: datavault: ['String', "Generate 'raw' or 'business' data vault tables"],
  }
end
process_options(options) click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 48
def process_options options
  @options = defaults_and_options options

  @quote_keywords = {nil=>true, 't'=>true, 'f'=>false, 'y'=>true, 'n'=>false}[@options.delete 'keywords']
  @quote_keywords = false if @keywords == nil  # Set default
  case (@options.delete "fks" || true)
  when true, '', 't', 'y', 'yes'
    @fks = true
  when 'd', 'delay'
    @fks = true
    @delay_fks = true
  when false, 'f', 'n', 'no'
    @fks = false
  end
  @unicode = @options.delete "unicode"
  @restrict = @options.delete "restrict"
  @surrogate_method = @options.delete('surrogates') || 'counter'
  raise "Unknown surrogate assignment method" unless %w{counter guid hash}.include?(@surrogate_method)

  # Name configuration options:
  @joiner = @options.delete('joiner')
  @table_joiner = @options.has_key?('tables') ? @joiner : nil
  @table_case = ((@options.delete('tables') || 'cap') + 'words').to_sym
  @table_joiner ||= [:snakewords, :shoutwords].include?(@table_case) ? '_' : ''
  @column_joiner = @options.has_key?('columns') ? @joiner : nil
  @column_case = ((@options.delete('columns') || 'cap') + 'words').to_sym
  @column_joiner ||= [:snakewords, :shoutwords].include?(@column_case) ? '_' : ''

  # Legacy option. Use restrict=bdv/rdv instead
  @datavault = @options.delete "datavault"
  case @datavault
  when "business"
    @restrict = "bdv"
  when "raw"
    @restrict = "rdv"
  end

  # Do not (yet) expose the closed-world vs open world problem.
  # Closed World vs Open World uniqueness is a semantic issue,
  # and so is OW, CW or CW with negation for unary fact types.
  # We need an overall strategy for handling it.
  @closed_world_indices = false   # Allow for SQL Server's non-standard NULL indexing
end
reserved_words() click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 249
def reserved_words
  @reserved_words ||= %w{
    ABS ABSOLUTE ACTION ADD ALL ALLOCATE ALTER AND ANY ARE
    ARRAY ARRAY_AGG ARRAY_MAX_CARDINALITY AS ASC ASENSITIVE
    ASSERTION ASYMMETRIC AT ATOMIC AUTHORIZATION AVG BEGIN
    BEGIN_FRAME BEGIN_PARTITION BETWEEN BIGINT BINARY BIT
    BIT_LENGTH BLOB BOOLEAN BOTH BY CALL CALLED CARDINALITY
    CASCADE CASCADED CASE CAST CATALOG CEIL CEILING CHAR
    CHARACTER CHARACTER_LENGTH CHAR_LENGTH CHECK CLOB CLOSE
    COALESCE COLLATE COLLATION COLLECT COLUMN COMMIT CONDITION
    CONNECT CONNECTION CONSTRAINT CONSTRAINTS CONTAINS CONTINUE
    CONVERT CORR CORRESPONDING COUNT COVAR_POP COVAR_SAMP
    CREATE CROSS CUBE CUME_DIST CURRENT CURRENT_CATALOG
    CURRENT_DATE CURRENT_DEFAULT_TRANSFORM_GROUP CURRENT_PATH
    CURRENT_ROLE CURRENT_ROW CURRENT_SCHEMA CURRENT_TIME
    CURRENT_TIMESTAMP CURRENT_TRANSFORM_GROUP_FOR_TYPE
    CURRENT_USER CURSOR CYCLE DATALINK DATE DAY DEALLOCATE
    DEC DECIMAL DECLARE DEFAULT DEFERRABLE DEFERRED DELETE
    DENSE_RANK DEREF DESC DESCRIBE DESCRIPTOR DETERMINISTIC
    DIAGNOSTICS DISCONNECT DISTINCT DLNEWCOPY DLPREVIOUSCOPY
    DLURLCOMPLETE DLURLCOMPLETEONLY DLURLCOMPLETEWRITE DLURLPATH
    DLURLPATHONLY DLURLPATHWRITE DLURLSCHEME DLURLSERVER
    DLVALUE DO DOMAIN DOUBLE DROP DYNAMIC EACH ELEMENT ELSE
    ELSEIF END END-EXEC END_FRAME END_PARTITION EQUALS ESCAPE
    EVERY EXCEPT EXCEPTION EXEC EXECUTE EXISTS EXIT EXP
    EXTERNAL EXTRACT FALSE FETCH FILTER FIRST FIRST_VALUE
    FLOAT FLOOR FOR FOREIGN FOUND FRAME_ROW FREE FROM FULL
    FUNCTION FUSION GET GLOBAL GO GOTO GRANT GROUP GROUPING
    GROUPS HANDLER HAVING HOLD HOUR IDENTITY IF IMMEDIATE
    IMPORT IN INDICATOR INITIALLY INNER INOUT INPUT INSENSITIVE
    INSERT INT INTEGER INTERSECT INTERSECTION INTERVAL INTO
    IS ISOLATION ITERATE JOIN KEY LAG LANGUAGE LARGE LAST
    LAST_VALUE LATERAL LEAD LEADING LEAVE LEFT LEVEL LIKE
    LIKE_REGEX LN LOCAL LOCALTIME LOCALTIMESTAMP LOOP LOWER
    MATCH MAX MAX_CARDINALITY MEMBER MERGE METHOD MIN MINUTE
    MOD MODIFIES MODULE MONTH MULTISET NAMES NATIONAL NATURAL
    NCHAR NCLOB NEW NEXT NO NONE NORMALIZE NOT NTH_VALUE NTILE
    NULL NULLIF NUMERIC OCCURRENCES_REGEX OCTET_LENGTH OF
    OFFSET OLD ON ONLY OPEN OPTION OR ORDER OUT OUTER OUTPUT
    OVER OVERLAPS OVERLAY PAD PARAMETER PARTIAL PARTITION
    PERCENT PERCENTILE_CONT PERCENTILE_DISC PERCENT_RANK
    PERIOD PORTION POSITION POSITION_REGEX POWER PRECEDES
    PRECISION PREPARE PRESERVE PRIMARY PRIOR PRIVILEGES
    PROCEDURE PUBLIC RANGE RANK READ READS REAL RECURSIVE REF
    REFERENCES REFERENCING REGR_AVGX REGR_AVGY REGR_COUNT
    REGR_INTERCEPT REGR_R2 REGR_SLOPE REGR_SXX REGR_SXY
    REGR_SYY RELATIVE RELEASE REPEAT RESIGNAL RESTRICT RESULT
    RETURN RETURNS REVOKE RIGHT ROLLBACK ROLLUP ROW ROWS
    ROW_NUMBER SAVEPOINT SCHEMA SCOPE SCROLL SEARCH SECOND
    SECTION SELECT SENSITIVE SESSION SESSION_USER SET SIGNAL
    SIMILAR SIZE SMALLINT SOME SPACE SPECIFIC SPECIFICTYPE
    SQL SQLCODE SQLERROR SQLEXCEPTION SQLSTATE SQLWARNING
    SQRT START STATIC STDDEV_POP STDDEV_SAMP SUBMULTISET
    SUBSTRING SUBSTRING_REGEX SUCCEEDS SUM SYMMETRIC SYSTEM
    SYSTEM_TIME SYSTEM_USER TABLE TABLESAMPLE TEMPORARY THEN
    TIME TIMESTAMP TIMEZONE_HOUR TIMEZONE_MINUTE TO TRAILING
    TRANSACTION TRANSLATE TRANSLATE_REGEX TRANSLATION TREAT
    TRIGGER TRIM TRIM_ARRAY TRUE TRUNCATE UESCAPE UNDO UNION
    UNIQUE UNKNOWN UNNEST UNTIL UPDATE UPPER USAGE USER USING
    VALUE VALUES VALUE_OF VARBINARY VARCHAR VARYING VAR_POP
    VAR_SAMP VERSIONING VIEW WHEN WHENEVER WHERE WHILE
    WIDTH_BUCKET WINDOW WITH WITHIN WITHOUT WORK WRITE XML
    XMLAGG XMLATTRIBUTES XMLBINARY XMLCAST XMLCOMMENT XMLCONCAT
    XMLDOCUMENT XMLELEMENT XMLEXISTS XMLFOREST XMLITERATE
    XMLNAMESPACES XMLPARSE XMLPI XMLQUERY XMLSERIALIZE XMLTABLE
    XMLTEXT XMLVALIDATE YEAR ZONE
  }
end
safe_column_expr(leaf, table_prefix = '') click to toggle source

Return an Expression for the Component passed as leaf, optionally using a table or alias name

# File lib/activefacts/generator/traits/sql.rb, line 213
def safe_column_expr leaf, table_prefix = ''
  column_name = safe_column_name(leaf)
  type_name, = leaf.data_type(data_type_context)
  type_num = MM::DataType.intrinsic_type(type_name)
  Expression.new(table_prefix+column_name, type_num, leaf.is_mandatory)
end
safe_column_exprs(leaves, use_table_name = nil) click to toggle source

Return an array of Expressions for the fields, optionally qualified with a table or alias name

# File lib/activefacts/generator/traits/sql.rb, line 221
def safe_column_exprs leaves, use_table_name = nil
  leaves.map{|leaf| safe_column_expr(leaf, table_prefix(leaf, use_table_name))}
end
safe_column_name(component) click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 413
def safe_column_name component
  escape(column_name(component), column_name_max)
end
safe_column_names(leaves) click to toggle source

The Components passed as leaves are fields in a table. Return an array of SQL field names.

# File lib/activefacts/generator/traits/sql.rb, line 208
def safe_column_names leaves
  leaves.map &method(:safe_column_name)
end
safe_table_name(composite) click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 409
def safe_table_name composite
  escape(table_name(composite), table_name_max)
end
schema_name(composition = @composition) click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 417
def schema_name composition = @composition
  composition.name.words.send(@table_case)*@table_joiner
end
schema_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 112
def schema_name_max
  1024
end
schema_prefix() click to toggle source

Anything this flavour needs to prefix a schema:

# File lib/activefacts/generator/traits/sql.rb, line 117
def schema_prefix
  ''
end
sql_string(str) click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 382
def sql_string(str)
  "'" + str.gsub(/'/,"''") + "'"
end
sql_value(value) click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 378
def sql_value(value)
  value.is_literal_string ? sql_string(value.literal) : value.literal
end
table_name(composite) click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 421
def table_name composite
  composite.mapping.name.words.send(@table_case)*@table_joiner
end
table_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql.rb, line 100
def table_name_max
  1024
end
table_prefix(component, use_table_name = nil) click to toggle source

Return the string to prefix a column expression with to qualify it with a table or alias name

# File lib/activefacts/generator/traits/sql.rb, line 226
def table_prefix component, use_table_name = nil
  case use_table_name
  when false, nil
    ''
  when true
    safe_table_name(component)+'.'
  else
    use_table_name+'.'
  end
end