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()
