This is a novel implementation of the U-form abstract data type using PostgreSQL's JSONB Binary JSON field for attribute storage.
The inspiration for the U-fom goes back to Michael Dertouzos book which introduced the E-form. I did not invent the U-Form. I did work at MAYA Design where the U-Form was created.
My hope is to keep the idea alive in Open Source Software. While there are many similar models now such as Entity-Attribute-Value (EAV) or Datomic's 5-tuple, few of these existed when the U-Form was created.
See wikipedia for more information on the U-form.
The code provided here is based on PeeWee to establish the PostgreSQL connection, models, and attributes. This simplifies the code dramatically.
-
Clone the repository:
https://github.com/davidthewatson/uform.git
-
Change directories:
cd uform
-
Make a virtualenv:
mkvirtualenv uform
-
Install the python requirements:
pip install -r requirements.txt
-
Replace user variable with your postgres user in uform.py:
db = PostgresqlExtDatabase(name, user='dwatson')
-
Run the script:
python example.py
-
Examine the database:
SELECT * FROM cats; "id","uuid","attrs" 1,"012E201B-83BF-45E0-AD92-7EA9FC768464","{\"age\": 7, \"name\": \"Keiko\"}" 2,"3590BD6B-F2BB-406A-8E93-80EE17AFF13A","{\"age\": 13, \"name\": \"Goose\"}" 3,"85D3E0A7-7C8F-4B67-94E7-E0568A2D8ED3","{\"age\": 11, \"name\": \"Maxwell\"}"
-
Examine the attributes of a particular cat:
SELECT * FROM cats WHERE attrs->>'name' = 'Keiko'; "id","uuid","attrs" 1,"012E201B-83BF-45E0-AD92-7EA9FC768464","{\"age\": 7, \"name\": \"Keiko\"}"
More advanced queries are left as an exercise for the reader. Suffice it so say that the full power of PostgreSQL is available and that query results on attributes are extremely fast provided that you create GIN indexes.
It's trivial to create REST interfaces for these models using PostgREST. PostgREST even enables querying the JSONB attributes through HTTP query parameters.