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: if perm.identity 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_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_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 count_users_by_tenant_id(self, tenant_id, query=""): """ Support search by username, email, phone number """ 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 count(*) as total FROM ( SELECT DISTINCT a.user_id AS user_id FROM user_info a, tenant_perms b, tenant_info c {where} ) as userid""".format(where=where) conn = BaseConnection() result = conn.query(sql) return result[0].get("total")
def get_app_list(self, tenant_ids, name, page, page_size): where = 'WHERE A.tenant_id in ({}) '.format(','.join( ['"' + x + '"' for x in tenant_ids])) if name: where += 'AND (A.group_name LIKE "{}%" OR C.service_cname LIKE "{}%") '.format( name, name) limit = "LIMIT {page}, {page_size}".format(page=page - 1, page_size=page_size) conn = BaseConnection() sql = """ SELECT A.ID, A.group_name, A.tenant_id, CONCAT('[', GROUP_CONCAT( CONCAT('{"service_cname":"',C.service_cname,'"'),',', CONCAT('"service_id":"',C.service_id,'"'),',', CONCAT('"service_key":"',C.service_key,'"'),',', CONCAT('"service_alias":"',C.service_alias),'"}') ,']') AS service_list FROM service_group A LEFT JOIN service_group_relation B ON A.ID = B.group_id AND A.tenant_id = B.tenant_id LEFT JOIN tenant_service C ON B.service_id = C.service_id AND B.tenant_id = C.tenant_id """ sql += where + "GROUP BY A.ID " sql += limit result = conn.query(sql) return result
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) conn = BaseConnection() result = conn.query(sql) return result
def get_app_count(self, tenant_ids, name): where = 'WHERE A.tenant_id in ({}) '.format(','.join( map(lambda x: '"' + x + '"', tenant_ids))) if name: where += ' AND (A.group_name LIKE "{}%" OR C.service_cname LIKE "{}%")'.format( name, name) conn = BaseConnection() sql = """ SELECT A.ID, A.group_name, A.tenant_id, CONCAT('[', GROUP_CONCAT( CONCAT('{"service_cname":"',C.service_cname,'"'),',', CONCAT('"service_id":"',C.service_id,'"'),',', CONCAT('"service_key":"',C.service_key,'"'),',', CONCAT('"service_alias":"',C.service_alias),'"}') ,']') AS service_list FROM service_group A LEFT JOIN service_group_relation B ON A.ID = B.group_id AND A.tenant_id = B.tenant_id LEFT JOIN tenant_service C ON B.service_id = C.service_id AND B.tenant_id = C.tenant_id """ sql += where + "GROUP BY A.ID " result = conn.query(sql) return result
def count_by_user_id(self, eid, user_id, query=""): where = """WHERE a.ID = b.tenant_id AND c.user_id = b.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}%" """.format( query=query) sql = """ SELECT count( * ) AS total FROM ( SELECT DISTINCT a.tenant_id AS tenant_id FROM tenant_info a, tenant_perms b, user_info c {where} ) as tmp""".format(where=where) conn = BaseConnection() result = conn.query(sql) return result[0].get("total")
def list_by_tenant_id(self, tenant_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}, {page_size}".format(page=page, page_size=page_size) 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 ri.*, ti.tenant_name FROM region_info ri, tenant_info ti, tenant_region tr {where} {limit} """.format(where=where, limit=limit) conn = BaseConnection() return conn.query(sql)
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, tsp.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
def list_appstore_infos(self, 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 = "" if query: where = "WHERE a.enterprise_alias LIKE '%{query}%' OR a.enterprise_name LIKE '%{query}%'".format(query=query) sql = """ SELECT a.enterprise_id, a.enterprise_name, a.enterprise_alias, b.access_url FROM tenant_enterprise a JOIN tenant_enterprise_token b ON a.id = b.enterprise_id {where} {limit} """.format( where=where, limit=limit) conn = BaseConnection() result = conn.query(sql) return result
def get_services_in_multi_apps_with_app_info(self, group_ids): ids = "{0}".format(",".join(str(group_id) for group_id in group_ids)) sql = """ select svc.*, sg.id as group_id, sg.group_name, sg.region_name, sg.is_default, sg.note from tenant_service svc left join service_group_relation sgr on svc.service_id = sgr.service_id left join service_group sg on sg.id = sgr.group_id where sg.id in ({ids}); """.format(ids=ids) conn = BaseConnection() return conn.query(sql)
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", "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() 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_app_list(self, tenant_pk, user, tenant_id, region, query=""): user_pk = user.pk services = [] def list_services(): q = Q(tenant_id=tenant_id, service_region=region) if query: q &= Q(service_cname__contains=query) return TenantServiceInfo.objects.filter(q) if user.is_sys_admin: services = list_services() else: perm = perms_repo.get_user_tenant_perm(tenant_pk, user_pk) if not perm: if tenant_pk == 5073: services = list_services().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 = list_services().order_by('service_alias') else: dsn = BaseConnection() add_sql = '' where = """ WHERE s.tenant_id = "{tenant_id}" AND sp.user_id = { user_id } AND sp.service_id = s.ID AND s.service_cname LIKE "%{query}%" AND s.service_region = "{region}" { add_sql }""".format( tenant_id=tenant_id, user_id=user_pk, region=region, query=query, add_sql=add_sql) query_sql = ''' SELECT s.* FROM tenant_service s, service_perms sp {where} ORDER BY s.service_alias'''.format(where=where) services = dsn.query(query_sql) return services
def count_appstore_infos(self, query=""): where = "" if query: where = "WHERE a.enterprise_alias LIKE '%{query}%' OR a.enterprise_name LIKE '%{query}%'".format(query=query) sql = """ SELECT count(*) as total FROM tenant_enterprise a JOIN tenant_enterprise_token b ON a.id = b.enterprise_id {where} """.format(where=where) conn = BaseConnection() result = conn.query(sql) return result[0]["total"]
def check_db_from_market_by_eid(self, eid): conn = BaseConnection() sql = """ SELECT service_alias FROM tenant_service a, tenant_info b WHERE a.tenant_id = b.tenant_id AND b.enterprise_id = "{eid}" AND a.service_source = "market" AND ( a.image LIKE "%mysql%" OR a.image LIKE "%postgres%" OR a.image LIKE "%mariadb%" ) LIMIT 1""".format(eid=eid) result = conn.query(sql) return True if len(result) > 0 else False
def check_image_svc_by_eid(self, eid): conn = BaseConnection() sql = """ SELECT service_alias FROM tenant_service a, tenant_info b WHERE a.tenant_id = b.tenant_id AND b.enterprise_id = "{eid}" AND a.create_status="complete" AND a.service_source IN ( "docker_image", "docker_compose", "docker_run" ) LIMIT 1""".format(eid=eid) result = conn.query(sql) return True if len(result) > 0 else False
def get_app_with_tags(self, eid, app_id): sql = """ select 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 = '{app_id}'; """.format(eid=eid, app_id=app_id) conn = BaseConnection() apps = conn.query(sql) return apps
def check_sourcecode_svc_by_eid(self, eid): conn = BaseConnection() sql = """ SELECT service_alias FROM tenant_service a, tenant_info b WHERE a.tenant_id = b.tenant_id AND a.service_region = b.region AND b.enterprise_id = "{eid}" AND a.service_source = "source_code" AND a.create_status = "complete" LIMIT 1""".format(eid=eid) result = conn.query(sql) return True if len(result) > 0 else False
def get_not_join_users(self, enterprise, tenant, query): where = """(SELECT DISTINCT user_id FROM tenant_perms WHERE tenant_id="{}" AND enterprise_id={})""".format( tenant.ID, enterprise.ID) sql = """ SELECT user_id, nick_name, enterprise_id, email FROM user_info WHERE user_id NOT IN {where} AND enterprise_id="{enterprise_id}" """.format(where=where, enterprise_id=enterprise.enterprise_id) if query: sql += """ AND nick_name like "%{query}%" """.format(query=query) conn = BaseConnection() 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_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 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_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 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"]