class Bookmark(ResourceMixin, db.Model):
__tablename__ = 'bookmarks'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
ride_id = db.Column(db.Integer,
db.ForeignKey('rides.id',
onupdate="CASCADE",
ondelete="CASCADE"),
index=True,
nullable=False)
user_id = db.Column(db.Integer,
db.ForeignKey('users.id',
onupdate="CASCADE",
ondelete="CASCADE"),
index=True,
nullable=False)
class Review(ResourceMixin, db.Model):
__tablename__ = 'reviews'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
description = db.Column(db.String, nullable=False)
rating = db.Column(db.Integer, nullable=False)
# Relationships
ride_id = db.Column(db.Integer, db.ForeignKey('rides.id',
onupdate="CASCADE",
ondelete="CASCADE"),
index=True, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('users.id',
onupdate="CASCADE",
ondelete="CASCADE"),
index=True, nullable=False)
class Sponsor(ResourceMixin, db.Model):
__tablename__ = 'sponsors'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
name = db.Column(db.String, nullable=False)
@classmethod
def find_if_none_create(cls, sponsors):
found = db.session.query(Sponsor).filter(
Sponsor.name.in_(sponsors)).all()
if not found:
db.session.begin()
for name in sponsors:
new = Sponsor()
new.name = name
db.session.add(new)
db.session.commit()
return db.session.query(Sponsor).filter(
Sponsor.name.in_(sponsors)).all()
return found
class Ride(ResourceMixin, db.Model):
__tablename__ = 'rides'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
ride_name = db.Column(db.String, nullable=False)
min_height_in_cm = db.Column(db.Float, nullable=False)
min_age = db.Column(db.Integer, nullable=False)
average_rating = db.Column(db.Float, default=0)
# Relationships
ride_type_id = db.Column(db.Integer,
db.ForeignKey('ride_types.id',
onupdate="CASCADE",
ondelete="CASCADE"),
index=True,
nullable=False)
park_id = db.Column(db.Integer,
db.ForeignKey('parks.id',
onupdate="CASCADE",
ondelete="CASCADE"),
index=True,
nullable=False)
ride_type = db.relationship(RideType, foreign_keys=[ride_type_id])
reviews = db.relationship(Review,
backref='rides',
lazy='dynamic',
passive_deletes=True)
def update_average_rating(self):
new_rating = db.session.query(
func.avg(Review.rating).label("average_rating")).filter(
Review.ride_id == self.id).first()
self.average_rating = round(new_rating[0], 1)
@classmethod
def find_by_id(cls, id):
"""
:param id: id to search
:return: Ride instance, none if not found
"""
ride = cls.query.filter_by(id=id).first()
if not ride:
return None
return ride
class Operator(db.Model):
__tablename__ = 'operators'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
name = db.Column(db.String, index=True, nullable=False)
class BusinessHours(db.Model):
__tablename__ = 'business_hours'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
opening_time = db.Column(db.Time, nullable=False)
closing_time = db.Column(db.Time, nullable=False)
class Park(ResourceMixin, db.Model):
__tablename__ = 'parks'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
name = db.Column(db.String, nullable=False)
address = db.Column(db.String, nullable=False)
location = db.Column('location', Geometry(geometry_type='POINT', srid=4326))
is_active = db.Column(db.Boolean, default=True, nullable=False)
admission_price = db.Column(db.Float, nullable=False)
# Relationships
operator_id = db.Column(db.Integer, db.ForeignKey('operators.id',
onupdate="CASCADE",
ondelete="CASCADE"),
index=True, nullable=False)
business_hours = db.Column(db.Integer, db.ForeignKey('business_hours.id',
onupdate="CASCADE",
ondelete="CASCADE"),
index=True, nullable=False)
operator = db.relationship(Operator, foreign_keys=[operator_id])
operating_hours = db.relationship(BusinessHours, foreign_keys=[business_hours])
sponsors = db.relationship(Sponsor,
secondary=park_sponsors,
backref='park_sponsors',
lazy='dynamic',
passive_deletes=True)
rides = db.relationship(Ride,
backref='park',
lazy='dynamic',
passive_deletes=True)
def __init__(self, **kwargs):
location = Location(latitude=kwargs.get('lat', 0),
longitude=kwargs.get('lng', 0))
self.location = location.to_wkt_element()
super(Park, self).__init__()
@classmethod
def get_parks_by_point(cls, point, radius=5, filters=None):
if filters is None:
filters = {}
"""
Get parks within given coordinates and radius.
:param: point: a Location instance
:param filters: filters for searching
:return: list of parks
"""
q = db.session \
.query(Park) \
.filter(db.func.ST_DWithin(Park.location,
point.to_wkt_element(),
radius)) \
final = _combine_filters(q, filters)
return final.all()
@classmethod
def get_poi_along_path(cls, lines, radius=5, filters=None):
if filters is None:
filters = {}
"""
SELECT park.*
FROM park
WHERE ST_DWithin(line.geom, park.geom, radius)
ORDER BY ST_Line_Locate_Point(line.geom, park.geom),
ST_Distance(line.geom, park.geom);
:param lines: an array of locations
:param radius: the search radius
:param filters: filters for searching
:return: list of all POI's found
"""
linestring = RoutePaths(paths=lines)
q = db.session.query(Park).join(Ride).filter(func.ST_DWithin(linestring.locations,
Park.location,
radius)) \
.order_by(
desc(func.ST_Line_Locate_Point(linestring.locations, Park.location)),
desc(func.ST_Distance(linestring.locations, Park.location)))
final = _combine_filters(q, filters)
return final.all()
@classmethod
def get_all(cls, filters):
if filters is None:
filters = {}
"""
Retrieve all active Parks.
:param filters: filters for searching
:return: Park instance
"""
q = Park.query.filter(Park.is_active == True)
final = _combine_filters(q, filters)
return final.all()
from geoalchemy2 import Geometry
from sqlalchemy import func, desc, and_
from magicride.extensions import db
from magicride.extensions.api.util_sqlalchemy import ResourceMixin
from magicride.modules.businesshours.models import BusinessHours
from magicride.modules.geo.models import Location, RoutePaths
from magicride.modules.operators.models import Operator
from magicride.modules.rides.models import Ride, RideType
from magicride.modules.sponsors.models import Sponsor
park_sponsors = db.Table('park_sponsors',
db.Column('park_id',
db.Integer,
db.ForeignKey('parks.id',
onupdate="CASCADE",
ondelete="CASCADE"),
index=True,
nullable=False),
db.Column('sponsor_id',
db.Integer,
db.ForeignKey('sponsors.id',
onupdate="CASCADE",
ondelete="CASCADE"),
index=True,
nullable=False))
class Park(ResourceMixin, db.Model):
__tablename__ = 'parks'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
class RideType(ResourceMixin, db.Model):
__tablename__ = 'ride_types'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
ride_type = db.Column(db.String, nullable=False)
class User(ResourceMixin, db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
name = db.Column(db.String, nullable=False)
# Authentication
email = db.Column(db.String(255),
unique=True,
index=True,
nullable=False,
default='')
password = db.Column(db.String(128), nullable=False, default='')
# Relationships
reviews = db.relationship(Review, backref='reviews', passive_deletes=True)
bookmarks = db.relationship(Bookmark,
backref='bookmarks',
passive_deletes=True)
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(User, self).__init__(**kwargs)
self.password = User.encrypt_password(kwargs.get('password', ''))
@classmethod
def find_with_password(cls, email, password):
"""
:param email: email to search
:param password: password in plain text
:return: User instance, none if not found
"""
user = cls.query.filter_by(email=email).first()
if not user:
return None
if check_password_hash(user.password, password):
return user
return None
@classmethod
def find_user(cls, email):
"""
:param email: email to search
:return: User instance, none if not found
"""
user = cls.query.filter_by(email=email).first()
if not user:
return None
return user
@classmethod
def find_by_id(cls, id):
"""
:param id: id to search
:return: User instance, none if not found
"""
user = cls.query.filter_by(id=id).first()
if not user:
return None
return user
@classmethod
def encrypt_password(cls, plaintext_password):
"""
Hash a plaintext string using PBKDF2.
:param plaintext_password: Password in plain text
:type plaintext_password: str
:return: str
"""
if plaintext_password:
return generate_password_hash(plaintext_password)
return None