class User(db.Model): # Generates default class name for table. For changing use # __tablename__ = 'users' id = db.Column(db.String, unique=True, primary_key=True) password = db.Column(db.String(length=80)) email = db.Column(db.String(length=80)) name = db.Column(db.String(length=80)) userType = db.Column(db.Enum('student', 'mentor', 'admin')) phone = db.Column(db.String(length=80)) birthday = db.Column(db.DateTime) location = db.Column(db.String) # Other data data = db.Column(db.JSON) def is_active(self): return True def get_id(self): return self.id def is_authenticated(self): return self.authenticated def is_anonymous(self): return False def as_dict(self): return {c.name: getattr(self, c.name) for c in self.__table__.columns}
class RegistrationEvent(_Event): """ Define the RegistrationEvent class for table 'event_registration' with the following columns: id integer, foreign key to `event.id` registration_id integer, foreign key to 'document.id' action registration_action, the action that was performed on the registration """ __tablename__ = "event_registration" __mapper_args__ = {'polymorphic_identity': "registration"} __table_args__ = ( db.UniqueConstraint( "registration_id", "action"), # only one of each action per registration ) # columns id = db.Column(db.Integer, db.ForeignKey(_Event.id), primary_key=True) registration_id = db.Column(db.Integer, db.ForeignKey("registration.id"), nullable=False) action = db.Column(db.Enum(*registration_action.ALL, name="registration_action"), nullable=False) # relationships registration = db.relationship("Registration", back_populates="events") def __init__(self, user_guid, user_auth_type, registration_id, action): super().__init__(user_guid, user_auth_type) self.registration_id = registration_id self.action = action
class DocumentEvent(_Event): """ Define the DocumentEvent class for table 'event_document' with the following columns: id integer, foreign key to `event.id` document_id integer, foreign key to 'document.id' action document_action, the action that was performed on the document state JSON, state of document corresponding to the action """ __tablename__ = "event_document" __mapper_args__ = {'polymorphic_identity': "document"} # columns id = db.Column(db.Integer, db.ForeignKey(_Event.id), primary_key=True) document_id = db.Column(db.Integer, db.ForeignKey("document.id"), nullable=False) action = db.Column(db.Enum(*document_action.ALL, name="document_action"), nullable=False) state = db.Column(db.JSON()) # relationships document = db.relationship("Document", back_populates="events") def __init__(self, user_guid, user_auth_type, document_id, action, state=None): super().__init__(user_guid, user_auth_type) self.document_id = document_id self.action = action self.state = state
class _Event(db.Model): """ Define the Event class for table 'event' with the following columns: id integer, primary key timestamp datetime, when the event occurred user_guid varchar(64), foreign key to 'auth_user.guid' user_auth_type user_auth_type, foreign key to 'auth_user.auth_type' """ __tablename__ = "event" __mapper_args__ = {'polymorphic_on': "type"} __table_args__ = (db.ForeignKeyConstraint( ("user_guid", "user_auth_type"), ("auth_user.guid", "auth_user.auth_type"), ), ) # columns id = db.Column(db.Integer, primary_key=True) timestamp = db.Column(db.DateTime, nullable=False) user_guid = db.Column(db.String(64), nullable=False) user_auth_type = db.Column(user_auth_type, nullable=False) type = db.Column(db.Enum("document", "registration", name="event_type"), nullable=False) # relationships agent = db.relationship("User", primaryjoin="and_(_Event.user_guid == User.guid, " "_Event.user_auth_type == User.auth_type)", back_populates="events") def __init__(self, user_guid, user_auth_type): self.user_guid = user_guid self.user_auth_type = user_auth_type self.timestamp = datetime.utcnow()
class Package(db.Model): """ This class is DB model for storing package data """ __tablename__ = "package" id = db.Column(db.Integer, primary_key=True, autoincrement=True) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) name = db.Column(db.TEXT, index=True) status = db.Column(db.Enum(PackageStateEnum, native_enum=False), index=True, default=PackageStateEnum.active) private = db.Column(db.BOOLEAN, default=False) publisher_id = db.Column(db.Integer, ForeignKey('publisher.id', ondelete='CASCADE')) publisher = relationship( "Publisher", back_populates="packages", cascade="save-update, merge, delete, delete-orphan", single_parent=True) descriptor = db.Column(db.JSON) readme = db.Column(db.TEXT) tags = relationship("PackageTag", back_populates="package") __table_args__ = (UniqueConstraint("name", "publisher_id"), ) @classmethod def get_by_publisher(cls, publisher_name, package_name): instance = cls.query.join(Publisher) \ .filter(Package.name == package_name, Publisher.name == publisher_name).one_or_none() return instance
class Role(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Enum(RoleNames)) updated_at = db.Column(db.TIMESTAMP, nullable=False, default=func.now(), onupdate=func.now) created_at = db.Column(db.TIMESTAMP, nullable=False, server_default=func.now()) deleted_at = db.Column(db.TIMESTAMP, nullable=True) @classmethod def create_user_roles(cls): for role in RoleNames: role = cls(name=role) cls.query.session.add(role) cls.query.session.commit() @classmethod def get_user_role(cls): return cls.query.filter(cls.name == RoleNames.user).first() @classmethod def get_user_manager_role(cls): return cls.query.filter(cls.name == RoleNames.user_manager).first() @classmethod def get_admin_role(cls): return cls.query.filter(cls.name == RoleNames.admin).first()
class Song(BaseModel): __tablename__ = 'song' artist = db.Column(db.String, nullable=False) title = db.Column(db.String, nullable=False) playlist_id = db.Column(UUIDType(binary=False), db.ForeignKey('playlist.id'), unique=False, index=True) status = db.Column(db.Enum(SongStatus), nullable=False)
class PublisherUser(db.Model): """ This class is association object between user and publisher as they have many to many relationship """ __tablename__ = 'publisher_user' id = db.Column(db.Integer, primary_key=True, autoincrement=True) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) user_id = db.Column(db.Integer, ForeignKey('user.id'), primary_key=True) publisher_id = db.Column(db.Integer, ForeignKey('publisher.id'), primary_key=True) role = db.Column(db.Enum(UserRoleEnum, native_enum=False), nullable=False) """role can only OWNER or MEMBER""" publisher = relationship("Publisher", back_populates="users") user = relationship("User", back_populates="publishers")
""" For enum types shared across model. """ from app.database import db from app.constants import user_auth_type user_auth_type = db.Enum(user_auth_type.NYC_ID, user_auth_type.NYC_EMPLOYEES, user_auth_type.FACEBOOK, user_auth_type.MICROSOFT, user_auth_type.YAHOO, user_auth_type.LINKEDIN, user_auth_type.GOOGLE, name='user_auth_type')
class UserHashtag(db.Model): id = db.Column(db.String, unique=True, primary_key=True) user_id = db.Column(db.String) hashtag_id = db.Column(db.String) userType = db.Column(db.Enum('student', 'mentor', 'admin'))
class Document(db.Model): """ Define the Document class for the table 'publication' with the following columns: COLUMNS MODS 3.6 EQUIVALENT DESCRIPTION id NA integer, primary key user_guid NA varchar(64), foreign key to 'auth_user.guid' user_auth_type NA user_auth_type, foreign key to 'auth_user.auth_type' title titleInfo.title varchar(), chief title of this resource subtitle titleInfo.subTitle varchar(), the remainder of the title information names name.namePart json, ... type genre.type publication_type, ... publisher originInfo.publisher varchar(), entity that produced this resource date_created originInfo.dateCreated datetime, date of creation date_issued originInfo.dateIssuesd datetime, date of publication language language.languageTerm language_code, ISO-639-2 language code topic subject.topic topic, term/phrase representing primary topic of focus geographic subject.geographic varchar(), geographic designation temporal subject.temporal temporal, chronological subject terms or temporal coverage url location.url varchar(), Uniform Resource Location of resource (once available on portal) language codes are retrieved from https://www.loc.gov/standards/iso639-2/php/code_list.php """ __tablename__ = "document" __table_args__ = (db.ForeignKeyConstraint( ("user_guid", "user_auth_type"), ("auth_user.guid", "auth_user.auth_type")), ) # columns id = db.Column(db.Integer, primary_key=True) user_guid = db.Column(db.String(64), nullable=False) user_auth_type = db.Column(user_auth_type, nullable=False) title = db.Column(db.String(), nullable=False) subtitle = db.Column(db.String()) names = db.Column(db.JSON(), nullable=False) type = db.Column(db.Enum("foo", name="document_type"), nullable=False) publisher = db.Column(db.String(), nullable=False) date_created = db.Column(db.DateTime(), nullable=False) date_issued = db.Column(db.DateTime(), nullable=False) language = db.Column(db.Enum("bar", name="language"), nullable=False) topic = db.Column(db.Enum("baz", name="topic"), nullable=False) geographic = db.Column(db.String()) temporal = db.Column(db.Enum("qux", name="temporal"), nullable=False) url = db.Column(db.String()) # relationships files = db.relationship("File", back_populates="document") events = db.relationship("DocumentEvent", back_populates="document", lazy="dynamic") submitter = db.relationship( "User", primaryjoin="and_(Document.user_guid == User.guid, " "Document.user_auth_type == User.auth_type)", back_populates="submissions") @property def status(self): return self.events.order_by( DocumentEvent.timestamp.desc()).first().action @property def date_created(self): return self.events.order_by( DocumentEvent.timestamp.asc()).first().timestamp @property def date_published(self): return self.events.filter_by(document_action.PUBLISHED).one().timestamp def as_dict(self, event_type ): # TODO: for DocumentEvent.state, possibly use DataDiff return { "title": self.title, "subtitle": self.subtitle, # ... }
class Package(db.Model): """ This class is DB model for storing package data """ __tablename__ = "package" id = db.Column(db.Integer, primary_key=True, autoincrement=True) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) name = db.Column(db.TEXT, index=True) version = db.Column(db.TEXT, index=True, default='latest') descriptor = db.Column(db.JSON) status = db.Column(db.Enum(PackageStateEnum, native_enum=False), index=True, default=PackageStateEnum.active) private = db.Column(db.BOOLEAN, default=False) readme = db.Column(db.TEXT) publisher_id = db.Column(db.Integer, ForeignKey('publisher.id')) publisher = relationship( "Publisher", back_populates="packages", cascade="save-update, merge, delete, delete-orphan", single_parent=True) __table_args__ = (UniqueConstraint("name", "version", "publisher_id"), ) @staticmethod def create_or_update_version(publisher_name, package_name, version): try: data_latest = Package.query.join(Publisher). \ filter(Publisher.name == publisher_name, Package.name == package_name, Package.version == 'latest').one() instance = Package.query.join(Publisher). \ filter(Publisher.name == publisher_name, Package.name == package_name, Package.version == version).first() update_props = [ 'name', 'version', 'descriptor', 'status', 'private', 'readme', 'publisher_id' ] if instance is None: instance = Package() for update_prop in update_props: setattr(instance, update_prop, getattr(data_latest, update_prop)) instance.version = version db.session.add(instance) db.session.commit() return True except Exception as e: app.logger.error(e) return False @staticmethod def create_or_update(name, publisher_name, **kwargs): """ This method creates data package of update data package attributes :param name: package name :param publisher_name: publisher name :param kwargs: package attribute names """ pub_id = Publisher.query.filter_by(name=publisher_name).one().id instance = Package.query.join(Publisher)\ .filter(Package.name == name, Publisher.name == publisher_name).first() if not instance: instance = Package(name=name) instance.publisher_id = pub_id for key, value in kwargs.items(): setattr(instance, key, value) db.session.add(instance) db.session.commit() @staticmethod def change_status(publisher_name, package_name, status=PackageStateEnum.active): """ This method changes status of the data package. This method used for soft delete the data package :param publisher_name: publisher name :param package_name: package name :param status: status of the package :return: If success True else False """ try: data = Package.query.join(Publisher). \ filter(Publisher.name == publisher_name, Package.name == package_name).one() data.status = status db.session.add(data) db.session.commit() return True except Exception as e: app.logger.error(e) return False @staticmethod def delete_data_package(publisher_name, package_name): """ This method deletes the data package. This method used for hard delete the data package :param publisher_name: publisher name :param package_name: package name :return: If success True else False """ try: data = Package.query.join(Publisher). \ filter(Publisher.name == publisher_name, Package.name == package_name).one() package_id = data.id meta_data = Package.query.get(package_id) db.session.delete(meta_data) db.session.commit() return True except Exception as e: app.logger.error(e) return False @staticmethod def get_package(publisher_name, package_name): """ This method returns certain data packages belongs to a publisher :param publisher_name: publisher name :param package_name: package name :return: data package object based on the filter. """ try: instance = Package.query.join(Publisher) \ .filter(Package.name == package_name, Publisher.name == publisher_name).first() return instance except Exception as e: app.logger.error(e) return None