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