通讯录(sqlite3 version)

#include<stdio.h>
#include<sqlite3.h>
#include<string.h>
#include<stdlib.h>

char *errmsg;
sqlite3 *DB;
int ret;
void create_DB();
void create_table();
void interface();
void operation();
void option(int*);
void insert();
void delete();
void edit();
void query();

int main()
{	
	create_DB();
	create_table();
	operation();
	
	ret = sqlite3_close(DB);
	if(SQLITE_OK != ret)
	{
		exit(1);
	}
	return 0;
}

void create_DB()
{
	int ret;
	
	ret = sqlite3_open("./mydatabase.db", &DB);
	if(SQLITE_OK != ret)
	{
		perror("open");
		return;
	}
	return;
}

void create_table()
{
	char *str = "create table ADDRLIST(id int primary key, name text, number text, address text);";	
	int ret;
	
	ret = sqlite3_exec(DB, str, NULL, NULL, &errmsg);
	if(SQLITE_OK != ret)
	{
		printf("%s\n", errmsg);
		return;
	}
	
	return;
}

void operation()
{
	int flag = 0;
	int sub_op = 1;
	interface();
	option(&flag);
	while(flag != 5)
	{
		sleep(1);
		switch(flag)
		{
			case 1:
				while(sub_op == 1)
				{	
					insert();
					printf("enter 0 to return,enter 1 to continue\n");
					option(&sub_op);
				}
				sub_op = 1;
				break;
			case 2:
				while(sub_op == 1)
				{	
					delete();
					printf("enter 0 to return,enter 1 to continue\n");
					option(&sub_op);
				}
				sub_op = 1;
				break;
			case 3:
				while(sub_op == 1)
				{	
					edit();
					printf("enter 0 to return,enter 1 to continue\n");
					option(&sub_op);
				}
				sub_op = 1;
				break;
			case 4:
				query();
				break;
			default:
				printf("input error\n");
				break;
		}
		sleep(1);
		interface();
		option(&flag);
	}
	return;
}

void interface()
{
	printf("Welcome to use ADDRLIST_DB system\n");
	printf("1.insert contact\n");
	printf("2.delete contact\n");
	printf("3.edit info\n");
	printf("4.query\n");
	printf("5.quit\n");
	printf("Please choose a function(1~5)\n");
}

void option(int* op)
{
	int flag;
	scanf("%d", &flag);
	*op = flag;
}

void insert()
{
	int id;
	char name[15];
	char num[15];
	char addr[15];
	printf("input contact's id name num addr\n");
	scanf("%d %s %s %s", &id, name, num, addr);
	
	char str[1000] = {0};
	sprintf(str, "insert into ADDRLIST values (%d, '%s', '%s', '%s');", id, name, num, addr);
	ret = sqlite3_exec(DB, str, NULL, NULL, &errmsg);
	if(SQLITE_OK != ret)
	{
		printf("insert error:%s\n", errmsg);
		exit(1);
	}
}

void delete()
{
	int id;
	printf("enter id\n");
	scanf("%d", &id);
	
	char str[1000] = {0};
	sprintf(str, "delete from ADDRLIST where id = %d;", id);
	ret = sqlite3_exec(DB, str, NULL, NULL, &errmsg);
	if(SQLITE_OK != ret)
	{
		printf("delete error:%s\n", errmsg);
		exit(1);
	}
}

void edit()
{
	int id;
	printf("enter id\n");	
	scanf("%d", &id);
	char name[15];
	char num[15];
	char addr[15];
	printf("input contact's new name num addr\n");
	scanf("%s %s %s", name, num, addr);

	
	char str[1000] = {0};
	sprintf(str, "update ADDRLIST set name = '%s', number = '%s', address = '%s' where id = %d;", name, num, addr, id);
	ret = sqlite3_exec(DB, str, NULL, NULL, &errmsg);
	if(SQLITE_OK != ret)
	{
		printf("update error:%s\n", errmsg);
		exit(1);
	}
}

void query()
{
	int row, col;
	char **result;
	char *str = "select * from ADDRLIST;";
	ret = sqlite3_get_table(DB, str, &result, &row, &col, &errmsg);
	if(ret != SQLITE_OK)
	{	
		printf("get table : %s\n", errmsg);
		exit(1);
	}
	int i,j;
	printf("r = %d,c = %d\n", row, col);
	for(i = 0; i <= row; i++)
	{
		for(j = 0; j < col; j++)
		{
			printf("%s|", result[i * col + j]);
		}
		printf("\n");
	}

	sqlite3_free_table(result);
}