by_loco/
schema.rs

1use heck::ToSnakeCase;
2use sea_orm::{
3    sea_query::{
4        Alias, ColumnDef, Expr, Index, IntoIden, PgInterval, Table, TableAlterStatement,
5        TableCreateStatement, TableForeignKey,
6    },
7    ColumnType, DbErr, ForeignKeyAction,
8};
9pub use sea_orm_migration::schema::*;
10use sea_orm_migration::{prelude::Iden, sea_query, SchemaManager};
11
12#[derive(Iden)]
13enum GeneralIds {
14    CreatedAt,
15    UpdatedAt,
16}
17
18/// Alter table
19pub fn alter<T: IntoIden + 'static>(name: T) -> TableAlterStatement {
20    Table::alter().table(name).take()
21}
22
23/// Wrapping table schema creation.
24pub fn table_auto_tz<T>(name: T) -> TableCreateStatement
25where
26    T: IntoIden + 'static,
27{
28    timestamps_tz(Table::create().table(name).if_not_exists().take())
29}
30
31// these two are just aliases, original types exist in seaorm already.
32
33#[must_use]
34pub fn timestamps_tz(t: TableCreateStatement) -> TableCreateStatement {
35    let mut t = t;
36    t.col(timestamp_with_time_zone(GeneralIds::CreatedAt).default(Expr::current_timestamp()))
37        .col(timestamp_with_time_zone(GeneralIds::UpdatedAt).default(Expr::current_timestamp()));
38    t.take()
39}
40
41/// Create a nullable timestamptz column definition.
42pub fn timestamptz_null<T>(name: T) -> ColumnDef
43where
44    T: IntoIden,
45{
46    ColumnDef::new(name)
47        .timestamp_with_time_zone()
48        .null()
49        .take()
50}
51
52/// Create a non-nullable timestamptz column definition.
53pub fn timestamptz<T>(name: T) -> ColumnDef
54where
55    T: IntoIden,
56{
57    ColumnDef::new(name)
58        .timestamp_with_time_zone()
59        .not_null()
60        .take()
61}
62
63#[derive(Debug)]
64pub enum ColType {
65    PkAuto,
66    PkUuid,
67    CharLen(u32),
68    CharLenWithDefault(u32, char),
69    CharLenNull(u32),
70    CharLenUniq(u32),
71    Char,
72    CharWithDefault(char),
73    CharNull,
74    CharUniq,
75    StringLen(u32),
76    StringLenWithDefault(u32, String),
77    StringLenNull(u32),
78    StringLenUniq(u32),
79    String,
80    StringWithDefault(String),
81    StringNull,
82    StringUniq,
83    Text,
84    TextWithDefault(String),
85    TextNull,
86    TextUniq,
87    Integer,
88    IntegerWithDefault(i32),
89    IntegerNull,
90    IntegerUniq,
91    Unsigned,
92    UnsignedWithDefault(u32),
93    UnsignedNull,
94    UnsignedUniq,
95    SmallUnsigned,
96    SmallUnsignedWithDefault(u16),
97    SmallUnsignedNull,
98    SmallUnsignedUniq,
99    BigUnsigned,
100    BigUnsignedWithDefault(u64),
101    BigUnsignedNull,
102    BigUnsignedUniq,
103    SmallInteger,
104    SmallIntegerWithDefault(i16),
105    SmallIntegerNull,
106    SmallIntegerUniq,
107    BigInteger,
108    BigIntegerWithDefault(i64),
109    BigIntegerNull,
110    BigIntegerUniq,
111    Decimal,
112    DecimalWithDefault(f64),
113    DecimalNull,
114    DecimalUniq,
115    DecimalLen(u32, u32),
116    DecimalLenWithDefault(u32, u32, f64),
117    DecimalLenNull(u32, u32),
118    DecimalLenUniq(u32, u32),
119    Float,
120    FloatWithDefault(f32),
121    FloatNull,
122    FloatUniq,
123    Double,
124    DoubleWithDefault(f64),
125    DoubleNull,
126    DoubleUniq,
127    Boolean,
128    BooleanWithDefault(bool),
129    BooleanNull,
130    Date,
131    DateWithDefault(String),
132    DateNull,
133    DateUniq,
134    DateTime,
135    DateTimeWithDefault(String),
136    DateTimeNull,
137    DateTimeUniq,
138    Time,
139    TimeWithDefault(String),
140    TimeNull,
141    TimeUniq,
142    Interval(Option<PgInterval>, Option<u32>),
143    IntervalNull(Option<PgInterval>, Option<u32>),
144    IntervalUniq(Option<PgInterval>, Option<u32>),
145    Binary,
146    BinaryNull,
147    BinaryUniq,
148    BinaryLen(u32),
149    BinaryLenNull(u32),
150    BinaryLenUniq(u32),
151    VarBinary(u32),
152    VarBinaryNull(u32),
153    VarBinaryUniq(u32),
154    TimestampWithTimeZone,
155    TimestampWithTimeZoneWithDefault(String),
156    TimestampWithTimeZoneNull,
157    Json,
158    JsonNull,
159    JsonUniq,
160    JsonBinary,
161    JsonBinaryNull,
162    JsonBinaryUniq,
163    Blob,
164    BlobNull,
165    BlobUniq,
166    Money,
167    MoneyWithDefault(f64),
168    MoneyNull,
169    MoneyUniq,
170    Uuid,
171    UuidNull,
172    UuidUniq,
173    VarBitLen(u32),
174    VarBitLenNull(u32),
175    VarBitLenUniq(u32),
176    Array(ColumnType),
177    ArrayNull(ColumnType),
178    ArrayUniq(ColumnType),
179}
180
181pub enum ArrayColType {
182    String,
183    Int,
184    BigInt,
185    Float,
186    Double,
187    Bool,
188}
189
190impl ColType {
191    #[must_use]
192    #[allow(clippy::needless_pass_by_value)]
193    pub fn array(kind: ArrayColType) -> Self {
194        Self::Array(Self::array_col_type(&kind))
195    }
196
197    #[must_use]
198    #[allow(clippy::needless_pass_by_value)]
199    pub fn array_uniq(kind: ArrayColType) -> Self {
200        Self::ArrayUniq(Self::array_col_type(&kind))
201    }
202
203    #[must_use]
204    #[allow(clippy::needless_pass_by_value)]
205    pub fn array_null(kind: ArrayColType) -> Self {
206        Self::ArrayNull(Self::array_col_type(&kind))
207    }
208
209    fn array_col_type(kind: &ArrayColType) -> ColumnType {
210        match kind {
211            ArrayColType::String => ColumnType::string(None),
212            ArrayColType::Int => ColumnType::Integer,
213            ArrayColType::BigInt => ColumnType::BigInteger,
214            ArrayColType::Float => ColumnType::Float,
215            ArrayColType::Double => ColumnType::Double,
216            ArrayColType::Bool => ColumnType::Boolean,
217        }
218    }
219}
220
221impl ColType {
222    #[allow(clippy::too_many_lines)]
223    fn to_def(&self, name: impl IntoIden) -> ColumnDef {
224        match self {
225            Self::PkAuto => big_integer(name).auto_increment().primary_key().take(),
226            Self::PkUuid => pk_uuid(name),
227            Self::CharLen(len) => char_len(name, *len),
228            Self::CharLenNull(len) => char_len_null(name, *len),
229            Self::CharLenUniq(len) => char_len_uniq(name, *len),
230            Self::Char => char(name),
231            Self::CharNull => char_null(name),
232            Self::CharUniq => char_uniq(name),
233            Self::StringLen(len) => string_len(name, *len),
234            Self::StringLenNull(len) => string_len_null(name, *len),
235            Self::StringLenUniq(len) => string_len_uniq(name, *len),
236            Self::String => string(name),
237            Self::StringNull => string_null(name),
238            Self::StringUniq => string_uniq(name),
239            Self::Text => text(name),
240            Self::TextNull => text_null(name),
241            Self::TextUniq => text_uniq(name),
242            Self::Integer => integer(name),
243            Self::IntegerNull => integer_null(name),
244            Self::IntegerUniq => integer_uniq(name),
245            // Self::TinyInteger => tiny_integer(name),
246            // Self::TinyIntegerNull => tiny_integer_null(name),
247            // Self::TinyIntegerUniq => tiny_integer_uniq(name),
248            Self::Unsigned => unsigned(name),
249            Self::UnsignedNull => unsigned_null(name),
250            Self::UnsignedUniq => unsigned_uniq(name),
251            // Self::TinyUnsigned => tiny_unsigned(name),
252            // Self::TinyUnsignedNull => tiny_unsigned_null(name),
253            // Self::TinyUnsignedUniq => tiny_unsigned_uniq(name),
254            Self::SmallUnsigned => small_unsigned(name),
255            Self::SmallUnsignedNull => small_unsigned_null(name),
256            Self::SmallUnsignedUniq => small_unsigned_uniq(name),
257            Self::BigUnsigned => big_unsigned(name),
258            Self::BigUnsignedNull => big_unsigned_null(name),
259            Self::BigUnsignedUniq => big_unsigned_uniq(name),
260            Self::SmallInteger => small_integer(name),
261            Self::SmallIntegerNull => small_integer_null(name),
262            Self::SmallIntegerUniq => small_integer_uniq(name),
263            Self::BigInteger => big_integer(name),
264            Self::BigIntegerNull => big_integer_null(name),
265            Self::BigIntegerUniq => big_integer_uniq(name),
266            Self::Decimal => decimal(name),
267            Self::DecimalNull => decimal_null(name),
268            Self::DecimalUniq => decimal_uniq(name),
269            Self::DecimalLen(precision, scale) => decimal_len(name, *precision, *scale),
270            Self::DecimalLenNull(precision, scale) => decimal_len_null(name, *precision, *scale),
271            Self::DecimalLenUniq(precision, scale) => decimal_len_uniq(name, *precision, *scale),
272            Self::Float => float(name),
273            Self::FloatNull => float_null(name),
274            Self::FloatUniq => float_uniq(name),
275            Self::Double => double(name),
276            Self::DoubleNull => double_null(name),
277            Self::DoubleUniq => double_uniq(name),
278            Self::Boolean => boolean(name),
279            Self::BooleanNull => boolean_null(name),
280            // Self::Timestamp => timestamp(name),
281            // Self::TimestampNull => timestamp_null(name),
282            // Self::TimestampUniq => timestamp_uniq(name),
283            Self::Date => date(name),
284            Self::DateNull => date_null(name),
285            Self::DateUniq => date_uniq(name),
286            Self::DateTime => date_time(name),
287            Self::DateTimeNull => date_time_null(name),
288            Self::DateTimeUniq => date_time_uniq(name),
289            Self::Time => time(name),
290            Self::TimeNull => time_null(name),
291            Self::TimeUniq => time_uniq(name),
292            Self::Interval(ival, prec) => interval(name, ival.clone(), *prec),
293            Self::IntervalNull(ival, prec) => interval_null(name, ival.clone(), *prec),
294            Self::IntervalUniq(ival, prec) => interval_uniq(name, ival.clone(), *prec),
295            Self::Binary => binary(name),
296            Self::BinaryNull => binary_null(name),
297            Self::BinaryUniq => binary_uniq(name),
298            Self::BinaryLen(len) => binary_len(name, *len),
299            Self::BinaryLenNull(len) => binary_len_null(name, *len),
300            Self::BinaryLenUniq(len) => binary_len_uniq(name, *len),
301            Self::VarBinary(len) => var_binary(name, *len),
302            Self::VarBinaryNull(len) => var_binary_null(name, *len),
303            Self::VarBinaryUniq(len) => var_binary_uniq(name, *len),
304            Self::TimestampWithTimeZone => timestamptz(name),
305            Self::TimestampWithTimeZoneNull => timestamptz_null(name),
306            Self::Json => json(name),
307            Self::JsonNull => json_null(name),
308            Self::JsonUniq => json_uniq(name),
309            Self::JsonBinary => json_binary(name),
310            Self::JsonBinaryNull => json_binary_null(name),
311            Self::JsonBinaryUniq => json_binary_uniq(name),
312            Self::Blob => blob(name),
313            Self::BlobNull => blob_null(name),
314            Self::BlobUniq => blob_uniq(name),
315            Self::Money => money(name),
316            Self::MoneyNull => money_null(name),
317            Self::MoneyUniq => money_uniq(name),
318            Self::Uuid => uuid(name),
319            Self::UuidNull => uuid_null(name),
320            Self::UuidUniq => uuid_uniq(name),
321            Self::VarBitLen(len) => varbit(name, *len),
322            Self::VarBitLenNull(len) => varbit_null(name, *len),
323            Self::VarBitLenUniq(len) => varbit_uniq(name, *len),
324            Self::Array(kind) => array(name, kind.clone()),
325            Self::ArrayNull(kind) => array_null(name, kind.clone()),
326            Self::ArrayUniq(kind) => array_uniq(name, kind.clone()),
327            // defaults
328            Self::MoneyWithDefault(v) => money(name).default(*v).take(),
329            Self::IntegerWithDefault(v) => integer(name).default(*v).take(),
330            Self::UnsignedWithDefault(v) => unsigned(name).default(*v).take(),
331            Self::SmallUnsignedWithDefault(v) => small_unsigned(name).default(*v).take(),
332            Self::BigUnsignedWithDefault(v) => big_unsigned(name).default(*v).take(),
333            Self::SmallIntegerWithDefault(v) => small_integer(name).default(*v).take(),
334            Self::BigIntegerWithDefault(v) => big_integer(name).default(*v).take(),
335            Self::DecimalWithDefault(v) => decimal(name).default(*v).take(),
336            Self::DecimalLenWithDefault(p, s, v) => decimal_len(name, *p, *s).default(*v).take(),
337            Self::FloatWithDefault(v) => float(name).default(*v).take(),
338            Self::DoubleWithDefault(v) => double(name).default(*v).take(),
339            Self::BooleanWithDefault(v) => boolean(name).default(*v).take(),
340            Self::DateWithDefault(v) => date(name).default(v.clone()).take(),
341            Self::DateTimeWithDefault(v) => date_time(name).default(v.clone()).take(),
342            Self::TimeWithDefault(v) => time(name).default(v.clone()).take(),
343            Self::TimestampWithTimeZoneWithDefault(v) => {
344                timestamptz(name).default(v.clone()).take()
345            }
346            Self::CharWithDefault(v) => char(name).default(*v).take(),
347            Self::CharLenWithDefault(len, v) => char_len(name, *len).default(*v).take(),
348            Self::StringWithDefault(v) => string(name).default(v.clone()).take(),
349            Self::StringLenWithDefault(len, v) => string_len(name, *len).default(v.clone()).take(),
350            Self::TextWithDefault(v) => text(name).default(v.clone()).take(),
351        }
352    }
353}
354
355///
356/// Create a table.
357/// ```ignore
358/// create_table(m, "movies", vec![
359///     ("title", ColType::String)
360/// ],
361/// vec![]
362/// )
363/// .await;
364/// ```
365///
366/// ```shell
367/// loco g migration CreateMovies title:string user:references
368/// loco g migration CreateMovies title:string user:references:admin_id
369/// ```
370/// # Errors
371/// fails when it fails
372pub async fn create_table(
373    m: &SchemaManager<'_>,
374    table: &str,
375    cols: &[(&str, ColType)],
376    refs: &[(&str, &str)], // [(from_tbl, to_tbl), ...]
377) -> Result<(), DbErr> {
378    create_table_impl(m, table, cols, refs, false).await
379}
380
381///
382/// Create a join table. A join table has a composite primary key.
383/// ```ignore
384/// create_join_table(m, "movies", vec![
385///     ("title", ColType::String)
386/// ],
387/// vec![]
388/// )
389/// .await;
390/// ```
391///
392/// # Errors
393/// fails when it fails
394pub async fn create_join_table(
395    m: &SchemaManager<'_>,
396    table: &str,
397    cols: &[(&str, ColType)],
398    refs: &[(&str, &str)], // [(from_tbl, to_tbl), ...]
399) -> Result<(), DbErr> {
400    create_table_impl(m, table, cols, refs, true).await
401}
402
403async fn create_table_impl(
404    m: &SchemaManager<'_>,
405    table: &str,
406    cols: &[(&str, ColType)],
407    refs: &[(&str, &str)], // [(from_tbl, to_tbl), ...]
408    is_join: bool,
409) -> Result<(), DbErr> {
410    let nz_table = normalize_table(table);
411
412    let mut stmt = table_auto_tz(Alias::new(&nz_table));
413    if is_join {
414        let mut idx = Index::create();
415        idx.name(format!("idx-{nz_table}-refs-pk"))
416            .table(Alias::new(&nz_table));
417
418        for (from_tbl, ref_name) in refs {
419            let nz_from_table = normalize_table(from_tbl);
420            // in movies, user:references, creates a `user_id` field or what ever in
421            // `ref_name` if given
422            let nz_ref_name = if ref_name.is_empty() {
423                reference_id(&nz_from_table)
424            } else {
425                (*ref_name).to_string()
426            };
427            idx.col(Alias::new(nz_ref_name));
428        }
429        stmt.primary_key(&mut idx);
430    }
431
432    for (name, atype) in cols {
433        stmt.col(atype.to_def(Alias::new(*name)));
434    }
435
436    // user, None
437    // users, None
438    // user, admin_id
439    for (from_tbl, ref_name) in refs {
440        let nz_from_table = normalize_table(from_tbl);
441        // in movies, user:references, creates a `user_id` field or what ever in
442        // `ref_name` if given
443        let nz_ref_name = if ref_name.is_empty() {
444            reference_id(&nz_from_table)
445        } else {
446            (*ref_name).to_string()
447        };
448        // user -> users
449
450        // create user_id in movies
451        stmt.col(ColType::BigInteger.to_def(Alias::new(&nz_ref_name)));
452        // link user_id in movies to users#id
453        stmt.foreign_key(
454            sea_query::ForeignKey::create()
455                // fk-movies-user_id-to-users
456                .name(format!("fk-{nz_from_table}-{nz_ref_name}-to-{nz_table}")) // XXX fix
457                // from movies#user_id (user_id is just created now)
458                .from(Alias::new(&nz_table), Alias::new(&nz_ref_name)) // XXX fix
459                // to users#id
460                .to(Alias::new(nz_from_table), Alias::new("id")) // XXX fix
461                .on_delete(ForeignKeyAction::Cascade)
462                .on_update(ForeignKeyAction::Cascade),
463        );
464    }
465    m.create_table(stmt).await?;
466    Ok(())
467}
468
469/// person -> people, movies -> movie
470fn normalize_table(table: &str) -> String {
471    cruet::to_plural(table).to_snake_case()
472}
473
474/// users -> `user_id`
475fn reference_id(totbl: &str) -> String {
476    format!("{}_id", cruet::to_singular(totbl).to_snake_case())
477}
478
479///
480/// Add a column to a table with a column type.
481///
482/// ```ignore
483/// add_column(m, "movies", "title", ColType::String).await;
484/// ```
485/// # Errors
486/// fails when it fails
487pub async fn add_column(
488    m: &SchemaManager<'_>,
489    table: &str,
490    name: &str,
491    atype: ColType,
492) -> Result<(), DbErr> {
493    let nz_table = normalize_table(table);
494    m.alter_table(
495        alter(Alias::new(nz_table))
496            .add_column(atype.to_def(Alias::new(name)))
497            .to_owned(),
498    )
499    .await?;
500    Ok(())
501}
502
503///
504/// Drop a column from a table.
505///
506/// ```ignore
507/// drop_column(m, "movies", "title").await;
508/// ```
509/// # Errors
510/// fails when it fails
511pub async fn remove_column(m: &SchemaManager<'_>, table: &str, name: &str) -> Result<(), DbErr> {
512    let nz_table = normalize_table(table);
513    m.alter_table(
514        alter(Alias::new(nz_table))
515            .drop_column(Alias::new(name))
516            .to_owned(),
517    )
518    .await?;
519    Ok(())
520}
521
522///
523/// Adds a reference. Reads "movies belongs-to users":
524/// ```ignore
525/// add_reference(m, "movies", "users").await;
526/// ```
527///
528/// # Errors
529/// fails when it fails
530pub async fn add_reference(
531    m: &SchemaManager<'_>,
532    fromtbl: &str,
533    totbl: &str,
534    refname: &str,
535) -> Result<(), DbErr> {
536    // movies
537    let nz_fromtbl = normalize_table(fromtbl);
538    // users
539    let nz_totbl = normalize_table(totbl);
540    // user_id
541    let nz_ref_name = if refname.is_empty() {
542        reference_id(totbl)
543    } else {
544        refname.to_string()
545    };
546    let bk = m.get_database_backend();
547    let col = ColType::BigInteger.to_def(Alias::new(&nz_ref_name));
548    let fk = TableForeignKey::new()
549        // fk-movies-user_id-to-users
550        .name(format!("fk-{nz_fromtbl}-{nz_ref_name}-to-{nz_totbl}"))
551        // from movies#user_id
552        .from_tbl(Alias::new(&nz_fromtbl))
553        .from_col(Alias::new(&nz_ref_name)) // xxx fix
554        // to users#id
555        .to_tbl(Alias::new(nz_totbl))
556        .to_col(Alias::new("id"))
557        .on_delete(ForeignKeyAction::Cascade)
558        .on_update(ForeignKeyAction::Cascade)
559        .to_owned();
560    match bk {
561        sea_orm::DatabaseBackend::MySql | sea_orm::DatabaseBackend::Postgres => {
562            // from movies to users -> movies#user_id to users#id
563            m.alter_table(
564                alter(Alias::new(&nz_fromtbl))
565                    // add movies#user_id (the user_id column is new)
566                    .add_column(col.clone()) // XXX fix, totbl_id
567                    // add fk on movies#user_id
568                    .add_foreign_key(&fk)
569                    .to_owned(),
570            )
571            .await?;
572        }
573        sea_orm::DatabaseBackend::Sqlite => {
574            // from movies to users -> movies#user_id to users#id
575            m.alter_table(
576                alter(Alias::new(&nz_fromtbl))
577                    // add movies#user_id (the user_id column is new)
578                    .add_column(col.clone()) // XXX fix, totbl_id
579                    .to_owned(),
580            )
581            .await?;
582            // Per Rails 5.2, adding FK to existing table does nothing because
583            // sqlite will not allow it. FK in sqlite are applied only on table
584            // creation. more: https://www.bigbinary.com/blog/rails-6-adds-add_foreign_key-and-remove_foreign_key-for-sqlite3
585            // we comment it below leaving it for academic purposes.
586            /*
587                m.alter_table(
588                    alter(Alias::new(&nz_fromtbl))
589                        // add fk on movies#user_id
590                        .add_foreign_key(&fk)
591                        .to_owned(),
592                )
593                .await?;
594            */
595        }
596    }
597    Ok(())
598}
599
600///
601/// Removes a reference by constructing its name from the table names.
602/// ```ignore
603/// remove_reference(m, "movies", "users").await;
604/// ```
605///
606/// # Errors
607/// fails when it fails
608pub async fn remove_reference(
609    m: &SchemaManager<'_>,
610    fromtbl: &str,
611    totbl: &str,
612    refname: &str,
613) -> Result<(), DbErr> {
614    // movies
615    let nz_fromtbl = normalize_table(fromtbl);
616    // users
617    let nz_totbl = normalize_table(totbl);
618    // user_id
619    let nz_ref_name = if refname.is_empty() {
620        reference_id(totbl)
621    } else {
622        refname.to_string()
623    };
624    let bk = m.get_database_backend();
625    match bk {
626        sea_orm::DatabaseBackend::MySql | sea_orm::DatabaseBackend::Postgres => {
627            // from movies to users -> movies#user_id to users#id
628            m.alter_table(
629                alter(Alias::new(&nz_fromtbl))
630                    .drop_foreign_key(
631                        // fk-movies-user_id-to-users
632                        Alias::new(format!("fk-{nz_fromtbl}-{nz_ref_name}-to-{nz_totbl}")),
633                    )
634                    .to_owned(),
635            )
636            .await?;
637        }
638        sea_orm::DatabaseBackend::Sqlite => {
639            // Per Rails 5.2, removing FK on existing table does nothing because
640            // sqlite will not allow it.
641            // more: https://www.bigbinary.com/blog/rails-6-adds-add_foreign_key-and-remove_foreign_key-for-sqlite3
642        }
643    }
644    Ok(())
645}
646
647///
648/// Drop a table
649/// ```ignore
650/// drop_table(m, "movies").await;
651/// ```
652///
653/// # Errors
654/// fails when it fails
655pub async fn drop_table(m: &SchemaManager<'_>, table: &str) -> Result<(), DbErr> {
656    let nz_table = normalize_table(table);
657    m.drop_table(Table::drop().table(Alias::new(nz_table)).to_owned())
658        .await
659}
by_loco/schema.rs

by_loco/
schema.rs
