Sqlite3 databases are created expecting a PK, and aren't falling back to the automatic ROWID or generating a WITHOUT ROWID table

[TapGhoul]

Description

When creating a table via SeaORM's migration system, you can create tables without primary keys, but not then generate entities and expect them to work. This is known about in #485 and #2141 - however, in SQLite's case, there's missing info here.

SQLite doesn't need a primary key, because all tables - unless explicitly disabled - automatically gain an internal rowid column which can be accessed and even updated by hitting the column names rowid, oid or _rowid_ (unless those names are used by an existing column).

It would be good to pick a path here - either enforce rowid-less table by adding WITHOUT ROWID to tables, or have the entity generator automatically detect this case and pull out the rowid column.

Note, there's a couple caveats to this. The rowid is not durable - during a VACUUM this ID can change. Additionally, when something is declared as INTEGER PRIMARY KEY (no other primary key type works here) rowid becomes an alias to the explicit key. The general design of the rowid column is apparently a design mistake according to official docs 💀 - but accesses to it are extremely fast as it's used as the direct key for the internal btree - not the case for other types of primary keys.

I propose that either the WITHOUT ROWID is added (actually not my preference) or you enforce somewhere in the query builder that a PK - integer or otherwise - is required to be created.

More information can be found here:
https://www.sqlite.org/rowidtable.html

Steps to Reproduce

1. sqlite3 database.db
2. CREATE TABLE blah (value);
3. SELECT rowid, value FROM blah;
4. Generate entities
5. Oops, won't format/build

Expected Behavior

Either a ROWID key is created in the generator, or prevent sea-orm-migration from generating a table without a primary key on SQLite.

Actual Behavior

Code is generated:

impl PrimaryKeyTrait for PrimaryKey { type ValueType = ; fn auto_increment () -> bool { false } }

Reproduces How Often

Every time

Workarounds

Don't create tables without explicit PKs, or utilize the solutions mentioned in #485 (comment) (not feasible for complex DBs)

Reproducible Example

See steps to reproduce (can also be done via a migration)

Versions

OS: Linux
Arch: AMD64
Database: SQLite,

libsqlite3-sys: 0.30.1
Bundled SQLite version: 3.46.0
Bundled SQLite source ID: 2024-05-23 13:25:27 96c92aba00c8375bc32fafcdf12429c58bd8aabfcadab6683e35bbb9cdebf19e

sea-orm: 1.1.1
sea-orm-macros: 1.1.1
sea-bae: 0.2.1
sea-query: 0.32.0
sea-orm-migration: 1.1.1
sea-orm-cli: 1.1.1
sea-schema: 0.16.0
sea-schema-derive: 0.3.0