//! The ticket store: the rows behind a repository's tickets, and the
//! lookups the webui makes of them.
//!
//! Split from [`super`], which holds the rules that do not need a database.
use std::collections::{HashMap, HashSet};
use sea_orm::ActiveValue::Set;
use sea_orm::sea_query::OnConflict;
use sea_orm::{
ActiveModelTrait, ColumnTrait, ConnectionTrait, DatabaseConnection, DbBackend, DbErr,
EntityTrait, PaginatorTrait, QueryFilter, QueryOrder, Statement,
};
use uuid::Uuid;
use super::{DEFAULT_LABELS, search_expression};
use crate::entities::{
ticket_attachments, ticket_label_assignments, ticket_labels, tickets, users,
};
/// A repository's labels.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn labels(
db: &DatabaseConnection,
repository_id: i64,
) -> Result<Vec<ticket_labels::Model>, DbErr> {
list_labels(db, repository_id).await
}
/// The labels a repository is missing of [`DEFAULT_LABELS`], and no others.
///
/// A repository that already has all of them - because it was listed, or
/// because its owner renamed one and made it again - is left exactly as it
/// stands: the defaults are what a repository starts with, not what it is
/// corrected back to.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn ensure_default_labels(
db: &DatabaseConnection,
repository_id: i64,
) -> Result<(), DbErr> {
let present: HashSet<String> = list_labels(db, repository_id)
.await?
.into_iter()
.map(|label| label.name)
.collect();
let missing: Vec<ticket_labels::ActiveModel> = DEFAULT_LABELS
.iter()
.filter(|name| !present.contains(**name))
.map(|name| ticket_labels::ActiveModel {
repository_id: Set(repository_id),
name: Set((*name).to_string()),
..Default::default()
})
.collect();
if missing.is_empty() {
return Ok(());
}
// The pair of repository and name is what makes a filter able to name
// its label, so two requests making the same label at once is one label
// rather than one page answering with a constraint violation.
ticket_labels::Entity::insert_many(missing)
.on_conflict(
OnConflict::columns([
ticket_labels::Column::RepositoryId,
ticket_labels::Column::Name,
])
.do_nothing()
.to_owned(),
)
.exec(db)
.await?;
Ok(())
}
/// The labels of a repository, in the order the repository made them.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn list_labels(
db: &DatabaseConnection,
repository_id: i64,
) -> Result<Vec<ticket_labels::Model>, DbErr> {
ticket_labels::Entity::find()
.filter(ticket_labels::Column::RepositoryId.eq(repository_id))
.order_by_asc(ticket_labels::Column::Id)
.all(db)
.await
}
/// One label of a repository, looked up the way a filter names it: by name.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn find_label(
db: &DatabaseConnection,
repository_id: i64,
name: &str,
) -> Result<Option<ticket_labels::Model>, DbErr> {
ticket_labels::Entity::find()
.filter(ticket_labels::Column::RepositoryId.eq(repository_id))
.filter(ticket_labels::Column::Name.eq(name))
.one(db)
.await
}
/// One label by its id, regardless of repository.
///
/// Used to check whether a label exists before deletion; the handler then
/// scopes it to the repository.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn find_label_by_id(
db: &DatabaseConnection,
label_id: i64,
) -> Result<Option<ticket_labels::Model>, DbErr> {
ticket_labels::Entity::find_by_id(label_id).one(db).await
}
/// A new label for `repository_id`, refused by the repository's own unique
/// pair of repository and name if the same name is being made at the same
/// moment.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn create_label(
db: &DatabaseConnection,
repository_id: i64,
name: &str,
) -> Result<ticket_labels::Model, DbErr> {
ticket_labels::ActiveModel {
repository_id: Set(repository_id),
name: Set(name.to_string()),
..Default::default()
}
.insert(db)
.await
}
/// Removes a label from `repository_id`.
///
/// Returns true if the label existed and was deleted, false otherwise.
/// The label must belong to the repository (scoped by repository_id).
#[tracing::instrument(level = "debug", skip(db))]
pub async fn delete_label(
db: &DatabaseConnection,
repository_id: i64,
label_id: i64,
) -> Result<bool, DbErr> {
let result = ticket_labels::Entity::delete_by_id(label_id)
.filter(ticket_labels::Column::RepositoryId.eq(repository_id))
.exec(db)
.await?;
Ok(result.rows_affected > 0)
}
/// The repository's tickets as the list page shows them: newest first, every
/// one carrying every label the filter asks for, and - when the search box
/// holds something - among the ones it found.
///
/// The two are one view rather than two: a ticket the filter leaves out is
/// not found by a search either.
#[tracing::instrument(level = "debug", skip(db, state, selected), fields(words = query.len()))]
pub async fn list_tickets(
db: &DatabaseConnection,
repository_id: i64,
state: Option<tickets::State>,
selected: &[String],
query: &str,
) -> Result<Vec<tickets::Model>, DbErr> {
let found = match search_expression(query) {
Some(expression) => {
let found = search_ticket_ids(db, &expression).await?;
if found.is_empty() {
return Ok(Vec::new());
}
Some(found)
}
// A search box holding nothing worth searching for has not searched
// for anything; one holding only things the engine has opinions of
// its own about - a stray quote, a `*` - is a search for those
// characters, which matches nothing rather than being refused.
None if !query.trim().is_empty() => return Ok(Vec::new()),
None => None,
};
let mut select =
tickets::Entity::find().filter(tickets::Column::RepositoryId.eq(repository_id));
// Only filter by state if a specific state was requested
if let Some(state) = state {
select = select.filter(tickets::Column::State.eq(state));
}
if let Some(found) = found {
select = select.filter(tickets::Column::Id.is_in(found));
}
let mut listed = select
.order_by_desc(tickets::Column::Number)
.all(db)
.await?;
// An empty filter shows everything, and an empty list has nothing left
// to filter - either way the assignments below would say no more than
// this does.
if listed.is_empty() || selected.is_empty() {
return Ok(listed);
}
// The labels to keep by, looked up in this repository's own. A name this
// repository does not have matches nothing instead of being let through:
// a ticket cannot carry a label it could never be filtered by, so a
// filter naming one has no tickets to show.
let mut wanted: Vec<i64> = Vec::with_capacity(selected.len());
for name in selected {
match find_label(db, repository_id, name).await? {
Some(label) => wanted.push(label.id),
None => return Ok(Vec::new()),
}
}
let ids: Vec<i64> = listed.iter().map(|ticket| ticket.id).collect();
let mut held: HashMap<i64, HashSet<i64>> = HashMap::new();
for assignment in ticket_label_assignments::Entity::find()
.filter(ticket_label_assignments::Column::TicketId.is_in(ids))
.all(db)
.await?
{
held.entry(assignment.ticket_id)
.or_default()
.insert(assignment.label_id);
}
// Every one of the labels, not any of them: a filter of `bug,improvement`
// is a ticket that is both a bug and an improvement.
listed.retain(|ticket| {
wanted.iter().all(|label| {
held.get(&ticket.id)
.is_some_and(|held| held.contains(label))
})
});
Ok(listed)
}
/// The rows a search finds across every ticket, as their ids.
///
/// FTS5 answers a `MATCH`, which is not something the query builder writes,
/// so the search itself is raw SQL. The expression is passed as a bound
/// value rather than pasted into the statement - though
/// [`super::search_expression`] has already reduced it to quoted words and
/// `*` for all that.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn search_ticket_ids(
db: &DatabaseConnection,
expression: &str,
) -> Result<Vec<i64>, DbErr> {
let statement = Statement::from_sql_and_values(
DbBackend::Sqlite,
"SELECT rowid AS id FROM ticket_search WHERE ticket_search MATCH ?",
vec![expression.to_string().into()],
);
let mut found = Vec::new();
for row in db.query_all_raw(statement).await? {
found.push(row.try_get::<i64>("", "id")?);
}
Ok(found)
}
/// One ticket of a repository, addressed by the number it is known by.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn find_ticket(
db: &DatabaseConnection,
repository_id: i64,
number: i64,
) -> Result<Option<tickets::Model>, DbErr> {
tickets::Entity::find()
.filter(tickets::Column::RepositoryId.eq(repository_id))
.filter(tickets::Column::Number.eq(number))
.one(db)
.await
}
/// The labels of each of `ticket_ids`, as their names, in the order the
/// repository made them.
///
/// One lookup for the whole list rather than one per ticket: a page showing
/// thirty tickets should not make thirty-one more queries to say what each
/// one is filed under.
#[tracing::instrument(level = "debug", skip(db, ticket_ids))]
pub async fn labels_by_ticket(
db: &DatabaseConnection,
ticket_ids: &[i64],
) -> Result<HashMap<i64, Vec<String>>, DbErr> {
let mut named: HashMap<i64, Vec<String>> = HashMap::new();
if ticket_ids.is_empty() {
return Ok(named);
}
let assignments = ticket_label_assignments::Entity::find()
.filter(ticket_label_assignments::Column::TicketId.is_in(ticket_ids.to_vec()))
.all(db)
.await?;
if assignments.is_empty() {
return Ok(named);
}
let label_ids: HashSet<i64> = assignments
.iter()
.map(|assignment| assignment.label_id)
.collect();
let mut name_of: HashMap<i64, String> = HashMap::new();
for label in ticket_labels::Entity::find()
.filter(ticket_labels::Column::Id.is_in(label_ids))
.all(db)
.await?
{
name_of.insert(label.id, label.name);
}
let mut held: HashMap<i64, Vec<i64>> = HashMap::new();
for assignment in assignments {
held.entry(assignment.ticket_id)
.or_default()
.push(assignment.label_id);
}
for (ticket_id, mut label_ids) in held {
// Label ids were handed out in creation order, which is the order
// the repository's own labels are offered in.
label_ids.sort_unstable();
let names = label_ids
.into_iter()
.filter_map(|label_id| name_of.get(&label_id).cloned())
.collect();
named.insert(ticket_id, names);
}
Ok(named)
}
/// The names of the users who opened `creator_ids`, for saying on a ticket
/// who opened it.
#[tracing::instrument(level = "debug", skip(db, creator_ids))]
pub async fn creator_names(
db: &DatabaseConnection,
creator_ids: &[i64],
) -> Result<HashMap<i64, String>, DbErr> {
let mut names = HashMap::new();
if creator_ids.is_empty() {
return Ok(names);
}
for user in users::Entity::find()
.filter(users::Column::Id.is_in(creator_ids.to_vec()))
.all(db)
.await?
{
names.insert(user.id, user.name);
}
Ok(names)
}
/// The authors who created tickets, looking up both local users and remote
/// actors.
#[tracing::instrument(level = "debug", skip(db, creator_ids, remote_actor_ids))]
pub async fn creator_authors(
db: &DatabaseConnection,
creator_ids: &[i64],
remote_actor_ids: &[i64],
) -> Result<HashMap<i64, crate::comments::CommentAuthor>, DbErr> {
let mut authors = HashMap::new();
// Look up local users
if !creator_ids.is_empty() {
for user in users::Entity::find()
.filter(users::Column::Id.is_in(creator_ids.to_vec()))
.all(db)
.await?
{
authors.insert(
user.id,
crate::comments::CommentAuthor {
name: user.name,
actor_url: None,
is_remote: false,
},
);
}
}
// Look up remote actors
if !remote_actor_ids.is_empty() {
use crate::entities::ap_remote_actors;
for actor in ap_remote_actors::Entity::find()
.filter(ap_remote_actors::Column::Id.is_in(remote_actor_ids.to_vec()))
.all(db)
.await?
{
let name = actor
.preferred_username
.or(actor.name)
.unwrap_or_else(|| "unknown".to_string());
authors.insert(
actor.id,
crate::comments::CommentAuthor {
name,
actor_url: Some(actor.actor_url),
is_remote: true,
},
);
}
}
Ok(authors)
}
/// The number for the next ticket of `repository_id`, which is never given
/// out twice.
///
/// The count lives on the repository and is moved on in the same statement
/// that reads it, so two tickets opened at once are two numbers rather than
/// a race for one. What comes back is the number the repository was at:
/// counting what it has already handed out rather than what still exists is
/// the whole point, because a deleted ticket is still talked about by its
/// number and the number therefore cannot come to mean another ticket.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn take_ticket_number(db: &DatabaseConnection, repository_id: i64) -> Result<i64, DbErr> {
// `RETURNING` is SQLite's own way of answering with the row a statement
// changed.
let statement = Statement::from_sql_and_values(
DbBackend::Sqlite,
"UPDATE repositories SET next_ticket_number = next_ticket_number + 1 \
WHERE id = ? RETURNING next_ticket_number",
vec![repository_id.into()],
);
let row = db
.query_one_raw(statement)
.await?
.ok_or_else(|| DbErr::RecordNotFound(format!("repository {repository_id} is not there")))?;
let counted: i64 = row.try_get::<i64>("", "next_ticket_number")?;
Ok(counted - 1)
}
/// A new ticket on `repository_id`, numbered as the repository's next.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn create_ticket(
db: &DatabaseConnection,
repository_id: i64,
creator_id: i64,
number: i64,
summary: &str,
description: Option<&str>,
state: tickets::State,
) -> Result<tickets::Model, DbErr> {
tickets::ActiveModel {
uuid: Set(Uuid::new_v4()),
repository_id: Set(repository_id),
creator_id: Set(Some(creator_id)),
remote_actor_id: Set(None),
remote_offer_id: Set(None),
resolve_activity_url: Set(None),
number: Set(number),
summary: Set(summary.to_string()),
description: Set(description.map(str::to_string)),
state: Set(state),
..Default::default()
}
.insert(db)
.await
}
/// Creates a ticket from a remote Offer (inbound federation).
///
/// The `remote_offer_id` is the Offer activity's id, used for idempotency:
/// duplicate Offers return the existing ticket rather than creating a new one.
#[allow(clippy::too_many_arguments)]
pub async fn create_ticket_from_remote(
db: &DatabaseConnection,
repository_id: i64,
remote_actor_id: i64,
number: i64,
summary: &str,
description: Option<&str>,
state: tickets::State,
remote_offer_id: &str,
) -> Result<tickets::Model, DbErr> {
tickets::ActiveModel {
uuid: Set(Uuid::new_v4()),
repository_id: Set(repository_id),
creator_id: Set(None),
remote_actor_id: Set(Some(remote_actor_id)),
remote_offer_id: Set(Some(remote_offer_id.to_string())),
resolve_activity_url: Set(None),
number: Set(number),
summary: Set(summary.to_string()),
description: Set(description.map(str::to_string)),
state: Set(state),
..Default::default()
}
.insert(db)
.await
}
/// A ticket as it was edited to be, said in the words of the columns rather
/// than in SQLite's.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn update_ticket(
db: &DatabaseConnection,
ticket_id: i64,
summary: &str,
description: Option<&str>,
state: tickets::State,
resolve_activity_url: Option<String>,
) -> Result<(), DbErr> {
tickets::Entity::update_many()
.filter(tickets::Column::Id.eq(ticket_id))
.set(tickets::ActiveModel {
summary: Set(summary.to_string()),
description: Set(description.map(str::to_string)),
state: Set(state),
resolve_activity_url: Set(resolve_activity_url),
updated_at: Set(chrono::Utc::now().fixed_offset()),
..Default::default()
})
.exec(db)
.await?;
Ok(())
}
/// Gives `ticket_id` exactly `label_ids`, dropping whatever it carried
/// before: an edit is the ticket's labels as they are now, not as they were
/// with the touched ones swapped out.
#[tracing::instrument(level = "debug", skip(db, label_ids))]
pub async fn set_ticket_labels(
db: &DatabaseConnection,
ticket_id: i64,
label_ids: &[i64],
) -> Result<(), DbErr> {
ticket_label_assignments::Entity::delete_many()
.filter(ticket_label_assignments::Column::TicketId.eq(ticket_id))
.exec(db)
.await?;
if label_ids.is_empty() {
return Ok(());
}
let assigned: Vec<ticket_label_assignments::ActiveModel> = label_ids
.iter()
.map(|label_id| ticket_label_assignments::ActiveModel {
ticket_id: Set(ticket_id),
label_id: Set(*label_id),
..Default::default()
})
.collect();
// The same label checked twice in one form is still one label on the
// ticket, and the unique pair is what says so.
ticket_label_assignments::Entity::insert_many(assigned)
.on_conflict(
OnConflict::columns([
ticket_label_assignments::Column::TicketId,
ticket_label_assignments::Column::LabelId,
])
.do_nothing()
.to_owned(),
)
.exec(db)
.await?;
Ok(())
}
/// A ticket taken down, along with what it was labelled with - the foreign
/// key between them is a cascade, so the assignments go with their ticket
/// rather than being left as labels of nothing.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn delete_ticket(db: &DatabaseConnection, ticket_id: i64) -> Result<(), DbErr> {
tickets::Entity::delete_many()
.filter(tickets::Column::Id.eq(ticket_id))
.exec(db)
.await?;
Ok(())
}
/// Lists all attachments for a given ticket, ordered by creation time.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn list_attachments(
db: &DatabaseConnection,
ticket_id: i64,
) -> Result<Vec<ticket_attachments::Model>, DbErr> {
ticket_attachments::Entity::find()
.filter(ticket_attachments::Column::TicketId.eq(ticket_id))
.order_by_asc(ticket_attachments::Column::CreatedAt)
.all(db)
.await
}
/// Gets a single attachment by its UUID.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn find_attachment_by_uuid(
db: &DatabaseConnection,
uuid: Uuid,
) -> Result<Option<ticket_attachments::Model>, DbErr> {
ticket_attachments::Entity::find()
.filter(ticket_attachments::Column::Uuid.eq(uuid))
.one(db)
.await
}
/// Creates a new attachment for a ticket.
#[tracing::instrument(level = "debug", skip(db, data))]
pub async fn create_attachment(
db: &DatabaseConnection,
ticket_id: i64,
filename: &str,
content_type: &str,
data: Vec<u8>,
) -> Result<ticket_attachments::Model, DbErr> {
ticket_attachments::ActiveModel {
uuid: Set(Uuid::new_v4()),
ticket_id: Set(ticket_id),
filename: Set(filename.to_string()),
content_type: Set(content_type.to_string()),
size: Set(data.len() as i64),
data: Set(data),
..Default::default()
}
.insert(db)
.await
}
/// Counts how many attachments a ticket currently has.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn count_attachments(db: &DatabaseConnection, ticket_id: i64) -> Result<u64, DbErr> {
ticket_attachments::Entity::find()
.filter(ticket_attachments::Column::TicketId.eq(ticket_id))
.count(db)
.await
}
/// Deletes an attachment by its ID.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn delete_attachment(db: &DatabaseConnection, attachment_id: i64) -> Result<(), DbErr> {
ticket_attachments::Entity::delete_many()
.filter(ticket_attachments::Column::Id.eq(attachment_id))
.exec(db)
.await?;
Ok(())
}