Class: ActiveRecord::ConnectionAdapters::SQLServerAdapter

Inherits:
AbstractAdapter
  • Object
show all
Defined in:
lib/connection_adapters/sqlserver_adapter.rb

Instance Method Summary collapse

Instance Method Details

#constraints(table_name, name = nil) ⇒ Object

SQL Server allows you to duplicate table names & constraint names in a single database, provided that each constraint belongs to a different schema.

The nice trickery in Rails and DrySQL that relies on retrieving info from the information_schema views will get confused if you create > 1 table with the same name in the same database or > 1 constraint with the same name.

A future solution to this problem would be to have the ability to specify the desired schema in the database connection properties and have information_schema queries select only rows from the desired schema. Until this is implemented, do not duplicate table names or constraint names inside any DB.



61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
 
# File 'lib/connection_adapters/sqlserver_adapter.rb', line 61

def constraints(table_name, name = nil)#:nodoc:

  constraints = [] 
  
  # May 15/2007 - Constraints query re-written by Clifford Heath

      sql = %Q{
    -- Get fields of all unique indexes:
    SELECT  user_name(o.uid) as constraint_schema,
      i.name AS constraint_name,
      o.name AS table_name,
      CASE (SELECT p.xtype FROM sysconstraints t, sysobjects p
  WHERE t.id = o.id
    AND t.constid = p.id
    AND i.name = p.name
 )
 WHEN 'PK' THEN 'PRIMARY KEY'
 WHEN 'UQ' THEN 'UNIQUE' -- unique constraint
 ELSE 'UNIQUE'    -- unique index
      END AS constraint_type,
      c.name AS column_name,
      NULL AS foreign_constraint_name,
      NULL AS referenced_constraint_name,
      NULL AS update_rule,
      NULL AS delete_rule,
      NULL AS foreign_table_name,
      NULL AS foreign_column_name
    FROM  sysobjects AS o,
      sysindexes AS i,
      sysindexkeys AS k,
      syscolumns AS c
    WHERE ('#{table_name}' = '' -- All tables
      OR '#{table_name}' = o.name)
      AND o.type = 'U'    -- Tables
      AND o.status >= 0   -- exclude system tables
      AND o.id = i.id   -- indexes for this table
      AND o.id = k.id   -- indexkeys for table
      AND i.indid = k.indid -- indexkey for index
      AND k.colid = c.colid -- indexkey for column
      AND o.id = c.id   -- column for table
      AND (i.status&2) <> 0 -- unique
      AND i.indid NOT IN (0, 255) -- not base table or text
    -- ORDER BY o.name, i.indid, k.keyno  -- Can't do this with UNION

    UNION ALL   -- Don't bother with distinct union!

    -- Get field pairs of all FK constraints to and from this table
    SELECT  user_name(foreign_key.uid) AS constraint_schema,
      foreign_key.name AS constraint_name,
      from_table.name AS table_name,
      'FOREIGN KEY' AS constraint_type,
      from_column.name AS column_name,
      foreign_key.name AS foreign_constraint_name,
      i.name AS referenced_constraint_name,
      CASE WHEN (ObjectProperty(f.constid, 'CnstIsUpdateCascade')=1)
 THEN 'CASCADE'
 ELSE 'NO ACTION'
      END AS update_rule,
      CASE WHEN (ObjectProperty(f.constid, 'CnstIsDeleteCascade')=1)
 THEN 'CASCADE'
 ELSE 'NO ACTION'
      END AS delete_rule,
      to_table.name AS foreign_table_name,
      to_column.name AS foreign_column_name
    FROM    sysobjects AS from_table,
      sysforeignkeys AS f,
      sysobjects AS to_table,
      sysobjects AS foreign_key,
      syscolumns AS from_column,
      syscolumns AS to_column,
      sysreferences AS r,
      sysindexes AS i
    WHERE   ('#{table_name}' = ''   -- FK's for all tables
      OR from_table.name = '#{table_name}'
      OR to_table.name = '#{table_name}')
      AND from_table.type = 'U'     -- All user tables
      AND   from_table.status >= 0
      AND   from_table.id = f.fkeyid        -- All fk's from the table
      AND   f.constid = foreign_key.id      -- Get the sysobject from fk
      AND   f.rkeyid = to_table.id    -- Get referenced table
      AND   f.fkey = from_column.colid      -- Get source table's column
      AND   from_column.id = from_table.id
      AND   f.rkey = to_column.colid        -- And referenced's table's col
      AND   to_column.id = to_table.id
      AND foreign_key.id = r.constid
      AND r.rkeyid = i.id
      AND r.rkeyindid = i.indid
      }

  results = select_all(sql, name)
  constraint_name_hash = {}
  results.each do |row| 
    constraint_name = row['constraint_name']
    foreign_constraint_name = row['foreign_constraint_name']
    column_name = row['column_name']
    # Process constraints local to this table

    if !(current_constraint = constraint_name_hash[constraint_name])
      current_constraint = SQLServerConstraint.new(row['constraint_schema'], row['table_name'], column_name, constraint_name, 
      row['constraint_type'], row['referenced_constraint_name'], row['foreign_table_name'], row['foreign_column_name'] )
      constraints << current_constraint
      constraint_name_hash[constraint_name] = current_constraint 
    # This key is a composite

    else
      current_constraint.column_names << column_name unless current_constraint.column_names.include?(column_name)
      referenced_column_name = row['foreign_column_name']
      if referenced_column_name
        current_constraint.referenced_column_names << referenced_column_name unless current_constraint.referenced_column_names.include?(referenced_column_name)
      end
    end
  end
  constraints
end