[置顶] 基于sqlite3实现的简单通讯录程序（控制台）

1. sqlite3 安装

1.1. 下载sqlite3源码
   www.sqlite3.org
   下载 sqlite-autoconf-3070701.tar.gz


1.2. 解压
   将下载的 sqlite-autoconf-3070701.tar.gz 解压，得到sqlite-autoconf-3070701 文件夹


1.3. 编译源码(参照解压文件夹下的install文件)
   $ cd sqlite-autoconf-3070701 #进入文件夹
   $ ./configure
   $ make
   $ sudo make install          #注意一定要root权限


1.4. 查看安装情况
   在/usr/local/include 下：sqlite3.h sqlite3ext.h
   在/usr/local/bin     下：sqlite3
   在/usr/local/lib      下：pkgconfig（文件夹） libsqlite3.a  libsqlite3.la 
                                          libsqlite3.so  libsqlite3.so.0 libsqlite3.so.0.8.6


2. sqlite3 c编程 之 环境搭建(Codeblocks)

2.1. 新建 c project
   打开codeblocks,File--> new --> project-->console application-->c


2.2. 引入库
   project-->build options --> linker setting
   在linker library 下点击 add， 选择 /usr/local/lib/libsqlite3.so  文件


2.3. 添加.h文件
   在程序开头#include <sqlite3.h>


2.4. 至此环境搭建完成。
   在程序中，就可以使用sqlite3的c语言接口了
   如：sqlite3_open()

           sqlite3_close()

3. 通讯录程序

    一个简单的通讯录程序，主要是为了练手，熟悉Slqite3的使用。

   数据库：contact.db，只有一张表info，保存联系人信息（姓名，年龄，关系，手机号）

   程序中bug较多，但却涵盖了sqlite3的数据库常用操作：创建数据库、创建表、增、删、改、查等。

/************************************************************************
 * 名  称：contact_using_sqlite3.c
 * 功  能：一个简单的通讯录程序，主要是为了练习sqlite3的使用
 * 描  述：使用sqlite3，建立数据库contact,其中有表一张
          info(name varchar(10), age smallint, relation varchar(10), phone varchar(11))
          包括如下功能：
             1. 查看通讯录
             2. 增加联系人
             3. 删除联系人
             4. 修改联系人信息
             5. 查找联系人
 * 作  者：JarvisChu
 * 时  间：2011-8-22
 * 修  订：2011-8-24，完成修改和删除；
 ************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>

sqlite3* db = NULL;               //the database


//show the menu on the screen,and return user's choice
int showMenu(){
    int choice = 0;
    while(1){
        printf("*****************************************************\n");
        printf("                      Welcome                        \n");
        printf("1. Display All          2. Add   Contact\n");
        printf("3. Delete  Contact      4. Alter Contact\n");
        printf("5. Find    Contact\n");
        printf("Your choice is:");
        scanf("%d",&choice);
        if(choice == 1 || choice == 2 ||\
           choice == 3 || choice == 4 ||\
           choice == 5){
            return choice;
        }
        else{
            printf("Wrong! Again!\n");
        }
    }
    return 0;
}

//show all records in db
void displayAll(){
    int i = 0;
    int j = 0;
    int index = 0;
    int ret = 0;
    int row = 0;
    int column = 0;
    char* sql = NULL;
    char** resultSet = NULL;     //store the query result

    sql = (char*)malloc(sizeof(char)*20);
    strcpy(sql,"select * from info;");

    ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
    if(ret != SQLITE_OK){
        fprintf(stderr,"select records err\n");
    }

    printf("There are %d Contact:\n",row);
    index = 0;
    for(i=0;i<=row;i++){
        for(j=0;j<column;j++){
            printf("%-11s",resultSet[index++]);
        }
        printf("\n");
    }

    sqlite3_free_table(resultSet);
    free(sql);

}

//add contact
void addContact(){
    int ret = 0;
    char* name = NULL;
    int age = 0;
    char* relation = NULL;
    char* phone = NULL;
    char* sql = NULL;

    name = (char*)malloc(sizeof(char)*10);
    relation = (char*)malloc(sizeof(char)*10);
    phone = (char*)malloc(sizeof(char)*12);
    sql = (char*)malloc(sizeof(char)*64);

    printf("input (name age relation phone):");
    scanf("%s %d %s %s",name,&age,relation,phone);
    //printf("%s， %d， %s，%s\n",name,age,relation,phone);


    sprintf(sql,"insert into info values('%s',%d,'%s','%s');",name,age,relation,phone);
    //printf("%s\n",sql);
    ret = sqlite3_exec(db,sql,0,0,0);
    if(ret != SQLITE_OK){
        printf("failed!\n");
    }
    else{
        printf("ok!\n");
    }
    free(name);
    free(relation);
    free(phone);
    free(sql);

}

//find Contact
void findContact(){
    int i,j,index;
    int ret  = 0;
    int row = 0;
    int column = 0;
    char* name = NULL;
    char* sql = NULL;
    char** resultSet = NULL;

    name = (char*)malloc(sizeof(char)*10);
    printf("Input the name you want to find:");
    scanf("%s",name);

    sql = (char*)malloc(sizeof(char)*64);
    sprintf(sql,"select * from info where name = '%s'",name);

    ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
    if(ret != SQLITE_OK){
        fprintf(stderr,"select err:%s\n",sqlite3_errmsg(db));
        return;
    }
    index = 0;
    if(row>0){
        for(i=0;i<=row;i++){
            for(j=0;j<column;j++){
                printf("%-11s",resultSet[index++]);
            }
            printf("\n");
        }
    }
    else{
        printf("no such person!\n");
    }
}

//alertContact()
void alterContact(){
    //first,find the contact info to be altered.
    int i,j,index;
    int ret  = 0;
    int row = 0;
    int column = 0;
    int age = 0;
    char* name = NULL;
    char* relation = NULL;
    char* phone = NULL;
    char* sql = NULL;
    char** resultSet = NULL;

    name = (char*)malloc(sizeof(char)*10);
    printf("Input the name you want to alter:");
    scanf("%s",name);

    sql = (char*)malloc(sizeof(char)*128);
    sprintf(sql,"select * from info where name = '%s'",name);

    ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
    if(ret != SQLITE_OK){
        fprintf(stderr,"select err:%s\n",sqlite3_errmsg(db));
        return;
    }
    index = 0;
    if(row>0){
        for(i=0;i<=row;i++){
            for(j=0;j<column;j++){
                printf("%-11s",resultSet[index++]);
            }
            printf("\n");
        }
        sqlite3_free_table(resultSet);

        //exist ,then alter
        relation = (char*)malloc(sizeof(char)*10);
        phone = (char*)malloc(sizeof(char)*12);

        printf("input the new data (age relation phone):");
        scanf("%d %s %s",&age,relation,phone);
        //printf(" %d， %s，%s\n",name,age,relation,phone);


        sprintf(sql,"update info set age=%d,relation='%s',phone='%s' where name='%s';",age,relation,phone,name);
        //printf("%s\n",sql);
        ret = sqlite3_exec(db,sql,0,0,0);
        if(ret != SQLITE_OK){
            printf("failed!\n");
        }
        else{
            printf("ok!\n");
        }
        free(relation);
        free(phone);
    }
    else{
        printf("no such person!\n");
    }
    free(sql);
    free(name);

}

//delete Contact
void deleteContact(){
    int ret = 0;
    char* name = NULL;
    char* sql = NULL;

    name = (char*)malloc(sizeof(char)*10);
    sql = (char*)malloc(sizeof(char)*64);

    printf("Input the name of contact you want to delete:");
    scanf("%s",name);

    sprintf(sql,"delete from info where name='%s';",name);

    //to be simple, there will be no warning if the contact does not exist
    ret = sqlite3_exec(db,sql,0,0,0);
    if(ret != SQLITE_OK){
        printf("delete err:%s",sqlite3_errmsg(db));
    }
    else{
        printf("ok!");
    }

    free(name);
    free(sql);

}


int main()
{
    int ret = 0;
    int choice = 0;
    int ch = 0;
    char* errmsg = NULL;
    char* sql = NULL;

    //open the db if exist or create it
    ret = sqlite3_open("contact.db",&db);
    if(ret){
        fprintf(stderr,"Cannot open database:%s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(1);
    }
    else{
        printf("Open database successfully...\n");
    }

    //create the table info if not exists
    sql = (char*)malloc(sizeof(char)*128);//
    //strcpy(sql,);
    //printf("Copy sql successfully\n");
    ret = sqlite3_exec(db,"create table if not exists info(\
                    name varchar(10) primary key, \
                    age smallint, \
                    relation varchar(10), \
                    phone varchar(11));",0,0,&errmsg);
    if(ret != SQLITE_OK){
        //printf("Create table error\n");
        fprintf(stderr,"Create table err:%s\n",sqlite3_errmsg(db));
    }
    //printf("Create table successfully\n");

    //insert some initial records,
    //it will cause a err if not the frist time,but that does not matter
    strcpy(sql,"insert into info values('zhu',22,'本人','15109217871');");

    ret = sqlite3_exec(db,sql,0,0,&errmsg);
    if(ret != SQLITE_OK){
        fprintf(stderr,"Insert record err:%s\n",sqlite3_errmsg(db));
    }
    free(sql);
    //printf("Insert  record successfully\n");

    //main menu
    while(1){
        choice = showMenu();                        //show the menu and get the user's choose
        switch(choice){
            case 1:
                displayAll();                       //show all records in db
                break;
            case 2:
                addContact();
                break;
            case 3:
                deleteContact();
                break;
            case 4:
                alterContact();
                break;
            case 5:
                findContact();
                break;
            default:
                break;
        }

        //if back to main menu or not
        printf("\nBack to Menu 1(yes) / 0(no)?");
        scanf("%d",&ch);
        if(ch == 0){
            break;
        }
        //printf("\33[2J");
        system("clear");
    }

    sqlite3_close(db);
    return 0;
}