mediasermon_models.py

"""Place your SQLAlchemy models in this file."""

'''
PROD/live environment EXPORTING:

    pg_dump -U postgres --no-owner --no-acl kjvrvg_production > kjvrvg_dev.pgsql

PROD/live environment IMPORTING:

    sudo -i
    supervisor stop all
    sudo -u postgres psql
    psql postgres
    alter role wsgi superuser;
    drop database kjvrvg_production;
    create database kjvrvg_production;
    sudo -i -u wsgi
    cd /srv/pyramid/kjvrvg
    psql kjvrvg_production < kjvrvg_dev.pgsql > /dev/null
    reboot

DEVELOPER machine EXPORTING:

    "/Applications/Postgres.app/Contents/Versions/9.5/bin/pg_dump" -p5432 -U postgres --no-owner --no-acl kjvrvg_dev > kjvrvg_dev.pgsql

DEVELOPER machine IMPORTING:

    "/Applications/Postgres.app/Contents/Versions/9.5/bin/psql"
    drop database kjvrvg_dev;
    create database kjvrvg_dev;
    "/Applications/Postgres.app/Contents/Versions/9.5/bin/psql" kjvrvg_dev < kjvrvg_dev.pgsql > /dev/null

handy commands:

sudo -i
sudo -u postgres -i
dropdb -U postgres -i kjvrvg_production

'''

import datetime
from uuid import uuid4

from sqlalchemy import Column, Unicode, String, Integer, ForeignKey, UnicodeText, Boolean
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import relationship

from websauna.system.model.meta import Base
from websauna.system.model.columns import UTCDateTime

class Book(Base):

    #: The table in the database
    __tablename__ = "book"

    #: Database primary key for the row (running counter)
    id = Column(Integer, autoincrement=True, primary_key=True)

    #: absolute index in the bible, i.e. Matthew == 40
    absolute_id = Column(Integer)

    #: Publicly exposed non-guessable
    uuid = Column(UUID(as_uuid=True), default=uuid4)

    #: book of Bible in base language -- English
    basename = Column(Unicode(64), default=None)

    #: Relationship mapping between book and chapter.
    #: Each chapter can have only one book.
    #: Deleting book deletes its chapters.
    chapters = relationship("Chapter",
                           back_populates="book",
                           lazy="dynamic",
                           cascade="all, delete-orphan",
                           single_parent=True)

    def __repr__(self):
        return "#{}: {}".format(self.basename, self.uuid)

    def __str__(self):
        """Python default and admin UI string presentation."""
        return self.basename


class Chapter(Base):

    #: The table in the database
    __tablename__ = "chapter"

    #: Database primary key for the row (running counter)
    id = Column(Integer, autoincrement=True, primary_key=True)

    #: absolute index in the book, i.e. Matthew 1 == 1
    absolute_id = Column(Integer)

    #: Publicly exposed non-guessable id
    uuid = Column(UUID(as_uuid=True), default=uuid4)

    #: chapter of Bible in base language -- English
    basename = Column(Unicode(64), default=None)

    #: Which book this chapter is part of
    book_id = Column(Integer, ForeignKey('book.id'))
    book = relationship("Book", back_populates="chapters")

    #: Relationship mapping between mediachapter and chapter.
    #: Each mediachapter can have only one chapter.
    #: Deleting chapter deletes its mediachapter.
    mediachapter = relationship("MediaChapter",
                           back_populates="chapter",
                           lazy="dynamic",
                           cascade="all, delete-orphan",
                           single_parent=True)

    def __repr__(self):
        """Shell and debugger presentation."""
        return "{} {}".format(self.book.basename, self.id)

    def __str__(self):
        """Python default and admin UI string presentation."""
        if hasattr(self.book, 'basename'):
            return "{} {}".format(self.book.basename, self.absolute_id)
        else:
            return "{} {}".format('(not set)', self.absolute_id)


class MediaChapter(Base):

    #: The table in the database
    __tablename__ = "mediachapter"

    #: Database primary key for the row (running counter)
    id = Column(Integer, autoincrement=True, primary_key=True)

    #: Publicly exposed non-guessable
    uuid = Column(UUID(as_uuid=True), default=uuid4)

    #: chapter of Bible in localized language
    localizedname = Column(Unicode(128), default=None)

    #: path
    path = Column(Unicode(384), default=None)

    # full iso language-locale identifier i.e. zh-Hans-US
    language_id = Column(String(16), default=None)

    # name of the person presenting the material
    presenter_name = Column(Unicode(64), default=None)

    #name of the source material i.e. "Reina Valera Gomez"
    source_material = Column(Unicode(128), default=None)

    #: Which chapter this media is part of
    chapter_id = Column(Integer, ForeignKey('chapter.id'))
    chapter = relationship("Chapter", back_populates="mediachapter")

    def __repr__(self):
        """Shell and debugger presentation."""
        return '{} ({}) {} <{}>'.format(self.localizedname, self.language_id, str(self.uuid), self.path)

    def __str__(self):
        """Python default and admin UI string presentation."""
        return '{} ({}) presenter: {} source: {}  <{}>'.format(self.localizedname, self.language_id, self.presenter_name, self.source_material, self.path)


class MediaGospel(Base):

    #: The table in the database
    __tablename__ = "mediagospel"

    #: Database primary key for the row (running counter)
    id = Column(Integer, autoincrement=True, primary_key=True)

    #: Publicly exposed non-guessable
    uuid = Column(UUID(as_uuid=True), default=uuid4)

    #: name of presentation in its native language
    localizedname = Column(Unicode(128), default=None)

    #: path
    path = Column(Unicode(384), default=None)

    # full iso language-locale identifier i.e. zh-Hans-US
    language_id = Column(String(16), default=None)

    # name of the person presenting the material
    presenter_name = Column(Unicode(64), default=None)


    def __repr__(self):
        """Shell and debugger presentation."""
        return '{} ({}) {} <{}>'.format(self.localizedname, self.language_id, str(self.uuid), self.path)

    def __str__(self):
        """Python default and admin UI string presentation."""
        return '{} ({}) presenter: {} <{}>'.format(self.localizedname, self.language_id, self.presenter_name, self.path)



class Music(Base):

    #: The table in the database
    __tablename__ = "music"

    #: Database primary key for the row (running counter)
    id = Column(Integer, autoincrement=True, primary_key=True)

    #: absolute index in the list, i.e. Matthew 1 == 1
    absolute_id = Column(Integer)

    #: Publicly exposed non-guessable id
    uuid = Column(UUID(as_uuid=True), default=uuid4)

    #: psalms project name in base language -- English
    basename = Column(Unicode(64), default=None)

    #: Relationship mapping between mediamusic and music.
    #: Each mediamusic can have only one music.
    #: Deleting music deletes its mediamusic.
    mediamusic = relationship("MediaMusic",
                           back_populates="music",
                           lazy="dynamic",
                           cascade="all, delete-orphan",
                           single_parent=True)


    def __repr__(self):
        return "#{}: {}".format(self.basename, self.uuid)

    def __str__(self):
        """Python default and admin UI string presentation."""
        return self.basename


class MediaMusic(Base):

    #: The table in the database
    __tablename__ = "mediamusic"

    #: Database primary key for the row (running counter)
    id = Column(Integer, autoincrement=True, primary_key=True)

    #: Publicly exposed non-guessable
    uuid = Column(UUID(as_uuid=True), default=uuid4)

    #: title of Psalm in localized language
    localizedname = Column(Unicode(128), default=None)

    #: path
    path = Column(Unicode(384), default=None)

    # full iso language-locale identifier i.e. zh-Hans-US
    language_id = Column(String(16), default=None)

    # name of the person presenting the material
    presenter_name = Column(Unicode(64), default=None)

    #name of the source material i.e. "Reina Valera Gomez"
    source_material = Column(Unicode(128), default=None)

    #: Which music this mediamusic is part of
    music_id = Column(Integer, ForeignKey('music.id'))
    music = relationship("Music", back_populates="mediamusic")

    def __repr__(self):
        """Shell and debugger presentation."""
        return '{} ({}) {} <{}>'.format(self.localizedname, self.language_id, str(self.uuid), self.path)

    def __str__(self):
        """Python default and admin UI string presentation."""
        return '{} ({}) presenter: {} source: {}  <{}>'.format(self.localizedname, self.language_id, self.presenter_name, self.source_material, self.path)


class Church(Base):

    #: The table in the database
    __tablename__ = "church"

    #: Database primary key for the row (running counter)
    id = Column(Integer, autoincrement=True, primary_key=True)

    #: Publicly exposed non-guessable id
    uuid = Column(UUID(as_uuid=True), default=uuid4)

    #: church name
    church_name = Column(Unicode(128), default=None)

    #: Relationship mapping between mediasermon and church.
    #: Each mediasermon can have only one church.
    #: Deleting church deletes its mediasermon.
    mediasermon = relationship("MediaSermon",
                           back_populates="church",
                           lazy="dynamic",
                           cascade="all, delete-orphan",
                           single_parent=True)

    #: Relationship mapping between leader and church.
    #: Each leader can have only one church.
    #: Deleting church deletes its leaders.
    leader = relationship("Leader",
                           back_populates="church",
                           lazy="dynamic",
                           cascade="all, delete-orphan",
                           single_parent=True)

    def __repr__(self):
        return "#{}: {}".format(self.church_name, self.uuid)

    def __str__(self):
        """Python default and admin UI string presentation."""
        return self.church_name


class MediaSermon(Base):

    #: The table in the database
    __tablename__ = "mediasermon"

    #: Database primary key for the row (running counter)
    id = Column(Integer, autoincrement=True, primary_key=True)

    #: Publicly exposed non-guessable
    uuid = Column(UUID(as_uuid=True), default=uuid4)

    #: title of Psalm in localized language
    title = Column(Unicode(128), default=None)

    # name of the person presenting the material
    # presenter_name = Column(Unicode(64), default=None)

    #: date presented
    date_presented = Column(UTCDateTime, default=None)

    #: path
    path = Column(Unicode(384), default=None)

    #: Which church this mediasermon is part of
    church_id = Column(Integer, ForeignKey('church.id'))
    church = relationship("Church", back_populates="mediasermon")

    leader_id = Column(Integer, ForeignKey('leader.id', ondelete="SET NULL"))
    leader = relationship("Leader", uselist=False)

    def __repr__(self):
        """Shell and debugger presentation."""
        return '{} ({}) {}'.format(self.title, str(self.uuid), self.path)

    def __str__(self):
        """Python default and admin UI string presentation."""
        if hasattr(self, 'leader'):
            return '{} presenter: {} date: {} <{}>'.format(self.title, self.leader, self.date_presented, self.path)
        else:
            return '{} presenter: {} date: {} <{}>'.format(self.title, '(null)', self.date_presented, self.path)



class Leader(Base):

    #: The table in the database
    __tablename__ = "leader"

    #: Database primary key for the row (running counter)
    id = Column(Integer, autoincrement=True, primary_key=True)

    #: Publicly exposed non-guessable
    uuid = Column(UUID(as_uuid=True), default=uuid4)

    #: Pastor, Brother, Deacon
    title = Column(Unicode(64), default=None)

    # name of the church leader
    first_name = Column(Unicode(64), default=None)
    last_name = Column(Unicode(64), default=None)

    #: Which church this leader is part of
    church_id = Column(Integer, ForeignKey('church.id'))
    church = relationship("Church", back_populates="leader")

    def __repr__(self):
        """Shell and debugger presentation."""
        return '{} ({})'.format(self.title, str(self.uuid))

    def __str__(self):
        """Python default and admin UI string presentation."""
        return '{} {} {} <{}>'.format(self.title, self.first_name, self.last_name, str(self.uuid))



class LanguageIdentifier(Base):
    __tablename__ = "languageidentifier"

    #: Database primary key for the row (running counter)
    id = Column(Integer, autoincrement=True, primary_key=True)

    #: Publicly exposed non-guessable
    uuid = Column(UUID(as_uuid=True), default=uuid4)

    # full iso language-locale identifier i.e. zh-Hans-US
    identifier = Column(String(16), default=None)

    supported = Column(Boolean, default=None)

    def __repr__(self):
        """Shell and debugger presentation."""
        return '< {} > supported: {}'.format(self.identifier, str(self.supported))

    def __str__(self):
        """Python default and admin UI string presentation."""
        return '< {} > supported: {}'.format(self.identifier, str(self.supported))