seaql / sea-schema Goto Github PK

View Code? Open in Web Editor NEW

174.0 174.0 37.0 3.75 MB

🌿 SQL schema definition and discovery

Home Page: https://docs.rs/sea-schema

License: Apache License 2.0

Rust 99.77% Shell 0.23%

database database-schema mysql postgresql rust sql sqlite

sea-schema's People

Contributors

Stargazers

Watchers

sea-schema's Issues

Discovering Postgres enum

So to support SeaQL/sea-orm#252

We need to properly discover Postgres enums from a given schema (MySQL enum is done already)

For example, given a schema:

sea-schema/tests/sakila/postgres/sakila-schema.sql

Lines 64 to 70 in e79afd4

    
           CREATE TYPE mpaa_rating AS ENUM ( 
        
               'G', 
        
               'PG', 
        
               'PG-13', 
        
               'R', 
        
               'NC-17' 
        
           );

We want to query the enum definition from information_schema and then output a TypeCreateStatement in the end:

Type::create()
    .as_enum(Alias::new("mpaa_rating"))
    .values(vec![
        "G",
        "PG",
        "PG-13",
        "R",
        "NC-17",
    ])

https://github.com/SeaQL/sea-query/blob/HEAD/src/extension/postgres/types.rs

We may need to add new data structures to the def module too.

Once we got the writer done, we can transform that in SeaORM inside the codegen.

parse unsigned number to signed value

i use MySql, a column with type bigint(20) unsigned, but use sea-orm-cli generate it will generate i64 rust type, i no idea where has issue, anyone to fix it?

Re-architect sea-orm depedency

Okay so the culprit is that sea-orm and sea-schema each has a dependency on sea-query, currently stuck in a deadlock, and there is no way to upgrade one side without breaking the other side.

We definitely have to re-architect this before releasing. The problem is the API exposed sea-query symbols and that tied things together. Ideally, sea-schema should be able to use a different sea-query version internally from sea-orm without causing trouble.

The migrator should use the sea-query pubed by sea-orm, or ideally, not using the high level sea-query interface at all.

Originally posted by @tyt2y3 in #26 (comment)

Unhandled panics when detecting Postgres table schemas

Description

We're using sea-schema to detect the schema of Postgres tables. We've noticed that in some cases, panics occur from within sea-schema and there are no ways for us to handle them.

One of the panics that we've noticed seems to result from network blips between the service and the DB:

Ssl(ErrorStack([Error { code: 503841036, library: "DECODER routines", function: "OSSL_DECODER_from_bio", reason: "unsupported", file: "../crypto/encode_decode/decoder_lib.c", line: 101, data: "No supported data to decode. Input type: PEM" }, Error { code: 503841036, library: "DECODER routines", function: "OSSL_DECODER_from_bio", reason: "unsupported", file: "../crypto/encode_decode/decoder_lib.c", line: 101, data: "No supported data to decode. Input type: PEM" }])))

It seems that these errors are all coming from sea_schema::postgres::discovery::executor::real::Executor::fetch_all. Looking at the code, it's clear that no error handling is attempted. .unwrap().s are present in many places and errors aren't exposed to the user for handling.

This makes it difficult to use sea-query for production applications. Ideally, these query-level errors would returned back the user and the schema detection operations would all return Results.

Steps to Reproduce

Run a ton of schema detections, possibly with an unstable network.

Expected Behavior

DB errors encountered while detecting Postgres table schemas are bubbled up to the user

Actual Behavior

Query errors result in panics within the sea-schema library

Reproduces How Often

Rarely since it seems to depend on network issues or some other spurious condition

Versions

Using sea-schema v0.10.3

Add support for custom where predicate and index type

Motivation

Creating a partial index is impossible right now, but adding support for both index type and where predicate would help
creating an index like this:

CREATE UNIQUE INDEX cake_filling_id_idx ON public.cakes USING btree (filling_id) WHERE (filling_id IS NOT NULL);

sea-orm-cli generate entity -> Unknown column 'generation_expression' error

Description

sea-orm-cli generate entity
-u mysql://sea:sea@localhost/bakery
-o entity/src

SELECT
column_name,
column_type,
is_nullable,
column_key,
column_default,
extra,
generation_expression,
column_comment
FROM
information_schema.columns
WHERE
table_schema = ?
AND table_name = ?
ORDER BY
ordinal_position ASC

thread 'main' panicked at 'called Result::unwrap() on an Err value: Database(MySqlDatabaseError { code: Some("42S22"), number: 1054, message: "Unknown column 'generation_expression' in 'field list'" })', F:\cargo\registry\src\github.com-1ecc6299db9ec823\sea-schema-0.8.0\src\mysql\discovery\executor\real.rs:32:14
note: run with RUST_BACKTRACE=1 environment variable to display a backtrace

Steps to Reproduce

sea-orm-cli generate entity
-u mysql://sea:sea@localhost/bakery
-o entity/src
-v

Expected Behavior

Actual Behavior

Reproduces How Often

always

Versions

mysql 5.6

Additional Information

Support SQLite foreign key short hand syntax

I use command sea-orm-cli generate entity -o entity/src/ --with-serde -t host_site

Adapter is SQLite

My host_site table DDL:

create table host_site
(
    host_id integer not null
        references host,
    site_id integer not null
        references site
);

create unique index host_site_host_id_site_id_uindex
    on host_site (host_id, site_id);

And I have error:
thread 'main' panicked at 'Ident is not allowed to be empty; use Option', /Users/averichev/.cargo/registry/src/github.com-1ecc6299db9ec823/proc-macro2-1.0.36/src/fallback.rs:686:9
note: run with RUST_BACKTRACE=1 environment variable to display a backtrace

Need help, please

Schema Manager

To support SeaORM, we want to develop SeaSchema into a fully-featured schema manager.

Functionalities:

A cli client (to be wrapped by sea-orm-cli as a sub command)
Migration manager
Migration codegen
Schema manager

Migration Manager

The migration manager would work like sqlx-cli migration, keeping track of executed migrations.
Our private table would be named seaql_migrations.

The migration 'script' would look like:

use sea_schema::*;

#[derive(Migrator)]
pub struct M20210101020202CreateGlyphTable;

#[derive(Iden)]
pub enum Glyph {
    Table,
    Id,
    Image,
    Aspect,
}

#[async_trait]
impl MigratorTrait for M20210101020202CreateGlyphTable {
    async fn up(mg: &SchemaManager) -> Result<(), DbErr> {
        mg.create_table(
            // TableCreateStatement
            Table::create()
                .table(Glyph::Table)
                .col(
                    ColumnDef::new(Glyph::Id)
                        .integer_len(11)
                        .not_null()
                        .auto_increment()
                        .primary_key()
                )
                .col(ColumnDef::new(Glyph::Aspect).double().not_null())
                .col(ColumnDef::new(Glyph::Image).text())
        ).await?;

        Ok(())
    }

    async fn down(mg: &SchemaManager) -> Result<(), DbErr> {
        mg.drop_table(Glyph::Table).await?;

        Ok(())
    }
}

Migration Codegen

The migration should scaffold a rust project with the following structure:

\migration
    Cargo.toml
    \src
        m20210101020202_create_glyph_table.rs
        lib.rs
        main.rs

The project would serve two functions: as an executable to be executed:

cd migration
cargo run -- run

On the other hand, it can also be used as a library, to be required by the web server when deployed:

use migration::Migrator;

async fn main() {
    Migrator::run().await;
    WebServer::start().await;
}

Schema Manager

The SchemaManager will provide 3 categories of APIs:

Definition: create_table, create_index, create_foreign_key etc
Mutation: add_column, rename_column etc
Inspection: has_table, has_column etc

Dump SeaORM Dependency from SeaSchema's Migrator

Motivation

A dependency tree of a typical SeaORM application looks like this. Which SeaORM is depends by the application and the underlying SeaSchema migrator. This is hard to maintain and difficult to release new version of SeaORM.

axum-graphql v0.1.0 (sea-orm/examples/axum-graphql_example)
├── entity v0.1.0 (sea-orm/examples/axum-graphql_example/entity)
│   ├── sea-orm v0.7.1
│   │   ├── sea-orm-macros v0.7.0 (proc-macro)
│   │   ├── sea-query v0.23.0
│   │   │   ├── sea-query-derive v0.2.0 (proc-macro)
│   │   ├── sea-strum v0.23.0
│   │   │   └── sea-strum_macros v0.23.0 (proc-macro)
├── migration v0.1.0 (sea-orm/examples/axum-graphql_example/migration)
│   ├── entity v0.1.0 (sea-orm/examples/axum-graphql_example/entity) (*)
│   └── sea-schema v0.7.1
│       ├── sea-orm v0.7.1 (*)
│       ├── sea-query v0.22.0
│       │   └── sea-query-derive v0.2.0 (proc-macro) (*)
│       ├── sea-schema-derive v0.1.0 (proc-macro)

Solution

We could get rid of SeaORM dependency on SeaSchema's migrator, and manage the connection to various db backend with sqlx::Any. sqxl::Pool::any_kind method can be used to identify the db backend of current connection.

Support for VIEWS

Summary

After add support for VIEWS in sea-query, add support here

Additional Information

sea-query issues: SeaQL/sea-query#179

Upgrade sqlx to 0.6

I don't think there is anything needed to do here. But sea-query has to modify the driver support

MSRV

Description

Add MSRV to Cargo.toml

Link: SeaQL/sea-query#535 (review)

Error generating entity fromSQLite database with decimal type

Description

I encountered an issue while attempting to generate an entity from a Django site's SQLite database using the sea-orm-cli. It seems to fail when dealing with the decimal type.

Steps to Reproduce

Create a SQLite table as follows:

CREATE TABLE django_apscheduler_djangojobexecution
(
    id        INTEGER      NOT NULL PRIMARY KEY AUTOINCREMENT,
    status    VARCHAR(50)  NOT NULL,
    run_time  DATETIME     NOT NULL,
    duration  DECIMAL,
    finished  DECIMAL,
    exception VARCHAR(1000),
    traceback TEXT,
    job_id    VARCHAR(255) NOT NULL,
    CONSTRAINT unique_job_executions UNIQUE (job_id, run_time)
);

Execute sea-orm-cli generate entity -v

2024-01-30T11:15:00.907209Z DEBUG sqlx::query: summary="PRAGMA foreign_key_list('django_apscheduler_djangojobexecution')" db.statement="" rows_affected=0 rows_returned=1 elapsed=70.6µs
2024-01-30T11:15:00.907468Z DEBUG sqlx::query: summary="PRAGMA table_info('django_apscheduler_djangojobexecution')" db.statement="" rows_affected=0 rows_returned=8 elapsed=97.6µs
thread 'main' panicked at C:\Users\rustdev\.cargo\registry\src\index.crates.io-6f17d22bba15001f\sea-schema-0.14.2\src\sqlite\def\types.rs:62:42:
index out of bounds: the len is 1 but the index is 1
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

Error occurs at sea-schema/types.rs:62.

Expected Behavior

Actual Behavior

Reproduces How Often

Always reproducible.

Versions

sea-orm-cli: 0.12.12

Additional Information

Generating entity with sea-orm-cli panics when foreign key was added on table on postgres

Description

Generating entity with sea-orm-cli panics when the foreign key is added to the table on postgres.

Steps to Reproduce

Run this query to create the tables

create table "user"
(
    user_id       serial
        primary key,
    username      text                     not null
);
create table chat_channel_user
(
    user_id      integer              not null
        constraint chat_channel_user_user_user_id_fk
            references "user",
    channel_id   integer              not null,
    hidden       boolean default true not null,
    primary key (user_id, channel_id)
);

run sea-orm-cli generate entity
see the panics.

Expected Behavior

Should not crash and generate a schema.

Actual Behavior

It panics. I've checked with (tables without foreign keys) and it ran successfully.

Reproduces How Often

I'm guessing that this problem happens every time when foreign key is added.

Versions

OS: Windows 11 (x64)
PostgreSQL Version: PostgreSQL 15.3, compiled by Visual C++ build 1914, 64-bit
sea-orm-cli: 0.12.3

Additional Information

The code where it crashes:

sea-schema/src/postgres/parser/table_constraints.rs

Line 52 in bdc6f5e

let table = result.referential_key_table_name.unwrap();
The logs that ran sea-orm-cli generate entity --verbose: log.txt

Parse Postgres `citext` column type as string

Motivation

citext data type in Postgres is similar to string but it's case-insensitive. We can treat it just like string.

Proposed Solutions

Parse citext column as ~~sea_query::ColumnType::String(None)~~ sea_query::ColumnType::Custom("citext")

Related Discussion

SeaQL/seaography#104

`sea-schema-derive` relies on obsolete `syn` v1

The vast majority of syn dependencies are on v2 while this crate continues to rely on v1.

This leaves this the only crate in my dependency list that still requires v1.

See sea-schema-derive's Cargo.toml for the line which includes the dependency.

Mapping db column type to Rust type

A trait to map ColumnType to Rust type in String.

To support entity generator in SeaQL/sea-orm#11

Improve ColumnType output from MySQL's writer

SeaQL/sea-orm#11

MySQL ColumnInfo

pub struct ColumnInfo {
    /// Default value expression for this column, if any
    pub default: Option<ColumnDefault>,
    /// The generation expression if this is a generated column 
    pub expression: Option<String>,
}

Right now the default and expression fields are inconsistent. We'd better also make a separate struct ColumnExpression

How to define column with `DEFAULT CURRENT_TIMESTAMP`?

manager
.create_table(
    sea_query::Table::create()
        .table(scene::Entity)
        .if_not_exists()
        .col(
            ColumnDef::new(scene::Column::Id)
                .integer()
                .not_null()
                .auto_increment()
                .primary_key(),
        )
        .col(
            ColumnDef::new(scene::Column::CreatedAt)
                .date_time()
                .not_null()
                .default("CURRENT_TIMESTAMP"),
        )
        .col(
            ColumnDef::new(scene::Column::UpdatedAt)
                .date_time()
                .not_null()
                .default("CURRENT_TIMESTAMP"),
        )
        .to_owned(),
)

default("CURRENT_TIMESTAMP") can't work.

SQLite Schema Discovery

I found a SQLite version of Sakila schema for testing

https://github.com/ivanceras/sakila/tree/master/sqlite-sakila-db

Discovery

Getting column info: https://www.sqlite.org/pragma.html#pragma_table_info
Getting foreign key info: https://www.sqlite.org/pragma.html#pragma_foreign_key_list
Getting index & primary key info: https://www.sqlite.org/pragma.html#pragma_index_list

Parser

Parse the above result into schema definition structs. Including information like

table name
columns
- name
- data type
  - length (if any)
- default value
- nullable
- primary key
- auto increment
foreign key constraints

Writer

Converting the discovered & parsed table info into sea_query::TableCreateStatement.

postgres timestamp mapping error: SQL type `TIMESTAMPTZ`) is not compatible with SQL type `TIMESTAMP`

Description

I want use postgres timestamp without time zone type in sea-orm

Steps to Reproduce

(in postgres create statement)created_at timestamp without time zone default current_timestamp not null
sea-orm-cli generate entity -o src/tables_codegen
Orders::find().one(&db).await.unwrap().unwrap();

Expected Behavior

The generate code created_at field is

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "orders")]
pub struct Model {
    pub created_at: DateTime,
    ...
}

Actual Behavior

thread 'tokio-runtime-worker' panicked at 'called `Result::unwrap()` on an `Err` value: Query("error occurred while decoding column \"paid_at\": mismatched types; Rust type `core::option::Option<chrono::datetime::DateTime<chrono::offset::utc::Utc>>` (as SQL type `TIMESTAMPTZ`) is not compatible with SQL type `TIMESTAMP`")', src/handler/order.rs:17:56

The generate code created_at field is DateTimeUtc type not DateTime (without time zone)

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "orders")]
pub struct Model {
    pub created_at: DateTimeUtc,
    ...
}

Reproduces How Often

always

Versions

├── sea-orm v0.6.0
│   ├── sea-orm-macros v0.6.0 (proc-macro)
│   ├── sea-query v0.21.0
│   │   ├── sea-query-derive v0.2.0 (proc-macro)
│   ├── sea-strum v0.23.0
│   │   └── sea-strum_macros v0.23.0 (proc-macro)

psql --version
psql (PostgreSQL) 13.6

Linux ww 5.10.102-1-MANJARO #1 SMP PREEMPT Wed Feb 23 14:44:40 UTC 2022 x86_64 GNU/Linux

Additional Information

postgres orders table schema

psql (13.6, server 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "w" as user "postgres".
w=# \d orders
                                Table "public.orders"
   Column   |            Type             | Collation | Nullable |      Default      
------------+-----------------------------+-----------+----------+-------------------
 team_id    | bigint                      |           | not null | 
 product_id | bigint                      |           | not null | 
 user_id    | bigint                      |           | not null | 
 service_id | bigint                      |           | not null | 
 status     | order_status                |           | not null | 
 type_      | order_type                  |           | not null | 
 paid_at    | timestamp without time zone |           | not null | 
 price      | integer                     |           | not null | 
 id         | bigint                      |           | not null | 
 created_at | timestamp without time zone |           | not null | CURRENT_TIMESTAMP

`referential_key_table_name` empty for some schemas (panic on Option::unwrap on None during migration) since sea-schema 0.14.2

Hi everyone, thanks so much for your great work on the entire SeaQL project ❤️

Description

This week we found an issue where sea-orm-cli generate entity fails with a panic in sea-schema's table_constraints.rs here when it tries to unwrap the Option result.referential_key_table_name that is None in our case.

This seems to be the same error behavior as described in SeaQL/sea-orm#2131.

Steps to Reproduce

Have a situation where one table T1 has a foreign key (FK) that references a column C in another table T2, where there is a unique index on C but not a unique key (which postgres lets you do, see this SO answer)
Run sea-orm-cli generate entity

Expected Behavior

The entities should be generated according to the schema.

If there is a problem with the schema, an informative error message should be printed.

Actual Behavior

The tool panics without any useful error message.

Reproduces How Often

Always (with our schema)

Versions

We are using postgres 14.5.

This issue occurs for us when sea-orm-cli is using [email protected] and does not occur when sea-orm-cli is using [email protected].

We first noticed this after one of our team members recently installed the latest version of sea-orm-cli (0.12.15) where this issue occurred, while on another person's machine it was still working with an older installation of [email protected]. When that person upgraded to latest, they also experienced the error.

We also noticed that when you install an older version of sea-orm-cli with only the version selector (e.g. cargo install [email protected]), Cargo will still use the newest dependencies that match the tool's dependencies spec and will not use the versions that it was published with.

Since sea-orm-cli used the dependency sea-schema = { version = "0.14.0" } (see here) up to 0.12.11, the newer patch version 0.14.2 of sea-schema still matches that version specifier and gets used.

To install an older sea-orm-cli with an older sea-schema for comparative testing, you can use the --locked switch to make Cargo use the versions used at time of publishing, e.g. cargo install --locked [email protected].

Additional Information

Here's a shell script that creates a postgres docker container, creates two tables with the unique/FK combination that causes the problem, and then runs sea-orm-cli generate entity on it to trigger the issue.

You need to have docker and [email protected] on the PATH for this to work.

https://gist.github.com/Patrick-Lehner-Woven-by-Toyota/811668880cf78a1bc78c679993ce137d

Unknown column 'user_defined_type_schema' error (CockroachDB)

Description

CockroachDB is not supported by sea-schema, because it queries columns from the table information schema which don't exist in CockroachDB.

Steps to Reproduce

Run an instance of CockroachDB. For how to install, check the docs.
Create some random tables.
Run sea-orm-cli generate entity
See the error:

thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: Database(PgDatabaseError { severity: Error, code: "42703", message: "column \"user_defined_type_schema\" does not exist", detail: None, hint: None, position: None, where: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("column_resolver.go"), line: Some(196), routine: Some("NewUndefinedColumnError") })', /home/lucas/.cargo/registry/src/github.com-1ecc6299db9ec823/sea-schema-0.9.3/src/postgres/discovery/executor/real.rs:32:14
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

Expected Behavior

It should generate entities like normal and not throw an error.
Using PostgreSQL instead of CockroachDB does not throw an error, but this is not a solution.

Actual Behavior

An error is thrown because CockroachDB has a different information schema.

Reproduces How Often

Every time sea-orm-cli generate entity is ran.

Versions

OS: WSL 2 Ubuntu
DB: CockroachDB

│   ├── sea-orm-migration v0.9.1
│   │   ├── sea-orm v0.9.1
│   │   │   ├── sea-orm-macros v0.9.1 (proc-macro)
│   │   │   ├── sea-query v0.26.2
│   │   │   │   ├── sea-query-derive v0.2.0 (proc-macro)
│   │   │   │   ├── sea-query-driver v0.2.0 (proc-macro)
│   │   │   ├── sea-strum v0.23.0
│   │   │   │   └── sea-strum_macros v0.23.0 (proc-macro)
│   │   ├── sea-orm-cli v0.9.1
│   │   │   ├── sea-schema v0.9.3
│   │   │   │   ├── sea-query v0.26.2 (*)
│   │   │   │   └── sea-schema-derive v0.1.0 (proc-macro)
│   │   ├── sea-schema v0.9.3 (*)
├── sea-orm v0.9.1 (*)

Additional Information

billy1624 on the Discord server:

Actually, we didn't test generate entity on CockroachDB. From what I found online CockroachDB is somewhat compatible with PostgreSQL, but its information schema is different:

https://www.cockroachlabs.com/docs/v22.1/information-schema.html#tables

https://www.postgresql.org/docs/13/infoschema-tables.html

I don't think the table information schema of CockroachDB has user_defined_type_schema and user_defined_type_name column:

sea-schema/src/postgres/query/table.rs

Lines 43 to 44 in 8548898

TablesFields::UserDefinedTypeSchema,

TablesFields::UserDefinedTypeName,

sqlite ForeignKeysInfo pub access struct fields

Description

I want to generate a relationship graph from an sqlite database using the following code

use std::collections::HashMap;
use sea_schema::sqlite::Schema;

let schema: Schema = ...;

let tables_relations_hash_map: HashMap<String, Vec<String>> = schema
    .tables
    .iter()
    .fold(HashMap::new(), |mut acc, table| {
        if !acc.contains_key(&table.name) {
            acc.insert(table.name, Vec::new()).unwrap();
        }
        for fk in table.foreign_keys.iter() {
            if !acc.contains_key(&fk.table) {
                acc.insert(fk.table, Vec::new()).unwrap();
            }
            acc.get_mut(&fk.table).insert(table.name);
        }
        acc
    });

but fields in sqlite ForeignKeysInfo are pub (crate) and I cannot access them.

Does this serve a purpose ? Can I make an MR that fixes the issue ?

Steps to Reproduce

Run code snippet using a generated schema

Expected Behavior

Be able to access ForeignKeysInfo struct fields

Actual Behavior

ForeignKeysInfo struct fields are pub (crate) access

Versions

version = "0.7.1"

Postgres support

This issue is to track the status of Postgres support:

Version bump of sea-orm required for compatibility with 0.7.0

Description

Out of date sea-orm dependency is causing an error involving the Iden trait.

Steps to Reproduce

Use sea-orm 0.7.0 in an entity crate and sea-schema 0.6.0 in a migration crate
Attempt to write a migration, referencing an entity from the aforementioned entity crate

Expected Behavior

The compiler shouldn't throw an error.

Actual Behavior

The compiler reports an incompatibility between sea-orm's Iden and the one that sea-schema knows:

the trait bound `entity::preset::Entity: sea_schema::migration::prelude::Iden` is not satisfied

Reproduces How Often

Always

Versions

└── sea-orm v0.7.0
    ├── sea-orm-macros v0.7.0 (proc-macro)
    ├── sea-query v0.23.0
    │   ├── sea-query-derive v0.2.0 (proc-macro)
    ├── sea-strum v0.23.0
    │   └── sea-strum_macros v0.23.0 (proc-macro)
└── sea-schema v0.6.0
    ├── sea-orm v0.6.0
    │   ├── sea-orm-macros v0.6.0 (proc-macro)
    │   ├── sea-query v0.21.0
    │   │   └── sea-query-derive v0.2.0 (proc-macro) (*)
    │   ├── sea-strum v0.23.0 (*)
    ├── sea-query v0.22.0
    │   └── sea-query-derive v0.2.0 (proc-macro) (*)
    ├── sea-schema-derive v0.1.0 (proc-macro)

Determine SQLite type by following affinity rule

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

Fail to distinguish MySQL default expression / value

thread 'main' panicked at 'assertion failed: `(left == right)`

Diff < left / right > :
<"CREATE TABLE `order` ( `id` int NOT NULL AUTO_INCREMENT, `total` decimal(19, 4), `bakery_id` int NOT NULL, `customer_id` int NOT NULL, `placed_at` datetime NOT NULL DEFAULT '2021-11-05 00:00:00', KEY `FK_order_bakery` (`bakery_id`), KEY `FK_order_customer` (`customer_id`), PRIMARY KEY (`id`), CONSTRAINT `FK_order_bakery` FOREIGN KEY (`bakery_id`) REFERENCES `bakery` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_order_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci"
>"CREATE TABLE `order` ( `id` int NOT NULL AUTO_INCREMENT, `total` decimal(19, 4), `bakery_id` int NOT NULL, `customer_id` int NOT NULL, `placed_at` datetime NOT NULL DEFAULT 2021-11-05 00:00:00, KEY `FK_order_bakery` (`bakery_id`), KEY `FK_order_customer` (`customer_id`), PRIMARY KEY (`id`), CONSTRAINT `FK_order_bakery` FOREIGN KEY (`bakery_id`) REFERENCES `bakery` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_order_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci"

', tests/live/mysql/src/main.rs:56:9

Panics with certain Pg FKs

I don't have a very easily replicable recipe for this. I'm working with the musicbrainz schema by way of the mbdata tool, which allows you to import the musicbrainz data dump locally. Then I'm trying to generate entities for that schema with sea-orm-cli.

Here's the error with backtrace:

thread 'main' panicked at 'called `Option::unwrap()` on a `None` value', /home/autarch/projects/sea-schema/src/postgres/parser/table_constraints.rs:65:77
stack backtrace:
   0: rust_begin_unwind
             at /rustc/59eed8a2aac0230a8b53e89d4e99d55912ba6b35/library/std/src/panicking.rs:517:5
   1: core::panicking::panic_fmt
             at /rustc/59eed8a2aac0230a8b53e89d4e99d55912ba6b35/library/core/src/panicking.rs:101:14
   2: core::panicking::panic
             at /rustc/59eed8a2aac0230a8b53e89d4e99d55912ba6b35/library/core/src/panicking.rs:50:5
   3: <sea_schema::postgres::parser::table_constraints::TableConstraintsQueryResultParser as core::iter::traits::iterator::Iterator>::next
   4: <alloc::vec::Vec<T> as alloc::vec::spec_from_iter::SpecFromIter<T,I>>::from_iter
   5: <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll
   6: <futures_util::stream::futures_ordered::FuturesOrdered<Fut> as futures_core::stream::Stream>::poll_next
   7: <futures_util::stream::stream::collect::Collect<St,C> as core::future::future::Future>::poll
   8: <futures_util::future::join_all::JoinAll<F> as core::future::future::Future>::poll
   9: sea_orm_cli::run_generate_command::{{closure}}
  10: <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll
  11: std::thread::local::LocalKey<T>::with
  12: <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll
  13: async_io::driver::block_on
  14: std::thread::local::LocalKey<T>::with
  15: async_std::task::builder::Builder::blocking
  16: sea_orm_cli::main

I've done some digging and the issue is with the query that gets table constraints. The generated SQL ends up looking like this (I've replaced the query parameters with actual values so I could reproduce via psql):

   SELECT
      "table_constraints"."constraint_schema",
      "table_constraints"."constraint_name",
      "table_constraints"."table_schema",
      "table_constraints"."table_name",
      "table_constraints"."constraint_type",
      "table_constraints"."is_deferrable",
      "table_constraints"."initially_deferred",
      "check_constraints"."check_clause",
      "key_column_usage"."column_name",
      "key_column_usage"."ordinal_position",
      "key_column_usage"."position_in_unique_constraint",
      "referential_constraints_subquery"."unique_constraint_schema",
      "referential_constraints_subquery"."unique_constraint_name",
      "referential_constraints_subquery"."match_option",
      "referential_constraints_subquery"."update_rule",
      "referential_constraints_subquery"."delete_rule",
      "referential_constraints_subquery"."table_name",
      "referential_constraints_subquery"."column_name"
    FROM
      "information_schema"."table_constraints"
      LEFT JOIN "information_schema"."check_constraints" ON "table_constraints"."constraint_name" = "check_constraints"."constraint_name"
      LEFT JOIN "information_schema"."key_column_usage" ON "table_constraints"."constraint_name" = "key_column_usage"."constraint_name"
      LEFT JOIN (
        SELECT
          "referential_constraints"."constraint_name",
          "referential_constraints"."unique_constraint_schema",
          "referential_constraints"."unique_constraint_name",
          "referential_constraints"."match_option",
          "referential_constraints"."update_rule",
          "referential_constraints"."delete_rule",
          "key_column_usage"."table_name",
          "key_column_usage"."column_name"
        FROM
          "information_schema"."referential_constraints"
          LEFT JOIN "information_schema"."key_column_usage" ON "referential_constraints"."unique_constraint_name" = "key_column_usage"."constraint_name"
      ) AS "referential_constraints_subquery" ON "table_constraints"."constraint_name" = "referential_constraints_subquery"."constraint_name"
    WHERE
      "table_constraints"."table_schema" = 'musicbrainz'
      AND "table_constraints"."table_name" = 'artist_release'
    ORDER BY
      "table_constraints"."constraint_name" ASC,
      "key_column_usage"."ordinal_position" ASC,
      "referential_constraints_subquery"."unique_constraint_name" ASC,
      "referential_constraints_subquery"."constraint_name" ASC

The underlying issue is in the sea-schema/src/postgres/parser/table_constraints.rs code, specifically the "FOREIGN KEY" match arm:

            "FOREIGN KEY" => {
                let mut columns = Vec::new();
                let mut foreign_columns = Vec::new();

                columns.push(result.column_name.unwrap());
                let table = result.referential_key_table_name.unwrap();
                foreign_columns.push(result.referential_key_column_name.unwrap());
                let on_update =
                    ForeignKeyAction::from_str(&result.update_rule.clone().unwrap_or_default());
                let on_delete =
                    ForeignKeyAction::from_str(&result.delete_rule.clone().unwrap_or_default());

                while let Some(result) = self.results.next() {
                    if result.constraint_name != constraint_name {
                        self.curr = Some(result);
                        return Some(Constraint::References(References {
                            name: constraint_name,
                            columns,
                            table,
                            foreign_columns,
                            on_update,
                            on_delete,
                        }));
                    }

                    columns.push(result.column_name.unwrap());
                    foreign_columns.push(result.referential_key_column_name.unwrap());
                }

                Some(Constraint::References(References {
                    name: constraint_name,
                    columns,
                    table,
                    foreign_columns,
                    on_update,
                    on_delete,
                }))
            }

The problem is that the query as executed above contains a lot of duplicate
rows for non-CHECK constraints. Here's the output:

┌───────────────────┬───────────────────────────┬──────────────┬────────────────┬─────────────────┬───────────────┬────────────────────┬─────────────────────────────┬─────────────┬──────────────────┬───────────────────────────────┬──────────────────────────┬────────────────────────┬──────────────┬─────────────┬─────────────┬────────────┬─────────────┐
│ constraint_schema │      constraint_name      │ table_schema │   table_name   │ constraint_type │ is_deferrable │ initially_deferred │        check_clause         │ column_name │ ordinal_position │ position_in_unique_constraint │ unique_constraint_schema │ unique_constraint_name │ match_option │ update_rule │ delete_rule │ table_name │ column_name │
├───────────────────┼───────────────────────────┼──────────────┼────────────────┼─────────────────┼───────────────┼────────────────────┼─────────────────────────────┼─────────────┼──────────────────┼───────────────────────────────┼──────────────────────────┼────────────────────────┼──────────────┼─────────────┼─────────────┼────────────┼─────────────┤
│ musicbrainz       │ 83158_83428_1_not_null    │ musicbrainz  │ artist_release │ CHECK           │ NO            │ NO                 │ is_track_artist IS NOT NULL │             │                  │                               │                          │                        │              │             │             │            │             │
│ musicbrainz       │ 83158_83428_2_not_null    │ musicbrainz  │ artist_release │ CHECK           │ NO            │ NO                 │ artist IS NOT NULL          │             │                  │                               │                          │                        │              │             │             │            │             │
│ musicbrainz       │ 83158_83428_7_not_null    │ musicbrainz  │ artist_release │ CHECK           │ NO            │ NO                 │ sort_character IS NOT NULL  │             │                  │                               │                          │                        │              │             │             │            │             │
│ musicbrainz       │ 83158_83428_8_not_null    │ musicbrainz  │ artist_release │ CHECK           │ NO            │ NO                 │ release IS NOT NULL         │             │                  │                               │                          │                        │              │             │             │            │             │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │ musicbrainz              │ artist_pkey            │ NONE         │ NO ACTION   │ CASCADE     │ artist     │ id          │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │ musicbrainz              │ artist_pkey            │ NONE         │ NO ACTION   │ CASCADE     │ artist     │ id          │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │ musicbrainz              │ artist_pkey            │ NONE         │ NO ACTION   │ CASCADE     │ artist     │ id          │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │ musicbrainz              │ artist_pkey            │ NONE         │ NO ACTION   │ CASCADE     │ artist     │ id          │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │ musicbrainz              │ artist_pkey            │ NONE         │ NO ACTION   │ CASCADE     │ artist     │ id          │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │ musicbrainz              │ artist_pkey            │ NONE         │ NO ACTION   │ CASCADE     │ artist     │ id          │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │ musicbrainz              │ artist_pkey            │ NONE         │ NO ACTION   │ CASCADE     │ artist     │ id          │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │ musicbrainz              │ artist_pkey            │ NONE         │ NO ACTION   │ CASCADE     │ artist     │ id          │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │ musicbrainz              │ artist_pkey            │ NONE         │ NO ACTION   │ CASCADE     │ artist     │ id          │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │                          │                        │ NONE         │ NO ACTION   │ CASCADE     │            │             │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │                          │                        │ NONE         │ NO ACTION   │ CASCADE     │            │             │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │                          │                        │ NONE         │ NO ACTION   │ CASCADE     │            │             │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │                          │                        │ NONE         │ NO ACTION   │ CASCADE     │            │             │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │                          │                        │ NONE         │ NO ACTION   │ CASCADE     │            │             │
│ musicbrainz       │ artist_release_fk_artist  │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ artist      │                1 │                             1 │                          │                        │ NONE         │ NO ACTION   │ CASCADE     │            │             │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │ musicbrainz              │ release_pkey           │ NONE         │ NO ACTION   │ CASCADE     │ release    │ id          │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │ musicbrainz              │ release_pkey           │ NONE         │ NO ACTION   │ CASCADE     │ release    │ id          │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │ musicbrainz              │ release_pkey           │ NONE         │ NO ACTION   │ CASCADE     │ release    │ id          │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │ musicbrainz              │ release_pkey           │ NONE         │ NO ACTION   │ CASCADE     │ release    │ id          │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │ musicbrainz              │ release_pkey           │ NONE         │ NO ACTION   │ CASCADE     │ release    │ id          │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │ musicbrainz              │ release_pkey           │ NONE         │ NO ACTION   │ CASCADE     │ release    │ id          │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │ musicbrainz              │ release_pkey           │ NONE         │ NO ACTION   │ CASCADE     │ release    │ id          │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │ musicbrainz              │ release_pkey           │ NONE         │ NO ACTION   │ CASCADE     │ release    │ id          │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │ musicbrainz              │ release_pkey           │ NONE         │ NO ACTION   │ CASCADE     │ release    │ id          │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │                          │                        │ NONE         │ NO ACTION   │ CASCADE     │            │             │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │                          │                        │ NONE         │ NO ACTION   │ CASCADE     │            │             │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │                          │                        │ NONE         │ NO ACTION   │ CASCADE     │            │             │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │                          │                        │ NONE         │ NO ACTION   │ CASCADE     │            │             │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │                          │                        │ NONE         │ NO ACTION   │ CASCADE     │            │             │
│ musicbrainz       │ artist_release_fk_release │ musicbrainz  │ artist_release │ FOREIGN KEY     │ NO            │ NO                 │                             │ release     │                1 │                             1 │                          │                        │ NONE         │ NO ACTION   │ CASCADE     │            │             │
└───────────────────┴───────────────────────────┴──────────────┴────────────────┴─────────────────┴───────────────┴────────────────────┴─────────────────────────────┴─────────────┴──────────────────┴───────────────────────────────┴──────────────────────────┴────────────────────────┴──────────────┴─────────────┴─────────────┴────────────┴─────────────┘
(34 rows)

Note a number of the rows have no values for table_name or column_name, which causes the unconditional unwrap() on that field to fail. I think this query needs some sort of DISTINCT in it. Another option may be to split out the query for check constraints from other types so you don't need need such a complex query with so many LEFT OUTER JOIN bits. I think that would make it easier to reason about the query and ensure that you only get unique, correct rows.

Add year, bit and varbit types

Motivation

Add support for year, bit and varbit types.

Link: SeaQL/sea-query#466

Test Suite

Given our current facility, we can develop a 'feedback loop' test suite,
writing a bunch of SeaQuery schema statements, execute it, run discovery,
if we got back the original schema statement, then we succeed.

This allows us to generalize testing over MySQL and Postgres.

MySQL: handle panic upon unique constraint

SeaQL/sea-orm#191

Schema to reproduce:

CREATE TABLE `table_2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `table_1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `table_2_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `table_2_id` (`table_2_id`),
  CONSTRAINT `table_2_id` FOREIGN KEY (`table_2_id`) REFERENCES `table_2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Is there a plan to support MSSQL?

Support Posgtresql network types

Summary

Add support for postgresql network types

Additional Information

sea-query issues: SeaQL/sea-query#187

Postgres primary key with generated expression

This id column should be auto incremented but sea-schema fail to capture it.

create table tag
(
    id bigint generated by default as identity
        constraint tag_pkey
            primary key
);

Incorrect Discovery for Composite Foreign Keys in PostgreSQL

Description

Postgres schema discovery produces incorrect results for composite foreign keys.
Specifically, for a key with $n$ columns each key is repeated $n$ times in both columns and foreign_columns.

Steps to Reproduce

On a Postgres database:

Create a schema with a composite primary key and a corresponding foreign key. The following is a minimal example:

CREATE TABLE Parent (
    id1 int,
    id2 int,

   PRIMARY KEY (id1, id2)
);

CREATE TABLE Child (
    id INT PRIMARY KEY,
    parent_id1 INT,
    parent_id2 INT,
    
    FOREIGN KEY (parent_id1, parent_id2) REFERENCES Parent(id1, id2)
);

Run schema discovery

Expected Behavior

The references section for the example should like this:

References {
  name: "child_parent_id1_parent_id2_fkey",
  columns: [
    "parent_id1",
    "parent_id2",
  ],
  table: "parent",
  foreign_columns: [
    "id1",
    "id2",
  ],
  on_update: Some(
    NoAction,
  ),
  on_delete: Some(
    NoAction,
  ),
}

Actual Behavior

References {
  name: "child_parent_id1_parent_id2_fkey",
  columns: [
    "parent_id1",
    "parent_id1",
    "parent_id2",
    "parent_id2",
  ],
  table: "parent",
  foreign_columns: [
    "id1",
    "id2",
    "id1",
    "id2",
  ],
  on_update: Some(
    NoAction,
  ),
  on_delete: Some(
    NoAction,
  ),
}

Reproduces How Often

Always

Versions

sea-schema=0.11.0
Postgres 14.6

Additional Information

I am relatively confident that the root cause is this query:

sea-schema/src/postgres/query/constraints/mod.rs

Lines 134 to 160 in e709644

    
           .join_subquery( 
        
               JoinType::LeftJoin, 
        
               Query::select() 
        
                   .distinct() 
        
                   .columns(vec![ 
        
                       (Schema::ReferentialConstraints, RefC::ConstraintName), 
        
                       (Schema::ReferentialConstraints, RefC::UniqueConstraintSchema), 
        
                       (Schema::ReferentialConstraints, RefC::UniqueConstraintName), 
        
                       (Schema::ReferentialConstraints, RefC::MatchOption), 
        
                       (Schema::ReferentialConstraints, RefC::UpdateRule), 
        
                       (Schema::ReferentialConstraints, RefC::DeleteRule), 
        
                   ]) 
        
                   .columns(vec![ 
        
                       (Schema::ConstraintColumnUsage, Kcuf::TableName), 
        
                       (Schema::ConstraintColumnUsage, Kcuf::ColumnName), 
        
                   ]) 
        
                   .from((Schema::Schema, Schema::ReferentialConstraints)) 
        
                   .left_join( 
        
                       (Schema::Schema, Schema::ConstraintColumnUsage), 
        
                       Expr::col((Schema::ReferentialConstraints, RefC::ConstraintName)) 
        
                           .equals((Schema::ConstraintColumnUsage, Kcuf::ConstraintName)), 
        
                   ) 
        
                   .take(), 
        
               rcsq.clone(), 
        
               Expr::col((Schema::TableConstraints, Tcf::ConstraintName)) 
        
                   .equals((rcsq.clone(), RefC::ConstraintName)), 
        
           )

Specifically, the inner select will produce all primary keys targeted by a foreign key. Each of these will be joined with all referencing foreign key columns in a matching child table. This produces all $n \times n$ possible combinations of primary and foreign key columns which is of course fine when $n=1$ but not in any other case.

One potential way to fix this is to additionally query the ordinal_position of each column in the primary key in the inner select.
This requires joining with the key_column_usage table in the inner select to retrieve the primary key.
ordinal_position can then be matched with position_in_unique_constraint in the key_column_usage row for the foreign key.

The overall query would look like this (changes commented)

.join_subquery(
        JoinType::LeftJoin,
        Query::select()
            .distinct()
            .columns(vec![
                (Schema::ReferentialConstraints, RefC::ConstraintName),
                (Schema::ReferentialConstraints, RefC::UniqueConstraintSchema),
                (Schema::ReferentialConstraints, RefC::UniqueConstraintName),
                (Schema::ReferentialConstraints, RefC::MatchOption),
                (Schema::ReferentialConstraints, RefC::UpdateRule),
                (Schema::ReferentialConstraints, RefC::DeleteRule),
            ])
            .columns(vec![
                (Schema::ConstraintColumnUsage, Kcuf::TableName),
                (Schema::ConstraintColumnUsage, Kcuf::ColumnName),
            ])
            .columns(vec![
                // Extract the ordinal position of the referenced primary keys
                (Schema::KeyColumnUsage, Kcuf::OrdinalPosition)
            ])
            .from((Schema::Schema, Schema::ReferentialConstraints))
            .left_join(
                (Schema::Schema, Schema::ConstraintColumnUsage),
                Expr::col((Schema::ReferentialConstraints, RefC::ConstraintName))
                    .equals((Schema::ConstraintColumnUsage, Kcuf::ConstraintName)),
            )
            .left_join(
                // Join the key_column_usage rows for the referenced primary keys
                (Schema::Schema, Schema::KeyColumnUsage),
                Condition::all()
                .add(
                    Expr::col((Schema::ConstraintColumnUsage, Kcuf::ColumnName))
                    .equals((Schema::KeyColumnUsage, Kcuf::ColumnName)),
                ).add(
                    Expr::col((Schema::ReferentialConstraints, RefC::UniqueConstraintName))
                    .equals((Schema::KeyColumnUsage, Kcuf::ConstraintName))
                ).add(
                    Expr::col((Schema::ReferentialConstraints, RefC::UniqueConstraintSchema))
                    .equals((Schema::KeyColumnUsage, Kcuf::ConstraintSchema))
                )
            )
            .take(),
        rcsq.clone(),
        Condition::all().add(
            Expr::col((Schema::TableConstraints, Tcf::ConstraintName))
            .equals((rcsq.clone(), RefC::ConstraintName))
        ).add(
            // Only join when the referenced primary key position matches position_in_unique_constraint for the foreign key column
            Expr::col((Schema::KeyColumnUsage, Kcuf::PositionInUniqueConstraint))
            .equals((rcsq.clone(), Kcuf::OrdinalPosition))
        ))

However, I am not sure if I overlook any side effects of this fix.

	CREATE TYPE mpaa_rating AS ENUM (
	'G',
	'PG',
	'PG-13',
	'R',
	'NC-17'
	);

	TablesFields::UserDefinedTypeSchema,
	TablesFields::UserDefinedTypeName,

	.join_subquery(
	JoinType::LeftJoin,
	Query::select()
	.distinct()
	.columns(vec![
	(Schema::ReferentialConstraints, RefC::ConstraintName),
	(Schema::ReferentialConstraints, RefC::UniqueConstraintSchema),
	(Schema::ReferentialConstraints, RefC::UniqueConstraintName),
	(Schema::ReferentialConstraints, RefC::MatchOption),
	(Schema::ReferentialConstraints, RefC::UpdateRule),
	(Schema::ReferentialConstraints, RefC::DeleteRule),
	])
	.columns(vec![
	(Schema::ConstraintColumnUsage, Kcuf::TableName),
	(Schema::ConstraintColumnUsage, Kcuf::ColumnName),
	])
	.from((Schema::Schema, Schema::ReferentialConstraints))
	.left_join(
	(Schema::Schema, Schema::ConstraintColumnUsage),
	Expr::col((Schema::ReferentialConstraints, RefC::ConstraintName))
	.equals((Schema::ConstraintColumnUsage, Kcuf::ConstraintName)),
	)
	.take(),
	rcsq.clone(),
	Expr::col((Schema::TableConstraints, Tcf::ConstraintName))
	.equals((rcsq.clone(), RefC::ConstraintName)),
	)

seaql / sea-schema Goto Github PK

sea-schema's People

Contributors

Stargazers

Watchers

Forkers

sea-schema's Issues

Description

Steps to Reproduce

Expected Behavior

Actual Behavior

Reproduces How Often

Versions

Motivation

Description

Steps to Reproduce

Expected Behavior

Actual Behavior

Reproduces How Often

Versions

Additional Information

Migration Manager

Migration Codegen

Schema Manager

Motivation

Solution

Summary

Additional Information

Description

Description

Steps to Reproduce

Expected Behavior

Actual Behavior

Reproduces How Often

Versions

Additional Information

Description

Steps to Reproduce

Expected Behavior

Actual Behavior

Reproduces How Often

Versions

Additional Information

Motivation

Proposed Solutions

Related Discussion

Discovery

Parser

Writer

Description

Steps to Reproduce

Expected Behavior

Actual Behavior

Reproduces How Often

Versions

Additional Information

Description

Steps to Reproduce

Expected Behavior

Actual Behavior

Reproduces How Often

Versions

Additional Information

Description

Steps to Reproduce

Expected Behavior

Actual Behavior

Reproduces How Often

Versions

Additional Information

Description

Steps to Reproduce

Expected Behavior

Actual Behavior

Versions

Description

Steps to Reproduce

Expected Behavior

Actual Behavior

Reproduces How Often