Debian Codegraph @

www/different.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Distinctive Features Of SQLite</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118
119 <h1 align=center>Distinctive Features Of SQLite</h1>
120
121 <p>
122 This page highlights some of the characteristics of SQLite that are
123 unusual and which make SQLite different from many other SQL
124 database engines.
125 </p>
126
127 <a name="zeroconfig"></a>
128 <p><b>Zero-Configuration</b></p>
129 <blockquote>
130 SQLite does not need to be "installed" before it is used.
131 There is no "setup" procedure. There is no
132 server process that needs to be started, stopped, or configured.
133 There is
134 no need for an administrator to create a new database instance or assign
135 access permissions to users.
136 SQLite uses no configuration files.
137 Nothing needs to be done to tell the system that SQLite is running.
138 No actions are required to recover after a system crash or power failure.
139 There is nothing to troubleshoot.
140 <p>
141 SQLite just works.
142 <p>
143 Other more familiar database engines run great once you get them going.
144 But doing the initial installation and configuration can be
145 intimidatingly complex.
146 </blockquote>
147 <a name="serverless"></a>
148 <p><b>Serverless</b></p>
149 <blockquote>
150 <p>
151 <i>(See also the <a href="serverless.html">serverless</a> documentation page.)</i>
152 <p>
153 Most SQL database engines are implemented as a separate server
154 process. Programs that want to access the database communicate
155 with the server using some kind of interprocess communication
156 (typically TCP/IP) to send requests to the server and to receive
157 back results. SQLite does not work this way. With SQLite, the
158 process that wants to access the database reads and writes
159 directly from the database files on disk. There is no intermediary
160 server process.
161 <p>
162 There are advantages and disadvantages to being serverless. The
163 main advantage is that there is no separate server process
164 to install, setup, configure, initialize, manage, and troubleshoot.
165 This is one reason why SQLite is a "zero-configuration" database
166 engine. Programs that use SQLite require no administrative support
167 for setting up the database engine before they are run. Any program
168 that is able to access the disk is able to use an SQLite database.
169 <p>
170 On the other hand, a database engine that uses a server can provide
171 better protection from bugs in the client application - stray pointers
172 in a client cannot corrupt memory on the server. And because a server
173 is a single persistent process, it is able to control database access with
174 more precision, allowing for finer grain locking and better concurrency.
175 <p>
176 Most SQL database engines are client/server based. Of those that are
177 serverless, SQLite is the only one that this author knows of that
178 allows multiple applications to access the same database at the same time.
179 </blockquote>
180 <a name="onefile"></a>
181 <p><b>Single Database File</b></p>
182 <blockquote>
183 An SQLite database is a single ordinary disk file that can be located
184 anywhere in the directory hierarchy. If SQLite can read
185 the disk file then it can read anything in the database. If the disk
186 file and its directory are writable, then SQLite can change anything
187 in the database. Database files can easily be copied onto a USB
188 memory stick or emailed for sharing.
189 <p>
190 Other SQL database engines tend to store data as a large collection of
191 files. Often these files are in a standard location that only the
192 database engine itself can access. This makes the data more secure,
193 but also makes it harder to access. Some SQL database engines provide
194 the option of writing directly to disk and bypassing the filesystem
195 altogether. This provides added performance, but at the cost of
196 considerable setup and maintenance complexity.
197 </blockquote>
198 <a name="onefile"></a>
199 <p><b>Stable Cross-Platform Database File</b></p>
200 <blockquote>
201 The SQLite file format is cross-platform. A database file written
202 on one machine can be copied to and used on a different machine with
203 a different architecture. Big-endian or little-endian, 32-bit or
204 64-bit does not matter. All machines use the same file format.
205 Furthermore, the developers have pledged to keep the file format
206 stable and backwards compatible, so newer versions of SQLite can
207 read and write older database files.
208 <p>
209 Most other SQL database engines require you to dump and restore
210 the database when moving from one platform to another and often
211 when upgrading to a newer version of the software.
212 </blockquote>
213 <a name="small"></a>
214 <p><b>Compact</b></p>
215 <blockquote>
216 When optimized for size, the whole SQLite library with everything enabled
217 is <a href="footprint.html">less than 1MiB in size</a>
218 (as measured on an ix86 using the "size"
219 utility from the GNU compiler suite.) Unneeded features can be disabled
220 at compile-time to further reduce the size of the library even further.
221 <p>
222 Most other SQL database engines are much larger than this. IBM boasts
223 that its recently released CloudScape database engine is "only" a 2MiB
224 jar file - an order of magnitude larger than SQLite even after it is
225 compressed!
226 Firebird boasts that its client-side library is only 350KiB. That's
227 as big as SQLite and does not even contain the database engine.
228 The Berkeley DB library from Oracle is 450KiB and it omits SQL
229 support, providing the programmer with only simple key/value pairs.
230 </blockquote>
231 <a name="typing"></a>
232 <p><b>Manifest typing</b></p>
233 <blockquote>
234 Most SQL database engines use static typing. A datatype is associated
235 with each column in a table and only values of that particular datatype
236 are allowed to be stored in that column. SQLite relaxes this restriction
237 by using manifest typing.
238 In manifest typing, the datatype is a property of the value itself, not
239 of the column in which the value is stored.
240 SQLite thus allows the user to store
241 any value of any datatype into any column regardless of the declared type
242 of that column. (There are some exceptions to this rule: An INTEGER
243 PRIMARY KEY column may only store integers. And SQLite attempts to coerce
244 values into the declared datatype of the column when it can.)
245 <p>
246 As far as we can tell, the SQL language specification allows the use
247 of manifest typing. Nevertheless, most other SQL database engines are
248 statically typed and so some people
249 feel that the use of manifest typing is a bug in SQLite. But the authors
250 of SQLite feel very strongly that this is a feature. The use of manifest
251 typing in SQLite is a deliberate design decision which has proven in practice
252 to make SQLite more reliable and easier to use, especially when used in
253 combination with dynamically typed programming languages such as Tcl and
254 Python.
255 </blockquote>
256 <a name="flex"></a>
257 <p><b>Variable-length records</b></p>
258 <blockquote>
259 Most other SQL database engines allocated a fixed amount of disk space
260 for each row in most tables. They play special tricks for handling
261 BLOBs and CLOBs which can be of wildly varying length. But for most
262 tables, if you declare a column to be a VARCHAR(100) then the database
263 engine will allocate
264 100 bytes of disk space regardless of how much information you actually
265 store in that column.
266 <p>
267 SQLite, in contrast, uses only the amount of disk space actually
268 needed to store the information in a row. If you store a single
269 character in a VARCHAR(100) column, then only a single byte of disk
270 space is consumed. (Actually two bytes - there is some overhead at
271 the beginning of each column to record its datatype and length.)
272 <p>
273 The use of variable-length records by SQLite has a number of advantages.
274 It results in smaller database files, obviously. It also makes the
275 database run faster, since there is less information to move to and from
276 disk. And, the use of variable-length records makes it possible for
277 SQLite to employ manifest typing instead of static typing.
278 </blockquote>
279 <a name="readable"></a>
280 <p><b>Readable source code</b></p>
281 <blockquote>
282 The source code to SQLite is designed to be readable and accessible to
283 the average programmer. All procedures and data structures and many
284 automatic variables are carefully commented with useful information about
285 what they do. Boilerplate commenting is omitted.
286 </blockquote>
287 <a name="vdbe"></a>
288 <p><b>SQL statements compile into virtual machine code</b></p>
289 <blockquote>
290 Every SQL database engine compiles each SQL statement into some kind of
291 internal data structure which is then used to carry out the work of the
292 statement. But in most SQL engines that internal data structure is a
293 complex web of interlinked structures and objects. In SQLite, the compiled
294 form of statements is a short program in a machine-language like
295 representation. Users of the database can view this
296 <a href="opcode.html">virtual machine language</a>
297 by prepending the <a href="lang_explain.html">EXPLAIN</a> keyword
298 to a query.
299 <p>
300 The use of a virtual machine in SQLite has been a great benefit to the
301 library's development. The virtual machine provides a crisp, well-defined
302 junction between the front-end of SQLite (the part that parses SQL
303 statements and generates virtual machine code) and the back-end (the
304 part that executes the virtual machine code and computes a result.)
305 The virtual machine allows the developers to see clearly and in an
306 easily readable form what SQLite is trying to do with each statement
307 it compiles, which is a tremendous help in debugging.
308 Depending on how it is compiled, SQLite also has the capability of
309 tracing the execution of the virtual machine - printing each
310 virtual machine instruction and its result as it executes.
311 </blockquote>
312 <a name="license"></a>
313 <p><b>Public domain</b></p>
314 <blockquote>
315 The source code for SQLite is in the public domain. No claim of copyright
316 is made on any part of the core source code. (The documentation and test
317 code is a different matter - some sections of documentation and test logic
318 are governed by open-source licenses.) All contributors to the
319 SQLite core software have signed affidavits specifically disavowing any
320 copyright interest in the code. This means that anybody is able to legally
321 do anything they want with the SQLite source code.
322 <p>
323 There are other SQL database engines with liberal licenses that allow
324 the code to be broadly and freely used. But those other engines are
325 still governed by copyright law. SQLite is different in that copyright
326 law simply does not apply.
327 <p>
328 The source code files for other SQL database engines typically begin
329 with a comment describing your legal rights to view and copy that file.
330 The SQLite source code contains no license since it is not governed by
331 copyright. Instead of a license, the SQLite source code offers a blessing:
332 <blockquote>
333 <i>May you do good and not evil<br>
334 May you find forgiveness for yourself and forgive others<br>
335 May you share freely, never taking more than you give.</i>
336 </blockquote>
337 </blockquote>
338 <a name="extensions"></a>
339 <p><b>SQL language extensions</b></p>
340 <blockquote>
341 SQLite provides a number of enhancements to the SQL language
342 not normally found in other database engines.
343 The EXPLAIN keyword and manifest typing have already been mentioned
344 above. SQLite also provides statements such as
345 <a href="lang_replace.html">REPLACE</a> and the
346 <a href="lang_conflict.html">ON CONFLICT</a> clause that allow for
347 added control over the resolution of constraint conflicts.
348 SQLite supports <a href="lang_attach.html">ATTACH</a> and
349 <a href="lang_detach.html">DETACH</a> commands that allow multiple
350 independent databases to be used together in the same query.
351 And SQLite defines APIs which allow the user to add new
352 <a href="c3ref/create_function.html">SQL functions</a>
353 and <a href="c3ref/create_collation.html">collating sequences</a>.
354 </blockquote>
355
356 <p align="center"><small><i>This page was last updated on 2025-05-31 13:08:22Z </small></i></p>
357