class MultiRouteTrip(db.Model): """ Some transit trips serve more than one route. This experimental file indicates routes that a trip is associated with, in addition to the route_id identified with this trip in trips.txt. Requires: added_route_id, trip_id Relies on: Route, Trip Reference: https://github.com/mbta/gtfs-documentation/blob/master/reference/gtfs.md#multi_route_tripstxt """ id = db.Column(db.Integer, primary_key=True) added_route_id = db.Column(db.String(64), db.ForeignKey("route.route_id"), nullable=False, index=True) route = db.relationship("Route", backref="multi_trips") trip_id = db.Column(db.String(128), db.ForeignKey("trip.trip_id"), nullable=False, index=True) trip = db.relationship("Trip", backref="multi_trips") def __init__(self, added_route_id: str, trip_id: str): self.added_route_id = added_route_id self.trip_id = trip_id def __repr__(self): return f"<MultiRouteTrip: Route {self.added_route_id}, Trip {self.trip_id}>"
class Comment(db.Model): id = db.Column(db.Integer, primary_key=True, autoincrement=True) author_id = db.Column(db.Integer, db.ForeignKey("user.id")) post_id = db.Column(db.Integer, db.ForeignKey("post.id")) content = db.Column(db.Text, nullable=False) created = db.Column(db.DateTime(timezone=True), server_default=now()) author = db.relationship("User") post = db.relationship("Post")
class StopTime(db.Model): """ Times that a vehicle arrives at and departs from stops for each trip. Requires: trip_id, arrival_time, departure_time, stop_id, stop_sequence Relies on: Trip, Stop Reference: https://github.com/google/transit/blob/master/gtfs/spec/en/reference.md#stop_timestxt """ id = db.Column(db.Integer, primary_key=True) trip_id = db.Column(db.String(128), db.ForeignKey("trip.trip_id"), nullable=False) trip = db.relationship("Trip", backref="times") arrival_time = db.Column(db.Integer(), nullable=False) # Seconds since 00:00:00 departure_time = db.Column(db.Integer(), nullable=False) # Seconds since 00:00:00 stop_id = db.Column(db.String(64), db.ForeignKey("stop.stop_id"), nullable=False) stop = db.relationship("Stop", backref="times") stop_sequence = db.Column(db.Integer(), nullable=False) stop_headsign = db.Column(db.String(128), nullable=True) pickup_type = db.Column(db.Enum(PickupDropOffType), nullable=True) drop_off_type = db.Column(db.Enum(PickupDropOffType), nullable=True) shape_dist_traveled = db.Column( # Distance traveled (in meters) from the first stop to this stop db.Float(), nullable=True) timepoint = db.Column( # 0 = times are approximate, 1 = times are exact db.SmallInteger(), nullable=True) checkpoint_id = db.Column(db.String(16), db.ForeignKey("checkpoint.checkpoint_id"), nullable=True) checkpoint = db.relationship("Checkpoint", backref="times") def __init__( self, trip_id: str, arrival_time: datetime.time, departure_time: datetime.time, stop_id: str, stop_sequence: int, **kwargs, ): self.trip_id = trip_id self.arrival_time = arrival_time self.departure_time = departure_time self.stop_id = stop_id self.stop_sequence = stop_sequence for fieldname, value in kwargs.items(): setattr(self, fieldname, value) def __repr__(self): return ( f"<StopTime: {self.arrival_time}->{self.departure_time} @ {self.stop_id}>" )
class 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 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 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 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 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 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 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 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 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 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 Stop(db.Model, GeoMixin): """ A transit stop Requires: stop_id Relies on: None Reference: https://github.com/google/transit/blob/master/gtfs/spec/en/reference.md#stopstxt """ lonlat_field = "stop_lonlat" stop_id = db.Column(db.String(64), primary_key=True) stop_code = db.Column( db.String(64), nullable=True ) # Often the same as stop_id (or shortened version thereof) stop_name = db.Column(db.String(128), nullable=True) tts_stop_name = db.Column( db.String(64), nullable=True ) # Defaults to stop_name - used to resolve TTS ambiguities stop_desc = db.Column(db.String(256), nullable=True) platform_code = db.Column(db.String(8), nullable=True) platform_name = db.Column(db.String(64), nullable=True) # to retrieve lon, lat: db.session.query(func.ST_X(Stop.stop_lonlat), func.ST_Y(Stop.stop_lonlat)).first() stop_lonlat = db.Column(Geometry("POINT"), nullable=True, index=True) zone_id = db.Column(db.String(32), nullable=True) stop_address = db.Column(db.String(128), nullable=True) stop_url = db.Column(db.String(256), nullable=True) level_id = db.Column(db.String(64), nullable=True) location_type = db.Column(db.Enum(LocationType), nullable=True) parent_station = db.Column(db.String(64), db.ForeignKey("stop.stop_id"), nullable=True) parent = db.relationship("Stop", backref="children", remote_side=[stop_id]) wheelchair_boarding = db.Column(db.Enum(AccessibilityType), nullable=True) municipality = db.Column(db.String(64), nullable=True) on_street = db.Column(db.String(64), nullable=True) at_street = db.Column(db.String(64), nullable=True) vehicle_type = db.Column(db.Enum(RouteType), nullable=True) stop_timezone = db.Column( db.Enum(TimeZone), nullable=True) # Inherits from Agency.agency_timezone if null def __init__(self, stop_id: str, **kwargs): self.stop_id = stop_id for fieldname, value in kwargs.items(): setattr(self, fieldname, value) def __repr__(self): return f"<Stop: {self.stop_id} ({self.stop_name})>"
class 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 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 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}>"
# from sqlalchemy.orm import relationship # from sqlalchemy.dialects.postgresql import UUID # from sqlalchemy import Column, String, Integer, VARCHAR # from flaskr.mysqldb import DbBase, DatabaseConnect from flaskr.database import DbBase, db from flask import g import random followers = db.Table( 'followers', db.Column('follower_id', db.Integer, db.ForeignKey('User.id')), db.Column('followed_id', db.Integer, db.ForeignKey('User.id')) ) class User(DbBase): __tablename__ = 'User' id = db.Column(db.Integer, unique=True, autoincrement=True, primary_key=True) username = db.Column(db.VARCHAR(12), nullable=False) password = db.Column(db.VARCHAR(12), nullable=False) avatar = db.Column(db.VARCHAR(256), default=None) nickname = db.Column(db.VARCHAR(20), nullable=False) blog = db.relationship('Blog', backref='user') # 用户的关注与粉丝都是 User 时, # sqlalchemy 无法区分主次 # 需使用 primaryjoin 与 secondaryjoin 两个参数指定 followed = db.relationship( 'User',
from sqlalchemy.sql.functions import now from flaskr.database import db post_tag = db.Table( "post_tag", db.Column("tag_id", db.Integer, db.ForeignKey("tag.id")), db.Column("post_id", db.Integer, db.ForeignKey("post.id")), ) class User(db.Model): id = db.Column(db.Integer, primary_key=True, autoincrement=True) username = db.Column(db.String(50), unique=True) password = db.Column(db.String(200), nullable=False) 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 Importance(db.Model): """ 作成者: kazu 概要: 各プロジェクトごとに設定された各Webアプリケーションの重要度を保存するテーブル """ __tablename__ = 'projects_importance' __table_args__ = ( CheckConstraint('updated_at >= created_at'), # チェック制約 ) id = db.Column(db.Integer, primary_key=True, autoincrement=True) # 主キー project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), nullable=False) service = db.Column(db.String(50), nullable=False) importance = db.Column(db.Integer, 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, project_id, service, importance, created_at, updated_at): self.project_id = project_id self.service = service self.importance = importance self.created_at = created_at self.updated_at = updated_at def __str__(self): return f"id = {self.id}, project_id = {self.project_id}, service = {self.service}, importance = {self.importance}, create_at={self.created_at}, update_at={self.updated_at} " @classmethod def select_importance(cls, project_id): data = db.session.query(cls).with_entities( cls.service, cls.importance).filter(cls.project_id == project_id).all() result_data = {} for d in data: result_data[d[0]] = d[1] return result_data @classmethod def update_importance(cls, values): target = db.session.query(cls).filter( cls.project_id == values['project_id']).filter( cls.service == 'mail').first() target.importance = values['importance']['mail'] target.updated_at = datetime.now() db.session.add(target) target = db.session.query(cls).filter( cls.project_id == values['project_id']).filter( cls.service == 'schedule').first() target.importance = values['importance']['schedule'] target.updated_at = datetime.now() db.session.add(target) target = db.session.query(cls).filter( cls.project_id == values['project_id']).filter( cls.service == 'slack').first() target.importance = values['importance']['slack'] target.updated_at = datetime.now() db.session.add(target) target = db.session.query(cls).filter( cls.project_id == values['project_id']).filter( cls.service == 'zoom').first() target.importance = values['importance']['zoom'] target.updated_at = datetime.now() db.session.add(target) db.session.commit()
class Information(db.Model): """ 作成者: kazu 概要: 従業員情報を保存しているテーブル """ __tablename__ = 'information' __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) name = db.Column(db.String(20), index=True) department = db.Column(db.String(30), index=True) birthday = db.Column(db.DateTime) sex = db.Column(db.String(5)) 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, name, department, birthday, sex, created_at, updated_at): self.user_id = user_id self.name = name self.department = department self.birthday = birthday self.sex = sex self.created_at = created_at self.updated_at = updated_at def __str__(self): return f"id = {self.id}, user_id = {self.user_id}, name = {self.name}, department = {self.department}, birthday = {self.birthday}, sex = {self.sex}, create_at={self.created_at}, update_at={self.updated_at} " @classmethod def select_information(cls, user_id): raw_data = cls.query.with_entities( cls.id, cls.user_id, cls.name, cls.department, cls.birthday, cls.sex).filter(cls.user_id == user_id).first() data = {} data['id'] = raw_data[0] data['user_id'] = raw_data[1] data['name'] = raw_data[2] data['department'] = raw_data[3] data['birthday'] = raw_data[4].date() data['sex'] = raw_data[5] return data @classmethod def insert_information(self, user_id, information_data): target = Information(user_id=user_id, name=information_data['name'], department=information_data['department'], birthday=information_data['birthday'], sex=information_data['sex'], created_at=datetime.now(), updated_at=datetime.now()) db.session.add(target) db.session.commit() @classmethod def update_information(cls, user_id, information_data): information = db.session.query(cls).filter( cls.user_id == user_id).first() if 'name' in information_data: information.name = information_data['name'] if 'department' in information_data: information.department = information_data['department'] if 'sex' in information_data: information.sex = information_data['sex'] if 'birthday' in information_data: information.birthday = information_data['birthday'] information.updated_at = datetime.now() db.session.commit() @classmethod def check_information_mail(cls, user_id): return db.session.query(cls).with_entities( cls.id).filter(cls.user_id == user_id).first()
class Mail(db.Model): """ 作成者: kazu 概要: GMail APIで取得したgmail情報を保存するテーブル """ __tablename__ = 'gmail' __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')) message_id = db.Column(db.String(50), nullable=False, unique=True) sender_name = db.Column(db.String(50), nullable=False) sender_email = db.Column(db.String(50)) date = db.Column(db.DateTime, nullable=False) subject = db.Column(db.String(100)) message = db.Column(db.String(2000)) 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, message_id, sender_name, sender_email, date, subject, message, created_at, updated_at): self.user_id = user_id self.message_id = message_id self.sender_name = sender_name self.sender_email = sender_email self.date = date self.subject = subject self.message = message self.created_at = created_at self.updated_at = updated_at def __str__(self): return f"id = {self.id}, user_id = {self.user_id}, message_id = {self.message_id}, sender_name = {self.sender_name}, sender_email = {self.sender_email}, date = {self.date}, subject = {self.subject}, message = {self.message}, create_at={self.created_at}, update_at={self.updated_at} " @classmethod def select_mail(cls, user_id): raw_data = cls.query.with_entities( cls.id, cls.user_id, cls.message_id, cls.sender_name, cls.sender_email, cls.date, cls.subject, cls.message).filter(cls.user_id == user_id).order_by( cls.date.asc(), cls.id.asc()).all() result_data = [] for r in raw_data: data = {} data['id'] = r[0] data['user_id'] = r[1] data['message_id'] = r[2] data['sender_name'] = r[3] data['sender_email'] = r[4] data['date'] = r[5] data['subject'] = r[6] data['message'] = r[7] result_data.append(data) return result_data @classmethod def insert_message(self): print(self.created_at) db.session.add(self) @classmethod def check_duplicate(cls, message_list): result = [] for message_data in message_list: target = db.session.query(cls).with_entities( cls.id).filter(cls.message_id == message_data['id']).first() if not target: result.append(message_data) return result @classmethod def insert_mail(self, message_list): for message_data in message_list: target = Mail(user_id=message_data['user_id'], message_id=message_data['id'], sender_name=message_data['sender_name'], sender_email=message_data['sender_email'], date=message_data['date'], subject=message_data['subject'], message=message_data['body'], created_at=datetime.now(), updated_at=datetime.now()) db.session.add(target) db.session.commit()
class Calendar(db.Model): """ 作成者: kazu 概要: Google Calendar APIで取得したカレンダー情報を保存するテーブル """ __tablename__ = 'schedule' __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) event_id = db.Column(db.String(50), nullable=False, unique=True) link = db.Column(db.String(200), nullable=False) event_created = db.Column(db.DateTime, nullable=False) event_updated = db.Column(db.DateTime, nullable=False) title = db.Column(db.String(50), nullable=False) description = db.Column(db.String(400)) location = db.Column(db.String(50)) all_day = db.Column(db.Boolean, nullable=False, default=False) start = db.Column(db.DateTime) end = db.Column(db.DateTime) date = db.Column(db.DateTime) sort_date = 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) def __init__(self, user_id, event_id, link, event_created, event_updated, title, description, location, all_day, start, end, date, sort_date, created_at, updated_at): self.user_id = user_id self.event_id = event_id self.link = link self.event_created = event_created self.event_updated = event_updated self.title = title self.description = description self.location = location self.all_day = all_day self.start = start self.end = end self.date = date self.sort_date = sort_date self.created_at = created_at self.updated_at = updated_at def __str__(self): return f"id = {self.id}, user_id = {self.user_id}, event_id = {self.event_id}, link = {self.link}, event_created = {self.event_created}, event_updated = {self.event_updated}, title = {self.title}, description = {self.description}, location = {self.location}, all_day = {self.all_day}, start = {self.start}, end = {self.end}, date = {self.date}, sort_date = {self.sort_date}, create_at={self.created_at}, update_at={self.updated_at} " @classmethod def insert_schedule(cls, user_id, event_data): target = Calendar(user_id=user_id, event_id=event_data['id'], link=event_data['htmlLink'], title=event_data['summary'], description=event_data['description'], location=event_data['location'], event_created=event_data['created'], event_updated=event_data['updated'], all_day=event_data['all_day'], start=event_data['start'], end=event_data['end'], date=event_data['date'], sort_date=event_data['sort_date'], created_at=datetime.now(), updated_at=datetime.now()) db.session.add(target) db.session.commit() @classmethod def update_schedule(cls, event_data): schedule = db.session.query(cls).filter( cls.event_id == event_data['id']).first() schedule.event_updated = event_data['updated'] schedule.title = event_data['summary'] schedule.description = event_data['description'] schedule.location = event_data['location'] schedule.all_day = event_data['all_day'] schedule.start = event_data['start'] schedule.end = event_data['end'] schedule.date = event_data['date'] schedule.sort_date = event_data['sort_date'] schedule.updated_at = datetime.now() db.session.commit() @classmethod def check_schedule_event_id(cls, event_id): target = db.session.query(cls).with_entities( cls.id).filter(cls.event_id == event_id).first() if not target: return False else: return True @classmethod def select_schedule_id(cls, user_id): raw_data = db.session.query(cls).with_entities( cls.id, cls.user_id, cls.event_id, cls.link, cls.event_created, cls.event_updated, cls.title, cls.description, cls.location, cls.all_day, cls.start, cls.end, cls.date).filter(cls.user_id == user_id).order_by( cls.sort_date.asc(), cls.all_day.asc()).all() result_data = [] for r in raw_data: data = {} data['id'] = r[0] data['user_id'] = r[1] data['event_id'] = r[2] data['link'] = r[3] data['event_created'] = r[4] data['event_updated'] = r[5] data['title'] = r[6] data['description'] = r[7] data['location'] = r[8] data['creator'] = r[9] if r[10] == 0: data['date'] = str(r[11]) + " - " + str(r[12]) elif r[10] == 1: data['date'] = r[13].date() result_data.append(data) return result_data
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()