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. Note I have a usb hard disk attached for more storage space and less

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