Documentation Source Text

[http://www.libreoffice.org/|LibreOffice] on Linux and Windows.

<p>
An OpenDocument Presentation or "ODP" file is a
[http://en.wikipedia.org/wiki/Zip_%28file_format%29|ZIP archive] containing
XML files describing presentation slides and separate image files for the
various images that are included as part of the presentation.
(OpenDocument word processor and spreedsheet files are similarly
structured but are not considered by this article.) The reader can
easily see the content of an ODP file by using the "zip -l" command.
For example, the following is the "zip -l" output from a 49-slide presentation
about SQLite from the 2014
<a href="http://southeastlinuxfest.org/">SouthEast LinuxFest</a>
conference:

[http://www.libreoffice.org/|LibreOffice] on Linux and Windows.

<p>
An OpenDocument Presentation or "ODP" file is a
[http://en.wikipedia.org/wiki/Zip_%28file_format%29|ZIP archive] containing
XML files describing presentation slides and separate image files for the
various images that are included as part of the presentation.
(OpenDocument word processor and spreadsheet files are similarly
structured but are not considered by this article.) The reader can
easily see the content of an ODP file by using the "zip -l" command.
For example, the following is the "zip -l" output from a 49-slide presentation
about SQLite from the 2014
<a href="http://southeastlinuxfest.org/">SouthEast LinuxFest</a>
conference:

proc chng {date desc {options {}}} {
  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2018-12-00 (3.26.0)} {
<li>Optimization: When doing an [UPDATE] on a table with [indexes on expressions],
    do not update the expression indexes if they do not refer to any of the columns
    of the table being updated.
<li>Allow the [xBestIndex()] method of [virtual table] implementations to return
    [SQLITE_CONSTRAINT] to indicate that the proposed query plan is unusable and
    should not be given further consideration.
<li>Added the [SQLITE_DBCONFIG_DEFENSIVE] option which disables the ability to 

proc chng {date desc {options {}}} {
  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2018-12-01 (3.26.0)} {
<li>Optimization: When doing an [UPDATE] on a table with [indexes on expressions],
    do not update the expression indexes if they do not refer to any of the columns
    of the table being updated.
<li>Allow the [xBestIndex()] method of [virtual table] implementations to return
    [SQLITE_CONSTRAINT] to indicate that the proposed query plan is unusable and
    should not be given further consideration.
<li>Added the [SQLITE_DBCONFIG_DEFENSIVE] option which disables the ability to 

filling in a box on "supplier registration" forms
submitted to the SQLite developers by various minor clients.  However,
we subsequently learned that "Code of Conduct" has a specific technical
meaning within many software development communities, a meaning which
was at odds with the intent of this document.  Hence, this document
is now renamed and replaced by a 
[Code of Conduct|Code of Conduct] that does comply with the
specific techical requirements was inserted in the place of
the old name.

<p>
This document is still sometimes used as a "Code of Conduct" on supplier
registration forms.  But it is not a Code of Conduct in the same sense
that many communities mean a Code of Conduct.  Rather,
this document describes the foundational ethical principals upon
which SQLite is based.  Another way to look at this document is
as a succinct description of the SQLite Founder's idea of what it
means to be "virtuous".

<p>
Readers can also interpret this document as a primer on the
world-view of the SQLite Founder, to help them better understand

filling in a box on "supplier registration" forms
submitted to the SQLite developers by various minor clients.  However,
we subsequently learned that "Code of Conduct" has a specific technical
meaning within many software development communities, a meaning which
was at odds with the intent of this document.  Hence, this document
is now renamed and replaced by a 
[Code of Conduct|Code of Conduct] that does comply with the
specific technical requirements was inserted in the place of
the old name.

<p>
This document is still sometimes used as a "Code of Conduct" on supplier
registration forms.  But it is not a Code of Conduct in the same sense
that many communities mean a Code of Conduct.  Rather,
this document describes the ethical principals upon
which SQLite is based.  Another way to look at this document is
as a succinct description of the SQLite Founder's idea of what it
means to be "virtuous".

<p>
Readers can also interpret this document as a primer on the
world-view of the SQLite Founder, to help them better understand

<li> Notes on the [optimizer | query optimizer].
<li> How [indexing] works.
<li> The [next generation query planner].
</ul>

<p>A query plan is represented as a tree.
In raw form, as returned by [sqlite3_step()], each node of the tree
consists of four fields:  A integer node id, an integer parent id,
an auxiliary integer field that is not currently used, and a description
of the node.
The entire tree is therefore a table with four columns and zero or more
rows.
The [command-line shell] will usually intercept this table and renders
it as an ASCII-art graph for more convenient viewing.  To defeat the
shells automatic graph rendering, simply include extra white space

<li> Notes on the [optimizer | query optimizer].
<li> How [indexing] works.
<li> The [next generation query planner].
</ul>

<p>A query plan is represented as a tree.
In raw form, as returned by [sqlite3_step()], each node of the tree
consists of four fields:  An integer node id, an integer parent id,
an auxiliary integer field that is not currently used, and a description
of the node.
The entire tree is therefore a table with four columns and zero or more
rows.
The [command-line shell] will usually intercept this table and renders
it as an ASCII-art graph for more convenient viewing.  To defeat the
shells automatic graph rendering, simply include extra white space

overlaps the polygon in the $query_polygon parameter.  The
geopoly_within() function works similarly, but only returns rows for
which the _shape is completely contained within $query_polygon.

<p>
Queries (and also DELETE and UPDATE statements) in which the WHERE
clause contains a bare geopoly_overlap() or geopoly_within() function
make use of the underly R*Tree data structures for a fast lookup that
only has to examine a subset of the rows in the table.  The number of
rows examines depends, of course, on the size of the $query_polygon.
Large $query_polygons will normally need to look at more rows than small
ones.

<p>
Queries against the rowid of a geopoly table are also very quick, even

overlaps the polygon in the $query_polygon parameter.  The
geopoly_within() function works similarly, but only returns rows for
which the _shape is completely contained within $query_polygon.

<p>
Queries (and also DELETE and UPDATE statements) in which the WHERE
clause contains a bare geopoly_overlap() or geopoly_within() function
make use of the underlying R*Tree data structures for a fast lookup that
only has to examine a subset of the rows in the table.  The number of
rows examines depends, of course, on the size of the $query_polygon.
Large $query_polygons will normally need to look at more rows than small
ones.

<p>
Queries against the rowid of a geopoly table are also very quick, even

<p>^Note also that when adding a [CHECK constraint], the CHECK constraint
is not tested against preexisting rows of the table.
^This can result in a table that contains data that
is in violation of the CHECK constraint.  Future versions of SQLite might
change to validate CHECK constraints as they are added.</p>

<p>The ALTER TABLE command works by modifing the SQL text of the schema
stored in the [sqlite_master table].
No changes are made to table content.
Because of this,
the execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
on a table with 10 million rows as it does on a table with 1 row.
</p>
................................................................................
so the date is normalized to 2001-05-01.  ^A similar effect occurs when
the original date is February 29 of a leapyear and the modifier is
&plusmn;N years where N is not a multiple of four.</p>

<p>^The "start of" modifiers (7 through 9) shift the date backwards 
to the beginning of the current month, year or day.</p>

<p>^The "weekday" modifier advances the date forward, if necessary,
to the next date where the weekday number is N. Sunday is 0, Monday is 1,
and so forth.
^If the date is already on the desired weekday, the "weekday" modifier
leaves the date unchanged.  </p>

<p>^The "unixepoch" modifier (11) only works if it immediately follows 
a timestring in the DDDDDDDDDD format. 
^This modifier causes the DDDDDDDDDD to be interpreted not 
as a Julian day number as it normally would be, but as

<p>^Note also that when adding a [CHECK constraint], the CHECK constraint
is not tested against preexisting rows of the table.
^This can result in a table that contains data that
is in violation of the CHECK constraint.  Future versions of SQLite might
change to validate CHECK constraints as they are added.</p>

<p>The ALTER TABLE command works by modifying the SQL text of the schema
stored in the [sqlite_master table].
No changes are made to table content.
Because of this,
the execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
on a table with 10 million rows as it does on a table with 1 row.
</p>
................................................................................
so the date is normalized to 2001-05-01.  ^A similar effect occurs when
the original date is February 29 of a leapyear and the modifier is
&plusmn;N years where N is not a multiple of four.</p>

<p>^The "start of" modifiers (7 through 9) shift the date backwards 
to the beginning of the current month, year or day.</p>

<p>^(The "weekday" modifier advances the date forward, if necessary,
to the next date where the weekday number is N. Sunday is 0, Monday is 1,
and so forth.)^
^If the date is already on the desired weekday, the "weekday" modifier
leaves the date unchanged.  </p>

<p>^The "unixepoch" modifier (11) only works if it immediately follows 
a timestring in the DDDDDDDDDD format. 
^This modifier causes the DDDDDDDDDD to be interpreted not 
as a Julian day number as it normally would be, but as

      {<a href="releaselog/\2_\3_\4.html">\0</a>} title
  }
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}







newsitem {2018-11-05} {Release 3.25.3} {
SQLite [version 3.25.3] is a third patch against 3.25.0 that fixes various
problems that have come to light and which seem serious enough to 
justify a patch.
}

      {<a href="releaselog/\2_\3_\4.html">\0</a>} title
  }
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2018-12-01} {Release 3.26.0} {
SQLite [version 3.26.0] is a routine maintenance release with various
performance and feature enhancements.  See the
[version 3.26.0|release notes] for details.
}

newsitem {2018-11-05} {Release 3.25.3} {
SQLite [version 3.25.3] is a third patch against 3.25.0 that fixes various
problems that have come to light and which seem serious enough to 
justify a patch.
}

    </ul>
    The default setting for this pragma is OFF, which means that all
    references to the table anywhere in the schema are converted to the new name.
    <p>This pragma is provided as a work-around for older programs that
    contain code that expect the incomplete behavior
    of ALTER TABLE RENAME found in older versions of SQLite.
    New applications should leave this flag turned off.
    <p>For compability with older [virtual table] implementations,
    this flag is turned on temporarily while the [sqlite3_module.xRename]
    method is being run.  The value of this flag is restore after the 
    [sqlite3_module.xRename] method finishes.
}

Pragma legacy_file_format {
   <p>^(<b>PRAGMA legacy_file_format;

    </ul>
    The default setting for this pragma is OFF, which means that all
    references to the table anywhere in the schema are converted to the new name.
    <p>This pragma is provided as a work-around for older programs that
    contain code that expect the incomplete behavior
    of ALTER TABLE RENAME found in older versions of SQLite.
    New applications should leave this flag turned off.
    <p>For compatibility with older [virtual table] implementations,
    this flag is turned on temporarily while the [sqlite3_module.xRename]
    method is being run.  The value of this flag is restore after the 
    [sqlite3_module.xRename] method finishes.
}

Pragma legacy_file_format {
   <p>^(<b>PRAGMA legacy_file_format;

<tcl>
RecursiveBubbleDiagram window-function-invocation window-defn frame-spec filter
</tcl>

<p>Window functions are distinguished from ordinary SQL functions by the
presence of an OVER clause.  If a function invocation has an OVER clause
then it is a window function, and if lacks a OVER clause it is an ordinary
function.  Window functions might also have a FILTER
clause in between the function and the OVER clause.

<p>Unlike ordinary functions, window functions
cannot use the DISTINCT keyword.
Also, Window functions may only appear in the result set and in the
ORDER BY clause of a SELECT statement.

<tcl>
RecursiveBubbleDiagram window-function-invocation window-defn frame-spec filter
</tcl>

<p>Window functions are distinguished from ordinary SQL functions by the
presence of an OVER clause.  If a function invocation has an OVER clause
then it is a window function, and if lacks an OVER clause it is an ordinary
function.  Window functions might also have a FILTER
clause in between the function and the OVER clause.

<p>Unlike ordinary functions, window functions
cannot use the DISTINCT keyword.
Also, Window functions may only appear in the result set and in the
ORDER BY clause of a SELECT statement.