Facing memory issue while Recursively inserting and updating data in database in swift

Question

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!
}

Answer 1

You will leak memory because most of your sqlite3_prepare_v2 calls are not followed by the corresponding sqlite3_finalize calls. Every time you successfully prepare a SQL statement, you must make sure to finalize that statement when you're done with it.