~ubuntu-branches/ubuntu/wily/sqlite3/wily

onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">

112

113

</form>

114

</div>

115

</table>

116

</div></div></div></div>

117

</td></tr></table>

118

119

120

121

<h1> Moving From SQLite 3.5.9 to 3.6.0</h1><p>

122

SQLite version 3.6.0 contains many changes. As is the custom with

123

the SQLite project, most changes are fully backwards compatible.

124

However, a few of the changes in version 3.6.0 are incompatible and

125

might require modifications to application code and/or makefiles.

126

This document is a briefing on the changes in SQLite 3.6.0

127

with special attention to the incompatible changes.

128

</p>

129

130

<strong>Key Points:</strong>

131

<ul>

132

<li> The database file format is unchanged. </li>

133

<li> All incompatibilities are on obscure interfaces and hence should

134

have zero impact on most applications. </li>

135

</ul>

136

</blockquote>

137

<h2>1.0 Incompatible Changes</h2><p>

138

Incompatible changes are covered first since they are the most

139

important to maintainers and programmers.

140

</p>

141

<h3>1.1 Overview Of Incompatible Changes</h3><p>

142

<ol>

143

<li><p>Changes to the <a href="c3ref/vfs.html">sqlite3_vfs</a> object</p>

144

145

146

<li><p>The signature of the xAccess method has been modified to

147

return an <a href="c3ref/c_abort.html">error code</a> and to store its output into an integer pointed

148

to by a parameter, rather than returning the output directly.

149

This change allows the xAccess() method to report failures.

150

In association with this signature change, a new

151

extended error code <a href="c3ref/c_abort_rollback.html">SQLITE_IOERR_ACCESS</a> has been added.

152

</p></li>

153

154

<li><p>The xGetTempname method has been removed from <a href="c3ref/vfs.html">sqlite3_vfs</a>.

155

In its place, the xOpen method is enhanced to open a temporary file

156

of its own invention when the filename parameter is NULL.</p></li>

157

158

<li><p>Added the xGetLastError() method to <a href="c3ref/vfs.html">sqlite3_vfs</a> for returning

159

filesystem-specific error messages and error codes back to

160

SQLite.</p></li>

161

</ol>

162

</li>

163

164

<li><p>The signature of the xCheckReservedLock method on <a href="c3ref/io_methods.html">sqlite3_io_methods</a>

165

has been modified so that it returns an <a href="c3ref/c_abort.html">error code</a> and stores its

166

boolean result into an integer pointed to by a parameter. In

167

association with this change, a new extended error code

168

<a href="c3ref/c_abort_rollback.html">SQLITE_IOERR_CHECKRESERVEDLOCK</a> has been added.</p></li>

169

170

<li><p>When SQLite is ported to new operation systems (operating systems

171

other than Unix, Windows, and OS/2 for which ports are provided together

172

with the core)

173

two new functions, <a href="c3ref/initialize.html">sqlite3_os_init()</a> and <a href="c3ref/initialize.html">sqlite3_os_end()</a>, must

174

be provided as part of the port.</p></li>

175

176

<li><p>The way in which the IN and NOT IN operators handle NULL values

177

in their right-hand expressions has been brought into compliance with

178

the SQL standard and with other SQL database engines.</p></li>

179

180

<li><p>The column names for the result sets of <a href="lang_select.html">SELECT</a> statements have

181

been tweaked in some cases to work more like other SQL database

182

engines.</p></li>

183

184

<li><p>Changes to compile-time options:</p>

185

186

187

<li><p>The SQLITE_MUTEX_APPDEF compile-time parameter is no longer

188

recognized. As a replacement, alternative

189

<a href="c3ref/mutex_alloc.html">mutex implementations</a> may be created

190

at runtime using <a href="c3ref/config.html">sqlite3_config()</a> with the <a href="c3ref/c_config_getmalloc.html#sqliteconfigmutex">SQLITE_CONFIG_MUTEX</a>

191

operator and the <a href="c3ref/mutex_methods.html">sqlite3_mutex_methods</a> object.</p></li>

192

193

<li><p>Compile-time options OS_UNIX, OS_WIN, OS_OS2, OS_OTHER, and

194

TEMP_STORE have been renamed to include an "SQLITE_" prefix in order

195

to help avoid namespace collisions with application software. The

196

new names of these options are respectively:

197

SQLITE_OS_UNIX, SQLITE_OS_WIN, SQLITE_OS_OS2, SQLITE_OS_OTHER,

198

and <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a>.</p></li>

199

</ol>

200

</li>

201

</ol>

202

</p>

203

<h3>1.2 Changes To The VFS Layer</h3><p>

204

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

205

provided an abstraction of the underlying operating system.

206

This was an important innovation and has proven to be helpful

207

in porting and maintaining SQLite.

208

However, the developers have discovered some minor flaws in the

209

original "virtual file system" design introduced in version 3.5.0

210

and so SQLite 3.6.0 includes some small incompatible changes

211

to address these flaws.

212

</p>

213

214

<strong>Key Point:</strong> The incompatible

215

changes in the SQLite operating-system interface for version 3.6.0

216

only affect the rare applications that make use of the

217

<a href="c3ref/vfs.html">virtual file system</a> interface or that

218

supply an application-defined <a href="c3ref/mutex_alloc.html">mutex implementation</a>

219

or that make use of other obscure compile-time options. The

220

changes introduced by SQLite version 3.6.0 will have zero impact on the

221

vast majority of SQLite applications that use the built-in interfaces

222

to Unix, Windows, and OS/2 and that use the standard build configuration.

223

</blockquote>

224

<h3>1.3 Changes In The Way The IN Operator Handles NULLs</h3><p>

225

All versions of SQLite up to and including version 3.5.9 have mishandled

226

NULL values on the right-hand side of IN and NOT IN operators.

227

Specifically, SQLite has previously ignored NULLs on the right-hand side

228

of IN and NOT IN.

229

</p>

230

<p>

231

Suppose we have a table X1 defined as follows:

232

</p>

233

234

CREATE TABLE x1(x INTEGER);

235

INSERT INTO x1 VALUES(1);

236

INSERT INTO x1 VALUES(2);

237

INSERT INTO x1 VALUES(NULL);

238

</pre></blockquote><p>

239

Given the definition of X1 above, the following expressions have

240

historically evaluated to FALSE in SQLite, though the correct

241

answer is actually NULL:

242

</p>

243

244

3 IN (1,2,NULL)

245

3 IN (SELECT * FROM x1)

246

</pre></blockquote><p>

247

Similarly, the following expressions have historically evaluated to

248

TRUE when in fact NULL is also the correct answer here:

249

</p>

250

251

3 NOT IN (1,2,NULL)

252

3 NOT IN (SELECT * FROM x1)

253

</pre></blockquote><p>

254

The historical behavior of SQLite is incorrect according to the SQL:1999

255

standard and it is inconsistent with the behavior of MySQL and

256

PostgreSQL. Version 3.6.0 changes the behavior of the IN and

257

NOT IN operators to conform to the standard and to give the same

258

results as other SQL database engines.

259

</p>

260

261

<strong>Key Point:</strong> The change to the way NULL values are handled

262

by the IN and NOT IN operators is technically a bug fix, not a design

263

change. However, maintainers should check to ensure that applications

264

do not depend on the older, buggy behavior prior to upgrading to

265

version 3.6.0.

266

</blockquote>

267

<h3>1.4 Changes To Column Naming Rules</h3><p>

268

The column names reported by join subqueries have been modified slightly

269

in order to work more like other database engines. Consider the following

270

query:

271

</p>

272

273

CREATE TABLE t1(a);

274

CREATE TABLE t2(x);

275

SELECT * FROM (SELECT t1.a FROM t1 JOIN t2 ORDER BY t2.x LIMIT 1) ORDER BY 1;

276

</pre></blockquote><p>

277

In version 3.5.9 the query above would return a single column named "t1.a".

278

In version 3.6.0 the column name is just "a".

279

</p>

280

<p>

281

SQLite has never made any promises about the names of columns in the

282

result set of <a href="lang_select.html">SELECT</a> statement unless the column contains an AS clause.

283

So this change to column name is technically not an incompatibility.

284

SQLite is merely changing from one undefined behavior to another.

285

Nevertheless, many applications depend on the unspecified column naming

286

behavior of SQLite and so this change is discussed under the

287

incompatible changes subheading.

288

</p>

289

<h3>1.5 Changes To Compile-Time Options</h3><p>

290

Compile-time options to SQLite are controlled by C-preprocessor

291

macros. SQLite version 3.6.0 changes the names of some of these

292

macros so that all C-preprocessor macros that are specific to

293

SQLite begin with the "SQLITE_" prefix. This is done to reduce the

294

risk of name collisions with other software modules.

295

</p>

296

297

<strong>Key Point:</strong> Changes to compile-time options have the

298

potential to affect makefiles in projects that do customized builds of

299

SQLite. These changes should have zero impact on application code and for

300

most projects which use a standard, default build of SQLite.

301

</blockquote>

302

<h2>2.0 Fully Backwards-Compatible Enhancements</h2><p>

303

In addition to the incompatible changes listed above, SQLite

304

version 3.6.0 adds the following backwards compatible changes and

305

enhancements:

306

</p>

307

<p>

308

<ol>

309

310

<li value="7"><p>The new <a href="c3ref/config.html">sqlite3_config()</a> interface allows an application

311

to customize the behavior of SQLite at run-time. Customizations possible

312

using <a href="c3ref/config.html">sqlite3_config()</a> include the following:</p>

313

314

<li><p>Specify an alternative mutex implementation using the

315

<a href="c3ref/c_config_getmalloc.html#sqliteconfigmutex">SQLITE_CONFIG_MUTEX</a> verb with the <a href="c3ref/mutex_methods.html">sqlite3_mutex_methods</a> object.</p></li>

316

<li><p>Specify an alternative malloc implementation using the

317

<a href="c3ref/c_config_getmalloc.html#sqliteconfigmalloc">SQLITE_CONFIG_MALLOC</a> verb with the <a href="c3ref/mem_methods.html">sqlite3_mem_methods</a> object.</p></li>

318

<li><p>Partially or fully disable the use of mutexes using

319

<a href="c3ref/c_config_getmalloc.html#sqliteconfigsinglethread">SQLITE_CONFIG_SINGLETHREAD</a>, <a href="c3ref/c_config_getmalloc.html#sqliteconfigmultithread">SQLITE_CONFIG_MULTITHREAD</a> and

320

<a href="c3ref/c_config_getmalloc.html#sqliteconfigserialized">SQLITE_CONFIG_SERIALIZED</a>.</p></li>

321

</ol>

322

</li>

323

324

<li><p>A new flag <a href="c3ref/c_open_autoproxy.html">SQLITE_OPEN_NOMUTEX</a> is made available to the

325

<a href="c3ref/open.html">sqlite3_open_v2()</a> interface.</p></li>

326

327

<li><p>The new <a href="c3ref/status.html">sqlite3_status()</a> interface allows an application to query

328

the performance status of SQLite at runtime.

329

</p></li>

330

331

<li><p>The <a href="c3ref/memory_highwater.html">sqlite3_memory_used()</a> and <a href="c3ref/memory_highwater.html">sqlite3_memory_highwater()</a>

332

interfaces are deprecated. The equivalent functionality is now available

333

through <a href="c3ref/status.html">sqlite3_status()</a>.</p></li>

334

335

<li><p>The <a href="c3ref/initialize.html">sqlite3_initialize()</a> interface can be called to explicitly

336

initialize the SQLite subsystem. The <a href="c3ref/initialize.html">sqlite3_initialize()</a> interface is

337

called automatically when invoking certain interfaces so the use of

338

<a href="c3ref/initialize.html">sqlite3_initialize()</a> is not required, but it is recommended.</p></li>

339

340

<li><p>The <a href="c3ref/initialize.html">sqlite3_shutdown()</a> interface causes SQLite release any

341

system resources (memory allocations, mutexes, open file handles)

342

that it might have been allocated by <a href="c3ref/initialize.html">sqlite3_initialize()</a>.</p></li>

343

344

<li><p>The <a href="c3ref/next_stmt.html">sqlite3_next_stmt()</a> interface allows an application to discover

345

all <a href="c3ref/stmt.html">prepared statements</a> associated with a <a href="c3ref/sqlite3.html">database connection</a>.</p></li>

346

347

<li><p>Added the <a href="pragma.html#pragma_page_count">page_count</a> PRAGMA for returning the size of the underlying

348

database file in pages.</p></li>

349

350

<li><p>Added a new <a href="rtree.html">R*Tree index extension</a>.</p></li>

351

352

</ol>

353

</p>

354

355