dbmysql.cpp

用ODBC写的数据库接口,支持SQlserver,mysql

//---------------------------------------------------------------------------


#pragma hdrstop

#include "dbmysql.h"
#include <stdio.h>
#include <dir.h>
//---------------------------------------------------------------------------

#pragma package(smart_init)
CDBMySQL::CDBMySQL()
{
    strcpy(m_ClassName,STR_MYSQL);
    strcpy(m_Driver,"MySQL"); //初始值
    m_WDBINFO.bSupportTrans = false;
    m_WDBINFO.bSupportLockOne = false;
    m_WDBINFO.bSupportView = false;
    m_WDBINFO.bEscape = true;
}

CDBMySQL::~CDBMySQL()
{
 
}

//AnsiString   ConnString="Provider=SQLOLEDB.1;Persist   Security   Info=False;"
//    "User   ID=sa;Initial   Catalog=tempdb;Data   Source=Server   name";//?
//    Microsoft OLE db provider for SQL Server

//采用父类的连接
//bool  CDBMySQL::ConnectDB (char *szhostserver,char *szUser,char *szPassWord,char *szDBName)
//{
    //通过OLE
/*    strcpy(szConStr,"Provider=SQLOLEDB.1");
    if(szhostserver)
    {
        sprintf(sztmp,";SERVER=%s",szhostserver);
        strcat(szConStr,sztmp);
    }
    if(szUser)
    {
        sprintf(sztmp,";UID=%s",szUser);
        strcat(szConStr,sztmp);
    }
    if(szPassWord)
    {
        sprintf(sztmp,";PWD=%s",szPassWord);
        strcat(szConStr,sztmp);
    }
    if(szDBName)
    {
        sprintf(sztmp,";DATABASE=%s",szDBName);
        strcat(szConStr,sztmp);
    }
    return ConnectDBByStr(szConStr);*/
//}


bool  CDBMySQL::GetAllDB (char * szDBName,int iSize)
{
int offset=0,len;
    memset(szDBName,0,iSize);
    if(Query("Show Databases"))
    {
        First();
        for(int id=0;id<iRecordCount&&szDBName;id++)
        {
            GetDataValue("Database",&len);
            if(len+offset>=iSize-2)
                len=iSize-2-offset;
            if(len>0)
            {
                strncpy(&szDBName[offset],GetDataValue("Database"),len);
                offset+=len;
                szDBName[offset]='\n';
                offset++;
            }
            else
                break;
            Next();
        }
    }
    else
        return false;
    return true;
}

bool  CDBMySQL::GetAllUser (DB_USER_INFO * szUser,int iInNum,int *iOutNum)
{
DB_USER_INFO *userinfo;
char sql[560];
//    if(!mDatabase->Connected&&!mQuery[CurrQuery]->Active)
//        return false;
    if(Query("select host,user,password from mysql.user"))
    {
        First();
        for(int id=0;id<iRecordCount&&id<iInNum&&szUser;id++)
        {
            //if((int)(id*sizeof(USER_INFO))>=iSize)
            //    return true;
            userinfo=(DB_USER_INFO *)&szUser[id];
            memset(userinfo,0,sizeof(DB_USER_INFO));
            strcpy(userinfo->szHost,GetDataValue("host"));
            strcpy(userinfo->szUser,GetDataValue("user"));
            strcpy(userinfo->szPassword,GetDataValue("password"));
            Next();
        }
    }
    else
        return false;
    return true;
}

/*bool  CDBFaceBase::GetAllDSN (char * szDBDSN,int iSize)
{
int offset=0,len;
int idx;
AnsiString dn;
    TStringList *MyStringList = new TStringList;
    TStringList *MyStringListPara = new TStringList;
    try
    {
        memset(szDBDSN,0,iSize);
        Session->GetDatabaseNames(MyStringList);
        for(int id=0;id<MyStringList->Count;id++)
        {
            dn=Session->GetAliasDriverName(MyStringList->Strings[id]);

            if(idx>=0&&strstr(dn.c_str(),"MySQL"))
            {
                len=MyStringList->Strings[id].Length();
                if(len+offset>=iSize-2)
                    len=iSize-2-offset;
                if(len>0)
                {
                    strncpy(&szDBDSN[offset],MyStringList->Strings[id].c_str(),len);
                    offset+=len;
                    szDBDSN[offset]='\n';
                    offset++;
                }
                else
                    break;
            }
        }
    }
    catch(...)
    {
        delete  MyStringList;
        delete  MyStringListPara;
        return false;
    }
    delete  MyStringList;
    delete  MyStringListPara;
    return true;
*/

bool  CDBMySQL::SetUserRole (DB_USER_INFO *userinfo)
{
char cmd[512],str[120];
    if(!userinfo)
        return false;
    //if(userinfo->szDBName&&userinfo->szDBName[0])
    //    sprintf(cmd,"grant all privileges ON %s.* ",userinfo->szDBName); //无文件全县
    //else
        sprintf(cmd,"grant all privileges ON *.* ");
    if(userinfo->szUser[0])
        sprintf(str," TO '%s'",userinfo->szUser);
    else
        sprintf(str," TO ''");
    strcat(cmd,str);
    if(userinfo->szHost[0])
        sprintf(str,"@'%s' ",userinfo->szHost);
    else
        sprintf(str,"@'%%' ");
    strcat(cmd,str);
    sprintf(str," identified by '%s' with grant option",userinfo->szPassword);
    strcat(cmd,str);
    int ret = ExecSQLCmd(cmd);
    ExecSQLCmd("flush privileges");
    return ret;
}

bool  CDBMySQL::DeleteUser(DB_USER_INFO *UserInfo)
{
char cmd[256];
    sprintf(cmd,"delete from mysql.user where host='%s' and user='%s';FLUSH PRIVILEGES",
        UserInfo->szHost,UserInfo->szUser);
    return ExecSQLCmd(cmd);
}

bool  CDBMySQL::LockTables(char *tables)
{
    char sql[1024];
    sprintf(sql,"lock tables %s",tables);
    return ExecSQLCmd(sql);
}

bool  CDBMySQL::UnlockTables(char *tables)
{
    return ExecSQLCmd("unlock tables");
}

bool  CDBMySQL::BatchAdd(HANDLE hdBatchHandle,char *szValues,int type,int ValueLen,bool endline)
{
    DB_BATCH_INFO *pDbBatchInfo = (DB_BATCH_INFO *)hdBatchHandle;
    if(szValues && ValueLen>0)
    {
        if(ValueLen>409600)
        {
            sprintf(m_Err,"值长度太长!LEN=%d",ValueLen);
            WriteLog(m_Err);
            return false;
        }
        if(type==AS_BLOB)
        {
            pDbBatchInfo->pData = (char *)realloc(pDbBatchInfo->pData,pDbBatchInfo->nDataLen+ValueLen*2+3);
            ValueLen = EscapeData(szValues,pDbBatchInfo->pData+pDbBatchInfo->nDataLen,ValueLen);
            //HexToChar(szValues,pDbBatchInfo->pData+pDbBatchInfo->nDataLen,ValueLen);
            pDbBatchInfo->nDataLen += (ValueLen);
        }
        else
        {
            //pDbBatchInfo->nDataLen+=(ValueLen+1);
            pDbBatchInfo->pData = (char *)realloc(pDbBatchInfo->pData,pDbBatchInfo->nDataLen+ValueLen*2+3);
            if(type==AS_STRING)
            {
                while(ValueLen>0 && (szValues[ValueLen-1]==' ' ||szValues[ValueLen-1]=='\0'))
                    ValueLen--;
                ValueLen = EscapeData(szValues,pDbBatchInfo->pData+pDbBatchInfo->nDataLen,ValueLen);
            }
            else
                memcpy(pDbBatchInfo->pData+pDbBatchInfo->nDataLen,szValues,ValueLen);
            pDbBatchInfo->nDataLen += ValueLen;
        }
    }
    else
        pDbBatchInfo->pData = (char *)realloc(pDbBatchInfo->pData,pDbBatchInfo->nDataLen+3);
    if(endline)
    {
//        pDbBatchInfo->pData[pDbBatchInfo->nDataLen] = 0X0D;
//        pDbBatchInfo->nDataLen++;
        pDbBatchInfo->pData[pDbBatchInfo->nDataLen] = 0X0A;
        pDbBatchInfo->nDataLen++;
    }
    else
    {
        pDbBatchInfo->pData[pDbBatchInfo->nDataLen] = 0X09;
        pDbBatchInfo->nDataLen++;
    }
    if(pDbBatchInfo->nDataLen>409600)
    {
        int writesize = fwrite(pDbBatchInfo->pData,pDbBatchInfo->nDataLen,1,pDbBatchInfo->fp);
        if(writesize!=1)
        {
            strcpy(m_Err,"写入文件失败!");
            return false;
        }
        free(pDbBatchInfo->pData);
        pDbBatchInfo->nDataLen = 0;
        pDbBatchInfo->pData = 0;
//        WriteLog("write file batch end ");
    }
    return true;
}

bool  CDBMySQL::BatchEnd(HANDLE hdBatchHandle,void *callback)
{
    DB_BATCH_INFO *pDbBatchInfo = (DB_BATCH_INFO *)hdBatchHandle;
    if(pDbBatchInfo->pData)
    {
        int writesize = fwrite(pDbBatchInfo->pData,pDbBatchInfo->nDataLen,1,pDbBatchInfo->fp);
        if(writesize!=1)
        {
            strcpy(m_Err,"写入文件失败!");
            return false;
        }
        free(pDbBatchInfo->pData);
        pDbBatchInfo->nDataLen = 0;
        pDbBatchInfo->pData = 0;
    }
    fclose(pDbBatchInfo->fp);
    pDbBatchInfo->fp = NULL;
    return BatchByFile(pDbBatchInfo->tmpFile,DB_FILE_TYPE_DEF,pDbBatchInfo->TableName,callback);
}

bool  CDBMySQL::BatchByFile(char *sFileName,int filetype,char *szTableName,void *callback)
{
    char szSql[2048],szOutFile[1024];
    EscapeData(sFileName,szOutFile,strlen(sFileName));
    sprintf(szSql,"LOAD DATA INFILE \"%s\" REPLACE into table %s ",szOutFile,szTableName);
    return ExecSQLCmd(szSql);
}

bool  CDBMySQL::FetchToFile(char *sql,char *pathname,char *szTable,void *callback)
{
    char szSql[2048],szOutFile[1024];
    DeleteFile(pathname);
    EscapeData(pathname,szSql,strlen(pathname));
    sprintf(szOutFile," into outfile \"%s\" FROM ",szSql);
    sprintf(szSql,sql);
    ReplaceValues(szSql," FROM ",szOutFile);
    ReplaceValues(szSql," from ",szOutFile);
    bool ret = ExecSQLCmd(szSql);
    Query(sql);
    return ret;
}

bool CDBMySQL::OptimizeTable(char *tables)
{
    char szSql[1024];
    sprintf(szSql,"OPTIMIZE TABLE %s",tables);
    return ExecSQLCmd(szSql);
}

bool CDBMySQL::Backup(char *pathname,char *db,void *callback)
{
char OldPath[MAX_PATH+1],MySQLPath[MAX_PATH+1],*pp;
getcwd(OldPath,MAX_PATH);
HKEY hKey;
int re;
DWORD type,lpcbData=MAX_PATH ;
char command[MAX_PATH],cmd[MAX_PATH+4560]={0};
//HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\Session Manager  PendingFileRenameOperations
//HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MySql
    re=RegOpenKeyEx(HKEY_LOCAL_MACHINE,
            "SYSTEM\\ControlSet001\\Services\\MySql",
            0,KEY_ALL_ACCESS,
            &hKey);
    if(re==ERROR_SUCCESS)
    {
        RegQueryValueEx(hKey,
            "ImagePath",
            NULL,
            &type,
            MySQLPath,
            &lpcbData);
    }
    else
        strcpy(MySQLPath,"C:/mysql/bin/mysqld-nt.exe");
    RegCloseKey(hKey);
    char *pos=strstr(MySQLPath,"mysqld");
    if(pos)
        pos[0]=0;
    if(MySQLPath[0]=='\"')
        pp=MySQLPath+1;
    else
        pp=MySQLPath;
    memset(command,0,sizeof(command));
    sprintf(command,"%smysqldump.exe",pp);
    if(db)
        strcpy(m_DBName,db);
    sprintf(cmd," --opt -r \"%s\" -u%s -p%s -B %s",pathname,m_LoginUser,m_LoginPS,m_DBName);

    STARTUPINFO startinfo;
    PROCESS_INFORMATION info;
    memset(&startinfo,0,sizeof(STARTUPINFO));
    memset(&info,0,sizeof(PROCESS_INFORMATION));
    startinfo.dwFlags=STARTF_USESHOWWINDOW;
    startinfo.wShowWindow=SW_HIDE;
    if(CreateProcess(command,cmd,NULL,NULL,
        false,CREATE_NEW_CONSOLE,NULL,pp,&startinfo,&info)==0)
        return false;
    chdir(OldPath);
    return true;
}

bool CDBMySQL::Restore(char *pathname,char *db,void *callback)
{
char OldPath[MAX_PATH],MySQLPath[MAX_PATH],*pp;
getcwd(OldPath,MAX_PATH-1);
HKEY hKey;
int re;
DWORD type,lpcbData=MAX_PATH ;
char command[MAX_PATH],cmd[MAX_PATH+4560]={0};
//    PROCESS_INFORMATION ProcessInfo;
    re=RegOpenKeyEx(HKEY_LOCAL_MACHINE,
            "SYSTEM\\ControlSet001\\Services\\MySql",
            0,KEY_ALL_ACCESS,
            &hKey);
    if(re==ERROR_SUCCESS)
    {
        RegQueryValueEx(hKey,
            "ImagePath",
            NULL,
            &type,
            MySQLPath,
            &lpcbData);
    }
    else
        strcpy(MySQLPath,"C:/mysql/bin/mysqld-nt.exe");
    RegCloseKey(hKey);
    char *pos=strstr(MySQLPath,"mysqld");
    if(pos)
        pos[0]=0;
    if(MySQLPath[0]=='\"')
        pp=MySQLPath+1;
    else
        pp=MySQLPath;
    memset(command,0,sizeof(command));
    sprintf(command,"%smysql.exe",pp);
    if(db)
        strcpy(m_DBName,db);
//    sprintf(command,"%s/mysql.exe ",MySQLPath);
    //if(db)
        sprintf(cmd," -e \"source %s\" -u%s -p%s -B %s",pathname,m_LoginUser,m_LoginPS,m_DBName);
    //else
    //    sprintf(cmd," -e \"source %s\" -u%s -p%s",pathname,m_LoginUser,m_LoginPS);
    STARTUPINFO startinfo;
    PROCESS_INFORMATION info;
    memset(&startinfo,0,sizeof(STARTUPINFO));
    memset(&info,0,sizeof(PROCESS_INFORMATION));
    startinfo.dwFlags=STARTF_USESHOWWINDOW;
    startinfo.wShowWindow=SW_HIDE;

//    memset(&ProcessInfo,0,sizeof(ProcessInfo));
    if(CreateProcess(command,cmd,NULL,NULL,
        false,CREATE_NEW_CONSOLE,NULL,pp,&startinfo,&info))
        WaitForSingleObject(info.hProcess,INFINITE);
    else
        return false;
    chdir(OldPath);
    return true;
}