~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

122

123

<style>

124

/* Formatting for the blocks containing the example code */

125

pre a:visited, pre a:link { text-decoration: none ; color: #40534b }

126

pre {

127

background: #F3F3F3;

128

float: right;

129

padding: 1ex 2ex;

130

margin-left: 1em;

131

border: solid black 1px;

132

}

133

h1,h2 { clear: both }

134

</style>

135

136

137

<h1>Using the SQLite Online Backup API</h1>

138

139

<p>

140

Historically, backups (copies) of SQLite databases have been created

141

using the following method:

142

143

<ol>

144

<li> Establish a shared lock on the database file using the SQLite API (i.e.

145

the shell tool).

146

<li> Copy the database file using an external tool (for example the unix 'cp'

147

utility or the DOS 'copy' command).

148

<li> Relinquish the shared lock on the database file obtained in step 1.

149

</ol>

150

151

<p>

152

This procedure works well in many scenarios and is usually very

153

fast. However, this technique has the following shortcomings:

154

155

<ul>

156

<li> Any database clients wishing to write to the database file while a

157

backup is being created must wait until the shared lock is

158

relinquished.

159

160

<li> It cannot be used to copy data to or from in-memory databases.

161

162

<li> If a power failure or operating system failure occurs while copying

163

the database file the backup database may be corrupted following

164

system recovery.

165

</ul>

166

167

<p>

168

The <a href="c3ref/backup_finish.html#sqlite3backupinit">Online Backup API</a> was created to

169

address these concerns. The online backup API allows the contents of

170

one database to be copied into another database, overwriting the

171

original contents of the target database. The copy operation may be

172

done incrementally, in which case the source database does not need

173

to be locked for the duration of the copy, only for the brief periods

174

of time when it is actually being read from. This allows other database

175

users to continue uninterrupted while a backup of an online database is

176

made.

177

178

<p>

179

The online backup API is <a href="c3ref/backup_finish.html#sqlite3backupinit">documented here</a>.

180

The remainder of this page contains two C language examples illustrating

181

common uses of the API and discussions thereof. Reading these examples

182

is no substitute for reading the API documentation!

183

184

<h2>Example 1: Loading and Saving In-Memory Databases</h2>

185

186

<pre>

187

188

<span style="color:blue;font-style:italic">** This function is used to load the contents of a database file on disk </span>

189

<span style="color:blue;font-style:italic">** into the "main" database of open database connection pInMemory, or</span>

190

<span style="color:blue;font-style:italic">** to save the current contents of the database opened by pInMemory into</span>

191

<span style="color:blue;font-style:italic">** a database file on disk. pInMemory is probably an in-memory database, </span>

192

<span style="color:blue;font-style:italic">** but this function will also work fine if it is not.</span>

193

194

<span style="color:blue;font-style:italic">** Parameter zFilename points to a nul-terminated string containing the</span>

195

<span style="color:blue;font-style:italic">** name of the database file on disk to load from or save to. If parameter</span>

196

<span style="color:blue;font-style:italic">** isSave is non-zero, then the contents of the file zFilename are </span>

197

<span style="color:blue;font-style:italic">** overwritten with the contents of the database opened by pInMemory. If</span>

198

<span style="color:blue;font-style:italic">** parameter isSave is zero, then the contents of the database opened by</span>

199

<span style="color:blue;font-style:italic">** pInMemory are replaced by data loaded from the file zFilename.</span>

200

201

<span style="color:blue;font-style:italic">** If the operation is successful, SQLITE_OK is returned. Otherwise, if</span>

202

<span style="color:blue;font-style:italic">** an error occurs, an SQLite error code is returned.</span>

203

204

int loadOrSaveDb(<a href="c3ref/sqlite3.html">sqlite3</a> *pInMemory, const char *zFilename, int isSave){

205

int rc; <span style="color:blue;font-style:italic">/* Function return code */</span>

206

<a href="c3ref/sqlite3.html">sqlite3</a> *pFile; <span style="color:blue;font-style:italic">/* Database connection opened on zFilename */</span>

207

<a href="c3ref/backup.html">sqlite3_backup</a> *pBackup; <span style="color:blue;font-style:italic">/* Backup object used to copy data */</span>

208

<a href="c3ref/sqlite3.html">sqlite3</a> *pTo; <span style="color:blue;font-style:italic">/* Database to copy to (pFile or pInMemory) */</span>

209

<a href="c3ref/sqlite3.html">sqlite3</a> *pFrom; <span style="color:blue;font-style:italic">/* Database to copy from (pFile or pInMemory) */</span>

210

211

<span style="color:blue;font-style:italic">/* Open the database file identified by zFilename. Exit early if this fails</span>

212

<span style="color:blue;font-style:italic">** for any reason. */</span>

213

rc = <a href="c3ref/open.html">sqlite3_open</a>(zFilename, &pFile);

214

if( rc==SQLITE_OK ){

215

216

<span style="color:blue;font-style:italic">/* If this is a 'load' operation (isSave==0), then data is copied</span>

217

<span style="color:blue;font-style:italic">** from the database file just opened to database pInMemory. </span>

218

<span style="color:blue;font-style:italic">** Otherwise, if this is a 'save' operation (isSave==1), then data</span>

219

<span style="color:blue;font-style:italic">** is copied from pInMemory to pFile. Set the variables pFrom and</span>

220

<span style="color:blue;font-style:italic">** pTo accordingly. */</span>

221

pFrom = (isSave ? pInMemory : pFile);

222

pTo = (isSave ? pFile : pInMemory);

223

224

<span style="color:blue;font-style:italic">/* Set up the backup procedure to copy from the "main" database of </span>

225

<span style="color:blue;font-style:italic">** connection pFile to the main database of connection pInMemory.</span>

226

<span style="color:blue;font-style:italic">** If something goes wrong, pBackup will be set to NULL and an error</span>

227

<span style="color:blue;font-style:italic">** code and message left in connection pTo.</span>

228

229

<span style="color:blue;font-style:italic">** If the backup object is successfully created, call backup_step()</span>

230

<span style="color:blue;font-style:italic">** to copy data from pFile to pInMemory. Then call backup_finish()</span>

231

<span style="color:blue;font-style:italic">** to release resources associated with the pBackup object. If an</span>

232

<span style="color:blue;font-style:italic">** error occurred, then an error code and message will be left in</span>

233

<span style="color:blue;font-style:italic">** connection pTo. If no error occurred, then the error code belonging</span>

234

<span style="color:blue;font-style:italic">** to pTo is set to SQLITE_OK.</span>

235

236

pBackup = <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init</a>(pTo, "main", pFrom, "main");

237

if( pBackup ){

238

(void)<a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step</a>(pBackup, -1);

239

(void)<a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish</a>(pBackup);

240

}

241

rc = <a href="c3ref/errcode.html">sqlite3_errcode</a>(pTo);

242

}

243

244

<span style="color:blue;font-style:italic">/* Close the database connection opened on database file zFilename</span>

245

<span style="color:blue;font-style:italic">** and return the result of this function. */</span>

246

(void)<a href="c3ref/close.html">sqlite3_close</a>(pFile);

247

return rc;

248

}

249

</pre>

250

251

252

<p>

253

The C function to the right demonstrates of one of the simplest,

254

and most common, uses of the backup API: loading and saving the contents

255

of an in-memory database to a file on disk. The backup API is used as

256

follows in this example:

257

258

<ol>

259

<li>Function <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init()</a> is called to create an <a href="c3ref/backup.html">sqlite3_backup</a>

260

object to copy data between the two databases (either from a file and

261

into the in-memory database, or vice-versa).

262

<li>Function <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> is called with a parameter of

263

<tt>-1</tt> to copy the entire source database to the destination.

264

<li>Function <a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish()</a> is called to clean up resources

265

allocated by <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init()</a>.

266

</ol>

267

268

<p><b>Error handling</b>

269

270

<p>

271

If an error occurs in any of the three main backup API routines

272

then the <a href="c3ref/c_abort.html">error code</a> and <a href="c3ref/errcode.html">message</a> are attached to

273

the destination <a href="c3ref/sqlite3.html">database connection</a>.

274

Additionally, if

275

<a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> encounters an error, then the <a href="c3ref/c_abort.html">error code</a> is returned

276

by both the <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> call itself, and by the subsequent call

277

to <a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish()</a>. So a call to <a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish()</a>

278

does not overwrite an <a href="c3ref/c_abort.html">error code</a> stored in the destination

279

<a href="c3ref/sqlite3.html">database connection</a> by <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a>. This feature

280

is used in the example code to reduce amount of error handling required.

281

The return values of the <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> and <a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish()</a>

282

calls are ignored and the error code indicating the success or failure of

283

the copy operation collected from the destination <a href="c3ref/sqlite3.html">database connection</a>

284

afterward.

285

286

<p><b>Possible Enhancements</b>

287

288

<p>

289

The implementation of this function could be enhanced in at least two ways:

290

291

<ol>

292

<li> Failing to obtain the lock on database file zFilename (an <a href="c3ref/c_abort.html">SQLITE_BUSY</a>

293

error) could be handled, and

294

<li> Cases where the page-sizes of database pInMemory and zFilename are

295

different could be handled better.

296

</ol>

297

298

<p>

299

Since database zFilename is a file on disk, then it may be accessed

300

externally by another process. This means that when the call to

301

sqlite3_backup_step() attempts to read from or write data to it, it may

302

fail to obtain the required file lock. If this happens, this implementation

303

will fail, returning SQLITE_BUSY immediately. The solution would be to

304

305

timeout with <a href="c3ref/sqlite3.html">database connection</a> pFile

306

using <a href="c3ref/busy_handler.html">sqlite3_busy_handler()</a> or <a href="c3ref/busy_timeout.html">sqlite3_busy_timeout()</a>

307

as soon as it is opened. If it fails to obtain a required lock immediately,

308

<a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> uses any registered busy-handler callback or timeout

309

in the same way as <a href="c3ref/step.html">sqlite3_step()</a> or <a href="c3ref/exec.html">sqlite3_exec()</a> does.

310

311

<p>

312

Usually, it does not matter if the page-sizes of the source database and the

313

destination database are different before the contents of the destination

314

are overwritten. The page-size of the destination database is simply changed

315

as part of the backup operation. The exception is if the destination database

316

happens to be an in-memory database. In this case, if the page sizes

317

are not the same at the start of the backup operation, then the operation

318

fails with an SQLITE_READONLY error. Unfortunately, this could occur when

319

loading a database image from a file into an in-memory database using

320

function loadOrSaveDb().

321

322

<p>

323

However, if in-memory database pInMemory has just been opened (and is

324

therefore completely empty) before being passed to function loadOrSaveDb(),

325

then it is still possible to change its page size using an SQLite "PRAGMA

326

page_size" command. Function loadOrSaveDb() could detect this case, and

327

attempt to set the page-size of the in-memory database to the page-size

328

of database zFilename before invoking the online backup API functions.

329

330

<h2>Example 2: Online Backup of a Running Database</h2>

331

332

<pre>

333

334

<span style="color:blue;font-style:italic">** Perform an online backup of database pDb to the database file named</span>

335

<span style="color:blue;font-style:italic">** by zFilename. This function copies 5 database pages from pDb to</span>

336

<span style="color:blue;font-style:italic">** zFilename, then unlocks pDb and sleeps for 250 ms, then repeats the</span>

337

<span style="color:blue;font-style:italic">** process until the entire database is backed up.</span>

338

339

<span style="color:blue;font-style:italic">** The third argument passed to this function must be a pointer to a progress</span>

340

<span style="color:blue;font-style:italic">** function. After each set of 5 pages is backed up, the progress function</span>

341

<span style="color:blue;font-style:italic">** is invoked with two integer parameters: the number of pages left to</span>

342

<span style="color:blue;font-style:italic">** copy, and the total number of pages in the source file. This information</span>

343

<span style="color:blue;font-style:italic">** may be used, for example, to update a GUI progress bar.</span>

344

345

<span style="color:blue;font-style:italic">** While this function is running, another thread may use the database pDb, or</span>

346

<span style="color:blue;font-style:italic">** another process may access the underlying database file via a separate </span>

347

<span style="color:blue;font-style:italic">** connection.</span>

348

349

<span style="color:blue;font-style:italic">** If the backup process is successfully completed, SQLITE_OK is returned.</span>

350

<span style="color:blue;font-style:italic">** Otherwise, if an error occurs, an SQLite error code is returned.</span>

351

352

int backupDb(

353

<a href="c3ref/sqlite3.html">sqlite3</a> *pDb, <span style="color:blue;font-style:italic">/* Database to back up */</span>

354

const char *zFilename, <span style="color:blue;font-style:italic">/* Name of file to back up to */</span>

355

void(*xProgress)(int, int) <span style="color:blue;font-style:italic">/* Progress function to invoke */ </span>

356

){

357

int rc; <span style="color:blue;font-style:italic">/* Function return code */</span>

358

<a href="c3ref/sqlite3.html">sqlite3</a> *pFile; <span style="color:blue;font-style:italic">/* Database connection opened on zFilename */</span>

359

<a href="c3ref/backup.html">sqlite3_backup</a> *pBackup; <span style="color:blue;font-style:italic">/* Backup handle used to copy data */</span>

360

361

<span style="color:blue;font-style:italic">/* Open the database file identified by zFilename. */</span>

362

rc = <a href="c3ref/open.html">sqlite3_open</a>(zFilename, &pFile);

363

if( rc==SQLITE_OK ){

364

365

<span style="color:blue;font-style:italic">/* Open the <a href="c3ref/backup.html">sqlite3_backup</a> object used to accomplish the transfer */</span>

366

pBackup = <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init</a>(pFile, "main", pDb, "main");

367

if( pBackup ){

368

369

<span style="color:blue;font-style:italic">/* Each iteration of this loop copies 5 database pages from database</span>

370

<span style="color:blue;font-style:italic">** pDb to the backup database. If the return value of backup_step()</span>

371

<span style="color:blue;font-style:italic">** indicates that there are still further pages to copy, sleep for</span>

372

<span style="color:blue;font-style:italic">** 250 ms before repeating. */</span>

373

do {

374

rc = <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step</a>(pBackup, 5);

375

xProgress(

376

<a href="c3ref/backup_finish.html">sqlite3_backup_remaining</a>(pBackup),

377

<a href="c3ref/backup_finish.html#sqlite3backuppagecount">sqlite3_backup_pagecount</a>(pBackup)

378

);

379

if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){

380

<a href="c3ref/sleep.html">sqlite3_sleep</a>(250);

381

}

382

} while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED );

383

384

<span style="color:blue;font-style:italic">/* Release resources allocated by backup_init(). */</span>

385

(void)<a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish</a>(pBackup);

386

}

387

rc = <a href="c3ref/errcode.html">sqlite3_errcode</a>(pFile);

388

}

389

390

<span style="color:blue;font-style:italic">/* Close the database connection opened on database file zFilename</span>

391

<span style="color:blue;font-style:italic">** and return the result of this function. */</span>

392

(void)<a href="c3ref/close.html">sqlite3_close</a>(pFile);

393

return rc;

394

}

395

</pre>

396

397

398

<p>

399

The function presented in the previous example copies the entire source

400

database in one call to <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a>. This requires holding a

401

read-lock on the source database file for the duration of the operation,

402

preventing any other database user from writing to the database. It also

403

holds the mutex associated with database pInMemory throughout the copy,

404

preventing any other thread from using it. The C function in this section,

405

designed to be called by a background thread or process for creating a

406

backup of an online database, avoids these problems using the following

407

approach:

408

409

<ol>

410

<li>Function <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init()</a> is called to create an <a href="c3ref/backup.html">sqlite3_backup</a>

411

object to copy data from database pDb to the backup database file

412

identified by zFilename.

413

<li>Function <a href="c3ref/backup_finish.html#sqlite3backupstep">sqlite3_backup_step()</a> is called with a parameter of 5 to

414

copy 5 pages of database pDb to the backup database (file zFilename).

415

<li>If there are still more pages to copy from database pDb, then the

416

function sleeps for 250 milliseconds (using the <a href="c3ref/sleep.html">sqlite3_sleep()</a>

417

utility) and then returns to step 2.

418

<li>Function <a href="c3ref/backup_finish.html#sqlite3backupfinish">sqlite3_backup_finish()</a> is called to clean up resources

419

allocated by <a href="c3ref/backup_finish.html#sqlite3backupinit">sqlite3_backup_init()</a>.

420

</ol>

421

422

<p><b>File and Database Connection Locking</b>

423

424

<p>

425

During the 250 ms sleep in step 3 above, no read-lock is held on the database

426

file and the mutex associated with pDb is not held. This allows other threads

427

to use <a href="c3ref/sqlite3.html">database connection</a> pDb and other connections to write to the

428

underlying database file.

429

430

<p>

431

If another thread or process writes to the source database while this

432

function is sleeping, then SQLite detects this and usually restarts the

433

backup process when sqlite3_backup_step() is next called. There is one

434

exception to this rule: If the source database is not an in-memory database,

435

and the write is performed from within the same process as the backup

436

operation and uses the same database handle (pDb), then the destination

437

database (the one opened using connection pFile) is automatically updated

438

along with the source. The backup process may then be continued after the

439

xSleep() call returns as if nothing had happened.

440

441

<p>

442

Whether or not the backup process is restarted as a result of writes to

443

the source database mid-backup, the user can be sure that when the backup

444

operation is completed the backup database contains a consistent and

445

up-to-date snapshot of the original. However:

446

447

<ul>

448

<li> Writes to an in-memory source database, or writes to a file-based

449

source database by an external process or thread using a

450

database connection other than pDb are significantly more expensive

451

than writes made to a file-based source database using pDb (as the

452

entire backup operation must be restarted in the former two cases).

453

454

<li> If the backup process is restarted frequently enough it may never

455

run to completion and the backupDb() function may never return.

456

</ul>

457

458

<p><b>backup_remaining() and backup_pagecount()</b>

459

460

<p>

461

The backupDb() function uses the sqlite3_backup_remaining() and

462

sqlite3_backup_pagecount() functions to report its progress via the

463

user-supplied xProgress() callback. Function sqlite3_backup_remaining()

464

returns the number of pages left to copy and sqlite3_backup_pagecount()

465

returns the total number of pages in the source database (in this case

466

the database opened by pDb). So the percentage completion of the process

467

may be calculated as:

468

469

470

Completion = 100% * (pagecount() - remaining()) / pagecount()

471

472

<p>

473

The sqlite3_backup_remaining() and sqlite3_backup_pagecount() APIs report

474

values stored by the previous call to sqlite3_backup_step(), they do not

475

actually inspect the source database file. This means that if the source

476

database is written to by another thread or process after the call to

477

sqlite3_backup_step() returns but before the values returned by

478

sqlite3_backup_remaining() and sqlite3_backup_pagecount() are used, the

479

values may be technically incorrect. This is not usually a problem.

480

481

482

483