Determine SQLite type by following affinity rule

[RipleyTom]

I'm currently in the process of migrating a program from sqlite to sea-orm and used sea-orm-codegen to generate the entities from the existing database.

It seems it converted an UNSIGNED SMALLINT NOT NULL to a Vec<u8>

The database table used to be created like this:

        conn.execute(
            "CREATE TABLE IF NOT EXISTS users ( userId INTEGER PRIMARY KEY NOT NULL, username TEXT NOT NULL COLLATE NOCASE, hash BLOB NOT NULL, salt BLOB NOT NULL, online_name TEXT NOT NULL, avatar_url TEXT NOT NULL, email TEXT NOT NULL, email_check TEXT NOT NULL, token TEXT NOT NULL, rst_token TEXT, flags UNSIGNED SMALLINT NOT NULL)",
            [],
        )
        .expect("Failed to create users table!");

resulting users.rs:

//! SeaORM Entity. Generated by sea-orm-codegen 0.8.0

use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "users")]
pub struct Model {
    #[sea_orm(column_name = "userId", primary_key, auto_increment = false)]
    pub user_id: i32,
    pub username: String,
    pub hash: Vec<u8>,
    pub salt: Vec<u8>,
    pub online_name: String,
    pub avatar_url: String,
    pub email: String,
    pub email_check: String,
    pub token: String,
    pub rst_token: Option<String>,
    pub flags: Vec<u8>,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(has_many = "super::dates::Entity")]
    Dates,
}

impl Related<super::dates::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::Dates.def()
    }
}

impl ActiveModelBehavior for ActiveModel {}

I'd expect pub flags: u16

[billy1624]

Hey @RipleyTom, thanks for the questions! From what I understand SQLite does not support unsigned integer. It will be treated as a normal integer column in SQLite. https://www.sqlite.org/datatype3.html#affinity_name_examples

[RipleyTom]

I'm aware of this, every integer type in sqlite is really an INTEGER behind the hood(note that it's still a valid type in sqlite) but that still doesn't explained how a UNSIGNED SMALLINT(ie an INTEGER really) ended up as Vec\<u8> in codegen(at worst it should be an i32).

[billy1624]

It end up being Vec<u8> because UNSIGNED SMALLINT isn't included in the list:

• https://github.com/SeaQL/sea-schema/blob/00429f3bdbfe56af1afcee94b069a9619cea4263/src/sqlite/def/types.rs#L43-L77

[RipleyTom]

Hmm I'm even more confused now, why is there an UnsignedBigInt, Int2, Int8, etc types in there? Those are not sqlite types since there is only INTEGER.

[billy1624]

Hmm I'm even more confused now, why is there an UnsignedBigInt, Int2, Int8, etc types in there? Those are not sqlite types since there is only INTEGER.

All these types are on the list because they are mentioned on the page https://www.sqlite.org/datatype3.html#affinity_name_examples.

[billy1624]

I don't think there is a good way to parse arbitrary types, e.g. UNSIGNED SMALLINT, in SQLite. I would suggest you to define it as INTEGER datatype

[RipleyTom]

Yeah but like the table says, those are examples: This table shows only a small subset of the datatype names that SQLite will accept.. The actual types are, anything with INT in it => INTEGER(which in sqlite type is a variable integer type from 0 to 8 bytes). https://www.sqlite.org/datatype3.html#storage_classes_and_datatypes actually describes the only 5 actual types sqlite handles internally. The way it is done atm seems very inconsistent to me.

[billy1624]

Okay, I agree we can update the logic and search if INT exists in the datatype string: it's INTEGER column if we found INT in it.

[tyt2y3]

Summary: right now the database to type mapping is word-by-word. Ideally we should follow SQLite's rules in resolving types. This is a big task, because the implementation should have plenty of test cases verified against SQLite. Contribution is welcomed.