sea_schema/postgres/query/
pg_indexes.rs

1use super::SchemaQueryBuilder;
2use crate::sqlx_types::SqlxRow;
3use sea_query::{
4    Condition, DynIden, Expr, ExprTrait, Iden, JoinType, Order, Query, SelectStatement,
5};
6
7#[derive(Debug, Iden)]
8pub enum PgIndexes {
9    Table,
10    #[iden = "tablename"]
11    TableName,
12    #[iden = "schemaname"]
13    SchemaName,
14    #[iden = "indexname"]
15    IndexName,
16}
17
18#[derive(Debug, Iden)]
19pub enum PgIndex {
20    Table,
21    #[iden = "indexrelid"]
22    IndexRelId,
23    #[iden = "indrelid"]
24    IndRelId,
25    #[iden = "indisunique"]
26    IndIsUnique,
27    #[iden = "indisprimary"]
28    IndIsPrimary,
29    #[iden = "indpred"]
30    IndPred,
31    #[iden = "indkey"]
32    IndKey,
33}
34
35#[derive(Debug, Iden)]
36pub enum PgClass {
37    Table,
38    Oid,
39    #[iden = "relnamespace"]
40    RelNamespace,
41    #[iden = "relname"]
42    RelName,
43}
44
45#[derive(Debug, Iden)]
46pub enum PgNamespace {
47    Table,
48    Oid,
49    #[iden = "nspname"]
50    NspName,
51}
52
53#[derive(Debug, Iden)]
54pub enum PgAttribute {
55    Table,
56    Oid,
57    #[iden = "attrelid"]
58    AttRelId,
59    #[iden = "attname"]
60    AttName,
61    #[iden = "attnum"]
62    AttNum,
63}
64
65#[derive(Debug, Default)]
66pub struct UniqueIndexQueryResult {
67    pub index_name: String,
68    pub table_schema: String,
69    pub table_name: String,
70    pub column_name: String,
71    pub is_partial: bool,
72}
73
74impl SchemaQueryBuilder {
75    pub fn query_table_unique_indexes(&self, schema: DynIden, table: DynIden) -> SelectStatement {
76        let idx = "idx";
77        let insp = "insp";
78        let tbl = "tbl";
79        let tnsp = "tnsp";
80        let col = "col";
81        let partially = "partially";
82
83        Query::select()
84            .column((idx, PgClass::RelName))
85            .column((insp, PgNamespace::NspName))
86            .column((tbl, PgClass::RelName))
87            .column((col, PgAttribute::AttName))
88            .expr_as(Expr::col(PgIndex::IndPred).is_not_null(), partially)
89            .from(PgIndex::Table)
90            .join_as(
91                JoinType::Join,
92                PgClass::Table,
93                idx,
94                Expr::col((idx, PgClass::Oid)).equals((PgIndex::Table, PgIndex::IndexRelId)),
95            )
96            .join_as(
97                JoinType::Join,
98                PgNamespace::Table,
99                insp,
100                Expr::col((insp, PgNamespace::Oid)).equals((idx, PgClass::RelNamespace)),
101            )
102            .join_as(
103                JoinType::Join,
104                PgClass::Table,
105                tbl,
106                Expr::col((tbl, PgClass::Oid)).equals((PgIndex::Table, PgIndex::IndRelId)),
107            )
108            .join_as(
109                JoinType::Join,
110                PgNamespace::Table,
111                tnsp,
112                Expr::col((tnsp, PgNamespace::Oid)).equals((tbl, PgClass::RelNamespace)),
113            )
114            .join_as(
115                JoinType::Join,
116                PgAttribute::Table,
117                col,
118                Expr::col((col, PgAttribute::AttRelId)).equals((tbl, PgAttribute::Oid)),
119            )
120            .join_lateral(
121                JoinType::Join,
122                Query::select()
123                    .expr_as(
124                        Expr::cust("generate_subscripts(pg_index.indkey, 1)"),
125                        "subscript",
126                    )
127                    .take(),
128                "sub",
129                Expr::col((col, PgAttribute::AttNum))
130                    .eq(Expr::cust("pg_index.indkey[sub.subscript]")),
131            )
132            .cond_where(
133                Condition::all()
134                    .add(Expr::col((PgIndex::Table, PgIndex::IndIsUnique)).eq(true))
135                    .add(Expr::col((PgIndex::Table, PgIndex::IndIsPrimary)).eq(false))
136                    .add(Expr::col((tbl, PgClass::RelName)).eq(table.to_string()))
137                    .add(Expr::col((tnsp, PgNamespace::NspName)).eq(schema.to_string())),
138            )
139            .order_by((PgIndex::Table, PgIndex::IndexRelId), Order::Asc)
140            .order_by(("sub", "subscript"), Order::Asc)
141            .take()
142    }
143}
144
145#[cfg(feature = "sqlx-postgres")]
146impl From<SqlxRow> for UniqueIndexQueryResult {
147    fn from(row: SqlxRow) -> Self {
148        use crate::sqlx_types::Row;
149        let row = row.postgres();
150        Self {
151            index_name: row.get(0),
152            table_schema: row.get(1),
153            table_name: row.get(2),
154            column_name: row.get(3),
155            is_partial: row.get(4),
156        }
157    }
158}
159
160#[cfg(not(feature = "sqlx-postgres"))]
161impl From<SqlxRow> for UniqueIndexQueryResult {
162    fn from(_: SqlxRow) -> Self {
163        Self::default()
164    }
165}
sea_schema/postgres/query/pg_indexes.rs

sea_schema/postgres/query/
pg_indexes.rs
