Debian Codegraph @

www/35to36.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>SQLite Changes From Version 3.5.9 To 3.6.0</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> Moving From SQLite 3.5.9 to 3.6.0</h1><p>
120 SQLite version 3.6.0 (2008-07-16)
121 contains many changes. As is the custom with
122 the SQLite project, most changes are fully backwards compatible.
123 However, a few of the changes in version 3.6.0 are incompatible and
124 might require modifications to application code and/or makefiles.
125 This document is a briefing on the changes in SQLite 3.6.0
126 with special attention to the incompatible changes.
127 </p>
128 <blockquote>
129 <strong>Key Points:</strong>
130 <ul>
131 <li> The database file format is unchanged. </li>
132 <li> All incompatibilities are on obscure interfaces and hence should
133 have zero impact on most applications. </li>
134 </ul>
135 </blockquote>
136 <h2>1.0 Incompatible Changes</h2><p>
137 Incompatible changes are covered first since they are the most
138 important to maintainers and programmers.
139 </p>
140 <h3>1.1 Overview Of Incompatible Changes</h3><p>
141 <ol>
142 <li><p>Changes to the <a href="c3ref/vfs.html">sqlite3_vfs</a> object</p>
143
144 <ol type="a">
145 <li><p>The signature of the xAccess method has been modified to
146 return an <a href="rescode.html">error code</a> and to store its output into an integer pointed
147 to by a parameter, rather than returning the output directly.
148 This change allows the xAccess() method to report failures.
149 In association with this signature change, a new
150 extended error code <a href="rescode.html#ioerr_access">SQLITE_IOERR_ACCESS</a> has been added.
151 </p></li>
152
153 <li><p>The xGetTempname method has been removed from <a href="c3ref/vfs.html">sqlite3_vfs</a>.
154 In its place, the xOpen method is enhanced to open a temporary file
155 of its own invention when the filename parameter is NULL.</p></li>
156
157 <li><p>Added the xGetLastError() method to <a href="c3ref/vfs.html">sqlite3_vfs</a> for returning
158 filesystem-specific error messages and error codes back to
159 SQLite.</p></li>
160 </ol>
161 </li>
162
163 <li><p>The signature of the xCheckReservedLock method on <a href="c3ref/io_methods.html">sqlite3_io_methods</a>
164 has been modified so that it returns an <a href="rescode.html">error code</a> and stores its
165 boolean result into an integer pointed to by a parameter. In
166 association with this change, a new extended error code
167 <a href="rescode.html#ioerr_checkreservedlock">SQLITE_IOERR_CHECKRESERVEDLOCK</a> has been added.</p></li>
168
169 <li><p>When SQLite is ported to new operating systems (operating systems
170 other than Unix, Windows, and OS/2 for which ports are provided together
171 with the core)
172 two new functions, <a href="c3ref/initialize.html">sqlite3_os_init()</a> and <a href="c3ref/initialize.html">sqlite3_os_end()</a>, must
173 be provided as part of the port.</p></li>
174
175 <li><p>The way in which the IN and NOT IN operators handle NULL values
176 in their right-hand expressions has been brought into compliance with
177 the SQL standard and with other SQL database engines.</p></li>
178
179 <li><p>The column names for the result sets of <a href="lang_select.html">SELECT</a> statements have
180 been tweaked in some cases to work more like other SQL database
181 engines.</p></li>
182
183 <li><p>Changes to compile-time options:</p>
184
185 <ol type="a">
186 <li><p>The SQLITE_MUTEX_APPDEF compile-time parameter is no longer
187 recognized. As a replacement, alternative
188 <a href="c3ref/mutex_alloc.html">mutex implementations</a> may be created
189 at runtime using <a href="c3ref/config.html">sqlite3_config()</a> with the <a href="c3ref/c_config_covering_index_scan.html#sqliteconfigmutex">SQLITE_CONFIG_MUTEX</a>
190 operator and the <a href="c3ref/mutex_methods.html">sqlite3_mutex_methods</a> object.</p></li>
191
192 <li><p>Compile-time options OS_UNIX, OS_WIN, OS_OS2, OS_OTHER, and
193 TEMP_STORE have been renamed to include an "SQLITE_" prefix in order
194 to help avoid namespace collisions with application software. The
195 new names of these options are respectively:
196 SQLITE_OS_UNIX, SQLITE_OS_WIN, SQLITE_OS_OS2, SQLITE_OS_OTHER,
197 and <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a>.</p></li>
198 </ol>
199 </li>
200 </ol>
201 </p>
202 <h3>1.2 Changes To The VFS Layer</h3><p>
203 SQLite <a href="releaselog/3_5_0.html">version 3.5.0</a> introduced a <a href="34to35.html">new OS interface layer</a> that
204 provided an abstraction of the underlying operating system.
205 This was an important innovation and has proven to be helpful
206 in porting and maintaining SQLite.
207 However, the developers have discovered some minor flaws in the
208 original "virtual file system" design introduced in version 3.5.0
209 and so SQLite 3.6.0 includes some small incompatible changes
210 to address these flaws.
211 </p>
212 <blockquote>
213 <strong>Key Point:</strong> The incompatible
214 changes in the SQLite operating-system interface for version 3.6.0
215 only affect the rare applications that make use of the
216 <a href="c3ref/vfs.html">virtual file system</a> interface or that
217 supply an application-defined <a href="c3ref/mutex_alloc.html">mutex implementation</a>
218 or that make use of other obscure compile-time options. The
219 changes introduced by SQLite version 3.6.0 will have zero impact on the
220 vast majority of SQLite applications that use the built-in interfaces
221 to Unix, Windows, and OS/2 and that use the standard build configuration.
222 </blockquote>
223 <h3>1.3 Changes In The Way The IN Operator Handles NULLs</h3><p>
224 All versions of SQLite up to and including version 3.5.9 have mishandled
225 NULL values on the right-hand side of IN and NOT IN operators.
226 Specifically, SQLite has previously ignored NULLs on the right-hand side
227 of IN and NOT IN.
228 </p>
229 <p>
230 Suppose we have a table X1 defined as follows:
231 </p>
232 <blockquote><pre>
233 CREATE TABLE x1(x INTEGER);
234 INSERT INTO x1 VALUES(1);
235 INSERT INTO x1 VALUES(2);
236 INSERT INTO x1 VALUES(NULL);
237 </pre></blockquote><p>
238 Given the definition of X1 above, the following expressions have
239 historically evaluated to FALSE in SQLite, though the correct
240 answer is actually NULL:
241 </p>
242 <blockquote><pre>
243 3 IN (1,2,NULL)
244 3 IN (SELECT * FROM x1)
245 </pre></blockquote><p>
246 Similarly, the following expressions have historically evaluated to
247 TRUE when in fact NULL is also the correct answer here:
248 </p>
249 <blockquote><pre>
250 3 NOT IN (1,2,NULL)
251 3 NOT IN (SELECT * FROM x1)
252 </pre></blockquote><p>
253 The historical behavior of SQLite is incorrect according to the SQL:1999
254 standard and it is inconsistent with the behavior of MySQL and
255 PostgreSQL. Version 3.6.0 changes the behavior of the IN and
256 NOT IN operators to conform to the standard and to give the same
257 results as other SQL database engines.
258 </p>
259 <blockquote>
260 <strong>Key Point:</strong> The change to the way NULL values are handled
261 by the IN and NOT IN operators is technically a bug fix, not a design
262 change. However, maintainers should check to ensure that applications
263 do not depend on the older, buggy behavior prior to upgrading to
264 version 3.6.0.
265 </blockquote>
266 <h3>1.4 Changes To Column Naming Rules</h3><p>
267 The column names reported by join subqueries have been modified slightly
268 in order to work more like other database engines. Consider the following
269 query:
270 </p>
271 <blockquote><pre>
272 CREATE TABLE t1(a);
273 CREATE TABLE t2(x);
274 SELECT * FROM (SELECT t1.a FROM t1 JOIN t2 ORDER BY t2.x LIMIT 1) ORDER BY 1;
275 </pre></blockquote><p>
276 In version 3.5.9 the query above would return a single column named "t1.a".
277 In version 3.6.0 the column name is just "a".
278 </p>
279 <p>
280 SQLite has never made any promises about the names of columns in the
281 result set of <a href="lang_select.html">SELECT</a> statement unless the column contains an AS clause.
282 So this change to column name is technically not an incompatibility.
283 SQLite is merely changing from one undefined behavior to another.
284 Nevertheless, many applications depend on the unspecified column naming
285 behavior of SQLite and so this change is discussed under the
286 incompatible changes subheading.
287 </p>
288 <h3>1.5 Changes To Compile-Time Options</h3><p>
289 Compile-time options to SQLite are controlled by C-preprocessor
290 macros. SQLite version 3.6.0 changes the names of some of these
291 macros so that all C-preprocessor macros that are specific to
292 SQLite begin with the "SQLITE_" prefix. This is done to reduce the
293 risk of name collisions with other software modules.
294 </p>
295 <blockquote>
296 <strong>Key Point:</strong> Changes to compile-time options have the
297 potential to affect makefiles in projects that do customized builds of
298 SQLite. These changes should have zero impact on application code and for
299 most projects which use a standard, default build of SQLite.
300 </blockquote>
301 <h2>2.0 Fully Backwards-Compatible Enhancements</h2><p>
302 In addition to the incompatible changes listed above, SQLite
303 version 3.6.0 adds the following backwards compatible changes and
304 enhancements:
305 </p>
306 <p>
307 <ol>
308
309 <li value="7"><p>The new <a href="c3ref/config.html">sqlite3_config()</a> interface allows an application
310 to customize the behavior of SQLite at run-time. Customizations possible
311 using <a href="c3ref/config.html">sqlite3_config()</a> include the following:</p>
312 <ol type="a">
313 <li><p>Specify an alternative mutex implementation using the
314 <a href="c3ref/c_config_covering_index_scan.html#sqliteconfigmutex">SQLITE_CONFIG_MUTEX</a> verb with the <a href="c3ref/mutex_methods.html">sqlite3_mutex_methods</a> object.</p></li>
315 <li><p>Specify an alternative malloc implementation using the
316 <a href="c3ref/c_config_covering_index_scan.html#sqliteconfigmalloc">SQLITE_CONFIG_MALLOC</a> verb with the <a href="c3ref/mem_methods.html">sqlite3_mem_methods</a> object.</p></li>
317 <li><p>Partially or fully disable the use of mutexes using
318 <a href="c3ref/c_config_covering_index_scan.html#sqliteconfigsinglethread">SQLITE_CONFIG_SINGLETHREAD</a>, <a href="c3ref/c_config_covering_index_scan.html#sqliteconfigmultithread">SQLITE_CONFIG_MULTITHREAD</a> and
319 <a href="c3ref/c_config_covering_index_scan.html#sqliteconfigserialized">SQLITE_CONFIG_SERIALIZED</a>.</p></li>
320 </ol>
321 </li>
322
323 <li><p>A new flag <a href="c3ref/c_open_autoproxy.html">SQLITE_OPEN_NOMUTEX</a> is made available to the
324 <a href="c3ref/open.html">sqlite3_open_v2()</a> interface.</p></li>
325
326 <li><p>The new <a href="c3ref/status.html">sqlite3_status()</a> interface allows an application to query
327 the performance status of SQLite at runtime.
328 </p></li>
329
330 <li><p>The <a href="c3ref/memory_highwater.html">sqlite3_memory_used()</a> and <a href="c3ref/memory_highwater.html">sqlite3_memory_highwater()</a>
331 interfaces are deprecated. The equivalent functionality is now available
332 through <a href="c3ref/status.html">sqlite3_status()</a>.</p></li>
333
334 <li><p>The <a href="c3ref/initialize.html">sqlite3_initialize()</a> interface can be called to explicitly
335 initialize the SQLite subsystem. The <a href="c3ref/initialize.html">sqlite3_initialize()</a> interface is
336 called automatically when invoking certain interfaces so the use of
337 <a href="c3ref/initialize.html">sqlite3_initialize()</a> is not required, but it is recommended.</p></li>
338
339 <li><p>The <a href="c3ref/initialize.html">sqlite3_shutdown()</a> interface causes SQLite to release any
340 system resources (memory allocations, mutexes, open file handles)
341 that might have been allocated by <a href="c3ref/initialize.html">sqlite3_initialize()</a>.</p></li>
342
343 <li><p>The <a href="c3ref/next_stmt.html">sqlite3_next_stmt()</a> interface allows an application to discover
344 all <a href="c3ref/stmt.html">prepared statements</a> associated with a <a href="c3ref/sqlite3.html">database connection</a>.</p></li>
345
346 <li><p>Added the <a href="pragma.html#pragma_page_count">page_count</a> PRAGMA for returning the size of the underlying
347 database file in pages.</p></li>
348
349 <li><p>Added a new <a href="rtree.html">R*Tree index extension</a>.</p></li>
350
351 </ol>
352 </p>
353
354 <p align="center"><small><i>This page was last updated on 2022-01-08 05:02:57Z </small></i></p>
355