From 3e502daf08eb879f0e47fc17526e6187dd08a75a Mon Sep 17 00:00:00 2001 From: Bryan Newbold Date: Thu, 12 Apr 2018 12:08:31 -0700 Subject: schema cleanups; actually include files --- fatcat/models.py | 93 +++++++++++++++++++++++++++++++++++--------------------- fatcat/sql.py | 14 ++++++--- 2 files changed, 67 insertions(+), 40 deletions(-) diff --git a/fatcat/models.py b/fatcat/models.py index fb319331..6672ce2e 100644 --- a/fatcat/models.py +++ b/fatcat/models.py @@ -15,28 +15,46 @@ from fatcat import db # TODO: EntityMixin, EntityIdMixin -work_contrib = db.Table("work_contrib", - db.Column("work_rev", db.ForeignKey('work_revision.id'), nullable=False, primary_key=True), - db.Column("creator_id", db.ForeignKey('creator_id.id'), nullable=False, primary_key=True), - db.Column("type", db.String, nullable=True), - db.Column("stub", db.String, nullable=True)) - -release_contrib = db.Table("release_contrib", - db.Column("release_rev", db.ForeignKey('release_revision.id'), nullable=False, primary_key=True), - db.Column("creator_id", db.ForeignKey('creator_id.id'), nullable=False, primary_key=True), - db.Column("type", db.String, nullable=True), - db.Column("stub", db.String, nullable=True)) - -release_ref = db.Table("release_ref", - db.Column("release_rev", db.ForeignKey('release_revision.id'), nullable=False), - db.Column("target_release_id", db.ForeignKey('release_id.id'), nullable=False), - db.Column("index", db.Integer, nullable=True), - db.Column("stub", db.String, nullable=True), - db.Column("doi", db.String, nullable=True)) - -file_release = db.Table("file_release", - db.Column("release_id", db.ForeignKey('release_id.id'), nullable=False, primary_key=True), - db.Column("file_rev", db.ForeignKey('file_revision.id'), nullable=False, primary_key=True)) +class WorkContrib(db.Model): + __tablename__ = "work_contrib" + work_rev= db.Column(db.ForeignKey('work_revision.id'), nullable=False, primary_key=True) + creator_id = db.Column(db.ForeignKey('creator_id.id'), nullable=False, primary_key=True) + type = db.Column(db.String, nullable=True) + stub = db.Column(db.String, nullable=True) + + creator = db.relationship("CreatorId") + work = db.relationship("WorkRevision") + +class ReleaseContrib(db.Model): + __tablename__ = "release_contrib" + release_rev = db.Column(db.ForeignKey('release_revision.id'), nullable=False, primary_key=True) + creator_id = db.Column(db.ForeignKey('creator_id.id'), nullable=False, primary_key=True) + type = db.Column(db.String, nullable=True) + stub = db.Column(db.String, nullable=True) + + creator = db.relationship("CreatorId") + release = db.relationship("ReleaseRevision") + +class ReleaseRef(db.Model): + __tablename__ = "release_ref" + id = db.Column(db.Integer, primary_key=True, nullable=False) + release_rev= db.Column(db.ForeignKey('release_revision.id'), nullable=False) + target_release_id= db.Column(db.ForeignKey('release_id.id'), nullable=False) + index = db.Column(db.Integer, nullable=True) + stub = db.Column(db.String, nullable=True) + doi = db.Column(db.String, nullable=True) + + release = db.relationship("ReleaseRevision") + target = db.relationship("ReleaseId") + +class FileRelease(db.Model): + __tablename__ = "file_release" + id = db.Column(db.Integer, primary_key=True, nullable=False) + file_rev= db.Column(db.ForeignKey('file_revision.id'), nullable=False) + release_id = db.Column(db.ForeignKey('release_id.id'), nullable=False) + + release = db.relationship("ReleaseId") + file = db.relationship("FileRevision") class WorkId(db.Model): """ @@ -75,13 +93,15 @@ class WorkRevision(db.Model): work_type = db.Column(db.String) date = db.Column(db.String) - creators = db.relationship('CreatorId', secondary=work_contrib, - lazy='subquery', backref=db.backref('works', lazy=True)) + creators = db.relationship('WorkContrib', lazy='subquery', + backref=db.backref('works', lazy=True)) class ReleaseId(db.Model): __tablename__ = 'release_id' - id = db.Column(db.Integer, primary_key=True) #autoincrement=False + id = db.Column(db.Integer, primary_key=True) + live = db.Column(db.Boolean, nullable=False, default=False) revision_id = db.Column(db.ForeignKey('release_revision.id')) + redirect_id = db.Column(db.ForeignKey('release_id.id'), nullable=True) class ReleaseRevision(db.Model): __tablename__ = 'release_revision' @@ -101,17 +121,17 @@ class ReleaseRevision(db.Model): pages = db.Column(db.String, nullable=True) issue = db.Column(db.String, nullable=True) - creators = db.relationship('CreatorId', secondary=release_contrib, - lazy='subquery') + creators = db.relationship('ReleaseContrib', lazy='subquery') #backref=db.backref('releases', lazy=True)) - refs = db.relationship('ReleaseId', secondary=release_ref, - lazy='subquery') + refs = db.relationship('ReleaseRef', lazy='subquery') #backref=db.backref('backrefs', lazy=True)) class CreatorId(db.Model): __tablename__ = 'creator_id' - id = db.Column(db.Integer, primary_key=True) #autoincrement=False) + id = db.Column(db.Integer, primary_key=True) + live = db.Column(db.Boolean, nullable=False, default=False) revision_id = db.Column(db.ForeignKey('creator_revision.id')) + redirect_id = db.Column(db.ForeignKey('creator_id.id'), nullable=True) class CreatorRevision(db.Model): __tablename__ = 'creator_revision' @@ -127,8 +147,10 @@ class CreatorRevision(db.Model): class ContainerId(db.Model): __tablename__ = 'container_id' - id = db.Column(db.Integer, primary_key=True) #autoincrement=False) + id = db.Column(db.Integer, primary_key=True) + live = db.Column(db.Boolean, nullable=False, default=False) revision_id = db.Column(db.ForeignKey('container_revision.id')) + redirect_id = db.Column(db.ForeignKey('container_id.id'), nullable=True) class ContainerRevision(db.Model): __tablename__ = 'container_revision' @@ -145,8 +167,10 @@ class ContainerRevision(db.Model): class FileId(db.Model): __tablename__ = 'file_id' - id = db.Column(db.Integer, primary_key=True, autoincrement=False) - revision_id = db.Column('revision', db.ForeignKey('container_revision.id')) + id = db.Column(db.Integer, primary_key=True) + live = db.Column(db.Boolean, nullable=False, default=False) + revision_id = db.Column('revision', db.ForeignKey('file_revision.id')) + redirect_id = db.Column(db.ForeignKey('file_id.id'), nullable=True) class FileRevision(db.Model): __tablename__ = 'file_revision' @@ -159,8 +183,7 @@ class FileRevision(db.Model): sha1 = db.Column(db.Integer) # TODO: hash table... only or in addition? url = db.Column(db.Integer) # TODO: URL table - releases = db.relationship('ReleaseId', secondary=file_release, - lazy='subquery') + releases = db.relationship('FileRelease', lazy='subquery') #backref=db.backref('backrefs', lazy=True)) class Edit(db.Model): diff --git a/fatcat/sql.py b/fatcat/sql.py index 4b35c904..c3c928a8 100644 --- a/fatcat/sql.py +++ b/fatcat/sql.py @@ -20,8 +20,9 @@ def populate_db(): pi_work_id = WorkId(revision_id=pi_work.id) pi_release = ReleaseRevision( title=pi_work.title, - creators=[n_elkies_id], work_id=pi_work.id) + pi_contrib = ReleaseContrib(creator=n_elkies_id) + pi_release.creators.append(pi_contrib) pi_release_id = ReleaseId(revision_id=pi_release.id) pi_work.primary_release = pi_release.id @@ -87,7 +88,7 @@ def populate_complex_db(count=100): authors = set(random.sample(author_ids, 5)) release = ReleaseRevision( title=work.title, - creators=list(authors), + creators=[ReleaseContrib(creator=a) for a in list(authors)], work_id=work.id, container_id=random.choice(container_ids).id) release_id = ReleaseId(revision_id=release.id) @@ -95,7 +96,7 @@ def populate_complex_db(count=100): authors.add(random.choice(author_ids)) release2 = ReleaseRevision( title=work.title + " (again)", - creators=list(authors), + creators=[ReleaseContrib(creator=a) for a in list(authors)], work_id=work.id, container_id=random.choice(container_ids).id) release_id2 = ReleaseId(revision_id=release2.id) @@ -112,8 +113,11 @@ def populate_complex_db(count=100): sha1=file_sha, size=len(file_content), url="http://archive.invalid/{}".format(file_sha), - releases=[release_id, release_id2], + releases=[FileRelease(release=release_id), FileRelease(release=release_id2)], ) + file_id = FileId(revision_id=file_rev.id) + file_revs.append(file_rev) + file_ids.append(file_id) db.session.add_all(author_revs) db.session.add_all(author_ids) @@ -157,7 +161,7 @@ def add_crossref(meta): work_id = WorkId(revision_id=work.id) release = ReleaseRevision( title=title, - creators=author_ids, + creators=[ReleaseContrib(creator=a) for a in author_ids], work_id=work.id, container_id=container_id.id, release_type=meta['type'], -- cgit v1.2.3