C++ CppSQLite3Buffer::clear方法代码示例

int CDbMeter::GroupDelete(const char* dbfilename, int nGroupKey)
{
	try
	{
		CppSQLite3DB db;
		db.open(dbfilename);
		// 그룹테이블에 그룹이름이 있느지 확인
		CppSQLite3Buffer bufSQL;
		//CppSQLite3Query q;
		CppSQLite3Table t;

		bufSQL.format("SELECT GroupKey FROM groups WHERE GroupKey=%d;", nGroupKey);
		t = db.getTable(bufSQL);

		if( t.numRows() == 0 )
		{
			XDEBUG("ERROR: %d GroupKey doesn't exist in Groups Table!!\r\n", nGroupKey);
			return IF4ERR_GROUP_NAME_NOT_EXIST;
		}
		else
		{
			bufSQL.clear();
			bufSQL.format("DELETE FROM groups WHERE GroupKey=%d;", nGroupKey);
			db.execDML(bufSQL);

			XDEBUG("%d GroupKey deleted in Groups Table!!\r\n", nGroupKey);
			//bufSQL.format("SELECT Groups.GroupName FROM groupmember LEFT OUTER JOIN groups ON groupmember.GroupKey=groups.GroupKey LEFT OUTER JOIN member ON groupmember.EUI64ID=member.EUI64ID WHERE groups.GroupName = %s;", szGroupName);
		}

		bufSQL.clear();
		bufSQL.format("SELECT GroupKey FROM groupmember WHERE GroupKey=%d;", nGroupKey);
		t = db.getTable(bufSQL);

		if( t.numRows() == 0 )
		{
			XDEBUG("ERROR: %d GroupKey doesn't exist!! in Groupmember Table!!\r\n", nGroupKey);
			return IF4ERR_GROUP_NAME_NOT_EXIST;
		}
		else
		{
			bufSQL.clear();
			bufSQL.format("DELETE FROM groupmember WHERE GroupKey=%d;", nGroupKey);
			db.execQuery(bufSQL);
			XDEBUG("%d GroupKey deleted in Groupmember Table!!\r\n", nGroupKey);
		}
		db.close();
	}
	catch( CppSQLite3Exception& e )
	{
		XDEBUG("%s\r\n",e.errorMessage());
		return e.errorCode();
	}
	return SQLITE_OK;
}

int CDbMeter::JoinTableDelete(const char* dbfilename, char* szID)
{
	try
	{
		CppSQLite3DB db;
		db.open(dbfilename);
		CppSQLite3Buffer bufSQL;
		CppSQLite3Table t;

		db.execDML("CREATE TABLE IF NOT EXISTS jointable (joinkey INTEGER PRIMARY KEY, EUI64ID CHAR(17), jointry INTEGER);");
		bufSQL.format("SELECT * FROM jointable WHERE EUI64ID=%Q", szID);
		t = db.getTable(bufSQL);

		if( t.numRows() == 0 )
		{
			XDEBUG("Failure: Don't have JoinTable %s\r\n", szID);
		}
		else
		{
			bufSQL.clear();
			bufSQL.format("DELETE FROM jointable WHERE EUI64ID=%Q;", szID);
			db.execQuery(bufSQL);
		}
	}
	catch( CppSQLite3Exception& e )
	{
		XDEBUG("%s\r\n",e.errorMessage());
		return e.errorCode();
	}
	return SQLITE_OK;
}

int CDbMeter::JoinTableAdd(const char* dbfilename, char* szID)
{
	try
	{
		CppSQLite3DB db;
		db.open(dbfilename);
		CppSQLite3Buffer bufSQL;
		CppSQLite3Table t;

		db.execDML("CREATE TABLE IF NOT EXISTS jointable (joinkey INTEGER PRIMARY KEY, EUI64ID CHAR(17), jointry INTEGER);");
		bufSQL.format("SELECT * FROM jointable WHERE EUI64ID=%Q", szID);
		t = db.getTable(bufSQL);
		if( t.numRows() == 0 )
		{
			bufSQL.clear();
			bufSQL.format("INSERT INTO jointable(EUI64ID, jointry) VALUES(%Q, %d);", szID, 0);
			db.execDML(bufSQL);
		}
		else
			XDEBUG("Jointable ID Duplicate!!\r\n");

		db.close();
	}
	catch( CppSQLite3Exception& e )
	{
		XDEBUG("%s\r\n",e.errorMessage());
		return e.errorCode();
	}
	return SQLITE_OK;
}

int CDbMeter::GroupAddMember(const char* dbfilename, int nGroupKey, char* szMemberId)
{
	try
	{
		CppSQLite3DB db;
		db.open(dbfilename);
		CppSQLite3Buffer bufSQL;
		CppSQLite3Query q;
		CppSQLite3Table t;
		int nFindgroupkey = -1;

		bufSQL.format("SELECT EUI64ID FROM member WHERE EUI64ID=%Q;", szMemberId);
		t = db.getTable(bufSQL);
	
		if( t.numRows() == 0 )
		{
			bufSQL.clear();
			bufSQL.format("INSERT INTO member VALUES(%Q, 'Meter', 'none', '1.0.0', '1.0.0', 'Nomal', 'None');", szMemberId);
			db.execDML(bufSQL);
		}

		bufSQL.clear();
		bufSQL.format("SELECT * FROM groups WHERE GroupKey=%d;", nGroupKey);
		t = db.getTable(bufSQL);
		if( t.numRows() == 1)
			nFindgroupkey = atoi(t.fieldValue(0));
		else
			nFindgroupkey = -1;

		if( nFindgroupkey >= -1)
		{
			bufSQL.clear();
			bufSQL.format("INSERT INTO groupmember(EUI64ID, GroupKey) VALUES(%Q, %d);", szMemberId, nGroupKey);
			db.execDML(bufSQL);
		}

		db.close();
	}
	catch( CppSQLite3Exception& e )
	{
		XDEBUG("%s\r\n",e.errorMessage());
		return -1;
	}

	return SQLITE_OK;
}

//////////////////////////////////////////////////////////////////////////
//  [3/23/2011 DHKim]
//  성공적으로 그룹테이블에 그룹이 추가되면 GroupKey를 리턴한다. 실패시 -1을 리턴
//////////////////////////////////////////////////////////////////////////
int CDbMeter::GroupAdd(const char* dbfilename, char* szGroupName)
{
	int nGroupKey = -1;
	try
	{
		CppSQLite3DB db;
		db.open(dbfilename);
		CppSQLite3Buffer bufSQL;
		CppSQLite3Table t;

		bufSQL.format("INSERT INTO groups(GroupName) VALUES(%Q);", szGroupName);
		db.execDML(bufSQL);
		XDEBUG("Success: INSERT Command!!\r\n");

		bufSQL.clear();
		bufSQL.format("SELECT GroupKey FROM groups WHERE GroupName=%Q;", szGroupName);
		//CppSQLite3Query q = db.execQuery(bufSQL);
		t = db.getTable(bufSQL);
		
		if( t.numRows() == 0 )
		{
			XDEBUG("Failure: SELECT Command!!\r\n");
			return nGroupKey;
		}
		else
		{
			// 동일 이름시 마지막에 추가된 그룹 키를 리턴한다.
			int nFinalRow = t.numRows() - 1;
			t.setRow(nFinalRow);
			if( !t.fieldIsNull(nFinalRow) )
				nGroupKey = atoi(t.fieldValue(nFinalRow));
			else
			{
				XDEBUG("Error: GroupKey is NULL!!\r\n");
				return nGroupKey;
			}
			XDEBUG("Success: SELECT Command!!\r\n");
		}

		//nGroupKey = q.getIntField("GroupKey", -1);
		
		db.close();
	}
	catch( CppSQLite3Exception& e )
	{
		XDEBUG("%s\r\n",e.errorMessage());
		return -1;
	}

	return nGroupKey;
}

int CDbMeter::GroupDeleteMember(const char* dbfilename, int nGroupKey, char* szMemberId)
{
	try
	{
		CppSQLite3DB db;
		db.open(dbfilename);
		CppSQLite3Buffer bufSQL;
		//CppSQLite3Query q;
		CppSQLite3Table t;

		bufSQL.format("SELECT * FROM groupmember WHERE GroupKey=%d AND EUI64ID=%Q;", nGroupKey, szMemberId);
		//q = db.execQuery(bufSQL);
		t = db.getTable(bufSQL);

		if( t.numRows() == 0 )
		{
			XDEBUG("ERROR: %s EUI64ID doesn't exist in %d Groupkey Table!!\r\n", szMemberId, nGroupKey);
			return IF4ERR_GROUP_NAME_NOT_EXIST;
		}
		else
		{
			bufSQL.clear();
			bufSQL.format("DELETE FROM groupmember WHERE GroupKey=%d AND EUI64ID=%Q;", nGroupKey, szMemberId);
			db.execQuery(bufSQL);
			XDEBUG("%s Groupmember of %d GroupKey deleted in Groupmember Table!!\r\n", szMemberId, nGroupKey);
		}
		db.close();
	}
	catch( CppSQLite3Exception& e )
	{
		XDEBUG("%s\r\n",e.errorMessage());
		return e.errorCode();
	}

	return SQLITE_OK;
}

int CDbMeter::JoinTableFileAdd(const char* dbfilename, char* szfilename)
{
	try
	{
		CppSQLite3DB db;
		db.open(dbfilename);
		CppSQLite3Buffer bufSQL;
		CppSQLite3Table t;
		FILE *fp;
		vector<char *> bufEUI64ID;
		char pszBuffer[64] = {0, };
		char * buff;
		int bufcnt = 0;
		//vector<char *>::iterator iter;

		db.execDML("CREATE TABLE IF NOT EXISTS jointable (joinkey INTEGER PRIMARY KEY, EUI64ID CHAR(17), jointry INTEGER);");
		
		fp = fopen(szfilename, "rt");

		if(fp != NULL)
		{
			while(fgets(pszBuffer, 64, fp))
			{
				if(strcmp(pszBuffer, "\n") == 0 || strcmp(pszBuffer, "\0") == 0)
					break;
				buff = new char[strlen(pszBuffer)+1];
				strcpy(buff, pszBuffer);
				XDEBUG("Get Line: %s\r\n", pszBuffer);
				//pszBuffer[strlen(pszBuffer)] = '\0';
				bufEUI64ID.push_back(buff);
				XDEBUG("Read File: %s\r\n", bufEUI64ID[bufcnt]);
				bufcnt++;
			}
			fclose(fp);
		}
		else
			XDEBUG("ERROR: No Such File Name!!\r\n");

		db.execDML("BEGIN TRANSACTION;");

		//for(iter = bufEUI64ID.begin(); iter != bufEUI64ID.end(); iter++)
		for(unsigned int i = 0; i < bufEUI64ID.size(); i++)
		{
			bufSQL.clear();
			XDEBUG("bufEUI64ID: %s\r\n", bufEUI64ID[i] );
			bufSQL.format("INSERT INTO jointable(EUI64ID, jointry) VALUES(%Q, %d);", bufEUI64ID[i], 0);
			db.execDML(bufSQL);
		}

		db.execDML("COMMIT TRANSACTION;");

		delete buff;

	}
	catch( CppSQLite3Exception& e )
	{
		XDEBUG("%s\r\n",e.errorMessage());
		return e.errorCode();
	}
	return SQLITE_OK;
}

int CDbMeter::GroupCommand(const char* dbfilename, int nGroupKey, vector<char*> vbufMember, MIBValue* pValue, int cnt)
{

	try
	{
		CppSQLite3DB db;
		db.open(dbfilename);
		CppSQLite3Buffer bufSQL;
		CppSQLite3Query q;
		CppSQLite3Table t;
		FILE *fp;
		char szCmd[12] = {0, };
		char szFileName[20] = {0, };

		bufSQL.format("SELECT groups.GroupKey, groups.GroupName, groupmember.EUI64ID FROM groups LEFT OUTER JOIN groupmember ON groups.GroupKey=groupmember.GroupKey WHERE groupmember.GroupKey=%d;", nGroupKey);
		t = db.getTable(bufSQL);

		for ( int row = 0; row < t.numRows(); row++)
		{
			t.setRow(row);
			if( !t.fieldIsNull(1))
			{
				VARAPI_OidToString(&pValue[1].stream.id, szCmd);

				bufMember.push_back((char*)t.fieldValue(2));
				XDEBUG("%d | %s | %s\r\n", atoi(t.fieldValue(0)), t.fieldValue(1), t.fieldValue(2) );

				bufSQL.clear();
				bufSQL.format("SELECT datetime('now', 'localtime');");
				q = db.execQuery(bufSQL);

				sprintf(szFileName,"/app/sqlite/%s.txt", t.fieldValue(2));
				fp = fopen(szFileName,"a+t");

				for (int paramcnt=2; paramcnt<cnt; paramcnt++)
				{
					if( VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.11") == 0 )
					{
						fprintf(fp, "%s", pValue[paramcnt].stream.p);
					}
					else if( VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.5") == 0 || VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.8") == 0 )
					{
						fprintf(fp, "%d", pValue[paramcnt].stream.u16);
					}
					else if( VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.6") == 0 || VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.9") == 0)
					{
						fprintf(fp, "%d", pValue[paramcnt].stream.u32);
					}
					else 
					{
						fprintf(fp, "%d", pValue[paramcnt].stream.u8);
					}

					if(paramcnt < cnt - 1)
						fprintf(fp, "|");
				}
				fprintf(fp,"\n");
				fclose(fp);
				XDEBUG("Success: File write!!\r\n");

				bufSQL.clear();
				XDEBUG("CMD: %s\r\n", szCmd);
				bufSQL.format("INSERT INTO command(GroupKey, EUI64ID, GroupName, CommandState, Command, CommandCreateTime, ResultErrorCode, ParamPathInfo) VALUES(%d, %Q, %Q, %d, %s, %Q, %d, %Q);", nGroupKey, t.fieldValue(2), t.fieldValue(1), 1, szCmd, q.fieldValue(0), 0, "CmdParamPathInfo.txt" );
				db.execDML(bufSQL);
			}
		}
		db.close();
	}
	catch( CppSQLite3Exception& e )
	{
		XDEBUG("%s\r\n",e.errorMessage());
		return e.errorCode();
	}
	return SQLITE_OK;
}

int CDbMeter::GroupInfo(const char* dbfilename, GROUPINFO *Group)
{
	try
	{
		CppSQLite3DB db;
		db.open(dbfilename);
		CppSQLite3Buffer bufSQL;
		CppSQLite3Table t, t1, t2;

		EUI64				memberID;
		char				szID[20] = {0,};
		char*				pMember = NULL;
		int					nMemCnt = 0;

		bufSQL.format("SELECT groups.GroupKey, groups.GroupName, groupmember.EUI64ID FROM groups LEFT OUTER JOIN groupmember ON groups.GroupKey=groupmember.GroupKey;");
		t = db.getTable(bufSQL);

		for( int fld = 0; fld < t.numFields(); fld++)
			XDEBUG("%s | ", t.fieldName(fld));
		XDEBUG("\r\n");

		Group->nTotalCnt = t.numRows();
		bufSQL.clear();
		bufSQL.format("SELECT * FROM groups;");
		t2 = db.getTable(bufSQL);
		Group->nTotalGroupCnt = t2.numRows();

		Group->nGroupKey = (int *)MALLOC(sizeof(int)*Group->nTotalCnt);
		Group->pGroupName[0] = (char *)MALLOC(GROUP_MEMBER_SIZE*Group->nTotalCnt);
		Group->nMemberCnt = (int *)MALLOC(sizeof(int)*Group->nTotalCnt);
		Group->pMemberID = (EUI64 *)MALLOC(sizeof(EUI64)*Group->nTotalCnt);
		pMember = (char *)MALLOC(16*Group->nTotalCnt);
		memset(pMember, 0, 16*Group->nTotalCnt);
		for(int j=0; j < Group->nTotalCnt; j++)
		{
			Group->nGroupKey[j] = -1;
			Group->nMemberCnt[j] = 0;
		}

		for ( int row = 0; row < t.numRows(); row++)
		{
			t.setRow(row);
			for (int fld=0; fld<t.numFields(); fld++)
			{
				if( !t.fieldIsNull(fld))
				{
					switch(fld)
					{
					//	GroupKey
					case 0:
						if( row == 0 || Group->nGroupKey[row - 1] != atoi( t.fieldValue(fld) ) )
						{
							Group->nGroupKey[row] = atoi( t.fieldValue(fld) );
							bufSQL.format("SELECT * FROM groupmember WHERE Groupkey = %d;", Group->nGroupKey[row]);
							t1 = db.getTable(bufSQL);
							Group->nMemberCnt[row] = t1.numRows();
						}
						else
							Group->nGroupKey[row] = atoi( t.fieldValue(fld) );
						break;
					//	GroupName
					case 1:
						memset( &(Group->pGroupName[0][row*GROUP_MEMBER_SIZE]), 0, sizeof(GROUP_MEMBER_SIZE));
						memcpy( &(Group->pGroupName[0][row*GROUP_MEMBER_SIZE]), t.fieldValue(fld), strlen( t.fieldValue(fld) )+ 1);
						break;
					//	MemberID
					case 2:
						StrToEUI64(t.fieldValue(fld), &memberID);
						if( Group->nMemberCnt[row] !=0 )
							memcpy( &(Group->pMemberID[row]), &memberID, sizeof(EUI64));
						else
							memset( &(Group->pMemberID[row]), 0, sizeof(EUI64));
						break;
					}
					//XDEBUG("%s | ", t.fieldValue(fld));
				}
			}
			EUI64ToStr(&(Group->pMemberID[row]), szID);
			XDEBUG("GroupName: %s\r\n", &(Group->pGroupName[0][row*GROUP_MEMBER_SIZE]));
			if(Group->nMemberCnt[row] != 0)
			{
				memcpy( &pMember[row*16], szID, strlen(szID));
				XDEBUG("pMember: %s\r\n", pMember);
				nMemCnt++;
				if( nMemCnt < Group->nMemberCnt[row] )
					Group->nMemberCnt[row+1] = Group->nMemberCnt[row];
				if( nMemCnt == Group->nMemberCnt[row] )
				{
					XDEBUG("Total Cnt: %d\r\n", Group->nTotalCnt);
					XDEBUG("GroupKey: %d\r\n", Group->nGroupKey[row]);
					XDEBUG("Member ID: %s\r\n", pMember);
					nMemCnt = 0;
				}
			}
			//XDEBUG("Total Cnt: %d, nGroupKey: %d, pGroupName: %s, nMemberCnt: %d, pMember: %s", Group.nTotalCnt, Group.nGroupKey, &(Group.pGroupName[0][row]), Group.nMemberCnt, szID );
			XDEBUG("\r\n");
		}

		db.close();
	}
//.........这里部分代码省略.........