Materialized views

[stevelacey]

I wrote a materialized views extension which I can pull request if this project is still being maintained.

This allows you to throw --materialize on the artisan command and get tables instead of views.

<?php

namespace App\Console\Commands;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;
use Stats4SD\SqlViews;

class UpdateSqlViewsCommand extends SqlViews\Commands\UpdateSqlViews
{
    protected $signature = 'updatesql {--materialize : Materialize the views to tables}';

    public function handle()
    {
        if (config('sqlviews.folder.create.procedures')) {
            $countProcs = $this->processProcsDir(base_path('database/procedures'));
            $this->info($countProcs . ' procedures run');
        }

        if (config('sqlviews.folder.create.views')) {
            $materialize = $this->input->getOption('materialize');
            $countViews = $this->processDir(base_path('database/views'), $materialize);
            $this->info($countViews . ' views ' . ($materialize ? 'materialized' : 'created'));
        }
    }

    public function makeView(string $name, string $query)
    {
        $materialize = $this->input->getOption('materialize');

        if (Schema::hasTable($name)) {
            DB::statement("DROP TABLE {$name}");
        }

        if (Str::contains($query, '---')) {
            [$query, $indexes] = explode('---', $query);

            $query = trim($query);
        }

        return parent::makeView($name, $query);
    }

    public function materializeView(string $name, string $query)
    {
        $indexes = [];

        if (Str::contains($query, '---')) {
            [$query, $indexes] = explode('---', $query);

            $query = trim($query);
            $indexes = explode("\n", trim($indexes));
        }

        DB::statement("RENAME TABLE {$name} TO {$name}_view");

        $result = DB::statement("CREATE TABLE {$name} (SELECT * FROM {$name}_view)");

        foreach ($indexes as $index) {
            DB::statement("ALTER TABLE {$name} {$index}");
        }

        DB::statement("DROP VIEW {$name}_view");

        return $result;
    }

    public function processDir(string $dir_path, bool $materialize = false)
    {
        $countViews = parent::processDir($dir_path);

        if ($materialize) {
            $files = scandir($dir_path);

            foreach ($files as $file) {
                if (is_dir("{$dir_path}/{$file}") && $file != '.' && $file != '..') {
                    $this->materializeViews("{$dir_path}/{$file}");
                }
            }

            foreach ($files as $file) {
                if (Str::endsWith($file, '.sql')) {
                    $name = Str::replaceLast('.sql', '', $file);
                    $query = file_get_contents("{$dir_path}/{$file}");

                    $this->materializeView($name, $query);
                }
            }
        }

        return $countViews;
    }
}

Also adds support for altering the table via a syntax at the end of the view files, e.g. to add indexes:

SELECT ...

---

ADD PRIMARY KEY ...
ADD INDEX ...
ADD CONSTRAINT ...