edsgershao
commented
5 years ago I have check my code, all database operation use FMDBQueue inDatabase or inTransaction, and only create one FMDatabaseQueue when single instance create time.
Open edsgershao opened 5 years ago
edsgershao
commented
5 years ago I have check my code, all database operation use FMDBQueue inDatabase or inTransaction, and only create one FMDatabaseQueue when single instance create time.
CarlosLadd
commented
5 years ago I have check my code, all database operation use FMDBQueue inDatabase or inTransaction, and only create one FMDatabaseQueue when single instance create time.
I have many transaction in my app and the FMDBQueue object is initialize in a singleton and never I seen this error. Can you explain more about where your object FMDBQueue is declared and used?
edsgershao
commented
5 years ago I initialize database and FMDatabaseQueue in a singleton.
@interface MFNetworkDBManager()
@property(nonatomic, strong) FMDatabase *database;
@end
@implementation MFNetworkDBManager
/**
create database singleton
@return database singleton
*/
+ (instancetype)shareInstance {
static id sharedInstance;
static dispatch_once_t onceToken;
dispatch_once(&onceToken, ^{
sharedInstance = [[self alloc] init];
});
return sharedInstance;
}
- (instancetype)init {
self = [super init];
if (self) {
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *cachesDir = [paths objectAtIndex:0];
NSString *dbDir = [cachesDir stringByAppendingPathComponent:@"longconnectionservice.db"];
_database = [FMDatabase databaseWithPath:dbDir];
_databaseQueue = [FMDatabaseQueue databaseQueueWithPath:dbDir];
[_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
NSString *createSendPacketTableSql = @"CREATE TABLE IF NOT EXISTS send_packet (packetId TEXT PRIMARY KEY, businessType TEXT, businessName TEXT, businessId TEXT, companyId TEXT, fromPerson TEXT, toPerson TEXT, data TEXT, extend TEXT)";
BOOL success = [db executeUpdate:createSendPacketTableSql];
NSString *createReceivePacketTableSql = @"CREATE TABLE IF NOT EXISTS receive_packet (packetId TEXT PRIMARY KEY, businessType TEXT, businessName TEXT, businessId TEXT, companyId TEXT, fromPerson TEXT, toPerson TEXT, data TEXT, extend TEXT)";
success = [db executeUpdate:createReceivePacketTableSql];
NSString *createReceiveInfoTableSql = @"CREATE TABLE IF NOT EXISTS receive_info (packetId TEXT PRIMARY KEY, receiveTime double)";
success = [db executeUpdate:createReceiveInfoTableSql];
NSLog(@"success = %d", success);
}];
}
return self;
}
@endand used in follow classes
#import "MFSendPacketDao.h"
#import "MFSendPacketTable.h"
#import "MFJSONSerialization.h"
#import "MFNetworkDBManager.h"
#import "LKDBHelper.h"
static NSString * const tableName = @"send_packet";
@implementation MFSendPacketDao
#pragma mark - public
- (BOOL) insertPacket:(MFSendPacket *) packet {
__block BOOL insertSuccess = NO;
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
[mgr.databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO %@(packetId, businessType, businessName, businessId, companyId, fromPerson, toPerson, data, extend) VALUES(?,?,?,?,?,?, ?,?, ?)", tableName];
NSString *extend = [MFJSONSerialization dictionaryToJsonString:packet.extendAttibute];
insertSuccess = [db executeUpdate:insertSql, packet.packetId, packet.businessType, packet.businessName, packet.businessId, packet.companyId, packet.from, packet.to, packet.data, extend];
}];
return insertSuccess;
}
- (BOOL) deletePacket:(MFSendPacket *) packet {
__block BOOL deleteSuccess = NO;
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
[mgr.databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE packetId = ?", tableName];
deleteSuccess = [db executeUpdate:deleteSql, packet.packetId];
}];
return deleteSuccess;
}
- (NSArray<MFSendPacket *> *) getPackets {
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
__block NSMutableArray<MFSendPacket *> *packets = [[NSMutableArray<MFSendPacket *> alloc] init];
[mgr.databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
NSString *selectSql = [NSString stringWithFormat:@"SELECT * FROM %@", tableName];
FMResultSet *rs = [db executeQuery:selectSql];
while ([rs next]) {
MFSendPacket *sendPacket = [[MFSendPacket alloc] init];
sendPacket.packetId = [rs stringForColumn:@"packetId"];
sendPacket.businessType = [rs stringForColumn:@"businessType"];
sendPacket.businessName = [rs stringForColumn:@"businessName"];
sendPacket.businessId = [rs stringForColumn:@"businessId"];
sendPacket.companyId = [rs stringForColumn:@"companyId"];
sendPacket.from = [rs stringForColumn:@"fromPerson"];
sendPacket.to = [rs stringForColumn:@"toPerson"];
sendPacket.data = [rs stringForColumn:@"data"];
NSString *extend = [rs stringForColumn:@"extend"];
sendPacket.extendAttibute = [MFJSONSerialization jsonStringToDictionary:extend];
sendPacket.sendPacketType = TYPE_MUST_ARRIVED;
[packets addObject:sendPacket];
}
[rs close];
}];
return packets;
}
-(BOOL) isExistsFromDB:(MFSendPacket*) packet {
__block BOOL isExist = NO;
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
[mgr.databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
NSMutableArray<MFSendPacket *> *packets = [[NSMutableArray<MFSendPacket *> alloc] init];
NSString *selectSql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE packetId = ?", tableName];
FMResultSet *rs = [db executeQuery:selectSql, packet.packetId];
while ([rs next]) {
MFSendPacket *sendPacket = [[MFSendPacket alloc] init];
sendPacket.packetId = [rs stringForColumn:@"packetId"];
[packets addObject:sendPacket];
}
isExist = packets.count > 0;
}];
return isExist;
}
- (BOOL) isExistsSameBusinessPacket:(MFSendPacket *) packet {
__block BOOL isExist = NO;
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
[mgr.databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
NSMutableArray<MFSendPacket *> *packets = [[NSMutableArray<MFSendPacket *> alloc] init];
NSString *selectSql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE packetId = ?", tableName];
FMResultSet *rs = [db executeQuery:selectSql, packet.packetId];
while ([rs next]) {
MFSendPacket *sendPacket = [[MFSendPacket alloc] init];
sendPacket.packetId = [rs stringForColumn:@"packetId"];
[packets addObject:sendPacket];
}
isExist = packets.count > 0;
}];
return isExist;
}
@end#import "MFReceiveInfoDao.h"
#import "MFNetworkDBManager.h"
#import <Xlog/Xlog.h>
static NSString * const tableName = @"receive_info";
@implementation MFReceiveInfoDao
- (BOOL) insertReceiveInfo:(MFReceiveInfoTable *) receiveInfo {
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
__block BOOL insertSuccess = NO;
[mgr.databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
//NSString * insertSql = [NSString stringWithFormat:@"INSERT INTO %@(packetId, receiveTime) VALUES(?,?)", tableName];
//NSNumber *number = [[NSNumber alloc] initWithDouble:receiveInfo.receiveTime];
//insertSuccess = [db executeUpdate:insertSql, receiveInfo.packetId, number];
NSString * insertSql = [NSString stringWithFormat:@"INSERT INTO %@(packetId, receiveTime) VALUES('%@', %f)", tableName, receiveInfo.packetId, receiveInfo.receiveTime];
insertSuccess = [db executeUpdate:insertSql];
}];
return insertSuccess;
}
- (BOOL) deleteReceiveInfo:(MFReceiveInfoTable *) receiveInfo {
__block BOOL deleteSuccess = NO;
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
[mgr.databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE packetId = ?", tableName];
deleteSuccess = [db executeUpdate:deleteSql, receiveInfo.packetId];
}];
return deleteSuccess;
}
- (BOOL) isExistInDB:(MFReceiveInfoTable *) receiveInfo {
__block BOOL isExist = NO;
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
[mgr.databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE packetId = '%@'", tableName, receiveInfo.packetId];
FMResultSet* rs = [db executeQuery:querySql];
NSMutableArray<MFReceiveInfoTable *> *tables = [[NSMutableArray<MFReceiveInfoTable *> alloc] init];
while ([rs next]) {
MFReceiveInfoTable *table = [[MFReceiveInfoTable alloc] init];
table.packetId = [rs stringForColumn:@"packetId"];
table.receiveTime = [rs doubleForColumn:@"receiveTime"];
[tables addObject:table];
}
isExist = tables.count > 0;
}];
return isExist;
}
- (void) deleteReceiveInfoLessThan:(NSTimeInterval) earlierDate {
__block BOOL deleteSuccess = NO;
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
[mgr.databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE receiveTime < ?", tableName];
deleteSuccess = [db executeUpdate:deleteSql, @(earlierDate)];
}];
}
@end#import "MFReceivePacketDao.h"
#import "MFJSONSerialization.h"
#import "MFNetworkDBManager.h"
#import "MFNetworkConstants.h"
#import "MFNetworkErrorCode.h"
#import <Xlog/Xlog.h>
#import <Bugly/Bugly.h>
static NSString * const tableName = @"receive_packet";
@implementation MFReceivePacketDao
#pragma mark - public
- (BOOL) insertPacket:(MFReceivedPacket *) packet {
__block BOOL insertSuccess = NO;
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
[mgr.databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO %@(packetId, businessType, businessName, businessId, companyId, fromPerson, toPerson, data, extend) VALUES(?,?,?,?,?,?, ?,?, ?)", tableName];
NSString *extend = [MFJSONSerialization dictionaryToJsonString:packet.extendAttibute];
insertSuccess = [db executeUpdate:insertSql, packet.packetId, packet.businessType, packet.businessName, packet.businessId, packet.companyId, packet.from, packet.to, packet.data, extend];
if (!insertSuccess) {
XLOG_ERROR(@"insert failed packetId = %@, businessType = %@, businessName = %@, businessId = %@, companyId = %@, fromPerson = %@, toPerson = %@, data = %@, extend = %@", packet.packetId, packet.businessType, packet.businessName, packet.businessId, packet.companyId, packet.from, packet.to, packet.data, extend);
}
}];
return insertSuccess;
}
- (BOOL) deletePacket:(MFReceivedPacket *) packet {
if (![self isExistInDB:packet]) {
return YES;
}
__block BOOL deleteSuccess = NO;
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
[mgr.databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE packetId = ?", tableName];
deleteSuccess = [db executeUpdate:deleteSql, packet.packetId];
if (!deleteSuccess) {
XLOG_ERROR(@"delete failed sql = %@, packetId = %@", deleteSql, packet.packetId);
}
}];
return deleteSuccess;
}
- (NSArray<MFReceivedPacket *> *) getAllPackets {
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
__block NSMutableArray<MFReceivedPacket *> *packets = [[NSMutableArray<MFReceivedPacket *> alloc] init];
[mgr.databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
NSString *selectSql = [NSString stringWithFormat:@"SELECT * FROM %@", tableName];
FMResultSet *rs = [db executeQuery:selectSql];
while ([rs next]) {
MFReceivedPacket *receivePacket = [[MFReceivedPacket alloc] init];
receivePacket.packetId = [rs stringForColumn:@"packetId"];
receivePacket.businessType = [rs stringForColumn:@"businessType"];
receivePacket.businessName = [rs stringForColumn:@"businessName"];
receivePacket.businessId = [rs stringForColumn:@"businessId"];
receivePacket.companyId = [rs stringForColumn:@"companyId"];
receivePacket.from = [rs stringForColumn:@"fromPerson"];
receivePacket.to = [rs stringForColumn:@"toPerson"];
receivePacket.data = [rs stringForColumn:@"data"];
NSString *extend = [rs stringForColumn:@"extend"];
receivePacket.extendAttibute = [MFJSONSerialization jsonStringToDictionary:extend];
[packets addObject:receivePacket];
}
}];
return packets;
}
- (BOOL) isExistInDB:(MFReceivedPacket *) packet {
__block BOOL isExist = NO;
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
[mgr.databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE packetId = '%@'", tableName, packet.packetId];
NSMutableArray<MFReceivedPacket *> *packets = [[NSMutableArray<MFReceivedPacket *> alloc] init];
FMResultSet *rs = [db executeQuery:querySql];
while ([rs next]) {
MFReceivedPacket *receivePacket = [[MFReceivedPacket alloc] init];
receivePacket.packetId = [rs stringForColumn:@"packetId"];
receivePacket.businessType = [rs stringForColumn:@"businessType"];
receivePacket.businessName = [rs stringForColumn:@"businessName"];
receivePacket.businessId = [rs stringForColumn:@"businessId"];
receivePacket.companyId = [rs stringForColumn:@"companyId"];
receivePacket.from = [rs stringForColumn:@"fromPerson"];
receivePacket.to = [rs stringForColumn:@"toPerson"];
receivePacket.data = [rs stringForColumn:@"data"];
NSString *extend = [rs stringForColumn:@"extend"];
receivePacket.extendAttibute = [MFJSONSerialization jsonStringToDictionary:extend];
[packets addObject:receivePacket];
}
isExist = packets.count > 0;
}];
return isExist;
}
- (BOOL) batchInsertPackets:(NSArray<MFReceivedPacket*> *) packets {
if (!packets) {
return YES;
}
if (packets.count <= 0) {
return YES;
}
__block BOOL batchInsertSuccess = YES;
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
[mgr.databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) {
BOOL insertSuccess = NO;
NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO %@(packetId, businessType, businessName, businessId, companyId, fromPerson, toPerson, data, extend) VALUES(?,?,?,?,?,?, ?,?, ?)", tableName];
for (MFReceivedPacket *packet in packets) {
NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE packetId = '%@'", tableName, packet.packetId];
FMResultSet *rs = [db executeQuery:querySql];
if (rs.next) {
XLOG_INFO(@"exist same packetId packet");
} else {
NSString *extend = [MFJSONSerialization dictionaryToJsonString:packet.extendAttibute];
insertSuccess = [db executeUpdate:insertSql, packet.packetId, packet.businessType, packet.businessName, packet.businessId, packet.companyId, packet.from, packet.to, packet.data, extend];
if (!insertSuccess) {
NSString *errorMessage = [NSString stringWithFormat:@"batch insert failed packetId = %@, businessType = %@, businessName = %@, businessId = %@, companyId = %@, fromPerson = %@, toPerson = %@, data = %@, extend = %@", packet.packetId, packet.businessType, packet.businessName, packet.businessId, packet.companyId, packet.from, packet.to, packet.data, extend];
NSError *batchInsertError = [NSError errorWithDomain:MFLongConnectionLogicErrorDomain code:MFErrorDatabaseOperationFailed userInfo:@{NSLocalizedDescriptionKey:errorMessage}];
[Bugly reportError:batchInsertError];
XLOG_ERROR(errorMessage);
*rollback = YES;
batchInsertSuccess = NO;
return;
}
}
}
}];
return batchInsertSuccess;
}
- (BOOL) batchDeletePackets:(NSArray<MFReceivedPacket*> *) packets {
if (!packets) {
return YES;
}
if (packets.count <= 0) {
return YES;
}
__block BOOL batchDeleteSuccess = NO;
MFNetworkDBManager *mgr = [MFNetworkDBManager shareInstance];
[mgr.databaseQueue inDatabase:^(FMDatabase * _Nonnull db) {
NSMutableString * where = [[NSMutableString alloc] initWithString:@"packetId IN ("];
NSInteger count = [packets count];
for (NSInteger i = 0; i < count; i ++) {
MFReceivedPacket *receivedPacket = [packets objectAtIndex:i];
if (i == 0) {
[where appendFormat:@"'%@'",receivedPacket.packetId];
} else {
[where appendFormat:@",'%@'",receivedPacket.packetId];
}
}
[where appendString:@")"];
NSString *batchDeleteSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@", tableName, where];
batchDeleteSuccess = [db executeUpdate:batchDeleteSql];
if (!batchDeleteSuccess) {
XLOG_ERROR(@"batch delete failed sql = %@", batchDeleteSql);
}
}];
return batchDeleteSuccess;
}
@end@CarlosLadd Can you give me a help? Thanks!
CarlosLadd
commented
5 years ago Well, I can't see your FMDatabaseQueue object declaration but is similar like this:
*@property (nonatomic) FMDatabaseQueue databaseQueue;**
Don't use "strong" because we are in a singleton and you need to delete de FMDatabase Object because we don't use anymore. I can't see an error directly but I think in this method there is something wrong:
The "for loop" execute many times "executeQuery" method you need rethink that logic, In your Singleton class you can do a method like this to make sure the object is already exists:
For next step you can invoke the method like this:
[MFNetworkDBManager. shareInstance.execDB inDatabase:^(FMDatabase * _Nonnull db) { // do the magic }];
@edsgershao I hope it helps you :) let me know
edsgershao
commented
5 years ago @CarlosLadd Thanks for your help! I modify it by your advices, can't see this issue any more, I think it may be solved.
CarlosLadd
commented
5 years ago Excellent :) cheers
crmo
commented
5 years ago I also encountered the same problem, close FMResultSet manually may be helpful.
FMResultSet *rs = [db executeQuery:querySql];
if (rs.next) {
XLOG_INFO(@"exist same packetId packet");
// close FMResultSet manually
[rs close];
}
Sometimes I met a crash like this. Xcode console output "2019-01-04 15:39:47.679076+0800 MicroVideo[64613:1974582] [logging] BUG IN CLIENT OF sqlite3.dylib: illegal multi-threaded access to database connection". It seems that multi-threaded access sqlite database . I followed FMDB thread safety rule to code, but it's strange to crash . I don't know why. Can somebody give advices ?Any suggestions will be nice.