Last active
September 20, 2023 13:26
-
-
Save nviz/eb90afb61858bd13cfe003642477f412 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
namespace App; | |
use Illuminate\Support\Facades\DB; | |
class CreateLaravelHypertable | |
{ | |
/** | |
* Check if database type is pgsql, if so, check if timescaledb extension is installed and convert Laravel table to hypertable. | |
* @return bool | |
* @throws \Exception | |
* @var string $table | |
*/ | |
public static function table(string $table, string $column = 'created_at'): bool | |
{ | |
if(env('DB_CONNECTION') == 'pgsql') | |
{ | |
$test = DB::select("SELECT * FROM pg_available_extensions where name ='timescaledb'"); | |
if(!empty($test)) | |
{ | |
DB::select("DROP TABLE IF EXISTS {$table}_old CASCADE"); | |
DB::select("CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE"); | |
DB::select("ALTER TABLE $table RENAME TO {$table}_old"); | |
DB::select("CREATE TABLE $table (LIKE {$table}_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES)"); | |
DB::select("ALTER TABLE $table DROP COLUMN id"); | |
DB::select("ALTER TABLE $table ADD COLUMN id SERIAL"); | |
DB::select("DROP TABLE IF EXISTS {$table}_old CASCADE"); | |
DB::select("SELECT create_hypertable('$table', '$column')"); | |
return true; | |
} | |
throw new \Exception('timescaledb not installed'); | |
} | |
throw new \Exception('Only pgsql supported'); | |
} | |
} | |
/** | |
* For timescaleDB to function with Laravel, we need to make the created_at (or some other timestamp column) | |
* The primary key. We need to drop the primary 'id' column and convert it to a SERIAL column to maintain | |
* Eloquent relations etc. | |
* | |
* Usage: Put CreateLaravelHyperTable::table('name_of_table'); | |
* in a Laravel migration, after the Schema::create statement. | |
* Example: | |
* Schema::create('user_login_logs', function(Blueprint $table) { | |
* $table->increments('id'); | |
* $table->bigInteger('user_id)->index(); | |
* $table->timestamps(); | |
* }); | |
* | |
* CreateLaravelHypertable::table('user_login_logs'); | |
* | |
**/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thank you!