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

#define BUFFSIZE 256
#define OPTIONSIZE 20

char *zErrMsg = 0;
char strDB[BUFFSIZE];

static void
tableCreat(sqlite3 *db)
{
    int ret;
    //create the table
    sprintf(strDB,"CREATE TABLE STUSCORE(\
        ID INTEGER PRIMARY KEY ,\
        STUID INTEGER,\
        NAME VARCHAR(12),\
        SCORE INTEGER\
        );");
      
    ret = sqlite3_exec(db, strDB ,0 , 0, &zErrMsg);
    if(ret!= 0)
        {
            printf("create the table failed!\n");
            exit(-1);
        }
    printf("create the table success!\n");
}

static void
showTable(sqlite3 *db)
{
int nrow=1, ncolumn=1 ;
char **azResult ;
int i,j=0;
   
strcpy(strDB, "SELECT * FROM STUSCORE") ;
   
sqlite3_get_table(db, strDB, &azResult, &nrow, &ncolumn, &zErrMsg) ;
   
printf("nrow : %d ncolumn :%d \n", nrow, ncolumn) ;
for (i = 0; i < (nrow + 1) * ncolumn; i++)
{
   j++;
   printf("%7s ", azResult[i]) ;
   if (j == ncolumn)
   {
    printf("\n");
    j = 0;
   }
}
   
sqlite3_free_table(azResult);
}

static void
search(sqlite3 *db)
{
    char *stuidc = "STUID";
    char *namec = "NAME";
    char *scorec = "SCORE";

    char **azResult;
    int nrow = 0, ncolumn = 0;
   
    char optionAttrib[OPTIONSIZE];
    char optionValue[OPTIONSIZE];
     
    int i = 0 ;
    int j = 0;
   
    printf("Welcome to search!\n");
  
   
    while(1)
        {
            printf("Option(STUID,NAME,SCORE):");
            scanf("%s",optionAttrib);
            if(!strncasecmp(stuidc,optionAttrib,sizeof(optionAttrib)))
                {
                    printf("Option(num):");
                    scanf("%s",optionValue);
                    break;
                }
            else if(!strncasecmp(namec,optionAttrib,sizeof(optionAttrib)))
                {
                    printf("Option(name):");
                    scanf("%s",optionValue);
                    break;
                }
             else if(!strncasecmp(scorec,optionAttrib,sizeof(optionAttrib)))
                {
                    printf("Option(num(0~100)):");
                    scanf("%s",optionValue);
                    break;
                }
             printf("input error!Please try again!\n");
        }
    sqlite3_snprintf(sizeof(strDB), strDB,
       "SELECT * FROM STUSCORE WHERE %s = '%s'", optionAttrib,optionValue);

    sqlite3_get_table( db , strDB , &azResult , &nrow , &ncolumn , &zErrMsg );
    
    printf("%s\n",sqlite3_errmsg(db));

    printf("nrow : %d ncolumn :%d \n", nrow, ncolumn) ;
for (i = 0; i < (nrow+1)*ncolumn; i++)
{
   j++;
   printf("%7s ", azResult[i]) ;
   if (j == ncolumn)
   {
    printf("\n");
    j=0;
   }
}
    if(0 == nrow)
        {
            printf("there is no record you want!\n");
        }
sqlite3_free_table( azResult );
  
#ifdef _DEBUG_
        printf("zErrMsg = %s \n", zErrMsg);
    #endif
}

static void
_delete(sqlite3 *db)
{
    char *zErrMsg = 0;
   
    char *stuidc = "STUID";
    char *namec = "NAME";
    char *scorec = "SCORE";

    char **azResult;
    int nrow = 0, ncolumn = 0;
    memset(strDB,0,sizeof(strDB));

    int i = 0 ;
    int j = 0;
    int IDnum;
    int ret;
   
    char optionAttrib[OPTIONSIZE];
    char optionValue[OPTIONSIZE];
   
    printf("Welcome to delete!\n");
   
    while(1)
        {
            printf("Option(STUID,NAME,SCORE):");
            scanf("%s",optionAttrib);
           
            if(!strncasecmp(stuidc,optionAttrib,sizeof(optionAttrib)))
                {
                    printf("Option(num):");
                    scanf("%s",optionValue);
                    break;
                }
           
             if(!strncasecmp(namec,optionAttrib,sizeof(optionAttrib)))
                {
                    printf("Option(name):");
                    scanf("%s",optionValue);
                    break;
                }
              if(!strncasecmp(scorec,optionAttrib,sizeof(optionAttrib)))
                {
                    printf("Option(num(0~100)):");
                    scanf("%s",optionValue);
                    break;
                }
              printf("input error!Please try again!\n");
             
        }
    sqlite3_snprintf(sizeof(strDB), strDB,
       "SELECT * FROM STUSCORE WHERE %s = '%s'", optionAttrib,optionValue);
  
    ret = sqlite3_get_table( db , strDB , &azResult , &nrow , &ncolumn , &zErrMsg );
    if(ret != 0)
        {
            printf("SELECT ERROR");
            exit(-1);
        }
   
   
    printf("nrow : %d ncolumn :%d \n", nrow, ncolumn) ;
for (i = 0; i < (nrow+1)*ncolumn; i++)
{
   j++;
   printf("%7s ", azResult[i]) ;
   if (j == ncolumn)
   {
    printf("\n");
    j=0;
   }
}

    if(0 == nrow)
        {
            printf("there is no record you want!\n");
        }
    else
        {
            printf("%d groups are there,choose the ID\n",nrow);
           
            scanf("%d",&IDnum);
            memset(strDB,0,sizeof(strDB));

            sqlite3_snprintf(sizeof(strDB), strDB,
               "DELETE FROM STUSCORE WHERE ID = %d ;", IDnum);
           
            sqlite3_exec( db , strDB , 0 , 0 , &zErrMsg );
           
            showTable(db);
        }
}
static void
insert(sqlite3 *db)
{
    int ret;
    int stuid;
    char name[20];
    memset(name,0,sizeof(name));
    int score;
   
    printf("Welcome to insert!\n");

    printf("Option(stuid,name,score):");
   
    scanf("%d%s%d",&stuid,name,&score);
   
    memset(strDB,0,sizeof(strDB));

    sprintf(strDB, "INSERT INTO \"STUSCORE\" VALUES(NULL,%d,'%s',%d)",stuid,name,score);
  
   
    if((ret = sqlite3_exec(db,strDB,0,0,&zErrMsg))!= 0)
        {
            printf("Insert error\n");
            exit(-1);
        }

    printf("Insert Success!\n");
}

static sqlite3*
open_db(const char *filename)
{
sqlite3 *db = NULL;
int rc;

rc = sqlite3_open(filename, &db);

if(rc!=SQLITE_OK)
{
   fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
   sqlite3_close(db);
   exit(1);
}
   
return db;
}

static void
modify(sqlite3 *db)
{
    int ret;
    int stuid;
   
    char optionAttrib[OPTIONSIZE];
    char optionValue[OPTIONSIZE];
   
    printf("Welcome to modify");
   
    showTable(db);
   
    printf("choose ID,Attrib and New Value\n");
   
    scanf("%d%s%s",&stuid,optionAttrib,optionValue);

    memset(strDB,0,sizeof(strDB));

    sqlite3_snprintf(sizeof(strDB), strDB,
       "UPDATE STUSCORE SET %s='%s' WHERE ID=%d ;", optionAttrib,optionValue,stuid);

    printf("%s",strDB);
     if((ret = sqlite3_exec(db,strDB,0,0,&zErrMsg))!= 0)
        {
            printf("modify error\n");
            exit(-1);
        }

    printf("modify Success!\n");

    showTable(db);
}

int main()
{
    char option[OPTIONSIZE];
    char optionValue[OPTIONSIZE];
sqlite3 *db;
    char* searchc = "search";
    char* deletec = "delete";
    char* insertc = "insert";
    char* modifyc = "modify";
    char* showTablec = "showTable";
    char _switch[2];

db = open_db("STUSCORE.db");

    while(1)
        {
            printf("Welcome to Student Score DataBase\n");
            printf("Option(search|delete|insert|modify|showTable)");
            scanf("%s",option);

            if(!strncasecmp(searchc,option,sizeof(option)))
            {
                search(db);
            }
             if(!strncasecmp(deletec,option,sizeof(option)))
            {
                _delete(db);
            }
              if(!strncasecmp(insertc,option,sizeof(option)))
            {
                insert(db);
            }
               if(!strncasecmp(modifyc,option,sizeof(option)))
            {
                modify(db);
            }
               if(!strncasecmp(showTablec,option,sizeof(option)))
            {
                showTable(db);
            }
               printf("eixt?(y/n)");
               memset(_switch,0,sizeof(_switch));
               scanf("%s",_switch);
               if(!strcmp(_switch,"y"))
                {
                    printf("ByeBye!");
                    break;
                }
        }
    sqlite3_close(db); //关闭数据库
    return 0;
}