DatabaseProcApplicationCreatedLinks
sybsystemprocssp_multdb_creategroup  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** Messages for "sp_multdb_creategroup"
5     ** 
6     ** 17260, "Can't run %1! from within a transaction."
7     ** 17240, "'%1!' is not a valid name."
8     ** 18610, "%1!: Insert row to master.dbo.sysattributes failed. Command aborted."
9     ** 18941, "Currently only 'default' group is supported."
10    ** 18967, "Group '%1!' already exists."
11    ** 18942, "Internal Error: failed to create group."
12    */
13    
14    /*
15    ** Procedure sp_multdb_creategroup
16    **
17    ** This procedure adds a new group in SYSATTRIBUTES
18    ** for the MULTEMPDB_CLASS (class 16)
19    **      
20    */
21    create or replace procedure sp_multdb_creategroup
22        @tdbgroup varchar(255) /* temp db group */
23    as
24    
25        declare @class_id smallint, /* class in SYSATTRIBUTES */
26            @attrib_id smallint, /* attribute in SYSATTRIBUTES */
27            @object_type char(2), /* object type in SYSATTRIBUTES */
28            @object_cinfo varchar(255), /* object_cinfo in SYSATTRIBUTES */
29            @int_val int, /* int_value in SYSATTRIBUTES */
30            @group_id int, /* group id database being added to */
31            @action int, /* action for built in */
32            @upcase_str varchar(30),
33            @svrmode int, /* Indicates the SMP or SDC mode */
34            @SMP int, /* Indicates SMP Server */
35            @SDC int, /* Indicates SDC Server */
36            @action_code int, /* Indication action code for the op. */
37            @MULTDB_ACTION_DO int, /* Action code that operation
38            ** completed successfully
39            */
40            @MULTDB_ACTION_UNDO int /* Action code to indicate that 
41        ** operation did not complete
42        */
43    
44        select @SMP = 0,
45            @SDC = 1
46    
47        if @@clustermode != "shared disk cluster"
48        begin
49            select @svrmode = @SMP
50        end
51        else
52        begin
53            select @svrmode = @SDC
54    
55            /* 
56            ** These values must be kept in sync with their definitions in
57            ** multempdb.h.
58            */
59    
60            select @MULTDB_ACTION_DO = 2
61            select @MULTDB_ACTION_UNDO = 3
62        end
63    
64        /*
65        **  if we're in a transaction, disallow this since it might make recovery
66        **  impossible.
67        */
68        if @@trancount > 0
69        begin
70            raiserror 17260, "sp_multdb_creategroup"
71            return (1)
72        end
73        else
74        begin
75            /* Use TSQL mode of unchained transactions */
76            set chained off
77        end
78    
79        /* Dont do "Dirty Reads" */
80        set transaction isolation level 1
81    
82        select @class_id = 16 /* class is MULTEMPDB_CLASS */
83        select @attrib_id = 0 /* attribute is MULTEMPDB_GROUP */
84        select @object_type = 'GR' /* Object type if 'GR' for temp. db group */
85        select @action = 1 /* add new group */
86    
87        /*
88        ** Make sure group name is valid.
89        */
90        if (@tdbgroup != "default")
91        begin
92            if valid_name(@tdbgroup) = 0
93            begin
94                raiserror 17240, @tdbgroup
95                return (1)
96            end
97        end
98    
99        /*
100       ** Check if group already exists. It shouldn't.
101       */
102       if exists (select * from master..sysattributes
103               where class = @class_id
104                   AND attribute = @attrib_id
105                   AND object_type = @object_type
106                   AND object_cinfo = @tdbgroup)
107       begin
108           raiserror 18967, @tdbgroup
109           return (1)
110       end
111   
112       /*
113       ** The "default" group id is fixed at 0.
114       ** For a different group, find the next available id to use.
115       */
116       if (@tdbgroup = "default")
117       begin
118           select @group_id = 0
119       end
120       else
121       begin
122           select @group_id = (select MAX(int_value)
123                   from master..sysattributes
124                   where class = @class_id
125                       AND attribute = @attrib_id
126                       AND object_type = @object_type)
127   
128           select @group_id = @group_id + 1
129       end
130   
131       /*
132       ** Insert the group entry into the sysattributes.
133       */
134       begin tran crt_tdbgrp
135   
136       /*
137       ** Send the notification to indicate the beginning of the transaction
138       ** to allow cluster synchronization mechanism to be deployed.
139       */
140       if (@svrmode = @SDC)
141       begin
142           select @action = 5 /* ATTR_BEGIN */
143           if (attrib_notify(@class_id, @attrib_id, @object_type,
144                   NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
145                   NULL, "", @action)) = 0
146           begin
147               raiserror 18942
148               goto error_exit
149           end
150       end
151   
152       insert into master..sysattributes
153       (class, attribute, object_type, object_cinfo, int_value)
154       values
155       (@class_id, @attrib_id, @object_type, @tdbgroup, @group_id)
156   
157       if (@@error != 0)
158       begin
159           raiserror 18610, "sp_multdb_creategroup"
160           goto error_exit
161       end
162   
163       /*
164       ** Notify the addition of the new group.
165       */
166       select @action = 1 /* ATTR_ADD */
167       if (attrib_notify(@class_id, @attrib_id, @object_type, NULL, NULL, NULL,
168               NULL, @tdbgroup, @group_id, NULL, NULL, NULL, "",
169               @action)) = 0
170       begin
171           raiserror 18942
172           goto error_exit
173       end
174   
175       commit tran
176       /*
177       ** Send notification to communicate that transaction is committed and
178       ** in-memory changes be made final.
179       */
180       if (@svrmode = @SDC)
181       begin
182           select @action = 6 /* ATTR_END */
183           select @action_code = @MULTDB_ACTION_DO
184           select attrib_notify(@class_id, @attrib_id, @object_type,
185                   NULL, NULL, NULL, NULL, NULL, @action_code, NULL, NULL,
186                   NULL, "", @action)
187       end
188       return (0)
189   
190   error_exit:
191       /*
192       ** In SDC, the in-memory changes are undone as part of the 
193       ** abort transaction processing. So, there is no need to send
194       ** end notification with @MULTDB_ACTION_UNDO action.
195       */
196       rollback tran crt_tdbgrp
197       return (1)
198   

RESULT SETS
sp_multdb_creategroup_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 145
 MEST 4 Empty String will be replaced by Single Space 168
 MEST 4 Empty String will be replaced by Single Space 186
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MGTP 3 Grant to public master..sysattributes  
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 197
 QCRS 3 Conditional Result Set 184
 QISO 3 Set isolation level 80
 QIWC 3 Insert with not all columns specified missing 10 columns out of 15 153
 QNAM 3 Select expression has no name attrib_notify(@class_id, @attrib_id, @object_type, NULL, NULL, NULL, NULL, NULL, @action_code, NULL,... 184
 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}
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: {class, object_type, attribute}
124
 VNRD 3 Variable is not read @MULTDB_ACTION_UNDO 61
 VUNU 3 Variable is not used @object_cinfo 28
 VUNU 3 Variable is not used @int_val 29
 VUNU 3 Variable is not used @upcase_str 32
 MRST 2 Result Set Marker 184
 MSUB 2 Subquery Marker 102
 MSUB 2 Subquery Marker 122
 MTR1 2 Metrics: Comments Ratio Comments: 41% 21
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 17dec - 4exi + 2 21
 MTR3 2 Metrics: Query Complexity Complexity: 72 21

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..sysattributes (1)  
writes table sybsystemprocs..sp_multdb_creategroup_rset_001 

CALLERS
called by proc sybsystemprocs..sp_tempdb