4d9d58da860409e2086b2eeb2c78010bef97620c / migration / src / m20261018_000005_create_tickets.rs
Raw
use sea_orm_migration::prelude::*;
#[derive(DeriveMigrationName)]
pub struct Migration;
#[async_trait::async_trait]
impl MigrationTrait for Migration {
async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
// ticket_labels: labels/tags created by repo owner (defaults per repo)
manager
.create_table(
Table::create()
.table(TicketLabels::Table)
.if_not_exists()
.col(
ColumnDef::new(TicketLabels::Id)
.integer()
.not_null()
.auto_increment()
.primary_key(),
)
.col(
ColumnDef::new(TicketLabels::RepositoryId)
.integer()
.not_null(),
)
.col(ColumnDef::new(TicketLabels::Name).string_len(64).not_null())
.col(
ColumnDef::new(TicketLabels::CreatedAt)
.timestamp_with_time_zone()
.default(Expr::current_timestamp())
.not_null(),
)
.foreign_key(
ForeignKey::create()
.name("fk_ticket_labels_repository_id")
.from(TicketLabels::Table, TicketLabels::RepositoryId)
.to(Repositories::Table, Repositories::Id)
.on_delete(ForeignKeyAction::Cascade),
)
.index(
Index::create()
.name("index_ticket_labels_on_repository_id_and_name")
.col(TicketLabels::RepositoryId)
.col(TicketLabels::Name)
.unique(),
)
.to_owned(),
)
.await?;
// tickets: issues/tickets
manager
.create_table(
Table::create()
.table(Tickets::Table)
.if_not_exists()
.col(
ColumnDef::new(Tickets::Id)
.integer()
.not_null()
.auto_increment()
.primary_key(),
)
.col(ColumnDef::new(Tickets::Uuid).uuid().not_null().unique_key())
.col(ColumnDef::new(Tickets::RepositoryId).integer().not_null())
.col(ColumnDef::new(Tickets::CreatorId).integer().not_null())
.col(ColumnDef::new(Tickets::Number).integer().not_null())
.col(ColumnDef::new(Tickets::Summary).string_len(255).not_null())
.col(ColumnDef::new(Tickets::Description).text())
.col(
ColumnDef::new(Tickets::CreatedAt)
.timestamp_with_time_zone()
.default(Expr::current_timestamp())
.not_null(),
)
.col(
ColumnDef::new(Tickets::UpdatedAt)
.timestamp_with_time_zone()
.default(Expr::current_timestamp())
.not_null(),
)
.foreign_key(
ForeignKey::create()
.name("fk_tickets_repository_id")
.from(Tickets::Table, Tickets::RepositoryId)
.to(Repositories::Table, Repositories::Id)
.on_delete(ForeignKeyAction::Cascade),
)
.foreign_key(
ForeignKey::create()
.name("fk_tickets_creator_id")
.from(Tickets::Table, Tickets::CreatorId)
.to(Users::Table, Users::Id)
.on_delete(ForeignKeyAction::SetNull),
)
.index(
Index::create()
.name("index_tickets_on_repository_id_and_number")
.col(Tickets::RepositoryId)
.col(Tickets::Number)
.unique(),
)
.to_owned(),
)
.await?;
// ticket_label_assignments: many-to-many
manager
.create_table(
Table::create()
.table(TicketLabelAssignments::Table)
.if_not_exists()
.col(
ColumnDef::new(TicketLabelAssignments::Id)
.integer()
.not_null()
.auto_increment()
.primary_key(),
)
.col(
ColumnDef::new(TicketLabelAssignments::TicketId)
.integer()
.not_null(),
)
.col(
ColumnDef::new(TicketLabelAssignments::LabelId)
.integer()
.not_null(),
)
.col(
ColumnDef::new(TicketLabelAssignments::CreatedAt)
.timestamp_with_time_zone()
.default(Expr::current_timestamp())
.not_null(),
)
.foreign_key(
ForeignKey::create()
.name("fk_ticket_label_assignments_ticket_id")
.from(
TicketLabelAssignments::Table,
TicketLabelAssignments::TicketId,
)
.to(Tickets::Table, Tickets::Id)
.on_delete(ForeignKeyAction::Cascade),
)
.foreign_key(
ForeignKey::create()
.name("fk_ticket_label_assignments_label_id")
.from(
TicketLabelAssignments::Table,
TicketLabelAssignments::LabelId,
)
.to(TicketLabels::Table, TicketLabels::Id)
.on_delete(ForeignKeyAction::Cascade),
)
.index(
Index::create()
.name("index_ticket_label_assignments_on_ticket_id_and_label_id")
.col(TicketLabelAssignments::TicketId)
.col(TicketLabelAssignments::LabelId)
.unique(),
)
.to_owned(),
)
.await?;
// attachments
manager
.create_table(
Table::create()
.table(TicketAttachments::Table)
.if_not_exists()
.col(
ColumnDef::new(TicketAttachments::Id)
.integer()
.not_null()
.auto_increment()
.primary_key(),
)
.col(
ColumnDef::new(TicketAttachments::Uuid)
.uuid()
.not_null()
.unique_key(),
)
.col(
ColumnDef::new(TicketAttachments::TicketId)
.integer()
.not_null(),
)
.col(
ColumnDef::new(TicketAttachments::Filename)
.string_len(255)
.not_null(),
)
.col(
ColumnDef::new(TicketAttachments::ContentType)
.string_len(255)
.not_null(),
)
.col(ColumnDef::new(TicketAttachments::Size).integer().not_null())
.col(ColumnDef::new(TicketAttachments::Data).blob().not_null())
.col(
ColumnDef::new(TicketAttachments::CreatedAt)
.timestamp_with_time_zone()
.default(Expr::current_timestamp())
.not_null(),
)
.foreign_key(
ForeignKey::create()
.name("fk_ticket_attachments_ticket_id")
.from(TicketAttachments::Table, TicketAttachments::TicketId)
.to(Tickets::Table, Tickets::Id)
.on_delete(ForeignKeyAction::Cascade),
)
.to_owned(),
)
.await?;
manager
.create_index(
Index::create()
.name("index_ticket_attachments_on_ticket_id_and_created_at")
.table(TicketAttachments::Table)
.col(TicketAttachments::TicketId)
.col(TicketAttachments::CreatedAt)
.to_owned(),
)
.await?;
// The search index over tickets, in SQLite's own words: an FTS5 table
// over the two columns a ticket is searched by. It reads its rows from
// `tickets` rather than holding copies (`content=`), so the triggers
// below only have to say when a row changed - a ticket that is edited
// or removed is searchable, or unsearchable, the moment the row lands,
// and no code path can forget to keep the two in step.
manager
.get_connection()
.execute_unprepared(
"CREATE VIRTUAL TABLE IF NOT EXISTS ticket_search \
USING fts5(summary, description, content='tickets', content_rowid='id', \
tokenize='unicode61')",
)
.await?;
manager
.get_connection()
.execute_unprepared(
"CREATE TRIGGER IF NOT EXISTS tickets_search_after_insert \
AFTER INSERT ON tickets BEGIN \
INSERT INTO ticket_search(rowid, summary, description) \
VALUES (new.id, new.summary, new.description); \
END",
)
.await?;
manager
.get_connection()
.execute_unprepared(
"CREATE TRIGGER IF NOT EXISTS tickets_search_after_update \
AFTER UPDATE ON tickets BEGIN \
INSERT INTO ticket_search(ticket_search, rowid, summary, description) \
VALUES ('delete', old.id, old.summary, old.description); \
INSERT INTO ticket_search(rowid, summary, description) \
VALUES (new.id, new.summary, new.description); \
END",
)
.await?;
manager
.get_connection()
.execute_unprepared(
"CREATE TRIGGER IF NOT EXISTS tickets_search_after_delete \
AFTER DELETE ON tickets BEGIN \
INSERT INTO ticket_search(ticket_search, rowid, summary, description) \
VALUES ('delete', old.id, old.summary, old.description); \
END",
)
.await?;
Ok(())
}
async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
manager
.drop_table(Table::drop().table(TicketAttachments::Table).to_owned())
.await?;
manager
.drop_table(
Table::drop()
.table(TicketLabelAssignments::Table)
.to_owned(),
)
.await?;
manager
.drop_table(Table::drop().table(Tickets::Table).to_owned())
.await?;
manager
.drop_table(Table::drop().table(TicketLabels::Table).to_owned())
.await?;
Ok(())
}
}
#[derive(Iden)]
enum Repositories {
Table,
Id,
}
#[derive(Iden)]
enum Users {
Table,
Id,
}
#[derive(Iden)]
enum Tickets {
Table,
Id,
Uuid,
RepositoryId,
CreatorId,
Number,
Summary,
Description,
CreatedAt,
UpdatedAt,
}
#[derive(Iden)]
enum TicketLabels {
Table,
Id,
RepositoryId,
Name,
CreatedAt,
}
#[derive(Iden)]
enum TicketLabelAssignments {
Table,
Id,
TicketId,
LabelId,
CreatedAt,
}
#[derive(Iden)]
enum TicketAttachments {
Table,
Id,
Uuid,
TicketId,
Filename,
ContentType,
Size,
Data,
CreatedAt,
}