DatabaseProcApplicationCreatedLinks
sybsystemprocssp_multdb_addtogroup  14 déc. 14Defects Propagation Dependencies

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

RESULT SETS
sp_multdb_addtogroup_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 216
 MEST 4 Empty String will be replaced by Single Space 235
 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}
111
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
116
 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 79
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 197
 MUCO 3 Useless Code Useless Brackets 215
 MUCO 3 Useless Code Useless Brackets 229
 MUCO 3 Useless Code Useless Brackets 237
 MUCO 3 Useless Code Useless Brackets 246
 QAFM 3 Var Assignment from potentially many rows 109
 QAFM 3 Var Assignment from potentially many rows 114
 QCRS 3 Conditional Result Set 233
 QISO 3 Set isolation level 88
 QIWC 3 Insert with not all columns specified missing 10 columns out of 15 193
 QNAM 3 Select expression has no name attrib_notify(@class_id, @attrib_id, @object_type, NULL, NULL, NULL, NULL, NULL, @action_code, NULL,... 233
 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}
157
 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}
185
 VNRD 3 Variable is not read @MULTDB_ACTION_UNDO 69
 VUNU 3 Variable is not used @object_cinfo 34
 VUNU 3 Variable is not used @int_val 35
 VUNU 3 Variable is not used @upcase_str 38
 VUNU 3 Variable is not used @instanceid 41
 MRST 2 Result Set Marker 233
 MSUB 2 Subquery Marker 124
 MSUB 2 Subquery Marker 156
 MSUB 2 Subquery Marker 184
 MTR1 2 Metrics: Comments Ratio Comments: 47% 26
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 20 = 24dec - 6exi + 2 26
 MTR3 2 Metrics: Query Complexity Complexity: 95 26

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

CALLERS
called by proc sybsystemprocs..sp_tempdb