DatabaseProcApplicationCreatedLinks
sybsystemprocssp_rename_qpgroup  14 déc. 14Defects Propagation Dependencies

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


exec sp_procxmode 'sp_rename_qpgroup', 'AnyMode'
go

Grant Execute on sp_rename_qpgroup to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 156
 QTYP 4 Comparison type mismatch smallint = int 156
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 157
 QTYP 4 Comparison type mismatch smallint = int 157
 MGTP 3 Grant to public sybsystemprocs..sp_rename_qpgroup  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MNER 3 No Error Check should check return value of exec 110
 MNER 3 No Error Check should check return value of exec 113
 MNER 3 No Error Check should check return value of exec 131
 MNER 3 No Error Check should check @@error after update 154
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 68
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 165
 MUCO 3 Useless Code Useless Brackets 175
 MUPK 3 Update column which is part of a PK or unique index object_cinfo 155
 QISO 3 Set isolation level 108
 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}
156
 VNRD 3 Variable is not read @gp_enabled 62
 VNRD 3 Variable is not read @dummy 74
 MTR1 2 Metrics: Comments Ratio Comments: 59% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 11dec - 7exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 55 22

DATA PROPAGATION detailed
ColumnWritten To
@new_namesysattributes.object_cinfo  

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