max13/laravel-sqfix

Laravel package fixing SQLite Schema grammar

v1.3.0 2024-03-16 00:33 UTC

This package is auto-updated.

Last update: 2024-10-16 01:52:08 UTC


README

Latest Version on Packagist GitHub Tests Action Status Total Downloads

SQLite has many quirks, some of them are bugs and stayed like this because of legacy code, bugs that became features, etc… See the complete list on their website:

  • ROWIDs: Every table (except explicitely created WITHOUT ROWID) have a 64-bit signed integer column named rowid. This column has the same attributes expected for a PRIMARY KEY (starts at 1, is an INTEGER, is UNIQUE across the table, and is usually incremented by 1 on every insert), and can reuse (in certain condition) previously deleted rowids. Only the exact type (case insensitive) INTEGER PRIMARY KEY will make a column an alias of rowid. Which is recommended in most cases.
  • AUTOINCREMENT: When this keyword is used, it will use a different algorithm for rowid, briefly, an AUTOINCREMENT will make SQLite create an table used to track the increments (sqlite_sequence), rowids are guaranteed to be increasing and never reused. The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

This package replaces the default optimized behavior advised by SQLite and will have no effect on other drivers. Laravel’s ids are automatically created as aliases of rowid, and the ->trueSyntax() modifier will use the "unfixed" SQL grammar.

Installation

You can install the package via composer:

composer require max13/laravel-sqfix

That’s it!

Usage

Schema::create('jobs', function (Blueprint $table) {
    $table->bigIncrements('id');
    // …
});

The normal behavior of Laravel is to define the table jobs with the following statement:

create table "jobs" ("id" integer not null primary key autoincrement)

Which will both create a column named id distinct from rowid with the same properties. Double the work.

This package is making Laravel produce the following table:

create table "jobs" ("id" integer primary key)

Optimisation max.

Testing

composer test

Contributing

Please see CONTRIBUTING for details.

Credits

License

The MIT License (MIT). Please see License File for more information.