operationdb.c

linux下的网站

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
 
static int sort_callback(void *p, int argc, char **argv, char **argvv)
{
    int i;
    int time = *(int *) p;     
    time++;
    *(int *) p = time;
          
    printf("[%d]:  ", time);
    for(i = 0; i < argc; i++)
    printf("%8s ", argv[i]? argv[i]: "NULL");
    printf("\n");     
    return 0;    
}

int do_insert(sqlite3 *db)
{
    int ret = 0;    
    char name[10];
    char gender[10];
    char score_a[10];
    char score_b[10];
    char score_c[10];
    char *sql = NULL;
    
    printf("Input Name:\n");
    scanf("%s",name);
    printf("Input Gender:\n");
    scanf("%s",gender);    
    printf("Input Score A:\n");
    scanf("%s",score_a);    
    printf("Input Score B:\n");
    scanf("%s",score_b);    
    printf("Input Score C:\n");
    scanf("%s",score_c);    

    printf("Name:%s,Gender:%s,Score A:%s,Score B:%s,Score C:%s\n",
     name,gender,score_a,score_b,score_c);

    sql = sqlite3_mprintf("insert into student values(%Q, %Q, %s, %s, %s)", 
                          name,gender,score_a,score_b,score_c);    

    ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
    sqlite3_free(sql);    
    if(ret != SQLITE_OK) {
        fputs("insert error\n",stderr);
    }    
    return ret;
}    

int do_delete(sqlite3 *db)
{
    int ret = 0;    
    char name[10];
    char *sql = NULL;

    printf("Input Name:\n");
    scanf("%s",name);

    sql = sqlite3_mprintf("delete from student where name=%Q", name);
    ret = sqlite3_exec(db, sql, NULL,NULL ,NULL);
    sqlite3_free(sql);
    if(ret != SQLITE_OK) {
        fputs("delete error\n",stderr);
    }       
    
    return ret;
}

int do_update(sqlite3 *db)
{
    int ret = 0;
    char name[10];
    char gender[10];
    char score_a[10];
    char score_b[10];
    char score_c[10];
    char *sql = NULL;
    
    printf("Input Name:\n");
    scanf("%s",name);
    printf("Input Gender:\n");
    scanf("%s",gender);    
    printf("Input Score A:\n");
    scanf("%s",score_a);    
    printf("Input Score B:\n");
    scanf("%s",score_b);    
    printf("Input Score C:\n");
    scanf("%s",score_c);    

    printf("Name:%s,Gender:%s,Score A:%s,Score B:%s,Score C:%s\n",
     name,gender,score_a,score_b,score_c);

    sql = sqlite3_mprintf("update student set "
         "gender=%Q, scoreA=%s, scoreB=%s, "
         "scoreC=%s where name=%Q", 
                            gender,score_a,score_b,score_c,name);

    ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
    sqlite3_free(sql);    
    if(ret != SQLITE_OK) {
        fputs("update error\n",stderr);
    }   
    return ret;
}

int do_sort(sqlite3 *db)
{
    int ret = 0;
    int time = 0;
    
    ret  = sqlite3_exec(db, 
           "select name, gender, scoreA, "
           "scoreB, scoreC, scoreA+scoreB+scoreC "
           "from student order by scoreA+scoreB+scoreC desc",
           sort_callback, &time, NULL);          
    if(ret != SQLITE_OK) {
        fputs("sort error\n",stderr);
    }    
    return ret;
}

void displaymenu(void)
{
    printf("1. Insert Record\n");
    printf("2. Delete Record\n");
    printf("3. Update Record\n");
    printf("4. Sort\n");
    printf("q. quit\n");    
    printf("Please Select [1-4]:\n");
    return;    
}    

int main(void)
{
    sqlite3 *db;
    char *err = 0;
    int ret = 0;
    int empty = 1;
    int options = 0;
    int donext = 1;

    ret = sqlite3_open("./test.db", &db);
     
    if(ret != SQLITE_OK) {
        fputs(sqlite3_errmsg(db), stderr);
        fputs("\n", stderr);
        exit(1);
    }
    
    displaymenu();
    
    while(donext)
    {   
        options = getchar();
        switch(options){
            case '1':
            {
                do_insert(db);
                displaymenu();
            }
                break;        
            case '2':
            {
                do_delete(db);
                displaymenu();
            }
                break;        
            case '3':
            {
                do_update(db);
                displaymenu();
            }
                break;        
            case '4':
            {
                do_sort(db);
                displaymenu();
            }
                break;        
            case 'q':
            {
                donext = 0;
            }
                break;        
            default:
            {
            }
            break;        
        } 
    }
    
    sqlite3_close(db);
    return 0;
}