/ php

SQLite to JSON with PHP

Sometimes when you use SQLite, and even more when using it with mobile devices running on iOS, you'd like to work with SQLite. But, on iOS you'd like to use CoreData instead of pure SQLite, because CoreData performance is greater than pure SQLite.

So What if I have a unique database for both Android and iOS and I wish to use it on both? Ok there is no problem for the first one, but there is a need of translating the SQLite database from the native one to a CoreData equivalent in order to profit of its benefits…

The common interface we could use is the JSON format as a third party, since there exist a lot of seeders like RestKit that will map your JSON into a CoreData equivalent.

One possible way to achieve such a thing could be to dump the SQLite data in a specific way so that we could fetch data and structure into a JSON.

We're going to use PHP for this, because it is simple to use and commonly used. One can use a different language to perform such an operation.

Here are the main tasks we are going to use to achieve it :

  1. Get all tables (names, etc) so that we can go deep into them individually and fetch informations
  2. For each table,
    1. get all information we would need about the columns types, names, etc…
    2. fetch all rows and build the part of the JSON for it.

This is almost quite simple, and this is how we can achieve it.

Getting information about the tables

The first thing we need to do is to get information about all the tables present in the database. We want to know some information about it, their name, etc… There exist an SQL statement for that, which is the following :

sqlite> SELECT * FROM sqlite_master WHERE type='table';

Then, we should try fetch a thinner view of the structure of each table.

Getting information about the column of each table

We can fetch all data about the structure of a table, using the following PRAGMA statement of SQLite :

sqlite> PRAGMA table_info(<table_name>);

This will return a list of information about the structure of the columns, like their name, their type, telling if they are or not PRIMARY keys, etc???

Fetching data from the rows

At last we would fetch data according to the tables we've found in the Database. We could do this with a real simple query :

sqlite> SELECT * from `<table_name>`;

Final result

You'll find then the source code for a PHP script that do all this job for you :

<?php

$sDatabaseFileName = '/Users/vaseltior/Dropbox/IVD.db';
$oDatabaseHandler = new SQlite3ToJSONConverter($sDatabaseFileName);
var_dump($oDatabaseHandler->getJSONFromSQLite());

/**
 * The aim of this class is to offer a transformation
 * of a SQLite database to its JSON stirng format equivalent
 * @author Samuel Grau : <samuel.grau@gmail.com>
 */
class SQlite3ToJSONConverter {
    //==========================================================================
    // Private properties
    //==========================================================================

    /**
     * The SQlite 3 database
     */
    private $database = NULL;

    //==========================================================================
    // Constructors / Destructors
    //==========================================================================

    /**
     * Open the database by creating a new SQLite3 instance from the
     * filename given to the constructor initially
     */
    public function __construct($sDatabaseFileName = NULL) {
        $this->openDatabase($sDatabaseFileName);
    }

    //==========================================================================
    // Protected methods
    //==========================================================================

    /**
     * Open the database by creating a new SQLite3 instance from the
     * filename given to the constructor initially
     */
    protected function openDatabase($databaseFileName = NULL) {
        $this->database = new SQLite3($databaseFileName);
        assert($this->database);
    }

    //==========================================================================
    // Private methods
    //==========================================================================

    /**
     * This method returns information about the given table name
     */
    private function columnsInformationWithTableName($sTableName) {
        // Setting the query
        $sQueryColumns = "PRAGMA table_info(':name')";
        $sQueryColumns = str_replace(':name', $sTableName, $sQueryColumns);
        
        // Preparing the query
        $oResult = $this->database->query($sQueryColumns);

        // Fetching result and preparing formatting of the result to a 
        // convenient usable format.
        $aResult = array();
        while ($aRow = $oResult->fetchArray(SQLITE3_ASSOC)) {
            $aResult[] = $aRow;
        }
        
        return $aResult;
    }
    
    /**
     * This method returns information about the given table name
     */
    private function tablesInformation() {
        // Launch the query to find information about tables
        // of the database
        $sQueryTables = "SELECT * FROM sqlite_master WHERE type='table'";
        $uResult = $this->database->query($sQueryTables);
        
        // Fetching result and preparing formatting of the result to a 
        // convenient usable format.
        $aResult = array();
        while ($aRow = $uResult->fetchArray(SQLITE3_ASSOC)) {
            $aResult[] = $aRow;
        }
        
        return $aResult;
    }
    
    private function schemaInformation() {
        $aSchema = array();
        $aTables = $this->tablesInformation();
        foreach ($aTables as $aTableInformation) {
            if (!isset($aTableInformation['name'])) {
                throw new Exception();
            }
            
            $sTableName = $aTableInformation['name'];
            $aColumns = $this->columnsInformationWithTableName($sTableName);            
            $aSchema[$sTableName] = $aColumns;
            
            unset($sTableName);
        }
        unset($aTables);
        return $aSchema;
    }
    
    private function dataInformation($sTableName) {
    
        $sQueryData = "SELECT * FROM `$sTableName`";
        $uResult = $this->database->query($sQueryData);
        
        // Fetching result and preparing formatting of the result to a 
        // convenient usable format.
        $aResult = array();
        while ($aRow = $uResult->fetchArray(SQLITE3_ASSOC)) {
            $aResult[] = $aRow;
        }
        
        return $aResult;
    }
    
    //==========================================================================
    // Public methods
    //==========================================================================

    /**
     * Main method that will export the SQLite Database to a JSON stirng format.
     */
    public function getJSONFromSQLite() {
        $aResult = array();
        
        $aTables = $this->schemaInformation();
        $aData = array();
        foreach($aTables as $sTableName => $aColumns) {
            $aRows = $this->dataInformation($sTableName);
            $aData[$sTableName] = $aRows;
        }
        
        $aResult['database_schema'] = $aTables;
        $aResult['data'] = $aData;
        
        return json_encode($aResult);
    }
}


?>

If you have any questions, leave it below! or on GitHub

Enjoy!