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 and User Attribute Implementation

The contents of the User table are used to authenticate users via the REST API.

Table	Description
User	List of registered users and password hashes
User_Attribute	Available per-user attributes
User_Attribute_Value	Maps users to values for their user attributes

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.6.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.6.0.0
Applied the latest database migrations

Add a user to the database using the manager with the "add" option, providing the required username and password:

dotnet run -- add someuser <password>

The output should look similar to the following:

Flight Recorder Database Management 1.6.0.0
Added user someuser

Airport IATA Codes

• When entering sighting details, the application expects the embarkation and destination airport IATA codes to be in the database
• If not, then a validation error is generated when attempting to enter the flight details
• Airport IATA codes can be populated in one of two ways:
  • Manually, via the data maintenance pages ("Data Maintenance > Countries" and "Data Maintenance > Airports")
  • Using the command line tool to upload a CSV file containing the codes
• The second option is more convenient as it can be used to set up the airport codes for which flights are likely to be encountered, initially
• Data import is covered in the section on "CSV Import"

Per-User Attributes

The application supports the following per-user attributes:

Attribute Name	Purpose
Default Location	Default location for adding sightings

There is currently no UI for maintaining user attributes, so they must be added directly in the SQLite database:

• Create the user(s)
• Set up the locations using the data maintenance pages in the UI
• Insert a record in the "USER_ATTRIBUTE" table as follows:

INSERT INTO USER_ATTRIBUTE (Id, Name) VALUES (1, 'Default Location');

• Insert a record for each user that should have a default location, as follows:

INSERT INTO USER_ATTRIBUTE_VALUE(Id, User_Id, User_Attribute_Id, Value)
VALUES (id, user_id, 1, location_id);

• Where:

Place Holder	Description
id	An integer starting at 1 and increasing by 1 for each record inserted
user_id	The ID of the user (from the USER table) with which the attribute value is associated
location_id	The ID of the location to be set as the default for the user