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 GeoStub(db.Model, models.GeoMixin):
lonlat_field = "lonlat_column"
geo_stub_id = db.Column(db.Integer, primary_key=True)
lonlat_column = db.Column(geoalchemy2.Geometry("POINT"), nullable=False)
def __init__(self, geo_stub_id: int, longitude: float, latitude: float):
self.geo_stub_id = geo_stub_id
self.lonlat_column = f"POINT({longitude} {latitude})"
class Blog(DbBase):
__tablename__ = 'Blog'
title = db.Column(db.VARCHAR(20), nullable=False)
content = db.Column(db.VARCHAR(500), nullable=False)
author_id = db.Column(db.Integer, db.ForeignKey('User.id'))
def __init__(self, title, content, user_id):
self.title = title
self.content = content
self.author_id = user_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 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 BillMod(DbBase):
__tablename__ = 'BillMod'
type = db.Column(db.VARCHAR(20), nullable=False)
description = db.Column(db.VARCHAR(20), default=None)
user_id = db.Column(db.Integer, db.ForeignKey('User.id'))
amount = db.Column(db.Float, nullable=False)
def __init__(self, user_id, base_type='基本消费', amount=0, description=None):
self.user_id = user_id
self.type = base_type
self.amount = amount
self.description = description
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 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 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 SlackChannelMember(db.Model):
"""
作成者: kazu
概要: チャネルとメンバーの関係を保存するテーブル
"""
__tablename__ = 'slack_channel_members'
__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)
channel_id = db.Column(db.Integer,
db.ForeignKey('slack_channels.id'),
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, channel_id, created_at, updated_at):
self.user_id = user_id
self.channel_id = channel_id
self.created_at = created_at
self.updated_at = updated_at
def __str__(self):
return f"id = {self.id}, user_id = {self.user_id}, channel_id = {self.channel_id}, created_at = {self.created_at}, updated_at = {self.updated_at}"
@classmethod
def insert_channel_member(cls, user_id, channel_id):
target = SlackChannelMember(user_id=user_id,
channel_id=channel_id,
created_at=datetime.now(),
updated_at=datetime.now())
db.session.add(target)
db.session.commit()
return target
@classmethod
def check_by_ids(cls, user_id, channel_id):
target = db.session.query(cls).with_entities(
cls.id).filter(cls.user_id == user_id).filter(
cls.channel_id == channel_id).first()
if not target:
return False
else:
return True
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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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})>"
