Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Migrate classic Spark tables to Jetstream + new Spark
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class MigrateToJetstream extends Migration
{
public function up()
{
Schema::table('users', function (Blueprint $table) {
$table->text('profile_photo_path')->nullable();
$table->text('two_factor_secret')
->after('password')
->nullable();
$table->text('two_factor_recovery_codes')
->after('two_factor_secret')
->nullable();
});
Schema::table('teams', function (Blueprint $table) {
$table->renameColumn('owner_id', 'user_id');
});
Schema::table('teams', function (Blueprint $table) {
$table->boolean('personal_team')->default(false);
});
Schema::create('team_invitations', function (Blueprint $table) {
$table->id();
$table->foreignId('team_id');
$table->string('email');
$table->string('role')->nullable();
$table->timestamps();
$table->unique(['team_id', 'email']);
});
Schema::create('sessions', function (Blueprint $table) {
$table->string('id')->primary();
$table->foreignId('user_id')->nullable()->index();
$table->string('ip_address', 45)->nullable();
$table->text('user_agent')->nullable();
$table->text('payload');
$table->integer('last_activity')->index();
});
Schema::create('team_user', function (Blueprint $table) {
$table->id();
$table->foreignId('team_id');
$table->foreignId('user_id');
$table->string('role')->nullable();
$table->timestamps();
$table->unique(['team_id', 'user_id']);
});
Schema::create('personal_access_tokens', function (Blueprint $table) {
$table->bigIncrements('id');
$table->morphs('tokenable');
$table->string('name');
$table->string('token', 64)->unique();
$table->text('abilities')->nullable();
$table->timestamp('last_used_at')->nullable();
$table->timestamps();
});
}
}
<?php
use App\Domain\Team\Models\Team;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\Schema;
class MigrateTeamUserData extends Migration
{
public function up()
{
DB::table('team_users')
->get()
->each(function (stdClass $teamUser) {
$teamUser = get_object_vars($teamUser);
$teamUser['role'] === 'owner'
? $this->createOwnerOrAdmin($teamUser)
: $this->createMember($teamUser);
});
Schema::drop('team_users');
}
protected function createOwnerOrAdmin(array $teamUser): void
{
$team = Team::find($teamUser['team_id']);
if (! $team) {
echo 'did not find team ' . $teamUser['team_id'] . PHP_EOL;
return;
}
if (is_null($team->user_id)) {
$team->update(['user_id' => $teamUser['user_id']]);
return;
}
if ($team->user_id === $teamUser['user_id']) {
return;
}
DB::table('team_user')->insert([
'team_id' => $teamUser['team_id'],
'user_id' => $teamUser['user_id'],
'role' => 'admin',
]);
}
protected function createMember(array $teamUser)
{
DB::table('team_user')->insert([
'team_id' => $teamUser['team_id'],
'user_id' => $teamUser['user_id'],
'role' => 'member',
]);
}
}
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class MigrateToNextGenSpark extends Migration
{
public function up()
{
$this
->createNewTables()
->dropUnusedTables()
->createSubscriptionTables()
->updateTeamsTable()
->dropOldColumnsFromUsersTable()
->dropOldColumnsFromInvoicesTable();
}
protected function dropUnusedTables(): self
{
Schema::drop('action_events');
Schema::drop('announcements');
return $this;
}
protected function createSubscriptionTables(): self
{
Schema::drop('subscriptions');
Schema::create('subscriptions', function (Blueprint $table) {
$table->id();
$table->foreignId('team_id');
$table->string('name');
$table->string('stripe_id');
$table->string('stripe_status');
$table->string('stripe_plan')->nullable();
$table->integer('quantity')->nullable();
$table->timestamp('trial_ends_at')->nullable();
$table->timestamp('ends_at')->nullable();
$table->timestamps();
$table->index(['team_id', 'stripe_status']);
});
Schema::drop('subscription_items');
Schema::create('subscription_items', function (Blueprint $table) {
$table->id();
$table->foreignId('subscription_id');
$table->string('stripe_id')->index();
$table->string('stripe_plan');
$table->integer('quantity');
$table->timestamps();
$table->unique(['subscription_id', 'stripe_plan']);
});
return $this;
}
protected function createNewTables(): self
{
Schema::create('receipts', function (Blueprint $table) {
$table->id();
$table->foreignId('team_id')->index();
$table->string('provider_id')->index();
$table->string('amount');
$table->string('tax');
$table->timestamp('paid_at');
$table->timestamps();
});
Schema::create('tax_rates', function (Blueprint $table) {
$table->id();
$table->string('stripe_id')->index();
$table->double('percentage')->index();
$table->timestamps();
});
return $this;
}
protected function updateTeamsTable(): self
{
Schema::table('teams', function (Blueprint $table) {
$table->renameColumn('billing_zip', 'billing_postal_code');
});
Schema::table('teams', function (Blueprint $table) {
$table->string('card_expiration')->nullable();
});
return $this;
}
protected function dropOldColumnsFromUsersTable(): self
{
Schema::table('users', function (Blueprint $table) {
$table->dropColumn([
'current_billing_plan',
'card_brand',
'card_last_four',
'card_country',
'billing_address',
'billing_address_line_2',
'billing_city',
'billing_state',
'billing_zip',
'billing_country',
'vat_id',
'extra_billing_information',
'trial_ends_at',
'last_read_announcements_at',
'announcements_sent',
'referral_token',
'stripe_id',
'phone',
]);
});
return $this;
}
protected function dropOldColumnsFromInvoicesTable(): self
{
Schema::table('invoices', function (Blueprint $table) {
$table->dropColumn('user_id');
});
return $this;
}
}
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Schema;
class MigrateSubscriptionData extends Migration
{
public function up()
{
$this
->migrateSubscriptionData()
->dropOldSubscriptionTables();
}
protected function migrateSubscriptionData(): self
{
DB::table('team_subscriptions')
->orderBy('id')
->each(function (stdClass $teamSubscriptionData) {
$teamSubscriptionData = get_object_vars($teamSubscriptionData);
DB::table('subscriptions')->insert($teamSubscriptionData);
});
DB::table('team_subscription_items')
->orderBy('id')
->each(function (stdClass $teamSubscriptionItemData) {
$teamSubscriptionItemData = get_object_vars($teamSubscriptionItemData);
DB::table('subscriptions_items')->insert($teamSubscriptionItemData);
});
return $this;
}
protected function dropOldSubscriptionTables(): self
{
Schema::drop('team_subscriptions');
Schema::drop('team_subscription_items');
return $this;
}
}
<?php
use App\Domain\Team\Models\User;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Schema;
class MigrateApiTokensData extends Migration
{
public function up()
{
DB::table('api_tokens')
->orderBy('id')
->chunk(100, function (Collection $apiTokens) {
$apiTokens->each(function (stdClass $apiToken) {
$apiToken = get_object_vars($apiToken);
$user = User::find($apiToken['user_id']);
if ($user) {
echo "Did not find a user for API token id `{$apiToken['id']}`";
}
$user->tokens()->create([
'name' => $apiToken['name'],
'token' => hash('sha256', $apiToken['token']),
'abilities' => ['*'],
]);
});
});
Schema::drop('api_tokens');
}
}
@nuernbergerA

This comment has been minimized.

Copy link

@nuernbergerA nuernbergerA commented Feb 25, 2021

DB::table('team_subscriptions')
    ->orderBy('id')
    ->each(function (stdClass $teamSubscriptionData) {
        $teamSubscriptionData = get_object_vars($teamSubscriptionData);

        DB::table('subscriptions')->insert($teamSubscriptionData);
    }, 100);
@nuernbergerA

This comment has been minimized.

Copy link

@nuernbergerA nuernbergerA commented Feb 25, 2021

If you just want to clone a table:

INSERT subscription_items
SELECT * FROM team_subscriptions;
@freekmurze

This comment has been minimized.

Copy link
Owner Author

@freekmurze freekmurze commented Feb 26, 2021

Thanks for the tips!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment