DatabaseProcApplicationCreatedLinks
sybsystemprocssp_addengine  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */
3     
4     /*
5     ** Messages for "sp_addengine"
6     **
7     ** 17260, "Can't run %1! from within a transaction."
8     ** 18248, "Validation of Engine Group modification failed. Check server
9     **	   errorlog for any additional information."
10    ** 18249, "Name '%1!' is not valid."
11    ** 18250, "Failed to add engine '%1!' to engine group '%2!'. Check server
12    **	   errorlog for any additional information. List of engines in
13    **	   engine group unchanged.
14    ** 18251, "Cannot modify pre-defined engine groups."
15    ** 19703, "Instance id '%1!' is not valid under instance system view."
16    */
17    
18    create or replace procedure sp_addengine
19        @engine_number int, /* engine number of engine */
20        @engine_group varchar(255), /* group that engine belongs to */
21        @instanceid smallint = NULL /* instance id */
22    as
23    
24        declare @attrib_id int,
25            @action int,
26            @engine_name varchar(5),
27            @object_type varchar(2),
28            @new_list varchar(768),
29            @engine_list varchar(768),
30            @upcase_str varchar(255),
31            @nullarg char(1),
32            @dummy int,
33            @status int,
34            @gp_enabled int
35    
36    
37        IF @@kernelmode != 'process'
38        BEGIN
39            /*
40            ** 17117 - "The '%1!' command is supported only in process kernel mode"
41            */
42            raiserror 17117, "sp_addengine"
43            return 1
44        END
45    
46        /*
47        **  IF we're in a transaction, disallow this since it might make recovery
48        **  impossible.
49        **  This check must be done before any select statement. Otherwise,
50        **  the select statement might start a transaction if the session is
51        **  running in chained mode.
52        */
53        IF @@trancount > 0
54        BEGIN
55            /*
56            ** 17260, "Can't run %1! from within a transaction."
57            */
58            raiserror 17260, "sp_addengine"
59            return (1)
60        END
61        ELSE
62        BEGIN
63            /* Use TSQL mode of unchained transactions */
64            set chained off
65        END
66    
67        select @action = 1 /* add or modify entry */
68        select @attrib_id = 1 /* attribute is ENGINE_GROUP */
69        select @object_type = 'EG'
70        select @engine_list = NULL /* var to hold list of engines in group */
71    
72        /* Dont do "Dirty Reads" */
73        set transaction isolation level 1
74    
75        /*
76        ** Check to see that the input params are correct and then hook up with
77        ** Sysattributes table to enter data.
78        */
79    
80        /* first convert any system defined engine group name to upper case */
81        select @upcase_str = upper(@engine_group)
82        IF ((@upcase_str = "ANYENGINE") OR (@upcase_str = "LASTONLINE"))
83            select @engine_group = @upcase_str
84    
85        /* Cannot add to pre-defined groups */
86        if (@engine_group = "ANYENGINE" OR @engine_group = "LASTONLINE")
87        BEGIN
88            /*
89            ** 18251, "Cannot modify pre-defined engine groups."        
90            */
91            raiserror 18251
92            return (1)
93        END
94    
95        IF ((@engine_group != "ANYENGINE") AND (@engine_group != "LASTONLINE"))
96        BEGIN
97            IF valid_name(@engine_group, 30) = 0
98            BEGIN
99                /*
100               ** 18249, "Name '%1!' is not valid."
101               */
102               raiserror 18249, @engine_group
103               return (1)
104           END
105       END
106   
107       /* For SDC, use @@instanceid if NULL is specified */
108       if (@@clustermode = "shared disk cluster")
109       BEGIN
110           if ((@@system_view = "instance") AND
111                   (@instanceid != NULL) AND
112                   (@instanceid != @@instanceid))
113           BEGIN
114               /*
115               ** 19703, "Instance id '%1!' is not valid under instance 
116               **	   system view."
117               */
118               raiserror 19703, @instanceid
119               return (1)
120           END
121   
122           if (@instanceid = NULL)
123               select @instanceid = @@instanceid
124       END
125       else
126           select @instanceid = NULL
127   
128   
129       /*
130       ** If granular permissions is not enabled then sa_role is required.
131       ** If granular permissions is enabled then the permission 
132       ** 'manage any execution class' is required.  proc_role and proc_auditperm 
133       ** will also do auditing if required. Both will also print error message 
134       ** if required.
135       */
136   
137       select @nullarg = NULL
138       execute @status = sp_aux_checkroleperm "sa_role", "manage any execution class",
139           @nullarg, @gp_enabled output
140   
141       /* For Auditing */
142       if (@gp_enabled = 0)
143       begin
144           if (proc_role("sa_role") = 0)
145               return (1)
146       end
147       else
148       begin
149           select @dummy = proc_auditperm("manage any execution class", @status)
150       end
151   
152       if (@status != 0)
153           return (1)
154       /*
155       ** The validation routine checks that the supplied engine is not
156       ** already in the group and that it is a valid engine number
157       */
158       IF attrib_valid(6, @attrib_id, @object_type, NULL, NULL, NULL,
159               @instanceid, @engine_group, @engine_number, NULL, NULL,
160               NULL, "", @action) = 0
161       BEGIN
162           /*
163           ** 18248, "Validation of Engine Group modification failed. Check
164           **	   server errorlog for any additional information."
165           */
166           raiserror 18248
167           return (1)
168       END
169   
170       IF ((@engine_group != "ANYENGINE") AND (@engine_group != "LASTONLINE"))
171       BEGIN
172           /*
173           ** The list of engines belonging to an engine
174           ** group are kept in the format -
175           ** engine # : engine # : ....
176           ** So add the new engine in the correct format
177           */
178           select @engine_name = str(@engine_number, 5)
179           IF exists (select * from master..sysattributes where
180                       class = 6 AND
181                       attribute = 1 AND
182                       object_type = 'EG' AND
183                       object_cinfo = @engine_group AND
184                       object_info3 = @instanceid)
185           BEGIN
186               select @engine_list = (select char_value
187                       from master..sysattributes where
188                           class = 6 AND
189                           attribute = 1 AND
190                           object_type = 'EG' AND
191                           object_cinfo = @engine_group AND
192                           object_info3 = @instanceid)
193   
194               select @new_list = (@engine_list +
195                   @engine_name + ":")
196   
197               update master..sysattributes
198               set char_value = @new_list
199               where class = 6 AND
200                   attribute = 1 AND
201                   object_type = 'EG' AND
202                   object_cinfo = @engine_group AND
203                   object_info3 = @instanceid
204           END
205           ELSE
206           BEGIN
207               select @new_list = (@engine_name + ":")
208               insert master..sysattributes
209               (class, attribute, object_type, object_info3, object_cinfo, char_value)
210               values (6, @attrib_id, @object_type, @instanceid, @engine_group, @new_list)
211           END
212       END
213   
214       if (attrib_notify(6, @attrib_id, @object_type, NULL, @engine_number, NULL,
215               @instanceid, @engine_group, NULL, NULL, NULL, NULL, "",
216               @action)) = 0
217       BEGIN
218           /*
219           ** 18250, "Failed to add engine '%1!' to engine group '%2!'. Check
220           **	   server errorlog for any additional information. List of
221           **	   engines in engine group unchanged."
222           */
223           IF (@engine_list is not NULL)
224               update master..sysattributes
225               set char_value = @engine_list
226               where class = 6 AND
227                   attribute = 1 AND
228                   object_type = 'EG' AND
229                   object_cinfo = @engine_group AND
230                   object_info3 = @instanceid
231           ELSE
232               delete master..sysattributes where
233                   class = 6 AND
234                   attribute = 1 AND
235                   object_type = 'EG' AND
236                   object_cinfo = @engine_group AND
237                   object_info3 = @instanceid
238   
239           raiserror 18250, @engine_number, @engine_group
240           return (1)
241       END
242   
243       return (0)
244   


exec sp_procxmode 'sp_addengine', 'AnyMode'
go

Grant Execute on sp_addengine to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 160
 MEST 4 Empty String will be replaced by Single Space 215
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch @instanceid: smallint = int 123
 MTYP 4 Assignment type mismatch attribute: smallint = int 210
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 180
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 181
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 184
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 188
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 189
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 192
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 199
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 200
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 203
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 226
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 227
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 230
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 233
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 234
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 237
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sp_addengine  
 MNAC 3 Not using ANSI 'is null' 111
 MNAC 3 Not using ANSI 'is null' 122
 MNER 3 No Error Check should check @@error after update 197
 MNER 3 No Error Check should check @@error after insert 208
 MNER 3 No Error Check should check @@error after update 224
 MNER 3 No Error Check should check @@error after delete 232
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 144
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 153
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 214
 MUCO 3 Useless Code Useless Brackets 223
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 243
 MUOT 3 Updates outside transaction 232
 QISO 3 Set isolation level 73
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 209
 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_info3, object_cinfo, attribute, class}
180
 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_info3, object_cinfo, attribute, class}
188
 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_info3, object_cinfo, attribute, class}
199
 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_info3, object_cinfo, attribute, class}
226
 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_info3, object_cinfo, attribute, class}
233
 VNRD 3 Variable is not read @dummy 149
 MSUB 2 Subquery Marker 179
 MSUB 2 Subquery Marker 186
 MTR1 2 Metrics: Comments Ratio Comments: 39% 18
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 25 = 32dec - 9exi + 2 18
 MTR3 2 Metrics: Query Complexity Complexity: 111 18

DATA PROPAGATION detailed
ColumnWritten To
@engine_groupsysattributes.object_cinfo   sp_optgoal_rset_002.name sp_passwordpolicy_rset_001.value sp_rjs_retrieve_rset_001.js_server
@engine_numbersysattributes.char_value   sysattributes.int_value   °.char_value   sp_dropdevice_rset_001.device sp_dropdevice_rset_001.device sp_forceonline_object_rset_001.status sp_forceonline_object_rset_002.status sp_forceonline_page_rset_001.status sp_forceonline_page_rset_002.status sp_listsuspect_object_rset_001.Access
sp_listsuspect_page_rset_001.Access sp_makesuspect_obj_rset_001.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_002.Indid °.LogType °.PageType
°.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_003.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_passwordpolicy_rset_001.message
sp_passwordpolicy_rset_002.value sp_passwordpolicy_rset_003.value sp_passwordpolicy_rset_004.Policy_option sp_rjs_retrieve_rset_001.host_name sp_setsuspect_granularity_rset_001.Online mode sp_ssladmin_rset_001.certificate_path sp_ssladmin_rset_002.Cipher Suite Name °.Preference
@instanceidsysattributes.object_info3   °.char_value   sysattributes.object_info3   °.int_value   °.char_value   sp_dropdevice_rset_001.device sp_dropdevice_rset_001.device sp_forceonline_object_rset_001.status sp_forceonline_object_rset_002.status sp_forceonline_page_rset_001.status
sp_forceonline_page_rset_002.status sp_listsuspect_object_rset_001.Access sp_listsuspect_page_rset_001.Access sp_makesuspect_obj_rset_001.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_002.Indid
°.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_003.Indid °.LogType °.PageType °.ErrType °.Delay
°.TotalNum sp_passwordpolicy_rset_001.message sp_passwordpolicy_rset_002.value sp_passwordpolicy_rset_003.value sp_passwordpolicy_rset_004.Policy_option sp_rjs_retrieve_rset_001.host_name sp_setsuspect_granularity_rset_001.Online mode sp_ssladmin_rset_001.certificate_path sp_ssladmin_rset_002.Cipher Suite Name °.Preference

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..sysattributes (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)