class GroupMemberModel(db.Model): __modelname__ = "Group Member" __tablename__ = "group_members" group_id = db.Column(db.Integer, db.ForeignKey("groups.id"), primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey("users.id"), primary_key=True) is_lead = db.Column(db.Boolean, default=False, nullable=False) permission = db.Column(db.Integer, default=Permission.READ, nullable=False) group = db.relationship("GroupModel", backref=db.backref( "members", cascade="delete, delete-orphan")) user = db.relationship("UserModel", backref=db.backref("groups_membership", cascade="delete, delete-orphan")) @db.validates('is_lead') def validate_leader(self, key, is_lead): # We can only have one leader in a group if self.team.leader(): raise ValueError(f"{self.group.name} group already have a leader") return is_lead @property def user_role(self): return f"{self.group.name}:{'leader' if self.is_lead else 'member'}"
class LanguagesKnown(db.Model): __tablename__ = 'languages_known' id = db.Column(db.Integer, primary_key=True, nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) language_id = db.Column(db.Integer, db.ForeignKey('languages.id'), nullable=False) # Relationships user = db.relationship('Users', backref='languages_known') language = db.relationship('Languages', backref='languages_known') def __init__(self, **kwargs): self.id = kwargs.get('id') self.user_id = kwargs.get('user_id') self.language_id = kwargs.get('language_id') def __repr__(self): return '<LanguagesKnown -> id: {}, user_id: {}, language_id: {}'.format( self.id, self.user_id, self.language_id) def to_dict(self): return dict(id=self.id, user_id=self.user_id, language_id=self.language_id)
class Participants(db.Model): __tablename__ = "participants" id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.ForeignKey(User.id)) schedule_id = db.Column(db.ForeignKey(Schedule.schedule_id)) schedule = db.relationship('Schedule', foreign_keys='Participants.schedule_id') participant = db.relationship('User', foreign_keys='Participants.user_id') def __init__(self, schedule_id, user_id): self.schedule_id = schedule_id self.user_id = user_id def commit(self): db.session.add(self) db.session.commit() @staticmethod def clash(startTime, endTime, givenStartTime, givenEndTime): if startTime <= givenStartTime: return endTime >= givenStartTime else: return startTime <= givenEndTime @staticmethod def is_available(email, start_time, end_time, curr_schedule_id=None): """""" user_id = db.session.query( User.id).filter(User.email == email).scalar() print("User ID:", user_id) schedules = db.session.query(Participants.schedule_id).filter( Participants.user_id == user_id).all() print("Schedules:", list(schedules)) clashing = False if schedules is not None: for schedule_id in schedules: if curr_schedule_id is not None and schedule_id[ 0] != curr_schedule_id: schedule = db.session.query(Schedule).filter( Schedule.schedule_id == schedule_id[0]).scalar() if schedule is not None: print('if schedule not none', schedule.start_time, schedule.end_time) clashing = clashing or Participants.clash(schedule.start_time, schedule.end_time,\ start_time, end_time) print(clashing) return (not clashing), user_id @staticmethod def delete_schedule_participants(schedule_id): db.session.query(Participants).filter( Participants.schedule_id == schedule_id).delete() db.session.commit()
class Book(db.Model): __tablename__ = 'books' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(255), nullable=False) author_id = db.Column(db.Integer, db.ForeignKey('authors.id'), nullable=False) genre_id = db.Column(db.Integer, db.ForeignKey('genres.id')) year_of_writing = db.Column(db.String(255)) pages = db.Column(db.String(255)) publish_house_id = db.Column(db.Integer, db.ForeignKey('publish_houses.id')) def __repr__(self): return self.title
class Ticket(db.Model): __tablename__ = 'tickets' id = db.Column(db.Integer, primary_key=True, unique=True, autoincrement=True) email = db.Column(db.String(), primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey("users.id")) user = db.relationship("User") submitted_at = db.Column(db.DateTime, default=datetime.datetime.now) code = db.Column(db.Text, unique=True) notes = db.Column(db.Text, default="") def __init__(self, email, notes, user, ticketNumber=""): self.email = email self.notes = notes self.user = user self.generateCode() def generateCode(self): self.code = secrets.token_hex(16) def json(self): return { "email": self.email, "code": self.code, "id": self.id, "user": self.user.description, "notes": self.notes }
class Availabilities(db.Model): __tablename__ = 'availabilities' id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) day_of_week = db.Column(db.Integer, nullable=False) from_time = db.Column(db.Integer, nullable=False) to_time = db.Column(db.Integer, nullable=False) # Relationships user = db.relationship('Users', backref='availabilities') def __init__(self, user_id, day_of_week, from_time, to_time): self.user_id = user_id self.day_of_week = day_of_week self.from_time = from_time self.to_time = to_time def __repr__(self): return '<Availabilities -> id: {}, user_id: {}, day_of_week: {}, from_time: {}, to_time: {}'.format( self.id, self.user_id, self.day_of_week, self.from_time, self.to_time) def to_dict(self): return dict(id=self.id, user_id=self.user_id, day_of_week=self.day_of_week, from_time=self.from_time, to_time=self.to_time)
class Log(db.Model): __tablename__ = 'logs' id = db.Column(db.Integer, primary_key=True, autoincrement=True) action = db.Column(db.String()) value = db.Column(db.String()) attendee_id = db.Column(db.Integer, db.ForeignKey("attendees.id")) attendee = db.relationship("Attendee") created_at = db.Column(db.DateTime, default=datetime.datetime.now)
class Requests(db.Model): __tablename__ = 'requests' id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) other_user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) from_time = db.Column(db.DateTime, nullable=False) to_time = db.Column(db.DateTime, nullable=False) req_accepted = db.Column(db.Boolean, nullable=False) req_confirmed = db.Column(db.Boolean, nullable=False) def __init__(self, user_id, other_user_id, from_time, to_time, req_accepted=False, req_confirmed=False): self.user_id = user_id self.other_user_id = other_user_id self.from_time = from_time self.to_time = to_time self.req_accepted = req_accepted self.req_confirmed = req_confirmed # Relationships # TODO figure out have relationships with two User objects def __repr__(self): return '<Requests -> id: {}, user_id: {}, other_user_id: {}, from_time: {}, to_time: {}, req_accepted: {}, req_confimed: {}'.format( self.id, self.user_id, self.other_user_id, self.from_time, self.to_time, self.req_accepted, self.req_confirmed) def to_dict(self): user = Users.query.get(self.user_id) return dict(id=self.id, user_id=self.user_id, other_user_id=self.other_user_id, from_time=self.from_time, to_time=self.to_time, req_accepted=self.req_accepted, req_confirmed=self.req_confirmed, user_name=user.user_name)
class Genre(db.Model): __tablename__ = 'genres' id = db.Column(db.Integer, primary_key=True) genre = db.Column(db.String(255), nullable=False, unique=True) books = db.relationship('Book', backref='genre') creator_id = db.Column(db.Integer, db.ForeignKey('users.id')) def __repr__(self): return self.genre
class Attendee(db.Model): __tablename__ = 'attendees' id = db.Column(db.Integer, primary_key=True, autoincrement=True) checkin_status = db.Column(db.Integer, default=0) updated_at = db.Column(db.DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now) scan_value = db.Column(db.String(60), nullable=True) scan_value_others = db.Column(db.Text, nullable=True) name = db.Column(db.String(40)) email = db.Column(db.String(60)) school = db.Column(db.String(100)) # Tags are separated by `;` tags = db.Column(db.Text, default=";") notes = db.Column(db.Text, default="") # 0 for participant, 10 for mentor, 11 for sponsor type = db.Column(db.Integer, default=0) event_id = db.Column(db.Integer, db.ForeignKey('events.id')) event = db.relationship("Event", back_populates="attendees") logs = db.relationship("Log", back_populates="attendee") def __init__(self, event, name, scan_value, email, school, tags=";", checkin_status=0, notes="", type=0, scan_value_others=";"): self.name = name self.scan_value = scan_value self.email = email self.school = school self.type = type self.event = event self.tags = tags self.checkin_status = checkin_status self.notes = notes self.scan_value_others = scan_value_others def as_dict(self): return { c.name: str(getattr(self, c.name)) if getattr(self, c.name) is not None else None for c in self.__table__.columns }
class Author(db.Model): __tablename__ = 'authors' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255), nullable=False, unique=True) direction = db.Column(db.String(255)) date_of_birth = db.Column(db.String(255)) books = db.relationship('Book', backref='author') creator_id = db.Column(db.Integer, db.ForeignKey('users.id')) def __repr__(self): return self.name
class PublishHouse(db.Model): __tablename__ = 'publish_houses' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255), nullable=False, unique=True) address = db.Column(db.String(255)) phone_num = db.Column(db.String(20)) website = db.Column(db.String(255)) books = db.relationship('Book', backref='publish_house') creator_id = db.Column(db.Integer, db.ForeignKey('users.id')) def __repr__(self): return self.name
class Tag (BaseModel, db.Model): """Model for movies table""" __tablename__ = "frametag" id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) movieID = db.Column(db.Integer, db.ForeignKey('movies.id'), nullable=True) fn = db.Column(db.Integer, nullable=True) tag = db.Column(db.String(16), nullable=True) def __init__(self, movieID, fn, tag): super().__init__() self.movieID = movieID self.fn = fn self.tag = tag
class UserModel(BaseModel): __modelname__ = "User" __tablename__ = "users" first_name = db.Column(db.String(255), nullable=False) last_name = db.Column(db.String(255)) username = db.Column(db.String(100), nullable=False, unique=True) password_hash = db.Column(db.String(150), nullable=False) role_id = db.Column(db.Integer, db.ForeignKey("roles.id")) @classmethod def get_username(cls, username): return cls.query.filter_by(username=username).first() @property def fullname(self): return f"{self.first_name} {self.last_name}" def can(self, permission): return self.role is not None and \ (self.role.permission and permission) == True def is_admin(self): return self.can(Permission.ADMINISTRATOR) @property def password(self): raise AttributeError('`password` is not a readable attribute') @password.setter def password(self, password): self.password_hash = generate_password_hash(password) def verify_password(self, password): return check_password_hash(self.password_hash, password) # { # "sub": 1 #userid, # "role": "User", # "groups": ["corecommerce:lead", "travel:member"], # "iat": "23/12/1995", # "exp": "29/30/1998" # } # @jwt_required # @jwt_role("Administrator") # @jwt_any_roles(["User", "Administrator"])
class Client(db.Model): __tablename__ = 'clients' id = db.Column(db.Integer, primary_key=True, autoincrement=True) user_id = db.Column(db.Integer, db.ForeignKey("users.id")) user = db.relationship("User") token = db.Column(db.String(), primary_key=True, unique=True) token_expired_at = db.Column( db.DateTime, default=lambda _: (datetime.datetime.now() + datetime.timedelta(days=5))) token_created_at = db.Column(db.DateTime, default=datetime.datetime.now) def __init__(self, user, token): self.user = user self.token = token
class ContentElement(BaseModel, db.Model): """Model for content elements table""" __tablename__ = "contentelement" id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) uuid = db.Column(db.String(128), nullable=True) documentID = db.Column(db.Integer, db.ForeignKey('document.id'), nullable=True) contentType = db.Column(db.String(16), nullable=True) content = db.Column(db.String(), nullable=True) def __init__(self, uuid, documentID, contentType, content): super().__init__() self.uuid = uuid self.documentID = documentID self.contentType = contentType self.content = content
class Job(db.Model, CRUDMixin): """ Задача """ __tablename__ = 'jobs' id = db.Column(db.Integer, primary_key=True) group_id = db.Column(db.Integer, db.ForeignKey(JobGroup.id)) state = db.Column(db.Enum(JobState), default=JobState.RUNNING) start_time = db.Column(db.DateTime, default=datetime.now) end_time = db.Column(db.DateTime, nullable=True) def __repr__(self) -> str: return f'Job {self.id} state: {self.state}' @property def is_stopped(self) -> bool: return self.state in [JobState.CANCELED, JobState.COMPLETED] @staticmethod def is_valid_state(value: Any) -> bool: states = [item.value for item in JobState] return value in states def start(self) -> None: self.update(state=JobState.COMPLETED, end_time=datetime.now()) logger.debug(f'Job {self.id} in state {self.state}') scheduler.remove_job(str(self.id)) def to_dict(self) -> dict: return { 'id': self.id, 'group_id': self.group_id, 'state': self.state, 'start_time': self.start_time, 'end_time': self.end_time }
from server.app import db import datetime from server.helpers.passwords import * eventUsers = db.Table( 'eventusers', db.Column('user_id', db.Integer, db.ForeignKey('users.id'), primary_key=True), db.Column('event_id', db.Integer, db.ForeignKey('events.id'), primary_key=True)) class User(db.Model): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True, autoincrement=True) username = db.Column(db.String(20), unique=True, nullable=False) password = db.Column(db.String(), nullable=False) name = db.Column(db.String(40)) is_admin = db.Column(db.Boolean(), default=False) events = db.relationship("Event", secondary=eventUsers, lazy='dynamic') def __init__(self, username, password, name, is_admin): self.username = username self.password = hash_password(password) self.name = name
class PartyParticipationModel(db.Model): __tablename__ = "party_participation" id = db.Column(db.Integer, primary_key=True) #has the host responded hostResponse = db.Column(db.Integer, default=0) #has the client responded clientResponse = db.Column(db.Integer, default=0) # relations partyId = db.Column(db.Integer, db.ForeignKey('party.id')) clientId = db.Column(db.Integer, db.ForeignKey('user.id')) # back ref user = relationship("UserModel") party = relationship("PartyModel") UNKNOWN = 0 ACCEPTED = 1 REJECTED = 2 def addPartyParticipation(self): results = PartyParticipationModel.query.filter_by( party=self.party, user=self.user).all() assert len( results ) <= 1 # for some reason, doing the query sets the state of self to persistent db.session.add(self) db.session.commit() def update(self): try: PartyParticipationModel.query.filter_by(id=self.id).one() except: # TODO make better error handling raise db.session.commit() return self @classmethod def find_by_id(cls, id): return PartyParticipationModel.query.filter_by(id=id).one() @classmethod def delete_by_ids(cls, party, user): results = PartyParticipationModel.query.filter_by(party=party, user=user).all() for result in results: db.session.delete(result) @classmethod def find_by_ids(cls, partyId, clientId): logger.debug("party, user: %s, %s", partyId, clientId) return PartyParticipationModel.query.filter_by( partyId=partyId, clientId=clientId).all() @classmethod def find_participants_by_party_id(cls, partyId): return cls.query.filter_by(partyId=partyId).all() @classmethod def find_participants_by_name(cls, party_name): return cls.query.filter_by(party_name=party_name).all() @classmethod def count_participants_by_id(cls, partyId): return cls.find_participants_by_party_id(partyId).count() @classmethod def count_participants_by_name(cls, partyId): return cls.find_participants_by_name(partyId).count() @classmethod def count_coming(cls, partyId): return cls.accepting_users(partyId).count() @classmethod def count_decline(cls, partyId): return cls.declining_users(partyId).count() @classmethod def declining_users(cls, partyId): return cls.query.filter_by(partyId=partyId, clientAccepted=False).all() @classmethod def accepting_users(cls, partyId): return cls.query.filter_by(partyId=partyId, clientAccepted=True).all() @classmethod def count_replies(cls, partyId): return cls.query.filter_by(partyId=partyId).count()
from server.app import db shops_malls = db.Table( 'shops_malls', db.Column('shop_id', db.Integer, db.ForeignKey('shop.id', ondelete='SET NULL'), nullable=True), db.Column('mall_id', db.Integer, db.ForeignKey('mall.id', ondelete='SET NULL'), nullable=True)) class Shop(db.Model): id = db.Column(db.Integer, primary_key=True) Type = db.Column(db.String(140)) Name = db.Column(db.String(140), unique=True) malls = db.relationship('Mall', secondary=shops_malls, backref=db.backref( 'shops', lazy='dynamic', )) def __repr__(self): return f'< {self.Name} >' class Mall(db.Model):
class PartyModel(db.Model): __tablename__ = 'party' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(120), unique=True) latitude = db.Column(db.Float(5), default=0.0) longitude = db.Column(db.Float(5), default=0.0) description = db.Column(db.String, default="") seats = db.Column(db.Integer, default=1) teaching = db.Column(db.Boolean, default=False) cousin = db.Column(db.String(100), default="") date = db.Column(db.DateTime, default=datetime.datetime.now()) image = db.Column(db.String(100), default='') # relations host_id = db.Column(db.Integer, db.ForeignKey('user.id')) host = relationship("UserModel", back_populates='hosted_parties') def addParty(self): try: db.session.add(self) db.session.commit() except exc.IntegrityError as exception: db.session.rollback() if "UNIQUE constraint failed: party.name" in str(exception): raise PartyException("Party already exists") else: raise exception def update(self): try: PartyModel.query.filter_by(id=self.id, name=self.name).one() except: # TODO make better error handling raise db.session.commit() return self @classmethod def find_by_name(cls, name): return cls.query.filter_by(name=name).one() @classmethod def find_by_id(cls, id): return cls.query.filter_by(id=id).one() @classmethod def find_by_host(cls, host_id): return cls.query.filter_by(host_id=host_id).all() @classmethod def find_by_range(cls, rangeDict): rangeDict["latitude"]["min"] return cls.query.filter( (cls.latitude >= rangeDict["latitude"]["min"]) & (cls.latitude <= rangeDict["latitude"]["max"]) & (cls.longitude >= rangeDict["longitude"]["min"]) & (cls.longitude <= rangeDict["longitude"]["max"])) @staticmethod def remove(party): try: db.session.delete(party) db.session.commit() return {"Message": "Party {} has been removed".format(party.name)} except Exception as error: raise error return {'message': 'something gone wrong!'}, 500
class RecordModel(BaseModel): __modelname__ = "DNS Record" __tablename__ = "dns_records" name = db.Column(db.String(255), nullable=False) content = db.Column(db.String(255), nullable=False) ttl = db.Column(db.Integer, nullable=False) zone_uuid = db.Column(UUID(as_uuid=True), db.ForeignKey("dns_zones.uuid"), nullable=False) record_type_id = db.Column(db.Integer, db.ForeignKey("dns_record_types.id"), nullable=False) def __repr__(self): return (f"<{self.__class__.__name__}" f"(uuid={self.uuid})>" f"(name={self.name})>" f"(content={self.content})" f"(ttl={self.ttl})" f"(rtype={self.rtype.name})" f"(zone={self.zone.name})" f">") def __str__(self): return (f"<{self.__class__.__name__}" f"(uuid={self.uuid})>" f"(name={self.name})>" f"(content={self.content})" f"(ttl={self.ttl})" f"(rtype={self.rtype.name})" f"(zone={self.zone.name})" f">") @property def dns_service(self): return DNSService(nameserver=self.zone.server_name, keyring_name=self.zone.keyring_name, keyring_value=self.zone.keyring_value, timeout=10) def insert(self): super().new() def new(self): super().new() result = self.dns_service.add_record( **{ "record_name": self.name, "record_content": self.content, "record_type": self.rtype.name, "record_ttl": self.ttl, "zone": self.zone.name }) if result == "NOERROR": return self else: self.delete() raise ValueError(result) def update(self, **kwargs): if kwargs.get("name") and (self.name != kwargs.get("name")): self.dns_service.remove_record(**{ "record_name": self.name, "zone": self.zone.name }) self.name = kwargs.get("name", self.name) self.content = kwargs.get("content", self.content) self.ttl = kwargs.get("ttl", self.ttl) result = self.dns_service.replace_record( **{ "record_name": self.name, "record_content": self.content, "record_type": self.rtype.name, "record_ttl": self.ttl }) if result == "NOERROR": self.save() return self else: raise ValueError(result) def delete(self): result = self.dns_service.remove_record(**{ "record_name": self.name, "zone": self.zone.name }) if result == "NOERROR": super().delete() else: raise ValueError(result) @db.validates("ttl") def validate_ttl(self, key, ttl): if ttl % 60 != 0: raise ValueError("TTL should be multiply by 60") return ttl
# from flask import g from wtforms.validators import Email, Regexp from flask_security import RoleMixin, UserMixin from flask_security.utils import hash_password from server.models.base import Base from server.app import db, app import pdb roles_users = db.Table( 'roles_users', db.Column('user_id', db.Integer(), db.ForeignKey('user.id')), db.Column('role_id', db.Integer(), db.ForeignKey('role.id'))) class Role(Base, RoleMixin): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), nullable=False, unique=True) description = db.Column(db.String(255)) def __init__(self, name, desc): self.name = name self.description = desc def __repr__(self): return '<Role %r -- %r>' % (self.name, self.description) class User(Base, UserMixin): id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(120), unique=True,