Examples of SQLite applications

Introduction to SQLite:
SQLite is an open source library written in C, which implements an open source self-contained SQL relational database engine that can store large amounts of associated data using SQLite. can be embedded on iphone or ipad devices.

Advantages:
1, SQLite is a fully self-contained (high independence) SQL database engine,
1) All database data are stored in a separate, cross-platform disk;
2) It requires several external libraries and a little bit of operating system support.
2, SQLite occupies the space is very know. Probably just 300k.
3, SQLite does not need to configure, install and administrator process.
4, SQLite support a lot of SQL features, but there are some features are not supported, you can view the official documents;

Difference:
The core Data:core Data Application Programming Interface (API) is designed to store data on iOS. Good at managing the creation of data on devices.
(1) is a framework, not a relational database, the main purpose is to provide developers with a framework to save the object created by the application;
(2) You can use a rich set of APIs to manipulate these objects in code, which is the creation of models in MVC;
(3) You can use SQLite and other storage types as background storage for data (but note that it is not an implementation of a relational database).
(4) It is not directly accessible to the developer for storage. If you only need to persist objects created during use during application use, you should consider using core Data.

SQLite: When you need to preload an application with a large amount of data, SQLite behaves more prominently. Eg: the GPS function on the map, which is shown to be a more powerful pre-load feature. If you need to use the functionality of relational database storage, you should consider using SQLite directly,

For a reference to some of the detailed content in Sqlite3:
1. Sqlite3 Catalog.db starts the Sqlite3 tool and creates a catalog.db database
2. Attach database command, attaching an existing database to the SQLite tool, or the specified file does not exist.
3, you can attach more than one database a command-line tool instance, and the database name. Table name format use dot notation to reference data in each database
4. You can migrate one data from one database to another database.

There are many meta-commands (Metacommmands) in SQLite to control the tool itself.
Eg:sqlite in the "." Symbol Execution Meta command is not required ";" End of the symbol. You can perform the. Help to view the command after entering the SQLite environment.
Some other statements do not have a "." When the symbol starts, it needs to be ";" The symbol ends.

Use the Sqlite3 database in Xcode:
0, add the required framework Libsqlite3.0.dylib, this dynamic library integrates the operation that wants all sqlite, display interface.
1, add the database, eg: add catalog.db to the Suport directory of the Xcode project.
2, create the model, eg: corresponding to the table in the database, and then is to create and database workers of the same name as the field of the property, so it is not prone to errors. Conversion is not so troublesome. (Attributes < —————— > fields) correspond between them one by one.
3, create a connection to the database of the underlying class, and create the data, modify the data, and so on, here need to call the database SQLite interface method.

Disclaimer: The following code refers to someone else's:

Code:

Run the result diagram:

The following is the main code in the program:

Code for Model product:

Product.h's file loves you
#import <Foundation/Foundation.h>

/ / This is the model class, corresponding to the attribute fields in this database.
@interface Product : NSObject {
     Int ID;
     NSString* name;
     NSString* manufacturer;
     NSString* details;
     Float price;
     Int quantity;
     NSString* countryOfOrigin;
     NSString* image;
}

@property (nonatomic) int ID;
@property (strong, nonatomic) NSString *name;
@property (strong, nonatomic) NSString *manufacturer;
@property (strong, nonatomic) NSString *details;
@property (nonatomic) float price;
@property (nonatomic) int quantity;
@property (strong, nonatomic) NSString *countryOfOrigin;
@property (strong, nonatomic) NSString *image;

@end

The product.m file, especially implemented in order to correspond to the attribute name in the field.
#import "Product.h"

@implementation Product

@synthesize ID;
@synthesize name;
@synthesize manufacturer;
@synthesize details;
@synthesize price;
@synthesize quantity;
@synthesize countryOfOrigin;
@synthesize image;

@end

The following is the code for the Dbaccess class:

DBAccess.h file.
#import <Foundation/Foundation.h>

// This includes the header for the SQLite library.
#import <sqlite3.h>
#import "Product.h"

/ / used to get the data in the database, added to the library in the model

@interface DBAccess : NSObject {
    
    
}

/ / Get the array method
- (NSMutableArray*) getAllProducts;
/ / Close the database
- (void) closeDatabase;

/ / Initialize the database, including opening the database
- (void)initializeDatabase;

@end

DBAccess.m file.
#import "DBAccess.h"

@implementation DBAccess

// Reference to the SQLite database.
Sqlite3* database;

/ / Initialize the method, integrate the NSObject class, this method will be called when starting.
-(id) init
{
    If ((self = [super init]))
    {
        [self initializeDatabase];
    }
    Return self;
}

/ / Get the database file, open, connect
- (void)initializeDatabase {
    
    // Get the database from the application bundle.
    NSString *path = [[NSBundle mainBundle]
                      pathForResource:@"catalog"
                      ofType:@"db"];
    
    // Open the database.
    If (sqlite3_open([path UTF8String], &database) == SQLITE_OK)
    {
        NSLog(@"Opening Database");
    }
    Else
    {
        // Call close to properly clean up
        Sqlite3_close(database);
        NSAssert1(0, @"Failed to open database: ‘%s’.",
                  Sqlite3_errmsg(database));
    }
}

/ / Close the database
-(void) closeDatabase
{
    // Close the database.
    If (sqlite3_close(database) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to close database: ‘%s’.",
                  Sqlite3_errmsg(database));
    }
}

/ / Get all the records of the product in the database is stored in the array
- (NSMutableArray*) getAllProducts
{
    NSLog (@"Get Data");
    // The array of products that we will create
    NSMutableArray *products = [[NSMutableArray alloc] init];

    // The SQL statement that we plan on leading against the database
    Const char *sql = "SELECT product.ID,product.Name, Manufacturer.name,product.details,product.price, product.quantityonhand, country.country, product.image FROM Product,Manufacturer, Country where manufacturer.manufacturerid=product .manufacturerid and product.countryoforiginid=country.countryid";

    // The SQLite statement object that will hold our result set
    Sqlite3_stmt *statement;
    
    // Prepare the statement to compile the SQL query into byte-code
    Int sqlResult = sqlite3_prepare_v2(database, sql, -1, &statement, NULL);

    If ( sqlResult== SQLITE_OK) {
        // Step through the results - once for each row.
        //#define SQLITE_ROW sqlite3_step() has another row ready
        While (sqlite3_step(statement) == SQLITE_ROW) {
            // allocate a Product object to add to products array
            Product *product = [[Product alloc] init];
            // The second parameter is the column index (0 based) in
            // the result set.
            Char *name = (char *) sqlite3_column_text(statement, 1);
            Char *manufacturer = (char *) sqlite3_column_text(statement, 2);
            Char *details = (char *) sqlite3_column_text(statement, 3);
            Char *countryOfOrigin = (char *) sqlite3_column_text(statement, 6);
            Char *image = (char *) sqlite3_column_text(statement, 7);
            
            // Set all the attributes of the product
            product.ID = sqlite3_column_int(statement, 0);
            Product.name = (name) ? [NSString stringWithUTF8String:name] : @"";
            Product.manufacturer = (manufacturer) ? [NSString
                                                     stringWithUTF8String:manufacturer] : @"";
            Product.details = (details) ? [NSString stringWithUTF8String:details] : @"";
            Product.price = sqlite3_column_double(statement, 4);
            Product.quantity = sqlite3_column_int(statement, 5);
            product.countryOfOrigin = (countryOfOrigin) ? [NSString
                                                           stringWithUTF8String:countryOfOrigin] : @"";
            Product.image = (image) ? [NSString stringWithUTF8String:image] : @"";
            
            // Add the product to the Products array and move to the next line
            [products addObject:product];
        }
        / / Release the resources related to the compiler statement
        Sqlite3_finalize(statement);
    }
    Else {
        NSLog(@"Problem with the database:");
        NSLog(@"%d",sqlResult);
    }
    Return products;
}
@end

The above two classes are as long as the model that creates the database in the application, and the connection database gets the corresponding operation in the database.

The code that is used in the controller to display in the view:

- (void)viewDidLoad
{
     [super viewDidLoad];
    
     DBAccess *dbAccess = [[DBAccess alloc] init];
    
     Self.products = [dbAccess getAllProducts]; //The product should be defined in front of it.
    
     [dbAccess closeDatabase];
    
} 

Source Code Download Connection:

http://www.wrox.com/WileyCDA/WroxTitle/ Professional-ios-database-application-programming-2nd-edition.productcd-1118391845,desccd-download.html

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Examples of SQLite applications