large composite key table

[billy1624]

Discussed in #1293

^{Originally posted by meowser December 9, 2022}
I have a table I am working with which has a very large composite primary key. When I auto-generate a model using sea-orm-cli the created model causes the following error:

the trait `ValueType` is not implemented for `(std::string::String, std::string::String, std::string::String, std::string::String, std::string::String, std::string::String, std::string::String)

Here is an example of the model:

use sea_orm::entity::prelude::*;                                                                                                                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                                                                                             
#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq)]                                                                                                                                                                                                                                                                                                                    
#[sea_orm(table_name = "example")]                                                                                                                                                                                                                                                                                                                                           
pub struct Model {                                                                                                                                                                                                                                                                                                                                                           
    #[sea_orm(primary_key, auto_increment = false)]                                                                                                                                                                                                                                                                                                                          
    pub a: String,                                                                                                                                                                                                                                                                                                                                                           
    #[sea_orm(primary_key, auto_increment = false)]                                                                                                                                                                                                                                                                                                                          
    pub b: String,                                                                                                                                                                                                                                                                                                                                                           
    #[sea_orm(primary_key, auto_increment = false)]                                                                                                                                                                                                                                                                                                                          
    pub c: String,                                                                                                                                                                                                                                                                                                                                                           
    #[sea_orm(primary_key, auto_increment = false)]                                                                                                                                                                                                                                                                                                                          
    pub d: String,                                                                                                                                                                                                                                                                                                                                                           
    #[sea_orm(primary_key, auto_increment = false)]                                                                                                                                                                                                                                                                                                                          
    pub e: String,                                                                                                                                                                                                                                                                                                                                                           
    #[sea_orm(primary_key, auto_increment = false)]                                                                                                                                                                                                                                                                                                                          
    pub f: String,                                                                                                                                                                                                                                                                                                                                                           
    #[sea_orm(primary_key, auto_increment = false)]                                                                                                                                                                                                                                                                                                                          
    pub g: String,                                                                                                                                                                                                                                                                                                                                                           
    pub h: String,                                                                                                                                                                                                                                                                                                                                                           
}                                                                                                                                                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                                                                                             
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]                                                                                                                                                                                                                                                                                                                      
pub enum Relation {}                                                                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                                                                             
impl ActiveModelBehavior for ActiveModel {}   

I notice if I take out one of the primary key attributes my code will compile, so I believe there is an six column primary key limit. I was wondering what would be the best approach for dealing with tables like this since I have quite a few tables with long composite keys, which I cannot change?

[billy1624]

Hey @meowser, thanks for asking! Yes, the hard limit is 6 composite primary key.

https://github.com/SeaQL/sea-query/blob/83f548472894453afa6c8a5ab780a376a10bc710/src/value.rs#L246-L254

As a temporary solution, you can pick up to 6 columns to be the composite primary key. And leave other as ordinary columns.

I'll create a PR to bump the limit up to 10 this afternoon. Then, you can point your sea-orm to a git dependency if you don't mind.

[billy1624]

Somewhat related:

• Joins only support up to 3 composite keys #1182

[meowser]

Hey @meowser, thanks for asking! Yes, the hard limit is 6 composite primary key.

https://github.com/SeaQL/sea-query/blob/83f548472894453afa6c8a5ab780a376a10bc710/src/value.rs#L246-L254

As a temporary solution, you can pick up to 6 columns to be the composite primary key. And leave other as ordinary columns.

I'll create a PR to bump the limit up to 10 this afternoon. Then, you can point your sea-orm to a git dependency if you don't mind.

thanks!

[billy1624]

Hey @tyt2y3, I want your opinion on this.

Currently, we have two similar list constructed in Enum:

• sea_query::ValueTuple
• sea_orm::Identity

The Dataflow

• Input: tuple i.e. (a, b, c)
• Storage: Enum with various size represented in Enum variants
• Retrieval: tuple or Vec

The Concern

As the size of tuple increased, the Enum implementation waste more memory space. Because the memory size of a Enum is determined by its largest variant.

pub enum ValueTuple {
    One(Value),
    Two(Value, Value),
    Three(Value, Value, Value),
    Four(Value, Value, Value, Value),
    Five(Value, Value, Value, Value, Value),
    Six(Value, Value, Value, Value, Value, Value),
    Seven(Value, Value, Value, Value, Value, Value, Value),
    Eight(Value, Value, Value, Value, Value, Value, Value, Value),
    Nine(Value, Value, Value, Value, Value, Value, Value, Value, Value),
    Ten(Value, Value, Value, Value, Value, Value, Value, Value, Value, Value),
}

The memory needed to store ValueTuple::One is the same as ValueTuple::Ten.

The Proposal

We can swap the storage from Enum to Vec, this will greatly reduce memory waste. The conversion methods will be kept but to convert input tuple into Vec instead of Enum. We can still check the arity by calling Vec::len().

However, it will open a hole where an empty Vec is a potential. But I think normal user who instantiate through tuple won't fall into this.

[JonasCir]

Just a random thought: is it possible to add feature configurations to enum variants? Such that 7-10 is only compiled conditionally if you ask for it?

[billy1624]

Just a random thought: is it possible to add feature configurations to enum variants? Such that 7-10 is only compiled conditionally if you ask for it?

Still, this isn't attacking the fundamental problem of it. And I think it's not a good use of feature hahaa.

[JonasCir]

True :D

[tyt2y3]

Yeah it started from a tuple of three which I think is the ideal size.

I don't want basic usage to be penalized, so potentially it can look like:

enum ValueTuple {
    One(Value),
    Two(Value, Value),
    Three(Value, Value, Value),
    Many(Vec<Value>),
}