database.cpp

电力系统Linux设备通讯程序 主要是为了各大电网通讯数据代码

	    qstmp.sprintf("%s",tmps2.data());FieldList.append(qstmp);
	    iStart=i;
	}
       tmps2=SqlStr.mid(iStart+1,index-iStart-1);
       j=tmps2.find("=",iStart+1);tmps2=tmps2.mid(iStart+1,j-iStart-1);
       qstmp.sprintf("%s",tmps2.data());FieldList.append(qstmp);
       WhereStr=SqlStr.mid(index+1);
    }
    else if(tmps1=="DELETE")
    {
       FieldList.clear();
	index=SqlStr.find(" from ",0);if(index<0) index=SqlStr.find(" From ",0);
	if(index<0) index=SqlStr.find(" FROM ",0);
	if(index<0) return;
	iStart=index;
       index=SqlStr.find(" where ",iStart+1);if(index<0) index=SqlStr.find(" Where ",iStart+1);
       if(index<0) index=SqlStr.find(" WHERE ",iStart+1);
       if(index<0) index=SqlStr.length();
       Table=SqlStr.mid(iStart+6,index-iStart-6);
       WhereStr=SqlStr.mid(index+1);
    }
    else
    {
       Table="";FieldList.clear();WhereStr="";
    }
}

Oid CDataBase :: importfile(long hdbc,char *filename)
{
    PGconn *conn=(PGconn*)hdbc;
    Oid lobjId;int lobj_fd;char buf[BUFSIZE];int nbytes,tmp,fd;
    //open file
    fd=open(filename,O_RDONLY,0660);
    if(fd<0)
    {
	fprintf(stderr,"不能打开文件:%s\n",filename);return 0;
    }
    //create the large object
    lobjId=lo_creat(conn,INV_READ|INV_WRITE);
    if(lobjId==0)
    {
	fprintf(stderr,"不能创建大对象\n");(void)close(fd);return 0;
    }
    lobj_fd=lo_open(conn,lobjId,INV_WRITE);
    if(lobj_fd<0)
    {
	fprintf(stderr,"不能打开大对象:%d\n",lobjId);(void)close(fd);return 0;
    }
    while((nbytes=read(fd,buf,BUFSIZE))>0)
    {
	tmp=lo_write(conn,lobj_fd,buf,nbytes);
	if(tmp<nbytes)
	{
	    fprintf(stderr,"读大对象出错\n");
	    (void)close(fd);(void)lo_close(conn,lobj_fd);return 0;
	}
    }
    (void)close(fd);(void)lo_close(conn,lobj_fd);return lobjId;
}

bool CDataBase :: pickout(long hdbc,Oid lobjId,int start,int len,void* retbuf)
{
    int lobj_fd;char *buf;int nbytes,nread;
    PGconn *conn=(PGconn*)hdbc;
    if(retbuf==NULL) return 0;
    lobj_fd=lo_open(conn,lobjId,INV_READ);
    if(lobj_fd<0)
    {
	fprintf(stderr,"不能打开大对象:%d\n",lobjId);return 0;
    }
    lo_lseek(conn,lobj_fd,start,SEEK_SET);
    buf=(char*)malloc(len+1);nread=0;
    while(len-nread>0)
    {
	nbytes=lo_read(conn,lobj_fd,buf,len-nread);
	memcpy((char*)retbuf+nread,buf,nbytes);
	nread+=nbytes;
    }
    free(buf);lo_close(conn,lobj_fd);
    return 1;
}

bool CDataBase :: overwrite(long hdbc,Oid lobjId,int start,int len,void* srcbuf)
{
    int lobj_fd;char *buf;int nbytes,nwritten;
    if(srcbuf==NULL) return 0;
    PGconn *conn=(PGconn*)hdbc;
    lobj_fd=lo_open(conn,lobjId,INV_READ|INV_WRITE);
    if(lobj_fd<0)
    {
	fprintf(stderr,"不能打开大对象:%d\n",lobjId);return 0;
    }
    lo_lseek(conn,lobj_fd,start,SEEK_SET);
    buf=(char*)malloc(len+1);
    memset(buf,0x00,len+1);
    memcpy(buf,(char*)srcbuf,len);
    nwritten=0;
    while(len-nwritten>0)
    {
	nbytes=lo_write(conn,lobj_fd,buf+nwritten,len-nwritten);
	nwritten+=nbytes;
    }
    free(buf);lo_close(conn,lobj_fd);
    return 1;
}

bool CDataBase :: exportfile(long hdbc,Oid lobjId,char *filename)
{
    int lobj_fd;char buf[BUFSIZE];int nbytes,tmp,fd;
    PGconn *conn=(PGconn*)hdbc;
    lobj_fd=lo_open(conn,lobjId,INV_READ);
    if(lobj_fd<0)
    {
	fprintf(stderr,"不能打开大对象:%d\n",lobjId);return 0;
    }
    fd=open(filename,O_CREAT|O_WRONLY,0660);
    if(fd<0)
    {
	fprintf(stderr,"不能打开或创建文件:%s\n",filename);lo_close(conn,lobj_fd);return 0;
    }
    while((nbytes=lo_read(conn,lobj_fd,buf,BUFSIZE))>0)
    {
	tmp=write(fd,buf,nbytes);
	if(tmp<nbytes)
	{
	    break;
	}
    }
    (void)lo_close(conn,lobj_fd);(void)close(fd);return 1;
}

//******************************************************************
//*插入带有大对象的函数
//*SqlStr="Insert into image(name,photo1,photo2) values('12345',?,?);"
//*photo1,photo2为大对象的字段
//*FileList是文件的列表,类似Windows VC++中的CStringArray
//*返回=0表示失败;=1表示成功
//******************************************************************
bool CDataBase :: InsertWithLargeObject(long hdbc,QCString SqlStr,QStringList FileList)
{
    int iCount=FileList.count();
    QString s1;Oid tmpid;bool bret=1;QCString param;
    QPtrList<Oid> OidArray;OidArray.clear();
    PGresult *res = PQexec((PGconn*)hdbc, "begin");
    if(!res)
    {
	PQclear(res);return 0;
    }
    for(int i=0;i<iCount;i++)
    {
	s1=*(FileList.at(i));
	tmpid = lo_import((PGconn*)hdbc, s1.data());
	if(tmpid<=0) 
	{
	    bret=0;continue;
	}
	param.sprintf("%d",tmpid);
	SqlStr=FillParam(SqlStr,i+1,0,param);
	OidArray.append(&tmpid);
    }
    res = PQexec((PGconn*)hdbc, SqlStr.data());
    if (!res)
    {
       PQclear(res);bret=0;
	//记录插入失败,删除大对象
	for(int j=0;j<(int)OidArray.count();j++)
       {
	   lo_unlink((PGconn*)hdbc,*(OidArray.at(j)));
       }
    }
    res = PQexec((PGconn*)hdbc, "end");
    PQclear(res);
    return bret;
}

//******************************************************************
//*删除带有大对象的记录函数:Table=表名;WhereStr=删除条件;FieldList=存放大对象的字段数组
//*bDeleRow=1表示先删除大对象再删除对应的记录
//*返回=0表示失败;=1表示成功
//******************************************************************
bool CDataBase :: DeleteWithLargeObject(long hdbc,QCString Table,QCString WhereStr,QStringList FieldList,
					bool bForceDele,bool bDeleRow)
{
    QCString tmpsql,tmps; tmpsql="Select ";QString FieldName;
    for(int i=0;i<(int)FieldList.count();i++)
    {
	FieldName=*(FieldList.at(i));
	tmps.sprintf("%s",FieldName.data()); tmpsql+=tmps;
	if(i<(int)FieldList.count()-1)  tmpsql+=",";
    }
    tmpsql+=" from "+Table+" "+WhereStr;
    //开始数据库事务
    PGresult *res = PQexec((PGconn*)hdbc, "BEGIN");
    if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "BEGIN command failed\n");PQclear(res);return 0;
    }
    PQclear(res);
    //执行查询语句
    res = PQexec((PGconn*)hdbc,tmpsql.data());
    if(  PQresultStatus(res)  !=  PGRES_TUPLES_OK) 
    {
       fprintf(stderr,"Exec Query Fauled!\\n");PQclear(res);return 0;
    }
    int i = PQntuples(res); int t = PQnfields(res);Oid tmpOid=0;int ret=0;
    for(int s=0; s<i; s++)
    {
	ret=0;
       for (int k = 0; k<t; k++)
	{
	    tmps.sprintf("%s",PQgetvalue(res,s,k));
	    tmpOid=(Oid)tmps.toUInt();
	    if(tmpOid<=0) continue;      	     //该字段无对应的大对象
	    ret=lo_unlink((PGconn*)hdbc,tmpOid);       //删除大对象
	    if(ret<0&&bForceDele==0) break;          //删除失败
       }
       if(ret<0&&bForceDele==0) break;              //删除失败
    }
    PQclear(res); 
    //如果出错回滚否则继续
    if(ret<0&&bForceDele==0)
    {
	res = PQexec((PGconn*)hdbc, "ROLLBACK");
	if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
	{
	    fprintf(stderr, "ROLLBACK command failed\n");PQclear(res);return 0;
	}
	PQclear(res); 
    }
    //是否删除数据库记录
    if(bDeleRow)
    {
	tmpsql=" Delete from "+Table+" "+WhereStr; res = PQexec((PGconn*)hdbc,tmpsql.data());PQclear(res);
    }
    res = PQexec((PGconn*)hdbc, "COMMIT");
    if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
    {
       fprintf(stderr, "COMMIT command failed\n");PQclear(res);return 0;
    }
    PQclear(res);
    return 1;
}

//******************************************************************
//*修改带有大对象的记录函数:SqlStr=Update 表名 Set field1=?,field2=? where recordindex='111'
//*FileList=文件名数组
//*返回=0表示失败;=1表示成功
//******************************************************************
bool CDataBase :: UpdateLargeObject(long hdbc,QCString SqlStr,QStringList FileList)
{
    QCString Table,WhereStr;QStringList FieldList;QString s1;Oid tmpid;bool bret=1;QCString param;
    QPtrList<Oid> OidArray;OidArray.clear();
    //得到表名,字段,条件
    GetTableFromSql(SqlStr,Table,WhereStr,FieldList);
    //删除原来的大对象[强制+不删除记录]
    DeleteWithLargeObject(hdbc,Table,WhereStr,FieldList,1,0);
    //开始数据库事务
    PGresult *res = PQexec((PGconn*)hdbc, "begin");
    if(!res)
    {
	PQclear(res);return 0;
    }
    //保存数据库大对象
    int iCount=FileList.count();
    for(int i=0;i<iCount;i++)
    {
	s1=*(FileList.at(i));
	tmpid = lo_import((PGconn*)hdbc, s1.data());
	if(tmpid<=0) 
	{
	    bret=0;continue;
	}
	param.sprintf("%d",tmpid);
	SqlStr=FillParam(SqlStr,i+1,0,param);
	OidArray.append(&tmpid);
    }
    res = PQexec((PGconn*)hdbc, SqlStr.data());
    if (!res)
    {
       PQclear(res);bret=0;
	//记录插入失败,删除大对象
	for(int j=0;j<(int)OidArray.count();j++)
       {
	   lo_unlink((PGconn*)hdbc,*(OidArray.at(j)));
       }
    }
    res = PQexec((PGconn*)hdbc, "end");
    PQclear(res);
    return bret;
}