DatabaseProcApplicationCreatedLinks
sybsystemprocssp_multdb_removefromgroup  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** Messages for "sp_multdb_removefromgroup"
5     ** 
6     ** 17260, "Can't run %1! from within a transaction."
7     ** 17240, "'%1!' is not a valid name."
8     ** 18946, "Either the database '%1!' does not exist or is not a user 
9     **         created temporary database."
10    ** 18947, "Group '%1!' does not exist."
11    ** 18950, "Internal Error: failed to remove database from group."
12    ** 18975, "Database '%1!' is not a member of the '%2!' group."
13    ** 18608, "%1!: Delete row from master.dbo.sysattributes failed. 
14    **	   Command aborted."
15    ** 19580, "Either the database '%1!' does not exist or is not a
16    **	   local user temporary database."
17    */
18    
19    
20    /*
21    ** Procedure sp_multdb_removefromgroup
22    **
23    ** This procedure removes a database from a group in SYSATTRIBUTES
24    ** for the MULTEMPDB_CLASS (class 16)
25    **      
26    */
27    create or replace procedure sp_multdb_removefromgroup
28        @tdb varchar(255), /* temporary database to be bound */
29        @tdbgroup varchar(255) /* temp db group */
30    as
31    
32        declare @class_id smallint, /* class in SYSATTRIBUTES */
33            @attrib_id smallint, /* attribute in SYSATTRIBUTES */
34            @object_type char(2), /* object type in SYSATTRIBUTES */
35            @object_cinfo varchar(255), /* object_cinfo in SYSATTRIBUTES */
36            @int_val int, /* int_value in SYSATTRIBUTES */
37            @group_id int, /* group id database being added to */
38            @action int, /* action for built in */
39            @upcase_str varchar(30),
40            @instanceid int,
41            @bindabletdb_stat int, /* dbstatus for database can be added
42            **  to a tdb group */
43            @svrmode int, /* Indicates the SMP or SDC mode */
44            @SMP int, /* Indicates SMP Server */
45            @SDC int, /* Indicates SDC Server */
46            @action_code int, /* Indicates action code for the op. */
47            @MULTDB_ACTION_DO int, /* Action code that operation
48            ** completed successfully
49            */
50            @MULTDB_ACTION_UNDO int /* Action code to indicate that 
51        ** operation did not complete
52        */
53    
54        select @SMP = 0,
55            @SDC = 1
56    
57        if @@clustermode != "shared disk cluster"
58        begin
59            select @svrmode = @SMP
60        end
61        else
62        begin
63            select @svrmode = @SDC
64    
65            /* 
66            ** These values must be kept in sync with their definitions in
67            ** multempdb.h.
68            */
69            select @MULTDB_ACTION_DO = 2
70            select @MULTDB_ACTION_UNDO = 3
71        end
72    
73        /*
74        **  if we're in a transaction, disallow this since it might make recovery
75        **  impossible.
76        */
77        if @@trancount > 0
78        begin
79            raiserror 17260, "sp_multdb_removefromgroup"
80            return (1)
81        end
82        else
83        begin
84            /* Use TSQL mode of unchained transactions */
85            set chained off
86        end
87    
88        /* Dont do "Dirty Reads" */
89        set transaction isolation level 1
90    
91        select @class_id = 16 /* class is MULTEMPDB_CLASS */
92        select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */
93        select @object_type = 'D ' /* Object type if 'D ' for temp. db */
94        select @action = 3 /* Drop Binding */
95    
96        /*
97        ** Make sure group name is valid.
98        */
99        if (@tdbgroup != "default")
100       begin
101           if valid_name(@tdbgroup) = 0
102           begin
103               raiserror 17240, @tdbgroup
104               return (1)
105           end
106       end
107   
108       /*
109       ** tempdb is a special case since its binding to the default group
110       ** is implicit and is not stored in sysattributes. So we cannot rely
111       ** on the lookup in sysattributes as we do for other databases to determine
112       ** if such a binding exists or not. 
113       ** In addition, we want to catch the type of database that can not be
114       ** specificly bind to a group and give proper msg here. Even though tempdb
115       ** in SMP is implicitly part of the default group, it can not be specificly
116       ** bind or unbind from any group.
117       */
118       if (@svrmode = @SMP)
119           select @bindabletdb_stat = number
120           from master.dbo.spt_values
121           where type = "D3" and name = "user created temp db"
122       else
123           select @bindabletdb_stat = number
124           from master.dbo.spt_values
125           where type = "D3" and name = "local user temp db"
126   
127       if not exists (select *
128               from master..sysdatabases
129               where name = @tdb
130                   AND (status3 & @bindabletdb_stat) = @bindabletdb_stat)
131       begin
132           if (@svrmode = @SMP)
133               raiserror 18946, @tdb
134           else
135               raiserror 19580, @tdb
136           return (1)
137       end
138   
139   
140       /*
141       ** Check if group exists and get group id
142       */
143       select @attrib_id = 0 /* attribute is MULTEMPDB_GROUP */
144       select @object_type = 'GR' /* Object type if 'GR' for temp. db group */
145   
146       select @group_id = (select int_value from master..sysattributes
147               where class = @class_id
148                   AND attribute = @attrib_id
149                   AND object_type = @object_type
150                   AND object_cinfo = @tdbgroup)
151   
152       /*
153       ** Group not found
154       */
155       if @group_id is NULL
156       begin
157           raiserror 18947, @tdbgroup
158           return (1)
159       end
160   
161       select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */
162       select @object_type = 'D ' /* Object type if 'D ' for temp db */
163   
164       begin tran unbind_tdbtogrp
165   
166       /*
167       ** Send the notification to indicate the beginning of the transaction
168       ** to allow cluster synchronization mechanisms to be deployed.
169       */
170       if (@svrmode = @SDC)
171       begin
172           select @action = 5 /* ATTR_BEGIN */
173           if (attrib_notify(@class_id, @attrib_id, @object_type,
174                   NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
175                   NULL, "", @action)) = 0
176           begin
177               raiserror 18950
178               goto error_exit
179           end
180       end
181   
182       /*
183       ** Delete the binding from sysattributes.
184       */
185       if exists (select * from master..sysattributes
186               where class = @class_id
187                   AND attribute = @attrib_id
188                   AND object_type = @object_type
189                   AND object_cinfo = @tdb
190                   AND object = @group_id)
191       begin
192           delete from master..sysattributes
193           where class = @class_id
194               AND attribute = @attrib_id
195               AND object_type = @object_type
196               AND object_cinfo = @tdb
197               AND object = @group_id
198   
199           if @@error != 0
200           begin
201               raiserror 18608, 'sp_multdb_removefromgroup'
202               goto error_exit
203           end
204       end
205       else
206       begin
207           raiserror 18975, @tdb, @tdbgroup
208           goto error_exit
209       end
210   
211       /*
212       ** Notify the unbinding of the database 
213       ** If this returns failure, then abort the transaction.
214       **
215       ** Note in SDC, object_info2 will hold the instance id of the tempdb;
216       ** while in SMP, this field is filled with null.
217       */
218       select @action = 3 /* ATTR_DROP */
219   
220       if (attrib_notify(@class_id, @attrib_id, @object_type, @group_id, NULL, NULL,
221               NULL, @tdb, NULL, NULL, NULL, NULL, "",
222               @action)) = 0
223   
224       begin
225           raiserror 18950
226           goto error_exit
227       end
228   
229       commit tran unbind_tdbgrp
230       /*
231       ** Send notification to communicate that transaction is committed and
232       ** in-memory changes be made final.
233       */
234       if (@svrmode = @SDC)
235       begin
236           select @action = 6 /* ATTR_END */
237           select @action_code = @MULTDB_ACTION_DO
238           select attrib_notify(@class_id, @attrib_id, @object_type,
239                   NULL, NULL, NULL, NULL, NULL, @action_code, NULL, NULL,
240                   NULL, "", @action)
241       end
242       return (0)
243   
244   error_exit:
245       /*
246       ** In SDC, the in-memory changes are undone as part of the 
247       ** abort transaction processing. So, there is no need to send
248       ** end notification with @MULTDB_ACTION_UNDO action.
249       */
250       rollback tran unbind_tdbtogrp
251       return (1)
252   

RESULT SETS
sp_multdb_removefromgroup_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 175
 MEST 4 Empty String will be replaced by Single Space 221
 MEST 4 Empty String will be replaced by Single Space 240
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
121
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
125
 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  
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 136
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 220
 MUCO 3 Useless Code Useless Brackets 234
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 251
 QAFM 3 Var Assignment from potentially many rows 119
 QAFM 3 Var Assignment from potentially many rows 123
 QCRS 3 Conditional Result Set 238
 QISO 3 Set isolation level 89
 QNAM 3 Select expression has no name attrib_notify(@class_id, @attrib_id, @object_type, NULL, NULL, NULL, NULL, NULL, @action_code, NULL,... 238
 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}
147
 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}
186
 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}
193
 VNRD 3 Variable is not read @MULTDB_ACTION_UNDO 70
 VUNU 3 Variable is not used @object_cinfo 35
 VUNU 3 Variable is not used @int_val 36
 VUNU 3 Variable is not used @upcase_str 39
 VUNU 3 Variable is not used @instanceid 40
 MRST 2 Result Set Marker 238
 MSUB 2 Subquery Marker 127
 MSUB 2 Subquery Marker 146
 MSUB 2 Subquery Marker 185
 MTR1 2 Metrics: Comments Ratio Comments: 46% 27
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 22 = 25dec - 5exi + 2 27
 MTR3 2 Metrics: Query Complexity Complexity: 103 27

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

CALLERS
called by proc sybsystemprocs..sp_tempdb