sivers / Store
Licence: mit
PostgreSQL shopping cart
Stars: ✭ 213
Labels
Projects that are alternatives of or similar to Store
Pg rational
Precise fractional arithmetic for PostgreSQL
Stars: ✭ 168 (-21.13%)
Mutual labels: plpgsql
Chicago Atlas
View citywide information about health trends and take action near you to improve your own health.
Stars: ✭ 152 (-28.64%)
Mutual labels: plpgsql
Hierarchy Data Closure Table
This is a mysql and postgresql store procedure and trigger implementation of closure table in RDBMS about hierarchy data model.
Stars: ✭ 135 (-36.62%)
Mutual labels: plpgsql
Schemaverse
The Schemaverse is a space-based strategy game implemented entirely within a PostgreSQL database. Compete against other players using raw SQL commands to command your fleet. Or, if your PL/pgSQL-foo is strong, wield it to write AI and have your fleet command itself!
Stars: ✭ 207 (-2.82%)
Mutual labels: plpgsql
Docker Compose
一些基础服务的docker-compose配置文件，方便在一台新电脑上快速开始工作
Stars: ✭ 163 (-23.47%)
Mutual labels: plpgsql
Postgis coordinate transform
一个在postgis中结合中国国情，批量对数据进行加偏到百度坐标，高德谷歌的火星坐标，或者逆向纠偏
Stars: ✭ 182 (-14.55%)
Mutual labels: plpgsql
Wikipedia Mirror
🌐 Guide and tools to run a full offline mirror of Wikipedia.org with three different approaches: Nginx caching proxy, Kimix + ZIM dump, and MediaWiki/XOWA + XML dump
Stars: ✭ 160 (-24.88%)
Mutual labels: plpgsql
Colpivot
Dynamic row to column pivotation/transpose in Postgres made simple.
Stars: ✭ 173 (-18.78%)
Mutual labels: plpgsql
Adventureworks For Postgres
Set up the AdventureWorks sample database for use with Postgres
Stars: ✭ 148 (-30.52%)
Mutual labels: plpgsql
Amazon Redshift Udfs
A collection of example UDFs for Amazon Redshift.
Stars: ✭ 194 (-8.92%)
Mutual labels: plpgsql
Learn Sql The Hard Way
http://sql.learncodethehardway.org/book/
Stars: ✭ 168 (-21.13%)
Mutual labels: plpgsql
3dcitydb
3D City Database - The Open Source CityGML Database
Stars: ✭ 210 (-1.41%)
Mutual labels: plpgsql
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:
- "code" = HTTP status code
- "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.
Note that the project description data, including the texts, logos, images, and/or trademarks,
for each open source project belongs to its rightful owner.
If you wish to add or remove any projects, please contact us at [email protected].