/db-api

PostgreSQL database with API

Primary LanguagePLpgSQL

db-api

My PostgreSQL database. © 2015 50pop LLC | Contact: Derek Sivers

One central database, called d50b, with each of my different projects in its own schema.

For some explanation, see http://sivers.org/pg

The schemas:

  • core : tables and functions shared by other schemas: currencies and translations
  • peeps : people, emails, api keys. people table shared by all other schemas
  • sivers : sivers.org blog comments
  • lat : lateral thinking concepts
  • muckwork : muckwork.com
  • musicthoughts : musicthoughts.com
  • woodegg : book research data, shared at data.woodegg.com

In each schema directory:

  • api.sql : main public functions, each returning status & JSON
  • fixtures.sh : shell script to output fixtures.sql
  • fixtures.sql : dump of values for testing, to be loaded with each test
  • functions.sql : any other functions, not API
  • README.md : read me
  • schema.m4 : with each change to any .sql files, run m4 schema.m4 > schema.sql
  • schema.sql : don't edit! auto generated by schema.m4
  • tables.sql : create table
  • test-api.rb : tests for api.sql functions
  • test-db.rb : tests for non-API functions, like constraints, triggers, and functions
  • triggers.sql : triggers
  • views.sql : views used for complex JSON responses

What's this other stuff?

  • script/ directory of scripts. set shell $PATH to add this
  • lib/ directory of require includes. symlink into $: Ruby path.
  • defs.m4 m4 macros used by schema.m4 in each schema directory
  • test_tools.rb testing tools used by each schema directory

Changes?

For small changes, just use psql to add a function, drop a table, etc.

The easiest way to make major changes is to copy the schema.sql files to /tmp/, then edit it to remove the "DROP SCHEMA" and "CREATE TABLE" lines. All functions, triggers, and views can be replaced.

When adding a new schema, run this:

ALTER USER d50b SET SEARCH_PATH TO core, peeps, muckwork, lat, musicthoughts, sivers, woodegg;

See also:

For some experiments, see https://github.com/sivers/pg

For the websites that use these, see https://github.com/50pop/50web

TODO:

  • NOTIFY / LISTEN for outgoing emails
  • email parsing: set personID using email address first before in-reply-to
  • Where to translate ugly errors (probably using regexp matching) into simple i18n keys for the UI to show in user's language?

API:

Muckwork Client API:

  • TODO: create payment, add note(projectId, taskId)

Muckwork Worker API:

  • initialize with API keys: MuckworkWorker
  • get project(id)
  • get task(id)
  • claim task(id)
  • unclaim task(id)
  • start task(id)
  • finish task(id)
  • unclaimed tasks
  • TODO: get payments, add note(projectId, taskId)
  • TODO?: business rule against # of claimed tasks by worker?

Muckwork Manager API:

  • everything but init with API keys