Labs/Bespin/DesignDocs/Collaboration/Database

Even though we're using sqlalchemy, I've written out the SQL. The point of writing this out in detail is to force me to think about exactly how it's going to work. I'm more at home with SQL and didn't need the distractions. I'm sure it's easy to port ;-)

users

We need to alter the 'users' table as follows:

ALTER TABLE users ADD COLUMN everyone_viewable BOOLEAN;

This allows us to track if the user is allowing everyone to use the view command on them.

connections

The 'connections' table records who is following whom

CREATE TABLE connections (
    followed_id INTEGER NOT NULL, 
    following_id INTEGER NOT NULL,
    followed_viewable BOOLEAN, 

    PRIMARY KEY (followed_id, following_id), 
    FOREIGN KEY(followed_id) REFERENCES users (id) ON DELETE cascade, 
    FOREIGN KEY(following_id) REFERENCES users (id) ON DELETE cascade

);

groups

The 'groups' table records a list of groups created by a user

CREATE TABLE groups (
    id INTEGER NOT NULL,
    owner_id INTEGER NOT NULL,
    name VARCHAR(128),
    owner_viewable BOOLEAN,

    PRIMARY KEY (id), 
    FOREIGN KEY(owner_id) REFERENCES users (id) ON DELETE cascade, 
    UNIQUE (owner_id, name)
);

group_memberships

The 'group_memberships' table records a list of the members of a group

CREATE TABLE group_memberships (
    group_id INTEGER NOT NULL, 
    user_id INTEGER NOT NULL, 

    PRIMARY KEY (group_id, user_id), 
    FOREIGN KEY(group_id) REFERENCES groups (id) ON DELETE cascade, 
    FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE cascade
);

user_sharing and group_sharing

The 'user_sharing' and 'group_sharing' tables record the ways in which a project is shared amongst users/groups. It replaces a table called 'members'

CREATE TABLE user_sharing (
    id INTEGER NOT NULL, 
    owner_id INTEGER, 
    project_name VARCHAR(128), 
    invited_user_id INTEGER, 
    edit BOOLEAN, 
    loadany BOOLEAN, 

    PRIMARY KEY (id), 
    FOREIGN KEY(owner_id) REFERENCES users (id) ON DELETE cascade,
    FOREIGN KEY(invited_user_id) REFERENCES users (id) ON DELETE cascade
    UNIQUE (owner_id, project_name, invited_user_id),
);

CREATE TABLE group_sharing (
    id INTEGER NOT NULL, 
    owner_id INTEGER, 
    project_name VARCHAR(128), 
    invited_group_id INTEGER, 
    edit BOOLEAN, 
    loadany BOOLEAN, 

    PRIMARY KEY (id), 
    FOREIGN KEY(owner_id) REFERENCES users (id) ON DELETE cascade, 
    FOREIGN KEY(invited_group_id) REFERENCES groups (id) ON DELETE cascade
    UNIQUE (owner_id, project_name, invited_group_id), 
);