I am making a flask app where I have 2 tables in a data base.

1. User
2. File

A file has a column called fileOwnerId but I want to add another called allowed viewers. How would I implement such a situation.

I've had 2 ideas:

1. Is to make a column called validViewers that would store an array of valid viewer but I don't think you can store an array in a db.
2. Is to make a foreign key in User that would connect to only certain File rows. The problem is I don't think you can choose which files would apply to which user.

If theres any other ideas you guys have I'm all ears. Ty in advance

from flask_login import UserMixin
from . import db

class User(db.Model, UserMixin):
    
    id = db.Column(db.Integer, primary_key = True)
    userUuid = db.Column(db.String(1000), unique = True)
    email = db.Column(db.String(1000), unique = True)
    name = db.Column(db.String(1000))
    password = db.Column(db.String(1000))
    isAdmin = db.Column(db.Boolean(), default = False)
    files = db.relationship("File")
    
class File(db.Model):
    
    id = db.Column(db.Integer, primary_key = True)
    fileUuid = db.Column(db.String(1000), unique = True)
    filePath = db.Column(db.String(1000), unique = True)
    fileName = db.Column(db.String(1000))
    fileOwnerId = db.Column(db.Integer, db.ForeignKey("user.id"))

CodePudding user response：

You have 2 relationships to consider:

Users can own multiple files, 1 to Many relationship

Users can view Multiple Files and a file can be viewed by Multiple Users. ie Many to Many relationship

The first is handled by adding the owner ID to Files. The second is solved by creating as intermediate or jump table.

from flask_login import UserMixin
from . import db

class User(db.Model, UserMixin):
    
    id = db.Column(db.Integer, primary_key = True)
    userUuid = db.Column(db.String(1000), unique = True)
    email = db.Column(db.String(1000), unique = True)
    name = db.Column(db.String(1000))
    password = db.Column(db.String(1000))
    isAdmin = db.Column(db.Boolean(), default = False)
    files = db.relationship("File")

class FileAccess(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    userid = db.Column(db.Integer, db.ForeignKey("user.id"))
    fileid = db.Column(db.Integer, primary_key = True)    


class File(db.Model):
    
    id = db.Column(db.Integer, primary_key = True)
    fileUuid = db.Column(db.String(1000), unique = True)
    filePath = db.Column(db.String(1000), unique = True)
    fileName = db.Column(db.String(1000))
    fileOwnerId = db.Column(db.Integer, db.ForeignKey("user.id"))

CodePudding user response：

Define a model Role

class Role(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    permissions = db.Column(db.Integer)

    users = db.relationship('User', backref='role', lazy='dynamic')

    def __init__(self, **kwargs):
        super().__init__(**kwargs)
        if self.permissions is None:
            self.permissions = 0

    def add_permission(self, perm):
        if not self.has_permission(perm):
            self.permissions  = perm

    def remove_permission(self, perm):
        if self.has_permission(perm):
            self.permissions -= perm

    def reset_permissions(self):
        self.permissions = 0

    def has_permission(self, perm):
        return self.permissions & perm == perm

Define an enum for your permissions:

from enum import Enum

class Permission(Enum):
    READ = 1
    WRITE = 2
    SPECIFIC_PERMISSION = 4
    ANOTHER_SPECIFIC_ERMISSION = 8
    ADMIN = 16
    ...

Add role_id to your User model, and also ways to determine permissions. In this case I have added is_administrator but you can implement your own methods to check permissions.

class User(...):
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))

    def can(self, perm):
        return self.role is not None and self.role.has_permission(perm)

    def is_administrator(self):
        return self.can(Permission.ADMIN)

You can create different roles that have different permissions to handle access to not only files but other stuff.