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