Adding upstream version 3.45.1.upstream/3.45.1

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-13 14:07:11 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-13 14:07:11 +0000
commit: 63847496f14c813a5d80efd5b7de0f1294ffe1e3 (patch)
tree: 01c7571c7c762ceee70638549a99834fdd7c411b /www/useovernet.html
parent: Initial commit. (diff)
download: sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.tar.xz
sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.zip
1 files changed, 365 insertions, 0 deletions
diff --git a/www/useovernet.html b/www/useovernet.html
new file mode 100644
index 0000000..b81d321
--- /dev/null
+++ b/www/useovernet.html
@@ -0,0 +1,365 @@
+<!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 Over a Network, Caveats and Considerations</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>
+
+
+
+
+<h1 align="center">SQLite Over a Network,<br/>Caveats and Considerations</h1>
+
+
+<h1>Introduction</h1>
+<p>
+  Users of the SQLite library, particularly application developers,
+  who want to access a SQLite database
+  from different systems connected by a network are often
+  tempted to simply open a <a href="c3ref/sqlite3.html">database connection</a> by specifying
+  a filename which references a database file somewhere within
+  a network filesystem. ("remote database" here)
+  This "file" is then accessed by means of
+  OS API's which permit the illusion of I/O from/to a local file.
+  The illusion is good but imperfect in important ways.
+</p><p>
+  This simple, "remote database" approach is usually not the best way
+  to use a single SQLite database from multiple systems,
+  (even if it appears to "work"),
+  as it often leads to various kinds of trouble and grief.
+  Because these problems are inevitable with some usages,
+  but not frequent or repeatable,
+  it behooves application developers to not rely
+  on early testing success to decide
+  that their remote database use will work as desired.
+</p>
+<h1>Issues Arising with Remote Database Files</h1>
+<p>
+This diagram shows components and their linkages
+for reference in the discussion following:
+</p>
+
+<div class="center imgcontainer">
+<div style="max-width:705px;"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" width="705" height="84" viewBox="0 0 940.32 112.32">
+<path d="M2,110L182,110L182,2L2,2Z"  style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="92" y="39" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Client</text>
+<text x="92" y="73" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Application</text>
+<path d="M380,110L560,110L560,2L380,2Z"  style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="470" y="22" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">SQLite</text>
+<text x="470" y="56" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Database</text>
+<text x="470" y="90" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Engine</text>
+<path d="M758,12L758,99A90 10 0 0 0 938 99L938,12A90 10 0 0 0 758 12A90 10 0 0 0 938 12"  style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="848" y="47" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Database</text>
+<text x="848" y="81" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">File(s)</text>
+<polygon points="182,56 193,51 193,60" style="fill:rgb(0,0,0)"/>
+<polygon points="380,56 368,60 368,51" style="fill:rgb(0,0,0)"/>
+<path d="M187,56L374,56"  style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="281" y="37" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">SQLite API</text>
+<text x="281" y="74" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Calls</text>
+<polygon points="560,56 571,51 571,60" style="fill:rgb(0,0,0)"/>
+<polygon points="758,56 746,60 746,51" style="fill:rgb(0,0,0)"/>
+<path d="M565,56L752,56"  style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="659" y="37" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">DB Engine</text>
+<text x="659" y="74" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">File I/O</text>
+</svg>
+</div>
+</div>
+<p>
+  The issues arise from the properties and utilization
+  of the two data/control channels between the above three blocks.
+</p>
+
+<h2>Channel Traffic Volume</h2>
+<p>
+  The "API Call" channel carries less information
+  than the "File I/O" channel.
+  API calls to submit queries or specify data modification
+  normally require substantially fewer bits to be passed
+  back and forth than are transferred to/from
+  the database file to store or find the data.
+  Query result retrieval will normally require much more file traffic
+  than API traffic because the data to be returned is rarely
+  to be found without reading unrequested data.
+</p>
+<h2>Channel Bandwidth</h2>
+<p>
+  The API Call channel operates at processor main memory speeds
+  (Giga-words/second), with data often passed by reference (and so not copied.)
+  In contrast, even the fastest File I/O channels are slower.
+  They require the data to be copied, usually over a medium
+  requiring bit-serialization. For spinning magnetic media,
+  transfers await platter rotation and head movement, then
+  are limited by spin velocity.
+</p>
+<p>
+  When the File I/O channel includes a network connection,
+  (in addition to some genuine File I/O at its far end),
+  additional slowness is imposed. Even where raw transfer
+  rate does not limit bandwidth, the traffic must still be
+  packetized and buffered at both ends.
+  Additional layers of I/O handlers add scheduling delays.
+  However, slowed transfers are the least significant
+  issue with network filesystems.
+</p>
+<h2>Channel Reliability</h2>
+<p>
+  The "API Call" channel is highly reliable, to the extent
+  that error rates are unstated and ignored as negligible.
+  The channel fails only when the system loses power
+  (excepting meteorites, etc.)
+</p>
+<p>
+  The "File I/O" channel, when it directly reaches a local storage device,
+  is also highly reliable.
+  (Spinning storage MTBF exceeds 1 million hours,
+  and NVRAM lasts longer.)
+  Local devices also have a characteristic
+  which is critical for enabling database management software
+  to be designed to ensure <a href="transactional.html">ACID</a> behavior:
+  When all process writes to the device have completed,
+  (when POSIX fsync() or Windows FlushFileBuffers() calls return),
+  the filesystem then either has
+  stored the "written" data or will do so
+  before storing any subsequently written data.
+</p>
+<p>
+  When network filesystem apparatus and software layers are interposed
+  between filesystem clients and a filesystem over an actual storage device,
+  significant sources of failure and misbehavior are introduced.
+  While network data transfers are error-checked well, transfer packets
+  do not all reliably arrive at their destination once sent.
+  Some packets are clobbered by other packets and must be resent.
+  Under packet clobbering conditions, repeated retries
+  can impose delays exceeding
+  what is needed for similar data to reach local storage.
+  Some portions of what a client writes can end up stored
+  out of time order relative to other portions written.
+</p>
+<p>
+  Because of the disordering and outright data loss
+  which occur in network filesystem writes, it is critical
+  that sets of file writes can be accurately known to be done
+  before a subsequent set of file writes begins.
+  This assurance is obtained by use of robustly designed
+  and correctly implemented fsync() (or equivalent) OS functions.
+  Unfortunately for some applications, network filesystem sync
+  operation can be less robust than local filesystem sync.
+  Attaining robust sync in the face of network packet transport errors
+  is hard, and safeguards are sometimes relaxed in favor of performance.
+</p>
+<p>
+  A similar hazard arises with file locking in network filesystems.
+  SQLite relies on exclusive locks for write operations, and those have
+  been known to operate incorrectly for some network filesystems. This
+  has led to database corruption. That may happen again as the designers
+  of such change their implementation to suit more common use cases.
+</p>
+<p>
+  The bottom line is that network filesystem sync and locking reliability
+  vary among implementations and installations. The design
+  assumptions upon which it relies may hold more true where
+  an application is tested than where it is relied upon.
+  <b>Rely upon it at your (and your customers') peril.</b>
+  See <a href="lockingv3.html#how_to_corrupt">How To Corrupt Your Database Files</a>.
+</p>
+
+<h1>Performance and Reliability Issues</h1>
+<p>
+  From the above diagram and discussion, it is obvious that
+  performance (aka "speed") is degraded by insertion
+  of a network link into one of the two channels.
+  Consideration of relative traffic volumes between
+  the API Call channel and the File I/O channel
+  reveals that such insertion will have less performance
+  impact at the API Call channel.
+</p>
+<p>
+  Consideration of reliability impact is easier, with a clearer outcome:
+  Inserting a network link into the API Call channel may also result
+  in call failures at times. But if the Client Application
+  has bothered to use SQL/SQLite transactions properly,
+  such failures will only cause a transaction to fail
+  and be rolled back, without compromising the integrity
+  of the data. In contrast, if the network link is
+  inserted into the File I/O channel, transactions may fail
+  (as for the API Call insertion) but with the additional
+  effect that the remote database is corrupted.
+</p>
+<p>
+  These network unreliability issues can be mitigated,
+  completely or to an acceptable degree,
+  by using SQLite in rollback mode.
+  However, the SQLite library is not tested in across-a-network
+  scenarios, nor is that reasonably possible.
+  Hence, use of a remote database is done <b>at the user's risk</b>.
+</p>
+
+<h1>Recommendations</h1>
+<p>
+  Generally, if your data is separated from the application
+  by a network, you want to use a client/server database.
+  This is due to the fact that the database engine acts
+  as a bandwidth-reducing filter on the database traffic.
+</p><p>
+  If your data is separated from the application by a network,
+  you want the low-traffic link to be across the network,
+  not the high-traffic link. This means that the database engine
+  needs to be on the same machine as the database itself.
+  Such is the case with a client/server database like PostgreSQL.
+  SQLite is different in that the database engine runs on
+  the same machine as the application, which forces the
+  higher-traffic link to traverse the network in remote
+  database scenarios.  That normally results in lower performance.
+</p><p>
+  Network filesystems do not support the ability to do
+  simultaneous reads and writes while at the same time
+  keeping the database consistent.
+  So if you have multiple clients on multiple different
+  machines which need to do simultaneous database
+  reads and writes, you have these choices:
+</p><p>
+  1.  Use a client/server database engine.
+  <a href=https://postgresql.org/>PostgreSQL</a>
+  is an excellent choice. A variation of this is:
+</p><p>
+  2.  Host an SQLite database in <a href="wal.html">WAL mode</a>, but do
+  all reads and writes from processes on the same machine
+  that stores the database file.
+  Implement a proxy that runs on the database machine that
+  relays read/write requests from remote machines.
+</p><p>
+  3.  Use SQLite in <a href="isolation.html">rollback mode</a>.
+  This means you can have multiple simultaneous readers or one writer,
+  but not simultaneous readers and writers.
+</p><p>
+  Application programmers should be cognizant of the possibility
+  that their application's users will elect to use a remote database
+  if they can do so. Unless one of the above choices
+  has been effected, or one at a time, exclusive access is used,
+  a programmer should consider blocking that
+  election unless reliability is of little importance.
+</p>
+<h1>Summary</h1>
+<p>
+  Choose the technology that is right for you and your customers.
+  If your data lives on a different machine from your application,
+  then you should consider a client/server database.
+  SQLite is designed for situations where the data and application
+  coexist on the same machine.
+  SQLite can still be made to work in many remote database
+  situations, but a client/server solution will usually work
+  better in that scenario.
+</p>
+<p align="center"><small><i>This page last modified on  <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink"  data-href="https://sqlite.org/docsrc/finfo/pages/useovernet.in?m=9c50e45c37">2022-06-22 21:14:29</a> UTC </small></i></p>
+
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-13 14:07:11 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-13 14:07:11 +0000
commit	63847496f14c813a5d80efd5b7de0f1294ffe1e3 (patch)
tree	01c7571c7c762ceee70638549a99834fdd7c411b /www/useovernet.html
parent	Initial commit. (diff)
download	sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.tar.xz sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.zip