Skip to content
PostgreSQL shopping cart
PLpgSQL TSQL Ruby
Branch: master
Clone or download
Latest commit be92ee3 Oct 25, 2019
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
peeps - Oct 25, 2019
store getdb + merge maker Oct 25, 2019
LICENSE Initial commit Oct 25, 2019
README.md Oct 25, 2019
getdb-example.rb getdb + merge maker Oct 25, 2019
getdb.rb getdb + merge maker Oct 25, 2019
test_tools.rb init Oct 25, 2019

README.md

PostgreSQL shopping cart

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

Install

createuser -s dude
createdb -U dude -E UTF8 dude_test
gem install pg
gem install json
cd store
ruby test-db.rb
ruby test-api.rb

Play

ruby getdb-example.rb
psql -U dude dude_test
pg» set search_path = store,peeps;
pg» select * from invoices_get();
pg» select * from invoice_shipped(4, 'posted');

Every API function returns:

  1. "code" = HTTP status code
  2. "js" = JSON result

Directory structure in store/

Each function is its own file inside the subdirectories. Then make.rb merges them into schema.sql.

  • tables.sql = tables and indexes
  • api/ = public API functions (only use these)
  • functions/ = private functions used by API
  • triggers/ = triggers for data logic
  • views/ = re-usable views for JSON
  • fixtures.sql = sample data I use for testing
  • make.rb = every time you change a function in api/functions/triggers/views, re-run make.rb to re-generate schema.sql
  • schema.sql = generated by make.rb : don't alter
  • test-api.rb = unit tests of API calls
  • test-db.rb = unit tests of private functions and triggers

Schema

I put each project into its own schema (in this case "store") that references a central schema called "peeps" of all the people I know.

That's why peeps.people and peeps.countries are in a separate directory. My live version has way more to it than this, but I saved a subset of it here to show an example.

Nice thing about having a separate schema per-project, too, is for unit tests, just drop and re-build the schema inbetween every test.

Questions?

Email me at https://sivers.org/contact

Sorry I won't be watching pull-requests and such, here. I'm posting this just as some example code.

You can’t perform that action at this time.