Documentation Source Text

Check-in [296e23a4b8]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Preliminary documentation for the enhancements to index_info and index_xinfo to support viewing the on-disk representation of WITHOUT ROWID tables.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 296e23a4b8d6decb27b1f9fb29c0d205bd5a3ad08e652c7e0ce9bdbcf6120eb2
User & Date: drh 2019-07-17 13:02:07
Context
2019-07-26
20:22
Update the checklist.tcl script to align with the latest code on the website. check-in: ef7dae4ce8 user: drh tags: trunk
2019-07-17
13:02
Preliminary documentation for the enhancements to index_info and index_xinfo to support viewing the on-disk representation of WITHOUT ROWID tables. check-in: 296e23a4b8 user: drh tags: trunk
2019-07-15
19:25
Update syntax diagrams to show the FILTER clause on aggregate functions. Start a change log for version 3.30.0. check-in: ba9800704d user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

19
20
21
22
23
24
25



26
27
28
29
30
31
32
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2019-10-00 (3.30.0)} {
<li> Add support for the [FILTER clause on aggregate functions].



}

chng {2019-07-10 (3.29.0)} {
<li> Added the [SQLITE_DBCONFIG_DQS_DML] and [SQLITE_DBCONFIG_DQS_DDL]
     actions to [sqlite3_db_config()] for activating and deactivating
     the [double-quoted string literal] misfeature.  Both default to "on"
     for legacy compatibility, but developers are encouraged to turn them







>
>
>







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2019-10-00 (3.30.0)} {
<li> Add support for the [FILTER clause on aggregate functions].
<li> The [index_info] and [index_xinfo] pragmas are enhanced to provide
     information about the on-disk representation of [WITHOUT ROWID]
     tables.
}

chng {2019-07-10 (3.29.0)} {
<li> Added the [SQLITE_DBCONFIG_DQS_DML] and [SQLITE_DBCONFIG_DQS_DDL]
     actions to [sqlite3_db_config()] for activating and deactivating
     the [double-quoted string literal] misfeature.  Both default to "on"
     for legacy compatibility, but developers are encouraged to turn them

Changes to pages/pragma.in.

1468
1469
1470
1471
1472
1473
1474







1475
1476
1477
1478
1479
1480
1481
....
1495
1496
1497
1498
1499
1500
1501







1502
1503
1504
1505
1506
1507
1508

    <p>Output columns from the index_info pragma are as follows:
    <ol>
    <li>^(The rank of the column within the index.  (0 means left-most.))^
    <li>^The rank of the column within the table being indexed.
    <li>^The name of the column being indexed.
    </ol>







}

Pragma index_xinfo {
    <p>^(<b>PRAGMA DB.index_xinfo(</b><i>index-name</i><b>);</b></p>
    <p>This pragma returns information about every column in an index.)^
    ^(Unlike this [index_info pragma], this pragma returns information about
    every column in the index, not just the key columns.)^
................................................................................
    <li>^1 if the index-column is sorted in reverse (DESC) order by the
         index and 0 otherwise.
    <li>^The name for the [collating sequence]
         used to compare values in the index-column.
    <li>^1 if the index-column is a key column and 0 if the index-column
         is an auxiliary column.
    </ol>







}

Pragma index_list {
    <p>^(<b>PRAGMA DB.index_list(</b><i>table-name</i><b>);</b></p>
    <p>This pragma returns one row for each index associated with the
    given table.)^
    <p>Output columns from the index_list pragma are as follows:







>
>
>
>
>
>
>







 







>
>
>
>
>
>
>







1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
....
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522

    <p>Output columns from the index_info pragma are as follows:
    <ol>
    <li>^(The rank of the column within the index.  (0 means left-most.))^
    <li>^The rank of the column within the table being indexed.
    <li>^The name of the column being indexed.
    </ol>

    <p>If there is no index named <i>index-name</i> but there is a
    [WITHOUT ROWID] table name <i>index_name</i>, then (as of
    SQLite [version 3.30.0] on [dateof:3.30.0]) this pragma returns the
    PRIMARY KEY columns of the WITHOUT ROWID table as they are used
    in the records of the underlying b-tree, which is to say with
    duplicate columns removed.
}

Pragma index_xinfo {
    <p>^(<b>PRAGMA DB.index_xinfo(</b><i>index-name</i><b>);</b></p>
    <p>This pragma returns information about every column in an index.)^
    ^(Unlike this [index_info pragma], this pragma returns information about
    every column in the index, not just the key columns.)^
................................................................................
    <li>^1 if the index-column is sorted in reverse (DESC) order by the
         index and 0 otherwise.
    <li>^The name for the [collating sequence]
         used to compare values in the index-column.
    <li>^1 if the index-column is a key column and 0 if the index-column
         is an auxiliary column.
    </ol>

    <p>If there is no index named <i>index-name</i> but there is a
    [WITHOUT ROWID] table name <i>index_name</i>, then (as of
    SQLite [version 3.30.0] on [dateof:3.30.0]) this pragma returns the
    columns of the WITHOUT ROWID table as they are used
    in the records of the underlying b-tree, which is to say with
    de-duplicated PRIMARY KEY columns first followed by data columns.
}

Pragma index_list {
    <p>^(<b>PRAGMA DB.index_list(</b><i>table-name</i><b>);</b></p>
    <p>This pragma returns one row for each index associated with the
    given table.)^
    <p>Output columns from the index_list pragma are as follows: