Closed brianfoshee closed 9 years ago
@letsgitgrowing are we safe to drop everything on the Heroku database? I'm going to test this out.
@letsgitgrowing I was invited to beta test a feature on Heroku which lets us setup automatic deploys to Heroku when code is pushed to the master branch. I set it up on this app and repository to test it out.
https://devcenter.heroku.com/articles/github-sync
Also, the database is full on Heroku so we'll have to delete all the data and set up the new schema so that we can start using it again.
@letsgitgrowing I had some fun with Postgres last night at the event I went to with Kirsten (it was a design-related talk so I got away with not paying attention). I got the database schema squared away and figured out the function that'll be required to allow us to UPDATE
a JSON field (and it supports nested JSON on Postgres 9.3 already).
See the original post at the top of this pull request for the raw SQL schema, function, and examples of adding a device then adding individual readings. These are all updated in the database migrations in the goose branch (this PR).
This is what the database looks like afterwards:
aquaponics-data-dev=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+----------+-------
public | device | table | brian
public | goose_db_version | table | brian
public | goose_db_version_id_seq | sequence | brian
public | reading | table | brian
(4 rows)
aquaponics-data-dev=# \d device
Table "public.device"
Column | Type | Modifiers
------------+-----------------------------+-----------------------------------------------
id | uuid | not null default uuid_generate_v4()
identifier | character varying | not null
updated_at | timestamp without time zone | not null default timezone('UTC'::text, now())
created_at | timestamp without time zone | not null default timezone('UTC'::text, now())
Indexes:
"device_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "reading" CONSTRAINT "reading_device_id_fkey" FOREIGN KEY (device_id) REFERENCES device(id) ON DELETE CASCADE
aquaponics-data-dev=# \d reading
Table "public.reading"
Column | Type | Modifiers
-----------+------+-----------------------------
device_id | uuid | not null
readings | json | not null default '{}'::json
Indexes:
"reading_device_id" btree (device_id)
Foreign-key constraints:
"reading_device_id_fkey" FOREIGN KEY (device_id) REFERENCES device(id) ON DELETE CASCADE
aquaponics-data-dev=# select * from device;
id | identifier | updated_at | created_at
--------------------------------------+------------+----------------------------+----------------------------
6c7ae8aa-3853-4c27-a1cc-3d991b56ff29 | ABC123 | 2014-12-04 21:16:05.593885 | 2014-12-04 21:16:05.593885
(1 row)
Time: 0.395 ms
aquaponics-data-dev=# select * from reading;
device_id | readings
--------------------------------------+-----------------------------------------------------------------------------------------
6c7ae8aa-3853-4c27-a1cc-3d991b56ff29 | {"2014-11-05T21:00:00Z":{"ph":4, "tds":121},"2014-12-01T23:00:00Z":{"ph":7, "tds":101}}
(1 row)
Time: 0.316 ms
Required changes to the schema for converting to jsonb
:
-- We have to remove the default value of '{}'::json because it's incompatible with jsonb
ALTER TABLE reading ALTER COLUMN readings DROP DEFAULT;
-- Change the column type, and cast existing data to jsonb
ALTER TABLE reading ALTER COLUMN readings TYPE jsonb USING readings::jsonb;
-- Add the default value back, of course using jsonb this time
ALTER TABLE reading ALTER COLUMN readings SET DEFAULT '{}'::jsonb;
I'll make a migration for this once we have the refactoring-goose branch finished up. So far I'm seeing query times cut in half for selecting on a particular json key. It'll be interesting to see what an index will do when we're dealing with a ton of data.
Also, need to modify the json_object_set_key
function types.
Working on parts of #9 Closes #9 #20 #19 #18 #16
uuid-ossp
extensiondevice
tablereading
table