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"]
