Sqlite Manager

A_SqliteManager not only provides the base CRUD Sqlite operations but also provides data model searching and storing functions . Combining with A_DateModel, it's able to implement the minitype OR/M with data model.

Open connection

    // Open default Sqlite file
    [A_SqliteManager A_Instance];
    // Open the Sqlite file which the name is "sqlite.db"
    [A_SqliteManager A_Instance:@"sqlite.db"];

Execute a query

    [[A_SqliteManager A_Instance]
            A_ExecuteQuery:@"UPDATE `Table` SET `Key`='value' WHERE ID=1"];

Search data set

    NSArray* Dataset = [[A_SqliteManager A_Instance]
            A_SearchDataset:@"SELECT * FROM `Table`"];

    // Using query with params
    NSArray* Dataset = [[A_SqliteManager A_Instance]
            A_SearchDataset:@"SELECT * FROM `Table` WHERE ID=?"
            withParams:@[@1]];
        

Search for single data

    id createDate = [[A_SqliteManager A_Instance]
            A_GetValueFromQuery:@"SELECT CreateDate FROM `Table` WHERE ID=1"];

Data model example

    // ModelTest.h file
    #import <A_IOSHelper/A_IOSHelper.h>
    @interface ModelTest : A_DataModel

    @property (retain, nonatomic) NSString* Name;
    @property (retain, nonatomic) NSDate* Day;
    @property (nonatomic) BOOL Passed;
    @property (nonatomic) NSInteger ID;

    @end

Data model CRUD operation

    ModelTest* _model = [[ModelTest alloc] init];
    [_model setDay:[NSDate date]];
    [_model setName:@"Name"];
    [_model setPassed:YES];

    // Create Table
    Boolean _exist = [[A_SqliteManager A_Instance]
                                A_TableExist:[A_SqliteManager A_GenerateTableName:_model]];
    if (!_exist) {
        [[A_SqliteManager A_Instance] A_CreateTable:_model AndKey:@"id"];
    }

    // Insert new record
    [[A_SqliteManager A_Instance] A_Insert:_model WithIgnore:@[@"id"]];
    // Set giving ID
    [_model setID: [[[A_SqliteManager A_Instance] A_lastInsertId] integerValue]];

    // Search Table
    NSArray* _values = [[A_SqliteManager A_Instance] A_SearchDataset:
    [NSString stringWithFormat:@"SELECT * FROM %@", [A_SqliteManager A_GenerateTableName:_model]]];
    NSLog(@"%@", _values);
    /*
    The result in here is: [NSArray [NSDictionary]]
        {
            Day = "2015-03-16 18:51:10 +0000";
            ID = 1;
            Name = Name;
            Passed = 1;
        }
    */

    // Update data
    [_model setName:@"Name2"];
    [[A_SqliteManager A_Instance] A_Update:_model AndKeys:@[@"id"]];

    // Search Table
    _values = [[A_SqliteManager A_Instance] A_SearchDataset:
    [NSString stringWithFormat:@"SELECT * FROM %@", [A_SqliteManager A_GenerateTableName:_model]]];
    NSLog(@"%@", _values);
    /*
    The result in here is: [NSArray [NSDictionary]]
        {
            Day = "2015-03-16 18:51:10 +0000";
            ID = 1;
            Name = Name2;
            Passed = 1;
        }
    */

    // Delete Data
    [[A_SqliteManager A_Instance] A_Delete:_model AndKeys:@[@"id"]];

    // Search Table
    _values = [[A_SqliteManager A_Instance] A_SearchDataset:
    [NSString stringWithFormat:@"SELECT * FROM %@", [A_SqliteManager A_GenerateTableName:_model]]];
    NSLog(@"%@", _values); // The result is empty 

Search similar models

This function can found the models which the value of attributes are same as example model, and it will ignore the default value of attributes.
The result in this function is NSArray with the data model objects.

    // Get all ModelTest objects.
    ModelTest* _searchExample = [[ModelTest alloc] init];
    NSArray* _result = [[A_SqliteManager A_Instance] A_SearchSimilarModels:_searchExample];

    // Get all ModelTest objects that Name is @"Animax"
    ModelTest* _searchExample = [[ModelTest alloc] init];
    [_searchExample setName:@"Animax"];
    NSArray* _result = [[A_SqliteManager A_Instance] A_SearchSimilarModels:_searchExample];

Search models with query

The result in this function is NSArray with the data model objects.

    // Get all ModelTest objects that Name like "%name%"
    NSArray* _result = [[A_SqliteManager A_Instance] A_SearchModels:[ModelTest class]
            Where:@"Name like '%name%'"];