Miscellany

from sqlalchemy import Column, ForeignKey, String, Integer, Float, Date, Time, \
  ForeignKeyConstraint
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref

Base = declarative_base()

class InnoDBTable(object):
    __table_args__ = {'mysql_engine': 'InnoDB'}

class SpotType(Base, InnoDBTable):
    __tablename__ = 'spot_type'

    id = Column('spot_type_id', Integer, primary_key=True, autoincrement=True)
    description = Column('description', String(25))

    def __init__(self, description=None):
        self.description = description

class Spot(Base, InnoDBTable):
    __tablename__ = 'spot'

    id = Column('spot_id', Integer, primary_key=True, autoincrement=True)
    name = Column('name', String(100))
    latitude = Column('latitude', Float, nullable=False)
    longitude = Column('longitude', Float, nullable=False)
    spot_type_id = Column('spot_type_id', Integer, ForeignKey('spot_type'), nullable=False)

    spot_type = relationship(SpotType, backref=backref('spots'), order_by=id)

    def __init__(self, latitude, longitude, spot_type, name=None):
        self.latitude = latitude
        self.longitude = longitude
        self.spot_type = spot_type
        self.name = name

class FishSpecies(Base, InnoDBTable):
    __tablename__ = 'fish_species'

    id = Column('fish_species_id', Integer, primary_key=True, autoincrement=True)
    name = Column('name', String(50), nullable=False)

    def __init__(self, name):
        self.name = name

class FishCatch(Base, InnoDBTable):
    __tablename__ = 'fish_catch'
    __table_args__ = (
        ForeignKeyConstraint(['trip_id', 'spot_id'], ['trip_spots.trip_id', 'trip_spots.spot_id']),
        InnoDBTable.__table_args__,
    )

    id = Column('fish_catch_id', Integer, primary_key=True, autoincrement=True)
    fish_species_id = Column('fish_species_id', Integer, ForeignKey('fish_species'), nullable=False)
    trip_id = Column('trip_id', Integer, ForeignKey('trip'), nullable=False)
    spot_id = Column('spot_id', Integer, ForeignKey('spot'), nullable=False)
    weight = Column('weight', Float)
    length = Column('length', Float)
    bait = Column('bait', String(250))

    species = relationship(FishSpecies, backref=backref('fish_catches'), order_by=id)

    def __init__(self, species_id, weight=None, length=None, bait=None):
        self.species_id = species_id
        self.weight = weight
        self.length = length
        self.bait = bait

class TripSpots(Base, InnoDBTable):
    __tablename__ = 'trip_spots'

    trip_id = Column('trip_id', Integer, ForeignKey('trip'), primary_key=True)
    spot_id = Column('spot_id', Integer, ForeignKey('spot'), primary_key=True)
    start_time = Column('start_time', Time)
    end_time = Column('end_time', Time)

    def __init__(self, trip_id, spot_id, start_time=None, end_time=None):
        self.trip_id = trip_id
        self.spot_id = spot_id
        self.start_time = start_time
        self.end_time = end_time

class Trip(Base, InnoDBTable):
    __tablename__ = 'trip'

    id = Column('trip_id', Integer, primary_key=True, autoincrement=True)
    start_date = Column('start_date', Date)
    end_date = Column('end_date', Date)
    unsuccessful_bait = Column('unsuccessful_bait', String(500))
    notes = Column('notes', String(1000))

    spots = relationship(Spot, secondary=TripSpots, backref='trips')

    def __init__(self, start_date=None, end_date=None, unsuccessful_bait=None, notes=None):
        self.start_date = start_date
        self.end_date = end_date
        self.unsuccessful_bait = unsuccessful_bait
        self.notes = notes