Database

Database Diagram

Drone Flight Log Database Diagram

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

Section	Contents
Drone Information	The drones and associated models and manufacturers held in the log
Maintenance Information	Maintenance records and "maintainers" responsible for performing the work
Location Information	The named locations for logged flights
Operator Information	Details of operators and their addresses
Flight Properties	The set of named properties that can be recorded about logged flights
Flight Information	The containing "Flight" and its associated property values
Security Implementation	Registered users for use in authentication via the web service

Drone Information

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

Table	Description
Manufacturers	Named manufacturer details
Models	Named models, each associated with a manufacturer
Drones	Named drones, each associated with a model

Maintenance Information

Each drone may have one or more maintenance records associated with it. Each record is, in turn, associated with a "maintainer" responsible for the work:

Table	Description
Maintainers	Named maintainers
MaintenanceRecords	Maintenance records, each associated with a maintainer

Location Information

Flights take place at a named location. Multiple flights may be associated with a single location:

Table	Description
Locations	Named location details

Operator Information

Named operators are associated with an address. Multiple operators may be associated with a single address:

Table	Description
Addresses	Address details
Operators	Named operators, optionally with operator and flyer registration details

Flight Properties

Each flight may have a number of named properties recorded in the log. Examples might be average wind speed, wind gust speed, temperature and so on:

Table	Description
FlightProperties	Named flight property details

The "DataType" column specifies the type of data stored against the property and its value maps onto one of the named constants in the FlightPropertyDataType enumeration:

DataType Value	Named Constant
0	String
1	Date
2	Number

Numbers are retrieved as C# Decimal values.

The "IsSingleInstance" column controls whether a given flight can have only one value for a property or may have multiple values for the same property. The business logic enforces this constraint as required.

Flight Information

Each flight is associated with a drone, a location and an operator and has one or more associated named property values:

Table	Description
Flight	The flight record
FlightPropertyValues	Property values for the flight

The "Start" and "End" columns on the flight record hold the date and time the flight started and completed, respectively.

Security Implementation

The registered users for the application are held in the FlightLogUsers table. The contents of the table are used to authenticate users via the web service.

In-Memory Database

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

Creating a 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 "DroneFlightLog.Manager" folder and set the location where you want the database to be created:

{
  "ConnectionStrings": {
    "DroneLogDb": "Data Source=<path>/droneflightlog.db"
  }
}

Open a terminal window, change to your working copy of the DroneFlightLog.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:

Drone Flight Log Database Management 1.1.0.0
Usage:
[1] DroneFlightLog.Manager add username password
[2] DroneFlightLog.Manager setpassword username password
[3] DroneFlightLog.Manager delete username
[4] DroneFlightLog.Manager update

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:

Drone Flight Log Database Management 1.1.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:

Drone Flight Log Database Management 1.1.0.0
Added user someuser

Updating an Existing Database

Caution

It's strongly recommended that you make a backup copy of the SQLite Database before applying database updates. To do this, stop the Drone Flight Log (if it's running) and make a copy of the SQLite Database file.

If a new release of the application includes new database tables or changes to existing ones, it will include one or more migrations to apply those changes to an existing database, without deleting the existing data.

The database management tool includes an option that applies migrations for you.

To use the tool, first make sure you've pulled the latest version of the repository. Edit the "appsettings.json" file in the "DroneFlightLog.Manager" folder and set the location to point to the database to be updated:

{
  "ConnectionStrings": {
    "DroneLogDb": "Data Source=<path>/droneflightlog.db"
  }
}

Open a terminal window, change to your working copy of the DroneFlightLog.Manager project and enter the following commands to apply the latest migrations:

dotnet run -- update