DatabaseProcApplicationCreatedLinks
sybsystemprocssp_multdb_bind  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** Messages for "sp_multdb_bind"
5     **
6     ** 17260, "Can't run %1! from within a transaction."
7     ** 18252, "%1!' value '%2!' is not valid."
8     ** 18255, "%1! cannot be NULL."
9     ** 18276, "%1! is not a valid object type."
10    ** 18314, "Login '%1!' does not exist in syslogins table."
11    ** 18609, "%1!: Update row of master.dbo.sysattributes failed. Command aborted."
12    ** 18610, "%1!: Insert row to master.dbo.sysattributes failed. Command aborted."
13    ** 18946, "Either the database '%1!' does not exist or is not a user 
14    **         created temporary database."
15    ** 18947, "Group '%1!' does not exist."
16    ** 18951, "'%1!' is not currently supported."
17    ** 18953, "Internal Error: failed to add binding."
18    ** 18976, "The '%1!' login can not have a hard binding."
19    ** 19580, "Either the database '%1!' does not exist or is not a local 
20    **	   user temporary database." 
21    ** 19581, "The %1! '%2!' is already bound to a group. To bind it to a 
22    **	   database, first remove existing group binding to it."
23    ** 19582, "The %1! '%2!' already has one or more database bindings. To 
24    **	   bind it to a group, first remove existing database bindings to it."
25    ** 19594, "Unable to determine owner instance of the database '%1!'. An 
26    **	   internal inconsistency is encountered in row for the database '%1!' 
27    **	   in master..sysdatabases. Please contact Sybase Technical Support."
28    */
29    
30    /*
31    ** Procedure sp_multdb_bind
32    **
33    ** This procedure adds a new multiple tempdb related binding 
34    ** in SYSATTRIBUTES
35    **      
36    ** SMP
37    **      Triplet  could be either bound to a
38    **      temporary database group or to a temporary database i.e. there
39    **      could be only one binding entry for the given triplet.
40    **
41    ** SDC
42    **      Triplet  could be either bound to a
43    **      temporary database group or to local temporary databases. If the
44    **      binding is to a temporary database group, then there could be
45    **      only one binding entry. However, if binding is to local temporary
46    **      databases then there could be multiple binding entries, one for
47    **      each cluster instance.
48    */
49    create or replace procedure sp_multdb_bind
50        @obj_name varchar(255), /* object to be bound  */
51        @obj_type varchar(30), /* Type of object 'AP' or 'LG' */
52        @scope varchar(255), /* Application name or login name */
53        @tdb_type varchar(30), /* 'DB' for db or "GR" for group */
54        @tdb varchar(255), /* temporary db/group to be bound to*/
55        @hardness varchar(30) /* hard of soft binding */
56    as
57    
58        declare @class_id smallint, /* class in SYSATTRIBUTES */
59            @attrib_id smallint, /* attribute in SYSATTRIBUTES */
60            @object_cinfo varchar(255), /* object_cinfo in SYSATTRIBUTES */
61            @new_bind_type int, /* New binding type. Corresponds to 
62            ** int_value in SYSATTRIBUTES 
63            */
64            @existing_bind_type int, /* Existing binding type. Corresponds
65            ** to int_value in SYSATTRIBUTES
66            */
67            @group_id int, /* group id */
68            @action int, /* action for built in */
69            @upcase_str varchar(30),
70            @app_name varchar(255), /* application name */
71            @user_id int, /* user id of login */
72            @hardorsoft int, /* hard or soft */
73            @instanceid int, /* (SDC only) instance id of the owner 
74            ** of local user tempdb. 
75            */
76            @obj_type_str varchar(30), /* To hold object type "login" or
77            ** "application" string constants 
78            ** for message printing.
79            */
80            @svrmode int, /* Indicates the SMP or SDC mode */
81            @notify_status int, /* return value from attrib_notify() */
82            @MULTDB_HARDBINDING int, /* hard binding */
83            @MULTDB_SOFTBINDING int, /* soft binding */
84            @MULTDB_BINDGRP int, /* binding is to a group */
85            @MULTDB_BINDDB int, /* binding is to a temporary database */
86            @SMP int, /* Indicates SMP Server */
87            @SDC int, /* Indicates SDC Server */
88            @DBT3_USER_TEMPDB int, /* (SMP only) Bit value corresponding 
89            ** to DBT3_USER_TEMPDB 
90            */
91            @DBT3_LOCAL_USER_TEMPDB int, /* (SDC only) Bit value corresponding to
92            ** DBT3_LOCAL_USER_TEMPDB 
93            */
94            @ATTR_ADD int, /* indicates a new binding */
95            @ATTR_CHANGE int /* indicates a binding update */
96    
97        select @SMP = 0,
98            @SDC = 1
99    
100       if @@clustermode != "shared disk cluster"
101       begin
102           select @svrmode = @SMP
103   
104           select @DBT3_USER_TEMPDB = number
105           from master.dbo.spt_values
106           where type = "D3" and name = "user created temp db"
107   
108           /* instanceid is not relevant in SMP, set it to NULL */
109           select @instanceid = NULL
110       end
111   
112   
113       /*
114       **  if we're in a transaction, disallow this since it might make recovery
115       **  impossible.
116       */
117       if @@trancount > 0
118       begin
119           /*
120           ** 17260, "Can't run %1! from within a transaction."
121           */
122           raiserror 17260, "sp_multdb_bind"
123           return (1)
124       end
125       else
126       begin
127           /* Use TSQL mode of unchained transactions */
128           set chained off
129       end
130   
131       /* Don't do "Dirty Reads" */
132       set transaction isolation level 1
133   
134       select @class_id = 16 /* class is MULTEMPDB_CLASS */
135       select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */
136   
137       /* 
138       ** Following constants must be consistent with their definition in
139       ** multempdb.c 
140       */
141       select @MULTDB_HARDBINDING = 1,
142           @MULTDB_SOFTBINDING = 0,
143           @MULTDB_BINDGRP = 1,
144           @MULTDB_BINDDB = 0
145   
146       /* keep in sync with sysattr.h */
147       select @ATTR_ADD = 1,
148           @ATTR_CHANGE = 2
149   
150       /* 
151       ** Make sure that object type is valid
152       ** Can only be 'AP' or 'LG' or 'APPLICATION_NAME' or 'LOGIN_NAME'
153       */
154       select @upcase_str = upper(@obj_type)
155       if (@upcase_str = "LG") OR (@upcase_str = "AP")
156           OR (@upcase_str = "APPLICATION_NAME") OR (@upcase_str = "LOGIN_NAME")
157       begin
158           if (@upcase_str = "APPLICATION_NAME") OR (@upcase_str = "AP")
159           begin
160               select @obj_type = "AP"
161               select @obj_type_str = "application"
162           end
163           else
164           begin
165               select @obj_type = "LG"
166               select @obj_type_str = "login"
167           end
168       end
169       else
170       begin
171           /*
172           ** 18276, "%1! is not a valid object type."
173           */
174           raiserror 18276, @obj_type
175           return (1)
176       end
177   
178       /*
179       ** Make sure that tdb type is valid
180       ** Can only be 'GR' (binding to a group) or 'GROUP'
181       ** 'DB' (binding to specific database) or 'DATABASE'
182       */
183       select @upcase_str = upper(@tdb_type)
184       if (@upcase_str = "DB") OR (@upcase_str = "GR")
185           OR (@upcase_str = "DATABASE") OR (@upcase_str = "GROUP")
186       begin
187           if (@upcase_str = "DATABASE") OR (@upcase_str = "DB")
188           begin
189               select @tdb_type = "DB"
190           end
191           else
192           begin
193               select @tdb_type = "GR"
194           end
195       end
196       else
197       begin
198           /*
199           ** 18252, "%1!' value '%2!' is not valid."
200           */
201           raiserror 18252, "Bindtype", @tdb_type
202           return (1)
203       end
204   
205       /*
206       ** Object name can't be null
207       */
208       if (@obj_name is NULL)
209       begin
210           /*
211           ** 18255, "%1! cannot be NULL."
212           */
213           raiserror 18255, "Object name"
214           return (1)
215       end
216   
217       /*
218       ** Check to see that the hardness is valid.
219       ** Either "hard" or "soft" or null (in which case
220       ** we use the default of "soft"
221       */
222       select @upcase_str = upper(@hardness)
223       if (@upcase_str = "HARD")
224       begin
225           select @hardorsoft = @MULTDB_HARDBINDING
226       end
227       else if (@upcase_str = "SOFT") OR (@upcase_str is NULL)
228       begin
229           select @hardorsoft = @MULTDB_SOFTBINDING
230       end
231       else
232       begin
233           /*
234           ** 18252, "%1!' value '%2!' is not valid."
235           */
236           raiserror 18252, "Hardness", @hardness
237           return (1)
238       end
239   
240   
241       /*
242       ** Temp db or group name can't be null
243       */
244       if (@tdb is NULL)
245       begin
246           /*
247           ** 18255, "%1! cannot be NULL."
248           */
249           raiserror 18255, "bindobj"
250           return (1)
251       end
252   
253       select @action = @ATTR_ADD /* new binding */
254       select @user_id = NULL /* id of user from syslogins */
255       select @app_name = NULL /* application name */
256   
257       /*
258       ** We don't currently support a non null scope.
259       */
260       if (@scope is not NULL)
261       begin
262           /*
263           ** 18951, "'%1!' is not currently supported."
264           */
265           raiserror 18951, "Scope"
266           return (1)
267       end
268   
269       /*
270       ** If binding to temporary database then 
271       ** check to see that the database being bound to
272       ** is indeed a temporary database. We do not allow the
273       ** binding to a non temporary database.
274       **
275       ** For SDC, check to see if the database being bound is a local user 
276       ** temporary database. Bindings to non temporary databases, global 
277       ** temporary databases and local system temporary databases are not allowed.
278       */
279       if (@tdb_type = "DB")
280       begin
281   
282           if not exists (select * from master..sysdatabases
283                   where name = @tdb
284                       AND ((status3 & @DBT3_USER_TEMPDB) =
285                           @DBT3_USER_TEMPDB))
286           begin
287               /*
288               ** 18946, "Either the database '%1!' does not exist 
289               **	   or is not a user created temporary database."
290               */
291               raiserror 18946, @tdb
292               return (1)
293           end
294   
295           /* 
296           ** Specified (local) temporary database exists. Indicate that
297           ** binding is to a temporary database.
298           */
299           select @new_bind_type = @MULTDB_BINDDB
300   
301       end
302       else
303       /*
304       ** Binding is to a group. 
305       ** Check to see if the group name exists.
306       */
307       begin
308           select @attrib_id = 0 /* attribute is MULTEMPDB_GROUP */
309           if not exists (select * from master..sysattributes
310                   where class = @class_id
311                       AND attribute = @attrib_id
312                       AND object_type = 'GR'
313                       AND object_cinfo = @tdb)
314           begin
315               /*
316               ** 18947, "Group '%1!' does not exist."
317               */
318               raiserror 18947, @tdb
319               return (1)
320           end
321           else
322           begin
323               select @new_bind_type = @MULTDB_BINDGRP /* indicates group */
324   
325           end
326       end
327   
328       /* 
329       ** convert user name to user id after checking its existence 
330       */
331       if (@obj_type = "LG")
332       begin
333           /* 'sa' cannot have a hard binding */
334           if ((@hardorsoft = @MULTDB_HARDBINDING) and (@obj_name = "sa"))
335           begin
336               /*
337               ** 18976, "The '%1!' login can not have a hard binding."
338               */
339               raiserror 18976, @obj_name
340               return (1)
341           end
342   
343           if not exists (select suid from master..syslogins
344                   where (name = @obj_name)
345                       and ((status & 512) != 512)) /* not LOGIN PROFILE */
346           begin
347               /*
348               ** 18314, "Login '%1!' does not exist in syslogins table."
349               */
350               raiserror 18314, @obj_name
351               return (1)
352           end
353   
354           select @user_id = (select suid from master..syslogins
355                   where (name = @obj_name))
356   
357           if (@scope is not NULL)
358           begin
359               select @app_name = @scope
360           end
361       end
362   
363       /*
364       ** For "AP" type bindings, obj_name is an application name.
365       */
366       else if (@obj_type = "AP")
367       begin
368           select @app_name = @obj_name
369   
370           /*
371           ** if obj_type is "AP", then a non null scope
372           ** would have the login name. If user name is specified
373           ** then convert user name to user id after checking its existence
374           */
375           if (@scope is not NULL)
376           begin
377               if not exists (select suid from master..syslogins
378                       where (name = @scope)
379                           and ((status & 512) != 512)) /* not LOGIN PROFILE */
380               begin
381                   /*
382                   ** 18314, "Login '%1!' does not exist in syslogins 
383                   **	   table."
384                   */
385                   raiserror 18314, @scope
386                   return (1)
387               end
388   
389               select @user_id = (select suid from master..syslogins
390                       where (name = @scope))
391           end
392       end
393   
394       /* Attribute if for binding */
395       select @attrib_id = 1
396   
397       begin transaction tempdb_bind
398   
399       /*
400       ** If binding already exists then update it,
401       ** else insert the new binding
402       */
403       if exists (select * from master..sysattributes
404               where class = @class_id
405                   AND attribute = @attrib_id
406                   AND object_type = @obj_type
407                   AND object_cinfo = @app_name
408                   AND object = @user_id)
409       begin
410   
411   
412           select @action = @ATTR_CHANGE
413   
414           /* Update the existing binding */
415           update master..sysattributes
416           set char_value = @tdb, int_value = @new_bind_type,
417               object_info1 = @hardorsoft
418           where class = @class_id
419               AND attribute = @attrib_id
420               AND object_type = @obj_type
421               AND object_cinfo = @app_name
422               AND object = @user_id
423   
424           if (@@error != 0)
425           begin
426               /*
427               ** 18609, "%1!: Update row of master.dbo.sysattributes 
428               **	   failed. Command aborted."
429               */
430               raiserror 18609, "sp_multdb_bind"
431               goto error_exit
432           end
433   
434       end
435       else
436       begin
437   
438           select @action = @ATTR_ADD
439   
440           /* Insert the new binding */
441           insert into master..sysattributes
442           (class, attribute, object_type, object_cinfo, object,
443               object_info1, int_value, char_value)
444           values
445           (@class_id, @attrib_id, @obj_type, @app_name, @user_id,
446               @hardorsoft, @new_bind_type, @tdb)
447   
448           if (@@error != 0)
449           begin
450               /*
451               ** 18610, "%1!: Insert row to master.dbo.sysattributes 
452               **	   failed. Command aborted."
453               */
454               raiserror 18610, "sp_multdb_bind"
455               goto error_exit
456           end
457   
458       end
459   
460       /*
461       ** Notify the server about the new/updated binding.
462       ** Note that for SMP, @instanceid is always NULL and should be ignored
463       ** by the notification handler routine.
464       **
465       ** Currently, no action is taken upon notification of a new binding.
466       ** Uncomment the following notification when some action needs to be
467       ** implemented.
468       */
469       --select @notify_status =  
470       --	attrib_notify (@class_id, @attrib_id, @obj_type, @user_id,
471       --		@hardorsoft, @instanceid, NULL, @app_name, 
472       --		@new_bind_type, @tdb, NULL, NULL, "", @action)
473       --
474       --if (@notify_status = 0)
475       --begin
476       --	/*
477       --	** Server responded with failure, rollback the transaction.
478       --	**
479       --	** 18953, "Internal Error: failed to add binding."
480       --	*/
481       --      raiserror 18953
482       --	goto error_exit
483       --end
484   
485       /* Commit the transaction */
486       commit transaction tempdb_bind
487       return (0)
488   
489   error_exit:
490       rollback transaction tempdb_bind
491       return (1)
492   

DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch object_type: char(2) = varchar(30) 445
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
106
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..syslogins  
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 214
 MUCO 3 Useless Code Useless Brackets 223
 MUCO 3 Useless Code Useless Brackets 237
 MUCO 3 Useless Code Useless Brackets 244
 MUCO 3 Useless Code Useless Brackets 250
 MUCO 3 Useless Code Useless Brackets 260
 MUCO 3 Useless Code Useless Brackets 266
 MUCO 3 Useless Code Useless Brackets 279
 MUCO 3 Useless Code Useless Brackets 292
 MUCO 3 Useless Code Useless Brackets 319
 MUCO 3 Useless Code Useless Brackets 331
 MUCO 3 Useless Code Useless Brackets 334
 MUCO 3 Useless Code Useless Brackets 340
 MUCO 3 Useless Code Useless Brackets 351
 MUCO 3 Useless Code Useless Brackets 355
 MUCO 3 Useless Code Useless Brackets 357
 MUCO 3 Useless Code Useless Brackets 366
 MUCO 3 Useless Code Useless Brackets 375
 MUCO 3 Useless Code Useless Brackets 386
 MUCO 3 Useless Code Useless Brackets 390
 MUCO 3 Useless Code Useless Brackets 424
 MUCO 3 Useless Code Useless Brackets 448
 MUCO 3 Useless Code Useless Brackets 487
 MUCO 3 Useless Code Useless Brackets 491
 MUPK 3 Update column which is part of a PK or unique index object_info1 416
 QAFM 3 Var Assignment from potentially many rows 104
 QISO 3 Set isolation level 132
 QIWC 3 Insert with not all columns specified missing 7 columns out of 15 442
 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_cinfo, attribute, class}
310
 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}
404
 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}
418
 VNRD 3 Variable is not read @SDC 98
 VNRD 3 Variable is not read @svrmode 102
 VNRD 3 Variable is not read @instanceid 109
 VNRD 3 Variable is not read @obj_type_str 166
 VNRD 3 Variable is not read @action 438
 VUNU 3 Variable is not used @object_cinfo 60
 VUNU 3 Variable is not used @existing_bind_type 64
 VUNU 3 Variable is not used @group_id 67
 VUNU 3 Variable is not used @notify_status 81
 VUNU 3 Variable is not used @DBT3_LOCAL_USER_TEMPDB 91
 MSUB 2 Subquery Marker 282
 MSUB 2 Subquery Marker 309
 MSUB 2 Subquery Marker 343
 MSUB 2 Subquery Marker 354
 MSUB 2 Subquery Marker 377
 MSUB 2 Subquery Marker 389
 MSUB 2 Subquery Marker 403
 MTR1 2 Metrics: Comments Ratio Comments: 53% 49
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 35 = 46dec - 13exi + 2 49
 MTR3 2 Metrics: Query Complexity Complexity: 164 49

DEPENDENCIES
PROCS AND TABLES USED
reads table master..syslogins (1)  
reads table master..sysdatabases (1)  
reads table master..spt_values (1)  
read_writes table master..sysattributes (1)  

CALLERS
called by proc sybsystemprocs..sp_tempdb