Populated SQB file

[gonzalu]

Another update... after running the new DB for a while, I have noticed that a few airplanes have what seems to be the MODELICAO in the OPERATOR ICAO field. Could this be due to the merge process?

Edit: It may be that's how S92Driver has his file purposely

So, does the process in the document and the SQL respect existing entries and either not overwrite them or add a duplicate? Just curious as to what is actually happening. I assumed (perhaps naively) that the process checks for existing ICAO HEX and then if exists, and fields are populated, leave as is on original, and if not found, it adds the record from the NEW imported db?

[Triple7]

Hi Gonzalu,

SQL Two fails with the error as I believe SQL One is loading the same database we are working on. Currently the documentation has SQL One as:

Code Select Expand

attach database "C:\Basestation_Original\BaseStationOriginal.sqb" as bsnew;


I assumed the actual code for SQL One should be

Code Select Expand

attach database "C:\Basestation_Import\BaseStationImport.sqb" as bsnew;


Yes, despite all the proof reading, you are correct, the SQL 1 should load the new 'Import' sqb file - thank you for testing and reporting it. I am sure Anmer will amend the file and re-publish

Now, the close, does it matter that the TempData table stays in the db? I left it as is and seems to work but, figured it may be good to clean up and leave db back as it was originally?

It doesn't matter - your choice - but if you choose to run the SQL again in the future, you won't need to re-create the TempData table. It won't make any difference to the functioning of your database if you leave it in place – it is emptied of any data at the end of the merge process.

Another update... after running the new DB for a while, I have noticed that a few airplanes have what seems to be the MODELICAO in the OPERATOR ICAO field. Could this be due to the merge process?

That would indicate either an error in the SQL - which has been tested and works, or your db is not in the same order as the original Kinetic one! I assume by MODELICAO you mean ICAOTypeCode as the column header and by OPERATORICAO you mean OperatorFlagCode? In the original layout, ICAOTypeCode was in column 14 and OperatorFlagCode was in column 50. Are your 2 columns in those precise positions? Open the db in SQLite Personal and click on the aircraft tab then (if not already selected) the Columns tab. Check where those columns are in your own db. If they are not in columns 14 and 50 respectively, then you will need to follow the instructions in the Appendix to sort it out.
[/quote]

So, does the process in the document and the SQL respect existing entries and either not overwrite them or add a duplicate? Just curious as to what is actually happening. I assumed (perhaps naively) that the process checks for existing ICAO HEX and then if exists, and fields are populated, leave as is on original, and if not found, it adds the record from the NEW imported db?

Yes, the idea is that any existing records are retained and all you are doing here is adding anything that is new and not currently listed in your db.

Tim

[Anmer]

Hi Manny

You mention:

One is loading the same database we are working on.

If, as advised, you've taken a copy of your current Basestation.dqb file, nothing, other than SQLite, should have access to the copy?

See Page 5.

I have noticed that a few airplanes have what seems to be the MODELICAO in the OPERATOR ICAO field

As far as I can ascertain, s92driver's populated sqb file doesn't have an OPERATOR ICAO field. 

What field do you mean?

So, does the process in the document and the SQL respect existing entries and either not overwrite them or add a duplicate?

See Page 4:

This Guide shows how to merge two files, typically one's existing file (original) and one with new aircraft data (import).  The original file will include aircraft data, flights, sessions and the local receiver's "Home" .location.  The import will typically only have aircraft data.  This Guide shows how to merge aircraft data from an import file.

Following a merge, the updated original file will hold flights and sessions from the original, aircraft from the import and any aircraft from the original not in the import.

Merging databases is not without risk which is why the Guide advises working with copies of sqb files.  If the resultant merged database isn't what one expected or has errors, it's possible to revert back.

The merging is not a selective "compare and replace" process for individual data fields.

If an aircraft record doesn't exist in the original sqb it will add all the the available data from the imported sqb.  If an aircraft record already exists it will ignore it and leave "as is".

Be warned!






[attachment deleted reduce file load]

[Anmer]

Yes, despite all the proof reading, you are correct, the SQL 1 should load the new 'Import' sqb file - thank you for testing and reporting it. I am sure Anmer will amend the file and re-publish

Thanks Manny and Tim.

Even repeated proof-reading missed that. 

The Guide has been amended and the new version uploaded.

[gonzalu]

You guys are absolutely superb. Legend as my Aussie friends say

Thank you so much for the work done and the confirmation and clarification for my dumb ass questions. With this in mind, I will once again do a fresh merge and confirm as Triple7 said, that my db and the import db are exactly the same column order. Since my db was a freshly created one via VRS, I assumed it was a legit layout.

Thank you guys ...

[gonzalu]

Hi Gonzalu,

...snip...

That would indicate either an error in the SQL - which has been tested and works, or your db is not in the same order as the original Kinetic one! I assume by MODELICAO you mean ICAOTypeCode as the column header and by OPERATORICAO you mean OperatorFlagCode? In the original layout, ICAOTypeCode was in column 14 and OperatorFlagCode was in column 50. Are your 2 columns in those precise positions? Open the db in SQLite Personal and click on the aircraft tab then (if not already selected) the Columns tab. Check where those columns are in your own db. If they are not in columns 14 and 50 respectively, then you will need to follow the instructions in the Appendix to sort it out.

---snip...
Tim

Tim, you honestly believe I proofread that or verified the column headers? LOL. I told you I am a dweeb. So sorry. I should have used the actual column headers to describe the situation
Let me look at this again "carefully" and report back.

Cheers!

[Faramir]

While an absolute nightmare coder, I'm trying to find out if I can automate the process within a Python script.
Actual work is being done by SQLite but I want to read the fieldnames from the original aircraft table and alter the queries with the order found.
It requires slicing lists, tuples and dictionaries and that is a tough task for me.
Furthermore it could be helpful in avoiding typing error's etc. and perhaps even some logging.
Just at the concept stage, but trying nevertheless.
The query to read the original Aircraft table fieldnames in order is working.
Now the tricky stuff begins

[gonzalu]

OK, round two of doing a merge

I now have done the merge but, I noticed the imported records all have the interesting flag set? Is this factually correct or is it something I am doing wrong on my end?

IS there a way to clear that flag before merging?

Here is an example...



Thank you!

[gonzalu]

Oh well, I hacked around and think I figured a way to do it...

Open S92driver's database. In the SQL tab, entered the following SQL:

Code Select Expand

UPDATE Aircraft
SET `Interested` = NOT `Interested`;

This flipped the bit from checked to unchecked (true to false)

Seems to have worked fine

[Faramir]

While working on a python script to automate the database merging process I find that more often than I like the database gets corrupted. It doesn't produce an error but when working with the databse at some point it crashes with the 'SQLite database disk image is malformed' message. The the database is no longer usable!
If you want to try merging, please use a copy of your database and check it's integrity before you start the merging process.
This can be done by entering the following command in the SQLite Expert Personal program:
PRAGMA integrity_check. If the result is anything else than 'ok', don't go any further and use the BasestationReporter program to pack the database first!

[s92driver]

The latest sqb file is now available here  https://radarspotting.com/forum/index.php?action=tportal;sa=download;dl=item64

Other than containing the latest aircraft data, it also now captures any Alaska Airlines aircraft still carrying the old livery.

Next month 'may' include a bigger change!


Steve

[s92driver]

This months sqb will be generated later today.

Further to Gonzalu's recent posts, the Interested flag is something I deliberately set.  Would users prefer it to be set On or Off for all records?

Finally, if anyone finds that an aircraft flying with a miscoded Transponder is constantly showing something very odd please let me know.  For example an Emirates A380 is using the code allocated to a Cirrus.  This next amendment will remove the Cirrus and only show the A380.


Steve

[gonzalu]

Thank you, Steve... your hard work does not go unnoticed or used

I have my INTERESTING flag set only on such aircraft that I want to pop to the top of the VRS airplane list ... so I don;t mind whichever way you set your db as I now know how to unset it via SQL commands prior to merge.

Cheers!

[Anmer]

This months sqb will be generated later today.

Latest SQB file now available.

[s92driver]

This latest version of the sqb now includes almost 3000 ground based vehicles and radar heads that are fitted with ModeS transponders.  These are all set as "not interested", so filters can be utilised so these don't show.

As usual any feedback or updates are very, very welcome.


Here's to a better 2021,
Steve