Python db.SQL類代碼示例

def get_policies(sql_filter, sql_order, sql_limit):
    dbtp = get_db().table_prefix
    s = SQL(get_db())
    s.select_from('audit_policy', ['id', 'rank', 'name', 'desc', 'state'], alt_name='p')

    str_where = ''
    _where = list()

    if len(sql_filter) > 0:
        for k in sql_filter:
            if k == 'search':
                _where.append('(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.format(filter=sql_filter[k]))
            if k == 'state':
                _where.append('p.state={}'.format(sql_filter[k]))
            else:
                log.e('unknown filter field: {}\n'.format(k))
                return TPE_PARAM, s.total_count, 0, s.recorder

    if len(_where) > 0:
        str_where = '( {} )'.format(' AND '.join(_where))

    s.where(str_where)

    s.order_by('p.rank', True)

    if len(sql_limit) > 0:
        s.limit(sql_limit['page_index'], sql_limit['per_page'])

    err = s.query()
    return err, s.total_count, s.page_index, s.recorder

def set_password(handler, user_id, password):
    db = get_db()

    operator = handler.get_current_user()
    # print('----------', operator)

    # 1. get user info (user name)
    s = SQL(db)
    err = s.reset().select_from('user', ['username', 'surname']).where('user.id={}'.format(user_id)).query()
    if err != TPE_OK:
        return err
    if len(s.recorder) == 0:
        return TPE_NOT_EXISTS

    name = s.recorder[0]['username']
    surname = s.recorder[0]['surname']
    if len(surname) == 0:
        surname = name

    sql = 'UPDATE `{}user` SET password="{password}" WHERE id={user_id};' \
          ''.format(db.table_prefix, password=password, user_id=user_id)
    db_ret = db.exec(sql)
    if not db_ret:
        return TPE_DATABASE

    if operator['id'] == 0:
        syslog.sys_log({'username': name, 'surname': surname}, handler.request.remote_ip, TPE_OK,
                       "用戶 {} 通過郵件方式重置了密碼".format(name))
    else:
        syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "為用戶 {} 手動重置了密碼".format(name))

    return TPE_OK

def get_users_by_type(_type):
    s = SQL(get_db())
    err = s.select_from('user', ['id', 'type', 'ldap_dn'], alt_name='u').where('u.type={}'.format(_type)).query()
    if err != TPE_OK:
        return None
    if len(s.recorder) == 0:
        return None
    return s.recorder

def create_users(handler, user_list, success, failed):
    """
    批量創建用戶
    """
    db = get_db()
    _time_now = tp_timestamp_utc_now()

    operator = handler.get_current_user()
    name_list = list()

    s = SQL(db)

    for i in range(len(user_list)):
        user = user_list[i]
        if 'type' not in user:
            user['type'] = TP_USER_TYPE_LOCAL
        if 'ldap_dn' not in user:
            user['ldap_dn'] = ''

        err = s.reset().select_from('user', ['id']).where('user.username="{}"'.format(user['username'])).query()
        if err != TPE_OK:
            failed.append({'line': user['_line'], 'error': '數據庫查詢失敗'})
        if len(s.recorder) > 0:
            failed.append({'line': user['_line'], 'error': '賬號 `{}` 已經存在'.format(user['username'])})
            continue

        if user['type'] == TP_USER_TYPE_LOCAL:
            _password = tp_password_generate_secret(user['password'])
        else:
            _password = ''

        sql = 'INSERT INTO `{}user` (' \
              '`role_id`, `username`, `surname`, `type`, `ldap_dn`, `auth_type`, `password`, ' \
              '`state`, `email`, `creator_id`, `create_time`, `last_login`, `last_chpass`, `desc`' \
              ') VALUES (' \
              '0, "{username}", "{surname}", {user_type}, "{ldap_dn}", 0, "{password}", ' \
              '{state}, "{email}", {creator_id}, {create_time}, {last_login}, {last_chpass}, "{desc}");' \
              ''.format(db.table_prefix, username=user['username'], surname=user['surname'], user_type=user['type'],
                        ldap_dn=user['ldap_dn'], password=_password, state=TP_STATE_NORMAL, email=user['email'],
                        creator_id=operator['id'], create_time=_time_now, last_login=0, last_chpass=_time_now,
                        desc=user['desc'])
        db_ret = db.exec(sql)
        if not db_ret:
            failed.append({'line': user['_line'], 'error': '寫入數據庫時發生錯誤'})
            continue

        success.append(user['username'])
        name_list.append(user['username'])
        user['_id'] = db.last_insert_id()

    if len(name_list) > 0:
        syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "批量導入方式創建用戶：{}".format('，'.join(name_list)))
        # tp_stats().user_counter_change(len(name_list))

    # calc count of users.
    err, cnt = s.reset().count('user')
    if err == TPE_OK:
        tp_stats().user_counter_change(cnt)

def remove_members(handler, policy_id, policy_type, ids):
    s = SQL(get_db())

    auz_ids = [str(i) for i in ids]

    # 將用戶從所在組中移除
    where = 'policy_id={} AND type={} AND id IN ({})'.format(policy_id, policy_type, ','.join(auz_ids))
    err = s.reset().delete_from('ops_auz').where(where).exec()
    if err != TPE_OK:
        return err

    #return TPE_OK
    return policy.rebuild_ops_auz_map()

def create_user(handler, user):
    """
    創建一個用戶賬號
    """
    db = get_db()
    _time_now = tp_timestamp_utc_now()
    operator = handler.get_current_user()

    if 'type' not in user:
        user['type'] = TP_USER_TYPE_LOCAL
    if 'ldap_dn' not in user:
        user['ldap_dn'] = ''

    # 1. 判斷此賬號是否已經存在了
    s = SQL(db)
    err = s.reset().select_from('user', ['id']).where('user.username="{}"'.format(user['username'])).query()
    if err != TPE_OK:
        return err, 0
    if len(s.recorder) > 0:
        return TPE_EXISTS, 0

    # _password = tp_password_generate_secret(user['password'])
    if user['type'] == TP_USER_TYPE_LOCAL:
        _password = tp_password_generate_secret(user['password'])
    else:
        _password = ''

    sql = 'INSERT INTO `{}user` (' \
          '`role_id`, `username`, `surname`, `type`, `ldap_dn`, `auth_type`, `password`, `state`, ' \
          '`email`, `creator_id`, `create_time`, `last_login`, `last_chpass`, `desc`' \
          ') VALUES (' \
          '{role}, "{username}", "{surname}", {user_type}, "{ldap_dn}", {auth_type}, "{password}", {state}, ' \
          '"{email}", {creator_id}, {create_time}, {last_login}, {last_chpass}, "{desc}");' \
          ''.format(db.table_prefix, role=user['role'], username=user['username'], surname=user['surname'],
                    user_type=user['type'], ldap_dn=user['ldap_dn'], auth_type=user['auth_type'], password=_password,
                    state=TP_STATE_NORMAL, email=user['email'], creator_id=operator['id'], create_time=_time_now,
                    last_login=0, last_chpass=_time_now, desc=user['desc'])
    db_ret = db.exec(sql)
    if not db_ret:
        return TPE_DATABASE, 0

    _id = db.last_insert_id()

    syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "創建用戶：{}".format(user['username']))

    # calc count of users.
    err, cnt = s.reset().count('user')
    if err == TPE_OK:
        tp_stats().user_counter_change(cnt)

    return TPE_OK, _id

def get_auth(auth_id):
    db = get_db()
    s = SQL(db)
    err = s.select_from('ops_map', ['id', 'p_id', 'h_id', 'u_id', 'a_id']).where('ops_map.uni_id="{}"'.format(auth_id)).query()
    if err != TPE_OK:
        return None, err
    if len(s.recorder) == 0:
        return None, TPE_NOT_EXISTS

    # if len(s.recorder) != 1:
    #     return None, TPE_FAILED

    # log.v(s.recorder[0])
    return s.recorder[0], TPE_OK

def remove_users(handler, users):
    db = get_db()
    s = SQL(db)

    str_users = ','.join([str(i) for i in users])

    # 1. 獲取用戶名稱，用於記錄係統日誌
    where = 'u.id IN ({})'.format(str_users)
    err = s.select_from('user', ['username'], alt_name='u').where(where).query()
    if err != TPE_OK:
        return err
    if len(s.recorder) == 0:
        return TPE_NOT_EXISTS

    str_names = '，'.join([n['username'] for n in s.recorder])

    sql_list = []

    # 將用戶從所在組中移除
    sql = 'DELETE FROM `{tpdp}group_map` WHERE type={t} AND mid IN ({ids});' \
          ''.format(tpdp=db.table_prefix, t=TP_GROUP_USER, ids=str_users)
    sql_list.append(sql)
    # 刪除用戶
    sql = 'DELETE FROM `{tpdp}user` WHERE id IN ({ids});'.format(tpdp=db.table_prefix, ids=str_users)
    sql_list.append(sql)
    # 將用戶從運維授權中移除
    sql = 'DELETE FROM `{}ops_auz` WHERE rtype={rtype} AND rid IN ({ids});' \
          ''.format(db.table_prefix, rtype=TP_USER, ids=str_users)
    sql_list.append(sql)
    sql = 'DELETE FROM `{}ops_map` WHERE u_id IN ({ids});'.format(db.table_prefix, ids=str_users)
    sql_list.append(sql)
    # 將用戶從審計授權中移除
    sql = 'DELETE FROM `{}audit_auz` WHERE rtype={rtype} AND rid IN ({ids});' \
          ''.format(db.table_prefix, rtype=TP_USER, ids=str_users)
    sql_list.append(sql)
    sql = 'DELETE FROM `{}audit_map` WHERE u_id IN ({ids});'.format(db.table_prefix, ids=str_users)
    sql_list.append(sql)

    if not db.transaction(sql_list):
        return TPE_DATABASE

    syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "刪除用戶：{}".format(str_names))

    # calc count of users.
    err, cnt = s.reset().count('user')
    if err == TPE_OK:
        tp_stats().user_counter_change(cnt)

    return TPE_OK

def rank_reorder(handler, pid, new_rank, start_rank, end_rank, direct):
    db = get_db()

    # 調節順序：
    # 由pid獲取被移動的策略，得到其rank，即，p_rank
    #  p_rank > new_rank，向前移動
    #    所有 new_rank <= rank < p_rank 的條目，其rank+1
    #  p_rank < new_rank，向後移動
    #    所有 new_rank >= rank > p_rank 的條目，其rank-1
    # 最後令pid條目的rank為new_rank

    # 1. 判斷此賬號是否已經存在
    s = SQL(db)
    err = s.select_from('ops_policy', ['id', 'name', 'rank']).where('ops_policy.id={}'.format(pid)).query()
    if err != TPE_OK:
        return err
    if len(s.recorder) == 0:
        return TPE_NOT_EXISTS

    p_name = s.recorder[0]['name']
    p_rank = s.recorder[0]['rank']

    # if p_rank > new_rank:
    #     compare = '>'
    #     if insert_before:
    #         compare = '>='
    #     sql = 'UPDATE `{dbtp}ops_policy` SET rank=rank+1 WHERE (rank{compare}{new_rank} AND rank<{p_rank});' \
    #           ''.format(dbtp=db.table_prefix, compare=compare, new_rank=new_rank, p_rank=p_rank)
    # else:
    #     compare = '<'
    #     if insert_before:
    #         compare = '<='
    #     sql = 'UPDATE `{dbtp}ops_policy` SET rank=rank-1 WHERE (rank{compare}{new_rank} AND rank>{p_rank});' \
    #           ''.format(dbtp=db.table_prefix, compare=compare, new_rank=new_rank, p_rank=p_rank)
    sql = 'UPDATE `{dbtp}ops_policy` SET rank=rank{direct} WHERE (rank>={start_rank} AND rank<={end_rank});' \
          ''.format(dbtp=db.table_prefix, direct=direct, start_rank=start_rank, end_rank=end_rank)
    db_ret = db.exec(sql)
    if not db_ret:
        return TPE_DATABASE

    sql = 'UPDATE `{dbtp}ops_policy` SET rank={new_rank} WHERE id={pid};' \
          ''.format(dbtp=db.table_prefix, new_rank=new_rank, pid=pid)
    db_ret = db.exec(sql)
    if not db_ret:
        return TPE_DATABASE

    syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "調整運維授權策略順序：{}，從{}到{}".format(p_name, p_rank, new_rank))

    return policy.rebuild_ops_auz_map()

def get_host_accounts(host_id):
    # 獲取指定主機的所有賬號
    s = SQL(get_db())
    # s.select_from('acc', ['id', 'state', 'host_ip', 'router_ip', 'router_port', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'pri_key'], alt_name='a')
    s.select_from('acc', ['id', 'state', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'username_prompt', 'password_prompt'], alt_name='a')

    s.where('a.host_id={}'.format(host_id))
    s.order_by('a.username', True)

    err = s.query()
    return err, s.recorder

def generate_reset_password_token(handler, user_id):
    db = get_db()
    operator = handler.get_current_user()
    s = SQL(db)
    _time_now = tp_timestamp_utc_now()

    # 0. query user's email by user_id
    err = s.select_from('user', ['email'], alt_name='u').where('u.id={user_id}'.format(user_id=user_id)).query()
    if err != TPE_OK:
        return err, None, None
    if len(s.recorder) == 0:
        return TPE_DATABASE, None, None

    email = s.recorder[0].email

    # 1. clean all timed out tokens.
    s.reset().delete_from('user_rpt').where('create_time<{}'.format(_time_now - 24 * 60 * 60)).exec()

    # 2. find out if this user already have a token.
    err = s.reset().select_from('user_rpt', ['id'], alt_name='u').where('u.user_id={}'.format(user_id)).query()
    if err != TPE_OK:
        return err, None, None

    token = tp_generate_random(16)

    if len(s.recorder) == 0:
        sql = 'INSERT INTO `{dbtp}user_rpt` (user_id, token, create_time) VALUES ' \
              '({user_id}, "{token}", {create_time});' \
              ''.format(dbtp=db.table_prefix, user_id=user_id, token=token, create_time=_time_now)
        db_ret = db.exec(sql)
        if not db_ret:
            return TPE_DATABASE, None, None
    else:
        sql = 'UPDATE `{dbtp}user_rpt` SET token="{token}", create_time={create_time} WHERE user_id={user_id};' \
              ''.format(dbtp=db.table_prefix, token=token, create_time=_time_now, user_id=user_id)
        db_ret = db.exec(sql)
        if not db_ret:
            return TPE_DATABASE, None, None

    # syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "為用戶 {} 手動重置了密碼".format(name))

    return TPE_OK, email, token

def set_flags(self, policy_id, flag_record, flag_rdp, flag_ssh):
    db = get_db()

    # 1. 判斷此賬號是否已經存在
    s = SQL(db)
    err = s.select_from('ops_policy', ['id']).where('ops_policy.id={}'.format(policy_id)).query()
    if err != TPE_OK:
        return err
    if len(s.recorder) == 0:
        return TPE_NOT_EXISTS

    sql = 'UPDATE `{}ops_policy` SET flag_record={flag_record}, flag_rdp={flag_rdp}, flag_ssh={flag_ssh} WHERE id={p_id};' \
          ''.format(db.table_prefix,
                    flag_record=flag_record, flag_rdp=flag_rdp, flag_ssh=flag_ssh, p_id=policy_id
                    )
    db_ret = db.exec(sql)
    if not db_ret:
        return TPE_DATABASE

    return TPE_OK

def update_policy(handler, args):
    db = get_db()

    # 1. 判斷此賬號是否已經存在
    s = SQL(db)
    err = s.reset().select_from('ops_policy', ['id']).where('ops_policy.id={}'.format(args['id'])).query()
    if err != TPE_OK:
        return err
    if len(s.recorder) == 0:
        return TPE_NOT_EXISTS

    sql = 'UPDATE `{}ops_policy` SET `name`="{name}", `desc`="{desc}" WHERE `id`={p_id};' \
          ''.format(db.table_prefix,
                    name=args['name'], desc=args['desc'], p_id=args['id']
                    )
    db_ret = db.exec(sql)
    if not db_ret:
        return TPE_DATABASE

    return TPE_OK

def get_logs(sql_filter, sql_order, sql_limit):
    s = SQL(get_db())
    s.select_from('syslog', ['id', 'user_name', 'user_surname', 'client_ip', 'code', 'log_time', 'message'], alt_name='l')

    str_where = ''
    _where = list()

    if len(sql_filter) > 0:
        for k in sql_filter:
            if k == 'log_user_name':
                _where.append('l.user_name="{}"'.format(sql_filter[k]))
            # elif k == 'search_record':
            #     _where.append('(h.name LIKE "%{}%" OR h.ip LIKE "%{}%" OR h.router_addr LIKE "%{}%" OR h.desc LIKE "%{}%" OR h.cid LIKE "%{}%")'.format(sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k]))

    if len(_where) > 0:
        str_where = '( {} )'.format(' AND '.join(_where))

    s.where(str_where)

    if sql_order is not None:
        _sort = False if not sql_order['asc'] else True
        if 'log_time' == sql_order['name']:
            s.order_by('l.log_time', _sort)
        # elif 'name' == sql_order['name']:
        #     s.order_by('h.name', _sort)
        # elif 'os_type' == sql_order['name']:
        #     s.order_by('h.os_type', _sort)
        # elif 'cid' == sql_order['name']:
        #     s.order_by('h.cid', _sort)
        # elif 'state' == sql_order['name']:
        #     s.order_by('h.state', _sort)
        else:
            log.e('unknown order field: {}\n'.format(sql_order['name']))
            return TPE_PARAM, s.total_count, s.recorder

    if len(sql_limit) > 0:
        s.limit(sql_limit['page_index'], sql_limit['per_page'])

    err = s.query()
    return err, s.total_count, s.recorder

def get_list(gtype):
    s = SQL(get_db())
    s.select_from('group', ['id', 'name'], alt_name='g')
    s.where('g.type={}'.format(gtype))

    err = s.query()
    return err, s.recorder