SQLite Help Required

[Pete]

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

[Faramir]

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...

[Pete]

Hi.  A complete solution if it isn't too much bother.  Otherwise a query would suffice.  Many thanks for your reply.  Pete

[Faramir]

The simplest thing to do is to get a copy of the sqlite software for Windows ( for 32-bit you can use this and for 64-bit you can use this 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

[Pete]

Brilliant! Thank you very much indeed.
Regards
Pete

[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, ") = ";

[Faramir]

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...

Code Select Expand

DELETE FROM Aircraft
WHERE IFNULL(Registration, ' ') = ' '
OR IFNULL(Type, ' ') = ' ';

[Pete]

Works perfectly now - it was me being stupid!

Thanks again for all your help

Pete

[benipaz]

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.

Code Select Expand


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

[Pete]

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

[Anmer]

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:

Code Select Expand

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.

[Faramir]

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)

[benipaz]

Pete, delete all records in a table also quick and simple solution to reduce the size of a table 

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

[Anmer]

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.

[benipaz]

cool