IOS 5 | SQLite | How-To Create a Multi Database IOS 5 App with SQLite

//
//  Person.h
//  MultiDB
//
//  Created by Kevin Languedoc on 1/26/12.
//  Copyright (c) 2012 kCodebook. All rights reserved.
//

#import <Foundation/Foundation.h>

@interface Person : NSObject{
    NSString * name;
    NSString * gender;
    NSString * fromWhere;
}

@property(nonatomic, retain) NSString * name;
@property(nonatomic, retain) NSString * gender;
@property(nonatomic, retain) NSString * fromWhere;
                             

@end

//
//  Person.m
//  MultiDB
//
//  Created by Kevin Languedoc on 1/26/12.
//  Copyright (c) 2012 kCodebook. All rights reserved.
//

#import "Person.h"

@implementation Person

@synthesize name;
@synthesize gender;
@synthesize fromWhere;



@end

//
//  MultiDAO.h
//  MultiDB
//
//  Created by Kevin Languedoc on 1/25/12.
//  Copyright (c) 2012 kCodebook. All rights reserved.
//

#import <Foundation/Foundation.h>
#import <sqlite3.h>
#import "Person.h"

@interface MultiDAO : NSObject{
     sqlite3 *db;
     NSFileManager *fileMgr;
     NSString *homeDir;
     Person * people;
}



@property (nonatomic,retain) NSString *fileMgr;
@property (nonatomic,retain) NSString *homeDir;
-(void)databaseOperations:(NSString *)OperationName:(Person *) person;

-(Person *) selectRecords:(Person *)person;
-(void) insertRecords:(Person *) person;
-(NSString *) GetDocumentsDirectory;
-(void) CopyDocumentsFiles;

@end

//
//  MultiDAO.m
//  MultiDB
//
//  Created by Kevin Languedoc on 1/25/12.
//  Copyright (c) 2012 kCodebook. All rights reserved.
//

#import "MultiDAO.h"
#import "Person.h"
#import "QueryViewController.h"


@implementation MultiDAO
@synthesize fileMgr = _fileMgr;
@synthesize homeDir = _homeDir;


-(Person *) selectRecords:(Person *)person{
  
    const char *sql = "SELECT main.table1.name, secondDb.table1.fromWhere FROM  main.table1 , secondDb.table1 where main.table1.name = secondDb.table1.name ";
    
    sqlite3_stmt *sqlStatement;
    if(sqlite3_prepare_v2(db, sql, 2, &sqlStatement, NULL) == SQLITE_OK)
    {
        sqlite3_bind_text(sqlStatement, 1, [person.name UTF8String], -1, SQLITE_TRANSIENT);
        
        sqlite3_step(sqlStatement);
        sqlite3_finalize(sqlStatement);
        sqlite3_close(db); 
    }else{
        int err= SQLITE_ERROR;
        
        NSLog(@"%d", err);

    }
   
    person.name = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStatement,0)];
    person.fromWhere = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStatement,1)];
        
       
    return person;
}

-(void)insertRecords:(Person *)person{
    fileMgr = [NSFileManager defaultManager];
    
    sqlite3_stmt *stmt=nil;

    //insert
    const char *sql = "Insert into main.table1(name , gender) ?,?";
    

    sqlite3_prepare_v2(db, sql, 1, &stmt, NULL);
    sqlite3_bind_text(stmt, 1, [person.name UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, [person.gender UTF8String], -1, SQLITE_TRANSIENT);
    

    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    sql = "Insert into secondDb.table1(name , city) ?,?";
    
    
    sqlite3_prepare_v2(db, sql, 1, &stmt, NULL);
    sqlite3_bind_text(stmt, 1, [person.name UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, [person.fromWhere UTF8String], -1, SQLITE_TRANSIENT);
    
    
    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    
    sqlite3_close(db);    

}
                      

-(void)databaseOperations:(NSString *) OperationName :(Person *) person{
    @try {
        NSString * multiDbPath = [self.GetDocumentsDirectory stringByAppendingPathComponent:@"multiDB.sqlite"];
        NSString * otherDbPath = [self.GetDocumentsDirectory stringByAppendingPathComponent:@"otherDB.sqlite"];
        
        BOOL success = [fileMgr fileExistsAtPath:multiDbPath];
        if(!success)
        {
            NSLog(@"Cannot locate database file '%@'.", multiDbPath);
        }
        success = [fileMgr fileExistsAtPath:otherDbPath];
        if(!success)
        {
            NSLog(@"Cannot locate database file '%@'.", otherDbPath);
        }
        if(!(sqlite3_open([multiDbPath UTF8String], &db) == SQLITE_OK))
        {
            NSLog(@"An error has occured.");
            return;
        }
        NSString * attach = [[NSString alloc] initWithString:[[@"attach database '" stringByAppendingPathComponent:otherDbPath] stringByAppendingString:@"' AS secondDb"]];
               
        NSLog(@"'%@'", attach);

        sqlite3_stmt * sqlStatement;
        
        if(sqlite3_prepare_v2(db, [attach UTF8String], -1, &sqlStatement, NULL) == SQLITE_OK)
        {
            sqlite3_step(sqlStatement);
            sqlite3_finalize(sqlStatement);

            if([OperationName isEqualToString:@"Select"]){

                person =[self selectRecords:person];
                
                QueryViewController * qv = [[QueryViewController alloc]init];
                qv.dispplayName.text = person.name;
                qv.displayGender.text = person.gender;
                qv.displayFromWhere.text = person.fromWhere;

                sqlite3_close(db);
            }else if([OperationName isEqualToString:@"Insert"]){
                [self insertRecords:person];
            }
        }else{
          
            NSLog(@"Attach error: %@ ", SQLITE_ERROR);
        }
    }
    @catch (NSException *exception) {
        NSLog(@"An exception occured: %@", [exception reason]);
    }
}

-(NSString *)GetDocumentsDirectory{
    fileMgr = [NSFileManager defaultManager];
    homeDir = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents"];
    
    return homeDir;
}

-(void)CopyDocumentsFiles{
    NSError *err=nil;
    
    fileMgr = [NSFileManager defaultManager];
    
    NSString * multiDbpath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"multiDb.sqlite"]; 
    NSString * copyMultiDb = [self.GetDocumentsDirectory stringByAppendingPathComponent:@"multiDb.sqlite"];
    
    NSString *otherDbPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"otherDb.sqlite"]; 
    NSString *copyOtheDb = [self.GetDocumentsDirectory stringByAppendingPathComponent:@"otherDb.sqlite"];
    
    [fileMgr removeItemAtPath:copyMultiDb error:&err];
    if(![fileMgr copyItemAtPath:multiDbpath toPath:copyMultiDb error:&err])
    {
        NSLog(@"Unable to copy database files into documents folder");
    }
    
    [fileMgr removeItemAtPath:copyOtheDb error:&err];
    if(![fileMgr copyItemAtPath:otherDbPath toPath:copyOtheDb error:&err])
    {
        NSLog(@"Unable to copy database files into documents folder");
    }
    
}

@end

//
//  QueryViewController.h
//  MultiDB
//
//  Created by Kevin Languedoc on 1/26/12.
//  Copyright (c) 2012 kCodebook. All rights reserved.
//

#import <UIKit/UIKit.h>
#import "MultiDAO.h"
#import "Person.h"

@interface QueryViewController : UIViewController{
    MultiDAO * mDao;
   }

@property(retain,nonatomic)MultiDAO * mDao;


@property (weak, nonatomic) IBOutlet UITextField *InputName;
@property (weak, nonatomic) IBOutlet UITextField *InputGender;
@property (weak, nonatomic) IBOutlet UITextField *InputFromWhere;
- (IBAction)saveAction:(id)sender;
- (IBAction)QueryAction:(id)sender;
@property (weak, nonatomic) IBOutlet UITextField *QueryText;
@property (weak, nonatomic) IBOutlet UITextField *dispplayName;
@property (weak, nonatomic) IBOutlet UITextField *displayGender;
@property (weak, nonatomic) IBOutlet UITextField *displayFromWhere;

@end

//
//  QueryViewController.m
//  MultiDB
//
//  Created by Kevin Languedoc on 1/26/12.
//  Copyright (c) 2012 kCodebook. All rights reserved.
//

#import "QueryViewController.h"
#import "MultiDAO.h"
#import "Person.h"

@implementation QueryViewController

@synthesize mDao=_mDao;

@synthesize QueryText=_QueryText;
@synthesize dispplayName=_dispplayName;
@synthesize displayGender=_displayGender;
@synthesize displayFromWhere=_displayFromWhere;
@synthesize InputName;
@synthesize InputGender;
@synthesize InputFromWhere;

- (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil
{
    self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil];
    if (self) {
        // Custom initialization
    }
    return self;
}

- (void)didReceiveMemoryWarning
{
    // Releases the view if it doesn't have a superview.
    [super didReceiveMemoryWarning];
    
    // Release any cached data, images, etc that aren't in use.
}

#pragma mark - View lifecycle

/*
// Implement loadView to create a view hierarchy programmatically, without using a nib.
- (void)loadView
{
}
*/


// Implement viewDidLoad to do additional setup after loading the view, typically from a nib.
- (void)viewDidLoad
{
    [super viewDidLoad];
}


- (void)viewDidUnload
{
    [self setInputName:nil];
    [self setInputGender:nil];
    [self setInputFromWhere:nil];
    [self setQueryText:nil];
    [self setDispplayName:nil];
    [self setDisplayGender:nil];
    [self setDisplayFromWhere:nil];
    [super viewDidUnload];
    // Release any retained subviews of the main view.
    // e.g. self.myOutlet = nil;
}

- (BOOL)shouldAutorotateToInterfaceOrientation:(UIInterfaceOrientation)interfaceOrientation
{
    // Return YES for supported orientations
	return YES;
}

- (IBAction)saveAction:(id)sender {
    mDao = [[MultiDAO alloc] init];
    Person * me = [[Person alloc] init];
    
    NSString * test = [[NSString alloc]initWithString:self.InputName.text];
    [me setName:test];
  
    me.gender = [self InputGender].text;
    me.fromWhere = self.InputFromWhere.text;
    
    [mDao databaseOperations:@"Insert":me];
}

- (IBAction)QueryAction:(id)sender {
    mDao = [[MultiDAO alloc] init];
    Person * human = [[Person alloc] init];
    [human setName:self.QueryText.text];
    
    
    [mDao databaseOperations:@"Select":human];
}
@end