How to Manage Sqlite3 Database

There are several ways to archive, reset or start a fresh speed camera sqlite3 database. The sqlite3 database and schema structure will automatically be created by speed-cam.py.

First stop speed_cam.py using menubox.sh or from console using speed-cam.sh stop

Options

1. Rename data/speed_cam.db to another name

At an ssh or terminal console run the following commands

cd ~/speed-camera
mv data/speed_cam.db data/speed_cam_30jun21.db

A fresh copy will be created on restart. If you do not delete images you can restore back to old database by changing the DB_NAME in config.py.

2. Delete data/speed_cam.db

cd ~/speed-camera
rm data/dpeed_cam.db

Also see How to View Speed Camera Data

The database and structure will be recreated on next restart but your db history will be lost.

3. Change config.py sqlite3 variable settings

You can change any of the following or any combination in config.py. I suggest just creating a new database table per config.py setting below

DB_TABLE= "speed2"  

The previous speed database table will still exist and you can change back to it if you want to run historical reports or graphs.

# Sqlite3 Settings   in config.py
# ----------------
DB_DIR   = "data"
DB_NAME  = "speed_cam.db"
DB_TABLE = "speed"

You can then decide if you want to keep media/images or start a new media/images folder or delete everything.. Depending on what you do above and how much disk space you have. Note disk space information is available at the bottom of the speed camera webserver page or run the following unix command

df -h

Sample output below. Note I have a usb hard disk attached for more data storage space and less SD card wear and tear.

Filesystem      Size  Used Avail Use% Mounted on`
/dev/root        15G  3.0G   11G  22% /`
devtmpfs        182M     0  182M   0% /dev`
tmpfs           186M     0  186M   0% /dev/shm`
tmpfs           186M   21M  165M  12% /run`
tmpfs           5.0M  4.0K  5.0M   1% /run/lock`
tmpfs           186M     0  186M   0% /sys/fs/cgroup`
/dev/mmcblk0p1   60M   22M   39M  37% /boot`
/dev/sda1       466G   12G  455G   3% /mnt/usb`
tmpfs            38M     0   38M   0% /run/user/1000`

Simplest would be just to change db name eg DB_NAME = "speed_cam_2.db"

Restart speed_cam.py in ssh or terminal to monitor logs

cd ~/speed-camera
./speed-cam.py

Monitor activity logs for correct operation. Use menubox.sh to restart speed camera in background or run command below from ssh or terminal session

cd ~/speed-camera
./speed-cam.sh start

If you are having problems with media/recent then the symbolic links can just be deleted and new ones will be created as new data and images are collected.

cd~/speed-camera
rm media/recent/*

(Optional) You can also create custom plugins for special database configuration changes or settings.

Also see How to View Speed Camera Data