class FormAddDataTable(db.Model, DMPModel):
"""数据从数据库添加表单表"""
__tablename__ = 'dmp_form_add_data_table'
fid = db.Column(db.Integer, primary_key=True, autoincrement=True)
dmp_data_table_name = db.Column(db.String(64), comment='数据名称')
db_table_name = db.Column(db.String(32),
nullable=False,
comment='数据库内数据表名称')
dmp_database_id = db.Column(db.Integer, nullable=False, comment='数据库ID')
dmp_case_id = db.Column(db.Integer, nullable=False, comment='所属案例ID')
@property
def dmp_database_name(self):
from dmp.models import Database
d = Database.get(self.dmp_database_id)
d_name = d.dmp_database_name if d else "-"
return d_name
@property
def dmp_case_name(self):
from dmp.models import Case
c = Case.get(self.dmp_case_id)
c_name = c.dmp_case_name if c else "-"
return c_name
@property
def _json_tmp(self):
_d = {
"submit_dmp_username": self.submit_dmp_username,
"approve_dmp_username": self.approve_dmp_username,
"dmp_case_name": self.dmp_case_name,
"dmp_database_name": self.dmp_database_name,
}
return _d
class FormMigrate(db.Model, DMPModel):
"""数据迁移表单表"""
__tablename__ = 'dmp_form_migrate'
fid = db.Column(db.Integer, primary_key=True, autoincrement=True)
origin_dmp_table_id = db.Column(db.Integer,
nullable=False,
comment='起点数据表')
new_table_name = db.Column(db.String(32), nullable=False, comment='新表名')
destination_dmp_database_id = db.Column(db.Integer,
nullable=False,
comment='目标数据库ID')
@property
def destination_dmp_database_name(self):
from dmp.models import Database
d = Database.get(self.destination_dmp_database_id)
d_name = d.dmp_database_name if d else "-"
return d_name
@property
def origin_data_table_name(self):
from dmp.models import DataTable
t = DataTable.get(self.origin_dmp_table_id)
t_name = t.dmp_data_table_name if t else "-"
return t_name
@property
def _json_tmp(self):
_d = {
"origin_data_table_name": self.origin_data_table_name,
"destination_dmp_database_name":
self.destination_dmp_database_name,
}
return _d
class ArchiveStar(db.Model, DMPModel):
"""文件夹用户收藏关联表"""
__tablename__ = 'dmp_archive_star'
id = db.Column(db.Integer, primary_key=True,
autoincrement=True, comment='看板ID')
dmp_user_id = db.Column(db.Integer, nullable=False, comment='用户ID')
dmp_archive_id = db.Column(db.Integer, nullable=False, comment='文件夹ID')
class FormDownload(db.Model, DMPModel):
"""数据下载表单表"""
__tablename__ = 'dmp_form_download'
fid = db.Column(db.Integer, primary_key=True, autoincrement=True)
dmp_data_table_id = db.Column(db.Integer, nullable=False, comment='源数据表ID')
ftp_url = db.Column(db.String(128), comment='FTP下载链接')
filepath = db.Column(db.String(128), comment='文件路径')
@property
def data_table_name(self):
from dmp.models import DataTable
t = DataTable.get(self.dmp_data_table_id)
t_name = t.dmp_data_table_name if t else "-"
return t_name
@property
def dmp_case_name(self):
from dmp.models import Case
c = Case.get(self.dmp_case_id)
c_name = c.dmp_case_name if c else "-"
return c_name
@property
def _json_tmp(self):
_d = {
"dmp_case_name":self.dmp_case_name,
"data_table_name":self.data_table_name,
}
return _d
class DataTableColumnRange(db.Model, DMPModel):
"""数据列区间表"""
__tablename__ = 'dmp_data_table_column_range'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
context = db.Column(db.String(256), comment='内容')
dmp_data_table_column_id = db.Column(db.Integer,
nullable=False,
comment='列ID')
class UserDashboard(db.Model, DMPModel):
"""主页看板用户关联表"""
__tablename__ = 'dmp_user_dashboard'
id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
comment='ID')
dmp_user_id = db.Column(db.Integer, nullable=False, comment='用户ID')
dmp_dashboard_id = db.Column(db.Integer, nullable=False, comment='看板ID')
class DashboardStar(db.Model, DMPModel):
"""看板用户收藏关联表"""
__tablename__ = 'dmp_dashboard_star'
id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
comment='看板ID')
dmp_user_id = db.Column(db.Integer, nullable=False, comment='用户ID')
dmp_dashboard_id = db.Column(db.Integer, nullable=False, comment='文件夹ID')
class Rights(db.Model, DMPModel):
"""权利表"""
__tablename__ = 'dmp_rights'
id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
comment='功能ID')
dmp_rights_name = db.Column(db.String(32),
nullable=False,
comment='路由功能名称')
class Permissions(db.Model, DMPModel):
"""权限表"""
__tablename__ = 'dmp_permission'
id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
comment='权限ID')
route = db.Column(db.String(64), nullable=False, comment='权限路由')
dmp_permission_name = db.Column(db.String(32),
nullable=False,
comment='路由功能名称')
def __repr__(self):
return self.dmp_permission_name
def permission_to_dict(self):
permission_dict = {
'id': self.id,
'route': self.route,
'dmp_permission_name': self.dmp_permission_name
}
return permission_dict
@classmethod
def init_permission(cls):
permission_list = [{
"route":
r.rule,
"desc":
None if not r.defaults else r.defaults.get("desc")
} for r in current_app.url_map.__dict__.get("_rules")]
db_permission_list = []
current_app.logger.info(permission_list)
# 将is_permission=None的接口和{'route': '/static/<path:filename>'}、
# {'route': '/user/DMP-service/<path:filename>', 'desc': None}这两个接口剔除
try:
for rout in permission_list:
current_app.logger.info(rout)
child_rout = rout.get('desc')
if rout.get('desc') == None or child_rout.get(
'is_permission') == False:
continue
permission = Permissions()
permission.route = str(rout.get("route"))
desc_dict = rout.get('desc')
permission.dmp_permission_name = str(
desc_dict.get("interface_name"))
db_permission_list.append(rout)
db.session.add(permission)
# db.session.commit()
session['db_permission_list'] = db_permission_list
current_app.logger.info("permission init complete")
except Exception as err:
current_app.logger.error(err)
class SavedQuery(db.Model, DMPModel):
"""已保存查询表"""
__tablename__ = 'dmp_saved_query'
id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
comment='看板ID')
query_name = db.Column(db.String(64), nullable=False, comment='查询语句的保存名称')
query_sql = db.Column(db.Text, nullable=False, comment='查询语句')
description = db.Column(db.String(512), comment='备注')
dmp_data_table_id = db.Column(db.Integer, nullable=False, comment='数据表ID')
dmp_case_id = db.Column(db.Integer, nullable=False, comment='所属案例ID')
created_dmp_user_id = db.Column(db.Integer, nullable=False, comment='创建人')
changed_dmp_user_id = db.Column(db.Integer, comment='修改人')
created_on = db.Column(db.DateTime,
default=datetime.datetime.now,
comment='创建时间')
changed_on = db.Column(db.DateTime,
default=datetime.datetime.now,
onupdate=datetime.datetime.now,
comment='最后修改时间')
@property
def dmp_data_table_name(self):
from .dmp_data_table import DataTable
if DataTable.exist_item_by_id(self.dmp_data_table_id):
data_table_name = DataTable.get(
self.dmp_data_table_id).dmp_data_table_name
return data_table_name
return "-"
@property
def dmp_case_name(self):
from .dmp_data_table import DataTable
from .dmp_case import Case
if DataTable.exist_item_by_id(self.dmp_data_table_id):
case_id = DataTable.get(self.dmp_data_table_id).dmp_case_id
if Case.exist_item_by_id(case_id):
case_name = Case.get(case_id).dmp_case_name
return case_name
return "-"
@property
def _json_tmp(self):
_d = {
"created_dmp_user_name": self.created_dmp_user_name,
"changed_dmp_user_name": self.changed_dmp_user_name,
"dmp_data_table_name": self.dmp_data_table_name,
"dmp_case_name": self.dmp_case_name,
}
return _d
class UserDataService(db.Model, DMPModel):
"""用户服务访问关联表"""
__tablename__ = 'dmp_user_data_service'
id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
comment='ID')
dmp_user_id = db.Column(db.Integer, nullable=False, comment='用户ID')
dmp_data_service_id = db.Column(db.Integer,
nullable=False,
comment='数据服务ID')
access_time = db.Column(db.DateTime,
default=datetime.datetime.now,
comment='到访时间')
class Case(db.Model, DMPModel):
"""案例表"""
__tablename__ = 'dmp_case'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
dmp_case_name = db.Column(db.String(32), nullable=False, comment='案例名称')
description = db.Column(db.Text, comment='案例说明')
url_name = db.Column(db.String(32), comment='可视化网站名称')
url = db.Column(db.String(64), comment='网址')
created_on = db.Column(db.DateTime,
nullable=False,
default=datetime.datetime.now,
comment='创建时间')
changed_on = db.Column(db.DateTime,
default=datetime.datetime.now,
onupdate=datetime.datetime.now,
comment='修改时间')
def delete(self):
from dmp.models import DataTable
case_table_count = DataTable.query.filter_by(
dmp_case_id=self.id).count()
if case_table_count == 0:
db.session.delete(self)
elif case_table_count > 0:
raise Exception(
"This case contains a data table and cannot be deleted!")
class KylinTable(db.Model, DMPModel):
"""kylin表"""
__tablename__ = 'dmp_kylin_table'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
kylin_cube_name = db.Column(db.String(64),
unique=True,
nullable=False,
comment='cube名称')
kylin_models_name = db.Column(db.String(64),
unique=True,
nullable=False,
comment='model名称')
db_table_name = db.Column(db.String(64),
nullable=False,
comment='数据库内数据表名称')
is_kylin = db.Column(db.Integer,
default=0,
comment='是否是Kylin表,0否，1是， 2转化中 ')
created_on = db.Column(db.DateTime,
nullable=False,
default=datetime.datetime.now,
comment='创建时间')
changed_on = db.Column(db.DateTime,
default=datetime.datetime.now,
onupdate=datetime.datetime.now,
comment='修改时间')
@classmethod
def exist_item_by_db_table_name(cls, table_name):
item = cls.query.filter_by(db_table_name=table_name).first()
if item:
return True
return False
class FormUpload(db.Model, DMPModel):
"""数据文件上传表单表"""
__tablename__ = 'dmp_form_upload'
fid = db.Column(db.Integer, primary_key=True, autoincrement=True)
filetype = db.Column(db.Integer, nullable=False, comment='文件类型')
filepath = db.Column(db.String(256), nullable=False, comment='文件路径')
column_line = db.Column(db.Integer, comment='列名行号')
column = db.Column(db.String(32), comment='自定义列名')
method = db.Column(db.Integer, default=1, comment='新建1、添加2或覆盖3')
destination_dmp_database_id = db.Column(db.Integer,
nullable=False,
comment='目标数据库ID')
destination_db_table_name = db.Column(db.String(32),
nullable=False,
comment='数据库内数据表名称')
dmp_case_id = db.Column(db.Integer, nullable=False, comment='所属案例')
dmp_data_table_name = db.Column(db.String(32),
unique=True,
nullable=False,
comment='数据名称')
@property
def destination_database_name(self):
from dmp.models import Database
d = Database.get(self.destination_dmp_database_id)
d_name = d.dmp_database_name if d else "-"
return d_name
@property
def dmp_case_name(self):
from dmp.models import Case
c = Case.get(self.dmp_case_id)
c_name = c.dmp_case_name if c else "-"
return c_name
@property
def _json_tmp(self):
_d = {
"dmp_case_name": self.dmp_case_name,
"destination_database_name": self.destination_database_name,
}
return _d
class DataTableColumn(db.Model, DMPModel):
"""数据列信息表"""
__tablename__ = 'dmp_data_table_column'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
dmp_data_table_column_name = db.Column(db.String(32),
nullable=False,
comment='列名')
dmp_data_table_column_type = db.Column(db.String(32), comment='数据类型')
groupby = db.Column(db.Boolean, default=False, comment='可以进行分组')
wherein = db.Column(db.Boolean, default=False, comment='可以区间筛选')
isdate = db.Column(db.Boolean, default=False, comment='是否为时间日期字段')
description = db.Column(db.Text, comment='字段说明')
dmp_data_table_id = db.Column(db.Integer, nullable=False, comment='数据ID')
def delete(self):
from dmp.models import DataTableColumnRange
data_table_columns_range = DataTableColumnRange.query.filter_by(
dmp_data_table_column_id=self.id).delete()
db.session.delete(self)
class DataServiceParameter(db.Model, DMPModel):
"""数据服务表"""
__tablename__ = 'dmp_data_service_parameter'
id = db.Column(db.Integer, primary_key=True,
autoincrement=True, comment='参数ID')
dmp_data_service_id = db.Column(
db.Integer, nullable=False, comment='数据服务ID')
parameter_name = db.Column(
db.String(128), nullable=False, comment='参数名(要进行筛选的字段名)')
type = db.Column(db.String(64), default='-', comment='参数类型。取决于源数据表的类型定义')
required_parameter = db.Column(db.Integer, default=0, comment='是否为必填项')
description = db.Column(db.String(512), comment='简介')
def save_bulk(self, objects):
item = objects[0]
dmp_data_service_id = item.dmp_data_service_id
parameters = DataServiceParameter.query.filter_by(dmp_data_service_id=dmp_data_service_id)
count = parameters.count()
if count > 0:
parameters.delete()
db.session.add_all(objects)
db.session().commit()
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date : 2020/5/6
# @Author : SHTD
from dmp.extensions import db
"""用户组_权限表"""
group_permission = db.Table(
'dmp_group_permission',
db.Column('dmp_group_id',
db.Integer,
db.ForeignKey('dmp_group.id'),
comment='用户组ID'),
db.Column('dmp_permission_id',
db.Integer,
db.ForeignKey('dmp_permission.id'),
comment='权限ID'))
class Dashboard(db.Model, DMPModel):
"""数据看板表"""
__tablename__ = 'dmp_dashboard'
id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
comment='看板ID')
dmp_dashboard_name = db.Column(db.String(64),
unique=False,
nullable=False,
comment='数据看板名称')
description = db.Column(db.String(512), default=None, comment='备注,简介')
release = db.Column(db.Integer, default=0, comment='是否发布，0否1是2下线')
charts_position = db.Column(db.Text, comment='图标布局数据')
upper_dmp_dashboard_archive_id = db.Column(db.Integer, comment='文件夹ID')
created_dmp_user_id = db.Column(db.Integer, nullable=False, comment='创建人')
changed_dmp_user_id = db.Column(db.Integer, comment='修改人')
created_on = db.Column(db.DateTime,
default=datetime.datetime.now,
comment='创建时间')
changed_on = db.Column(db.DateTime,
default=datetime.datetime.now,
onupdate=datetime.datetime.now,
comment='最后修改时间')
def dashboard_to_dict(self):
dashboard_dict = {
'id': self.id,
'dmp_dashboard_name': self.dmp_dashboard_name,
'description': self.description,
'release': self.release,
'charts_position': self.charts_position,
'upper_dmp_dashboard_archive_id':
self.upper_dmp_dashboard_archive_id,
'created_dmp_user_id': self.created_dmp_user_id,
'changed_dmp_user_id': self.changed_dmp_user_id,
'created_on': self.created_on.strftime("%Y-%m-%d %H:%M:%S"),
'changed_on': self.changed_on.strftime("%Y-%m-%d %H:%M:%S")
}
return dashboard_dict
def update_change_time(self, item):
from .dmp_archive import DashboardArchive
if DashboardArchive.exist_item_by_id(
item.upper_dmp_dashboard_archive_id):
upper = DashboardArchive.get(item.upper_dmp_dashboard_archive_id)
upper.changed_on = item.changed_on
upper.save()
if upper.upper_dmp_dashboard_archive_id:
self.update_change_time(upper)
def save(self):
try:
self.put()
self.commit()
if self.upper_dmp_dashboard_archive_id:
self.update_change_time(item=self)
except Exception as e:
self.rollback()
raise e
@classmethod
def exsit_dashboard_by_name(cls, dashboard_name):
item = cls.query.filter_by(dmp_dashboard_name=dashboard_name).first()
if item:
return True
return False
def delete(self):
from .dmp_chart import Chart
charts = db.session.query(Chart).filter(
Chart.dmp_dashboard_id == self.id).all()
for chart in charts:
chart.delete()
db.session.delete(self)
db.session.commit()
@property
def upper_dmp_dashboard_archive_name(self):
from .dmp_archive import DashboardArchive
if self.upper_dmp_dashboard_archive_id:
if DashboardArchive.exist_item_by_id(
self.upper_dmp_dashboard_archive_id):
archive_name = DashboardArchive.get(
self.upper_dmp_dashboard_archive_id).dashboard_archive_name
return archive_name
return "-"
@property
def _json_tmp(self):
_d = {
"created_dmp_user_name":
self.created_dmp_user_name,
"changed_dmp_user_name":
self.changed_dmp_user_name,
"upper_dmp_dashboard_archive_name":
self.upper_dmp_dashboard_archive_name,
}
return _d
class Groups(db.Model, DMPModel):
"""用户组表"""
__tablename__ = 'dmp_group'
id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
comment='用户组ID')
dmp_group_name = db.Column(db.String(32),
unique=True,
nullable=False,
comment='用户组名')
max_count = db.Column(db.Integer, comment='最大用户数量')
created_on = db.Column(db.DateTime,
default=datetime.datetime.now,
comment='创建时间')
changed_on = db.Column(db.DateTime,
default=datetime.datetime.now,
onupdate=datetime.datetime.now,
comment='修改时间')
permissions = db.relationship('Permissions', secondary=group_permission)
rights = db.relationship('Rights', secondary=group_rights)
def __repr__(self):
return self.dmp_group_name
def group_to_dict(self):
group_dict = {
'id': self.id,
'dmp_group_name': self.dmp_group_name,
'max_count': self.max_count,
'created_on': self.created_on.strftime("%Y-%m-%d %H:%M:%S"),
'changed_on': self.changed_on.strftime("%Y-%m-%d %H:%M:%S"),
}
return group_dict
@classmethod
def init_group(cls):
try:
from .dmp_permission import Permissions
admin_group = Groups()
admin_group.dmp_group_name = "admin"
current_app.logger.info(Permissions.query.all())
db.session.add(admin_group)
db.session.commit()
for per in Permissions.query.all():
admin_group.permissions.append(per)
db.session.commit()
current_app.logger.info("create admin group")
teacher_group = Groups()
teacher_group.dmp_group_name = "teacher"
db.session.add(teacher_group)
db.session.commit()
# 默认给教师分配权限
db_permission_list = session.get('db_permission_list')
for rout in db_permission_list:
desc_dict = rout.get('desc')
# 1:为教师拥有的权限
if desc_dict.get('permission_belong') == 0 or desc_dict.get(
'permission_belong') == 1:
teacher_group.permissions.append(
Permissions.query.filter(
Permissions.dmp_permission_name == desc_dict.get(
'interface_name')).first())
current_app.logger.info("create teacher group")
student_group = Groups()
student_group.dmp_group_name = "student"
db.session.add(student_group)
db.session.commit()
# 默认给学生分配权限
for rout in db_permission_list:
desc_dict = rout.get('desc')
# 0:为学生拥有的权限(同时教师也拥有)
if desc_dict.get('permission_belong') == 0:
student_group.permissions.append(
Permissions.query.filter(
Permissions.dmp_permission_name == desc_dict.get(
'interface_name')).first())
current_app.logger.info("create student group")
except Exception as err:
current_app.logger.error(err)
class Database(db.Model, DMPModel):
"""数据库表"""
__tablename__ = 'dmp_database'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
dmp_database_name = db.Column(db.String(32),
unique=True,
nullable=False,
comment='数据显示名称')
db_type = db.Column(db.Integer,
nullable=False,
comment='数据库类型,hive:1,mysql:2,mongo:3')
db_host = db.Column(db.String(32), nullable=False, comment='数据库主机地址')
db_port = db.Column(db.Integer, nullable=False, comment='数据库端口号')
db_name = db.Column(db.String(32), comment='数据库名称')
db_username = db.Column(db.String(32), comment='数据库用户名')
db_passwd = db.Column(db.String(64), comment='数据库密码')
ispublic = db.Column(db.Boolean, default=False, comment='是否公开')
description = db.Column(db.Text, comment='数据库说明')
created_on = db.Column(db.DateTime,
nullable=False,
default=datetime.datetime.now,
comment='创建时间')
changed_on = db.Column(db.DateTime,
default=datetime.datetime.now,
onupdate=datetime.datetime.now,
comment='修改时间')
dmp_user_id = db.Column(db.Integer, nullable=False, comment='所属用户ID')
@property
def dmp_user_name(self):
from dmp.models import Users
u = Users.get(self.dmp_user_id)
user_name = u.dmp_username if u else "-"
return user_name
def delete(self):
from dmp.models import DataTable
case_table_count = DataTable.query.filter_by(
dmp_database_id=self.id).count()
if case_table_count == 0:
db.session.delete(self)
elif case_table_count > 0:
raise Exception(
"The database already has data association and cannot be deleted"
)
@property
def _json_tmp(self):
_d = {
"dmp_user_name": self.dmp_user_name,
}
return _d
class Chart(db.Model, DMPModel):
"""图表表"""
__tablename__ = 'dmp_chart'
id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
comment='看板ID')
chart_name = db.Column(db.String(64), nullable=False, comment='图表名称')
dmp_data_table_id = db.Column(db.Integer, comment='数据表ID')
query_string = db.Column(db.JSON, comment='查询语句')
chart_data = db.Column(db.JSON, comment='数据')
chart_type = db.Column(db.Integer,
nullable=False,
comment='图表类型，柱状图1，折线图2，饼图3，地图4，雷达图5')
params = db.Column(db.Text, comment='图表参数')
update_interval = db.Column(db.Integer, default=0, comment='时间间隔时间')
update_unit = db.Column(db.Integer, default=0, comment='时间间隔单位，0小时，1日，3周')
description = db.Column(db.String(512), comment='简介')
charts_position = db.Column(db.JSON, nullable=False, comment='图表布局数据')
dmp_dashboard_id = db.Column(db.Integer, nullable=False, comment='数据看板ID')
update_task_id = db.Column(db.String(64), comment='更新任务ID')
created_dmp_user_id = db.Column(db.Integer, nullable=False, comment='创建人')
changed_dmp_user_id = db.Column(db.Integer, comment='修改人')
created_on = db.Column(db.DateTime,
default=datetime.datetime.now,
comment='创建时间')
changed_on = db.Column(db.DateTime,
default=datetime.datetime.now,
onupdate=datetime.datetime.now,
comment='最后修改时间')
def chart_to_dict(self):
chart_dict = {
'id': self.id,
'chart_name': self.chart_name,
'dmp_data_table_id': self.dmp_data_table_id,
'query_string': self.query_string,
'chart_data': self.chart_data,
'chart_type': self.chart_type,
'params': self.params,
'update_interval': self.update_interval,
'update_unit': self.update_unit,
'description': self.description,
'charts_position': self.charts_position,
'dmp_dashboard_id': self.dmp_dashboard_id,
'update_task_id': self.update_task_id,
'created_dmp_user_id': self.created_dmp_user_id,
'changed_dmp_user_id': self.changed_dmp_user_id,
'created_on': self.created_on.strftime("%Y-%m-%d %H:%M:%S"),
'changed_on': self.changed_on.strftime("%Y-%m-%d %H:%M:%S")
}
return chart_dict
def delete(self):
from dmp.extensions import apscheduler
if self.update_task_id:
apscheduler.delete_job(id=self.update_task_id)
db.session.delete(self)
db.session.commit()
@property
def dmp_data_table_name(self):
from .dmp_data_table import DataTable
if DataTable.exist_item_by_id(self.dmp_data_table_id):
data_table_name = DataTable.get(
self.dmp_data_table_id).dmp_data_table_name
return data_table_name
return "-"
@property
def dmp_data_table(self):
from .dmp_data_table import DataTable
if DataTable.exist_item_by_id(self.dmp_data_table_id):
data_table = DataTable.get(self.dmp_data_table_id)
return data_table if data_table else None
return "-"
@property
def _json_tmp(self):
_d = {
"created_dmp_user_name": self.created_dmp_user_name,
"changed_dmp_user_name": self.changed_dmp_user_name,
"dmp_data_table_name": self.dmp_data_table_name,
}
return _d
class Users(db.Model, DMPModel):
"""用户表"""
__tablename__ = 'dmp_user'
id = db.Column(db.Integer, primary_key=True,
autoincrement=True, comment='用户ID')
dmp_username = db.Column(db.String(64), unique=True,
nullable=False, comment='用户名')
real_name = db.Column(db.String(32), nullable=False, comment='真实姓名')
email = db.Column(db.String(64), unique=True,
nullable=False, comment='用户邮箱')
passwd = db.Column(db.String(512), nullable=False, comment='用户密码，加密储存')
confirmed = db.Column(db.Boolean, default=False, comment='用户激活状态')
icon = db.Column(db.String(128), default=None, comment='用户头像')
is_deleted = db.Column(db.Boolean, default=False, comment="用户逻辑删除")
dmp_user_info = db.Column(db.String(256), default=None, comment='个人简介')
last_login = db.Column(
db.DateTime, default=datetime.datetime.now, comment='最后登录时间')
created_on = db.Column(
db.DateTime, default=datetime.datetime.now, comment='创建时间')
changed_on = db.Column(db.DateTime, default=datetime.datetime.now,
onupdate=datetime.datetime.now, comment='修改时间')
dmp_group_id = db.Column(db.Integer, db.ForeignKey(
'dmp_group.id'), nullable=False, comment='所属用户组ID，默认学生用户组')
leader_dmp_user_id = db.Column(db.Integer, db.ForeignKey(
'dmp_user.id'), comment='直属管理者，默认是超级管理员用户, 自关联')
groups = db.relationship('Groups', backref='users')
leader = db.relationship('Users', remote_side=[id], backref='selfleader')
def __init__(self, **kwargs):
super(Users, self).__init__(**kwargs)
if self.dmp_group_id is None:
self.dmp_group_id = 3
def user_to_dict(self):
user_dict = {
'id': self.id,
'dmp_username': self.dmp_username,
'real_name': self.real_name,
'email': self.email,
'passwd': self.passwd,
'confirmed': self.confirmed,
'icon': self.icon,
'dmp_user_info': self.dmp_user_info,
'last_login': self.last_login.strftime("%Y-%m-%d %H:%M:%S"),
'created_on': self.created_on.strftime("%Y-%m-%d %H:%M:%S"),
'changed_on': self.changed_on.strftime("%Y-%m-%d %H:%M:%S"),
'dmp_group_id': self.dmp_group_id,
'leader_dmp_user_id': self.leader_dmp_user_id,
}
return user_dict
def encode_auth_token(self):
'''用户登录后，发放有效的 JWT'''
try:
payload = {
'exp': datetime.datetime.utcnow() + datetime.timedelta(days=0, seconds=60*60*24),
'iat': datetime.datetime.utcnow(),
'user_id': self.id
}
return jwt.encode(
payload,
current_app.config.get('SECRET_KEY'),
algorithm='HS256'
)
except Exception as e:
return e
@staticmethod
def decode_auth_token(token):
'''验证 JWT 的有效性'''
try:
payload = jwt.decode(
token,
current_app.config['SECRET_KEY'],
algorithms=['HS256'])
return payload['user_id']
except jwt.ExpiredSignatureError:
return 'Signature expired. Please log in again.'
except jwt.InvalidTokenError:
return 'Invalid token. Please log in again.'
@staticmethod
def check_activate_token(res):
# 激活邮箱
try:
user = Users.query.get(res.get('id'))
if not user:
return resp_hanlder(code=1008, msg=RET.alert_code[1008], result={})
if not user.confirmed:
user.confirmed = True
db.session.add(user)
db.session.commit()
return True
except Exception as err:
return resp_hanlder(code=999, err=err)
@classmethod
def reset_password(cls, token, new_password):
res = cls.decode_auth_token(token)
if not isinstance(res, str):
user = Users.query.filter(Users.id == res).first()
user.password = new_password
db.session.add(user)
db.session.commit()
return True
else:
return res
@property
def password(self):
raise AttributeError("password is not a readable attribute.")
@password.setter
def password(self, password):
self.passwd = generate_password_hash(password)
def verify_password(self, password):
"""验证密码"""
return check_password_hash(self.passwd, password)
class DataService(db.Model, DMPModel):
"""数据服务表"""
__tablename__ = 'dmp_data_service'
id = db.Column(db.Integer, primary_key=True,
autoincrement=True, comment='看板ID')
data_service_name = db.Column(
db.String(64), nullable=False, comment='数据服务名称')
request_method = db.Column(
db.Integer, default=1, comment='请求方法，1GET 2POST')
api_path = db.Column(db.String(256), unique=True,
nullable=False, comment='API地址')
source_dmp_data_table_id = db.Column(
db.Integer, comment='数据表ID')
query_sql = db.Column(db.Text, comment='查询语句，MySQL必须项')
query_params = db.Column(db.Text, comment='查询参数，MongoDB必须项')
state = db.Column(db.Integer, default=0, comment='是否启用')
description = db.Column(db.String(512), comment='服务简介')
created_dmp_user_id = db.Column(db.Integer, nullable=False, comment='创建人')
changed_dmp_user_id = db.Column(db.Integer, comment='修改人')
created_on = db.Column(
db.DateTime, default=datetime.datetime.now, comment='创建时间')
changed_on = db.Column(db.DateTime, default=datetime.datetime.now,
onupdate=datetime.datetime.now, comment='最后修改时间')
def delete(self):
from dmp.models import DataServiceParameter
count = DataServiceParameter.query.filter_by(dmp_data_service_id=self.id).count()
if count > 0:
parameters = DataServiceParameter.query.filter_by(dmp_data_service_id=self.id)
parameters.delete()
db.session.delete(self)
else:
db.session.delete(self)
@classmethod
def exsit_data_service_by_name(cls, ds_name):
item = cls.query.filter_by(data_service_name=ds_name).first()
if item:
return True
return False
@classmethod
def exsit_data_service_by_apipath(cls, apipath):
item = cls.query.filter(cls.api_path==apipath).first()
# print(db.session.query(cls.api_path).all())
if item:
return True
return False
def delete(self):
try:
from .dmp_data_service_parameter import DataServiceParameter
DataServiceParameter.query.filter_by(
dmp_data_service_id=self.id).delete()
db.session.delete(self)
except Exception as err:
raise err
@property
def params(self):
from .dmp_data_service_parameter import DataServiceParameter
_params = DataServiceParameter.query.filter_by(dmp_data_service_id=self.id).all()
if len(_params) > 0:
res = [{"parameter_name": p.parameter_name, "required": bool(p.required_parameter)} for p in _params]
return res
else:
return []
@ property
def source_dmp_data_table_name(self):
from .dmp_data_table import DataTable
if DataTable.exist_item_by_id(self.source_dmp_data_table_id):
data_table_name = DataTable.get(
self.source_dmp_data_table_id).db_table_name
return data_table_name
return "-"
@ property
def dmp_case_id(self):
from .dmp_data_table import DataTable
if DataTable.exist_item_by_id(self.source_dmp_data_table_id):
dmp_case_id = DataTable.get(
self.source_dmp_data_table_id).dmp_case_id
return dmp_case_id
return "-"
@ property
def database_type(self):
from .dmp_data_table import DataTable
if DataTable.exist_item_by_id(self.source_dmp_data_table_id):
dmp_database_type = DataTable.get(
self.source_dmp_data_table_id).dmp_database_type
return dmp_database_type
return "-"
@property
def full_api(self):
from flask import current_app
return current_app.config.get("DS_API_URL") + self.api_path
@ property
def _json_tmp(self):
_d = {
"created_dmp_user_name": self.created_dmp_user_name,
"changed_dmp_user_name": self.changed_dmp_user_name,
"source_dmp_data_table_name": self.source_dmp_data_table_name,
"database_type": self.database_type,
"full_api":self.full_api,
"dmp_case_id":self.dmp_case_id,
}
return _d
class DashboardArchive(db.Model, DMPModel):
"""数据看板文件夹表"""
__tablename__ = 'dmp_dashboard_archive'
id = db.Column(db.Integer, primary_key=True,
autoincrement=True, comment='看板ID')
dashboard_archive_name = db.Column(
db.String(64), unique=True, nullable=False, comment='文件夹名称')
upper_dmp_dashboard_archive_id = db.Column(
db.Integer, comment='文件夹ID')
created_dmp_user_id = db.Column(db.Integer, nullable=False, comment='创建人')
changed_dmp_user_id = db.Column(db.Integer, comment='修改人')
created_on = db.Column(
db.DateTime, default=datetime.datetime.now, comment='创建时间')
changed_on = db.Column(db.DateTime, default=datetime.datetime.now,
onupdate=datetime.datetime.now, comment='最后修改时间')
def dashboard_archive_to_dict(self):
dashboard_archive_dict = {
'id': self.id,
'dashboard_archive_name': self.dashboard_archive_name,
'upper_dmp_dashboard_archive_id': self.upper_dmp_dashboard_archive_id,
'created_dmp_user_id': self.created_dmp_user_id,
'changed_dmp_user_id': self.changed_dmp_user_id,
'created_on': self.created_on.strftime("%Y-%m-%d %H:%M:%S"),
'changed_on': self.changed_on.strftime("%Y-%m-%d %H:%M:%S")
}
return dashboard_archive_dict
def save(self):
try:
self.put()
self.commit()
# if self.upper_dmp_dashboard_archive_id:
# if DashboardArchive.exist_item_by_id(self.upper_dmp_dashboard_archive_id):
# upper = DashboardArchive.get(self.upper_dmp_dashboard_archive_id)
# upper.changed_on = self.changed_on
# upper.save()
except Exception as e:
self.rollback()
raise e
@classmethod
def exsit_archive_by_name(cls, archive_name):
item = cls.query.filter_by(dashboard_archive_name=archive_name).first()
if item:
return True
return False
def delete(self):
from .dmp_dashboard import Dashboard
db.session.query(Dashboard).filter(
Dashboard.upper_dmp_dashboard_archive_id == self.id).delete()
db.session.delete(self)
db.session.commit()
@property
def upper_dmp_dashboard_archive_name(self):
if self.upper_dmp_dashboard_archive_id:
if self.exist_item_by_id(self.upper_dmp_dashboard_archive_id):
archive_name = DashboardArchive.get(
self.upper_dmp_dashboard_archive_id).dashboard_archive_name
return archive_name
return "-"
@property
def _json_tmp(self):
_d = {
"created_dmp_user_name": self.created_dmp_user_name,
"changed_dmp_user_name": self.changed_dmp_user_name,
"upper_dmp_dashboard_archive_name": self.upper_dmp_dashboard_archive_name,
}
return _d
class DataTable(db.Model, DMPModel):
"""数据表"""
__tablename__ = 'dmp_data_table'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
dmp_data_table_name = db.Column(db.String(32),
unique=True,
nullable=False,
comment='数据名称')
db_table_name = db.Column(db.String(32),
nullable=False,
comment='数据库内数据表名称')
db_data_count = db.Column(db.Integer, default=0, comment='数据表内的数据量')
is_kylin = db.Column(db.Integer,
default=0,
comment='是否是Kylin表,0否，1是， 2转化中 ')
kylin_info_id = db.Column(db.Integer, comment='Kylin信息表ID')
description = db.Column(db.Text, comment='数据说明')
created_on = db.Column(db.DateTime,
nullable=False,
default=datetime.datetime.now,
comment='创建时间')
changed_on = db.Column(db.DateTime,
default=datetime.datetime.now,
onupdate=datetime.datetime.now,
comment='修改时间')
dmp_user_id = db.Column(db.Integer, nullable=False, comment='添加人')
dmp_database_id = db.Column(db.Integer, nullable=False, comment='数据库ID')
dmp_case_id = db.Column(db.Integer, nullable=False, comment='所属案例ID')
@property
def dmp_user_name(self):
from dmp.models import Users
u = Users.get(self.dmp_user_id)
user_name = u.dmp_username if u else "-"
return user_name
@property
def dmp_case_name(self):
from dmp.models import Case
c = Case.get(self.dmp_case_id)
c_name = c.dmp_case_name if c else "-"
return c_name
@property
def dmp_database_name(self):
from dmp.models import Database
d = Database.get(self.dmp_database_id)
d_name = d.dmp_database_name if d else "-"
return d_name
@property
def dmp_database_type(self):
from dmp.models import Database
d = Database.get(self.dmp_database_id)
d_type = d.db_type if d else "-"
return d_type
def data_count(self):
from dmp.utils.engine import auto_connect
conn = auto_connect(db_id=self.dmp_database_id)
count = conn.count(self.db_table_name)
self.db_data_count = count
self.put()
def delete(self):
try:
from dmp.models import DataTableColumn
data_table_columns = DataTableColumn.query.filter_by(
dmp_data_table_id=self.id).all()
for dtc in data_table_columns:
dtc.delete()
db.session.delete(self)
except Exception as err:
db.session.rollback()
raise err
@property
def _json_tmp(self):
_d = {
"dmp_user_name": self.dmp_user_name,
"dmp_case_name": self.dmp_case_name,
"dmp_database_name": self.dmp_database_name,
"database_type": self.dmp_database_type,
}
return _d
class Forms(db.Model, DMPModel):
"""表单总表"""
__tablename__ = 'dmp_form'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
submit_dmp_user_id = db.Column(db.Integer, nullable=False, comment='提交人')
submit_on = db.Column(db.DateTime, nullable=False, default=datetime.datetime.now, comment='提交时间')
description = db.Column(db.Text, comment='说明')
approve_dmp_user_id = db.Column(db.Integer, comment='审批人')
approve_on = db.Column(db.DateTime, comment='审批时间')
approve_result = db.Column(db.Integer, default=0, comment='审批结果,默认:0,通过:1,不通过:2')
answer = db.Column(db.Text, comment='审批答复')
created_on = db.Column(db.DateTime, nullable=False, default=datetime.datetime.now, comment='创建时间')
changed_on = db.Column(db.DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, comment='修改时间')
form_type = db.Column(db.Integer, default=1, comment='表单类型')
form_info_id = db.Column(db.Integer,nullable=False, comment='表单详情ID ')
finish = db.Column(db.Boolean,default=False, comment='是否完成')
result = db.Column(db.Text, comment='结果')
__mapper_args__ = {
"order_by": submit_on.desc()
}
@property
def submit_dmp_username(self):
from dmp.models import Users
u = Users.get(self.submit_dmp_user_id)
s_u_name = u.dmp_username if u else "-"
return s_u_name
@property
def approve_dmp_username(self):
from dmp.models import Users
u = Users.get(self.approve_dmp_user_id)
a_u_name = u.dmp_username if u else "-"
return a_u_name
@property
def info_form(self):
from dmp.models import FormAddDataTable,FormUpload,FormMigrate,FormDownload
form_type_map = {
1:FormAddDataTable,
2:FormUpload,
3:FormMigrate,
4:FormDownload
}
form_info = form_type_map.get(self.form_type).get(self.form_info_id)
return form_info
@property
def _json_tmp(self):
_d = {
"submit_dmp_username":self.submit_dmp_username,
"approve_dmp_username": self.approve_dmp_username,
}
info_d = self.info_form.__json__()
_d.update(info_d)
return _d
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date : 2020/5/6
# @Author : SHTD
from dmp.extensions import db
"""用户组权利表"""
group_rights = db.Table(
'dmp_group_rights',
db.Column('dmp_group_id',
db.Integer,
db.ForeignKey('dmp_group.id'),
comment='用户组ID'),
db.Column('dmp_rights_id',
db.Integer,
db.ForeignKey('dmp_rights.id'),
comment='权利ID'))
