I have three tables: User, File, and UserFile
The first stored data about users including username, email, password, and all of their files.
The second stores info about files.
The third connects a user to a file in order to create a list of shared users.
class Base(sqlalchemy.orm.DeclarativeBase):
__allow_unmapped__ = True
class User(Base):
__tablename__ = 'users'
token = sqlalchemy.Column(sqlalchemy.String,
primary_key=True)
username = sqlalchemy.Column(sqlalchemy.String,
nullable=False)
email = sqlalchemy.Column(sqlalchemy.String,
nullable=False)
password = sqlalchemy.Column(sqlalchemy.String,
nullable=False)
files = sqlalchemy.orm.relationship('File',
backref='users')
bytes_used = sqlalchemy.Column(sqlalchemy.Integer,
nullable=False)
total_bytes = sqlalchemy.Column(sqlalchemy.Integer,
nullable=False)
def __init__(self,
username: str,
email: str,
password: str):
while db.query(User).filter(
User.token == (token:=secrets.token_urlsafe(32))).first():
pass
self.token = token
self.username = username
self.email = email
self.password = password
self.bytes_used = 0
self.total_bytes = 1_000_000_000 # 1 GB
class File(Base):
__tablename__ = 'files'
token = sqlalchemy.Column(sqlalchemy.String,
primary_key = True)
filename = sqlalchemy.Column(sqlalchemy.String,
nullable = False)
size = sqlalchemy.Column(sqlalchemy.Integer,
nullable = False)
data = sqlalchemy.Column(sqlalchemy.BLOB,
nullable = False)
owner = sqlalchemy.Column(sqlalchemy.String,
sqlalchemy.ForeignKey('users.token'))
share_list = sqlalchemy.orm.relationship('UserFile',
uselist = True,
backref = 'files')
def __init__(self,
filename: str,
data: bytes,
owner_token: str):
while db.query(File).filter(
File.token == (token:=secrets.token_urlsafe(32))).first():
pass
self.token = token
self.filename = filename
self.size = len(data)
self.data = data
db.query(User).filter(User.token == owner_token).first().bytes_used += self.size
self.owner = owner_token
self.share_list = []
class UserFile(Base):
__tablename__ = 'user_files'
user_token = sqlalchemy.Column(sqlalchemy.String,
sqlalchemy.ForeignKey('users.token'),
primary_key = True)
file_token = sqlalchemy.Column(sqlalchemy.String,
sqlalchemy.ForeignKey('files.token'),
primary_key = True)
def __init__(self,
user_token: str,
file_token: str):
self.user_token = user_token
self.file_token = file_token
Base.metadata.create_all(engine)
What I'm trying to do is make it so when a user is deleted, that all the files associated with it are also deleted, and any UserFile rows referencing it's token is deleted as well.
I also want to make it so that when a File is deleted that all the UserFile row referencing it's token are deleted.
I think I have to use some kind of ForeignKeyConstraint, but I haven't been able to find any solutions online.
