//
//  CLViewController.m
//  LessonDatabase
//
//  Created by lanouhn on 14-9-19.
//  Copyright (c) 2014年 [email protected] 陳聰雷. All rights reserved.
//

#import "CLViewController.h"
#import "DatabaseHelper.h"
#import "Student.h"
@interface CLViewController ()

@end

@implementation CLViewController

- (void)viewDidLoad
{
    [super viewDidLoad];
	// Do any additional setup after loading the view, typically from a nib.
    //數據庫(Database): 存放數據的倉庫, 存放的是一張的表, 特別像Excel, Numbers, 都以表格的形式存放數據, 可以創建多張表
    //常見的數據庫: sqlite, MySQL, SQLServer, Oracle, Access
    //為什麼要用數據庫 1 文件讀寫和歸檔讀取數據需要一次把數據全部讀出來, 占用內存開銷大 2 數據庫數據效率高, 體現在增刪改查
    //SQL Structured Query Language 用於對數據庫的操作語句 (增刪改查)
    //SQL 語句不區分大小寫, 字符串需要加""或''
    //主鍵: 是一條數據的唯一標示符, 一張表只能有一個主鍵, 主鍵不能夠重復, 一般把主鍵名設為"id", 不需要賦值, 會自增
    //*代表所有的字段
    //where是條件
    //創建表: creat table 表名 (字段名 字段數據類型 是否為主鍵, 字段名 字段數據類型, 字段名 字段數據類型...)
    //查: select 字段名 (或者*) from 表名 where 字段名 = 值
    //增: insert into 表名 (字段1, 字段2...) values (值1, 值2...)
    //改: update 表名 set 字段 = 值 where 字段 = 值
    //刪: delete from 表名 where 字段 = 值
    
}

- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

- (IBAction)selectAll:(id)sender {
    NSMutableArray *array = [DatabaseHelper getAllStudents];
    for (Student *stu in array) {
        NSLog(@"%@", stu);
    }
}

- (IBAction)selectOne:(id)sender {
    Student *stu = [DatabaseHelper getStudentWithID:2];
    NSLog(@"%@", stu);
}

- (IBAction)insetOne:(id)sender {
    Student *stu = [[Student alloc] init];
    stu.name = @"vaercly";
    stu.sex = @"man";
    stu.age = 22;
    BOOL result = [DatabaseHelper insertStudent:stu];
    NSLog(@"%d", result);
}

- (IBAction)updateName:(id)sender {
    [DatabaseHelper updateStudentName:@"陳聰雷" byID:5];
}

- (IBAction)deleteOne:(id)sender {
    [DatabaseHelper deleteStudentWithID:5];
}
@end
//
//  Datebase.m
//  LessonDatabase
//
//  Created by lanouhn on 14-9-19.
//  Copyright (c) 2014年 [email protected] 陳聰雷. All rights reserved.
//
#define FILE_NAME       @"Database.sqlite"
#import "Database.h"
static sqlite3 *db = nil;
@implementation Database
//打開數據庫
+ (sqlite3 *)openDB
{
    if (!db) {
        //1 獲取document文件夾的路徑
        //參數1: 文件夾的名字 參數2: 查找域 參數3: 是否使用絕對路徑
        NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
        //獲取數據庫文件的路徑
        NSString *dbPath = [docPath stringByAppendingPathComponent:FILE_NAME];
        //iOS 中管理文件的類, 負責復制文件, 刪除文件, 移動文件
        NSFileManager *fm = [NSFileManager defaultManager];
        //判斷document中是否有sqlite文件
        if (![fm fileExistsAtPath:dbPath]) {
            //獲取在*.app中sqlite文件的路徑
            NSString *boundlePath = [[NSBundle mainBundle] pathForResource:@"Database" ofType:@"sqlite"];
            NSError *error = nil;
            //將*.app中sqlite文件復制一份到dbPath
            BOOL result = [fm copyItemAtPath:boundlePath toPath:dbPath error:&error];
            //若復制文件失敗, 打印錯誤信息
            if (!result) {
                NSLog(@"%@", error);
            }
        }
        //打開數據庫 參數1: 文件路徑(UTF8String可以將OC的NSString轉為C中的char) 參數2: 接受數據庫的指針
        sqlite3_open([dbPath UTF8String], &db);
    }
    return db;
}

//關閉數據庫
+ (void)closeDB
{
    sqlite3_close(db);
    db = nil;
}
@end
//
//  DatabaseHelper.m
//  LessonDatabase
//
//  Created by lanouhn on 14-9-19.
//  Copyright (c) 2014年 [email protected] 陳聰雷. All rights reserved.
//

#import "DatabaseHelper.h"
#import "Student.h"
#import "Database.h"
@implementation DatabaseHelper
//查詢所有學生
+ (NSMutableArray *)getAllStudents
{
    //打開數據庫
    sqlite3 *db = [Database openDB];
    //數據庫操作指針 stmt:statement
    sqlite3_stmt *stmt = nil;
    //驗證SQL的正確性 參數1: 數據庫指針, 參數2: SQL語句, 參數3: SQL語句的長度 -1代表無限長(會自動匹配長度), 參數4: 返回數據庫操作指針, 參數5: 為未來做准備的, 預留參數, 一般寫成NULL
    int result = sqlite3_prepare_v2(db, "select * from Student", -1, &stmt, NULL);
    NSMutableArray *studentArr = [NSMutableArray array];
    //判斷SQL執行的結果
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {//存在一行數據
            //列數從0開始
            int ID = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *sex = sqlite3_column_text(stmt, 2);
            int age = sqlite3_column_int(stmt, 3);
            //blob類型的獲取
            //1 獲取長度
            int length = sqlite3_column_bytes(stmt, 4);
            //2 獲取數據
            const void *photo = sqlite3_column_blob(stmt, 4);
            //3 轉成NSData
            NSData *photoData = [NSData dataWithBytes:photo length:length];
            //4 轉成UIImage
            UIImage *image = [UIImage imageWithData:photoData];
            //封裝Student模型
            Student *student = [[Student alloc] init];
            student.ID = ID;
            student.name = [NSString stringWithUTF8String:(const char *)name];
            student.sex = [NSString stringWithUTF8String:(const char *)sex];
            student.age = age;
            student.photo = image;
            //添加到數組
            [studentArr addObject:student];
        }
    }
    //釋放stmt指針
    sqlite3_finalize(stmt);
    //關閉數據庫
    [Database closeDB];
    return studentArr;
}
//查詢單個學生
+ (Student *)getStudentWithID:(NSInteger)aID
{
    sqlite3 *db = [Database openDB];
    sqlite3_stmt *stmt = nil;
    NSString *sqlStr = [NSString stringWithFormat:@"select * from Student where id = %d", aID];
    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);
    Student *student = nil;
    if (result == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            int ID = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *sex = sqlite3_column_text(stmt, 2);
            int age = sqlite3_column_int(stmt, 3);
            int length = sqlite3_column_bytes(stmt, 4);
            const unsigned char *photo = sqlite3_column_blob(stmt, 4);
            NSData *photoData = [NSData dataWithBytes:photo length:length];
            UIImage *image = [UIImage imageWithData:photoData];
            student = [[Student alloc] init];
            student.ID = ID;
            student.name = [NSString stringWithUTF8String:(const char *)name];
            student.sex = [NSString stringWithUTF8String:(const char *)sex];
            student.age = age;
            student.photo = image;
        }
    }
    sqlite3_finalize(stmt);
    [Database closeDB];
    return student;
}
//添加一個新學生
+ (BOOL)insertStudent:(Student *)aStudent
{
    sqlite3 *db = [Database openDB];
    sqlite3_stmt *stmt = nil;
    NSString *sqlStr = [NSString stringWithFormat:@"insert into Student (name, sex, age) values ('%@', '%@', '%d')", aStudent.name, aStudent.sex, aStudent.age];
    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //判斷語句執行完成沒有
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            sqlite3_finalize(stmt);
            [Database closeDB];
            return YES;
        }
    }
    sqlite3_finalize(stmt);
    [Database closeDB];
    return NO;
}
//修改學生的姓名
+ (BOOL)updateStudentName:(NSString *)aName byID:(NSInteger)aID
{
    sqlite3 *db = [Database openDB];
    sqlite3_stmt *stmt = nil;
    NSString *sqlStr = [NSString stringWithFormat:@"update Student set name = '%@' where id = %d", aName, aID];
    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) {//覺的應加一個判斷, 若有這一行則修改
            if (sqlite3_step(stmt) == SQLITE_DONE) {
                sqlite3_finalize(stmt);
                [Database closeDB];
                return YES;
            }
        }
    }
    sqlite3_finalize(stmt);
    [Database closeDB];
    return NO;
}
//刪除一個學生
+ (BOOL)deleteStudentWithID:(NSInteger)aID
{
    sqlite3 *db = [Database openDB];
    sqlite3_stmt *stmt = nil;
    NSString *sqlStr = [NSString stringWithFormat:@"delete from Student where id = %d", aID];
    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) {//覺的應加一個判斷, 若有這一行則刪除
            if (sqlite3_step(stmt) == SQLITE_DONE) {
                sqlite3_finalize(stmt);
                [Database closeDB];
                return YES;
            }
        }
    }
    sqlite3_finalize(stmt);
    [Database closeDB];
    return NO;
}
@end
//
//  Student.h
//  LessonDatabase
//
//  Created by lanouhn on 14-9-19.
//  Copyright (c) 2014年 [email protected] 陳聰雷. All rights reserved.
//

#import 

@interface Student : NSObject
@property (nonatomic, assign) NSInteger ID;
@property (nonatomic, retain) NSString *name;
@property (nonatomic, retain) NSString *sex;
@property (nonatomic, assign) NSInteger age;
@property (nonatomic, retain) UIImage *photo;
@end