db.cpp

离线的RSS阅读器

	strcat(sql,itoa(rootID,buffer,10));
	strcat(sql,";");

	sqlite3_get_table(db,sql,&szResult,&nrow,&ncol,NULL);
	//如果没有数据,则创建的节点不是一个目录节点,继续查找频道表
	if (nrow==0)
	{
		strcpy(sql,"select * from ChannelList where id=");
		strcat(sql,itoa(rootID,buffer,10));
		strcat(sql,";");
		sqlite3_free_table(szResult);

		sqlite3_get_table(db,sql,&szResult,&nrow,&ncol,NULL);

		//如果仍然没有记录,在收藏中查找.
		if (!nrow)
		{
			strcpy(sql,"select * from CollectionList where id=");
			strcat(sql,itoa(rootID,buffer,10));
			strcat(sql,";");
			sqlite3_free_table(szResult);

			sqlite3_get_table(db,sql,&szResult,&nrow,&ncol,NULL);

			if (!nrow)
			{
				return NULL;
			}

			CollectionNode *result = new CollectionNode(*(szResult+ncol+2),*(szResult+ncol+1),atoi(*(szResult+ncol)));
			//递归调用,获取下一个节点

			
			result->nextSubling = DBBuildTree(atoi(*(szResult+ncol+3)),db);
			result->id = rootID;
			sqlite3_free_table(szResult);
			return result;
		}
		else{
			ChannelNode *result = new ChannelNode(*(szResult+ncol+1),*(szResult+ncol+3),*(szResult+ncol+4),atoi(*(szResult+ncol)));
			//递归调用,获取下一个节点
			result->nextSubling = DBBuildTree(atoi(*(szResult+ncol+2)),db);
			result->id = rootID;
			sqlite3_free_table(szResult);
			return result;
		}
		
		//创建树节点
		
	}
	//目录节点
	else{
		DirNode *result = new DirNode(*(szResult+ncol+3),atoi(*(szResult+ncol+2)));
		//result->type = atoi(*(szResult+4));
		//递归调用,获取下一个节点
		result->nextSubling = DBBuildTree(atoi(*(szResult+ncol+4)),db);
		result->firstChild =  DBBuildTree(atoi(*(szResult+ncol+1)),db);
		sqlite3_free_table(szResult);
		result->id = rootID;
		return result;
	}
	sqlite3_close(db);
	return NULL;
}

//获取下一个可用的id(频道和目录)，为当前最大的id+1
int getMaxID(sqlite3 *){
	sqlite3 *db;
	sqlite3_open(gszFile,&db);
	char **szResult;
	int nrow = 0,ncol = 0;
	sqlite3_get_table(db,"select id from DirList order by id desc",&szResult,&nrow,&ncol,NULL);
	int i = 0;
	if (nrow>0)
	{
		i = atoi(*(szResult+ncol));
	}
	sqlite3_get_table(db,"select id from ChannelList order by id desc",&szResult,&nrow,&ncol,NULL);
	int k = 0;
	if (nrow>0)
	{
		k = atoi(*(szResult+ncol));
	}
	sqlite3_close(db);
	return (i>k)?i:k;
}

//拖动树后改变节点id
void DBUpdateItem(DirNode *parent,MyTreeNode *insItem,sqlite3 *){
	sqlite3 *db;
	sqlite3_open(gszFile,&db);
	//critical_section.Lock();
	char sql[1024];
	if (insItem==parent->firstChild)
	{
		int id = -1;
		if (insItem)
		{
			id = insItem->id;
		}
		sprintf(sql,"update DirList set firstChildID=%d where id=%d",id,parent->id);
		sqlite3_exec(db,sql,NULL,NULL,NULL);
		if (!insItem)
		{
			return;
		}
		int nextChildId = -1;
		if (insItem->nextSubling)
		{
			nextChildId = insItem->nextSubling->id;
		}
		sprintf(sql,"update DirList set nextSublingID=%d where id=%d",nextChildId,insItem->id);
		sqlite3_exec(db,sql,NULL,NULL,NULL);
		sprintf(sql,"update channelList set nextSublingID=%d where id=%d",nextChildId,insItem->id);
		sqlite3_exec(db,sql,NULL,NULL,NULL);
	}
	else{
		MyTreeNode *child = parent->firstChild;
		while(child->nextSubling!=insItem){
			child = child->nextSubling;
		}

		int nextChildID = -1;
		if (insItem->nextSubling)
		{
			nextChildID = insItem->nextSubling->id;
		}
		sprintf(sql,"update DirList set nextSublingID=%d where id=%d",insItem->id,child->id);
		sqlite3_exec(db,sql,NULL,NULL,NULL);
		sprintf(sql,"update ChannelList set nextSublingID=%d where id=%d",insItem->id,child->id);
		sqlite3_exec(db,sql,NULL,NULL,NULL);
		sprintf(sql,"update DirList set nextSublingID=%d where id=%d",nextChildID,insItem->id);
		sqlite3_exec(db,sql,NULL,NULL,NULL);
		sprintf(sql,"update channelList set nextSublingID=%d where id=%d",nextChildID,insItem->id);
		sqlite3_exec(db,sql,NULL,NULL,NULL);
	}
	sqlite3_exec(db,"commit transaction",NULL,NULL,NULL);
	//critical_section.Unlock();

	sqlite3_close(db);
}

void DBSetItemRead(int id,sqlite3 *){
	sqlite3 *db;
	sqlite3_open(gszFile,&db);
	char sql[1024];
	sprintf(sql,"update itemlist set isRead=1 where id = %d",id);
	sqlite3_exec(db,sql,NULL,NULL,NULL);
	sqlite3_close(db);
}

int getMaxItemID(sqlite3 *){
	sqlite3 *db;
	sqlite3_open(gszFile,&db);
	critical_section.Lock();
	char **szResult;
	int nrow = 0,ncol = 0;
	sqlite3_get_table(db,"select id from ItemList order by id desc",&szResult,&nrow,&ncol,NULL);
	int i = 0;
	if (nrow>0)
	{
		i = atoi(*(szResult+ncol));
	}
	sqlite3_close(db);
	return i;
}

void _DBImportChannelList(MyTreeNode *root,sqlite3 *db){
	
	if (!root)
	{
		return;
	}
	if (root->type==1)
	{
		ChannelNode *data = (ChannelNode*)root;
		char sql[256];
		int nextSublingID = -1;
		if (data->nextSubling)
		{
			nextSublingID = data->nextSubling->id;
		}
		sprintf(sql,"insert into channelList(id,name,nextsublingID,url,pubTime) values(%d,'%s',%d,'%s','%s')",data->id,data->name,nextSublingID,data->url,data->pubTime);
		sqlite3_exec(db,sql,NULL,NULL,NULL);
	}
	else{
		DirNode *dir = (DirNode*)root;
		char *sql = new char[256];
		int nextSublingID = -1;
		if (dir->nextSubling)
		{
			nextSublingID = dir->nextSubling->id;
		}
		int firstChildID = -1;
		if (dir->firstChild)
		{
			firstChildID = dir->firstChild->id;
		}
		sprintf(sql,"insert into DirList(type,firstChildID,ID,name,nextSublingID) values(%d,%d,%d,'%s',%d)",0,firstChildID,dir->id,dir->name,nextSublingID);
		sqlite3_exec(db,sql,NULL,NULL,NULL);
		delete[] sql;
		MyTreeNode *child = dir->firstChild;
		while (child)
		{
			_DBImportChannelList(child,db);
			child = child->nextSubling;
		}
	}
	
}

void DBImportChannelList(void *root){
	//critical_section.Lock();
	sqlite3 *db;
	sqlite3_open(gszFile,&db);
	if (!root)
	{
		return;
	}
	sqlite3_exec(db,"begin transaction",NULL,NULL,NULL);
	MyTreeNode *mRoot = (MyTreeNode*)root;
	mRoot->id = 0;
	sqlite3_exec(db,"delete from channelList",NULL,NULL,NULL);
	sqlite3_exec(db,"delete from DirList where type = 0",NULL,NULL,NULL);
	sqlite3_exec(db,"delete from ItemList",NULL,NULL,NULL);

	while (mRoot)
	{
		_DBImportChannelList(mRoot,db);
		mRoot = mRoot->nextSubling;
	}
	sqlite3_exec(db,"commit transaction",NULL,NULL,NULL);
//	sqlite3_exec(db,"commit transaction",NULL,NULL,NULL);
	//critical_section.Unlock();
	sqlite3_close(db);
}

ItemList *DBFindAll(char *key){
	sqlite3 *db;
	sqlite3_open(gszFile,&db);
	char sql[1024];
	//sprintf(sql,"select * from itemlist where title like '%%%s%%'",key);
	sprintf(sql,"select * from itemlist where title like '");
	strcat(sql,"%");
	strcat(sql,key);
	strcat(sql,"%'");
	
	char **szResult;
	int nrow = 0,ncol = 0;

	sqlite3_get_table(db,sql,&szResult,&nrow,&ncol,NULL);
	ItemList *result = NULL;
	//创建一个ItemList链,并且把最后一个指向NULL
	if (nrow>0)
	{
		result = new ItemList;
		ItemList *last = result;
		for (int i=1;i<nrow;i++)
		{
			last->nextItem = new ItemList;
			last = last->nextItem;
		}
		last->nextItem = NULL;
	}
	else{
		sqlite3_close(db);
		return NULL;
	}

	ItemList *cur = result;
	//每一条记录初始化ItemList链里面的一项
	for (int i=0;i<nrow;i++,cur = cur->nextItem)
	{
		cur->author = new char[strlen(*(szResult+ncol*(i+1)))+1];
		strcpy(cur->author,*(szResult+ncol*(i+1)));
		cur->description = new char[strlen(*(szResult+ncol*(i+1)+2))+1];
		strcpy(cur->description,*(szResult+ncol*(i+1)+2));

		cur->isRead = atoi(*((szResult+ncol*(i+1))+4));

		cur->time = new char[strlen(*(szResult+ncol*(i+1)+5))+1];
		strcpy(cur->time,*(szResult+ncol*(i+1)+5));

		cur->title = new char[strlen(*(szResult+ncol*(i+1)+6))+1];
		strcpy(cur->title,*(szResult+ncol*(i+1)+6));

		cur->url = new char[strlen(*(szResult+ncol*(i+1)+7))+1];
		strcpy(cur->url,*(szResult+ncol*(i+1)+7));

		cur->id = atoi(*(szResult+ncol*(i+1)+3));
	}
	ItemList *last = (ItemList*)(result+(nrow-1));
// 	if (last)
// 	{
// 		last->nextItem = NULL;
// 	}
	//critical_section.Unlock();
	sqlite3_close(db);
	return result;
}