DatabaseProcApplicationCreatedLinks
sybsystemprocssp_add_qpgroup  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_add_qpgroup"
6     **
7     ** 17240, "'%1!' is not a valid name."
8     ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
9     ** 17260, "Can't run %1! from within a transaction."
10    ** 17756, "The execution of the stored procedure '%1!' in database
11    **         '%2!' was aborted because there was an error in writing the
12    **         replication log record."
13    ** 18660, "There is already a query plans group named '%1!' in this database."
14    */
15    
16    /*
17    ** sp_add_qpgroup adds a new AP GID - group name 
18    ** association in SYSATTRIBUTES. 
19    */
20    
21    create or replace procedure sp_add_qpgroup
22        @new_name varchar(255)
23    as
24        declare
25            @gid int,
26            @class int,
27            @attribute int,
28            @gp_enabled int,
29            @status int,
30            @status1 int,
31            @dummy int,
32            @object_type char(2),
33            @nullarg char(1),
34            @dbname varchar(255)
35    
36        set nocount on
37    
38        select @dbname = db_name()
39        select @status1 = 1
40    
41    
42        /* Check to see that the @new_name is valid. */
43        if ((@new_name is not null) and (char_length(@new_name) > 30))
44        begin
45            /*
46            ** 17240, "'%1!' is not a valid name."
47            */
48            raiserror 17240, @new_name
49            return 1
50        end
51    
52    
53        /* 
54        ** If granular permissions is not enabled then sa_role is required.
55        ** If granular permissions is enabled then the permission 'manage abstract 
56        ** plans' is required.  proc_role and proc_auditperm will also do auditing
57        ** if required. Both will also print error message if required.
58        */
59    
60        select @nullarg = NULL
61        execute @status = sp_aux_checkroleperm "dbo",
62            "manage abstract plans", @dbname, @gp_enabled output
63    
64        if (@status != 0)
65        begin
66            /*
67            ** Send apropriate audit records.
68            */
69            if (@gp_enabled = 0)
70            begin
71                execute @status1 = sp_aux_checkroleperm "sa_role",
72                    @nullarg, @nullarg, @gp_enabled output
73                if (@status1 > 0)
74                begin
75                    /*
76                    ** 17230, "You must be the System
77                    ** Administrator (SA) or the Database
78                    ** Owner (dbo) to execute this procedure."
79                    */
80                    raiserror 17230
81                    return 2
82                end
83            end
84            else
85            begin
86                select @dummy = proc_auditperm("manage abstract plans",
87                        @status, @dbname)
88                return (2)
89            end
90        end
91    
92        /* For Auditing */
93        if (@gp_enabled = 0)
94            if (@status1 = 0)
95                select @dummy = proc_role("sa_role")
96            else
97                select @dummy = proc_auditperm("manage abstract plans",
98                        @status, @dbname)
99    
100       /*
101       ** AP group handling may not be done within a transaction, 
102       ** specifically for sp_add_qpgroup. 
103       **
104       ** Indeed, once a group is created, it can be enabled in 
105       ** DUMP mode and plans start being captured therein - as 
106       ** nested internal Xacts. So if the group creation is part 
107       ** of an user Xact that is rolled back, the APs stay behind, 
108       ** group-less. 
109       **
110       ** sp_drop_qpgroup and sp_rename_qpgroup don't raise 
111       ** the same issue, but for coherence they are subject 
112       ** to the same limitation.
113       */
114       if @@trancount > 0
115       begin
116           /*
117           ** 17260, "Can't run %1! from within a transaction."
118           */
119           raiserror 17260, "sp_add_qpgroup"
120           return 1
121       end
122   
123       /* Don't start a transaction for the user, he does not expect it. */
124       set chained off
125   
126       /* Don't hold long READ locks, the user might not appreciate it. */
127       set transaction isolation level 1
128   
129       exec sp_aux_sargs_qpgroup @class out, @attribute out, @object_type out
130   
131       /*
132       ** This transaction also writes a log record for replicating the
133       ** invocation of this procedure. If logexec() fails, the transaction
134       ** is aborted.
135       **
136       ** IMPORTANT: The name rs_logexec is significant and is used by
137       ** Replication Server.
138       */
139       begin transaction rs_logexec
140   
141       exec sp_aux_get_qpgroup @new_name, @gid out
142   
143       if @gid is not null
144       begin
145           /* 18660, "There is already a query plans group named '%1!' in this database." */
146           raiserror 18660, @new_name
147           rollback transaction rs_logexec
148           return 1
149       end
150   
151       select @gid = isnull(max(object) + 1, 1) from sysattributes
152           holdlock
153       where class = @class
154           and attribute = @attribute
155           and object_type = @object_type
156   
157   
158       insert into sysattributes
159       (class, attribute, object_type, object, object_cinfo)
160       values (@class, @attribute, @object_type, @gid, @new_name)
161   
162       /*
163       ** Write the log record to replicate this invocation
164       ** of the stored procedure.
165       */
166       if (logexec() != 1)
167       begin
168           /*
169           ** 17756, "The execution of the stored procedure
170           **         '%1!' in database '%2!' was aborted
171           **          because there was an error in writing
172           **          the replication log record."
173           */
174           raiserror 17756, "sp_add_qpgroup", @dbname
175           rollback transaction rs_logexec
176           return (1)
177       end
178   
179       commit transaction rs_logexec
180       return 0
181   


exec sp_procxmode 'sp_add_qpgroup', 'AnyMode'
go

Grant Execute on sp_add_qpgroup to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MTYP 4 Assignment type mismatch @name: varchar(30) = varchar(255) 141
 MTYP 4 Assignment type mismatch attribute: smallint = int 160
 MTYP 4 Assignment type mismatch class: smallint = int 160
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 153
 QTYP 4 Comparison type mismatch smallint = int 153
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 154
 QTYP 4 Comparison type mismatch smallint = int 154
 MGTP 3 Grant to public sybsystemprocs..sp_add_qpgroup  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MNER 3 No Error Check should check return value of exec 129
 MNER 3 No Error Check should check return value of exec 141
 MNER 3 No Error Check should check @@error after insert 158
 MUCO 3 Useless Code Useless Brackets 43
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 73
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 176
 QISO 3 Set isolation level 127
 QIWC 3 Insert with not all columns specified missing 10 columns out of 15 159
 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}
153
 VNRD 3 Variable is not read @dummy 97
 MTR1 2 Metrics: Comments Ratio Comments: 54% 21
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 13dec - 7exi + 2 21
 MTR3 2 Metrics: Query Complexity Complexity: 57 21

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

CALLERS
called by proc sybsystemprocs..sp_import_qpgroup