#include
#include
#include
#include
#include
#include
#define DATABASE "fru.db"
typedef struct{
char time[20];
char name[16];
float cost;
float price;
char in_out[5];
float number;
float money;
float total;
}fruit_t;
void operate_table_cmd_menu()
{
puts("/******************************************/");
puts("*operate table cmd menu:");
printf("*1:insert 2:delete 3:query 4:update 5:quite
");
puts("/******************************************/");
printf("Please input cmd:");
}
const char *get_local_time(char *time_str);
fruit_t *get_last_row(sqlite3 *db,fruit_t *fruit);
int query_callback(void *para, int f_num, char **f_value, char **f_name);
int do_insert_sqlite3(sqlite3 * db);
int do_delete_sqlite3(sqlite3 * db);
int do_query_sqlite3(sqlite3 * db);
void get_columns_name(sqlite3 * db);
int do_update_sqlite3(sqlite3 * db);
int main(int argc, const char *argv[])
{
sqlite3 * db;
char *errmsg;
int cmd;
char sql[128]={};
if(sqlite3_open(DATABASE,&db) != SQLITE_OK)
{
printf("%s
",sqlite3_errmsg(db));
exit(-1);
}
else
{
printf("open %s success.
",DATABASE);
}
sprintf(sql,"create table fru(time char,name char,cost Integer,price Integer,in_out char,number Integer,money Integer,total Integer);");
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
{
printf("%s
",errmsg);
}
else
{
printf("create or open table success.
");
}
while(1)
{
operate_table_cmd_menu();
while(scanf("%d",&cmd) == 0)
{
getchar();
operate_table_cmd_menu();
}
getchar();
switch(cmd)
{
case 1:
do_insert_sqlite3(db);
break;
case 2:
do_delete_sqlite3(db);
break;
case 3:
do_query_sqlite3(db);
break;
case 4:
do_update_sqlite3(db);
break;
case 5:
sqlite3_close(db);
exit(0);
default:
printf("Error cmd.
");
}
}
return 0;
}
const char *get_local_time(char *time_str)
{
time_t tim_t;
struct tm loc_t;
time(&tim_t);
loc_t = *localtime(&tim_t);
sprintf(time_str,"%04d-%d-%02d %02d:%02d:%02d",loc_t.tm_year+1900,loc_t.tm_mon+1,loc_t.tm_mday,loc_t.tm_hour,loc_t.tm_min,loc_t.tm_sec);
return time_str;
}
fruit_t *get_last_row(sqlite3 *db,fruit_t *fruit)
{
char **resultp;
int nrow;
int ncolumn;
char sql[128];
int index_base;
char *errmsg;
sprintf(sql,"select * from fru where name='%s';",fruit->name);
sqlite3_get_table(db,sql,&resultp,&nrow,&ncolumn,&errmsg);
if(nrow == 0)
{
return NULL;
}
else
{
index_base = (nrow)*(ncolumn);
fruit->cost = atof(resultp[index_base+2]);
fruit->price = atof(resultp[index_base+3]);
fruit->total = atof(resultp[index_base+7]);
}
return fruit;
}
int do_insert_sqlite3(sqlite3 * db)
{
fruit_t fruit;
char sql[128];
char *errmsg;
char type[10];
bzero(&fruit,sizeof(fruit));
get_local_time(fruit.time);
printf("input fruit name:");
scanf("%s",fruit.name);
getchar();
if(get_last_row(db,&fruit) != NULL)
{
puts("*************************************");
printf("%s cost:%.2f price:%.2f
",fruit.name,fruit.cost,fruit.price);
puts("*************************************");
printf("if you change it[y|n]:");
scanf("%s",type);
getchar();
if(strncasecmp(type,"y",strlen("y")) == 0)
{
char value[20];
printf("change cost:%.2f of %s [cost|n]:",fruit.cost,fruit.name);
scanf("%s",value);
getchar();
if(strncasecmp(value,"n",strlen("n")) != 0)
fruit.cost = atof(value);
printf("change price:%.2f of %s [price|n]:",fruit.price,fruit.name);
scanf("%s",value);
getchar();
if(strncasecmp(value,"n",strlen("n")) != 0)
fruit.price = atof(value);
}
printf("Please input the number of %s:",fruit.name);
scanf("%f",&fruit.number);
getchar();
fruit.total = fruit.total +fruit.number;
}
else
{
printf("Please input the cost of %s [cost]:",fruit.name);
while(scanf("%f",&fruit.cost) == 0)
{
getchar();
printf("Please input the cost of %s [cost]:",fruit.name);
}
getchar();
printf("Please input the price of %s [price]:",fruit.name);
while(scanf("%f",&fruit.price) == 0)
{
getchar();
printf("Please input the price of %s [price]:",fruit.name);
}
getchar();
printf("Please input the number of %s:",fruit.name);
scanf("%f",&fruit.number);
getchar();
fruit.total = fruit.number;
}
sprintf(fruit.in_out,"in");
fruit.money = fruit.cost * fruit.number;
sprintf(sql,"insert into fru values('%s','%s',%.4f,%.4f,'%s',%.4f,%.4f,%.4f);",fruit.time,fruit.name,fruit.cost,fruit.price,fruit.in_out,fruit.number,fruit.money,fruit.total);
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK )
{
printf("%s
",errmsg);
return -1;
}
else
{
printf("insert:
");
sprintf(sql,"select * from fru where time='%s';",fruit.time);
get_columns_name(db);
sqlite3_exec(db,sql,query_callback,NULL,&errmsg);
printf("done
");
return 0;
}
}
int query_callback(void *para, int f_num, char **f_value, char **f_name)
{
int i;
for(i=0;i<f_num;i++)
{
if(i == 0)
printf(" %-19s ",f_value[i]);
else
printf("%-10s ",f_value[i]);
}
puts("");
return 0;
}
void get_columns_name(sqlite3 * db)
{
char **resultp;
int nrow;
int ncolumn;
char *errmsg;
int i;
sqlite3_get_table(db,"select * from fru",&resultp,&nrow,&ncolumn,&errmsg);
printf(" %-19s ",resultp[0]);
for(i=1;i<ncolumn;i++)
{
printf("%-10s ",resultp[i]);
}
puts("");
}
int do_delete_sqlite3(sqlite3 * db)
{
fruit_t fruit;
char sql[128];
char *errmsg;
char value[20];
bzero(&fruit,sizeof(fruit));
get_local_time(fruit.time);
printf("input fruit name:");
scanf("%s",fruit.name);
getchar();
if(get_last_row(db,&fruit) != NULL)
{
printf("input the out number of %s:",fruit.name);
scanf("%s",value);
getchar();
if(atof(value) > fruit.total)
{
printf("The total number:%.4f of %s is not enough, please stock it.
",fruit.total,fruit.name);
return -1;
}
else
{
fruit.number = atof(value);
fruit.money = fruit.price * fruit.number;
fruit.total = fruit.total - fruit.number;
}
}
else
{
printf("We don't have this fruit:%s. Please stock it.",fruit.name);
return -1;
}
sprintf(fruit.in_out,"out");
printf("are you sure delete number:%.4f of %s[y|n]:",fruit.number,fruit.name);
scanf("%s",value);
getchar();
if(strncasecmp(value,"y",strlen("y")) == 0)
{
sprintf(sql,"insert into fru values('%s','%s',%.4f,%.4f,'%s',%.4f,%.4f,%.4f);",fruit.time,fruit.name,fruit.cost,fruit.price,fruit.in_out,fruit.number,fruit.money,fruit.total);
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK )
{
printf("%s
",errmsg);
return -1;
}
else
{
printf("delete done
");
return 0;
}
}
else
{
printf("delete cancelled.
");
return 0;
}
}
int do_query_sqlite3(sqlite3 * db)
{
fruit_t fruit;
char sql[128];
char *errmsg;
char value[96];
bzero(&fruit,sizeof(fruit));
puts("*************************************");
printf("*Examples of query types:
");
puts("*[all | name='apple' | in_out='in']");
puts("*[time<'2018-12-5 17:08:08' | total>90 and total<200]");
puts("*[name='apple' and in_out='in']");
puts("*************************************");
printf("Pease input query type:");
scanf("%s",value);
getchar();
if(strncasecmp("all",value,strlen(value)) == 0)
sprintf(sql,"select * from fru;");
else
sprintf(sql,"select * from fru where %s;",value);
get_columns_name(db);
if(sqlite3_exec(db,sql,query_callback,NULL,&errmsg) != SQLITE_OK )
{
printf("%s
",errmsg);
return -1;
}
else
{
printf("query done
");
return 0;
}
}
int do_update_sqlite3(sqlite3 * db)
{
fruit_t fruit;
char sql[128];
char *errmsg;
int type;
bzero(&fruit,sizeof(fruit));
get_local_time(fruit.time);
printf("input fruit name:");
scanf("%s",fruit.name);
getchar();
if(get_last_row(
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://www.xiaopingtou.cn//data/attach/topic/topicKPo7gB.jpg', '推荐 519476964 的文章《嵌入式linux-sqlite3数据库，代码示例》','https://www.xiaopingtou.net/article-66171.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}