SA Bookstore

bookstore.py

"""
# Bookstore

-  Book => Images

-  Book <=> Author

-  Book <=> Genre

-  Book -> Genre (Primary)
"""
from sqlalchemy import (
        Column,
        String,
        Table,
        ForeignKeyConstraint,
        ForeignKey,
        and_,
        Integer
        )
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship


Base = declarative_base()

book_author = Table('_book_author', Base.metadata,
        Column('book_isbn', String(10), ForeignKey('book.isbn')),
        Column('author_id', Integer, ForeignKey('author.id'))
        )

book_genre = Table('_book_genre', Base.metadata,
        Column('book_isbn', String(10), ForeignKey('book.isbn')),
        Column('genre_name', String, ForeignKey('genre.name'))
        )


class Genre(Base):
    __tablename__ = 'genre'

    name = Column(String, primary_key=True)

    def __repr__(self):
        if self.id:
            return 'Genre(id={self.id!r}, name={self.name!r})'.format(self=self)
        return 'Genre(name={self.name!r})'.format(self=self)


class Book(Base):
    __tablename__ = 'book'

    isbn = Column(String(10), primary_key=True)
    title = Column(String)
    primary_genre_name = Column(String)

    __table_args__ = (
        ForeignKeyConstraint(
            [isbn, primary_genre_name],
            [book_genre.c.genre_name, book_genre.c.book_isbn],
            name="fk_primary_genre", use_alter=True
        ),
    )

    authors = relationship("Author", secondary=book_author, backref="books")

    genres = relationship(Genre,
            secondary=book_genre,
            primaryjoin=isbn==book_genre.c.book_isbn,
            secondaryjoin=book_genre.c.genre_name==Genre.name,
            backref='books')

    images = relationship("Image", backref="book")

    primary_genre = relationship(Genre, secondary=book_genre,
            primaryjoin=and_(
                isbn==book_genre.c.book_isbn,
                primary_genre_name==book_genre.c.genre_name
                ),
            secondaryjoin=book_genre.c.genre_name==Genre.name,
            backref='primary_for',
            uselist=False)

    def __repr__(self):
        return 'Book(title={self.title!r}, isbn={self.isbn!r}>'.format(self=self)


class Author(Base):
    __tablename__ = 'author'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    books = relationship(Book,
            secondary=book_author,
            primaryjoin=Author.id==book_genre.c.author_id,
            secondaryjoin=book_genre.c.book_isbn==isbn,
            backref='authors')

    def __repr__(self):
        if self.id:
            return 'Author(id={self.id!r}, name={self.name!r})'.format(self=self)
        return 'Author(name={self.name!r})'.format(self=self)


class Image(Base):
    __tablename__ = 'image'

    name = Column(String)
    url = Column(String, primary_key=True)
    book_id = Column(String(10), ForeignKey('book.isbn'))

    def __repr__(self):
        return 'Image(name={self.name!r}, url={self.url!r})'.format(self=self)


__all__ = [Book, Author, Genre, Image]