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})>"