boogerlad/mj - Change ZI2RJOZ2HXBHX7L54BTSDKESY6NG5KBJLVHCNY7K7ZLGIUOIHQUQC

cloudinary upload preset return, periods, schema cleanup, return options for create product, get rid of first_last_agg(still need to tweak another query)

Created by boogerlad on February 3, 2020

ZI2RJOZ2HXBHX7L54BTSDKESY6NG5KBJLVHCNY7K7ZLGIUOIHQUQC

Dependencies

In channels

main

Change contents

Replacement in dotenv at line 7 [6.45]
B:BD[5.9] → [5.9:14]
```
GAPI=
```
[5.9]
```
GAPI=
CLOUDINARY=
```
Insertion in initialize.sql at line 1 [7.17]
[7.17]
[7.18]
```
drop database lobomj;
```

Replacement in initialize.sql at line 4 [7.17]

B:BD[7.54] → [7.54:528]

CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS first_last_agg;
-- Create a function that always returns the first non-NULL item
CREATE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
        SELECT $1;
$$;
 
-- And then wrap an aggregate around it
CREATE AGGREGATE public.FIRST (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement

[7.54]

[7.528]

create extension if not exists citext;
create extension if not exists periods version '1.1' cascade;
create table store (
	ID serial primary key,
	name text not null,
	URL text not null unique,
	address text,
	address2 text,
	city text,
	region text not null,
	country text not null,
	postal_code text,
	coordinate point not null,
	timezone text
);
SELECT periods.add_system_time_period('store');
SELECT periods.add_system_versioning('store');
create table store_tax (
	ID serial primary key,
	store integer not null references store on delete cascade,
	name text not null,
	rate decimal(4,4) not null

Replacement in initialize.sql at line 27 [7.17]

B:BD[7.532] → [7.532:917]

 
-- Create a function that always returns the last non-NULL item
CREATE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
        SELECT $2;
$$;
 
-- And then wrap an aggregate around it
CREATE AGGREGATE public.LAST (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement

[7.532]

[7.917]

SELECT periods.add_system_time_period('store_tax');
SELECT periods.add_system_versioning('store_tax');
create table store_time (
	ID serial primary key,
	store integer not null references store on delete cascade,
	day integer constraint valid_day check (day > 0 and day < 8) not null,
	start time not null,
	end_ time not null,
	constraint end_greater_than_start check(end_ > start)
	--need an exclude constraint with day, start, and end_
	--is it better to use timestamptz so we can use range / period functions, and query extract just time?

Replacement in initialize.sql at line 39 [7.17]

B:BD[7.921] → [7.921:1134]

create type product_type as enum ('flower', 'preroll', 'oil', 'spray', 'capsule');
create type concentration_unit as enum ('mg', 'proportion');
create type measurement_unit as enum('ml', 'mg', 'g', 'capsule');

[7.921]

[5.15]

SELECT periods.add_system_time_period('store_time');
SELECT periods.add_system_versioning('store_time');

Replacement in initialize.sql at line 42 [7.17]
B:BD[5.102] → [5.102:139]
```
create table strain_family_effect (
```
[5.102]
[5.139]
```
create table strain_family (
```
Replacement in initialize.sql at line 47 [7.17]
B:BD[5.213] → [5.213:254]
```
insert into strain_family_effect values
```
[5.213]
[5.254]
```
insert into strain_family values
```

Replacement in initialize.sql at line 213 [7.17]

∅:D[5.5968] → [7.1192:1404]

B:BD[7.1192] → [7.1192:1404]

B:BD[8.56] → [8.56:85]

B:BD[8.85] → [9.0:16]

∅:D[9.16] → [7.1459:1463]

∅:D[8.448] → [7.1459:1463]

B:BD[7.1459] → [7.1459:1463]

B:BD[7.1463] → [9.17:43]

∅:D[9.43] → [8.475:583]

B:BD[8.475] → [8.475:583]

B:BD[8.583] → [2.0:44]

∅:D[2.44] → [8.611:615]

B:BD[8.611] → [8.611:615]

B:BD[8.615] → [2.45:90]

∅:D[2.90] → [9.72:249]

B:BD[9.72] → [9.72:249]

B:BD[9.249] → [2.91:177]

∅:D[2.177] → [9.303:307]

B:BD[9.303] → [9.303:307]

∅:D[9.307] → [7.1463:1670]

∅:D[8.615] → [7.1463:1670]

B:BD[7.1463] → [7.1463:1670]

create table store (
	ID serial primary key,
	name text not null,
	URL text not null unique,
	address text,
	address2 text,
	city text,
	region text not null,
	country text not null,
	postal_code text,
	coordinate point not null,
	timezone text
);
create table store_tax (
	ID serial primary key,
	store integer not null references store on delete cascade,
	name text not null,
	tax decimal(4,4) not null--should be rate
);
create table store_hours (--should be times
	ID serial primary key,
	store integer not null references store on delete cascade,
	day integer constraint valid_day check (day > 0 and day < 8),--does this imply not null?
	open_time time not null,--should be start
	close_time time not null--should be end
);
--store_with_history view
--store_history table via trigger
--https://stackoverflow.com/questions/4464898/best-way-to-store-working-hours-and-query-it-efficiently
--do we care about store hours history?

[5.5968]

[7.1670]

create type product_type as enum ('flower', 'preroll', 'oil', 'spray', 'capsule');
create type strain as enum('indica', 'sativa', 'hybrid');
create type concentration_unit as enum ('mg', 'proportion');
create type measurement_unit as enum('ml', 'mg', 'g', 'capsule');

Replacement in initialize.sql at line 222 [7.17]

B:BD[7.1808] → [7.1808:1904]

	concentration_unit concentration_unit not null,
	measurement_unit measurement_unit not null,

[7.1808]

[7.1904]

	producer text,

Deletion in initialize.sql at line 224 [7.17]
B:BD[7.1935] → [5.6033:6050]
```
	producer text,
```

Replacement in initialize.sql at line 225 [7.17]

B:BD[7.1990] → [5.6051:6150]

	strain text not null,
	strain_family integer references strain_family_effect on delete cascade,

[7.1990]

[5.6150]

	strain strain not null,
	strain_family integer references strain_family on delete cascade,

Replacement in initialize.sql at line 228 [7.17]

∅:D[5.6179] → [7.2059:2540]

B:BD[7.2059] → [7.2059:2540]

	show boolean default false,
	created_at timestamptz not null default now(),--useful for "new arrivals". do we need this at store level too? todo: terpene text array, image_paths text array
	daily boolean default false not null,
	weekly boolean default false not null,
	monthly boolean default false not null,
	rarely boolean default false not null,
	happy boolean default false not null,
	relaxed boolean default false not null,
	balanced boolean default false not null,

[5.6179]

[7.2540]

	concentration_unit concentration_unit not null,
	measurement_unit measurement_unit not null,
	show boolean default false,--todo: terpene text array, image_paths text array

Replacement in initialize.sql at line 233 [7.17]

B:BD[7.2612] → [7.2612:2678]

--product_with_history view
--product_history table via trigger

[7.2612]

[7.2678]

SELECT periods.add_system_time_period('product');
SELECT periods.add_system_versioning('product');

Replacement in initialize.sql at line 242 [7.17]

B:BD[7.2925] → [7.2925:2991]

--variant_with_history view
--variant_history table via trigger

[7.2925]

[7.2991]

SELECT periods.add_system_time_period('variant');
SELECT periods.add_system_versioning('variant');

Replacement in initialize.sql at line 246 [7.17]

B:BD[7.3042] → [7.3042:3258]

	variant integer not null references variant on delete cascade,--perhaps restrict makes more sense. in other areas too... but with temporal_table is it safe to cascade since it'll still be present in history table?

[7.3042]

[7.3258]

	variant integer not null references variant on delete cascade,

Replacement in initialize.sql at line 254 [7.17]

B:BD[7.3514] → [7.3514:3557]

	featured boolean default false not null,

[7.3514]

[7.3557]

	featured boolean default false not null,--rank someday: https://stackoverflow.com/a/49956113/1201238
	use_for_compute boolean default true not null,--online stores have stupid ass numbers so initially, use for compute, but once we have stores turn it off.

Replacement in initialize.sql at line 258 [7.17]

B:BD[7.3614] → [7.3614:3684]

--menu_item_with_history view
--menu_item_history table via trigger

[7.3614]

[7.3684]

SELECT periods.add_system_time_period('menu_item');
SELECT periods.add_system_versioning('menu_item');

Replacement in initialize.sql at line 265 [7.17]

B:BD[7.3948] → [7.3948:4186]

create type "CYP2C19*2" as enum ('inconclusive', '*1/*1', '*1/*2', '*2/*2');
create type "CYP2C19*3" as enum ('inconclusive', '*1/*1', '*1/*3', '*3/*3');
create type "CYP2C19*17" as enum ('inconclusive', '*1/*1', '*1/*17', '*17/*17');

[7.3948]

[7.4186]

create type CYP2C19_2 as enum ('inconclusive', '*1/*1', '*1/*2', '*2/*2');
create type CYP2C19_3 as enum ('inconclusive', '*1/*1', '*1/*3', '*3/*3');
create type CYP2C19_17 as enum ('inconclusive', '*1/*1', '*1/*17', '*17/*17');

Replacement in initialize.sql at line 293 [7.17]

B:BD[7.4723] → [7.4723:4805]

	"CYP2C19*2" "CYP2C19*2",
	"CYP2C19*3" "CYP2C19*3",
	"CYP2C19*17" "CYP2C19*17"

[7.4723]

[7.4805]

	CYP2C19_2 CYP2C19_2,
	CYP2C19_3 CYP2C19_3,
	CYP2C19_17 CYP2C19_17

Replacement in initialize.sql at line 297 [7.17]

B:BD[7.4913] → [7.4913:5040]

--todo: store reviews, product reviews, notifications, watched products, mixology, bidding for product placement on home page

[7.4913]

[7.5040]

SELECT periods.add_system_time_period('user_account', excluded_column_names => ARRAY['token_hash', 'token_expiry']);
SELECT periods.add_system_versioning('user_account');
create type status as enum ('placed', 'ready', 'paid', 'user_cancelled', 'store_cancelled', 'no_show');--'created' is null

Replacement in initialize.sql at line 302 [7.17]

B:BD[7.5086] → [7.5086:5486]

B:BD[7.5486] → [8.616:708]

∅:D[8.708] → [7.5571:5740]

B:BD[7.5571] → [7.5571:5740]

	user_account integer references user_account on delete cascade, --nullable, for no user
	store integer not null, --references store on delete cascade, since temporal_table relevation lol
	created_at timestamptz not null default now(),
	abandoned_at timestamptz,--when cart is cleared or they start adding stuff from other store
	placed_at timestamptz,--before this, they are editing their cart
	want_at timestamptz, --user could want it later ie not immidiately. null means now. kill?
	ready_at timestamptz,
	payed_at timestamptz,
	delivered_at timestamptz,
	user_cancelled_at timestamptz,
	store_cancelled_at timestamptz,
	no_show_at timestamptz,

[7.5086]

[7.5740]

	user_account integer not null references user_account on delete cascade,
	store integer not null references store on delete cascade,
	status status,

Replacement in initialize.sql at line 306 [7.17]

B:BD[7.5753] → [4.0:33]

∅:D[4.33] → [7.5785:5826]

B:BD[7.5785] → [7.5785:5826]

B:BD[7.5826] → [4.34:229]

∅:D[4.229] → [7.5826:5860]

B:BD[7.5826] → [7.5826:5860]

	--todo: discounts and loyalty,
);--compute total in application layer?
create unique index unique_cart_3 on cart (user_account, store, placed_at) where placed_at is not null;
create unique index unique_cart_2 on cart (user_account, store) where placed_at is null;
--history table enough for cart?

[7.5753]

[7.5860]

);
create unique index unique_cart on cart (user_account, store) where status is null;
SELECT periods.add_system_time_period('cart');
SELECT periods.add_system_versioning('cart');

Replacement in initialize.sql at line 313 [7.17]

B:BD[7.5970] → [7.5970:6167]

	product_with_history integer not null,--can I fk this? https://stackoverflow.com/a/10183157/1201238
	variant_with_history integer not null,--ditto
	menu_item_with_history integer not null,--yo

[7.5970]

[4.230]

	menu_item integer not null references menu_item on delete cascade,

Replacement in initialize.sql at line 315 [7.17]

B:BD[4.260] → [4.260:371]

	constraint unique_line_item unique(cart, product_with_history, variant_with_history, menu_item_with_history)

[4.260]

[7.6196]

	constraint unique_line_item unique(cart, menu_item)

Insertion in initialize.sql at line 317 [7.17]

[7.6200]

SELECT periods.add_system_time_period('line_item');
SELECT periods.add_system_versioning('line_item');
--todo: store reviews, product reviews, notifications, watched products, mixology, bidding for product placement on home page

Replacement in initialize.sql at line 324 [7.17]

B:BD[7.6477] → [7.6477:6645]

--);
--maybe line_item and cart no fk link to with_history
--can only fk if no deletes ie restrict
--everything else on delete cascade, since temporal_table solves

[7.6477]

--);

Replacement in server.js at line 344 [10.590]

B:BD[2.1014] → [2.1014:1072]

										let query = 'insert into store_hours values ';

[2.1014]

[2.1072]

										let query = 'insert into store_time values ';

Replacement in server.js at line 438 [10.590]
B:BD[5.8457] → [5.8457:8485]
```
			case 'strain_families':
```
[5.8457]
[5.8485]
```
			case 'product_form':
```

Insertion in server.js at line 441 [10.590]

[5.8537]

						const [strain_families, strains, product_types, concentration_units, measurement_units] = await Promise.all([
							pool.query('select id, name from strain_family').then(x => x.rows),
							pool.query('select array_to_json(enum_range(null::strain)) as wow').then(x => x.rows[0].wow),
							pool.query('select array_to_json(enum_range(null::product_type)) as wow').then(x => x.rows[0].wow),
							pool.query('select array_to_json(enum_range(null::concentration_unit)) as wow').then(x => x.rows[0].wow),
							pool.query('select array_to_json(enum_range(null::measurement_unit)) as wow').then(x => x.rows[0].wow),
						]);

Replacement in server.js at line 450 [10.590]

B:BD[5.8602] → [5.8602:8685]

							data: (await pool.query('select id, name from strain_family_effect')).rows

[5.8602]

[5.8685]

							data: {strain_families, strains, product_types, concentration_units, measurement_units, cloudinary: process.env.CLOUDINARY}

Replacement in server.js at line 470 [10.590]

B:BD[5.9129] → [11.0:146]

							let product = (await pool.query('insert into product values (default, $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning id', [

[5.9129]

[5.9274]

							let product = (await pool.query('insert into product values (default, $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, true) returning id', [

Deletion in server.js at line 472 [10.590]

B:BD[5.9289] → [5.9289:9394]

								parameters.concentration_unit,
								parameters.measurement_unit,
								parameters.brand,

Insertion in server.js at line 473 [10.590]
[5.9424]
[5.9424]
```
								parameters.brand,
```

Replacement in server.js at line 478 [10.590]

B:BD[5.9600] → [5.9600:9614]

								true

[5.9600]

[11.147]

								parameters.concentration_unit,
								parameters.measurement_unit

Replacement in server.js at line 525 [10.590]

B:BD[5.10275] → [5.10275:10306]

B:BD[5.10306] → [3.0:699]

					response_ID: request_ID,
					data: (await pool.query(` select first(product.id) as id, first(type) as type, first(display_brand) as brand, first(producer) as producer, first(display_name) as name, first(strain) as strain, first(strain_family_effect.name) as strain_family, COALESCE(json_agg(json_build_object('id', variant.id, 'portions', portions, 'quantity', quantity)) filter (where quantity is not null)) as variants, first(concentration_unit) as concentration_unit, first(measurement_unit) as measurement_unit from product left outer join variant on (product.id = variant.product) left outer join strain_family_effect on (product.strain_family = strain_family_effect.id) where show = true group by product.id`)).rows

[5.10275]

[5.10581]

					response_ID: request_ID,//https://dba.stackexchange.com/a/159899
					data: (await pool.query(`
						select distinct on(product.id)
							product.id as id,
							type,
							display_brand as brand,
							producer,
							display_name as name,
							strain,
							strain_family_effect.name as strain_family,
							COALESCE(json_agg(json_build_object('id', variant.id, 'portions', portions, 'quantity', quantity)) filter (where quantity is not null)) as variants,
							concentration_unit,
							measurement_unit
						from
							product
							left outer join strain_family_effect on (product.strain_family = strain_family_effect.id)
							left outer join variant on (product.id = variant.product)
						where
							show = true
						group by
							product.id,
							strain_family_effect.id
					`)).rows//I guess it's necessary to group by strain_family_effect.id as well because there's no way for postgres to know that there'll only be one row in the result per strain_family_effect. ask dba