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

sea_schema/postgres/query/constraints/
mod.rs
