Database design and implementation - LassiHeikkila/taskey GitHub Wiki

Important information for Deadline 2

‼️  This chapter should be completed by Deadline 2 (see course information at Lovelace)

📑  Chapter summary In this section students must design and implement the database structure (mainly the data model).

In this section you must implement:

  • The database table structure.
  • The data models (ORM)
  • Data models access methods (if needed)
  • Populating the database using the models you have created
In this section you should aim for a high quality small implementation instead of implementing a lot of features containing bugs and lack of proper documentation.

SECTION GOALS:

  1. Understand database basics
  2. Understand how to use ORM to create database schema and populate a database
  3. Setup and configure database
  4. Implement database backend
✔️     Chapter evaluation (max 5 points) You can get a maximum of 9 points after completing this section. More detailed evaluation is provided in the evaluation sheet in Lovelace.

Database design and implementation

Database design

📑  Content that must be included in the section Describe your database. The documentation must include:
  • A name and a short description of each database model. Describe in one or two sentences what the model represents.
  • An enumeration of the attributes (columns) of each model. Each attribute must include:
    • Its type and restrictions (values that can take)
    • A short description of the attribute whenever the name is not explicit enough. E.g. If you are describing the users of a "forum", it is not necessary to explain the attributes "name", "surname" or "address"
      • because their meanings are obvious.
    • Characteristics of this attribute (e.g. if it is unique, if it contains default values)
  • Connection with other models (primary keys and foreign keys)
  • Other keys
You can use the table skeleton provided below

For this section you can use a visual tool to generate a diagram. Be sure that the digram contains all the information provided in the models. Some tools you can use include: https://dbdesigner.net/, https://www.lucidchart.com/pages/tour/ER_diagram_tool, https://dbdiffo.com/

Table descriptions

organizations

This is a table containing records for organizations, which are entities that users, machines, and task definitions belong to.

Name Type Restrictions Description Characteristics Links
id uint not null id of the row unique primary key
name string not empty name of the organization unique
created_at datetime when organization was created
updated_at datetime when organization was updated
deleted_at datetime when organization was deleted

users

This is a table containing records for users, i.e. entities representing human clients.

Name Type Restrictions Description Characteristics Links
id uint not null id of the row unique primary key
name string not empty name of the user unique
role integer not null bitmap of access roles (user|maintainer|admin|root)
organization_id uint not empty which organization this user belongs to not null foreign key to organizations table
created_at datetime when user was created
updated_at datetime when user was updated
deleted_at datetime when user was deleted

machines

This is a table containing records for machines, i,e, entities representing machine clients.

Name Type Restrictions Description Characteristics Links
id uint not null id of the row unique primary key
name string not empty name of the machine unique
description string not empty textual description of the machine to help user identify it unique
os string operating system name unique
arch string system architecture, e.g. "armv6l" or "x86_64" unique
organization_id uint not empty which organization this machine belongs to not null foreign key to organizations table
schedule_id uint not empty unique foreign key to schedules table
created_at datetime when machine was created
updated_at datetime when machine was updated
deleted_at datetime when machine was deleted

tasks

This is a table containing task definitions, i.e. what the machine clients should do.

Name Type Restrictions Description Characteristics Links
id uint not null id of the row unique primary key
name string not empty name of the task unique
description string not empty textual description of the task to help user understand it unique
content byte array what should actually be done. Application will likely use JSON, but using byte array for now
organization_id uint not empty which organization this task definition belongs to not null foreign key to organizations table
created_at datetime when task was created
updated_at datetime when task was updated
deleted_at datetime when task was deleted

schedules

This is a table containing schedule definitions, i.e. when a machine client should do something.

Name Type Restrictions Description Characteristics Links
id uint not null id of the row unique primary key
content byte array definition of the schedule. Application will likely use JSON, but using byte array for now
machine_id uint not empty which machine this schedule is for not null foreign key to machines table
created_at datetime when schedule was created
updated_at datetime when schedule was updated
deleted_at datetime when schedule was deleted

login_infos

This is a table containing login information, i.e. username and password for user accounts.

Name Type Restrictions Description Characteristics Links
id uint not null id of the row unique primary key
username string not null unique
password string not null
user_id uint not empty which user this login is for not null foreign key to users table
created_at datetime when login_info was created
updated_at datetime when login_info was updated
deleted_at datetime when login_info was deleted

user_tokens

This is a table containing user access tokens. Used for authentication when username+password is not a good fit.

Name Type Restrictions Description Characteristics Links
id uint not null id of the row unique primary key
value uuid not null unique
expiration datetime not null when will this token stop being valid
user_id uint not empty which user this token is for not null foreign key to users table
created_at datetime when token was created
updated_at datetime when token was updated
deleted_at datetime when token was deleted

machine_tokens

This is a table containing machine access tokens. Used by machine clients for authentication.

Name Type Restrictions Description Characteristics Links
id uint not null id of the row unique primary key
value uuid not null unique
expiration datetime not null when will this token stop being valid
machine_id uint not empty which machine this token is for not null foreign key to machines table
created_at datetime when token was created
updated_at datetime when token was updated
deleted_at datetime when token was deleted

records

This is a table containing log records of task execution.

Name Type Restrictions Description Characteristics Links
id uint not null id of the row unique primary key
executed_at datetime when task was executed (finished)
status integer status code / exit code of the task
output text what the task output was (e.g. what script printed)
machine_id uint not empty which machine performed the task not null foreign key to machines table
task_id uint not empty which task was performed not null foreign key to tasks table
created_at datetime when record was created
updated_at datetime when record was updated
deleted_at datetime when record was deleted

Schema diagram

Database implementation

💻     TODO: SOFTWARE TO DELIVER IN THIS SECTION The code repository must contain:
  1. The ORM models and functions
  2. A .sql dump of a database or the .db file (if you are using SQlite). You must provide a populated database in order to test your models.
  3. The scripts used to generate your database (if any)
  4. If you are using python, the requirements.txt file.
  5. A README.md file containing:
    • All dependencies (external libraries) and how to install them
    • Define database (MySQL, SQLite, MariaDB, MongoDB...) and version utilized
    • Instructions how to setup the database framework and external libraries you might have used, or a link where it is clearly explained.
    • Instructions on how to setup and populate the database.
  6. If you are using python a `requirements.txt` with the dependencies

Implementation found here. There is no script for producing example data yet.

Main executable can connect to Postgres and initialize the tables. Address is hardcoded to work with container set up with docker-compose. Adminer can then be used to see what the tables look like.

Test code sets up Postgres container and creates, queries, updates and deletes data.

Resources allocation

Task Student Estimated time
Design Lassi Heikkilä 8h
Implementation Lassi Heikkilä 16h
Documentation Lassi Heikkilä 4h
⚠️ **GitHub.com Fallback** ⚠️