sea_schema/postgres/query/constraints/
mod.rs

1pub mod check_constraints;
2pub mod key_column_usage;
3pub mod referential_constraints;
4pub mod table_constraints;
5
6pub use check_constraints::*;
7pub use key_column_usage::*;
8pub use referential_constraints::*;
9pub use table_constraints::*;
10
11use super::{InformationSchema, SchemaQueryBuilder};
12use crate::{postgres::query::select_base_table_and_view, sqlx_types::postgres::PgRow};
13use sea_query::{Alias, Condition, Expr, Iden, JoinType, Order, Query, SeaRc, SelectStatement};
14
15#[derive(Debug, Default)]
16pub struct TableConstraintsQueryResult {
17    // From table_constraints
18    pub constraint_schema: String,
19    pub constraint_name: String,
20    pub table_schema: String,
21    pub table_name: String,
22    pub constraint_type: String,
23    pub is_deferrable: String,
24    pub initially_deferred: String,
25
26    // From check_constraints
27    pub check_clause: Option<String>,
28
29    // From key_column_usage
30    pub column_name: Option<String>,
31    pub ordinal_position: Option<i32>,
32    pub position_in_unique_constraint: Option<i32>,
33
34    // From referential_constraints
35    pub unique_constraint_schema: Option<String>,
36    pub unique_constraint_name: Option<String>,
37    pub match_option: Option<String>,
38    pub update_rule: Option<String>,
39    pub delete_rule: Option<String>,
40
41    // From key_column_usage as part of subquery involving referential_constraints
42    pub referential_key_table_name: Option<String>,
43    pub referential_key_column_name: Option<String>,
44}
45
46impl SchemaQueryBuilder {
47    pub fn query_table_constraints(
48        &self,
49        schema: SeaRc<dyn Iden>,
50        table: SeaRc<dyn Iden>,
51    ) -> SelectStatement {
52        type Schema = InformationSchema;
53        type Tcf = TableConstraintsField;
54        type Cf = CheckConstraintsFields;
55        type Kcuf = KeyColumnUsageFields;
56        type RefC = ReferentialConstraintsFields;
57
58        let rcsq = Alias::new("referential_constraints_subquery");
59
60        Query::select()
61            .columns(vec![
62                (Schema::TableConstraints, Tcf::ConstraintSchema),
63                (Schema::TableConstraints, Tcf::ConstraintName),
64                (Schema::TableConstraints, Tcf::TableSchema),
65                (Schema::TableConstraints, Tcf::TableName),
66                (Schema::TableConstraints, Tcf::ConstraintType),
67                (Schema::TableConstraints, Tcf::IsDeferrable),
68                (Schema::TableConstraints, Tcf::InitiallyDeferred),
69            ])
70            .column((Schema::CheckConstraints, Cf::CheckClause))
71            .columns(vec![
72                (Schema::KeyColumnUsage, Kcuf::ColumnName),
73                (Schema::KeyColumnUsage, Kcuf::OrdinalPosition),
74                (Schema::KeyColumnUsage, Kcuf::PositionInUniqueConstraint),
75            ])
76            .columns(vec![
77                (rcsq.clone(), RefC::UniqueConstraintSchema),
78                (rcsq.clone(), RefC::UniqueConstraintName),
79                (rcsq.clone(), RefC::MatchOption),
80                (rcsq.clone(), RefC::UpdateRule),
81                (rcsq.clone(), RefC::DeleteRule),
82            ])
83            .columns(vec![
84                (rcsq.clone(), Kcuf::TableName),
85                (rcsq.clone(), Kcuf::ColumnName),
86            ])
87            .from((Schema::Schema, InformationSchema::TableConstraints))
88            .join(
89                JoinType::LeftJoin,
90                (Schema::Schema, Schema::CheckConstraints),
91                Condition::all()
92                    .add(
93                        Expr::col((Schema::TableConstraints, Tcf::ConstraintName))
94                            .equals((Schema::CheckConstraints, Cf::ConstraintName)),
95                    )
96                    .add(
97                        Expr::col((Schema::TableConstraints, Tcf::ConstraintCatalog))
98                            .equals((Schema::CheckConstraints, Cf::ConstraintCatalog)),
99                    )
100                    .add(
101                        Expr::col((Schema::TableConstraints, Tcf::ConstraintSchema))
102                            .equals((Schema::CheckConstraints, Cf::ConstraintSchema)),
103                    ),
104            )
105            .join(
106                JoinType::LeftJoin,
107                (Schema::Schema, Schema::KeyColumnUsage),
108                Condition::all()
109                    .add(
110                        Expr::col((Schema::TableConstraints, Tcf::ConstraintName))
111                            .equals((Schema::KeyColumnUsage, Kcuf::ConstraintName)),
112                    )
113                    .add(
114                        Expr::col((Schema::TableConstraints, Tcf::ConstraintCatalog))
115                            .equals((Schema::KeyColumnUsage, Kcuf::ConstraintCatalog)),
116                    )
117                    .add(
118                        Expr::col((Schema::TableConstraints, Tcf::ConstraintSchema))
119                            .equals((Schema::KeyColumnUsage, Kcuf::ConstraintSchema)),
120                    )
121                    .add(
122                        Expr::col((Schema::TableConstraints, Tcf::TableCatalog))
123                            .equals((Schema::KeyColumnUsage, Kcuf::TableCatalog)),
124                    )
125                    .add(
126                        Expr::col((Schema::TableConstraints, Tcf::TableSchema))
127                            .equals((Schema::KeyColumnUsage, Kcuf::TableSchema)),
128                    )
129                    .add(
130                        Expr::col((Schema::TableConstraints, Tcf::TableName))
131                            .equals((Schema::KeyColumnUsage, Kcuf::TableName)),
132                    ),
133            )
134            .join_subquery(
135                JoinType::LeftJoin,
136                Query::select()
137                    .distinct()
138                    .columns(vec![
139                        (Schema::ReferentialConstraints, RefC::ConstraintName),
140                        (Schema::ReferentialConstraints, RefC::UniqueConstraintSchema),
141                        (Schema::ReferentialConstraints, RefC::UniqueConstraintName),
142                        (Schema::ReferentialConstraints, RefC::MatchOption),
143                        (Schema::ReferentialConstraints, RefC::UpdateRule),
144                        (Schema::ReferentialConstraints, RefC::DeleteRule),
145                    ])
146                    .columns(vec![
147                        (Schema::ConstraintColumnUsage, Kcuf::TableName),
148                        (Schema::ConstraintColumnUsage, Kcuf::ColumnName),
149                    ])
150                    .columns(vec![
151                        // Extract the ordinal position of the referenced primary keys
152                        (Schema::KeyColumnUsage, Kcuf::OrdinalPosition),
153                    ])
154                    .from((Schema::Schema, Schema::ReferentialConstraints))
155                    .left_join(
156                        (Schema::Schema, Schema::ConstraintColumnUsage),
157                        Expr::col((Schema::ReferentialConstraints, RefC::ConstraintName))
158                            .equals((Schema::ConstraintColumnUsage, Kcuf::ConstraintName)),
159                    )
160                    .left_join(
161                        // Join the key_column_usage rows for the referenced primary keys
162                        (Schema::Schema, Schema::KeyColumnUsage),
163                        Condition::all()
164                            .add(
165                                Expr::col((Schema::ConstraintColumnUsage, Kcuf::ColumnName))
166                                    .equals((Schema::KeyColumnUsage, Kcuf::ColumnName)),
167                            )
168                            .add(
169                                Expr::col((
170                                    Schema::ReferentialConstraints,
171                                    RefC::UniqueConstraintName,
172                                ))
173                                .equals((Schema::KeyColumnUsage, Kcuf::ConstraintName)),
174                            )
175                            .add(
176                                Expr::col((
177                                    Schema::ReferentialConstraints,
178                                    RefC::UniqueConstraintSchema,
179                                ))
180                                .equals((Schema::KeyColumnUsage, Kcuf::ConstraintSchema)),
181                            ),
182                    )
183                    .take(),
184                rcsq.clone(),
185                Condition::all()
186                    .add(
187                        Expr::col((Schema::TableConstraints, Tcf::ConstraintName))
188                            .equals((rcsq.clone(), RefC::ConstraintName)),
189                    )
190                    .add(
191                        Condition::any()
192                            .add(
193                                // Only join when the referenced primary key position matches position_in_unique_constraint for the foreign key column
194                                Expr::col((
195                                    Schema::KeyColumnUsage,
196                                    Kcuf::PositionInUniqueConstraint,
197                                ))
198                                .equals((rcsq.clone(), Kcuf::OrdinalPosition)),
199                            )
200                            .add(
201                                // Allow foreign key column without unique constraint
202                                Expr::col((rcsq.clone(), Kcuf::OrdinalPosition)).is_null(),
203                            ),
204                    ),
205            )
206            .and_where(
207                Expr::col((Schema::TableConstraints, Tcf::TableSchema)).eq(schema.to_string()),
208            )
209            .and_where(Expr::col((Schema::TableConstraints, Tcf::TableName)).eq(table.to_string()))
210            .cond_where(
211                Condition::any()
212                    .add(Expr::col((rcsq.clone(), Kcuf::TableName)).is_null())
213                    .add(
214                        Expr::col((rcsq.clone(), Kcuf::TableName))
215                            .not_in_subquery(select_base_table_and_view()),
216                    ),
217            )
218            .order_by((Schema::TableConstraints, Tcf::ConstraintName), Order::Asc)
219            .order_by((Schema::KeyColumnUsage, Kcuf::OrdinalPosition), Order::Asc)
220            .order_by((rcsq.clone(), RefC::UniqueConstraintName), Order::Asc)
221            .order_by((rcsq, Tcf::ConstraintName), Order::Asc)
222            .take()
223    }
224}
225
226#[cfg(feature = "sqlx-postgres")]
227impl From<&PgRow> for TableConstraintsQueryResult {
228    fn from(row: &PgRow) -> Self {
229        use crate::sqlx_types::Row;
230        Self {
231            constraint_schema: row.get(0),
232            constraint_name: row.get(1),
233            table_schema: row.get(2),
234            table_name: row.get(3),
235            constraint_type: row.get(4),
236            is_deferrable: row.get(5),
237            initially_deferred: row.get(6),
238
239            check_clause: row.get(7),
240
241            column_name: row.get(8),
242            ordinal_position: row.get(9),
243            position_in_unique_constraint: row.get(10),
244
245            unique_constraint_schema: row.get(11),
246            unique_constraint_name: row.get(12),
247            match_option: row.get(13),
248            update_rule: row.get(14),
249            delete_rule: row.get(15),
250
251            referential_key_table_name: row.get(16),
252            referential_key_column_name: row.get(17),
253        }
254    }
255}
256
257#[cfg(not(feature = "sqlx-postgres"))]
258impl From<&PgRow> for TableConstraintsQueryResult {
259    fn from(_: &PgRow) -> Self {
260        Self::default()
261    }
262}
sea_schema/postgres/query/constraints/mod.rs

sea_schema/postgres/query/constraints/
mod.rs
