I'm trying to reduce the size of my BaseStation.sqb file using SQLite. Can anyone help with a script that will identify/delete entries that have 'Registration' and/or 'Type' fields that are either blank or contain <null>. Any help greatly appreciated. Pete
Quote from: Pete on January 29, 2023, 01:29:26 PM
I'm trying to reduce the size of my BaseStation.sqb file using SQLite.
Pete. are you able to run SQLite queries or do you need a complete solution for this? The query is simple enough...
Hi. A complete solution if it isn't too much bother. Otherwise a query would suffice. Many thanks for your reply. Pete
The simplest thing to do is to get a copy of the sqlite software for Windows ( for 32-bit you can use this (https://www.sqlite.org/2022/sqlite-dll-win32-x86-3400100.zip) and for 64-bit you can use this (https://www.sqlite.org/2022/sqlite-dll-win64-x64-3400100.zip) link).
Shut down Basestation and extract the downloaded archive in the Basestation directory. Just overwrite the original sqlite3.dll with the new one, it will still work perfectly.
After that is done there is a command-line utility present called sqlite3.exe
If you run that utility (sqlite3.exe basestation.sqb) you have a way to enter queries. The query you need is:
DELETE FROM Aircraft
WHERE IFNULL(Registration, '') = ''
OR IFNULL(Type, '') = '';
(All quotes are single quotes)
Close the tool after running the query with Ctrl-Z and you're done
Brilliant! Thank you very much indeed.
Regards
Pete
This is exactly what I entered into SQLite but it came back with a sintax error. Any ideas where I went wrong?
I wasn't sure what you meant by 'all quotes are single quotes'. Thanks again.
DELETE FROM Aircraft
WHERE IFNULL(Registration, ") = "
OR IFNULL(Type, ") = ";
Quote from: Pete on January 29, 2023, 03:27:49 PM
I wasn't sure what you meant by 'all quotes are single quotes'. Thanks again.
DELETE FROM Aircraft
WHERE IFNULL(Registration, ") = "
OR IFNULL(Type, ") = ";
A single quote is this ' When two are put after each other they may be confused with a double quote "
Let me put a space between the quotes so you know what I mean. In the tool the space between the quotes should
NOT be there...
DELETE FROM Aircraft
WHERE IFNULL(Registration, ' ') = ' '
OR IFNULL(Type, ' ') = ' ';
Works perfectly now - it was me being stupid!
Thanks again for all your help
Pete
Hi Pete
From my experience the biggest table in DB is the Flights table.
I'm using the next script to clear all history except last 50 flights for each plane.
I reduced my DB from 730MB to 150MB on first run.
DELETE FROM Flights
WHERE FlightID IN
(SELECT FlightID FROM (
SELECT FlightID, row_number() OVER win as rn
FROM Flights F
WINDOW win as (PARTITION BY AircraftID ORDER BY StartTime desc)) T
WHERE rn > 50)
P.S. make copy of BaseStation.sqb before any changes
Hi. Thanks for your input, I had realised that the Flights table was very large. My solution was to go into the table then use Ctrl A followed by Delete. A bit rough and ready but it worked!
My concern (and it's lucky I made a backup) was that when I ran the script mentioned earlier in this thread, it deleted all the Aircraft records bar one from the database. Surprisingly, when I tried running the SQL again with a second backup, it was the same record that remained.
I now need to find out if I did something wrong or if there is a problem with the SQL itself.
Any solutions greatly appreciated.
Pete
Sorry to jump in here but I'm not an SQLite expert (pun not intended) but this suggested SQL doesn't work using SQLite Expert Personal:
DELETE FROM Flights
WHERE FlightID IN
(SELECT FlightID FROM (
SELECT FlightID, row_number() OVER win as rn
FROM Flights F
WINDOW win as (PARTITION BY AircraftID ORDER BY StartTime desc)) T
WHERE rn > 50)
I get a Syntax error as per attached.
It seems that SQLite Expert Personal doesn't support (all) the Window functions of SQLite. The query goes above my head but I copied it into the latest version of SQLite Maestro and it worked flawlessly...
To make sure my sqlite is the latest version I regularly update. BaseStation keeps working well with the latest sqlite3.dll (version 3.40.1)
Pete, delete all records in a table also quick and simple solution to reduce the size of a table ;D
Anme
I'm using DB Browser for SQLite for DB maintenance and this script works fine.
UPD: Just checked the application version. DB Browser for SQLite Version 3.12.2 with SQLite Version 3.35.5
Quote from: benipaz on January 30, 2023, 04:55:02 PM
Anme
I'm using DB Browser for SQLite for DB maintenance and this script works fine.
UPD: Just checked the application version. DB Browser for SQLite Version 3.12.2 with SQLite Version 3.35.5
Thanks, that works for me too, using DB Browser for SQLite.
cool :)
Back to the original question, I now know why the SQL script deleted all my records bar one. I finally realised that in my BaseStation.sqb, the entries for Aircraft Type were not in the Type field but actually in ICAOTypeCode! As my Type field only had one entry, it's obvious why all my entries disappeared. The SQL script worked perfectly, it was a case of garbage in garbage out/ operator error! Thanks all for your help. Pete
Thanks for sharing your findings. It helps others to get updates like this.