Database Models

Overview

Defines SQLAlchemy models for the tables and the main business logic for:

• Users
• Virtual Machines
• Bookmark

Prerequisites

The database must be configured and working.

Tables

The models for the tables are in backend/models:

• Base Model: This is the SQLAlchemy base model used by all the tables.
• User: Stores user-related information, including login credentials and roles.
  • In a one-to-many relationship with VirtualMachine and Bookmark.
• VirtualMachine: Represents virtual machines owned by users, with encryption methods for secure handling of sensitive data like passwords and SSH keys.
  • The user_id is the foreign key to the user that has created the VM.
  • The assigned_to is the username of the user assigned to the VM.
• Bookmark: Contains links saved by users, categorized by name and linked to user accounts.
  • The user_id is the foreign key to the user that has created the bookmark.

Schema

Below is a diagram of the main database tables and their relationships:

classDiagram
    class User {
        + Integer id
        + String username
        + String password
        + String email
        + String first_name
        + String last_name
        + String role
        + Boolean disabled
    }
    style User fill:#2a5d3399

    class VirtualMachine {
        + Integer id
        + String name
        + String host
        + Integer port
        + String username
        + String password
        + LargeBinary ssh_key
        + Boolean shared
        + Integer user_id
        + String assigned_to
    }
    style VirtualMachine fill:#21385b99

    class Bookmark {
        + Integer id
        + String name
        + String link
        + Integer user_id
    }
    style Bookmark fill:#5f3c2599

    User "1" --> "0..*" VirtualMachine : owns
    User "1" --> "0..*" Bookmark : owns

Loading

Querying the database for objects

SQLAlchemy provides a powerful querying API, but remembering all query syntax can be cumbersome. To simplify common operations, each table class offers some predefined helper methods such as get_by_id(), find_all(), and more.

Some methods have specific filters that can be helpful.

with get_db() as db:
    # Get all the virtual machines of a certain user
    all_vms_of_user = VirtualMachine.find_by_user_name(db, "jsmith")
    
    # Get all the bookmarks in the database, except for a certain user
    all_bookmarks = Bookmark.find_all(db, exclude_user_name="jsmith")