基于 Linux sqlite3 的通讯录

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


#define MAX_NAME 100
#define MAX_TEL 11
#define OK 1
#define ERROR 0


typedef char Elementtype;
typedef char Status;


void Sqlite3_insert();
void Sqlite3_deletename();
void Sqlite3_queryname();
void Sqlite3_printflist();
void Sqlite3_updatetel();
void Sqlite3_updatename();
void Sqlite3_printflist();
void Sqlite3_wipedata();


//void inserttel_db(Node*head);


int main(int argc, char ** argv)
{

system("clear");



    for(;;)
    {
   switch(menu_select())
   {
            case 1:
            {   
    Sqlite3_insert();
    break;
   }
   case 2:
   { 
        Sqlite3_printflist();
    break;
   }
   case 3:
   {   

Sqlite3_deletename();
break;
   }
   case 4:
   {   
   Sqlite3_updatetel();
   break;
   }
   case 5:
   {  
   Sqlite3_updatename();
break;

   }
case 6:
{ 
   Sqlite3_queryname();
break;
}
case 7:
{
   Sqlite3_wipedata();
   break;
}
   case 8: 
   {
       exit(0);
   }

   } 
}

    return 0; 
}


menu_select()
{  
    int c;
char str[100];
printf("----- Welcome to the address book system ------\n");
printf("|--------------1. insert-----------------------|\n");
printf("|--------------2. printf_list------------------|\n");
printf("|--------------3. delete_name------------------|\n");
printf("|--------------4. update_tel-------------------|\n");
printf("|--------------5. update_name----------------- |\n");
printf("|--------------6. query_name-------------------|\n");
printf("|--------------7. wipedata---------------------|\n");
printf("|--------------8. quit-------------------------|\n");
    printf("|----------------------------------------------|\n");

do
{
   printf("please input num  1 ~ 8 :\n");
        scanf ("%s",&str);
   c = atoi(str);
}while(c < 1 || c > 8);

return c;

}


 
void Sqlite3_insert()
{
char name[20] = {0};
int tel;
    int ret;
    sqlite3 *db;
    char *errmsg;


    ret = sqlite3_open("tel.db",&db);
    if(ret != SQLITE_OK)
    {
        perror("open");
   exit(1);
    }

    printf("please input name.\n");
scanf("%s",&name);
printf("please input tel.\n");
scanf("%d",&tel);

    char sql_insert[1024] = {0};

    sprintf(sql_insert,"insert into tel(name,tel) values('%s',%d);",name,tel);
printf("----------------------Sqlite3----------------------\n");
printf("%s\n",sql_insert);
printf("---------------------------------------------------\n");
    ret = sqlite3_exec(db,sql_insert,NULL,NULL,&errmsg);
    if(ret != SQLITE_OK)
    {
        perror("inserttel_db");
   exit(2);
    }


 
    ret = sqlite3_close(db);
    if(ret != SQLITE_OK)
    {
        perror("close");
   exit(3);
    }

   
}

void  Sqlite3_deletename()
{  
    char name_1[10] ;

    printf("please input the name you want to remove.\n");
scanf("%s",&name_1); 
int ret;
    sqlite3 *db;
    char *errmsg;


    ret = sqlite3_open("tel.db",&db);
    if(ret != SQLITE_OK)
    {
        perror("open");
   exit(1);
    }
   
    char sql_delete[1024] = {0};

    sprintf(sql_delete,"delete from tel where name = '%s';",name_1);

printf("----------------------Sqlite3----------------------\n");
printf("%s\n",sql_delete);
printf("---------------------------------------------------\n");
    ret = sqlite3_exec(db,sql_delete,NULL,NULL,&errmsg);
    if(ret != SQLITE_OK)
    {
        perror("delete");
   exit(2);
    }


 
    ret = sqlite3_close(db);
    if(ret != SQLITE_OK)
    {
        perror("close");
   exit(3);
    }
  
}


void  Sqlite3_queryname()
{
char name_2[20];

    printf("please input name you want to query .\n");
scanf("%s",&name_2);

int ret;
    sqlite3 *db;
    char *errmsg;
    int  row;
int  column;
char**result;

    ret = sqlite3_open("tel.db",&db);
    if(ret != SQLITE_OK)
    {
        perror("open");
   exit(1);
    }

char sql_query[1024] = {0};

    sprintf(sql_query,"select name,tel from tel where name = '%s';",name_2);


    ret = sqlite3_get_table(db, sql_query, &result, &row, &column, &errmsg);
if(ret != SQLITE_OK)
{
   perror("query_name");
        exit(2);
}
int i;
    int j;
    
printf("-----------------Sqlite3---------------------\n");

    for(i = 1;i <= row;i++)
{
        for(j = 0;j < column;j++)
{
printf("%s|",result[i * column + j]);

}
printf("\n");


}
printf("---------------------------------------------\n");
 
ret = sqlite3_close(db);
    if(ret != SQLITE_OK)
    {
        perror("close");
   exit(2);
    }

}


void  Sqlite3_updatetel()
{
    int te1_1;
int tel_2;

printf("Please input  your original number. \n");
scanf("%d", &te1_1);
printf("Please the number you want to change. \n");
scanf("%d", &tel_2);


int ret;
    sqlite3 *db;
    char *errmsg;


    ret = sqlite3_open("tel.db",&db);
    if(ret != SQLITE_OK)
    {
        perror("open");
   exit(1);
    }
   
    char sql_update_tel[1024] = {0};

    sprintf(sql_update_tel,"update tel set tel = %d where tel = %d;",tel_2,te1_1);
printf("-----------------------------------------------------------\n");
printf("%s\n",sql_update_tel);
printf("-----------------------------------------------------------\n");
    ret = sqlite3_exec(db,sql_update_tel,NULL,NULL,&errmsg);
    if(ret != SQLITE_OK)
    {
        perror("update_tel");
   exit(3);
    }


 
    ret = sqlite3_close(db);
    if(ret != SQLITE_OK)
    {
        perror("close");
   exit(2);
    }
 
}


void Sqlite3_updatename()
{


char name_1[10];
char name_2[10];

printf("Please input  your original name. \n");
scanf("%s", &name_1);

printf("Please the name you want to change.\n");
scanf("%s", &name_2);
  
int ret;
    sqlite3 *db;
    char *errmsg;


    ret = sqlite3_open("tel.db",&db);
    if(ret != SQLITE_OK)
    {
        perror("open");
   exit(1);
    }
   
    char sql_update_name[1024] = {0};

    sprintf(sql_update_name,"update tel set name = '%s' where name = '%s';",name_2,name_1);
printf("-----------------------------------------------------------\n");
printf("%s\n",sql_update_name);
printf("-----------------------------------------------------------\n");
    ret = sqlite3_exec(db,sql_update_name,NULL,NULL,&errmsg);
    if(ret != SQLITE_OK)
    {
        perror("update_name");
   exit(3);
    }


 
    ret = sqlite3_close(db);
    if(ret != SQLITE_OK)
    {
        perror("close");
   exit(2);
    }  

}
void Sqlite3_printflist()
{
    int ret;
    sqlite3* db;
    char** result;
    char* errmsg;
    int row;
    int column;


    ret = sqlite3_open("tel.db", &db);
    if (ret != SQLITE_OK)
    {
        perror("open");
   exit(1);
    }


    char sql_select[] = "select * from tel order by name";
    ret = sqlite3_get_table(db, sql_select, &result, &row, &column, &errmsg);


    if (ret != SQLITE_OK)
        {
        perror("selest");
   exit(2);
        }
    int i, j;
int count = 0;
    printf("---------------------list--------------------\n");
    for (i = 1; i <= row; i++)
    {
        for (j = 0; j < column; j++)
   {
   printf("%s|", result[i * column + j]);
   }
    count++;

printf("\n");
    }

if(count == 0)
{
printf("Sqlite3 is empty.\n");
}
printf("---------------------------------------------\n");


}


void  Sqlite3_wipedata()
{  
     
int ret;
    sqlite3 *db;
    char *errmsg;


    ret = sqlite3_open("tel.db",&db);
    if(ret != SQLITE_OK)
    {
        perror("open");
   exit(1);
    }
   
    char sql_wipedata[1024] = {0};

    sprintf(sql_wipedata,"delete from tel where tel >= 0");

    ret = sqlite3_exec(db,sql_wipedata,NULL,NULL,&errmsg);
    if(ret != SQLITE_OK)
    {
        perror("wipedata");
   exit(2);
    }


 
    ret = sqlite3_close(db);
    if(ret != SQLITE_OK)
    {
        perror("close");
   exit(3);
    }
  
}