module ActiveFacts::Generators::Traits::SQL::MySQL

Public Instance Methods

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/mysql.rb, line 125
def as_text exprs
  return exprs.map{|e| as_text(e)} if Array === exprs

  Expression.new("CAST(#{exprs} AS CHAR)", MM::DataType::TYPE_String, exprs.is_mandatory)
end
choose_sql_type(type_name, value_constraint, component, options) click to toggle source
# File lib/activefacts/generator/traits/sql/mysql.rb, line 66
def choose_sql_type(type_name, value_constraint, component, options)
  case MM::DataType.intrinsic_type(type_name)
  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)
      options[:default] = ' AUTO_INCREMENT' if options[:auto_assign]
      'BIGINT'
    else
      super
    end

  when MM::DataType::TYPE_Money
    'DECIMAL'

  when MM::DataType::TYPE_DateTime
    'DATETIME'

  when MM::DataType::TYPE_Timestamp
    'DATETIME'

  when MM::DataType::TYPE_Binary
    case binary_surrogate(type_name, value_constraint, options)
    when :guid_fk             # A GUID surrogate that's auto-assigned elsewhere
      options[:length] = 16
    when :guid                # A GUID
      options[:length] = 16
      options[:default] = " DEFAULT UNHEX(REPLACE(UUID(),'-',''))"
    when :hash                # A hash of the natural key
      options[:length] = 20   # Assuming SHA-1. SHA-256 would need 32 bytes
      leaves = component.root.natural_index.all_index_field.map(&:component)
      # NDB Only, not InnoDB:
      # options[:default] = " GENERATED ALWAYS AS (#{hash(concatenate(coalesce(as_text(safe_column_exprs(leaves)))))}) STORED"
      options[:delayed] = trigger_hash_assignment(component, component.root.natural_index.all_index_field.map(&:component))
    else                      # Not a surrogate
      # MySQL has various non-standard blob types also
    end
    'BINARY'

  else
    super
  end
end
close_escape() click to toggle source
# File lib/activefacts/generator/traits/sql/mysql.rb, line 233
def close_escape
  '`'
end
column_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql/mysql.rb, line 46
def column_name_max
  64
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/mysql.rb, line 132
def concatenate expressions
  Expression.new(
    "CONCAT('|', #{expressions.map(&:to_s)*', '})",
    MM::DataType::TYPE_String,
    true
  )
end
data_type_context_class() click to toggle source
# File lib/activefacts/generator/traits/sql/mysql.rb, line 37
def data_type_context_class
  MySQLDataTypeContext
end
defaults_and_options(options) click to toggle source
# File lib/activefacts/generator/traits/sql/mysql.rb, line 29
def defaults_and_options options
  super
end
hash(expr, algo = nil) click to toggle source

Return an expression that yields a hash of the given expression SHA1 produces 40 hexadecimal digits

# File lib/activefacts/generator/traits/sql/mysql.rb, line 142
def hash expr, algo = nil
  Expression.new("UNHEX(SHA1(#{expr}))", MM::DataType::TYPE_Binary, expr.is_mandatory)
end
index_kind(index) click to toggle source
# File lib/activefacts/generator/traits/sql/mysql.rb, line 62
def index_kind(index)
  super
end
index_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql/mysql.rb, line 50
def index_name_max
  64
end
key_words() click to toggle source
# File lib/activefacts/generator/traits/sql/mysql.rb, line 171
def key_words
  # These keywords should not be used for columns or tables:
  @mysql_key_words ||= %w{
    ACCOUNT AGAINST AGGREGATE ALGORITHM ANALYSE ASCII
    AUTOEXTEND_SIZE AUTO_INCREMENT AVG_ROW_LENGTH BACKUP
    BINLOG BLOCK BOOL BTREE BYTE CACHE CHANGED CHANNEL
    CHARSET CHECKSUM CIPHER CLIENT CODE COLUMN_FORMAT COMMENT
    COMPACT COMPLETION COMPRESSED COMPRESSION CONCURRENT
    CONSISTENT CONTEXT CPU DATAFILE DATETIME DEFAULT_AUTH
    DELAY_KEY_WRITE DES_KEY_FILE DIRECTORY DISABLE DISCARD
    DISK DUMPFILE DUPLICATE ENABLE ENCRYPTION ENDS ENGINE
    ENGINES ENUM ERROR ERRORS EVENT EVENTS EXCHANGE EXPANSION
    EXPIRE EXPORT EXTENDED EXTENT_SIZE FAST FAULTS FIELDS
    FILE_BLOCK_SIZE FIXED FLUSH FOLLOWS FORMAT GEOMETRY
    GEOMETRYCOLLECTION GET_FORMAT GRANTS GROUP_REPLICATION
    HASH HELP HOST HOSTS IDENTIFIED IGNORE_SERVER_IDS INDEXES
    INITIAL_SIZE INSERT_METHOD INSTALL IO IO_THREAD IPC
    ISSUER JSON KEY_BLOCK_SIZE LEAVES LESS LINESTRING LIST
    LOCKS LOGFILE LOGS MASTER MASTER_AUTO_POSITION
    MASTER_CONNECT_RETRY MASTER_DELAY MASTER_HEARTBEAT_PERIOD
    MASTER_HOST MASTER_LOG_FILE MASTER_LOG_POS MASTER_PASSWORD
    MASTER_PORT MASTER_RETRY_COUNT MASTER_SERVER_ID MASTER_SSL
    MASTER_SSL_CA MASTER_SSL_CAPATH MASTER_SSL_CERT
    MASTER_SSL_CIPHER MASTER_SSL_CRL MASTER_SSL_CRLPATH
    MASTER_SSL_KEY MASTER_TLS_VERSION MASTER_USER
    MAX_CONNECTIONS_PER_HOUR MAX_QUERIES_PER_HOUR MAX_ROWS
    MAX_SIZE MAX_STATEMENT_TIME MAX_UPDATES_PER_HOUR
    MAX_USER_CONNECTIONS MEDIUM MEMORY MICROSECOND MIGRATE
    MIN_ROWS MODE MODIFY MULTILINESTRING MULTIPOINT
    MULTIPOLYGON MUTEX MYSQL_ERRNO NDB NDBCLUSTER NEVER
    NODEGROUP NONBLOCKING NO_WAIT NVARCHAR OLD_PASSWORD ONE
    OWNER PACK_KEYS PAGE PARSER PARSE_GCOL_EXPR PARTITIONING
    PARTITIONS PASSWORD PHASE PLUGIN PLUGINS PLUGIN_DIR
    POINT POLYGON PORT PREV PROCESSLIST PROFILE PROFILES
    PROXY QUARTER QUERY QUICK READ_ONLY REBUILD RECOVER
    REDOFILE REDO_BUFFER_SIZE REDUNDANT RELAY RELAYLOG
    RELAY_LOG_FILE RELAY_LOG_POS RELAY_THREAD RELOAD REMOVE
    REORGANIZE REPAIR REPLICATE_DO_DB REPLICATE_DO_TABLE
    REPLICATE_IGNORE_DB REPLICATE_IGNORE_TABLE REPLICATE_REWRITE_DB
    REPLICATE_WILD_DO_TABLE REPLICATE_WILD_IGNORE_TABLE
    REPLICATION RESET RESUME REVERSE ROTATE ROW_FORMAT RTREE
    SCHEDULE SERIAL SHARE SHUTDOWN SIGNED SLAVE SLOW SNAPSHOT
    SOCKET SONAME SOUNDS SQL_AFTER_GTIDS SQL_AFTER_MTS_GAPS
    SQL_BEFORE_GTIDS SQL_BUFFER_RESULT SQL_CACHE SQL_NO_CACHE
    SQL_THREAD SQL_TSI_DAY SQL_TSI_HOUR SQL_TSI_MINUTE
    SQL_TSI_MONTH SQL_TSI_QUARTER SQL_TSI_SECOND SQL_TSI_WEEK
    SQL_TSI_YEAR STACKED STARTS STATS_AUTO_RECALC
    STATS_PERSISTENT STATS_SAMPLE_PAGES STATUS STOP STORAGE
    STRING SUBJECT SUBPARTITION SUBPARTITIONS SUPER SUSPEND
    SWAPS SWITCHES TABLES TABLESPACE TABLE_CHECKSUM TEMPTABLE
    TEXT THAN TIMESTAMPADD TIMESTAMPDIFF TRIGGERS TYPES
    UNDEFINED UNDOFILE UNDO_BUFFER_SIZE UNICODE UNINSTALL
    UPGRADE USER_RESOURCES USE_FRM VALIDATION VARIABLES
    WAIT WARNINGS WEEK WEIGHT_STRING X509 XA XID
  }
  super + @mysql_key_words
end
open_escape() click to toggle source
# File lib/activefacts/generator/traits/sql/mysql.rb, line 229
def open_escape
  '`'
end
options() click to toggle source
# File lib/activefacts/generator/traits/sql/mysql.rb, line 23
def options
  super.merge({
    # no: [String, "no new options defined here"]
  })
end
process_options(options) click to toggle source
# File lib/activefacts/generator/traits/sql/mysql.rb, line 33
def process_options options
  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/mysql.rb, line 149
def reserved_words
  @mysql_reserved_words ||= %w{
    ACCESSIBLE ANALYZE CHANGE DATABASE DATABASES DAY_HOUR
    DAY_MICROSECOND DAY_MINUTE DAY_SECOND DELAYED DISTINCTROW
    DIV DUAL ENCLOSED ESCAPED EXPLAIN FLOAT4 FLOAT8 FORCE
    FULLTEXT HIGH_PRIORITY HOUR_MICROSECOND HOUR_MINUTE
    HOUR_SECOND INDEX INFILE INT1 INT2 INT3 INT4 INT8
    IO_AFTER_GTIDS IO_BEFORE_GTIDS KEYS KILL LINEAR LINES
    LOAD LOCK LONG LONGBLOB LONGTEXT LOW_PRIORITY MASTER_BIND
    MASTER_SSL_VERIFY_SERVER_CERT MEDIUMBLOB MEDIUMINT
    MEDIUMTEXT MIDDLEINT MINUTE_MICROSECOND MINUTE_SECOND
    NO_WRITE_TO_BINLOG OPTIMIZE OPTIMIZER_COSTS OPTIONALLY
    OUTFILE PURGE READ_WRITE REGEXP RENAME REPLACE REQUIRE
    RLIKE SCHEMAS SECOND_MICROSECOND SEPARATOR SHOW SPATIAL
    SQL_BIG_RESULT SQL_CALC_FOUND_ROWS SQL_SMALL_RESULT SSL
    STARTING STORED STRAIGHT_JOIN TERMINATED TINYBLOB TINYINT
    TINYTEXT UNLOCK UNSIGNED USE UTC_DATE UTC_TIME UTC_TIMESTAMP
    VARCHARACTER VIRTUAL XOR YEAR_MONTH ZEROFILL _FILENAME
  }
  super + @mysql_reserved_words
end
schema_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql/mysql.rb, line 54
def schema_name_max
  64
end
schema_prefix() click to toggle source
# File lib/activefacts/generator/traits/sql/mysql.rb, line 58
def schema_prefix
  ''
end
table_name_max() click to toggle source

See dev.mysql.com/doc/refman/5.7/en/identifiers.html

# File lib/activefacts/generator/traits/sql/mysql.rb, line 42
def table_name_max
  64
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/mysql.rb, line 111
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 TRIGGER #{trigger_function} BEFORE INSERT ON #{table_name}
      FOR EACH ROW SET #{safe_column_name(hash_field)} = #{
        hash(concatenate(coalesce(as_text(safe_column_exprs(leaves, 'NEW')))))
    }}.unindent
  ]
end