利用SQL實現通訊錄

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

char menu(void)
{
    char ch;

    printf("welcome to menu   \n");
    printf("******************\n");
    printf("a: add    a pep   \n");
    printf("b: del    a pep   \n");
    printf("c: find   a pep   \n");
    printf("d: change a pep   \n");
    printf("e: list   all     \n");
    printf("q: quit           \n");
    printf("******************\n");
    printf("input your choice:");
    scanf(" %c", &ch);

    return ch;
}

void insert_node(sqlite3 * db)
{
    int ID = 0;
    char name[20] , phone[20] , addr[20], buf[100];
    char * sql = NULL;
    char * errmsg = NULL;

    printf("input new ID\n");
    scanf(" %d", &ID);
    printf("input new name\n");
    scanf(" %s", name);
    printf("input new phone\n");
    scanf(" %s", phone);
    printf("input new addr\n");
    scanf(" %s", addr);

    sprintf(buf, "insert into student values(%d, '%s', '%s', '%s');",
            ID, name, phone, addr);
    
    sql = buf;
    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg)!= SQLITE_OK)
    {
        printf("insert is error [%s]\n", errmsg);
    }

    return ;
}

void add(sqlite3 * db)
{
    printf("this add\n");
    
    insert_node(db);
    printf("add over\n");

    return ;
}

void del(sqlite3 * db)
{
    printf("this del\n");

    int sql_res = 0;
    char * errmsg = NULL;
    char * sql = NULL;
    char name[20] , buf[100];

    printf("input the name that you want del\n");
    scanf(" %s", name);

    sprintf(buf , "delete from student where name = '%s';" , name);

    sql = buf;
    sql_res = sqlite3_exec(db , sql , NULL, NULL, &errmsg);
    if(sql_res != SQLITE_OK)
    {
        printf("del is error [%s]\n", errmsg);
    }

    printf("del over\n");

    return ;
}

void find_name(char ** result , int nrow , int ncol , char name[])
{
    int i , loc = 1;

    for(i = 0 ; i < nrow + 1 ; i++)
    {
        if(strcmp(name , result[loc]) == 0)
        {
            printf("ID   -> [%s]\n", result[loc-1]);
            printf("name -> [%s]\n", result[loc]);
            printf("phone-> [%s]\n", result[loc+1]);
            printf("addr -> [%s]\n", result[loc+2]);
            break;
        }
        loc = loc + 4;
        if(loc >= (nrow*ncol + 4))
        {
            printf("not select pep\n");
            break;
        }
    }

    printf("select over\n");

    return ;
}


void find(sqlite3 * db)
{
    printf("this find\n");

    char name[10];
    char * errmsg = NULL;
    char * sql = NULL;
    char ** result = NULL;
    int sql_res = 0 , nrow , ncol;

    printf("input the name that you want find\n");
    scanf(" %s", name);

    sql = "select * from student;";
    sql_res = sqlite3_get_table(db, sql, &result, &nrow, &ncol, &errmsg);
    if(sql_res != SQLITE_OK)
    {
        printf("select is error [%s]\n", errmsg);
    }

    find_name(result , nrow , ncol , name);
    sqlite3_free_table(result);

    return ;
}

void change(sqlite3 * db)
{
    printf("this change\n");

    char name[20] , phone[20] , addr[20];
    char name_old[20];
    char buf[100];
    int sql_res = 0;
    char * errmsg = NULL , * sql = NULL;
    
    printf("input the name that you want change\n");
    scanf(" %s", name_old);
    printf("input new name\n");
    scanf(" %s", name);
    printf("input new phone\n");
    scanf(" %s", phone);
    printf("input new addr\n");
    scanf(" %s", addr);

    sprintf(buf , "update student set name = '%s' , phone = '%s',\
            addr = '%s' where  name = '%s';", name, phone, addr, name_old);

    sql = buf;
    sql_res = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if(sql_res != SQLITE_OK)
    {
        printf("change is error [%s]\n", errmsg);
    }

    printf("change over\n");

    return ;
}

void dis_list(char ** result , int nrow , int ncol)
{
    int i , j , loc;

    loc = ncol;
    for(i = 0 ; i < nrow ; i++)
    {
        for(j = 0 ; j < ncol ; j++)
        {
            printf("result[%s] -> [%s]\n", result[j], result[loc++]);
        }
    }
    printf("dis over\n");

    return ;
}

void dis_table(sqlite3 * db)
{
    printf("this list all\n");

    char * sql = NULL;
    char * errmsg = NULL;
    char ** result = NULL;
    int nrow = 0 , ncol = 0;
    int sql_res = 0;

    sql = "select * from student;";
    sql_res = sqlite3_get_table(db , sql , &result, &nrow , &ncol, &errmsg);
    if(sql_res != SQLITE_OK)
    {
        printf("list is error [%s]\n", errmsg);
    }

    dis_list(result , nrow , ncol);

    sqlite3_free_table(result);

    return ;
}

void open_sql(sqlite3 ** db)
{
    int sql_res = 0;

    sql_res = sqlite3_open("student.db", db);
    if(sql_res != SQLITE_OK)
    {
        printf("open is error [%s]\n", sql_res);
        return ;
    }

    printf("open success\n");

    return ;
}

void close_sql(sqlite3 * db)
{
    sqlite3_close(db);
    printf("close success\n");

    return ;
}

void create_sql(sqlite3 * db)
{
    char * sql = NULL;
    char * errmsg = NULL;
    int sql_res = 0;

    sql = "create table if not exists student(ID integer, name text,\
           phone text, addr text, primary key(ID));";
    sql_res = sqlite3_exec(db , sql , NULL , NULL , &errmsg);
    if(sql_res != SQLITE_OK)
    {
        printf("create is error [%s]\n", errmsg);
    }

    printf("create success\n");

    return ;
}

int main()
{
    char ch;
    sqlite3 * db = NULL;
    

    open_sql(&db);
    create_sql(db);

    while(ch = menu())
    {
        switch(ch)
        {
            case 'a':
                system("clear");
                add(db);
                break;
            case 'b':
                system("clear");
                del(db);
                break;
            case 'c':
                system("clear");
                find(db);
                break;
            case 'd':
                system("clear");
                change(db);
                break;
            case 'e':
                dis_table(db);
                break;
            case 'q':
                printf("byebye\n");
                return 0;
                break;
            default:
                printf("error!\n");
                break;
        }
    }

    close_sql(db);

    return 0;
}