db.cpp

离线的RSS阅读器

#include "StdAfx.h"
#include "db.h"
#include <afxmt.h>

CCriticalSection critical_section;
sqlite3 *db;
sqlite3 *channelDB;

char* gszFile;

//根据频道id获取频道内所有的项
ItemList* DBGetItemById(int id,sqlite3 *){
	//critical_section.Lock();
	//
	//保存临时数据
	sqlite3 *db;
	sqlite3_open(gszFile,&db);

	char sql[4*1024];
	char buffer[8];
	char **szResult;
	int nrow = 0,ncol = 0;

	//查询的sql语句
	sprintf(sql,"select * from ItemList where channelID=%d",id);
	//查询,获取所有的记录
	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{
		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;
}

//更新一个频道的所有项
void DBUpdateChannel(int channelID,ItemList* items,sqlite3 *){
	sqlite3 *db;
	sqlite3_open(gszFile,&db);
	//critical_section.Lock();
	int size = 4*1024;
	char *sql = new char[size];

	char *temp = "0000/00/00 00:00:00";
	sprintf(sql,"select pubTime from ItemList where channelID=%d order by pubTime desc",channelID);
	char **szResult;
	int nrow = 0,ncol = 0;
	sqlite3_get_table(db,sql,&szResult,&nrow,&ncol,NULL);
	char *time = 0;
	if (nrow>0)
	{
		time = *(szResult+ncol);
	}
	else{
		time = temp;
	}

	//把ItemList里面的每一项创建一条记录,插入到数据库中
	sqlite3_exec( db, "begin transaction", 0, 0, NULL); 
	while (items!=NULL)
	{
		char c;
		char *szmsg = &c;
		if (strlen(items->description)+512>size)
		{
			size = strlen(items->description)+1024+512;
			delete[] sql;
			sql = new char[size];
		}
		if (strcmp(time,items->time)<0)
		{
			sprintf(sql,"insert into ItemList(author,channelID,description,id,isRead,pubTime,title,url) values('%s',%d,'%s',%d,%d,'%s','%s','%s')",items->author,channelID,items->description,items->id,items->isRead,items->time,items->title,items->url);
			sqlite3_exec(db,sql,NULL,NULL,NULL);
		}
		

		items = items->nextItem;
	}
	sqlite3_exec( db, "commit transaction", 0, 0, NULL); 
	delete[] sql;
	sqlite3_close(db);
	//critical_section.Unlock();
	
}

//删除数据库的一条记录（目录，频道）
//给定的是删除记录之前的树状结构
void DBDeleteItem(DirNode *parent,MyTreeNode *delItem,sqlite3 *){
	//critical_section.Lock();

	sqlite3 *db;
	sqlite3_open(gszFile,&db);

	char sql[1024];
	char buffer[8];
	char **szResult;
	int nrow = 0,ncol = 0;

	
	//获取父节点的第一个子节点
	MyTreeNode *child = parent->firstChild;
	//判断要删除的节点是否为第一个子节点
	if (child==delItem)
	{
		//如果没有下一个兄弟节点,设置下一个兄弟节点的id为-1
		//如果有则下一个兄弟节点的id可以通过nextSubling->id获得
		int nextsublingID = -1;
		if (delItem->nextSubling)
		{
			nextsublingID = delItem->nextSubling->id;
		}
		//更新父节点,使他指向删除节点的下一个节点
		sprintf(sql,"update DirList set firstChildId=%d where id=%d",nextsublingID,parent->id);
		sqlite3_exec(db,sql,NULL,NULL,NULL);
	}
	else{
		//要删除的节点不是第一个子节点
		//遍历找到要删除节点的前一个节点
		while (child->nextSubling!=delItem)
		{
			child = child->nextSubling;
		}
		//如果没有下一个兄弟节点,设置下一个兄弟节点的id为-1
		//如果有则下一个兄弟节点的id可以通过nextSubling->id获得
		int nextsublingID = -1;
		if (delItem->nextSubling)
		{
			nextsublingID = delItem->nextSubling->id;
		}
		//更新要删除节点的前一个节点
		if (child->type==0)
		{
			sprintf(sql,"update DirList set nextSublingID=%d where id=%d",nextsublingID,child->id);
		}
		else if (child->type==1)
		{
			sprintf(sql,"update ChannelList set nextSublingID=%d where id=%d",nextsublingID,child->id);
		}
		else if (child->type==2)
		{
			sprintf(sql,"update CollectionList set nextSublingID=%d where id=%d",nextsublingID,child->id);
		}
		sqlite3_exec(db,sql,NULL,NULL,NULL);
	}

	//以上的所有操作是为了维持树结构

	//删除要删除的节点
	if (delItem->type==0)
	{
		sprintf(sql,"delete from DirList where id=%d",delItem->id);
	}
	else if (delItem->type==1)
	{
		sprintf(sql,"delete from ChannelList where id=%d",delItem->id);
	}
	else{
		sprintf(sql,"delete from CollectionList where id=%d",delItem->id);
	}
	
	sqlite3_exec(db,sql,NULL,NULL,NULL);

	sqlite3_close(db);
	//critical_section.Unlock();

}

//在数据库中插入一条记录（频道，目录）
void DBInsertItem(DirNode *parent,MyTreeNode *insItem,sqlite3 *){
	//critical_section.Lock();

	sqlite3 *db;
	sqlite3_open(gszFile,&db);
	char sql[1024];
	char buffer[8];
	char **szResult;
	int nrow = 0,ncol = 0;
	MyTreeNode *child = parent->firstChild;
	
	//如果要插入到节点是父节点的第一个子节点,则更新父节点
	if (child==insItem)
	{
		//设置父节点的firstchildid为要插入节点的id
		sprintf(sql,"update DirList set firstChildID=%d where id=%d",insItem->id,parent->id);
		sqlite3_exec(db,sql,NULL,NULL,NULL);
	}

	else{
		//遍历找到要插入节点的前一个兄弟节点
		while (child->nextSubling!=insItem)
		{
			child = child->nextSubling;
		}
		//更新前一个兄弟节点的nextsublingid为要插入节点的id
		if (child->type==0)
		{
			sprintf(sql,"update DirList set nextSublingID=%d where id=%d",insItem->id,child->id);
		}
		else if (child->type==1)
		{
			sprintf(sql,"update ChannelListList set nextSublingID=%d where id=%d",insItem->id,child->id);
		}
		else if (child->type==2)
		{
			sprintf(sql,"update CollectionList set nextSublingID=%d where id=%d",insItem->id,child->id);
		}
		sqlite3_exec(db,sql,NULL,NULL,NULL);
	}

	//如果要插入到节点没有下一个兄弟节点,则设置他的下一个兄弟节点id为-1
	int nextSublingID = -1;
	if (insItem->nextSubling!=NULL)
	{
		nextSublingID = insItem->nextSubling->id;
	}
	//插入一个目录
	if (insItem->type==0)
	{
		DirNode *dInsItem = (DirNode*)insItem;
		int type = 0;
		
		sprintf(sql,"insert into DirList(type,firstChildID,ID,name,nextSublingID) values(%d,%d,%d,'%s',%d)",type,-1,dInsItem->id,dInsItem->name,nextSublingID);

	}
	//插入一个频道
	else if (insItem->type==1)
	{
		ChannelNode *cCnsItem = (ChannelNode*)insItem;
		sprintf(sql,"insert into channelList(id,name,nextsublingID,url,pubTime) values(%d,'%s',%d,'%s','%s')",cCnsItem->id,cCnsItem->name,nextSublingID,cCnsItem->url,cCnsItem->pubTime);
	}
	else if (insItem->type==2)
	{
		CollectionNode *collInsItem = (CollectionNode*)insItem;
		sprintf(sql,"insert into collectionList(id,name,nextsublingID,url) values(%d,'%s',%d,'%s')",collInsItem->id,collInsItem->name,nextSublingID,collInsItem->url);
	}
	//执行插入操作
	sqlite3_exec(db,sql,NULL,NULL,NULL);

	sqlite3_close(db);
	//critical_section.Unlock();
}


//从数据库里面的记录创建一棵树
//初始化的时候调用

MyTreeNode *DBBuildTree(int rootID,sqlite3 *){
	//不存在的节点,退出
	if (rootID==-1)
	{
		return NULL;
	}
	sqlite3 *db;
	sqlite3_open(gszFile,&db);
	char sql[1024];
	char buffer[8];
	char **szResult;
	int nrow = 0,ncol = 0;

	//要创建节点的数据
	strcpy(sql,"select * from DirList where id=");