Database modifications

[Faramir]

Does anyone have modifications made to the Basestation database? Maybe others would enjoy them too. Personally I use some modifications (triggers and extra tables) to extend the available information in the 'Aircraft Details' window.

[Pipo]

Thanks to the help of Faramir, i use a database modification, and it looks great, after today i can see Flight counts of a registration and also how many days a go i have seen it last. Also i have a bmp file named Spotted and it is showing in aircraft details of planes that have spotted in real time.

Here is a screenshot of what i can see in Aircraft Details:


This is most done by Faramir, with some ideas from my side, he hase made triggers and queries to make this happend. Also did some adding in the basesttion.xsl file.

Thanks for this Faramir

[Oldfella]

Greetings Faramir and Pipo. The mods you have made to the Aircraft Details screen look very impressive.

Any chance you can post the upgraded file so others could enjoy.

Many thanks for your efforts.

Oldfella

[Faramir]

... Any chance you can post the upgraded file so others could enjoy.

Sure, but the screenshot from Pipo contains more than one kind of modifications. Besides triggers added to the database, it also uses altered .xsl and .css files and even an added table to the database.
(Triggers are automated SQL instructions that are executed if a condition is met, the .xsl and .css files from the UserData directory determine the layout of the Aircraft Details screen.) The trick is to use triggers to 'provide' the extra information and store it in a field that is present in the .xml files.

My modifications to date:

• added table Callsigns that contains the official ICAO radio callsigns and designators. Source for that table is the brilliant website Aviation Codes Central
• a trigger 'OperatorInfo' that copies the OperatorFlagCode to the MTOW field (the OperatorFlagCode is not present in the temp.xml files but the MTOW field is), and that uses the Callsigns table to retrieve the radio callsign and stores it in the CofACategory field every time a record is updated.
• a trigger 'FlightCount' that for a given mode-s code stores the number of flights present in the Flights table in the OwnershipStatus field
• a trigger 'DaysUnseen' that for a given mode-s code stores the number of days since it's last entry in the Flights table to the CofAExpiry field
• separate SQLite query to populate the InfoUrl field with the Aviation Alliance membership, (to be added to the Callsigns table later to make this a dynamic process too).
• added a second .css file to use a separate backgroud for military aircraft.
• a modified .xsl file that shows all the added information.

The triggers are easy to be added to the database, the ones used are fired everytime a record from the Aircraft table is updated.
The extra table was added (and filled) manually as are some SQLite scripts.
Besides BaseStationReporter I also use a SQLite environment (I use SQLIte Maestro but there are free alternatives) to be able to manipulate the database directly. Not all fields from the Aircraft and Flight tables are accessable in BasestationReporter.
If you like I can give you a copy my Basestation.sqb (with emptied Active, Aircraft, Flights, Sessions and Locations tables) and UserData files so you can explore it in all details.

John

[Oldfella]

Thank you John for your offer, I would very much appreciated a copy of your files. I had a play with my .xml and .xsl files a got myself in a bit of a mess. Fortunately I had backup my originals so no harm was done.

Thanks again,

Oldfella
noelwhite@aussiebb.com.au

[Faramir]

Thank you John for your offer, I would very much appreciated a copy of your files.

Sent them via e-mail, if anything is unclear just give me a shout.

[Oldfella]

Thank you, working through the files now, needing some adjustment as expected.

Appreciate your help.

Oldfella