DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dboption_flmode  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_dboption_flmode"    
6     **
7     ** 17421, "No such database -- run sp_helpdb to list databases."
8     ** 17422, "The 'master' database's options can not be changed."
9     ** 17423, "Usage: sp_dboption [dbname, optname, {true | false}]"
10    ** 17424, "Database option doesn't exist or can't be set by user."
11    ** 17425, "Run sp_dboption with no parameters to see options."
12    ** 17953, "The full logging mode cannot be defined for '%1!'."
13    ** 17431, "true"
14    ** 17432, "false"
15    */
16    
17    /* 
18    ** IMPORTANT: Please read the following instructions before
19    **   making changes to this stored procedure.
20    **
21    **	To make this stored procedure compatible with High Availability (HA),
22    **	changes to certain system tables must be propagated to the companion 
23    **	server under some conditions.
24    **	The tables include (but are not limited to):
25    **		syslogins, sysservers, sysattributes, systimeranges,
26    **		sysresourcelimits, sysalternates, sysdatabases,
27    **		syslanguages, sysremotelogins, sysloginroles,
28    **		sysalternates (master DB only), systypes (master DB only),
29    **		sysusers (master DB only), sysprotects (master DB only)
30    **	please refer to the HA documentation for details.
31    **
32    **	Here is what you need to do: 
33    **	For each insert/update/delete statement, add three sections to
34    **	-- start HA transaction prior to the statement
35    **	-- add the statement
36    **	-- add HA synchronization code to propagate the change to the companion
37    **
38    **	For example, if you are adding 
39    **		insert master.dbo.syslogins ......
40    **	the code should look like:
41    **	1. Before that SQL statement:
42    **		
43    **	2. Now, the SQL statement:
44    **		insert master.dbo.syslogins ......
45    **	3. Add a HA synchronization section right after the SQL statement:
46    **		
47    **
48    **	You may need to do similar change for each built-in function you
49    **	want to add.
50    **
51    **	Finally, add a separate part at a place where it can not
52    **	be reached by the normal execution path:
53    **	clean_all:
54    **		
55    **		return (1)
56    */
57    
58    create or replace procedure sp_dboption_flmode
59        @dbname varchar(30) = null,
60        @optname varchar(30) = null,
61        @optvalue varchar(10) = null
62    as
63    
64        declare @msg varchar(1024),
65            @fl_class smallint,
66            @action smallint,
67            @HA_CERTifIED tinyint, /* Is the SP HA certified ? */
68            @retstat int,
69            @type char(2),
70            @optval int,
71            @true varchar(10),
72            @false varchar(10),
73            @dbid int,
74            @dbuid int,
75            @bit int,
76            @bitmap int,
77            @newbitmap int,
78            @haproc sysname,
79            @dummy int,
80            @status int,
81            @gp_enabled int
82    
83    
84        /*
85        **  Verify the database name and get the @dbid and @dbuid
86        */
87        select @dbid = dbid, @dbuid = suid
88        from master.dbo.sysdatabases
89        where name = @dbname
90    
91        /* 
92        ** If @dbname not found raise an error. 
93        */
94        if @dbid is null
95        begin
96            raiserror 17421
97            return (1)
98        end
99    
100       /* 
101       ** If granular permissions is not enabled then the user must be dbo or sa_role 
102       ** is required.  If granular permissions is enabled then the permission 
103       ** 'own database' is required.  proc_role and proc_auditperm will also do 
104       ** auditing if required. Both will also print an error message if required.
105       */
106   
107       execute @status = sp_aux_checkroleperm "sa_role", "own database",
108           @dbname, @gp_enabled output
109       if (@gp_enabled = 0)
110       begin
111           if ((suser_id() != @dbuid))
112           begin
113               /* For Auditing */
114               if (proc_role("sa_role") = 0)
115               begin
116                   return (1)
117               end
118           end
119           else
120           begin
121               /* If DBO, then we don't need sa_role. */
122               select @status = 0
123           end
124       end
125       else
126       begin
127           select @dummy = proc_auditperm("own database", @status, @dbname)
128       end
129   
130       if (@status != 0)
131           return (1)
132   
133       /*
134       **  You can not change any of the options in master.  If the user tries to
135       **  do so tell them they can't.
136       */
137       if @dbid = 1
138       begin
139           raiserror 17422
140           return (1)
141       end
142   
143       /*
144       ** The full logging options are stored in master..sysattributes as:
145       **
146       **	- class:	38
147       **	- type:		'D'
148       **
149       **      attribute char_value	    object      object_info1
150       **      ----------------------------------------------------
151       **	0	  NULL		    	
152       **      0	  all		    1		0x0000000f
153       **      1	  select into       1		0x00000001
154       **      3	  alter table       1		0x00000004
155       **      4	  reorg rebuild     1		0x00000008
156       **
157       ** The database master stores only the descriptions, so, the
158       ** attribute 0 that in other databases stores the database bitmap,
159       ** in the case of master it's used just to store the description
160       ** 'all' for the bitmap 0xd. This is ok because the full 
161       ** logging options cannot be changed in master.
162       ** 
163       ** If there is an attribute configured, we will update it, otherwise
164       ** we will insert a new row.
165       **
166       */
167   
168       exec sp_getmessage 17431, @true out
169       exec sp_getmessage 17432, @false out
170   
171       /*
172       ** If the option name is null, the current values will be displayed
173       */
174       select @fl_class = 38, @type = 'D'
175   
176       if (@optname is null)
177       begin
178           select t1.char_value as mode,
179               case when t1.object_info1 & t2.object_info1 = 0
180                   then @false else @true
181               end as enable
182           into #tmp
183           from master.dbo.sysattributes t1, master.dbo.sysattributes t2
184           where
185               t1.class = @fl_class
186               and t2.class = @fl_class
187               and t1.object = 1
188               and t2.object = @dbid
189               and t1.attribute != 0
190               and t2.attribute = 0
191   
192           exec sp_autoformat
193               @fulltabname = '#tmp',
194               @selectlist = '''Logging option'' = mode, 
195   				''Enabled'' = enable'
196           return 0
197       end
198   
199       select @optvalue = lower(@optvalue)
200       if @optvalue in ("true", @true)
201           select @optval = 1
202       else if @optvalue in ("false", @false)
203           select @optval = 0
204       else
205       begin
206           raiserror 17423
207           return (1)
208       end
209   
210       select @HA_CERTifIED = 0
211       select @retstat = 0
212   
213   
214   
215       /* check to see if we are using HA specific SP for a HA enabled server */
216       select @haproc = "sp_ha_check_certified"
217       exec @retstat = @haproc 'sp_dboption_flmode', @HA_CERTifIED
218       if (@retstat != 0)
219           return (1)
220   
221       /*
222       ** Check whether a logging mode exists. Option name must exist for the
223       ** database master (object = 1).
224       **
225       */
226       select @bit = object_info1
227       from master.dbo.sysattributes
228       where class = @fl_class
229           and object = 1
230           and char_value = @optname
231   
232       if @bit is null
233       begin
234           raiserror 17953, @optname
235           return (1)
236       end
237   
238       /*
239       ** Do we have an entry in sysattributes for this database ? attribute = 0
240       ** and object = @dbid
241       */
242       select @bitmap = object_info1
243       from master.dbo.sysattributes
244       where class = @fl_class
245           and object = @dbid
246           and attribute = 0
247   
248       if @bitmap is null
249           select @bitmap = 0, @action = 1 /* insert */
250       else
251           select @action = 2 /* update */
252   
253       /* Set or reset the bit. */
254       if (@optval = 0)
255           select @newbitmap = @bitmap & ~ @bit
256       else
257           select @newbitmap = @bitmap | @bit
258   
259       /*
260       ** First validate the row. 
261       */
262       if attrib_valid(@fl_class, 0, @type, @dbid, @newbitmap, null, null,
263               null, null, null, null, null, null, @action) = 0
264           return 1
265   
266   
267   
268       /*
269       ** Now update the row
270       */
271       if (@action = 1)
272           insert master.dbo.sysattributes
273           (class, attribute, object_type, object, object_info1)
274           values (@fl_class, 0, @type, @dbid, @newbitmap)
275       else
276           update master.dbo.sysattributes
277           set object_info1 = @newbitmap
278           where class = @fl_class
279               and object = @dbid
280               and attribute = 0
281   
282       if (@@error != 0)
283           goto clean_fl_all
284   
285   
286   
287       /*
288       ** Sync the in-memory RDES with the new values
289       ** in sysattributes.
290       */
291       if attrib_notify(@fl_class, 0, @type, @dbid, @newbitmap, null, null,
292               null, null, null, null, null, null, @action) = 0
293           return (1)
294       else
295           return (@retstat)
296   
297   clean_fl_all:
298   
299   
300   
301       return (1)
302   
303   


exec sp_procxmode 'sp_dboption_flmode', 'AnyMode'
go

Grant Execute on sp_dboption_flmode to public
go
DEFECTS
 QCAR 6 Cartesian product between tables master..sysattributes t1 and [master..sysattributes t2] 183
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 189
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 190
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 246
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 280
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_dboption_flmode  
 MNER 3 No Error Check should check return value of exec 168
 MNER 3 No Error Check should check return value of exec 169
 MNER 3 No Error Check should check @@error after select into 178
 MNER 3 No Error Check should check return value of exec 192
 MNER 3 No Error Check should check @@error after insert 272
 MNER 3 No Error Check should check @@error after update 276
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 207
 MUCO 3 Useless Code Useless Brackets 218
 MUCO 3 Useless Code Useless Brackets 219
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 254
 MUCO 3 Useless Code Useless Brackets 271
 MUCO 3 Useless Code Useless Brackets 282
 MUCO 3 Useless Code Useless Brackets 293
 MUCO 3 Useless Code Useless Brackets 295
 MUCO 3 Useless Code Useless Brackets 301
 MUOT 3 Updates outside transaction 276
 MUPK 3 Update column which is part of a PK or unique index object_info1 277
 QAFM 3 Var Assignment from potentially many rows 226
 QAFM 3 Var Assignment from potentially many rows 242
 QCTC 3 Conditional Table Creation 178
 QIWC 3 Insert with not all columns specified missing 10 columns out of 15 273
 QNAJ 3 Not using ANSI Inner Join 183
 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, class, attribute}
185
 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, class, attribute}
186
 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, class}
228
 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, class, attribute}
244
 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, class, attribute}
278
 VNRD 3 Variable is not read @dummy 127
 VUNU 3 Variable is not used @msg 64
 MDYE 2 Dynamic Exec Marker exec @retstat 217
 MTR1 2 Metrics: Comments Ratio Comments: 53% 58
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 22 = 27dec - 7exi + 2 58
 MTR3 2 Metrics: Query Complexity Complexity: 103 58

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#tmp (1) 
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
read_writes table master..sysattributes (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  

CALLERS
called by proc sybsystemprocs..sp_dboption