~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

<h2>Appropriate Uses For SQLite</h2>

123

124

<p>

125

SQLite is different from most other SQL database engines in that its

126

primary design goal is to be simple:

127

</p>

128

129

<ul>

130

<li>Simple to administer</li>

131

<li>Simple to operate</li>

132

<li>Simple to embed in a larger program</li>

133

<li>Simple to maintain and customize</li>

134

</ul>

135

136

<p>

137

Many people like SQLite because it is small and fast. But those

138

qualities are just happy accidents.

139

Users also find that SQLite is very reliable. Reliability is

140

a consequence of simplicity. With less complication, there is

141

less to go wrong. So, yes, SQLite is small, fast, and reliable,

142

but first and foremost, SQLite strives to be simple.

143

</p>

144

145

<p>

146

Simplicity in a database engine can be either a strength or a

147

weakness, depending on what you are trying to do. In order to

148

achieve simplicity, SQLite has had to sacrifice other characteristics

149

that some people find useful, such as high concurrency, fine-grained

150

access control, a rich set of built-in functions, stored procedures,

151

esoteric SQL language features, XML and/or Java extensions,

152

tera- or peta-byte scalability, and so forth. If you need some of these

153

features and do not mind the added complexity that they

154

bring, then SQLite is probably not the database for you.

155

SQLite is not intended to be an enterprise database engine. It is

156

not designed to compete with Oracle or PostgreSQL.

157

</p>

158

159

<p>

160

The basic rule of thumb for when it is appropriate to use SQLite is

161

this: Use SQLite in situations where simplicity of administration,

162

implementation, and maintenance are more important than the countless

163

complex features that enterprise database engines provide.

164

As it turns out, situations where simplicity is the better choice

165

are more common than many people realize.

166

</p>

167

168

<p>

169

Another way to look at SQLite is this: SQLite is not designed

170

to replace <a href="http://www.oracle.com/database/index.html">Oracle</a>.

171

It is designed to replace <a href="http://man.he.net/man3/fopen">fopen()</a>.

172

</p>

173

174

<h2>Situations Where SQLite Works Well</h2>

175

176

<ul>

177

178

179

180

<li><p><b>Application File Format</b></p>

181

182

<p>

183

SQLite has been used with great success as the on-disk file format

184

for desktop applications such as financial analysis tools, CAD

185

packages, record keeping programs, and so forth. The traditional

186

File/Open operation does an sqlite3_open() and executes a

187

BEGIN TRANSACTION to get exclusive access to the content. File/Save

188

does a COMMIT followed by another BEGIN TRANSACTION. The use

189

of transactions guarantees that updates to the application file are atomic,

190

durable, isolated, and consistent.

191

</p>

192

193

<p>

194

Temporary triggers can be added to the database to record all

195

changes into a (temporary) undo/redo log table. These changes can then

196

be played back when the user presses the Undo and Redo buttons. Using

197

this technique, an unlimited depth undo/redo implementation can be written

198

in surprisingly little code.

199

</p>

200

</li>

201

202

<li><p><b>Embedded devices and applications</b></p>

203

204

<p>Because an SQLite database requires little or no administration,

205

SQLite is a good choice for devices or services that must work

206

unattended and without human support. SQLite is a good fit for

207

use in cellphones, PDAs, set-top boxes, and/or appliances. It also

208

works well as an embedded database in downloadable consumer applications.

209

</p>

210

</li>

211

212

<li><p><b>Websites</b></p>

213

214

<p>SQLite usually will work great as the database engine for low to

215

medium traffic websites (which is to say, 99.9% of all websites).

216

The amount of web traffic that SQLite can handle depends, of course,

217

on how heavily the website uses its database. Generally

218

speaking, any site that gets fewer than 100K hits/day should work

219

fine with SQLite.

220

The 100K hits/day figure is a conservative estimate, not a

221

hard upper bound.

222

SQLite has been demonstrated to work with 10 times that amount

223

of traffic.</p>

224

</li>

225

226

<li><p><b>Replacement for <i>ad hoc</i> disk files</b></p>

227

228

<p>Many programs use

229

<a href="http://man.he.net/man3/fopen">fopen()</a>,

230

<a href="http://man.he.net/man3/fread">fread()</a>, and

231

<a href="http://man.he.net/man3/fwrite">fwrite()</a> to create and

232

manage files of data in home-grown formats. SQLite works

233

particularly well as a

234

replacement for these <i>ad hoc</i> data files.</p>

235

</li>

236

237

<li><p><b>Internal or temporary databases</b></p>

238

239

<p>

240

For programs that have a lot of data that must be sifted and sorted

241

in diverse ways, it is often easier and quicker to load the data into

242

an in-memory SQLite database and use queries with joins and ORDER BY

243

clauses to extract the data in the form and order needed rather than

244

to try to code the same operations manually.

245

Using an SQL database internally in this way also gives the program

246

greater flexibility since new columns and indices can be added without

247

having to recode every query.

248

</p>

249

</li>

250

251

<li><p><b>Command-line dataset analysis tool</b></p>

252

253

<p>

254

Experienced SQL users can employ

255

the command-line <b>sqlite</b> program to analyze miscellaneous

256

datasets. Raw data can be imported from CSV files, then that

257

data can be sliced and diced to generate a myriad of summary

258

reports. Possible uses include website log analysis, sports

259

statistics analysis, compilation of programming metrics, and

260

analysis of experimental results.

261

</p>

262

263

<p>

264

You can also do the same thing with an enterprise client/server

265

database, of course. The advantages to using SQLite in this situation

266

are that SQLite is much easier to set up and the resulting database

267

is a single file that you can store on a floppy disk or flash-memory stick

268

or email to a colleague.

269

</p>

270

</li>

271

272

<li><p><b>Stand-in for an enterprise database during demos or testing</b></p>

273

274

<p>

275

If you are writing a client application for an enterprise database engine,

276

it makes sense to use a generic database backend that allows you to connect

277

to many different kinds of SQL database engines. It makes even better

278

sense to

279

go ahead and include SQLite in the mix of supported databases and to statically

280

link the SQLite engine in with the client. That way the client program

281

can be used standalone with an SQLite data file for testing or for

282

demonstrations.

283

</p>

284

</li>

285

286

<li><p><b>Database Pedagogy</b></p>

287

288

<p>

289

Because it is simple to setup and use (installation is trivial: just

290

copy the <b>sqlite</b> or <b>sqlite.exe</b> executable to the target machine

291

and run it) SQLite makes a good database engine for use in teaching SQL.

292

Students can easily create as many databases as they like and can

293

email databases to the instructor for comments or grading. For more

294

advanced students who are interested in studying how an RDBMS is

295

implemented, the modular and well-commented and documented SQLite code

296

can serve as a good basis. This is not to say that SQLite is an accurate

297

model of how other database engines are implemented, but rather a student who

298

understands how SQLite works can more quickly comprehend the operational

299

principles of other systems.

300

</p>

301

</li>

302

303

<li><p><b>Experimental SQL language extensions</b></p>

304

305

<p>The simple, modular design of SQLite makes it a good platform for

306

prototyping new, experimental database language features or ideas.

307

</p>

308

</li>

309

310

311

</ul>

312

313

<h2>Situations Where Another RDBMS May Work Better</h2>

314

315

<ul>

316

<li><p><b>Client/Server Applications</b><p>

317

318

<p>If you have many client programs accessing a common database

319

over a network, you should consider using a client/server database

320

engine instead of SQLite. SQLite will work over a network filesystem,

321

but because of the latency associated with most network filesystems,

322

performance will not be great. Also, the file locking logic of

323

many network filesystems implementation contains bugs (on both Unix

324

and Windows). If file locking does not work like it should,

325

it might be possible for two or more client programs to modify the

326

same part of the same database at the same time, resulting in

327

database corruption. Because this problem results from bugs in

328

the underlying filesystem implementation, there is nothing SQLite

329

can do to prevent it.</p>

330

331

<p>A good rule of thumb is that you should avoid using SQLite

332

in situations where the same database will be accessed simultaneously

333

from many computers over a network filesystem.</p>

334

</li>

335

336

<li><p><b>High-volume Websites</b></p>

337

338

<p>SQLite will normally work fine as the database backend to a website.

339

But if you website is so busy that you are thinking of splitting the

340

database component off onto a separate machine, then you should

341

definitely consider using an enterprise-class client/server database

342

engine instead of SQLite.</p>

343

</li>

344

345

<li><p><b>Very large datasets</b></p>

346

347

<p>With the default page size of 1024 bytes, an SQLite database is

348

limited in size to 2 terabytes (2<sup><small>41</small></sup> bytes).

349

And even if it could handle larger databases, SQLite stores the entire

350

database in a single disk file and many filesystems limit the maximum

351

size of files to something less than this. So if you are contemplating

352

databases of this magnitude, you would do well to consider using a

353

client/server database engine that spreads its content across multiple

354

disk files, and perhaps across multiple volumes.

355

</p>

356

</li>

357

358

<li><p><b>High Concurrency</b></p>

359

360

<p>

361

SQLite uses reader/writer locks on the entire database file. That means

362

if any process is reading from any part of the database, all other

363

processes are prevented from writing any other part of the database.

364

Similarly, if any one process is writing to the database,

365

all other processes are prevented from reading any other part of the

366

database.

367

For many situations, this is not a problem. Each application

368

does its database work quickly and moves on, and no lock lasts for more

369

than a few dozen milliseconds. But there are some applications that require

370

more concurrency, and those applications may need to seek a different

371

solution.

372

</p>

373

</li>

374

375

</ul>

376