aboutsummaryrefslogtreecommitdiff
path: root/src/backenddb/plugin_merchantdb_postgres.c
diff options
context:
space:
mode:
Diffstat (limited to 'src/backenddb/plugin_merchantdb_postgres.c')
-rw-r--r--src/backenddb/plugin_merchantdb_postgres.c1248
1 files changed, 607 insertions, 641 deletions
diff --git a/src/backenddb/plugin_merchantdb_postgres.c b/src/backenddb/plugin_merchantdb_postgres.c
index fc5e2d1b..b2a4df69 100644
--- a/src/backenddb/plugin_merchantdb_postgres.c
+++ b/src/backenddb/plugin_merchantdb_postgres.c
@@ -54,6 +54,8 @@
54#define TALER_PQ_RESULT_SPEC_AMOUNT_NBO(field, \ 54#define TALER_PQ_RESULT_SPEC_AMOUNT_NBO(field, \
55 amountp) TALER_PQ_result_spec_amount_nbo ( \ 55 amountp) TALER_PQ_result_spec_amount_nbo ( \
56 field,pg->currency,amountp) 56 field,pg->currency,amountp)
57
58
57/** 59/**
58 * Wrapper macro to add the currency from the plugin's state 60 * Wrapper macro to add the currency from the plugin's state
59 * when fetching amounts from the database. 61 * when fetching amounts from the database.
@@ -75,7 +77,7 @@ struct PostgresClosure
75 /** 77 /**
76 * Postgres connection handle. 78 * Postgres connection handle.
77 */ 79 */
78 PGconn *conn; 80 struct GNUNET_PQ_Context *conn;
79 81
80 /** 82 /**
81 * Which currency do we deal in? 83 * Which currency do we deal in?
@@ -140,611 +142,6 @@ postgres_drop_tables (void *cls)
140 142
141 143
142/** 144/**
143 * Initialize merchant tables
144 *
145 * @param cls closure our `struct Plugin`
146 * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure
147 */
148static int
149postgres_initialize (void *cls)
150{
151 struct PostgresClosure *pg = cls;
152 struct GNUNET_PQ_ExecuteStatement es[] = {
153 /* Orders created by the frontend, not signed or given a nonce yet.
154 The contract terms will change (nonce will be added) when moved to the
155 contract terms table */
156 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_orders ("
157 "order_id VARCHAR NOT NULL"
158 ",merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)"
159 ",contract_terms BYTEA NOT NULL"
160 ",timestamp INT8 NOT NULL"
161 ",PRIMARY KEY (order_id, merchant_pub)"
162 ");"),
163 /* Offers we made to customers */
164 GNUNET_PQ_make_execute (
165 "CREATE TABLE IF NOT EXISTS merchant_contract_terms ("
166 "order_id VARCHAR NOT NULL"
167 ",merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)"
168 ",contract_terms BYTEA NOT NULL"
169 ",h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)"
170 ",timestamp INT8 NOT NULL"
171 ",row_id BIGSERIAL UNIQUE"
172 ",paid boolean DEFAULT FALSE NOT NULL"
173 ",PRIMARY KEY (order_id, merchant_pub)"
174 ",UNIQUE (h_contract_terms, merchant_pub)"
175 ");"),
176 /* Table with the proofs for each coin we deposited at the exchange */
177 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_deposits ("
178 " h_contract_terms BYTEA NOT NULL"
179 ",merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)"
180 ",coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)"
181 ",exchange_url VARCHAR NOT NULL"
182 ",amount_with_fee_val INT8 NOT NULL"
183 ",amount_with_fee_frac INT4 NOT NULL"
184 ",deposit_fee_val INT8 NOT NULL"
185 ",deposit_fee_frac INT4 NOT NULL"
186 ",refund_fee_val INT8 NOT NULL"
187 ",refund_fee_frac INT4 NOT NULL"
188 ",wire_fee_val INT8 NOT NULL"
189 ",wire_fee_frac INT4 NOT NULL"
190 ",signkey_pub BYTEA NOT NULL CHECK (LENGTH(signkey_pub)=32)"
191 ",exchange_proof BYTEA NOT NULL"
192 ",PRIMARY KEY (h_contract_terms, coin_pub)"
193 ",FOREIGN KEY (h_contract_terms, merchant_pub) REFERENCES merchant_contract_terms (h_contract_terms, merchant_pub)"
194 ");"),
195 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_proofs ("
196 " exchange_url VARCHAR NOT NULL"
197 ",wtid BYTEA CHECK (LENGTH(wtid)=32)"
198 ",execution_time INT8 NOT NULL"
199 ",signkey_pub BYTEA NOT NULL CHECK (LENGTH(signkey_pub)=32)"
200 ",proof BYTEA NOT NULL"
201 ",PRIMARY KEY (wtid, exchange_url)"
202 ");"),
203 /* Note that h_contract_terms + coin_pub may actually be unknown to
204 us, e.g. someone else deposits something for us at the exchange.
205 Hence those cannot be foreign keys into deposits/transactions! */
206 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_transfers ("
207 " h_contract_terms BYTEA NOT NULL"
208 ",coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)"
209 ",wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)"
210 ",PRIMARY KEY (h_contract_terms, coin_pub)"
211 ");"),
212 GNUNET_PQ_make_try_execute (
213 "CREATE INDEX IF NOT EXISTS merchant_transfers_by_coin"
214 " ON merchant_transfers (h_contract_terms, coin_pub)"),
215 GNUNET_PQ_make_try_execute (
216 "CREATE INDEX IF NOT EXISTS merchant_transfers_by_wtid"
217 " ON merchant_transfers (wtid)"),
218 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS exchange_wire_fees ("
219 " exchange_pub BYTEA NOT NULL CHECK (length(exchange_pub)=32)"
220 ",h_wire_method BYTEA NOT NULL CHECK (length(h_wire_method)=64)"
221 ",wire_fee_val INT8 NOT NULL"
222 ",wire_fee_frac INT4 NOT NULL"
223 ",closing_fee_val INT8 NOT NULL"
224 ",closing_fee_frac INT4 NOT NULL"
225 ",start_date INT8 NOT NULL"
226 ",end_date INT8 NOT NULL"
227 ",exchange_sig BYTEA NOT NULL CHECK (length(exchange_sig)=64)"
228 ",PRIMARY KEY (exchange_pub,h_wire_method,start_date,end_date)"
229 ");"),
230 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_refunds ("
231 " rtransaction_id BIGSERIAL UNIQUE"
232 ",merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)"
233 ",h_contract_terms BYTEA NOT NULL"
234 ",coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)"
235 ",reason VARCHAR NOT NULL"
236 ",refund_amount_val INT8 NOT NULL"
237 ",refund_amount_frac INT4 NOT NULL"
238 ",refund_fee_val INT8 NOT NULL"
239 ",refund_fee_frac INT4 NOT NULL"
240 ");"),
241 /* balances of the reserves available for tips */
242 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_tip_reserves ("
243 " reserve_priv BYTEA NOT NULL CHECK (LENGTH(reserve_priv)=32)"
244 ",expiration INT8 NOT NULL"
245 ",balance_val INT8 NOT NULL"
246 ",balance_frac INT4 NOT NULL"
247 ",PRIMARY KEY (reserve_priv)"
248 ");"),
249 /* table where we remember when tipping reserves where established / enabled */
250 GNUNET_PQ_make_execute (
251 "CREATE TABLE IF NOT EXISTS merchant_tip_reserve_credits ("
252 " reserve_priv BYTEA NOT NULL CHECK (LENGTH(reserve_priv)=32)"
253 ",credit_uuid BYTEA UNIQUE NOT NULL CHECK (LENGTH(credit_uuid)=64)"
254 ",timestamp INT8 NOT NULL"
255 ",amount_val INT8 NOT NULL"
256 ",amount_frac INT4 NOT NULL"
257 ",PRIMARY KEY (credit_uuid)"
258 ");"),
259 /* tips that have been authorized */
260 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_tips ("
261 " reserve_priv BYTEA NOT NULL CHECK (LENGTH(reserve_priv)=32)"
262 ",tip_id BYTEA NOT NULL CHECK (LENGTH(tip_id)=64)"
263 ",exchange_url VARCHAR NOT NULL"
264 ",justification VARCHAR NOT NULL"
265 ",extra BYTEA NOT NULL"
266 ",timestamp INT8 NOT NULL"
267 ",amount_val INT8 NOT NULL" /* overall tip amount */
268 ",amount_frac INT4 NOT NULL"
269 ",left_val INT8 NOT NULL" /* tip amount not yet picked up */
270 ",left_frac INT4 NOT NULL"
271 ",PRIMARY KEY (tip_id)"
272 ");"),
273 /* tips that have been picked up */
274 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_tip_pickups ("
275 " tip_id BYTEA NOT NULL REFERENCES merchant_tips (tip_id) ON DELETE CASCADE"
276 ",pickup_id BYTEA NOT NULL CHECK (LENGTH(pickup_id)=64)"
277 ",amount_val INT8 NOT NULL"
278 ",amount_frac INT4 NOT NULL"
279 ",PRIMARY KEY (pickup_id)"
280 ");"),
281 /* sessions and their order_id/fulfillment_url mapping */
282 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_session_info ("
283 " session_id VARCHAR NOT NULL"
284 ",fulfillment_url VARCHAR NOT NULL"
285 ",order_id VARCHAR NOT NULL"
286 ",merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)"
287 ",timestamp INT8 NOT NULL"
288 ",PRIMARY KEY (session_id, fulfillment_url, merchant_pub)"
289 ",UNIQUE (session_id, fulfillment_url, order_id, merchant_pub)"
290 ");"),
291 GNUNET_PQ_EXECUTE_STATEMENT_END
292 };
293 struct GNUNET_PQ_PreparedStatement ps[] = {
294 GNUNET_PQ_make_prepare ("insert_deposit",
295 "INSERT INTO merchant_deposits"
296 "(h_contract_terms"
297 ",merchant_pub"
298 ",coin_pub"
299 ",exchange_url"
300 ",amount_with_fee_val"
301 ",amount_with_fee_frac"
302 ",deposit_fee_val"
303 ",deposit_fee_frac"
304 ",refund_fee_val"
305 ",refund_fee_frac"
306 ",wire_fee_val"
307 ",wire_fee_frac"
308 ",signkey_pub"
309 ",exchange_proof) VALUES "
310 "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)",
311 14),
312 GNUNET_PQ_make_prepare ("insert_transfer",
313 "INSERT INTO merchant_transfers"
314 "(h_contract_terms"
315 ",coin_pub"
316 ",wtid) VALUES "
317 "($1, $2, $3)",
318 3),
319 GNUNET_PQ_make_prepare ("insert_refund",
320 "INSERT INTO merchant_refunds"
321 "(merchant_pub"
322 ",h_contract_terms"
323 ",coin_pub"
324 ",reason"
325 ",refund_amount_val"
326 ",refund_amount_frac"
327 ",refund_fee_val"
328 ",refund_fee_frac"
329 ") VALUES"
330 "($1, $2, $3, $4, $5, $6, $7, $8)",
331 8),
332 GNUNET_PQ_make_prepare ("insert_proof",
333 "INSERT INTO merchant_proofs"
334 "(exchange_url"
335 ",wtid"
336 ",execution_time"
337 ",signkey_pub"
338 ",proof) VALUES "
339 "($1, $2, $3, $4, $5)",
340 5),
341 GNUNET_PQ_make_prepare ("insert_contract_terms",
342 "INSERT INTO merchant_contract_terms"
343 "(order_id"
344 ",merchant_pub"
345 ",timestamp"
346 ",contract_terms"
347 ",h_contract_terms)"
348 " VALUES "
349 "($1, $2, $3, $4, $5)",
350 5),
351 GNUNET_PQ_make_prepare ("insert_order",
352 "INSERT INTO merchant_orders"
353 "(order_id"
354 ",merchant_pub"
355 ",timestamp"
356 ",contract_terms)"
357 " VALUES "
358 "($1, $2, $3, $4)",
359 4),
360 GNUNET_PQ_make_prepare ("insert_session_info",
361 "INSERT INTO merchant_session_info"
362 "(session_id"
363 ",fulfillment_url"
364 ",order_id"
365 ",merchant_pub"
366 ",timestamp)"
367 " VALUES "
368 "($1, $2, $3, $4, $5)",
369 5),
370 GNUNET_PQ_make_prepare ("mark_proposal_paid",
371 "UPDATE merchant_contract_terms SET"
372 " paid=TRUE"
373 " WHERE h_contract_terms=$1"
374 " AND merchant_pub=$2",
375 2),
376 GNUNET_PQ_make_prepare ("insert_wire_fee",
377 "INSERT INTO exchange_wire_fees"
378 "(exchange_pub"
379 ",h_wire_method"
380 ",wire_fee_val"
381 ",wire_fee_frac"
382 ",closing_fee_val"
383 ",closing_fee_frac"
384 ",start_date"
385 ",end_date"
386 ",exchange_sig)"
387 " VALUES "
388 "($1, $2, $3, $4, $5, $6, $7, $8, $9)",
389 9),
390 GNUNET_PQ_make_prepare ("lookup_wire_fee",
391 "SELECT"
392 " wire_fee_val"
393 ",wire_fee_frac"
394 ",closing_fee_val"
395 ",closing_fee_frac"
396 ",start_date"
397 ",end_date"
398 ",exchange_sig"
399 " FROM exchange_wire_fees"
400 " WHERE exchange_pub=$1"
401 " AND h_wire_method=$2"
402 " AND start_date <= $3"
403 " AND end_date > $3",
404 1),
405 GNUNET_PQ_make_prepare ("find_contract_terms_from_hash",
406 "SELECT"
407 " contract_terms"
408 " FROM merchant_contract_terms"
409 " WHERE h_contract_terms=$1"
410 " AND merchant_pub=$2",
411 2),
412 GNUNET_PQ_make_prepare ("find_paid_contract_terms_from_hash",
413 "SELECT"
414 " contract_terms"
415 " FROM merchant_contract_terms"
416 " WHERE h_contract_terms=$1"
417 " AND merchant_pub=$2"
418 " AND paid=TRUE",
419 2),
420 GNUNET_PQ_make_prepare ("end_transaction",
421 "COMMIT",
422 0),
423
424 GNUNET_PQ_make_prepare ("find_refunds",
425 "SELECT"
426 " refund_amount_val"
427 ",refund_amount_frac"
428 " FROM merchant_refunds"
429 " WHERE coin_pub=$1",
430 1),
431 GNUNET_PQ_make_prepare ("find_contract_terms_history",
432 "SELECT"
433 " contract_terms"
434 " FROM merchant_contract_terms"
435 " WHERE"
436 " order_id=$1"
437 " AND merchant_pub=$2"
438 " AND paid=TRUE",
439 2),
440 GNUNET_PQ_make_prepare ("find_contract_terms",
441 "SELECT"
442 " contract_terms"
443 " FROM merchant_contract_terms"
444 " WHERE"
445 " order_id=$1"
446 " AND merchant_pub=$2",
447 2),
448 GNUNET_PQ_make_prepare ("find_order",
449 "SELECT"
450 " contract_terms"
451 " FROM merchant_orders"
452 " WHERE"
453 " order_id=$1"
454 " AND merchant_pub=$2",
455 2),
456 GNUNET_PQ_make_prepare ("find_session_info",
457 "SELECT"
458 " order_id"
459 " FROM merchant_session_info"
460 " WHERE"
461 " fulfillment_url=$1"
462 " AND session_id=$2"
463 " AND merchant_pub=$3",
464 2),
465 GNUNET_PQ_make_prepare ("find_contract_terms_by_date",
466 "SELECT"
467 " contract_terms"
468 ",order_id"
469 ",row_id"
470 " FROM merchant_contract_terms"
471 " WHERE"
472 " timestamp<$1"
473 " AND merchant_pub=$2"
474 " AND paid=TRUE"
475 " ORDER BY row_id DESC, timestamp DESC"
476 " LIMIT $3",
477 3),
478 GNUNET_PQ_make_prepare ("find_refunds_from_contract_terms_hash",
479 "SELECT"
480 " coin_pub"
481 ",rtransaction_id"
482 ",refund_amount_val"
483 ",refund_amount_frac"
484 ",refund_fee_val"
485 ",refund_fee_frac"
486 ",reason"
487 " FROM merchant_refunds"
488 " WHERE merchant_pub=$1"
489 " AND h_contract_terms=$2",
490 2),
491 GNUNET_PQ_make_prepare ("find_contract_terms_by_date_and_range_asc",
492 "SELECT"
493 " contract_terms"
494 ",order_id"
495 ",row_id"
496 " FROM merchant_contract_terms"
497 " WHERE"
498 " timestamp>$1"
499 " AND merchant_pub=$2"
500 " AND row_id>$3"
501 " AND paid=TRUE"
502 " ORDER BY row_id ASC, timestamp ASC"
503 " LIMIT $4",
504 4),
505 GNUNET_PQ_make_prepare ("find_contract_terms_by_date_and_range",
506 "SELECT"
507 " contract_terms"
508 ",order_id"
509 ",row_id"
510 " FROM merchant_contract_terms"
511 " WHERE"
512 " timestamp>$1"
513 " AND merchant_pub=$2"
514 " AND row_id>$3"
515 " AND paid=TRUE"
516 " ORDER BY row_id DESC, timestamp DESC"
517 " LIMIT $4",
518 4),
519 GNUNET_PQ_make_prepare ("find_contract_terms_by_date_and_range_past_asc",
520 "SELECT"
521 " contract_terms"
522 ",order_id"
523 ",row_id"
524 " FROM merchant_contract_terms"
525 " WHERE"
526 " timestamp<$1"
527 " AND merchant_pub=$2"
528 " AND row_id<$3"
529 " AND paid=TRUE"
530 " ORDER BY row_id ASC, timestamp ASC"
531 " LIMIT $4",
532 4),
533 GNUNET_PQ_make_prepare ("find_contract_terms_by_date_and_range_past",
534 "SELECT"
535 " contract_terms"
536 ",order_id"
537 ",row_id"
538 " FROM merchant_contract_terms"
539 " WHERE"
540 " timestamp<$1"
541 " AND merchant_pub=$2"
542 " AND row_id<$3"
543 " AND paid=TRUE"
544 " ORDER BY row_id DESC, timestamp DESC"
545 " LIMIT $4",
546 4),
547 GNUNET_PQ_make_prepare ("find_deposits",
548 "SELECT"
549 " coin_pub"
550 ",exchange_url"
551 ",amount_with_fee_val"
552 ",amount_with_fee_frac"
553 ",deposit_fee_val"
554 ",deposit_fee_frac"
555 ",refund_fee_val"
556 ",refund_fee_frac"
557 ",wire_fee_val"
558 ",wire_fee_frac"
559 ",exchange_proof"
560 " FROM merchant_deposits"
561 " WHERE h_contract_terms=$1"
562 " AND merchant_pub=$2",
563 2),
564 GNUNET_PQ_make_prepare ("find_deposits_by_hash_and_coin",
565 "SELECT"
566 " amount_with_fee_val"
567 ",amount_with_fee_frac"
568 ",deposit_fee_val"
569 ",deposit_fee_frac"
570 ",refund_fee_val"
571 ",refund_fee_frac"
572 ",wire_fee_val"
573 ",wire_fee_frac"
574 ",exchange_url"
575 ",exchange_proof"
576 " FROM merchant_deposits"
577 " WHERE h_contract_terms=$1"
578 " AND merchant_pub=$2"
579 " AND coin_pub=$3",
580 3),
581 GNUNET_PQ_make_prepare ("find_transfers_by_hash",
582 "SELECT"
583 " coin_pub"
584 ",wtid"
585 ",merchant_proofs.execution_time"
586 ",merchant_proofs.proof"
587 " FROM merchant_transfers"
588 " JOIN merchant_proofs USING (wtid)"
589 " WHERE h_contract_terms=$1",
590 1),
591 GNUNET_PQ_make_prepare ("find_deposits_by_wtid",
592 "SELECT"
593 " merchant_transfers.h_contract_terms"
594 ",merchant_transfers.coin_pub"
595 ",merchant_deposits.amount_with_fee_val"
596 ",merchant_deposits.amount_with_fee_frac"
597 ",merchant_deposits.deposit_fee_val"
598 ",merchant_deposits.deposit_fee_frac"
599 ",merchant_deposits.refund_fee_val"
600 ",merchant_deposits.refund_fee_frac"
601 ",merchant_deposits.wire_fee_val"
602 ",merchant_deposits.wire_fee_frac"
603 ",merchant_deposits.exchange_url"
604 ",merchant_deposits.exchange_proof"
605 " FROM merchant_transfers"
606 " JOIN merchant_deposits"
607 " ON (merchant_deposits.h_contract_terms = merchant_transfers.h_contract_terms"
608 " AND"
609 " merchant_deposits.coin_pub = merchant_transfers.coin_pub)"
610 " WHERE wtid=$1",
611 1),
612 GNUNET_PQ_make_prepare ("find_proof_by_wtid",
613 "SELECT"
614 " proof"
615 " FROM merchant_proofs"
616 " WHERE wtid=$1"
617 " AND exchange_url=$2",
618 2),
619 GNUNET_PQ_make_prepare ("lookup_tip_reserve_balance",
620 "SELECT"
621 " expiration"
622 ",balance_val"
623 ",balance_frac"
624 " FROM merchant_tip_reserves"
625 " WHERE reserve_priv=$1",
626 1),
627 GNUNET_PQ_make_prepare ("find_tip_authorizations",
628 "SELECT"
629 " amount_val"
630 ",amount_frac"
631 ",justification"
632 ",extra"
633 ",tip_id"
634 " FROM merchant_tips"
635 " WHERE reserve_priv=$1",
636 1),
637 GNUNET_PQ_make_prepare ("update_tip_reserve_balance",
638 "UPDATE merchant_tip_reserves SET"
639 " expiration=$2"
640 ",balance_val=$3"
641 ",balance_frac=$4"
642 " WHERE reserve_priv=$1",
643 4),
644 GNUNET_PQ_make_prepare ("insert_tip_reserve_balance",
645 "INSERT INTO merchant_tip_reserves"
646 "(reserve_priv"
647 ",expiration"
648 ",balance_val"
649 ",balance_frac"
650 ") VALUES "
651 "($1, $2, $3, $4)",
652 4),
653 GNUNET_PQ_make_prepare ("insert_tip_justification",
654 "INSERT INTO merchant_tips"
655 "(reserve_priv"
656 ",tip_id"
657 ",exchange_url"
658 ",justification"
659 ",extra"
660 ",timestamp"
661 ",amount_val"
662 ",amount_frac"
663 ",left_val"
664 ",left_frac"
665 ") VALUES "
666 "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)",
667 10),
668 GNUNET_PQ_make_prepare ("lookup_reserve_by_tip_id",
669 "SELECT"
670 " reserve_priv"
671 ",left_val"
672 ",left_frac"
673 " FROM merchant_tips"
674 " WHERE tip_id=$1",
675 1),
676 GNUNET_PQ_make_prepare ("lookup_amount_by_pickup",
677 "SELECT"
678 " amount_val"
679 ",amount_frac"
680 " FROM merchant_tip_pickups"
681 " WHERE pickup_id=$1"
682 " AND tip_id=$2",
683 2),
684 GNUNET_PQ_make_prepare ("find_tip_by_id",
685 "SELECT"
686 " exchange_url"
687 ",extra"
688 ",timestamp"
689 ",amount_val"
690 ",amount_frac"
691 ",left_val"
692 ",left_frac"
693 " FROM merchant_tips"
694 " WHERE tip_id=$1",
695 1),
696 GNUNET_PQ_make_prepare ("update_tip_balance",
697 "UPDATE merchant_tips SET"
698 " left_val=$2"
699 ",left_frac=$3"
700 " WHERE tip_id=$1",
701 3),
702 GNUNET_PQ_make_prepare ("insert_pickup_id",
703 "INSERT INTO merchant_tip_pickups"
704 "(tip_id"
705 ",pickup_id"
706 ",amount_val"
707 ",amount_frac"
708 ") VALUES "
709 "($1, $2, $3, $4)",
710 4),
711 GNUNET_PQ_make_prepare ("insert_tip_credit_uuid",
712 "INSERT INTO merchant_tip_reserve_credits"
713 "(reserve_priv"
714 ",credit_uuid"
715 ",timestamp"
716 ",amount_val"
717 ",amount_frac"
718 ") VALUES "
719 "($1, $2, $3, $4, $5)",
720 5),
721 GNUNET_PQ_make_prepare ("lookup_tip_credit_uuid",
722 "SELECT 1 "
723 "FROM merchant_tip_reserve_credits "
724 "WHERE credit_uuid=$1 AND reserve_priv=$2",
725 2),
726 GNUNET_PQ_PREPARED_STATEMENT_END
727 };
728
729 if (GNUNET_OK !=
730 GNUNET_PQ_exec_statements (pg->conn,
731 es))
732 {
733 GNUNET_break (0);
734 return GNUNET_SYSERR;
735 }
736 if (GNUNET_OK !=
737 GNUNET_PQ_prepare_statements (pg->conn,
738 ps))
739 {
740 GNUNET_break (0);
741 return GNUNET_SYSERR;
742 }
743 return GNUNET_OK;
744}
745
746
747/**
748 * Check that the database connection is still up. 145 * Check that the database connection is still up.
749 * 146 *
750 * @param pg connection to check 147 * @param pg connection to check
@@ -752,14 +149,7 @@ postgres_initialize (void *cls)
752static void 149static void
753check_connection (struct PostgresClosure *pg) 150check_connection (struct PostgresClosure *pg)
754{ 151{
755 if (CONNECTION_BAD != PQstatus (pg->conn)) 152 GNUNET_PQ_reconnect_if_down (pg->conn);
756 return;
757 PQfinish (pg->conn);
758 pg->conn = GNUNET_PQ_connect_with_cfg (pg->cfg,
759 "merchantdb-postgres");
760 GNUNET_break (NULL != pg->conn);
761 GNUNET_break (GNUNET_OK ==
762 postgres_initialize (pg));
763} 153}
764 154
765 155
@@ -774,15 +164,16 @@ static void
774postgres_preflight (void *cls) 164postgres_preflight (void *cls)
775{ 165{
776 struct PostgresClosure *pg = cls; 166 struct PostgresClosure *pg = cls;
777 PGresult *result; 167 struct GNUNET_PQ_ExecuteStatement es[] = {
778 ExecStatusType status; 168 GNUNET_PQ_make_execute ("COMMIT"),
169 GNUNET_PQ_EXECUTE_STATEMENT_END
170 };
779 171
780 if (NULL == pg->transaction_name) 172 if (NULL == pg->transaction_name)
781 return; /* all good */ 173 return; /* all good */
782 result = PQexec (pg->conn, 174 if (GNUNET_OK ==
783 "COMMIT"); 175 GNUNET_PQ_exec_statements (pg->conn,
784 status = PQresultStatus (result); 176 es))
785 if (PGRES_COMMAND_OK == status)
786 { 177 {
787 GNUNET_log (GNUNET_ERROR_TYPE_ERROR, 178 GNUNET_log (GNUNET_ERROR_TYPE_ERROR,
788 "BUG: Preflight check committed transaction `%s'!\n", 179 "BUG: Preflight check committed transaction `%s'!\n",
@@ -795,7 +186,6 @@ postgres_preflight (void *cls)
795 pg->transaction_name); 186 pg->transaction_name);
796 } 187 }
797 pg->transaction_name = NULL; 188 pg->transaction_name = NULL;
798 PQclear (result);
799} 189}
800 190
801 191
@@ -812,26 +202,23 @@ postgres_start (void *cls,
812 const char *name) 202 const char *name)
813{ 203{
814 struct PostgresClosure *pg = cls; 204 struct PostgresClosure *pg = cls;
815 PGresult *result; 205 struct GNUNET_PQ_ExecuteStatement es[] = {
816 ExecStatusType ex; 206 GNUNET_PQ_make_execute ("START TRANSACTION ISOLATION LEVEL SERIALIZABLE"),
207 GNUNET_PQ_EXECUTE_STATEMENT_END
208 };
817 209
818 check_connection (pg); 210 check_connection (pg);
819 postgres_preflight (pg); 211 postgres_preflight (pg);
820 GNUNET_log (GNUNET_ERROR_TYPE_DEBUG, 212 GNUNET_log (GNUNET_ERROR_TYPE_DEBUG,
821 "Starting merchant DB transaction\n"); 213 "Starting merchant DB transaction\n");
822 result = PQexec (pg->conn, 214 if (GNUNET_OK !=
823 "START TRANSACTION ISOLATION LEVEL SERIALIZABLE"); 215 GNUNET_PQ_exec_statements (pg->conn,
824 if (PGRES_COMMAND_OK != 216 es))
825 (ex = PQresultStatus (result)))
826 { 217 {
827 TALER_LOG_ERROR ("Failed to start transaction (%s): %s\n", 218 TALER_LOG_ERROR ("Failed to start transaction\n");
828 PQresStatus (ex),
829 PQerrorMessage (pg->conn));
830 GNUNET_break (0); 219 GNUNET_break (0);
831 PQclear (result);
832 return GNUNET_SYSERR; 220 return GNUNET_SYSERR;
833 } 221 }
834 PQclear (result);
835 pg->transaction_name = name; 222 pg->transaction_name = name;
836 return GNUNET_OK; 223 return GNUNET_OK;
837} 224}
@@ -847,15 +234,16 @@ static void
847postgres_rollback (void *cls) 234postgres_rollback (void *cls)
848{ 235{
849 struct PostgresClosure *pg = cls; 236 struct PostgresClosure *pg = cls;
850 PGresult *result; 237 struct GNUNET_PQ_ExecuteStatement es[] = {
238 GNUNET_PQ_make_execute ("ROLLBACK"),
239 GNUNET_PQ_EXECUTE_STATEMENT_END
240 };
851 241
852 GNUNET_log (GNUNET_ERROR_TYPE_DEBUG, 242 GNUNET_log (GNUNET_ERROR_TYPE_DEBUG,
853 "Rolling back merchant DB transaction\n"); 243 "Rolling back merchant DB transaction\n");
854 result = PQexec (pg->conn, 244 GNUNET_break (GNUNET_OK ==
855 "ROLLBACK"); 245 GNUNET_PQ_exec_statements (pg->conn,
856 GNUNET_break (PGRES_COMMAND_OK == 246 es));
857 PQresultStatus (result));
858 PQclear (result);
859 pg->transaction_name = NULL; 247 pg->transaction_name = NULL;
860} 248}
861 249
@@ -3669,6 +3057,582 @@ libtaler_plugin_merchantdb_postgres_init (void *cls)
3669 struct PostgresClosure *pg; 3057 struct PostgresClosure *pg;
3670 struct TALER_MERCHANTDB_Plugin *plugin; 3058 struct TALER_MERCHANTDB_Plugin *plugin;
3671 const char *ec; 3059 const char *ec;
3060 struct GNUNET_PQ_ExecuteStatement es[] = {
3061 /* Orders created by the frontend, not signed or given a nonce yet.
3062 The contract terms will change (nonce will be added) when moved to the
3063 contract terms table */
3064 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_orders ("
3065 "order_id VARCHAR NOT NULL"
3066 ",merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)"
3067 ",contract_terms BYTEA NOT NULL"
3068 ",timestamp INT8 NOT NULL"
3069 ",PRIMARY KEY (order_id, merchant_pub)"
3070 ");"),
3071 /* Offers we made to customers */
3072 GNUNET_PQ_make_execute (
3073 "CREATE TABLE IF NOT EXISTS merchant_contract_terms ("
3074 "order_id VARCHAR NOT NULL"
3075 ",merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)"
3076 ",contract_terms BYTEA NOT NULL"
3077 ",h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)"
3078 ",timestamp INT8 NOT NULL"
3079 ",row_id BIGSERIAL UNIQUE"
3080 ",paid boolean DEFAULT FALSE NOT NULL"
3081 ",PRIMARY KEY (order_id, merchant_pub)"
3082 ",UNIQUE (h_contract_terms, merchant_pub)"
3083 ");"),
3084 /* Table with the proofs for each coin we deposited at the exchange */
3085 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_deposits ("
3086 " h_contract_terms BYTEA NOT NULL"
3087 ",merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)"
3088 ",coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)"
3089 ",exchange_url VARCHAR NOT NULL"
3090 ",amount_with_fee_val INT8 NOT NULL"
3091 ",amount_with_fee_frac INT4 NOT NULL"
3092 ",deposit_fee_val INT8 NOT NULL"
3093 ",deposit_fee_frac INT4 NOT NULL"
3094 ",refund_fee_val INT8 NOT NULL"
3095 ",refund_fee_frac INT4 NOT NULL"
3096 ",wire_fee_val INT8 NOT NULL"
3097 ",wire_fee_frac INT4 NOT NULL"
3098 ",signkey_pub BYTEA NOT NULL CHECK (LENGTH(signkey_pub)=32)"
3099 ",exchange_proof BYTEA NOT NULL"
3100 ",PRIMARY KEY (h_contract_terms, coin_pub)"
3101 ",FOREIGN KEY (h_contract_terms, merchant_pub) REFERENCES merchant_contract_terms (h_contract_terms, merchant_pub)"
3102 ");"),
3103 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_proofs ("
3104 " exchange_url VARCHAR NOT NULL"
3105 ",wtid BYTEA CHECK (LENGTH(wtid)=32)"
3106 ",execution_time INT8 NOT NULL"
3107 ",signkey_pub BYTEA NOT NULL CHECK (LENGTH(signkey_pub)=32)"
3108 ",proof BYTEA NOT NULL"
3109 ",PRIMARY KEY (wtid, exchange_url)"
3110 ");"),
3111 /* Note that h_contract_terms + coin_pub may actually be unknown to
3112 us, e.g. someone else deposits something for us at the exchange.
3113 Hence those cannot be foreign keys into deposits/transactions! */
3114 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_transfers ("
3115 " h_contract_terms BYTEA NOT NULL"
3116 ",coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)"
3117 ",wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)"
3118 ",PRIMARY KEY (h_contract_terms, coin_pub)"
3119 ");"),
3120 GNUNET_PQ_make_try_execute (
3121 "CREATE INDEX IF NOT EXISTS merchant_transfers_by_coin"
3122 " ON merchant_transfers (h_contract_terms, coin_pub)"),
3123 GNUNET_PQ_make_try_execute (
3124 "CREATE INDEX IF NOT EXISTS merchant_transfers_by_wtid"
3125 " ON merchant_transfers (wtid)"),
3126 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS exchange_wire_fees ("
3127 " exchange_pub BYTEA NOT NULL CHECK (length(exchange_pub)=32)"
3128 ",h_wire_method BYTEA NOT NULL CHECK (length(h_wire_method)=64)"
3129 ",wire_fee_val INT8 NOT NULL"
3130 ",wire_fee_frac INT4 NOT NULL"
3131 ",closing_fee_val INT8 NOT NULL"
3132 ",closing_fee_frac INT4 NOT NULL"
3133 ",start_date INT8 NOT NULL"
3134 ",end_date INT8 NOT NULL"
3135 ",exchange_sig BYTEA NOT NULL CHECK (length(exchange_sig)=64)"
3136 ",PRIMARY KEY (exchange_pub,h_wire_method,start_date,end_date)"
3137 ");"),
3138 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_refunds ("
3139 " rtransaction_id BIGSERIAL UNIQUE"
3140 ",merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)"
3141 ",h_contract_terms BYTEA NOT NULL"
3142 ",coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)"
3143 ",reason VARCHAR NOT NULL"
3144 ",refund_amount_val INT8 NOT NULL"
3145 ",refund_amount_frac INT4 NOT NULL"
3146 ",refund_fee_val INT8 NOT NULL"
3147 ",refund_fee_frac INT4 NOT NULL"
3148 ");"),
3149 /* balances of the reserves available for tips */
3150 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_tip_reserves ("
3151 " reserve_priv BYTEA NOT NULL CHECK (LENGTH(reserve_priv)=32)"
3152 ",expiration INT8 NOT NULL"
3153 ",balance_val INT8 NOT NULL"
3154 ",balance_frac INT4 NOT NULL"
3155 ",PRIMARY KEY (reserve_priv)"
3156 ");"),
3157 /* table where we remember when tipping reserves where established / enabled */
3158 GNUNET_PQ_make_execute (
3159 "CREATE TABLE IF NOT EXISTS merchant_tip_reserve_credits ("
3160 " reserve_priv BYTEA NOT NULL CHECK (LENGTH(reserve_priv)=32)"
3161 ",credit_uuid BYTEA UNIQUE NOT NULL CHECK (LENGTH(credit_uuid)=64)"
3162 ",timestamp INT8 NOT NULL"
3163 ",amount_val INT8 NOT NULL"
3164 ",amount_frac INT4 NOT NULL"
3165 ",PRIMARY KEY (credit_uuid)"
3166 ");"),
3167 /* tips that have been authorized */
3168 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_tips ("
3169 " reserve_priv BYTEA NOT NULL CHECK (LENGTH(reserve_priv)=32)"
3170 ",tip_id BYTEA NOT NULL CHECK (LENGTH(tip_id)=64)"
3171 ",exchange_url VARCHAR NOT NULL"
3172 ",justification VARCHAR NOT NULL"
3173 ",extra BYTEA NOT NULL"
3174 ",timestamp INT8 NOT NULL"
3175 ",amount_val INT8 NOT NULL" /* overall tip amount */
3176 ",amount_frac INT4 NOT NULL"
3177 ",left_val INT8 NOT NULL" /* tip amount not yet picked up */
3178 ",left_frac INT4 NOT NULL"
3179 ",PRIMARY KEY (tip_id)"
3180 ");"),
3181 /* tips that have been picked up */
3182 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_tip_pickups ("
3183 " tip_id BYTEA NOT NULL REFERENCES merchant_tips (tip_id) ON DELETE CASCADE"
3184 ",pickup_id BYTEA NOT NULL CHECK (LENGTH(pickup_id)=64)"
3185 ",amount_val INT8 NOT NULL"
3186 ",amount_frac INT4 NOT NULL"
3187 ",PRIMARY KEY (pickup_id)"
3188 ");"),
3189 /* sessions and their order_id/fulfillment_url mapping */
3190 GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS merchant_session_info ("
3191 " session_id VARCHAR NOT NULL"
3192 ",fulfillment_url VARCHAR NOT NULL"
3193 ",order_id VARCHAR NOT NULL"
3194 ",merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)"
3195 ",timestamp INT8 NOT NULL"
3196 ",PRIMARY KEY (session_id, fulfillment_url, merchant_pub)"
3197 ",UNIQUE (session_id, fulfillment_url, order_id, merchant_pub)"
3198 ");"),
3199 GNUNET_PQ_EXECUTE_STATEMENT_END
3200 };
3201 struct GNUNET_PQ_PreparedStatement ps[] = {
3202 GNUNET_PQ_make_prepare ("insert_deposit",
3203 "INSERT INTO merchant_deposits"
3204 "(h_contract_terms"
3205 ",merchant_pub"
3206 ",coin_pub"
3207 ",exchange_url"
3208 ",amount_with_fee_val"
3209 ",amount_with_fee_frac"
3210 ",deposit_fee_val"
3211 ",deposit_fee_frac"
3212 ",refund_fee_val"
3213 ",refund_fee_frac"
3214 ",wire_fee_val"
3215 ",wire_fee_frac"
3216 ",signkey_pub"
3217 ",exchange_proof) VALUES "
3218 "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)",
3219 14),
3220 GNUNET_PQ_make_prepare ("insert_transfer",
3221 "INSERT INTO merchant_transfers"
3222 "(h_contract_terms"
3223 ",coin_pub"
3224 ",wtid) VALUES "
3225 "($1, $2, $3)",
3226 3),
3227 GNUNET_PQ_make_prepare ("insert_refund",
3228 "INSERT INTO merchant_refunds"
3229 "(merchant_pub"
3230 ",h_contract_terms"
3231 ",coin_pub"
3232 ",reason"
3233 ",refund_amount_val"
3234 ",refund_amount_frac"
3235 ",refund_fee_val"
3236 ",refund_fee_frac"
3237 ") VALUES"
3238 "($1, $2, $3, $4, $5, $6, $7, $8)",
3239 8),
3240 GNUNET_PQ_make_prepare ("insert_proof",
3241 "INSERT INTO merchant_proofs"
3242 "(exchange_url"
3243 ",wtid"
3244 ",execution_time"
3245 ",signkey_pub"
3246 ",proof) VALUES "
3247 "($1, $2, $3, $4, $5)",
3248 5),
3249 GNUNET_PQ_make_prepare ("insert_contract_terms",
3250 "INSERT INTO merchant_contract_terms"
3251 "(order_id"
3252 ",merchant_pub"
3253 ",timestamp"
3254 ",contract_terms"
3255 ",h_contract_terms)"
3256 " VALUES "
3257 "($1, $2, $3, $4, $5)",
3258 5),
3259 GNUNET_PQ_make_prepare ("insert_order",
3260 "INSERT INTO merchant_orders"
3261 "(order_id"
3262 ",merchant_pub"
3263 ",timestamp"
3264 ",contract_terms)"
3265 " VALUES "
3266 "($1, $2, $3, $4)",
3267 4),
3268 GNUNET_PQ_make_prepare ("insert_session_info",
3269 "INSERT INTO merchant_session_info"
3270 "(session_id"
3271 ",fulfillment_url"
3272 ",order_id"
3273 ",merchant_pub"
3274 ",timestamp)"
3275 " VALUES "
3276 "($1, $2, $3, $4, $5)",
3277 5),
3278 GNUNET_PQ_make_prepare ("mark_proposal_paid",
3279 "UPDATE merchant_contract_terms SET"
3280 " paid=TRUE"
3281 " WHERE h_contract_terms=$1"
3282 " AND merchant_pub=$2",
3283 2),
3284 GNUNET_PQ_make_prepare ("insert_wire_fee",
3285 "INSERT INTO exchange_wire_fees"
3286 "(exchange_pub"
3287 ",h_wire_method"
3288 ",wire_fee_val"
3289 ",wire_fee_frac"
3290 ",closing_fee_val"
3291 ",closing_fee_frac"
3292 ",start_date"
3293 ",end_date"
3294 ",exchange_sig)"
3295 " VALUES "
3296 "($1, $2, $3, $4, $5, $6, $7, $8, $9)",
3297 9),
3298 GNUNET_PQ_make_prepare ("lookup_wire_fee",
3299 "SELECT"
3300 " wire_fee_val"
3301 ",wire_fee_frac"
3302 ",closing_fee_val"
3303 ",closing_fee_frac"
3304 ",start_date"
3305 ",end_date"
3306 ",exchange_sig"
3307 " FROM exchange_wire_fees"
3308 " WHERE exchange_pub=$1"
3309 " AND h_wire_method=$2"
3310 " AND start_date <= $3"
3311 " AND end_date > $3",
3312 1),
3313 GNUNET_PQ_make_prepare ("find_contract_terms_from_hash",
3314 "SELECT"
3315 " contract_terms"
3316 " FROM merchant_contract_terms"
3317 " WHERE h_contract_terms=$1"
3318 " AND merchant_pub=$2",
3319 2),
3320 GNUNET_PQ_make_prepare ("find_paid_contract_terms_from_hash",
3321 "SELECT"
3322 " contract_terms"
3323 " FROM merchant_contract_terms"
3324 " WHERE h_contract_terms=$1"
3325 " AND merchant_pub=$2"
3326 " AND paid=TRUE",
3327 2),
3328 GNUNET_PQ_make_prepare ("end_transaction",
3329 "COMMIT",
3330 0),
3331
3332 GNUNET_PQ_make_prepare ("find_refunds",
3333 "SELECT"
3334 " refund_amount_val"
3335 ",refund_amount_frac"
3336 " FROM merchant_refunds"
3337 " WHERE coin_pub=$1",
3338 1),
3339 GNUNET_PQ_make_prepare ("find_contract_terms_history",
3340 "SELECT"
3341 " contract_terms"
3342 " FROM merchant_contract_terms"
3343 " WHERE"
3344 " order_id=$1"
3345 " AND merchant_pub=$2"
3346 " AND paid=TRUE",
3347 2),
3348 GNUNET_PQ_make_prepare ("find_contract_terms",
3349 "SELECT"
3350 " contract_terms"
3351 " FROM merchant_contract_terms"
3352 " WHERE"
3353 " order_id=$1"
3354 " AND merchant_pub=$2",
3355 2),
3356 GNUNET_PQ_make_prepare ("find_order",
3357 "SELECT"
3358 " contract_terms"
3359 " FROM merchant_orders"
3360 " WHERE"
3361 " order_id=$1"
3362 " AND merchant_pub=$2",
3363 2),
3364 GNUNET_PQ_make_prepare ("find_session_info",
3365 "SELECT"
3366 " order_id"
3367 " FROM merchant_session_info"
3368 " WHERE"
3369 " fulfillment_url=$1"
3370 " AND session_id=$2"
3371 " AND merchant_pub=$3",
3372 2),
3373 GNUNET_PQ_make_prepare ("find_contract_terms_by_date",
3374 "SELECT"
3375 " contract_terms"
3376 ",order_id"
3377 ",row_id"
3378 " FROM merchant_contract_terms"
3379 " WHERE"
3380 " timestamp<$1"
3381 " AND merchant_pub=$2"
3382 " AND paid=TRUE"
3383 " ORDER BY row_id DESC, timestamp DESC"
3384 " LIMIT $3",
3385 3),
3386 GNUNET_PQ_make_prepare ("find_refunds_from_contract_terms_hash",
3387 "SELECT"
3388 " coin_pub"
3389 ",rtransaction_id"
3390 ",refund_amount_val"
3391 ",refund_amount_frac"
3392 ",refund_fee_val"
3393 ",refund_fee_frac"
3394 ",reason"
3395 " FROM merchant_refunds"
3396 " WHERE merchant_pub=$1"
3397 " AND h_contract_terms=$2",
3398 2),
3399 GNUNET_PQ_make_prepare ("find_contract_terms_by_date_and_range_asc",
3400 "SELECT"
3401 " contract_terms"
3402 ",order_id"
3403 ",row_id"
3404 " FROM merchant_contract_terms"
3405 " WHERE"
3406 " timestamp>$1"
3407 " AND merchant_pub=$2"
3408 " AND row_id>$3"
3409 " AND paid=TRUE"
3410 " ORDER BY row_id ASC, timestamp ASC"
3411 " LIMIT $4",
3412 4),
3413 GNUNET_PQ_make_prepare ("find_contract_terms_by_date_and_range",
3414 "SELECT"
3415 " contract_terms"
3416 ",order_id"
3417 ",row_id"
3418 " FROM merchant_contract_terms"
3419 " WHERE"
3420 " timestamp>$1"
3421 " AND merchant_pub=$2"
3422 " AND row_id>$3"
3423 " AND paid=TRUE"
3424 " ORDER BY row_id DESC, timestamp DESC"
3425 " LIMIT $4",
3426 4),
3427 GNUNET_PQ_make_prepare ("find_contract_terms_by_date_and_range_past_asc",
3428 "SELECT"
3429 " contract_terms"
3430 ",order_id"
3431 ",row_id"
3432 " FROM merchant_contract_terms"
3433 " WHERE"
3434 " timestamp<$1"
3435 " AND merchant_pub=$2"
3436 " AND row_id<$3"
3437 " AND paid=TRUE"
3438 " ORDER BY row_id ASC, timestamp ASC"
3439 " LIMIT $4",
3440 4),
3441 GNUNET_PQ_make_prepare ("find_contract_terms_by_date_and_range_past",
3442 "SELECT"
3443 " contract_terms"
3444 ",order_id"
3445 ",row_id"
3446 " FROM merchant_contract_terms"
3447 " WHERE"
3448 " timestamp<$1"
3449 " AND merchant_pub=$2"
3450 " AND row_id<$3"
3451 " AND paid=TRUE"
3452 " ORDER BY row_id DESC, timestamp DESC"
3453 " LIMIT $4",
3454 4),
3455 GNUNET_PQ_make_prepare ("find_deposits",
3456 "SELECT"
3457 " coin_pub"
3458 ",exchange_url"
3459 ",amount_with_fee_val"
3460 ",amount_with_fee_frac"
3461 ",deposit_fee_val"
3462 ",deposit_fee_frac"
3463 ",refund_fee_val"
3464 ",refund_fee_frac"
3465 ",wire_fee_val"
3466 ",wire_fee_frac"
3467 ",exchange_proof"
3468 " FROM merchant_deposits"
3469 " WHERE h_contract_terms=$1"
3470 " AND merchant_pub=$2",
3471 2),
3472 GNUNET_PQ_make_prepare ("find_deposits_by_hash_and_coin",
3473 "SELECT"
3474 " amount_with_fee_val"
3475 ",amount_with_fee_frac"
3476 ",deposit_fee_val"
3477 ",deposit_fee_frac"
3478 ",refund_fee_val"
3479 ",refund_fee_frac"
3480 ",wire_fee_val"
3481 ",wire_fee_frac"
3482 ",exchange_url"
3483 ",exchange_proof"
3484 " FROM merchant_deposits"
3485 " WHERE h_contract_terms=$1"
3486 " AND merchant_pub=$2"
3487 " AND coin_pub=$3",
3488 3),
3489 GNUNET_PQ_make_prepare ("find_transfers_by_hash",
3490 "SELECT"
3491 " coin_pub"
3492 ",wtid"
3493 ",merchant_proofs.execution_time"
3494 ",merchant_proofs.proof"
3495 " FROM merchant_transfers"
3496 " JOIN merchant_proofs USING (wtid)"
3497 " WHERE h_contract_terms=$1",
3498 1),
3499 GNUNET_PQ_make_prepare ("find_deposits_by_wtid",
3500 "SELECT"
3501 " merchant_transfers.h_contract_terms"
3502 ",merchant_transfers.coin_pub"
3503 ",merchant_deposits.amount_with_fee_val"
3504 ",merchant_deposits.amount_with_fee_frac"
3505 ",merchant_deposits.deposit_fee_val"
3506 ",merchant_deposits.deposit_fee_frac"
3507 ",merchant_deposits.refund_fee_val"
3508 ",merchant_deposits.refund_fee_frac"
3509 ",merchant_deposits.wire_fee_val"
3510 ",merchant_deposits.wire_fee_frac"
3511 ",merchant_deposits.exchange_url"
3512 ",merchant_deposits.exchange_proof"
3513 " FROM merchant_transfers"
3514 " JOIN merchant_deposits"
3515 " ON (merchant_deposits.h_contract_terms = merchant_transfers.h_contract_terms"
3516 " AND"
3517 " merchant_deposits.coin_pub = merchant_transfers.coin_pub)"
3518 " WHERE wtid=$1",
3519 1),
3520 GNUNET_PQ_make_prepare ("find_proof_by_wtid",
3521 "SELECT"
3522 " proof"
3523 " FROM merchant_proofs"
3524 " WHERE wtid=$1"
3525 " AND exchange_url=$2",
3526 2),
3527 GNUNET_PQ_make_prepare ("lookup_tip_reserve_balance",
3528 "SELECT"
3529 " expiration"
3530 ",balance_val"
3531 ",balance_frac"
3532 " FROM merchant_tip_reserves"
3533 " WHERE reserve_priv=$1",
3534 1),
3535 GNUNET_PQ_make_prepare ("find_tip_authorizations",
3536 "SELECT"
3537 " amount_val"
3538 ",amount_frac"
3539 ",justification"
3540 ",extra"
3541 ",tip_id"
3542 " FROM merchant_tips"
3543 " WHERE reserve_priv=$1",
3544 1),
3545 GNUNET_PQ_make_prepare ("update_tip_reserve_balance",
3546 "UPDATE merchant_tip_reserves SET"
3547 " expiration=$2"
3548 ",balance_val=$3"
3549 ",balance_frac=$4"
3550 " WHERE reserve_priv=$1",
3551 4),
3552 GNUNET_PQ_make_prepare ("insert_tip_reserve_balance",
3553 "INSERT INTO merchant_tip_reserves"
3554 "(reserve_priv"
3555 ",expiration"
3556 ",balance_val"
3557 ",balance_frac"
3558 ") VALUES "
3559 "($1, $2, $3, $4)",
3560 4),
3561 GNUNET_PQ_make_prepare ("insert_tip_justification",
3562 "INSERT INTO merchant_tips"
3563 "(reserve_priv"
3564 ",tip_id"
3565 ",exchange_url"
3566 ",justification"
3567 ",extra"
3568 ",timestamp"
3569 ",amount_val"
3570 ",amount_frac"
3571 ",left_val"
3572 ",left_frac"
3573 ") VALUES "
3574 "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)",
3575 10),
3576 GNUNET_PQ_make_prepare ("lookup_reserve_by_tip_id",
3577 "SELECT"
3578 " reserve_priv"
3579 ",left_val"
3580 ",left_frac"
3581 " FROM merchant_tips"
3582 " WHERE tip_id=$1",
3583 1),
3584 GNUNET_PQ_make_prepare ("lookup_amount_by_pickup",
3585 "SELECT"
3586 " amount_val"
3587 ",amount_frac"
3588 " FROM merchant_tip_pickups"
3589 " WHERE pickup_id=$1"
3590 " AND tip_id=$2",
3591 2),
3592 GNUNET_PQ_make_prepare ("find_tip_by_id",
3593 "SELECT"
3594 " exchange_url"
3595 ",extra"
3596 ",timestamp"
3597 ",amount_val"
3598 ",amount_frac"
3599 ",left_val"
3600 ",left_frac"
3601 " FROM merchant_tips"
3602 " WHERE tip_id=$1",
3603 1),
3604 GNUNET_PQ_make_prepare ("update_tip_balance",
3605 "UPDATE merchant_tips SET"
3606 " left_val=$2"
3607 ",left_frac=$3"
3608 " WHERE tip_id=$1",
3609 3),
3610 GNUNET_PQ_make_prepare ("insert_pickup_id",
3611 "INSERT INTO merchant_tip_pickups"
3612 "(tip_id"
3613 ",pickup_id"
3614 ",amount_val"
3615 ",amount_frac"
3616 ") VALUES "
3617 "($1, $2, $3, $4)",
3618 4),
3619 GNUNET_PQ_make_prepare ("insert_tip_credit_uuid",
3620 "INSERT INTO merchant_tip_reserve_credits"
3621 "(reserve_priv"
3622 ",credit_uuid"
3623 ",timestamp"
3624 ",amount_val"
3625 ",amount_frac"
3626 ") VALUES "
3627 "($1, $2, $3, $4, $5)",
3628 5),
3629 GNUNET_PQ_make_prepare ("lookup_tip_credit_uuid",
3630 "SELECT 1 "
3631 "FROM merchant_tip_reserve_credits "
3632 "WHERE credit_uuid=$1 AND reserve_priv=$2",
3633 2),
3634 GNUNET_PQ_PREPARED_STATEMENT_END
3635 };
3672 3636
3673 pg = GNUNET_new (struct PostgresClosure); 3637 pg = GNUNET_new (struct PostgresClosure);
3674 ec = getenv ("TALER_MERCHANTDB_POSTGRES_CONFIG"); 3638 ec = getenv ("TALER_MERCHANTDB_POSTGRES_CONFIG");
@@ -3694,7 +3658,9 @@ libtaler_plugin_merchantdb_postgres_init (void *cls)
3694 } 3658 }
3695 pg->cfg = cfg; 3659 pg->cfg = cfg;
3696 pg->conn = GNUNET_PQ_connect_with_cfg (cfg, 3660 pg->conn = GNUNET_PQ_connect_with_cfg (cfg,
3697 "merchantdb-postgres"); 3661 "merchantdb-postgres",
3662 es,
3663 ps);
3698 if (NULL == pg->conn) 3664 if (NULL == pg->conn)
3699 { 3665 {
3700 GNUNET_free (pg); 3666 GNUNET_free (pg);
@@ -3709,13 +3675,13 @@ libtaler_plugin_merchantdb_postgres_init (void *cls)
3709 GNUNET_log_config_missing (GNUNET_ERROR_TYPE_ERROR, 3675 GNUNET_log_config_missing (GNUNET_ERROR_TYPE_ERROR,
3710 "taler", 3676 "taler",
3711 "CURRENCY"); 3677 "CURRENCY");
3678 GNUNET_PQ_disconnect (pg->conn);
3712 GNUNET_free (pg); 3679 GNUNET_free (pg);
3713 return NULL; 3680 return NULL;
3714 } 3681 }
3715 plugin = GNUNET_new (struct TALER_MERCHANTDB_Plugin); 3682 plugin = GNUNET_new (struct TALER_MERCHANTDB_Plugin);
3716 plugin->cls = pg; 3683 plugin->cls = pg;
3717 plugin->drop_tables = &postgres_drop_tables; 3684 plugin->drop_tables = &postgres_drop_tables;
3718 plugin->initialize = &postgres_initialize;
3719 plugin->store_deposit = &postgres_store_deposit; 3685 plugin->store_deposit = &postgres_store_deposit;
3720 plugin->store_coin_to_transfer = &postgres_store_coin_to_transfer; 3686 plugin->store_coin_to_transfer = &postgres_store_coin_to_transfer;
3721 plugin->store_transfer_to_proof = &postgres_store_transfer_to_proof; 3687 plugin->store_transfer_to_proof = &postgres_store_transfer_to_proof;
@@ -3772,7 +3738,7 @@ libtaler_plugin_merchantdb_postgres_done (void *cls)
3772 struct TALER_MERCHANTDB_Plugin *plugin = cls; 3738 struct TALER_MERCHANTDB_Plugin *plugin = cls;
3773 struct PostgresClosure *pg = plugin->cls; 3739 struct PostgresClosure *pg = plugin->cls;
3774 3740
3775 PQfinish (pg->conn); 3741 GNUNET_PQ_disconnect (pg->conn);
3776 GNUNET_free (pg->currency); 3742 GNUNET_free (pg->currency);
3777 GNUNET_free (pg); 3743 GNUNET_free (pg);
3778 GNUNET_free (plugin); 3744 GNUNET_free (plugin);