All functionality is in PostgreSQL’s PL/pgSQL functions.
| SELECT * FROM … | result |
|---|---|
| items_get() | show all items |
| cart_get(person_id) | get cart (unpaid invoice) |
| lineitem_add(person_id, item_id, quantity) | add item to cart |
| lineitem_delete(lineitems.id) | delete lineitem in cart |
| lineitem_update(lineitems.id, quantity) | change quantity (0=delete) |
| invoice_get(invoices.id) | get order |
| invoice_update(invoices.id, country) | update country |
| invoice_update(invoices.id, country, address) | update address |
| invoice_delete(invoices.id) | delete order |
| invoice_paid(invoices.id, payment info) | mark order as paid |
| invoices_get() | show all orders |
| invoices_get_unshipped() | orders needing to be shipped |
| invoice_shipped(invoice_id, info) | mark order as shipped |
| invoices_get_for(person_id) | this person’s orders |
| items_get_for(person_id) | items this person has paid for |
gem install pg
gem install json
sh init.sh
sh reset.sh
ruby test-db.rb
ruby test-api.rb
- “ok” = boolean success/fail
- “js” = JSON result (if !ok then {error: "explanation"})
$ irb
irb» require './getdb.rb'
=> true
irb» db = getdb('store')
irb» ok, res = db.call('invoice_get', 4)
irb» res
{:id=>4,
:person_id=>7,
:name=>"巩俐",
:order_date=>"2019-10-02",
:payment_date=>nil,
:payment_info=>nil,
:subtotal=>2,
:shipping=>0,
:total=>2,
:country=>"CN",
:address=>nil,
:ship_date=>nil,
:ship_info=>nil,
:lineitems=>[{:id=>4, :item_id=>4, :name=>"JPG of Mr. Wonka", :quantity=>1, :price=>2}]}
irb» ok, res = db.call('invoice_paid', 4, 'cash')
irb» puts ok ? 'paid' : res[:error]
paid
irb» ok, res = db.call('invoices_get')
irb» res.map {|i| "%d = %s" % [i[:id], i[:name]]}
=> ["1 = Charlie Buckets", "2 = Veruca Salt", "3 = Augustus Gloop", "4 = 巩俐"]
irb» exit
$ psql -U dude dude
pg» select * from store.invoices_get();
ok │ t
js │ [{"id":1,"person_id":4,"name":"Charlie Buckets","order_date":"2019-10-02","payment_date":"2019-10-02","payment_info":"PayPal #abc123","subtotal":15,"shipping":6,"total":21,"country":"US","address":"Charlie Buckets\n3 Skid Row\nHershey, PA 04141","ship_date":"2019-10-03","ship_info":"usps# a1b2","lineitems":[{"id":1,"item_id":3,"name":"Fizzy Lifting Drink","quantity":3,"price":15}]},
…
pg» select * from store.invoice_shipped(4, 'posted');
ok │ t
js │ {"id":4,"person_id":7,"order_date":"2019-10-02","payment_date":"2023-03-12","payment_info":"cash","subtotal":2,"shipping":0,"total":2,"country":"CN","address":null,"ship_date":"2023-03-12","ship_info":"posted"}
- api/ = public API functions (only use these)
- functions/ = private functions used by API
- getdb.rb = helper to call API
- tables.sql = tables and indexes
- test-api.rb = unit tests of API calls
- test-db.rb = unit tests of private functions
- test_data.sql = sample data for testing
- tester.rb = helper for unit tests
- triggers/ = triggers for data logic
- views/ = re-usable views for JSON
All functions, views, and triggers are in the "store" schema.
So any time you make a change, you can run … "drop schema store cascade; create schema store" … then re-load them all anytime. It won't lose your data.
Email me at https://sive.rs/contact
Sorry I won’t be watching pull-requests or issues here. I’m posting this just as some example code.