database_create.py

#!/usr/bin/env python3

import sqlite3 as sqlite


def create_db(path):
    conn = sqlite.connect(path)
    conn.isolation_level = None  # so we don't have to use self.connection.commit()
    curs = conn.cursor()

    curs.execute(
        '''
        CREATE TABLE Groups (
        group_id INTEGER PRIMARY KEY,
        name     VARCHAR UNIQUE
                         NOT NULL
    );
    '''
    )

    curs.execute(
        '''
        CREATE TABLE Member (
        member_id INTEGER PRIMARY KEY AUTOINCREMENT,
        name      VARCHAR,
        group_id  INTEGER REFERENCES Groups (group_id) ON DELETE CASCADE,
        UNIQUE (
            name,
            group_id
        )
    );
    '''
    )

    curs.execute(
        '''
        CREATE TABLE Distribution (
        distribution_id INTEGER  PRIMARY KEY AUTOINCREMENT,
        name            VARCHAR  NOT NULL,
        creation_time   DATETIME DEFAULT (CURRENT_TIMESTAMP),
        group_id                 REFERENCES Groups (group_id) ON DELETE CASCADE
    );
    '''
    )

    curs.execute(
        '''
        CREATE TABLE Team (
        team_id         INTEGER PRIMARY KEY AUTOINCREMENT,
        name            VARCHAR,
        distribution_id INTEGER REFERENCES Distribution (distribution_id) ON DELETE CASCADE
    );'''
    )

    curs.execute(
        '''
        CREATE TABLE Member_to_Team (
        member_id INTEGER REFERENCES Member (member_id) ON DELETE CASCADE,
        team_id   INTEGER REFERENCES Team (team_id) ON DELETE CASCADE,
        PRIMARY KEY (
            team_id,
            member_id
        )
    );
    '''
    )

    curs.execute(
        '''
        CREATE VIEW member_history_view as 
        SELECT t1.member_id member_id,
           t2.member_id With_member_id,
           count( * ) times_together
        FROM Member_to_Team t1
           JOIN
           Member_to_Team t2 ON t1.team_id = t2.team_id AND 
                                t1.member_id != t2.member_id
           JOIN
           Member ON t2.member_id = Member.member_id
        GROUP BY t1.member_id,
                      t2.member_id
    '''
    )