boogerlad/mj - Change 5XHBBUBSPTNNPEZZVELCS5AEAKQVHEMLAI2URKBMMBUGWTPARFVQC

lower case unquoted identifiers to reduce confusion(postgres doesn't differentiate) store online / physical explicit prefix all id columns with TABLENAME_ to make foreign keys in queries more intuitive for new devs and `using` keyword fix a bug where show is nullable(three valued logic is not applicable here!) portions default to 1 instead of null(no such thing as "unknown" number of portions) rename user_account to usr finally figure out a name ('active') for the initial state of a cart instead of null todo: update all the queries... In doing so, might as well move all queries out of js. regarding the highly debated `insert on conflict do nothing returning id`, fuck it. why is doing it in one statement so important? > You could check the row count of the output and repeat the statement if it does not match the row count of the input. May be good enough for the rare case. The point is to start a new query (can be in the same transaction), which will then see the newly committed rows. Or check for missing result rows within the same query and overwrite those with the brute force trick demonstrated in Alextoni's answer. can't emphasize, but instead of doing what "Or" suggests, just do another query. it's not like I've implemented the necessary locks to benefit from a single query anyways. I probably wouldn't want to, since scraping is a long operation and I don't want to ever force update (which will hit triggers). I guess the one benefit of one query method is can be pure sql? but now that I'm embracing plpgsql that's a moot point https://stackoverflow.com/a/42217872 https://dba.stackexchange.com/q/212580/

Created by boogerlad on March 16, 2020

5XHBBUBSPTNNPEZZVELCS5AEAKQVHEMLAI2URKBMMBUGWTPARFVQC

Dependencies

In channels

main

Change contents

Insertion in initialize.sql at line 48 [8.17]
[3.480]
[7.146]
```
create type store_type as enum ('online', 'physical');
```
Replacement in initialize.sql at line 50 [8.17]
B:BD[7.168] → [7.168:193]
```
	ID serial primary key,
```
[7.168]
[9.4526]
```
	store_id serial primary key,
```
Replacement in initialize.sql at line 53 [8.17]
B:BD[7.215] → [10.0:12]
```
	URL text,
```
[7.215]
[10.12]
```
	url text,
```

Replacement in initialize.sql at line 63 [8.17]

B:BD[4.72] → [4.72:101]

	phone packed_phone_number,

[4.72]

[4.101]

	phone packed_phone_number,--https://dba.stackexchange.com/a/164797

Insertion in initialize.sql at line 65 [8.17]
[4.171]
[10.158]
```
	type store_type not null default 'physical',
```

Replacement in initialize.sql at line 67 [8.17]

B:BD[10.198] → [2.0:150]

∅:D[2.150] → [10.328:454]

B:BD[10.328] → [10.328:454]

		(URL is not null and address is null and address2 is null and city is null and postal_code is null and coordinate is null and timezone is null) or
		(address is not null and city is not null and postal_code is not null and coordinate is not null and timezone is not null)

[10.198]

[10.454]

		(type = 'online' and url is not null and address is null and address2 is null and city is null and postal_code is null and coordinate is null and timezone is null) or
		(type = 'physical' and address is not null and city is not null and postal_code is not null and coordinate is not null and timezone is not null)

Replacement in initialize.sql at line 75 [8.17]

B:BD[7.530] → [7.530:616]

	ID serial primary key,
	store integer not null references store on delete cascade,

[7.530]

[7.616]

	store_tax_id serial primary key,
	store_id integer not null references store on delete cascade,

Replacement in initialize.sql at line 79 [8.17]

B:BD[9.5370] → [9.5370:5420]

	constraint unique_store_tax unique(store, name)

[9.5370]

[8.528]

	constraint unique_store_tax unique(store_id, name)

Replacement in initialize.sql at line 82 [8.17]

B:BD[7.800] → [7.800:886]

	ID serial primary key,
	store integer not null references store on delete cascade,

[7.800]

[9.5421]

	store_time_id serial primary key,
	store_id integer not null references store on delete cascade,

Replacement in initialize.sql at line 85 [8.17]

B:BD[9.5699] → [11.54:135]

	exclude using gist (store with =, open with &&) where (isempty(open) is false)

[9.5699]

[8.917]

	exclude using gist (store_id with =, open with &&) where (not isempty(open))

Replacement in initialize.sql at line 88 [8.17]

B:BD[9.5778] → [9.5778:5885]

	ID serial primary key,
	store integer not null references store on delete cascade,
	URL text not null,

[9.5778]

[9.5885]

	store_image_id serial primary key,
	store_id integer not null references store on delete cascade,
	url text not null,

Replacement in initialize.sql at line 93 [8.17]

B:BD[9.6021] → [9.6021:6072]

	constraint unique_store_image unique(store, URL)

[9.6021]

[9.6072]

	constraint unique_store_image unique(store_id, url)

Replacement in initialize.sql at line 97 [8.17]
∅:D[7.1361] → [12.139:164]
B:BD[12.139] → [12.139:164]
```
	ID serial primary key,
```
[7.1361]
[9.6077]
```
	strain_family_id serial primary key,
```
Replacement in initialize.sql at line 105 [8.17]
B:BD[8.1694] → [8.1694:1719]
```
	ID serial primary key,
```
[8.1694]
[5.158]
```
	product_id serial primary key,
```

Replacement in initialize.sql at line 112 [8.17]

B:BD[7.1714] → [9.6484:6553]

	strain_family integer references strain_family on delete set null,

[7.1714]

[13.214]

	strain_family_id integer references strain_family on delete set null,

Replacement in initialize.sql at line 114 [8.17]
B:BD[7.1879] → [9.6554:6584]
```
	show boolean default false,
```
[13.234]
[4.172]
```
	show boolean default false not null,
```

Replacement in initialize.sql at line 125 [8.17]

B:BD[9.7107] → [9.7107:7197]

	ID serial primary key,
	product integer not null references product on delete cascade,

[9.7107]

[9.7197]

	product_image_id serial primary key,
	product_id integer not null references product on delete cascade,

Replacement in initialize.sql at line 131 [8.17]

B:BD[9.7441] → [9.7441:7504]

	constraint unique_product_image unique(product, destination)

[9.7441]

[9.7504]

	constraint unique_product_image unique(product_id, destination)

Replacement in initialize.sql at line 134 [8.17]

B:BD[8.2702] → [8.2702:2792]

B:BD[8.2792] → [13.575:617]

	ID serial primary key,
	product integer not null references product on delete cascade,
	portions smallint check (portions > 1),

[8.2702]

[13.617]

	variant_id serial primary key,
	product_id integer not null references product on delete cascade,
	portions smallint not null default 1 check (portions > 0),

Replacement in initialize.sql at line 141 [8.17]

B:BD[8.2925] → [13.685:1201]

create unique index unique_variant_2 on variant (product, quantity) where portions is null and flavor is null;
create unique index unique_variant_3_portions on variant (product, portions, quantity) where portions is not null and flavor is null;
create unique index unique_variant_3_flavor on variant (product, quantity, flavor) where portions is null and flavor is not null;
create unique index unique_variant_4 on variant (product, portions, quantity, flavor) where portions is not null and flavor is not null;

[8.2925]

[9.7768]

create unique index unique_variant_2 on variant (product_id, quantity) where portions is null and flavor is null;
create unique index unique_variant_3_portions on variant (product_id, portions, quantity) where portions is not null and flavor is null;
create unique index unique_variant_3_flavor on variant (product_id, quantity, flavor) where portions is null and flavor is not null;
create unique index unique_variant_4 on variant (product_id, portions, quantity, flavor) where portions is not null and flavor is not null;

Replacement in initialize.sql at line 146 [8.17]

B:BD[9.7803] → [9.7803:7893]

	ID serial primary key,
	variant integer not null references variant on delete cascade,

[9.7803]

[9.7893]

	variant_identifier_id serial primary key,
	variant_id integer not null references variant on delete cascade,

Replacement in initialize.sql at line 149 [8.17]

B:BD[9.7915] → [9.7915:7976]

	constraint unique_variant_identifier unique(variant, gtin)

[9.7915]

[9.7976]

	constraint unique_variant_identifier unique(variant_id, gtin)

Replacement in initialize.sql at line 152 [8.17]

B:BD[8.3017] → [8.3017:3042]

B:BD[8.3042] → [7.2164:2229]

∅:D[7.2229] → [8.3258:3319]

B:BD[8.3258] → [8.3258:3319]

	ID serial primary key,
	variant integer not null references variant on delete cascade,
	store integer not null references store on delete cascade,

[8.3017]

[14.175]

	menu_item_id serial primary key,
	variant_id integer not null references variant on delete cascade,
	store_id integer not null references store on delete cascade,

Replacement in initialize.sql at line 161 [8.17]

B:BD[9.8302] → [15.51:105]

	constraint unique_menu_item unique(variant, store),

[9.8302]

[15.105]

	constraint unique_menu_item unique(variant_id, store_id),

Replacement in initialize.sql at line 166 [8.17]

B:BD[8.3691] → [8.3691:3906]

create type COMT as enum ('inconclusive', 'Val/Val', 'Val/Met', 'Met/Met');
create type AKT1 as enum ('inconclusive', 'T/T', 'C/T', 'C/C');
create type CYP2C9 as enum ('inconclusive', '*1/*1', '*1/*3', '*3/*3');

[8.3691]

[8.3906]

create type comt as enum ('inconclusive', 'Val/Val', 'Val/Met', 'Met/Met');
create type akt1 as enum ('inconclusive', 'T/T', 'C/T', 'C/C');
create type cyp2c9 as enum ('inconclusive', '*1/*1', '*1/*3', '*3/*3');

Replacement in initialize.sql at line 170 [8.17]

B:BD[8.3948] → [7.2596:2828]

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');

[8.3948]

[8.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 174 [8.17]

B:BD[8.4265] → [8.4265:4319]

create table user_account (
	ID serial primary key,

[8.4265]

[8.4319]

create table usr (--only because user is a reserved keyword
	user_id serial primary key,

Replacement in initialize.sql at line 195 [8.17]

∅:D[16.248] → [8.4680:4723]

B:BD[8.4680] → [8.4680:4723]

B:BD[8.4723] → [7.2829:2899]

	COMT COMT,
	AKT1 AKT1,
	CYP2C9 CYP2C9,
	CYP2C19_2 CYP2C19_2,
	CYP2C19_3 CYP2C19_3,
	CYP2C19_17 CYP2C19_17

[16.248]

[8.4805]

	comt comt,
	akt1 akt1,
	cyp2c9 cyp2c9,
	cyp2c19_2 cyp2c19_2,
	cyp2c19_3 cyp2c19_3,
	cyp2c19_17 cyp2c19_17

Replacement in initialize.sql at line 202 [8.17]

B:BD[7.3073] → [7.3073:3197]

create type status as enum ('placed', 'ready', 'paid', 'user_cancelled', 'store_cancelled', 'no_show');--'created' is null

[8.4913]

[8.5040]

create type status as enum ('active', 'placed', 'ready', 'paid', 'user_cancelled', 'store_cancelled', 'no_show');

Replacement in initialize.sql at line 204 [8.17]

B:BD[8.5061] → [8.5061:5086]

B:BD[8.5086] → [17.0:66]

∅:D[17.66] → [7.3273:3351]

B:BD[7.3273] → [7.3273:3351]

	ID serial primary key,
	user_account integer references user_account on delete cascade,
	store integer not null references store on delete cascade,
	status status,

[8.5061]

[8.5740]

	cart_id serial primary key,
	user_id integer references usr on delete cascade,
	store_id integer not null references store on delete cascade,
	status status not null default 'active',

Replacement in initialize.sql at line 210 [8.17]

B:BD[7.3356] → [9.8335:8461]

create unique index unique_cart on cart (user_account, store) where status is null or status = 'placed' or status = 'ready';

[7.3356]

[8.5860]

create unique index unique_cart on cart (user_id, store_id) where status = 'active' or status = 'placed' or status = 'ready';

Replacement in initialize.sql at line 212 [8.17]

B:BD[8.5886] → [8.5886:5970]

B:BD[8.5970] → [7.3537:3606]

	ID serial primary key,
	cart integer not null references cart on delete cascade,
	menu_item integer not null references menu_item on delete cascade,

[8.5886]

[18.501]

	line_item_id serial primary key,
	cart_id integer not null references cart on delete cascade,
	menu_item_id integer not null references menu_item on delete cascade,

Replacement in initialize.sql at line 216 [8.17]
∅:D[18.552] → [7.3607:3661]
B:BD[19.260] → [7.3607:3661]
```
	constraint unique_line_item unique(cart, menu_item)
```
[18.552]
[8.6196]
```
	constraint unique_line_item unique(cart_id, menu_item_id)
```

Replacement in initialize.sql at line 240 [8.17]

B:BD[9.9527] → [9.9527:9688]

	periods.add_system_time_period('user_account', excluded_column_names => ARRAY['token_hash', 'token_expiry']),
	periods.add_system_versioning('user_account'),

[9.9527]

[9.9688]

	periods.add_system_time_period('usr', excluded_column_names => ARRAY['token_hash', 'token_expiry']),
	periods.add_system_versioning('usr'),

Replacement in scrape/ocs.ca.js at line 51 [21.11674]
B:BD[20.1059] → [20.1059:1079]
```
		portions = null;
```
[20.1059]
[20.1079]
```
		portions = 1;
```

Replacement in scrape/ocs.ca.js at line 353 [21.11674]

B:BD[20.6132] → [6.0:156]

				if(e[i][0].handle === 'thc-dark-chocolate-bar-bhang' || e[i][0].handle === 'thc-milk-chocolate-bar-bhang' && quantity === '10' && portions === null) {

[20.6132]

[22.135]

				if(e[i][0].handle === 'thc-dark-chocolate-bar-bhang' || e[i][0].handle === 'thc-milk-chocolate-bar-bhang' && quantity === '10' && portions === 1) {

Replacement in scrape/ocs.ca.js at line 356 [21.11674]

B:BD[22.181] → [6.157:342]

				} else if(e[i][0].handle === 'balance-solid-milk-chocolate-chowie-wowie' || e[i][0].handle === 'thc-solid-milk-chocolate-chowie-wowie' && quantity === '16' && portions === null) {

[22.181]

[22.345]

				} else if(e[i][0].handle === 'balance-solid-milk-chocolate-chowie-wowie' || e[i][0].handle === 'thc-solid-milk-chocolate-chowie-wowie' && quantity === '16' && portions === 1) {

Replacement in scrape/ocs.ca.js at line 359 [21.11674]

B:BD[22.389] → [6.343:449]

				} else if(e[i][0].handle === 'dark-chocolate-bar-foray' && quantity === '40' && portions === null) {

[22.389]

[22.474]

				} else if(e[i][0].handle === 'dark-chocolate-bar-foray' && quantity === '40' && portions === 1) {

Replacement in scrape/ocs.ca.js at line 362 [21.11674]

B:BD[22.519] → [6.450:575]

				} else if(e[i][0].handle === 'bakerstreet-peppermint-milk-chocolate-tweed' && quantity === '32' && portions === null) {

[22.519]

[22.623]

				} else if(e[i][0].handle === 'bakerstreet-peppermint-milk-chocolate-tweed' && quantity === '32' && portions === 1) {

Replacement in scrape/ocs.ca.js at line 365 [21.11674]

B:BD[22.667] → [6.576:762]

				} else if(e[i][0].handle === 'pause-dark-milk-chocolate-bar-tokyo-smoke' || e[i][0].handle === 'go-dark-milk-chocolate-bar-tokyo-smoke' && quantity === '32' && portions === null) {

[22.667]

[22.832]

				} else if(e[i][0].handle === 'pause-dark-milk-chocolate-bar-tokyo-smoke' || e[i][0].handle === 'go-dark-milk-chocolate-bar-tokyo-smoke' && quantity === '32' && portions === 1) {

Replacement in scrape/ocs.ca.js at line 394 [21.11674]

B:BD[21.22398] → [21.22398:22488]

							portions ${portions === null ? 'is null' : '= $' + parameters.push(portions)} and

[21.22398]

[21.22488]

							portions = $${parameters.push(portions)} and