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 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 Agency(db.Model):
"""
A transit agency
Requires: agency_id, agency_name, agency_url, agency_timezone
Relies on: None
Reference: https://github.com/google/transit/blob/master/gtfs/spec/en/reference.md#agencytxt
"""
agency_id = db.Column(db.Integer, primary_key=True)
agency_name = db.Column(db.String(64), nullable=False, unique=True)
agency_url = db.Column(db.String(256), nullable=False, unique=True)
agency_timezone = db.Column(db.Enum(TimeZone), nullable=False)
agency_lang = db.Column(db.Enum(LangCode), nullable=True)
agency_phone = db.Column(db.String(16), nullable=True)
def __init__(self, agency_id: int, name: str, url: str, timezone: TimeZone,
**kwargs):
self.agency_id = agency_id
self.agency_name = name
self.agency_url = url
self.agency_timezone = timezone
for fieldname, value in kwargs.items():
setattr(self, fieldname, value)
def __repr__(self):
return f"<Agency: {self.agency_id} ({self.agency_name})>"
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(20), nullable=False)
email = db.Column(db.String(100), unique=True, nullable=False)
password = db.Column(db.LargeBinary(128), nullable=True)
# bio = db.Column(db.String, nullable=True)
# image = db.Column(db.String, nullable=True)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
updated_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
def __init__(self, username, email, password=None, **kwargs):
db.Model.__init__(self, username=username, email=email, password=password, **kwargs)
if password:
self.set_password(password)
else:
self.password = None
def set_password(self, password):
self.password = bcrypt.generate_password_hash(password)
def check_password(self, value):
bcrypt.check_password_hash(self.password, value)
def __repr__(self):
return '<User(%r)>' % self.username
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 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 Checkpoint(db.Model):
checkpoint_id = db.Column(db.String(16), primary_key=True)
checkpoint_name = db.Column(db.String(128), nullable=False)
def __init__(self, checkpoint_id: str, checkpoint_name: str):
self.checkpoint_id = checkpoint_id
self.checkpoint_name = checkpoint_name
def __repr__(self):
return f"<Checkpoint: {self.checkpoint_id}>"
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 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 Users(db.Model):
"""定义数据模型"""
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
passwd = db.Column(db.String(80), unique=True)
role = db.Column(db.String(120), unique=True, default='tourist')
@classmethod
def find_by_name(cls, username):
return cls.query.filter_by(username=username).first()
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 ZoomAccessToken(db.Model):
"""
作成者: kazu
概要: Zoom Apiへのアクセストークンを保存するテーブル
"""
__tablename__ = 'zoom_access_tokens'
__table_args__ = (
CheckConstraint('updated_at >= created_at'), # チェック制約
)
id = db.Column(db.Integer, primary_key=True, autoincrement=True) # 主キー
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
access_token = db.Column(db.String(1000), nullable=False)
refresh_token = db.Column(db.String(1000), 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)
def __init__(self, user_id, access_token, refresh_token, created_at,
updated_at):
self.user_id = user_id
self.access_token = access_token
self.refresh_token = refresh_token
self.created_at = created_at
self.updated_at = updated_at
def __str__(self):
return f"id = {self.id}, user_id = {self.user_id}, access_token = {self.access_token}, refresh_token = {self.refresh_token}, create_at={self.created_at}, update_at={self.updated_at} "
@classmethod
def get_access_token(cls, user_id):
return db.session.query(cls).with_entities(
cls.access_token).filter(cls.user_id == user_id).first()
@classmethod
def get_refresh_token(cls, user_id):
return db.session.query(cls).with_entities(
cls.refresh_token).filter(cls.user_id == user_id).first()
@classmethod
def update_access_token(cls, user_id, access_token, refresh_token):
target = db.session.query(ZoomAccessToken).filter(
cls.user_id == user_id).first()
target.access_token = access_token
target.refresh_token = refresh_token
target.updated_at = datetime.now()
db.session.commit()
class LinkedDataset(db.Model):
"""
URLs and authentication information for linked GTFS-realtime datasets:
Trip Updates, Vehicle Positions and Service Alerts.
Requires: url, trip_updates, vehicle_positions, service_alerts, authentication_type
Relies on: None
Reference: https://github.com/mbta/gtfs-documentation/blob/master/reference/gtfs.md#linked_datasetstxt
"""
id = db.Column(db.Integer, primary_key=True)
url = db.Column(db.String(256), nullable=False)
trip_updates = db.Column(db.SmallInteger, nullable=False) # 0 or 1
vehicle_positions = db.Column(db.SmallInteger, nullable=False) # 0 or 1
service_alerts = db.Column(db.SmallInteger, nullable=False) # 0 or 1
authentication_type = db.Column(db.Enum(AuthenticationType),
nullable=False)
def __init__(
self,
url: str,
trip_updates: int,
vehicle_positions: int,
service_alerts: int,
authentication_type: AuthenticationType,
):
self.url = url
self.trip_updates = trip_updates
self.vehicle_positions = vehicle_positions
self.service_alerts = service_alerts
self.authentication_type = authentication_type
def __repr__(self):
return f"<LinkedDataset: {self.url}>"
class Line(db.Model):
"""
A transit line
Requires: line_id, line_long_name
Relies on: None
Reference: None
"""
line_id = db.Column(db.String(32), primary_key=True)
line_short_name = db.Column(db.String(16), nullable=True)
line_long_name = db.Column(db.String(128), nullable=False)
line_desc = db.Column(db.String(32), nullable=True)
line_url = db.Column(db.String(256), nullable=True)
line_color = db.Column(db.String(8), nullable=True)
line_text_color = db.Column(db.String(8), nullable=True)
line_sort_order = db.Column(db.Integer, nullable=True)
def __init__(self, line_id: str, long_name: str, **kwargs):
self.line_id = line_id
self.line_long_name = long_name
for fieldname, value in kwargs.items():
setattr(self, fieldname, value)
def __repr__(self):
return f"<Line: {self.line_id}>"
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 ZoomParticipant(db.Model):
__tablename__ = 'zoom_participants'
__table_args__ = (
CheckConstraint('updated_at >= created_at'),
UniqueConstraint('zoom_user_id', 'meeting_id'),
)
id = db.Column(db.Integer, primary_key=True, autoincrement=True) # 主キー
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
meeting_id = db.Column(db.Integer,
db.ForeignKey('zoom_meetings.id'),
unique=True)
zoom_user_id = db.Column(db.String(50), nullable=False)
zoom_name = db.Column(db.String(50), 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)
def __init__(self, user_id, meeting_id, zoom_user_id, zoom_name,
created_at, updated_at):
self.user_id = user_id
self.meeting_id = meeting_id
self.zoom_user_id = zoom_user_id
self.zoom_name = zoom_name
self.created_at = created_at
self.updated_at = updated_at
def __str__(self):
return f"id = {self.id}, user_id = {self.user_id}, meeting_id = {self.meeting_id}, zoom_user_id = {self.zoom_user_id}, zoom_name = {self.zoom_name} create_at={self.created_at}, update_at={self.updated_at} "
@classmethod
def insert_participant(cls, user_id, meeting_id, participant):
target = ZoomMeeting(user_id=user_id,
meeting_id=meeting_id,
zoom_user_id=participant['id'],
zoom_name=participant['name'],
created_at=datetime.now(),
updated_at=datetime.now())
db.session.add(target)
db.session.commit()
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 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 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 Calendar(db.Model):
"""
Identifies the days of service within a given date range
Requires: service_id, monday, tuesday, wednesday, thursday, friday, saturday, sunday, start_date, end_date
Relies on: None
Reference: https://github.com/google/transit/blob/master/gtfs/spec/en/reference.md#calendartxt
"""
service_id = db.Column(db.String(64), primary_key=True)
monday = db.Column(db.Boolean(), nullable=False)
tuesday = db.Column(db.Boolean(), nullable=False)
wednesday = db.Column(db.Boolean(), nullable=False)
thursday = db.Column(db.Boolean(), nullable=False)
friday = db.Column(db.Boolean(), nullable=False)
saturday = db.Column(db.Boolean(), nullable=False)
sunday = db.Column(db.Boolean(), nullable=False)
start_date = db.Column(db.Date(), nullable=False)
end_date = db.Column(db.Date(), nullable=False)
def __init__(
self,
service_id: str,
monday: bool,
tuesday: bool,
wednesday: bool,
thursday: bool,
friday: bool,
saturday: bool,
sunday: bool,
start_date: datetime.date,
end_date: datetime.date,
):
self.service_id = service_id
self.monday = monday
self.tuesday = tuesday
self.wednesday = wednesday
self.thursday = thursday
self.friday = friday
self.saturday = saturday
self.sunday = sunday
self.start_date = start_date
self.end_date = end_date
def __repr__(self):
return f"<Calendar: {self.service_id} ({self.start_date}-{self.end_date})>"
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 Bill(DbBase):
__tablename__ = 'Bill'
record_type = db.Column(db.VARCHAR(20), nullable=False)
amount = db.Column(db.Float, nullable=False)
alias = db.Column(db.VARCHAR(20), nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('User.id'))
bill_mod = db.Column(db.Integer, db.ForeignKey('BillMod.id'))
year = db.Column(db.Integer, nullable=False)
month = db.Column(db.Integer, nullable=False)
record_time = db.Column(db.String(10), nullable=False)
def __init__(self, record_type, amount, alias, user_id, times, bill_mod):
self.record_type = record_type
self.amount = amount
self.alias = alias
self.user_id = user_id
self.bill_mod = bill_mod
self.update_time(*times)
def update_time(self, year, month, day):
self.year = year
self.month = month
self.record_time = f'{year}-{month:0>2d}-{day:0>2d}'
class Shape(db.Model, GeoMixin):
"""
A rule for mapping vehicle travel paths, sometimes referred to as a route alignment
Requires: shape_id, shape_pt_lon, shape_pt_lat, shape_pt_sequence
Relies on: None
Reference: https://github.com/google/transit/blob/master/gtfs/spec/en/reference.md#shapestxt
"""
lonlat_field = "shape_pt_lonlat"
id = db.Column(db.Integer, primary_key=True)
shape_id = db.Column(db.String(64), nullable=False, index=True)
shape_pt_lonlat = db.Column(Geometry("POINT"), nullable=False)
# Increasing but not necessarily consecutive for each subsequent stop
shape_pt_sequence = db.Column(db.Integer(), nullable=False)
shape_dist_traveled = db.Column(db.Float(), nullable=True)
def __init__(
self,
shape_id: str,
shape_pt_lon: float,
shape_pt_lat: float,
shape_pt_sequence: int,
**kwargs,
):
self._longitude_cache = shape_pt_lon
self._latitude_cache = shape_pt_lat
self.shape_id = shape_id
self.shape_pt_lonlat = f"POINT({shape_pt_lon} {shape_pt_lat})"
self.shape_pt_sequence = shape_pt_sequence
for fieldname, value in kwargs.items():
setattr(self, fieldname, value)
def __repr__(self):
return f"<Shape: {self.shape_id} @ ({self.longitude}, {self.latitude})>"
class Teacher(db.Model):
"""定义数据模型"""
__tablename__ = 'teacher'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), unique=True)
account_name = db.Column(db.String(50), unique=True)
# 如何二选一
sex = db.Column(db.String(10))
nation = db.Column(db.String(10))
birth_date = db.Column(db.String(20))
check_in = db.Column(db.String(20))
age = db.Column(db.Integer)
working_years = db.Column(db.Integer)
address = db.Column(db.String(80))
email = db.Column(db.String(50))
f_phone = db.Column(db.String(50))
m_phone = db.Column(db.String(50))
education = db.Column(db.String(50))
graduation = db.Column(db.String(50))
status = db.Column(db.Integer, default=0)
add_date = db.Column(db.String(50),
default=datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
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()
class SlackMessage(db.Model):
"""
作成者: kazu
概要: Slack内メッセージを保存するテーブル
"""
__tablename__ = 'slack_messages'
__table_args__ = (
CheckConstraint('updated_at >= created_at'), # チェック制約
)
id = db.Column(db.Integer, primary_key=True, autoincrement=True) # 主キー
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
team_id = db.Column(db.String(20), index=True, nullable=False)
channel_id = db.Column(db.Integer, db.ForeignKey('slack_channels.id'))
event_id = db.Column(db.String(20), nullable=False, unique=True)
event_type = db.Column(db.String(20), index=True, nullable=False)
event_time = db.Column(db.Integer, nullable=False)
message_time = db.Column(db.Float(10))
file_id = db.Column(db.String(20))
file_name = db.Column(db.String(50))
file_deleted = db.Column(db.Integer, default=0)
text = db.Column(db.String(300))
reaction = 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)
def __init__(self, user_id, team_id, event_id, event_type, event_time,
message_time, channel_id, text, file_id, file_name,
file_deleted, reaction, created_at, updated_at):
self.user_id = user_id
self.team_id = team_id
self.event_id = event_id
self.event_type = event_type
self.event_time = event_time
self.message_time = message_time
self.channel_id = channel_id
self.text = text
self.file_id = file_id
self.file_name = file_name
self.file_deleted = file_deleted
self.reaction = reaction
self.created_at = created_at
self.updated_at = updated_at
def __str__(self):
return f"id = {self.id}, user_id={self.user_id}, team_id={self.team_id}, event_id={self.event_id}, event_type={self.event_type}, " \
f"event_time={self.event_time}, message_time={self.message_time}, " \
f"channel_id={self.channel_id}, text={self.text}, file_id={self.file_id}, file_name={self.file_name}, " \
f"file_deleted={self.file_deleted}, reaction={self.reaction}, create_at={self.created_at}, update_at=" \
f"{self.updated_at} "
@classmethod
def insert_message(cls, user_id, channel_id, message_data):
if 'edited' in message_data['event']:
message_data['event']['type'] = 'message_edited'
message_data['event']['event_ts'] = message_data['event'][
'edited']['ts']
if 'files' in message_data['event']:
for file in message_data['event']['files']:
target = SlackMessage(
user_id=user_id,
team_id=message_data['team_id'],
channel_id=channel_id,
event_id=message_data['event_id'],
event_type=message_data['event']['type'],
event_time=message_data['event_time'],
message_time=message_data['event']['event_ts'],
text=message_data['event']['text'],
file_id=file['id'],
file_name=file['name'],
file_deleted=0,
reaction=None,
created_at=datetime.now(),
updated_at=datetime.now())
db.session.add(target)
else:
target = SlackMessage(
user_id=user_id,
team_id=message_data['team_id'],
channel_id=channel_id,
event_id=message_data['event_id'],
event_type=message_data['event']['type'],
event_time=message_data['event_time'],
message_time=message_data['event']['event_ts'],
text=message_data['event']['text'],
file_id=None,
file_name=None,
file_deleted=0,
reaction=None,
created_at=datetime.now(),
updated_at=datetime.now())
db.session.add(target)
db.session.commit()
return target
@classmethod
def insert_message_channel(cls, user_id, channel_id, message_data):
target = SlackMessage(user_id=user_id,
team_id=message_data['team_id'],
channel_id=channel_id,
event_id=message_data['event_id'],
event_type=message_data['event']['type'],
event_time=message_data['event_time'],
message_time=None,
text=None,
file_id=None,
reaction=None,
created_at=datetime.now(),
updated_at=datetime.now())
db.session.add(target)
db.session.commit()
return target
@classmethod
def insert_message_file(cls, user_id, message_data):
target = SlackMessage(user_id=user_id,
team_id=message_data['team_id'],
channel_id=None,
event_id=message_data['event_id'],
event_type=message_data['event']['type'],
event_time=message_data['event_time'],
message_time=None,
text=None,
file_id=message_data['event']['file_id'],
reaction=None,
created_at=datetime.now(),
updated_at=datetime.now())
db.session.add(target)
db.session.commit()
return target
@classmethod
def insert_message_reaction(cls, user_id, channel_id, message_data):
target = SlackMessage(user_id=user_id,
team_id=message_data['team_id'],
channel_id=channel_id,
event_id=message_data['event_id'],
event_type=message_data['event']['type'],
event_time=message_data['event_time'],
message_time=message_data['event']['item']['ts'],
text=None,
file_id=None,
reaction=message_data['event']['reaction'],
created_at=datetime.now(),
updated_at=datetime.now())
db.session.add(target)
db.session.commit()
return target
@classmethod
def insert_message_join(cls, user_id, channel_id, message_data):
target = SlackMessage(user_id=user_id,
team_id=message_data['team_id'],
channel_id=channel_id,
event_id=message_data['event_id'],
event_type=message_data['event']['type'],
event_time=message_data['event_time'],
message_time=None,
text=None,
file_id=None,
reaction=None,
created_at=datetime.now(),
updated_at=datetime.now())
db.session.add(target)
db.session.commit()
return target
@classmethod
def delete_file(cls, file_id):
target = db.session.query(cls).filter(cls.file_id == file_id).first()
target.file_deleted = 1
target.updated_at = datetime.now()
db.session.commit()
return target
@classmethod
def check_duplicate(cls, message_data):
target = db.session.query(cls).with_entities(
cls.id).filter(cls.event_id == message_data['event_id']).first()
if not target:
return False
else:
return True
class ZoomMeeting(db.Model):
"""
作成者: kazu
概要: Zoom APIを用いて取得したミーティング情報を保存するテーブル
"""
__tablename__ = 'zoom_meetings'
__table_args__ = (
CheckConstraint('updated_at >= created_at'), # チェック制約
)
id = db.Column(db.Integer, primary_key=True, autoincrement=True) # 主キー
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
meeting_id = db.Column(db.String(30), nullable=False)
meeting_uuid = db.Column(db.String(50), nullable=False, unique=True)
topic = db.Column(db.String(100), nullable=False)
start_time = db.Column(db.DateTime, nullable=False)
duration = db.Column(db.Integer, nullable=False)
meeting_created = db.Column(db.DateTime, 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)
# zoom_participant = db.relationship('ZoomParticipant', backref='meeting', lazy=True)
def __init__(self, user_id, meeting_id, meeting_uuid, topic, start_time,
duration, meeting_created, created_at, updated_at):
self.user_id = user_id
self.meeting_id = meeting_id
self.meeting_uuid = meeting_uuid
self.topic = topic
self.start_time = start_time
self.duration = duration
self.meeting_created = meeting_created
self.created_at = created_at
self.updated_at = updated_at
def __str__(self):
return f"id = {self.id}, user_id = {self.user_id}, meeting_id = {self.meeting_id}, meeting_uuid = {self.meeting_uuid}, topic = {self.topic}, start_time = {self.start_time}, duration = {self.duration}, meeting_created = {self.meeting_created}, create_at={self.created_at}, update_at={self.updated_at} "
@classmethod
def insert_meeting(cls, meeting_list):
for meeting_data in meeting_list:
target = ZoomMeeting(user_id=meeting_data['user_id'],
meeting_id=meeting_data['meeting_id'],
meeting_uuid=meeting_data['meeting_uuid'],
topic=meeting_data['topic'],
start_time=meeting_data['start_time'],
duration=meeting_data['duration'],
meeting_created=meeting_data['created_at'],
created_at=datetime.now(),
updated_at=datetime.now())
db.session.add(target)
db.session.commit()
@classmethod
def update_meeting(cls, meeting_list):
for meeting_data in meeting_list:
meeting = db.session.query(cls).filter(
cls.meeting_uuid == meeting_data['meeting_uuid']).first()
meeting.topic = meeting_data['topic']
meeting.start_time = meeting_data['start_time']
meeting.duration = meeting_data['duration']
meeting.updated_at = datetime.now()
db.session.commit()
@classmethod
def check_duplicate(cls, meeting_list):
result = {}
update_target = []
insert_target = []
for meeting_data in meeting_list:
target = db.session.query(cls).with_entities(cls.id).filter(
cls.meeting_uuid == meeting_data['meeting_uuid']).first()
if not target:
insert_target.append(meeting_data)
else:
update_target.append(meeting_data)
result['update'] = update_target
result['insert'] = insert_target
return result
@classmethod
def check_meeting_uuid(cls, meeting_uuid):
return cls.query.with_entities(
cls.id).filter(cls.meeting_uuid == meeting_uuid).first()
@classmethod
def select_meeting(cls, user_id):
return db.session.query(cls).with_entities(
cls.meeting_id, cls.topic, cls.start_time, cls.duration,
cls.meeting_created).filter(cls.user_id == user_id).all()
class Article(db.Model):
__tablename__ = 'articles'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(128), nullable=False)
content = db.Column(db.Text, nullable=False)
slug = db.Column(db.String, nullable=False)
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})>"
