module ActiveFacts::Generators::Traits::SQL::Server

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

  return exprs.map{|e| as_text(e)} if Array === exprs

  style =
    case exprs.type_num
    when MM::DataType::TYPE_Date, MM::DataType::TYPE_DateTime, MM::DataType::TYPE_Timestamp
      ', 121'
    # REVISIT: What about MM::DataType::TYPE_Time?
    else
      ''
    end
  Expression.new("CONVERT(VARCHAR, #{exprs}#{style})", MM::DataType::TYPE_String, exprs.is_mandatory)
end
cast_as_string(d) click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 240
def cast_as_string d
  # select right('0000000000'+cast(1234.45 as varchar(10)), 11);  -- Only good for +ve numbers!
  # set @x = -12345;
  # select right('0000000000'+convert(varchar(10), 1234.45, style), 11);  -- Use styles
  # select convert(varchar, GETDATE(), 21); -- 'YYYY-MM-DD HH:mm:ss.123'
  #
  # declare @x decimal(10,3);
  # set @x = -12345.67;
  # select case when @x < 0 then '-'+right('0000000000'+cast(-@x as varchar(10)), 11)
  #        else ' '+right('0000000000'+cast(@x as varchar(10)), 11)
  #        end;
  #
  # declare @x money; set @x = 123.456; select CONVERT(varchar, @x, 0);
  #
  # declare @x float; set @x = 123.456; select CONVERT(varchar, @x, 2); -- Always 16 characters, exponential notation
end
choose_sql_type(type_name, value_constraint, component, options) click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 62
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)
      options[:default] = ' IDENTITY' if options[:auto_assign]
      'BIGINT'
    else
      super
    end

  when MM::DataType::TYPE_Money
    'MONEY'

  when MM::DataType::TYPE_DateTime
    'DATETIME'

  when MM::DataType::TYPE_Timestamp
    'DATETIME'

  when MM::DataType::TYPE_Binary
    length = options[:length]
    case binary_surrogate(type_name, value_constraint, options)
    when :guid_fk             # A GUID surrogate that's auto-assigned elsewhere
      'UNIQUEIDENTIFIER'
    when :guid                # A GUID
      options[:default] = " DEFAULT NEWID()"
      # NEWSEQUENTIALID improves indexing locality and page fill factor.
      # However, it makes values more easily guessable, and
      # exposes the MAC address of the generating computer(!)
      # options[:default] = " DEFAULT NEWSEQUENTIALID()"
      'UNIQUEIDENTIFIER'
    when :hash                # A hash of the natural key
      options[:length] = 20   # Assuming SHA-1. SHA-256 would need 32 bytes
      options[:computed] = hash_assignment(component, component.root.natural_index.all_index_field.map(&:component))
      'BINARY'
    else                      # Not a surrogate
      length = options[:length]
      if length && length <= 8192
        super
      else
        'IMAGE'
      end
    end
  else
    super
  end
end
close_escape() click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 200
def close_escape
  ']'
end
column_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 46
def column_name_max
  128
end
concatenate(exprs) click to toggle source

Return an SQL expression that concatenates the given expressions (which must be text)

# File lib/activefacts/generator/traits/sql/server.rb, line 145
def concatenate exprs
  # SQL Server 2012 onwards: %Q{CONCAT('|'+#{exprs.flat_map{|e| [e.to_s, "+'|'"]}*''})}
  Expression.new(
    %Q{('|'+#{
      exprs.flat_map{|e| [e.to_s, "'|'"] } * '+'
      })},
    MM::DataType::TYPE_String,
    true
  )
end
create_or_replace(name, kind) click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 112
def create_or_replace(name, kind)
  # From SQL Server 2016 onwards, you can use "CREATE OR ALTER ..."
  go("IF OBJECT_ID('#{name}') IS NOT NULL\n\tDROP #{kind} #{name}") +
  "CREATE #{kind} #{name}"
end
data_type_context_class() click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 38
def data_type_context_class
  SQLServerDataTypeContext
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/server.rb, line 28
def defaults_and_options options
  super
end
go(s = '') click to toggle source

Although SQL Server accepts ; as a statement separator, it runs commands in batches when the “GO” command is issued.

# File lib/activefacts/generator/traits/sql/server.rb, line 192
def go s = ''
  "#{s.sub(/\A\n+/,'')}\nGO\n"
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/server.rb, line 157
def hash expr, algo = 'SHA1'
  Expression.new("CONVERT(BINARY(32), HASHBYTES('#{algo}', #{expr}), 2)", MM::DataType::TYPE_Binary, expr.is_mandatory)
end
hash_assignment(hash_field, leaves) click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 118
def hash_assignment hash_field, leaves
  table_name = safe_table_name(hash_field.root)
  trigger_function = escape('assign_'+column_name(hash_field), 128)
  %Q{
  AS #{hash(concatenate(coalesce(as_text(safe_column_exprs(leaves)))))}
  PERSISTED}.gsub(/\s+/,' ').strip
end
index_kind(index) click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 204
def index_kind(index)
  (index.composite_as_primary_index ? ' CLUSTERED' : ' NONCLUSTERED')
end
index_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 50
def index_name_max
  128
end
key_words() click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 182
def key_words
  # These keywords should not be used for columns or tables:
  @sqlserver_key_words ||= %w{
    INCLUDE INDEX SQLCA
  }
  super + @sqlserver_key_words
end
open_escape() click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 196
def open_escape
  '['
end
options() click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 21
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/server.rb, line 32
def process_options options
  # No extra options to process
  super
  @closed_world_indices = true
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/server.rb, line 164
def reserved_words
  @sqlserver_reserved_words ||= %w{
    BACKUP BREAK BROWSE BULK CHECKPOINT CLUSTERED COMPUTE
    CONTAINSTABLE DATABASE DBCC DENY DISK DISTRIBUTED DUMP
    ERRLVL FILE FILLFACTOR FREETEXT FREETEXTTABLE HOLDLOCK
    IDENTITYCOL IDENTITY_INSERT INDEX KILL LINENO LOAD
    NOCHECK NONCLUSTERED OFF OFFSETS OPENDATASOURCE OPENQUERY
    OPENROWSET OPENXML PIVOT PLAN PRINT PROC RAISERROR
    READTEXT RECONFIGURE REPLICATION RESTORE REVERT ROWCOUNT
    ROWGUIDCOL RULE SAVE SECURITYAUDIT SEMANTICKEYPHRASETABLE
    SEMANTICSIMILARITYDETAILSTABLE SEMANTICSIMILARITYTABLE
    SETUSER SHUTDOWN STATISTICS TEXTSIZE TOP TRAN TRY_CONVERT
    TSEQUAL UNPIVOT UPDATETEXT USE WAITFOR WITHIN GROUP
    WRITETEXT
  }
  super + @sqlserver_reserved_words
end
schema_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 54
def schema_name_max
  128
end
schema_prefix() click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 58
def schema_prefix
  ''
end
table_name_max() click to toggle source
# File lib/activefacts/generator/traits/sql/server.rb, line 42
def table_name_max
  128
end