use crate::common::aggregation_sql::{
criteria_predicate, destination_table_name, row_value, watched_columns,
};
use schema_model::model::aggregation::{Aggregation, AggregationFrequency};
use schema_model::model::table::Table;
use schema_model::model::types::DatabaseType;
fn date_bucket(aggregation: &Aggregation) -> String {
let date_column = aggregation.date_column();
match aggregation.aggregation_frequency() {
AggregationFrequency::Daily => format!("cast({} as date)", date_column),
AggregationFrequency::Weekly => {
format!("cast(date_trunc('week', {}) as date)", date_column)
}
AggregationFrequency::Monthly => {
format!("cast(date_trunc('month', {}) as date)", date_column)
}
AggregationFrequency::Yearly => {
format!("cast(date_trunc('year', {}) as date)", date_column)
}
}
}
fn contribution_select(aggregation: &Aggregation, record: &str) -> String {
let mut items = vec![format!(
"{} as {}",
date_bucket(aggregation),
aggregation.date_column()
)];
for group in aggregation.aggregation_groups() {
items.push(format!("{} as {}", group.source(), group.destination()));
}
for column in aggregation.aggregation_columns() {
items.push(format!(
"{} as {}",
row_value(column),
column.destination_column()
));
}
let mut sql = format!(
"select {} from (select {}.*) as src",
items.join(", "),
record
);
if let Some(criteria) = criteria_predicate(aggregation) {
sql.push_str(" where ");
sql.push_str(&criteria);
}
sql
}
fn add_new_statement(table: &Table, aggregation: &Aggregation) -> String {
let destination = destination_table_name(DatabaseType::Postgresql, table, aggregation);
let timestamp = aggregation.time_stamp_column();
let date = aggregation.date_column();
let mut insert_columns = vec![timestamp.to_string(), date.to_string()];
let mut select_items = vec!["now()".to_string(), format!("agg.{}", date)];
let mut conflict_columns: Vec<String> = Vec::new();
for group in aggregation.aggregation_groups() {
insert_columns.push(group.destination().to_string());
select_items.push(format!("agg.{}", group.destination()));
conflict_columns.push(group.destination().to_string());
}
conflict_columns.push(date.to_string());
let mut update_items: Vec<String> = Vec::new();
for column in aggregation.aggregation_columns() {
let name = column.destination_column();
insert_columns.push(name.to_string());
select_items.push(format!("agg.{}", name));
update_items.push(format!(
"{} = coalesce(dest.{}, 0) + excluded.{}",
name, name, name
));
}
update_items.push(format!("{} = excluded.{}", timestamp, timestamp));
format!(
" insert into {} as dest ({})\n select {} from ({}) as agg where true\n on conflict ({}) do update set\n {};",
destination,
insert_columns.join(", "),
select_items.join(", "),
contribution_select(aggregation, "new"),
conflict_columns.join(", "),
update_items.join(",\n ")
)
}
fn subtract_statement(table: &Table, aggregation: &Aggregation, record: &str) -> String {
let destination = destination_table_name(DatabaseType::Postgresql, table, aggregation);
let timestamp = aggregation.time_stamp_column();
let mut set_items: Vec<String> = aggregation
.aggregation_columns()
.iter()
.map(|column| {
let name = column.destination_column();
format!("{} = coalesce(dest.{}, 0) - agg.{}", name, name, name)
})
.collect();
set_items.push(format!("{} = now()", timestamp));
let mut join_items: Vec<String> = aggregation
.aggregation_groups()
.iter()
.map(|group| format!("dest.{} = agg.{}", group.destination(), group.destination()))
.collect();
join_items.push(format!(
"dest.{} = agg.{}",
aggregation.date_column(),
aggregation.date_column()
));
format!(
" update {} as dest set\n {}\n from ({}) as agg\n where {};",
destination,
set_items.join(",\n "),
contribution_select(aggregation, record),
join_items.join(" and ")
)
}
pub fn upsert_aggregation_sql(table: &Table, aggregation: &Aggregation) -> Vec<String> {
let mut lines: Vec<String> = Vec::new();
match watched_columns(aggregation) {
Some(columns) => {
let changed = columns
.iter()
.map(|c| format!("new.{} is distinct from old.{}", c, c))
.collect::<Vec<_>>()
.join("\n or ");
let add = add_new_statement(table, aggregation);
lines.push(" if tg_op = 'INSERT' then".to_string());
lines.push(indent(&add, 3));
lines.push(format!(" elsif {} then", changed));
lines.push(indent(&subtract_statement(table, aggregation, "old"), 3));
lines.push(indent(&add, 3));
lines.push(" end if;".to_string());
}
None => {
lines.push(" if tg_op = 'UPDATE' then".to_string());
lines.push(indent(&subtract_statement(table, aggregation, "old"), 3));
lines.push(" end if;".to_string());
lines.push(add_new_statement(table, aggregation));
}
}
lines
}
pub fn delete_aggregation_sql(table: &Table, aggregation: &Aggregation) -> Vec<String> {
vec![subtract_statement(table, aggregation, "old")]
}
fn indent(sql: &str, spaces: usize) -> String {
let pad = " ".repeat(spaces);
sql.lines()
.map(|line| format!("{}{}", pad, line))
.collect::<Vec<_>>()
.join("\n")
}
#[cfg(test)]
mod tests {
use crate::common::test_support::make_context;
use crate::common::trigger_generator::TriggerGenerator;
use crate::postgresql::postgres_trigger_generator::PostgresTriggerGenerator;
use schema_model::builder::{ColumnBuilder, SchemaBuilder, TableBuilder};
use schema_model::model::aggregation::{
Aggregation, AggregationColumn, AggregationFrequency, AggregationGroup, AggregationType,
};
use schema_model::model::column_type::ColumnType;
use schema_model::model::database_model::DatabaseModel;
use schema_model::model::types::{BooleanMode, DatabaseType, ForeignKeyMode};
fn generate(criteria: Option<&str>, frequency: AggregationFrequency) -> String {
let aggregation = Aggregation::new(
"daily_sales",
"sold",
criteria,
"updated_at",
frequency,
vec![
AggregationColumn::new(AggregationType::Sum, "amount", "total"),
AggregationColumn::new(AggregationType::Count, "", "sale_count"),
],
vec![AggregationGroup::new(
"store",
"store_id",
Some("store_ref"),
)],
);
let table = TableBuilder::new(Some("app"), "sale")
.add_column(ColumnBuilder::new(None::<&str>, "amount", ColumnType::Int).build())
.add_aggregation(aggregation)
.build();
let schema = SchemaBuilder::new(Some("app")).add_table(table).build();
let model =
DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
let (ctx, buffer) = make_context(model, DatabaseType::Postgresql);
PostgresTriggerGenerator::new(ctx).output_triggers();
buffer.contents()
}
#[test]
fn insert_update_trigger_upserts_into_destination_keyed_on_groups_and_bucket() {
let sql = generate(None, AggregationFrequency::Monthly);
assert!(sql.contains("insert into app.daily_sales as dest (updated_at, sold, store_id, total, sale_count)"), "{sql}");
assert!(
sql.contains("cast(date_trunc('month', sold) as date) as sold"),
"{sql}"
);
assert!(
sql.contains("coalesce(amount, 0) as total, 1 as sale_count"),
"{sql}"
);
assert!(
sql.contains("on conflict (store_id, sold) do update set"),
"{sql}"
);
assert!(
sql.contains("total = coalesce(dest.total, 0) + excluded.total"),
"{sql}"
);
}
#[test]
fn update_only_reaggregates_when_a_contributing_column_changed() {
let sql = generate(None, AggregationFrequency::Daily);
assert!(sql.contains("if tg_op = 'INSERT' then"), "{sql}");
assert!(
sql.contains("elsif new.amount is distinct from old.amount\n or new.store_ref is distinct from old.store_ref\n or new.sold is distinct from old.sold then"),
"{sql}"
);
}
#[test]
fn criteria_filters_rows_and_disables_the_changed_column_shortcut() {
let sql = generate(Some("voided = false"), AggregationFrequency::Daily);
assert!(
sql.contains("from (select new.*) as src where (voided = false)"),
"{sql}"
);
assert!(
sql.contains("from (select old.*) as src where (voided = false)"),
"{sql}"
);
assert!(!sql.contains("is distinct from"), "{sql}");
}
#[test]
fn delete_trigger_subtracts_the_old_row() {
let sql = generate(None, AggregationFrequency::Yearly);
assert!(
sql.contains("create trigger sale_delete after delete on app.sale"),
"{sql}"
);
assert!(sql.contains("update app.daily_sales as dest set\n total = coalesce(dest.total, 0) - agg.total,\n sale_count = coalesce(dest.sale_count, 0) - agg.sale_count,\n updated_at = now()"), "{sql}");
assert!(
sql.contains("cast(date_trunc('year', sold) as date) as sold"),
"{sql}"
);
assert!(
sql.contains("where dest.store_id = agg.store_id and dest.sold = agg.sold;"),
"{sql}"
);
}
}