Class: ActiveFacts::Generate::SQL::SERVER

Inherits:

Object

• Object
• ActiveFacts::Generate::SQL::SERVER

Includes:

Persistence

Defined in:

lib/activefacts/generate/sql/server.rb

Overview

Generate SQL for SQL Server for an ActiveFacts vocabulary. Invoke as

afgen --sql/server[=options] <file>.cql

Options are comma or space separated:

• delay_fks Leave all foreign keys until the end, not just those that contain forward-references

Instance Method Summary

• #generate(out = $>) ⇒ Object

  :nodoc:.

Methods included from Persistence

rails_plural_name, rails_singular_name, rails_type

Instance Method Details

#generate(out = $>) ⇒ Object

:nodoc:

# File 'lib/activefacts/generate/sql/server.rb', line 131

def generate(out = $>)      #:nodoc:
  @out = out
  #go "CREATE SCHEMA #{@vocabulary.name}"

  tables_emitted = {}
  delayed_foreign_keys = []

  @vocabulary.tables.each do |table|
    puts "CREATE TABLE #{escape table.name.gsub(' ',@underscore)} ("

    pk = table.identifier_columns
    identity_column = pk[0] if pk[0].is_auto_assigned

    fk_refs = table.references_from.select{|ref| ref.is_simple_reference }
    fk_columns = table.columns.select do |column|
      column.references[0].is_simple_reference
    end

    # We sort the columns here, not in the persistence layer, because it affects
    # the ordering of columns in an index :-(.
    columns = table.columns.sort_by { |column| column.name(@underscore) }.map do |column|
      name = escape column.name(@underscore)
      padding = " "*(name.size >= ColumnNameMax ? 1 : ColumnNameMax-name.size)
      type, params, constraints = column.type
      constraints = [] if (fk_columns.include?(column))  # Don't enforce VT constraints on FK columns
      length = params[:length]
      length &&= length.to_i
      scale = params[:scale]
      scale &&= scale.to_i
      type, length = normalise_type(type, length)
      sql_type = "#{type}#{
        if !length
          ""
        else
          "(" + length.to_s + (scale ? ", #{scale}" : "") + ")"
        end
        }"
      identity = column == identity_column ? " IDENTITY" : ""
      null = (column.is_mandatory ? "NOT " : "") + "NULL"
      check = check_clause(name, constraints)
      comment = column.comment
      [ "-- #{comment}", "#{name}#{padding}#{sql_type}#{identity} #{null}#{check}" ]
    end.flatten

    pk_def = (pk.detect{|column| !column.is_mandatory} ? "UNIQUE(" : "PRIMARY KEY(") +
        pk.map{|column| escape column.name(@underscore)}*", " +
        ")"

    inline_fks = []
    table.foreign_keys.each do |fk|
      fk_text = "FOREIGN KEY (" +
        fk.from_columns.map{|column| column.name(@underscore)}*", " +
        ") REFERENCES #{escape fk.to.name.gsub(' ',@underscore)} (" +
        fk.to_columns.map{|column| column.name(@underscore)}*", " +
        ")"
      if !@delay_fks and              # We don't want to delay all Fks
        (tables_emitted[fk.to] or     # The target table has been emitted
        fk.to == table && !fk.to_columns.detect{|column| !column.is_mandatory})   # The reference columns already have the required indexes
        inline_fks << fk_text
      else
        delayed_foreign_keys << ("ALTER TABLE #{escape fk.from.name.gsub(' ',@underscore)}\n\tADD " + fk_text)
      end
    end

    indices = table.indices
    inline_indices = []
    delayed_indices = []
    indices.each do |index|
      next if index.over == table && index.is_primary   # Already did the primary keys
      abbreviated_column_names = index.abbreviated_column_names(@underscore)*""
      column_names = index.column_names(@underscore)
      column_name_list = column_names.map{|n| escape(n)}*", "
      if index.columns.all?{|column| column.is_mandatory}
        inline_indices << "UNIQUE(#{column_name_list})"
      else
        view_name = escape "#{index.view_name}_#{abbreviated_column_names}"
        delayed_indices <<
%Q{CREATE VIEW dbo.#{view_name} (#{column_name_list}) WITH SCHEMABINDING AS
\tSELECT #{column_name_list} FROM dbo.#{escape index.on.name.gsub(' ',@underscore)}
\tWHERE\t#{
  index.columns.
    select{|column| !column.is_mandatory }.
    map{|column|
      escape(column.name(@underscore)) + " IS NOT NULL"
    }*"\n\t  AND\t"
}
GO

CREATE UNIQUE CLUSTERED INDEX #{escape index.name} ON dbo.#{view_name}(#{index.columns.map{|column| column.name(@underscore)}*", "})
}
      end
    end

    tables_emitted[table] = true

    puts("\t" + (columns + [pk_def] + inline_indices + inline_fks)*",\n\t")
    go ")"
    delayed_indices.each {|index_text|
      go index_text
    }
  end

  delayed_foreign_keys.each do |fk|
    go fk
  end
end