sea_schema/mysql/query/
foreign_key.rs

1use super::{InformationSchema, SchemaQueryBuilder};
2use crate::sqlx_types::SqlxRow;
3use sea_query::{DynIden, Expr, ExprTrait, Order, Query, SelectStatement};
4
5#[derive(Debug, sea_query::Iden)]
6/// Ref: https://dev.mysql.com/doc/refman/8.0/en/information-schema-key-column-usage-table.html
7pub enum KeyColumnUsageFields {
8    ConstraintSchema,
9    ConstraintName,
10    TableSchema,
11    TableName,
12    ColumnName,
13    OrdinalPosition,
14    PositionInUniqueConstraint,
15    ReferencedTableSchema,
16    ReferencedTableName,
17    ReferencedColumnName,
18}
19
20#[derive(Debug, sea_query::Iden)]
21/// Ref: https://dev.mysql.com/doc/refman/8.0/en/information-schema-referential-constraints-table.html
22pub enum ReferentialConstraintsFields {
23    ConstraintSchema,
24    ConstraintName,
25    UniqueConstraintSchema,
26    UniqueConstraintName,
27    UpdateRule,
28    DeleteRule,
29    TableName,
30    ReferencedTableName,
31}
32
33#[derive(Debug, Default)]
34pub struct ForeignKeyQueryResult {
35    pub constraint_name: String,
36    pub column_name: String,
37    pub referenced_table_name: String,
38    pub referenced_column_name: String,
39    pub update_rule: String,
40    pub delete_rule: String,
41}
42
43impl SchemaQueryBuilder {
44    pub fn query_foreign_key(&self, schema: DynIden, table: DynIden) -> SelectStatement {
45        type Schema = InformationSchema;
46        type Key = KeyColumnUsageFields;
47        type Ref = ReferentialConstraintsFields;
48        Query::select()
49            .columns(vec![
50                (Schema::KeyColumnUsage, Key::ConstraintName),
51                (Schema::KeyColumnUsage, Key::ColumnName),
52                (Schema::KeyColumnUsage, Key::ReferencedTableName),
53                (Schema::KeyColumnUsage, Key::ReferencedColumnName),
54            ])
55            .columns(vec![
56                (Schema::ReferentialConstraints, Ref::UpdateRule),
57                (Schema::ReferentialConstraints, Ref::DeleteRule),
58            ])
59            .from((Schema::Schema, Schema::KeyColumnUsage))
60            .inner_join(
61                (Schema::Schema, Schema::ReferentialConstraints),
62                Expr::col((Schema::KeyColumnUsage, Key::ConstraintSchema))
63                    .equals((Schema::ReferentialConstraints, Ref::ConstraintSchema))
64                    .and(
65                        Expr::col((Schema::KeyColumnUsage, Key::ConstraintName))
66                            .equals((Schema::ReferentialConstraints, Ref::ConstraintName)),
67                    ),
68            )
69            .and_where(
70                Expr::col((Schema::KeyColumnUsage, Key::ConstraintSchema)).eq(schema.to_string()),
71            )
72            .and_where(Expr::col((Schema::KeyColumnUsage, Key::TableName)).eq(table.to_string()))
73            .and_where(Expr::col((Schema::KeyColumnUsage, Key::ReferencedTableName)).is_not_null())
74            .and_where(Expr::col((Schema::KeyColumnUsage, Key::ReferencedColumnName)).is_not_null())
75            .order_by(Key::ConstraintName, Order::Asc)
76            .order_by(Key::OrdinalPosition, Order::Asc)
77            .take()
78    }
79}
80
81#[cfg(feature = "sqlx-mysql")]
82impl From<SqlxRow> for ForeignKeyQueryResult {
83    fn from(row: SqlxRow) -> Self {
84        let row = row.mysql();
85        use crate::mysql::discovery::GetMySqlValue;
86        Self {
87            constraint_name: row.get_string(0),
88            column_name: row.get_string(1),
89            referenced_table_name: row.get_string(2),
90            referenced_column_name: row.get_string(3),
91            update_rule: row.get_string(4),
92            delete_rule: row.get_string(5),
93        }
94    }
95}
96
97#[cfg(not(feature = "sqlx-mysql"))]
98impl From<SqlxRow> for ForeignKeyQueryResult {
99    fn from(_: SqlxRow) -> Self {
100        Self::default()
101    }
102}
sea_schema/mysql/query/foreign_key.rs

sea_schema/mysql/query/
foreign_key.rs
