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?

The SQLite Command Line tool is the best way to learn about SQLite and its SQL syntax. It is easy to download for Windows, Linux, or Macintosh, and is easy to use. It will expose you directly to the SQL commands that you will work with when you are using the database for real. Go to the downloads section of sqlite.org and download the command line program for Windows, Linux, or MacOS X. (For other platforms, you will have to download the source and build it). The command line is a single file program. Just put the program somewhere on your system path, or if you can't do that, then just copy it into the directory in which you are working. That's it.

The command line program can be run in interactive mode or batch mode. To start the program in interactive mode just go:

>sqlite3 learn.db
sqlite> .quit
>

This will create the database file called "learn.db".
Here are some important commands that you'll use often:

.quit - This command gets you out of the command shell. .exit also works for this.

.dump - This command dumps the entire contents of the database, including schemas (complete with tables, views, indices, triggers, and constraints), and data.

.schema - This command dumps the schema of all of the tables. If you have a table called "Names", and you run ".schema Names", it will just dump the schema for that table. This command is great if you don't remember which tables you have or the names of their fields.

.help - This command lists all of the SQLite command-line commands available.

create table table_name (column_name, type_name, ...); This is an important command that lets you create new tables, each with columns.

insert into table_name values (val1, val2...); - This command lets you insert rows into the table. The number and types of values must correspond to what was defined in the table schema.

select * from table_name; - This allows you to delete all rows from a table.

drop table table_name; - This allows you to get rid of a table.

Try it out. Fire up your sqlite3 command line and let's go through a sequence to see what happens:

[jgodse@localhost learn]$ sqlite3 learn.db
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> .schema
sqlite> create table learnTb (a integer, b string);
sqlite> .schema
CREATE TABLE learnTb (a integer, b string);
sqlite> insert into learnTb values (1,"Jay");
sqlite> insert into learnTb values (3,"Kay");
sqlite> insert into learnTb values (5,"May");
sqlite> insert into learnTb values (6,"Ray");
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE learnTb (a integer, b string);
INSERT INTO "learnTb" VALUES(1,'Jay');
INSERT INTO "learnTb" VALUES(3,'Kay');
INSERT INTO "learnTb" VALUES(5,'May');
INSERT INTO "learnTb" VALUES(6,'Ray');
COMMIT;
sqlite> select * from learnTb;
1|Jay
3|Kay
5|May
6|Ray
sqlite> delete from learnTb;
sqlite> select * from learnTb;
sqlite> .schema
CREATE TABLE learnTb (a integer, b string);
sqlite> drop table learnTb;
sqlite> .schema
sqlite> .help
.bail ON|OFF Stop after hitting an error. Default OFF
.databases List names and files of attached databases
.dump ?TABLE? ... Dump the database in an SQL text format
.echo ON|OFF Turn command echo on or off
.exit Exit this program
.explain ON|OFF Turn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF Turn display of headers on or off
.help Show this message
.import FILE TABLE Import data from FILE into TABLE
.indices TABLE Show names of all indices on TABLE
.load FILE ?ENTRY? Load an extension library
.mode MODE ?TABLE? Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator string
tabs Tab-separated values
tcl TCL list elements
.nullvalue STRING Print STRING in place of NULL values
.output FILENAME Send output to FILENAME
.output stdout Send output to the screen
.prompt MAIN CONTINUE Replace the standard prompts
.quit Exit this program
.read FILENAME Execute SQL in FILENAME
.schema ?TABLE? Show the CREATE statements
.separator STRING Change separator used by output mode and .import
.show Show the current values for various settings
.tables ?PATTERN? List names of tables matching a LIKE pattern
.timeout MS Try opening locked tables for MS milliseconds
.timer ON|OFF Turn the CPU timer measurement on or off
.width NUM NUM ... Set column widths for "column" mode
sqlite> .quit
[jgodse@localhost learn]$

This session demonstrates the basics of using the SQLite command line program. Once you master these basic commands, it will become easier to learn more and more SQL via the command line. Just remember that SQL commands must always with a ";", or the command line will not execute it.

If a command runs on your command line, it will run in your program, so this tool is also a great debugging tool for spplications that use SQLite.

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.

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.

| View | Upload your own

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.

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

Programming Language Interfaces to SQLite
Python
ANSI C
Perl
Ruby
Java
JDBC driver for SQLite with Java
ODBC driver for SQLite with Visual Basic (VB6)
Lua
C# or Visual Basic .Net
PHP
REXX
Lazarus Free Pascal IDE
C++ IOStream.
Windows ANSI Forth
Ada
JavaScript or ECMAScript wrapper.
The BlitzBasic Programming Language

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.

//static global database handle
static sqlite3* db; //database with product list
//somewhere in the initialization code you did the following:
db=sqlite3_open(":memory:", &db); //creates an in-memory database

// The SQLite equivalent function of Linq33
void SQLite_Linq33() {
List products = GetProductList();
int nrows, ncols, rc, i,j;
char *zErr;
char *sortedProducts[];
char* sql="select * from products as p order by p.UnitsInStock desc;"

rc = sqlite3_get_table(db, sql, &sortedProducts, &nrows, &ncols, &zErr);
for (i=0; lessThan(i,nrows);i++) {
for(j=0; lessThan(j,ncols); j++) {
printf("%s |", sortedProducts[(i+1)*ncols + j]
}
printf ("\n");
}
sqlite3_free_table(sortedProducts);
}
//note that lessThan(a,b) returns TRUE if a is less than b.
//I needed this function because you can't use the actual
//less than sign in Squidoo without it getting rejected,
// possibly as a HTML injection attack.

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.

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!

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.

The code is as follows:
/* a.c - There are two more files b.c and c.c that look like a.c but a_ is replaced with b_ or c_. They are not
here in the Squidoo Lens, but you can construct them by
using a.c as a reference */

extern void trace(char* inText, int inTraceLevel, int inModuleTrace);
static int a_traceLevel; /* The trace level of A */
static int a_enabled; /* The enabling of A */

void a_setTraceLevel(int inLevel) { a_traceLevel=inLevel;}

void a_setEnabling(int inEnabled) { a_enabled=inEnabled;} /* 0 to disable, 1 to enable*/

void a_doIt() {
int i,j,k;
if (a_enabled!=0) {
for ( i=0; i<2; i++) {
trace("a outer", 1, a_traceLevel);
for ( j=0; j<2; j++) {
trace("\ta middle", 2, a_traceLevel);
for ( k=0; k<2; k++) trace("\t\ta inner", 3, a_traceLevel);
}
}
} else printf ("a_doIt disabled\n");
}
/* end of a.c */

/* trace.c */
#include
void trace(char* inText, int inTraceLevel, int inModuleTrace) {
if (inTraceLevel<=inModuleTrace) printf ("trace %s\n", inText);
}
/* end of trace.c */

/* configExampleMain.c */
#include
#include "/home/jgodse/squidoo/configExample/sqlite3.h"

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

extern void a_setTraceLevel(int inLevel);
extern void a_setEnabling(int inEnabled);

extern void a_doIt();

int main () {
printf ("configExampleMain.c\n");

int nrows, ncols, rc, i,j;
char *zErr;
char **startupConfig;
char* sql="select * from subsystems;";

rc = sqlite3_open("./configExample.db", &db); /* open the config database */

rc = sqlite3_get_table(db, "select * from subsystems;", &startupConfig, &nrows, &ncols, &zErr); /* get the config data*/
if (rc==1) printf("zErr=%s\n", zErr);

/* For each row, pull out the name (offset 0), enabled (1), and traceLevel(2) off the stack. */
for (i=0; i* Run the main functions of a,b,c. That will show the tracing and enabling */
printf ("Main: a_doIt\n");a_doIt();
printf ("Main: b_doIt\n");b_doIt();
printf ("Main: c_doIt\n");c_doIt();

return 0;
}

/* end of configExampleMain.c */

#Makefile - don't forget the tabs!
all:
gcc -c a.c
gcc -c b.c
gcc -c c.c
gcc -c trace.c
gcc -c configExampleMain.c
gcc a.o b.o c.o trace.o configExampleMain.o -lsqlite3 -o runTest
# end of Makefile

First you build the code (this example is on Linux), by running "make" on the command line.
Then populate the database with the following from the command line:
> sqlite3 configExample.db "CREATE TABLE subsystems (name string, enabled int, traceLevel int);"
> sqlite3 configExample.db "INSERT INTO subsystems VALUES('a',1,2);"
> sqlite3 configExample.db "INSERT INTO subsystems VALUES('b',1,3);"
> sqlite3 configExample.db "INSERT INTO subsystems VALUES('c',1,1);"

Note that the file configExample.db is the database file holding the configuration data. It is accessible from the SQLite command line program "sqlite3".

Then you fire up the program
>./runTest

For each of the scenarios below, change the data and see how the logs appear through the application, but running the application
>./runTest.
See which trace messages come out, and see which subsystems are enabled.

If you want to disable all systems, do
sqlite3 configExampledb "update subsystems set enabled=0;"

To enable system a, do
sqlite3 configExampledb "update subsystems set enabled=1 where name='a'; "

To also turn tracing level 3 in c do:
sqlite3 configExample.db "update subsystems set enabled=1, traceLevel=3 where name='a';"

What does this all mean?

1. You can activate tracing in your application with some simple canned SQL commands that are scriptable from the command line.
2. You can enable and disable software modules from running during a debug session by setting the "enabled" property to 0.

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

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.

# 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

/* 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;

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.

Say you have to create strings in your application based on values in a database. SQLite has a simple but powerful string concatenation utility which can be used to generate data-driven strings. You can generate such strings simply by using the concatenation operator "||". For example in the sequence below with the sqlite3 command utility,

sqlite> select "a" || 2 || "b" ;
a2b
sqlite>

you'll notice that you can concatenate multiple elements of varying types. SQLite handles it for you. This is fairly easy to do in Ruby or Perl, but in C you have to worry about allocating and de-allocating memory buffers to hold strings, and to convert from char to int (atoi) or vice versa (itoa). SQLite provides a simple way to build the strings and all of the memory allocation and type conversions are handled by the SQLite library.

The example below shows how to build a list of function names based on a list of things and a list of common operations. Concatenating integers (rowid) and literal strings are thrown into the mix for completeness. Run the commands and study the results as it relates to the query.

sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE things (thingName string);
INSERT INTO "things" VALUES('Button');
INSERT INTO "things" VALUES('Window');
INSERT INTO "things" VALUES('Pane');
INSERT INTO "things" VALUES('Label');
CREATE TABLE operations(opName string);
INSERT INTO "operations" VALUES('Create');
INSERT INTO "operations" VALUES('Read');
INSERT INTO "operations" VALUES('Update');
INSERT INTO "operations" VALUES('Delete');
INSERT INTO "operations" VALUES('Ioctl');
COMMIT;
sqlite> select thingName || "_" || opName || "___" || things.rowid || "__" || operations.rowid from things, operations;
Button_Create___1__1
Button_Read___1__2
Button_Update___1__3
Button_Delete___1__4
Button_Ioctl___1__5
Window_Create___2__1
Window_Read___2__2
Window_Update___2__3
Window_Delete___2__4
Window_Ioctl___2__5
Pane_Create___3__1
Pane_Read___3__2
Pane_Update___3__3
Pane_Delete___3__4
Pane_Ioctl___3__5
Label_Create___4__1
Label_Read___4__2
Label_Update___4__3
Label_Delete___4__4
Label_Ioctl___4__5
sqlite>

If you need data-driven string construction, SQLite provides a convenient native utility, and it is especially useful for C programs that use SQLite.

Many networked devices must be monitored on a network. The protocol of choice is the Simple Network Management Protocol (SNMP). SNMP mandates the use of standard data structures to monitor any type of networked device. SQLite can be used to store the data for a SNMP Agent implementation. Why? First of all, initialization is very easy. Secondly, querying the data is very easy.

Here is a simple example:

We havea 4-port DSL home router, with 4 10-100 ethernet ports, and one DSL port. There are no removable modules. Provide the data for a SNMP agent to "walk" the Entity MIB.

The entity MIB tells what is actually there. This is a pretty trivial example because this router has exactly one module.

According to RFC 2737 ,the Entity MIB data structure looks like the structure below. (My comments are to the right of each variable).

EntPhysicalEntry ::= SEQUENCE {
entPhysicalIndex Index of table, starting at 1
entPhysicalDescr Manufacturer's name, product code
entPhysicalVendorType Use NULL here
entPhysicalContainedIn index of entity contained in. Use 0 for the
entPhysicalClass ports=10, chassis=3
entPhysicalParentRelPos port number (1..4) for ethernet, 1 for DSL, 0 for chassis
entPhysicalName "home router" for the chassis, "DSL uplink" for the DSL uplink, and "port XX" for the ethernet port.
entPhysicalHardwareRev "" for the ports, "hrx213" for the chassis.
entPhysicalFirmwareRev "" for ports, "hrx213fw" for the chassis.
entPhysicalSoftwareRev "" for the ports, "hrx213sw-build101" for the chassis
entPhysicalSerialNum "" for the ports, "MFGXX12748398" for the chassis
entPhysicalMfgName "XX Networks" for all.
entPhysicalModelName "" for ports "DSL Home Router 3x17" for chassis
entPhysicalAlias "" for all
entPhysicalAssetID ""
entPhysicalIsFRU true=1 for chassis, false=2 for ports.
}

There will be one row for the router, one entry for the DSL port, and 4 entries for the ethernet ports. Read the RFC 2737 for details.

Here is what the table will look like on its side.

entPhysicalIndex 1 2 3 4 5 6
entPhysicalDescr "XX Networks DSL Home Router 3x17" "" "" "" "" ""
entPhysicalVendorType "" "" "" "" ""
entPhysicalContainedIn 0 1 1 1 1 1
entPhysicalClass 3 10 10 10 10 10
entPhysicalParentRelPos 0 1 1 2 3 4
entPhysicalName "home router" "DSL uplink" "ethernet port 1" "ethernet port 2" "ethernet port 3" "ethernet port 4"

entPhysicalHardwareRev "hrx213" "" "" "" "" ""
entPhysicalFirmwareRev "hrx213fw" "" "" "" "" ""
entPhysicalSoftwareRev "hrx213sw-build101" "" "" "" "" ""
entPhysicalSerialNum "MFGXX12748398" "" "" "" "" ""
entPhysicalMfgName "XX Networks" "" "" "" "" ""
entPhysicalModelName "DSL Home Router 3x17" "" "" "" "" ""
entPhysicalAlias "" "" "" "" "" ""
entPhysicalAssetID "" "" "" "" "" ""
entPhysicalIsFRU 1 2 2 2 2 2

To implement the table in SQLite, first create the table:
CREATE TABLE entPhysicalTable (entPhysicalIndex integer, entPhysicalDescr string, entPhysicalVendorType string, entPhysicalContainedIn integer, entPhysicalClass integer, entPhysicalParentRelPos integer, entPhysicalName string, entPhysicalHardwareRev string, entPhysicalFirmwareRev string, entPhysicalSoftwareRev string, entPhysicalSerialNum string, entPhysicalMfgName string, entPhysicalModelName string, entPhysicalAlias string, entPhysicalAssetID string, entPhysicalIsFRU integer);

The statements to initialize the tables is as follows:
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (1,0,"home router");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (2,1,"DSL uplink");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (3,1,"ethernet port 1");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (4,2,"ethernet port 2");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (5,3,"ethernet port 3");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (6,4,"ethernet port 4");

update entPhysicalTable set entPhysicalDescr='', entPhysicalVendorType="", entPhysicalContainedIn=1, entPhysicalClass=10, entPhysicalHardwareRev="", entPhysicalFirmWareRev="", entPhysicalSoftwareRev="", entPhysicalSerialNum="", entPhysicalMfgName="", entPhysicalModelName="", entPhysicalAlias="", entPhysicalAssetID="" ,entPhysicalIsFRU=2;
update entPhysicalTable set entPhysicalDescr="XX Networks DSL Home Router 3x17", entPhysicalModelName="DSL Home Router 3x17", entPhysicalIsFRU=1 where entPhysicalIndex=1;

These can be done once when the load is built, or at initialization time.

There will be a software routine that reads the hardware, firmware, and software revisions at initialization time from well-known APIs.
Once done, do the following at system initialization time right after reading the values:
update entPhysicalTable set entPhysicalHardwareRev='hrx213', entPhysicalFirmwareRev='hrx213fw', entPhysicalSoftwareRev='hrx213sw-build101' where entPhysicalIndex=1;

The actual values will be built into the update query string in the language of your choice.

If hard disk storage is not available, an in-memory SQLite database can be used instead.

The SNMP manager will issue SNMP-GET requests to the SNMP agent. The following query satisfies the request for the index @X:

select * from entPhysicalTable where entPhysicalIndex = @X;

If the query yields no results, then the MIB objects do not exist.

The SNMP manager will issue SNMP-GETNEXT requests to the SNMP agent. The following query satisfies the request for the index @X:

select * from entPhyscialTable where entPhysicalIndex >= @X order by entPhysicalIndex limit 1;

If the query yields no results, then the table is empty or the table has been walked completely.

This example shows that you can use a very simple SQLite table and simple SQLite queries to implement the data storage for the SNMP Entity MIB.

P.S.
There is a great wiki at http://net-snmp.sourceforge.net/wiki/index.php/Tutorials which shows how to hook up NetSNMP to a c++ extension.

The previous example showed that it is possible to use SQLite for the SNMP data storage of a simple single-module device. It gets a little more complicated for a modular device. Why? The entPhysicalTable assumes that chassis, modules, ports, etc have a uniform data structure and naming scheme. That is simply not the case. In any system, the quantity that identifies one kind of entity (e.g. port) is probably different than the quantity that identifies a different kind of entity. Also, different kinds of entities have different attributes. One particular tool from SNMP Research comes with the entPhysicalTable data structures already coded and tested. The problem with this example is that because the schema of the system is different from the schema of the storage, you will probably end up with the same concepts (e.g cards & ports) stored in two places (Entity MIB tables and provisioning tables). Duplicating data like this is rarely ever a good thing because over time, it leads to bugs. This Squidoo Lens module shows a way out.

Take the following example:

1) Ethernet switch chassis, with a built-in main controller, and three plug-in slots, and a 1000-base T ethernet interface...northbound
2) Optional cards can be plugged into any slot in any combination. One card type is a 2-port PoE card. The is a 4-port Ethernet card.

A number of issues come up:

1) Slots and modules are uniquely identified by slot number (1, 2, or 3).
2) Ports could be identified by a combination of slot number and port number, or just by the fact that it is a northbound port.
3) Depending on which kind of cards are plugged in, the configuration could have anywhere from 3 to 13 ports. Allocating entPhysicalIndex could be troublesome, because there will either be an inconsistent numbering scheme for a given slot/port combination, or the allocation of entPhysicalIndex could be sparse.

The solution to this problem is to realize that entPhysicalTable is only a view of data. You can and should store data in tables that are keyed by the appropriate quantities.
Also, to make this work, we need to ensure that entPhysicalIndex is consistently allocated. A convenient way to do this is to pre-allocate them up front. For example:
1) entPhysicalIndex=1 goes for the chassis.
2) 2 goes for the 1000-base-T port
3) 3, 4, and 5 go for the slots.
4) 6, 7, and 8 go for the modules.
5) Since we know that a module can have up to 4 ports, indices 9-12 go to the possible ports in slot 1. 13-16 go to the ports in slot 2. 17-20 go to the ports in slot 3.
6) We know that modules 6,7,and 8 are contained in slots 3.4,and 5 respectively (entPhysicalContainedIn). We know that the slots 3,4,and 5 as well as the port at 2 are contained in the chassis (1), that the chassis is contained in 0.
7) We know that the slots 3,4,and 5 are positioned relatively in the module as 1,2,and 3 respectively (entPhysicalParentRelPos). We also know that the port number of a port within a module is the entPhysicalParentRelPos as well. Any other contained entity (including the modules) have 1 as the entPhysicalParentRelPos.
8) We know that only modules and the chassis are field-replaceable units (entPhysicalIsFRU=1), and ports and slots/containers are not field-replaceable.

We will only focus on the following fields of the entPhysicalTable, as they are the only ones relevant to using entPhysicalTable as a view on physical entity data.

EntPhysicalEntry ::= SEQUENCE {
entPhysicalIndex Index of table, starting at 1
entPhysicalContainedIn index of entity contained in. Use 0 for the chassis
entPhysicalClass ports=10, chassis=3, container=5, module=9
entPhysicalParentRelPos port number for ethernet, 1 for 1000-Base-T, 0 for chassis, slot number for slots, and 1 for modules
entPhysicalName "FlexiRouter" for the chassis,
entPhysicalHardwareRev
entPhysicalIsFRU true=1 for chassis, false=2 for ports.
}

In this example, we need separate tables for ports, slots and modules, and input default data for the maximum configuration. For modules it is:

CREATE TABLE Modules (slotNumber integer, hardwareRev string, moduleType string, isPresent integer, entPhysicalIndex integer);
INSERT INTO "Modules" VALUES(1,'','',0,6);
INSERT INTO "Modules" VALUES(2,'','',0,7);
INSERT INTO "Modules" VALUES(3,'','',0,8);

Notice how the values for entPhysicalIndex are 6, 7, 8. Also note that isPresent=0 because nothing is present yet.

For the ports, it is a bit more tedious. Again, the port indices start at 9, and go up as specified above, and isPresent is 0 for all.
CREATE TABLE AccessPorts (slotNumber integer, portNumber integer, portType string, isPresent integer, entPhysicalIndex integer, entPhysicalContainedIn );
INSERT INTO "AccessPorts" VALUES(1,1,'',0,9,6);
INSERT INTO "AccessPorts" VALUES(1,2,'',0,10,6);
INSERT INTO "AccessPorts" VALUES(1,3,'',0,11,6);
INSERT INTO "AccessPorts" VALUES(1,4,'',0,12,6);
INSERT INTO "AccessPorts" VALUES(2,1,'',0,13,7);
INSERT INTO "AccessPorts" VALUES(2,2,'',0,14,7);
INSERT INTO "AccessPorts" VALUES(2,3,'',0,15,7);
INSERT INTO "AccessPorts" VALUES(2,4,'',0,16,7);
INSERT INTO "AccessPorts" VALUES(3,1,'',0,17,8);
INSERT INTO "AccessPorts" VALUES(3,2,'',0,18,8);
INSERT INTO "AccessPorts" VALUES(3,3,'',0,19,8);
INSERT INTO "AccessPorts" VALUES(3,4,'',0,20,8);

Now let's suppose that we insert a "2-PoE-Port" module in slot 1, and a "4-Port-Ethernet" module in slot 3, and the plug&play software detects hardware revision "A001" for the 2 port PoE card and "B002" for the 4 port card. We'll need to update the module table as follows:

update Modules set isPresent=1, hardwareRev="A001", moduleType="2-PoE-Port" where slotNumber=1;
update Modules set isPresent=1, hardwareRev="B002", moduleType="4-Port-Ethernet" where slotNumber=3;

We'll need to update the port table as follows:
update AccessPorts set isPresent=1, portType="PoE" where slotNumber=1 and portNumber>0 and portNumber <3;
update AccessPorts set isPresent=1, portType="Ethernet" where slotNumber=3;

For simplicity we'll have a table for the slots:
create table Slots (slotNumber integer, entPhysicalIndex integer);
insert into Slots values (1,3);
insert into Slots values (2,4);
insert into Slots values (3,5);

We don't need tables for the chassis, or the 1000-base T port on the chassis, because they are always there, but we will need to represent them in a view for entPhysicalTable.

The view of entPhysicalTable is just a union of the views for the chassis, uplink, slots, modules and ports, and ordered by entPhysicalIndex. The SQL view below encodes this idea.

create view entPhysicalTable as
-- This part represents the whole chassis
select 1 as entPhysicalIndex, 0 as entPhysicalContainedIn, 3 as
entPhysicalClass, 0 as entPhysicalParentRelPos, "FlexiRouter" as
entPhysicalName, "AAXX01" as entPhysicalHardwareRev, 1 as
entPhysicalIsFRU
UNION
-- This part represents the 1000 Base T uplink
select 2 as entPhysicalIndex, 1 as entPhysicalContainedIn, 10 as
entPhysicalClass, 1 as entPhysicalParentRelPos, "1000-Base-T Uplink" as
entPhysicalName, "" as entPhysicalHardwareRev, 2 as entPhysicalIsFRU
UNION
-- This part represents the slots
select entPhysicalIndex, 1 as entPhysicalContainedIn, 5 as
entPhysicalClass, slotNumber as entPhysicalParentRelPos, "Slot " ||
slotNumber as entPhysicalName, "" as entPhysicalHardwareRev, 2 as
entPhysicalIsFRU from Slots
UNION
-- This part represents the modules
select entPhysicalIndex, entPhysicalIndex-3 as entPhysicalContainedIn,
9 as entPhysicalClass, slotNumber as entPhysicalParentRelPos,
moduleType || ' module/slot ' || slotNumber as entPhysicalName,
hardwareRev as entPhysicalHardwareRev, 1 as entPhysicalFRU from Modules
where isPresent=1
UNION
-- This part represents the ports
select entPhysicalIndex, entPhysicalContainedIn, 10 as
entPhysicalClass, portType || "/slot=" || slotNumber || "/port=" ||
portNumber as entPhysicalName, "" as entPhysicalHardwareRev, 2 as
entPhysicalIsFRU from AccessPorts where isPresent=1
ORDER BY entPhysicalIndex;

Because the structure of the entPhysicalTable view is the same as with the previous example, the queries for SNMP requests are the same as with the previous example.
The SNMP manager will issue SNMP-GET requests to the SNMP agent. The following query satisfies the request for the index @X:
select * from entPhysicalTable where entPhysicalIndex = @X;

If the query yields no results, then the MIB objects do not exist.

The SNMP manager will issue SNMP-GETNEXT requests to the SNMP agent. The following query satisfies the request for the index @X:
select * from entPhyscialTable where entPhysicalIndex >= @X order by entPhysicalIndex limit 1;

If the query yields no results, then the table is empty or the table has been walked completely.

The net effect of such as system is as follows:
1) Data about modules, ports, slots is normalized and only the minimum required is stored.
2) There exists a view of the stored data in the form of a view called entPhysicalTable which can satisfy SNMP requests.
2) The provisioning software can easily change the contents of the stored data by updating the isPresent field of the Modules and AccessPorts table, and filling in module/port types if needed.

The product from SNMP Research generates k_XXX function shells that are called by their SNMP agent software. In those functions you can figure out if you are going to use a GET or GETNEXT, and call the appropriate SQL query through the C SQLite API.

SQLite provides application designers the ability to store blobs in a databa