Using SQLite as a configuration database

For smaller installations with lighter concurrent access, one can consider using SQLite as a configuration database.

Copy the SQLite JDBC from https://github.com/xerial/sqlite-jdbc to the ${TOMCAT_HOME}/lib folder.
```
$ cd $TOMCAT_HOME/lib
$ ls sqlite-jdbc-3.39.3.0.jar
sqlite-jdbc-3.39.3.0.jar
```

Configure a connection pool using SQLite in the Tomcat context.xml

$ cd $TOMCAT_HOME/conf
$ cat context.xml
<?xml version="1.0" encoding="UTF-8"?>
...
    <Resource   name="jdbc/archappl"
                auth="Container"
                type="javax.sql.DataSource"
                username="xxx"
                password="xxx"
                maxTotal="1"
                maxIdle="1"
                maxActive="1"
                driverClassName="org.sqlite.JDBC"
                url="jdbc:sqlite:/archappl/config/archappl.sqlite?journal_mode=WAL"
                />
...

Note that sqlite requires both the DB file and the containing directory to be writable.
SQLite does file locking during updates; so we cannot really use multiple connections in the connection pool. This requires us setting the maxActive, maxIdle and maxTotal to 1.

If more than one connection tries to write to the DB at the same time, we’d see exceptions of the following nature

ERROR org.epics.archiverappliance.config.DefaultConfigService  - Exception persisting pvTypeInfo for pv ...
java.io.IOException: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)

Initialize the SQLite database using the install/archappl_sqlite.sql SQL script shipped as part of the mgmt.war

$ cd /archappl/config/
$ sqlite3 --init ~/unzipped_mgmt_war/install/archappl_sqlite.sql archappl.sqlite
-- Loading resources from install/archappl_sqlite.sql

SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .quit
$

It is recommended to use the SQLite WAL journal mode to improve write performance. Using WAL usually implies the presence of a .shm and .wal file in addition to the .sqlite file.