Importing old detections from MariaDB to sqlite3

[davidmam]

I have finally, after about 10 months, taken the plunge to update my home BirdNET-Pi to the latest versions.
After a few wrinkles were resolved (sqlite not being installed with the installer) I would love to be able to view my historic recordings with the new interface. I am DB literate but thought it would be best to ask whether the two databases are similar enough to just dump and import.
Is there any reason for using sqlite instead of MariaDB? Will future versions give the option, as with a MariaDB setup, multiple BirdNET-Pi could report to the same DB (definitely something I would like to be able to do as we look at setting up more).

[mcguirepr89]

Hi, @davidmam

The MariaDB version has been off of main for a long, long time now. I don't even remember what the db table structure looked like for those.

No versions of BirdNET-Pi that I will be part of will switch from SQLite back to any server-based database as the system performs far better with that server no longer part of the installation.

If you'd like to share your database somewhere, I'd be happy to take a look and provide the appropriate SQL queries that would restore an SQL export of that database. (Depending on that aforementioned level of DB literacy, you may or may not be comfortable taking a backup manually -- use mysqldump from this resource and we can move from there.

Future database sharing won't rely on a database server, but a centralized API over HTTPS (using user tokens) -- those requests will allow amalgamated DBs without the downfalls of exposing a centralized database and somehow trying to secure it and keep it open to the world. For your own aggregation, you can utilize an MQTT server and the new notification features to JSON-format your data and collect it using your own server management.

Addition:
This script contain the steps that I will ultimately outline -- feel free to utilize this tool(mysql2sqlite) instead.

[davidmam]

Superb. I've checked the table structures and the MariaDB is missing the File_Name column. I should be able to create that once I know the format so will wait till tomorrow when I have some detections in there.

I had not updated this one since December 2021. I do have a new one up at the University Botanic Gardens and it has proved very popular but is not on the internet (yet) as we are awaiting some network upgrades there.

[davidmam]

Another issue - I cannot find where the new recordings are going. Usually they would be in the Extracted folder but the recordings don't appear to be findable through the web interface when you browse for the recent recordings/spectrograms. Nothing in the log to show any errors - the View Log button gives a 404 error.

[davidmam]

This issue turned out to be a sox format error. The update script updated sox but not the libsox-fmt-all package which, upon installation and restarting services, has now sorted out the backlog. Now all happy and progressing.