Paintinglite Sqlite3 SDK

Paintinglite [ Github ] https://github.com/CreaterOS/Paintinglite

Pod installation

pod'Paintinglite', :git =>'https://github.com/CreaterOS/Paintinglite.git'#, :tag => '2.1.1' Introduction

Paintinglite is an excellent and fast Sqlite3 database framework. Paintinglite has good encapsulation of data, fast data insertion characteristics, and can still show good resource utilization for huge amounts of data. Paintinglite supports object mapping and has carried out a very lightweight object encapsulation on sqlite3. It establishes a mapping relationship between POJOs and database tables. Paintinglite can automatically generate SQL statements and manually write SQL statements to achieve convenient development and efficient querying. All-in-one lightweight framework.

Database operation (PaintingliteSessionManager)

Build a library

Create PaintingliteSessionManager, create a database through the manager.

-(Boolean)openSqlite:(NSString *)fileName;

-(Boolean)openSqlite:(NSString *)fileName completeHandler:(void(^ __nullable)(NSString *filePath,PaintingliteSessionError *error,Boolean success))completeHandler; **Paintinglite has a good processing mechanism. It creates a database by passing in the database name. Even if the database suffix is not standardized, it can still create a database with a .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.."]; **Get the absolute path of the created database. **

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

-(Boolean)releaseSqlite;

-(Boolean)releaseSqliteCompleteHandler:(void(^)(PaintingliteSessionError *error,Boolean success))completeHandler; 3. Create a table

Three ways to create a table:

SQL creation [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==="); } }]; Table name creation [self.sessionM createTableForName:@"student" content:@"name TEXT,age INTEGER"]; Object creation User *user = [[User alloc] init]; [self.sessionM createTableForObj:user createStyle:PaintingliteDataBaseOptionsUUID]; Object creation can automatically generate primary keys:

Primary key Type UUID String ID Value 4. Update table

Three ways to update the table:

SQL Update

Table name update

[self.sessionM alterTableForName:@"cart" newName:@"carts"]; [self.sessionM alterTableAddColumnWithTableName:@"carts" columnName:@"newColumn" columnType:@"TEXT"]; Object update Update User table operation #import

NS_ASSUME_NONNULL_BEGIN

@interface User: NSObject

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

@end

NS_ASSUME_NONNULL_END According to the mapping relationship between the table and the object, the table fields are automatically updated according to the object.

User *user = [[User alloc] init]; [self.sessionM alterTableForObj:user]; 5. Delete operation

Three ways to delete a table:

SQL operations Table name deletion [self.sessionM execTableOptForSQL:@"DROP TABLE carts" completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success) { if (success) { NSLog(@"===DROP TABLE SUCCESS==="); } }]; Object deletion User *user = [[User alloc] init]; [self.sessionM dropTableForObj:user]; Table operation

Query

**Query can provide the feature of query results encapsulated in array or directly encapsulated by object. **

General inquiry -General enquiries -(NSMutableArray *)execQuerySQL:(NSString *__nonnull)sql; -(Boolean)execQuerySQL:(NSString *__nonnull)sql completeHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success,NSMutableArray *resArray))completeHandler; [self.sessionM execQuerySQL:@"SELECT * FROM student" completeHandler:^(PaintingliteSessionError * _Nonnull error, Boolean success, NSMutableArray * _Nonnull resArray) { if (success) { for (NSDictionary *dict in resArray) { NSLog(@"%@",dict); } } }]; -Package query

Encapsulated query can encapsulate query results into objects corresponding 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; 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); } } }]; Conditional query Conditional query syntax rules:

Subscripts start from 0 Use? As a placeholder for conditional parameters SELECT * FROM user WHERE name =? And age =? -(NSMutableArray *)execPrepareStatementSql; -(Boolean)execPrepareStatementSqlCompleteHandler:(void(^ __nullable)(PaintingliteSessionError *error,Boolean success,NSMutableArray *resArray))completeHandler; [self.sessionM execQuerySQLPrepareStatementSql:@"SELECT * FROM student WHERE name = ?"]; [self.sessionM setPrepareStatementPQLParameter:0 paramter:@"CreaterOS"]; NSLog(@"%@",[self.sessionM execPrepareStatementSql]); Fuzzy query -(NSMutableArray *)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; [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 * _Nonnull resArray, NSMutableArray * _Nonnull resObjList) { if (success) { for (NSDictionary *dict in resArray) { NSLog(@"%@",dict); } } }]; Paging query -(NSMutableArray *)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 ; [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); } } }]; Sort 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; 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 resOb if (success) { for (Student *stu in resObjList) { NSLog(@"stu.name = %@ and stu.age = %@",stu.name,stu.age); } } }]; 2. Increase 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; SQL Insert [self.sessionM insert:@"INSERT INTO student(name,age) VALUES('CreaterOS',21),('Painting',19)"]; Object Insertion #import

NS_ASSUME_NONNULL_BEGIN

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

NS_ASSUME_NONNULL_END Student *stu = [[Student alloc] init]; stu.name = @"ReynBryant"; stu.age = [NSNumber numberWithInteger:21]; [self.sessionM insertWithObj:stu completeHandler:nil]; 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; SQL update data [self.sessionM update:@"UPDATE student SET name ='Painting' WHERE name ='ReynBryant'"]; Object update Student *stu = [[Student alloc] init]; stu.name = @"CreaterOS"; [self.sessionM updateWithObj:stu condition:@"age = 21" completeHandler:nil]; 4. Delete data

-(Boolean)del:(NSString *__nonnull)sql; -(Boolean)del:(NSString *__nonnull)sql completeHandler:(void(^)(PaintingliteSessionError *error,Boolean success))completeHandler; PQL Syntax (PaintingliteSessionManager) Through the PQL statement, Paintinglite can automatically help you complete the writing of the SQL statement.

PQL grammar rules (uppercase | the class name must be associated with the 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; [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); } } }]; [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); } } }]; [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); } } }]; [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); } } }];

[self.sessionM execQueryPQLPrepareStatementPQL:@"FROM Student WHERE name = ?"]; [self.sessionM setPrepareStatementPQLParameter:@[@"CreaterOS"]]; NSLog(@"%@",[self.sessionM execPrepareStatementPQL]); Aggregate function (PaintingliteAggregateFunc) Paintinglite encapsulates Sqlite3 aggregation functions, and automatically writes SQL statements to get the aggregation results.

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

Transaction (PaintingliteTransaction) Sqlite3 development defaults that an insert statement is a transaction. If there are multiple insert statements, the transaction will be repeated. This consumes a lot of resources. Paintinglite provides an operation to start a transaction (display transaction).

(void)begainPaintingliteTransaction:(sqlite3 *)ppDb;
(void)commit:(sqlite3 *)ppDb;
(void)rollback:(sqlite3 *)ppDb; Constraint In order to achieve better operation and comply with database specifications, table names are all lowercase.