start refactoring pythong code

6 files changed, 47 insertions, 1043 deletions

diff --git a/python/fatcat/__init__.py b/python/fatcat/__init__.py
index a824d220..0240e3e9 100644
--- a/python/fatcat/__init__.py
+++ b/python/fatcat/__init__.py
@@ -1,15 +1,11 @@
 
 from flask import Flask
-from flask_sqlalchemy import SQLAlchemy
-from flask_marshmallow import Marshmallow
 from flask_debugtoolbar import DebugToolbarExtension
 from config import Config
 
 toolbar = DebugToolbarExtension()
 app = Flask(__name__)
 app.config.from_object(Config)
-db = SQLAlchemy(app)
-ma = Marshmallow(app)
 toolbar = DebugToolbarExtension(app)
 
-from fatcat import routes, models, api, sql, dummy
+from fatcat import routes
diff --git a/python/fatcat/api.py b/python/fatcat/api.py
deleted file mode 100644
index 2c91533b..00000000
--- a/python/fatcat/api.py
+++ /dev/null
@@ -1,280 +0,0 @@
-
-from flask import Flask, render_template, send_from_directory, request, \
-    url_for, abort, g, redirect, jsonify, session
-from fatcat import app, db
-from fatcat.models import *
-from fatcat.sql import *
-
-
-### Helpers #################################################################
-
-def get_or_create_editgroup(param=None):
-    if param != None:
-        editgroup = EditGroup.query.get_or_404(int(param))
-        return editgroup
-    editor = Editor.query.get_or_404(1)
-    if editor.active_editgroup:
-        return editor.active_editgroup
-
-    editgroup = EditGroup(editor=editor)
-    db.session.add(editgroup)
-    db.session.commit()
-    editor.active_editgroup = editgroup
-    db.session.add(editor)
-    db.session.commit()
-    return editgroup
-
-### Views ###################################################################
-
-@app.route('/v0/work/<int:ident>', methods=['GET'])
-def api_work_get(ident):
-    entity = WorkIdent.query.get_or_404(ident)
-    return work_schema.jsonify(entity)
-
-@app.route('/v0/work', methods=['POST'])
-def api_work_create(params=None):
-    # TODO: Special-case to pull out primary and create that?
-    if params == None:
-        params = request.get_json()
-    editgroup = get_or_create_editgroup(params.get('editgroup'))
-    rev = WorkRev(
-        title=params.get('title', None),
-        work_type=params.get('work_type', None),
-    )
-    ident = WorkIdent(is_live=False, rev=rev)
-    edit = WorkEdit(editgroup=editgroup, ident=ident, rev=rev)
-    if params.get('extra', None):
-        rev.extra_json = json.dumps(params['extra'], indent=False).encode('utf-8')
-    db.session.add_all([edit, ident, rev])
-    db.session.commit()
-    return work_schema.jsonify(ident)
-
-@app.route('/v0/work/random', methods=['GET'])
-def api_work_random():
-    entity = WorkIdent.query.order_by(db.func.random()).first()
-    return redirect('/v0/work/{}'.format(entity.id))
-
-
-@app.route('/v0/release/<int:ident>', methods=['GET'])
-def api_release_get(ident):
-    entity = ReleaseIdent.query.get_or_404(ident)
-    return release_schema.jsonify(entity)
-
-@app.route('/v0/release', methods=['POST'])
-def api_release_create(params=None):
-    if params == None:
-        params = request.get_json()
-    editgroup = get_or_create_editgroup(params.get('editgroup'))
-    creators = params.get('creators', [])
-    creators = [CreatorIdent.query.get_or_404(c) for c in creators]
-    targets = [ref['target'] for ref in params.get('refs', []) if ref.get('target') != None]
-    targets = [ReleaseIdent.query.get_or_404(t) for t in targets]
-    work = params.get('work')
-    if work:
-        work = WorkIdent.query.get_or_404(work)
-    container = params.get('container')
-    if container:
-        container = ContainerIdent.query.get_or_404(container)
-    rev = ReleaseRev(
-        title=params.get('title', None),
-        release_type=params.get('release_type', None),
-        work=work,
-        container=container,
-        doi=params.get('doi', None),
-    )
-    contribs = [ReleaseContrib(release=rev, creator=c) for c in creators]
-    rev.creators = contribs
-    db.session.add_all(contribs)
-    refs = [ReleaseRef(release=rev, target=t) for t in targets]
-    rev.refs = refs
-    db.session.add_all(refs)
-    ident = ReleaseIdent(is_live=False, rev=rev)
-    edit = ReleaseEdit(editgroup=editgroup, ident=ident, rev=rev)
-    if params.get('extra', None):
-        rev.extra_json = json.dumps(params['extra'], indent=False).encode('utf-8')
-    db.session.add_all([edit, ident, rev])
-    db.session.commit()
-    return release_schema.jsonify(ident)
-
-@app.route('/v0/release/<int:ident>/changelog', methods=['GET'])
-def api_release_changelog(ident):
-    entries = ChangelogEntry.query\
-        .join(ReleaseEdit.editgroup)\
-        .filter(ReleaseEdit.ident_id==ident)\
-        .all()
-    return changelogentry_schema.jsonify(entries, many=True)
-
-@app.route('/v0/release/random', methods=['GET'])
-def api_release_random():
-    entity = ReleaseIdent.query.order_by(db.func.random()).first()
-    return redirect('/v0/release/{}'.format(entity.id))
-
-@app.route('/v0/release/lookup', methods=['GET'])
-def api_release_lookup():
-    params = request.get_json()
-    doi = params['doi'].strip().lower()
-    # TODO: proper regex
-    if not (doi.startswith("10.") and len(doi.split('/')) == 2):
-        abort(400)
-    entity = ReleaseIdent.query\
-        .join(ReleaseIdent.rev)\
-        .filter(ReleaseRev.doi==doi)\
-        .first_or_404()
-    return release_schema.jsonify(entity)
-
-
-@app.route('/v0/creator/<int:ident>', methods=['GET'])
-def api_creator_get(ident):
-    entity = CreatorIdent.query.get_or_404(ident)
-    return creator_schema.jsonify(entity)
-
-@app.route('/v0/creator', methods=['POST'])
-def api_creator_create(params=None):
-    if params == None:
-        params = request.get_json()
-    editgroup = get_or_create_editgroup(params.get('editgroup'))
-    rev = CreatorRev(
-        name=params.get('name', None),
-        orcid=params.get('orcid', None),
-    )
-    ident = CreatorIdent(is_live=False, rev=rev)
-    edit = CreatorEdit(editgroup=editgroup, ident=ident, rev=rev)
-    if params.get('extra', None):
-        rev.extra_json = json.dumps(params['extra'], indent=False).encode('utf-8')
-    db.session.add_all([edit, ident, rev])
-    db.session.commit()
-    return creator_schema.jsonify(ident)
-
-@app.route('/v0/creator/lookup', methods=['GET'])
-def api_creator_lookup():
-    params = request.get_json()
-    orcid = params['orcid'].strip()
-    # TODO: proper regex
-    if not (len(orcid) == len("0000-0002-1825-0097") and len(orcid.split('-')) == 4):
-        abort(400)
-    entity = CreatorIdent.query\
-        .join(CreatorIdent.rev)\
-        .filter(CreatorRev.orcid==orcid)\
-        .first_or_404()
-    return creator_schema.jsonify(entity)
-
-
-@app.route('/v0/container/<int:ident>', methods=['GET'])
-def api_container_get(ident):
-    entity = ContainerIdent.query.get_or_404(ident)
-    return container_schema.jsonify(entity)
-
-@app.route('/v0/container', methods=['POST'])
-def api_container_create(params=None):
-    if params == None:
-        params = request.get_json()
-    editgroup = get_or_create_editgroup(params.get('editgroup'))
-    rev = ContainerRev(
-        name=params.get('name', None),
-        publisher=params.get('publisher', None),
-        issn=params.get('issn', None),
-    )
-    ident = ContainerIdent(is_live=False, rev=rev)
-    edit = ContainerEdit(editgroup=editgroup, ident=ident, rev=rev)
-    if params.get('extra', None):
-        rev.extra_json = json.dumps(params['extra'], indent=False).encode('utf-8')
-    db.session.add_all([edit, ident, rev])
-    db.session.commit()
-    return container_schema.jsonify(ident)
-
-@app.route('/v0/container/lookup', methods=['GET'])
-def api_container_lookup():
-    params = request.get_json()
-    issn = params['issn'].strip()
-    # TODO: proper regex
-    if not (len(issn) == 9 and issn[0:4].isdigit() and issn[5:7].isdigit()):
-        abort(400)
-    entity = ContainerIdent.query\
-        .join(ContainerIdent.rev)\
-        .filter(ContainerRev.issn==issn)\
-        .first_or_404()
-    return container_schema.jsonify(entity)
-
-
-@app.route('/v0/file/<int:ident>', methods=['GET'])
-def api_file_get(ident):
-    entity = FileIdent.query.get_or_404(ident)
-    return file_schema.jsonify(entity)
-
-@app.route('/v0/file', methods=['POST'])
-def api_file_create(params=None):
-    if params == None:
-        params = request.get_json()
-    editgroup = get_or_create_editgroup(params.get('editgroup'))
-    releases = params.get('releases', [])
-    releases = [ReleaseIdent.query.get_or_404(r) for r in releases]
-    rev = FileRev(
-        sha1=params.get('sha1', None),
-        size=params.get('size', None),
-        url=params.get('url', None),
-    )
-    file_releases = [FileRelease(file=rev, release=r) for r in releases]
-    rev.releases = file_releases
-    db.session.add_all(file_releases)
-    ident = FileIdent(is_live=False, rev=rev)
-    edit = FileEdit(editgroup=editgroup, ident=ident, rev=rev)
-    if params.get('extra', None):
-        rev.extra_json = json.dumps(params['extra'], indent=False).encode('utf-8')
-    db.session.add_all([edit, ident, rev])
-    db.session.commit()
-    return file_schema.jsonify(ident)
-
-
-@app.route('/v0/editgroup/<int:ident>', methods=['GET'])
-def api_editgroup_get(ident):
-    entity = EditGroup.query\
-        .join(EditGroup.editor)\
-        .filter(EditGroup.id==ident)\
-        .first_or_404()
-    rv = editgroup_schema.dump(entity).data
-    rv['work_edits'] = work_edit_schema.dump(
-        WorkEdit.query.filter(EditGroup.id==ident).all(), many=True).data
-    rv['release_edits'] = release_edit_schema.dump(
-        ReleaseEdit.query.filter(EditGroup.id==ident).all(), many=True).data
-    rv['creator_edits'] = creator_edit_schema.dump(
-        CreatorEdit.query.filter(EditGroup.id==ident).all(), many=True).data
-    rv['container_edits'] = container_edit_schema.dump(
-        ContainerEdit.query.filter(EditGroup.id==ident).all(), many=True).data
-    rv['file_edits'] = file_edit_schema.dump(
-        FileEdit.query.filter(EditGroup.id==ident).all(), many=True).data
-    return jsonify(rv)
-
-@app.route('/v0/editgroup', methods=['POST'])
-def api_editgroup_create(params=None):
-    if params == None:
-        params = request.get_json()
-    eg = EditGroup(
-        editor_id=1,
-        description=params.get('description', None),
-    )
-    if params.get('extra', None):
-        eg.extra_json = json.dumps(params['extra'], indent=False).encode('utf-8')
-    db.session.add(eg)
-    db.session.commit()
-    return editgroup_schema.jsonify(eg)
-
-@app.route('/v0/editgroup/<int:ident>/accept', methods=['POST'])
-def api_editgroup_accept(ident):
-    entity = EditGroup.query.get_or_404(ident)
-    accept_editgroup(entity)
-    return jsonify({'success': True})
-
-
-@app.route('/v0/editor/<username>', methods=['GET'])
-def api_editor_get(username):
-    entity = Editor.query.filter(Editor.username==username).first_or_404()
-    return editor_schema.jsonify(entity)
-
-@app.route('/v0/editor/<username>/changelog', methods=['GET'])
-def api_editor_changelog(username):
-    entries = ChangelogEntry.query\
-        .join(ChangelogEntry.editgroup)\
-        .join(EditGroup.editor)\
-        .filter(Editor.username==username)\
-        .all()
-    return changelogentry_schema.jsonify(entries, many=True)
diff --git a/python/fatcat/dummy.py b/python/fatcat/dummy.py
deleted file mode 100644
index f22c4dcb..00000000
--- a/python/fatcat/dummy.py
+++ /dev/null
@@ -1,135 +0,0 @@
-
-import random
-import hashlib
-from fatcat import db
-from fatcat.models import *
-
-def insert_example_works():
-    """
-    TODO: doesn't create an edit trail (yet)
-    """
-
-    n_elkies = CreatorRev(
-        name="Noam D. Elkies",
-        sortname="Elkies, N",
-        orcid=None)
-    n_elkies_id = CreatorIdent(rev=n_elkies)
-    pi_work = WorkRev(
-        title="Why is π^2 so close to 10?",
-        work_type="journal-article")
-    pi_work_id = WorkIdent(rev=pi_work)
-    pi_release = ReleaseRev(
-        title=pi_work.title,
-        work_ident_id=pi_work.id,
-        release_type="journal-article")
-    pi_contrib = ReleaseContrib(creator=n_elkies_id)
-    pi_release.creators.append(pi_contrib)
-    pi_release_id = ReleaseIdent(rev=pi_release)
-    pi_work.primary_release = pi_release_id
-
-    # TODO:
-    #pi_file = File(
-    #    sha1="efee52e46c86691e2b892dbeb212f3b92e92e9d3",
-    #    url="http://www.math.harvard.edu/~elkies/Misc/pi10.pdf")
-    db.session.add_all([n_elkies, n_elkies_id, pi_work, pi_work_id, pi_release,
-        pi_release_id])
-
-    # TODO:
-    #ligo_collab = CreatorRev(name="LIGO Scientific Collaboration")
-    #ligo_paper = ReleaseRev(
-    #    title="Full Band All-sky Search for Periodic Gravitational Waves in the O1 LIGO Data")
-    db.session.commit()
-
-
-def insert_random_works(count=100):
-    """
-    TODO: doesn't create an edit trail (yet)
-    """
-
-    first_names = ("Sarah", "Robin", "Halko", "Jefferson", "Max", "桃井",
-        "Koizumi", "Rex", "Billie", "Tenzin")
-    last_names = ("Headroom", "はるこ", "Jun'ichirō", "Wong", "Smith")
-
-    author_revs = []
-    author_ids = []
-    for _ in range(count):
-        first = random.choice(first_names)
-        last = random.choice(last_names)
-        ar = CreatorRev(
-            name="{} {}".format(first, last),
-            sortname="{}, {}".format(last, first[0]),
-            orcid=None)
-        author_revs.append(ar)
-        author_ids.append(CreatorIdent(rev=ar))
-
-    container_revs = []
-    container_ids = []
-    for _ in range(5):
-        cr = ContainerRev(
-            name="The Fake Journal of Stuff",
-            #container_id=None,
-            publisher="Big Paper",
-            sortname="Fake Journal of Stuff",
-            issn="1234-5678")
-        container_revs.append(cr)
-        container_ids.append(ContainerIdent(rev=cr))
-
-    title_start = ("All about ", "When I grow up I want to be",
-        "The final word on", "Infinity: ", "The end of")
-    title_ends = ("Humankind", "Bees", "Democracy", "Avocados", "«küßî»", "“ЌύБЇ”")
-    work_revs = []
-    work_ids = []
-    release_revs = []
-    release_ids = []
-    file_revs = []
-    file_ids = []
-    for _ in range(count):
-        title = "{} {}".format(random.choice(title_start), random.choice(title_ends))
-        work = WorkRev(title=title)
-        work_id = WorkIdent(rev=work)
-        authors = set(random.sample(author_ids, 5))
-        release = ReleaseRev(
-            title=work.title,
-            creators=[ReleaseContrib(creator=a) for a in list(authors)],
-            #work=work,
-            container=random.choice(container_ids))
-        release_id = ReleaseIdent(rev=release)
-        work.primary_release = release_id
-        authors.add(random.choice(author_ids))
-        release2 = ReleaseRev(
-            title=work.title + " (again)",
-            creators=[ReleaseContrib(creator=a) for a in list(authors)],
-            #work=work,
-            container=random.choice(container_ids))
-        release_id2 = ReleaseIdent(rev=release2)
-        work_revs.append(work)
-        work_ids.append(work_id)
-        release_revs.append(release)
-        release_revs.append(release2)
-        release_ids.append(release_id)
-        release_ids.append(release_id2)
-
-        file_content = str(random.random()) * random.randint(3,100)
-        file_sha = hashlib.sha1(file_content.encode('utf-8')).hexdigest()
-        file_rev = FileRev(
-            sha1=file_sha,
-            size=len(file_content),
-            url="http://archive.invalid/{}".format(file_sha),
-            releases=[FileRelease(release=release_id), FileRelease(release=release_id2)],
-        )
-        file_id = FileIdent(rev=file_rev)
-        file_revs.append(file_rev)
-        file_ids.append(file_id)
-
-    db.session.add_all(author_revs)
-    db.session.add_all(author_ids)
-    db.session.add_all(work_revs)
-    db.session.add_all(work_ids)
-    db.session.add_all(release_revs)
-    db.session.add_all(release_ids)
-    db.session.add_all(container_revs)
-    db.session.add_all(container_ids)
-    db.session.add_all(file_revs)
-    db.session.add_all(file_ids)
-
-    db.session.commit()
diff --git a/python/fatcat/models.py b/python/fatcat/models.py
deleted file mode 100644
index c35e541f..00000000
--- a/python/fatcat/models.py
+++ /dev/null
@@ -1,429 +0,0 @@
-
-"""
-states for identifiers:
-- pre-live: points to a rev (during edit/accept period)
-- live: points to a rev
-- redirect: live, points to upstream rev, also points to redirect id
-    => if live and redirect non-null, all other fields copied from redirect target
-- deleted: live, but doesn't point to a rev
-
-possible refactors:
-- '_rev' instead of '_rev'
-- use mixins for entities
-"""
-
-import json
-import hashlib
-from marshmallow import post_dump, pre_load
-from fatcat import db, ma
-
-
-### Inter-Entity Relationships ###############################################
-
-class ReleaseContrib(db.Model):
-    __tablename__ = "release_contrib"
-    release_rev = db.Column(db.ForeignKey('release_rev.id'), nullable=False, primary_key=True)
-    creator_ident_id = db.Column(db.ForeignKey('creator_ident.id'), nullable=False, primary_key=True)
-    stub = db.Column(db.String, nullable=True)
-    type = db.Column(db.String, nullable=True)
-    # TODO: index (int)?
-
-    creator = db.relationship("CreatorIdent")
-    release = db.relationship("ReleaseRev")
-
-class ReleaseRef(db.Model):
-    __tablename__ = "release_ref"
-    id = db.Column(db.Integer, primary_key=True, nullable=False)
-    release_rev = db.Column(db.ForeignKey('release_rev.id'), nullable=False)
-    target_release_ident_id = db.Column(db.ForeignKey('release_ident.id'), nullable=True)
-    index = db.Column(db.Integer, nullable=True)
-    stub = db.Column(db.String, nullable=True)
-    doi = db.Column(db.String, nullable=True)
-
-    release = db.relationship("ReleaseRev")
-    target = db.relationship("ReleaseIdent")
-
-class FileRelease(db.Model):
-    __tablename__ = "file_release"
-    id = db.Column(db.Integer, primary_key=True, nullable=False)
-    file_rev= db.Column(db.ForeignKey('file_rev.id'), nullable=False)
-    release_ident_id = db.Column(db.ForeignKey('release_ident.id'), nullable=False)
-
-    release = db.relationship("ReleaseIdent")
-    file = db.relationship("FileRev")
-
-
-### Entities #################################################################
-
-class WorkRev(db.Model):
-    __tablename__ = 'work_rev'
-    id = db.Column(db.Integer, primary_key=True)
-    extra_json = db.Column(db.String, nullable=True)
-
-    title = db.Column(db.String)
-    work_type = db.Column(db.String)
-    primary_release_id = db.Column(db.ForeignKey('release_ident.id'), nullable=True)
-    primary_release = db.relationship('ReleaseIdent')
-
-class WorkIdent(db.Model):
-    """
-    If rev_id is null, this was deleted.
-    If redirect_id is not null, this has been merged with the given id. In this
-        case rev_id is a "cached" copy of the redirect's rev_id, as
-        an optimization. If the merged work is "deleted", rev_id can be
-        null and redirect_id not-null.
-    """
-    __tablename__ = 'work_ident'
-    id = db.Column(db.Integer, primary_key=True, nullable=False)
-    is_live = db.Column(db.Boolean, nullable=False, default=False)
-    rev_id = db.Column(db.ForeignKey('work_rev.id'), nullable=True)
-    redirect_id = db.Column(db.ForeignKey('work_ident.id'), nullable=True)
-    rev = db.relationship("WorkRev")
-
-class WorkEdit(db.Model):
-    __tablename__ = 'work_edit'
-    id = db.Column(db.Integer, primary_key=True)
-    ident_id = db.Column(db.ForeignKey('work_ident.id'), nullable=True)
-    rev_id = db.Column(db.ForeignKey('work_rev.id'), nullable=True)
-    redirect_id = db.Column(db.ForeignKey('work_ident.id'), nullable=True)
-    editgroup_id = db.Column(db.ForeignKey('editgroup.id'), nullable=True)
-    extra_json = db.Column(db.String, nullable=True)
-    ident = db.relationship("WorkIdent", foreign_keys="WorkEdit.ident_id")
-    rev = db.relationship("WorkRev")
-    editgroup = db.relationship("EditGroup")
-
-
-class ReleaseRev(db.Model):
-    __tablename__ = 'release_rev'
-    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
-    extra_json = db.Column(db.String, nullable=True)
-
-    work_ident_id = db.Column(db.ForeignKey('work_ident.id', use_alter=True), nullable=True) # XXX: nullable=False
-    container_ident_id = db.Column(db.ForeignKey('container_ident.id'), nullable=True)
-    title = db.Column(db.String, nullable=False)
-    license = db.Column(db.String, nullable=True)   # TODO: oa status foreign key
-    release_type = db.Column(db.String)             # TODO: foreign key
-    date = db.Column(db.String, nullable=True)      # TODO: datetime
-    doi = db.Column(db.String, nullable=True)       # TODO: identifier table
-    volume = db.Column(db.String, nullable=True)
-    pages = db.Column(db.String, nullable=True)
-    issue = db.Column(db.String, nullable=True)
-
-    work = db.relationship("WorkIdent", lazy='subquery', foreign_keys="ReleaseRev.work_ident_id")
-    container = db.relationship("ContainerIdent", lazy='subquery')
-    creators = db.relationship('ReleaseContrib', lazy='subquery')
-    refs = db.relationship('ReleaseRef', lazy='subquery')
-
-class ReleaseIdent(db.Model):
-    __tablename__ = 'release_ident'
-    id = db.Column(db.Integer, primary_key=True)
-    is_live = db.Column(db.Boolean, nullable=False, default=False)
-    rev_id = db.Column(db.ForeignKey('release_rev.id'))
-    redirect_id = db.Column(db.ForeignKey('release_ident.id'), nullable=True)
-    rev = db.relationship("ReleaseRev")
-
-class ReleaseEdit(db.Model):
-    __tablename__ = 'release_edit'
-    id = db.Column(db.Integer, primary_key=True)
-    ident_id = db.Column(db.ForeignKey('release_ident.id'), nullable=True)
-    rev_id = db.Column(db.ForeignKey('release_rev.id'), nullable=True)
-    redirect_id = db.Column(db.ForeignKey('release_ident.id'), nullable=True)
-    editgroup_id = db.Column(db.ForeignKey('editgroup.id'), nullable=True)
-    extra_json = db.Column(db.String, nullable=True)
-    ident = db.relationship("ReleaseIdent", foreign_keys="ReleaseEdit.ident_id")
-    rev = db.relationship("ReleaseRev")
-    editgroup = db.relationship("EditGroup")
-
-
-class CreatorRev(db.Model):
-    __tablename__ = 'creator_rev'
-    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
-    extra_json = db.Column(db.String, nullable=True)
-
-    name = db.Column(db.String)
-    sortname = db.Column(db.String)
-    orcid = db.Column(db.String)            # TODO: identifier table
-
-class CreatorIdent(db.Model):
-    __tablename__ = 'creator_ident'
-    id = db.Column(db.Integer, primary_key=True)
-    is_live = db.Column(db.Boolean, nullable=False, default=False)
-    rev_id = db.Column(db.ForeignKey('creator_rev.id'))
-    redirect_id = db.Column(db.ForeignKey('creator_ident.id'), nullable=True)
-    rev = db.relationship("CreatorRev")
-
-class CreatorEdit(db.Model):
-    __tablename__ = 'creator_edit'
-    id = db.Column(db.Integer, primary_key=True)
-    ident_id = db.Column(db.ForeignKey('creator_ident.id'), nullable=True)
-    rev_id = db.Column(db.ForeignKey('creator_rev.id'), nullable=True)
-    redirect_id = db.Column(db.ForeignKey('creator_ident.id'), nullable=True)
-    editgroup_id = db.Column(db.ForeignKey('editgroup.id'), nullable=True)
-    extra_json = db.Column(db.String, nullable=True)
-    ident = db.relationship("CreatorIdent", foreign_keys="CreatorEdit.ident_id")
-    rev = db.relationship("CreatorRev")
-    editgroup = db.relationship("EditGroup")
-
-
-class ContainerRev(db.Model):
-    __tablename__ = 'container_rev'
-    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
-    extra_json = db.Column(db.String, nullable=True)
-
-    name = db.Column(db.String)
-    parent_id = db.Column(db.ForeignKey('container_ident.id', use_alter=True))
-    publisher = db.Column(db.String)        # TODO: foreign key
-    sortname = db.Column(db.String)
-    issn = db.Column(db.String)             # TODO: identifier table
-    parent = db.relationship("ContainerIdent", foreign_keys="ContainerRev.parent_id")
-
-class ContainerIdent(db.Model):
-    __tablename__ = 'container_ident'
-    id = db.Column(db.Integer, primary_key=True)
-    is_live = db.Column(db.Boolean, nullable=False, default=False)
-    rev_id = db.Column(db.ForeignKey('container_rev.id'))
-    redirect_id = db.Column(db.ForeignKey('container_ident.id'), nullable=True)
-    rev = db.relationship("ContainerRev", foreign_keys="ContainerIdent.rev_id")
-
-class ContainerEdit(db.Model):
-    __tablename__ = 'container_edit'
-    id = db.Column(db.Integer, primary_key=True)
-    ident_id = db.Column(db.ForeignKey('container_ident.id'), nullable=True)
-    rev_id = db.Column(db.ForeignKey('container_rev.id'), nullable=True)
-    redirect_id = db.Column(db.ForeignKey('container_ident.id'), nullable=True)
-    editgroup_id = db.Column(db.ForeignKey('editgroup.id'), nullable=True)
-    extra_json = db.Column(db.String, nullable=True)
-    ident = db.relationship("ContainerIdent", foreign_keys="ContainerEdit.ident_id")
-    rev = db.relationship("ContainerRev")
-    editgroup = db.relationship("EditGroup")
-
-
-class FileRev(db.Model):
-    __tablename__ = 'file_rev'
-    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
-    extra_json = db.Column(db.String, nullable=True)
-
-    size = db.Column(db.Integer)
-    sha1 = db.Column(db.String)             # TODO: hash table... only or in addition?
-    url = db.Column(db.Integer)             # TODO: URL table
-    releases = db.relationship('FileRelease', lazy='subquery')
-
-class FileIdent(db.Model):
-    __tablename__ = 'file_ident'
-    id = db.Column(db.Integer, primary_key=True)
-    is_live = db.Column(db.Boolean, nullable=False, default=False)
-    rev_id = db.Column(db.ForeignKey('file_rev.id'))
-    redirect_id = db.Column(db.ForeignKey('file_ident.id'), nullable=True)
-    rev = db.relationship("FileRev")
-
-class FileEdit(db.Model):
-    __tablename__ = 'file_edit'
-    id = db.Column(db.Integer, primary_key=True)
-    ident_id = db.Column(db.ForeignKey('file_ident.id'), nullable=True)
-    rev_id = db.Column(db.ForeignKey('file_rev.id'), nullable=True)
-    redirect_id = db.Column(db.ForeignKey('file_ident.id'), nullable=True)
-    editgroup_id = db.Column(db.ForeignKey('editgroup.id'), nullable=True)
-    extra_json = db.Column(db.String, nullable=True)
-    ident = db.relationship("FileIdent", foreign_keys="FileEdit.ident_id")
-    rev = db.relationship("FileRev")
-    editgroup = db.relationship("EditGroup")
-
-
-### Editing #################################################################
-
-class EditGroup(db.Model):
-    __tablename__ = 'editgroup'
-    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
-    editor_id = db.Column(db.ForeignKey('editor.id'), nullable=False)
-    description = db.Column(db.String)
-    extra_json = db.Column(db.String, nullable=True)
-
-    editor = db.relationship("Editor", foreign_keys="EditGroup.editor_id")
-
-class Editor(db.Model):
-    __tablename__ = 'editor'
-    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
-    username = db.Column(db.String, nullable=False, unique=True)
-    is_admin = db.Column(db.Boolean, nullable=False, default=False)
-    active_editgroup_id = db.Column(db.ForeignKey('editgroup.id', use_alter=True))
-    active_editgroup = db.relationship('EditGroup', foreign_keys='Editor.active_editgroup_id')
-
-class ChangelogEntry(db.Model):
-    __tablename__= 'changelog'
-    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
-    editgroup_id = db.Column(db.ForeignKey('editgroup.id'))
-    timestamp = db.Column(db.Integer)
-    editgroup = db.relationship("EditGroup")
-
-
-### Marshmallow Wrappers ####################################################
-
-class ExtraJsonSchema(ma.ModelSchema):
-
-    @post_dump(pass_many=False)
-    def json_unflatten(self, data):
-        extra = data.pop('extra_json', None)
-        if extra != None:
-            extra = json.loads(extra)
-        data['extra'] = extra
-
-    @pre_load(pass_many=False)
-    def json_flatten(self, data):
-        extra = data.pop('extra', None)
-        if extra != None:
-            extra = json.dumps(extra)
-        data['extra_json'] = extra
-
-class EntitySchema(ExtraJsonSchema):
-
-    @post_dump(pass_many=False)
-    def merge_rev(self, data):
-        if data.get('rev', None) != None:
-            rev_id = data['rev'].pop('id')
-            data.update(data['rev'])
-            data['rev'] = rev_id
-        else:
-            data['rev'] = None
-
-class ReleaseContribSchema(ma.ModelSchema):
-    class Meta:
-        model = ReleaseContrib
-    creator = db.relationship("CreatorIdent")
-    release = db.relationship("ReleaseRev")
-
-class ReleaseRefSchema(ma.ModelSchema):
-    class Meta:
-        model = ReleaseRef
-    release = db.relationship("ReleaseRev")
-    target = db.relationship("ReleaseIdent")
-
-class FileReleaseSchema(ma.ModelSchema):
-    class Meta:
-        model = FileRelease
-    release = db.relationship("ReleaseIdent")
-    file = db.relationship("FileRev")
-
-class WorkRevSchema(ma.ModelSchema):
-    class Meta:
-        model = WorkRev
-        include_fk = True
-
-class WorkSchema(EntitySchema):
-    class Meta:
-        model = WorkIdent
-        include_fk = True
-    rev = ma.Nested(WorkRevSchema)
-
-class WorkEditSchema(ma.ModelSchema):
-    class Meta:
-        model = WorkEdit
-
-work_rev_schema = WorkRevSchema()
-work_schema = WorkSchema()
-work_edit_schema = WorkEditSchema()
-
-
-class ReleaseRevSchema(ma.ModelSchema):
-    class Meta:
-        model = ReleaseRev
-        include_fk = True
-    work = ma.Nested('WorkSchema')
-    container = ma.Nested('ContainerSchema')
-    creators = ma.Nested(ReleaseContribSchema, many=True)
-    refs = ma.Nested(ReleaseRefSchema, many=True)
-
-class ReleaseSchema(EntitySchema):
-    class Meta:
-        model = ReleaseIdent
-        include_fk = True
-    rev = ma.Nested(ReleaseRevSchema)
-    # XXX: files = ma.Nested('FileSchema', many=True)
-
-class ReleaseEditSchema(ma.ModelSchema):
-    class Meta:
-        model = ReleaseEdit
-
-release_rev_schema = ReleaseRevSchema()
-release_schema = ReleaseSchema()
-release_edit_schema = ReleaseEditSchema()
-
-
-class CreatorRevSchema(ma.ModelSchema):
-    class Meta:
-        model = CreatorRev
-        include_fk = True
-
-class CreatorSchema(EntitySchema):
-    class Meta:
-        model = CreatorIdent
-        include_fk = True
-    rev = ma.Nested(CreatorRevSchema)
-
-class CreatorEditSchema(ma.ModelSchema):
-    class Meta:
-        model = CreatorEdit
-
-creator_rev_schema = CreatorRevSchema()
-creator_schema = CreatorSchema()
-creator_edit_schema = CreatorEditSchema()
-
-
-class ContainerRevSchema(ma.ModelSchema):
-    class Meta:
-        model = ContainerRev
-        include_fk = True
-
-class ContainerSchema(EntitySchema):
-    class Meta:
-        model = ContainerIdent
-        include_fk = True
-    rev = ma.Nested(ContainerRevSchema)
-
-class ContainerEditSchema(ma.ModelSchema):
-    class Meta:
-        model = ContainerEdit
-
-container_rev_schema = ContainerRevSchema()
-container_schema = ContainerSchema()
-container_edit_schema = ContainerEditSchema()
-
-
-class FileRevSchema(ma.ModelSchema):
-    class Meta:
-        model = FileRev
-        include_fk = True
-
-    releases = ma.Nested(FileReleaseSchema, many=True)
-
-class FileSchema(EntitySchema):
-    class Meta:
-        model = FileIdent
-        include_fk = True
-    rev = ma.Nested(FileRevSchema)
-
-class FileEditSchema(ma.ModelSchema):
-    class Meta:
-        model = FileEdit
-
-file_rev_schema = FileRevSchema()
-file_schema = FileSchema()
-file_edit_schema = FileEditSchema()
-
-
-class EditorSchema(ma.ModelSchema):
-    class Meta:
-        model = Editor
-
-class EditGroupSchema(ma.ModelSchema):
-    class Meta:
-        model = EditGroup
-    editor = ma.Nested(EditorSchema)
-
-editor_schema = EditorSchema()
-editgroup_schema = EditGroupSchema()
-
-class ChangelogEntrySchema(ma.ModelSchema):
-    class Meta:
-        model = ChangelogEntry
-
-changelogentry_schema = ChangelogEntrySchema()
diff --git a/python/fatcat/routes.py b/python/fatcat/routes.py
index 0c86bd78..7db0ff6d 100644
--- a/python/fatcat/routes.py
+++ b/python/fatcat/routes.py
@@ -3,48 +3,11 @@ import os
 import json
 from flask import Flask, render_template, send_from_directory, request, \
     url_for, abort, g, redirect, jsonify, session
-from fatcat import app, db, api
+from fatcat import app
 
 
 ### Views ###################################################################
 
-@app.route('/work/create', methods=['GET'])
-def work_create():
-    return render_template('work_add.html')
-
-@app.route('/work/random', methods=['GET'])
-def work_random():
-    rv = api.api_work_random()
-    ident = rv.location.split('/')[-1]
-    return redirect("/work/{}".format(ident))
-
-@app.route('/work/<int:ident>', methods=['GET'])
-def work_view(ident):
-    rv = api.api_work_get(ident)
-    entity = json.loads(rv.data.decode('utf-8'))
-    return render_template('work_view.html', work=entity)
-
-@app.route('/release/<int:ident>', methods=['GET'])
-def release_view(ident):
-    rv = api.api_release_get(ident)
-    entity = json.loads(rv.data.decode('utf-8'))
-    return render_template('release_view.html', release=entity)
-
-@app.route('/release/<int:ident>/changelog', methods=['GET'])
-def release_changelog(ident):
-    rv = api.api_release_get(ident)
-    release = json.loads(rv.data.decode('utf-8'))
-    rv = api.api_release_changelog(ident)
-    changelog_entries = json.loads(rv.data.decode('utf-8'))
-    return render_template('release_changelog.html', release=release,
-        changelog_entries=changelog_entries)
-
-@app.route('/release/random', methods=['GET'])
-def release_random():
-    rv = api.api_release_random()
-    ident = rv.location.split('/')[-1]
-    return redirect("/release/{}".format(ident))
-
 @app.route('/container/create', methods=['GET'])
 def container_create_view():
     return render_template('container_add.html')
@@ -59,23 +22,51 @@ def container_create():
     container = json.loads(rv.data.decode('utf-8'))
     return redirect("/container/{}".format(container['id']))
 
-@app.route('/creator/<int:ident>', methods=['GET'])
-def creator_view(ident):
-    rv = api.api_creator_get(ident)
-    entity = json.loads(rv.data.decode('utf-8'))
-    return render_template('creator_view.html', creator=entity)
-
 @app.route('/container/<int:ident>', methods=['GET'])
 def container_view(ident):
     rv = api.api_container_get(ident)
     entity = json.loads(rv.data.decode('utf-8'))
     return render_template('container_view.html', container=entity)
 
+@app.route('/creator/random', methods=['GET'])
+def creator_random():
+    """Not actually random, just a dummy example"""
+    return redirect("/creator/f1f046a3-45c9-4b99-adce-000000000002")
+
+@app.route('/creator/<int:ident>', methods=['GET'])
+def creator_view(ident):
+    rv = api.api_creator_get(ident)
+    entity = json.loads(rv.data.decode('utf-8'))
+    return render_template('creator_view.html', creator=entity)
+
 @app.route('/file/<int:ident>', methods=['GET'])
 def file_view(ident):
     rv = api.api_file_get(ident)
     entity = json.loads(rv.data.decode('utf-8'))
     return render_template('file_view.html', file=entity)
+@app.route('/work/create', methods=['GET'])
+def work_create():
+    return render_template('work_add.html')
+
+@app.route('/release/<int:ident>', methods=['GET'])
+def release_view(ident):
+    rv = api.api_release_get(ident)
+    entity = json.loads(rv.data.decode('utf-8'))
+    return render_template('release_view.html', release=entity)
+
+@app.route('/release/<int:ident>/changelog', methods=['GET'])
+def release_changelog(ident):
+    rv = api.api_release_get(ident)
+    release = json.loads(rv.data.decode('utf-8'))
+    rv = api.api_release_changelog(ident)
+    changelog_entries = json.loads(rv.data.decode('utf-8'))
+    return render_template('release_changelog.html', release=release,
+        changelog_entries=changelog_entries)
+
+@app.route('/release/random', methods=['GET'])
+def release_random():
+    """Not actually random, just a dummy example"""
+    return redirect("/release/f1f046a3-45c9-4b99-3333-000000000002")
 
 @app.route('/editgroup/<int:ident>', methods=['GET'])
 def editgroup_view(ident):
@@ -83,6 +74,17 @@ def editgroup_view(ident):
     entity = json.loads(rv.data.decode('utf-8'))
     return render_template('editgroup_view.html', editgroup=entity)
 
+@app.route('/work/random', methods=['GET'])
+def work_random():
+    """Not actually random, just a dummy example"""
+    return redirect("/work/f1f046a3-45c9-4b99-3333-000000000002")
+
+@app.route('/work/<int:ident>', methods=['GET'])
+def work_view(ident):
+    rv = api.api_work_get(ident)
+    entity = json.loads(rv.data.decode('utf-8'))
+    return render_template('work_view.html', work=entity)
+
 @app.route('/editgroup/current', methods=['GET'])
 def editgroup_current():
     eg = api.get_or_create_editgroup()
diff --git a/python/fatcat/sql.py b/python/fatcat/sql.py
deleted file mode 100644
index 9b1922ba..00000000
--- a/python/fatcat/sql.py
+++ /dev/null
@@ -1,150 +0,0 @@
-
-import json
-import time
-import random
-import hashlib
-from sqlalchemy.orm.session import make_transient
-from fatcat import db
-import fatcat.api
-from fatcat.models import *
-
-def populate_db():
-    admin_editor = Editor(id=1, username="admin", is_admin=True)
-    db.session.add(admin_editor)
-    db.session.commit()
-
-def add_crossref_via_model(meta):
-
-    title = meta['title'][0]
-
-    # authors
-    author_revs = []
-    author_ids = []
-    for am in meta['author']:
-        ar = CreatorRev(
-            name="{} {}".format(am['given'], am['family']),
-            sortname="{}, {}".format(am['family'], am['given']),
-            orcid=None)
-        author_revs.append(ar)
-        author_ids.append(CreatorIdent(rev=ar))
-
-    # container
-    container = ContainerRev(
-        issn=meta['ISSN'][0],
-        name=meta['container-title'][0],
-        #container_id=None,
-        publisher=meta['publisher'],
-        sortname=meta['short-container-title'][0])
-    container_id = ContainerIdent(rev=container)
-
-    # work and release
-    work = WorkRev(title=title)
-    work_id = WorkIdent(rev=work)
-    release = ReleaseRev(
-        title=title,
-        creators=[ReleaseContrib(creator=a) for a in author_ids],
-        # XXX: work=work,
-        container=container_id,
-        release_type=meta['type'],
-        doi=meta['DOI'],
-        date=meta['created']['date-time'],
-        license=meta.get('license', [dict(URL=None)])[0]['URL'] or None,
-        issue=meta.get('issue', None),
-        volume=meta.get('volume', None),
-        pages=meta.get('page', None))
-    release_id = ReleaseIdent(rev=release)
-    work.primary_release = release_id
-    release.extra_json = json.dumps({
-        'crossref': {
-            'links': meta.get('link', []),
-            'subject': meta['subject'],
-            'type': meta['type'],
-            'alternative-id': meta.get('alternative-id', []),
-        }
-    }, indent=None).encode('utf-8')
-
-    # references
-    for i, rm in enumerate(meta.get('reference', [])):
-        ref = ReleaseRef(
-            release_rev=release,
-            doi=rm.get("DOI", None),
-            index=i+1,
-            # TODO: how to generate a proper stub here from k/v metadata?
-            stub="| ".join(rm.values()))
-        release.refs.append(ref)
-
-    db.session.add_all([work, work_id, release, release_id, container,
-        container_id])
-    db.session.add_all(author_revs)
-    db.session.add_all(author_ids)
-    db.session.commit()
-
-def accept_editgroup(eg):
-
-    # check if already accepted
-    # XXX: add a test for this
-    assert ChangelogEntry.query.filter(ChangelogEntry.editgroup_id==eg.id).count() == 0
-
-    # start transaction (TODO: explicitly?)
-
-    # for each entity type:
-    for cls in (WorkEdit, ReleaseEdit, CreatorEdit, ContainerEdit, FileEdit):
-        edits = cls.query.filter(cls.editgroup_id==eg.id).all()
-        # for each entity edit->ident:
-        for edit in edits:
-            # update entity ident state (activate, redirect, delete)
-            edit.ident.is_live = True
-            edit.ident.rev_id = edit.rev_id
-            edit.ident.redirect_id = edit.redirect_id
-            db.session.add(edit.ident)
-
-    # append log/changelog row
-    cle = ChangelogEntry(
-        editgroup_id=eg.id,
-        # TODO: is this UTC?
-        timestamp=int(time.time()))
-    db.session.add(cle)
-
-    # update edit group state
-    db.session.add(eg)
-
-    # no longer "active"
-    eg.editor.active_editgroup = None
-    db.session.add(eg.editor)
-
-    db.session.commit()
-
-def merge_works(left_id, right_id, editgroup=None):
-    """Helper to merge two works together."""
-    left = WorkIdent.query.get_or_404(left_id)
-    right = WorkIdent.query.get_or_404(right_id)
-    assert left.is_live and right.is_live
-    assert left.rev and right.rev
-    assert (left.redirect_id is None) and (right.redirect_id is None)
-
-    if editgroup is None:
-        editgroup = fatcat.api.get_or_create_editgroup()
-
-    releases = ReleaseIdent.query\
-        .join(ReleaseIdent.rev)\
-        .filter(ReleaseRev.work_ident_id==right_id)\
-        .filter(ReleaseIdent.is_live==True)\
-        .all()
-
-    # update all right releases to point to left
-    for release_ident in releases:
-        rev = release_ident.rev
-        old_id = rev.id
-        db.session.expunge(rev)
-        make_transient(rev)
-        rev.id = None
-        rev.parent = old_id
-        rev.work_ident_id = left.id
-        re = ReleaseEdit(editgroup=editgroup, ident=release_ident, rev=rev)
-        db.session.add_all([rev, re])
-
-    # redirect right id to left (via editgroup)
-    neww = WorkEdit(editgroup=editgroup, ident=right,
-        rev=left.rev, redirect_id=left.id)
-
-    db.session.add_all([neww])