An Introduction to SQLite

It seems that SQLite is widely used. Http://www.sqlite.org/whentouse.html

Appropriate uses for SQLite

SQLite is different from most other SQL database engines in that its
Primary design goal is to be simple:

• Simple to administer
• Simple to operate
• Simple to embed in a larger program
• Simple to maintain and customize

Login people like SQLite because it is small and fast. But those
Qualities are just happy accidents.
Users also find that SQLite is very reliable. reliability is
A consequence of Simplicity. With less complication, there is
Less to go wrong. So, yes, SQLite is small, fast, and reliable,
But first and foremost, SQLite strives to be simple.

Simplicity in a database engine can be either a strength or
Weakness, depending on what you are trying to do. In order
Achieve simplicity, SQLite has had to sacrifle ice other characteristics
That some people find useful, such as high concurrency, fine-grained
Access control, a rich set of built-in functions, stored procedures,
Esoteric SQL language features, XML and/or Java extensions,
Scaling-or peta-byte scalability, and so forth. If you need some of these
Features and do not mind the added complexity that they
Bring, then SQLite is probably not the database for you.
SQLite is not intended to be an enterprise database engine. It is
Not designed to compete with Oracle or PostgreSQL.

The basic rule of thumb for when it is appropriate to use SQLite is
This: Use SQLite in situations where simplicity of administration,
Implementation, and maintenance are more important than the countless
Complex features that enterprise database engines provide.
As it turns out, situations where simplicity is the better choice
Are more common than people realize.

Another way to look at SQLite is this: SQLite is not designed
To replace oracle.
It is designed to replace
Fopen ().

Situations where SQLite works well

• Application file format

  SQLite has been used with great success as the on-disk file format
  For desktop applications such as financial analysis tools, CAD
  Packages, record keeping programs, and so forth. The traditional
  File/open operation does an sqlite3_open () and executes
  Begin transaction to get exclusive access to the content. File/save
  Does a commit followed by another begin transaction. The Use
  Of transactions guarantees that updates to the application file are atomic,
  Durable, isolated, and consistent.

  Temporary triggers can be added to the database to record all
  Changes into a (temporary) Undo/redo log table. These changes can then
  Be Played back when the user presses the Undo and redo buttons. Using
  This technique, an unlimited depth Undo/Redo implementation can be written
  In surprisingly little code.

• Embedded devices and applications

  Because an SQLite database requires little or no administration,
  SQLite is a good choice for devices or services that must work
  Unattended and without human support. SQLite is a good fit
  Use in cellphones, PDAs, set-top boxes, and/or appliances. It also
  Works well as an embedded database in downloadable consumer applications.

• Websites

  SQLite usually will work great as the database engine for low
  Medium Traffic websites (which is to say, 99.9% of all websites ).
  The amount of web traffic that SQLite can handle depends, of course,
  On how heavily the website uses its database. Generally
  Speaking, any site that gets fewer than 100 k hits/day shocould work
  Fine with SQLite.
  The 100 k hits/day figure is a conservative estimate, not
  Hard upper bound.
  SQLite has been demonstrated to work with 10 times that amount
  Of traffic.

• ReplacementAd hocDisk Files

  Many programs use
  Fopen (),
  Fread (), and
  Fwrite () to create and
  Manage files of data in home-grown formats. SQLite works
  Particle ly well as
  Replacement for theseAd hocData files.

• Internal or temporary Databases

  For programs that have a lot of data that must be sifted and sorted
  In diverse ways, it is often easier and quicker to load the data
  An in-memory SQLite database and use queries with joins and order
  Clauses to extract the data in the form and order needed rather
  To try to code the same operations manually.
  Using an SQL database internally in this way also gives the program
  Greater flexibility since new columns and indices can be added
  Having to recode every query.

• Command-line dataset analysis tool

  Experienced SQL users can employ
  The command-lineSQLiteProgram to analyze Miscellaneous
  Datasets. Raw data can be imported from CSV files, then that
  Data can be sliced and diced to generate a myriad of summary
  Reports. Possible uses include website log analysis, sports
  Statistics Analysis, compilation of programming metrics, and
  Analysis of experimental results.

  You can also do the same thing with an Enterprise Client/Server
  Database, of course. The advantages to using SQLite in this situation
  Are that SQLite is much easier to set up and the resulting database
  Is a single file that you can store on a floppy disk or flash-memory stick
  Or email to a colleague.

• Stand-in for an enterprise database during demos or testing

  If you are writing a client application for an enterprise database engine,
  It makes sense to use a generic database backend that allows you to connect
  To parse different kinds of SQL database engines. It makes even better
  Sense
  Go ahead and include SQLite in the mix of supported databases and to statically
  Link the SQLite engine in with the client. That way the client program
  Can be used standalone with an SQLite data file for testing or
  Demonstrations.

• Database pedaggy

  Because it is simple to setup and use (installation is trivial: Just
  CopySQLiteOrSqlite.exeExecutable to the target machine
  And run it) SQLite makes a good database engine for use in teaching SQL.
  Students can easily create as your databases as they like and can
  Email databases to the policuctor for comments or grading. For more
  Advanced students who are interested in studying how an RDBMS is
  Implemented, the modular and well-commented and quit ented SQLite code
  Can serve as a good basis. This is not to say that SQLite is an accurate
  Model of how other database engines are implemented, but rather a student who
  Understands how SQLite works can more quickly comprehend the operational
  Principles of other systems.

• Experimental SQL language extensions

  The simple, modular design of SQLite makes it a good platform
  Prototyping new, experimental database language features or ideas.

Situations where another RDBMS may work better

• Client/Server Applications

  If you have your client programs accessing a common database
  Over a network, you shoshould consider using a Client/Server Database
  Engine instead of SQLite. SQLite will work over a network filesystem,
  But because of the latency associated with most network filesystems,
  Performance will not be great. Also, the file locking logic
  Using Network filesystems Implementation contains bugs (on both UNIX
  And windows). If File Locking does not work like it shoshould,
  It might be possible for two or more client programs to modify
  Same part of the same database at the same time, resulting in
  Database failed uption. Because this problem results from bugs in
  The underlying filesystem implementation, there is nothing SQLite
  Can do to prevent it.

  A good rule of thumb is that you shoshould avoid using SQLite
  In situations where the same database will be accessed simultaneously
  From compute computers over a network filesystem.

• High-volume websites

  SQLite will normally work fine as the database backend to a website.
  But if you website is so busy that you are thinking of splitting
  Database Component off onto a separate machine, then you shoshould
  Definitely consider using an enterprise-class Client/Server Database
  Engine instead of SQLite.

• Very large datasets

  When you start a transaction in SQLite (which happens automatically
  Before any write operation that is not within an explicit begin... commit)
  The engine has to allocate a bitmap of dirty pages in the disk file
  Help it manage its rollback journal. SQLite needs 256 bytes of RAM
  Every 1mib of Database (assuming a 1024-byte page size: less memory is
  Used with larger page sizes, of course ).
  For smaller databases, the amount of memory
  Required is not a problem, but when database begins to grow into
  Multi-gigabyte range, the size of the bitmap can get quite large. If
  You need to store and modify more than a few dozen GB of data, you shoshould
  Consider using a different database engine.

• High concurrency

  SQLite uses Reader/writer locks on the entire database file. That means
  If any process is reading from any part of the database, all other
  Processes are prevented from writing any other part of the database.
  Similarly, if any one process is writing to the database,
  All other processes are prevented from reading any other part of
  Database.
  For each situations, this is not a problem. Each application
  Does its database work quickly and moves on, and no lock lasts for more
  Than a few dozen milliseconds. But there are some applications that require
  More concurrency, and those applications may need to seek a different
  Solution.