Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQLite TEXT data types specialization #565

Closed
billy1624 opened this issue Dec 22, 2022 · 10 comments
Closed

SQLite TEXT data types specialization #565

billy1624 opened this issue Dec 22, 2022 · 10 comments
Assignees

Comments

@billy1624
Copy link
Member

Motivation

Data types such as DATE, DATETIME, UUID... etc. All of them will be written as TEXT data type when creating SQLite column.

match column_type {
ColumnType::Char(length) => match length {
Some(length) => format!("text({})", length),
None => "text".into(),
},
ColumnType::String(length) => match length {
Some(length) => format!("text({})", length),
None => "text".into(),
},
ColumnType::Text => "text".into(),
ColumnType::TinyInteger | ColumnType::TinyUnsigned => "integer".into(),
ColumnType::SmallInteger | ColumnType::SmallUnsigned => "integer".into(),
ColumnType::Integer | ColumnType::Unsigned => "integer".into(),
ColumnType::BigInteger | ColumnType::BigUnsigned => "bigint".into(),
ColumnType::Float => "real".into(),
ColumnType::Double => "real".into(),
ColumnType::Decimal(precision) => match precision {
Some((precision, scale)) => format!("real({}, {})", precision, scale),
None => "real".into(),
},
ColumnType::DateTime => "text".into(),
ColumnType::Timestamp => "text".into(),
ColumnType::TimestampWithTimeZone => "text".into(),
ColumnType::Time => "text".into(),
ColumnType::Date => "text".into(),
ColumnType::Interval(_, _) => "unsupported".into(),
ColumnType::Binary(blob_size) => match blob_size {
BlobSize::Blob(Some(length)) => format!("binary({})", length),
_ => "blob".into(),
},
ColumnType::VarBinary(length) => format!("binary({})", length),
ColumnType::Boolean => "boolean".into(),
ColumnType::Money(precision) => match precision {
Some((precision, scale)) => format!("integer({}, {})", precision, scale),
None => "integer".into(),
},
ColumnType::Json => "text".into(),
ColumnType::JsonBinary => "text".into(),
ColumnType::Uuid => "text(36)".into(),
ColumnType::Custom(iden) => iden.to_string(),
ColumnType::Enum { .. } => "text".into(),
ColumnType::Array(_) => unimplemented!("Array is not available in Sqlite."),
ColumnType::Cidr => unimplemented!("Cidr is not available in Sqlite."),
ColumnType::Inet => unimplemented!("Inet is not available in Sqlite."),
ColumnType::MacAddr => unimplemented!("MacAddr is not available in Sqlite."),
ColumnType::Year(_) => unimplemented!("Year is not available in Sqlite."),
ColumnType::Bit(_) => unimplemented!("Bit is not available in Sqlite."),
ColumnType::VarBit(_) => unimplemented!("VarBit is not available in Sqlite."),
}

This result in a "data lost": where sea-schema failed to discover the original data type of a column. E.g. A DATETIME column will be treated as TEXT.

Proposed Solutions

Given that SQLite is essentially "typeless", we can simply write the specialized TEXT data types directly:

  • ColumnType::DateTime => "datetime"
  • ColumnType::Timestamp => "timestamp"
  • ColumnType::TimestampWithTimeZone => "timestamp"
  • ColumnType::Time => "time"
  • ColumnType::Date => "date"
  • ColumnType::Json => "json"
  • ColumnType::JsonBinary => "json"
  • ColumnType::Uuid => "uuid"
@billy1624
Copy link
Member Author

Should we? @tyt2y3

@Sytten
Copy link
Contributor

Sytten commented Dec 22, 2022

That is essentially what the other orms and query builders do. One thing that would be nice would be to document that so people dont have to read the source code to get the mapping (looking at you diesel 😅)

@ikrivosheev
Copy link
Member

@billy1624 this is a cool idea! But breaking changes. Well, on the other hand we have been talking about this for a long time and I saw similar issues.
If type names are ok I can implement this.

@tyt2y3
Copy link
Member

tyt2y3 commented Dec 23, 2022

+1 on this proposal, and yes it would be nice if we can put a table in our rustdoc.

@billy1624
Copy link
Member Author

I'd prefer adding a doctest to each methods in ColumnDef. A minimal demo the showcase the resulting SQL for each db backend. See #566

image

@billy1624
Copy link
Member Author

Hey @ikrivosheev, yes, please. Your help is greatly appreciated!! You can push commits directly to #566 :)

@Sytten
Copy link
Contributor

Sytten commented Jul 14, 2023

FYI while I was building the diesel integration, I saw that UUID is written as text(36) but no engine stores them as text, they all store them as a Blob:

@tyt2y3
Copy link
Member

tyt2y3 commented Aug 31, 2023

Push. This is an issue we'd like to tackle once and for all.
Noted. That means UUID should actually be text(16).

@jondot
Copy link

jondot commented Nov 29, 2023

We also need this, looks like that generating entities w/sqlite isn't working so not usable (because information gets lost)

@tyt2y3
Copy link
Member

tyt2y3 commented Apr 2, 2024

Closed via #735

@tyt2y3 tyt2y3 closed this as completed Apr 3, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants