# 2023/3/20 数据库练习，词典导入

2023/3/20 数据库练习，词典导入

#include "head.h"

#define MAXSIZE 20

//void do_insert(sqlite3* db,char *word_name,char *word_mean);
void do_insert(sqlite3* db);
void do_search(sqlite3* db);
void do_delete(sqlite3* db);
int callBack(void* arg,int ncloumn,char** column_text,char** column_name);

int main(int argc, const char *argv[])
{
	//创建并打开数据库
	sqlite3 *db = NULL;
	if(sqlite3_open("./dictionary.db",&db) != SQLITE_OK)
	{
		fprintf(stderr,"__%d__sqlite3_open failed %d|%s\n",\
				__LINE__,sqlite3_errcode(db),sqlite3_errmsg(db));
		return -1;
	}

	printf("sqlite3_open success __%d__\n",__LINE__);

	//创建表格
	char sql[128] = "create table if not exists dict (word char,mean char);";
	//错误信息
	char *errmsg = NULL;

	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"__%d__ sqlite3_exec %s\n",__LINE__,errmsg);
		return -1;
	}
	printf("table dict create success\n");

	int choose = 0;
	while(1)
	{
		printf("================\n");
		printf("====1.增加======\n");
		printf("====2.查找======\n");
		printf("====3.删除======\n");
		printf("====4.退出======\n");
		printf("================\n");
		printf("请输入：");
		scanf("%d",&choose);
		while(getchar() != 10);

		switch(choose)
		{
		case 1:
			//do_insert
			/*
			FILE* fp = fopen("./dict.txt","r");
			if(NULL == fp)
			{
				ERR_MSG("fopen");
				goto END;
			}
			char word_name[32] = "";
			char word_mean[32] = "";
			while(fscanf(fp,"%s %s",word_name,word_mean))
			{
				if(fscanf(fp,"%s %s",word_name,word_mean) == EOF)
				{
					printf("写入完毕\n");
					break;
				}
				do_insert(db,word_name,word_mean);
			}
			fclose(fp);
			*/
			do_insert(db);
			break;
		case 2:
			//do_search
			do_search(db);
			break;
		case 3:
			//do_delete
			do_delete(db);
			break;
		case 4:
			goto END;
			break;
		default:
			printf("输入错误\n");
		}
	}

END:

	//关闭数据库，释放内存空间
	if(sqlite3_close(db) != SQLITE_OK)
	{
		fprintf(stderr,"__%d__sqlite3_close failed %s\n",\
				__LINE__,errmsg);
		return -1;
	}

	printf("sqlite3_close success __%d__\n",__LINE__);
	return 0;
}

//void do_insert(sqlite3* db,char *word,char *mean)
void do_insert(sqlite3* db)
{
	FILE* fp = fopen("./dict.txt","r");
	if(NULL == fp)
	{
		ERR_MSG("fopen");
		return;
	}
	char word_name[128] = "";
	char word_mean[128] = "";
		char temp[128] = "";
	char sql[128] = "";
	char *errmsg = NULL;
	while(1)
	{  
		int i,j;
		int k = 0;//翻译开始的下标
		memset(temp,0,sizeof(temp));
		memset(word_name,0,sizeof(word_name));
		memset(word_mean,0,sizeof(word_mean));
		fgets(temp,sizeof(temp),fp);
		temp[strlen(temp) - 1] = 0;
		if(0  == temp[0])
		{
			break;
		}
		for(i=0;temp[i]!=' ';i++)
		{
			word_name[i] = temp[i];
		}
		if(temp[i+1] != ' ')
		{
			word_name[i] = ' ';
			for(i=i+1;temp[i]!=' ';i++)
			{
				word_name[i] = temp[i];
			}
		}
		word_name[i] = 0;
		for(j=i+1;j<strlen(temp);j++)
		{
			if(temp[j]!=' ')
			{
				word_mean[k++] = temp[j];
			}
		}
		word_mean[k+1] = 0;
		sprintf(sql,"insert into dict values ( \"%s\" ,\"%s\");",word_name,word_mean);
		if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
		{
			fprintf(stderr,"__%d__ sqlite3_exec %s\n",__LINE__,errmsg);
			return;
		}
	}
	fclose(fp);
	printf("insert success __%d__\n",__LINE__);
}

/*

void do_search(sqlite3* db)
{
	char word[128] = "";

	scanf("%s",word);
	while(getchar() != 10);

	char sql[128] = "";
	char *errmsg = NULL;
	int flag = 0;
	if(strcmp(word,"0") == 0)
	{
		strcpy(sql,"select * from dict");
	}else{
		sprintf(sql,"select * from dict where word = \"%s\";",word);
	}
	if(sqlite3_exec(db,sql,callBack,&flag,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"__%d__ sqlite3_exec %s\n",__LINE__,errmsg);
		return;
	}
	printf("search success __%d__\n",__LINE__);
}

*/

void do_search(sqlite3* db)
{
	char word[128]= "";

	bzero(word,sizeof(word));
	scanf("%s",word);
	while(getchar() != 10);

	char sql[128] = "";
	bzero(sql,sizeof(sql));
	char *errmsg = NULL;
	char **pres = NULL;
	int row,column;
	if(strcmp(word,"0") == 0)
	{
		strcpy(sql,"select * from dict");
	}else{
		sprintf(sql,"select * from dict where word= \"%s\";",word);
	}

	if(sqlite3_get_table(db,sql,&pres,&row,&column,&errmsg) != SQLITE_OK)
	{

		fprintf(stderr,"__%d__ sqlite3_exec %s\n",__LINE__,errmsg);
		return;
	}
	printf("search success __%d__\n",__LINE__);

	int index = 0;
	for(int i=0;i<row+1;i++)
	{
		for(int j=0;j<column;j++)
		{
			printf("%s\t",pres[index++]);
		}
		putchar(10);
	}
	//释放内存空间
	sqlite3_free_table(pres);
	pres = NULL;
}


void do_delete(sqlite3* db)
{
	char word[128] = "";
	scanf("%s",word);
	while(getchar() != 10);

	char sql[128] = "";
	char *errmsg = NULL;
	sprintf(sql,"delete from dict where word = %s;",word);
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"__%d__ sqlite3_exec %s\n",__LINE__,errmsg);
		return;
	}
	printf("insert success __%d__\n",__LINE__);
}

int callBack(void* arg,int ncloumn,char** column_text,char** column_name)
{
	int i;
	if(0 == *(int*)arg)
	{
		for(i=0;i<ncloumn;i++)
		{
			printf("%s\t",column_name[i]);
		}
		putchar(10);
		*(int*)arg = 1;
	}

	for(i=0;i<ncloumn;i++)
	{
		printf("%s\t",column_text[i]);
	}
	putchar(10);
	 
	return 0;
}