DatabaseProcApplicationCreatedLinks
sybsystemprocssp_ddlgen_db_print_with_clause  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** sp_ddlgen_db_print_with_clause
4     **
5     **	Print the WITH clauses as required, depending on whether override
6     **	has been specified, and if the other options like durability or
7     **	dml_logging need to be printed.
8     **
9     ** Parameters:
10    **	@with_override	- Boolean; whether WITH OVERRIDE should be generated.
11    **	@with_dur_and_dmllog
12    **			- Boolean; whether WITH DURABILITY AND DML_LOGGING
13    **			  should be generated.
14    **	@durability	- DURABILITY setting of db
15    **	@dmllogging	- DML_LOGGING setting of db
16    **	@archivedb	- Boolean; whether it's an archive db.
17    **	@with_dcomp_and_IRL
18    **			- Boolean; whether WITH COMPRESSION, LOB_COMPRESSION and 
19    **			  INROW_LOB_LENGTH should be generated.
20    **	@with_dek_name
21    **			- Boolean; whether WITH Database Encryption Key
22    **			  should be generated.
23    **	@data_compression	- data compression setting of db
24    **	@lob_compression	- lob compression setting of db
25    **	@inrow_lob_length	- inrow lob length of db
26    **	@dek_name	- the name of database encryption key
27    **
28    ** Returns:	0 always.
29    {
30    */
31    create or replace procedure sp_ddlgen_db_print_with_clause(
32        @with_override tinyint output
33        , @with_dur_and_dmllog tinyint output
34        , @with_dcomp_and_IRL tinyint output
35        , @with_dek_name tinyint output
36        , @durability varchar(13) = NULL
37        , @dmllogging varchar(9) = NULL
38        , @data_compression varchar(4) = NULL
39        , @lob_compression int
40        , @inrow_lob_length int
41        , @dek_name varchar(255) = NULL
42        , @archivedb tinyint
43    ) as
44        begin
45            declare @newline char(1)
46                , @tabchar char(1)
47                , @override_sql varchar(13)
48                , @printfmt varchar(10) -- Check its length before use.
49                , @withopt_sep varchar(10) -- indentation for starting
50                -- new clause on a new line.
51                , @nextopt_sep varchar(10) -- indentation for the next 
52                -- WITH option.
53                , @dmllogging_full_sql varchar(4)
54                , @data_compression_none_sql varchar(4)
55    
56            select @newline = char(10)
57                , @tabchar = char(9)
58                , @override_sql = "WITH OVERRIDE"
59                , @withopt_sep = "WITH "
60                , @nextopt_sep = "   , "
61                , @dmllogging_full_sql = "FULL"
62                , @data_compression_none_sql = "NONE"
63    
64            -- If any disk piece in the current block of rows
65            -- had a WITH OVERRIDE setting, then generate that
66            -- clause here.
67            --
68            if (@with_override = 1)
69            begin
70                select @printfmt = case @archivedb
71                        when 1 then "-- %1!"
72                        else "%1!"
73                    end
74                print @printfmt, @override_sql
75    
76                -- Now that WITH OVERRIDE has been printed, re-set the
77                -- indentation string for the remaining WITH options on the
78                -- next line.
79                --
80                select @withopt_sep = @nextopt_sep
81                    , @with_override = 0
82            end
83    
84            -- Print remaining WITH options, using the appropriate indentation
85            -- separator.
86            --
87            if (@with_dur_and_dmllog = 1)
88            begin
89                if (@durability IS NOT NULL)
90                begin
91                    print "%1!DURABILITY = %2!"
92                        , @withopt_sep, @durability
93                    select @withopt_sep = @nextopt_sep
94                end
95    
96                if (@dmllogging != @dmllogging_full_sql)
97                begin
98                    print "%1!DML_LOGGING = %2!"
99                        , @withopt_sep, @dmllogging
100                   select @withopt_sep = @nextopt_sep
101               end
102   
103               select @with_dur_and_dmllog = 0
104           end
105   
106           -- Print remaining WITH options, using the appropriate indentation
107           -- separator.
108           --
109           if (@with_dcomp_and_IRL = 1)
110           begin
111               if ((@data_compression IS NOT NULL) and (@data_compression != @data_compression_none_sql))
112               begin
113                   print "%1!COMPRESSION = %2!"
114                       , @withopt_sep, @data_compression
115                   select @withopt_sep = @nextopt_sep
116               end
117   
118               if ((@lob_compression IS NOT NULL) and (@lob_compression != 0))
119               begin
120                   print "%1!LOB_COMPRESSION = %2!"
121                       , @withopt_sep, @lob_compression
122                   select @withopt_sep = @nextopt_sep
123               end
124   
125   
126               if ((@inrow_lob_length IS NOT NULL) and (@inrow_lob_length != 0))
127               begin
128                   print "%1!INROW_LOB_LENGTH = %2!"
129                       , @withopt_sep, @inrow_lob_length
130                   select @withopt_sep = @nextopt_sep
131               end
132   
133               select @with_dcomp_and_IRL = 0
134           end
135   
136           -- Print remaining WITH options, using the appropriate indentation
137           -- separator.
138           --
139           if (@with_dek_name = 1)
140           begin
141               if ((@dek_name IS NOT NULL) and
142                       (char_length(@dek_name) > 0))
143               begin
144                   print "ENCRYPT WITH %1!"
145                       , @dek_name
146               end
147   
148               select @with_dek_name = 0
149           end
150   
151           print "go%1!%2!", @newline, @newline
152   
153           return 0
154   
155       end
156   

DEFECTS
 MUCO 3 Useless Code Useless Brackets in create proc 31
 MUCO 3 Useless Code Useless Begin-End Pair 44
 MUCO 3 Useless Code Useless Brackets 68
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 126
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 141
 VNRD 3 Variable is not read @tabchar 57
 VNRD 3 Variable is not read @withopt_sep 130
 MTR1 2 Metrics: Comments Ratio Comments: 37% 31
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 16 = 15dec - 1exi + 2 31
 MTR3 2 Metrics: Query Complexity Complexity: 46 31

DEPENDENCIES
CALLERS
called by proc sybsystemprocs..sp_ddlgen_database  
called by proc sybsystemprocs..sp_ddlgen_db_print_alterdb  
   called by proc sybsystemprocs..sp_ddlgen_database