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();
}
catch( CppSQLite3Exception& e )
{
XDEBUG("%s\r\n",e.errorMessage());
return e.errorCode();
}
return SQLITE_OK;
}
