major exchange DB schema change: store common properties of batch deposit in batch_deposits table, and coin-specific ones in coin_deposits table; plus minor cleanups

1 files changed, 32 insertions, 25 deletions

diff --git a/src/exchangedb/pg_aggregate.c b/src/exchangedb/pg_aggregate.c
index 82f731927..6f143e940 100644
--- a/src/exchangedb/pg_aggregate.c
+++ b/src/exchangedb/pg_aggregate.c
@@ -52,54 +52,61 @@ TEH_PG_aggregate (
                                          pg->aggregator_shift);
   PREPARE (pg,
            "aggregate",
-           "WITH dep AS (" /* restrict to our merchant and account and mark as done */
-           "  UPDATE deposits"
+           "WITH bdep AS (" /* restrict to our merchant and account and mark as done */
+           "  UPDATE batch_deposits"
            "     SET done=TRUE"
            "   WHERE NOT (done OR policy_blocked)" /* only actually executable deposits */
-           "     AND refund_deadline<$1" /* filter by shard */
+           "     AND refund_deadline<$1"
+           /* FIXME: maybe more efficient to add shard here, too? */
            "     AND merchant_pub=$2" /* filter by target merchant */
            "     AND wire_target_h_payto=$3" /* merchant could have a 2nd bank account */
            "   RETURNING"
-           "     deposit_serial_id"
+           "     batch_deposit_serial_id)"
+           " ,cdep AS ("
+           "   SELECT"
+           "     coin_deposit_serial_id"
+           "    ,batch_deposit_serial_id"
            "    ,coin_pub"
-           "    ,amount_with_fee AS amount)"
+           "    ,amount_with_fee AS amount"
+           "   FROM coin_deposits"
+           "   WHERE batch_deposit_serial_id IN (SELECT batch_deposit_serial_id FROM bdep))"
            " ,ref AS (" /* find applicable refunds -- NOTE: may do a full join on the master, maybe find a left-join way to integrate with query above to push it to the shards? */
            "  SELECT"
            "    amount_with_fee AS refund"
            "   ,coin_pub"
-           "   ,deposit_serial_id" /* theoretically, coin could be in multiple refunded transactions */
+           "   ,batch_deposit_serial_id" /* theoretically, coin could be in multiple refunded transactions */
            "    FROM refunds"
-           "   WHERE coin_pub IN (SELECT coin_pub FROM dep)"
-           "     AND deposit_serial_id IN (SELECT deposit_serial_id FROM dep))"
+           "   WHERE coin_pub IN (SELECT coin_pub FROM cdep)"
+           "     AND batch_deposit_serial_id IN (SELECT batch_deposit_serial_id FROM bdep))"
            " ,ref_by_coin AS (" /* total up refunds by coin */
            "  SELECT"
            "    SUM((ref.refund).val) AS sum_refund_val"
            "   ,SUM((ref.refund).frac) AS sum_refund_frac"
            "   ,coin_pub"
-           "   ,deposit_serial_id" /* theoretically, coin could be in multiple refunded transactions */
+           "   ,batch_deposit_serial_id" /* theoretically, coin could be in multiple refunded transactions */
            "    FROM ref"
-           "   GROUP BY coin_pub, deposit_serial_id)"
+           "   GROUP BY coin_pub, batch_deposit_serial_id)"
            " ,norm_ref_by_coin AS (" /* normalize */
            "  SELECT"
            "    sum_refund_val + sum_refund_frac / 100000000 AS norm_refund_val"
            "   ,sum_refund_frac % 100000000 AS norm_refund_frac"
            "   ,coin_pub"
-           "   ,deposit_serial_id" /* theoretically, coin could be in multiple refunded transactions */
+           "   ,batch_deposit_serial_id" /* theoretically, coin could be in multiple refunded transactions */
            "    FROM ref_by_coin)"
            " ,fully_refunded_coins AS (" /* find applicable refunds -- NOTE: may do a full join on the master, maybe find a left-join way to integrate with query above to push it to the shards? */
            "  SELECT"
-           "    dep.coin_pub"
+           "    cdep.coin_pub"
            "    FROM norm_ref_by_coin norm"
-           "    JOIN dep"
-           "      ON (norm.coin_pub = dep.coin_pub"
-           "      AND norm.deposit_serial_id = dep.deposit_serial_id"
-           "      AND norm.norm_refund_val = (dep.amount).val"
-           "      AND norm.norm_refund_frac = (dep.amount).frac))"
+           "    JOIN cdep"
+           "      ON (norm.coin_pub = cdep.coin_pub"
+           "      AND norm.batch_deposit_serial_id = cdep.batch_deposit_serial_id"
+           "      AND norm.norm_refund_val = (cdep.amount).val"
+           "      AND norm.norm_refund_frac = (cdep.amount).frac))"
            " ,fees AS (" /* find deposit fees for not fully refunded deposits */
            "  SELECT"
            "    denom.fee_deposit AS fee"
-           "   ,cs.deposit_serial_id" /* ensures we get the fee for each coin, not once per denomination */
-           "    FROM dep cs"
+           "   ,cs.batch_deposit_serial_id" /* ensures we get the fee for each coin, not once per denomination */
+           "    FROM cdep cs"
            "    JOIN known_coins kc" /* NOTE: may do a full join on the master, maybe find a left-join way to integrate with query above to push it to the shards? */
            "      USING (coin_pub)"
            "    JOIN denominations denom"
@@ -107,18 +114,18 @@ TEH_PG_aggregate (
            "    WHERE coin_pub NOT IN (SELECT coin_pub FROM fully_refunded_coins))"
            " ,dummy AS (" /* add deposits to aggregation_tracking */
            "    INSERT INTO aggregation_tracking"
-           "    (deposit_serial_id"
+           "    (batch_deposit_serial_id"
            "    ,wtid_raw)"
-           "    SELECT deposit_serial_id,$4"
-           "      FROM dep)"
+           "    SELECT batch_deposit_serial_id,$4"
+           "      FROM bdep)"
            "SELECT" /* calculate totals (deposits, refunds and fees) */
-           "  CAST(COALESCE(SUM((dep.amount).val),0) AS INT8) AS sum_deposit_value" /* cast needed, otherwise we get NUMBER */
-           " ,COALESCE(SUM((dep.amount).frac),0) AS sum_deposit_fraction" /* SUM over INT returns INT8 */
+           "  CAST(COALESCE(SUM((cdep.amount).val),0) AS INT8) AS sum_deposit_value" /* cast needed, otherwise we get NUMBER */
+           " ,COALESCE(SUM((cdep.amount).frac),0) AS sum_deposit_fraction" /* SUM over INT returns INT8 */
            " ,CAST(COALESCE(SUM((ref.refund).val),0) AS INT8) AS sum_refund_value"
            " ,COALESCE(SUM((ref.refund).frac),0) AS sum_refund_fraction"
            " ,CAST(COALESCE(SUM((fees.fee).val),0) AS INT8) AS sum_fee_value"
            " ,COALESCE(SUM((fees.fee).frac),0) AS sum_fee_fraction"
-           " FROM dep "
+           " FROM cdep "
            "   FULL OUTER JOIN ref ON (FALSE)"    /* We just want all sums */
            "   FULL OUTER JOIN fees ON (FALSE);");