database_helper.py

import os

from __init__ import app

import json
from datetime import datetime
from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow, fields

basedir = os.path.abspath(os.path.dirname(__file__))

# Database
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'db.sqlite')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)
ma = Marshmallow(app)

# EditorSettings
class EditorSettings(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    fontSize = db.Column(db.Integer, nullable=False)
    margin = db.Column(db.Integer, nullable=False)
    tabSize = db.Column(db.Integer, nullable=False)

    def __init__(self, font_size, margin, tab_size):
        self.fontSize = font_size
        self.margin = margin
        self.tabSize = tab_size

class EditorSettingsSchema(ma.Schema):
    class Meta:
        model = EditorSettings
        fields = ('id', 'fontSize', 'margin', 'tabSize')

editor_settings_schema = EditorSettingsSchema(strict=True)

# User
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(100), unique=True, nullable=False)
    username = db.Column(db.String(50), unique=True, nullable=False)
    password = db.Column(db.Text, nullable=False)
    editorSettingsId = db.Column(db.Integer, db.ForeignKey(EditorSettings.id))
    editorSettings = db.relationship("EditorSettings")

    def __init__(self, email, username, password):
        self.email = email
        self.username = username
        self.password = password

class UserSchema(ma.Schema):
    editorSettings = ma.Nested(EditorSettingsSchema)
    class Meta:
        model = User
        fields = ('id', 'email', 'username', 'editorSettingsId', 'editorSettings')

user_schema = UserSchema(strict=True)
users_schema = UserSchema(many=True, strict=True)

# Project
class Project(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(50), nullable=False)
    creatorId = db.Column(db.Integer, db.ForeignKey(User.id), nullable=False)
    creator = db.relationship('User')
    created = db.Column(db.String(50), default=datetime.now, nullable=False)
    edited = db.Column(db.String(50), onupdate=datetime.now)
    archived = db.Column(db.Boolean, default=False, nullable=False)

    def __init__(self, title, creator_id, created=None, edited=None, archived=False):
        self.title = title
        self.creatorId = creator_id
        self.created = created
        self.edited = edited
        self.archived = archived

class ProjectSchema(ma.Schema):
    creator = ma.Nested(UserSchema)
    class Meta:
        model = Project
        fields = ('id', 'title', 'creatorId', 'creator', 'created', 'edited', 'archived')

project_schema = ProjectSchema(strict=True)
projects_schema = ProjectSchema(many=True, strict=True)

# File
class File(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    projectId = db.Column(db.Integer, db.ForeignKey(Project.id), nullable=False)
    project = db.relationship('Project', lazy='subquery')
    name = db.Column(db.String(100), nullable=False)
    isFolder = db.Column(db.Boolean, nullable=False)
    parent = db.Column(db.String(100))
    content = db.Column(db.Text)

    def __init__(self, project_id, name, is_folder, parent, content):
        self.projectId = project_id
        self.name = name
        self.isFolder = is_folder
        self.parent = parent
        self.content = content

class FileSchema(ma.Schema):
    project = ma.Nested(ProjectSchema)
    class Meta:
        model = File
        fields = ('id', 'projectId', 'project', 'name', 'isFolder', 'parent', 'content')

file_schema = FileSchema(strict=True)
files_schema = FileSchema(many=True, strict=True)

# Collaborator
class Collaborator(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    projectId = db.Column(db.Integer, db.ForeignKey(Project.id), nullable=False)
    project = db.relationship('Project', lazy='subquery')
    userId = db.Column(db.Integer, db.ForeignKey(User.id), nullable=False)
    user = db.relationship('User', lazy='subquery')
    permission = db.Column(db.Text, nullable=False)

    def __init__(self, project_id, user_id, permission):
        self.projectId = project_id
        self.userId = user_id
        self.permission = permission

class CollaboratorSchema(ma.Schema):
    project = ma.Nested(ProjectSchema)
    user = ma.Nested(UserSchema)
    class Meta:
        fields = ('id', 'projectId', 'project', 'userId', 'user', 'permission')

collaborator_schema = CollaboratorSchema(strict=True)
collaborators_schema = CollaboratorSchema(many=True, strict=True)

# Message
class Message(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    projectId = db.Column(db.Integer, db.ForeignKey(Project.id), nullable=False)
    project = db.relationship('Project')
    authorId = db.Column(db.Integer, db.ForeignKey(User.id), nullable=False)
    author = db.relationship('User')
    message = db.Column(db.Text)
    time = db.Column(db.String(50), default=datetime.now, nullable=False)

    def __init__(self, project_id, author_id, message, time=None):
        self.projectId = project_id
        self.authorId = author_id
        self.message = message
        self.time = time

class MessageSchema(ma.Schema):
    project = ma.Nested(ProjectSchema)
    author = ma.Nested(UserSchema)
    class Meta:
        fields = ('id', 'projectId', 'project', 'authorId', 'author', 'message', 'time')

message_schema = MessageSchema(strict=True)
messages_schema = MessageSchema(many=True, strict=True)

### USER ###
def add_user(new_user):
    db.session.add(new_user)
    db.session.commit()

def get_users():
    all_users = User.query.all()
    result = users_schema.dump(all_users)
    return result.data

def get_user(id):
    user = User.query.get(id)
    return user

def update_user(id, updated_user):
    user = User.query.get(id)
    user.email = updated_user.email
    user.username = updated_user.username
    user.password = updated_user.password
    user.editorSettingsId = updated_user.editorSettingsId
    db.session.commit()
    return user

def delete_user(id):
    user = User.query.get(id)
    db.session.delete(user)
    db.session.commit()
    return user

def delete_all_users():
    num_users_deleted = db.session.query(User).delete()
    db.session.commit()
    return num_users_deleted

def is_user(user):
    return user != None and 'id' in user.keys() and bool(User.query.get(user['id']))

def is_user_id(userId):
    return userId != None and bool(User.query.get(userId))

def is_unregistered_email(email):
    return not bool(get_user_from_email(email))

def is_unregistered_username(username):
    return not bool(get_user_from_username(username))

def get_user_from_email(email):
    return User.query.filter_by(email=email).first()

def get_user_from_username(username):
    return User.query.filter_by(username=username).first()

def get_username_from_email(email):
    return get_user_from_email(email).username

def is_valid_email_password(email, password):
    user = get_user_from_email(email)
    valid_password = user.password if user else None
    return password != None and password == valid_password

def is_valid_username_password(username, password):
    user = get_user_from_username(username)
    valid_password = user.password if user else None
    return password != None and password == valid_password

def change_password(username, newPassword):
    user = get_user_from_username(username)
    user.password = newPassword
    db.session.commit()
    return user

### EDITORSETTINGS ###
def add_editor_settings(editor_settings):
    db.session.add(editor_settings)
    db.session.commit()

def get_editor_settings(user_id):
    return EditorSettings.query.filter_by(userId=user_id).first()

def update_editor_settings(updated_editor_settings):
    editor_settings = EditorSettings.query.get(updated_editor_settings.id)
    editor_settings.fontSize = updated_editor_settings.fontSize
    editor_settings.margin = updated_editor_settings.margin
    editor_settings.tabSize = updated_editor_settings.tabSize
    db.session.commit()
    return editor_settings

def delete_editor_settings(id):
    editor_settings = EditorSettings.query.get(id)
    db.session.delete(editor_settings)
    db.session.commit()
    return editor_settings

def delete_all_editor_settings():
    num_editor_settings_deleted = db.session.query(EditorSettings).delete()
    db.session.commit()
    return num_editor_settings_deleted

### PROJECT ###
def add_project(project):
    db.session.add(project)
    db.session.commit()

def get_projects():
    all_projects = Project.query.all()
    result = projects_schema.dump(all_projects)
    return result.data

def get_project(id):
    project = Project.query.get(id)
    return project

def update_project(updated_project):
    project = Project.query.get(updated_project.id)
    project.title = updated_project.title
    project.creatorId = updated_project.creatorId
    project.archived = updated_project.archived
    db.session.commit()
    return project

def delete_project(id):
    project = Project.query.get(id)
    db.session.delete(project)
    db.session.commit()
    return project

def delete_user_projects(user_id):
    """ Deletes all projects owned by user with id. """
    user = User.query.get(user_id)
    user_projects_query = Project.query.filter_by(creatorId=user_id)

    user_projects = user_projects_query.all()
    for project in user_projects:
        delete_project_files(project.id)
        delete_project_collaborators(project.id)
        delete_project_messages(project.id)
        delete_project(project.id)

    num_projects_deleted = user_projects_query.delete()
    db.session.commit()
    return num_projects_deleted

def delete_all_projects():
    num_projects_deleted = db.session.query(Project).delete()
    db.session.commit()
    return num_projects_deleted

### FILE ###
def add_file(file):
    db.session.add(file)
    db.session.commit()

def get_file(id):
    return File.query.get(id)

def get_files():
    all_files = File.query.all()
    result = files_schema.dump(all_files)
    return result.data

def get_project_files(project_id):
    project_files = File.query.filter_by(projectId=project_id).all()
    result = files_schema.dump(project_files)
    return result.data

def update_file(updated_file):
    file = File.query.get(updated_file.id)
    file.projectId = updated_file.projectId
    file.name = updated_file.name
    file.isFolder = updated_file.isFolder
    file.parent = updated_file.parent
    file.content = updated_file.content
    db.session.commit()
    return file

def delete_file(file_id):
    file = File.query.filter_by(id=file_id).first()
    db.session.delete(file)
    db.session.commit()
    return file

def delete_project_files(project_id):
    num_project_files_deleted = File.query.filter_by(projectId=project_id).delete()
    db.session.commit()
    return num_project_files_deleted

def delete_all_files():
    num_files_deleted = db.session.query(File).delete()
    db.session.commit()
    return num_files_deleted

### COLLABORATOR ###
def add_collaborator(collaborator):
    db.session.add(collaborator)
    db.session.commit()

def get_collaborator(id):
    collaborator = Collaborator.query.get(id)
    return collaborator

def get_collaborators():
    all_collaborators = Collaborator.query.all()
    result = collaborators_schema.dump(all_collaborators)
    return result.data

def get_project_collaborators(project_id):
    project_collaborators = Collaborator.query.filter_by(projectId=project_id).all()
    result = collaborators_schema.dump(project_collaborators)
    return result.data

def update_collaborator(updated_collaborator):
    collaborator = Collaborator.query.get(updated_collaborator.id)
    collaborator.projectId = updated_collaborator.projectId
    collaborator.userId = updated_collaborator.userId
    collaborator.permission = updated_collaborator.permission
    db.session.commit()
    return collaborator

def delete_collaborator(collaborator_id):
    collaborator = Collaborator.query.filter_by(id=collaborator_id).first()
    db.session.delete(collaborator)
    db.session.commit()
    return collaborator

def delete_project_collaborators(project_id):
    num_project_collaborators_deleted = Collaborator.query.filter_by(projectId=project_id).delete()
    db.session.commit()
    return num_project_collaborators_deleted

def delete_all_collaborators():
    num_collaborators_deleted = db.session.query(Collaborator).delete()
    db.session.commit()
    return num_collaborators_deleted

### Message ###
def add_message(message):
    db.session.add(message)
    db.session.commit()

def get_message(id):
    message = Message.query.get(id)
    return message

def get_messages():
    all_messages = Message.query.all()
    result = messages_schema.dump(all_messages)
    return result.data

def get_project_messages(project_id):
    project_messages = Message.query.filter_by(projectId=project_id).all()
    result = messages_schema.dump(project_messages)
    return result.data

def update_message(updated_message):
    message = Message.query.get(updated_message.id)
    message.projectId = updated_message.projectId
    message.autorId = updated_message.autorId
    message.time = updated_message.time
    db.session.commit()
    return message

def delete_project_messages(project_id):
    num_project_messages_deleted = Message.query.filter_by(projectId=project_id).delete()
    db.session.commit()
    return num_project_messages_deleted

def delete_all_messages():
    num_messages_deleted = db.session.query(Message).delete()
    db.session.commit()
    return num_messages_deleted