#coding: utf-8
# +-------------------------------------------------------------------
# | 宝塔Windows面板
# +-------------------------------------------------------------------
# | Copyright (c) 2015-2016 宝塔软件(https://www.bt.cn) All rights reserved.
# +-------------------------------------------------------------------
# | Author: 沐落 <cjx@bt.cn>
# +-------------------------------------------------------------------

#------------------------------
# 数据库管理类
#------------------------------
import public,db,re,time,os,sys,panelMysql,panelMssql,json

try:
    from BTPanel import session
except :pass

import datatool
class database(datatool.datatools):
    sid = 0
    _setup_path = None
    _version = None
    def __init__(self):


        if public.get_server_status("mysql") >=0 :
            self._version = re.search('([MySQL|MariaDB-]+\d+\.\d+)',public.get_server_path('mysql')).groups()[0]
            self._setup_path  = public.GetConfigValue('setup_path') + '/mysql/' + self._version


    def AddCloudServer(self,get):
        '''
            @name 添加远程服务器
            @author hwliang<2021-01-10>
            @param db_host<string> 服务器地址
            @param db_port<port> 数据库端口
            @param db_user<string> 用户名
            @param db_password<string> 数据库密码
            @param db_ps<string> 数据库备注
            @return dict
        '''
        if not hasattr(get,'db_host'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        if not hasattr(get,'db_port'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        if not hasattr(get,'db_user'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        if not hasattr(get,'db_password'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        if not hasattr(get,'db_ps'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        get.db_name = None
        res = self.CheckCloudDatabase(get)
        if isinstance(res,dict): return res
        if public.M('database_servers').where('db_host=? AND db_port=?',(get.db_host,get.db_port)).count():
            return public.returnMsg(False,'指定服务器已存在: [{}:{}]'.format(get.db_host,get.db_port))
        get.db_port = int(get.db_port)
        pdata = {
            'db_host':get.db_host,
            'db_port':get.db_port,
            'db_user':get.db_user,
            'db_password':get.db_password,
            'ps': public.xssencode2(get.db_ps.strip()),
            'addtime': int(time.time())
        }

        result = public.M("database_servers").insert(pdata)

        if isinstance(result,int):
            public.WriteLog('数据库管理','添加远程MySQL服务器[{}:{}]'.format(get.db_host,get.db_port))
            return public.returnMsg(True,'添加成功!')
        return public.returnMsg(False,'添加失败： {}'.format(result))

    def GetCloudServer(self,get):
        '''
            @name 获取远程服务器列表
            @author hwliang<2021-01-10>
            @return list
        '''
        data = public.M('database_servers').select()
        bt_mysql_bin = public.get_mysql_info()['path'] + '/bin/mysql.exe'

        if not isinstance(data,list): data = []
        if os.path.exists(bt_mysql_bin):
            data.insert(0,{'id':0,'db_host':'127.0.0.1','db_port':3306,'db_user':'root','db_password':'','ps':'本地服务器','addtime':0})
        return data


    def RemoveCloudServer(self,get):
        '''
            @name 删除远程服务器
            @author hwliang<2021-01-10>
            @param id<int> 远程服务器ID
            @return dict
        '''

        id = int(get.id)
        if not id: return public.returnMsg(False,'参数传递错误，请重试!')
        db_find = public.M('database_servers').where('id=?',(id,)).find()
        if not db_find: return public.returnMsg(False,'指定远程服务器不存在!')
        public.M('databases').where('sid=?',id).delete()
        result = public.M('database_servers').where('id=?',id).delete()
        if isinstance(result,int):
            public.WriteLog('数据库管理','删除远程MySQL服务器[{}:{}]'.format(db_find['db_host'],int(db_find['db_port'])))
            return public.returnMsg(True,'删除成功!')
        return public.returnMsg(False,'删除失败： {}'.format(result))


    def ModifyCloudServer(self,get):
        '''
            @name 修改远程服务器
            @author hwliang<2021-01-10>
            @param id<int> 远程服务器ID
            @param db_host<string> 服务器地址
            @param db_port<port> 数据库端口
            @param db_user<string> 用户名
            @param db_password<string> 数据库密码
            @param db_ps<string> 数据库备注
            @return dict
        '''
        if not hasattr(get,'id'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        if not hasattr(get,'db_host'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        if not hasattr(get,'db_port'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        if not hasattr(get,'db_user'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        if not hasattr(get,'db_password'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        if not hasattr(get,'db_ps'):
            return public.returnMsg(False,'参数传递错误，请重试!')

        id = int(get.id)
        get.db_port = int(get.db_port)
        db_find = public.M('database_servers').where('id=?',(id,)).find()
        if not db_find: return public.returnMsg(False,'指定远程服务器不存在!')
        _modify = False
        if db_find['db_host'] != get.db_host or db_find['db_port'] != get.db_port:
            _modify = True
            if public.M('database_servers').where('db_host=? AND db_port=?',(get.db_host,get.db_port)).count():
                return public.returnMsg(False,'指定服务器已存在: [{}:{}]'.format(get.db_host,get.db_port))

        if db_find['db_user'] != get.db_user or db_find['db_password'] != get.db_password:
            _modify = True

        if _modify:
            res = self.CheckCloudDatabase(get)
            if isinstance(res,dict): return res

        pdata = {
            'db_host':get.db_host,
            'db_port':get.db_port,
            'db_user':get.db_user,
            'db_password':get.db_password,
            'ps': public.xssencode2(get.db_ps.strip())
        }

        result = public.M("database_servers").where('id=?',(id,)).update(pdata)
        if isinstance(result,int):
            public.WriteLog('数据库管理','修改远程MySQL服务器[{}:{}]'.format(get.db_host,get.db_port))
            return public.returnMsg(True,'修改成功!')
        return public.returnMsg(False,'修改失败： {}'.format(result))


    def AddCloudDatabase(self,get):
        '''
            @name 添加远程数据库
            @author hwliang<2022-01-06>
            @param db_host<string> 服务器地址
            @param db_port<port> 数据库端口
            @param db_user<string> 用户名
            @param db_name<string> 数据库名称
            @param db_password<string> 数据库密码
            @param db_ps<string> 数据库备注
            @return dict
        '''
        if not hasattr(get,'db_host'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        if not hasattr(get,'db_port'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        if not hasattr(get,'db_user'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        if not hasattr(get,'db_name'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        if not hasattr(get,'db_password'):
            return public.returnMsg(False,'参数传递错误，请重试!')
        if not hasattr(get,'db_ps'):
            return public.returnMsg(False,'参数传递错误，请重试!')

        #检查数据库是否能连接
        res = self.CheckCloudDatabase(get)
        if isinstance(res,dict): return res

        if public.M('databases').where('name=?',(get.db_name,)).count():
            return public.returnMsg(False,'已存在同名的数据库: ['+get.db_name+']')
        get.db_port = int(get.db_port)
        conn_config = {
            'db_host':get.db_host,
            'db_port':get.db_port,
            'db_user':get.db_user,
            'db_password':get.db_password,
            'db_name':get.db_name
        }

        pdata = {
            'name': get.db_name,
            'ps': get.db_ps,
            'conn_config': json.dumps(conn_config),
            'db_type': '1',
            'username': get.db_user,
            'password': get.db_password,
            'accept': '127.0.0.1',
            'addtime': time.strftime('%Y-%m-%d %X',time.localtime()),
            'pid': 0
        }

        result = public.M('databases').insert(pdata)
        if isinstance(result,int):
            public.WriteLog('数据库管理','添加远程MySQL数据库[%s]成功' % (get.db_name))
            return public.returnMsg(True,'添加成功!')
        return public.returnMsg(False,'添加失败： {}'.format(result))


    def CheckCloudDatabase(self,conn_config):
        '''
            @name 检查远程数据库信息是否正确
            @author hwliang<2022-01-06>
            @param conn_config<dict> 连接信息
                db_host<string> 服务器地址
                db_port<port> 数据库端口
                db_user<string> 用户名
                db_name<string> 数据库名称
                db_password<string> 数据库密码
            @return True / dict
        '''
        try:
            import db_mysql
            if not 'db_name' in conn_config: conn_config['db_name'] = None
            mysql_obj = db_mysql.panelMysql().set_host(conn_config['db_host'],conn_config['db_port'],conn_config['db_name'],conn_config['db_user'],conn_config['db_password'])
            result = mysql_obj.query("show databases")
            if not conn_config['db_name']: return True
            for i in result:
                if i[0] == conn_config['db_name']:
                    return True
            return public.returnMsg(False,'指定数据库不存在!')
        except Exception as ex:
            res  = self.GetMySQLError(ex)
            if not res: res = str(ex)
            return public.returnMsg(False,res)

    def GetMySQLError(self,e):
        res = ''
        if e.args[0] == 1045:
            res = '用户名或密码错误!'
        if e.args[0] == 1049:
            res = '数据库不存在!'
        if e.args[0] == 1044:
            res = '没有指定数据库的访问权限，或指定数据库不存在!'
        if e.args[0] == 1062:
            res = '数据库已存在!'
        if e.args[0] == 1146:
            res = '数据表不存在!'
        if e.args[0] == 2003:
            res = '数据库服务器连接失败!'
        if res:
            res = res + "<pre>" + str(e) + "</pre>"
        else:
            res = str(e)
        return res

    def get_mysql_conf(self):
        if public.get_server_status("mysql") >=0 :
            data = {}
            data['version'] = re.search('([MySQL|MariaDB-]+\d+\.\d+)',public.get_server_path('mysql')).groups()[0]
            data['path']  = public.GetConfigValue('setup_path') + '/mysql/' + self._version
            return data
        return False

    #检测是否6.x安装的服务
    def check_server(self):
         if public.get_server_status("mysql") < 0 or not os.path.exists(self._setup_path + '/my.ini')  :
             return False
         return True

    #添加数据库
    def AddDatabase(self,get):

        data_name = get['name'].strip().lower()
        if self.CheckRecycleBin(data_name): return public.returnMsg(False,'数据库['+data_name+']已在回收站，请从回收站恢复!');
        if len(data_name) > 16: return public.returnMsg(False, 'DATABASE_NAME_LEN')

        if not hasattr(get,'db_user'): get.db_user = data_name;
        username = get.db_user.strip();
        checks = ['root','mysql','test','sys','panel_logs']
        if username in checks or len(username) < 1: return public.returnMsg(False,'数据库用户名不合法!');
        if data_name in checks or len(data_name) < 1: return public.returnMsg(False,'数据库名称不合法!');
        reg = "^\w+$"
        if not re.match(reg, data_name): return public.returnMsg(False,'DATABASE_NAME_ERR_T')

        data_pwd = get['password']
        if len(data_pwd) < 1:
            data_pwd = public.md5(str(time.time()))[0:8]

        if public.M('databases').where("name=? or username=?",(data_name,username)).count(): return public.returnMsg(False,'DATABASE_NAME_EXISTS')
        address = get['address'].strip()
        user = '是'
        password = data_pwd

        codeing = get['codeing']
        wheres={
                'utf8'      :   'utf8_general_ci',
                'utf8mb4'   :   'utf8mb4_general_ci',
                'gbk'       :   'gbk_chinese_ci',
                'big5'      :   'big5_chinese_ci'
                }
        codeStr=wheres[codeing]

        dtype = 'MySQL'
        if hasattr(get,'dtype') and get['dtype'] == 'SQLServer':

            if re.match("^\d+",data_name): return public.returnMsg(False,'SQLServer数据库不能以数字开头!')

            reg_count = 0
            regs = ['[a-z]','[A-Z]','\W','[0-9]']
            for x in regs:
                if re.search(x,password): reg_count += 1

            if len(password) < 8 or len(password) >128 or reg_count < 3 :
                return public.returnMsg(False,'SQLServer密码复杂性策略不匹配，应为8-128位，并包含大写，小写，数字，特殊符号其中任何3项!')

            dtype = get['dtype']
            #添加SQLServer
            mssql_obj = panelMssql.panelMssql()
            result = mssql_obj.execute("CREATE DATABASE %s" % data_name)
            isError = self.IsSqlError(result)
            if  isError != None: return isError
            mssql_obj.execute("DROP LOGIN %s" % username)
        else:

            try:
                self.sid = int(get['sid'])
            except :
                self.sid = 0

            mysql_obj = public.get_mysql_obj_by_sid(self.sid)
            #从MySQL验证是否存在
            if self.database_exists_for_mysql(mysql_obj,data_name):
               return public.returnMsg(False,'指定数据库已在MySQL中存在，请换个名称!')

            if re.search('[\u4e00-\u9fa5]',data_name):
                return public.returnMsg(False,'数据库名称不能为中文，请换个名称!')

            for a in address.split(','):
                if a != '%' and not public.check_ip(a): return public.returnMsg(False,'访问权限设置不正确，应为%或指定ip.')

            result = mysql_obj.execute("create database `" + data_name + "` DEFAULT CHARACTER SET " + codeing + " COLLATE " + codeStr)

            isError = self.IsSqlError(result)
            if  isError != None: return isError
            mysql_obj.execute("drop user '" + username + "'@'localhost'")
            for a in address.split(','):
                mysql_obj.execute("drop user '" + username + "'@'" + a + "'")
        #添加用户
        self.__CreateUsers(dtype,data_name,username,password,address)

        if not hasattr(get,'ps'): get['ps'] = public.getMsg('INPUT_PS');
        addTime = time.strftime('%Y-%m-%d %X',time.localtime())

        pid = 0
        if hasattr(get,'pid'): pid = get.pid

        if hasattr(get,'contact'):
            site = public.M('sites').where("id=?",(get.contact,)).field('id,name').find()
            if site:
                pid = int(get.contact)
                get['ps'] = site['name']

        db_type = 0
        if self.sid: db_type = 2
        #添加入SQLITE
        public.M('databases').add('pid,sid,db_type,name,username,password,accept,ps,addtime,type',(pid,self.sid,db_type,data_name,username,password,address,get['ps'],addTime,dtype))
        public.WriteLog("TYPE_DATABASE", 'DATABASE_ADD_SUCCESS',(data_name,))
        return public.returnMsg(True,'ADD_SUCCESS')

    #判断数据库是否存在—从MySQL
    def database_exists_for_mysql(self,mysql_obj,dataName):
        databases_tmp = self.map_to_list(mysql_obj.query('show databases'))
        if not isinstance(databases_tmp,list):
            return True

        for i in databases_tmp:
            if i[0] == dataName:
                return True
        return False


    #本地创建数据库
    def __CreateUsers(self,type,data_name,username,password,address):
        if type == 'SQLServer':
            #添加SQLServer
            mssql_obj = panelMssql.panelMssql()
            mssql_obj.execute("use %s create login %s with password ='%s' , default_database = %s" % (data_name,username,password,data_name))
            mssql_obj.execute("use %s create user %s for login %s with default_schema=dbo" % (data_name,username,username))
            mssql_obj.execute("use %s exec sp_addrolemember 'db_owner','%s'" % (data_name,data_name))
            mssql_obj.execute("ALTER DATABASE %s SET MULTI_USER" % data_name)
        else:
            mysql_obj = public.get_mysql_obj_by_sid(self.sid)

            mysql_obj.execute("CREATE USER `%s`@`localhost` IDENTIFIED BY '%s'" % (username,password))
            mysql_obj.execute("grant all privileges on `%s`.* to `%s`@`localhost`" % (data_name,username))
            for a in address.split(','):
                if a:
                    mysql_obj.execute("CREATE USER `%s`@`%s` IDENTIFIED BY '%s'" % (username,a,password))
                    mysql_obj.execute("grant all privileges on `%s`.* to `%s`@`%s`" % (data_name,username,a))
            mysql_obj.execute("flush privileges")

    #检查是否在回收站
    def CheckRecycleBin(self,name):
        try:
            for n in os.listdir('{}/Recycle_bin'.format(public.get_soft_path())):
                if n.find('BTDB_'+name+'_t_') != -1: return True;
            return False;
        except:
            return False;

    #检测数据库执行错误
    def IsSqlError(self,mysqlMsg):
        if mysqlMsg:
            mysqlMsg = str(mysqlMsg)
            if "MySQLdb" in mysqlMsg: return public.returnMsg(False,'DATABASE_ERR_MYSQLDB')
            if "2002," in mysqlMsg: return public.returnMsg(False,'DATABASE_ERR_CONNECT')
            if "2003," in mysqlMsg: return public.returnMsg(False,'数据库连接超时，请检查配置是否正确.')
            if "1045," in mysqlMsg: return public.returnMsg(False,'MySQL密码错误.')
            if "1040," in mysqlMsg: return public.returnMsg(False,'超过最大连接数，请稍后重试.')
            if "1130," in mysqlMsg: return public.returnMsg(False,'数据库连接失败，请检查root用户是否授权127.0.0.1访问权限.')
            if "using password:" in mysqlMsg: return public.returnMsg(False,'DATABASE_ERR_PASS')
            if "Connection refused" in mysqlMsg: return public.returnMsg(False,'DATABASE_ERR_CONNECT')
            if "1133" in mysqlMsg: return public.returnMsg(False,'DATABASE_ERR_NOT_EXISTS')
            if "2005_login_error" == mysqlMsg: return public.returnMsg(False,'连接超时,请手动开启TCP/IP功能(开始菜单->SQL 2005->配置工具->2005网络配置->TCP/IP->启用)')
            if "DB-Lib error message 20018" in mysqlMsg: return public.returnMsg(False,'创建失败，SQL Server需要GUI支持，请通过远程桌面连接连接上SQL Server管理工具，依次找到安全性 -> 登录名 -> NT AUTHORITY\SYSTEM -> 属性 -> 服务器角色 -> 勾选列表中的sysadmin后重启SQL Server服务，重新操作添加数据库。<a style="color:red" href="https://www.bt.cn/bbs/thread-50555-1-1.html">帮助</a>')

        return None

    #删除数据库
    def DeleteDatabase(self,get):

        id=get['id']
        name = get['name']

        find = public.M('databases').where("id=?",(id,)).field('id,pid,name,username,password,type,accept,ps,addtime,sid,db_type').find();
        accept = find['accept'];
        username = find['username'];
        if find['type'] == 'SQLServer':
            mssql_obj = panelMssql.panelMssql()

            mssql_obj.execute("ALTER DATABASE %s SET SINGLE_USER with ROLLBACK IMMEDIATE" % name)
            result = mssql_obj.execute("DROP DATABASE %s" % name)
            isError=self.IsSqlError(result)
            if  isError != None: return isError
            mssql_obj.execute("DROP LOGIN %s" % username)

        else:
            self.sid = find['sid']
            if find['db_type'] in ['0',0] or self.sid: # 删除本地数据库
                if not self.check_server():
                    return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')

                if not self.sid:
                    if not self.DeleteToRecycleBin(name):
                        return public.returnMsg(False,'数据库移动到回收站失败.')

                #删除MYSQL
                mysql_obj = public.get_mysql_obj_by_sid(self.sid)
                result = mysql_obj.execute("drop database `" + name + "`")
                isError=self.IsSqlError(result)
                if  isError != None: return isError
                try:
                    users = mysql_obj.query("select Host from mysql.user where User='" + username + "' AND Host!='localhost'")
                    mysql_obj.execute("drop user '" + username + "'@'localhost'")
                    for us in users:
                        mysql_obj.execute("drop user '" + username + "'@'" + us[0] + "'")
                except :pass
                mysql_obj.execute("flush privileges")
        #删除SQLITE
        public.M('databases').where("id=?",(id,)).delete()
        public.WriteLog("TYPE_DATABASE", 'DATABASE_DEL_SUCCESS',(name,))
        return public.returnMsg(True, 'DEL_SUCCESS')


    def db_name_to_unicode(self,name):
        '''
            @name 中文数据库名转换为Unicode编码
            @author hwliang<2021-12-20>
            @param name<string> 数据库名
            @return name<string> Unicode编码的数据库名
        '''
        name = name.replace('.','@002e')
        return name.encode("unicode_escape").replace(b"\\u",b"@").decode()


    #删除数据库到回收站
    def DeleteToRecycleBin(self,name):
        backup_name = name + '_backup'

        if not os.path.exists('data/recycle_bin_db.pl'):
            return True

        srcPath = '{}/data/{}'.format(self._setup_path,self.db_name_to_unicode(name))
        print(srcPath)
        if not os.path.exists(srcPath):
            return True

        dstPath = srcPath.replace(self.db_name_to_unicode(name),backup_name)
        try:
            os.rename(srcPath,dstPath)
        except :
            try:
                public.move(srcPath,dstPath)
            except :
                public.copytree(srcPath,dstPath)

        if not os.path.exists(srcPath):
            os.makedirs(srcPath)

        if os.path.exists(dstPath):

            import panelTask,json
            task_obj = panelTask.bt_task()
            exec_shell = public.to_path(public.get_run_python('[PYTHON] {}/script/backup.py db_recyclebin {}'.format(public.get_panel_path(),name)))
            task_obj.create_task('删除数据库[{}]到数据库回收站'.format(name), 0, exec_shell)
            data = public.M('databases').where("name=?",(name,)).field('id,pid,name,username,password,type,accept,ps,addtime').find();

            data['codeing'] = public.get_database_character(name)
            spath = 'data/recycle_bin_db'
            if not os.path.exists(spath): os.makedirs(spath)
            public.writeFile('{}/{}'.format(spath,data['name']),json.dumps(data))

            return True
        return False

    #永久删除数据库
    def DeleteTo(self,filename):
        import json
        try:

            name = os.path.basename(filename).split('_t_')[0].replace('BTDB_','')
            os.remove(filename)
            spath = 'data/recycle_bin_db/{}'.format(name)
            data = json.loads(public.readFile(spath))
            os.remove(spath)
        except :pass

        public.WriteLog("TYPE_DATABASE", 'DATABASE_DEL_SUCCESS',(name,))
        return public.returnMsg(True,'DEL_SUCCESS');

    #恢复数据库
    def RecycleDB(self,filename):
        if not os.path.exists(filename):
            return public.returnMsg(False,'恢复数据库失败，指定备份文件不存在.');

        name = os.path.basename(filename).split('_t_')[0].replace('BTDB_','');
        if public.M('databases').where("name=?",(name,)).count():
            return public.returnMsg(True,'已存在同名数据库.')

        spath = 'data/recycle_bin_db/{}'.format(name)
        data = json.loads(public.readFile(spath))

        mysql_obj = panelMysql.panelMysql()
        result = mysql_obj.execute("create database `" + name + "` DEFAULT CHARACTER SET " + data['codeing'] + " COLLATE " + public.get_database_codestr(data['codeing']))
        isError = self.IsSqlError(result)
        if  isError != None: return isError

        self.__CreateUsers(data['type'],data['name'],data['username'],data['password'],data['accept'])
        public.M('databases').add('id,pid,name,username,password,accept,ps,addtime,type',(data['id'],data['pid'],data['name'],data['username'],data['password'],data['accept'],data['ps'],data['addtime'],data['type']))

        root = public.M('config').where('id=?',(1,)).getField('mysql_root');
        shell = public.to_path(self._setup_path + "/bin/mysql.exe -uroot -p" + root + " --force --default-character-set=utf8 " + name + " < " + filename)
        public.ExecShell(shell)
        os.remove(filename)
        public.WriteLog("TYPE_DATABASE", '从数据库回收站恢复【{}】成功.'.format(name))
        return public.returnMsg(True,"RECYCLEDB")



    #获取sa密码
    def GetSaPass(self,get):

        mssql_obj = panelMssql.panelMssql()
        ret = mssql_obj.get_sql_name()
        if not ret : return public.returnMsg(False, 'SQL Server未安装或者未启动，请先安装或启动')

        sa_path = 'data/sa.pl'
        if os.path.exists(sa_path):
            password = public.readFile(sa_path)
            return public.returnMsg(True,password)
        return public.returnMsg(True,'')

    #设置sa密码
    def SetSaPassword(self,get):
        password = get['password'].strip()
        rep = "^[\w@\.]+$"

        if not re.match(rep, password): return public.returnMsg(False, 'DATABASE_NAME_ERR_T')
        mssql_obj = panelMssql.panelMssql()
        result = mssql_obj.execute("EXEC sp_password NULL, '%s', 'sa'" % password)
        isError = self.IsSqlError(result)
        if  isError != None: return isError

        public.writeFile('data/sa.pl',password)
        msg = '修改sa密码成功！';
        session['config']['mssql_sa'] = password
        return public.returnMsg(True,msg)

    #设置ROOT密码
    def SetupPassword(self,get):
        if not hasattr(get, 'password'):return public.returnMsg(False, '修改失败,参数传递错误.')

        password = get['password'].strip()
        if not self.check_server(): return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')
        if not password:  return public.returnMsg(False, '修改失败，root密码不能为空.')



        rep = "^[\w@\.]+$"
        if not re.match(rep, password): return public.returnMsg(False, 'root密码不能带有特殊符号!')
        mysql_root = public.M('config').where("id=?",(1,)).getField('mysql_root')
        #修改MYSQL
        mysql_obj = panelMysql.panelMysql()
        result = mysql_obj.query("show databases")
        isError=self.IsSqlError(result)

        is_modify = True
        if  isError != None:
            #尝试使用新密码
            public.M('config').where("id=?",(1,)).setField('mysql_root',password)
            result = mysql_obj.query("show databases")
            isError=self.IsSqlError(result)
            if  isError != None:
                setup_path = public.GetConfigValue('setup_path')
                shell = public.get_run_python("%s && cd %s/panel && [PYTHON] tools.py root %s" % (setup_path[0:2],setup_path,password));
                os.system(shell)
                is_modify = False
        if is_modify:
            if self._version.find('5.7') >= 0  or self._version.find('8.0') >= 0:
                panelMysql.panelMysql().execute("UPDATE mysql.user SET authentication_string='' WHERE user='root'")
                panelMysql.panelMysql().execute("ALTER USER 'root'@'localhost' IDENTIFIED BY '%s'" % password)
                panelMysql.panelMysql().execute("ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY '%s'" % password)
            else:
                result = mysql_obj.execute("update mysql.user set Password=password('" + password + "') where User='root'")
            mysql_obj.execute("flush privileges")

        msg = public.getMsg('DATABASE_ROOT_SUCCESS');
        #修改SQLITE
        public.M('config').where("id=?",(1,)).setField('mysql_root',password)
        public.WriteLog("TYPE_DATABASE", "DATABASE_ROOT_SUCCESS")
        session['config']['mysql_root']= password
        return public.returnMsg(True,msg)


    #修改用户密码
    def ResDatabasePassword(self,get):

        newpassword = get['password']
        username = get['name']
        id = get['id']

        if not newpassword: return public.returnMsg(False, '修改失败，数据库[' + username + ']密码不能为空.');

        find = public.M('databases').where("id=?",(id,)).field('id,pid,name,username,password,type,accept,ps,addtime,sid').find();
        name = find['name']

        rep = "^[\w@\.]+$"
        try:
            if len(re.search(rep, newpassword).groups()) > 0:
                return public.returnMsg(False, 'DATABASE_NAME_ERR_T')
        except :
            return public.returnMsg(False, 'DATABASE_NAME_ERR_T')


        if find['type'] == 'SQLServer':
                mssql_obj = panelMssql.panelMssql()
                mssql_obj.execute("EXEC sp_password NULL, '%s', '%s'" % (newpassword,username))
        else:
            if not self.check_server(): return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')
            #修改MYSQL

            self.sid = find['sid']
            if self.sid and username == 'root': return public.returnMsg(False,'不能修改远程数据库的root密码')
            mysql_obj = public.get_mysql_obj_by_sid(self.sid)

            m_version = public.readFile(self._setup_path + '/version.pl')

            if m_version.find('5.7') == 0  or m_version.find('8.0') == 0:
                accept = self.map_to_list(panelMysql.panelMysql().query("select Host from mysql.user where User='" + name + "' AND Host!='localhost'"));
                mysql_obj.execute("update mysql.user set authentication_string='' where User='" + username + "'")
                result = mysql_obj.execute("ALTER USER `%s`@`localhost` IDENTIFIED BY '%s'" % (username,newpassword))
                for my_host in accept:
                    mysql_obj.execute("ALTER USER `%s`@`%s` IDENTIFIED BY '%s'" % (username,my_host[0],newpassword))
            else:
                result = mysql_obj.execute("update mysql.user set Password=password('" + newpassword + "') where User='" + username + "'")

            isError=self.IsSqlError(result)
            if  isError != None: return isError
            mysql_obj.execute("flush privileges")

        #修改SQLITE
        if int(id) > 0:
            public.M('databases').where("id=?",(id,)).setField('password',newpassword)
        else:
            public.M('config').where("id=?",(id,)).setField('mysql_root',newpassword)
            session['config']['mysql_root'] = newpassword

        public.WriteLog("TYPE_DATABASE",'DATABASE_PASS_SUCCESS',(name,))
        return public.returnMsg(True,'DATABASE_PASS_SUCCESS',(name,))


    def setMyCnf(self,action = True):
        myFile = '/etc/my.cnf'
        mycnf = public.readFile(myFile)
        root = session['config']['mysql_root']
        pwdConfig = "\n[mysqldump]\nuser=root\npassword=root"
        rep = "\n\[mysqldump\]\nuser=root\npassword=.+"
        if action:
            if mycnf.find(pwdConfig) > -1: return
            if mycnf.find("\n[mysqldump]\nuser=root\n") > -1:
                mycnf = mycnf.replace(rep, pwdConfig)
            else:
                mycnf  += "\n[mysqldump]\nuser=root\npassword=root"
        else:
            mycnf = mycnf.replace(rep, '')
        public.writeFile(myFile,mycnf)

    #检测备份目录并赋值权限（MSSQL需要Authenticated Users）
    def CheckBackupPath(self,get):
        backupFile = session['config']['backup_path'] + '/database/sqlserver'
        if not os.path.exists(backupFile):
            os.makedirs(backupFile)
            get.filename = backupFile
            get.user = 'Authenticated Users'
            get.access = 2032127
            import files
            files.files().SetFileAccess(get)

    #备份
    def ToBackup(self,get):

        id = get['id']
        find = public.M('databases').where("id=?",(id,)).find()
        if not find: return public.returnMsg(False,'数据库不存在!')

        self.CheckBackupPath(get);

        fileName = find['name'] + '_' + time.strftime('%Y%m%d_%H%M%S',time.localtime()) + '.sql'
        if find['type'] == 'SQLServer': fileName = fileName.replace('.sql','.bak')

        backupName = session['config']['backup_path'] + '/database/' + fileName
        if find['type'] == 'SQLServer':

            backupName = session['config']['backup_path'] + '/database/sqlserver/' + fileName
            #sqlserver .bak文件
            mssql_obj = panelMssql.panelMssql()
            ret = mssql_obj.execute("backup database %s To disk='%s'" % (find['name'],backupName))

            isError=self.IsSqlError(ret)
            if  isError != None: return isError
        else:
            if find['db_type'] in ['0',0]:

                if not self.check_server(): return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')
                root = public.M('config').where('id=?',(1,)).getField('mysql_root');
                try:
                    myconf = public.readFile(self._setup_path + '/my.ini')
                    rep = "port\s*=\s*([0-9]+)"
                    port = re.search(rep,myconf).groups()[0].strip();
                except :
                    port = '3306'

                backup_shell = "{}/bin/mysqldump.exe -R -E --hex-blob --opt --force --default-character-set={} -P{} -uroot -p{} -R \"{}\" > {}".format(self._setup_path,public.get_database_character(find['name']),port,root,self.db_name_to_unicode(find['name']),backupName)
                ret = public.ExecShell(backup_shell)

            elif find['db_type'] in ['1',1]:
            # 远程数据库
                try:
                    conn_config = json.loads(find['conn_config'])
                    res = self.CheckCloudDatabase(conn_config)
                    if isinstance(res,dict): return res
                    os.environ["MYSQL_PWD"] = str(conn_config['db_password'])
                    public.ExecShell(self._setup_path+"/bin/mysqldump.exe -h "+ conn_config['db_host'] +" -P "+ str(int(conn_config['db_port'])) +" -R -E --triggers=false --default-character-set=" + public.get_database_character(find['name']) + " --force --opt \"" + str(find['name']) + "\"  -u "+ str(conn_config['db_user']) +" -p"+str(conn_config['db_password'])+"  > " + backupName)
                except Exception as e:
                    raise
                finally:
                    os.environ["MYSQL_PWD"] = ""
            elif find['db_type'] in ['2',2]:
                try:
                    conn_config = public.M('database_servers').where('id=?',find['sid']).find()
                    res = self.CheckCloudDatabase(conn_config)
                    if isinstance(res,dict): return res
                    os.environ["MYSQL_PWD"] = str(conn_config['db_password'])
                    public.ExecShell(self._setup_path+"/bin/mysqldump.exe -h "+ conn_config['db_host'] +" -P "+ str(int(conn_config['db_port'])) +" -R -E --triggers=false --default-character-set=" + public.get_database_character(find['name']) + " --force --opt \"" + str(find['name']) + "\"  -u "+ str(conn_config['db_user']) +" -p"+str(conn_config['db_password'])+"  > " + backupName)
                except Exception as e:
                    raise
                finally:
                    os.environ["MYSQL_PWD"] = ""
            else:
                return public.returnMsg(False,'不支持的数据库类型')
        if not os.path.exists(backupName):
            return public.returnMsg(False,'BACKUP_ERROR');

        sql = public.M('backup')
        addTime = time.strftime('%Y-%m-%d %X',time.localtime())
        sql.add('type,name,pid,filename,size,addtime',(1,fileName,id,backupName,0,addTime))
        public.WriteLog("TYPE_DATABASE", "DATABASE_BACKUP_SUCCESS",(find['name'],))

        if os.path.getsize(backupName) < 2048:
            return public.returnMsg(True, '备份执行成功，备份文件小于2Kb，请检查备份完整性.')
        else:
            return public.returnMsg(True, 'BACKUP_SUCCESS')


    #删除备份文件
    def DelBackup(self,get):

        name=''
        id = get.id
        where = "id=?"
        filename = public.M('backup').where(where,(id,)).getField('filename')
        if os.path.exists(filename): os.remove(filename)

        if filename == 'qiniu':
            name = public.M('backup').where(where,(id,)).getField('name');

            public.ExecShell(public.get_run_python("[PYTHON] "+public.GetConfigValue('setup_path') + '/panel/script/backup_qiniu.py delete_file ' + name))

        public.M('backup').where(where,(id,)).delete()
        public.WriteLog("TYPE_DATABASE", 'DATABASE_BACKUP_DEL_SUCCESS',(name,filename))
        return public.returnMsg(True, 'DEL_SUCCESS');


    #导入
    def InputSql(self,get):

        name = get.name
        file = get.file

        find = public.M('databases').where("name=?",(name,)).find()
        if not find: return public.returnMsg(False,'数据库不存在!')

        tmp = file.split('.')
        exts = ['sql','zip','bak']
        ext = tmp[len(tmp) -1]
        if ext not in exts:
            return public.returnMsg(False, 'DATABASE_INPUT_ERR_FORMAT')

        backupPath = session['config']['backup_path'] + '/database'

        if ext == 'zip':
            try:
                fname = os.path.basename(file).replace('.zip','')
                dst_path = backupPath + '/' +fname
                if not os.path.exists(dst_path): os.makedirs(dst_path)

                import zipfile
                zip_file = zipfile.ZipFile(file)
                for names in zip_file.namelist():
                    zip_file.extract(names,dst_path)
                zip_file.close()

                for x in os.listdir(dst_path):
                    if x.find('bak') >= 0 or x.find('sql') >= 0:
                        file = dst_path + '/' + x
                        break

            except :
                return public.returnMsg(False,'导入失败，该文件不是有效的zip格式的文件。')

        if find['type'] == 'SQLServer':
            mssql_obj = panelMssql.panelMssql()
            data = mssql_obj.query("use %s ;select filename from sysfiles" % find['name'])

            isError = self.IsSqlError(data)
            if isError != None: return isError
            if type(data) == str: return public.returnMsg(False,data)

            mssql_obj.execute("ALTER DATABASE %s SET OFFLINE WITH ROLLBACK IMMEDIATE" % (find['name']))

            mssql_obj.execute("use master;restore database %s from disk='%s' with replace, MOVE N'%s' TO N'%s',MOVE N'%s_Log'  TO N'%s' " % (find['name'],file,find['name'],data[0][0],find['name'],data[1][0]))
            mssql_obj.execute("ALTER DATABASE %s SET ONLINE" % (find['name']))
        else:
            if not os.path.exists(file):
                return public.returnMsg(False, 'FILE_NOT_EXISTS',(file,))

            if find['db_type'] in ['0',0]:
                if not self.check_server():
                    return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')

                root = public.M('config').where('id=?',(1,)).getField('mysql_root');
                os.environ["MYSQL_PWD"] = str(root)
                public.ExecShell(self._setup_path + "/bin/mysql.exe -P"+str(public.get_mysql_info()['port'])+" -uroot -p" + root + " --force --default-character-set=utf8 \"" + name + "\" < " + file)

            elif find['db_type'] in ['1',1]:
                    conn_config = json.loads(find['conn_config'])
                    os.environ["MYSQL_PWD"] = str(conn_config['db_password'])
                    public.ExecShell(self._setup_path + "/bin/mysql.exe -h "+ conn_config['db_host'] +" -P "+str(int(conn_config['db_port']))+" -u"+str(conn_config['db_user'])+" -p" + str(conn_config['db_password']) + " --force \"" + name + "\" < " +'"'+ file +'"')
            elif find['db_type'] in ['2',2]:
                conn_config = public.M('database_servers').where('id=?',find['sid']).find()
                os.environ["MYSQL_PWD"] = str(conn_config['db_password'])
                public.ExecShell(self._setup_path + "/bin/mysql.exe -h "+ conn_config['db_host'] +" -P "+str(int(conn_config['db_port']))+" -u"+str(conn_config['db_user'])+" -p" + str(conn_config['db_password']) + " --force \"" + name + "\" < " +'"'+ file +'"')


        public.WriteLog("TYPE_DATABASE", '导入数据库[{}]成功'.format(name))
        return public.returnMsg(True, 'DATABASE_INPUT_SUCCESS');


    #同步数据库到服务器
    def SyncToDatabases(self,get):
        type = int(get['type'])
        n = 0
        sql = public.M('databases')
        if type == 0:
            data = sql.field('id,name,username,password,accept,type,sid,db_type').select()
            for value in data:
                if value['db_type'] in ['1',1]:
                    continue # 跳过远程数据库
                #if value['type'] == 'SQLServer': return public.returnMsg(False,'SQLServer不支持同步功能.')
                result = self.ToDataBase(value)
                if result == 1: n +=1
        else:
            import json
            data = json.loads(get.ids)
            for value in data:
                find = sql.where("id=?",(value,)).field('id,name,username,password,accept,type').find()
                #if find['type'] == 'SQLServer':  return public.returnMsg(False,'SQLServer不支持同步功能.')
                result = self.ToDataBase(find)
                if result == 1: n +=1

        return public.returnMsg(True,'DATABASE_SYNC_SUCCESS',(str(n),))


    #添加到服务器
    def ToDataBase(self,find):
        if find['username'] == 'bt_default': return 0
        if len(find['password']) < 3 :
            find['username'] = find['name']
            find['password'] = public.md5(str(time.time()) + find['name'])[0:10]
            public.M('databases').where("id=?",(find['id'],)).save('password,username',(find['password'],find['username']))
        if find['type'] == 'SQLServer':

            mssql_obj = panelMssql.panelMssql()
            mssql_obj.execute("CREATE DATABASE %s" % find['name'])
            mssql_obj.execute("use %s create login %s with password ='%s' , default_database = %s" % (find['name'],find['username'],find['password'],find['name']))
            mssql_obj.execute("use %s create user %s for login %s with default_schema=dbo" % (find['name'],find['username'],find['username']))
            mssql_obj.execute("use %s exec sp_addrolemember 'db_owner','%s'" % (find['name'],find['name']))
            mssql_obj.execute("ALTER DATABASE %s SET MULTI_USER" % find['name'])

        else:
            if not self.check_server(): return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')

            result = panelMysql.panelMysql().execute("create database " + find['name'])
            if "using password:" in str(result): return -1
            if "Connection refused" in str(result): return -1
            panelMysql.panelMysql().execute("drop user '" + find['username'] + "'@'localhost'")
            panelMysql.panelMysql().execute("drop user '" + find['username'] + "'@'" + find['accept'] + "'")
            password = find['password']
            self.__CreateUsers(find['type'],find['name'],find['username'],password,find['accept'])

            return 1

    #从服务器获取数据库
    def SyncGetDatabases(self,get):
        sql = public.M('databases')
        n = 0
        s = 0

        self.sid = get.get('sid/d',0)
        db_type = 0
        if self.sid: db_type = 2
        mysql_obj = public.get_mysql_obj_by_sid(self.sid)

        nameArr = ['information_schema','performance_schema','mysql','sys','master','model','msdb','tempdb']

        data = mysql_obj.query("show databases")
        if not data: return public.returnMsg(False,'数据库连接失败，请检查配置是否正确.')

        isError = self.IsSqlError(data)
        if isError != None: return isError
        users = mysql_obj.query("select User,Host from mysql.user where User!='root' AND Host!='localhost' AND Host!=''")

        try:
            for value in data:
                b = False
                for key in nameArr:
                    if value[0] == key:
                        b = True
                        break
                if b:continue
                if value[0].find('_backup') >= 0: continue

                if sql.where("name=?",(value[0],)).count(): continue
                host = '127.0.0.1'
                for user in users:
                    if value[0] == user[0]:
                        host = user[1]
                        break

                ps = public.getMsg('INPUT_PS')
                if value[0] == 'test':
                        ps = public.getMsg('DATABASE_TEST')
                addTime = time.strftime('%Y-%m-%d %X',time.localtime())
                if sql.table('databases').add('name,sid,db_type,username,password,accept,ps,addtime,type',(value[0],self.sid,db_type,value[0],'',host,ps,addTime,'MySQL')): n +=1

        except Exception as ex:
            public.submit_panel_bug('同步数据库异常 --> ' + str(data) + '  --------->' + str(ex))


        if public.get_server_status("MSSQLSERVER") >= 0:
            data = panelMssql.panelMssql().query('SELECT name FROM MASTER.DBO.SYSDATABASES ORDER BY name')
            isError = self.IsSqlError(data)
            if isError != None: return isError
            if type(data) == str: return public.returnMsg(False,data)

            if data:
                for item in data:
                    dbname = item[0]
                    if sql.where("name=?",(dbname,)).count(): continue
                    if not dbname in nameArr:
                        ps = public.getMsg('INPUT_PS')
                        addTime = time.strftime('%Y-%m-%d %X',time.localtime())
                        if sql.table('databases').add('name,username,password,accept,ps,addtime,type',(dbname,dbname,'','',ps,addTime,'SQLServer')): s +=1

        return public.returnMsg(True,'DATABASE_GET_SUCCESS',(str(n),))

    #获取数据库权限
    def GetDatabaseAccess(self,get):
        name = get['name']
        db_name = public.M('databases').where('username=?',name).getField('name')

        mysql_obj = public.get_mysql_obj(db_name)

        users = mysql_obj.query("select Host from mysql.user where User='" + name + "' AND Host!='localhost'")
        isError = self.IsSqlError(users)
        if isError != None: return isError
        users = self.map_to_list(users)

        if len(users)<1:
            return public.returnMsg(True,"127.0.0.1")

        accs = []
        for c in users:
            accs.append(c[0]);
        userStr = ','.join(accs);
        return public.returnMsg(True,userStr)

    #设置数据库权限
    def SetDatabaseAccess(self,get):
        users = None

        name = get['name']
        db_find = public.M('databases').where('username=?',(name,)).find()
        db_name = db_find['name']
        self.sid = db_find['sid']
        mysql_obj = public.get_mysql_obj(db_name)

        access = get['access'].strip()
        if access in ['']: return public.returnMsg(False,'IP地址不能为空!')
        password = public.M('databases').where("username=?",(name,)).getField('password')

        result = mysql_obj.query("show databases")
        isError = self.IsSqlError(result)
        if isError != None: return isError

        users = mysql_obj.query("select Host from mysql.user where User='" + name + "' AND Host!='localhost'")
        for us in users:
            mysql_obj.execute("drop user '" + name + "'@'" + us[0] + "'")
        self.__CreateUsers('MySQL',db_name,name,password,access)

        mysql_obj.execute("flush privileges")
        return public.returnMsg(True, 'SET_SUCCESS')

    #修改sqlserver端口
    def SetMsSQLPort(self,get):
        mssql_obj = panelMssql.panelMssql()

        server_name = mssql_obj.get_sql_name()
        if not server_name: return public.returnMsg(True, '未识别的服务名称，请进服务器手动重启SQL服务.')

        os.system('net stop {}'.format(server_name))
        if not mssql_obj.set_port(get.port):
           return public.returnMsg(False, 'SQLServer端口修改失败,未知版本！')

        os.system('net start {}'.format(server_name))

        return public.returnMsg(True, 'SQLServer端口修改成功！')

    #获取mssql配置
    def GetMsSQLInfo(self,get):
        data = {}
        data['port'] = '1433';
        try:
            mssql_obj = panelMssql.panelMssql()
            data['port'] = mssql_obj.get_port()
        except : pass
        return data

    #获取数据库配置信息
    def GetMySQLInfo(self,get):
        data = {}
        try:
            if not self.check_server(): return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')
            path = public.get_server_path('mysql');
            file = None;
            if path:
                file = re.search('([MySQL|MariaDB-]+\d+\.\d+)',path).groups()[0]

                myfile = public.GetConfigValue('setup_path') + '/mysql/'+file+'/my.ini'
                mycnf = public.readFile(myfile);

                rep = "datadir\s*?=\s?\"(.+)\"\s?"
                data['datadir'] = re.search(rep,mycnf).groups()[0];

                rep = "port\s*=\s*([0-9]+)\s*\n"
                data['port'] = re.search(rep,mycnf).groups()[0];
        except:
            data['datadir'] = '';
            data['port'] = '3306';
        return data;

    #修改数据库目录
    def SetDataDir(self,get):
        return public.returnMsg(True,'暂不支持修改！');

    #修改数据库端口
    def SetMySQLPort(self,get):
        myfile = self._setup_path + '/my.ini';
        if not self._setup_path: return public.returnMsg(False,'修改失败，请检查是否安装MySQL');

        if not os.path.exists(myfile): return public.returnMsg(False,'修改失败，请检查是否安装MySQL');

        mycnf = public.readFile(myfile);
        rep = "port\s*=\s*([0-9]+)\s*\n"
        mycnf = re.sub(rep,'port = ' + get.port + '\n',mycnf);
        public.writeFile(myfile,mycnf);
        try:
            public.set_phpmyadmin_conf(get.port)
        except :
            return public.returnMsg(False,'修改失败，请修复面板后重新操作。');

        os.system('net stop mysql')
        os.system('net start mysql')

        return public.returnMsg(True,'EDIT_SUCCESS');

    #获取错误日志
    def GetErrorLog(self,get):
        info = self.GetMySQLInfo(get)
        if not info or not 'datadir' in info: return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')

        path = info['datadir']
        if not os.path.exists(path): return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')

        filename = ''
        for n in os.listdir(path):
            if len(n) < 5: continue

            if n[-9:] == 'error.log':
                filename = path + '/' + n
                break
        if not os.path.exists(filename):
            return public.returnMsg(False,'文件不存在.')
        if hasattr(get,'close'):
            public.writeFile(filename,'')
            return public.returnMsg(True,'LOG_CLOSE')

        return public.GetNumLines(filename,1000)

    #二进制日志开关
    def BinLog(self,get):
        if not self.check_server(): return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')

        myfile = self._setup_path + '/my.ini';
        mycnf = public.readFile(myfile);
        if mycnf.find('#log-bin=mysql-bin') != -1:
            if hasattr(get,'status'): return public.returnMsg(False,'0');
            mycnf = mycnf.replace('#log-bin=mysql-bin','log-bin=mysql-bin')
            mycnf = mycnf.replace('#binlog_format=mixed','binlog_format=mixed')
        else:
            info = self.GetMySQLInfo(get)
            if not info: return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')
            path = info['datadir'];
            if not os.path.exists(path): return public.returnMsg(False,'数据库目录不存在，请检查配置是否正确.')

            if hasattr(get,'status'):
                dsize = 0

                for n in os.listdir(path):
                    if len(n) < 9: continue
                    if n[0:9] == 'mysql-bin':
                        dsize += os.path.getsize(path + '/' + n)
                return public.returnMsg(True,dsize)

            mycnf = mycnf.replace('log-bin=mysql-bin','#log-bin=mysql-bin')
            mycnf = mycnf.replace('binlog_format=mixed','#binlog_format=mixed')
            public.set_server_status('mysql','restart')

        public.writeFile(myfile,mycnf)
        return public.returnMsg(True,'SUCCESS')

    #获取MySQL配置状态
    def GetDbStatus(self,get):
        if not self.check_server(): return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')

        result = {}
        data = self.map_to_list(panelMysql.panelMysql().query('show variables'))
        gets = ['table_open_cache','thread_cache_size','query_cache_type','key_buffer_size','query_cache_size','tmp_table_size','max_heap_table_size','innodb_buffer_pool_size','innodb_additional_mem_pool_size','innodb_log_buffer_size','max_connections','sort_buffer_size','read_buffer_size','read_rnd_buffer_size','join_buffer_size','thread_stack','binlog_cache_size'];
        result['mem'] = {}
        for d in data:
            try:
                for g in gets:
                    if d[0] == g: result['mem'][g] = d[1]
            except: continue
        if 'query_cache_type' in result['mem']:
            if result['mem']['query_cache_type'] != 'ON': result['mem']['query_cache_size'] = '0'
        return result

    #设置MySQL配置参数
    def SetDbConf(self,get):
        if not self.check_server(): return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')

        gets = ['key_buffer_size','query_cache_size','tmp_table_size','max_heap_table_size','innodb_buffer_pool_size','innodb_log_buffer_size','max_connections','query_cache_type','table_open_cache','thread_cache_size','sort_buffer_size','read_buffer_size','read_rnd_buffer_size','join_buffer_size','thread_stack','binlog_cache_size'];
        emptys = ['max_connections','query_cache_type','thread_cache_size','table_open_cache']
        mycnf = public.readFile(self._setup_path + '/my.ini')
        n = 0
        m_version = re.search('([MySQL|MariaDB-]+\d+\.\d+)',public.get_server_path('mysql')).groups()[0]
        if not m_version: m_version = ''

        for g in gets:

            if m_version.find('8.0') >= 0 and g in ['query_cache_type','query_cache_size']:
                n += 1
                continue
            s = 'M'
            if n > 5: s = 'K'
            if g in emptys: s = ''
            rep = '\s*'+g+'\s*=\s*\d+(M|K|k|m|G)?\n'
            c = g+' = ' + get[g] + s +'\n'
            if mycnf.find(g) != -1:
                mycnf = re.sub(rep,'\n'+c,mycnf,1)
            else:
                mycnf = mycnf.replace('[mysqld]\n','[mysqld]\n' +c)
            n+=1
        public.writeFile(self._setup_path + '/my.ini',mycnf)
        return public.returnMsg(True,'SET_SUCCESS')

    #获取MySQL运行状态
    def GetRunStatus(self,get):
        if not self.check_server(): return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')

        import time;
        result = {}
        data = panelMysql.panelMysql().query('show global status')

        isError = self.IsSqlError(data)
        if  isError != None: return isError

        gets = ['Max_used_connections','Com_commit','Com_rollback','Questions','Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests','Key_reads','Key_read_requests','Key_writes','Key_write_requests','Qcache_hits','Qcache_inserts','Bytes_received','Bytes_sent','Aborted_clients','Aborted_connects','Created_tmp_disk_tables','Created_tmp_tables','Innodb_buffer_pool_pages_dirty','Opened_files','Open_tables','Opened_tables','Select_full_join','Select_range_check','Sort_merge_passes','Table_locks_waited','Threads_cached','Threads_connected','Threads_created','Threads_running','Connections','Uptime']
        try:
            for d in data:
                for g in gets:
                    try:
                        if d[0].lower() == g.lower(): result[g] = d[1]
                    except : pass
            result['Run'] = int(time.time()) - int(result['Uptime'])
            tmp = panelMysql.panelMysql().query('show variables')
        except :
            public.submit_panel_bug('获取数据库状态失败 --> ' + str(data))

        try:
            result['File'] = tmp[0][0]
            result['Position'] = tmp[0][1]
        except:
            result['File'] = 'OFF'
            result['Position'] = 'OFF'
        return result

    #取慢日志
    def GetSlowLogs(self,get):
        if not self.check_server():
            return public.returnMsg(False,'MySQL服务未安装或不是通过宝塔6.x以上版本安装的.')

        path = self._setup_path + '/data/mysql-slow.log'
        print(path)
        if not os.path.exists(path): return public.returnMsg(False,'日志文件不存在!')
        return public.returnMsg(True,public.GetNumLines(path,1000))

    # 获取当前数据库信息
    def GetInfo(self,get):
        info=self.GetdataInfo(get)
        return info
        if info:
            return info
        else:
            return public.returnMsg(False,"获取数据库失败!")

    #修复表信息
    def ReTable(self,get):
        m_version = self._version
        if m_version.find('5.1.')!=-1:return public.returnMsg(False,"不支持mysql5.1!")
        info=self.RepairTable(get)
        if info:
            return public.returnMsg(True,"修复完成!")
        else:
            return public.returnMsg(False,"修复失败!")

    # 优化表
    def OpTable(self,get):
        info=self.OptimizeTable(get)
        if info:
            return public.returnMsg(True,"优化成功!")
        else:
            return public.returnMsg(False,"优化失败或者已经优化过了")

    #更改表引擎
    def AlTable(self,get):
        info=self.AlterTable(get)
        if info:
            return public.returnMsg(True,"更改成功")
        else:
            return public.returnMsg(False,"更改失败")


    def get_average_num(self,slist):
        """
        @获取平均值
        """
        count = len(slist)
        limit_size = 1 * 1024 * 1024
        if count <= 0: return limit_size

        if len(slist) > 1:
            slist = sorted(slist)
            limit_size =int((slist[0] + slist[-1])/2 * 0.85)
        return limit_size

    def get_database_size(self,ids,is_pid = False):
        """
        获取数据库大小
        """
        result = {}

        for id in ids:
            if not is_pid:
                x = public.M('databases').where('id=?',id).field('id,sid,pid,name,type,ps,addtime').find()
            else:
                x = public.M('databases').where('pid=?',id).field('id,sid,pid,name,ps,type,addtime').find()
            if not x: continue

            x['backup_count'] = public.M('backup').where("pid=? AND type=?",(x['id'],'1')).count()
            x['total'] = int(public.get_database_size_by_id(x['id']))

            try:
                from panelDatabaseController import DatabaseController
                project_obj = DatabaseController()

                get = public.dict_obj()
                get['data'] = {'db_id': x['id'] }
                get['mod_name'] = x['type'].lower()
                get['def_name'] = 'get_database_size_by_id'

                x['total'] = project_obj.model(get)
            except :
                x['total'] = int(public.get_database_size_by_id(x['id']))
            result[x['name']] = x
        return result

    def check_del_data(self,get):
        """
        @删除数据库前置检测
        """
        ids = json.loads(get.ids)
        slist = {};result = [];db_list_size = []
        db_data = self.get_database_size(ids)
        for key in db_data:
            data = db_data[key]
            if not data['id'] in ids: continue

            db_addtime = public.to_date(times = data['addtime'])
            data['score'] = int(time.time() - db_addtime) + data['total']
            data['st_time'] = db_addtime

            if data['total'] > 0 : db_list_size.append(data['total'])
            result.append(data)

        slist['data'] = sorted(result,key= lambda  x:x['score'],reverse=True)
        slist['db_size'] = self.get_average_num(db_list_size)
        return slist



