SQLite: The Hammer For Software Problems

With SQLite as a hammer, many software problems are easy to nail.

Use SQLite to solve software problems quickly for embedded systems, mobile applications, web applications and desktop applications. Use it to design smart data structures, and write complex data manipulation with just a few lines of SQL. Abandon the complex error-prone code caused by primitive collections, manual memory management, string manipulation, and managing file access robustly. SQLite gives you the full power of SQL in an embedded database. It can be dynamically linked (.so or .dll) or statically linked (.o or .a) into your application and it is about 300 kbytes in size. It is as easy to program as POSIX primitives fopen/fclose/fread/fwrite.

Hopefully I can help you to get you going with SQLite with this lens as a "SQLite tutorial" or a "SQLite primer". This lens also points you to interesting uses for SQLite. Many folks come here looking for a "sqllite tutorial", but according to www.sqlite.org, the proper spelling is SQLite. Welcome aboard anyway and enjoy this lens.  


Last Updated: 28 October, 2010

Related Lenses

....get to where you want to go...

This lens is the first place you want to come for extra information on SQLite. However if you are looking for specific sub-topics on SQLite, you may want to try the following lenses as well.

SQLite Programming

Use this lens to learn how to write software using SQLite. It starts with a description of the the basic APIs, presents a few programming examples, and then points you to other programming examples in your preferred programming language.

SQLite Tutorial

Use this lens to learn about SQLite. It will show you how to get started with SQLite using the command line tool, the SQL language through the command line tool, and some elementary programming.

SQLite Database Design

Use this lens to learn how to design the data and database of your application using SQLite. You will learn some basic data design, and then use the SQLite command line tool to describe your application data and then to verify the ability to add, change, and delete data.

How To Jump-Start Learning SQLite - SQLite is free, but expertise on getting the most out of it isn't.

You could spend your time scouring the internet for free tutorials, free code, etc. But you would spend a lot of time doing it. You could save a fair bit of time by coming back to this page to read and explore material. This is much more effective. But to really jump-start your learning of SQLite, the books shown below will ground you in the fundamentals of data design, and then help you get jump started on many possible platforms. If your time is worth money, the books will more than pay for themselves.

A wise old computer scientist once said, "Make your data structures smart and your code dumb". That is so true! When you have smart data structures, you just don't have to write as much code to build your application. SQLite makes it really easy to design smart data structures, but If you haven't done a lot of data design, it's hard. Get "Beginning Database Design: From Novice to Professional" if you need to jump-start your data design.

A new book has just come out called "Using SQLite". It will help people who want to get the most out of the tool, whether designing databases or programming against its API in one of many languages.

If you are going to use SQLite, get The Definitive Guide to SQLite . It will quickly teach you the intricacies and advanced details about SQLite that could otherwise take you a long time to learn. If you would like to "try before you buy", the publisher Apress has made the first 18 pages available at the Apress web site.

The Definitive Guide to SQLite
The Definitive Guide to SQLite

Read a snippet at Google Book Search

 

How To Build iPhone Apps using Javascript, CSS, HTML, and SQL - Now web designers can develop iPhone apps too!

Addison Wesley has published a new book that shows how to develop iPhone apps using standard web technologies such as Javascript, CSS, HTML and SQL. This is a huge leap forward because, all of a sudden, you don't have to be a server side developer or embedded development geek to build an iPhone application. Anybody with front-end web development skills such as HTML, CSS, and Javascript can use this book to build iPhone apps.

This book has a chapter on how to use SQLite with Javascript on an iPhone. This article at InformIT some of the chapter on how to use SQLite on the iPhone.

Go and get your copy.

My first experience with SQLite was very positive. Here's why...

I was working for a small startup company building a system for dynamic in-game advertising and we were really pressed for time. We were to write client side software in C/C++ and a server side written in .Net. The client piece had to work off-line, and we needed a way to store the ad scheduling data in the client software.

I had read about SQLite in Dr Dobb's Journal, and I especially liked that it had a really small footprint, that it used SQL for data definition and manipulation, and that it was linked into the application.

A use-case analysis revealed that I had to design for 15-20 input use-cases, each resulting in data manipulation and sometimes some output. I designed the client side with about 10 tables normalized to 4th normal form, and each input use case resulted in some SQL manipulation of the tables.

I tested and debugged SQL structures and data manipulations using the SQLite command line tool. (This tool, called "sqlite3", comes for free with the software distribution). I was able to get the business logic right without constantly going through the edit/compile/link/initialize/test cycle. That sped up development a lot. We still had to wrap all of that with communication, APIs, and threading all written in C/C++, but it went quickly because the business logic was mostly done. Overall, this went much faster than when I had solved similar problems in the embedded systems development using only C/C++ and some primitive collections and file access APIs.

To sum up, SQLite helped me develop faster for 3 main reasons: Firstly, it allowed me to design highly normalized data structures, thus resulting in much lower code volume in the surrounding code. Secondly, it allowed me to efficiently define and debug the data structures and data manipulations. Thirdly, I neither had to write code to store data on disk, nor code to retrieve and parse data from disk.

As a footnote to this story, the designer that took over the code around 2004 said that when he had time, he would swap out the SQLite and use XML. As of mid 2007, it had not yet happened. Of course! Why mess with something that just works?

How To Implement Simple Logging With SQLite

SQLite is a great tool for building a logger because it gives you the ability to easily analyze logs with SQL queries, while providing simple file-based storage that you would get with using a file based logger.

First create a logfile database with your command line tool.

>sqlite3 logs.db

Then go in and create a logs table.

sqlite3> CREATE TABLE logs (id integer primary key autoincrement, logtype text, logname text, logmessage text, logtime datetime);

This table is probably more complicated than you may want, but it has some interesting features:

1. The id is unique, and increments automatically. i.e. You don't have to keep track of it.

2. You can define custom log types for your application. For example, you can have logs such as 'critical', 'major', 'minor', 'trace'.Restricting yourself to these values allows you to filter based on criticality.

3. The logname allows you to name your logs...i.e. You can store multiple virtual log stream in one table. This allows different people or subsystems to own their own logs.

4. The logmessage lets you put in other useful information.

5. The logtime field allows you to take a time stamp.

After this, quit out of the logger;

sqlite3> .exit

How to log? The basic form of the SQL statement is as follows:

insert into logs (id,logtype,logname,logmessage,logtime) values (NULL, 'trace', 'UIEvent', 'mousepress on Form XYZ', datetime('now'));

Points to note:

1. Putting in NULL in the id field gets the database to autoincrement it.

2. datetime('now') is a built-in SQLite function that yields the current time stamp.

You probably want to have an API that takes in the logtype, logname, and logmessage as input parameters, and then calls the SQL API in the language of your choice. You'll also want to have function to initialize the log by opening a connection to logs.db, and holding it in a globally accessible handle or variable. I'll leave it as an exercise to wrap the insert statement in a log API call.

After your application uses your logs, you'll notice that logs.db has grown in size.

You can use the command line tool to analyze the logs in logs.db, or build another program to do the same.

For analysis after the fact, you can ask questions such as:

1. Show all logs in the last day (86400 seconds) => select * from logs where strftime('%s', 'now')-strftime('%s', logtime) < 86400;

2. Show all logs in the last hour (3600 seconds) => select * from logs where strftime('%s', 'now')-strftime(''%s', logtime) < 3600;

3. Show all critical or major logs in the last 15 minutes (900 seconds) => select * from logs where strftime('%s', 'now') - strftime('%s', logtime) < 900 and logtype in ('critical','major');

4. Show all non-trace logs => select * from logs where logtype not in ('trace');

5. Show how many of each type of log in the last 6 hours => select logtype, count(logtype) from logs group by logtype;

This list could go on and on. The point is, if you take the time to use SQLite for your log file, analyzing your logs afterwards is very simple.

How To Use SQLite on the iPhone

A tutorial to kick start your iPhone learning.

There is a great tutorial at icodeblog.com which shows how to build a "To-Do List" application in the iPhone. It covers:

  1. Programming environment setup, including database.
  2. Putting SQL data into UITablewView with images and multiple columns.
  3. Updating the to-do list and keeping the display up to date.
  4. Finishing up the odds and sods of the application.

Go through the tutorial to get more familiar with iPhone programming. Once you have that, use this site to do all kinds of interesting and useful data manipulation with SQLite.

How To Use The SQLite Editor for the iPhone - by Pasha Topchiyev

This video gives a great walkthrough on how to to use SQLite Editor v 1.0 by Pascha Topchiyev. The download coordinates are at http://itopchiyev.com/sqliteeditor/.

How To Administer SQLite without a DBA

Compression, Backup & Restore, Roles, Security...do it yourself...

I had a question about a few administration issues for SQLite. Since there is no DBA, some things have to be handled by the application or the application deployment folks. Here goes...

Backup: Since an SQLite database is stored in one file with a name of your choice, just copy the file to your backup set. If you don't trust the binary file format (and a lot of folks don't trust it for any database), you could just dump the database to another file as a set of sql commands. For example, if your database is in myData.db, then you just do:

> sqlite3 myData.db ".dump" > backup.txt

"backup.txt" contains the SQL statements of the database.

Restore: Since an SQLite database is stored in one file with a name of your choice, just copy the file from your backup medium to the place where your database file is expected. Alternatively, if you stored it as SQL text, as above, and you want to restore the data from backup.txt above to a new file (newData.db) just run:

>sqlite3 newData.db ".read backup.txt"

Compression: - If you want to compress the file, then use your favourite compression utility on the database file such as gzip or winzip. If your database file is getting large, and unwieldy, it could be because over time you have deleted stuff. SQLite leaves these records allocated. To clean up allocated but unused records, run the "vacuum" command through the database API, or run the following on the database file from the command line:

> sqlite3 myData.db "vacuum"

If you want a compressed read-only database, it'll cost you, but it isn't a lot of money if space is that critical. See http://www.hwaci.com/sw/sqlite/prosupport.html for details.

Encryption: There are free and paid versions of SQLite that encrypt the data files. If you have budget, get it from Hwaci software. I did it in a previous gig and it worked very well. You basically get one extra file with all of the encryption algorithms and APIs, and you just compile it and link it into your project. Your database connection APIs change because you need a password, but that's about it. Hwaci's version is very easy to use. It cuts performance by about half.

Authorization & Access Control Since the only access to the database file is through the file system or programming API, you get the first level of access restriction through your file system priveleges for each user. (It naturally follows that there is no such notion as "GRANT" and "REVOKE"). Once you are "in" to the database, you have full access. Any limited access to specific tables and fields is through the application that embeds SQLite. This works because SQLite is a serverless database. i.e. Your application is the server, and that is where you do the whole ACL, roles & rights, authorization, authentication, et cetera.

Integrity Check: To run integrity check, run the "pragma integrity_check" command from the API or the command line. For example, do:

> sqlite3 test.db "pragma integrity_check"

It returns OK, or the corrupt rows.

Integrity Repair: Good question...I'm not sure.

To be fair, this is exactly how you would handle configuration files and log files if you weren't using SQLite.

SQLite: Presentation at Google: Dr Hipp - Author of SQLite

Firefox SQLite Manager

If you like a GUI to manage your databases, some folks have developed a Firefox addon to manage SQLite databases which can be found at addons.mozilla.org.

It is easy to install and easy to use. It implements a lot of the SQLite database management functionality that you get from the command line. This image shows a screen shot of a test database. As you can see, it uses the Firefox infrastructure to structure the tool. You can use it to manage databases on your file system.

Try it. You may really enjoy using it.

How To Get SQLite Into Your Software Project

You've discovered SQLite. It works...really well. Now you have to convince your project leader to get it in. Here are a list of common "roadblocks", and how to overcome them.

Copyright: SQLite is public domain. All copyright claims have been renounced by the authors. This means that you can do whatever you want, including building an application with it, distributing the application, and not telling or paying anybody. No liabilities. I was involved in a software due-diligence, and their lawyers had no problem with the fact that we used SQLite.

License: Because it is public domain. There is no license. If you are from a place that does not recognize public domain, such as Deutschland, then you can purchase a license from Hwaci Software for $2000. No issues here.

Runtime Royalties: None. No impact to your project.

Support: You can optionally buy varying degrees of support from Hwaci software starting at about $2000 per year. There are also newsgroups and mailing lists. Support is covered if you need it.

Files: Not many. You can also get the amalgamated version from SQLite.org that is made up of one C file for the implementation (about 60k lines of code) and one C header file. This means that you can usually sneak the amalgamated version into a feature code directory, instead of a 3rd party code directory without anybody noticing. This may upset legal eagles, but your actions have not harmed anybody, except possibly you. If you are just going to use SQLite to drive unit tests or system tests, then there is usually no problem bringing it in.

Code Footprint: About 350k for the library, but with the right compiler options, you can strip it down to 250k. Not very big. If you compare it against what you would need for XML parsers & serializers, it is probably not very different. For most applications, this is "nothing". i.e. It will have neglible impact on most projects. SQLite is in Symbian cell phones, so size arguments are hard to justify.

Data Expansion: After some fixed overhead of about 2k, it takes about 1 byte of overhead per byte of data. Not very different from XML.

Languages: Lots, including most of the top 20 in the TIOBE software index. The main application is in ANSI C, but there are wrappers for most of the major languages.

Exposed Data Format: Some people legitimately complain that SQLite databases can be opened and read with the sqlite3 command line utility. However, you can buy the encrypted version of SQLite for about $2000 from Hwaci Software, which means that nobody can open your database with any utility unless they have the password, or the technology to cheaply break 128 bit encryption. (They would be robbing online banks if they could do that).

Overall, if you use SQLite and use good data design, you'll find that you deliver your software faster than expected, and with low code volume. Fast delivery means that you'll be forgiven for bringing in the software.

Open Source Projects That Use SQLite

Learn from what works and what you can see.

One great way to learn SQLite is to study projects that have used SQLite. The following open source projects use SQLite. Download and study the code.

The Horse's Mouth

SQLite web site

Apache Plug-ins

Apache plug-in for SQLite logging

Apache log file analyzer

Apache authentication module

Content Management and Blogging

Mojoportal - a .Net based content management system. SQLite is one of many databases supported.

Blogging and CMS platform.

PHP content management system

Content management system.

LiteBlog Blogging platform

Web site on CD.

Code Generators and Development Frameworks

Ruby-based web development framework. SQLite is one of many databases supported.

Android - Google et al wireless develoment platform.

C++ code generator from database schema

Convert SQL query results to XML without XSLT

Database application development framework

Tool for convertng Palm OS databases to XML. XML <--> SQLite conversion function too.

Rich Internet Application development kit.

Embedded system development tools

TCL/TK executalble generator

Web services development framework for grid computing

Web application server.

C++ MUD Game framework

Automatic Testing Framework in Perl. Not open source, but it is a tutorial on automated testing, with Perl, and using SQLite.

Unusual and Cool

Epidemic simulator

EDI translator, formats include edifact, x12, xml, SAP idoc

Storing & searching linguistically annotated text

Management of genome-scale biological databases.

Genetic pedigree analysis tool.

Python astronomy software library.

Open source project metatadata gatherer.

Home theatre platform (not unlike TiVo)

Open Source Windows clipboard extension

Full-text search engine.

Predictive text entry system

Scientific Word Processor for the blind

Flexible paste bin

Network monitor changes tool.

Media player for Sharp Zaurus

Print quota management for CUPS

Programming Language Interfaces to SQLite

REXX interface to SQLite

Lazarus Free Pascal IDE

SQL connection is represented as a C++ stream.

Gambas - A Basic-like development language

Windows ANSI Forth

Ada database wrapper

JavaScript or ECMAScript wrapper.

Information Management

Bibliographic reference

Mail classification tool

Movie database management

Code review management

Manage running & races

Information and contact manager.

Reference & bibliography database for SGML, XML, and LaTeX/BibTeX docs

CD collection manager

Issue Tracking System

Mail filter

GoGear (Phillips MP3 player) for Linux Boxes - a MP3 searcher and list builder

BibWork - bibliographic and research management application

Media Server to use with Netgear MP101 Digital Music Player

IMDb movie database management tool

RSS/Atom newsreader for Mac OS X.

Bible editor

Email grey listing software

Photo-image archive

Create and manage digital books

Manhattan virtual classroom course management system

Communication

Streamline Media Server

Mumble group voice chat system. murmur.sqlite is the database

Secure peer-to-peer networking

Open source WAP/SMS gateway

Enterprise Secure Shell (SSH)

Math Stuff, Statistics & Data Analysis

Statistical library for working with data sets and statistical models.

Cubulus OLAP engine

Equational Programming Language

Wrapper for J Programming Language - strong on statistics & mathematical analysis.

Logic Proof language

Money Management

Home accounting system

GNU Cash for embedded devices

Personal Finance Manager for KDE

How to Simulate Language Integrated Query (LINQ) using SQLite and C

The benefits of LINQ with ANSI C and without .Net

In November 2007, Microsoft released version 3.5 of the .Net framework. Buried among the features is a technology called Language Integrated Query or LINQ. What is it?

The Wikipedia article on LINQ says that it adds a kind of query language to the .Net framework. This allows the programmer to access data storage in SQL databases, XML formats, and program variables with a query language. The Wikipedia article describes the article quite well, including the main primitives of the query language. It looks and smells like standard SQL. Microsoft has also released some very illustrative sample code that shows how to use LINQ. The following example in C# shows a typical use of LINQ. It takes a list of products, called "products" and asks for all information, ordered by the units in stock.

public void Linq33() {

List products = GetProductList();

var sortedProducts =

from p in products

orderby p.UnitsInStock descending

select p;

ObjectDumper.Write(sortedProducts);

}

This is a very simple thing to do with a SQL database, or even an XML document. However, the code to sort a list by one of its fields is considerably more complex. It is also more error-prone.

Why is this a better way? Several reasons.

1. Using LINQ focuses designers on specifying exactly which data they want, and not on how to acquire that data. The query engine figures out how to manipulate the data to get the desired result, and then executes the query plan.

2. Using LINQ reduces the code volume, probably by a factor of 10 per query. The cheapest line of code to debug is the one you don't have to write.

3. LINQ lets you use the single language on any data storage, not just SQL databases or XML documents.

This is a great technology...unless you happen to work on:

* A software platform that is not Microsoft .Net 3.5.

* A platform with limited resources (can't afford a CLR).

* A code base written with an "old" language such as C, C++, or even Java. These platforms make up a large number of users and enterprises. (For example the TIOBE Programming Communicty Index suggests that about 22% of programmers still use C/C++).

This is where SQLite "comes to the rescue". If you define your internal data (including global variables) in an "in-memory" SQLite database, you can get the same effect as having LINQ. The following code examples implement the C# example from above using C and SQLite.

(The code can be found at SQLite Programming).

There you go. You can get the benefits of Language Integrated Query without using .Net 3.5. So even if you are trapped working in a legacy ANSI C or C++ software platform, SQLite can help you get the benefits of Language Integrated Query without switching platforms.

How To Choose A Database

It's really not that hard

You are building an application, possibly even a web application. How do you choose a database?

The answer may surprise you. Here is the decision tree if traffic is the consideration:

1. If your traffic is low (less than one request per second), choose SQLite. Otherwise choose one of the others.

2. If your application is read-intensive (lots of SELECT queries) use MySQL or Oracle.

3. If your application is write-intensive (lots of UPDATE, INSERT, or DELETE), choose PostgreSQL or MS SQL Server.

4. If you work for a large enterprise with an existing database, that is the one you'll pick, unless you sneak SQLite in as a "configuration file format".

The fact is that most web applications never exceed one request per second. Even if you are going to build the sexiest Web 2.0 social networking AJAX folksonomy thin-client SaaS application driven by a database, you almost certainly won't hit those traffic rates until you go beta with your release (disregard this if you are from Google, Yahoo, Microsoft, Amazon, EBay, Facebook, MySpace, Ning, or Squidoo). For the rest of you, it will take you a lot more effort to drive traffic to that application than you think. 'Nuff said.

So why SQLite? It is just plain fast and easy to set up and get going. It comes built in to PHP 5 and Ruby on Rails 2.0. Other frameworks offer it via plug-ins. Most popular languages have bindings to SQLite. That means no server to set up, no configuration, and no administration priviledges to worry about. Anybody who has ever gone through that pain with MySQL or SQL Server can tell you more. Furthermore, you don't have to set up a safe database server in your development environment for everybody to share. Net effect? You can get something working really fast. Furthermore, since SQLite's data is all kept in one file, everybody on a project team can easily develop and test their software on their own workstation without needing a "lab" or a separate server for the database. That in itself speeds up development greatly.

If you ever end up with traffic that provably needs another database, SQLite's SQL syntax is generic enough that you can switch to MySQL, PostgreSQL, Oracle, DB2, Sybase, or MS SQL Server with relative ease. Also if you have that much traffic, somebody will cough up the money to change databases. It's still cheaper to spend this money when you have a successful application with traffic than to spend it up front when you don't even know if your application will succeed, or if it will get too much traffic for SQLite.

Performance tests show that SQLite goes as fast as the best of them. It also supports multithreaded access as of version 3.5. This means that it will work with multi-threaded application servers or web servers.

Overall your best bet is to pick SQLite and GO!

How To Use SQLite for Program Configuration

A quick example in C to show how SQLite can help you configure a program.

This example is trite, and canned, has no header files, weak code structure, but it gets the point across.

You have a program composed of subsystems A,B, and C, each of which does something when told to by the main program. Each program has different levels of tracing. Some trace messages are present at all levels of tracing (level 1). Other trace messages are at some different level. Yet other trace messages are present at a different level.

Each of these subsystems has a method X_setEnabling() which sets whether or not the subsystem is activated. It also has the method X_set_traceLevel() to set the trace level.

When the system initializes, the main routine (in configExampleMain.c) show how to configure the behaviour of these subsystems at run-time.Based on this configuration, the system outputs different levels of tracing per subsystem. Changing the configuration data using the SQLite command line allows different levels of tracing without changing and rebuilding the code.

The code and instructions to build and run the program have moved to SQLite Programming.

Tracing and activation are key infrastructure features that can enable efficient software development. SQLite makes it easy.

How To Write Data Driven Code Generator Using SQLite (Part 1)

Use SQLite data to drive code generation

Often it is painful to get code to "just work". Say that you just want to define some subsystems with some functions and just get it to work so that you can build it up from a working implementation without doing the "donkey work" of making sure that the C is properly formatted, etc. SQLite data can be used to drive such a code generator.

The example that follows uses such a generator. The data assumes that there are authors, include files, subsystems, functions and parameters. Zero to many parameters can belong to a function, which returns a void or an int. Many functions can belong to a subsystem, which resides in one '.c' file. Function names must be globally unique. A main program then calls each of the functions once to show that they run. I don't use include files for my generated subsystems, but I just use "extern" symbol references. (I know, I know...bad code architecture). Also, the database is not super well normalized...i.e. There is redundant information in the "subsystems" and "functions" table. The subsystems table is not even needed if you just select distinct "sname"s from the functions table. However, this example works. The 2 files are test.rb and test.db (which is a SQLite database). The first step generates the C source code and makefile. The second step compiles and links it. The third step runs it.

Just run:

>ruby test.rb

>make

>./foo42TEST

If you want to insert more subsystems, just add them to the subsystems table. If you want to add functions to the subsystems, add them to the functions table, making sure that the "type" field is either "int" or "void". If your functions have parameters, add them to the parameters table, taking care that only "int" or "char *" parameters are used. All of these additions to test.db are done using the sqlite3 command line tool. See the SQLite data dump at the bottom of this module for sample commands.

/* SQLITE3 DATA */

sqlite3 test.db .dump

BEGIN TRANSACTION;

CREATE TABLE includes (includeFile string);

INSERT INTO "includes" VALUES('');

INSERT INTO "includes" VALUES('');

CREATE TABLE authors (name string, action string);

INSERT INTO "authors" VALUES('John Smith','Initial Creator');

INSERT INTO "authors" VALUES('Ramachandran Balasubrahmaniam','Fixed memory leak, bug 3424');

INSERT INTO "authors" VALUES('Li Tzu','Tuned print statement, bug 4093');

CREATE TABLE parameters (fname string, ptype string, pname string);

INSERT INTO "parameters" VALUES('doThisNow','int','inHowManyTimes');

INSERT INTO "parameters" VALUES('doThisNow','char *','inTag');

INSERT INTO "parameters" VALUES('doThisLater','char *','myName');

INSERT INTO "parameters" VALUES('itInitialize','char *','inObjectName');

INSERT INTO "parameters" VALUES('itInitialize','int','inTraceLevel');

INSERT INTO "parameters" VALUES('itInitialize','int','inReplicas');

INSERT INTO "parameters" VALUES('_2Now','int','inP1');

INSERT INTO "parameters" VALUES('_2Now','char *','inP2');

INSERT INTO "parameters" VALUES('_2Later','int','inP1');

CREATE TABLE subsystems (sname string);

INSERT INTO "subsystems" VALUES('test');

INSERT INTO "subsystems" VALUES('secondTest');

INSERT INTO "subsystems" VALUES('thirdTest');

CREATE TABLE functions (sname string, type string, fname string);

INSERT INTO "functions" VALUES('test','int','doThisNow');

INSERT INTO "functions" VALUES('test','int','doThisLater');

INSERT INTO "functions" VALUES('test','void','justDoIt');

INSERT INTO "functions" VALUES('test','int','itInitialize');

INSERT INTO "functions" VALUES('secondTest','void','_2Now');

INSERT INTO "functions" VALUES('secondTest','int','_2Later');

COMMIT;

How To Write A Date Driven Code Generator (Part 2)

...the code for the application...

# Copyright 2008 Dhananjay Godse

# License: Apache 2.0

# File test.rb

require 'rubygems'

require 'sqlite3'

@@dbh=SQLite3::Database.new( "test.db" )

def emitAuthors (outputFile, db, subsystemName)

outputFile.print "/* Authors */\n"

outputFile.print "/*\n"

db.execute( "select * from authors" ) do |a|

outputFile.print " ", a[0], " - ", a[1], "\n"

end

outputFile.print "*/\n"

end

def emitHeaders (outputFile, db,subsystemName)

outputFile.print "/*************************************/\n"

outputFile.print "/* Copyright 2008 Dhananjay Godse */\n"

outputFile.print "/* License: Apache 2.0 */\n"

outputFile.print "/* */\n"

outputFile.print "/* File: #{subsystemName}.c */\n"

emitAuthors(outputFile,db,subsystemName)

outputFile.print "/*************************************/\n"

end

def emitIncludes (outputFile, db,subsystemName)

outputFile.print "/* Include files */\n"

db.execute( "select * from includes" ) do |i|

outputFile.print "#include ",i[0],"\n"

end

outputFile.print "\n"

end

def emitFunctionPrintfs(outputFile,db,functionName,subsystemName)

outputFile.print("/*functionprintfs */\n")

# print first part of printf

outputFile.print "\tprintf(\"",functionName, " - "

firstParm=0

parmFlag=0

db.execute("select ptype, pname from parameters as p, functions as f where p.fname=f.fname and f.fname= :fname", "fname"=>functionName) do |ptype, pname|

parmFlag=1

if (firstParm==0) then firstParm=1 else outputFile.print ", " end

outputFile.print pname, " = "

if (ptype=="int") then outputFile.print "%d " end

if (ptype=="char *") then outputFile.print "%s " end

end

if (parmFlag==1) then outputFile.print " \\n\"," else outputFile.print " \\n\"" end

# print second part of printf

firstParm=0

db.execute("select ptype, pname from parameters as p, functions as f where p.fname=f.fname and f.fname= :fname", "fname"=>functionName) do |ptype, pname|

if (firstParm==0) then firstParm=1 else outputFile.print ", " end

outputFile.print pname

end

outputFile.print ");\n"

end

# emitFunctions emits the functions for a given subsystem

def emitFunctions(outputFile, db,subsystemName)

outputFile.print "/* Functions */\n\n"

db.execute( "select type, fname from functions where sname=\"#{subsystemName}\"" ) do |f|

outputFile.print f[0]," ",f[1], "("

firstParm=0

db.execute("select ptype, pname from parameters as p, functions as f where p.fname=f.fname and f.fname= :fname and f.sname= :sname","fname"=>f[1], "sname"=>subsystemName ) do |ptype, pname|

if (firstParm==0) then firstParm=1 else outputFile.print ", " end

outputFile.print ptype, " ", pname, " "

end

outputFile.print ")\t\{\n"

emitFunctionPrintfs(outputFile,db,f[1],subsystemName)

outputFile.print " \n\treturn "

if f[0]=="int" then outputFile.print "0 " end

if f[0]=="void" then outputFile.print " " end

outputFile.print ";\n\}\n\n"

end

end

# emitSubsystem takes in a name and emits the .c file

def emitSubsystem (subsystemName)

subfile = File.new(subsystemName +".c", "w")

emitHeaders(subfile, @@dbh,subsystemName)

emitIncludes(subfile, @@dbh, subsystemName)

emitFunctions(subfile, @@dbh, subsystemName)

subfile.close

end

# emitMainExterns emits the external function signatures

def emitMainExterns(mainfile, db)

mainfile.print "/* Main externs */\n"

db.execute( "select type, fname from functions" ) do |f|

mainfile.print "extern ", f[0]," ",f[1], "("

firstParm=0

db.execute("select ptype, pname from parameters as p, functions as f where p.fname=f.fname and f.fname= :fname","fname"=>f[1] ) do |ptype, pname|

if (firstParm==0) then firstParm=1 else mainfile.print ", " end

mainfile.print ptype, " ", pname, " "

end

mainfile.print ");\n"

end

end

# emitMainFunctions finds and calls all of the functions

def emitMainFunctions(mainfile, db)

mainfile.print "/* Main function Calls */\n"

mainfile.print "int main() { \n"

db.execute( "select type, fname from functions" ) do |f|

if (f[0]=="void") then mainfile.print "#{f[1]} " end

if (f[0]=="int") then mainfile.print "int local_#{f[1]} = #{f[1]}" end

mainfile.print "("

firstParm=0

db.execute("select ptype, pname from parameters as p, functions as f where p.fname=f.fname and f.fname= :fname","fname"=>f[1] ) do |ptype, pname|

if (firstParm==0) then firstParm=1 else mainfile.print ", " end

if (ptype=="int") then mainfile.print "666" end

if (ptype=="char *") then mainfile.print "\"TestValueFor_#{f[1]} #{pname}\"" end

end

mainfile.print ");\n"

end

mainfile.print "\n}"

mainfile.print "/* End main function calls*/\n"

end

# emitMain emits main.c which calls the functions generated.

def emitMain(db)

mainfile = File.new("main.c", "w")

emitHeaders(mainfile, db,"main")

emitMainExterns(mainfile, db)

emitIncludes(mainfile, db, "main")

emitMainFunctions(mainfile, db )

mainfile.close

end

# emitMakefiles emits the makefile with which the generated code is built.

def emitMakefile(db)

mfile = File.new("Makefile", "w")

mfile.print "#This is the makefile\n\n"

mfile.print "all:\n"

linkLine = "\tgcc "

db.execute( "select sname from subsystems" ) do |s|

mfile.print "\tgcc -c ", s[0], ".c\n"

linkLine += s[0]+".o "

end

mfile.print "\tgcc -c main.c \n"

mfile.print linkLine + " main.o -o foo42TEST\n"

mfile.print "clean:\n"

mfile.print "\trm *.o *.c Makefile\n"

mfile.close

end

# emitAllSubsystems finds the subsystems to emit and emits them

def emitAllSubSystems(db)

db.execute( "select sname from subsystems" ) do |s|

emitSubsystem(s[0]);

end

end

emitAllSubSystems(@@dbh)

emitMain(@@dbh)

emitMakefile(@@dbh)

#end test.rb

How To Remove Trailing Spaces in SQLite

using COLLATE RTRIM

With SQLite 3.5.6, there is a new feature called "COLLATE RTRIM". What is it? In dealing with real-world data entry and data imported into SQLite from spreadsheets, we often find that two fields are the same, but one has trailing spaces, often to pad the data entry to make it look nice in a text editor. So for example, you may see "Jay" and "Jay " and "Jay " as the entries in a table, but they were meant to be the same thing. Dealing with this was a pain, so the SQLite designers decided to implement a feature where when you create a table with a string field, you can add the qualifier "collate rtrim" at the end. What this does is that any comparisons done on that field are done after trailing spaces are trimmed. This makes it easier to execute proper queries on data that represents the same concept but is captured with differing numbers of trailing spaces.

If you have SQLite 3.5.6 installed, try out the following sequence of commands that I have recorded.

[jgodse@localhost ~]$ sqlite3

SQLite version 3.5.6

Enter ".help" for instructions

sqlite> BEGIN TRANSACTION;

sqlite> CREATE TABLE NameForms (rname string collate rtrim, runame string unique collate rtrim, name string);

sqlite> INSERT INTO "NameForms" VALUES('Jay','Jay ','Jay');

sqlite> INSERT INTO "NameForms" VALUES('Ray','Ray ','Ray');

sqlite> INSERT INTO "NameForms" VALUES('May','May ','Ray');

sqlite> COMMIT;

sqlite> .dump

BEGIN TRANSACTION;

CREATE TABLE NameForms (rname string collate rtrim, runame string unique collate rtrim, name string);

INSERT INTO "NameForms" VALUES('Jay','Jay ','Jay');

INSERT INTO "NameForms" VALUES('Ray','Ray ','Ray');

INSERT INTO "NameForms" VALUES('May','May ','Ray');

COMMIT;

sqlite> insert into NameForms values (' Hay','Hay', 'Hay ');

sqlite> insert into NameForms values ('Kay','Jay', 'Ray');

SQL error: column runame is not unique

sqlite> insert into NameForms values ('Kay',' Jay', 'Ray');

sqlite> .dump

BEGIN TRANSACTION;

CREATE TABLE NameForms (rname string collate rtrim, runame string unique collate rtrim, name string);

INSERT INTO "NameForms" VALUES('Jay','Jay ','Jay');

INSERT INTO "NameForms" VALUES('Ray','Ray ','Ray');

INSERT INTO "NameForms" VALUES('May','May ','Ray');

INSERT INTO "NameForms" VALUES(' Hay','Hay','Hay ');

INSERT INTO "NameForms" VALUES('Kay',' Jay','Ray');

COMMIT;

sqlite> select * from NameForms where rname=name;

Jay|Jay |Jay

Ray|Ray |Ray

sqlite> select * from NameForms where rname=runame;

Jay|Jay |Jay

Ray|Ray |Ray

May|May |Ray

sqlite>

Notice that leading spaces are not trimmed, only trailing spaces. Notice also that the collate rtrim does not take precedence over a unique runame.

This is a small but pleasant addition to the product.

SQLite Application: Google Gears - Offline web access enabled by SQLite...

Google needed to make web applications work offline. Since most web applications are database driven, they needed a database to work offline, and on the client computer. SQLite provided that solution. This video describes Google Gears.

How To Code SQL Conditional Inserts Using SQLite - ...you don't need tons of "if" statements just to insert already existing values.

Often in data development, you gather the same information many times, but you only want to save it once. For example, you may gather the email address of a subscriber that posts a comment to a blog, but this person posts often.

The pseudocode often looks like this:

if emailExists (@emailAddress) then

don't insert anything into the database

else

insert something into the database

In SQLite, you don't have to worry about it. There is an "ignore" command you can use to get rid of those "if" statements. Fire up your sqlite3 command line and try the following:

[jgodse@localhost ~]$ sqlite3

SQLite version 3.5.6

Enter ".help" for instructions

sqlite> create table names (name string unique);

sqlite> insert into names values ('Jay');

sqlite> insert into names values ('Kay');

sqlite> insert into names values ('Ray');

sqlite> insert into names values ('May');

sqlite> insert into names values ('Kay');

SQL error: column name is not unique

sqlite> insert into names values ('Jay');

SQL error: column name is not unique

sqlite> .dump

BEGIN TRANSACTION;

CREATE TABLE names (name string unique);

INSERT INTO "names" VALUES('Jay');

INSERT INTO "names" VALUES('Kay');

INSERT INTO "names" VALUES('Ray');

INSERT INTO "names" VALUES('May');

COMMIT;

sqlite> insert or ignore into names values ('Jay');

sqlite> insert or ignore into names values ('Shay');

sqlite> insert or ignore into names values ('Cray');

sqlite> .dump

BEGIN TRANSACTION;

CREATE TABLE names (name string unique);

INSERT INTO "names" VALUES('Jay');

INSERT INTO "names" VALUES('Kay');

INSERT INTO "names" VALUES('Ray');

INSERT INTO "names" VALUES('May');

INSERT INTO "names" VALUES('Shay');

INSERT INTO "names" VALUES('Cray');

COMMIT;

sqlite> insert or ignore into names values ('Cray');

sqlite> insert or ignore into names values ('Shay');

sqlite> .dump

BEGIN TRANSACTION;

CREATE TABLE names (name string unique);

INSERT INTO "names" VALUES('Jay');

INSERT INTO "names" VALUES('Kay');

INSERT INTO "names" VALUES('Ray');

INSERT INTO "names" VALUES('May');

INSERT INTO "names" VALUES('Shay');

INSERT INTO "names" VALUES('Cray');

COMMIT;

sqlite>

When you use "insert or ignore into..." existing rows are not inserted, while new rows are. That can save writing a lot of code.

SQLite Related Books - ...other products that use SQLite.

These books talk about languages and frameworks (Rails) that use SQLite.

ANDROID A PROGRAMMERS GUIDE
ANDROID A PROGRAMMERS GUIDE

This is a good starting point for Android. Check out a snippet at Google Book Search .

 

SQLite Professional Services

...when you need an expert on hand just to make sure.

Software producers often appreciate the benefit of SQLite in their software products because it is small, fast, and free. However their team may not have expertise in the ins and outs of SQLite.

Since I have come upon two professional service providers for SQLite, I'll write about them.

HWACI SOFTWARE - You can get a professional support contract from Hwaci Software from about $2000 per year. Hwaci is the company of the originator of SQLite and one of the few current core contributors. If you need SQLite tuned for your particular operating system, (lack of) file system, performance traits, footprint constraints, or whatever, Hwaci is the most likely company to be able to provide what you need.

ENCIRQ has decided to provide software support for SQLite. Encirq is an interesting example, because they have a competing product called DeviceSQL that claims to be 2 to 10+ times faster than SQLite. I am, of course, wary of anybody that provides service on a competing product, but Encirq has a lot of expertise in high-performance real-time embedded data management systems. If yours is such a product, and you are currently using SQLite, tapping their expertise will definitely help you get your product to market faster and cheaper. Their webinar and slides can give you more insight.

What are SQLite Virtual Tables?

...get the power of SQL with non SQLite data structures.

One of the problems with SQLite, or any other embedded database, is that you must bring data into the database before you can apply the power of the SQL query language to the data set. This is not a great idea if your data is scattered in other applications accessible only by a programming API, or in device drivers, or in file system metadata, or in large blobs of text. Why? Because you have to store the data in two places. (e.g. once in the file system metadata and once in the SQLite database). Storing the same concept in two places is a violation of data normalization, and leads to all kinds of software bugs that happen when the copy of the data is not kept in sync with the master.

With release 3.3.7 in August 2006, SQLite introduced virtual tables. This feature is powerful. It essentially lets you write a "plug-in" for SQLite to give the SQLite database engine access to your data without copying it into SQLite's internal format. Once the SQLite engine can access the data properly, you can use the SQL query language to search, sort, filter, and synthesize data from that data set, even if it is not in SQLite.

The potential is enormous. This opens up SQLite to be a great unified data server that ties together data stored in a variety of formats.

Recently Mike Owens described virtual tables in great detail. Dr Dobbs Journal is where you can find it. Have a read and explore the possibilities.

SQLite vs SimpleXML

...SQLite is not always the answer...

There was a discussion at Talkphp.com where the question was asked. Here is some of the banter.

Stewart

Alright, on a PHP application/website of mine I will be loading some featured items, which will simply consist of urls, names, ids, and a few small descriptions..

But they will be reloaded constantly, every page, etc.. This isn't the only instance I have this

similar... situation ? , but what would be better here speed and performance wise..

The PHP SQLite class or the SimpleXML class ? I mean I guess it comes down to which one takes up less memory, but which one would operate faster for small bits of information ?

Currently I have an xml file storing all of my featured items and such for my site, then I use the SimpleXML class to retrieve all of this information and draw it onto the page. This is much better than requery-ing my database every time.. but what about SQLite ?

SQLite seems like there is just more to it than some simple XML rendering, but I could be wrong ? It is pretty much just fopen with a different name.

If anything I'm going to implement SQLite for something, some smaller db operations I guess, but I'm not thinking it is the better solution here, but then again that's why I'm asking!

All thoughts and opinions are appreciated!

Thanks !

- Stewart

Jay

A few questions you need to ask:

1) Does each page access exactly the same data from the XML file?

2) Is the number of pages going to stay constant over time, or is it increasing?

3) Is the XML file "small" or "large"?

4) Will the content of the file stay the same during web site operation?

5) Will you write to the file during web site operation?

6) Will you need more such XML files over time?

7) Did you choose XML because you need to share data with another application?

If your answer to most of these questions is "yes", then stay with the XML file. It already works, and that is its biggest value now.

If your answer to most of these questions is "no", then you will end up spending time and money tuning the access, storage, update, performance, and consistency of the data in that file(s). You would be much better off using SQLite in this case.

If you are repeatedly reading from the file, XML or SQLite, your program will spend a lot of time parsing either XML or SQLite queries. If your file grows, the SQL gets cheaper to parse. If your program spends time traversing the file looking for stuff, SQLite probably wins with a big file because it implements a B-Tree under the covers, while you need to do a linear search with the XML every time. This performance stuff is moot if your file is "small".

Stewart

Thanks for the few tips/ideas Jay ;D

I plan on keeping it with XML, they're all of the featured items listed on my home page and throughout the site (which are the same..)

A few of the entries (only 9..) will change fairly often, but not too much.. and my xml file is quite small, no need for db type functions..

SQLite Data Types...Or Lack Thereof

...is weak typing good enough?

SQLite is quite different from the other popular databases because it uses "manifest typing" of its columns. It only has a few basic data types (integer, float, text, blob). It accepts others in table definitions, but they end up as one of these. For this SQLite has been criticized a lot, as have weakly typed (a.k.a. duck typed) languages such as Python, Ruby, Smalltalk, and Lua. Is weak typing the way to go?

Let's ask why we give columns specific data types. Usually you declare a column to be of a certain type because you want to ensure that only legitimate values (legitimate to your application) get into the table. Strong typing seems to be the way to do this. After all, if you restrict data to valid data, your code is more likely to be more robust. There are a couple of challenges here.

1. For example if a column has a field for a person's age, you will want to restrict it from 0 to about 130, (or perhaps 0 to 969 to cover Methuselah). Either way, "integer" is not a way to do it because you still have to have a 0..969 sub-range or subtype to ensure that the age is in a valid range. Furthermore because "age" can come from a tainted source such as human input or input from a network interface, you have to write a routine that validates the input at run-time. i.e. Storing the age of a person as an "integer" or as a "age" sub-range still requires that you write run-time validation routines. So there is no advantage to using a strongly typed SQL variant (e.g. PostgreSQL) versus a weakly typed one (e.g. SQLite). (A tainted data source is one where the source does not have to give a valid input).

2. Suppose that a key column in a table was the "colour", which could initially take on the values of "red", "green" and "blue", and there was an entry for each. Suppose another table referred to that key (foreign key) such that it could not add a colour to one of its fields unless it was in the "colour" table already. So adding "mauve" would not work initially. Then suppose somebody added "mauve" to the "colour" table. Now "mauve" could be added to the other table. The whole point here is that the definition of a valid colour could change during the life of the program. To ensure that a colour is always valid, the field must be checked every single time it is inserted or updated while the program is running. i.e. Strong typing will not help you here; you must have a validation routine is used at run-time.

In either the case of tainted inputs or the case of validation criteria changing at run-time, proper type checking had to be done at run-time no matter whether the programming language was strongly typed or weakly typed. Strong type checking simply does not help because it misses some validity checking . The run-time validation routines are what catch the input errors.

Interestingly, if you want to write robust code, whether using C++/PostgreSQL (strongly typed), Java/Oracle (strongly typed), or Ruby/SQLite (weakly typed or duck typed), you always have to write the run-time validations if you want robust code in the presence of tainted data. Whether you choose SQLite, PostgreSQL, SQL Server, or whatever, most of them have check constraints in their query language. This lets you check the validity of any data at the time it is inserted or updated. So...since you have to write run-time validation routines no matter which database you use, there is no advantage to using a strongly typed database versus a weakly typed database. i.e. SQLite is as good as any of the others in this respect.

If you have to write the run-time validations, why not just go with the strongly typed technologies, where you get some of the validation with "compile time" checking? Consider this: you don't always know exactly which elements belong in the "type", and which ones don't belong. In the example above, you may start with valid colours of "red", "green", and "blue", but if you decide to add "mauve" as a colour during design you will have to change your data schema as well as your code. In such a case, it is advantageous to use SQLite because you can just store that column as a "text" type, and have the validation routines or check constraints ensure that data that is inserted or updated is valid.

What is a SQL Injection Attack

...and how you can avoid SQL injection attacks wth SQLite.

Web and application security folks have identified a class of attack called the "SQL Injection Attack". Here is how it works:

1) Somebody finds out that you are using a SQL database to drive your web site or application.

2) They reasonably assume that inputs to your database are used to compose the SQL queries, updates, and deletions.

3) They guess the name of your table or of its fields.

4) They take an input field such as "Name", and inject a valid value, followed by a properly formatted SQL statement in the same line.

5) The program formats the string, which is comprised of the input-driven query and the input data, resulting in the intended statement and a malicious statement.

The program below shows how to build an example. The vulnerable version and the example script delete modify the data and then delete another table. The injection-proof version does not execute any of the SQL.

How do you make a SQLite program resistant to injection attacks?

1) Choose table names that are not obvious.

2) When you use data input from a user to determine the SQL query, use the sqlite3_bind_XXX() functions to bind the input to prepared statements built with sqlite3_prepare().

3) Don't use sqlite3_exec() for queries built with input data.

4) Don't use sprintf() to build formatted query strings driven by user input.

The example below shows the two ways to be injection proof and injection vulnerable. Build it, try it, observe it, use it.

/* Copyright 2008 Jay Godse

Licensed under the Apache License, Version 2.0 (the "License");

you may not use this file except in compliance with the License.

You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software

distributed under the License is distributed on an "AS IS" BASIS,

WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

See the License for the specific language governing permissions and

limitations under the License. */

/* File name - injattack.c */

/* Purpose - The main file to show how SQLite can be vulnerable to */

/* SQL injection attacks */

/* Assumes injattack.db already exists with a table : */

/* CREATE TABLE names (name string, age string); */

/* insert into names values ('Jay', 33); */

/* insert into names values ('Kay', 44); */

/* insert into names values ('Ray', 55); */

/* CREATE TABLE dummies(dname string, dage string); */

/* insert into dummies select * from names; */

/*

/* Build the injection proof program using: */

/* gcc injattack.c -lsqlite3 -DSQL_INJECTION_PROOF -o inj */

/* To dump the database, run the program, and dump the database do this: */

/* sqlite3 injattack.db ".dump"; ./inj 101 'Jay';sqlite3 injattack.db ".dump" */

/* and notice that the dummies table is fine. */

/* */

/* Now try it with an injection attack: */

/* sqlite3 injattack.db ".dump"; ./inj 73 "Jay'; drop table dummies;"; sqlite3 injattack.db ".dump" */

/* and notice that the dummies table is fine. */

/* */

/* Build the injection vulnerable program using: */

/* gcc injattack.c -lsqlite3 -DSQL_INJECTION_VULNERABLE -o inj */

/* Run the same dump/run/dump script and notice that the dummies table is gone */

#include "stdio.h" /* use angled brackets for the first 4 include files */

#include "stdlib.h"

#include "string.h"

#include "errno.h"

#include "sqlite3.h"

/*#define SQL_INJECTION_PROOF */

/* #define SQL_INJECTION_VULNERABLE */

sqlite3 *db; /* global SQLite database handle */

int main (int argc, char **argv) {

int nrows, ncols, rc, i,j;

char *zErr;

char **startupConfig;

char* sqlinsert="update names set age=? where name= ?";

char* end_ptr;

long lAge;

if (argc!=3) {

printf("Usage: injattExample \n");

return 0;

}

printf ("argv[1]=%s, argv[2]=%s\n", argv[1], argv[2]);

errno=0;

lAge=strtol(argv[1], &end_ptr, 0); /* extract the age */

if ((ERANGE==errno) || (end_ptr==argv[1])) {

printf("An integer for the age please!\n");

return 0;

};

printf ("The age is %d\n", (int)lAge);

printf("Opening the database to update name/age into it\n");

rc = sqlite3_open("./injattack.db", &db);

#ifdef SQL_INJECTION_PROOF

sqlite3_stmt *insertstmt;

printf ("Injection proof\n");

rc = sqlite3_prepare(db, sqlinsert, strlen(sqlinsert), &insertstmt, NULL);

/* This is where you use the variable binding instead of formatted strings */

sqlite3_bind_text(insertstmt, 2, argv[2], strlen(argv[2]), SQLITE_STATIC);

sqlite3_bind_int(insertstmt, 1, atoi(argv[1]));

sqlite3_step(insertstmt);

sqlite3_finalize(insertstmt);

#endif

#ifdef SQL_INJECTION_VULNERABLE

printf("Injection vulnerable\n");

char sqlvulnerable[256];

sprintf (sqlvulnerable, "update names set age=%s where name= '%s'", argv[1], argv[2]);

printf("SQL to execute is: \n\t\t %s\n", sqlvulnerable);

rc = sqlite3_exec(db,sqlvulnerable, NULL,0, &zErr);

#endif

sqlite3_close(db);

return 0;

}

How To Implement SQLite Encryption

....keep your information secret even if you lose the database file.

SQLite has a fairly open data format, and if you use the "standard" free version, anybody with the right version of the sqlite3 command line tool can open the database and probe your data. If you need to keep the data secret, there are a few options available.

Cough up $2000 to Hwaci Software and get the SQLite Encryption Extension. Hwaci is the company of the main author of SQLite, Dr Richard Hipp. I have used this software in a previous gig. It is absolutely fantastic! Hwaci sends the extra software file and an extra command to build that file into your SQLite dll. Building it is a no-brainer. The only part of the API that changes is when you open a database because you have to provide a key. Encryption reduces performance by about half. You may also buy an optional support package starting at about $2000 as well.

There is another provider called SQLCrypt that also has a paid-up and supported encryption product. Pricing starts at $149 for a 1-platform binary license and goes to $4999 for a full-blown source license. i.e. It is priced in the same ballpark as the offering from Hwaci. I would expect performance reductions to be similar because encryption and decryption is computationally intensive.

For those of you who don't have $2000 to spend on this, or would like to study how to do it, there is a web site at http://www.voidbrain.com/sqlitesec.html that provides the source code for free and shows you how to roll your own encryption. This software will help you learn the internals of SQLite and possibly encryption as well.

Podcast: Interview With D. Richard Hipp

In this podcast at Twit.tv, Randal Schwartz and Leo Laporte interview D. Richard Hipp, the principal authors of SQLite.

This podcast is dated in March 2008, and they talk about lots of things including licensing, deployment, customization, usage, adoption, etc. This podcast will better help you understand some of Dr. Hipp's motivations for writing SQLite.

Enjoy.

Use SQLite To Cache Local Data of SaaS Applications

...and reduce server-side bandwidth & processing.

According to the Google AdWords API Blog they have an open source project to store AdWords API data locally on the computer of the advertiser.

This is a good idea, because constantly querying the server for data can quickly get expensive for the service provider and the user can suffer with poor latency. Google implemented Gears to address server processing, bandwidth and perceived latency, and the local storage was done using SQLite.

Now they have provided an example for other projects who may not have a browser-based solution (needed by Gears). The hard part about such applications is keeping the local copy of database in sync with the server-side representation of the data. With this solution in place, others can implement SaaS applications and push a more of the interaction between the application and the data onto the client, thus freeing the server to serve more users.

SQLite is the obvious chioce for such local data storage because it is completely free. This application lets you use other databases that support the Python dbapi2 interface.

This kind of solution will help all kinds of developers implement RIA/Thick Client applications.

SQLite Diff

...a cheaper way to compare SQLite databases.

Have you ever had to compare two SQLite databases? For example a test database with a production database, or multiple versions of a database? First you dump & diff the schemas, then the views, then the triggers, then the data. Then you have to read the diff's. If you are getting paid by the hour, that's OK. However, if you are on a fixed price contract or working unpaid overtime, then it is annoying.

SQLite Diff is a neat little product that was recently released. It basically allows you to drag & drop two SQLite database files onto the program window and it goes and compares the two databases. The output is graphical and happens without chewing up a lot of computing resources.

At $25 a pop, this tool can pay for itself very quickly, and free the programmer to do the analysis without having to shlep diff files first. Check it out!

SQLite: Foreign Keys: genfkey

Finally! SQLite can "do" foreign keys!

For the longest time, SQLite could not "do" foreign keys. It would faithfully parse any foreign key constraints in the data schema, but it would not enforce them. The reasons are many. Many folks these days use Object-Relational-Mapping (ORM) layers such as ActiveRecord to execute the foreign key logic. It is also well-known in SQLite circles that you can implement foreign-key logic by defining database triggers on the relevant tables. This is a tedious and error prone process that is well suited to a code generation tool.

Somebody came to the rescue with a tool called genfkey. This tool will inspect your data schema, and if it is kosher, it will generate the foreign key trigger statements.

Here is roughly how it works (more details at genfkey):

  1. Download the source code and build the tool. (As with most tools from the SQLite folks, it needs a C compiler, and two source files).
  2. Then you write the data schema for your database.
  3. Run genfkey with your database file as the first argument to the command. genfkey will generate error messages or SQL trigger statements.
  4. Run the trigger statements on your database file, and that will implement the foreign-key constraint enforcement in your database. (You can also pipe the output of the previous step directly as STDIN for running sqlite3 on your database file)

With this you can get the benefits of better data integrity without burdening your application with a heavy ORM, or reams of home-grown foreign-key data integrity code.

Xenia: Geo-tagged data application

Jeremy Cothran has put together a great application that gathers geo-tagged data. This application uses SQLite as the database engine. Have a look at the video and the associated project pages.

The application is located at Google Code and the SQLite-specific information is located at http://code.google.com/p/xenia/wiki/XeniaSqliteNotes.

Web2Py Deployment Example

This video gives a great example of how to deploy a Web2Py application using the Google App Engine. Interestingly, the SQLite database is prominently used here as the default database engine.

An example of building an Android / SQLite example from scratch.

This video demonstrates how to build an android application from scratch. android.database.cursor is one of the APIs used, and on the Android platform, Android.database.sqlite.SQLiteCursor is used for the on-board SQLite database.

Custom SQLite Functions....with Ruby, Python, PHP, or Lua

There are other choices besides C for custom SQLite functions

SQLite is a very flexible database because you can write custom functions for the database. This allows you to enhance the functions built into the SQLite SQL language which can then make some of your application SQL logic simpler. Some instructions can be found at sqlite.org. This isn't a bad way to do it, but not everybody wants to muck around with C code to write their custom functions, especially if it is something they are quickly whipping up with a scripting language.

It would be nice if there was a way to write SQLite custom functions in the language of your choice.

Jeremy Hinegardner has put together a Ruby gem called Amalgalite which can be found at rubyforge.org. The basic idea of this project is that you can write the custom functions in Ruby without touching C. The API of Amalgalite provides a Ruby API with which to register Ruby functions with SQLite so that you can use those functions in your SQL queries.

Jeremy also wrote a posting on this topic at his blog. This provides more instruction on how to use this gem.

If Ruby is not your thing, you can also write custom SQLite functions in:

There are now lots of language choices for extending SQLite with custom functions.

SQLiteDBMS - SQLite as a server process.

As SQLite becomes used more and more, some folks are bound to want more than an embedded library. They may want a database server powered by the SQLite engine.

SQLiteDBMS provides the solution. You get the familiar SQLite database engine, but behind a server. There is also a client library to help other applications access the SQLiteDBMS database server.

Palm WebOS and SQLite

...SQLite is in there too...

Palm has just announced their Palm WebOS framework. Well wouldn't you know it...it includes SQLite as its embedded data engine. You can see the full story at Palm Info Center.

This is absolutely fantastic because now the Palm folks can use all of the power of SQL in their embedded applications. This means that they can design data structures that match the complexity of the problem domain, and not have to worry about complicated algorithms to extract data, which can be done instead by a short SQL query.

Newbie question: Distinct

...a new user starting out...

A new user wrote to me:

"Hi

I have a question regarding SQLite. I am new to database programming and hence this question is very basic.

I search a sqlite3 database and extract one set of characters (lets say 100 of them with 14 unique values) from a table . I need to find only the unique values from this set. Any ideas?

Ravi

Good question Ravi. Here is what you do:

C:>sqlite3

sqlite> create table numbers (i integer);

sqlite> insert into numbers values (3);

sqlite> insert into numbers values (2);

sqlite> insert into numbers values (1);

sqlite> insert into numbers values (4);

sqlite> insert into numbers values (4);

sqlite> insert into numbers values (4);

sqlite> insert into numbers values (1);

sqlite> insert into numbers values (2);

sqlite> insert into numbers values (2);

sqlite> insert into numbers values (3);

sqlite> insert into numbers values (3);

sqlite> insert into numbers values (3);

sqlite> insert into numbers values (3);

sqlite> insert into numbers values (4);

sqlite> select * from numbers;

3

2

1

4

4

4

1

2

2

3

3

3

3

4

sqlite> select distinct i from numbers;

1

2

3

4

sqlite>

Use "distinct" to get the different unique values.

SQLite Code Generator

...just in case you like to write a lot of code...

I just stumbled over sqlitegen which is a code generator for SQLite (Android platform only) in the form of an Eclipse plug-in. You write a bunch of object-oriented Android code and it generates the SQLite statements to create the table and generate a bunch of setters and getters.

Not bad. I would personally just opt for a one liner for the create table statement, and a bunch of one-liners for the setters and getters. However, for folks who are more comfortable using Java bean-style coding, this is a good idea.

April 22, 2008: 10000+ Visits - ...and it only took 17 months...

It looks like this lens has just crossed the milestone of 10000 visits. I'm glad that it has been that useful to many people. Although most readers don't send feedback, I can tell from statistics that many folks come here looking for information on how to get started with SQLite, and when they're done, they go to other blogs, software projects, and tutorials with links on this site. i.e. This site is useful.

How To Make SQLite Work Faster

SQLite can be made to run much faster than what you get out of the box.

There are some resources to help make your SQLite engine run faster.

Jim Lyon has put together a great page on optimizing the speed of SQLite at University of Tennessee. It is for an old version of SQLite, but the concepts carry forward to any of the latest 3.6.x releases. He covers everything from specific SQLite engine tuning tricks to SQL tricks to other coding tricks.

The folks at SQLite also have performance tuning tricks at sqlite.org. This mostly covers structuring queries to accommodate the underlying algorithms.

How To Use SQLite For Embedded Systems

Get the full power of SQL in your embedded system.

When using SQLite in embedded systems, you may be faced with resource limits or time limits. You may also have application compatibility issues.

In general, you should always design the smartest data structures for your application using the highest normal forms for your data. This is important because well-normalized data leads to less code because you can implement a lot of algorithms in SQL instead of an imperative language such as C.

Measure the performance of your application to see if it meets requirements. If it does, you are done!

If not, there are many things you can do to tune the performance, space, and interfaces. The folks who wrote SQLite also wrote in a lot of build-time configuration options which enable you to strip out functionality that is not needed. Stripping out functionality generally reduces code space, and/or removes some processing, and/or reduces data storage.

SQLite also has built-in functions that can be turned on to efficiently implement features such as full-text searching and R-Trees for geographic data.

For some embedded systems, the standard POSIX APIs are not followed for file access and multi-threading. Also, some embedded systems might not even have a persistent file system. Either way, SQLite provides APIs and build options to work with operating systems with different file acess APIs and threading APIs.

Many embedded systems have strict memory maps and can't use generic memory heap management. SQLite has memory management APIs to allow you to use custom memory allocators.

If your embedded system can't work as well as you would like with SQLite out of the box then study http://www.sqlite.org/compile.html to help you make SQLite work or work better with your embedded system.

SQLite works on Blackberry from Research In Motion, Windows CE, Google Android, Symbian S60, Embedded Linux, Maemo/Nokia N810, VxWorks, QNX, and Green Hills Integrity among others.

Newsflash: August 7, 2009: SQLite Ported to C#

...whatever...

According to an article at h-online.com SQLite has been ported to C#. The code can be found at Google Code.

This was an interesting and valiant effort, especially since out of 30000 tests for SQLite, only two failed. However, it was also shown that this version is at least 4 times slower than the DLL version which works just fine with C#.

Nice try anyway.

HTML5 Data Storage

This article at Linux Magazine gives a great demo on how to use SQLite for HTML5 data storage. With local SQL storage, we can get a whole new kind of client application.

Google I/O HTML5 and SQLite

This video describes Google's work on HTML 5 and how they use SQLite for the local storage features. At about 10:20 of the video, they start talking about SQLite.

SQLite Foreign Keys

This video shows how to use foreign keys with SQLite.

SQLite 3.7.0 - Released! Writeahead Logging Implemented

Well folks, it looks like the patent risks for implementing write-ahead logging in SQLite have expired! The good folks on the SQLite core team have now implemented write-ahead logging as of SQLite 3.7.0 which was released on July 22, 2010. This is good news for performance for two big reasons:

  1. Write performance has notably improved for SQLite.
  2. Concurrency is improved because multiple readers now cannot block a single writer writer and a single writer will not block multiple readers

The down-side is that SQLite with write-ahead logging enabled must run on an operating system such as Windows and Linux that support shared memory, and applications must not access the SQLite database through a network file system. This is not an issue for most applications, but you have to be aware.

For web applications that use SQLite, it got a whole lot better as well because of the improved concurrency.

SQLite 3.7.3 has been released

A week ago, SQLite 3.7.3 was released. This release included some API improvements and minor fixes and enhancements. The most interesting new feature was custom r-tree queries. Before 3.7.3, you had to make your r-tree queries on a bounding box that was some kind of multi-dimensional rectangle. This new release allows you to define custom functions so that you can make queries on bounding circles, or other kinds of bounding areas defined by a parameterizable geometry. The "circle" feature will allow for more accurate radius-based location queries for location based services.

SQLite and Android

There is a nice new tutorial on using SQLite with Android. It can be found at droidreign.com. It is a pretty comprehensive tutorial, certainly to get things going with SQLite on Android.

Reader Feedback - If you have a useful criticism, suggestion, or praise, I'd appreciate it if you left it here. 20 comments

montyloree 8 years ago

Is there SQLlite for PHP?


JayGodse profile image

JayGodse 8 years ago Author

There is SQLite for PHP. PHP 5 bundles it. PHP 4 has separate drivers for it.


True_Icon_USA 7 years ago

Impressive lens, nice work. Luv sqlite and waiting php/py driver play nice with FTS engine


amy1985 7 years ago

Great lens. Thanks for the links to the guides, I've thinking about using SQLite, but had not been able to find a suitable guide. Beginning Database Design looks great though, I'll look into purchasing it. Thanks again.


Lacy12 6 years ago

SQL Database Repair Software is a professional software used in recovering the SQL Database. With the use of this tool user can get their entire lost data back. The step of recovering process is also very easy, to know about step by step procedure get on http://www.mssqldatabaserepair.org


anonymous 6 years ago

so cool! great and very informative lens! more power! Las Vegas Web Design


aitsavemyfiles 6 years ago

Great Lense Jay,

Nice to see so much useful info on SQL in one place, sure would have been nice to find this much info when I was starting out.


anonymous 5 years ago

Great lens! you have put very nice information in this lens.Impeccably stated.Now our Best Website Designers team connect with your lens.More power!


SaveMySystem 5 years ago

Good lens about SQLite. You make it so informative, and very much simple to understand this complicated thing. SQL makes database activation very simpler. Thanks for providing all the info. related to it on single page.


JayGodse profile image

JayGodse 5 years ago Author

@SaveMySystem: Hi. Thanks for the feedback. I'm glad you found it useful. Please note that I have spread my SQLite information out over multiple lenses. e.g. sqliteprogramming, sqlitedatabasedesign, sqlitetutorial and this one. I also have a YouTube channel at http://www.youtube.com/user/Jaynonymous1 where I keep my SQLite videos.


javr profile image

javr 5 years ago from British Columbia, Canada

Great stuff! I recently found SQLite myself and now I'm a big fan.


JayGodse profile image

JayGodse 5 years ago Author

@javr: Go to http://www.youtube.com/user/Jaynonymous1 and subscribe.


developintelligence 5 years ago

I am little bit confuse about the performance of SQLite. Could you tell me more details about on it?

xhtml training | ruby training


anonymous 5 years ago

Useful guide. Some moments were really new for me!! Thancks for lens!

I'm in software development outsourcing


RichardMillner profile image

RichardMillner 4 years ago

Great site


LeadFinderPro 4 years ago

Hi, gr8 lens, very informative, keep up the good work.


christinsword profile image

christinsword 4 years ago

this is a heck of a lens. lots here that would otherwise cost money to learn in a classroom somewhere...

what is a file servers


medleyindia 4 years ago

This is good fundamental lens of sqllite and describe great each and every point especially Firefox SQLite Manager .

Thin clients


anonymous 4 years ago

Everyone can see that you are very familiar with sql lite. As a visual basic 2010 and c# I know what is sql lite and I am using it a lot. I even created a free blog where I share my knowledge. http://visualbasic-2010.blogspot.com


sherif-abas 3 years ago

nice lens,

if you like everything related to microcontrollers see this blog :

All about Embedded Systems

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working