server.rb in activefacts-0.8.5

- old
+ new

@@ -13,11 +13,11 @@
       # 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
-      # * norma Translate datatypes from NORMA to SQL Server
+      # * norma Translate valuetypes from NORMA to SQL Server
       class SERVER
       private
         include Persistence
         ColumnNameMax = 40
 
@@ -47,10 +47,11 @@
         def initialize(vocabulary, *options)
           @vocabulary = vocabulary
           @vocabulary = @vocabulary.Vocabulary.values[0] if ActiveFacts::API::Constellation === @vocabulary
           @delay_fks = options.include? "delay_fks"
           @norma = options.include? "norma"
+          @underscore = options.include?("underscore") ? "_" : ""
         end
 
         def puts s
           @out.puts s
         end
@@ -100,11 +101,11 @@
 
             when "Money"; "decimal"
             when "PictureRawData"; "image"
             when "VariableLengthRawData"; "varbinary"
             when "BIT"; "bit"
-            else raise "SQL type unknown for NORMA type #{type}"
+            else type # raise "SQL type unknown for NORMA type #{type}"
             end
           [sql_type, length]
         end
 
       public
@@ -114,11 +115,11 @@
 
           tables_emitted = {}
           delayed_foreign_keys = []
 
           @vocabulary.tables.each do |table|
-            puts "CREATE TABLE #{escape table.name} ("
+            puts "CREATE TABLE #{escape table.name(@underscore)} ("
 
             pk = table.identifier_columns
             identity_column = pk[0] if pk.size == 1 && pk[0].is_auto_assigned
 
             fk_refs = table.references_from.select{|ref| ref.is_simple_reference }
@@ -126,12 +127,12 @@
               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(nil) }.map do |column|
-              name = escape column.name("")
+            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, restrictions = column.type
               restrictions = [] if (fk_columns.include?(column))  # Don't enforce VT restrictions on FK columns
               length = params[:length]
               length &&= length.to_i
@@ -151,54 +152,54 @@
               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("")}*", " +
+                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}*", " +
-                ") REFERENCES #{escape fk.to.name} (" +
-                fk.to_columns.map{|column| column.name}*", " +
+                fk.from_columns.map{|column| column.name(@underscore)}*", " +
+                ") REFERENCES #{escape fk.to.name(@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}\n\tADD " + fk_text)
+                delayed_foreign_keys << ("ALTER TABLE #{escape fk.from.name(@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*""
-              column_names = index.column_names
+              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}
+\tSELECT #{column_name_list} FROM dbo.#{escape index.on.name(@underscore)}
 \tWHERE\t#{
   index.columns.
     select{|column| !column.is_mandatory }.
     map{|column|
-      escape(column.name) + " IS NOT NULL"
+      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}*", "})
+CREATE UNIQUE CLUSTERED INDEX #{escape index.name} ON dbo.#{view_name}(#{index.columns.map{|column| column.name(@underscore)}*", "})
 }
               end
             end
 
             tables_emitted[table] = true
@@ -214,27 +215,31 @@
             go fk
           end
         end
 
       private
+        def sql_value(value)
+          value.is_a_string ? sql_string(value.literal) : value.literal
+        end
+
+        def sql_string(str)
+          "'" + str.gsub(/'/,"''") + "'"
+        end
+
         def check_clause(column_name, restrictions)
           return "" if restrictions.empty?
           # REVISIT: Merge all restrictions (later; now just use the first)
           " CHECK(" +
-            restrictions[0].all_allowed_range.sort_by do |ar|
-              # Put the allowed ranges into a defined order:
-              ((min = ar.value_range.minimum_bound) && min.value) ||
-                ((max = ar.value_range.maximum_bound) && max.value)
-            end.map do |ar|
+            restrictions[0].all_allowed_range_sorted.map do |ar|
               vr = ar.value_range
               min = vr.minimum_bound
               max = vr.maximum_bound
-              if (min && max && max.value == min.value)
-                "#{column_name} = #{min.value}"
+              if (min && max && max.value.literal == min.value.literal)
+                "#{column_name} = #{sql_value(min.value)}"
               else
                 inequalities = [
-                  min && "#{column_name} >#{min.is_inclusive ? "=" : ""} #{min.value}",
-                  max && "#{column_name} <#{max.is_inclusive ? "=" : ""} #{max.value}"
+                  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 " +
           ")"