1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>SQLite's Built-in printf()</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
SQLite's Built-in printf()
</div>
<div class="fancy_toc">
<a onclick="toggle_toc()">
<span class="fancy_toc_mark" id="toc_mk">►</span>
Table Of Contents
</a>
<div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div>
<div class="fancy-toc2"><a href="#advantages">1.1. Advantages</a></div>
<div class="fancy-toc2"><a href="#disadvantages">1.2. Disadvantages</a></div>
<div class="fancy-toc1"><a href="#formatting_details">2. Formatting Details</a></div>
<div class="fancy-toc2"><a href="#substitution_types">2.1. Substitution Types</a></div>
<div class="fancy-toc2"><a href="#the_optional_length_field">2.2. The Optional Length Field</a></div>
<div class="fancy-toc2"><a href="#the_optional_width_field">2.3. The Optional Width Field</a></div>
<div class="fancy-toc2"><a href="#the_optional_precision_field">2.4. The Optional Precision Field</a></div>
<div class="fancy-toc2"><a href="#the_options_flags_field">2.5. The Options Flags Field</a></div>
<div class="fancy-toc1"><a href="#implementation_and_history">3. Implementation And History</a></div>
</div>
</div>
<script>
function toggle_toc(){
var sub = document.getElementById("toc_sub")
var mk = document.getElementById("toc_mk")
if( sub.style.display!="block" ){
sub.style.display = "block";
mk.innerHTML = "▼";
} else {
sub.style.display = "none";
mk.innerHTML = "►";
}
}
</script>
</div>
<h1 id="overview"><span>1. </span>Overview</h1>
<p>SQLite contains its own implementation of the string formatting routine "printf()",
accessible via the following interfaces:
</p><ul>
<li> <a href="lang_corefunc.html#format">format()</a> → an SQL function returning the formatted string
</li><li> <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> → Store the formatted string in memory obtained
<a href="c3ref/free.html">sqlite3_malloc64()</a>.
</li><li> <a href="c3ref/mprintf.html">sqlite3_snprintf()</a> → Store the formatted string in a static buffer
</li><li> <a href="c3ref/str_append.html">sqlite3_str_appendf()</a> → Append formatted text to a dynamic string
</li><li> <a href="c3ref/mprintf.html">sqlite3_vmprintf()</a> → Varargs version of sqlite3_mprintf()
</li><li> <a href="c3ref/mprintf.html">sqlite3_vsnprintf()</a> → Varargs version of sqlite3_snprintf()
</li><li> <a href="c3ref/str_append.html">sqlite3_str_vappendf()</a> → Varargs version of sqlite3_str_appendf()
</li></ul>
<p>The same core string formatter is also used internally by SQLite.
</p><h2 id="advantages"><span>1.1. </span>Advantages</h2>
<p>Why does SQLite have its own private built-in printf() implementation?
Why not use the printf() implementation from the standard C library?
Several reasons:
</p><p>
</p><ol>
<li><p>
By using its own built-in implementation, SQLite guarantees that the
output will be the same on all platforms and in all LOCALEs.
This is important for consistency and for testing. It would be problematic
if one machine gave an answer of "5.25e+08" and another gave an answer
of "5.250e+008". Both answers are correct, but it is better when SQLite
always gives the same answer.
</p></li><li><p>
We know of no way to use the standard library printf() C interface to
implement the <a href="lang_corefunc.html#format">format() SQL function</a> feature of SQLite. The built-in
printf() implementation can be easily adapted to that task, however.
</p></li><li><p>
The printf() in SQLite supports new non-standard substitution
types (<a href="printf.html#percentq">%q</a>, <a href="printf.html#percentq">%Q</a>, <a href="printf.html#percentw">%w</a>, and <a href="printf.html#percentz">%z</a>), and enhanced substitution
behavior (%s and <a href="printf.html#percentz">%z</a>) that are useful both internally to SQLite
and to applications using SQLite.
Standard library printf()s cannot normally be extended in this way.
</p></li><li><p>
Via the <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> and <a href="c3ref/mprintf.html">sqlite3_vmprintf()</a> interfaces,
the built-in printf() implementation supports the ability to render an
arbitrary-length string into a memory buffer obtained from <a href="c3ref/free.html">sqlite3_malloc64()</a>.
This is safer and less error prone than trying to precompute an upper size
limit on the result string, allocate an appropriately sized buffer, and
then calling snprintf().
</p></li><li><p>
The SQLite-specific printf() supports a new flag (!) called the
"alternate-form-2" flag. The alternate-form-2 flag changes the processing
of floating-point conversions in subtle ways so that the output is always
an SQL-compatible text representation of a floating-point number - something
that is not possible to achieve with standard-library printf(). For
string substitutions, the alternate-form-2 flag causes the width and
precision to be measured in characters instead of bytes, which simplifies
processing of strings containing multi-byte UTF8 characters.
</p></li><li><p>
The built-in SQLite has compile-time options such as
SQLITE_PRINTF_PRECISION_LIMIT that provide defense against
denial-of-service attacks for application that expose the
printf() functionality to untrusted users.
</p></li><li><p>
Using a built-in printf() implementation means that SQLite has one
fewer dependency on the host environment, making it more portable.
</p></li></ol>
<h2 id="disadvantages"><span>1.2. </span>Disadvantages</h2>
<p>
In fairness, having a built-in implementation of printf() also comes with
some disadvantages. To wit:
</p><ol>
<li><p>
The built-in printf() implementation uses extra code space
(about 7800 bytes on GCC 5.4 with -Os).
</p></li><li><p>
The floating-point to text conversion subfunction for the built-in printf()
is limited in precision to 16 significant digits or 26 significant digits
if the "!" alternate-form-2 flag is used.
Every IEEE-754 double can be represented <i>exactly</i> as a decimal
value, but for many doubles the exact decimal representation requires
more than 16 or 26 significant digits.
The SQLite printf() function only renders the first 16 or 26 significant digits
because that can be done efficient and because 16 decimal digits it is sufficient
to distinguish every possible double value. Use the <a href="floatingpoint.html#decext">decimal extension</a> to get
the exact decimal equivalent of double value for the rare cases where that is
required.
</p></li><li><p>
The order of the buffer pointer and buffer size parameters in the built-in
snprintf() implementation is reversed from the order used in standard-library
implementations.
</p></li><li><p>
The built-in printf() implementation does not handle posix positional referencing
modifiers that allow the order of arguments to printf() to be different from the
order of the %-substitutions. In the built-in printf(), the order of the arguments
must exactly match the order of the %-substitutions.
</p></li></ol>
<p>
In spite of the disadvantages, the developers believe that having a built-in
printf() implementation inside of SQLite is a net positive.
</p><h1 id="formatting_details"><span>2. </span>Formatting Details</h1>
<p>The format string for printf() is a template for the generated
string. Substitutions are made whenever a "%" character appears in
the format string. The "%" is followed by one or more additional
characters that describe the substitution. Each substitution has
the following format:
</p><blockquote>
<b>%</b><i>[flags][width][</i><b>.</b><i>precision][length]type</i>
</blockquote>
<p>All substitutions begin with a single "%" and end with a single type character.
The other elements of the substitution are optional.
</p><p>To include a single "%" character in the output, put two consecutive
"%" characters in the template.
</p><h2 id="substitution_types"><span>2.1. </span>Substitution Types</h2>
<p>The following chart shows the substitution types supported by SQLite:
</p><center>
<table border="1" cellpadding="10" width="80%">
<tr>
<th>Substitution Type</th><th>Meaning
</th></tr><tr>
<td>%
</td><td>Two "%" characters in a row are translated into a single "%" in the output,
without substituting any values.
</td></tr><tr>
<td>d, i
</td><td>The argument is a signed integer which is displayed in decimal.
</td></tr><tr>
<td>u
</td><td>The argument is an unsigned integer which is displayed in decimal.
</td></tr><tr>
<td>f
</td><td>The argument is a double which is displayed in decimal.
</td></tr><tr>
<td>e, E
</td><td>The argument is a double which is displayed in exponential notation.
The exponent character is 'e' or 'E' depending on the type.
</td></tr><tr>
<td>g, G
</td><td>The argument is a double which is displayed in either normal decimal
notation or if the exponent is not close to zero, in exponential
notation.
</td></tr><tr>
<td>x, X
</td><td>The argument is an integer which is displayed in hexadecimal.
Lower-case hexadecimal is used for %x and upper-case is used
for %X
</td></tr><tr>
<td>o
</td><td>The argument is an integer which is displayed in octal.
</td></tr><tr>
<td>s, z
</td><td>
<a name="percentz"></a>
The argument is either a zero-terminated string that is displayed,
or a null pointer which is treated as an empty string. For
the %z type in C-language interfaces, <a href="c3ref/free.html">sqlite3_free()</a> is invoked
on the string after it has been copied into the output. The %s and %z
substitutions are identical for the SQL printf() function, with
a NULL argument treated as an empty string.<br><br>
The %s substitution is universal among printf functions, but
the %z substitution and safe treatment of null pointers
are SQLite enhancements, not found in other
printf() implementations.
</td></tr><tr>
<td>c
</td><td>For the C-language interfaces, the argument is an integer which
is interpreted as a character. For the <a href="lang_corefunc.html#format">format() SQL function</a> the
argument is a string from which the first character is extracted and
displayed.
</td></tr><tr>
<td>p
</td><td>The argument is a pointer which is displayed as a hexadecimal address.
Since the SQL language has no concept of a pointer, the %p substitution
for the <a href="lang_corefunc.html#format">format() SQL function</a> works like %x.
</td></tr><tr>
<td>n
</td><td>The argument is a pointer to an integer. Nothing is displayed for
this substitution type. Instead, the integer to which the argument
points is overwritten with the number of characters in the generated
string that result from all format symbols to the left of the %n.
</td></tr><tr>
<td>q, Q
</td><td><a name="percentq"></a>
The argument is a zero-terminated string. The string is printed with
all single quote (') characters doubled so that the string can safely
appear inside an SQL string literal. The %Q substitution type also
puts single-quotes on both ends of the substituted string.
<br><br>If the argument
to %Q is a null pointer then the output is an unquoted "NULL". In other
words, a null pointer generates an SQL NULL, and a non-null pointer generates
a valid SQL string literal. If the argument to %q is a null pointer
then no output is generated. Thus a null-pointer to %q is the same as
an empty string.
<br><br>For these substitutions, the precision is the number of bytes or
characters taken from the argument, not the number of bytes or characters that
are written into the output.
<br><br>
The %q and %Q substitutions are SQLite enhancements, not found in
most other printf() implementations.
</td></tr><tr>
<td>w
</td><td><a name="percentw"></a>
This substitution works like %q except that it doubles all double-quote
characters (") instead of single-quotes, making the result suitable for
using with a double-quoted identifier name in an SQL statement.
<br><br>
The %w substitution is an SQLite enhancements, not found in
most other printf() implementations.
</td></tr></table>
</center>
<h2 id="the_optional_length_field"><span>2.2. </span>The Optional Length Field</h2>
<p>The length of the argument value can be specified by one or more letters
that occur just prior to the substitution type letter. In SQLite, the
length only matter for integer types. The length is ignored for the
<a href="lang_corefunc.html#format">format() SQL function</a> which always uses 64-bit values. The following
table shows the length specifiers allowed by SQLite:
</p><center>
<table border="1" cellpadding="10" width="80%">
<tr>
<th>Length Specifier
</th><th>Meaning
</th></tr><tr>
<td><i>(default)</i>
</td><td>An "int" or "unsigned int". 32-bits on all modern systems.
</td></tr><tr>
<td>l
</td><td>A "long int" or "long unsigned int". Also 32-bits on all modern systems.
</td></tr><tr>
<td>ll
</td><td>A "long long int" or "long long unsigned" or an "sqlite3_int64" or
"sqlite3_uint64" value. These are 64-bit integers on all modern systems.
</td></tr></table>
</center>
<p>Only the "ll" length modifier ever makes a difference for SQLite. And
it only makes a difference when using the C-language interfaces.
</p><h2 id="the_optional_width_field"><span>2.3. </span>The Optional Width Field</h2>
<p>The width field specifies the minimum width of the substituted value in
the output. If the string or number that is written into the output is shorter
than the width, then the value is padded. Padding is on the left (the
value is right-justified) by default. If the "-" flag is used, then the
padding is on the right and the value is left-justified.
</p><p>The width is measured in bytes by default. However, if the "!" flag is
present then the width is in characters. This only makes a difference for
multi-byte utf-8 characters, and those only occur on string substitutions.
</p><p>If the width is a single "*" character instead of a number, then the
actual width value is read as an integer from the argument list. If the
value read is negative, then the absolute value is used for the width and
the value is left-justified as if the "-" flag were present.
</p><p>If the value being substituted is larger than the width, then full value
is added to the output. In other words, the width is the minimum width of
the value as it is rendered in the output.
</p><h2 id="the_optional_precision_field"><span>2.4. </span>The Optional Precision Field</h2>
<p>The precision field, if it is present, must follow the width separated
by a single "." character. If there is no width, then the "." that introduces
the precision immediately follows either the flags (if there are any) or
the initial "%".
</p><p>For string substitutions %s, %z, %q, %Q, or %w the precision is the number
of byte or character used from the argument. The number is bytes by default but
is characters if the "!" flag is present. If there is no precision, then the
entire string is substituted. Examples: "%.3s" substitutes the first 3 bytes
of the argument string. "%!.3s" substitutes the first three characters of the
argument string.
</p><p>For integer substitutions %d, %i, %x, %X, %o, and %p the precision specifies
minimum number of digits to display. Leading zeros are added if necessary, to
expand the output to the minimum number of digits.
</p><p>For floating-point substitutions %e, %E, and %f the precision specifies
the number of digits to display to the right of the decimal point. With
the %g and %G, the precision is the total number of significant digits, rounded
up to 1 if the specified precision is 0.
</p><p>For the character substitution %c a precision N greater than 1 causes the
character to be repeated N times. This is a non-standard extension found only
in SQLite.
</p><p>If the precision is a single "*" character instead of a number, then the
actual precision value is read as an integer from the argument list.
</p><h2 id="the_options_flags_field"><span>2.5. </span>The Options Flags Field</h2>
<p>Flags consist of zero or more characters that immediately follow the
"%" that introduces the substitution. The various flags and their meanings
are as follows:
</p><center>
<table border="1" cellpadding="10" width="80%">
<tr>
<th>Flag
</th><th>Meaning
</th></tr><tr>
<td><b>-</b>
</td><td>Left-justify the value in the output. The default is to right-justify.
If the width is zero or is otherwise less than the length of the value being
substituted, then there is no padding and the "-" flag is a no-op.
</td></tr><tr>
<td><b>+</b>
</td><td>For signed numeric substitutions, include a "+" sign before positive numbers.
A "-" sign always appears before negative numbers regardless of flag settings.
</td></tr><tr>
<td><i>(space)</i>
</td><td>For signed numeric substitutions, prepend a single space before positive
numbers.
</td></tr><tr>
<td><b>0</b>
</td><td>(The zero-padding option)
Prepend as many "0" characters to numeric substitutions as necessary to
expand the value out to the specified width. If the width field is omitted,
then this flag is a no-op.
Infinity and NaN (Not-A-Number) floating point values are normally rendered
as "Inf" and "NaN", respectively, but with the zero-padding option enabled
they are rendered as "9.0e+999" and "null". In other words, with the zero-padding
option, floating-point Infinity and NaN are rendered as valid SQL and JSON literals.
</td></tr><tr>
<td><b>#</b>
</td><td>This is the "alternate-form-1" flag.
For %g and %G substitutions, this causes trailing zeros to be removed.
This flag forces a decimal point to appear for all floating-point substitutions.
For %o, %x, and %X substitutions, the alternate-form-1 flag cause the value
to be prepended with "0", "0x", or "0X", respectively.
</td></tr><tr>
<a name="comma"></a>
<td><b>,</b>
</td><td>The comma option causes comma separators to be added to the output of numeric
substitutions (%d, %f, and similar) before every 3rd digits to the left of
the decimal point. No commas are added for digits to the right of the decimal point.
This can help humans to more easily discern the magnitude of large integer values.
For example,
the value 2147483647 would be rendered as "2147483647" using "%d" but would
appear as "2,147,483,647" with "%,d". This flag is a non-standard extension.
</td></tr><tr>
<td><b>!</b>
</td><td>This is the "alternate-form-2 flag.
For string substitutions, this flag causes the width and precision to be understand
in terms of characters rather than bytes.
For floating point substitutions, the alternate-form-2 flag increases the
maximum number of significant digits displayed from 16 to 26,
forces the display of the decimal point and causes at least one digit
to appear after the decimal point.<br><br>
The alternate-form-2 flag is a non-standard extension that appears in no
other printf() implementations, as far as we know.
</td></tr></table>
</center>
<h1 id="implementation_and_history"><span>3. </span>Implementation And History</h1>
<p>
The core string formatting routine is the sqlite3VXPrintf() function found in the
<a href="https://sqlite.org/src/file/src/printf.c">printf.c</a> source file. All the
various interfaces invoke (sometimes indirectly) this one core function.
The sqlite3VXPrintf() function began as code written by the first author
of SQLite (<a href="crew.html">Hipp</a>) when he was a graduate student at Duke University in the
late 1980s. Hipp kept this printf() implementation in his personal toolbox until
he started working on SQLite in 2000. The code was incorporated into the
SQLite source tree on <a href="https://sqlite.org/src/timeline?c=f9372072a6">2000-10-08</a>
for SQLite version 1.0.9.
</p><p>
The <a href="https://www.fossil-scm.org/">Fossil Version Control System</a> uses its own
printf() implementation that is derived from an early version of the SQLite
printf() implementation, but those two implementations have since diverged.
</p><p>
The <a href="c3ref/mprintf.html">sqlite3_snprintf()</a> function has its buffer pointer and buffer size
arguments reversed from what is found in the standard C library snprintf()
routine. This is because there was no snprintf() routine in the
standard C library
when Hipp was first implementing his version, and he chose a different order
than the designers of the standard C library.
</p>
|
