【C语言+sqlite3 API接口】实现水果超市

实验内容：

假如我家开了个水果超市，有以下水果，想实现自动化管理，扫描二维码就能知道当前的水果状态，进货几天了，
好久需要再次进货，那些水果畅销，那些水果不畅销，那些水果春夏秋冬的价格波动，好，那么现在我想将这些信息保存在数据库中，那么我应该怎么做？

超市每天水果都有进货和卖出嘛，水果的价格随着季节和天气也会有波动，顾客也会看一下每天水果的价格的嘛，所以要求，利用数据库完成水果店各种水果的增（进货）删（卖出）改（波动）查（看价格）功能。
并将进出货的时间和顾客光顾的时间记录到数据库中保存。

实现流程：

首先创建一张数据表fruit，除了需要指定每条记录的唯一标识id外，还要有水果品类(name)、存量(weight)、价格(price)、最近交易时间(time)等字段。
每一次操作，都会改变表中的数据内容，这些我们通过API来实现：各种水果的增（进货）删（卖出）改（波动）查（看价格）功能。并将进出货的时间和顾客光顾的时间记录到数据库中保存。

实现代码：

fruit.h

#ifndef _FRUIT_H_
#define _FRUIT_H_

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

#define N 128
#define DATABASE "fruitery.db"
#define TABLE "fruit"

int do_insert(sqlite3 *db, char *buf);
int do_query(sqlite3 *db);
int do_update_weight(sqlite3 *db, char * buf);
int do_update_price(sqlite3 *db, char * buf);
int do_delete(sqlite3 *db, char *buf);
int do_delete_sort(sqlite3 *db, char * buf, int id);

#endif

fruit.c

#include "fruit.h"

int do_insert(sqlite3 *db, char *buf) {
	char *errmsg;
	char sql[N] = {};
	char name[N] = {};
	float weight;
	float price;

	printf("Input fruit name:");
	scanf("%s", name);
	getchar();

	printf("Input weight:");
	scanf("%f", &weight);
		
	printf("Input price:");
	scanf("%f", &price);

	sprintf(sql, "insert into '%s' (name, weight, price, time) values('%s', '%.3f', '%.3f', '%s')", TABLE, name, weight, price, buf);
	if ( sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
		printf("Error: %s\n", errmsg);
	} else {
		printf("insert done.\n");
	}

	return 0;
}

int callback (void* arg,int f_num ,char** f_value,char** f_name) {
	int i;

	for(i = 0; i < f_num; i++) {
		printf("%-8s", f_value[i]);
	}
	return 0;
}

int do_query(sqlite3 *db) {
	char *errmsg;
	char sql[N] = {};

	sprintf(sql, "select * from '%s'", TABLE);
	if ( sqlite3_exec(db, sql, callback, NULL, &errmsg) != SQLITE_OK) {
		printf("Error: %s\n", errmsg);
	} else {
		printf("query done.\n");
	}
	return 0;
}

int do_update_weight(sqlite3 *db, char * buf)
{
	char *errmsg;
	char sql[N] = {};
	float weight;
	int id;

	printf("Input id:");
	scanf("%d", &id);

	printf("Input weight:");
	scanf("%f", &weight);

	sprintf(sql, "update '%s' set weight = '%.3f', time = '%s' where id = %d", TABLE, weight, buf, id);
	if ( sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
		printf("Error: %s\n", errmsg);
	} else {
		printf("update weight done.\n");
	}
	return 0;
}

int do_update_price(sqlite3 *db, char * buf)
{
	char *errmsg;
	char sql[N] = {};
	float price;
	int id;

	printf("Input id:");
	scanf("%d", &id);

	printf("Input price:");
	scanf("%f", &price);

	sprintf(sql, "update '%s' set price = '%.3f', time = '%s' where id = %d", TABLE, price, buf, id);
	if ( sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
		printf("Error: %s\n", errmsg);
	} else {
		printf("update price done.\n");
	}
	return 0;
}

int do_delete(sqlite3 *db, char * buf) 
{
	char *errmsg;
	char sql[N] = {};
	int id;

	printf("Input id:");
	scanf("%d", &id);

	do_delete_sort(db, buf, id );
	sprintf(sql, "delete from '%s' where id = %d", TABLE, id);
	if ( sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
		printf("Error: %s\n", errmsg);
	} else {
		printf("delete done.\n");
	}
	return 0;
	
}

#define NEW_TABLE "new_table"
int do_delete_sort(sqlite3 *db, char *buf, int id) 
{
	char *errmsg;
	char sql[N] = {};

	//创建一张临时的新表格
	sprintf(sql, "create table '%s'(id integer  primary key autoincrement, name char, weight float, price float, time char);", NEW_TABLE);
	sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	memset(sql, 0, sizeof(sql));

	//
	sprintf(sql, "insert into '%s' select * from '%s' where id = %d;",NEW_TABLE, TABLE, id);
	sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	memset(sql, 0, sizeof(sql));

	//删除原有的表
	sprintf(sql, "drop table '%s'", TABLE);
	sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	memset(sql, 0, sizeof(sql));

	//将新表的名字改成原有的旧表的名字
	sprintf(sql, "alter table '%s' rename to '%s'", NEW_TABLE, TABLE);
	sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	memset(sql, 0, sizeof(sql));
	return 0;
}

test.c

#include "fruit.h"

int main (int argc, char *argv[]) {
	sqlite3 *db;
	char *errmsg;
	char buf[N] = {};
	char sql[N] = {};
	int n;

	time_t t;
	struct tm *tp;

//打开数据库文件 
	if ( sqlite3_open(DATABASE, &db) != SQLITE_OK ) {
		printf("%s\n", errmsg);
		return -1;
	} else {
		printf("open DATABASE success.\n");
	}
//创建一张数据库表格
	sprintf(sql, "create table '%s'(id integer  primary key autoincrement, name char, weight float, price float, time char);", TABLE);
	if ( sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
		printf("exec :%s\n", errmsg);
	} else {
		printf("create table success.\n");
	}

	//时间
	time(&t);
	tp = localtime(&t);
	sprintf(buf,"%d-%02d-%02d %02d:%02d:%02d\n",
			tp->tm_year+1900, tp->tm_mon+1,tp->tm_mday, tp->tm_hour, tp->tm_min, tp->tm_sec);
	printf("%s\n", buf);

	while (1) {
		printf("-------------------------------------------\n");
		printf("1: insert 2:query 3:trade 4:update 5:delete 6:quit\n");
		printf("-------------------------------------------\n");
		printf("Please select:");
		scanf("%d", &n);

		switch(n) 
		{
		case 1: 
			do_insert(db, buf);
			break;
		case 2:
			do_query(db);
			break;
		case 3:
			do_update_weight(db, buf);
			break;
		case 4:
			do_update_price(db, buf);
			break;
		case 5:
			do_delete(db, buf);
			break;
		case 6:
			printf("main exit.\n");
			sqlite3_close(db);
			exit(0);
			break;
		default :
			printf("Invalid data,\n");
		}
	}
	return 0;
}