SQLite support

[JayBizzle]

I know you've mentioned SQLite support in the future but was just wondering if you have any thoughts on the implementation.

For starters, would it be SQLite to SQLite setup or would it allow MySQL to SQLite setup? We use MySQL in production but SQLite in our tests.

We already have something similar to this package that we use to facilitate the MySQL to SQLite migration for testing, but implementing it in a package like this looks a little tricky because I can't see an easy way to get the database credentials for each database as when the tests are running, you cant get the MySQL credentials as they are normally stored in environment vars.

Have you any thoughts on this?

[drfraker]

I'll have to think about a way to do that. You're right, that could be tricky. Hopefully I'll get some time to work on this more next week.

[JayBizzle]

I threw something together yesterday based on this package that we will use internally. I'll create a repo for it later and you can take what you want from it. It's pretty hacky, but solves the MySQL to SQLite problem for us.

[winkbrace]

I always kept the "snapshot" as a sqlite file in a test/resources directory. Copying the file to the location you use in your tests might be even faster than running the create database script in an in-memory sqlite database.

[JayBizzle]

Haven't had chance to finalise this yet, but we are using something very similar to the following code in our current setup

<?php
namespace Jaybizzle\RapidMigrations;

use DB;

use Exception;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Artisan;

class Rapid
{
    /**
     * Path of the database that holds the clean import.
     *
     * @var string
     */
    protected $sourceDb = 'tests/source.sqlite';

    /**
     * The path of the database that the tests run against.
     *
     * @var string
     */
    protected $testDb = 'tests/test_db.sqlite';

    /**
     * Make sure the testing database is up to date.
     */
    public function importSnapshot()
    {
        $this->migrationChanges() ? $this->newSnapshot() : $this->importDatabase();
    }

    /**
     * Determine if there have been migration changes since the last time the snapshot was updated.
     *
     * @return bool
     */
    protected function migrationChanges()
    {
        $snipeFile = config('rapid-migrations.rapidfile-location');
        $snipeDumpFile = config('rapid-migrations.snapshot-location');
        $storedTimeSum = file_exists($snipeFile) ? file_get_contents($snipeFile) : 0;

        $timeSum = collect(File::allFiles(database_path('migrations')))->sum(function ($file) {
            return $file->getMTime();
        });

        if (! $storedTimeSum || (int) $storedTimeSum !== $timeSum || ! file_exists($snipeDumpFile)) {
            // store the new time sum.
            file_put_contents($snipeFile, $timeSum);
            return true;
        }

        return false;
    }

    /**
     * Generate a new snapshot of the MySql database.
     */
    protected function newSnapshot()
    {
        // Artisan::call('migrate:fresh');
        $storageLocation = config('rapid-migrations.snapshot-location');

        // if (empty(shell_exec("which mysqldump"))) {
        //     throw new Exception('mysqldump is not available');
        // }

        $convertor = __DIR__.DIRECTORY_SEPARATOR.'mysql2sqlite';

        if (is_executable($convertor) === false) {
            exec('chmod +x '.$convertor);
        }

        dump("/Applications/MAMP/Library/bin/mysqldump --no-data -u {$this->getRootEnv('DB_USERNAME')} --password={$this->getRootEnv('DB_PASSWORD')} {$this->getRootEnv('DB_DATABASE')} | {$convertor} - > {$storageLocation} 2>/dev/null");

        // Store a snapshot of the db after migrations run.
        exec("/Applications/MAMP/Library/bin/mysqldump --no-data -u {$this->getRootEnv('DB_USERNAME')} --password={$this->getRootEnv('DB_PASSWORD')} {$this->getRootEnv('DB_DATABASE')} | {$convertor} - > {$storageLocation} 2>/dev/null");

        $this->importDatabase();
    }

    /**
     * Import the snapshot file into the database if it hasn't been imported yet.
     */
    protected function importDatabase()
    {
        if (! RapidDatabaseState::$imported) {
            $storageLocation = config('rapid-migrations.snapshot-location');

            @unlink(base_path($this->sourceDb));
            @unlink(base_path($this->testDb));

            touch(base_path($this->sourceDb));

            DB::unprepared(file_get_contents($storageLocation));

            copy(base_path($this->sourceDb), base_path($this->testDb));

            RapidDatabaseState::$imported = true;
        } else {
            $this->reuseExistingDatabase();
        }
    }

    public function reuseExistingDatabase()
    {
        @unlink(base_path($this->sourceDb));
        copy(base_path($this->testDb), base_path($this->sourceDb));
    }

    public function getRootEnv($name)
    {
        $command = 'awk -F= \'$1=="'.$name.'"{print $2;exit}\' '.base_path().'/.env';

        return exec($command);
    }
}

The script we use to convert the MySQL dump to SQLite can be found here https://gist.github.com/JayBizzle/7a51c13bb5432eabc92d70bfc1de5581

[drfraker]

Thanks @JayBizzle, I'm looking into this for another project too. Your code will be helpful.

[drfraker]

Does the latest pull request solve this issue if you were to add specific sqlite binary instead of mysql?

PR 29