Differential D1810 Diff 5089 src/database/migrations/2020_10_18_091319_create_greylist_tables.php

Changeset View

Standalone View

src/database/migrations/2020_10_18_091319_create_greylist_tables.php

This file was added.

				<?php

				use Illuminate\Database\Migrations\Migration;
				use Illuminate\Database\Schema\Blueprint;
				use Illuminate\Support\Facades\Schema;

				// phpcs:ignore
				class CreateGreylistTables extends Migration
				{
				/**
				* Run the migrations.
				*
				* @return void
				*/
				public function up()
				{
				Schema::create(
				'greylist_connect',
				function (Blueprint $table) {
				$table->bigIncrements('id');
				$table->string('sender_local', 256);
				$table->string('sender_domain', 256);
				$table->string('recipient_hash', 64);
				$table->bigInteger('recipient_id')->unsigned()->nullable();
				$table->string('recipient_type', 16)->nullable();
				$table->bigInteger('net_id');
				$table->string('net_type', 16);
				$table->boolean('greylisting')->default(true);
				$table->bigInteger('connect_count')->unsigned()->default(1);
				$table->timestamps();

				/**
				* Index for recipient request.
				*/
				$table->index(
				[
				'sender_local',
				'sender_domain',
				'recipient_hash',
				'net_id',
				'net_type'
				],
				'ssrnn_idx'
				);

				machniakUnsubmitted Done Inline Actions The same columns like in the unique() below, so I think this is redundant. machniak: The same columns like in the unique() below, so I think this is redundant.
				vanmeeuwenAuthorUnsubmitted Done Inline Actions It is not, same as below. vanmeeuwen: It is not, same as below.
				machniakUnsubmitted Not Done Inline Actions They are the same. $table->unique( ['sender_local', 'sender_domain', 'recipient_hash', 'net_id', 'net_type'], 'ssrnn_unq' ); machniak: They are the same. ``` $table->unique( ['sender_local', 'sender_domain'…
				/**
				* Index for domain whitelist query.
				*/
				$table->index(
				[
				machniakUnsubmitted Done Inline Actions This also will be redundant if you re-order columns in the unique index below. machniak: This also will be redundant if you re-order columns in the unique index below.
				vanmeeuwenAuthorUnsubmitted Done Inline Actions A unique constraint may cause an index to be created, but it is a different type of index -- because it can't help you speed up queries by hitting the right index in the right way. vanmeeuwen: A unique constraint may cause an index to be created, but it is a different type of index…
				machniakUnsubmitted Done Inline Actions I'll trust you know better in this case, but I always thought that unique index is used as any other index. Why not? machniak: I'll trust you know better in this case, but I always thought that unique index is used as any…
				vanmeeuwenAuthorUnsubmitted Done Inline Actions A unique index is a constraint that incidentally uses an index in order to speed up the lookup to pass or fail on a unique constraint. For example, a unique constraint could simply exist of a single column using a hash over cells appended to one another. It doesn't concern itself with comparison or range lookups, can completely ignore column types and is as such, when implemented correctly (so as to be most efficient as a unique constrained) in a database technology, it isn't an index you'd likely hit with a query so as to get to a resulting row. vanmeeuwen: A unique index is a constraint that incidentally uses an index in order to speed up the lookup…
				'sender_domain',
				'net_id',
				'net_type'
				],
				'snn_idx'
				);

				/**
				* Index for updated_at
				*/
				$table->index('updated_at');

				$table->unique(
				['sender_local', 'sender_domain', 'recipient_hash', 'net_id', 'net_type'],
				'ssrnn_unq'
				);
				}
				);

				Schema::create(
				'greylist_penpals',
				function (Blueprint $table) {
				$table->bigIncrements('id');
				$table->bigInteger('local_id');
				$table->string('local_type', 16);
				$table->string('remote_local', 128);
				$table->string('remote_domain', 256);
				$table->timestamps();
				}
				);

				Schema::create(
				'greylist_settings',
				function (Blueprint $table) {
				machniakUnsubmitted Done Inline Actions There should be index here. machniak: There should be index here.
				$table->bigIncrements('id');
				$table->bigInteger('object_id');
				$table->string('object_type', 16);
				$table->string('key', 64);
				$table->text('value');
				$table->timestamps();

				$table->index(['object_id', 'object_type', 'key'], 'ook_idx');
				}
				);

				Schema::create(
				'greylist_whitelist',
				function (Blueprint $table) {
				$table->bigIncrements('id');
				$table->string('sender_local', 128)->nullable();
				$table->string('sender_domain', 256);
				$table->bigInteger('net_id');
				$table->string('net_type', 16);
				$table->timestamps();

				$table->index(['sender_local', 'sender_domain', 'net_id', 'net_type'], 'ssnn_idx');
				}
				);
				}

				/**
				* Reverse the migrations.
				*
				* @return void
				*/
				public function down()
				{
				Schema::dropIfExists('greylist_connect');
				Schema::dropIfExists('greylist_penpals');
				Schema::dropIfExists('greylist_settings');
				Schema::dropIfExists('greylist_whitelist');
				}
				}