Database

Overview

Conceptually, the schema is broken down into the following groups:

Group	Contents
Sighting	Main data tables used for holding sighting details and related information
Airport Reference Data	Register of valid airport IATA codes, used for input validation
Ancillary	User authentication table, background job statuses and EF Core migration history
Keyless SQL Reports	Empty tables generated as an artifact of the SQL report configuration

The "Sighting" group consists of the following sub-groups:

Sub-Group	Contents
Aircraft Information	Aircraft details and associated models and manufacturers
Flight Information	Flight start and end points and operating airlines
Locations	Named locations where sightings are made
Sightings	Records of specific aircraft on specific flights at specific locations

Aircraft Information

Each aircraft is associated with with a named model that, in turn, is associated with a named manufacturer. Multiple aircraft may be associated with a single model and multiple models may be associated with a single manufacturer:

Table	Description
Manufacturer	Named manufacturer details
Model	Named models, each associated with a manufacturer
Aircraft	Aircraft registration details, each associated with a model

Location Information

Sightings are made at named locations. Multiple sightings may be associated with a single location:

Table	Description
Location	Named location details

Flight Details

Each flight has a number and route and is operated by a named airline:

Table	Description
Flight	Flight numbers, points of embarkation and destination airports
Airline	Named airlines

Sighting Details

Each sighting is associated with a location, an aircraft and a flight and has additional details such as the aircraft altitude at the time of the sighting and the date:

Table	Description
Sighting	The sighting records

Security Implementation

The registered users for the application are held in the User table. The contents of the table are used to authenticate users via the REST API.

In-Memory Database

An in-memory database is supported for unit testing. Please see the unit tests project for examples.

SQLite Database

Creating the SQLite Database

Prior to recording data in a SQLite database, you must ensure:

• The database file has been created
• The Entity Framework Core migrations have been run to set up the database tables required by the capture

The repository includes a database management tool that includes an option that completes the above actions for you.

To use the tool, first edit the "appsettings.json" file in the "FlightRecorder.Manager" folder and set the location where you want the database to be created:

{
  "ConnectionStrings": {
    "FlightRecorderDB": "Data Source=<path>/flightrecorder.db"
  }
}

Open a terminal window, change to your working copy of the FlightRecorder.Manager project and enter the following commands to build the project and show its "usage" message:

dotnet run

The output from the "run" command should look similar to the following:

Flight Recorder Database Management 1.5.0.0

Usage:

[1] FlightRecorder.Manager add username password
[2] FlightRecorder.Manager setpassword username password
[3] FlightRecorder.Manager delete username
[4] FlightRecorder.Manager import airports|sightings csv_file_path
[5] FlightRecorder.Manager export sightings|airports|airlines|locations|manufacturers|flights csv_file_path
[6] FlightRecorder.Manager update
[7] FlightRecorder.Manager lookup flight|airport|aircraft

Create the database in the location specified in the appsettings.json file by running the following command:

dotnet run -- update

The output should be similar to the following:

Flight Recorder Database Management 1.5.0.0
Applied the latest database migrations

Configuring Your Application Settings File

Any application that wishes to use the database should have an appsettings.json file configured as described above with the "FlightRecorderDB" connection string pointing to a copy of the database created above.