boogerlad/mj - Change BUCBGGRUTH7HNAADSU24U4BPHPUY7QYB3KRZPIIDUERZTL3YNTZQC

add some constraints, remove decimal coercion for tax rate and quantity. change concentration min / max to range. learn how to coalesce lol. individual product query fixed. it now returns product info, aggregated ranges for its price and concentration depending on variant and / or store, a list of all variants associated with the product, and all stores that carry this product. product_select(variant) => menu_items to determine valid stores to pick. product_select(store) => menu_items to determine valid variants to pick. product_select(variant, store) => 0 or 1 menu_item(not used by frontend yet)

Created by boogerlad on February 5, 2020

BUCBGGRUTH7HNAADSU24U4BPHPUY7QYB3KRZPIIDUERZTL3YNTZQC

Dependencies

In channels

main

Change contents

Insertion in initialize.sql at line 6 [4.17]

[3.146]

create aggregate range_merge(anyrange)
(
	sfunc = range_merge,
	stype = anyrange
);

Replacement in initialize.sql at line 31 [4.17]
B:BD[3.638] → [3.638:667]
```
	rate decimal(4,4) not null
```
[3.638]
[4.528]
```
	rate decimal not null check (rate > 0 and rate < 1)
```
Replacement in initialize.sql at line 246 [4.17]
B:BD[4.2822] → [4.2822:2857]
```
	quantity decimal(6, 3) not null,
```
[4.2822]
[4.2857]
```
	quantity decimal not null,
```

Replacement in initialize.sql at line 255 [4.17]

B:BD[4.3319] → [4.3319:3514]

	CBD_min decimal(6, 4) not null,
	CBD_max decimal(6, 4) not null,
	THC_min decimal(6, 4) not null,
	THC_max decimal(6, 4) not null,
	price decimal(5, 2) not null,
	stock smallint not null,

[4.3319]

[3.2230]

	CBD numrange not null check (lower(CBD) >= 0),
	THC numrange not null check (lower(THC) >= 0),
	price decimal(5, 2) not null check (price >= 0),
	stock smallint not null check (stock >= 0),

Replacement in server.js at line 543 [6.590]

∅:D[5.219] → [3.5341:5498]

B:BD[3.5341] → [3.5341:5498]

							COALESCE(json_agg(json_build_object('id', variant.id, 'portions', portions, 'quantity', quantity)) filter (where quantity is not null)) as variants,

[5.219]

[3.5498]

							coalesce(json_agg(json_build_object('id', variant.id, 'portions', portions, 'quantity', quantity)) filter (where quantity is not null), '[]') as variants,

Insertion in server.js at line 561 [6.590]

[7.1246]

					let params = [parameters.id];
					let poop = 1;
					let str = '';
					if(parameters.variant !== undefined) {
						params.push(parameters.variant);
						++poop;
						str += ' and variant.id = $' + poop;
					}
					if(parameters.store !== undefined) {
						params.push(parameters.store);
						++poop;
						str += ' and menu_item.store = $' + poop;
					}
					console.log(str);

Replacement in server.js at line 583 [6.590]

B:BD[7.1863] → [7.1863:2077]

B:BD[7.2077] → [5.522:575]

∅:D[5.575] → [7.2137:2774]

B:BD[7.2137] → [7.2137:2774]

								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.name) as strain_family,
								COALESCE(
									json_agg(
										json_build_object(
											'id', variant.id,
											'portions', portions,
											'quantity', quantity,
											'stores', fuck
										)
									)
									filter (where quantity is not null)
								) as variants,
								first(concentration_unit) as concentration_unit,
								first(measurement_unit) as measurement_unit,
								(select json_agg(distinct jsonb_build_object('id', store.id, 'name', store.name)) from store inner join menu_item on menu_item.store = store.id inner join variant on menu_item.variant = variant.id where product = $1) as stores

[7.1863]

[7.2774]

								product.id,
								product.type,
								display_brand as brand,
								producer,
								display_name as name,
								strain,
								strain_family.name as strain_family,
								concentration_unit,
								measurement_unit,
								range_merge(menu_item.cbd) as cbd,
								range_merge(menu_item.thc) as thc,
								min(price) as price_min,
								max(price) as price_max,
								(select coalesce(json_agg(i), '[]') from (select id, portions, quantity from variant where product.id = variant.product) as i) as variants,
								(select coalesce(json_agg(i), '[]') from (select distinct on(store.id) store.id, store.url, store.name from store inner join menu_item on store.id = menu_item.store inner join variant on variant.id = menu_item.variant inner join product on product.id = variant.product where product.id = $1) as i) as stores

Replacement in server.js at line 600 [6.590]

B:BD[7.2804] → [7.2804:3631]

B:BD[7.3631] → [5.576:661]

								left outer join (
									select
										first(variant.id) as id,
										first(variant.product) as product,
										first(variant.portions) as portions,
										first(variant.quantity) as quantity,
										json_agg(
											json_build_object(
												'id', menu_item.id,
												'store', menu_item.store,
												'cbd_min', menu_item.cbd_min,
												'cbd_max', menu_item.cbd_max,
												'thc_min', menu_item.thc_min,
												'thc_max', menu_item.thc_max,
												'price', menu_item.price,
												'stock', menu_item.stock
											)
										) as fuck
									from
										variant
										inner join menu_item on variant.id = menu_item.variant
									group by
										variant.id
								) as variant on (product.id = variant.product)
								left outer join strain_family on (product.strain_family = strain_family.id)

[7.2804]

[7.3730]

								left outer join strain_family on product.strain_family = strain_family.id
								left outer join variant on variant.product = product.id
								left outer join menu_item on menu_item.variant = variant.id

Replacement in server.js at line 604 [6.590]

B:BD[7.3744] → [7.3744:3794]

								show = true and
								product.id = $1

[7.3744]

[7.3794]

								product.id = $1${str}

Replacement in server.js at line 606 [6.590]

B:BD[7.3811] → [7.3811:3867]

								product.id
						`, [parameters.id])).rows[0]

[7.3811]

[7.3867]

								product.id,
								strain_family.id;
						`, params)).rows[0]

Insertion in server.js at line 619 [6.590]

[7.4099]

			case 'product_select'://search for menu_item(s) given variant and / or store and return.
					let params = [];
					let poop = 0;
					let str = '';
					if(parameters.variant !== undefined) {
						params.push(parameters.variant);
						++poop;
						str += ' and menu_item.variant = $' + poop;
					}
					if(parameters.store !== undefined) {
						params.push(parameters.store);
						++poop;
						str += ' and menu_item.store = $' + poop;
					}
					str = str.slice(5);
					ws.send(JSON.stringify({
						response_ID: request_ID,
						data: (await pool.query(`
							select
								id,
								variant,
								store
							from
								menu_item
							where
								${str}
						`, params)).rows
					}));
				break;

Replacement in server.js at line 678 [6.590]

B:BD[8.493] → [8.493:661]

									query += `(default, $${i * 9 + 1}, $${i * 9 + 2}, $${i * 9 + 3}, $${i * 9 + 4}, $${i * 9 + 5}, $${i * 9 + 6}, $${i * 9 + 7}, $${i * 9 + 8}, $${i * 9 + 9}),`;

[8.493]

[8.661]

									query += `(default, $${i * 7 + 1}, $${i * 7 + 2}, $${i * 7 + 3}, $${i * 7 + 4}, $${i * 7 + 5}, $${i * 7 + 6}, $${i * 7 + 7}),`;

Replacement in server.js at line 682 [6.590]

B:BD[8.758] → [8.758:938]

										parameters.menu_items[i].min_cbd,
										parameters.menu_items[i].max_cbd,
										parameters.menu_items[i].min_thc,
										parameters.menu_items[i].max_thc,

[8.758]

[8.938]

										`[${parameters.menu_items[i].min_cbd},${parameters.menu_items[i].max_cbd}]`,
										`[${parameters.menu_items[i].min_thc},${parameters.menu_items[i].max_thc}]`,

Replacement in server.js at line 690 [6.590]

B:BD[5.768] → [5.768:920]

									cbd_min = excluded.cbd_min,
									cbd_max = excluded.cbd_max,
									thc_min = excluded.thc_min,
									thc_max = excluded.thc_max,

[5.768]

[5.920]

									cbd = excluded.cbd,
									thc = excluded.thc,

Replacement in server.js at line 694 [6.590]

B:BD[5.993] → [2.0:263]

								where (menu_item.cbd_min, menu_item.cbd_max, menu_item.thc_min, menu_item.thc_max, menu_item.price, menu_item.featured) is distinct from
								(excluded.cbd_min, excluded.cbd_max, excluded.thc_min, excluded.thc_max, excluded.price, excluded.featured)

[5.993]

								where (menu_item.cbd, menu_item.thc, menu_item.price, menu_item.featured) is distinct from
								(excluded.cbd, excluded.thc, excluded.price, excluded.featured)