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 |
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
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');
- "code" = HTTP status code
- "js" = JSON result
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
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.
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.