用c语言动态操作sqilite3数据库

/* 项目名称：用c语言动态操作sqilite3数据库
* 项目成员：张双喜
* 编译环境：gcc
* 项目功能：
* 1、动态创建表
* 2、动态实现对表的基本操作（增、删、改、查）
* 3、清空表数据、删除表（附加功能）
* 项目总结：
* 1、涉及的技术：
* 1、int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
* 欲实现动态操作数据库，必须动态构造sql语句，构造sql语句可以用strcat函数，逐步拼接，直至完整构造出sql语句。
* 2、欲实现简单菜单选项效果，须分离出实现每个子功能的函数，在switch中，选择的调用
* 3、
* 2、意外收获：
* 1、想输入一条带有空格的字符串，不能用scanf函数，应为scanf函数遇到空格、换行、回车自动结束输入，所以scanf不能达到预期效果
* ，解决该问题可以使用gets函数，但是gets是个危险函数，c语言不提倡使用，在使用gets函数前，一定要清空缓冲区，否则。会出现
* 意想不到的输入结果，这就是先前没有清空缓冲区造成的。
* 2、linux c 清空缓冲区的问题：
* 个人见解：
* 经测试，linux c 使用fflush(stdin)无法清空缓冲区，使用fflush(stdin)后，再使用gets函数，gets还是会从缓冲区中读取东西，
* 因为缓冲区经fflush(stdin)没有被清空；
* 经过在网上找资料，得知setbuf(stdin, NULL);这样一个函数，经测试，清理缓冲区成功。
* 简言之：linux c 清空缓冲区的函数：setbuf(stdin, NULL);
* 3、编程中问题及其解决方法：
* 1、更新数据库update正确使用方法：
* update table_name set name = 'new_name', sex = 'new_sex' where id = id_val; //是正确的
* 上句中的,不能换成and，
* 即：
* update table_name set name = 'new_name' and sex = 'new_sex' where id = id_val; //是错误的
* 2、字符串的问题：
* char * p = "hello world!"; //该字符串是只读的不能修改字符串，不能改写，不能strcat(p, "jack");
* char str[] = "hello world!" //这样可以通过指针或不通过指针对字符串进行任意操作
* char *p;
* p = str;
* 3、字符串清理零：
* char str[10];
* memset(str, 0,sizeof(str));
* 张双喜
* v1.0
* 2010.9.29 14:39
* update 1
* 1、增加了一个测试表test_table，解决了，每次对操作数据库必须首先建立一个表的bug
* 2、增加了确认退出功能
*
* 张双喜
* v2.0
* 2010.9.30 9:40
*
* */

#include <stdio.h> 
#include <stdlib.h> 
#include <string.h> 
#include "sqlite3.h" 
 
/*该结构体是方便对表内数据操作*/ 
 
typedef struct table_column 
{ 
    char column_name[100];        //列名 
    char column_type[200];        //列的数据类型 
}COLUMN; 
 
void db_create(sqlite3 * db, char * pTable_name, int column_num
, COLUMN column[], int * create_flag); 
int rscallback(void * p, int column, char ** column_val
,char ** column_name); 
void db_show(sqlite3 * db, char * pTable_name); 
void db_insert(sqlite3 * db, char * pTable_name, int column_num
, COLUMN column[], int * test_flag, int * create_flag); 
void db_update(sqlite3 * db, char * pTable_name, int column_Num
, COLUMN column[], int * test_flag, int * create_flag); 
void db_delete(sqlite3 * db, char * pTable_name); 
void db_empty(sqlite3 * db, char * pTable_name); 
void db_drop(sqlite3 * db, char *pTable_name, int * test_flag
, int * create_flag); 
void db_test(sqlite3 * db, char * pTable_name, int ** test_flag); 
void db_flag(sqlite3 * db, int * create_flag, int * test_flag
, char * pTable_name); 
void db_reset_table_name(sqlite3 * db, char * pTable_name); 
 
int main(int argc, char* argv[]) 
{ 
    sqlite3 * db; 
    int empty = 1; 
    int ret = 0; 
    int create_flag = 1; 
    int test_flag = 1; 
    int test_flag_create = 1; 
    char c = 'n'; 
    char * errmsg = NULL; 
    int choice = -1; 
    char table_name[20]; 
    char * pTable_name; 
    int column_num; 
    int i; 
    COLUMN column[10]; 
    pTable_name = table_name; 
    strcpy(pTable_name, "test_table"); 
 
    ret = sqlite3_open("student.db", &db); 
    if(ret != SQLITE_OK) 
    { 
        perror("slqite3_open"); 
        exit(1); 
    } 
 
    while(choice != 0) 
    { 
printf("please input your choise:/n"); 
printf("-------------------------------------/n"); 
printf("|0.exit|1.create|2.show|3.insert|4.update|5.delete|6.empty|7.drop|/n"); 
printf("-------------------------------------/n"); 
        scanf("%d", &choice); 
        
        switch(choice) 
        { 
            case 0: 
printf("you choise leave, y or n?/n"); 
                    setbuf(stdin, NULL); 
                    scanf("%c", &c); 
                    setbuf(stdin, NULL); 
                    if(c == 'y') 
                    { 
                        if(test_flag == 0) 
                        { 
db_drop(db, "test_table", &test_flag, &create_flag); 
                        } 
                        
                        printf("goodbye!/n"); 
 
                        sqlite3_close(db); 
 
                        return 0; 
                    } 
                    else 
                    { 
                        choice = -1; 
                    } 
 
                    break; 
                    
            case 1: 
printf("we will create a table for you, please input the name of your table:/n"); 
scanf("%s",pTable_name); 
printf("please input the number of column:/n"); 
scanf("%d", &column_num); 
printf("please input column_name column_type:/n"); 
 
for(i = 0; i < column_num; i++)
  scanf("%s %s", column[i].column_name, column[i].column_type);                        
 
db_create(db, table_name, column_num, column, &create_flag); 
                    break; 
            case 2: 
db_flag(db, &create_flag, &test_flag, table_name); 
db_show(db, table_name); 
                    break; 
                   
            case 3: 
db_flag(db, &create_flag, &test_flag, table_name); 
db_insert(db, table_name, column_num, column, &test_flag, &create_flag); 
                    break; 
            case 4: 
db_flag(db, &create_flag, &test_flag, table_name); 
db_update(db, table_name, column_num, column, &test_flag, &create_flag); 
                    break; 
            case 5: 
                    db_flag(db, &create_flag, &test_flag, table_name); 
                    db_delete(db, table_name); 
                    break; 
            case 6: 
                    db_flag(db, &create_flag, &test_flag, table_name); 
                    db_empty(db, table_name); 
                    break; 
            case 7: 
                    db_flag(db, &create_flag, &test_flag, table_name); 
                    db_drop(db, table_name, &test_flag, &create_flag); 
                    break; 
            default: 
                    printf("your choice is not exist!/n"); 
                    break; 
    
        } 
        
    } 
 
    sqlite3_close(db); 
 
    return 0; 
} 
 
void db_flag(sqlite3 * db, int * create_flag, int * test_flag
, char * pTable_name) 
{ 
    if(create_flag) 
    { 
        if(*test_flag) 
        { 
            db_test(db, pTable_name, &test_flag); 
        } 
        
    } 
 
    return; 
} 
 
void db_test(sqlite3 * db, char * pTable_name, int ** test_flag) 
{ 
    int ret; 
    char * errmsg; 
 
    **test_flag = 0; 
 
    printf("because you have not create a table,so we create　\
a test_table table for you!/n"); 
    ret = sqlite3_exec(db, "create table test_table (id integer primary key \
autoincrement, name text, sex text, age integer);", NULL, NULL, &errmsg); 
    if(ret != SQLITE_OK) 
    { 
        printf("error:%d:%s/n", ret, errmsg); 
        exit(1); 
    } 
    ret = sqlite3_exec(db, "insert into test_table (name, sex, age) values \
('zsx', 'm', 23);",NULL ,NULL, &errmsg); 
    if(ret != SQLITE_OK) 
    { 
        printf("error:%d:%s/n", ret, errmsg); 
        exit(1); 
    } 
 
    return; 
} 
 
void db_create(sqlite3 * db, char * pTable_name, int column_num
, COLUMN column[], int * create_flag) 
{ 
    int ret, i; 
    char * errmsg = NULL; 
    char sql[200]; 
    char * pSql; 
 
    *create_flag = 0; 
    
    pSql = sql; 
 
    strcpy(pSql, "create table "); 
    
    strcat(pSql, pTable_name); 
    
    strcat(pSql, "("); 
 
    strcat(pSql, column[0].column_name); 
    strcat(pSql, " "); 
    strcat(pSql, column[0].column_type); 
    strcat(pSql, " primary key autoincrement"); 
    strcat(pSql, ", "); 
 
    for(i = 1; i < column_num-1; i++) 
    { 
        strcat(pSql, column[i].column_name); 
        strcat(pSql, " "); 
        strcat(pSql, column[i].column_type); 
        strcat(pSql, ", "); 
    } 
 
    strcat(pSql, column[column_num-1].column_name); 
    strcat(pSql, " "); 
    strcat(pSql, column[column_num-1].column_type); 
    strcat(pSql, ");"); 
 
    printf("/nsqlite > %s/n/n",pSql); 
 
    ret = sqlite3_exec(db, pSql, NULL, NULL, &errmsg); 
    if(ret != SQLITE_OK) 
    { 
         printf("error:%d:%s/n", ret, errmsg); 
         exit(1); 
    } 
    
    return; 
} 
 
int rscallback(void * p, int column, char ** column_val,char ** column_name) 
{ 
    int i; 
    *(int *)p = 0; 
 
    for(i = 0; i < column; i++) 
    { 
        printf("%s > %s/t/t",column_name[i], column_val[i]); 
    } 
    printf("/n"); 
    return 0; 
} 
 
void db_show(sqlite3 * db, char * pTable_name) 
{ 
    int ret = 0; 
    char * errmsg = NULL; 
    int empty = 1; 
    char sql[200]; 
    char * pSql; 
 
    pSql = sql; 
 
    strcpy(pSql, "select * from "); 
    strcat(pSql, pTable_name); 
    strcat(pSql, ";"); 
    printf("/nsqlite > %s/n/n", pSql); 
 
    ret = sqlite3_exec(db, pSql, rscallback, &empty, &errmsg); 
    if(ret != SQLITE_OK) 
    { 
        printf("error:%d:%s/n", ret, errmsg); 
        exit(1); 
    } 
 
    if(empty) 
    { 
        printf("the table is empty!/n"); 
    } 
 
    printf("/n"); 
    return; 
} 
 
void db_insert(sqlite3 * db, char * pTable_name, int column_num
, COLUMN column[], int * test_flag, int * create_flag) 
{ 
    int ret = 0; 
    char * errmsg = NULL; 
    char sql[200]; 
    char * pSql; 
    char tmp[20]; 
    int i; 
    char test_val[100]; 
 
    pSql = sql; 
    strcpy(pSql, "insert into "); 
    strcat(pSql, pTable_name); 
    strcat(pSql, "("); 
 
    if((*test_flag == 0) && (*create_flag)) 
    { 
        strcat(pSql, " name, "); 
        strcat(pSql, "sex, "); 
        strcat(pSql, "age ) "); 
        strcat(pSql, "values ("); 
 
        printf("please input name:/n"); 
        scanf("%s", test_val); 
        strcat(pSql, " /'"); 
        strcat(pSql, test_val); 
        strcat(pSql, "/', "); 
        printf("please input sex:/n"); 
        scanf("%s", test_val); 
        strcat(pSql, "/'"); 
        strcat(pSql, test_val); 
        strcat(pSql,"/', "); 
        printf("please input age:/n"); 
        scanf("%s",test_val); 
        strcat(pSql,test_val); 
        strcat(pSql, ");"); 
    } 
    if(*create_flag == 0) 
    { 
        for(i = 1; i < column_num-1; i++) 
        { 
            strcat(pSql, column[i].column_name); 
            strcat(pSql, ", "); 
        } 
 
        strcat(pSql, column[column_num-1].column_name); 
        strcat(pSql, ") "); 
        strcat(pSql, "values("); 
        for(i = 1; i < column_num-1; i++) 
        { 
            printf("please input %s/n",column[i].column_name); 
            if(strcmp(column[i].column_type,"text") == 0) 
            { 
                strcat(pSql, "/'"); 
                memset(tmp, 0, sizeof(tmp)); 
                scanf("%s",tmp); 
                strcat(pSql, tmp); 
                strcat(pSql,"/'"); 
                strcat(pSql, ","); 
            } 
            else 
            { 
                memset(tmp,0,sizeof(tmp)); 
                scanf("%s",tmp); 
                strcat(pSql, tmp); 
                strcat(pSql,","); 
                strcat(pSql," "); 
            } 
        } 
        printf("please input %s/n",column[column_num-1].column_name); 
        if(strcmp(column[column_num-1].column_type, "text") == 0) 
        { 
            scanf("%s",tmp); 
            strcat(pSql, "/'"); 
            strcat(pSql, tmp); 
            strcat(pSql, "/'"); 
            strcat(pSql, ");"); 
        } 
        else 
        { 
            scanf("%s",tmp); 
            strcat(pSql, tmp); 
            strcat(pSql, ");"); 
        } 
    } 
    printf("/nsqlite > %s/n/n", pSql); 
  
    ret = sqlite3_exec(db, pSql, NULL, NULL, &errmsg); 
    if(ret != SQLITE_OK) 
    { 
        printf("error:%d:%s/n", ret, errmsg); 
        exit(1); 
    } 
 
    return; 
} 
 
void db_update(sqlite3 * db, char * pTable_name, int column_num
, COLUMN column[], int * test_flag, int * create_flag) 
{ 
    int ret = 0; 
    char * errmsg = NULL; 
    char sql[200]; 
    char * pSql; 
    char new_val[20]; 
    char  new_id[3]; 
    int i; 
    char test_val[100]; 
    
 
    pSql = sql; 
    strcpy(pSql, "update "); 
    strcat(pSql, pTable_name); 
    strcat(pSql, " set "); 
 
    if((*test_flag == 0) && (*create_flag)) 
    { 
        strcat(pSql, "name = "); 
        printf("please input a new name:/n"); 
        scanf("%s",test_val); 
        strcat(pSql,"/'"); 
        strcat(pSql,test_val); 
        strcat(pSql, "/', sex = "); 
        printf("please input a new sex: /n"); 
        scanf("%s", test_val); 
        strcat(pSql, "/'"); 
        strcat(pSql, test_val); 
        strcat(pSql, "/', age = "); 
        printf("please input a new age:/n"); 
        scanf("%s",test_val); 
        strcat(pSql,test_val); 
        strcat(pSql, " where id = "); 
        printf("please input a id that you want to change:/n"); 
        scanf("%s",test_val); 
        strcat(pSql, test_val); 
        strcat(pSql, ";");    
    } 
    if(*create_flag == 0) 
    { 
        for(i = 1; i < column_num-1; i++) 
        { 
            strcat(pSql, column[i].column_name); 
            strcat(pSql, " = "); 
            if(strcmp(column[i].column_type, "text") ==0) 
            { 
                strcat(pSql, "/'"); 
                memset(new_val, 0, sizeof(new_val)); 
                printf("please input a new %s/n", column[i].column_name); 
                scanf("%s",new_val); 
                strcat(pSql, new_val); 
                strcat(pSql, "/'"); 
                strcat(pSql, " , "); 
            } 
            else 
            { 
                memset(new_val, 0, sizeof(new_val)); 
                printf("please input a new %s/n", column[i].column_name); 
                scanf("%s",new_val); 
                strcat(pSql, new_val); 
                strcat(pSql, " , "); 
            } 
        } 
 
        strcat(pSql, column[i].column_name); 
        strcat(pSql, " = "); 
 
        if(strcmp(column[column_num-1].column_type, "text") ==0) 
        { 
            strcat(pSql, "/'"); 
            memset(new_val, 0, sizeof(new_val)); 
            printf("please input a new %s/n", column[i].column_name); 
            scanf("%s",new_val); 
            strcat(pSql, new_val); 
            strcat(pSql, "/'"); 
            strcat(pSql, " "); 
        } 
        else 
        { 
            memset(new_val, 0, sizeof(new_val)); 
            printf("please input a new %s/n", column[i].column_name); 
            scanf("%s",new_val); 
            strcat(pSql, new_val); 
            strcat(pSql, " "); 
        } 
 
        strcat(pSql, "where id = "); 
        printf("please input the id that you want to change its value:/n"); 
        scanf("%s",new_id); 
        strcat(pSql, new_id); 
        strcat(pSql, ";"); 
    } 
    printf("sqlite > %s/n/n",pSql); 
      
    ret = sqlite3_exec(db, pSql, NULL, NULL, &errmsg); 
    if(ret != SQLITE_OK) 
    { 
        printf("error:%d:%s/n", ret, errmsg); 
        exit(1); 
    } 
 
    return; 
    
} 
 
void db_delete(sqlite3 * db, char * pTable_name) 
{ 
    int ret = 0; 
    char * errmsg = NULL; 
    char sql[200]; 
    char * pSql; 
    char tmp_name[20]; 
    char tmp_id[3]; 
    
 
    pSql = sql; 
    strcpy(pSql, "delete from "); 
    strcat(pSql, pTable_name); 
    strcat(pSql, " where id = "); 
    printf("please input a id that you want to delete:/n"); 
    scanf("%s",tmp_id); 
    strcat(pSql, tmp_id); 
    strcat(pSql, ";"); 
 
    printf("/nsqlite > %s/n/n",pSql); 
      
    ret = sqlite3_exec(db, pSql, NULL, NULL, &errmsg); 
    if(ret != SQLITE_OK) 
    { 
        printf("error:%d:%s/n", ret, errmsg); 
        exit(1); 
    } 
 
    return; 
} 
 
void db_empty(sqlite3 * db, char * pTable_name) 
{ 
    int ret = 0; 
    char * errmsg = NULL; 
    char sql[200]; 
    char * pSql; 
 
 
    pSql = sql; 
    strcpy(pSql, "delete from "); 
    strcat(pSql, pTable_name); 
    strcat(pSql,";"); 
 
    printf("/nsqlite > %s/n/n",pSql); 
 
    ret = sqlite3_exec(db, pSql, NULL, NULL, &errmsg); 
    if(ret != SQLITE_OK) 
    { 
        printf("error:%d:%s/n", ret, errmsg); 
        exit(1); 
    } 
 
    return; 
} 
    
void db_drop(sqlite3 * db, char * pTable_name, int * test_flag
, int * create_flag) 
{ 
    int ret = 0; 
    char * errmsg = NULL; 
    char sql[200]; 
    char * pSql; 
 
 
    pSql = sql; 
    strcpy(pSql, "drop table "); 
    strcat(pSql, pTable_name); 
    strcat(pSql,";"); 
 
    if(*test_flag) 
    { 
        printf("/nsqlite > %s/n/n",pSql); 
    } 
 
    if(*create_flag == 0) 
    { 
        printf("/nsqlite > %s/n/n",pSql); 
    } 
 
    ret = sqlite3_exec(db, pSql, NULL, NULL, &errmsg); 
    if(ret != SQLITE_OK) 
    { 
        printf("error:%d:%s/n", ret, errmsg); 
        exit(1); 
    } 
 
    return; 
}