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

void  GetAllVisiter(int64 ParentID, vector<int64>& VisiterList,int64 NotIncludeChildID/*=0*/){

	char TableName[30];
	int64toa(ParentID,TableName);
	if(!GetWorldDB().tableExists(TableName)){
		return ;
	}

	CppSQLite3Buffer SQL;
	SQL.format("select %s from \"%s\" where  %s>%d",
		ITEM_ID,
		TableName,
		ITEM_TYPE,
		OBJECT_SPACE
		);

	CppSQLite3Query Result = GetWorldDB().execQuery(SQL);
	if(Result.eof())return ;

	while(!Result.eof()){
		int64  ChildID = Result.getInt64Field(0);
		if (ChildID != NotIncludeChildID)
		{
			VisiterList.push_back(ChildID);			
		}
		Result.nextRow();
	}
}

void  CBrainMemory::GetMemo(int64 RoomID, deque<tstring>& MemoList){
	int64 FatherID,ChildID,RoomType;
	char buf[30];
	CppSQLite3Buffer SQL;
	
	if(LBrainHasTable(RoomID)){
		//索引空间是否有表，有则表示其为其它空间的空间值
		//根据索引表得到其作为空间值时的空间存储ID，存入种子表
		ToLBrain(RoomID);
		int64toa(RoomID,buf);
		SQL.format("select %s,%s from  \"%s\"  ;",LB_FATHER_ID,LB_CHILD_ID,buf);
		CppSQLite3Table t0 = BrainDB.getTable(SQL);
		for (int row = 0; row < t0.numRows(); row++)
		{
			t0.setRow(row);
			FatherID = t0.getInt64Field(0);
			ChildID = t0.getInt64Field(1);
			if(ChildID == -RoomID)continue;
			RoomType = GetChildType(FatherID,ChildID);
			if(RoomType == MEMORY_LAN){ //看是否文本空间修饰RoomID
                tstring Text;
				if(RetrieveText(ChildID,Text)){
					MemoList.push_back(Text);
				}
			}
		}
	}
}

void CreateTable(int64 TableID)
{
	char     TableName[30]; 
    int64toa(TableID,TableName);
	CppSQLite3Buffer  SQL;          // 空间ID             空间名字         空间类型            空间指纹   空间属性   空间拥有者信息        
    SQL.format("CREATE TABLE \"%s\" ( a INTEGER NOT NULL ,b TEXT NOT NULL, c INTEGER NOT NULL,  d TEXT,   e TEXT,    f TEXT );",TableName);
    GetWorldDB().execDML(SQL);
}

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;
}

bool CSpace::Load(int64 ParentID,int64 ChildID){
	m_ID = ParentID;
	
	char     ParentName[30], ChildName[30]; 
	int64toa(ParentID,ParentName);
	int64toa(ChildID,ChildName);
	
	CppSQLite3Buffer SQL;
	SQL.format("select * from \"%s\" where %s = \"%s\" ",
		ParentName,
		ITEM_ID,
		ChildName  
	);

	CppSQLite3Query Result = GetWorldDB().execQuery(SQL);
	if(Result.eof()){
		return false;
	}
	assert(Size()>5);
	assert(GetDataType(0) == TYPE_INT);
	int64& OldID = *(int64*)GetData(0);
	OldID = ChildID;

	AnsiString AnsiName = Result.getStringField(1,"");	
	tstring Name = UTF8toWS(AnsiName);
	SetName(Name);

	SPACETYPE Type = (SPACETYPE)Result.getIntField(2);
	SetSpaceType(Type);

	AnsiString Fingerprint = Result.getStringField(3);
	tstring Fingerprint1 = UTF8toWS(Fingerprint);
    SetFingerprint(Fingerprint1);
	
	AnsiString AnsiProperty = Result.getStringField(4,"");     	
	ePipeline Property;
	uint32 pos =0;
	bool ret = Property.FromString(AnsiProperty,pos);
	if(!ret){
		return false;
	}
    SetProerty(Property);

	AnsiString AnsiOwner = Result.getStringField(5,"");
	ePipeline OwnerInfo;

	pos=0;
	ret = OwnerInfo.FromString(AnsiOwner,pos);
	if(!ret){
		return false;
	}  
	SetOwnerInfo(OwnerInfo);

	return true;
}

bool DBSetting::SetValue(long lKey,const std::string &strValue)
{
	//打开数据库
	CppSQLite3DB dbTask;
	dbTask.open(m_strDB.c_str());
	CheckCreateSettingTable(dbTask);

	CppSQLite3Buffer strSql;

	try{
		strSql.format("select count(*) from T_Setting where key=%d;",lKey);
		CppSQLite3Query q = dbTask.execQuery(strSql);
		if (0==q.getIntField(0))
		{
			strSql.format("insert into T_Setting(key,value) values(%d,'%q');",lKey,strValue.c_str());
			if(1!=dbTask.execDML(strSql))
			{
				ATLASSERT(FALSE);
				return false;
			}
		}
		else
		{
			strSql.format("update T_Setting set value = '%q' where key=%d;",strValue.c_str(),lKey);
			if(1!=dbTask.execDML(strSql))
			{
				ATLASSERT(FALSE);
				return false;
			}
		}

	}
	catch(CppSQLite3Exception &exp)
	{
		exp;
		ATLTRACE("error:%s\n",exp.errorMessage());
		ATLASSERT(FALSE);
		return false;
	}
	return true;
}

void CBrainMemory::InsertRow(int64 TableName,int64 one, int64 two, int64 three)
{
    CppSQLite3Buffer SQL;
	char a[30],b[30],c[30],d[30];
    int64toa(TableName,a);
	int64toa(one,b);
	int64toa(two,c);
	int64toa(three,d);
    SQL.format("insert into \"%s\" values (\"%s\", \"%s\", \"%s\")",a,b,c,d);

    BrainDB.execDML(SQL);
}

/*
-注意：由于是递归，外部调用着应该考虑用事物回滚
*/
void DeleteAllChild(int64 ParentID)
{
	char     ParentName[30];   	
    int64toa(ParentID,ParentName);
    if(!GetWorldDB().tableExists(ParentName))return;  //指定空间没有子空间
	
	CppSQLite3Buffer SQL;
	
	//首先找到所有子空间的地址
	vector<int64> AddressList;
	
	SQL.format("select %s from \"%s\" ",
		ITEM_ID,
		ParentName
		);
	CppSQLite3Query Result = GetWorldDB().execQuery(SQL);
	while(!Result.eof()){
		int64 ChildID  = Result.getInt64Field(0);
		AddressList.push_back(ChildID);
		Result.nextRow();
	}
	
	   
	//根据地址，执行递归操作
	for(uint32 i=0; i<AddressList.size();i++)
	{
        int64 ID = AddressList[i];
		DeleteAllChild(ID);
	}
	
	//最后删除子空间的所在的容器（表）
    if(AddressList.size() != 0){
		SQL.format("drop table \"%s\" ",ParentName);
		GetWorldDB().execDML(SQL);
	}
	
}

bool HasChild(int64 ParentID)
{
	 if(!HasTable(ParentID))return false;
	
	 char       TableName[30]; 
    int64toa(ParentID,TableName);

     CppSQLite3Buffer SQL;
	 SQL.format("select %s from \"%s\" ",ITEM_ID,TableName);
	
	 CppSQLite3Query Result;
	 Result = GetWorldDB().execQuery(SQL);
     if(Result.eof())return false;
	 return true;
};

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;
}

SpaceAddress  FindSpace(ePipeline& Path){
	SpaceAddress Addr;
	
	if (Path.Size()==0)
	{
		Addr.ParentID = ROOT_SPACE;
		Addr.ChildID  = LOCAL_SPACEID;
		return Addr;
	}
	
	CppSQLite3Buffer SQL;
	
	int64 ParentID = 0;
	int64 ChildID  = ROOT_SPACE;
	
	char       ParentName[30]; 
	while (Path.Size())
	{
		tstring Name = Path.PopString();
		AnsiString AnsiName = WStoUTF8(Name);
		
		ParentID = ChildID;
		ChildID  = 0;
		
		int64toa(ParentID,ParentName);
		
		SQL.format("select %s from \"%s\" where %s=\"%s\"",
			ITEM_ID,
			ParentName,
			ITEM_NAME,
			AnsiName.c_str()
			);
		CppSQLite3Query Result = GetWorldDB().execQuery(SQL);
		
		if(!Result.eof()){
			ChildID  = Result.getInt64Field(0);
			Result.nextRow();
		}else{
			return  Addr;
		}
	}
	
	assert(Path.Size()==0);
	
	Addr.ParentID = ParentID;
	Addr.ChildID  = ChildID;
    return Addr;	
}

int32 GetAllChildList(int64 ParentID, ePipeline& ChildList,int64 NotIncludeChildID /*=0*/){
    assert(ParentID >0);
	ChildList.Clear();

	char TableName[30];
	int64toa(ParentID,TableName);
	if(!GetWorldDB().tableExists(TableName)){
		return 0;
	}

	//ChildList.SetID(ParentID);

    CppSQLite3Buffer SQL;
	SQL.format("select %s,%s,%s,%s from \"%s\"",
		     ITEM_ID,
		     ITEM_NAME,
			 ITEM_TYPE,
			 ITEM_FINGERPRINT,
		     TableName
	);

	CppSQLite3Query Result = GetWorldDB().execQuery(SQL);
	
	while(!Result.eof()){
		int64      ChildID     = Result.getInt64Field(0);
		
		if (ChildID !=NotIncludeChildID)
		{
			AnsiString s    = Result.getStringField(1,"");
			
			tstring Name = UTF8toWS(s);
			Name = GetFileNoPathName(Name);
			Name = GetFileName(Name);
			
			int32      Type        = Result.getIntField(2);
			AnsiString FingerPrint = Result.getStringField(3,"");

			//ChildList.PushInt64(ChildID);
			ChildList.PushString(Name);
			ChildList.PushInt(Type);
			ChildList.PushString(FingerPrint);

		}
		Result.nextRow();
	}
	return ChildList.Size()/3;
}

void DeleteChild(int64 ParentID,int64 ChildID)
{

	DeleteAllChild(ChildID);

	char     FatherName[30], ChildName[30]; 
	int64toa(ParentID,FatherName);
	int64toa(ChildID,ChildName);   

	CppSQLite3Buffer SQL;

	//在父空间里删除本空间条目
	SQL.format("delete from \"%s\" where %s = \"%s\" ",
		FatherName,
		ITEM_ID,
		ChildName);
	GetWorldDB().execDML(SQL);
}

CBrainMemory::CBrainMemory()
{
	// TODO: add one-time construction code here
	Open();

	assert(BrainDB.IsOpen());

	//首先生成ROOT_SPACE，似乎没有必要，当生成第一个空间时，会自动生成。  
	int64 RoomID = ROOT_SPACE;   
	if(!RBrainHasTable(RoomID))CreateRBrainTable(RoomID);
	RoomID = ROOM_SYSTEM;
	if(!HasTable(RoomID)){
		CppSQLite3Buffer SQL;
		char TableName[30];
        int64toa(RoomID,TableName);
		SQL.format("CREATE TABLE \"%s\" ( a INTEGER NOT NULL ,b TEXT NOT NULL);",TableName);
		BrainDB.execDML(SQL);
	}
}

void CBrainMemory::CreateTable(int64 ID)
{
	CppSQLite3Buffer SQL;
	char TableName[30];
    int64toa(ID,TableName);
/*
关于：【记忆空间主键冲突问题】 16:54 2012/8/30
	索引空间的第三栏原本设置为主键唯一，但是当两个不同语言的单词指向同一个意义空间时，并且大家都是NOUN_MEANING时，
	此时意义空间值=意义空间ID，而意义空间值又是索引空间表名，这样，索引表的第三个数据就会相同，产生主键冲突。

     目前取消主键唯一，还不清楚会产生何种影响，大概会影响信息回取结果的唯一性

    if(ID<0)
		SQL.format("CREATE TABLE \"%s\" ( a INTEGER NOT NULL ,b INTEGER  NOT NULL,c INTEGER NOT NULL PRIMARY KEY ON CONFLICT FAIL);",TableName);
    else 
*/
	SQL.format("CREATE TABLE \"%s\" ( a INTEGER NOT NULL ,b INTEGER  NOT NULL,c INTEGER NOT NULL);",TableName);
	BrainDB.execDML(SQL);
}