def list_by_user_id(self, eid, user_id, query="", page=None, page_size=None): limit = "" if page is not None and page_size is not None: page = page if page > 0 else 1 page = (page - 1) * page_size limit = "Limit {page}, {size}".format(page=page, size=page_size) where = """WHERE a.ID = b.tenant_id AND c.user_id = b.user_id AND a.creater = d.user_id AND b.user_id = {user_id} AND a.enterprise_id = '{eid}' """.format(user_id=user_id, eid=eid) if query: where += """AND ( a.tenant_alias LIKE "%{query}%" OR d.nick_name LIKE "%{query}%" )""".format( query=query) sql = """ SELECT DISTINCT a.ID, a.tenant_id, a.tenant_name, a.tenant_alias, a.is_active, a.enterprise_id, a.create_time, d.nick_name as creater FROM tenant_info a, tenant_perms b, user_info c, user_info d {where} {limit} """.format(where=where, limit=limit) print sql conn = BaseConnection() result = conn.query(sql) return result
def get_plugins_by_service_id(self, region, tenant_id, service_id, category): QUERY_INSTALLED_SQL = """SELECT tp.plugin_id as plugin_id,tp.desc as "desc",tp.plugin_alias as plugin_alias,tp.category as category,pbv.build_version as build_version,tsp.plugin_status as plugin_status FROM tenant_service_plugin_relation tsp LEFT JOIN plugin_build_version pbv ON tsp.plugin_id=pbv.plugin_id AND tsp.build_version=pbv.build_version JOIN tenant_plugin tp ON tp.plugin_id=tsp.plugin_id WHERE tsp.service_id="{0}" AND tp.region="{1}" AND tp.tenant_id="{2}" """.format( service_id, region, tenant_id) QUERI_UNINSTALLED_SQL = """ SELECT tp.plugin_id as plugin_id,tp.desc as "desc",tp.plugin_alias as plugin_alias,tp.category as category,pbv.build_version as build_version FROM tenant_plugin AS tp JOIN plugin_build_version AS pbv ON (tp.plugin_id=pbv.plugin_id) WHERE pbv.plugin_id NOT IN ( SELECT plugin_id FROM tenant_service_plugin_relation WHERE service_id="{0}") AND tp.tenant_id="{1}" AND tp.region="{2}" AND pbv.build_status="{3}" """.format( service_id, tenant_id, region, "build_success") if category == "analysis": query_installed_plugin = """{0} AND tp.category="{1}" """.format( QUERY_INSTALLED_SQL, "analyst-plugin:perf") query_uninstalled_plugin = """{0} AND tp.category="{1}" """.format( QUERI_UNINSTALLED_SQL, "analyst-plugin:perf") elif category == "net_manage": query_installed_plugin = """{0} AND tp.category in {1} """.format( QUERY_INSTALLED_SQL, '("net-plugin:down","net-plugin:up")') query_uninstalled_plugin = """ {0} AND tp.category in {1} """.format( QUERI_UNINSTALLED_SQL, '("net-plugin:down","net-plugin:up")') else: query_installed_plugin = QUERY_INSTALLED_SQL query_uninstalled_plugin = QUERI_UNINSTALLED_SQL dsn = BaseConnection() logger.debug( "\n query_installed_plugin --- {0} \n query_uninstalled_plugin --- {1}" .format(query_installed_plugin, query_uninstalled_plugin)) installed_plugins = dsn.query(query_installed_plugin) uninstalled_plugins = dsn.query(query_uninstalled_plugin) return installed_plugins, uninstalled_plugins
def get_multi_apps_tags(self, eid, app_ids): if not app_ids: return None app_ids = ",".join("'{0}'".format(app_id) for app_id in app_ids) sql = """ select atr.app_id, tag.* from console.rainbond_center_app_tag_relation atr left join console.rainbond_center_app_tag tag on atr.enterprise_id = tag.enterprise_id and atr.tag_id = tag.ID where atr.enterprise_id = '{eid}' and atr.app_id in ({app_ids}); """.format(eid=eid, app_ids=app_ids) conn = BaseConnection() apps = conn.query(sql) return apps
def list_by_svc_share_uuids(self, group_id, dep_uuids): uuids = "'{}'".format("','".join(str(uuid) for uuid in dep_uuids)) conn = BaseConnection() sql = """ SELECT a.service_id, a.service_cname FROM tenant_service a, service_source b, service_group_relation c WHERE a.tenant_id = b.team_id AND a.service_id = b.service_id AND b.service_share_uuid IN ( {uuids} ) AND a.service_id = c.service_id AND c.group_id = {group_id} """.format(group_id=group_id, uuids=uuids) result = conn.query(sql) return result
def get_role_names(self, user_id, tenant_id): sql = """ SELECT group_concat( b.role_name ) AS role_names FROM tenant_perms a, tenant_user_role b, tenant_info c WHERE a.role_id = b.ID AND a.tenant_id = c.ID AND a.user_id = {user_id} AND c.tenant_id = '{tenant_id}'""".format(user_id=user_id, tenant_id=tenant_id) conn = BaseConnection() result = conn.query(sql) if len(result) == 0 or result[0].get("role_names") is None: raise UserRoleNotFoundException( "tenant_id: {tenant_id}; user_id: {user_id}; user role not found" .format(tenant_id=tenant_id, user_id=user_id)) return result[0].get("role_names")
def count_by_tenant_id(self, tenant_id, query=""): where = """ WHERE ti.tenant_id = tr.tenant_id AND ri.region_name = tr.region_name AND ti.tenant_id = "{tenant_id}" """.format(tenant_id=tenant_id) if query: where += "AND (ri.region_name like '%{query}% OR ri.region_alias like '%{query}%)'".format( query=query) sql = """ SELECT count(*) as total FROM region_info ri, tenant_info ti, tenant_region tr {where} """.format(where=where) conn = BaseConnection() result = conn.query(sql) return result[0]["total"]
def list_users_by_tenant_id(self, tenant_id, query="", page=None, size=None): """ Support search by username, email, phone number """ conn = BaseConnection() limit = "" if page is not None and size is not None: page = page if page > 0 else 1 page = (page - 1) * size limit = "Limit {page}, {size}".format(page=page, size=size) where = """WHERE a.user_id = b.user_id AND b.tenant_id = c.ID AND c.tenant_id = '{tenant_id}'""".format(tenant_id=tenant_id) if query: where += """ AND ( a.nick_name LIKE "%{query}%" OR a.phone LIKE "%{query}%" OR a.email LIKE "%{query}%" )""".format(query=query) sql = """ SELECT DISTINCT a.user_id, a.email, a.nick_name, a.phone, a.is_active, a.enterprise_id, b.identity FROM user_info a, tenant_perms b, tenant_info c {where} {limit}""".format(tenant_id=tenant_id, where=where, limit=limit) result = conn.query(sql) return result
def get_app_list(self, tenant_pk, user, tenant_id, region): user_pk = user.pk services = [] if user.is_sys_admin: services = TenantServiceInfo.objects.filter(tenant_id=tenant_id, service_region=region) else: perm = perms_repo.get_user_tenant_perm(tenant_pk, user_pk) if not perm: if tenant_pk == 5073: services = TenantServiceInfo.objects.filter(tenant_id=tenant_id, service_region=region).order_by( 'service_alias') else: role_name = role_repo.get_role_name_by_role_id(perm.role_id) if role_name in ('admin', 'developer', 'viewer', 'gray', 'owner'): services = TenantServiceInfo.objects.filter(tenant_id=tenant_id, service_region=region).order_by( 'service_alias') else: dsn = BaseConnection() add_sql = '' query_sql = ''' SELECT s.* FROM tenant_service s, service_perms sp WHERE s.tenant_id = "{tenant_id}" AND sp.user_id = { user_id } AND sp.service_id = s.ID AND s.service_region = "{region}" { add_sql } ORDER BY s.service_alias'''.format(tenant_id=tenant_id, user_id=user_pk, region=region, add_sql=add_sql) services = dsn.query(query_sql) return services
def list_teams_v2(self, query="", page=None, page_size=None): where = "WHERE t.creater = u.user_id" if query: where += " AND t.tenant_alias LIKE '%{query}%'".format(query=query) limit = "" if page is not None and page_size is not None: page = (page - 1) * page_size limit = "LIMIT {page}, {page_size}".format(page=page, page_size=page_size) sql = """ SELECT t.tenant_name, t.tenant_alias, t.region, t.limit_memory, t.enterprise_id, t.tenant_id, t.create_time, t.is_active, u.nick_name AS creater, count( s.ID ) AS service_num FROM tenant_info t LEFT JOIN tenant_service s ON t.tenant_id = s.tenant_id, user_info u {where} GROUP BY tenant_id ORDER BY service_num DESC {limit} """.format(where=where, limit=limit) print sql conn = BaseConnection() result = conn.query(sql) return result
def get_by_tenant_id(self, tenant_id, user_id): conn = BaseConnection() sql = """ SELECT DISTINCT a.user_id, a.email, a.nick_name, a.phone, a.is_active, a.enterprise_id, b.identity FROM user_info a, tenant_perms b, tenant_info c WHERE a.user_id = b.user_id AND b.tenant_id = c.ID AND a.user_id = {user_id} AND c.tenant_id = '{tenant_id}'""".format(tenant_id=tenant_id, user_id=user_id) result = conn.query(sql) if len(result) == 0: raise UserNotExistError("用户{0}不存在于团队{1}中".format(user_id, tenant_id)) return result[0]
def get_plugins_by_service_id(self, region, tenant_id, service_id, category): """获取组件已开通和未开通的插件""" QUERY_INSTALLED_SQL = """ SELECT tp.plugin_id AS plugin_id, tp.DESC AS "desc", tp.plugin_alias AS plugin_alias, tp.category AS category, tp.origin_share_id AS origin_share_id, pbv.build_version AS build_version, pbv.min_memory AS min_memory, tsp.plugin_status AS plugin_status FROM tenant_service_plugin_relation tsp LEFT JOIN plugin_build_version pbv ON tsp.plugin_id = pbv.plugin_id AND tsp.build_version = pbv.build_version JOIN tenant_plugin tp ON tp.plugin_id = tsp.plugin_id AND tp.tenant_id = pbv.tenant_id WHERE tsp.service_id = "{0}" AND tp.region = "{1}" AND tp.tenant_id = "{2}" """.format(service_id, region, tenant_id) QUERI_UNINSTALLED_SQL = """ SELECT tp.plugin_id AS plugin_id, tp.DESC AS "desc", tp.plugin_alias AS plugin_alias, tp.category AS category, pbv.build_version AS build_version FROM tenant_plugin AS tp JOIN plugin_build_version AS pbv ON tp.plugin_id = pbv.plugin_id AND tp.tenant_id = pbv.tenant_id WHERE pbv.plugin_id NOT IN ( SELECT plugin_id FROM tenant_service_plugin_relation WHERE service_id = "{0}" ) AND tp.tenant_id = "{1}" AND tp.region = "{2}" AND pbv.build_status = "{3}" """.format(service_id, tenant_id, region, "build_success") if category == "analysis": query_installed_plugin = """{0} AND tp.category="{1}" """.format( QUERY_INSTALLED_SQL, "analyst-plugin:perf") query_uninstalled_plugin = """{0} AND tp.category="{1}" """.format( QUERI_UNINSTALLED_SQL, "analyst-plugin:perf") elif category == "net_manage": query_installed_plugin = """{0} AND tp.category in {1} """.format( QUERY_INSTALLED_SQL, '("net-plugin:down","net-plugin:up","net-plugin:in-and-out")') query_uninstalled_plugin = """ {0} AND tp.category in {1} """.format( QUERI_UNINSTALLED_SQL, '("net-plugin:down","net-plugin:up","net-plugin:in-and-out")') else: query_installed_plugin = QUERY_INSTALLED_SQL query_uninstalled_plugin = QUERI_UNINSTALLED_SQL dsn = BaseConnection() installed_plugins = dsn.query(query_installed_plugin) uninstalled_plugins = dsn.query(query_uninstalled_plugin) return installed_plugins, uninstalled_plugins