gpephone采用sqlite作为数据库

其操作与其他的数据库一样，下面是一些例子：
先创建数据库sqlite3 callhistory.db

CREATE TABLE callhistory(
   id INTEGER PRIMARY KEY, //id自动递增的方式
        type INTEGER,
        num TEXT,
        name TEXT,
        date TEXT,
        time TEXT,
        duration INTEGER
);
之后可得如下
 dd@dd:/nfsroot/rootfs/usr/gpephone/share/callhistory/database$ sqlite3 callhistory.db
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> .schema
CREATE TABLE callhistory(
   id INTEGER PRIMARY KEY,
        type INTEGER,
        num TEXT,
        name TEXT,
        date TEXT,
        time TEXT,
        duration INTEGER
);
sqlite>



void add_call_history(tel_call_history_t state, char *name, char *num, char *date, char *time, unsigned long duration)
{
    sqlite3 *db;
    sqlite3_stmt *stat;
    gchar *sql_str;

    int rc = sqlite3_open (PREFIX "/share/callhistory/database/callhistory.db", &db);
    if (rc!= SQLITE_OK)
    {
        fprintf(stderr, "Can't open datebase: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return ;
    }
    sql_str = g_strdup("insert into callhistory(id, type, num, name, date, time, duration)values(NULL,?,?,?,?,?,?)"); //第0个为数据库每行的id，，采用自动递增的方式，故第0个为NULL

    rc = sqlite3_prepare(db, sql_str, -1, &stat, 0);
    if (rc!=SQLITE_OK)
    {
        printf("ERROR: prepare fail\n");
        sqlite3_finalize(stat);
        sqlite3_close(db);
        return;
    }

    sqlite3_bind_int(stat, 1, state);        //1   绑定，也就是给values(NULL,?,?,?,?,?,?)");里的赋值
    sqlite3_bind_text(stat, 2, num, strlen(num), NULL); //2
    sqlite3_bind_text(stat, 3, name, strlen(name), NULL); //3
    sqlite3_bind_text(stat, 4, date, strlen(date), NULL); //4
    sqlite3_bind_text(stat, 5, time, strlen(time), NULL); //5
    sqlite3_bind_int(stat, 6, duration); //6
    rc = sqlite3_step(stat);
    if (rc != SQLITE_ROW)
    {
        printf("there is %s\n",sqlite3_errmsg(db));
    }
    g_free(sql_str);
    sqlite3_finalize(stat);
    sqlite3_close(db);   
}

static void insert_items_to_list(voch_call_type_t type)
{
    //gchar name_or_num[30], date[20], time[20];
    //gchar *text[] = {name_or_num, date, time};
    gchar name_or_num[60];
    gchar *text[] = {name_or_num};
    gint number;
    sqlite3 *db;
    sqlite3_stmt* stat;
    char sql_str[100];
    call_item_detail_info_t *user_data;
    const unsigned char *tmp=NULL;
    int rc;
   
    current_row = -1;
    rc = sqlite3_open (PREFIX "/share/callhistory/database/callhistory.db", &db);
    if (rc!= SQLITE_OK)
    {
        fprintf(stderr, "Can't open datebase: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return ;
    }

    sprintf(sql_str, "select * from callhistory where type = %d", type);
    printf("%s\n",sql_str);
    rc = sqlite3_prepare(db, sql_str, -1, &stat, 0);
    if (rc!=SQLITE_OK)
    {
        printf("select ERROR\n");
        sqlite3_close(db);
        return;
    }
    rc = sqlite3_step(stat);

    gtk_clist_freeze(GTK_CLIST(self.clist));
    while (rc == SQLITE_ROW)
    {
        tmp = sqlite3_column_text(stat,3);
        if ((tmp==NULL) || (strlen(tmp) == 0))
            tmp = sqlite3_column_text(stat,2);
        //strcpy(name_or_num, tmp);
        //strcpy(date, sqlite3_column_text(stat,4));
        //strcpy(time, sqlite3_column_text(stat,5));
        memset(name_or_num, 0, sizeof(name_or_num));
        sprintf(name_or_num, "%-20s%-10s %-10s", tmp, sqlite3_column_text(stat,4), sqlite3_column_text(stat,5));
        number = gtk_clist_append (GTK_CLIST (self.clist), text);
        user_data = (call_item_detail_info_t *)g_malloc(sizeof(call_item_detail_info_t));
        user_data->id = sqlite3_column_int(stat,0);
        strcpy(user_data->name, sqlite3_column_text(stat,3));
        strcpy(user_data->num,  sqlite3_column_text(stat,2));
        strcpy(user_data->date,  sqlite3_column_text(stat,4));
        strcpy(user_data->time,  sqlite3_column_text(stat,5));
        user_data->duration = sqlite3_column_int(stat,6);
        gtk_clist_set_row_data_full(GTK_CLIST(self.clist), number, (gpointer)user_data,
            destroy_item_data);       
        rc = sqlite3_step(stat);
    }
    gtk_clist_thaw(GTK_CLIST(self.clist));
    rc = sqlite3_finalize(stat);
    sqlite3_close (db);
}

static void delete_history(void *user_data)
{
    sqlite3 *db;
    sqlite3_stmt *stat;
    gchar *sql_str;
    gboolean flag=TRUE;
    int *type;
    int rc;
   
    *type=GPOINTER_TO_INT(user_data);


    rc = sqlite3_open (PREFIX "/share/callhistory/database/callhistory.db", &db);
    if (rc!= SQLITE_OK)
    {
        fprintf(stderr, "Can't open datebase: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        flag = FALSE;
        goto show_result;
    }

    if (*type != DEL_HISTORY_ALL)
        sql_str = g_strdup_printf("delete from callhistory where type = %d", *type);
    else
        sql_str = g_strdup_printf("delete from callhistory");
       
    rc = sqlite3_prepare(db, sql_str, -1, &stat, 0);
    if (rc!=SQLITE_OK)
    {
        printf("ERROR: prepare fail\n");
        sqlite3_finalize(stat);
        g_free(sql_str);
        sqlite3_close(db);
        flag = FALSE;
        goto show_result;       
    }
    rc = sqlite3_step(stat);
    if (rc != SQLITE_DONE)
    {
        printf("Remove from call history %s\n",sqlite3_errmsg(db));
        sqlite3_finalize(stat);
        g_free(sql_str);
        sqlite3_close(db);
        flag = FALSE;
        goto show_result;       
    }

    g_free(sql_str);
    sqlite3_finalize(stat);
    sqlite3_close(db);

show_result:   
    if (flag)
        show_message("delete success!");
    else
        show_message("delete failure! maybe the database is readonly!");
}

附件为一些测试的小例子

文件:	phonebook.tar.gz
大小:	20KB
下载:	下载