SQLite Fix Error – Database Disk Image Is Malformed

SQLite3 Fix Error – Database Disk Image Is Malformed

We will be using SQLite Browser, so download it here if you don’t have it.  It’s pretty easy to fix it, just follow the steps below

  1. Load up your corrupted database into SQLite Browser,
  2. Run the integrity check  under Tools > Integrity Check .The images below shows the steps and the result (error) I got
    Run Integrity Check 0
    Run Integrity Check 0
    Run Integrity Check 1
    Run Integrity Check 1

    Run Integrity Check 2 Results
    Run Integrity Check 2 Results
  3. If it returns ‘OK’, it means your database is not corrupted. If it returns anything else, you will need to fix your database. Follow through with this guide.
  4. Export your database to any format

    SQLite Browser Export
    SQLite Browser Export
  5. Import your database back to .sqlite3 (or any format you which)
    SQLite Browser Import 0
    SQLite Browser Import 0

    SQLite Browser Import 1
    SQLite Browser Import 1
  6. If you run the integrity check once more, it should return ‘OK’

If you face any other issues, let me know in the comments below and I’ll try my best to help you out

Enjoyed the content ? Share it with your friends !

PHP SQLite3 Check For Constraint Violation

PHP SQLite3 Check For Constraint Violation

Sometimes we may need to use a constraint in our database design (e.g UNIQUE constraint) and want to be able to handle when such violations occur for logging purposes or others.

The PHP library for SQLite3 provides a way for us to know the last error that happened – its error code and message using SQLite3::lastErrorCode and SQLite3::lastErrorMsg

From SQLite documentation on the error code list here, you can see that SQLITE_CONSTRAINT error code is 19. So to check for a constraint violation, it will look something like this

// $this->db is the SQLite3 object / class instance
$last_error_code = $this->db->lastErrorCode();
$last_error_msg  = $this->db->lastErrorMsg();
if( $this->db->lastErrorCode() == self::SQLITE3_CONSTRAINT ) {	
    // Handle CONSTRAINT violations here
}

However, the SQLite3 library for PHP does not provide a way for us to get extended error code (or at least, i’m unable to find it from their documentation page here). Hence, to check for exactly which constraint was violated (e.g UNIQUE), you will have to check the SQLite3::lastErrorMsg instead.

$last_error_msg  = $this->db->lastErrorMsg();
if( strstr($last_error_msg, 'UNIQUE') ) {
   //UNIQUE CONSTRAINT VIOLATION ...
}

 

Enjoyed the content ? Share it with your friends !

PHP SQLite3 optimize large amount of inserts

PHP SQLite3 optimize large amount of inserts

One of the things many people forget to do is to start and end a transaction yourself instead of letting the internal SQLite3 do it.

If a transaction is not detected by SQLite, it will start a new transaction for each insert you do. As the numbers scale up, the time taken for the entire set of insert queries exponentially increases.

To start and end your own transaction, it is as simple as using the code below (remember to modify it for your own use)

// START TRANSACTION
$this->db->exec('BEGIN;'); 

// Prepare query
$stmt = $this->db->prepare($insert_query);

// Bind parameters to statement variables
$stmt->bindParam(':field', $field_value);

// Loop execute
foreach ($records as $record) {
    $field_value = $record['field'];
    $stmt->execute();	
}			

// END TRANSACTION
$this->db->exec('COMMIT;'); // END TRANSACTION

 

Enjoyed the content ? Share it with your friends !