class RawSlackEvent(db.Model, EnhancedDBModel):
__tablename__ = 'raw_slack_events'
id = db.Column(db.Integer, primary_key=True)
json_data = db.Column(db.JSON(none_as_null=True), nullable=False)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow)
def __init__(self, json_data={}):
self.json_data = json_data
def __repr__(self):
return "RawSlackEvent(%s)" % self.json_data
class GitHubUser(db.Model, EnhancedDBModel):
__tablename__ = 'github_users'
id = db.Column(db.Integer, primary_key=True)
github_api_user_id = db.Column(db.Integer, nullable=False)
github_oauth_access_token = db.Column(db.String(200))
github_email_address = db.Column(db.String(300))
github_username = db.Column(db.String(100), nullable=True)
is_authenticated = db.Column(db.Boolean, nullable=False, default=False)
is_deleted_on_github = db.Column(db.Boolean, nullable=False, default=False)
created_at = db.Column(db.DateTime, nullable=True)
updated_at = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
def __repr__(self):
return 'GitHubUser(%s, %s, %s)' % (self.github_username, self.id, self.user_id)
class SlackTeam(db.Model, EnhancedDBModel):
__tablename__ = 'slack_teams'
id = db.Column(db.Integer, primary_key=True)
api_scope = db.Column(db.String(1000), nullable=True)
slack_team_api_id = db.Column(db.String(100), nullable=False, unique=True)
slack_team_name = db.Column(db.String(100), nullable=False)
api_access_token = db.Column(db.String(100), nullable=True)
authenticated_at = db.Column(db.DateTime, nullable=True)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow)
def update_registration(self, new_slack_team):
if new_slack_team.slack_team_api_id != self.slack_team_api_id:
raise ValueError("Old slack team ID is not the same as new Slack team ID")
self.api_scope = new_slack_team.api_scope
self.slack_team_name = new_slack_team.slack_team_name
self.api_access_token = new_slack_team.api_access_token
self.datetime_authenticated = datetime.utcnow()
def slack_users(self):
return SlackUser.query.filter(SlackUser.slack_team_id == self.id).all()
def slack_conversations(self):
return SlackConversation.query.filter(SlackConversation.slack_team_id == self.id).all()
def __repr__(self):
return "SlackTeam('%s', '%s')" % (self.slack_team_name, self.slack_team_api_id)
def __init__(self, oauth_response_json):
self.api_scope = oauth_response_json.get('scope')
self.slack_team_id = oauth_response_json.get('team_id')
self.slack_team_api_id = oauth_response_json.get('team_id')
self.slack_team_name = oauth_response_json.get('team_name')
self.api_access_token = oauth_response_json.get('access_token')
self.datetime_authenticated = datetime.utcnow()
class GitHubComment(db.Model, EnhancedDBModel): __tablename__ = 'github_comments' id = db.Column(db.Integer, primary_key=True) github_api_comment_id = db.Column(db.Integer, nullable=False) github_api_author_id = db.Column(db.Integer, nullable=False) #Type distinguishes between "commit", "PR", "issue" etc. comments #Playing it safe with space allocation comment_type = db.Column(db.String(30), nullable=False) #ID of the commit, issue, PR or other that this comment was made on github_api_parent_id = db.Column(db.Integer, nullable=False) created_at = db.Column(db.DateTime, nullable=False) updated_at = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=False) github_api_written_at = db.Column(db.DateTime, nullable=False) github_api_edited_at = db.Column(db.DateTime, nullable=False) impact_score = db.Column(db.Float, nullable=True) def __repr__(self): return 'GitHubComment(%s, %s, %s, %s, %s, %s)' % (self.id, self.github_api_comment_id, self.github_api_author_id, self.comment_type, self.github_api_parent_id, self.impact_score)
class SlackConversationRead(db.Model):
__tablename__ = 'slack_conversation_reads'
id = db.Column(db.Integer, primary_key=True)
slack_conversation_query_id = db.Column(db.Integer, db.ForeignKey('slack_conversation_queries.id'), nullable=False, unique=True)
slack_user_id = db.Column(db.Integer, db.ForeignKey('slack_users.id'), nullable=False)
slack_conversation_id = db.Column(db.Integer, db.ForeignKey('slack_conversations.id'), nullable=False)
period_start_datetime = db.Column(db.DateTime, nullable=False)
period_end_datetime = db.Column(db.DateTime, nullable=False)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=False)
class SlackConversationQuery(db.Model):
__tablename__ = 'slack_conversation_queries'
id = db.Column(db.Integer, primary_key=True)
slack_user_id = db.Column(db.Integer, db.ForeignKey('slack_users.id'), nullable=False)
slack_conversation_id = db.Column(db.Integer, db.ForeignKey('slack_conversations.id'), nullable=False)
query_datetime = db.Column(db.DateTime, nullable=False)
last_read_datetime = db.Column(db.DateTime, nullable=False)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=False)
slack_conversation_query_run_id = db.Column(db.Integer, db.ForeignKey('slack_conversation_query_runs.id'), nullable=False)
class SlackConversation(db.Model, EnhancedDBModel):
__tablename__ = 'slack_conversations'
id = db.Column(db.Integer, primary_key=True)
slack_conversation_api_id = db.Column(db.String(100), nullable=False)
slack_team_id = db.Column(db.Integer, db.ForeignKey('slack_teams.id'), nullable=False)
conversation_type = db.Column(db.String(100), nullable=False) # 'im', 'mpim', 'channel', 'private_channel'
conversation_name = db.Column(db.String(100)) # can be null for IM conversation
is_deleted = db.Column(db.Boolean, nullable=False, default=False)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=False)
class SlackUserEvent(db.Model, EnhancedDBModel):
__tablename__ = 'slack_user_events'
id = db.Column(db.Integer, primary_key=True)
slack_user_id = db.Column(db.Integer, db.ForeignKey('slack_users.id'), nullable=False)
raw_slack_event_id = db.Column(db.Integer, db.ForeignKey('raw_slack_events.id')) # may be null if polled from Slack API
slack_event_api_id = db.Column(db.String(100), nullable=False)
event_datetime = db.Column(db.DateTime, nullable=False)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=False)
slack_event_type = db.Column(db.String(100), nullable=False)
slack_event_subtype = db.Column(db.String(100))
def __repr__(self):
return "SlackUserEvent(id: %s, slack_user_id: %s, slack_event_api_id: %s, slack_event_type: %s, slack_event_subtype: %s)" % \
(self.id, self.slack_user_id, self.slack_event_api_id, self.slack_event_type, self.slack_event_subtype or '')
class GitHubRepo(db.Model, EnhancedDBModel): __tablename__ = 'github_repos' id = db.Column(db.Integer, primary_key=True) github_api_repo_id = db.Column(db.Integer, nullable=False) #Repository names can be up to 100 characters long #https://github.com/evalEmpire/gitpan/issues/123 name = db.Column(db.String(100), nullable = False) created_at = db.Column(db.DateTime, nullable=True) updated_at = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=True) #GitHub API user id of owner github_api_owner_id = db.Column(db.Integer, nullable=True) organization = db.Column(db.String(100), nullable=True) is_private = db.Column(db.Boolean, nullable=False, default=False) def __repr__(self): return 'GitHubRepo(%s, %s, %s)' % (self.name, self.id, self.github_api_owner_id)
class User(db.Model, UserMixin):
__tablename__ = 'users' # follows general table name paradigm in database (plural)
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(20), unique=True, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
image_file = db.Column(db.String(20), nullable=False, default='default.jpg')
password = db.Column(db.String(60), nullable=False)
# we will default to workday start of 8:30 to 5:30;
workday_start = db.Column(db.DateTime, default=datetime(2020, 1, 1, 13, 30))
workday_end = db.Column(db.DateTime, default=datetime(2020, 1, 1, 22, 30))
focus_length = db.Column(db.Integer, default=2)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow)
posts = db.relationship('Post', backref='author', lazy=True)
google_calendar_user = db.relationship('GoogleCalendarUser', backref='user', uselist=False)
slack_user = db.relationship('SlackUser', backref='user', uselist=False)
github_user = db.relationship('GitHubUser', backref='user', uselist=False)
activity_report_data = db.relationship('ActivityReportRow', backref='user', uselist=True)
def __repr__(self):
return "User('%s','%s','%s')" % (self.username, self.email, self.image_file)
def to_dict(self):
return dict(id=self.id, username=self.username, email=self.email)
def user_details(self):
return dict(id=self.id, username=self.username, email=self.email, \
github_user_id=self.github_user.id if self.github_user else None, \
google_calendar_user_id=self.google_calendar_user.id if self.google_calendar_user else None, \
slack_user_id=self.slack_user.id if self.slack_user else None)
# GCal user may be null, so we need to account for this. username, email, id are req'd fields
@hybrid_property
def fully_authenticated(self):
return (self.google_calendar_user != None) & (self.slack_user != None)
@hybrid_property
def timezone_offset_hours(self):
return self.slack_user.slack_timezone_offset / 3600
class Post(db.Model):
__tablename__ = 'posts' # follows general table name paradigm in database (plural)
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100), nullable=False)
date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
content = db.Column(db.Text, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow, default=datetime.utcnow)
def __repr__(self):
return "Post('%s', '%s')" % (self.title, self.date_posted)
def date_to_string(self):
return self.date_posted.strftime("%a %b %d %Y %l:%M%p")
class GitHubIssue(db.Model, EnhancedDBModel): __tablename__ = 'github_issues' id = db.Column(db.Integer, primary_key=True) created_at = db.Column(db.DateTime, nullable=False) updated_at = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=False) github_api_issue_id = db.Column(db.Integer, nullable=False) github_api_creator_id = db.Column(db.Integer, nullable=False) github_api_closer_id = db.Column(db.Integer, nullable=True) is_open = db.Column(db.Boolean, nullable=True) github_api_opened_at = db.Column(db.DateTime, nullable=False) github_api_closed_at = db.Column(db.DateTime, nullable=True) open_impact_score = db.Column(db.Float, nullable=True) closure_impact_score = db.Column(db.Float, nullable=True) def __repr__(self): return 'GitHubIssue(%s, %s, %s, %s, %s, %s)' % (self.id, self.github_api_issue_id, self.github_api_creator_id, self.open_impact_score, self.closure_impact_score)
class GitHubPullRequest(db.Model, EnhancedDBModel): __tablename__ = 'github_pull_requests' id = db.Column(db.Integer, primary_key=True) created_at = db.Column(db.DateTime, nullable=False) updated_at = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=False) github_api_pr_id = db.Column(db.Integer, nullable=False) github_api_repo_id = db.Column(db.Integer, nullable=False) github_api_author_id = db.Column(db.Integer, nullable=False) base_sha = db.Column(db.String(40), nullable=False) head_sha = db.Column(db.String(40), nullable=False) github_api_opened_at = db.Column(db.DateTime, nullable=False) github_api_closed_at = db.Column(db.DateTime, nullable=True) github_api_merged_at = db.Column(db.DateTime, nullable=True) insertions = db.Column(db.Integer, nullable=True) deletions = db.Column(db.Integer, nullable=True) edit_points = db.Column(db.Integer, nullable=True) files_changed = db.Column(db.Integer, nullable=True) percent_churn = db.Column(db.Float, nullable=True) status = db.Column(db.String(10), nullable=True) impact_score = db.Column(db.Float, nullable=True) def __repr__(self): return 'GitHubPullRequest(%s, %s, %s, %s, %s)' % (self.id, self.github_api_pr_id, self.github_api_repo_id, self.github_api_author_id, self.impact_score)
class GitHubCommit(db.Model, EnhancedDBModel): __tablename__ = 'github_commits' id = db.Column(db.Integer, primary_key=True) created_at = db.Column(db.DateTime, nullable=False) updated_at = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=False) github_api_repo_id = db.Column(db.Integer, nullable=False) #Author is assumed original writer of the code #(might be different from committer in cases such as patches or history rewrites) github_api_author_id = db.Column(db.Integer, nullable=False) github_api_committer_id = db.Column(db.Integer, nullable=True) #Full-length GitHub sha are 40 hex characters #https://stackoverflow.com/a/18134919 sha = db.Column(db.String(40), nullable=False) github_api_committed_at = db.Column(db.DateTime, nullable=False) insertions = db.Column(db.Integer, nullable=True) deletions = db.Column(db.Integer, nullable=True) edit_points = db.Column(db.Integer, nullable=True) files_changed = db.Column(db.Integer, nullable=True) impact_score = db.Column(db.Float, nullable=True) def __repr__(self): return 'GitHubCommit(%s, %s, %s, %s)' % (self.id, self.github_api_repo_id, self.github_api_author_id, self.impact_score)
class APSchedulerJob(db.Model): __tablename__ = 'apscheduler_jobs' id = db.Column(db.Integer, primary_key=True) next_run_time = db.Column(db.Float, nullable=True, index=True) job_state = db.Column(db.LargeBinary, nullable=False)
class SlackConversationQueryRun(db.Model, EnhancedDBModel):
__tablename__ = 'slack_conversation_query_runs'
id = db.Column(db.Integer, primary_key=True)
query_start_time = db.Column(db.DateTime, nullable=False)
query_end_time = db.Column(db.DateTime)
slack_user_id = db.Column(db.Integer, db.ForeignKey('slack_users.id'), nullable=False)
class ActivityReportRow(db.Model):
__tablename__ = 'activity_report_rows'
id = db.Column(db.Integer, primary_key=True)
datetime_utc = db.Column(db.DateTime, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
slack_conversation_read_count = db.Column(db.Integer, nullable=True)
slack_user_event_count = db.Column(db.Integer, nullable=True)
google_calendar_event_id = db.Column(db.Integer, db.ForeignKey('google_calendar_events.id', \
ondelete='SET NULL'), nullable=True)
google_calendar_event_count = db.Column(db.Integer, nullable=True)
is_workday_time = db.Column(db.Boolean, nullable=True, default=False)
focused_work_period_start_utc = db.Column(db.DateTime, nullable=True)
is_focus_time = db.Column(db.Boolean, nullable=True)
is_refocus_time = db.Column(db.Boolean, nullable=True)
is_collaborative_time = db.Column(db.Boolean, nullable=True)
__table_args__ = ( \
Index('activity_report_unique_user_datetime', 'user_id', \
'datetime_utc', unique=True),)
def __repr__(self):
return f'ActivityReportRow({self.id}, {self.datetime_utc}, {self.user_id})'
def to_json(self):
props = self.__dict__
props.pop('_sa_instance_state')
props['datetime_utc'] = props['datetime_utc'].isoformat()
return props
class GoogleCalendarUser(db.Model, EnhancedDBModel):
__tablename__ = 'google_calendar_users'
id = db.Column(db.Integer, primary_key=True)
google_email = db.Column(db.String(300), nullable=False)
auth_token = db.Column(db.String(300), nullable=False)
refresh_token = db.Column(db.String(300))
scopes = db.Column(db.String(300), nullable=False)
primary_timeZone = db.Column(db.String(300), nullable=False)
primary_etag = db.Column(db.String(300), nullable=False)
primary_color_id = db.Column(db.Integer, nullable=False)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
updated_at = db.Column(db.DateTime, onupdate=datetime.utcnow)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
google_calendar_events = db.relationship('GoogleCalendarEvent', backref='google_calendar_user', lazy=True)
def __repr__(self):
return "GoogleCalendarUser('%s','%s','%s')" % (self.google_email, self.primary_timeZone, self.created_at)
class GoogleCalendarEvent(db.Model, EnhancedDBModel):
__tablename__ = 'google_calendar_events'
id = db.Column(db.Integer, primary_key=True)
google_id = db.Column(db.String(200), nullable=False)
ical_uid = db.Column(db.String(200), nullable=False)
start_time = db.Column(db.DateTime, nullable=False)
end_time = db.Column(db.DateTime, nullable=False)
summary = db.Column(db.String(300), nullable=False)
description = db.Column(db.Text)
organizer_email = db.Column(db.String(200), nullable=False)
organizer_self = db.Column(db.Boolean, default=False)
json_data = db.Column(db.JSON)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
updated_at = db.Column(db.DateTime, onupdate=datetime.utcnow)
google_calendar_user_id = db.Column(db.Integer, db.ForeignKey('google_calendar_users.id'))
FOCUS_TIME_EVENT_SUMMARY_NAME = 'Focus Time'
@hybrid_property
def marked_busy(self):
if (not self.json_data): return False
json_data = json.loads(self.json_data) if type(self.json_data) == str else self.json_data
if bool(json_data.get('transparency')) & (json_data.get('transparency') == 'transparent'):
return False
return True
@hybrid_property
def is_focus_time_event(self):
return self.summary == self.FOCUS_TIME_EVENT_SUMMARY_NAME
def __repr__(self):
return "GoogleCalendarEvent('%s','%s','%s','%s')" % (self.organizer_email, self.start_time, self.end_time, self.google_calendar_user_id)
class SlackUser(db.Model, EnhancedDBModel):
__tablename__ = 'slack_users'
id = db.Column(db.Integer, primary_key=True)
slack_user_api_id = db.Column(db.String(100), nullable=False)
slack_team_id = db.Column(db.Integer, db.ForeignKey('slack_teams.id'), nullable=False)
slack_email_address = db.Column(db.String(300))
first_name = db.Column(db.String(100))
last_name = db.Column(db.String(100))
slack_username = db.Column(db.String(100), nullable=False)
is_authenticated = db.Column(db.Boolean, nullable=False, default=False)
authentication_oauth_access_token = db.Column(db.String(200))
is_deleted_on_slack = db.Column(db.Boolean, nullable=False, default=False)
created_date = db.Column(db.DateTime, nullable=False)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=False)
slack_timezone_label = db.Column(db.String(100))
slack_timezone_offset = db.Column(db.Integer)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
def slack_team(self):
return SlackTeam.query.filter(SlackTeam.id == self.slack_team_id).one()
def __repr__(self):
return 'SlackUser(%s, %s, %s)' % (self.first_name + self.last_name, self.id, self.slack_user_api_id)
def slack_client(self):
if self.is_authenticated:
return slack.WebClient(self.authentication_oauth_access_token)
else:
return None