DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drop_qpgroup  14 déc. 14Defects Propagation Dependencies

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


exec sp_procxmode 'sp_drop_qpgroup', 'AnyMode'
go

Grant Execute on sp_drop_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) 118
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {gid}
136
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 154
 QTYP 4 Comparison type mismatch smallint = int 154
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 155
 QTYP 4 Comparison type mismatch smallint = int 155
 MGTP 3 Grant to public sybsystemprocs..sp_drop_qpgroup  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MNER 3 No Error Check should check return value of exec 116
 MNER 3 No Error Check should check return value of exec 118
 MNER 3 No Error Check should check @@error after delete 153
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 173
 QISO 3 Set isolation level 106
 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, attribute, class}
154
 VNRD 3 Variable is not read @gp_enabled 58
 VNRD 3 Variable is not read @dummy 72
 MSUB 2 Subquery Marker 135
 MTR1 2 Metrics: Comments Ratio Comments: 62% 24
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 4 = 10dec - 8exi + 2 24
 MTR3 2 Metrics: Query Complexity Complexity: 54 24

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