Debian Codegraph @

ext/qrf/README.md

raw
1 # SQLite Query Result Formatting Subsystem
2
3 The "Query Result Formatter" or "QRF" subsystem is a C-language
4 subroutine that formats the output from an SQLite query for display using
5 a fix-width font, for example on a terminal window over an SSH connection.
6 The output format is configurable. The application can request various
7 table formats, with flexible column widths and alignments, row-oriented
8 formats, such as CSV and similar, as well as various special purpose formats
9 like JSON.
10
11 For the first 25 years of SQLite's existance, the
12 [command-line interface](https://sqlite.org/cli.html) (CLI)
13 formatted query results using a hodge-podge of routines
14 that had grown slowly by accretion. The QRF was created
15 in fall of 2025 to refactor and reorganize this code into
16 a more usable form. The idea behind QRF is to implement all the
17 query result formatting capabilities of the CLI in a subroutine
18 that can be incorporated and reused by other applications.
19
20 ## 1.0 Overview Of Operation
21
22 Suppose variable `sqlite3_stmt *pStmt` is a pointer to an SQLite
23 prepared statement that has been reset and bound and is ready to run.
24 Then to format the output from this prepared statement, use code
25 similar to the following:
26
27 > ~~~
28 sqlite3_qrf_spec spec; /* Format specification */
29 char *zErrMsg; /* Text error message (optional) */
30 char *zResult = 0; /* Formatted output written here */
31 int rc; /* Result code */
32
33 memset(&spec, 0, sizeof(spec)); /* Initialize the spec */
34 spec.iVersion = 1; /* Version number must be 1 */
35 spec.pzOutput = &zResult; /* Write results in variable zResult */
36 /* Optionally fill in other settings in spec here, as needed */
37 zErrMsg = 0; /* Not required; just being pedantic */
38 rc = sqlite3_format_query_result(pStmt, &spec, &zErrMsg); /* Format results */
39 if( rc ){
40 printf("Error (%d): %s\n", rc, zErrMsg); /* Report an error */
41 sqlite3_free(zErrMsg); /* Free the error message text */
42 }else{
43 printf("%s", zResult); /* Report the results */
44 }
45 sqlite3_free(zResult); /* Free memory used to hold results */
46 ~~~
47
48 The `sqlite3_qrf_spec` object describes the desired output format
49 and where to send the generated output. Most of the work in using
50 the QRF involves filling out the sqlite3_qrf_spec.
51
52 ### 1.1 Using QRF with SQL text
53
54 If you start with SQL text instead of an sqlite3_stmt pointer, and
55 especially if the SQL text might comprise two or more statements, then
56 the SQL text needs to be converted into sqlite3_stmt objects separately.
57 If the original SQL text is in a variable `const char *zSql` and the
58 database connection is in variable `sqlite3 *db`, then code
59 similar to the following should work:
60
61 > ~~~
62 sqlite3_qrf_spec spec; /* Format specification */
63 char *zErrMsg; /* Text error message (optional) */
64 char *zResult = 0; /* Formatted output written here */
65 sqlite3_stmt *pStmt; /* Next prepared statement */
66 int rc; /* Result code */
67
68 memset(&spec, 0, sizeof(spec)); /* Initialize the spec */
69 spec.iVersion = 1; /* Version number must be 1 */
70 spec.pzOutput = &zResult; /* Write results in variable zResult */
71 /* Optionally fill in other settings in spec here, as needed */
72 zErrMsg = 0; /* Not required; just being pedantic */
73 while( zSql && zSql[0] ){
74 pStmt = 0; /* Not required; just being pedantic */
75 rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zSql);
76 if( rc!=SQLITE_OK ){
77 printf("Error: %s\n", sqlite3_errmsg(db));
78 }else{
79 rc = sqlite3_format_query_result(pStmt, &spec, &zErrMsg); /* Get results */
80 if( rc ){
81 printf("Error (%d): %s\n", rc, zErrMsg); /* Report an error */
82 sqlite3_free(zErrMsg); /* Free the error message text */
83 }else{
84 printf("%s", zResult); /* Report the results */
85 sqlite3_free(zResult); /* Free memory used to hold results */
86 zResult = 0;
87 }
88 }
89 sqlite3_finalize(pStmt);
90 }
91 ~~~
92
93 <a id="spec"></a>
94 ## 2.0 The `sqlite3_qrf_spec` object
95
96 The `sqlite3_qrf_spec` looks like this:
97
98 > ~~~
99 typedef struct sqlite3_qrf_spec sqlite3_qrf_spec;
100 struct sqlite3_qrf_spec {
101 unsigned char iVersion; /* Version number of this structure */
102 unsigned char eStyle; /* Formatting style. "box", "csv", etc... */
103 unsigned char eEsc; /* How to escape control characters in text */
104 unsigned char eText; /* Quoting style for text */
105 unsigned char eTitle; /* Quating style for the text of column names */
106 unsigned char eBlob; /* Quoting style for BLOBs */
107 unsigned char bTitles; /* True to show column names */
108 unsigned char bWordWrap; /* Try to wrap on word boundaries */
109 unsigned char bTextJsonb; /* Render JSONB blobs as JSON text */
110 unsigned char eDfltAlign; /* Default alignment, no covered by aAlignment */
111 unsigned char eTitleAlign; /* Alignment for column headers */
112 unsigned char bSplitColumn; /* Wrap single-column output into many columns */
113 unsigned char bBorder; /* Show outer border in Box and Table styles */
114 short int nWrap; /* Wrap columns wider than this */
115 short int nScreenWidth; /* Maximum overall table width */
116 short int nLineLimit; /* Maximum number of lines for any row */
117 short int nTitleLimit; /* Maximum number of characters in a title */
118 unsigned int nMultiInsert; /* Add rows to one INSERT until size exceeds */
119 int nCharLimit; /* Maximum number of characters in a cell */
120 int nWidth; /* Number of entries in aWidth[] */
121 int nAlign; /* Number of entries in aAlignment[] */
122 short int *aWidth; /* Column widths */
123 unsigned char *aAlign; /* Column alignments */
124 char *zColumnSep; /* Alternative column separator */
125 char *zRowSep; /* Alternative row separator */
126 char *zTableName; /* Output table name */
127 char *zNull; /* Rendering of NULL */
128 char *(*xRender)(void*,sqlite3_value*); /* Render a value */
129 int (*xWrite)(void*,const char*,sqlite3_int64); /* Write output */
130 void *pRenderArg; /* First argument to the xRender callback */
131 void *pWriteArg; /* First argument to the xWrite callback */
132 char **pzOutput; /* Storage location for output string */
133 /* Additional fields may be added in the future */
134 };
135 ~~~
136
137 Do not be alarmed by the complexity of this structure. Everything can
138 be zeroed except for:
139
140 * `.iVersion`
141 * One of `.pzOutput` or `.xWrite`.
142
143 You do not need to understand and configure every field of this object
144 in order to use QRF effectively. Start by zeroing out the whole structure,
145 then initializing iVersion and one of pzOutput or xWrite. Then maybe
146 tweak one or two other settings to get the output you want.
147
148 Further detail on the meanings of each of the fields in the
149 `sqlite3_qrf_spec` object is in the subsequent sections.
150
151 ### 2.1 Structure Version Number
152
153 The sqlite3_qrf_spec.iVersion field must be 1. Future enhancements to
154 the QRF might add new fields to the bottom of the sqlite3_qrf_spec
155 object. Those new fields will only be accessible if the iVersion is greater
156 than 1. Thus the iVersion field is used to support upgradability.
157
158 ### 2.2 Output Deposition (xWrite and pzOutput)
159
160 The formatted output can either be sent to a callback function
161 or accumulated into an output buffer in memory obtained
162 from sqlite3_malloc(). If the sqlite3_qrf_spec.xWrite column is not NULL,
163 then that function is invoked (using sqlite3_qrf_spec.xWriteArg as its
164 first argument) to transmit the formatted output. Or, if
165 sqlite3_qrf_spec.pzOutput points to a pointer to a character, then that
166 pointer is made to point to memory obtained from sqlite3_malloc() that
167 contains the complete text of the formatted output. If spec.pzOutput\[0\]
168 is initially non-NULL, then it is assumed to already point to memory obtained
169 from sqlite3_malloc(). In that case, the buffer is resized using
170 sqlite3_realloc() and the new text is appended.
171
172 One of either sqlite3_qrf_spec.xWrite and sqlite3_qrf_spec.pzOutput must be
173 non-NULL and the other must be NULL.
174
175 The return value from xWrite is an SQLITE result code. The usual return
176 should be SQLITE_OK. But if for some reason the write fails, a different
177 value might be returned.
178
179 ### 2.3 Output Format
180
181 The sqlite3_qrf_spec.eStyle field is an integer code that defines the
182 specific output format that will be generated. See [section 4.0](#style)
183 below for details on the meaning of the various style options.
184
185 Other fields in sqlite3_qrf_spec might be used or might be
186 ignored, depending on the value of eStyle.
187
188 ### 2.4 Show Column Names (bTitles)
189
190 The sqlite3_qrf_spec.bTitles field can be either QRF_SW_Auto,
191 QRF_SW_On, or QRF_SW_Off. Those three constants also have shorter
192 alternative spellings: QRF_Auto, QRF_No, and
193 QRF_Yes.
194
195 > ~~~
196 #define QRF_SW_Auto 0 /* Let QRF choose the best value */
197 #define QRF_SW_Off 1 /* This setting is forced off */
198 #define QRF_SW_On 2 /* This setting is forced on */
199 #define QRF_Auto 0 /* Alternate spelling for QRF_SW_Auto and others */
200 #define QRF_No 1 /* Alternate spelling for QRF_SW_Off */
201 #define QRF_Yes 2 /* Alternate spelling for QRF_SW_On */
202 ~~~
203
204 If the value is QRF_Yes, then column names appear in the output.
205 If the value is QRF_No, column names are omitted. If the
206 value is QRF_Auto, then an appropriate default is chosen.
207
208 ### 2.5 Control Character Escapes (eEsc)
209
210 The sqlite3_qrf_spec.eEsc determines how ASCII control characters are
211 formatted when displaying TEXT values in the result. These are the allowed
212 values:
213
214 > ~~~
215 #define QRF_ESC_Auto 0 /* Choose the ctrl-char escape automatically */
216 #define QRF_ESC_Off 1 /* Do not escape control characters */
217 #define QRF_ESC_Ascii 2 /* Unix-style escapes. Ex: U+0007 shows ^G */
218 #define QRF_ESC_Symbol 3 /* Unicode escapes. Ex: U+0007 shows U+2407 */
219 ~~~
220
221 If the value of eEsc is QRF_ESC_Ascii, then the control character
222 with value X is displayed as ^Y where Y is X+0x40. Hence, a
223 backspace character (U+0008) is shown as "^H".
224
225 If eEsc is QRF_ESC_Symbol, then control characters in the range of U+0001
226 through U+001f are mapped into U+2401 through U+241f, respectively.
227
228 If the value of eEsc is QRF_ESC_Off, then no translation occurs
229 and control characters that appear in TEXT strings are transmitted
230 to the formatted output as-is. This can be dangerous in applications,
231 since an adversary who can control TEXT values might be able to
232 inject ANSI cursor movement sequences to hide nefarious values.
233
234 The QRF_ESC_Auto value for eEsc means that the query result formatter
235 gets to pick whichever control-character encoding it thinks is best for
236 the situation. This will usually be QRF_ESC_Ascii.
237
238 The TAB (U+0009), LF (U+000a) and CR-LF (U+000d,U+000a) character
239 sequence are always output literally and are not mapped to alternative
240 display values, regardless of this setting.
241
242 ### 2.6 Display of TEXT values (eText, eTitle)
243
244 The sqlite3_qrf_spec.eText controls how text values are rendered in the
245 display. sqlite3_qrf_spec.eTitle controls how column names are rendered.
246 Both fields can have one of the following values:
247
248 > ~~~
249 #define QRF_TEXT_Auto 0 /* Choose text encoding automatically */
250 #define QRF_TEXT_Plain 1 /* Literal text */
251 #define QRF_TEXT_Sql 2 /* Quote as an SQL literal */
252 #define QRF_TEXT_Csv 3 /* CSV-style quoting */
253 #define QRF_TEXT_Html 4 /* HTML-style quoting */
254 #define QRF_TEXT_Tcl 5 /* C/Tcl quoting */
255 #define QRF_TEXT_Json 6 /* JSON quoting */
256 #define QRF_TEXT_Relaxed 7 /* Relaxed SQL quoting */
257 ~~~
258
259 A value of QRF_TEXT_Auto means that the query result formatter will choose
260 what it thinks will be the best text encoding.
261
262 A value of QRF_TEXT_Plain means that text values appear in the output exactly
263 as they are found in the database file, with no translation.
264
265 A value of QRF_TEXT_Sql means that text values are escaped so that they
266 look like SQL literals. That means the value will be surrounded by
267 single-quotes (U+0027) and any single-quotes contained within the text
268 will be doubled.
269
270 QRF_TEXT_Relaxed is similar to QRF_TEXT_Sql, except that it automatically
271 reverts to QRF_TEXT_Plain if the value to be displayed does not contain
272 special characters and is not easily confused with a NULL or a numeric
273 value. QRF_TEXT_Relaxed strives to minimize the amount of quoting syntax
274 while keeping the result unambiguous and easy for humans to read. The
275 precise rules for when quoting is omitted in QRF_TEXT_Relaxed, and when
276 it is applied, might be adjusted in future releases.
277
278 A value of QRF_TEXT_Csv means that text values are escaped in accordance
279 with RFC&nbsp;4180, which defines Comma-Separated-Value or CSV files.
280 Text strings that contain no special values appears as-is. Text strings
281 that contain special values are contained in double-quotes (U+0022) and
282 any double-quotes within the value are doubled.
283
284 A value of QRF_TEXT_Html means that text values are escaped for use in
285 HTML. Special characters "&lt;", "&amp;", "&gt;", "&quot;", and "&#39;"
286 are displayed as "&amp;lt;", "&amp;amp;", "&amp;gt;", "&amp;quot;",
287 and "&amp;#39;", respectively.
288
289 A value of QRF_TEXT_Tcl means that text values are displayed inside of
290 double-quotes and special characters within the string are escaped using
291 backslash escape, as in ANSI-C or TCL or Perl or other popular programming
292 languages.
293
294 A value of QRF_TEXT_Json gives similar results as QRF_TEXT_Tcl except that the
295 rules are adjusted so that the displayed string is strictly conforming
296 the JSON specification.
297
298 ### 2.7 How to display BLOB values (eBlob and bTextJsonb)
299
300 If the sqlite3_qrf_spec.bTextJsonb flag is QRF_SW_On and if the value to be
301 displayed is JSONB, then the JSONB is translated into text JSON and the
302 text is shown according to the sqlite3_qrf_spec.eText setting as
303 described in the previous section.
304
305 If the bTextJsonb flag is QRF_SW_Off (the usual case) or if the BLOB value to
306 be displayed is not JSONB, then the sqlite3_qrf_spec.eBlob field determines
307 how the BLOB value is formatted. The following options are available;
308
309 > ~~~
310 #define QRF_BLOB_Auto 0 /* Determine BLOB quoting using eText */
311 #define QRF_BLOB_Text 1 /* Display content exactly as it is */
312 #define QRF_BLOB_Sql 2 /* Quote as an SQL literal */
313 #define QRF_BLOB_Hex 3 /* Hexadecimal representation */
314 #define QRF_BLOB_Tcl 4 /* "\000" notation */
315 #define QRF_BLOB_Json 5 /* A JSON string */
316 #define QRF_BLOB_Size 6 /* Display the blob size only */
317 ~~~
318
319 A value of QRF_BLOB_Auto means that display format is selected automatically
320 by sqlite3_format_query_result() based on eStyle and eText.
321
322 A value of QRF_BLOB_Text means that BLOB values are interpreted as UTF8
323 text and are displayed using formatting results set by eEsc and
324 eText.
325
326 A value of QRF_BLOB_Sql means that BLOB values are shown as SQL BLOB
327 literals: a prefix "`x'`" following by hexadecimal and ending with a
328 final "`'`".
329
330 A value of QRF_BLOB_Hex means that BLOB values are shown as
331 hexadecimal text with no delimiters.
332
333 A value of QRF_BLOB_Tcl means that BLOB values are shown as a
334 C/Tcl/Perl string literal where every byte is an octal backslash
335 escape. So a BLOB of `x'052881f3'` would be displayed as
336 `"\005\050\201\363"`.
337
338 A value of QRF_BLOB_Json is similar to QRF_BLOB_Tcl except that is
339 uses unicode backslash escapes, since JSON does not understand
340 the C/Tcl/Perl octal backslash escapes. So the string from the
341 previous paragraph would be shown as
342 `"\u0005\u0028\u0081\u00f3"`.
343
344 A value of QRF_BLOB_Size does not show any BLOB content at all.
345 Instead, it substitutes a text string that says how many bytes
346 the BLOB contains.
347
348 ### 2.8 Maximum size of displayed content (nLineLimit, nCharLimit, nTitleLimit)
349
350 If the sqlite3_qrf_spec.nCharLimit setting is non-zero, then the formatter
351 will display only the first nCharLimit characters of each value.
352 Only characters that take up space are counted when enforcing this
353 limit. Zero-width characters and VT100 escape sequences do not count
354 toward this limit. The count is in characters, not bytes. When
355 imposing this limit, the formatter adds the three characters "..."
356 to the end of the value. Those added characters are not counted
357 as part of the limit. Very small limits still result in truncation,
358 but might render a few more characters than the limit.
359
360 If the sqlite3_qrf_spec.nLineLimit setting is non-zero, then the
361 formatter will only display the first nLineLimit lines of each value.
362 It does not matter if the value is split because it contains a newline
363 character, or if it split by wrapping. This setting merely limits
364 the number of displayed lines. The nLineLimit setting currently only
365 works for **Box**, **Column**, **Line**, **Markdown**, and **Table**
366 styles, though that limitation might change in future releases.
367
368 The idea behind both of these settings is to prevent large renderings
369 when doing a query that (unexpectedly) contains very large text or
370 blob values: perhaps megabyes of text.
371
372 If the sqlite3_qrf_spec.nTitleLimit is non-zero, then the formatter
373 attempts to limits the size of column titles to at most nTitleLimit
374 display characters in width and a single line of text. The nTitleLimit
375 is useful for queries that have result columns that are scalar
376 subqueries or complex expressions. If those columns lack an AS
377 clause, then the name of the column will be a copy of the expression
378 that defines the column, which in some queries can be hundreds of
379 characters and multiple lines in length, which can reduce the readability
380 of tabular displays. An nTitleLimit somewhere in the range of 10 to 20.
381 can improve readability. The nTitleLimit setting currently only
382 works for **Box**, **Column**, **Line**, **Markdown**, and **Table**
383 styles, though that limitation might change in future releases.
384
385 ### 2.9 Multiple Tuples Per INSERT In QRF_STYLE_Insert (nMultiInsert)
386
387 If the sqlite3_qrf_spec.nMultiInsert value is positive, then the
388 QRF_STYLE_Insert output mode will generate multiple tuples in
389 each INSERT statement until the total number of bytes in the
390 statement exceeds nMultiInsert. A value of a few thousand is
391 recommended here, in order to generate SQL output that is parsed
392 and inserted at maximum speed by SQLite.
393
394 ### 2.10 Word Wrapping In Columnar Styles (nWrap, bWordWrap)
395
396 When using columnar formatting modes (QRF_STYLE_Box, QRF_STYLE_Column,
397 QRF_STYLE_Markdown, or QRF_STYLE_Table), the formatter attempts to limit
398 the width of any individual column to sqlite3_qrf_spec.nWrap characters
399 if nWrap is non-zero. A zero value for nWrap means "unlimited".
400 The nWrap limit might be exceeded if the limit is very small.
401
402 In order to keep individual columns within requested width limits,
403 it is sometimes necessary to wrap the content for a single row of
404 a single column across multiple lines. When this
405 becomes necessary and if the bWordWrap setting is QRF_Yes, then the
406 formatter attempts to split the content on whitespace or at a word boundary.
407 If bWordWrap is QRF_No, then the formatter is free to split content
408 anywhere, including in the middle of a word.
409
410 For narrow columns and wide words, it might sometimes be necessary to split
411 a column in the middle of a word, even when bWordWrap is QRF_Yes.
412
413 ### 2.11 Helping The Output To Fit On The Terminal (nScreenWidth)
414
415 The sqlite3_qrf_spec.nScreenWidth field can be set the number of
416 characters that will fit on one line on the viewer output device.
417 This is typically a number like 80 or 132. The formatter will attempt
418 to reduce the length of output lines, depending on the style, so
419 that all output fits on that screen.
420
421 A value of zero for nScreenWidth means "unknown" or "no width limit".
422 When the value is zero, the formatter makes no attempt to keep the
423 lines of output short.
424
425 The nScreenWidth is a hint to the formatter, not a requirement.
426 The formatter trieds to keep lines below the nScreenWidth limit,
427 but it does not guarantee that it will.
428
429 The nScreenWidth field currently only makes a difference in
430 columnar styles (**Box**, **Column**, **Markdown**, and **Table**)
431 and in the **Line** style.
432
433 ### 2.12 Individual Column Width (nWidth and aWidth)
434
435 The sqlite3_qrf_spec.aWidth field is a pointer to an array of
436 signed 16-bit integers that control the width of individual columns
437 in columnar output modes (QRF_STYLE_Box, QRF_STYLE_Column,
438 QRF_STYLE_Markdown, or QRF_STYLE_Table). The sqlite3_qrf_spec.nWidth
439 field is the number of integers in the aWidth array.
440
441 If aWidth is a NULL pointer or if nWidth is zero, then the array is
442 assumed to be all zeros. If nWidth is less then the number of
443 columns in the output, then zero is used for the width
444 for all columns past then end of the aWidth array.
445
446 The aWidth array is deliberately an array of 16-bit signed integers.
447 Only 16 bits are used because no good comes for having very large
448 column widths. The range if further restricted as follows:
449
450 > ~~~
451 #define QRF_MAX_WIDTH 10000 /* Maximum column width */
452 #define QRF_MIN_WIDTH 0 /* Minimum column width */
453 ~~~
454
455 A width greater than then QRF_MAX_WIDTH is interpreted as QRF_MAX_WIDTH.
456
457 Any aWidth\[\] value of zero means the formatter should use a flexible
458 width column (limited only by sqlite_qrf_spec.mxWidth) that is just
459 big enough to hold the largest row.
460
461 For historical compatibility, aWidth\[\] can contain negative values,
462 down to -QRF_MAX_WIDTH. The column width used is the absolute value
463 of the number in aWidth\[\]. The only difference is that negative
464 values cause the default horizontal alignment to be QRF_ALIGN_Right.
465 The sign of the aWidth\[\] values only affects alignment if the
466 alignment is not otherwise specified by aAlign\[\] or eDfltAlign.
467 Again, negative values for aWidth\[\] entries are supported for
468 backwards compatibility only, and are not recommended for new
469 applications.
470
471 ### 2.13 Alignment (nAlignment, aAlignment, eDfltAlign, eTitleAlign)
472
473 Some cells in a display table might contain a lot of text and thus
474 be wide, or they might contain newline characters or be wrapped by
475 width constraints so that they span many rows of text. Other cells
476 might be narrower and shorter. In columnar formats, the display width
477 of a cell is the maximum of the widest value in the same column, and the
478 display height is the height of the tallest value in the same row.
479 So some cells might be much taller and wider than necessary to hold
480 their values.
481
482 Alignment determines where smaller values are placed within larger cells.
483
484 The sqlite3_qrf_spec.aAlign field points to an array of unsigned characters
485 that specifies alignment (both vertical and horizontal) of individual
486 columns within the table. The sqlite3_qrf_spec.nAlign fields holds
487 the number of entries in the aAlign\[\] array.
488
489 If sqlite3_qrf_spec.aAlign is a NULL pointer or if sqlite3_qrf_spec.nAlign
490 is zero, or for columns to the right of what are specified by
491 sqlite3_qrf_spec.nAlign, the sqlite3_qrf_spec.eDfltAlign value is used
492 for the alignment. Column names can be (and often are) aligned
493 differently, as specified by sqlite3_qrf_spec.eTitleAlign.
494
495 Each alignment value specifies both vertical and horizontal alignment.
496 Horizontal alignment can be left, center, right, or no preference.
497 Vertical alignment can be top, middle, bottom, or no preference.
498 Thus there are 16 possible alignment values, as follows:
499
500 > ~~~
501 /*
502 ** Horizontal Vertial
503 ** ---------- -------- */
504 #define QRF_ALIGN_Auto 0 /* auto auto */
505 #define QRF_ALIGN_Left 1 /* left auto */
506 #define QRF_ALIGN_Center 2 /* center auto */
507 #define QRF_ALIGN_Right 3 /* right auto */
508 #define QRF_ALIGN_Top 4 /* auto top */
509 #define QRF_ALIGN_NW 5 /* left top */
510 #define QRF_ALIGN_N 6 /* center top */
511 #define QRF_ALIGN_NE 7 /* right top */
512 #define QRF_ALIGN_Middle 8 /* auto middle */
513 #define QRF_ALIGN_W 9 /* left middle */
514 #define QRF_ALIGN_C 10 /* center middle */
515 #define QRF_ALIGN_E 11 /* right middle */
516 #define QRF_ALIGN_Bottom 12 /* auto bottom */
517 #define QRF_ALIGN_SW 13 /* left bottom */
518 #define QRF_ALIGN_S 14 /* center bottom */
519 #define QRF_ALIGN_SE 15 /* right bottom */
520 ~~~
521
522 Notice how alignment values with an unspecified horizontal
523 or vertical component can be added to another alignment value
524 for which that component is specified, to get a fully
525 specified alignment. For eample:
526
527 > QRF_ALIGN_Center + QRF_ALIGN_Bottom == QRF_ALIGN_S.
528
529 The alignment for column names is always determined by the
530 eTitleAlign setting. If eTitleAlign is QRF_Auto, then column
531 names use center-bottom alignment, QRF_ALIGN_W, value 14.
532 The aAlign\[\] and eDfltAlign settings have no affect on
533 column names.
534
535 For data in the first nAlign columns, the aAlign\[\] array
536 entry for that column takes precedence. If either the horizontal
537 or vertical alignment has an "auto" value for that column or if
538 a column is beyond the first nAlign entries, then eDfltAlign
539 is used as a backup. If neither aAlign\[\] nor eDfltAlign
540 specify a horizontal alignment, then values are right-aligned
541 (QRF_ALIGN_Right) if they are numeric and left-aligned
542 (QRF_ALIGN_Left) otherwise. If neither aAlign\[\] nor eDfltAlign
543 specify a vertical alignment, then values are top-aligned
544 (QRF_ALIGN_Top).
545
546 *As of 2025-11-08, only horizontal alignment is implemented.
547 The vertical alignment settings are currently ignored and
548 the vertical alignment is always QRF_ALIGN_Top.*
549
550 ### 2.14 Row and Column Separator Strings
551
552 The sqlite3_qrf_spec.zColumnSep and sqlite3_qrf_spec.zRowSep strings
553 are alternative column and row separator character sequences. If not
554 specified (if these pointers are left as NULL) then appropriate defaults
555 are used. Some output styles have hard-coded column and row separators
556 and these settings are ignored for those styles.
557
558 ### 2.15 The Output Table Name
559
560 The sqlite3_qrf_spec.zTableName value is the name of the output table
561 when eStyle is QRF_STYLE_Insert.
562
563 ### 2.16 The Rendering Of NULL (zNull)
564
565 If a value is NULL then show the NULL using the string
566 found in sqlite3_qrf_spec.zNull. If zNull is itself a NULL pointer
567 then NULL values are rendered as an empty string.
568
569 ### 2.17 Optional Value Rendering Callback
570
571 If the sqlite3_qrf_spec.xRender field is not NULL, then each
572 sqlite3_value coming out of the query is first passed to the
573 xRender function, giving that function an opportunity to render
574 the results itself, using whatever custom format is desired.
575 If xRender chooses to render, it should write the rendering
576 into memory obtained from sqlite3_malloc() and return a pointer
577 to that memory. The xRender function can decline
578 to render (for example, based on the sqlite3_value_type() or other
579 characteristics of the value) in which case it can simply return a
580 NULL pointer and the usual default rendering will be used instead.
581
582 The sqlite3_format_query_result() function (which calls xRender)
583 will take responsibility for freeing the string returned by xRender
584 after it has finished using it.
585
586 The eText, eBlob, and eEsc settings above become no-ops if the xRender
587 routine returns non-NULL. In other words, the application-supplied
588 xRender routine is expected to do all of its own quoting and formatting.
589
590 The xRender routine is expected to do character length limiting itself.
591 So the nCharLimit setting becomes a no-op if xRender is used. However
592 the nLineLimit setting is still applied. The nTitleLimit setting is
593 not applicable to xRender because title values come from the
594 sqlite3_column_name() interface not from sqlite3_column_value(),
595 and so that names of columns are never processed by xRender.
596
597 ## 3.0 The `sqlite3_format_query_result()` Interface
598
599 Invoke the `sqlite3_format_query_result(P,S,E)` interface to run
600 the prepared statement P and format its results according to the
601 specification found in S. The sqlite3_format_query_result() function
602 will return an SQLite result code, usually SQLITE_OK, but perhaps
603 SQLITE_NOMEM or SQLITE_ERROR or similar. If an error occurs and if
604 the E parameter is not NULL, then error message text might be written
605 into *E. Any error message text will be stored in memory obtained
606 from sqlite3_malloc() and it is the responsibility of the caller to
607 free that memory by a subsequent call to sqlite3_free().
608
609 <a id="style"></a>
610 ## 4.0 Output Styles
611
612 The result formatter supports a variety of output styles. The
613 output style (sometimes called "output mode") is determined by
614 the eStyle field of the sqlite3_qrf_spec object. The set of
615 supported output modes might increase in future versions.
616 The following output modes are currently defined:
617
618 > ~~~
619 #define QRF_STYLE_Auto 0 /* Choose a style automatically */
620 #define QRF_STYLE_Box 1 /* Unicode box-drawing characters */
621 #define QRF_STYLE_Column 2 /* One record per line in neat columns */
622 #define QRF_STYLE_Count 3 /* Output only a count of the rows of output */
623 #define QRF_STYLE_Csv 4 /* Comma-separated-value */
624 #define QRF_STYLE_Eqp 5 /* Format EXPLAIN QUERY PLAN output */
625 #define QRF_STYLE_Explain 6 /* EXPLAIN output */
626 #define QRF_STYLE_Html 7 /* Generate an XHTML table */
627 #define QRF_STYLE_Insert 8 /* Generate SQL "insert" statements */
628 #define QRF_STYLE_Json 9 /* Output is a list of JSON objects */
629 #define QRF_STYLE_JObject 10 /* Independent JSON objects for each row */
630 #define QRF_STYLE_Line 11 /* One column per line. */
631 #define QRF_STYLE_List 12 /* One record per line with a separator */
632 #define QRF_STYLE_Markdown 13 /* Markdown formatting */
633 #define QRF_STYLE_Off 14 /* No query output shown */
634 #define QRF_STYLE_Quote 15 /* SQL-quoted, comma-separated */
635 #define QRF_STYLE_Stats 16 /* EQP-like output but with performance stats */
636 #define QRF_STYLE_StatsEst 17 /* EQP-like output with planner estimates */
637 #define QRF_STYLE_StatsVm 18 /* EXPLAIN-like output with performance stats */
638 #define QRF_STYLE_Table 19 /* MySQL-style table formatting */
639 ~~~
640
641 In the following subsections, these styles will often be referred
642 to without the "QRF_STYLE_" prefix.
643
644 ### 4.1 Default Style (Auto)
645
646 The **Auto** style means QRF gets to choose an appropriate output
647 style. It will usually choose **Box**, but might also pick one of
648 **Explain** or **Eqp** if the `sqlite3_stmt_explain()` function
649 returns 1 or 2, respectively.
650
651 ### 4.2 Columnar Styles (Box, Column, Markdown, Table)
652
653 The **Box**, **Column**, **Markdown**, and **Table**
654 modes are columnar. This means the output is arranged into neat,
655 uniform-width columns. These styles can use more memory, especially when
656 the query result has many rows, because they need to load the entire output
657 into memory first in order to determine how wide to make each column.
658
659 The nWidth, aWidth, and mxWidth fields of the `sqlite3_qrf_spec` object
660 are used by these styles only, and are ignored by all other styles.
661 The zRowSep and zColumnSep settings are ignored by these styles. The
662 bTitles setting is honored by these styles; it defaults to QRF_SW_On.
663
664 The **Box** style uses Unicode box-drawing character to draw a grid
665 of columns and rows to show the result. The **Table** is the same,
666 except that it uses ASCII-art rather than Unicode box-drawing characters
667 to draw the grid. The **Column** arranges the results in neat columns
668 but does not draw in column or row separator, except that it does draw
669 lines horizontal lines using "`-`" characters to separate the column names
670 from the data below. This is very similar to default output styling in
671 psql. The **Markdown** renders its result in the Markdown table format.
672
673 The **Box** and **Table** styles normally have a border that surrounds
674 the entire result. However, if sqlite3_qrf_spec.bBorder is QRF_No, then
675 that border is omitted, saving a little space both horizontally and
676 vertically.
677
678 #### 4.2.1 Split Column Mode
679
680 If the bSplitColumn field is QRF_Yes, and eStyle is QRF_STYLE_Column,
681 and bTitles is QRF_No, and nScreenWidth is greater than zero, and if
682 the query only returns a single column, then a special rendering known
683 as "Split Column Mode" will be used. In split column mode, instead
684 of showing all results in one tall column, the content wraps vertically
685 so that it appears on the screen as multiple columns, as many as will
686 fit in the available screen width.
687
688 ### 4.3 Line-oriented Styles
689
690 The line-oriented styles output each row of result as it is received from
691 the prepared statement.
692
693 The **List** style is the most familiar line-oriented output format.
694 The **List** style shows output columns for each row on the
695 same line, each separated by a single "`|`" character and with lines
696 terminated by a single newline (\\u000a or \\n). These column
697 and row separator choices can be overridden using the zColumnSep
698 and zRowSep fields of the `sqlite3_qrf_spec` structure. The text
699 formatting is QRF_TEXT_Plain, and BLOB encoding is QRF_BLOB_Text. So
700 characters appear in the output exactly as they appear in the database.
701 Except the eEsp mode defaults to `QRF_ESC_On`, so that control
702 characters are escaped, for safety.
703
704 The **Csv** and **Quote** styles are simply variations on **List**
705 with hard-coded values for some of the sqlite3_qrf_spec settings:
706
707 <table border=1 cellpadding=2 cellspacing=0>
708 <tr><th>&nbsp;<th>Quote<th>Csv
709 <tr><td>zColumnSep<td>","<td>","
710 <tr><td>zRowSep<td>"\\n"<td>"\\r\\n"
711 <tr><td>zNull<td>"NULL"<td>""
712 <tr><td>eText<td>QRF_TEXT_Sql<td>QRF_TEXT_Csv
713 <tr><td>eBlob<td>QRF_BLOB_Sql<td>QRF_BLOB_Text
714 </table>
715
716 The **Html** style generates HTML table content, just without
717 the `<TABLE>..</TABLE>` around the outside.
718
719 The **Insert** style generates a series of SQL "INSERT" statements
720 that will inserts the data that is output into a table whose name is defined
721 by the zTableName field of `sqlite3_qrf_spec`. If zTableName is NULL,
722 then a substitute name is used. If nMultiInsert is positive, then the
723 output will add multiple rows to each INSERT statement until the size
724 of the INSERT statement exceeds nMultiInsert bytes before starting
725 a new INSERT statement.
726
727 The **Json** and **JObject** styles generates JSON text for the query result.
728 The **Json** style produces a JSON array of structures with one
729 structure per row. **JObject** outputs independent JSON objects, one per
730 row, with each structure on a separate line all by itself, and not
731 part of a larger array. In both cases, the labels on the elements of the
732 JSON objects are taken from the column names of the SQL query. So if
733 you have an SQL query that has two or more output columns with the same
734 name, you will end up with JSON structures that have duplicate elements.
735
736 Finally, the **Line** style paints each column of a row on a
737 separate line with the column name on the left and a "`=`" separating the
738 column name from its value. A single blank line appears between rows.
739
740 ### 4.4 EXPLAIN Styles (Eqp, Explain)
741
742 The **Eqp** and **Explain** styles format output for
743 EXPLAIN QUERY PLAN and EXPLAIN statements, respectively. If the input
744 statement is not already an EXPLAIN QUERY PLAN or EXPLAIN statement is
745 is temporarily converted for the duration of the rendering, but
746 is converted back before `sqlite3_format_query_result()` returns.
747
748 ### 4.5 ScanStatus Styles (Stats, StatsEst, StatsVm)
749
750 The **Stats**, **StatsEst**, and **StatsVm** styles are similar to **Eqp**
751 and **Explain** except that they include profiling information
752 from prior executions of the input prepared statement.
753 These modes only work if SQLite has been compiled with
754 -DSQLITE_ENABLE_STMT_SCANSTATUS and if the SQLITE_DBCONFIG_STMT_SCANSTATUS
755 is enabled for the database connection. The **StatsVm** style
756 also requires the bytecode() virtual table which is enabled using
757 the -DSQLITE_ENABLE_BYTECODE_VTAB compile-time option.
758
759 ### 4.6 Other Styles (Count, Off)
760
761 The **Count** style discards all query results and returns
762 a count of the number of rows of output at the end. The **Off**
763 style is completely silent; it generates no output. These corner-case
764 modes are sometimes useful for debugging.
765
766 ### 5.0 Source Code Files
767
768 The SQLite Query Result Formatter is implemented in three source code files:
769
770 * `qrf.c` &rarr; The implementation, written in portable C99
771 * `qrf.h` &rarr; A header file defining interfaces
772 * `README.md` &rarr; This documentation
773
774 To use the SQLite result formatter, include the "`qrf.h`" header file
775 and link the application against the "`qrf.c`" source file.