Paintinglite

Welcome follow, Star and Fork

Safety disclosure

If you have found a Paintinglite security vulnerability and one that needs to be fixed, you should email it to [email protected] as soon as possible. Thank you for your support.

The installation

Download Paintinglite directly from GitHub and drag it to your project to experience Paintinglite. (Libsqlite3. TBD or libsqlite3.0.tbd supported by Sqlite3 needs to be added to the project)

Version of the iteration

Paintinglite version updated
Version 1.1 Updated the summary Compared to V1.0, optimized open database operation and added important information such as view database file existence, size and so on
Version 1.2 Updated the profile The stress test strategy has been reworked to significantly reduce the frame size (<10MB) and add a level 1 cache and log write strategy

Introduction to the

Paintinglite is an excellent and fast Sqlite3 database framework. Paintinglite has good encapsulation of data, fast insertion of data, and good resource utilization for large amounts of data. Paintinglite supports object mapping, which is a very lightweight object encapsulation for SQlite3. It establishes a mapping relationship between POJOs and database tables. Paintinglite can not only automatically generate SQL statements, but also manually write SQL statements, achieving a lightweight framework for convenient development and efficient query.

Paintinglite menu.
Basic library operations
Table basic operations
Encapsulated query operation
PQL feature language query operations
Advanced database configuration operations
Intelligent query operation (multi-encapsulated query)
Aggregate query operation
Cascade operation
The transaction operations
Secure encryption operation
Split large table operations
Logging operations
Snapshot Saving operations
Backup database operations (support for MySQL, essentially, Sqlite3, Oracle)
Stress test operations (report generation support)

Core object

  • PaintingliteSessionManager: basic operations managers (library table | operation)
  • PaintingliteExec: Performs an operation
  • PaintingliteBackUpManager: database backup managers
  • PaintingliteSplitTable: Split operation
  • PaintinglitePressureOS: stress test

Database operations (PaintingliteSessionManager)

1. To build library

Create PaintingliteSessionManager by managers create the database.
- (Boolean)openSqlite:(NSString *)fileName;

- (Boolean)openSqlite:(NSString *)fileName completeHandler:(void(^ __nullable)(NSString *filePath,PaintingliteSessionError *error,Boolean success))completeHandler;
Copy the code

Paintinglite has a nice processing mechanism for creating a database by passing in the database name, even if the database suffix is not canonical, it can still create a database with the.db suffix.

[self.sessionM openSqlite:@"sqlite"]; [self.sessionM openSqlite:@"sqlite02.db"]; [self.sessionM openSqlite:@"sqlite03.image"]; [self.sessionM openSqlite:@"sqlite04.text"]; [self.sessionM openSqlite:@"sqlite05.."] ;Copy the code

Gets the absolute path to create the database.

[self.sessionM openSqlite:@"sqlite" completeHandler:^(NSString * _Nonnull filePath, PaintingliteSessionError * _Nonnull error, Boolean success) {
       if (success) {
           NSLog(@"%@",filePath);
        }
 }];
Copy the code

2. Close the library

- (Boolean)releaseSqlite;

- (Boolean)releaseSqliteCompleteHandler:(void(^)(PaintingliteSessionError *error,Boolean success))completeHandler;
Copy the code

3. Create a table

- (Boolean)execTableOptForSQL:(NSString *)sql;
- (Boolean)execTableOptForSQL:(NSString *)sql completeHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success))completeHandler;
- (Boolean)createTableForName:(NSString *)tableName content:(NSString *)content;
- (Boolean)createTableForName:(NSString *)tableName content:(NSString *)content completeHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success))completeHandler;
- (Boolean)createTableForObj:(id)obj createStyle:(PaintingliteDataBaseOptionsCreateStyle)createStyle;
- (Boolean)createTableForObj:(id)obj createStyle:(PaintingliteDataBaseOptionsCreateStyle)createStyle completeHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success))completeHandler;
Copy the code

There are three ways to create a table:

  1. SQL to create
[self.sessionM execTableOptForSQL:@"CREATE TABLE IF NOT EXISTS cart(UUID VARCHAR(20) NOT NULL PRIMARY KEY,shoppingName TEXT,shoppingID INT(11))" completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success) {
        if (success) {
            NSLog(@"===CREATE TABLE SUCCESS===");
        }
}];
Copy the code
  1. The name of the table to create
[self.sessionM createTableForName:@"student" content:@"name TEXT,age INTEGER"];
Copy the code
  1. Object creation
User *user = [[User alloc] init];
[self.sessionM createTableForObj:user createStyle:PaintingliteDataBaseOptionsUUID];
Copy the code

Object creation can automatically generate primary keys:

A primary key type
UUID string
ID The numerical

4. Update the table

- (Boolean)execTableOptForSQL:(NSString *)sql;
- (Boolean)execTableOptForSQL:(NSString *)sql completeHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success))completeHandler;
- (BOOL)alterTableForName:(NSString *__nonnull)oldName newName:(NSString *__nonnull)newName;
- (BOOL)alterTableForName:(NSString *__nonnull)oldName newName:(NSString *__nonnull)newName completeHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success))completeHandler;
- (BOOL)alterTableAddColumnWithTableName:(NSString *)tableName columnName:(NSString *__nonnull)columnName columnType:(NSString *__nonnull)columnType;
- (BOOL)alterTableAddColumnWithTableName:(NSString *)tableName columnName:(NSString *__nonnull)columnName columnType:(NSString *__nonnull)columnType completeHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success))completeHandler;
- (BOOL)alterTableForObj:(id)obj;
- (BOOL)alterTableForObj:(id)obj completeHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success))completeHandler;
Copy the code

There are three ways to update a table:

  1. SQL update

  2. Update the table name [table name | table field]

[self.sessionM alterTableForName:@"cart" newName:@"carts"];
[self.sessionM alterTableAddColumnWithTableName:@"carts" columnName:@"newColumn" columnType:@"TEXT"];
Copy the code
  1. Object update Updates the User table operation
#import <Foundation/Foundation.h>

NS_ASSUME_NONNULL_BEGIN

@interface User : NSObject

@property (nonatomic,strong)NSString *name;
@property (nonatomic,strong)NSNumber *age;
@property (nonatomic,strong)NSMutableArray<id> *mutableArray;

@end

NS_ASSUME_NONNULL_END
Copy the code

Table fields are automatically updated by object based on the mapping between the table and object.

User *user = [[User alloc] init];
[self.sessionM alterTableForObj:user];
Copy the code

5. Delete the vm

- (Boolean)execTableOptForSQL:(NSString *)sql;
- (Boolean)execTableOptForSQL:(NSString *)sql completeHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success))completeHandler;
- (Boolean)dropTableForTableName:(NSString *)tableName;
- (Boolean)dropTableForTableName:(NSString *)tableName completeHandler:(void(^)(PaintingliteSessionError *error,Boolean success))completeHandler;
- (Boolean)dropTableForObj:(id)obj;
- (Boolean)dropTableForObj:(id)obj completeHandler:(void(^)(PaintingliteSessionError *error,Boolean success))completeHandler;
Copy the code

There are three ways to drop a table:

  1. SQL operations
  2. The name of the table to delete
[self.sessionM execTableOptForSQL:@"DROP TABLE carts" completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success) {
        if (success) {
            NSLog(@"===DROP TABLE SUCCESS===");
        }
}];
Copy the code
  1. Object to delete
User *user = [[User alloc] init];
[self.sessionM dropTableForObj:user];
Copy the code

Table operation

1. Query

Queries can provide the ability for query results to be wrapped in arrays or directly wrapped in objects.

  1. Common query
  • The general query
- (NSMutableArray *)execQuerySQL:(NSString *__nonnull)sql;
- (Boolean)execQuerySQL:(NSString *__nonnull)sql completeHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success,NSMutableArray<NSDictionary *> *resArray))completeHandler;
Copy the code
[self.sessionM execQuerySQL:@"SELECT * FROM student" completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success, NSMutableArray<NSDictionary *> * _Nonnull resArray) {
        if (success) {
            for (NSDictionary *dict in resArray) {
                NSLog(@"%@",dict);
            }
        }
}];
Copy the code

2020-06-27 15:35:45.967569+0800 Paintinglite[5805:295051] {age = 21; name = CreaterOS; } 2020-06-27 15:35:45.967760+0800 Paintinglite[5805:295051] {age = 19; name = Painting; } 2020-06-27 15:35:45.967879+0800 Paintinglite[5805:295051] {age = 21; name = CreaterOS; }

  • Encapsulating query

    Encapsulating queries encapsulates query results into objects that correspond to table fields.

- (id)execQuerySQL:(NSString *__nonnull)sql obj:(id)obj;
- (Boolean)execQuerySQL:(NSString *__nonnull)sql obj:(id)obj completeHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success,NSMutableArray *resArray,NSMutableArray<id> *resObjList))completeHandler;
Copy the code
Student *stu = [[Student alloc] init];
[self.sessionM execQuerySQL:@"SELECT * FROM student" obj:stu completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success, NSMutableArray<NSDictionary *> * _Nonnull resArray, NSMutableArray<id> * _Nonnull resObjList) {
  if (success) {
    for (Student *stu in resObjList) {
      NSLog(@"stu.name = %@ and stu.age = %@",stu.name,stu.age);
    }
  }
}];
Copy the code

2020-06-27 15:39:27.306786+0800 Paintinglite[5892:302879] stu.name = CreaterOS and stu.age = 21 2020-06-27 15:39:27.306961+0800 Paintinglite[5892:302879] stu.name = Painting and stu.age = 19 2020-06-27 15:39:27.307110+0800 Paintinglite[5892:302879] stu.name = CreaterOS and stu.age = 21

  1. Conditions of the query

Conditional query syntax rules:

  • The subscripts start at 0
  • Conditional parameter use? As a placeholder
SELECT * FROM user WHERE name = ? and age = ?
Copy the code
- (NSMutableArray<NSDictionary *> *)execPrepareStatementSql;
- (Boolean)execPrepareStatementSqlCompleteHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success,NSMutableArray *resArray))completeHandler;
Copy the code
[self.sessionM execQuerySQLPrepareStatementSql:@"SELECT * FROM student WHERE name = ?"] ; [self.sessionM setPrepareStatementPQLParameter:0 paramter:@"CreaterOS"]; NSLog(@"%@",[self.sessionM execPrepareStatementSql]);Copy the code

2020-06-27 15:44:06.664951+0800 Paintinglite[5984:310580] ({age = 21; name = CreaterOS; }, { age = 21; name = CreaterOS; })

  1. Fuzzy query
- (NSMutableArray<NSDictionary *> *)execLikeQuerySQLWithTableName:(NSString *__nonnull)tableName field:(NSString *__nonnull)field like:(NSString *__nonnull)like;
- (Boolean)execLikeQuerySQLWithTableName:(NSString *__nonnull)tableName field:(NSString *__nonnull)field like:(NSString *__nonnull)like completeHandler:(void(^)(PaintingliteSessionError *error,Boolean success,NSMutableArray *resArray))completeHandler;

- (id)execLikeQuerySQLWithField:(NSString *__nonnull)field like:(NSString *__nonnull)like obj:(id)obj;
- (Boolean)execLikeQuerySQLWithField:(NSString *__nonnull)field like:(NSString *__nonnull)like obj:(id)obj completeHandler:(void(^)(PaintingliteSessionError *error,Boolean success,NSMutableArray *resArray,NSMutableArray<id>* resObjList))completeHandler;
Copy the code
[self.sessionM execLikeQuerySQLWithTableName:@"student" field:@"name" like:@"%t%" completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success, NSMutableArray<NSDictionary *> * _Nonnull resArray) { if (success) { for (NSDictionary *dict in resArray) { NSLog(@"%@",dict); } } }]; Student *stu = [[Student alloc] init]; [self.sessionM execLikeQuerySQLWithField:@"name" like:@"%t%" obj:stu completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success, NSMutableArray<NSDictionary *> * _Nonnull resArray, NSMutableArray<id> * _Nonnull resObjList) { if (success) { for (NSDictionary *dict in resArray) { NSLog(@"%@",dict); }}}];Copy the code

310495+0800 Paintinglite[6030:314851] {age = 21; name = CreaterOS; } 2020-06-27 15:46:31.310701+0800 Paintinglite[6030:314851] {age = 19; name = Painting; } Paintinglite[6030:314851] {age = 21; name = CreaterOS; }

  1. Paging query
- (NSMutableArray<NSDictionary *> *)execLimitQuerySQLWithTableName:(NSString *__nonnull)tableName limitStart:(NSUInteger)start limitEnd:(NSUInteger)end;
- (Boolean)execLimitQuerySQLWithTableName:(NSString *__nonnull)tableName limitStart:(NSUInteger)start limitEnd:(NSUInteger)end completeHandler:(void(^)(PaintingliteSessionError *error,Boolean success,NSMutableArray *resArray))completeHandler;

- (id)execLimitQuerySQLWithLimitStart:(NSUInteger)start limitEnd:(NSUInteger)end obj:(id)obj;
- (Boolean)execLimitQuerySQLWithLimitStart:(NSUInteger)start limitEnd:(NSUInteger)end obj:(id)obj completeHandler:(void(^)(PaintingliteSessionError *error,Boolean success,NSMutableArray *resArray,NSMutableArray<id>* resObjList))completeHandler;
Copy the code
[self.sessionM execLimitQuerySQLWithTableName:@"student" limitStart:0 limitEnd:1 completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success, NSMutableArray<NSDictionary *> * _Nonnull resArray) {
        if (success) {
            for (NSDictionary *dict in resArray) {
                NSLog(@"%@",dict);
            }
        }
}];

Student *stu = [[Student alloc] init];
[self.sessionM execLimitQuerySQLWithLimitStart:0 limitEnd:1 obj:stu completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success, NSMutableArray<NSDictionary *> * _Nonnull resArray, NSMutableArray<id> * _Nonnull resObjList) {
  if (success) {
    for (Student *stu in resObjList) {
      NSLog(@"stu.name = %@ and stu.age = %@",stu.name,stu.age);
    }
  }
}];
Copy the code

2020-06-27 15:51:13.026776+0800 Paintinglite[6117:323796] Name = CreaterOS and age = 21

  1. Sorting query
- (NSMutableArray<NSDictionary *> *)execOrderByQuerySQLWithTableName:(NSString *__nonnull)tableName orderbyContext:(NSString *__nonnull)orderbyContext orderStyle:(PaintingliteOrderByStyle)orderStyle;
- (Boolean)execOrderByQuerySQLWithTableName:(NSString *__nonnull)tableName orderbyContext:(NSString *__nonnull)orderbyContext orderStyle:(PaintingliteOrderByStyle)orderStyle completeHandler:(void(^)(PaintingliteSessionError *error,Boolean success,NSMutableArray *resArray))completeHandler;

- (id)execOrderByQuerySQLWithOrderbyContext:(NSString *__nonnull)orderbyContext orderStyle:(PaintingliteOrderByStyle)orderStyle obj:(id)obj;
- (Boolean)execOrderByQuerySQLWithOrderbyContext:(NSString *__nonnull)orderbyContext orderStyle:(PaintingliteOrderByStyle)orderStyle obj:(id)obj completeHandler:(void(^)(PaintingliteSessionError *error,Boolean success,NSMutableArray *resArray,NSMutableArray<id>* resObjList))completeHandler;
Copy the code
Student *student = [[Student alloc] init];
[self.sessionM execOrderByQuerySQLWithOrderbyContext:@"name" orderStyle:PaintingliteOrderByDESC obj:student completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success, NSMutableArray<NSDictionary *> * _Nonnull resArray, NSMutableArray<id> * _Nonnull resObjList) {
  if (success) {
    for (Student *stu in resObjList) {
      NSLog(@"stu.name = %@ and stu.age = %@",stu.name,stu.age);
    }
  }
}];
Copy the code

2020-06-27 15:55:06.714604+0800 Paintinglite[6196:331097] stu. Name = Painting and stu. Age = 19 2020-06-27 15:55:06.714801+0800 Paintinglite[6196:331097] Stu.name = CreaterOS and stu.age = 21 2020-06-27 15:55:06.714962+0800 Paintinglite[6196:331097] stu.name = CreaterOS and stu.age = 21

2. Add data

- (Boolean)insert:(NSString *__nonnull)sql;
- (Boolean)insert:(NSString *__nonnull)sql completeHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success))completeHandler;
- (Boolean)insertWithObj:(id)obj completeHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success))completeHandler;
Copy the code
  1. SQL insert
[self.sessionM insert:@"INSERT INTO student(name,age) VALUES('CreaterOS',21),('Painting',19)"];
Copy the code
  1. Object to insert
#import <Foundation/Foundation.h>

NS_ASSUME_NONNULL_BEGIN

@interface Student : NSObject
@property (nonatomic,strong)NSString *name;
@property (nonatomic,strong)NSNumber *age;
@end

NS_ASSUME_NONNULL_END
Copy the code
Student *stu = [[Student alloc] init];
stu.name = @"ReynBryant";
stu.age = [NSNumber numberWithInteger:21];
[self.sessionM insertWithObj:stu completeHandler:nil];
Copy the code

Paintinglit was still very efficient at reading 16 million pieces of data at a time, taking only 6ms to 7ms.

3. Update data

- (Boolean)update:(NSString *__nonnull)sql;
- (Boolean)update:(NSString *__nonnull)sql completeHandler:(void(^)(PaintingliteSessionError *error,Boolean success))completeHandler;
- (Boolean)updateWithObj:(id)obj condition:(NSString *__nonnull)condition completeHandler:(void(^)(PaintingliteSessionError *error,Boolean success))completeHandler;
Copy the code
  1. SQL update data
[self.sessionM update:@"UPDATE student SET name = 'Painting' WHERE name = 'ReynBryant'"];
Copy the code

  1. Object to update

    Student *stu = [[Student alloc] init];
stu.name = @"CreaterOS";
[self.sessionM updateWithObj:stu condition:@"age = 21" completeHandler:nil];
Copy the code

To add an update operation, you can use the object transfer mode to update for example: User * User = [[User alloc] init]; user.name = @”CreaterOS”; user.age = 21;

4. Delete data

- (Boolean)del:(NSString *__nonnull)sql;
- (Boolean)del:(NSString *__nonnull)sql completeHandler:(void(^)(PaintingliteSessionError *error,Boolean success))completeHandler;
Copy the code

PQL grammar (PaintingliteSessionManager)

With PQL statements,Paintinglite automatically writes SQL statements for you.

PQL grammar rules (uppercase | class name must be associated with table) FROM + class name + [condition]

- (id)execPrepareStatementPQL;
- (Boolean)execPrepareStatementPQLWithCompleteHandler:(void(^)(PaintingliteSessionError *error,Boolean success,NSMutableArray *resArray,NSMutableArray<id>* resObjList))completeHandler;

- (void)execQueryPQLPrepareStatementPQL:(NSString *__nonnull)prepareStatementPQL;
- (void)setPrepareStatementPQLParameter:(NSUInteger)index paramter:(NSString *__nonnull)paramter;
- (void)setPrepareStatementPQLParameter:(NSArray *__nonnull)paramter;

- (id)execPQL:(NSString *__nonnull)pql;
- (Boolean)execPQL:(NSString *__nonnull)pql completeHandler:(void(^)(PaintingliteSessionError *error,Boolean success,NSMutableArray *resArray,NSMutableArray<id>* resObjList))completeHandler;
Copy the code
[self.sessionM execPQL:@"FROM Student WHERE name = 'CreaterOS'" completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success, NSMutableArray * _Nonnull resArray, NSMutableArray<id> * _Nonnull resObjList) {
        if (success) {
            for (Student *stu in resObjList) {
                NSLog(@"stu.name = %@ and stu.age = %@",stu.name,stu.age);
            }
        }
}];
Copy the code

2020-06-27 16:16:47.145774+0800 Paintinglite[6533:369828] stu.name = CreaterOS and stu.age = 21 2020-06-27 16:16:47.145928+0800 Paintinglite[6753:369828] Stu. name = CreaterOS and stu.age = 21

[self.sessionM execPQL:@"FROM Student LIMIT 0,1" completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success, NSMutableArray * _Nonnull resArray, NSMutableArray<id> * _Nonnull resObjList) {
        if (success) {
            for (Student *stu in resObjList) {
                NSLog(@"stu.name = %@ and stu.age = %@",stu.name,stu.age);
            }
        }
}];
Copy the code
[self.sessionM execPQL:@"FROM Student WHERE name LIKE '%t%'" completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success, NSMutableArray * _Nonnull resArray, NSMutableArray<id> * _Nonnull resObjList) {
        if (success) {
            for (Student *stu in resObjList) {
                NSLog(@"stu.name = %@ and stu.age = %@",stu.name,stu.age);
            }
        }
}];
Copy the code
[self.sessionM execPQL:@"FROM Student ORDER BY name ASC" completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success, NSMutableArray * _Nonnull resArray, NSMutableArray<id> * _Nonnull resObjList) {
        if (success) {
            for (Student *stu in resObjList) {
                NSLog(@"stu.name = %@ and stu.age = %@",stu.name,stu.age);
            }
        }
}];

Copy the code
[self.sessionM execQueryPQLPrepareStatementPQL:@"FROM Student WHERE name = ?"] ; [self.sessionM setPrepareStatementPQLParameter:@[@"CreaterOS"]]; NSLog(@"%@",[self.sessionM execPrepareStatementPQL]);Copy the code

2020-06-27 16:26:11.404815+0800 Paintinglite[7025:389268] (“<Student: 0x600000565420>”, “<Student: 0x60000057e0 >”)

Aggregation function (PaintingliteAggregateFunc)

Paintinglite encapsulates the Sqlite3 aggregate function and automatically writes the SQL statement to get the aggregate result.

  1. Count
[self.aggreageteF count:[self.sessionM getSqlite3] tableName:@"eletest" completeHandler:^(PaintingliteSessionError * _Nonnull sessionerror, Boolean success, NSUInteger count) {
        if (success) {
            NSLog(@"%zd",count);
        }
 }];
Copy the code
  1. Max
[self.aggreageteF max:[self.sessionM getSqlite3] field:@"age" tableName:@"eletest" completeHandler:^(PaintingliteSessionError * _Nonnull sessionerror, Boolean success, double max) {
        if (success) {
            NSLog(@"%.2f",max);
        }
}];
Copy the code
  1. Min
[self.aggreageteF min:[self.sessionM getSqlite3] field:@"age" tableName:@"eletest" completeHandler:^(PaintingliteSessionError * _Nonnull sessionerror, Boolean success, double min) {
        if (success) {
            NSLog(@"%.2f",min);
        }
}];
Copy the code
  1. Sum
[self.aggreageteF sum:[self.sessionM getSqlite3] field:@"age" tableName:@"eletest" completeHandler:^(PaintingliteSessionError * _Nonnull sessionerror, Boolean success, double sum) {
        if (success) {
            NSLog(@"%.2f",sum);
        }
}];
Copy the code
  1. Avg
[self.aggreageteF avg:[self.sessionM getSqlite3] field:@"age" tableName:@"eletest" completeHandler:^(PaintingliteSessionError * _Nonnull sessionerror, Boolean success, double avg) {
        if (success) {
            NSLog(@"%.2f",avg);
        }
}];

Copy the code

Transaction (PaintingliteTransaction)

Sqlite3 development defaults to a single insert statement as a transaction. Assuming more than one insert statement will start the transaction repeatedly, which is a huge resource consumption. Paintinglite provides the operation to start the transaction (display the transaction).

+ (void)begainPaintingliteTransaction:(sqlite3 *)ppDb;
+ (void)commit:(sqlite3 *)ppDb;
+ (void)rollback:(sqlite3 *)ppDb;
Copy the code

Daily development integration

@try { } @catch (NSException *exception) { } @finally { }

use

Cascade operation (PaintingliteCascadeShowerIUD)

- (Boolean)cascadeInsert:(sqlite3 *)ppDb obj:(id)obj completeHandler:(void (^ __nullable)(PaintingliteSessionError *sessionError,Boolean success,NSMutableArray *resArray))completeHandler;

- (Boolean)cascadeUpdate:(sqlite3 *)ppDb obj:(id)obj condatation:(NSArray<NSString *> * __nonnull)condatation completeHandler:(void (^__nullable)(PaintingliteSessionError *sessionError,Boolean success,NSMutableArray *resArray))completeHandler;

- (Boolean)cascadeDelete:(sqlite3 *)ppDb obj:(id)obj condatation:(NSArray<NSString *> * __nonnull)condatation completeHandler:(void (^__nullable)(PaintingliteSessionError *sessionError,Boolean success,NSMutableArray *resArray))completeHandler;
Copy the code

The cascade is divided into three parts:

  1. insert

In a cascade insert operation, we need to join two related tables through a mutable array. For example, the user table is related to the student table. A user can contain multiple students

So, you can set variable data in user to hold multiple students, and then hand the User object to Paintinglite to write data to multiple tables at once.

User *user = [[User alloc] init];
user.name = @"Jay";
user.age = [NSNumber numberWithInteger:40];
user.studentsArray = [NSMutableArray array];

Student *student = [[Student alloc] init];
student.name = @"Hony";
student.age = [NSNumber numberWithInteger:30];

Student *student1 = [[Student alloc] init];
student1.name = @"Jack";
student1.age = [NSNumber numberWithInteger:41];

[user.studentsArray addObject:student];
[user.studentsArray addObject:student1];

[self.cascade cascadeInsert:[self.sessionM getSqlite3] obj:user completeHandler:^(PaintingliteSessionError * _Nonnull sessionError, Boolean success, NSMutableArray * _Nonnull resArray) {
    if (success) {
        NSLog(@"%@",resArray);
    }
}];
Copy the code
  1. The update function is the same as cascading inserts. You pass in the User object, a collection of student tables, and pass in the modification criteria as an array. Paintinglite can automatically update multiple tables. (Different conditions for multiple tables corresponding to conditional arrays)

Name = ‘CreaterOS’ corresponds to the user table

name = ‘OS… ‘corresponds to student table

[self.cascade cascadeUpdate:[self.sessionM getSqlite3] obj:user condatation:@[@"WHERE name = 'CreaterOS'",@"WHERE name =  'OS...'"] completeHandler:^(PaintingliteSessionError * _Nonnull sessionError, Boolean success, NSMutableArray * _Nonnull resArray) { if (success) { NSLog(@"%@",resArray); } }];Copy the code
  1. delete
[self.cascade cascadeDelete:[self.sessionM getSqlite3] obj:user condatation:@[@"name = 'WHY'",@"name = 'YHD...'"] completeHandler:^(PaintingliteSessionError * _Nonnull sessionError, Boolean success, NSMutableArray * _Nonnull resArray) {
       if (success) {
           NSLog(@"%@",resArray);
       }
}];

Copy the code

Log Mode (PaintingliteLog)

Paintinglite provides logging for developers, which records key operations on SQlite3 data during development and is time-stamped. Developers can easily read the logs by database name, or optionally read the logs based on the desired time node or the status of success or failure. Convenient software debugging after online.

- (void)readLogFile:(NSString *__nonnull)fileName;

- (void)readLogFile:(NSString *)fileName dateTime:(NSDate *__nonnull)dateTime;

- (void)readLogFile:(NSString *)fileName logStatus:(PaintingliteLogStatus)logStatus;

- (void)removeLogFile:(NSString *)fileName;

Copy the code

The database backup (PaintingliteBackUpManager)

Database migration is a frequent concern of developers, and it has always been a headache for THE migration of SQLITe3 clients, SQL Server, MySQL and Orcale. Paintinglite is very user-friendly and provides developers with four types of database backup files, from database creation to data insertion. Paintinglite writes backup files for developers. Developers only need to upload these SQL files and run them to get exactly the same data as mobile devices.

PaintingliteBackUpSqlite3,
PaintingliteBackUpMySql,
PaintingliteBackUpSqlServer,
PaintingliteBackUpORCALE
Copy the code
- (Boolean)backupDataBaseWithName:(sqlite3 *)ppDb sqliteName:(NSString *)sqliteName type:(PaintingliteBackUpManagerDBType)type completeHandler:(void(^ __nullable)(NSString *saveFilePath))completeHandler;
Copy the code

! [image-20200627211330562](/Users/bryantreyn/Library/Application Support/typora-user-images/image-20200627211330562.png)

Down the table (PaintingliteSplitTable)

The time-consuming operation of querying a table with too much data is huge. In the Paintinglite testing phase, the operation of splitting a large table into multiple smaller tables is provided. The amount of splitting is determined by the developer. For the first time in Paintinglite, the split table operation is available. The module is still being tested. Later iterations will focus on optimizing resource consumption and CPU usage in this area.

/** * tableName: database name * basePoint: */ - (Boolean)splitTable:(sqlite3 *)ppDb tabelName:(NSString *__nonnull)tableName basePoint:(NSUInteger)basePoint;Copy the code
  1. Query operation
- (NSMutableArray *)selectWithSpliteFile:(sqlite3 *)ppDb tableName:(NSString *__nonnull)tableName basePoint:(NSUInteger)basePoint;
Copy the code
  1. The insert
- (Boolean)insertWithSpliteFile:(sqlite3 *)ppDb tableName:(NSString *)tableName basePoint:(NSUInteger)basePoint insertSQL:(NSString *)insertSQL;
Copy the code
  1. The update operation
- (Boolean)updateWithSpliteFile:(sqlite3 *)ppDb tableName:(NSString *)tableName basePoint:(NSUInteger)basePoint updateSQL:(NSString *)updateSQL;
Copy the code
  1. Delete operation
- (Boolean)deleteWithSpliteFile:(sqlite3 *)ppDb tableName:(NSString *)tableName basePoint:(NSUInteger)basePoint deleteSQL:(NSString *)deleteSQL;

Copy the code

Stress testing (PaintinglitePressureOS)

PaintinglitePressureOS is a stress testing system that provides a reasonable estimate of database read and write time, resource consumption, and memory usage, and supports the generation of stress test reports. (No report generated by default)

Paintinglite can measure memory consumption differently for different devices, making it easier for developers to design more reasonable database table structures for different iphones.

- (Boolean)paintingliteSqlitePressure;
Copy the code

The constraint

In order to better implement the operation, conform to the database specification, all table names are lowercase.

Contribute to the project

If you have a feature request or error report, please feel free to send [email protected] to upload the problem, we will provide you with the first time to revise and help. Thank you very much for your support.

Safety disclosure

If you have found a Paintinglite security vulnerability and one that needs to be fixed, you should email it to [email protected] as soon as possible. Thank you for your support.

Download link

Paintinglite_GitHub.