ios学习--sqlite3数据库的使用

iphone sqlite3数据库的使用

//
//  VgirlDataBase.h
//  Vgirl
//
//  Created by  on 12-2-2.
//  Copyright (c) 2012年 __MyCompanyName__. All rights reserved.
//

#import <Foundation/Foundation.h>
#import <sqlite3.h>
#import "User.h"
#import "Status.h"

static NSString *DATABASE_NAME = @"xxx.db";
static NSString *DATABASE_PATH = @"Documents";

@interface VgirlDataBase : NSObject{
    sqlite3 *db;
    const char *cdatabasePath;
}
-(void)open;
-(void)close;
-(void)doBegin;
-(void)doCommit;

//
-(void)insertWeiboCount:(long long)statusid setCommentsCount:(int)commentsCount setRetweetsCount:(int)retweetsCount;
-(void)insertWeiboUser:(User *)weiboUser;
-(void)insertWeiboStatus:(Status *)weiboStatus setParentStatusID:(long long)parentStatusID;

-(NSString *)getNSStringFromChar:(char *)text;
-(Status *)getChildStatusByStatusID:(long long)statusid;
-(NSMutableArray *)getStatusListByUID:(NSString *)uid setCount:(int)rowCount;

-(void)deleteAllWeiboCount;
-(void)deleteAllWeiboUser;
-(void)deleteAllWeiboStatus;
-(void)deleteAllWeiboTables;


@end

－－－－－－－－－－－－－

//
//  VgirlDataBase.m
//  Vgirl
//
//  Created by  on 12-2-2.
//  Copyright (c) 2012年 __MyCompanyName__. All rights reserved.
//

#import "VgirlDataBase.h"
#import "PushMessage.h"

@implementation VgirlDataBase

/*
 * 初始化数据库vgirl.db
 * 如果不存在则新建表结构
 */
-(id)init{
    if(self = [super init]){
       
        @try {
            NSString *locationDir = [NSHomeDirectory() stringByAppendingPathComponent:DATABASE_PATH];
            NSString *dataBasePath = [locationDir stringByAppendingFormat:@"/%@",DATABASE_NAME];
            NSLog(@"database path: %@", dataBasePath);
           
            if (![[NSFileManager defaultManager] fileExistsAtPath:locationDir]) {
                [[NSFileManager defaultManager] createDirectoryAtPath:locationDir withIntermediateDirectories:NO attributes:nil error:nil];
            }
           
            cdatabasePath = [dataBasePath UTF8String];
            if(sqlite3_open(cdatabasePath, &db) != SQLITE_OK){
                sqlite3_close(db);
                NSLog(@"Failed to init vgirl database, please check...");
                return self;
            }
           
            char *errorMsg;    
            const char *MESSAGE_TABLE_CREATE_SQL = " CREATE TABLE IF NOT EXISTS pt_message (id integer primary key autoincrement, uid text not null, vgirl_message_id integer not null, vgirl_title text not null, vgirl_message text not null, vgirl_message_time text not null, vgirl_message_type integer not null, vgirl_message_has_read integer default 0, desc text ); ";
           
            const char *WEIBO_STATUS_TABLE_CREATE_SQL = " CREATE TABLE IF NOT EXISTS pt_weibo_status (id integer primary key autoincrement, created_at integer not null, status_id integer not null, weibo_uid integer not null, weibo_text text not null, weibo_source text, thumbnail_pic text, bmiddle_pic text, parent_status_id integer not null ); ";
           
            const char *WEIBO_USER_TABLE_CREATE_SQL = " CREATE TABLE IF NOT EXISTS pt_weibo_user (id integer primary key autoincrement, weibo_uid integer not null, screen_name text, user_name text, location text, description text, profile_image_url text, gender text, followers_count integer, friends_count integer, statuses_count integer, verified integer); ";
           
            const char *WEIBO_COUNT_TABLE_CREATE_SQL = " CREATE TABLE IF NOT EXISTS pt_weibo_count (id integer primary key autoincrement, status_id integer not null, weibo_comments integer, weibo_rt integer ); ";
           
            if(sqlite3_exec(db, MESSAGE_TABLE_CREATE_SQL, NULL, NULL, &errorMsg) != SQLITE_OK){
                NSLog(@"sqlite3_exec MESSAGE_TABLE_CREATE_SQL error...%s", errorMsg);
            }
           
            if(sqlite3_exec(db, WEIBO_STATUS_TABLE_CREATE_SQL, NULL, NULL, &errorMsg) != SQLITE_OK){
                NSLog(@"sqlite3_exec WEIBO_STATUS_TABLE_CREATE_SQL error...%s", errorMsg);
            }
           
            if(sqlite3_exec(db, WEIBO_USER_TABLE_CREATE_SQL, NULL, NULL, &errorMsg) != SQLITE_OK){
                NSLog(@"sqlite3_exec WEIBO_USER_TABLE_CREATE_SQL error...%s", errorMsg);
            }
           
            if(sqlite3_exec(db, WEIBO_COUNT_TABLE_CREATE_SQL, NULL, NULL, &errorMsg) != SQLITE_OK){
                NSLog(@"sqlite3_exec WEIBO_COUNT_TABLE_CREATE_SQL error...%s", errorMsg);
            }
           
            sqlite3_close(db);
            NSLog(@"init vgirl database success!");
        }
        @catch (NSException *exception) {
            NSLog(@"init vgirl database error!");
        }
       
    }
    return self;
}

-(void)dealloc{
    db = nil;
    cdatabasePath = nil;
}

/*
 * 打开数据库
 */
-(void)open{
    if (sqlite3_open(cdatabasePath, &db) == SQLITE_OK) {
        NSLog(@"open vgirl database ok...");
    }else{
        sqlite3_close(db);
        NSLog(@"Failed to open vgirl database, please check...");
    }
   
}

/*
 * 关闭数据库
 */
-(void)close{
    sqlite3_close(db);
    NSLog(@"close vgirl database...");
}

//

/**
 * 开始事务
 */
-(void)doBegin{
    char *errorMsg;
    if(sqlite3_exec(db, "begin;", NULL, NULL, &errorMsg) != SQLITE_OK){
        NSLog(@"sqlite3_exec BEGIN_SQL error...%s", errorMsg);
    }
}

/**
 * 提交事务
 */
-(void)doCommit{
    char *errorMsg;
    if(sqlite3_exec(db, "commit;", NULL, NULL, &errorMsg) != SQLITE_OK){
        NSLog(@"sqlite3_exec COMMIT_SQL error...%s", errorMsg);
    }
}


/*
 * 写入微博转发数，评论数
 * 参数:
 * statusid: 微博id
 * commentsCount: 微博的评论数
 * retweetsCount: 微博的转发数
 */
-(void)insertWeiboCount:(long long)statusid setCommentsCount:(int)commentsCount setRetweetsCount:(int)retweetsCount{
    char *sql = "insert or replace into pt_weibo_count(id, status_id, weibo_comments, weibo_rt) values (?, ?, ?, ?);";
    sqlite3_stmt *stmt;
    if(sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK){
        sqlite3_bind_int64(stmt, 1, statusid);
        sqlite3_bind_int64(stmt, 2, statusid);
        sqlite3_bind_int(stmt, 3, commentsCount);
        sqlite3_bind_int(stmt, 4, retweetsCount);
    }
    if (sqlite3_step(stmt) != SQLITE_DONE) {
        NSLog(@"insertWeiboCount error, please checking...");
    }else{
        NSLog(@"insertWeiboCount success!");
    }
   
    sqlite3_finalize(stmt);
   
    stmt = nil;
    sql = nil;
}

/*
 * 写入微博user
 * 参数:
 * User: 微博user
 */
-(void)insertWeiboUser:(User *)weiboUser{
    char *sql = "insert or replace into pt_weibo_user(id, weibo_uid, screen_name, user_name, location, description, profile_image_url, gender, followers_count, friends_count, statuses_count, verified) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
    sqlite3_stmt *stmt;
    if(sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK){
        sqlite3_bind_int64(stmt, 1, weiboUser.userId);
        sqlite3_bind_int64(stmt, 2, weiboUser.userId);
        sqlite3_bind_text(stmt, 3, [weiboUser.screenName UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 4, [weiboUser.name UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 5, [weiboUser.location UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 6, [weiboUser.description UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 7, [weiboUser.profileImageUrl UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 8, weiboUser.gender);
        sqlite3_bind_int(stmt, 9, weiboUser.followersCount);
        sqlite3_bind_int(stmt, 10, weiboUser.friendsCount);
        sqlite3_bind_int(stmt, 11, weiboUser.statusesCount);
        int tempVerified = weiboUser.verified ? 1 : 0;//1:true, 0:false
        sqlite3_bind_int(stmt, 12, tempVerified);
    }
    if (sqlite3_step(stmt) != SQLITE_DONE) {
        NSLog(@"insertWeiboUser error, please checking...");
    }else{
        NSLog(@"insertWeiboUser success!");
    }
   
    sqlite3_finalize(stmt);
   
    stmt = nil;
    sql = nil;
}

/*
 * 写入微博内容
 * 参数：
 * Status: 微博结构
 * parentStatusID: 微博id，微博的层次通过parentstatusid参数区分，
 * 顶层微博：parentstatusid为－1，子微博：parentstatusid为父微博的statusid
 */
-(void)insertWeiboStatus:(Status *)weiboStatus setParentStatusID:(long long)parentStatusID{
    char *sql = "insert or replace into pt_weibo_status(id, created_at, status_id, weibo_uid, weibo_text, weibo_source, thumbnail_pic, bmiddle_pic, parent_status_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?);";
    sqlite3_stmt *stmt;
    if(sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK){
        sqlite3_bind_int64(stmt, 1, weiboStatus.statusId);
        sqlite3_bind_int64(stmt, 2, weiboStatus.createdAt);
        sqlite3_bind_int64(stmt, 3, weiboStatus.statusId);
        sqlite3_bind_int64(stmt, 4, weiboStatus.user.userId);
        sqlite3_bind_text(stmt, 5, [weiboStatus.text UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 6, [weiboStatus.source UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 7, [weiboStatus.thumbnailPic UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 8, [weiboStatus.bmiddlePic UTF8String], -1, NULL);
        sqlite3_bind_int64(stmt, 9, parentStatusID);
    }
    if (sqlite3_step(stmt) != SQLITE_DONE) {
        NSLog(@"insertWeiboStatus error, please checking...");
    }else{
        NSLog(@"insertWeiboStatus success!");
    }
   
    sqlite3_finalize(stmt);
   
    stmt = nil;
    sql = nil;
}

-(NSString *)getNSStringFromChar:(char *)text{
    if (!text) {
        return nil;
    }
    return [NSString stringWithUTF8String:text];
}

/*
 * 根据父微博的statusid，查找是否存在子微博
 * 参数:
 * statusid: 微博id
 * 返回:
 * status
 */
-(Status *)getChildStatusByStatusID:(long long)statusid{
   
   
    Status *childStatus = nil;
    char *sql = " select distinct ptws.CREATED_AT as created_at, ptws.STATUS_ID as status_id, ptws.WEIBO_UID as weibo_uid, ptws.WEIBO_TEXT as weibo_text, ptws.WEIBO_SOURCE as weibo_source, ptws.THUMBNAIL_PIC as thumbnail_pic, ptws.BMIDDLE_PIC as bmiddle_pic, ptwu.SCREEN_NAME as screen_name, ptwu.USER_NAME as user_name, ptwu.LOCATION as location, ptwu.DESCRIPTION as description, ptwu.PROFILE_IMAGE_URL as profile_image_url, ptwu.GENDER as gender, ptwu.FOLLOWERS_COUNT as followers_count, ptwu.FRIENDS_COUNT as friends_count, ptwu.STATUSES_COUNT as statuses_count, ptwu.VERIFIED as verified from pt_weibo_status ptws, pt_weibo_user ptwu, pt_weibo_count ptwc where ptws.WEIBO_UID = ptwu.WEIBO_UID and ptws.PARENT_STATUS_ID = ? ; ";
   
   
    sqlite3_stmt *stmt;
    if(sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK){
        sqlite3_bind_int64(stmt, 1, statusid);
       
    }
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        NSLog(@"child");
       
        childStatus = [[Status alloc] init];
       
        childStatus.createdAt = sqlite3_column_int64(stmt, 0);
        childStatus.statusId = sqlite3_column_int64(stmt, 1);
        childStatus.text = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 3)] retain];
        childStatus.source = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 4)] retain];
        childStatus.thumbnailPic = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 5)] retain];
        childStatus.bmiddlePic = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 6)] retain];
       
        User *user = [[User alloc] init];
        user.userId = sqlite3_column_int64(stmt, 2);
        user.screenName = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 7)] retain];
        user.name = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 8)] retain];
        user.location = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 9)] retain];
        user.description = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 10)] retain];
        user.profileImageUrl = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 11)] retain];
        user.gender = sqlite3_column_int(stmt, 12);
        user.followersCount = sqlite3_column_int(stmt, 13);
        user.friendsCount = sqlite3_column_int(stmt, 14);
        user.statusesCount = sqlite3_column_int(stmt, 15);
        user.verified = (sqlite3_column_int(stmt, 16) > 0) ? YES : NO;
        childStatus.user = user;
        [user release];
       
    }
    sqlite3_finalize(stmt);
   
    stmt = nil;
    sql = nil;
   
    if(childStatus){
        return [childStatus autorelease];
    }else{
        return nil;
    }
}

/*
 * 根据微博uid，返回指定条数rowcount微博
 * 参数:
 * uid: 微博uid
 * rowCount: 返回微博条数
 * 返回:
 * status list
 */
-(NSMutableArray *)getStatusListByUID:(NSString *)uid setCount:(int)rowCount{
    NSMutableArray *statusList = [[NSMutableArray alloc] init];
   
    char *sql = " select distinct ptws.CREATED_AT as created_at, ptws.STATUS_ID as status_id, ptws.WEIBO_UID as weibo_uid, ptws.WEIBO_TEXT as weibo_text, ptws.WEIBO_SOURCE as weibo_source, ptws.THUMBNAIL_PIC as thumbnail_pic, ptws.BMIDDLE_PIC as bmiddle_pic, ptwu.SCREEN_NAME as screen_name, ptwu.USER_NAME as user_name, ptwu.LOCATION as location, ptwu.DESCRIPTION as description, ptwu.PROFILE_IMAGE_URL as profile_image_url, ptwu.GENDER as gender, ptwu.FOLLOWERS_COUNT as followers_count, ptwu.FRIENDS_COUNT as friends_count, ptwu.STATUSES_COUNT as statuses_count, ptwu.VERIFIED as verified, ptwc.WEIBO_COMMENTS as weibo_comments, ptwc.WEIBO_RT as weibo_rt from pt_weibo_status ptws, pt_weibo_user ptwu, pt_weibo_count ptwc where ptws.WEIBO_UID = ptwu.WEIBO_UID  and ptws.STATUS_ID = ptwc.STATUS_ID and ptws.PARENT_STATUS_ID <= 0  order by ptws.created_at desc limit ? ; ";
   
    sqlite3_stmt *stmt;
    if(sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK){
        //sqlite3_bind_text(stmt, 1, [uid UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 1, rowCount);
    }
   
    while (sqlite3_step(stmt) == SQLITE_ROW) {       
        Status *status = [[Status alloc] init];
       
        status.createdAt = sqlite3_column_int64(stmt, 0);
        status.statusId = sqlite3_column_int64(stmt, 1);
        status.text = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 3)] retain];
        status.source = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 4)] retain];
        status.thumbnailPic = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 5)] retain];
        status.bmiddlePic = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 6)] retain];
       
        status.retweetedStatus = [self getChildStatusByStatusID:sqlite3_column_int64(stmt, 1)];
       
        User *user = [[User alloc] init];
        user.userId = sqlite3_column_int64(stmt, 2);
        user.screenName = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 7)] retain];
        user.name = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 8)] retain];
        user.location = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 9)] retain];
        user.description = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 10)] retain];
        user.profileImageUrl = [[self getNSStringFromChar:(char *)sqlite3_column_text(stmt, 11)] retain];
        user.gender = sqlite3_column_int(stmt, 12);
        user.followersCount = sqlite3_column_int(stmt, 13);
        user.friendsCount = sqlite3_column_int(stmt, 14);
        user.statusesCount = sqlite3_column_int(stmt, 15);
        user.verified = (sqlite3_column_int(stmt, 16) > 0) ? YES : NO;
        status.user = user;
        [user release];
       
        status.commentsCount = sqlite3_column_int(stmt, 17);
        status.retweetsCount = sqlite3_column_int(stmt, 18);
       
        [statusList addObject:status];
        [status release];
       
    }

    sqlite3_finalize(stmt);
   
    stmt = nil;
    sql = nil;
   
    return [statusList autorelease];
}

/*
 * 删除所有微博的评论数和转发数记录
 */
-(void)deleteAllWeiboCount{
    char *sql = "delete from pt_weibo_count;";
    sqlite3_stmt *stmt;
    if(sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK){
       //
    }
    if (sqlite3_step(stmt) != SQLITE_DONE) {
        NSLog(@"deleteAllWeiboCount error, please checking...");
    }else{
        NSLog(@"deleteAllWeiboCount success!");
    }
   
    sqlite3_finalize(stmt);
   
    stmt = nil;
    sql = nil;
}

/*
 * 删除所有微博的用户资料记录
 */
-(void)deleteAllWeiboUser{
    char *sql = "delete from pt_weibo_user;";
    sqlite3_stmt *stmt;
    if(sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK){
        //
    }
    if (sqlite3_step(stmt) != SQLITE_DONE) {
        NSLog(@"deleteAllWeiboUser error, please checking...");
    }else{
        NSLog(@"deleteAllWeiboUser success!");
    }
   
    sqlite3_finalize(stmt);
   
    stmt = nil;
    sql = nil;
}

/*
 * 删除所有微博内容
 */
-(void)deleteAllWeiboStatus{
    char *sql = "delete from pt_weibo_status;";
    sqlite3_stmt *stmt;
    if(sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK){
        //
    }
    if (sqlite3_step(stmt) != SQLITE_DONE) {
        NSLog(@"deleteAllWeiboUser error, please checking...");
    }else{
        NSLog(@"deleteAllWeiboUser success!");
    }
   
    sqlite3_finalize(stmt);
   
    stmt = nil;
    sql = nil;
}

/*
 * 删除和微博相关的所有数据
 */
-(void)deleteAllWeiboTables{
    [self deleteAllWeiboCount];
    [self deleteAllWeiboUser];
    [self deleteAllWeiboStatus];
}

@end