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