SQLite3

You will experience future happiness. Embed.

1. SQLite3
You will experience future happiness. Embed.

2. What is SQLite
•Fast, embedded database platform
•Implements all common SQL92 features
•Easy setup, configure and use
•File-based storage, one database per file
•Open source and free!

3. Who uses SQLite?
•Adobe Apollo/AIR - native database
•Mozilla Firefox 3 - bookmarks and history
•Apple Safari 3 - it’s in there
•Apple Mail - Holds all your e-mail
•Google Gears - offline database in JS

4. Development Pros
•Self contained database allows fast
deployment of encapsulated solutions
•Using it requires referencing one .NET
assembly that is ADO.NET DB factory
•Easy unit testing. Just delete the file and
recreate the test

5. Administrative Pros
•No server required so you don’t have to
maintain things in two places
•No software to install, just the web
application
•Lots of quality applications for managing
database files
•Many tools available, accessible remotely

6. Geek Pros
•Supports ACID transactions
atomic, consistent, isolated and durable
•Performance is faster than...
MS SQL, PostgreSQL, XML and MySQL
•Small disk and memory footprint
•Stored procedures, triggers and views
•All the cool kids are using it!

7. Manager Pros
•Easier, smoother and faster deployment
means happier clients and developers
•Zero-cost means better ROI and sales
•Industry-wide support and documentation
•More developer friendly, no need for DBA
•Open standard for interoperability

8. SQLite Cons
•Columns types are not enforced
This may be a pro depending on who you are
•Strange string concatenation characters
•Foreign keys are not enforced
•File-based, relies on the file system for
database level permissions
•Lesser SQL features like FULL OUTER JOIN

9. When should I use it?
•When the web application IS the server
This includes most apps we have built lately
•Supporting a web site with a basic database
•Building a desktop app that needs file types
•When storing non-centralized data
This would have been great for storing
patient medical records for portability

10. When should I avoid it?
•On larger projects where enterprise access
and management is required
•When MS SQL or other is a requirement
•When the database may be accessed by
more than one code base
•When replication or multi-client is needed

11. How do I use it?
•Include the .NET assembly
•Include the Cltr assembly
•Add a Global.cs with a Connection
property and file management methods
•Code my classes with an “Ensure” method
•Write my classes as usual

12. So what’s different?
•Use || instead of + for string concatenation
•Use AUTOINCREMENT instead of IDENTITY
•Use LIMIT and OFFSET instead of TOP
•Use real quotes “” instead of square quotes [ ]
•Use different functions and date/time syntax
•Use sqlite_* not sys* tables

13. How do I get started?
•
SQLite3 - Language, types, functions
http://www.sqlite.org
•
System.Data.SQLite - .NET assembly
http://sqlite.phxsoftware.com
•
SQLabs - Great admin tool for Mac or PC
http://www.sqlabs.net - not free though
•
Other tools - See what I mean? Choices!
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools