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> {
// Reference to the Users table from the previous migration
let users_table = Table::create()
.table(Alias::new("users"))
.col(ColumnDef::new(Alias::new("id")).big_integer())
.to_owned();
// Create uploads table
manager
.create_table(
Table::create()
.table(Uploads::Table)
.if_not_exists()
.col(
ColumnDef::new(Uploads::Id)
.big_integer()
.not_null()
.auto_increment()
.primary_key(),
)
.col(
ColumnDef::new(Uploads::UserId)
.big_integer()
.not_null(),
)
.col(
ColumnDef::new(Uploads::FileNameOriginal)
.string_len(512)
.not_null(),
)
.col(
ColumnDef::new(Uploads::FileNameGenerated)
.string_len(512)
.not_null(),
)
.col(
ColumnDef::new(Uploads::FileSizeBytes)
.big_integer()
.not_null(),
)
.col(
ColumnDef::new(Uploads::ContentType)
.string_len(100)
.null(),
)
.col(
ColumnDef::new(Uploads::FileHash)
.string_len(64)
.null(),
)
.col(
ColumnDef::new(Uploads::ItemName)
.string_len(255)
.null(),
)
.col(
ColumnDef::new(Uploads::ItemDescription)
.text()
.null(),
)
.col(
ColumnDef::new(Uploads::ItemPath)
.text()
.not_null(),
)
.col(
ColumnDef::new(Uploads::StorageBackend)
.string_len(50)
.not_null()
.default("local"),
)
.col(
ColumnDef::new(Uploads::ProcessingStatus)
.string_len(20)
.not_null()
.default("pending"),
)
.col(
ColumnDef::new(Uploads::ProcessingError)
.text()
.null(),
)
.col(
ColumnDef::new(Uploads::DateUploaded)
.timestamp_with_time_zone()
.not_null()
.default(Expr::current_timestamp()),
)
.col(
ColumnDef::new(Uploads::DateUpdated)
.timestamp_with_time_zone()
.not_null()
.default(Expr::current_timestamp()),
)
.foreign_key(
ForeignKey::create()
.name("fk_uploads_user_id")
.from(Uploads::Table, Uploads::UserId)
.to(Alias::new("users"), Alias::new("id"))
.on_delete(ForeignKeyAction::Cascade)
.on_update(ForeignKeyAction::Cascade),
)
.to_owned(),
)
.await?;
// Create tags table
manager
.create_table(
Table::create()
.table(Tags::Table)
.if_not_exists()
.col(
ColumnDef::new(Tags::Id)
.big_integer()
.not_null()
.auto_increment()
.primary_key(),
)
.col(
ColumnDef::new(Tags::Namespace)
.string_len(50)
.null(),
)
.col(
ColumnDef::new(Tags::TagValue)
.string_len(255)
.not_null(),
)
.col(
ColumnDef::new(Tags::CreatedAt)
.timestamp_with_time_zone()
.not_null()
.default(Expr::current_timestamp()),
)
.to_owned(),
)
.await?;
// Create upload_tags table
manager
.create_table(
Table::create()
.table(UploadTags::Table)
.if_not_exists()
.col(
ColumnDef::new(UploadTags::Id)
.big_integer()
.not_null()
.auto_increment()
.primary_key(),
)
.col(
ColumnDef::new(UploadTags::UploadId)
.big_integer()
.not_null(),
)
.col(
ColumnDef::new(UploadTags::TagId)
.big_integer()
.not_null(),
)
.col(
ColumnDef::new(UploadTags::Confidence)
.float()
.null(),
)
.col(
ColumnDef::new(UploadTags::CreatedAt)
.timestamp_with_time_zone()
.not_null()
.default(Expr::current_timestamp()),
)
.foreign_key(
ForeignKey::create()
.name("fk_upload_tags_upload_id")
.from(UploadTags::Table, UploadTags::UploadId)
.to(Uploads::Table, Uploads::Id)
.on_delete(ForeignKeyAction::Cascade)
.on_update(ForeignKeyAction::Cascade),
)
.foreign_key(
ForeignKey::create()
.name("fk_upload_tags_tag_id")
.from(UploadTags::Table, UploadTags::TagId)
.to(Tags::Table, Tags::Id)
.on_delete(ForeignKeyAction::Cascade)
.on_update(ForeignKeyAction::Cascade),
)
.to_owned(),
)
.await?;
// Create ocr_results table
manager
.create_table(
Table::create()
.table(OcrResults::Table)
.if_not_exists()
.col(
ColumnDef::new(OcrResults::Id)
.big_integer()
.not_null()
.auto_increment()
.primary_key(),
)
.col(
ColumnDef::new(OcrResults::UploadId)
.big_integer()
.not_null(),
)
.col(
ColumnDef::new(OcrResults::ExtractedText)
.text()
.null(),
)
.col(
ColumnDef::new(OcrResults::Confidence)
.float()
.null(),
)
.col(
ColumnDef::new(OcrResults::ProcessingEngine)
.string_len(50)
.null(),
)
.col(
ColumnDef::new(OcrResults::ProcessedAt)
.timestamp_with_time_zone()
.not_null()
.default(Expr::current_timestamp()),
)
.foreign_key(
ForeignKey::create()
.name("fk_ocr_results_upload_id")
.from(OcrResults::Table, OcrResults::UploadId)
.to(Uploads::Table, Uploads::Id)
.on_delete(ForeignKeyAction::Cascade)
.on_update(ForeignKeyAction::Cascade),
)
.to_owned(),
)
.await?;
// Create ai_descriptions table
manager
.create_table(
Table::create()
.table(AiDescriptions::Table)
.if_not_exists()
.col(
ColumnDef::new(AiDescriptions::Id)
.big_integer()
.not_null()
.auto_increment()
.primary_key(),
)
.col(
ColumnDef::new(AiDescriptions::UploadId)
.big_integer()
.not_null(),
)
.col(
ColumnDef::new(AiDescriptions::Description)
.text()
.not_null(),
)
.col(
ColumnDef::new(AiDescriptions::ModelUsed)
.string_len(100)
.null(),
)
.col(
ColumnDef::new(AiDescriptions::Confidence)
.float()
.null(),
)
.col(
ColumnDef::new(AiDescriptions::ProcessedAt)
.timestamp_with_time_zone()
.not_null()
.default(Expr::current_timestamp()),
)
.foreign_key(
ForeignKey::create()
.name("fk_ai_descriptions_upload_id")
.from(AiDescriptions::Table, AiDescriptions::UploadId)
.to(Uploads::Table, Uploads::Id)
.on_delete(ForeignKeyAction::Cascade)
.on_update(ForeignKeyAction::Cascade),
)
.to_owned(),
)
.await?;
// Create similar_uploads table
manager
.create_table(
Table::create()
.table(SimilarUploads::Table)
.if_not_exists()
.col(
ColumnDef::new(SimilarUploads::Id)
.big_integer()
.not_null()
.auto_increment()
.primary_key(),
)
.col(
ColumnDef::new(SimilarUploads::UploadId1)
.big_integer()
.not_null(),
)
.col(
ColumnDef::new(SimilarUploads::UploadId2)
.big_integer()
.not_null(),
)
.col(
ColumnDef::new(SimilarUploads::SimilarityScore)
.float()
.not_null(),
)
.col(
ColumnDef::new(SimilarUploads::DetectionMethod)
.string_len(50)
.null(),
)
.col(
ColumnDef::new(SimilarUploads::CreatedAt)
.timestamp_with_time_zone()
.not_null()
.default(Expr::current_timestamp()),
)
.foreign_key(
ForeignKey::create()
.name("fk_similar_uploads_upload_id_1")
.from(SimilarUploads::Table, SimilarUploads::UploadId1)
.to(Uploads::Table, Uploads::Id)
.on_delete(ForeignKeyAction::Cascade)
.on_update(ForeignKeyAction::Cascade),
)
.foreign_key(
ForeignKey::create()
.name("fk_similar_uploads_upload_id_2")
.from(SimilarUploads::Table, SimilarUploads::UploadId2)
.to(Uploads::Table, Uploads::Id)
.on_delete(ForeignKeyAction::Cascade)
.on_update(ForeignKeyAction::Cascade),
)
.to_owned(),
)
.await?;
// Create indexes for uploads
manager
.create_index(
Index::create()
.name("idx_uploads_user_id")
.table(Uploads::Table)
.col(Uploads::UserId)
.to_owned(),
)
.await?;
manager
.create_index(
Index::create()
.name("idx_uploads_file_hash")
.table(Uploads::Table)
.col(Uploads::FileHash)
.to_owned(),
)
.await?;
manager
.create_index(
Index::create()
.name("idx_uploads_date_uploaded")
.table(Uploads::Table)
.col(Uploads::DateUploaded)
.to_owned(),
)
.await?;
manager
.create_index(
Index::create()
.name("idx_uploads_processing_status")
.table(Uploads::Table)
.col(Uploads::ProcessingStatus)
.to_owned(),
)
.await?;
manager
.create_index(
Index::create()
.name("idx_uploads_content_type")
.table(Uploads::Table)
.col(Uploads::ContentType)
.to_owned(),
)
.await?;
// Create unique constraint for file hash per user
manager
.create_index(
Index::create()
.name("unique_file_hash_user")
.table(Uploads::Table)
.col(Uploads::FileHash)
.col(Uploads::UserId)
.unique()
.to_owned(),
)
.await?;
// Create indexes for tags
manager
.create_index(
Index::create()
.name("idx_tags_namespace")
.table(Tags::Table)
.col(Tags::Namespace)
.to_owned(),
)
.await?;
manager
.create_index(
Index::create()
.name("idx_tags_value")
.table(Tags::Table)
.col(Tags::TagValue)
.to_owned(),
)
.await?;
manager
.create_index(
Index::create()
.name("unique_namespace_tag")
.table(Tags::Table)
.col(Tags::Namespace)
.col(Tags::TagValue)
.unique()
.to_owned(),
)
.await?;
// Create indexes for upload_tags
manager
.create_index(
Index::create()
.name("idx_upload_tags_upload_id")
.table(UploadTags::Table)
.col(UploadTags::UploadId)
.to_owned(),
)
.await?;
manager
.create_index(
Index::create()
.name("idx_upload_tags_tag_id")
.table(UploadTags::Table)
.col(UploadTags::TagId)
.to_owned(),
)
.await?;
manager
.create_index(
Index::create()
.name("unique_upload_tag")
.table(UploadTags::Table)
.col(UploadTags::UploadId)
.col(UploadTags::TagId)
.unique()
.to_owned(),
)
.await?;
// Create indexes for other tables
manager
.create_index(
Index::create()
.name("idx_ocr_results_upload_id")
.table(OcrResults::Table)
.col(OcrResults::UploadId)
.to_owned(),
)
.await?;
manager
.create_index(
Index::create()
.name("idx_ai_descriptions_upload_id")
.table(AiDescriptions::Table)
.col(AiDescriptions::UploadId)
.to_owned(),
)
.await?;
manager
.create_index(
Index::create()
.name("idx_similar_uploads_upload1")
.table(SimilarUploads::Table)
.col(SimilarUploads::UploadId1)
.to_owned(),
)
.await?;
manager
.create_index(
Index::create()
.name("idx_similar_uploads_upload2")
.table(SimilarUploads::Table)
.col(SimilarUploads::UploadId2)
.to_owned(),
)
.await?;
manager
.create_index(
Index::create()
.name("idx_similar_uploads_score")
.table(SimilarUploads::Table)
.col(SimilarUploads::SimilarityScore)
.to_owned(),
)
.await?;
manager
.create_index(
Index::create()
.name("unique_similarity_pair")
.table(SimilarUploads::Table)
.col(SimilarUploads::UploadId1)
.col(SimilarUploads::UploadId2)
.unique()
.to_owned(),
)
.await?;
Ok(())
}
async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
manager
.drop_table(Table::drop().table(SimilarUploads::Table).to_owned())
.await?;
manager
.drop_table(Table::drop().table(AiDescriptions::Table).to_owned())
.await?;
manager
.drop_table(Table::drop().table(OcrResults::Table).to_owned())
.await?;
manager
.drop_table(Table::drop().table(UploadTags::Table).to_owned())
.await?;
manager
.drop_table(Table::drop().table(Tags::Table).to_owned())
.await?;
manager
.drop_table(Table::drop().table(Uploads::Table).to_owned())
.await?;
Ok(())
}
}
#[derive(DeriveIden)]
enum Uploads {
Table,
Id,
UserId,
FileNameOriginal,
FileNameGenerated,
FileSizeBytes,
ContentType,
FileHash,
ItemName,
ItemDescription,
ItemPath,
StorageBackend,
ProcessingStatus,
ProcessingError,
DateUploaded,
DateUpdated,
}
#[derive(DeriveIden)]
enum Tags {
Table,
Id,
Namespace,
TagValue,
CreatedAt,
}
#[derive(DeriveIden)]
enum UploadTags {
Table,
Id,
UploadId,
TagId,
Confidence,
CreatedAt,
}
#[derive(DeriveIden)]
enum OcrResults {
Table,
Id,
UploadId,
ExtractedText,
Confidence,
ProcessingEngine,
ProcessedAt,
}
#[derive(DeriveIden)]
enum AiDescriptions {
Table,
Id,
UploadId,
Description,
ModelUsed,
Confidence,
ProcessedAt,
}
#[derive(DeriveIden)]
enum SimilarUploads {
Table,
Id,
UploadId1,
UploadId2,
SimilarityScore,
DetectionMethod,
CreatedAt,
}
⭐
Log in to mark this repository.
21.3 KiB · text
History
1ce9641