I'm inserting and updating more than 100000 data in database. I'm using libsqlite3 framework.
Issue: I'm facing is for each insertion memory increase to 4-5 mb and never gets released. Any better solution?
class DataBase{
let COMPANY_TABLE_NAME = "CompanyTable"
let fileName = "\(Constants.sharedInstance.DATABASE_NAME).sqlite"
let DBPath :String?
init() {
DBPath = FileManagement.sharedInstance.getDocumentFilePath(fileName)
}
private var dbPointer: OpaquePointer?
let SQLITE_STATIC = unsafeBitCast(0, to: sqlite3_destructor_type.self)
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
deinit
{
sqlite3_close(dbPointer)
}
func openDataBase(dbPath:String) -> OpaquePointer {
var db: OpaquePointer?
if sqlite3_open(dbPath, &db) == SQLITE_OK
{
print("Successfully opened connection to database at \(dbPath)")
}
else
{
//call to load database from file system
}
return db!
}
func inserttoCompanyTable(org_code: String , dataString:String)
{
let insertSql = "INSERT INTO CompanyTable(orgcode,company_data) VALUES (?,?)"
let insertStatement = prepareStatement(sql: insertSql)
sqlite3_bind_text(insertStatement, 0, org_code, -1, SQLITE_TRANSIENT)
sqlite3_bind_text(insertStatement, 1, dataString, -1,SQLITE_TRANSIENT)
if sqlite3_step(insertStatement) != SQLITE_DONE {
let errmsg = String(cString: sqlite3_errmsg(insertStatement))
print("failure inserting foo: \(errmsg)")
}
sqlite3_finalize(insertStatement)
sqlite3_close_v2(insertStatement)
}
func readAllData()-> [Company]
{
var companiesArray:[Company] = [Company]()
let selectQuery = "SELECT * FROM \(COMPANY_TABLE_NAME);"
let selectStatement = prepareStatement(sql: selectQuery)
while sqlite3_step(selectStatement) == SQLITE_ROW {
if let comStr = sqlite3_column_text(selectStatement, 1)
{
let comString = String(describing: comStr)
let comOBj = Mapper<Company>().map(JSONString: comString)
companiesArray.append(comOBj!)
}
}
return companiesArray
}
func isCompanyAvailable(org_code:String) -> Bool {
let selectQuery = "SELECT * FROM \(COMPANY_TABLE_NAME) WHERE \(CompanyTable.orgcode) =?;"
let selectStatement = prepareStatement(sql: selectQuery)
sqlite3_bind_text(selectStatement, 0, org_code, -1, SQLITE_TRANSIENT)
if sqlite3_step(selectStatement) == SQLITE_ROW
{
return true
}
else
{
return false
}
}
func updateTableData(company: Company)-> Bool {
let objString = company.toJSONString()
let updateQuery = "UPDATE TABLE \(COMPANY_TABLE_NAME) SET \(CompanyTable.company_data) = \(objString) WHERE \(CompanyTable.orgcode) = \(company.orgCode);"
let updateStatement = prepareStatement(sql: updateQuery)
if sqlite3_step(updateStatement) == SQLITE_ROW
{
return true
}
else
{
return false
}
}
func deleteFromCompanyTable(company:Company) {
let deleteQuery = "DELETE FROM \(COMPANY_TABLE_NAME) WHERE \(CompanyTable.orgcode) = \(company.orgCode);"
let deleteStatement = prepareStatement(sql: deleteQuery)
if sqlite3_step(deleteStatement) == SQLITE_DONE
{
print("Successfully deleted row.")
} else {
print("Could not delete row.")
}
}
func prepareStatement(sql: String) -> OpaquePointer {
var statement: OpaquePointer?
if sqlite3_open(DBPath, &self.dbPointer) == SQLITE_OK
{
print("Successfully opened connection to database at \(DBPath)")
if sqlite3_prepare_v2(self.dbPointer, sql, -1, &statement, nil) == SQLITE_OK
{
return statement!
}
else
{
let errmsg = String(cString: sqlite3_errmsg(statement))
print("error preparing select: \(errmsg)")
}
}
return statement!
}
