DatabaseProcApplicationCreatedLinks
sybsystemprocssp_multdb_unbind  31 Aug 14Defects Dependencies

1     
2     
3     /*
4     ** Messages for "sp_multdb_unbind"
5     **
6     ** 17260, "Can't run %1! from within a transaction."
7     ** 18608, "%1!: Delete row from master.dbo.sysattributes failed. Command 
8     **	   aborted."
9     ** 18276, "%1! is not a valid object type."
10    ** 18255, "%1! cannot be NULL."
11    ** 18314, "Login '%1!' does not exist in syslogins table."
12    ** 18951, "'%1!' is not currently supported."
13    ** 19595, "Instance '%1!' does not exist."
14    */
15    
16    /*
17    ** Procedure sp_multdb_unbind
18    **
19    ** This procedure removes a binding from SYSATTRIBUTES
20    ** for the MULTEMPDB_CLASS (class 16)
21    **      
22    ** SMP
23    **	Triplet  could be either bound to a 
24    **	temporary database group or to a temporary database i.e. there
25    **	could be only one binding entry for the given triplet.
26    **	For SMP, @instance_name argument must be NULL.
27    **
28    ** SDC
29    **	Triplet  could be either bound to a 
30    **	temporary database group or to local temporary databases. If the
31    **	binding is to a temporary database group, then there could be 
32    **	only one binding entry. However, if binding is to local temporary
33    **	databases then there could be multiple binding entries, one for
34    **	each cluster instance.
35    **	To unbind the given triplet from a temporary database group, 
36    **	instance_name is not required (it is ignored, if specified).
37    **	To unbind the given triplet from DB bindings, if instance_name
38    **	is given, then only the DB binding entry for the given instance
39    **	is removed. If no instance_name is provided, then all the 
40    **	DB binding entries for the given triplet are removed.
41    */
42    create procedure sp_multdb_unbind
43        @obj_name varchar(255), /* object to be bound  */
44        @obj_type varchar(30), /* Type of object 'AP' or 'LG' */
45        @scope varchar(255) = NULL, /* Application name or login name */
46        @instance_name varchar(255) = NULL /* Cluster instance name (optional 
47    					** and valid only in SDC).
48    					*/
49    as
50        declare @class_id smallint, /* class in SYSATTRIBUTES */
51            @attrib_id smallint, /* attribute in SYSATTRIBUTES */
52            @object_cinfo varchar(255), /* object_cinfo in SYSATTRIBUTES */
53            @int_val int, /* int_value in SYSATTRIBUTES */
54            @group_id int, /* group id database being added to */
55            @action int, /* action for built in */
56            @upcase_str varchar(30),
57            @app_name varchar(255), /* application name */
58            @user_id int, /* user id */
59            @instanceid int, /* (SDC only) instance id of the owner
60            ** of local user tempdb.
61            */
62            @existing_bind_type int, /* Existing binding type. Corresponds
63            ** to int_value in SYSATTRIBUTES
64            */
65            @svrmode int, /* Indicates the SMP or SDC mode */
66            @SMP int, /* Indicates SMP Server */
67            @SDC int, /* Indicates SDC Server */
68            @MULTDB_BINDDB int, /* binding is to a temporary database */
69            @MULTDB_BINDGRP int, /* binding is to a group */
70            @ATTR_DROP int /* indicates a binding update */
71    
72        select @SMP = 0,
73            @SDC = 1
74    
75    
76        select @svrmode = @SMP
77    
78        /* Instance related information is not relevant in SMP. */
79        select @instance_name = NULL
80        select @instanceid = NULL
81    
82    
83    
84        /*
85        **  If we're in a transaction, disallow this since it might make recovery
86        **  impossible.
87        */
88        if @@trancount > 0
89        begin
90            /*
91            ** 17260, "Can't run %1! from within a transaction."
92            */
93            raiserror 17260, "sp_multdb_unbind"
94            return (1)
95        end
96        else
97        begin
98            /* Use TSQL mode of unchained transactions */
99            set chained off
100       end
101   
102       /* Don't do "Dirty Reads" */
103       set transaction isolation level 1
104   
105       select @class_id = 16 /* class is MULTEMPDB_CLASS */
106       select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */
107   
108       /*
109       ** Following constants must be consistent with their definition in
110       ** multempdb.c
111       */
112       select @MULTDB_BINDGRP = 1,
113           @MULTDB_BINDDB = 0
114   
115   
116       /* keep in sync with sysattr.h */
117       select @ATTR_DROP = 3
118   
119       /* 
120       ** Make sure that object type is valid.
121       ** Can only be 'AP' or 'LG' or 'APPLICATION_NAME' or 'LOGIN_NAME'.
122       */
123       select @upcase_str = upper(@obj_type)
124       if (@upcase_str = "LG") OR (@upcase_str = "AP")
125           OR (@upcase_str = "APPLICATION_NAME") OR (@upcase_str = "LOGIN_NAME")
126       begin
127           if (@upcase_str = "APPLICATION_NAME") OR (@upcase_str = "AP")
128           begin
129               select @obj_type = "AP"
130           end
131           else
132           begin
133               select @obj_type = "LG"
134           end
135       end
136       else
137       begin
138           /*
139           ** 18276, "%1! is not a valid object type."
140           */
141           raiserror 18276, @obj_type
142           return (1)
143       end
144   
145       /*
146       ** Object name can't be null
147       */
148       if (@obj_name is NULL)
149       begin
150           /*
151           ** 18255, "%1! cannot be NULL."
152           */
153           raiserror 18255, "Object name"
154           return (1)
155       end
156   
157       select @action = @ATTR_DROP /* unbind */
158       select @user_id = NULL /* id of user from syslogins */
159   
160       /*
161       ** We don't currently support a non null scope.
162       */
163       if (@scope is not NULL)
164       begin
165           /*
166           ** 18951, "'%1!' is not currently supported."
167           */
168           raiserror 18951, "Scope"
169           return (1)
170       end
171   
172   
173   
174   
175   
176       /* 
177       ** convert user name to user id after checking its existence 
178       */
179       if (@obj_type = "LG")
180       begin
181           if not exists (select suid from master..syslogins
182                   where (name = @obj_name)
183                       and ((status & 512) != 512)) /* not LOGIN PROFILE*/
184           begin
185               /*
186               ** 18314, "Login '%1!' does not exist in syslogins table."
187               */
188               raiserror 18314, @obj_name
189               return (1)
190           end
191   
192           select @user_id = (select suid from master..syslogins
193                   where (name = @obj_name))
194   
195           if (@scope is not NULL)
196           begin
197               select @app_name = @scope
198           end
199       end
200       /*
201       ** if obj_type is "AP", then a non null scope
202       ** would have the login name. If user name is specified
203       ** then convert user name to user id after checking its existence
204       */
205       else if (@obj_type = "AP")
206       begin
207           select @app_name = @obj_name
208   
209           /*
210           ** if obj_type is "AP", then a non null scope
211           ** would have the login name. If user name is specified
212           ** then convert user name to user id after checking its existence
213           */
214           if (@scope is not NULL)
215           begin
216               if not exists (select suid from master..syslogins
217                       where (name = @scope)
218                           and ((status & 512) != 512)) /* not LOGIN PROFILE*/
219               begin
220                   /*
221                   ** 18314, "Login '%1!' does not exist in syslogins 
222                   **         table."
223                   */
224                   raiserror 18314, @scope
225                   return (1)
226               end
227   
228               select @user_id = (select suid from master..syslogins
229                       where (name = @scope))
230           end
231       end
232   
233       /* Attribute is for binding */
234       select @attrib_id = 1
235   
236       begin transaction multdb_unbind
237   
238   
239       begin
240           /*
241           ** SMP
242           **	There could be only one binding for a given triplet. Delete it.
243           **
244           ** SDC and @instance_name is NULL
245           **	If group binding, delete it.
246           **	If database bindings, delete all bindings. 
247           **	If the request was to remove a database binding for a spefific 
248           **	instance, then it must be already handled above.
249           */
250           delete from master..sysattributes
251           where class = @class_id
252               AND attribute = @attrib_id
253               AND object_type = @obj_type
254               AND object_cinfo = @app_name
255               AND object = @user_id
256   
257           if (@@error != 0)
258           begin
259               /*
260               ** 18608, "%1!: Delete row from master.dbo.sysattributes failed.
261               **	   Command aborted."
262               */
263               raiserror 18608, "sp_multdb_unbind"
264               goto error_exit
265           end
266       end
267   
268       /*
269       ** Currently, no action is required upon notification of an unbind event.
270       ** Uncomment the notification below when some action needs to be implemented.
271       ** SMP
272       **	Note that @instanceid is set as NULL as it is not relevant in SMP.
273       **
274       ** SDC
275       **	If group binding is removed, @instanceid is set to NULL even if 
276       **	a valid instance name was specified in the procedure arguments.
277       **	For other cases, @instanceid value is passed.
278       **	Notified routine will make use of instanceid to take the appropriate
279       **	action such as to remove all db bindings vs db binding for a 
280       **	particular cluster instance.
281       */
282       -- select @notify_status = 
283       --	attrib_notify (@class_id, @attrib_id, @obj_type, @user_id,
284       --		NULL, @instanceid, NULL, @app_name,
285       --		@MULTDB_BINDDB, @tempdb_name, NULL, NULL, "", @action)
286       --
287       --if (@notify_status = 0)
288       --begin
289       --	print "Internal error: Failed to remove binding."
290       --	goto error_exit
291       --end
292   
293   
294       /* 
295       ** Commit the transaction 
296       */
297       commit transaction multdb_unbind
298       return (0)
299   
300   error_exit:
301       rollback transaction multdb_unbind
302       return (1)
303
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 VOVR 4 Variable is an input param and is overwritten @instance_name 79
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..syslogins  
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 169
 MUCO 3 Useless Code Useless Brackets 179
 MUCO 3 Useless Code Useless Brackets 189
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 195
 MUCO 3 Useless Code Useless Brackets 205
 MUCO 3 Useless Code Useless Brackets 214
 MUCO 3 Useless Code Useless Brackets 225
 MUCO 3 Useless Code Useless Brackets 229
 MUCO 3 Useless Code Useless Begin-End Pair 239
 MUCO 3 Useless Code Useless Brackets 257
 MUCO 3 Useless Code Useless Brackets 298
 MUCO 3 Useless Code Useless Brackets 302
 QISO 3 Set isolation level 103
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, object_cinfo, attribute, class}		 251
 VNRD 3 Variable is not read @SDC 73
 VNRD 3 Variable is not read @svrmode 76
 VNRD 3 Variable is not read @instance_name 79
 VNRD 3 Variable is not read @instanceid 80
 VNRD 3 Variable is not read @MULTDB_BINDGRP 112
 VNRD 3 Variable is not read @MULTDB_BINDDB 113
 VNRD 3 Variable is not read @action 157
 VUNU 3 Variable is not used @object_cinfo 52
 VUNU 3 Variable is not used @int_val 53
 VUNU 3 Variable is not used @group_id 54
 VUNU 3 Variable is not used @existing_bind_type 62
 MSUB 2 Subquery Marker 181
 MSUB 2 Subquery Marker 192
 MSUB 2 Subquery Marker 216
 MSUB 2 Subquery Marker 228
 MTR1 2 Metrics: Comments Ratio Comments: 60% 42
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 13 = 18dec - 7exi + 2 42
 MTR3 2 Metrics: Query Complexity Complexity: 87 42

DEPENDENCIES
PROCS AND TABLES USED
reads table master..syslogins (1)  
writes table master..sysattributes (1)  

CALLERS
called by proc sybsystemprocs..sp_tempdb