Level 7
I switched to using a testing database so it's identical and it works.
If you have the answer to the original question feel free to post it and I will mark it as best for history purposes.
Jan 25, 2020
5
Level 7
SQLite and foreign cascade issues with unit tests
My Unit test fails with this
Failed asserting that a row in the table [pages] does not match the attributes {
"page": 1
}.
Correct, it is in there. Now if I delete the Book which has this hasMany from my dev environment it works fine. migration:
Schema::create('books', function (Blueprint $table)
{
$table->bigIncrements('id');
$table->string('name');
$table->timestamps();
});
Schema::create('pages', function (Blueprint $table)
{
$table->bigIncrements('id');
$table->unsignedBigInteger('book_id');
$table->text('content');
$table->integer('page');
$table->timestamps();
});
Schema::table('pages', function (Blueprint $table) {
$table->engine = 'InnoDB';
$table->foreign('book_id')->references('id')->on('books')->onDelete('cascade');
});
It works fine from my dev environment using mariadb, the unit test using sqlite fails. Why is this?
I have this too
'sqlite' => [
'driver' => 'sqlite',
'url' => env('DATABASE_URL'),
'database' => env('DB_DATABASE', database_path('database.sqlite')),
'prefix' => '',
'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
],
Level 61
Not sure what you have in tests but this is bit weird since SQLite does not have InnoDB engine
$table->engine = 'InnoDB';
Post your test here so it can give better insight into what the issue might be.
Level 7
@bugsysha It didn't cascade without it and it was the only solution I found that fixed it (mariadb)
Level 61
As far as I know foreign key support is not enabled in SQLite by default.
Use some client app and connect to that SQLite database and run following:
PRAGMA foreign_keys = ON;
Then from that client app delete some record and see if cascade is propagated and correct records are deleted.
If so then see for the tests if it works every time. If not then add somewhere PRAGMA foreign_keys = ON; to run for every test.
This is a great situation which shows why you should not depend on the database to handle those scenarios.
Level 7
@bugsysha Not switching back now that I'm up and running on Mariadb which I think is better (same DB for tests as for production)
Here was the test
/** @test */
public function canDeleteBook()
{
$this->withoutExceptionHandling();
$this->be(\App\Models\User::find(1));
$attributes = [
'name' => 'A good book',
'content' => $this->faker->text
];
$book = Book::create($attributes);
$page_attributes = [
'page' => 1,
'content' => $this->faker->text
];
$book->pages()->create($page_attributes);
$response = $this->delete(route('admin.books.destroy', $book));
$response->assertRedirect(route('admin.index'));
$this->assertDatabaseMissing('books', ['name' => $attributes['name'], 'content' => $attributes['content']]);
$this->assertDatabaseMissing('pages', ['page' => $page_attributes['page'], 'content' => $page_attributes['content']]);
}
I have it enabled as mentioned in the first post.
'sqlite' => [
'driver' => 'sqlite',
'url' => env('DATABASE_URL'),
'database' => env('DB_DATABASE', database_path('database.sqlite')),
'prefix' => '',
'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true), // is enabled here
],
This is a great situation which shows why you should not depend on the database to handle those scenarios.
I just learned that using the database for this is great instead of adding it to the boot of the model, so what do you suggest? What is the best here?
Please or to participate in this conversation.