通过C语言调用sqlite3API编程实现停车场管理系统
我自己写的很冗余,希望大家能给我一下意见
#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>
int creat_table(sqlite3 *db)
{
char *sql;
char *errmsg;
int i;
sql = "create table if not exists carpark (NO integer primary key,Num text not NULL default '',Date text not NULL default '',Code integer not NULL default '');";
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("fail: %s\n",errmsg);
exit(-1);
}
}
int insert_table(sqlite3 *db)
{
int n;
int i;
char *sql;
char *errmsg;
printf("璇疯緭鍏ュ仠杞﹀満鎬诲叡杞︿綅鏁?\n");
scanf("%d",&n);
sql = "insert into carpark (NO) values (NULL);";
for(i = 0; i < n; i++)
{
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("fail: %s\n",&errmsg);
exit(-1);
}
}
}
int display(sqlite3 *db)
{
int nrow;
int ncol;
int i;
char *errmsg;
char *sql;
char **aresult;
sql = "select * from carpark;";
if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncol,&errmsg))
{
printf("fail: %s\n");
exit(-1);
}
for(i = 0; i < (nrow + 1) * (ncol); i++)
{
printf("%-20s",aresult[i]);
if((i + 1) % (ncol) == 0)
{
printf("\n");
}
}
sqlite3_free_table(aresult);
}
int update_table(sqlite3 *db)
{
char sql[100];
char *errmsg;
char str[30];
// char **aresult;
char num[30];
int flag = 1;
// int nrow;
// int ncol;
int n;
int m;
char ch;
time_t t;
struct tm *tblock;
struct timeval tv;
while(flag)
{
t = time(NULL);
m = t;
gettimeofday(&tv,NULL);
strftime(str,sizeof(str),"%Y/%m/%d %T",localtime(&tv.tv_sec));
// tblock = localtime(&t);
// strcpy(str,asctime(tblock));
printf("璇疯緭鍏ヨ溅浣嶅簭鍙?\n");
scanf("%d",&n);
getchar();
printf("璇疯緭鍏ヨ溅鐗屽彿!\n");
scanf("%s",num);
getchar();
sprintf(sql,"update carpark set Num = '%s',Date = '%s',Code = %d where No = %d;",num,str,m,n);
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("fail: %s\n",errmsg);
exit(-1);
}
memset(sql,0,sizeof(sql));
memset(str,0,sizeof(str));
display(db);
printf("continue add again?[Y/N]");
scanf("%ch",&ch);
getchar();
if((ch == 'y') || (ch == 'Y'))
{
flag = 1;
}
else
{
flag = 0;
}
}
}
int solve_price(sqlite3 *db)
{
char sql[100];
char num[20];
char *errmsg;
int nrow;
int ncol;
int i;
char **aresult;
int m;
time_t end;
time_t begain;
float n;
char ch;
int flag = 1;
while(flag)
{
printf("璇疯緭鍏ヨ溅鐗屽彿:\n");
scanf("%s",num);
getchar();
sprintf(sql,"select * from carpark where num = '%s';",num);
if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncol,&errmsg))
{
printf("fail: %s\n");
exit(-1);
}
for(i = 0; i < (nrow + 1) * (ncol); i++)
{
printf("%-20s",aresult[i]);
if((i + 1) % (ncol) == 0)
{
printf("\n");
}
}
printf("\n");
begain = atoi(aresult[2 * ncol - 1]);
// printf("begain = %d\n",begain);
end = time(NULL);
n = difftime(end,begain);
n = n / 3600;
n = n * 10;
m = atoi(aresult[ncol]);
// printf("m = %d",m);
printf("The cost is %.1f yuan \n",n);
printf("\n");
/* printf("input the No!\n");
scanf("%d",&n);
getchar();*/
memset(sql,0,sizeof(sql));
sprintf(sql,"update carpark set Num = '' ,Date = '',Code = '' where No = %d;",m);
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("fail: %s\n",errmsg);
exit(-1);
}
display(db);
printf("continue?[Y/N]\n");
scanf("%c",&ch);
getchar();
if((ch == 'y') || (ch == 'Y'))
{
flag = 1;
}
else
{
flag = 0;
}
}
sqlite3_free_table(aresult);
}
int menu()
{
int n;
while(1)
{
printf("*************************************\n");
printf("* WELCOME *\n");
printf("* 1:鍒涘缓鍋滆溅鍦?锝?2:鍋滆溅 *\n");
printf("* 3:鍑鸿溅 锝?0:閫€鍑? *\n");
printf("* 4:鏌ョ湅鍋滆溅鍦虹姸鎬? *\n");
printf("*************************************\n");
printf("璇疯緭鍏ユ偍鐨勯€夋嫨:\n");
scanf("%d",&n);
if((n == 0) || (n == 1) || (n == 2) || (n == 3) || (n == 4))
{
return n;
}
else
{
printf("input error! input again!\n");
}
}
}
int main()
{
sqlite3 *db;
int ret;
int n;
ret = sqlite3_open("park.db",&db);
if(ret != SQLITE_OK)
{
perror("sqlite open:\n");
exit(-1);
}
creat_table(db);
system("clear");
while(1)
{
n = menu();
switch(n)
{
case 1:
insert_table(db);
display(db);
break;
case 2:
system("clear");
display(db);
update_table(db);
system("clear");
break;
case 3:
system("clear");
display(db);
solve_price(db);
system("clear");
break;
case 4:
display(db);
sleep(3);
system("clear");
break;
case 0:
printf("娆㈣繋鍐嶆浣跨敤!\n");
exit(0);
break;
default:
printf("input error!\n");
break;
}
}
return 0;
}