class MultiRouteTrip(db.Model):
"""
Some transit trips serve more than one route. This experimental file
indicates routes that a trip is associated with, in addition to the
route_id identified with this trip in trips.txt.
Requires: added_route_id, trip_id
Relies on: Route, Trip
Reference: https://github.com/mbta/gtfs-documentation/blob/master/reference/gtfs.md#multi_route_tripstxt
"""
id = db.Column(db.Integer, primary_key=True)
added_route_id = db.Column(db.String(64),
db.ForeignKey("route.route_id"),
nullable=False,
index=True)
route = db.relationship("Route", backref="multi_trips")
trip_id = db.Column(db.String(128),
db.ForeignKey("trip.trip_id"),
nullable=False,
index=True)
trip = db.relationship("Trip", backref="multi_trips")
def __init__(self, added_route_id: str, trip_id: str):
self.added_route_id = added_route_id
self.trip_id = trip_id
def __repr__(self):
return f"<MultiRouteTrip: Route {self.added_route_id}, Trip {self.trip_id}>"
class Comment(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
author_id = db.Column(db.Integer, db.ForeignKey("user.id"))
post_id = db.Column(db.Integer, db.ForeignKey("post.id"))
content = db.Column(db.Text, nullable=False)
created = db.Column(db.DateTime(timezone=True), server_default=now())
author = db.relationship("User")
post = db.relationship("Post")
class StopTime(db.Model):
"""
Times that a vehicle arrives at and departs from stops for each trip.
Requires: trip_id, arrival_time, departure_time, stop_id, stop_sequence
Relies on: Trip, Stop
Reference: https://github.com/google/transit/blob/master/gtfs/spec/en/reference.md#stop_timestxt
"""
id = db.Column(db.Integer, primary_key=True)
trip_id = db.Column(db.String(128),
db.ForeignKey("trip.trip_id"),
nullable=False)
trip = db.relationship("Trip", backref="times")
arrival_time = db.Column(db.Integer(),
nullable=False) # Seconds since 00:00:00
departure_time = db.Column(db.Integer(),
nullable=False) # Seconds since 00:00:00
stop_id = db.Column(db.String(64),
db.ForeignKey("stop.stop_id"),
nullable=False)
stop = db.relationship("Stop", backref="times")
stop_sequence = db.Column(db.Integer(), nullable=False)
stop_headsign = db.Column(db.String(128), nullable=True)
pickup_type = db.Column(db.Enum(PickupDropOffType), nullable=True)
drop_off_type = db.Column(db.Enum(PickupDropOffType), nullable=True)
shape_dist_traveled = db.Column( # Distance traveled (in meters) from the first stop to this stop
db.Float(),
nullable=True)
timepoint = db.Column( # 0 = times are approximate, 1 = times are exact
db.SmallInteger(),
nullable=True)
checkpoint_id = db.Column(db.String(16),
db.ForeignKey("checkpoint.checkpoint_id"),
nullable=True)
checkpoint = db.relationship("Checkpoint", backref="times")
def __init__(
self,
trip_id: str,
arrival_time: datetime.time,
departure_time: datetime.time,
stop_id: str,
stop_sequence: int,
**kwargs,
):
self.trip_id = trip_id
self.arrival_time = arrival_time
self.departure_time = departure_time
self.stop_id = stop_id
self.stop_sequence = stop_sequence
for fieldname, value in kwargs.items():
setattr(self, fieldname, value)
def __repr__(self):
return (
f"<StopTime: {self.arrival_time}->{self.departure_time} @ {self.stop_id}>"
)
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
author_id = db.Column(db.Integer, db.ForeignKey("user.id"))
title = db.Column(db.String(500), nullable=False)
body = db.Column(db.Text, nullable=False)
image_path = db.Column(db.String(200), nullable=True)
created = db.Column(db.DateTime(timezone=True), server_default=now())
author = db.relationship("User")
tags = db.relationship(
"Tag", secondary=post_tag, backref=db.backref("posts", lazy=True)
)
class SlackChannel(db.Model):
"""
作成者: kazu
概要: Slack内のチャネル情報を保存するテーブル
"""
__tablename__ = 'slack_channels'
__table_args__ = (CheckConstraint('updated_at >= created_at'), )
id = db.Column(db.Integer, primary_key=True, autoincrement=True) # 主キー
channel_id = db.Column(db.String(50), index=True, nullable=False)
name = db.Column(db.String(50))
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
updated_at = db.Column(db.DateTime,
nullable=False,
default=datetime.now,
onupdate=datetime.now)
channel = db.relationship('SlackMessage', backref='channel', lazy=True)
member = db.relationship('SlackChannelMember',
backref='channel',
lazy=True)
def __init__(self, channel_id, name, created_at, updated_at):
self.channel_id = channel_id
self.name = name
self.created_at = created_at
self.updated_at = updated_at
def __str__(self):
return f"id = {self.id}, channel_id = {self.channel_id}, name = {self.id}, created_at = {self.created_at}, updated_at = {self.updated_at}"
@classmethod
def select_channel_id(cls, channel_id):
return db.session.query(cls).with_entities(
cls.id).filter(cls.channel_id == channel_id).first()
@classmethod
def insert_channel(cls, channel_id, name):
target = SlackChannel(channel_id=channel_id,
name=name,
created_at=datetime.now(),
updated_at=datetime.now())
db.session.add(target)
db.session.commit()
@classmethod
def update_channel_name(cls, channel_id, name):
channel = db.session.query(cls).filter(
cls.channel_id == channel_id).first()
channel.name = name
channel.updated_at = datetime.now()
db.session.commit()
return channel
class Route(db.Model):
"""
A transit route
Requires: route_id, agency_id, route_long_name, route_type
Relies on: Agency, Line
Reference: https://github.com/google/transit/blob/master/gtfs/spec/en/reference.md#routestxt
"""
route_id = db.Column(db.String(64), primary_key=True)
agency_id = db.Column(db.Integer,
db.ForeignKey("agency.agency_id"),
nullable=False)
agency = db.relationship("Agency", backref="routes")
route_short_name = db.Column(db.String(16), nullable=True)
route_long_name = db.Column(db.String(128), nullable=False)
route_desc = db.Column(db.Enum(RouteDescription), nullable=True)
route_type = db.Column(db.Enum(RouteType), nullable=False)
route_url = db.Column(db.String(256), nullable=True)
route_color = db.Column(db.String(8), nullable=True)
route_text_color = db.Column(db.String(8), nullable=True)
route_sort_order = db.Column(db.Integer, nullable=True)
route_fare_class = db.Column(db.Enum(FareClass), nullable=True)
line_id = db.Column(db.String(32),
db.ForeignKey("line.line_id"),
nullable=True)
line = db.relationship("Line", backref="routes")
# 0: should list route publicly, 1: should not list route publicly
listed_route = db.Column(db.SmallInteger, nullable=False)
def __init__(
self,
route_id: str,
agency_id: int,
long_name: str,
route_type: RouteType,
listed_route: int,
**kwargs,
):
self.route_id = route_id
self.agency_id = agency_id
self.route_long_name = long_name
self.route_type = route_type
self.listed_route = listed_route
for fieldname, value in kwargs.items():
setattr(self, fieldname, value)
def __repr__(self):
return f"<Route: {self.route_id}>"
class RoutePattern(db.Model):
"""
For a given route, each pair of start and end stops generally has 2 RoutePatterns - one going each direction
Requires: route_pattern_id, route_id
Relies on: Route, Trip
Reference: None
"""
route_pattern_id = db.Column(db.String(64), primary_key=True)
route_id = db.Column(db.String(64),
db.ForeignKey("route.route_id"),
nullable=False,
index=True)
route = db.relationship("Route", backref="patterns")
direction_id = db.Column(db.SmallInteger, nullable=True) # 0 or 1
route_pattern_name = db.Column(db.String(128), nullable=True)
route_pattern_time_desc = db.Column(db.String(32), nullable=True)
route_pattern_typicality = db.Column(db.Enum(RoutePatternTypicality),
nullable=True)
route_pattern_sort_order = db.Column(db.Integer, nullable=True)
representative_trip_id = db.Column(
db.String(128), nullable=True) # Not a FK because use isn't clear
def __init__(self, route_pattern_id: str, route_id: str, **kwargs):
self.route_pattern_id = route_pattern_id
self.route_id = route_id
for fieldname, value in kwargs.items():
setattr(self, fieldname, value)
def __repr__(self):
return f"<RoutePattern: {self.route_pattern_id} (Route: {self.route_id})>"
class TestModel(db.Model):
"""
A model for tools and utilities that operate on models.
Has Integer, String, Float and Enum fields
"""
test_id = db.Column(db.String(32), primary_key=True)
test_name = db.Column(db.String(64), nullable=False, unique=True)
test_type = db.Column(db.Enum(TestType), nullable=False)
test_order = db.Column(db.Integer, nullable=True)
test_dist = db.Column(db.Float, nullable=True)
geo_stub_id = db.Column(
db.Integer, db.ForeignKey("geo_stub.geo_stub_id"), nullable=True
)
geo_stub = db.relationship("GeoStub", backref="test_models")
def __init__(self, test_id: str, test_name: str, test_type: TestType, **kwargs):
self.test_id = test_id
self.test_name = test_name
self.test_type = test_type
for fieldname, value in kwargs.items():
setattr(self, fieldname, value)
def __repr__(self):
return f"<TestModel: {self.test_id} ({self.test_name})>"
class Trip(db.Model):
"""
A trip for a route in a transit system. A trip is a sequence
of two or more stops that occur during a specific time period.
Requires: route_id, service_id, trip_id
Relies on: Route, Calendar, RoutePattern
Reference: https://github.com/google/transit/blob/master/gtfs/spec/en/reference.md#tripstxt
"""
trip_id = db.Column(db.String(128), primary_key=True)
route_id = db.Column(db.String(64),
db.ForeignKey("route.route_id"),
nullable=False,
index=True)
route = db.relationship("Route", backref="trips")
service_id = db.Column(db.String(64),
db.ForeignKey("calendar.service_id"),
nullable=False,
index=True)
service = db.relationship("Calendar", backref="trips")
trip_headsign = db.Column(db.String(128), nullable=True)
trip_short_name = db.Column(db.String(16), nullable=True)
direction_id = db.Column(db.SmallInteger(), nullable=True) # 0 or 1
block_id = db.Column(db.String(64), nullable=True)
shape_id = db.Column(db.String(64), nullable=True)
wheelchair_accessible = db.Column(db.Enum(TripAccessibility),
nullable=True)
trip_route_type = db.Column(db.Enum(RouteType), nullable=True)
route_pattern_id = db.Column(
db.String(64),
db.ForeignKey("route_pattern.route_pattern_id"),
nullable=True)
route_pattern = db.relationship("RoutePattern", backref="trips")
bikes_allowed = db.Column(db.Enum(TripAccessibility), nullable=True)
def __init__(self, trip_id: str, route_id: str, service_id: str, **kwargs):
self.trip_id = trip_id
self.route_id = route_id
self.service_id = service_id
for fieldname, value in kwargs.items():
setattr(self, fieldname, value)
def __repr__(self):
return f"<Trip: {self.trip_id} (Route: {self.route_id} @ {self.service_id})>"
class User(DbBase):
__tablename__ = 'User'
id = db.Column(db.Integer, unique=True, autoincrement=True, primary_key=True)
username = db.Column(db.VARCHAR(12), nullable=False)
password = db.Column(db.VARCHAR(12), nullable=False)
avatar = db.Column(db.VARCHAR(256), default=None)
nickname = db.Column(db.VARCHAR(20), nullable=False)
blog = db.relationship('Blog', backref='user')
# 用户的关注与粉丝都是 User 时，
# sqlalchemy 无法区分主次
# 需使用 primaryjoin 与 secondaryjoin 两个参数指定
followed = db.relationship(
'User',
secondary=followers,
primaryjoin=(followers.c.follower_id == id),
secondaryjoin=(followers.c.followed_id == id),
lazy='dynamic', # 延迟求值，这样才能用 filter_by 过滤函数
backref=db.backref('followers', lazy='dynamic')
)
def __init__(self, username, password, nickname=None):
self.username = username
self.password = password
if nickname is not None:
self.nickname = nickname
else:
self.nickname = f'user_{random.randint(0, 10000)}'
@classmethod
def login(cls, username, password):
user = cls.query.filter_by(username=username).first()
if user is None:
return None
if user.password != password:
return None
g.user = user.id
return user
@classmethod
def check_id(cls, user_id):
return cls.query.filter_by(id=user_id).first()
class Reaction(db.Model):
TYPE_POST = "post"
TYPE_COMMENT = "comment"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(100), nullable=False)
reaction_type = db.Column(db.String(100), default=TYPE_POST)
user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
entity_id = db.Column(db.Integer, db.ForeignKey("post.id"))
created = db.Column(db.DateTime(timezone=True), server_default=now())
user = db.relationship("User")
class Stop(db.Model, GeoMixin):
"""
A transit stop
Requires: stop_id
Relies on: None
Reference: https://github.com/google/transit/blob/master/gtfs/spec/en/reference.md#stopstxt
"""
lonlat_field = "stop_lonlat"
stop_id = db.Column(db.String(64), primary_key=True)
stop_code = db.Column(
db.String(64), nullable=True
) # Often the same as stop_id (or shortened version thereof)
stop_name = db.Column(db.String(128), nullable=True)
tts_stop_name = db.Column(
db.String(64), nullable=True
) # Defaults to stop_name - used to resolve TTS ambiguities
stop_desc = db.Column(db.String(256), nullable=True)
platform_code = db.Column(db.String(8), nullable=True)
platform_name = db.Column(db.String(64), nullable=True)
# to retrieve lon, lat: db.session.query(func.ST_X(Stop.stop_lonlat), func.ST_Y(Stop.stop_lonlat)).first()
stop_lonlat = db.Column(Geometry("POINT"), nullable=True, index=True)
zone_id = db.Column(db.String(32), nullable=True)
stop_address = db.Column(db.String(128), nullable=True)
stop_url = db.Column(db.String(256), nullable=True)
level_id = db.Column(db.String(64), nullable=True)
location_type = db.Column(db.Enum(LocationType), nullable=True)
parent_station = db.Column(db.String(64),
db.ForeignKey("stop.stop_id"),
nullable=True)
parent = db.relationship("Stop", backref="children", remote_side=[stop_id])
wheelchair_boarding = db.Column(db.Enum(AccessibilityType), nullable=True)
municipality = db.Column(db.String(64), nullable=True)
on_street = db.Column(db.String(64), nullable=True)
at_street = db.Column(db.String(64), nullable=True)
vehicle_type = db.Column(db.Enum(RouteType), nullable=True)
stop_timezone = db.Column(
db.Enum(TimeZone),
nullable=True) # Inherits from Agency.agency_timezone if null
def __init__(self, stop_id: str, **kwargs):
self.stop_id = stop_id
for fieldname, value in kwargs.items():
setattr(self, fieldname, value)
def __repr__(self):
return f"<Stop: {self.stop_id} ({self.stop_name})>"
class CalendarAttribute(db.Model):
"""
Adds human-readable names to calendar service_ids and further information about
when they operate and how closely the service aligns to service on a typical day.
Requires: service_id, service_description, service_schedule_name, service_schedule_type
Relies on: Calendar
Reference: https://github.com/mbta/gtfs-documentation/blob/master/reference/gtfs.md#calendar_attributestxt
"""
id = db.Column(db.Integer, primary_key=True)
service_id = db.Column(db.String(64),
db.ForeignKey("calendar.service_id"),
nullable=False,
index=True)
service = db.relationship("Calendar", backref="attributes")
service_description = db.Column(db.String(64), nullable=False)
service_schedule_name = db.Column(db.String(64), nullable=False)
service_schedule_type = db.Column(db.Enum(ServiceScheduleType),
nullable=False)
service_schedule_typicality = db.Column(db.Enum(ServiceScheduleTypicality))
rating_start_date = db.Column(db.Date())
rating_end_date = db.Column(db.Date())
rating_description = db.Column(db.String(32))
def __init__(
self,
service_id: str,
service_description: str,
service_schedule_name: str,
service_schedule_type: ServiceScheduleType,
**kwargs,
):
self.service_id = service_id
self.service_description = service_description
self.service_schedule_name = service_schedule_name
self.service_schedule_type = service_schedule_type
for fieldname, value in kwargs.items():
setattr(self, fieldname, value)
def __repr__(self):
return f"<CalendarAttribute: {self.id} (Calendar {self.service_id})>"
class Project(db.Model):
"""
作成者: kazu
概要: コミュニティ内で運用されているプロジェクト名を保存しているテーブル
"""
__tablename__ = 'projects'
__table_args__ = (
CheckConstraint('updated_at >= created_at'), # チェック制約
)
id = db.Column(db.Integer, primary_key=True, autoincrement=True) # 主キー
name = db.Column(db.String(50), index=True, nullable=False)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
updated_at = db.Column(db.DateTime,
nullable=False,
default=datetime.now,
onupdate=datetime.now)
importance = db.relationship('Importance', backref='projects', lazy=True)
def __init__(self, name, created_at, updated_at):
self.name = name
self.created_at = created_at
self.updated_at = updated_at
def __str__(self):
return f"id = {self.id}, name = {self.name}, create_at={self.created_at}, update_at={self.updated_at} "
@classmethod
def select_project(cls):
raw_data = db.session.query(cls).with_entities(cls.id, cls.name).all()
result_data = []
for r in raw_data:
data = {'id': r[0], 'name': r[1]}
result_data.append(data)
return result_data
@classmethod
def select_project_by_name(cls, name):
data = db.session.query(cls).with_entities(
cls.id).filter(cls.name == name).first()
return data
class Direction(db.Model):
id = db.Column(db.Integer, primary_key=True)
route_id = db.Column(db.String(64),
db.ForeignKey("route.route_id"),
nullable=False)
route = db.relationship("Route", backref="directions")
direction_id = db.Column(db.SmallInteger, nullable=False) # 0 or 1
direction = db.Column(db.Enum(DirectionOption), nullable=False)
direction_destination = db.Column(db.String(64), nullable=False)
def __init__(
self,
route_id: str,
direction_id: int,
direction: str,
direction_destination: str,
):
self.route_id = route_id
self.direction_id = direction_id
self.direction = direction
self.direction_destination = direction_destination
def __repr__(self):
return f"<Direction: {self.route_id} ({self.direction} -> {self.direction_destination})>"
class CalendarDate(db.Model):
"""
Exceptions for the services defined in the calendar
Requires: service_id, date, exception_type
Relies on: Calendar
References:
https://github.com/google/transit/blob/master/gtfs/spec/en/reference.md#calendar_datestxt
https://github.com/mbta/gtfs-documentation/blob/master/reference/gtfs.md#calendar_datestxt
"""
id = db.Column(db.Integer, primary_key=True)
service_id = db.Column(db.String(64),
db.ForeignKey("calendar.service_id"),
nullable=False,
index=True)
service = db.relationship("Calendar", backref="dates")
date = db.Column(db.Date(), nullable=False)
exception_type = db.Column(db.Enum(DateExceptionType), nullable=False)
holidate_name = db.Column(db.String(32))
def __init__(
self,
service_id: str,
date: datetime.date,
exception_type: DateExceptionType,
**kwargs,
):
self.service_id = service_id
self.date = date
self.exception_type = exception_type
for fieldname, value in kwargs.items():
setattr(self, fieldname, value)
def __repr__(self):
return f"<CalendarDate: {self.exception_type.value} on {self.date} for {self.service_id}>"
class User(db.Model):
"""
作成者: kazu
概要: 従業員識別情報を保存しているテーブル
"""
__tablename__ = 'users'
__table_args__ = (
CheckConstraint('updated_at >= created_at'), # チェック制約
)
id = db.Column(db.Integer, primary_key=True) # 主キー
slack_id = db.Column(db.String(20), index=True, unique=True)
gmail = db.Column(db.String(50), index=True, unique=True)
created_at = db.Column(db.DateTime)
updated_at = db.Column(db.DateTime)
mail = db.relationship('Mail', backref='user', lazy=True)
information = db.relationship('Information', backref='user', lazy=True)
calendar = db.relationship('Calendar', backref='user', lazy=True)
slack_channel_member = db.relationship('SlackChannelMember',
backref='user',
lazy=True)
slack_message = db.relationship('SlackMessage', backref='user', lazy=True)
zoom_access_token = db.relationship('ZoomAccessToken',
backref='user',
lazy=True)
zoom_meeting = db.relationship('ZoomMeeting', backref='user', lazy=True)
zoom_participant = db.relationship('ZoomParticipant',
backref='user',
lazy=True)
def __init__(self, slack_id, gmail, created_at, updated_at):
self.slack_id = slack_id
self.gmail = gmail
self.created_at = created_at
self.updated_at = updated_at
def __str__(self):
return f"id = {self.id}, slack_id={self.slack_id}, gmail={self.gmail}, created_at={self.created_at}, updated_at={self.updated_at}"
@classmethod
def select_users(cls):
raw_data = db.session.query(cls).with_entities(cls.id, cls.slack_id,
cls.gmail).all()
data = {}
data['id'] = raw_data[0]
data['slack_id'] = raw_data[1]
data['gmail'] = raw_data[2]
return data
@classmethod
def select_users_by_id(cls, user_id):
return db.session.query(cls).with_entities(
cls.id, cls.slack_id, cls.gmail).filter(cls.id == user_id).first()
@classmethod
def insert_user(self, data):
target = User(gmail=data['gmail'],
slack_id=data['slack_id'],
created_at=datetime.now(),
updated_at=datetime.now())
db.session.add(target)
db.session.commit()
@classmethod
def insert_gmail(self, gmail):
target = User(gmail=gmail,
slack_id=None,
created_at=datetime.now(),
updated_at=datetime.now())
db.session.add(target)
db.session.commit()
@classmethod
def update_user(cls, user_id, information_data):
user = db.session.query(cls).filter(cls.id == user_id).first()
if 'gmail' in information_data:
user.gmail = information_data['gmail']
if 'slack_id' in information_data:
user.slack_id = information_data['slack_id']
user.updated_at = datetime.now()
db.session.commit()
@classmethod
def check_user_mail(cls, gmail):
return db.session.query(cls).with_entities(
cls.id).filter(cls.gmail == gmail).first()
@classmethod
def check_user_slack_id(cls, slack_id):
return db.session.query(cls).with_entities(
cls.id).filter(cls.slack_id == slack_id).first()
@classmethod
def delete_by_id(cls, user_id):
db.session.query(cls).filter(cls.id == user_id).delete()
db.session.commit()
