Your knowledge on SQL far exceeds mine...
I wish GlynH - I only started learning it Saturday evening so I could do this!
Anyway here goes with the scripts and remember boys and girls to make 2 back-ups first - 1 to make your changes to and the second as a replacement if the edited one fails to start RB (RB actually creates its own backup each time it's ran 'NavData.db3.bak' that you could use).
First off I use this script to create a second aircraft table to do all the changes in:
CREATE TABLE aircraft2
(
MS varchar(6),
AR varchar(50),
AT varchar(80),
AN varchar(80),
AC varchar(80),
CN varchar(80),
PT varchar(80),
LK varchar(80),
PT2 varchar(80),
LK2 varchar(80),
MINE varchar(80)
)
This table can be left in the database from this point forward as RB will just ignore it. The
mine column isn't in the original aircraft table but I've added it so that I can use it as a temporary place to put changes for checking before I put them back into the column they belong in.
After doing each set of changes you will need to run this script to commit them back into the aircraft table:
REPLACE INTO aircraft (MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2)
SELECT MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2
FROM aircraft2
Then you'll need to run this script to clear the aircraft2 table ready for your next set of changes:
DELETE FROM aircraft2
If you want to remove the aircraft2 table just use
DROP TABLE aircraft2
but as I say it's not necessary. Obviously don't forget to copy the amended table back into your RB folder before running RB again.
Okay, that's the admin scripts done. Now for the actual changes.
--------
Let's start with the scripts for fixing aircraft that have the company name switched with the construction number. Run this first:
INSERT INTO aircraft2 (MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2)
SELECT MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2
FROM aircraft
WHERE AC LIKE '1%'
This will move into the
aircraft2 table any aircraft where the company name starts with '1'. You'll have to manually browse these records and delete any where the company name field is actually correct i.e 1st Air Company so you're just left with the records that need changing. Now run:
UPDATE aircraft2
SET mine = ac, ac = cn, cn = mine
All of the necesary records should now be corrected so run the second and third admin scripts to apply the changes to the
aircraft table and then clear the
aircraft2 table.
Now repeat changing 1% to 2%, then 3% .......... 0% to capture construction numbers starting 2,3 through to 0. Continue to repeat using %1%, %2% etc ....... %0% to capture records where the company name currently contains a number. This is to cover records where the construction number starts with a letter (or more) instead. You can also try replacing 1% with ... or empty quotes ''
[blank] or null - this last one will usually bring back records with only a ModeS code or ModeS and reg which we'll correct later.
---------------------
---------------------
Next we'll fix aircraft records that have a ModeS code but no other aircraft details. Run this script to extract them to the
aircraft2 table:
INSERT INTO aircraft2 (MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2)
SELECT MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2
FROM aircraft
WHERE ar LIKE 'null'
Now do an internet search on each ModeS code in the
aircraft2 table to find it's other details (I tend to use airframes.org). Enter the details into this script to apply each one:
UPDATE aircraft2
SET at='B738', an='Boeing 737-86N', ac='Spicejet', cn='32672', ar='VT-SPW'
WHERE ms='0D03F1'
at = four letter aircraft type code
an = aircraft type name
ac = company / owner
cn = construction number
ar = aircraft registration
ms = ModeS code we searched on
Once complete apply the changes to the
aircraft table and clean
aircraft2 again using the last 2 admin scripts.
Repeat replacing the
LIKE 'null' line in the first script with
LIKE '' and then with
LIKE '...'Where no details can be found I tend to replace the ar, at and an fields with ? so that they don't come out in future searches (I'll go back and try to resolve them specifically once a month).
--------------------------------------
--------------------------------------
Now we'll do the same as above but for aircraft that already had a registration but no actual aircraft details. Run these scripts instead of those above:
INSERT INTO aircraft2 (MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2)
SELECT MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2
FROM aircraft
WHERE an LIKE 'null'
UPDATE aircraft2
SET at='B738', an='Boeing 737-86N', ac='Spicejet', cn='32672'
WHERE ar='VT-SPW'
Once complete apply the changes to the
aircraft table and clean
aircraft2 again then run again replacing the
LIKE 'null' line in the first script with
LIKE '' and then with
LIKE '...'-------------------------------------
-------------------------------------
Now we'll look at aircraft with no aircraft type code. Run this script to extract the first batch:
INSERT INTO aircraft2 (MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2)
SELECT MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2
FROM aircraft
WHERE at LIKE '...'
We'll want to replace these is an many batches as possible rather than one at a time so if for instance the first aircraft was a Piper PA-28-161 Cherokee Warrior II (
an column) we might use this script:
UPDATE aircraft2
SET at = 'PA28'
WHERE an LIKE 'piper pa-28%'
The % is a wildcard so all aircraft who's names start Piper PA-28 in the list will be updated with a type code of PA28. Seaching is not case sensitive! You'll have to choose your own values for the last line of the script above depending on what suits. If your feeling really brave you could change that last line to read WHERE an LIKE '%pa-28%' which will cover every aircraft with pa-28 somewhere in the name text but I'd recommend not doing so.
Once complete (and applied to
aircraft / cleaned
aircraft2) try again replacing the ... in the first stage with [blank] and then 'null'.
------------------------------------------
------------------------------------------
To fix airline / owner names that start with Untitled (or any other text) we would run these scripts:
INSERT INTO aircraft2 (MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2)
SELECT MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2
FROM aircraft
WHERE ac LIKE 'Untitled%'
followed by
UPDATE aircraft2
SET mine = SUBSTR(ac,10,length(ac)-10), ac = mine
WHERE ac LIKE 'untitled(%'
which will change Untitled(United) to read United. If we had Untitled (United)
with the space we'd use
UPDATE aircraft2
SET mine = SUBSTR(ac,11,length(ac)-11), ac = mine
WHERE ac LIKE 'untitled (%'
SUBSTR extracts a middle section from a piece of text:-
SUBSTR(column name,character number to start at,length of text to retain)
In the last example above the string
Untitled ( is 10 characters so we want to start extracting from character 11. We dont know what length the text inside the parenthesis will be so to set the length of text to retain we use
length(ac)-11 - the length of the total string in column
ac minus 11 characters, 10 to cover
Untitled ( plus 1 for the end
). Hope that's understandable! Don't forget also to chage the last line
WHERE ac LIKE 'untitled (%' to ensure the correct records are identified.
If we had
rubbish United Airlines rubbish our script would read:
UPDATE aircraft2
SET mine = SUBSTR(ac,9,length(ac)-16), ac = mine
WHERE ac LIKE 'rubbish%'
Care must be exercised if similar text needs to be edited on different records. I've used 2 scripts
WHERE ac LIKE 'untitled(%'
WHERE ac LIKE 'untitled (%'
because using
untitled% would only work for some of the records dependng on which numbers we set in
SUBSTR(ac,11,length(ac)-11) - all the records would be altered but one or the other set
'untitled(' or 'untitled (' would be incorrect.
Hopefully the above is clear. Just check your changes before you apply them to the
aircraft table to be sure.
----------------------------------------
----------------------------------------
The last change we'd probably want to do is to standardise the airline name for all aircraft from a particular company. Most people have probably got aircraft from Easyjet listed as
Easyjet
Easyjet Airline
Easyjet Airlines
Easyjet Airline Company
Easyjet Switzerland
and possibly EasySwiss
To extract all of the these for changing we'd use:
INSERT INTO aircraft2 (MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2)
SELECT MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2
FROM aircraft
WHERE ac LIKE 'Easyjet%'
To edit just the UK part of the fleet we'd use
UPDATE aircraft2
SET ac = 'Easyjet'
WHERE ar LIKE 'G%'
only aircraft with a reg starting Gand likewise the Swiss part of the fleet with
UPDATE aircraft2
SET ac = 'Easyjet Switzerland'
or EasySwiss if you preferWHERE ar LIKE 'HB%'
They can then all be applied as one to the
aircraft table.
--------------------------------------
That's it really. There are other changes but armed with the above you should be able to change any of the scripts or write your own to suit whatever changes you need to make. Other than the SUBSTR script they are all simple SQL (need to be for my beginner status). I'm sure someone will come along with much more powerful or simplified scripts that would save no end of time / effort but these will at least do the trick.
