aboutsummaryrefslogtreecommitdiff
path: root/src/exchangedb/plugin_exchangedb_postgres.c
diff options
context:
space:
mode:
Diffstat (limited to 'src/exchangedb/plugin_exchangedb_postgres.c')
-rw-r--r--src/exchangedb/plugin_exchangedb_postgres.c4792
1 files changed, 2555 insertions, 2237 deletions
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c
index f589b92fc..d812f73e6 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -27,7 +27,9 @@
27#include "taler_pq_lib.h" 27#include "taler_pq_lib.h"
28#include "taler_json_lib.h" 28#include "taler_json_lib.h"
29#include "taler_exchangedb_plugin.h" 29#include "taler_exchangedb_plugin.h"
30#include <poll.h>
30#include <pthread.h> 31#include <pthread.h>
32#include <sys/eventfd.h>
31#include <libpq-fe.h> 33#include <libpq-fe.h>
32 34
33#include "plugin_exchangedb_common.c" 35#include "plugin_exchangedb_common.c"
@@ -99,6 +101,11 @@ struct TALER_EXCHANGEDB_Session
99 */ 101 */
100 const char *transaction_name; 102 const char *transaction_name;
101 103
104 /**
105 * Did we initialize the prepared statements
106 * for this session?
107 */
108 bool init;
102}; 109};
103 110
104 111
@@ -150,6 +157,34 @@ struct PostgresClosure
150 * Handle for the main() thread of the program. 157 * Handle for the main() thread of the program.
151 */ 158 */
152 pthread_t main_self; 159 pthread_t main_self;
160
161 /**
162 * Thread responsible for processing database event
163 * notifications.
164 */
165 pthread_t event_thread;
166
167 /**
168 * Lock for @e listener_count access.
169 */
170 pthread_mutex_t event_lock;
171
172 /**
173 * Number of registered listerners. @e event_thread
174 * should terminate if this value reaches 0.
175 */
176 uint64_t listener_count;
177
178 /**
179 * Additional FD to signal the @e event_thread
180 * (used to stop it).
181 */
182 int event_fd;
183
184 /**
185 * Current Postges socket we watch on for notifications.
186 */
187 int pg_sock;
153}; 188};
154 189
155 190
@@ -223,16 +258,2270 @@ db_conn_destroy (void *cls)
223 258
224 259
225/** 260/**
261 * Initialize prepared statements for @a sess.
262 *
263 * @param[in,out] sess session to initialize
264 * @return #GNUNET_OK on success
265 */
266static enum GNUNET_GenericReturnValue
267init_session (struct TALER_EXCHANGEDB_Session *sess)
268{
269 enum GNUNET_GenericReturnValue ret;
270 struct GNUNET_PQ_PreparedStatement ps[] = {
271 /* Used in #postgres_insert_denomination_info() and
272 #postgres_add_denomination_key() */
273 GNUNET_PQ_make_prepare ("denomination_insert",
274 "INSERT INTO denominations "
275 "(denom_pub_hash"
276 ",denom_pub"
277 ",master_sig"
278 ",valid_from"
279 ",expire_withdraw"
280 ",expire_deposit"
281 ",expire_legal"
282 ",coin_val" /* value of this denom */
283 ",coin_frac" /* fractional value of this denom */
284 ",fee_withdraw_val"
285 ",fee_withdraw_frac"
286 ",fee_deposit_val"
287 ",fee_deposit_frac"
288 ",fee_refresh_val"
289 ",fee_refresh_frac"
290 ",fee_refund_val"
291 ",fee_refund_frac"
292 ") VALUES "
293 "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10,"
294 " $11, $12, $13, $14, $15, $16, $17);",
295 17),
296 /* Used in #postgres_iterate_denomination_info() */
297 GNUNET_PQ_make_prepare ("denomination_iterate",
298 "SELECT"
299 " master_sig"
300 ",valid_from"
301 ",expire_withdraw"
302 ",expire_deposit"
303 ",expire_legal"
304 ",coin_val" /* value of this denom */
305 ",coin_frac" /* fractional value of this denom */
306 ",fee_withdraw_val"
307 ",fee_withdraw_frac"
308 ",fee_deposit_val"
309 ",fee_deposit_frac"
310 ",fee_refresh_val"
311 ",fee_refresh_frac"
312 ",fee_refund_val"
313 ",fee_refund_frac"
314 ",denom_pub"
315 " FROM denominations;",
316 0),
317 /* Used in #postgres_iterate_denominations() */
318 GNUNET_PQ_make_prepare ("select_denominations",
319 "SELECT"
320 " denominations.master_sig"
321 ",denom_revocations_serial_id IS NOT NULL AS revoked"
322 ",valid_from"
323 ",expire_withdraw"
324 ",expire_deposit"
325 ",expire_legal"
326 ",coin_val" /* value of this denom */
327 ",coin_frac" /* fractional value of this denom */
328 ",fee_withdraw_val"
329 ",fee_withdraw_frac"
330 ",fee_deposit_val"
331 ",fee_deposit_frac"
332 ",fee_refresh_val"
333 ",fee_refresh_frac"
334 ",fee_refund_val"
335 ",fee_refund_frac"
336 ",denom_pub"
337 " FROM denominations"
338 " LEFT JOIN "
339 " denomination_revocations USING (denominations_serial);",
340 0),
341 /* Used in #postgres_iterate_active_signkeys() */
342 GNUNET_PQ_make_prepare ("select_signkeys",
343 "SELECT"
344 " master_sig"
345 ",exchange_pub"
346 ",valid_from"
347 ",expire_sign"
348 ",expire_legal"
349 " FROM exchange_sign_keys esk"
350 " WHERE"
351 " expire_sign > $1"
352 " AND NOT EXISTS "
353 " (SELECT esk_serial "
354 " FROM signkey_revocations skr"
355 " WHERE esk.esk_serial = skr.esk_serial);",
356 1),
357 /* Used in #postgres_iterate_auditor_denominations() */
358 GNUNET_PQ_make_prepare ("select_auditor_denoms",
359 "SELECT"
360 " auditors.auditor_pub"
361 ",denominations.denom_pub_hash"
362 ",auditor_denom_sigs.auditor_sig"
363 " FROM auditor_denom_sigs"
364 " JOIN auditors USING (auditor_uuid)"
365 " JOIN denominations USING (denominations_serial)"
366 " WHERE auditors.is_active;",
367 0),
368 /* Used in #postgres_iterate_active_auditors() */
369 GNUNET_PQ_make_prepare ("select_auditors",
370 "SELECT"
371 " auditor_pub"
372 ",auditor_url"
373 ",auditor_name"
374 " FROM auditors"
375 " WHERE"
376 " is_active;",
377 0),
378 /* Used in #postgres_get_denomination_info() */
379 GNUNET_PQ_make_prepare ("denomination_get",
380 "SELECT"
381 " master_sig"
382 ",valid_from"
383 ",expire_withdraw"
384 ",expire_deposit"
385 ",expire_legal"
386 ",coin_val" /* value of this denom */
387 ",coin_frac" /* fractional value of this denom */
388 ",fee_withdraw_val"
389 ",fee_withdraw_frac"
390 ",fee_deposit_val"
391 ",fee_deposit_frac"
392 ",fee_refresh_val"
393 ",fee_refresh_frac"
394 ",fee_refund_val"
395 ",fee_refund_frac"
396 " FROM denominations"
397 " WHERE denom_pub_hash=$1;",
398 1),
399 /* Used in #postgres_insert_denomination_revocation() */
400 GNUNET_PQ_make_prepare ("denomination_revocation_insert",
401 "INSERT INTO denomination_revocations "
402 "(denominations_serial"
403 ",master_sig"
404 ") SELECT denominations_serial,$2"
405 " FROM denominations"
406 " WHERE denom_pub_hash=$1;",
407 2),
408 /* Used in #postgres_get_denomination_revocation() */
409 GNUNET_PQ_make_prepare ("denomination_revocation_get",
410 "SELECT"
411 " master_sig"
412 ",denom_revocations_serial_id"
413 " FROM denomination_revocations"
414 " WHERE denominations_serial="
415 " (SELECT denominations_serial"
416 " FROM denominations"
417 " WHERE denom_pub_hash=$1);",
418 1),
419 /* Used in #postgres_reserves_get() */
420 GNUNET_PQ_make_prepare ("reserves_get",
421 "SELECT"
422 " current_balance_val"
423 ",current_balance_frac"
424 ",expiration_date"
425 ",gc_date"
426 " FROM reserves"
427 " WHERE reserve_pub=$1"
428 " LIMIT 1;",
429 1),
430 GNUNET_PQ_make_prepare ("reserve_create",
431 "INSERT INTO reserves "
432 "(reserve_pub"
433 ",account_details"
434 ",current_balance_val"
435 ",current_balance_frac"
436 ",expiration_date"
437 ",gc_date"
438 ") VALUES "
439 "($1, $2, $3, $4, $5, $6)"
440 " ON CONFLICT DO NOTHING"
441 " RETURNING reserve_uuid;",
442 6),
443 /* Used in #postgres_insert_reserve_closed() */
444 GNUNET_PQ_make_prepare ("reserves_close_insert",
445 "INSERT INTO reserves_close "
446 "(reserve_uuid"
447 ",execution_date"
448 ",wtid"
449 ",receiver_account"
450 ",amount_val"
451 ",amount_frac"
452 ",closing_fee_val"
453 ",closing_fee_frac"
454 ") SELECT reserve_uuid, $2, $3, $4, $5, $6, $7, $8"
455 " FROM reserves"
456 " WHERE reserve_pub=$1;",
457 8),
458 /* Used in #reserves_update() when the reserve is updated */
459 GNUNET_PQ_make_prepare ("reserve_update",
460 "UPDATE reserves"
461 " SET"
462 " expiration_date=$1"
463 ",gc_date=$2"
464 ",current_balance_val=$3"
465 ",current_balance_frac=$4"
466 " WHERE reserve_pub=$5;",
467 5),
468 /* Used in #postgres_reserves_in_insert() to store transaction details */
469 GNUNET_PQ_make_prepare ("reserves_in_add_transaction",
470 "INSERT INTO reserves_in "
471 "(reserve_uuid"
472 ",wire_reference"
473 ",credit_val"
474 ",credit_frac"
475 ",exchange_account_section"
476 ",sender_account_details"
477 ",execution_date"
478 ") SELECT reserve_uuid, $2, $3, $4, $5, $6, $7"
479 " FROM reserves"
480 " WHERE reserve_pub=$1"
481 " ON CONFLICT DO NOTHING;",
482 7),
483 /* Used in #postgres_reserves_in_insert() to store transaction details */
484 GNUNET_PQ_make_prepare ("reserves_in_add_by_uuid",
485 "INSERT INTO reserves_in "
486 "(reserve_uuid"
487 ",wire_reference"
488 ",credit_val"
489 ",credit_frac"
490 ",exchange_account_section"
491 ",sender_account_details"
492 ",execution_date"
493 ") VALUES ($1, $2, $3, $4, $5, $6, $7)"
494 " ON CONFLICT DO NOTHING;",
495 7),
496 /* Used in postgres_select_reserves_in_above_serial_id() to obtain inbound
497 transactions for reserves with serial id '\geq' the given parameter */
498 GNUNET_PQ_make_prepare ("reserves_in_get_latest_wire_reference",
499 "SELECT"
500 " wire_reference"
501 " FROM reserves_in"
502 " WHERE exchange_account_section=$1"
503 " ORDER BY reserve_in_serial_id DESC"
504 " LIMIT 1;",
505 1),
506 /* Used in postgres_select_reserves_in_above_serial_id() to obtain inbound
507 transactions for reserves with serial id '\geq' the given parameter */
508 GNUNET_PQ_make_prepare ("audit_reserves_in_get_transactions_incr",
509 "SELECT"
510 " reserves.reserve_pub"
511 ",wire_reference"
512 ",credit_val"
513 ",credit_frac"
514 ",execution_date"
515 ",sender_account_details"
516 ",reserve_in_serial_id"
517 " FROM reserves_in"
518 " JOIN reserves"
519 " USING (reserve_uuid)"
520 " WHERE reserve_in_serial_id>=$1"
521 " ORDER BY reserve_in_serial_id;",
522 1),
523 /* Used in postgres_select_reserves_in_above_serial_id() to obtain inbound
524 transactions for reserves with serial id '\geq' the given parameter */
525 GNUNET_PQ_make_prepare (
526 "audit_reserves_in_get_transactions_incr_by_account",
527 "SELECT"
528 " reserves.reserve_pub"
529 ",wire_reference"
530 ",credit_val"
531 ",credit_frac"
532 ",execution_date"
533 ",sender_account_details"
534 ",reserve_in_serial_id"
535 " FROM reserves_in"
536 " JOIN reserves "
537 " USING (reserve_uuid)"
538 " WHERE reserve_in_serial_id>=$1 AND exchange_account_section=$2"
539 " ORDER BY reserve_in_serial_id;",
540 2),
541 /* Used in #postgres_get_reserve_history() to obtain inbound transactions
542 for a reserve */
543 GNUNET_PQ_make_prepare ("reserves_in_get_transactions",
544 "SELECT"
545 " wire_reference"
546 ",credit_val"
547 ",credit_frac"
548 ",execution_date"
549 ",sender_account_details"
550 " FROM reserves_in"
551 " WHERE reserve_uuid="
552 " (SELECT reserve_uuid "
553 " FROM reserves"
554 " WHERE reserve_pub=$1);",
555 1),
556 /* Lock withdraw table; NOTE: we may want to eventually shard the
557 deposit table to avoid this lock being the main point of
558 contention limiting transaction performance. */
559 GNUNET_PQ_make_prepare ("lock_withdraw",
560 "LOCK TABLE reserves_out;",
561 0),
562 /* Used in #postgres_insert_withdraw_info() to store
563 the signature of a blinded coin with the blinded coin's
564 details before returning it during /reserve/withdraw. We store
565 the coin's denomination information (public key, signature)
566 and the blinded message as well as the reserve that the coin
567 is being withdrawn from and the signature of the message
568 authorizing the withdrawal. */
569 GNUNET_PQ_make_prepare ("insert_withdraw_info",
570 "WITH ds AS"
571 " (SELECT denominations_serial"
572 " FROM denominations"
573 " WHERE denom_pub_hash=$2)"
574 "INSERT INTO reserves_out "
575 "(h_blind_ev"
576 ",denominations_serial"
577 ",denom_sig"
578 ",reserve_uuid"
579 ",reserve_sig"
580 ",execution_date"
581 ",amount_with_fee_val"
582 ",amount_with_fee_frac"
583 ") SELECT $1, ds.denominations_serial, $3, reserve_uuid, $5, $6, $7, $8"
584 " FROM reserves"
585 " CROSS JOIN ds"
586 " WHERE reserve_pub=$4;",
587 8),
588 /* Used in #postgres_get_withdraw_info() to
589 locate the response for a /reserve/withdraw request
590 using the hash of the blinded message. Used to
591 make sure /reserve/withdraw requests are idempotent. */
592 GNUNET_PQ_make_prepare ("get_withdraw_info",
593 "SELECT"
594 " denom.denom_pub_hash"
595 ",denom_sig"
596 ",reserve_sig"
597 ",reserves.reserve_pub"
598 ",execution_date"
599 ",amount_with_fee_val"
600 ",amount_with_fee_frac"
601 ",denom.fee_withdraw_val"
602 ",denom.fee_withdraw_frac"
603 " FROM reserves_out"
604 " JOIN reserves"
605 " USING (reserve_uuid)"
606 " JOIN denominations denom"
607 " USING (denominations_serial)"
608 " WHERE h_blind_ev=$1;",
609 1),
610 /* Used during #postgres_get_reserve_history() to
611 obtain all of the /reserve/withdraw operations that
612 have been performed on a given reserve. (i.e. to
613 demonstrate double-spending) */
614 GNUNET_PQ_make_prepare ("get_reserves_out",
615 "SELECT"
616 " h_blind_ev"
617 ",denom.denom_pub_hash"
618 ",denom_sig"
619 ",reserve_sig"
620 ",execution_date"
621 ",amount_with_fee_val"
622 ",amount_with_fee_frac"
623 ",denom.fee_withdraw_val"
624 ",denom.fee_withdraw_frac"
625 " FROM reserves_out"
626 " JOIN denominations denom"
627 " USING (denominations_serial)"
628 " WHERE reserve_uuid="
629 " (SELECT reserve_uuid"
630 " FROM reserves"
631 " WHERE reserve_pub=$1);",
632 1),
633 /* Used in #postgres_select_withdrawals_above_serial_id() */
634 GNUNET_PQ_make_prepare ("audit_get_reserves_out_incr",
635 "SELECT"
636 " h_blind_ev"
637 ",denom.denom_pub"
638 ",reserve_sig"
639 ",reserves.reserve_pub"
640 ",execution_date"
641 ",amount_with_fee_val"
642 ",amount_with_fee_frac"
643 ",reserve_out_serial_id"
644 " FROM reserves_out"
645 " JOIN reserves"
646 " USING (reserve_uuid)"
647 " JOIN denominations denom"
648 " USING (denominations_serial)"
649 " WHERE reserve_out_serial_id>=$1"
650 " ORDER BY reserve_out_serial_id ASC;",
651 1),
652
653 /* Used in #postgres_count_known_coins() */
654 GNUNET_PQ_make_prepare ("count_known_coins",
655 "SELECT"
656 " COUNT(*) AS count"
657 " FROM known_coins"
658 " WHERE denominations_serial="
659 " (SELECT denominations_serial"
660 " FROM denominations"
661 " WHERE denom_pub_hash=$1);",
662 1),
663 /* Used in #postgres_get_known_coin() to fetch
664 the denomination public key and signature for
665 a coin known to the exchange. */
666 GNUNET_PQ_make_prepare ("get_known_coin",
667 "SELECT"
668 " denominations.denom_pub_hash"
669 ",denom_sig"
670 " FROM known_coins"
671 " JOIN denominations USING (denominations_serial)"
672 " WHERE coin_pub=$1;",
673 1),
674 /* Used in #postgres_ensure_coin_known() */
675 GNUNET_PQ_make_prepare ("get_known_coin_dh",
676 "SELECT"
677 " denominations.denom_pub_hash"
678 " FROM known_coins"
679 " JOIN denominations USING (denominations_serial)"
680 " WHERE coin_pub=$1;",
681 1),
682 /* Used in #postgres_get_coin_denomination() to fetch
683 the denomination public key hash for
684 a coin known to the exchange. */
685 GNUNET_PQ_make_prepare ("get_coin_denomination",
686 "SELECT"
687 " denominations.denom_pub_hash"
688 " FROM known_coins"
689 " JOIN denominations USING (denominations_serial)"
690 " WHERE coin_pub=$1"
691 " FOR SHARE;",
692 1),
693 /* Lock deposit table; NOTE: we may want to eventually shard the
694 deposit table to avoid this lock being the main point of
695 contention limiting transaction performance. */
696 GNUNET_PQ_make_prepare ("lock_known_coins",
697 "LOCK TABLE known_coins;",
698 0),
699 /* Used in #postgres_insert_known_coin() to store
700 the denomination public key and signature for
701 a coin known to the exchange. */
702 GNUNET_PQ_make_prepare ("insert_known_coin",
703 "INSERT INTO known_coins "
704 "(coin_pub"
705 ",denominations_serial"
706 ",denom_sig"
707 ") SELECT $1, denominations_serial, $3 "
708 " FROM denominations"
709 " WHERE denom_pub_hash=$2;",
710 3),
711
712 /* Used in #postgres_insert_melt() to store
713 high-level information about a melt operation */
714 GNUNET_PQ_make_prepare ("insert_melt",
715 "INSERT INTO refresh_commitments "
716 "(rc "
717 ",old_known_coin_id "
718 ",old_coin_sig "
719 ",amount_with_fee_val "
720 ",amount_with_fee_frac "
721 ",noreveal_index "
722 ") SELECT $1, known_coin_id, $3, $4, $5, $6"
723 " FROM known_coins"
724 " WHERE coin_pub=$2",
725 6),
726 /* Used in #postgres_get_melt() to fetch
727 high-level information about a melt operation */
728 GNUNET_PQ_make_prepare ("get_melt",
729 "SELECT"
730 " denoms.denom_pub_hash"
731 ",denoms.fee_refresh_val"
732 ",denoms.fee_refresh_frac"
733 ",kc.coin_pub AS old_coin_pub"
734 ",old_coin_sig"
735 ",amount_with_fee_val"
736 ",amount_with_fee_frac"
737 ",noreveal_index"
738 " FROM refresh_commitments"
739 " JOIN known_coins kc"
740 " ON (refresh_commitments.old_known_coin_id = kc.known_coin_id)"
741 " JOIN denominations denoms"
742 " ON (kc.denominations_serial = denoms.denominations_serial)"
743 " WHERE rc=$1;",
744 1),
745 /* Used in #postgres_get_melt_index() to fetch
746 the noreveal index from a previous melt operation */
747 GNUNET_PQ_make_prepare ("get_melt_index",
748 "SELECT"
749 " noreveal_index"
750 " FROM refresh_commitments"
751 " WHERE rc=$1;",
752 1),
753 /* Used in #postgres_select_refreshes_above_serial_id() to fetch
754 refresh session with id '\geq' the given parameter */
755 GNUNET_PQ_make_prepare ("audit_get_refresh_commitments_incr",
756 "SELECT"
757 " denom.denom_pub"
758 ",kc.coin_pub AS old_coin_pub"
759 ",old_coin_sig"
760 ",amount_with_fee_val"
761 ",amount_with_fee_frac"
762 ",noreveal_index"
763 ",melt_serial_id"
764 ",rc"
765 " FROM refresh_commitments"
766 " JOIN known_coins kc"
767 " ON (refresh_commitments.old_known_coin_id = kc.known_coin_id)"
768 " JOIN denominations denom"
769 " ON (kc.denominations_serial = denom.denominations_serial)"
770 " WHERE melt_serial_id>=$1"
771 " ORDER BY melt_serial_id ASC;",
772 1),
773 /* Query the 'refresh_commitments' by coin public key */
774 GNUNET_PQ_make_prepare ("get_refresh_session_by_coin",
775 "SELECT"
776 " rc"
777 ",old_coin_sig"
778 ",amount_with_fee_val"
779 ",amount_with_fee_frac"
780 ",denoms.denom_pub_hash"
781 ",denoms.fee_refresh_val"
782 ",denoms.fee_refresh_frac"
783 ",melt_serial_id"
784 " FROM refresh_commitments"
785 " JOIN known_coins kc"
786 " ON (refresh_commitments.old_known_coin_id = kc.known_coin_id)"
787 " JOIN denominations denoms"
788 " USING (denominations_serial)"
789 " WHERE old_known_coin_id="
790 "(SELECT known_coin_id"
791 " FROM known_coins"
792 " WHERE coin_pub=$1);",
793 1),
794 /* Store information about the desired denominations for a
795 refresh operation, used in #postgres_insert_refresh_reveal() */
796 GNUNET_PQ_make_prepare ("insert_refresh_revealed_coin",
797 "WITH rcx AS"
798 " (SELECT melt_serial_id"
799 " FROM refresh_commitments"
800 " WHERE rc=$1)"
801 "INSERT INTO refresh_revealed_coins "
802 "(melt_serial_id "
803 ",freshcoin_index "
804 ",link_sig "
805 ",denominations_serial "
806 ",coin_ev"
807 ",h_coin_ev"
808 ",ev_sig"
809 ") SELECT rcx.melt_serial_id, $2, $3, "
810 " denominations_serial, $5, $6, $7"
811 " FROM denominations"
812 " CROSS JOIN rcx"
813 " WHERE denom_pub_hash=$4;",
814 7),
815 /* Obtain information about the coins created in a refresh
816 operation, used in #postgres_get_refresh_reveal() */
817 GNUNET_PQ_make_prepare ("get_refresh_revealed_coins",
818 "SELECT "
819 " freshcoin_index"
820 ",denom.denom_pub"
821 ",link_sig"
822 ",coin_ev"
823 ",ev_sig"
824 " FROM refresh_revealed_coins"
825 " JOIN denominations denom "
826 " USING (denominations_serial)"
827 " JOIN refresh_commitments"
828 " USING (melt_serial_id)"
829 " WHERE rc=$1"
830 " ORDER BY freshcoin_index ASC;",
831 1),
832
833 /* Used in #postgres_insert_refresh_reveal() to store the transfer
834 keys we learned */
835 GNUNET_PQ_make_prepare ("insert_refresh_transfer_keys",
836 "INSERT INTO refresh_transfer_keys "
837 "(melt_serial_id"
838 ",transfer_pub"
839 ",transfer_privs"
840 ") SELECT melt_serial_id, $2, $3"
841 " FROM refresh_commitments"
842 " WHERE rc=$1",
843 3),
844 /* Used in #postgres_get_refresh_reveal() to retrieve transfer
845 keys from /refresh/reveal */
846 GNUNET_PQ_make_prepare ("get_refresh_transfer_keys",
847 "SELECT"
848 " transfer_pub"
849 ",transfer_privs"
850 " FROM refresh_transfer_keys"
851 " JOIN refresh_commitments"
852 " USING (melt_serial_id)"
853 " WHERE rc=$1;",
854 1),
855 /* Used in #postgres_insert_refund() to store refund information */
856 GNUNET_PQ_make_prepare ("insert_refund",
857 "INSERT INTO refunds "
858 "(deposit_serial_id "
859 ",merchant_sig "
860 ",rtransaction_id "
861 ",amount_with_fee_val "
862 ",amount_with_fee_frac "
863 ") SELECT deposit_serial_id, $3, $5, $6, $7"
864 " FROM deposits"
865 " JOIN known_coins USING (known_coin_id)"
866 " WHERE coin_pub=$1"
867 " AND h_contract_terms=$4"
868 " AND merchant_pub=$2",
869 7),
870 /* Query the 'refunds' by coin public key */
871 GNUNET_PQ_make_prepare ("get_refunds_by_coin",
872 "SELECT"
873 " merchant_pub"
874 ",merchant_sig"
875 ",h_contract_terms"
876 ",rtransaction_id"
877 ",refunds.amount_with_fee_val"
878 ",refunds.amount_with_fee_frac"
879 ",denom.fee_refund_val "
880 ",denom.fee_refund_frac "
881 ",refund_serial_id"
882 " FROM refunds"
883 " JOIN deposits USING (deposit_serial_id)"
884 " JOIN known_coins USING (known_coin_id)"
885 " JOIN denominations denom USING (denominations_serial)"
886 " WHERE coin_pub=$1;",
887 1),
888 /* Query the 'refunds' by coin public key, merchant_pub and contract hash */
889 GNUNET_PQ_make_prepare ("get_refunds_by_coin_and_contract",
890 "SELECT"
891 " refunds.amount_with_fee_val"
892 ",refunds.amount_with_fee_frac"
893 " FROM refunds"
894 " JOIN deposits USING (deposit_serial_id)"
895 " JOIN known_coins USING (known_coin_id)"
896 " WHERE coin_pub=$1"
897 " AND merchant_pub=$2"
898 " AND h_contract_terms=$3;",
899 3),
900 /* Fetch refunds with rowid '\geq' the given parameter */
901 GNUNET_PQ_make_prepare ("audit_get_refunds_incr",
902 "SELECT"
903 " merchant_pub"
904 ",merchant_sig"
905 ",h_contract_terms"
906 ",rtransaction_id"
907 ",denom.denom_pub"
908 ",kc.coin_pub"
909 ",refunds.amount_with_fee_val"
910 ",refunds.amount_with_fee_frac"
911 ",refund_serial_id"
912 " FROM refunds"
913 " JOIN deposits USING (deposit_serial_id)"
914 " JOIN known_coins kc USING (known_coin_id)"
915 " JOIN denominations denom ON (kc.denominations_serial = denom.denominations_serial)"
916 " WHERE refund_serial_id>=$1"
917 " ORDER BY refund_serial_id ASC;",
918 1),
919 /* Lock deposit table; NOTE: we may want to eventually shard the
920 deposit table to avoid this lock being the main point of
921 contention limiting transaction performance. */
922 GNUNET_PQ_make_prepare ("lock_deposit",
923 "LOCK TABLE deposits;",
924 0),
925 /* Store information about a /deposit the exchange is to execute.
926 Used in #postgres_insert_deposit(). */
927 GNUNET_PQ_make_prepare ("insert_deposit",
928 "INSERT INTO deposits "
929 "(known_coin_id"
930 ",amount_with_fee_val"
931 ",amount_with_fee_frac"
932 ",wallet_timestamp"
933 ",refund_deadline"
934 ",wire_deadline"
935 ",merchant_pub"
936 ",h_contract_terms"
937 ",h_wire"
938 ",coin_sig"
939 ",wire"
940 ",exchange_timestamp"
941 ") SELECT known_coin_id, $2, $3, $4, $5, $6, "
942 " $7, $8, $9, $10, $11, $12"
943 " FROM known_coins"
944 " WHERE coin_pub=$1;",
945 12),
946 /* Fetch an existing deposit request, used to ensure idempotency
947 during /deposit processing. Used in #postgres_have_deposit(). */
948 GNUNET_PQ_make_prepare ("get_deposit",
949 "SELECT"
950 " amount_with_fee_val"
951 ",amount_with_fee_frac"
952 ",denominations.fee_deposit_val"
953 ",denominations.fee_deposit_frac"
954 ",wallet_timestamp"
955 ",exchange_timestamp"
956 ",refund_deadline"
957 ",wire_deadline"
958 ",h_contract_terms"
959 ",h_wire"
960 " FROM deposits"
961 " JOIN known_coins USING (known_coin_id)"
962 " JOIN denominations USING (denominations_serial)"
963 " WHERE ((coin_pub=$1)"
964 " AND (merchant_pub=$3)"
965 " AND (h_contract_terms=$2));",
966 3),
967 /* Fetch deposits with rowid '\geq' the given parameter */
968 GNUNET_PQ_make_prepare ("audit_get_deposits_incr",
969 "SELECT"
970 " amount_with_fee_val"
971 ",amount_with_fee_frac"
972 ",wallet_timestamp"
973 ",exchange_timestamp"
974 ",merchant_pub"
975 ",denom.denom_pub"
976 ",kc.coin_pub"
977 ",coin_sig"
978 ",refund_deadline"
979 ",wire_deadline"
980 ",h_contract_terms"
981 ",wire"
982 ",done"
983 ",deposit_serial_id"
984 " FROM deposits"
985 " JOIN known_coins kc USING (known_coin_id)"
986 " JOIN denominations denom USING (denominations_serial)"
987 " WHERE ("
988 " (deposit_serial_id>=$1)"
989 " )"
990 " ORDER BY deposit_serial_id ASC;",
991 1),
992 /* Fetch an existing deposit request.
993 Used in #postgres_lookup_transfer_by_deposit(). */
994 GNUNET_PQ_make_prepare ("get_deposit_for_wtid",
995 "SELECT"
996 " amount_with_fee_val"
997 ",amount_with_fee_frac"
998 ",denom.fee_deposit_val"
999 ",denom.fee_deposit_frac"
1000 ",wire_deadline"
1001 " FROM deposits"
1002 " JOIN known_coins USING (known_coin_id)"
1003 " JOIN denominations denom USING (denominations_serial)"
1004 " WHERE ((coin_pub=$1)"
1005 " AND (merchant_pub=$2)"
1006 " AND (h_contract_terms=$3)"
1007 " AND (h_wire=$4)"
1008 " );",
1009 4),
1010 /* Used in #postgres_get_ready_deposit() */
1011 GNUNET_PQ_make_prepare ("deposits_get_ready",
1012 "SELECT"
1013 " deposit_serial_id"
1014 ",amount_with_fee_val"
1015 ",amount_with_fee_frac"
1016 ",denom.fee_deposit_val"
1017 ",denom.fee_deposit_frac"
1018 ",wire_deadline"
1019 ",h_contract_terms"
1020 ",wire"
1021 ",merchant_pub"
1022 ",kc.coin_pub"
1023 ",exchange_timestamp"
1024 ",wallet_timestamp"
1025 " FROM deposits"
1026 " JOIN known_coins kc USING (known_coin_id)"
1027 " JOIN denominations denom USING (denominations_serial)"
1028 " WHERE tiny=FALSE"
1029 " AND done=FALSE"
1030 " AND wire_deadline<=$1"
1031 " AND refund_deadline<$1"
1032 " ORDER BY wire_deadline ASC"
1033 " LIMIT 1;",
1034 1),
1035 /* Used in #postgres_iterate_matching_deposits() */
1036 GNUNET_PQ_make_prepare ("deposits_iterate_matching",
1037 "SELECT"
1038 " deposit_serial_id"
1039 ",amount_with_fee_val"
1040 ",amount_with_fee_frac"
1041 ",denom.fee_deposit_val"
1042 ",denom.fee_deposit_frac"
1043 ",h_contract_terms"
1044 ",kc.coin_pub"
1045 " FROM deposits"
1046 " JOIN known_coins kc USING (known_coin_id)"
1047 " JOIN denominations denom USING (denominations_serial)"
1048 " WHERE"
1049 " merchant_pub=$1 AND"
1050 " h_wire=$2 AND"
1051 " done=FALSE"
1052 " ORDER BY wire_deadline ASC"
1053 " LIMIT "
1054 TALER_QUOTE (
1055 TALER_EXCHANGEDB_MATCHING_DEPOSITS_LIMIT) ";",
1056 2),
1057 /* Used in #postgres_mark_deposit_tiny() */
1058 GNUNET_PQ_make_prepare ("mark_deposit_tiny",
1059 "UPDATE deposits"
1060 " SET tiny=TRUE"
1061 " WHERE deposit_serial_id=$1",
1062 1),
1063 /* Used in #postgres_mark_deposit_done() */
1064 GNUNET_PQ_make_prepare ("mark_deposit_done",
1065 "UPDATE deposits"
1066 " SET done=TRUE"
1067 " WHERE deposit_serial_id=$1;",
1068 1),
1069 /* Used in #postgres_test_deposit_done() */
1070 GNUNET_PQ_make_prepare ("test_deposit_done",
1071 "SELECT done"
1072 " FROM deposits"
1073 " JOIN known_coins USING (known_coin_id)"
1074 " WHERE coin_pub=$1"
1075 " AND merchant_pub=$2"
1076 " AND h_contract_terms=$3"
1077 " AND h_wire=$4;",
1078 5),
1079 /* Used in #postgres_get_coin_transactions() to obtain information
1080 about how a coin has been spend with /deposit requests. */
1081 GNUNET_PQ_make_prepare ("get_deposit_with_coin_pub",
1082 "SELECT"
1083 " amount_with_fee_val"
1084 ",amount_with_fee_frac"
1085 ",denoms.fee_deposit_val"
1086 ",denoms.fee_deposit_frac"
1087 ",denoms.denom_pub_hash"
1088 ",wallet_timestamp"
1089 ",refund_deadline"
1090 ",wire_deadline"
1091 ",merchant_pub"
1092 ",h_contract_terms"
1093 ",h_wire"
1094 ",wire"
1095 ",coin_sig"
1096 ",deposit_serial_id"
1097 ",done"
1098 " FROM deposits"
1099 " JOIN known_coins kc"
1100 " USING (known_coin_id)"
1101 " JOIN denominations denoms"
1102 " USING (denominations_serial)"
1103 " WHERE coin_pub=$1;",
1104 1),
1105
1106 /* Used in #postgres_get_link_data(). */
1107 GNUNET_PQ_make_prepare ("get_link",
1108 "SELECT "
1109 " tp.transfer_pub"
1110 ",denoms.denom_pub"
1111 ",rrc.ev_sig"
1112 ",rrc.link_sig"
1113 " FROM refresh_commitments"
1114 " JOIN refresh_revealed_coins rrc"
1115 " USING (melt_serial_id)"
1116 " JOIN refresh_transfer_keys tp"
1117 " USING (melt_serial_id)"
1118 " JOIN denominations denoms"
1119 " ON (rrc.denominations_serial = denoms.denominations_serial)"
1120 " WHERE old_known_coin_id="
1121 " (SELECT known_coin_id "
1122 " FROM known_coins"
1123 " WHERE coin_pub=$1)"
1124 " ORDER BY tp.transfer_pub, rrc.freshcoin_index ASC",
1125 1),
1126 /* Used in #postgres_lookup_wire_transfer */
1127 GNUNET_PQ_make_prepare ("lookup_transactions",
1128 "SELECT"
1129 " aggregation_serial_id"
1130 ",deposits.h_contract_terms"
1131 ",deposits.wire"
1132 ",deposits.h_wire"
1133 ",kc.coin_pub"
1134 ",deposits.merchant_pub"
1135 ",wire_out.execution_date"
1136 ",deposits.amount_with_fee_val"
1137 ",deposits.amount_with_fee_frac"
1138 ",denom.fee_deposit_val"
1139 ",denom.fee_deposit_frac"
1140 ",denom.denom_pub"
1141 " FROM aggregation_tracking"
1142 " JOIN deposits"
1143 " USING (deposit_serial_id)"
1144 " JOIN known_coins kc"
1145 " USING (known_coin_id)"
1146 " JOIN denominations denom"
1147 " USING (denominations_serial)"
1148 " JOIN wire_out"
1149 " USING (wtid_raw)"
1150 " WHERE wtid_raw=$1;",
1151 1),
1152 /* Used in #postgres_lookup_transfer_by_deposit */
1153 GNUNET_PQ_make_prepare ("lookup_deposit_wtid",
1154 "SELECT"
1155 " aggregation_tracking.wtid_raw"
1156 ",wire_out.execution_date"
1157 ",amount_with_fee_val"
1158 ",amount_with_fee_frac"
1159 ",denom.fee_deposit_val"
1160 ",denom.fee_deposit_frac"
1161 " FROM deposits"
1162 " JOIN aggregation_tracking"
1163 " USING (deposit_serial_id)"
1164 " JOIN known_coins"
1165 " USING (known_coin_id)"
1166 " JOIN denominations denom"
1167 " USING (denominations_serial)"
1168 " JOIN wire_out"
1169 " USING (wtid_raw)"
1170 " WHERE coin_pub=$1"
1171 " AND h_contract_terms=$2"
1172 " AND h_wire=$3"
1173 " AND merchant_pub=$4;",
1174 4),
1175 /* Used in #postgres_insert_aggregation_tracking */
1176 GNUNET_PQ_make_prepare ("insert_aggregation_tracking",
1177 "INSERT INTO aggregation_tracking "
1178 "(deposit_serial_id"
1179 ",wtid_raw"
1180 ") VALUES "
1181 "($1, $2);",
1182 2),
1183 /* Used in #postgres_get_wire_fee() */
1184 GNUNET_PQ_make_prepare ("get_wire_fee",
1185 "SELECT "
1186 " start_date"
1187 ",end_date"
1188 ",wire_fee_val"
1189 ",wire_fee_frac"
1190 ",closing_fee_val"
1191 ",closing_fee_frac"
1192 ",master_sig"
1193 " FROM wire_fee"
1194 " WHERE wire_method=$1"
1195 " AND start_date <= $2"
1196 " AND end_date > $2;",
1197 2),
1198 /* Used in #postgres_insert_wire_fee */
1199 GNUNET_PQ_make_prepare ("insert_wire_fee",
1200 "INSERT INTO wire_fee "
1201 "(wire_method"
1202 ",start_date"
1203 ",end_date"
1204 ",wire_fee_val"
1205 ",wire_fee_frac"
1206 ",closing_fee_val"
1207 ",closing_fee_frac"
1208 ",master_sig"
1209 ") VALUES "
1210 "($1, $2, $3, $4, $5, $6, $7, $8);",
1211 8),
1212 /* Used in #postgres_store_wire_transfer_out */
1213 GNUNET_PQ_make_prepare ("insert_wire_out",
1214 "INSERT INTO wire_out "
1215 "(execution_date"
1216 ",wtid_raw"
1217 ",wire_target"
1218 ",exchange_account_section"
1219 ",amount_val"
1220 ",amount_frac"
1221 ") VALUES "
1222 "($1, $2, $3, $4, $5, $6);",
1223 6),
1224 /* Used in #postgres_wire_prepare_data_insert() to store
1225 wire transfer information before actually committing it with the bank */
1226 GNUNET_PQ_make_prepare ("wire_prepare_data_insert",
1227 "INSERT INTO prewire "
1228 "(type"
1229 ",buf"
1230 ") VALUES "
1231 "($1, $2);",
1232 2),
1233 /* Used in #postgres_wire_prepare_data_mark_finished() */
1234 GNUNET_PQ_make_prepare ("wire_prepare_data_mark_done",
1235 "UPDATE prewire"
1236 " SET finished=TRUE"
1237 " WHERE prewire_uuid=$1;",
1238 1),
1239 /* Used in #postgres_wire_prepare_data_mark_failed() */
1240 GNUNET_PQ_make_prepare ("wire_prepare_data_mark_failed",
1241 "UPDATE prewire"
1242 " SET failed=TRUE"
1243 " WHERE prewire_uuid=$1;",
1244 1),
1245 /* Used in #postgres_wire_prepare_data_get() */
1246 GNUNET_PQ_make_prepare ("wire_prepare_data_get",
1247 "SELECT"
1248 " prewire_uuid"
1249 ",type"
1250 ",buf"
1251 " FROM prewire"
1252 " WHERE finished=FALSE"
1253 " AND failed=FALSE"
1254 " ORDER BY prewire_uuid ASC"
1255 " LIMIT 1;",
1256 0),
1257 /* Used in #postgres_select_deposits_missing_wire */
1258 GNUNET_PQ_make_prepare ("deposits_get_overdue",
1259 "SELECT"
1260 " deposit_serial_id"
1261 ",coin_pub"
1262 ",amount_with_fee_val"
1263 ",amount_with_fee_frac"
1264 ",wire"
1265 ",wire_deadline"
1266 ",tiny"
1267 ",done"
1268 " FROM deposits d"
1269 " JOIN known_coins USING (known_coin_id)"
1270 " WHERE wire_deadline >= $1"
1271 " AND wire_deadline < $2"
1272 " AND NOT (EXISTS (SELECT 1"
1273 " FROM refunds"
1274 " JOIN deposits dx USING (deposit_serial_id)"
1275 " WHERE (dx.known_coin_id = d.known_coin_id))"
1276 " OR EXISTS (SELECT 1"
1277 " FROM aggregation_tracking"
1278 " WHERE (aggregation_tracking.deposit_serial_id = d.deposit_serial_id)))"
1279 " ORDER BY wire_deadline ASC",
1280 2),
1281 /* Used in #postgres_select_wire_out_above_serial_id() */
1282 GNUNET_PQ_make_prepare ("audit_get_wire_incr",
1283 "SELECT"
1284 " wireout_uuid"
1285 ",execution_date"
1286 ",wtid_raw"
1287 ",wire_target"
1288 ",amount_val"
1289 ",amount_frac"
1290 " FROM wire_out"
1291 " WHERE wireout_uuid>=$1"
1292 " ORDER BY wireout_uuid ASC;",
1293 1),
1294 /* Used in #postgres_select_wire_out_above_serial_id_by_account() */
1295 GNUNET_PQ_make_prepare ("audit_get_wire_incr_by_account",
1296 "SELECT"
1297 " wireout_uuid"
1298 ",execution_date"
1299 ",wtid_raw"
1300 ",wire_target"
1301 ",amount_val"
1302 ",amount_frac"
1303 " FROM wire_out"
1304 " WHERE wireout_uuid>=$1 AND exchange_account_section=$2"
1305 " ORDER BY wireout_uuid ASC;",
1306 2),
1307 /* Used in #postgres_insert_recoup_request() to store recoup
1308 information */
1309 GNUNET_PQ_make_prepare ("recoup_insert",
1310 "WITH rx AS"
1311 " (SELECT reserve_out_serial_id"
1312 " FROM reserves_out"
1313 " WHERE h_blind_ev=$7)"
1314 "INSERT INTO recoup "
1315 "(known_coin_id"
1316 ",coin_sig"
1317 ",coin_blind"
1318 ",amount_val"
1319 ",amount_frac"
1320 ",timestamp"
1321 ",reserve_out_serial_id"
1322 ") SELECT known_coin_id, $2, $3, $4, $5, $6, rx.reserve_out_serial_id"
1323 " FROM known_coins"
1324 " CROSS JOIN rx"
1325 " WHERE coin_pub=$1;",
1326 7),
1327 /* Used in #postgres_insert_recoup_refresh_request() to store recoup-refresh
1328 information */
1329 GNUNET_PQ_make_prepare ("recoup_refresh_insert",
1330 "WITH rrx AS"
1331 " (SELECT rrc_serial"
1332 " FROM refresh_revealed_coins"
1333 " WHERE h_coin_ev=$7)"
1334 "INSERT INTO recoup_refresh "
1335 "(known_coin_id"
1336 ",coin_sig"
1337 ",coin_blind"
1338 ",amount_val"
1339 ",amount_frac"
1340 ",timestamp"
1341 ",rrc_serial"
1342 ") SELECT known_coin_id, $2, $3, $4, $5, $6, rrx.rrc_serial"
1343 " FROM known_coins"
1344 " CROSS JOIN rrx"
1345 " WHERE coin_pub=$1;",
1346 7),
1347 /* Used in #postgres_select_recoup_above_serial_id() to obtain recoup transactions */
1348 GNUNET_PQ_make_prepare ("recoup_get_incr",
1349 "SELECT"
1350 " recoup_uuid"
1351 ",timestamp"
1352 ",reserves.reserve_pub"
1353 ",coins.coin_pub"
1354 ",coin_sig"
1355 ",coin_blind"
1356 ",ro.h_blind_ev"
1357 ",denoms.denom_pub_hash"
1358 ",coins.denom_sig"
1359 ",denoms.denom_pub"
1360 ",amount_val"
1361 ",amount_frac"
1362 " FROM recoup"
1363 " JOIN known_coins coins"
1364 " USING (known_coin_id)"
1365 " JOIN reserves_out ro"
1366 " USING (reserve_out_serial_id)"
1367 " JOIN reserves"
1368 " USING (reserve_uuid)"
1369 " JOIN denominations denoms"
1370 " ON (coins.denominations_serial = denoms.denominations_serial)"
1371 " WHERE recoup_uuid>=$1"
1372 " ORDER BY recoup_uuid ASC;",
1373 1),
1374 /* Used in #postgres_select_recoup_refresh_above_serial_id() to obtain
1375 recoup-refresh transactions */
1376 GNUNET_PQ_make_prepare ("recoup_refresh_get_incr",
1377 "SELECT"
1378 " recoup_refresh_uuid"
1379 ",timestamp"
1380 ",old_coins.coin_pub AS old_coin_pub"
1381 ",old_denoms.denom_pub_hash AS old_denom_pub_hash"
1382 ",new_coins.coin_pub As coin_pub"
1383 ",coin_sig"
1384 ",coin_blind"
1385 ",new_denoms.denom_pub AS denom_pub"
1386 ",rrc.h_coin_ev AS h_blind_ev"
1387 ",new_denoms.denom_pub_hash"
1388 ",new_coins.denom_sig AS denom_sig"
1389 ",amount_val"
1390 ",amount_frac"
1391 " FROM recoup_refresh"
1392 " INNER JOIN refresh_revealed_coins rrc"
1393 " USING (rrc_serial)"
1394 " INNER JOIN refresh_commitments rfc"
1395 " ON (rrc.melt_serial_id = rfc.melt_serial_id)"
1396 " INNER JOIN known_coins old_coins"
1397 " ON (rfc.old_known_coin_id = old_coins.known_coin_id)"
1398 " INNER JOIN known_coins new_coins"
1399 " ON (new_coins.known_coin_id = recoup_refresh.known_coin_id)"
1400 " INNER JOIN denominations new_denoms"
1401 " ON (new_coins.denominations_serial = new_denoms.denominations_serial)"
1402 " INNER JOIN denominations old_denoms"
1403 " ON (old_coins.denominations_serial = old_denoms.denominations_serial)"
1404 " WHERE recoup_refresh_uuid>=$1"
1405 " ORDER BY recoup_refresh_uuid ASC;",
1406 1),
1407 /* Used in #postgres_select_reserve_closed_above_serial_id() to
1408 obtain information about closed reserves */
1409 GNUNET_PQ_make_prepare ("reserves_close_get_incr",
1410 "SELECT"
1411 " close_uuid"
1412 ",reserves.reserve_pub"
1413 ",execution_date"
1414 ",wtid"
1415 ",receiver_account"
1416 ",amount_val"
1417 ",amount_frac"
1418 ",closing_fee_val"
1419 ",closing_fee_frac"
1420 " FROM reserves_close"
1421 " JOIN reserves"
1422 " USING (reserve_uuid)"
1423 " WHERE close_uuid>=$1"
1424 " ORDER BY close_uuid ASC;",
1425 1),
1426 /* Used in #postgres_get_reserve_history() to obtain recoup transactions
1427 for a reserve */
1428 GNUNET_PQ_make_prepare ("recoup_by_reserve",
1429 "SELECT"
1430 " coins.coin_pub"
1431 ",coin_sig"
1432 ",coin_blind"
1433 ",amount_val"
1434 ",amount_frac"
1435 ",timestamp"
1436 ",denoms.denom_pub_hash"
1437 ",coins.denom_sig"
1438 " FROM recoup"
1439 " JOIN known_coins coins"
1440 " USING (known_coin_id)"
1441 " JOIN denominations denoms"
1442 " USING (denominations_serial)"
1443 " JOIN reserves_out ro"
1444 " USING (reserve_out_serial_id)"
1445 " WHERE ro.reserve_uuid="
1446 " (SELECT reserve_uuid"
1447 " FROM reserves"
1448 " WHERE reserve_pub=$1);",
1449 1),
1450 /* Used in #postgres_get_coin_transactions() to obtain recoup transactions
1451 affecting old coins of refreshed coins */
1452 GNUNET_PQ_make_prepare ("recoup_by_old_coin",
1453 "SELECT"
1454 " coins.coin_pub"
1455 ",coin_sig"
1456 ",coin_blind"
1457 ",amount_val"
1458 ",amount_frac"
1459 ",timestamp"
1460 ",denoms.denom_pub_hash"
1461 ",coins.denom_sig"
1462 ",recoup_refresh_uuid"
1463 " FROM recoup_refresh"
1464 " JOIN known_coins coins"
1465 " USING (known_coin_id)"
1466 " JOIN denominations denoms"
1467 " USING (denominations_serial)"
1468 " WHERE rrc_serial IN"
1469 " (SELECT rrc.rrc_serial"
1470 " FROM refresh_commitments"
1471 " JOIN refresh_revealed_coins rrc"
1472 " USING (melt_serial_id)"
1473 " WHERE old_known_coin_id="
1474 " (SELECT known_coin_id"
1475 " FROM known_coins"
1476 " WHERE coin_pub=$1));",
1477 1),
1478 /* Used in #postgres_get_reserve_history() */
1479 GNUNET_PQ_make_prepare ("close_by_reserve",
1480 "SELECT"
1481 " amount_val"
1482 ",amount_frac"
1483 ",closing_fee_val"
1484 ",closing_fee_frac"
1485 ",execution_date"
1486 ",receiver_account"
1487 ",wtid"
1488 " FROM reserves_close"
1489 " WHERE reserve_uuid="
1490 " (SELECT reserve_uuid"
1491 " FROM reserves"
1492 " WHERE reserve_pub=$1);",
1493 1),
1494 /* Used in #postgres_get_expired_reserves() */
1495 GNUNET_PQ_make_prepare ("get_expired_reserves",
1496 "SELECT"
1497 " expiration_date"
1498 ",account_details"
1499 ",reserve_pub"
1500 ",current_balance_val"
1501 ",current_balance_frac"
1502 " FROM reserves"
1503 " WHERE expiration_date<=$1"
1504 " AND (current_balance_val != 0 "
1505 " OR current_balance_frac != 0)"
1506 " ORDER BY expiration_date ASC"
1507 " LIMIT 1;",
1508 1),
1509 /* Used in #postgres_get_coin_transactions() to obtain recoup transactions
1510 for a coin */
1511 GNUNET_PQ_make_prepare ("recoup_by_coin",
1512 "SELECT"
1513 " reserves.reserve_pub"
1514 ",denoms.denom_pub_hash"
1515 ",coin_sig"
1516 ",coin_blind"
1517 ",amount_val"
1518 ",amount_frac"
1519 ",timestamp"
1520 ",recoup_uuid"
1521 " FROM recoup"
1522 " JOIN reserves_out ro"
1523 " USING (reserve_out_serial_id)"
1524 " JOIN reserves"
1525 " USING (reserve_uuid)"
1526 " JOIN known_coins coins"
1527 " USING (known_coin_id)"
1528 " JOIN denominations denoms"
1529 " ON (denoms.denominations_serial = coins.denominations_serial)"
1530 " WHERE coins.coin_pub=$1;",
1531 1),
1532 /* Used in #postgres_get_coin_transactions() to obtain recoup transactions
1533 for a refreshed coin */
1534 GNUNET_PQ_make_prepare ("recoup_by_refreshed_coin",
1535 "SELECT"
1536 " old_coins.coin_pub AS old_coin_pub"
1537 ",coin_sig"
1538 ",coin_blind"
1539 ",amount_val"
1540 ",amount_frac"
1541 ",timestamp"
1542 ",denoms.denom_pub_hash"
1543 ",coins.denom_sig"
1544 ",recoup_refresh_uuid"
1545 " FROM recoup_refresh"
1546 " JOIN refresh_revealed_coins rrc"
1547 " USING (rrc_serial)"
1548 " JOIN refresh_commitments rfc"
1549 " ON (rrc.melt_serial_id = rfc.melt_serial_id)"
1550 " JOIN known_coins old_coins"
1551 " ON (rfc.old_known_coin_id = old_coins.known_coin_id)"
1552 " JOIN known_coins coins"
1553 " ON (recoup_refresh.known_coin_id = coins.known_coin_id)"
1554 " JOIN denominations denoms"
1555 " ON (denoms.denominations_serial = coins.denominations_serial)"
1556 " WHERE coins.coin_pub=$1;",
1557 1),
1558 /* Used in #postgres_get_reserve_by_h_blind() */
1559 GNUNET_PQ_make_prepare ("reserve_by_h_blind",
1560 "SELECT"
1561 " reserves.reserve_pub"
1562 " FROM reserves_out"
1563 " JOIN reserves"
1564 " USING (reserve_uuid)"
1565 " WHERE h_blind_ev=$1"
1566 " LIMIT 1;",
1567 1),
1568 /* Used in #postgres_get_old_coin_by_h_blind() */
1569 GNUNET_PQ_make_prepare ("old_coin_by_h_blind",
1570 "SELECT"
1571 " okc.coin_pub AS old_coin_pub"
1572 " FROM refresh_revealed_coins rrc"
1573 " JOIN refresh_commitments rcom USING (melt_serial_id)"
1574 " JOIN known_coins okc ON (rcom.old_known_coin_id = okc.known_coin_id)"
1575 " WHERE h_coin_ev=$1"
1576 " LIMIT 1;",
1577 1),
1578 /* Used in #postgres_lookup_auditor_timestamp() */
1579 GNUNET_PQ_make_prepare ("lookup_auditor_timestamp",
1580 "SELECT"
1581 " last_change"
1582 " FROM auditors"
1583 " WHERE auditor_pub=$1;",
1584 1),
1585 /* Used in #postgres_lookup_auditor_status() */
1586 GNUNET_PQ_make_prepare ("lookup_auditor_status",
1587 "SELECT"
1588 " auditor_url"
1589 ",is_active"
1590 " FROM auditors"
1591 " WHERE auditor_pub=$1;",
1592 1),
1593 /* Used in #postgres_lookup_wire_timestamp() */
1594 GNUNET_PQ_make_prepare ("lookup_wire_timestamp",
1595 "SELECT"
1596 " last_change"
1597 " FROM wire_accounts"
1598 " WHERE payto_uri=$1;",
1599 1),
1600 /* used in #postgres_insert_auditor() */
1601 GNUNET_PQ_make_prepare ("insert_auditor",
1602 "INSERT INTO auditors "
1603 "(auditor_pub"
1604 ",auditor_name"
1605 ",auditor_url"
1606 ",is_active"
1607 ",last_change"
1608 ") VALUES "
1609 "($1, $2, $3, true, $4);",
1610 4),
1611 /* used in #postgres_update_auditor() */
1612 GNUNET_PQ_make_prepare ("update_auditor",
1613 "UPDATE auditors"
1614 " SET"
1615 " auditor_url=$2"
1616 " ,auditor_name=$3"
1617 " ,is_active=$4"
1618 " ,last_change=$5"
1619 " WHERE auditor_pub=$1",
1620 5),
1621 /* used in #postgres_insert_wire() */
1622 GNUNET_PQ_make_prepare ("insert_wire",
1623 "INSERT INTO wire_accounts "
1624 "(payto_uri"
1625 ",master_sig"
1626 ",is_active"
1627 ",last_change"
1628 ") VALUES "
1629 "($1, $2, true, $3);",
1630 3),
1631 /* used in #postgres_update_wire() */
1632 GNUNET_PQ_make_prepare ("update_wire",
1633 "UPDATE wire_accounts"
1634 " SET"
1635 " is_active=$2"
1636 " ,last_change=$3"
1637 " WHERE payto_uri=$1",
1638 3),
1639 /* used in #postgres_update_wire() */
1640 GNUNET_PQ_make_prepare ("get_wire_accounts",
1641 "SELECT"
1642 " payto_uri"
1643 ",master_sig"
1644 " FROM wire_accounts"
1645 " WHERE is_active",
1646 0),
1647 /* used in #postgres_update_wire() */
1648 GNUNET_PQ_make_prepare ("get_wire_fees",
1649 "SELECT"
1650 " wire_fee_val"
1651 ",wire_fee_frac"
1652 ",closing_fee_val"
1653 ",closing_fee_frac"
1654 ",start_date"
1655 ",end_date"
1656 ",master_sig"
1657 " FROM wire_fee"
1658 " WHERE wire_method=$1",
1659 1),
1660 /* used in #postgres_insert_signkey_revocation() */
1661 GNUNET_PQ_make_prepare ("insert_signkey_revocation",
1662 "INSERT INTO signkey_revocations "
1663 "(esk_serial"
1664 ",master_sig"
1665 ") SELECT esk_serial, $2 "
1666 " FROM exchange_sign_keys"
1667 " WHERE exchange_pub=$1;",
1668 2),
1669 /* used in #postgres_insert_signkey_revocation() */
1670 GNUNET_PQ_make_prepare ("lookup_signkey_revocation",
1671 "SELECT "
1672 " master_sig"
1673 " FROM signkey_revocations"
1674 " WHERE esk_serial="
1675 " (SELECT esk_serial"
1676 " FROM exchange_sign_keys"
1677 " WHERE exchange_pub=$1);",
1678 1),
1679 /* used in #postgres_insert_signkey() */
1680 GNUNET_PQ_make_prepare ("insert_signkey",
1681 "INSERT INTO exchange_sign_keys "
1682 "(exchange_pub"
1683 ",valid_from"
1684 ",expire_sign"
1685 ",expire_legal"
1686 ",master_sig"
1687 ") VALUES "
1688 "($1, $2, $3, $4, $5);",
1689 5),
1690 /* used in #postgres_lookup_signing_key() */
1691 GNUNET_PQ_make_prepare ("lookup_signing_key",
1692 "SELECT"
1693 " valid_from"
1694 ",expire_sign"
1695 ",expire_legal"
1696 " FROM exchange_sign_keys"
1697 " WHERE exchange_pub=$1",
1698 1),
1699 /* used in #postgres_lookup_denomination_key() */
1700 GNUNET_PQ_make_prepare ("lookup_denomination_key",
1701 "SELECT"
1702 " valid_from"
1703 ",expire_withdraw"
1704 ",expire_deposit"
1705 ",expire_legal"
1706 ",coin_val"
1707 ",coin_frac"
1708 ",fee_withdraw_val"
1709 ",fee_withdraw_frac"
1710 ",fee_deposit_val"
1711 ",fee_deposit_frac"
1712 ",fee_refresh_val"
1713 ",fee_refresh_frac"
1714 ",fee_refund_val"
1715 ",fee_refund_frac"
1716 " FROM denominations"
1717 " WHERE denom_pub_hash=$1;",
1718 1),
1719 /* used in #postgres_insert_auditor_denom_sig() */
1720 GNUNET_PQ_make_prepare ("insert_auditor_denom_sig",
1721 "WITH ax AS"
1722 " (SELECT auditor_uuid"
1723 " FROM auditors"
1724 " WHERE auditor_pub=$1)"
1725 "INSERT INTO auditor_denom_sigs "
1726 "(auditor_uuid"
1727 ",denominations_serial"
1728 ",auditor_sig"
1729 ") SELECT ax.auditor_uuid, denominations_serial, $3 "
1730 " FROM denominations"
1731 " CROSS JOIN ax"
1732 " WHERE denom_pub_hash=$2;",
1733 3),
1734 /* used in #postgres_select_auditor_denom_sig() */
1735 GNUNET_PQ_make_prepare ("select_auditor_denom_sig",
1736 "SELECT"
1737 " auditor_sig"
1738 " FROM auditor_denom_sigs"
1739 " WHERE auditor_uuid="
1740 " (SELECT auditor_uuid"
1741 " FROM auditors"
1742 " WHERE auditor_pub=$1)"
1743 " AND denominations_serial="
1744 " (SELECT denominations_serial"
1745 " FROM denominations"
1746 " WHERE denom_pub_hash=$2);",
1747 2),
1748 /* used in #postgres_lookup_wire_fee_by_time() */
1749 GNUNET_PQ_make_prepare ("lookup_wire_fee_by_time",
1750 "SELECT"
1751 " wire_fee_val"
1752 ",wire_fee_frac"
1753 ",closing_fee_val"
1754 ",closing_fee_frac"
1755 " FROM wire_fee"
1756 " WHERE wire_method=$1"
1757 " AND end_date > $2"
1758 " AND start_date < $3;",
1759 1),
1760 /* used in #postgres_commit */
1761 GNUNET_PQ_make_prepare ("do_commit",
1762 "COMMIT",
1763 0),
1764 /* used in #postgres_lookup_serial_by_table() */
1765 GNUNET_PQ_make_prepare ("select_serial_by_table_denominations",
1766 "SELECT"
1767 " denominations_serial AS serial"
1768 " FROM denominations"
1769 " ORDER BY denominations_serial DESC"
1770 " LIMIT 1;",
1771 0),
1772 GNUNET_PQ_make_prepare ("select_serial_by_table_denomination_revocations",
1773 "SELECT"
1774 " denom_revocations_serial_id AS serial"
1775 " FROM denomination_revocations"
1776 " ORDER BY denom_revocations_serial_id DESC"
1777 " LIMIT 1;",
1778 0),
1779 GNUNET_PQ_make_prepare ("select_serial_by_table_reserves",
1780 "SELECT"
1781 " reserve_uuid AS serial"
1782 " FROM reserves"
1783 " ORDER BY reserve_uuid DESC"
1784 " LIMIT 1;",
1785 0),
1786 GNUNET_PQ_make_prepare ("select_serial_by_table_reserves_in",
1787 "SELECT"
1788 " reserve_in_serial_id AS serial"
1789 " FROM reserves_in"
1790 " ORDER BY reserve_in_serial_id DESC"
1791 " LIMIT 1;",
1792 0),
1793 GNUNET_PQ_make_prepare ("select_serial_by_table_reserves_close",
1794 "SELECT"
1795 " close_uuid AS serial"
1796 " FROM reserves_close"
1797 " ORDER BY close_uuid DESC"
1798 " LIMIT 1;",
1799 0),
1800 GNUNET_PQ_make_prepare ("select_serial_by_table_reserves_out",
1801 "SELECT"
1802 " reserve_out_serial_id AS serial"
1803 " FROM reserves_out"
1804 " ORDER BY reserve_out_serial_id DESC"
1805 " LIMIT 1;",
1806 0),
1807 GNUNET_PQ_make_prepare ("select_serial_by_table_auditors",
1808 "SELECT"
1809 " auditor_uuid AS serial"
1810 " FROM auditors"
1811 " ORDER BY auditor_uuid DESC"
1812 " LIMIT 1;",
1813 0),
1814 GNUNET_PQ_make_prepare ("select_serial_by_table_auditor_denom_sigs",
1815 "SELECT"
1816 " auditor_denom_serial AS serial"
1817 " FROM auditor_denom_sigs"
1818 " ORDER BY auditor_denom_serial DESC"
1819 " LIMIT 1;",
1820 0),
1821 GNUNET_PQ_make_prepare ("select_serial_by_table_exchange_sign_keys",
1822 "SELECT"
1823 " esk_serial AS serial"
1824 " FROM exchange_sign_keys"
1825 " ORDER BY esk_serial DESC"
1826 " LIMIT 1;",
1827 0),
1828 GNUNET_PQ_make_prepare ("select_serial_by_table_signkey_revocations",
1829 "SELECT"
1830 " signkey_revocations_serial_id AS serial"
1831 " FROM signkey_revocations"
1832 " ORDER BY signkey_revocations_serial_id DESC"
1833 " LIMIT 1;",
1834 0),
1835 GNUNET_PQ_make_prepare ("select_serial_by_table_known_coins",
1836 "SELECT"
1837 " known_coin_id AS serial"
1838 " FROM known_coins"
1839 " ORDER BY known_coin_id DESC"
1840 " LIMIT 1;",
1841 0),
1842 GNUNET_PQ_make_prepare ("select_serial_by_table_refresh_commitments",
1843 "SELECT"
1844 " melt_serial_id AS serial"
1845 " FROM refresh_commitments"
1846 " ORDER BY melt_serial_id DESC"
1847 " LIMIT 1;",
1848 0),
1849 GNUNET_PQ_make_prepare ("select_serial_by_table_refresh_revealed_coins",
1850 "SELECT"
1851 " rrc_serial AS serial"
1852 " FROM refresh_revealed_coins"
1853 " ORDER BY rrc_serial DESC"
1854 " LIMIT 1;",
1855 0),
1856 GNUNET_PQ_make_prepare ("select_serial_by_table_refresh_transfer_keys",
1857 "SELECT"
1858 " rtc_serial AS serial"
1859 " FROM refresh_transfer_keys"
1860 " ORDER BY rtc_serial DESC"
1861 " LIMIT 1;",
1862 0),
1863 GNUNET_PQ_make_prepare ("select_serial_by_table_deposits",
1864 "SELECT"
1865 " deposit_serial_id AS serial"
1866 " FROM deposits"
1867 " ORDER BY deposit_serial_id DESC"
1868 " LIMIT 1;",
1869 0),
1870 GNUNET_PQ_make_prepare ("select_serial_by_table_refunds",
1871 "SELECT"
1872 " refund_serial_id AS serial"
1873 " FROM refunds"
1874 " ORDER BY refund_serial_id DESC"
1875 " LIMIT 1;",
1876 0),
1877 GNUNET_PQ_make_prepare ("select_serial_by_table_wire_out",
1878 "SELECT"
1879 " wireout_uuid AS serial"
1880 " FROM wire_out"
1881 " ORDER BY wireout_uuid DESC"
1882 " LIMIT 1;",
1883 0),
1884 GNUNET_PQ_make_prepare ("select_serial_by_table_aggregation_tracking",
1885 "SELECT"
1886 " aggregation_serial_id AS serial"
1887 " FROM aggregation_tracking"
1888 " ORDER BY aggregation_serial_id DESC"
1889 " LIMIT 1;",
1890 0),
1891 GNUNET_PQ_make_prepare ("select_serial_by_table_wire_fee",
1892 "SELECT"
1893 " wire_fee_serial AS serial"
1894 " FROM wire_fee"
1895 " ORDER BY wire_fee_serial DESC"
1896 " LIMIT 1;",
1897 0),
1898 GNUNET_PQ_make_prepare ("select_serial_by_table_recoup",
1899 "SELECT"
1900 " recoup_uuid AS serial"
1901 " FROM recoup"
1902 " ORDER BY recoup_uuid DESC"
1903 " LIMIT 1;",
1904 0),
1905 GNUNET_PQ_make_prepare ("select_serial_by_table_recoup_refresh",
1906 "SELECT"
1907 " recoup_refresh_uuid AS serial"
1908 " FROM recoup_refresh"
1909 " ORDER BY recoup_refresh_uuid DESC"
1910 " LIMIT 1;",
1911 0),
1912 /* For postgres_lookup_records_by_table */
1913 GNUNET_PQ_make_prepare ("select_above_serial_by_table_denominations",
1914 "SELECT"
1915 " denominations_serial AS serial"
1916 ",denom_pub"
1917 ",master_sig"
1918 ",valid_from"
1919 ",expire_withdraw"
1920 ",expire_deposit"
1921 ",expire_legal"
1922 ",coin_val"
1923 ",coin_frac"
1924 ",fee_withdraw_val"
1925 ",fee_withdraw_frac"
1926 ",fee_deposit_val"
1927 ",fee_deposit_frac"
1928 ",fee_refresh_val"
1929 ",fee_refresh_frac"
1930 ",fee_refund_val"
1931 ",fee_refund_frac"
1932 " FROM denominations"
1933 " WHERE denominations_serial > $1"
1934 " ORDER BY denominations_serial ASC;",
1935 1),
1936 GNUNET_PQ_make_prepare (
1937 "select_above_serial_by_table_denomination_revocations",
1938 "SELECT"
1939 " denom_revocations_serial_id AS serial"
1940 ",master_sig"
1941 ",denominations_serial"
1942 " FROM denomination_revocations"
1943 " WHERE denom_revocations_serial_id > $1"
1944 " ORDER BY denom_revocations_serial_id ASC;",
1945 1),
1946 GNUNET_PQ_make_prepare ("select_above_serial_by_table_reserves",
1947 "SELECT"
1948 " reserve_uuid AS serial"
1949 ",reserve_pub"
1950 ",account_details"
1951 ",current_balance_val"
1952 ",current_balance_frac"
1953 ",expiration_date"
1954 ",gc_date"
1955 " FROM reserves"
1956 " WHERE reserve_uuid > $1"
1957 " ORDER BY reserve_uuid ASC;",
1958 1),
1959 GNUNET_PQ_make_prepare ("select_above_serial_by_table_reserves_in",
1960 "SELECT"
1961 " reserve_in_serial_id AS serial"
1962 ",wire_reference"
1963 ",credit_val"
1964 ",credit_frac"
1965 ",sender_account_details"
1966 ",exchange_account_section"
1967 ",execution_date"
1968 ",reserve_uuid"
1969 " FROM reserves_in"
1970 " WHERE reserve_in_serial_id > $1"
1971 " ORDER BY reserve_in_serial_id ASC;",
1972 1),
1973 GNUNET_PQ_make_prepare ("select_above_serial_by_table_reserves_close",
1974 "SELECT"
1975 " close_uuid AS serial"
1976 ",execution_date"
1977 ",wtid"
1978 ",receiver_account"
1979 ",amount_val"
1980 ",amount_frac"
1981 ",closing_fee_val"
1982 ",closing_fee_frac"
1983 ",reserve_uuid"
1984 " FROM reserves_close"
1985 " WHERE close_uuid > $1"
1986 " ORDER BY close_uuid ASC;",
1987 1),
1988 GNUNET_PQ_make_prepare ("select_above_serial_by_table_reserves_out",
1989 "SELECT"
1990 " reserve_out_serial_id AS serial"
1991 ",h_blind_ev"
1992 ",denom_sig"
1993 ",reserve_sig"
1994 ",execution_date"
1995 ",amount_with_fee_val"
1996 ",amount_with_fee_frac"
1997 ",reserve_uuid"
1998 ",denominations_serial"
1999 " FROM reserves_out"
2000 " WHERE reserve_out_serial_id > $1"
2001 " ORDER BY reserve_out_serial_id ASC;",
2002 1),
2003 GNUNET_PQ_make_prepare ("select_above_serial_by_table_auditors",
2004 "SELECT"
2005 " auditor_uuid AS serial"
2006 ",auditor_pub"
2007 ",auditor_name"
2008 ",auditor_url"
2009 ",is_active"
2010 ",last_change"
2011 " FROM auditors"
2012 " WHERE auditor_uuid > $1"
2013 " ORDER BY auditor_uuid ASC;",
2014 1),
2015 GNUNET_PQ_make_prepare ("select_above_serial_by_table_auditor_denom_sigs",
2016 "SELECT"
2017 " auditor_denom_serial AS serial"
2018 ",auditor_uuid"
2019 ",denominations_serial"
2020 ",auditor_sig"
2021 " FROM auditor_denom_sigs"
2022 " WHERE auditor_denom_serial > $1"
2023 " ORDER BY auditor_denom_serial ASC;",
2024 1),
2025 GNUNET_PQ_make_prepare ("select_above_serial_by_table_exchange_sign_keys",
2026 "SELECT"
2027 " esk_serial AS serial"
2028 ",exchange_pub"
2029 ",master_sig"
2030 ",valid_from"
2031 ",expire_sign"
2032 ",expire_legal"
2033 " FROM exchange_sign_keys"
2034 " WHERE esk_serial > $1"
2035 " ORDER BY esk_serial ASC;",
2036 1),
2037 GNUNET_PQ_make_prepare (
2038 "select_above_serial_by_table_signkey_revocations",
2039 "SELECT"
2040 " signkey_revocations_serial_id AS serial"
2041 ",esk_serial"
2042 ",master_sig"
2043 " FROM signkey_revocations"
2044 " WHERE signkey_revocations_serial_id > $1"
2045 " ORDER BY signkey_revocations_serial_id ASC;",
2046 1),
2047 GNUNET_PQ_make_prepare ("select_above_serial_by_table_known_coins",
2048 "SELECT"
2049 " known_coin_id AS serial"
2050 ",coin_pub"
2051 ",denom_sig"
2052 ",denominations_serial"
2053 " FROM known_coins"
2054 " WHERE known_coin_id > $1"
2055 " ORDER BY known_coin_id ASC;",
2056 1),
2057 GNUNET_PQ_make_prepare (
2058 "select_above_serial_by_table_refresh_commitments",
2059 "SELECT"
2060 " melt_serial_id AS serial"
2061 ",rc"
2062 ",old_known_coin_id"
2063 ",old_coin_sig"
2064 ",amount_with_fee_val"
2065 ",amount_with_fee_frac"
2066 ",noreveal_index"
2067 " FROM refresh_commitments"
2068 " WHERE melt_serial_id > $1"
2069 " ORDER BY melt_serial_id ASC;",
2070 1),
2071 GNUNET_PQ_make_prepare (
2072 "select_above_serial_by_table_refresh_revealed_coins",
2073 "SELECT"
2074 " rrc_serial AS serial"
2075 ",freshcoin_index"
2076 ",link_sig"
2077 ",coin_ev"
2078 ",h_coin_ev"
2079 ",ev_sig"
2080 ",melt_serial_id"
2081 ",denominations_serial"
2082 " FROM refresh_revealed_coins"
2083 " WHERE rrc_serial > $1"
2084 " ORDER BY rrc_serial ASC;",
2085 1),
2086 GNUNET_PQ_make_prepare (
2087 "select_above_serial_by_table_refresh_transfer_keys",
2088 "SELECT"
2089 " rtc_serial AS serial"
2090 ",transfer_pub"
2091 ",transfer_privs"
2092 ",melt_serial_id"
2093 " FROM refresh_transfer_keys"
2094 " WHERE rtc_serial > $1"
2095 " ORDER BY rtc_serial ASC;",
2096 1),
2097 GNUNET_PQ_make_prepare ("select_above_serial_by_table_deposits",
2098 "SELECT"
2099 " deposit_serial_id AS serial"
2100 ",amount_with_fee_val"
2101 ",amount_with_fee_frac"
2102 ",wallet_timestamp"
2103 ",exchange_timestamp"
2104 ",refund_deadline"
2105 ",wire_deadline"
2106 ",merchant_pub"
2107 ",h_contract_terms"
2108 ",h_wire"
2109 ",coin_sig"
2110 ",wire"
2111 ",tiny"
2112 ",done"
2113 ",known_coin_id"
2114 " FROM deposits"
2115 " WHERE deposit_serial_id > $1"
2116 " ORDER BY deposit_serial_id ASC;",
2117 1),
2118 GNUNET_PQ_make_prepare ("select_above_serial_by_table_refunds",
2119 "SELECT"
2120 " refund_serial_id AS serial"
2121 ",merchant_sig"
2122 ",rtransaction_id"
2123 ",amount_with_fee_val"
2124 ",amount_with_fee_frac"
2125 ",deposit_serial_id"
2126 " FROM refunds"
2127 " WHERE refund_serial_id > $1"
2128 " ORDER BY refund_serial_id ASC;",
2129 1),
2130 GNUNET_PQ_make_prepare ("select_above_serial_by_table_wire_out",
2131 "SELECT"
2132 " wireout_uuid AS serial"
2133 ",execution_date"
2134 ",wtid_raw"
2135 ",wire_target"
2136 ",exchange_account_section"
2137 ",amount_val"
2138 ",amount_frac"
2139 " FROM wire_out"
2140 " WHERE wireout_uuid > $1"
2141 " ORDER BY wireout_uuid ASC;",
2142 1),
2143 GNUNET_PQ_make_prepare (
2144 "select_above_serial_by_table_aggregation_tracking",
2145 "SELECT"
2146 " aggregation_serial_id AS serial"
2147 ",deposit_serial_id"
2148 ",wtid_raw"
2149 " FROM aggregation_tracking"
2150 " WHERE aggregation_serial_id > $1"
2151 " ORDER BY aggregation_serial_id ASC;",
2152 1),
2153 GNUNET_PQ_make_prepare ("select_above_serial_by_table_wire_fee",
2154 "SELECT"
2155 " wire_fee_serial AS serial"
2156 ",wire_method"
2157 ",start_date"
2158 ",end_date"
2159 ",wire_fee_val"
2160 ",wire_fee_frac"
2161 ",closing_fee_val"
2162 ",closing_fee_frac"
2163 ",master_sig"
2164 " FROM wire_fee"
2165 " WHERE wire_fee_serial > $1"
2166 " ORDER BY wire_fee_serial ASC;",
2167 1),
2168 GNUNET_PQ_make_prepare ("select_above_serial_by_table_recoup",
2169 "SELECT"
2170 " recoup_uuid AS serial"
2171 ",coin_sig"
2172 ",coin_blind"
2173 ",amount_val"
2174 ",amount_frac"
2175 ",timestamp"
2176 ",known_coin_id"
2177 ",reserve_out_serial_id"
2178 " FROM recoup"
2179 " WHERE recoup_uuid > $1"
2180 " ORDER BY recoup_uuid ASC;",
2181 1),
2182 GNUNET_PQ_make_prepare ("select_above_serial_by_table_recoup_refresh",
2183 "SELECT"
2184 " recoup_refresh_uuid AS serial"
2185 ",coin_sig"
2186 ",coin_blind"
2187 ",amount_val"
2188 ",amount_frac"
2189 ",timestamp"
2190 ",known_coin_id"
2191 ",rrc_serial"
2192 " FROM recoup_refresh"
2193 " WHERE recoup_refresh_uuid > $1"
2194 " ORDER BY recoup_refresh_uuid ASC;",
2195 1),
2196 /* For postgres_insert_records_by_table */
2197 GNUNET_PQ_make_prepare ("insert_into_table_denominations",
2198 "INSERT INTO denominations"
2199 "(denominations_serial"
2200 ",denom_pub_hash"
2201 ",denom_pub"
2202 ",master_sig"
2203 ",valid_from"
2204 ",expire_withdraw"
2205 ",expire_deposit"
2206 ",expire_legal"
2207 ",coin_val"
2208 ",coin_frac"
2209 ",fee_withdraw_val"
2210 ",fee_withdraw_frac"
2211 ",fee_deposit_val"
2212 ",fee_deposit_frac"
2213 ",fee_refresh_val"
2214 ",fee_refresh_frac"
2215 ",fee_refund_val"
2216 ",fee_refund_frac"
2217 ") VALUES "
2218 "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10,"
2219 " $11, $12, $13, $14, $15, $16, $17, $18);",
2220 18),
2221 GNUNET_PQ_make_prepare ("insert_into_table_denomination_revocations",
2222 "INSERT INTO denomination_revocations"
2223 "(denom_revocations_serial_id"
2224 ",master_sig"
2225 ",denominations_serial"
2226 ") VALUES "
2227 "($1, $2, $3);",
2228 3),
2229 GNUNET_PQ_make_prepare ("insert_into_table_reserves",
2230 "INSERT INTO reserves"
2231 "(reserve_uuid"
2232 ",reserve_pub"
2233 ",account_details"
2234 ",current_balance_val"
2235 ",current_balance_frac"
2236 ",expiration_date"
2237 ",gc_date"
2238 ") VALUES "
2239 "($1, $2, $3, $4, $5, $6, $7);",
2240 7),
2241 GNUNET_PQ_make_prepare ("insert_into_table_reserves_in",
2242 "INSERT INTO reserves_in"
2243 "(reserve_in_serial_id"
2244 ",wire_reference"
2245 ",credit_val"
2246 ",credit_frac"
2247 ",sender_account_details"
2248 ",exchange_account_section"
2249 ",execution_date"
2250 ",reserve_uuid"
2251 ") VALUES "
2252 "($1, $2, $3, $4, $5, $6, $7, $8);",
2253 8),
2254 GNUNET_PQ_make_prepare ("insert_into_table_reserves_close",
2255 "INSERT INTO reserves_close"
2256 "(close_uuid"
2257 ",execution_date"
2258 ",wtid"
2259 ",receiver_account"
2260 ",amount_val"
2261 ",amount_frac"
2262 ",closing_fee_val"
2263 ",closing_fee_frac"
2264 ",reserve_uuid"
2265 ") VALUES "
2266 "($1, $2, $3, $4, $5, $6, $7, $8, $9);",
2267 9),
2268 GNUNET_PQ_make_prepare ("insert_into_table_reserves_out",
2269 "INSERT INTO reserves_out"
2270 "(reserve_out_serial_id"
2271 ",h_blind_ev"
2272 ",denom_sig"
2273 ",reserve_sig"
2274 ",execution_date"
2275 ",amount_with_fee_val"
2276 ",amount_with_fee_frac"
2277 ",reserve_uuid"
2278 ",denominations_serial"
2279 ") VALUES "
2280 "($1, $2, $3, $4, $5, $6, $7, $8, $9);",
2281 9),
2282 GNUNET_PQ_make_prepare ("insert_into_table_auditors",
2283 "INSERT INTO auditors"
2284 "(auditor_uuid"
2285 ",auditor_pub"
2286 ",auditor_name"
2287 ",auditor_url"
2288 ",is_active"
2289 ",last_change"
2290 ") VALUES "
2291 "($1, $2, $3, $4, $5, $6);",
2292 6),
2293 GNUNET_PQ_make_prepare ("insert_into_table_auditor_denom_sigs",
2294 "INSERT INTO auditor_denom_sigs"
2295 "(auditor_denom_serial"
2296 ",auditor_uuid"
2297 ",denominations_serial"
2298 ",auditor_sig"
2299 ") VALUES "
2300 "($1, $2, $3, $4);",
2301 4),
2302 GNUNET_PQ_make_prepare ("insert_into_table_exchange_sign_keys",
2303 "INSERT INTO exchange_sign_keys"
2304 "(esk_serial"
2305 ",exchange_pub"
2306 ",master_sig"
2307 ",valid_from"
2308 ",expire_sign"
2309 ",expire_legal"
2310 ") VALUES "
2311 "($1, $2, $3, $4, $5, $6);",
2312 6),
2313 GNUNET_PQ_make_prepare ("insert_into_table_signkey_revocations",
2314 "INSERT INTO signkey_revocations"
2315 "(signkey_revocations_serial_id"
2316 ",esk_serial"
2317 ",master_sig"
2318 ") VALUES "
2319 "($1, $2, $3);",
2320 3),
2321 GNUNET_PQ_make_prepare ("insert_into_table_known_coins",
2322 "INSERT INTO known_coins"
2323 "(known_coin_id"
2324 ",coin_pub"
2325 ",denom_sig"
2326 ",denominations_serial"
2327 ") VALUES "
2328 "($1, $2, $3, $4);",
2329 4),
2330 GNUNET_PQ_make_prepare ("insert_into_table_refresh_commitments",
2331 "INSERT INTO refresh_commitments"
2332 "(melt_serial_id"
2333 ",rc"
2334 ",old_coin_sig"
2335 ",amount_with_fee_val"
2336 ",amount_with_fee_frac"
2337 ",noreveal_index"
2338 ",old_known_coin_id"
2339 ") VALUES "
2340 "($1, $2, $3, $4, $5, $6, $7);",
2341 7),
2342 GNUNET_PQ_make_prepare ("insert_into_table_refresh_revealed_coins",
2343 "INSERT INTO refresh_revealed_coins"
2344 "(rrc_serial"
2345 ",freshcoin_index"
2346 ",link_sig"
2347 ",coin_ev"
2348 ",h_coin_ev"
2349 ",ev_sig"
2350 ",denominations_serial"
2351 ",melt_serial_id"
2352 ") VALUES "
2353 "($1, $2, $3, $4, $5, $6, $7, $8);",
2354 8),
2355 GNUNET_PQ_make_prepare ("insert_into_table_refresh_transfer_keys",
2356 "INSERT INTO refresh_transfer_keys"
2357 "(rtc_serial"
2358 ",transfer_pub"
2359 ",transfer_privs"
2360 ",melt_serial_id"
2361 ") VALUES "
2362 "($1, $2, $3, $4);",
2363 4),
2364 GNUNET_PQ_make_prepare ("insert_into_table_deposits",
2365 "INSERT INTO deposits"
2366 "(deposit_serial_id"
2367 ",amount_with_fee_val"
2368 ",amount_with_fee_frac"
2369 ",wallet_timestamp"
2370 ",exchange_timestamp"
2371 ",refund_deadline"
2372 ",wire_deadline"
2373 ",merchant_pub"
2374 ",h_contract_terms"
2375 ",h_wire"
2376 ",coin_sig"
2377 ",wire"
2378 ",tiny"
2379 ",done"
2380 ",known_coin_id"
2381 ") VALUES "
2382 "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10,"
2383 " $11, $12, $13, $14, $15);",
2384 15),
2385 GNUNET_PQ_make_prepare ("insert_into_table_refunds",
2386 "INSERT INTO refunds"
2387 "(refund_serial_id"
2388 ",merchant_sig"
2389 ",rtransaction_id"
2390 ",amount_with_fee_val"
2391 ",amount_with_fee_frac"
2392 ",deposit_serial_id"
2393 ") VALUES "
2394 "($1, $2, $3, $4, $5, $6);",
2395 6),
2396 GNUNET_PQ_make_prepare ("insert_into_table_wire_out",
2397 "INSERT INTO wire_out"
2398 "(wireout_uuid"
2399 ",execution_date"
2400 ",wtid_raw"
2401 ",wire_target"
2402 ",exchange_account_section"
2403 ",amount_val"
2404 ",amount_frac"
2405 ") VALUES "
2406 "($1, $2, $3, $4, $5, $6, $7);",
2407 7),
2408 GNUNET_PQ_make_prepare ("insert_into_table_aggregation_tracking",
2409 "INSERT INTO aggregation_tracking"
2410 "(aggregation_serial_id"
2411 ",deposit_serial_id"
2412 ",wtid_raw"
2413 ") VALUES "
2414 "($1, $2, $3);",
2415 3),
2416 GNUNET_PQ_make_prepare ("insert_into_table_wire_fee",
2417 "INSERT INTO wire_fee"
2418 "(wire_fee_serial"
2419 ",wire_method"
2420 ",start_date"
2421 ",end_date"
2422 ",wire_fee_val"
2423 ",wire_fee_frac"
2424 ",closing_fee_val"
2425 ",closing_fee_frac"
2426 ",master_sig"
2427 ") VALUES "
2428 "($1, $2, $3, $4, $5, $6, $7, $8, $9);",
2429 9),
2430 GNUNET_PQ_make_prepare ("insert_into_table_recoup",
2431 "INSERT INTO recoup"
2432 "(recoup_uuid"
2433 ",coin_sig"
2434 ",coin_blind"
2435 ",amount_val"
2436 ",amount_frac"
2437 ",timestamp"
2438 ",known_coin_id"
2439 ",reserve_out_serial_id"
2440 ") VALUES "
2441 "($1, $2, $3, $4, $5, $6, $7, $8);",
2442 8),
2443 GNUNET_PQ_make_prepare ("insert_into_table_recoup_refresh",
2444 "INSERT INTO recoup_refresh"
2445 "(recoup_refresh_uuid"
2446 ",coin_sig"
2447 ",coin_blind"
2448 ",amount_val"
2449 ",amount_frac"
2450 ",timestamp"
2451 ",known_coin_id"
2452 ",rrc_serial"
2453 ") VALUES "
2454 "($1, $2, $3, $4, $5, $6, $7, $8);",
2455 8),
2456
2457 /* Used in #postgres_begin_shard() */
2458 GNUNET_PQ_make_prepare ("get_open_shard",
2459 "SELECT"
2460 " start_row"
2461 ",end_row"
2462 " FROM work_shards"
2463 " WHERE job_name=$1"
2464 " AND last_attempt<$2"
2465 " AND completed=FALSE"
2466 " ORDER BY last_attempt ASC"
2467 " LIMIT 1;",
2468 2),
2469 GNUNET_PQ_make_prepare ("reclaim_shard",
2470 "UPDATE work_shards"
2471 " SET last_attempt=$2"
2472 " WHERE job_name=$1"
2473 " AND start_row=$3"
2474 " AND end_row=$4",
2475 4),
2476 GNUNET_PQ_make_prepare ("get_last_shard",
2477 "SELECT"
2478 " end_row"
2479 " FROM work_shards"
2480 " WHERE job_name=$1"
2481 " ORDER BY end_row DESC"
2482 " LIMIT 1;",
2483 1),
2484 GNUNET_PQ_make_prepare ("claim_next_shard",
2485 "INSERT INTO work_shards"
2486 "(job_name"
2487 ",last_attempt"
2488 ",start_row"
2489 ",end_row"
2490 ") VALUES "
2491 "($1, $2, $3, $4);",
2492 4),
2493 /* Used in #postgres_complete_shard() */
2494 GNUNET_PQ_make_prepare ("complete_shard",
2495 "UPDATE work_shards"
2496 " SET completed=TRUE"
2497 " WHERE job_name=$1"
2498 " AND start_row=$2"
2499 " AND end_row=$3",
2500 3),
2501 GNUNET_PQ_PREPARED_STATEMENT_END
2502 };
2503
2504 ret = GNUNET_PQ_prepare_statements (sess->conn,
2505 ps);
2506 if (GNUNET_OK != ret)
2507 return ret;
2508 sess->init = true;
2509 return GNUNET_OK;
2510}
2511
2512
2513/**
226 * Get the thread-local database-handle. 2514 * Get the thread-local database-handle.
227 * Connect to the db if the connection does not exist yet. 2515 * Connect to the db if the connection does not exist yet.
228 * 2516 *
229 * @param cls the `struct PostgresClosure` with the plugin-specific state 2517 * @param pc the plugin-specific state
2518 * @param skip_prepare true if we should skip prepared statement setup
230 * @return the database connection, or NULL on error 2519 * @return the database connection, or NULL on error
231 */ 2520 */
232static struct TALER_EXCHANGEDB_Session * 2521static struct TALER_EXCHANGEDB_Session *
233postgres_get_session (void *cls) 2522internal_get_session (struct PostgresClosure *pc,
2523 bool skip_prepare)
234{ 2524{
235 struct PostgresClosure *pc = cls;
236 struct GNUNET_PQ_Context *db_conn; 2525 struct GNUNET_PQ_Context *db_conn;
237 struct TALER_EXCHANGEDB_Session *session; 2526 struct TALER_EXCHANGEDB_Session *session;
238 2527
@@ -267,2250 +2556,26 @@ postgres_get_session (void *cls)
267#else 2556#else
268 struct GNUNET_PQ_ExecuteStatement *es = NULL; 2557 struct GNUNET_PQ_ExecuteStatement *es = NULL;
269#endif 2558#endif
270 struct GNUNET_PQ_PreparedStatement ps[] = {
271 /* Used in #postgres_insert_denomination_info() and
272 #postgres_add_denomination_key() */
273 GNUNET_PQ_make_prepare ("denomination_insert",
274 "INSERT INTO denominations "
275 "(denom_pub_hash"
276 ",denom_pub"
277 ",master_sig"
278 ",valid_from"
279 ",expire_withdraw"
280 ",expire_deposit"
281 ",expire_legal"
282 ",coin_val" /* value of this denom */
283 ",coin_frac" /* fractional value of this denom */
284 ",fee_withdraw_val"
285 ",fee_withdraw_frac"
286 ",fee_deposit_val"
287 ",fee_deposit_frac"
288 ",fee_refresh_val"
289 ",fee_refresh_frac"
290 ",fee_refund_val"
291 ",fee_refund_frac"
292 ") VALUES "
293 "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10,"
294 " $11, $12, $13, $14, $15, $16, $17);",
295 17),
296 /* Used in #postgres_iterate_denomination_info() */
297 GNUNET_PQ_make_prepare ("denomination_iterate",
298 "SELECT"
299 " master_sig"
300 ",valid_from"
301 ",expire_withdraw"
302 ",expire_deposit"
303 ",expire_legal"
304 ",coin_val" /* value of this denom */
305 ",coin_frac" /* fractional value of this denom */
306 ",fee_withdraw_val"
307 ",fee_withdraw_frac"
308 ",fee_deposit_val"
309 ",fee_deposit_frac"
310 ",fee_refresh_val"
311 ",fee_refresh_frac"
312 ",fee_refund_val"
313 ",fee_refund_frac"
314 ",denom_pub"
315 " FROM denominations;",
316 0),
317 /* Used in #postgres_iterate_denominations() */
318 GNUNET_PQ_make_prepare ("select_denominations",
319 "SELECT"
320 " denominations.master_sig"
321 ",denom_revocations_serial_id IS NOT NULL AS revoked"
322 ",valid_from"
323 ",expire_withdraw"
324 ",expire_deposit"
325 ",expire_legal"
326 ",coin_val" /* value of this denom */
327 ",coin_frac" /* fractional value of this denom */
328 ",fee_withdraw_val"
329 ",fee_withdraw_frac"
330 ",fee_deposit_val"
331 ",fee_deposit_frac"
332 ",fee_refresh_val"
333 ",fee_refresh_frac"
334 ",fee_refund_val"
335 ",fee_refund_frac"
336 ",denom_pub"
337 " FROM denominations"
338 " LEFT JOIN "
339 " denomination_revocations USING (denominations_serial);",
340 0),
341 /* Used in #postgres_iterate_active_signkeys() */
342 GNUNET_PQ_make_prepare ("select_signkeys",
343 "SELECT"
344 " master_sig"
345 ",exchange_pub"
346 ",valid_from"
347 ",expire_sign"
348 ",expire_legal"
349 " FROM exchange_sign_keys esk"
350 " WHERE"
351 " expire_sign > $1"
352 " AND NOT EXISTS "
353 " (SELECT esk_serial "
354 " FROM signkey_revocations skr"
355 " WHERE esk.esk_serial = skr.esk_serial);",
356 1),
357 /* Used in #postgres_iterate_auditor_denominations() */
358 GNUNET_PQ_make_prepare ("select_auditor_denoms",
359 "SELECT"
360 " auditors.auditor_pub"
361 ",denominations.denom_pub_hash"
362 ",auditor_denom_sigs.auditor_sig"
363 " FROM auditor_denom_sigs"
364 " JOIN auditors USING (auditor_uuid)"
365 " JOIN denominations USING (denominations_serial)"
366 " WHERE auditors.is_active;",
367 0),
368 /* Used in #postgres_iterate_active_auditors() */
369 GNUNET_PQ_make_prepare ("select_auditors",
370 "SELECT"
371 " auditor_pub"
372 ",auditor_url"
373 ",auditor_name"
374 " FROM auditors"
375 " WHERE"
376 " is_active;",
377 0),
378 /* Used in #postgres_get_denomination_info() */
379 GNUNET_PQ_make_prepare ("denomination_get",
380 "SELECT"
381 " master_sig"
382 ",valid_from"
383 ",expire_withdraw"
384 ",expire_deposit"
385 ",expire_legal"
386 ",coin_val" /* value of this denom */
387 ",coin_frac" /* fractional value of this denom */
388 ",fee_withdraw_val"
389 ",fee_withdraw_frac"
390 ",fee_deposit_val"
391 ",fee_deposit_frac"
392 ",fee_refresh_val"
393 ",fee_refresh_frac"
394 ",fee_refund_val"
395 ",fee_refund_frac"
396 " FROM denominations"
397 " WHERE denom_pub_hash=$1;",
398 1),
399 /* Used in #postgres_insert_denomination_revocation() */
400 GNUNET_PQ_make_prepare ("denomination_revocation_insert",
401 "INSERT INTO denomination_revocations "
402 "(denominations_serial"
403 ",master_sig"
404 ") SELECT denominations_serial,$2"
405 " FROM denominations"
406 " WHERE denom_pub_hash=$1;",
407 2),
408 /* Used in #postgres_get_denomination_revocation() */
409 GNUNET_PQ_make_prepare ("denomination_revocation_get",
410 "SELECT"
411 " master_sig"
412 ",denom_revocations_serial_id"
413 " FROM denomination_revocations"
414 " WHERE denominations_serial="
415 " (SELECT denominations_serial"
416 " FROM denominations"
417 " WHERE denom_pub_hash=$1);",
418 1),
419 /* Used in #postgres_reserves_get() */
420 GNUNET_PQ_make_prepare ("reserves_get",
421 "SELECT"
422 " current_balance_val"
423 ",current_balance_frac"
424 ",expiration_date"
425 ",gc_date"
426 " FROM reserves"
427 " WHERE reserve_pub=$1"
428 " LIMIT 1;",
429 1),
430 GNUNET_PQ_make_prepare ("reserve_create",
431 "INSERT INTO reserves "
432 "(reserve_pub"
433 ",account_details"
434 ",current_balance_val"
435 ",current_balance_frac"
436 ",expiration_date"
437 ",gc_date"
438 ") VALUES "
439 "($1, $2, $3, $4, $5, $6)"
440 " ON CONFLICT DO NOTHING"
441 " RETURNING reserve_uuid;",
442 6),
443 /* Used in #postgres_insert_reserve_closed() */
444 GNUNET_PQ_make_prepare ("reserves_close_insert",
445 "INSERT INTO reserves_close "
446 "(reserve_uuid"
447 ",execution_date"
448 ",wtid"
449 ",receiver_account"
450 ",amount_val"
451 ",amount_frac"
452 ",closing_fee_val"
453 ",closing_fee_frac"
454 ") SELECT reserve_uuid, $2, $3, $4, $5, $6, $7, $8"
455 " FROM reserves"
456 " WHERE reserve_pub=$1;",
457 8),
458 /* Used in #reserves_update() when the reserve is updated */
459 GNUNET_PQ_make_prepare ("reserve_update",
460 "UPDATE reserves"
461 " SET"
462 " expiration_date=$1"
463 ",gc_date=$2"
464 ",current_balance_val=$3"
465 ",current_balance_frac=$4"
466 " WHERE reserve_pub=$5;",
467 5),
468 /* Used in #postgres_reserves_in_insert() to store transaction details */
469 GNUNET_PQ_make_prepare ("reserves_in_add_transaction",
470 "INSERT INTO reserves_in "
471 "(reserve_uuid"
472 ",wire_reference"
473 ",credit_val"
474 ",credit_frac"
475 ",exchange_account_section"
476 ",sender_account_details"
477 ",execution_date"
478 ") SELECT reserve_uuid, $2, $3, $4, $5, $6, $7"
479 " FROM reserves"
480 " WHERE reserve_pub=$1"
481 " ON CONFLICT DO NOTHING;",
482 7),
483 /* Used in #postgres_reserves_in_insert() to store transaction details */
484 GNUNET_PQ_make_prepare ("reserves_in_add_by_uuid",
485 "INSERT INTO reserves_in "
486 "(reserve_uuid"
487 ",wire_reference"
488 ",credit_val"
489 ",credit_frac"
490 ",exchange_account_section"
491 ",sender_account_details"
492 ",execution_date"
493 ") VALUES ($1, $2, $3, $4, $5, $6, $7)"
494 " ON CONFLICT DO NOTHING;",
495 7),
496 /* Used in postgres_select_reserves_in_above_serial_id() to obtain inbound
497 transactions for reserves with serial id '\geq' the given parameter */
498 GNUNET_PQ_make_prepare ("reserves_in_get_latest_wire_reference",
499 "SELECT"
500 " wire_reference"
501 " FROM reserves_in"
502 " WHERE exchange_account_section=$1"
503 " ORDER BY reserve_in_serial_id DESC"
504 " LIMIT 1;",
505 1),
506 /* Used in postgres_select_reserves_in_above_serial_id() to obtain inbound
507 transactions for reserves with serial id '\geq' the given parameter */
508 GNUNET_PQ_make_prepare ("audit_reserves_in_get_transactions_incr",
509 "SELECT"
510 " reserves.reserve_pub"
511 ",wire_reference"
512 ",credit_val"
513 ",credit_frac"
514 ",execution_date"
515 ",sender_account_details"
516 ",reserve_in_serial_id"
517 " FROM reserves_in"
518 " JOIN reserves"
519 " USING (reserve_uuid)"
520 " WHERE reserve_in_serial_id>=$1"
521 " ORDER BY reserve_in_serial_id;",
522 1),
523 /* Used in postgres_select_reserves_in_above_serial_id() to obtain inbound
524 transactions for reserves with serial id '\geq' the given parameter */
525 GNUNET_PQ_make_prepare (
526 "audit_reserves_in_get_transactions_incr_by_account",
527 "SELECT"
528 " reserves.reserve_pub"
529 ",wire_reference"
530 ",credit_val"
531 ",credit_frac"
532 ",execution_date"
533 ",sender_account_details"
534 ",reserve_in_serial_id"
535 " FROM reserves_in"
536 " JOIN reserves "
537 " USING (reserve_uuid)"
538 " WHERE reserve_in_serial_id>=$1 AND exchange_account_section=$2"
539 " ORDER BY reserve_in_serial_id;",
540 2),
541 /* Used in #postgres_get_reserve_history() to obtain inbound transactions
542 for a reserve */
543 GNUNET_PQ_make_prepare ("reserves_in_get_transactions",
544 "SELECT"
545 " wire_reference"
546 ",credit_val"
547 ",credit_frac"
548 ",execution_date"
549 ",sender_account_details"
550 " FROM reserves_in"
551 " WHERE reserve_uuid="
552 " (SELECT reserve_uuid "
553 " FROM reserves"
554 " WHERE reserve_pub=$1);",
555 1),
556 /* Lock withdraw table; NOTE: we may want to eventually shard the
557 deposit table to avoid this lock being the main point of
558 contention limiting transaction performance. */
559 GNUNET_PQ_make_prepare ("lock_withdraw",
560 "LOCK TABLE reserves_out;",
561 0),
562 /* Used in #postgres_insert_withdraw_info() to store
563 the signature of a blinded coin with the blinded coin's
564 details before returning it during /reserve/withdraw. We store
565 the coin's denomination information (public key, signature)
566 and the blinded message as well as the reserve that the coin
567 is being withdrawn from and the signature of the message
568 authorizing the withdrawal. */
569 GNUNET_PQ_make_prepare ("insert_withdraw_info",
570 "WITH ds AS"
571 " (SELECT denominations_serial"
572 " FROM denominations"
573 " WHERE denom_pub_hash=$2)"
574 "INSERT INTO reserves_out "
575 "(h_blind_ev"
576 ",denominations_serial"
577 ",denom_sig"
578 ",reserve_uuid"
579 ",reserve_sig"
580 ",execution_date"
581 ",amount_with_fee_val"
582 ",amount_with_fee_frac"
583 ") SELECT $1, ds.denominations_serial, $3, reserve_uuid, $5, $6, $7, $8"
584 " FROM reserves"
585 " CROSS JOIN ds"
586 " WHERE reserve_pub=$4;",
587 8),
588 /* Used in #postgres_get_withdraw_info() to
589 locate the response for a /reserve/withdraw request
590 using the hash of the blinded message. Used to
591 make sure /reserve/withdraw requests are idempotent. */
592 GNUNET_PQ_make_prepare ("get_withdraw_info",
593 "SELECT"
594 " denom.denom_pub_hash"
595 ",denom_sig"
596 ",reserve_sig"
597 ",reserves.reserve_pub"
598 ",execution_date"
599 ",amount_with_fee_val"
600 ",amount_with_fee_frac"
601 ",denom.fee_withdraw_val"
602 ",denom.fee_withdraw_frac"
603 " FROM reserves_out"
604 " JOIN reserves"
605 " USING (reserve_uuid)"
606 " JOIN denominations denom"
607 " USING (denominations_serial)"
608 " WHERE h_blind_ev=$1;",
609 1),
610 /* Used during #postgres_get_reserve_history() to
611 obtain all of the /reserve/withdraw operations that
612 have been performed on a given reserve. (i.e. to
613 demonstrate double-spending) */
614 GNUNET_PQ_make_prepare ("get_reserves_out",
615 "SELECT"
616 " h_blind_ev"
617 ",denom.denom_pub_hash"
618 ",denom_sig"
619 ",reserve_sig"
620 ",execution_date"
621 ",amount_with_fee_val"
622 ",amount_with_fee_frac"
623 ",denom.fee_withdraw_val"
624 ",denom.fee_withdraw_frac"
625 " FROM reserves_out"
626 " JOIN denominations denom"
627 " USING (denominations_serial)"
628 " WHERE reserve_uuid="
629 " (SELECT reserve_uuid"
630 " FROM reserves"
631 " WHERE reserve_pub=$1);",
632 1),
633 /* Used in #postgres_select_withdrawals_above_serial_id() */
634 GNUNET_PQ_make_prepare ("audit_get_reserves_out_incr",
635 "SELECT"
636 " h_blind_ev"
637 ",denom.denom_pub"
638 ",reserve_sig"
639 ",reserves.reserve_pub"
640 ",execution_date"
641 ",amount_with_fee_val"
642 ",amount_with_fee_frac"
643 ",reserve_out_serial_id"
644 " FROM reserves_out"
645 " JOIN reserves"
646 " USING (reserve_uuid)"
647 " JOIN denominations denom"
648 " USING (denominations_serial)"
649 " WHERE reserve_out_serial_id>=$1"
650 " ORDER BY reserve_out_serial_id ASC;",
651 1),
652
653 /* Used in #postgres_count_known_coins() */
654 GNUNET_PQ_make_prepare ("count_known_coins",
655 "SELECT"
656 " COUNT(*) AS count"
657 " FROM known_coins"
658 " WHERE denominations_serial="
659 " (SELECT denominations_serial"
660 " FROM denominations"
661 " WHERE denom_pub_hash=$1);",
662 1),
663 /* Used in #postgres_get_known_coin() to fetch
664 the denomination public key and signature for
665 a coin known to the exchange. */
666 GNUNET_PQ_make_prepare ("get_known_coin",
667 "SELECT"
668 " denominations.denom_pub_hash"
669 ",denom_sig"
670 " FROM known_coins"
671 " JOIN denominations USING (denominations_serial)"
672 " WHERE coin_pub=$1;",
673 1),
674 /* Used in #postgres_ensure_coin_known() */
675 GNUNET_PQ_make_prepare ("get_known_coin_dh",
676 "SELECT"
677 " denominations.denom_pub_hash"
678 " FROM known_coins"
679 " JOIN denominations USING (denominations_serial)"
680 " WHERE coin_pub=$1;",
681 1),
682 /* Used in #postgres_get_coin_denomination() to fetch
683 the denomination public key hash for
684 a coin known to the exchange. */
685 GNUNET_PQ_make_prepare ("get_coin_denomination",
686 "SELECT"
687 " denominations.denom_pub_hash"
688 " FROM known_coins"
689 " JOIN denominations USING (denominations_serial)"
690 " WHERE coin_pub=$1"
691 " FOR SHARE;",
692 1),
693 /* Lock deposit table; NOTE: we may want to eventually shard the
694 deposit table to avoid this lock being the main point of
695 contention limiting transaction performance. */
696 GNUNET_PQ_make_prepare ("lock_known_coins",
697 "LOCK TABLE known_coins;",
698 0),
699 /* Used in #postgres_insert_known_coin() to store
700 the denomination public key and signature for
701 a coin known to the exchange. */
702 GNUNET_PQ_make_prepare ("insert_known_coin",
703 "INSERT INTO known_coins "
704 "(coin_pub"
705 ",denominations_serial"
706 ",denom_sig"
707 ") SELECT $1, denominations_serial, $3 "
708 " FROM denominations"
709 " WHERE denom_pub_hash=$2;",
710 3),
711
712 /* Used in #postgres_insert_melt() to store
713 high-level information about a melt operation */
714 GNUNET_PQ_make_prepare ("insert_melt",
715 "INSERT INTO refresh_commitments "
716 "(rc "
717 ",old_known_coin_id "
718 ",old_coin_sig "
719 ",amount_with_fee_val "
720 ",amount_with_fee_frac "
721 ",noreveal_index "
722 ") SELECT $1, known_coin_id, $3, $4, $5, $6"
723 " FROM known_coins"
724 " WHERE coin_pub=$2",
725 6),
726 /* Used in #postgres_get_melt() to fetch
727 high-level information about a melt operation */
728 GNUNET_PQ_make_prepare ("get_melt",
729 "SELECT"
730 " denoms.denom_pub_hash"
731 ",denoms.fee_refresh_val"
732 ",denoms.fee_refresh_frac"
733 ",kc.coin_pub AS old_coin_pub"
734 ",old_coin_sig"
735 ",amount_with_fee_val"
736 ",amount_with_fee_frac"
737 ",noreveal_index"
738 " FROM refresh_commitments"
739 " JOIN known_coins kc"
740 " ON (refresh_commitments.old_known_coin_id = kc.known_coin_id)"
741 " JOIN denominations denoms"
742 " ON (kc.denominations_serial = denoms.denominations_serial)"
743 " WHERE rc=$1;",
744 1),
745 /* Used in #postgres_get_melt_index() to fetch
746 the noreveal index from a previous melt operation */
747 GNUNET_PQ_make_prepare ("get_melt_index",
748 "SELECT"
749 " noreveal_index"
750 " FROM refresh_commitments"
751 " WHERE rc=$1;",
752 1),
753 /* Used in #postgres_select_refreshes_above_serial_id() to fetch
754 refresh session with id '\geq' the given parameter */
755 GNUNET_PQ_make_prepare ("audit_get_refresh_commitments_incr",
756 "SELECT"
757 " denom.denom_pub"
758 ",kc.coin_pub AS old_coin_pub"
759 ",old_coin_sig"
760 ",amount_with_fee_val"
761 ",amount_with_fee_frac"
762 ",noreveal_index"
763 ",melt_serial_id"
764 ",rc"
765 " FROM refresh_commitments"
766 " JOIN known_coins kc"
767 " ON (refresh_commitments.old_known_coin_id = kc.known_coin_id)"
768 " JOIN denominations denom"
769 " ON (kc.denominations_serial = denom.denominations_serial)"
770 " WHERE melt_serial_id>=$1"
771 " ORDER BY melt_serial_id ASC;",
772 1),
773 /* Query the 'refresh_commitments' by coin public key */
774 GNUNET_PQ_make_prepare ("get_refresh_session_by_coin",
775 "SELECT"
776 " rc"
777 ",old_coin_sig"
778 ",amount_with_fee_val"
779 ",amount_with_fee_frac"
780 ",denoms.denom_pub_hash"
781 ",denoms.fee_refresh_val"
782 ",denoms.fee_refresh_frac"
783 ",melt_serial_id"
784 " FROM refresh_commitments"
785 " JOIN known_coins kc"
786 " ON (refresh_commitments.old_known_coin_id = kc.known_coin_id)"
787 " JOIN denominations denoms"
788 " USING (denominations_serial)"
789 " WHERE old_known_coin_id="
790 "(SELECT known_coin_id"
791 " FROM known_coins"
792 " WHERE coin_pub=$1);",
793 1),
794 /* Store information about the desired denominations for a
795 refresh operation, used in #postgres_insert_refresh_reveal() */
796 GNUNET_PQ_make_prepare ("insert_refresh_revealed_coin",
797 "WITH rcx AS"
798 " (SELECT melt_serial_id"
799 " FROM refresh_commitments"
800 " WHERE rc=$1)"
801 "INSERT INTO refresh_revealed_coins "
802 "(melt_serial_id "
803 ",freshcoin_index "
804 ",link_sig "
805 ",denominations_serial "
806 ",coin_ev"
807 ",h_coin_ev"
808 ",ev_sig"
809 ") SELECT rcx.melt_serial_id, $2, $3, "
810 " denominations_serial, $5, $6, $7"
811 " FROM denominations"
812 " CROSS JOIN rcx"
813 " WHERE denom_pub_hash=$4;",
814 7),
815 /* Obtain information about the coins created in a refresh
816 operation, used in #postgres_get_refresh_reveal() */
817 GNUNET_PQ_make_prepare ("get_refresh_revealed_coins",
818 "SELECT "
819 " freshcoin_index"
820 ",denom.denom_pub"
821 ",link_sig"
822 ",coin_ev"
823 ",ev_sig"
824 " FROM refresh_revealed_coins"
825 " JOIN denominations denom "
826 " USING (denominations_serial)"
827 " JOIN refresh_commitments"
828 " USING (melt_serial_id)"
829 " WHERE rc=$1"
830 " ORDER BY freshcoin_index ASC;",
831 1),
832
833 /* Used in #postgres_insert_refresh_reveal() to store the transfer
834 keys we learned */
835 GNUNET_PQ_make_prepare ("insert_refresh_transfer_keys",
836 "INSERT INTO refresh_transfer_keys "
837 "(melt_serial_id"
838 ",transfer_pub"
839 ",transfer_privs"
840 ") SELECT melt_serial_id, $2, $3"
841 " FROM refresh_commitments"
842 " WHERE rc=$1",
843 3),
844 /* Used in #postgres_get_refresh_reveal() to retrieve transfer
845 keys from /refresh/reveal */
846 GNUNET_PQ_make_prepare ("get_refresh_transfer_keys",
847 "SELECT"
848 " transfer_pub"
849 ",transfer_privs"
850 " FROM refresh_transfer_keys"
851 " JOIN refresh_commitments"
852 " USING (melt_serial_id)"
853 " WHERE rc=$1;",
854 1),
855 /* Used in #postgres_insert_refund() to store refund information */
856 GNUNET_PQ_make_prepare ("insert_refund",
857 "INSERT INTO refunds "
858 "(deposit_serial_id "
859 ",merchant_sig "
860 ",rtransaction_id "
861 ",amount_with_fee_val "
862 ",amount_with_fee_frac "
863 ") SELECT deposit_serial_id, $3, $5, $6, $7"
864 " FROM deposits"
865 " JOIN known_coins USING (known_coin_id)"
866 " WHERE coin_pub=$1"
867 " AND h_contract_terms=$4"
868 " AND merchant_pub=$2",
869 7),
870 /* Query the 'refunds' by coin public key */
871 GNUNET_PQ_make_prepare ("get_refunds_by_coin",
872 "SELECT"
873 " merchant_pub"
874 ",merchant_sig"
875 ",h_contract_terms"
876 ",rtransaction_id"
877 ",refunds.amount_with_fee_val"
878 ",refunds.amount_with_fee_frac"
879 ",denom.fee_refund_val "
880 ",denom.fee_refund_frac "
881 ",refund_serial_id"
882 " FROM refunds"
883 " JOIN deposits USING (deposit_serial_id)"
884 " JOIN known_coins USING (known_coin_id)"
885 " JOIN denominations denom USING (denominations_serial)"
886 " WHERE coin_pub=$1;",
887 1),
888 /* Query the 'refunds' by coin public key, merchant_pub and contract hash */
889 GNUNET_PQ_make_prepare ("get_refunds_by_coin_and_contract",
890 "SELECT"
891 " refunds.amount_with_fee_val"
892 ",refunds.amount_with_fee_frac"
893 " FROM refunds"
894 " JOIN deposits USING (deposit_serial_id)"
895 " JOIN known_coins USING (known_coin_id)"
896 " WHERE coin_pub=$1"
897 " AND merchant_pub=$2"
898 " AND h_contract_terms=$3;",
899 3),
900 /* Fetch refunds with rowid '\geq' the given parameter */
901 GNUNET_PQ_make_prepare ("audit_get_refunds_incr",
902 "SELECT"
903 " merchant_pub"
904 ",merchant_sig"
905 ",h_contract_terms"
906 ",rtransaction_id"
907 ",denom.denom_pub"
908 ",kc.coin_pub"
909 ",refunds.amount_with_fee_val"
910 ",refunds.amount_with_fee_frac"
911 ",refund_serial_id"
912 " FROM refunds"
913 " JOIN deposits USING (deposit_serial_id)"
914 " JOIN known_coins kc USING (known_coin_id)"
915 " JOIN denominations denom ON (kc.denominations_serial = denom.denominations_serial)"
916 " WHERE refund_serial_id>=$1"
917 " ORDER BY refund_serial_id ASC;",
918 1),
919 /* Lock deposit table; NOTE: we may want to eventually shard the
920 deposit table to avoid this lock being the main point of
921 contention limiting transaction performance. */
922 GNUNET_PQ_make_prepare ("lock_deposit",
923 "LOCK TABLE deposits;",
924 0),
925 /* Store information about a /deposit the exchange is to execute.
926 Used in #postgres_insert_deposit(). */
927 GNUNET_PQ_make_prepare ("insert_deposit",
928 "INSERT INTO deposits "
929 "(known_coin_id"
930 ",amount_with_fee_val"
931 ",amount_with_fee_frac"
932 ",wallet_timestamp"
933 ",refund_deadline"
934 ",wire_deadline"
935 ",merchant_pub"
936 ",h_contract_terms"
937 ",h_wire"
938 ",coin_sig"
939 ",wire"
940 ",exchange_timestamp"
941 ") SELECT known_coin_id, $2, $3, $4, $5, $6, "
942 " $7, $8, $9, $10, $11, $12"
943 " FROM known_coins"
944 " WHERE coin_pub=$1;",
945 12),
946 /* Fetch an existing deposit request, used to ensure idempotency
947 during /deposit processing. Used in #postgres_have_deposit(). */
948 GNUNET_PQ_make_prepare ("get_deposit",
949 "SELECT"
950 " amount_with_fee_val"
951 ",amount_with_fee_frac"
952 ",denominations.fee_deposit_val"
953 ",denominations.fee_deposit_frac"
954 ",wallet_timestamp"
955 ",exchange_timestamp"
956 ",refund_deadline"
957 ",wire_deadline"
958 ",h_contract_terms"
959 ",h_wire"
960 " FROM deposits"
961 " JOIN known_coins USING (known_coin_id)"
962 " JOIN denominations USING (denominations_serial)"
963 " WHERE ((coin_pub=$1)"
964 " AND (merchant_pub=$3)"
965 " AND (h_contract_terms=$2));",
966 3),
967 /* Fetch deposits with rowid '\geq' the given parameter */
968 GNUNET_PQ_make_prepare ("audit_get_deposits_incr",
969 "SELECT"
970 " amount_with_fee_val"
971 ",amount_with_fee_frac"
972 ",wallet_timestamp"
973 ",exchange_timestamp"
974 ",merchant_pub"
975 ",denom.denom_pub"
976 ",kc.coin_pub"
977 ",coin_sig"
978 ",refund_deadline"
979 ",wire_deadline"
980 ",h_contract_terms"
981 ",wire"
982 ",done"
983 ",deposit_serial_id"
984 " FROM deposits"
985 " JOIN known_coins kc USING (known_coin_id)"
986 " JOIN denominations denom USING (denominations_serial)"
987 " WHERE ("
988 " (deposit_serial_id>=$1)"
989 " )"
990 " ORDER BY deposit_serial_id ASC;",
991 1),
992 /* Fetch an existing deposit request.
993 Used in #postgres_lookup_transfer_by_deposit(). */
994 GNUNET_PQ_make_prepare ("get_deposit_for_wtid",
995 "SELECT"
996 " amount_with_fee_val"
997 ",amount_with_fee_frac"
998 ",denom.fee_deposit_val"
999 ",denom.fee_deposit_frac"
1000 ",wire_deadline"
1001 " FROM deposits"
1002 " JOIN known_coins USING (known_coin_id)"
1003 " JOIN denominations denom USING (denominations_serial)"
1004 " WHERE ((coin_pub=$1)"
1005 " AND (merchant_pub=$2)"
1006 " AND (h_contract_terms=$3)"
1007 " AND (h_wire=$4)"
1008 " );",
1009 4),
1010 /* Used in #postgres_get_ready_deposit() */
1011 GNUNET_PQ_make_prepare ("deposits_get_ready",
1012 "SELECT"
1013 " deposit_serial_id"
1014 ",amount_with_fee_val"
1015 ",amount_with_fee_frac"
1016 ",denom.fee_deposit_val"
1017 ",denom.fee_deposit_frac"
1018 ",wire_deadline"
1019 ",h_contract_terms"
1020 ",wire"
1021 ",merchant_pub"
1022 ",kc.coin_pub"
1023 ",exchange_timestamp"
1024 ",wallet_timestamp"
1025 " FROM deposits"
1026 " JOIN known_coins kc USING (known_coin_id)"
1027 " JOIN denominations denom USING (denominations_serial)"
1028 " WHERE tiny=FALSE"
1029 " AND done=FALSE"
1030 " AND wire_deadline<=$1"
1031 " AND refund_deadline<$1"
1032 " ORDER BY wire_deadline ASC"
1033 " LIMIT 1;",
1034 1),
1035 /* Used in #postgres_iterate_matching_deposits() */
1036 GNUNET_PQ_make_prepare ("deposits_iterate_matching",
1037 "SELECT"
1038 " deposit_serial_id"
1039 ",amount_with_fee_val"
1040 ",amount_with_fee_frac"
1041 ",denom.fee_deposit_val"
1042 ",denom.fee_deposit_frac"
1043 ",h_contract_terms"
1044 ",kc.coin_pub"
1045 " FROM deposits"
1046 " JOIN known_coins kc USING (known_coin_id)"
1047 " JOIN denominations denom USING (denominations_serial)"
1048 " WHERE"
1049 " merchant_pub=$1 AND"
1050 " h_wire=$2 AND"
1051 " done=FALSE"
1052 " ORDER BY wire_deadline ASC"
1053 " LIMIT "
1054 TALER_QUOTE (
1055 TALER_EXCHANGEDB_MATCHING_DEPOSITS_LIMIT) ";",
1056 2),
1057 /* Used in #postgres_mark_deposit_tiny() */
1058 GNUNET_PQ_make_prepare ("mark_deposit_tiny",
1059 "UPDATE deposits"
1060 " SET tiny=TRUE"
1061 " WHERE deposit_serial_id=$1",
1062 1),
1063 /* Used in #postgres_mark_deposit_done() */
1064 GNUNET_PQ_make_prepare ("mark_deposit_done",
1065 "UPDATE deposits"
1066 " SET done=TRUE"
1067 " WHERE deposit_serial_id=$1;",
1068 1),
1069 /* Used in #postgres_test_deposit_done() */
1070 GNUNET_PQ_make_prepare ("test_deposit_done",
1071 "SELECT done"
1072 " FROM deposits"
1073 " JOIN known_coins USING (known_coin_id)"
1074 " WHERE coin_pub=$1"
1075 " AND merchant_pub=$2"
1076 " AND h_contract_terms=$3"
1077 " AND h_wire=$4;",
1078 5),
1079 /* Used in #postgres_get_coin_transactions() to obtain information
1080 about how a coin has been spend with /deposit requests. */
1081 GNUNET_PQ_make_prepare ("get_deposit_with_coin_pub",
1082 "SELECT"
1083 " amount_with_fee_val"
1084 ",amount_with_fee_frac"
1085 ",denoms.fee_deposit_val"
1086 ",denoms.fee_deposit_frac"
1087 ",denoms.denom_pub_hash"
1088 ",wallet_timestamp"
1089 ",refund_deadline"
1090 ",wire_deadline"
1091 ",merchant_pub"
1092 ",h_contract_terms"
1093 ",h_wire"
1094 ",wire"
1095 ",coin_sig"
1096 ",deposit_serial_id"
1097 ",done"
1098 " FROM deposits"
1099 " JOIN known_coins kc"
1100 " USING (known_coin_id)"
1101 " JOIN denominations denoms"
1102 " USING (denominations_serial)"
1103 " WHERE coin_pub=$1;",
1104 1),
1105
1106 /* Used in #postgres_get_link_data(). */
1107 GNUNET_PQ_make_prepare ("get_link",
1108 "SELECT "
1109 " tp.transfer_pub"
1110 ",denoms.denom_pub"
1111 ",rrc.ev_sig"
1112 ",rrc.link_sig"
1113 " FROM refresh_commitments"
1114 " JOIN refresh_revealed_coins rrc"
1115 " USING (melt_serial_id)"
1116 " JOIN refresh_transfer_keys tp"
1117 " USING (melt_serial_id)"
1118 " JOIN denominations denoms"
1119 " ON (rrc.denominations_serial = denoms.denominations_serial)"
1120 " WHERE old_known_coin_id="
1121 " (SELECT known_coin_id "
1122 " FROM known_coins"
1123 " WHERE coin_pub=$1)"
1124 " ORDER BY tp.transfer_pub, rrc.freshcoin_index ASC",
1125 1),
1126 /* Used in #postgres_lookup_wire_transfer */
1127 GNUNET_PQ_make_prepare ("lookup_transactions",
1128 "SELECT"
1129 " aggregation_serial_id"
1130 ",deposits.h_contract_terms"
1131 ",deposits.wire"
1132 ",deposits.h_wire"
1133 ",kc.coin_pub"
1134 ",deposits.merchant_pub"
1135 ",wire_out.execution_date"
1136 ",deposits.amount_with_fee_val"
1137 ",deposits.amount_with_fee_frac"
1138 ",denom.fee_deposit_val"
1139 ",denom.fee_deposit_frac"
1140 ",denom.denom_pub"
1141 " FROM aggregation_tracking"
1142 " JOIN deposits"
1143 " USING (deposit_serial_id)"
1144 " JOIN known_coins kc"
1145 " USING (known_coin_id)"
1146 " JOIN denominations denom"
1147 " USING (denominations_serial)"
1148 " JOIN wire_out"
1149 " USING (wtid_raw)"
1150 " WHERE wtid_raw=$1;",
1151 1),
1152 /* Used in #postgres_lookup_transfer_by_deposit */
1153 GNUNET_PQ_make_prepare ("lookup_deposit_wtid",
1154 "SELECT"
1155 " aggregation_tracking.wtid_raw"
1156 ",wire_out.execution_date"
1157 ",amount_with_fee_val"
1158 ",amount_with_fee_frac"
1159 ",denom.fee_deposit_val"
1160 ",denom.fee_deposit_frac"
1161 " FROM deposits"
1162 " JOIN aggregation_tracking"
1163 " USING (deposit_serial_id)"
1164 " JOIN known_coins"
1165 " USING (known_coin_id)"
1166 " JOIN denominations denom"
1167 " USING (denominations_serial)"
1168 " JOIN wire_out"
1169 " USING (wtid_raw)"
1170 " WHERE coin_pub=$1"
1171 " AND h_contract_terms=$2"
1172 " AND h_wire=$3"
1173 " AND merchant_pub=$4;",
1174 4),
1175 /* Used in #postgres_insert_aggregation_tracking */
1176 GNUNET_PQ_make_prepare ("insert_aggregation_tracking",
1177 "INSERT INTO aggregation_tracking "
1178 "(deposit_serial_id"
1179 ",wtid_raw"
1180 ") VALUES "
1181 "($1, $2);",
1182 2),
1183 /* Used in #postgres_get_wire_fee() */
1184 GNUNET_PQ_make_prepare ("get_wire_fee",
1185 "SELECT "
1186 " start_date"
1187 ",end_date"
1188 ",wire_fee_val"
1189 ",wire_fee_frac"
1190 ",closing_fee_val"
1191 ",closing_fee_frac"
1192 ",master_sig"
1193 " FROM wire_fee"
1194 " WHERE wire_method=$1"
1195 " AND start_date <= $2"
1196 " AND end_date > $2;",
1197 2),
1198 /* Used in #postgres_insert_wire_fee */
1199 GNUNET_PQ_make_prepare ("insert_wire_fee",
1200 "INSERT INTO wire_fee "
1201 "(wire_method"
1202 ",start_date"
1203 ",end_date"
1204 ",wire_fee_val"
1205 ",wire_fee_frac"
1206 ",closing_fee_val"
1207 ",closing_fee_frac"
1208 ",master_sig"
1209 ") VALUES "
1210 "($1, $2, $3, $4, $5, $6, $7, $8);",
1211 8),
1212 /* Used in #postgres_store_wire_transfer_out */
1213 GNUNET_PQ_make_prepare ("insert_wire_out",
1214 "INSERT INTO wire_out "
1215 "(execution_date"
1216 ",wtid_raw"
1217 ",wire_target"
1218 ",exchange_account_section"
1219 ",amount_val"
1220 ",amount_frac"
1221 ") VALUES "
1222 "($1, $2, $3, $4, $5, $6);",
1223 6),
1224 /* Used in #postgres_wire_prepare_data_insert() to store
1225 wire transfer information before actually committing it with the bank */
1226 GNUNET_PQ_make_prepare ("wire_prepare_data_insert",
1227 "INSERT INTO prewire "
1228 "(type"
1229 ",buf"
1230 ") VALUES "
1231 "($1, $2);",
1232 2),
1233 /* Used in #postgres_wire_prepare_data_mark_finished() */
1234 GNUNET_PQ_make_prepare ("wire_prepare_data_mark_done",
1235 "UPDATE prewire"
1236 " SET finished=TRUE"
1237 " WHERE prewire_uuid=$1;",
1238 1),
1239 /* Used in #postgres_wire_prepare_data_mark_failed() */
1240 GNUNET_PQ_make_prepare ("wire_prepare_data_mark_failed",
1241 "UPDATE prewire"
1242 " SET failed=TRUE"
1243 " WHERE prewire_uuid=$1;",
1244 1),
1245 /* Used in #postgres_wire_prepare_data_get() */
1246 GNUNET_PQ_make_prepare ("wire_prepare_data_get",
1247 "SELECT"
1248 " prewire_uuid"
1249 ",type"
1250 ",buf"
1251 " FROM prewire"
1252 " WHERE finished=FALSE"
1253 " AND failed=FALSE"
1254 " ORDER BY prewire_uuid ASC"
1255 " LIMIT 1;",
1256 0),
1257 /* Used in #postgres_select_deposits_missing_wire */
1258 GNUNET_PQ_make_prepare ("deposits_get_overdue",
1259 "SELECT"
1260 " deposit_serial_id"
1261 ",coin_pub"
1262 ",amount_with_fee_val"
1263 ",amount_with_fee_frac"
1264 ",wire"
1265 ",wire_deadline"
1266 ",tiny"
1267 ",done"
1268 " FROM deposits d"
1269 " JOIN known_coins USING (known_coin_id)"
1270 " WHERE wire_deadline >= $1"
1271 " AND wire_deadline < $2"
1272 " AND NOT (EXISTS (SELECT 1"
1273 " FROM refunds"
1274 " JOIN deposits dx USING (deposit_serial_id)"
1275 " WHERE (dx.known_coin_id = d.known_coin_id))"
1276 " OR EXISTS (SELECT 1"
1277 " FROM aggregation_tracking"
1278 " WHERE (aggregation_tracking.deposit_serial_id = d.deposit_serial_id)))"
1279 " ORDER BY wire_deadline ASC",
1280 2),
1281 /* Used in #postgres_select_wire_out_above_serial_id() */
1282 GNUNET_PQ_make_prepare ("audit_get_wire_incr",
1283 "SELECT"
1284 " wireout_uuid"
1285 ",execution_date"
1286 ",wtid_raw"
1287 ",wire_target"
1288 ",amount_val"
1289 ",amount_frac"
1290 " FROM wire_out"
1291 " WHERE wireout_uuid>=$1"
1292 " ORDER BY wireout_uuid ASC;",
1293 1),
1294 /* Used in #postgres_select_wire_out_above_serial_id_by_account() */
1295 GNUNET_PQ_make_prepare ("audit_get_wire_incr_by_account",
1296 "SELECT"
1297 " wireout_uuid"
1298 ",execution_date"
1299 ",wtid_raw"
1300 ",wire_target"
1301 ",amount_val"
1302 ",amount_frac"
1303 " FROM wire_out"
1304 " WHERE wireout_uuid>=$1 AND exchange_account_section=$2"
1305 " ORDER BY wireout_uuid ASC;",
1306 2),
1307 /* Used in #postgres_insert_recoup_request() to store recoup
1308 information */
1309 GNUNET_PQ_make_prepare ("recoup_insert",
1310 "WITH rx AS"
1311 " (SELECT reserve_out_serial_id"
1312 " FROM reserves_out"
1313 " WHERE h_blind_ev=$7)"
1314 "INSERT INTO recoup "
1315 "(known_coin_id"
1316 ",coin_sig"
1317 ",coin_blind"
1318 ",amount_val"
1319 ",amount_frac"
1320 ",timestamp"
1321 ",reserve_out_serial_id"
1322 ") SELECT known_coin_id, $2, $3, $4, $5, $6, rx.reserve_out_serial_id"
1323 " FROM known_coins"
1324 " CROSS JOIN rx"
1325 " WHERE coin_pub=$1;",
1326 7),
1327 /* Used in #postgres_insert_recoup_refresh_request() to store recoup-refresh
1328 information */
1329 GNUNET_PQ_make_prepare ("recoup_refresh_insert",
1330 "WITH rrx AS"
1331 " (SELECT rrc_serial"
1332 " FROM refresh_revealed_coins"
1333 " WHERE h_coin_ev=$7)"
1334 "INSERT INTO recoup_refresh "
1335 "(known_coin_id"
1336 ",coin_sig"
1337 ",coin_blind"
1338 ",amount_val"
1339 ",amount_frac"
1340 ",timestamp"
1341 ",rrc_serial"
1342 ") SELECT known_coin_id, $2, $3, $4, $5, $6, rrx.rrc_serial"
1343 " FROM known_coins"
1344 " CROSS JOIN rrx"
1345 " WHERE coin_pub=$1;",
1346 7),
1347 /* Used in #postgres_select_recoup_above_serial_id() to obtain recoup transactions */
1348 GNUNET_PQ_make_prepare ("recoup_get_incr",
1349 "SELECT"
1350 " recoup_uuid"
1351 ",timestamp"
1352 ",reserves.reserve_pub"
1353 ",coins.coin_pub"
1354 ",coin_sig"
1355 ",coin_blind"
1356 ",ro.h_blind_ev"
1357 ",denoms.denom_pub_hash"
1358 ",coins.denom_sig"
1359 ",denoms.denom_pub"
1360 ",amount_val"
1361 ",amount_frac"
1362 " FROM recoup"
1363 " JOIN known_coins coins"
1364 " USING (known_coin_id)"
1365 " JOIN reserves_out ro"
1366 " USING (reserve_out_serial_id)"
1367 " JOIN reserves"
1368 " USING (reserve_uuid)"
1369 " JOIN denominations denoms"
1370 " ON (coins.denominations_serial = denoms.denominations_serial)"
1371 " WHERE recoup_uuid>=$1"
1372 " ORDER BY recoup_uuid ASC;",
1373 1),
1374 /* Used in #postgres_select_recoup_refresh_above_serial_id() to obtain
1375 recoup-refresh transactions */
1376 GNUNET_PQ_make_prepare ("recoup_refresh_get_incr",
1377 "SELECT"
1378 " recoup_refresh_uuid"
1379 ",timestamp"
1380 ",old_coins.coin_pub AS old_coin_pub"
1381 ",old_denoms.denom_pub_hash AS old_denom_pub_hash"
1382 ",new_coins.coin_pub As coin_pub"
1383 ",coin_sig"
1384 ",coin_blind"
1385 ",new_denoms.denom_pub AS denom_pub"
1386 ",rrc.h_coin_ev AS h_blind_ev"
1387 ",new_denoms.denom_pub_hash"
1388 ",new_coins.denom_sig AS denom_sig"
1389 ",amount_val"
1390 ",amount_frac"
1391 " FROM recoup_refresh"
1392 " INNER JOIN refresh_revealed_coins rrc"
1393 " USING (rrc_serial)"
1394 " INNER JOIN refresh_commitments rfc"
1395 " ON (rrc.melt_serial_id = rfc.melt_serial_id)"
1396 " INNER JOIN known_coins old_coins"
1397 " ON (rfc.old_known_coin_id = old_coins.known_coin_id)"
1398 " INNER JOIN known_coins new_coins"
1399 " ON (new_coins.known_coin_id = recoup_refresh.known_coin_id)"
1400 " INNER JOIN denominations new_denoms"
1401 " ON (new_coins.denominations_serial = new_denoms.denominations_serial)"
1402 " INNER JOIN denominations old_denoms"
1403 " ON (old_coins.denominations_serial = old_denoms.denominations_serial)"
1404 " WHERE recoup_refresh_uuid>=$1"
1405 " ORDER BY recoup_refresh_uuid ASC;",
1406 1),
1407 /* Used in #postgres_select_reserve_closed_above_serial_id() to
1408 obtain information about closed reserves */
1409 GNUNET_PQ_make_prepare ("reserves_close_get_incr",
1410 "SELECT"
1411 " close_uuid"
1412 ",reserves.reserve_pub"
1413 ",execution_date"
1414 ",wtid"
1415 ",receiver_account"
1416 ",amount_val"
1417 ",amount_frac"
1418 ",closing_fee_val"
1419 ",closing_fee_frac"
1420 " FROM reserves_close"
1421 " JOIN reserves"
1422 " USING (reserve_uuid)"
1423 " WHERE close_uuid>=$1"
1424 " ORDER BY close_uuid ASC;",
1425 1),
1426 /* Used in #postgres_get_reserve_history() to obtain recoup transactions
1427 for a reserve */
1428 GNUNET_PQ_make_prepare ("recoup_by_reserve",
1429 "SELECT"
1430 " coins.coin_pub"
1431 ",coin_sig"
1432 ",coin_blind"
1433 ",amount_val"
1434 ",amount_frac"
1435 ",timestamp"
1436 ",denoms.denom_pub_hash"
1437 ",coins.denom_sig"
1438 " FROM recoup"
1439 " JOIN known_coins coins"
1440 " USING (known_coin_id)"
1441 " JOIN denominations denoms"
1442 " USING (denominations_serial)"
1443 " JOIN reserves_out ro"
1444 " USING (reserve_out_serial_id)"
1445 " WHERE ro.reserve_uuid="
1446 " (SELECT reserve_uuid"
1447 " FROM reserves"
1448 " WHERE reserve_pub=$1);",
1449 1),
1450 /* Used in #postgres_get_coin_transactions() to obtain recoup transactions
1451 affecting old coins of refreshed coins */
1452 GNUNET_PQ_make_prepare ("recoup_by_old_coin",
1453 "SELECT"
1454 " coins.coin_pub"
1455 ",coin_sig"
1456 ",coin_blind"
1457 ",amount_val"
1458 ",amount_frac"
1459 ",timestamp"
1460 ",denoms.denom_pub_hash"
1461 ",coins.denom_sig"
1462 ",recoup_refresh_uuid"
1463 " FROM recoup_refresh"
1464 " JOIN known_coins coins"
1465 " USING (known_coin_id)"
1466 " JOIN denominations denoms"
1467 " USING (denominations_serial)"
1468 " WHERE rrc_serial IN"
1469 " (SELECT rrc.rrc_serial"
1470 " FROM refresh_commitments"
1471 " JOIN refresh_revealed_coins rrc"
1472 " USING (melt_serial_id)"
1473 " WHERE old_known_coin_id="
1474 " (SELECT known_coin_id"
1475 " FROM known_coins"
1476 " WHERE coin_pub=$1));",
1477 1),
1478 /* Used in #postgres_get_reserve_history() */
1479 GNUNET_PQ_make_prepare ("close_by_reserve",
1480 "SELECT"
1481 " amount_val"
1482 ",amount_frac"
1483 ",closing_fee_val"
1484 ",closing_fee_frac"
1485 ",execution_date"
1486 ",receiver_account"
1487 ",wtid"
1488 " FROM reserves_close"
1489 " WHERE reserve_uuid="
1490 " (SELECT reserve_uuid"
1491 " FROM reserves"
1492 " WHERE reserve_pub=$1);",
1493 1),
1494 /* Used in #postgres_get_expired_reserves() */
1495 GNUNET_PQ_make_prepare ("get_expired_reserves",
1496 "SELECT"
1497 " expiration_date"
1498 ",account_details"
1499 ",reserve_pub"
1500 ",current_balance_val"
1501 ",current_balance_frac"
1502 " FROM reserves"
1503 " WHERE expiration_date<=$1"
1504 " AND (current_balance_val != 0 "
1505 " OR current_balance_frac != 0)"
1506 " ORDER BY expiration_date ASC"
1507 " LIMIT 1;",
1508 1),
1509 /* Used in #postgres_get_coin_transactions() to obtain recoup transactions
1510 for a coin */
1511 GNUNET_PQ_make_prepare ("recoup_by_coin",
1512 "SELECT"
1513 " reserves.reserve_pub"
1514 ",denoms.denom_pub_hash"
1515 ",coin_sig"
1516 ",coin_blind"
1517 ",amount_val"
1518 ",amount_frac"
1519 ",timestamp"
1520 ",recoup_uuid"
1521 " FROM recoup"
1522 " JOIN reserves_out ro"
1523 " USING (reserve_out_serial_id)"
1524 " JOIN reserves"
1525 " USING (reserve_uuid)"
1526 " JOIN known_coins coins"
1527 " USING (known_coin_id)"
1528 " JOIN denominations denoms"
1529 " ON (denoms.denominations_serial = coins.denominations_serial)"
1530 " WHERE coins.coin_pub=$1;",
1531 1),
1532 /* Used in #postgres_get_coin_transactions() to obtain recoup transactions
1533 for a refreshed coin */
1534 GNUNET_PQ_make_prepare ("recoup_by_refreshed_coin",
1535 "SELECT"
1536 " old_coins.coin_pub AS old_coin_pub"
1537 ",coin_sig"
1538 ",coin_blind"
1539 ",amount_val"
1540 ",amount_frac"
1541 ",timestamp"
1542 ",denoms.denom_pub_hash"
1543 ",coins.denom_sig"
1544 ",recoup_refresh_uuid"
1545 " FROM recoup_refresh"
1546 " JOIN refresh_revealed_coins rrc"
1547 " USING (rrc_serial)"
1548 " JOIN refresh_commitments rfc"
1549 " ON (rrc.melt_serial_id = rfc.melt_serial_id)"
1550 " JOIN known_coins old_coins"
1551 " ON (rfc.old_known_coin_id = old_coins.known_coin_id)"
1552 " JOIN known_coins coins"
1553 " ON (recoup_refresh.known_coin_id = coins.known_coin_id)"
1554 " JOIN denominations denoms"
1555 " ON (denoms.denominations_serial = coins.denominations_serial)"
1556 " WHERE coins.coin_pub=$1;",
1557 1),
1558 /* Used in #postgres_get_reserve_by_h_blind() */
1559 GNUNET_PQ_make_prepare ("reserve_by_h_blind",
1560 "SELECT"
1561 " reserves.reserve_pub"
1562 " FROM reserves_out"
1563 " JOIN reserves"
1564 " USING (reserve_uuid)"
1565 " WHERE h_blind_ev=$1"
1566 " LIMIT 1;",
1567 1),
1568 /* Used in #postgres_get_old_coin_by_h_blind() */
1569 GNUNET_PQ_make_prepare ("old_coin_by_h_blind",
1570 "SELECT"
1571 " okc.coin_pub AS old_coin_pub"
1572 " FROM refresh_revealed_coins rrc"
1573 " JOIN refresh_commitments rcom USING (melt_serial_id)"
1574 " JOIN known_coins okc ON (rcom.old_known_coin_id = okc.known_coin_id)"
1575 " WHERE h_coin_ev=$1"
1576 " LIMIT 1;",