boogerlad/mj - Change 63VXWIHIAKGK7J4VTNRUAG2V32N2QUSWFELB6GD34S54FGRWAPCQC

rename strain_family to strain, strain to species support new schema move queries to postgres function

Created by boogerlad on March 21, 2020

63VXWIHIAKGK7J4VTNRUAG2V32N2QUSWFELB6GD34S54FGRWAPCQC

Dependencies

In channels

main

Change contents

Replacement in initialize.sql at line 96 [10.17]
B:BD[8.102] → [9.1331:1361]
B:BD[9.1361] → [11.1052:1091]
```
create table strain_family (
	strain_family_id serial primary key,
```
[8.102]
[12.6077]
```
create table strain (
	strain_id serial primary key,
```

Replacement in initialize.sql at line 103 [10.17]

∅:D[13.157] → [14.0:68]

B:BD[12.6341] → [14.0:68]

create type strain as enum('indica', 'sativa', 'hybrid', 'blend');

[13.157]

[10.1670]

create type species as enum('indica', 'sativa', 'hybrid', 'blend');

Replacement in initialize.sql at line 111 [10.17]
∅:D[13.305] → [9.1688:1714]
∅:D[14.213] → [9.1688:1714]
∅:D[12.6483] → [9.1688:1714]
B:BD[10.1990] → [9.1688:1714]
B:BD[9.1714] → [11.1126:1198]
```
	strain strain not null,
	strain_family_id integer references strain_family on delete set null,
```
[13.305]
[14.214]
```
	species species not null,
	strain_id integer references strain on delete set null,
```

Replacement in initialize.sql at line 155 [10.17]

∅:D[11.2451] → [15.175:273]

B:BD[10.3319] → [15.175:273]

	CBD numrange not null check (lower(CBD) >= 0),
	THC numrange not null check (lower(THC) >= 0),

[11.2451]

[15.273]

	cbd numrange not null check (lower(cbd) >= 0),
	thc numrange not null check (lower(thc) >= 0),

Replacement in initialize.sql at line 162 [10.17]
∅:D[7.1910] → [16.105:148]
∅:D[11.2512] → [16.105:148]
B:BD[16.105] → [16.105:148]
```
	check (lower(CBD) > 0 or lower(THC) > 0)
```
[7.1910]
[10.3610]
```
	check (lower(cbd) > 0 or lower(thc) > 0)
```

Replacement in initialize.sql at line 228 [10.17]

B:BD[12.8943] → [12.8943:9044]

	periods.add_system_time_period('strain_family'),
	periods.add_system_versioning('strain_family'),

[12.8943]

[12.9044]

	periods.add_system_time_period('strain'),
	periods.add_system_versioning('strain'),

Replacement in initialize.sql at line 247 [10.17]
B:BD[12.9917] → [12.9917:9951]
```
insert into strain_family values
```
[12.9917]
[12.9951]
```
insert into strain values
```

Replacement in initialize.sql at line 453 [10.17]

B:BD[6.642] → [6.642:916]

create or replace function ios_product_id(_producer text, _brand text, _display_name text, _joined_name citext, _type product_type, _strain strain, _strain_family citext, _description text, _show boolean, _terpenes citext[], _dosage decimal, out _product_id integer) as $$

[6.642]

[6.916]

create or replace function ios_product_id(_producer text, _brand text, _display_name text, _joined_name citext, _type product_type, _species species, _strain citext, _description text, _show boolean, _terpenes citext[], _dosage decimal, out _product_id integer) as $$

Replacement in initialize.sql at line 471 [10.17]
B:BD[6.1273] → [6.1273:1307]
```
			strain,
			strain_family_id,
```
[6.1273]
[6.1307]
```
			species,
			strain_id,
```

Replacement in initialize.sql at line 483 [10.17]

B:BD[6.1448] → [6.1448:1546]

			_strain,
			(select strain_family_id from strain_family where joined_name = _strain_family),

[6.1448]

[6.1546]

			_species,
			(select strain_id from strain where joined_name = _strain),

Insertion in initialize.sql at line 529 [10.17]

[6.2516]

[12.18338]

create or replace function get_store(_store_id integer) returns table(name text, url text, address citext, address2 citext, city citext, region citext, country citext, postal_code citext, coordinate point, timezone text, images json, times json) as $$
	select
		name,
		url,
		address,
		address2,
		city,
		region,
		country,
		postal_code,
		coordinate,
		timezone,
		images,
		times
	from
		store
		left outer join (
			select
				store_id,
				json_agg(url) as images
			from
				store_image
			--order by rank
			group by
				store_id
		) as store_images using (store_id)
		left outer join (
			select
				store_id,
				json_agg(open) as times--combine the ranges? key value? (day : array of times)
			from
				store_time
			group by
				store_id
		) as store_times using (store_id)
	where
		store_id = _store_id
$$ language sql;
create or replace function get_products(
	_store_id integer = null,
	_user_id integer = null
) returns table(
	id integer,
	type product_type,
	producer text,
	brand text,
	name text,
	species species,
	strain text,
	terpenes citext[],
	image text,
	cbd numrange,
	thc numrange,
	min_price decimal(5, 2),
	quantity decimal
) as $$
	select
		product_id,
		type,
		producer,
		brand,
		product.display_name,
		species,
		strain.display_name,
		terpenes,
		destination,
		cbd,
		thc,
		min,
		first
	from
		product
		left outer join strain using (strain_id)
		left outer join (
			select distinct on(product_id)--https://dba.stackexchange.com/a/159899
				product_id,
				destination
			from
				product_image
			--order by rank
		) as product_image using (product_id)
		inner join (--aggregate first, then join https://stackoverflow.com/a/27626358 I think this counts as "retrieve all or most"? obviously more filters maybe not, but let's optimize later
			select
				product_id,
				range_merge(cbd) as cbd,
				range_merge(thc) as thc,
				min(price),
				first(quantity)--this is used to convert mg / g to mg / piece for edibles
			from
				menu_item
				inner join variant using (variant_id)
			where
				store_id = _store_id or _store_id is null
			group by
				product_id
		) as product_agg using (product_id)
	where
		show = true
		--brand ilike $1
	--order by cbd, thc, min_price, etc. idk if ranges are sortable... looks like they are
$$ language sql;
create or replace function get_product_static(
	_product_id integer,
	_store_id integer = null
) returns table(
	producer text,
	brand text,
	name text,
	type product_type,
	species species,
	terpenes citext[],
	strain text,
	images json,
	variants json
) as $$
	select
		producer,
		brand,
		product.display_name,
		type,
		species,
		terpenes,
		--aggregate first, then join https://stackoverflow.com/a/27626358 use subquery since small selection
		(select strain.display_name from strain where strain.strain_id = product.strain_id),
		coalesce((select json_agg(destination) from product_image where product_image.product_id = product.product_id), '[]'),
		coalesce((select json_strip_nulls(json_agg(json_build_object(
			'id', variant_id,
			'portions', portions,
			'quantity', quantity,
			'disabled', case
				when _store_id is null then null
				else not exists(select 1 from menu_item where menu_item.variant_id = variant.variant_id and menu_item.store_id = _store_id)
			end
		))) from variant where variant.product_id = product.product_id), '[]')
	from
		product
	where
		product_id = $1
$$ language sql;

Replacement in scrape/common.js at line 130 [18.4897]
B:BD[17.585] → [17.585:612]
```
	strain_family_clean(s) {
```
[17.585]
[19.40]
```
	strain_clean(s) {
```

Replacement in scrape/common.js at line 145 [18.4897]

B:BD[17.1240] → [2.0:171]

		.replace(/\bgrand\s+daddy\b/, 'granddaddy')//todo: better space detection. lots of strain families can be spelt with or without spaces... which will throw off the sort

[17.1240]

		.replace(/\bgrand\s+daddy\b/, 'granddaddy')//todo: better space detection. lots of strains can be spelt with or without spaces... which will throw off the sort

Replacement in scrape/ocs.ca.js at line 117 [18.11674]
∅:D[19.1011] → [20.1816:1866]
B:BD[18.16231] → [20.1816:1866]
```
			let strain = e[i][0].meta.product.plant_type;
```
[19.1011]
[18.16282]
```
			let species = e[i][0].meta.product.plant_type;
```
Replacement in scrape/ocs.ca.js at line 151 [18.11674]
B:BD[18.16524] → [20.1910:1958]
```
			if(strain === undefined) {
				strain = f2
```
[18.16524]
[18.16574]
```
			if(species === undefined) {
				species = f2
```
Replacement in scrape/ocs.ca.js at line 154 [18.11674]
B:BD[18.16580] → [20.1959:1983]
```
			if(f2 !== strain) {
```
[18.16580]
[18.16605]
```
			if(f2 !== species) {
```
Replacement in scrape/ocs.ca.js at line 157 [18.11674]
B:BD[18.16661] → [20.1984:2010]
```
				console.log(strain);
```
[18.16661]
[20.2010]
```
				console.log(species);
```
Replacement in scrape/ocs.ca.js at line 159 [18.11674]
B:BD[20.2016] → [20.2016:2059]
```
			let spaceIndex = strain.search(/\s+/);
```
[20.2016]
[20.2059]
```
			let spaceIndex = species.search(/\s+/);
```
Replacement in scrape/ocs.ca.js at line 161 [18.11674]
B:BD[20.2158] → [20.2158:2205]
```
				strain = strain.substring(0, spaceIndex);
```
[20.2158]
[18.16688]
```
				species = species.substring(0, spaceIndex);
```

Replacement in scrape/ocs.ca.js at line 163 [18.11674]

B:BD[18.16694] → [20.2206:2241]

B:BD[20.2241] → [19.1012:1070]

			strain = strain.toLowerCase();
			if(strain === '-' && e[i][0].handle === 'indica-1') {

[18.16694]

[19.1070]

			species = species.toLowerCase();
			if(species === '-' && e[i][0].handle === 'indica-1') {

Replacement in scrape/ocs.ca.js at line 166 [18.11674]
B:BD[19.1117] → [19.1117:1141]
```
				strain = 'indica';
```
[19.1117]
[19.1141]
```
				species = 'indica';
```

Replacement in scrape/ocs.ca.js at line 188 [18.11674]

B:BD[21.5637] → [21.5637:5726]

				strain,
				strain_family_clean(quickview.product.metafields.product.street_name),

[21.5637]

[21.5726]

				species,
				strain_clean(quickview.product.metafields.product.street_name),

Replacement in server.js at line 72 [22.590]

B:BD[22.1734] → [23.547:779]

						let newUser = await pool.query('insert into user_account (email, passphrase_hash) values ($1, $2) returning id, email, type', [parameters.email, await argon2.hash(parameters.passphrase)]);//technically, we only need the id??

[22.1734]

[22.1886]

						let newUser = await pool.query('insert into usr (email, passphrase_hash) values ($1, $2) returning user_id, email, type', [parameters.email, await argon2.hash(parameters.passphrase)]);//technically, we only need the id??

Replacement in server.js at line 84 [22.590]

B:BD[22.2023] → [22.2023:2078]

						if(e.constraint !== 'user_account_email_key') {

[22.2023]

[22.2078]

						if(e.constraint !== 'usr_email_key') {

Replacement in server.js at line 97 [22.590]

B:BD[24.915] → [24.915:995]

					await pool.query(`delete from user_account where id = $1`, [ws.user_ID]);

[24.915]

[24.995]

					await pool.query(`delete from usr where user_id = $1`, [ws.user_ID]);

Replacement in server.js at line 125 [22.590]

B:BD[22.2427] → [22.2427:2539]

					let user = (await pool.query('select * from user_account where email = $1', [parameters.email])).rows[0];

[22.2427]

[22.2539]

					let user = (await pool.query('select * from usr where email = $1', [parameters.email])).rows[0];

Replacement in server.js at line 134 [22.590]
∅:D[23.859] → [22.2786:2817]
B:BD[22.2786] → [22.2786:2817]
```
								ws.user_ID = user.id;
```
[23.859]
[22.2817]
```
								ws.user_ID = user.user_id;
```

Replacement in server.js at line 137 [22.590]

B:BD[22.2897] → [22.2897:3097]

								await pool.query('update user_account set token_hash = $1 where id = $2', [crypto.createHash('BLAKE2b512').update(token).digest(), user.id]);//update to blake3 once it's available in openSSL

[22.2897]

[24.1658]

								await pool.query('update usr set token_hash = $1 where user_id = $2', [crypto.createHash('BLAKE2b512').update(token).digest(), user.user_id]);//update to blake3 once it's available in openSSL

Replacement in server.js at line 147 [22.590]

B:BD[24.1964] → [24.1964:2011]

								let old = authenticated.get(user.id);

[24.1964]

[24.2011]

								let old = authenticated.get(user.user_id);

Replacement in server.js at line 153 [22.590]

B:BD[24.2152] → [24.2152:2193]

								authenticated.set(user.id, ws);

[24.2152]

[24.2193]

								authenticated.set(user.user_id, ws);

Replacement in server.js at line 182 [22.590]

B:BD[22.4033] → [25.0:239]

					let user = (await pool.query('select * from user_account where token_hash = $1 and now() < token_expiry', [crypto.createHash('BLAKE2b512').update(Buffer.from(parameters.token, 'base64')).digest()])).rows[0];//possible timing attack?

[22.4033]

[22.4222]

					let user = (await pool.query('select * from usr where token_hash = $1 and now() < token_expiry', [crypto.createHash('BLAKE2b512').update(Buffer.from(parameters.token, 'base64')).digest()])).rows[0];//possible timing attack?

Replacement in server.js at line 189 [22.590]
B:BD[22.4375] → [22.4375:4404]
```
						ws.user_ID = user.id;
```
[22.4375]
[22.4404]
```
						ws.user_ID = user.user_id;
```

Replacement in server.js at line 197 [22.590]

B:BD[24.2669] → [24.2669:2714]

						let old = authenticated.get(user.id);

[24.2669]

[24.2714]

						let old = authenticated.get(user.user_id);

Replacement in server.js at line 203 [22.590]
B:BD[24.2845] → [24.2845:2884]
```
						authenticated.set(user.id, ws);
```
[24.2845]
[24.2884]
```
						authenticated.set(user.user_id, ws);
```

Replacement in server.js at line 214 [22.590]

B:BD[24.3077] → [24.3077:3195]

					await pool.query(`update user_account set token_expiry = null, token_hash = null where id = $1`, [ws.user_ID]);

[24.3077]

[24.3195]

					await pool.query(`update usr set token_expiry = null, token_hash = null where user_id = $1`, [ws.user_ID]);

Replacement in server.js at line 237 [22.590]

B:BD[24.3700] → [24.3700:3804]

						let users = (await pool.query('select * from user_account')).rows;//* is a bad idea... fix later

[24.3700]

[24.3804]

						let users = (await pool.query('select * from usr')).rows;//* is a bad idea... fix later

Replacement in server.js at line 280 [22.590]

B:BD[26.5905] → [26.5905:6100]

									insert into store_tax (store, name, rate)
									values ${parameters.taxes.map(tax => `((select id from insert1), $${params.push(tax.name)}, $${params.push(tax.rate)})`).join(', ')}

[26.5905]

[26.6100]

									insert into store_tax (store_id, name, rate)
									values ${parameters.taxes.map(tax => `((select store_id from insert1), $${params.push(tax.name)}, $${params.push(tax.rate)})`).join(', ')}

Replacement in server.js at line 293 [22.590]

B:BD[26.6728] → [26.6728:6810]

									return `((select id from insert1), $${params.push(`[${ts}, ${te})`)})`;

[26.6728]

[26.6810]

									return `((select store_id from insert1), $${params.push(`[${ts}, ${te})`)})`;

Replacement in server.js at line 304 [22.590]

B:BD[26.7167] → [26.7167:7214]

									insert into store_time (store, open)

[26.7167]

[26.7214]

									insert into store_time (store_id, open)

Replacement in server.js at line 310 [22.590]

B:BD[26.7388] → [26.7388:7551]

									insert into store_image (store, URL)
									values ${parameters.images.map(image => `((select id from insert1), $${params.push(image)})`).join(', ')}

[26.7388]

[26.7551]

									insert into store_image (store_id, URL)
									values ${parameters.images.map(image => `((select store_id from insert1), $${params.push(image)})`).join(', ')}

Replacement in server.js at line 367 [22.590]
B:BD[26.9756] → [26.9756:9779]
```
									returning id
```
[26.9756]
[26.9779]
```
									returning store_id
```
Replacement in server.js at line 369 [22.590]
B:BD[26.9798] → [26.9798:9832]
```
								select id from insert1`,
```
[26.9798]
[26.9832]
```
								select store_id from insert1`,
```

Replacement in server.js at line 372 [22.590]

B:BD[26.9870] → [26.9870:9919]

								console.log/*ws.publish*/(r.rows[0].id)

[26.9870]

[26.9919]

								console.log/*ws.publish*/(r.rows[0].store_id)

Replacement in server.js at line 426 [22.590]

∅:D[23.1105] → [27.496:680]

B:BD[27.496] → [27.496:680]

				let stores = (await pool.query('select distinct on(store.id) store.id, store.name, store_image.URL from store left outer join store_image on store.id = store_image.store')).rows;

[23.1105]

[27.680]

				let stores = (await pool.query('select distinct on(store_id) store_id as id, name, store_image.url from store left outer join store_image using (store_id)')).rows;

Replacement in server.js at line 437 [22.590]

B:BD[28.609] → [28.609:981]

∅:D[28.981] → [29.371:385]

B:BD[29.371] → [29.371:385]

B:BD[29.385] → [28.982:1416]

					let response = (await pool.query(`
						select
							name,
							URL,
							address,
							address2,
							city,
							region,
							country,
							postal_code,
							coordinate,
							timezone,
							images,
							times
						from
							store
							left outer join (
								select
									store,
									json_agg(URL) as images
								from
									store_image
								--order by rank
								group by
									store
							) as store_image on store_image.store = store.id
							left outer join (
								select
									store,
									json_agg(open) as times--need to agg the range
								from
									store_time
								group by
									store
							) as store_time on store_time.store = store.id
						where
							store.id = $1
					`, [s])).rows;

[28.609]

[28.1416]

					let response = (await pool.query(`select * from get_store($1)`, [s])).rows;

Replacement in server.js at line 474 [22.590]

B:BD[8.8537] → [3.0:77]

∅:D[3.77] → [30.138:230]

B:BD[9.4101] → [30.138:230]

∅:D[30.230] → [9.4177:4279]

B:BD[9.4177] → [9.4177:4279]

B:BD[9.4279] → [3.78:185]

						const [strain_families, strains, product_types] = await Promise.all([
							pool.query('select id, display_name as 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)

[8.8537]

[9.4613]

						const [strains, pgenum] = await Promise.all([
							pool.query('select strain_id as id, display_name as name from strain').then(x => x.rows),
							pool.query('select array_to_json(enum_range(null::species)) as species, array_to_json(enum_range(null::product_type)) as product_types').then(x => x.rows[0])

Replacement in server.js at line 480 [22.590]

B:BD[8.8602] → [3.186:242]

							data: {strain_families, strains, product_types}

[8.8602]

[8.8685]

							data: {strains, ...pgenum}

Replacement in server.js at line 497 [22.590]

B:BD[8.9011] → [27.835:929]

					if(is(ws, 'god')) {//todo: cte this into a single transaction, images. see create_store

[8.9011]

[8.9037]

					if(is(ws, 'god')) {

Replacement in server.js at line 499 [22.590]

B:BD[8.9050] → [8.9050:9129]

B:BD[8.9129] → [31.79:231]

∅:D[32.146] → [8.9274:9289]

∅:D[31.231] → [8.9274:9289]

∅:D[9.4905] → [8.9274:9289]

B:BD[8.9274] → [8.9274:9289]

B:BD[8.9394] → [8.9394:9424]

B:BD[8.9424] → [9.4906:4933]

∅:D[9.4933] → [8.9424:9600]

B:BD[8.9424] → [8.9424:9600]

B:BD[31.270] → [31.270:297]

∅:D[31.297] → [32.147:171]

∅:D[9.5011] → [32.147:171]

B:BD[8.9614] → [32.147:171]

							let j = normalize(parameters.brand, parameters.name, parameters.type);
							let product = (await pool.query('insert into product values (default, $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, true, $12) returning id', [
								...j,
								parameters.producer,
								parameters.brand,
								parameters.name,
								parameters.strain,
								parameters.strain_family === "" ? undefined : Number(parameters.strain_family),
								parameters.description,
								parameters.images
							])).rows[0].id;

[8.9050]

[32.171]

							let q = 1;
							let params = [...normalize(parameters.brand, parameters.name, parameters.type), parameters.type, parameters.species, parameters.strain === "" ? undefined : Number(parameters.strain), parameters.description];
							let query = `with insert1 as (insert into product (producer, brand, display_name, joined_name, type, species, strain_id, description) values ($1, $2, $3, $4, $5, $6, $7, $8) on conflict do nothing returning product_id)`;//todo: terpenes

Replacement in server.js at line 503 [22.590]

B:BD[32.250] → [32.250:532]

B:BD[32.532] → [33.0:228]

B:BD[33.228] → [5.155:169]

								let query = 'insert into variant values ';
								let params = [];
								for(let i = 0; i < parameters.variants.length; ++i) {
									if(parameters.variants[i].quantity !== undefined) {
										query += `(default, $${i * 3 + 1}, $${i * 3 + 2}, $${i * 3 + 3}),`;
										params.push(
											product,
											parameters.variants[i].portions === undefined ? 0 : Number(parameters.variants[i].portions),
											Number(parameters.variants[i].quantity)//Number(undefined) -> NaN
										);

[32.250]

[32.783]

								let values = parameters.variants.reduce((acc, v) => {
									if(v.quantity && v.gram_equivalency) {//todo: "SELECT instead of VALUES makes sure nothing is inserted in subsidiary tables if no row is returned from a previous INSERT" since on conflict do nothing
										acc.push(`((select product_id from insert1), ${v.portions ? '$' + params.push(v.portions) : 'default'}, $${params.push(v.quantity)}, $${params.push(v.gram_equivalency)})`);

Insertion in server.js at line 507 [22.590]

[32.795]

									return acc;
								}, []);
								if(values.length) {
									query += `, insert${++q} as (insert into variant (product_id, portions, quantity, gram_equivalency) values ${values.join(', ')} on conflict do nothing)`;//haven't decided what to do with flavor

Replacement in server.js at line 512 [22.590]

B:BD[32.806] → [32.806:924]

								//console.log(query);
								//console.log(params);
								await pool.query(query.slice(0, -1), params);

[32.806]

[32.924]

							}
							if(Array.isArray(parameters.images) && parameters.images.length) {
								query += `, insert${++q} as (insert into product_image (product_id, destination) values ${parameters.images.map(i => `((select product_id from insert1), $${params.push(i)})`).join(', ')} on conflict do nothing)`;

Insertion in server.js at line 516 [22.590]

[32.934]

[8.9626]

							let product = (await pool.query(query + ' select product_id from insert1', params)).rows[0].product_id;

Deletion in server.js at line 543 [22.590]

B:BD[28.1780] → [28.1780:1889]

				let query = '';
				if(Number.isInteger(s)) {
					query += `where store = $${params.push(s)}`
				}

Replacement in server.js at line 544 [22.590]

B:BD[8.10275] → [28.1890:2002]

∅:D[28.2002] → [9.5083:5115]

B:BD[9.5083] → [9.5083:5115]

B:BD[9.5115] → [28.2003:2017]

∅:D[28.2017] → [9.5153:5193]

B:BD[9.5153] → [9.5153:5193]

B:BD[9.5225] → [9.5225:5243]

B:BD[9.5243] → [34.227:242]

∅:D[34.242] → [35.0:38]

B:BD[9.5243] → [35.0:38]

∅:D[35.38] → [9.5273:5289]

B:BD[9.5273] → [9.5273:5289]

B:BD[9.5289] → [35.39:92]

B:BD[35.92] → [28.2018:2036]

B:BD[35.119] → [28.2037:2078]

B:BD[28.2078] → [4.1133:1162]

∅:D[28.2096] → [9.5551:5579]

∅:D[35.153] → [9.5551:5579]

∅:D[4.1162] → [9.5551:5579]

B:BD[9.5551] → [9.5551:5579]

B:BD[9.5579] → [28.2097:2554]

B:BD[28.2554] → [4.1163:1229]

∅:D[4.1229] → [28.2588:2808]

B:BD[28.2588] → [28.2588:2808]

∅:D[28.2808] → [9.5743:5776]

∅:D[35.267] → [9.5743:5776]

B:BD[9.5743] → [9.5743:5776]

B:BD[9.5776] → [34.243:268]

∅:D[34.268] → [28.2838:2954]

B:BD[28.2838] → [28.2838:2954]

					response_ID: request_ID,//https://dba.stackexchange.com/a/159899 and https://stackoverflow.com/a/27626358
					data: (await pool.query(`
						select
							product.id as id,
							type,
							producer,
							brand,
							product.display_name as name,
							strain,
							strain_family.display_name as strain_family,
							terpenes,
							image,
							cbd,
							thc,
							min_price,
							q
						from
							product
							left outer join strain_family on strain_family.id = product.strain_family
							left outer join (
								select distinct on(product)
									product,
									destination as image
								from
									product_image
								--order by rank
							) as product_image on product_image.product = product.id
							inner join (
								select
									variant.product,
									range_merge(cbd) as cbd,
									range_merge(thc) as thc,
									min(price) as min_price,
									first(quantity) as q
								from
									menu_item
									inner join variant on variant.id = menu_item.variant
								${query}
								group by
									variant.product
							) as product_agg on product_agg.product = product.id
						where
							show = true
							--brand ilike $1
						--order by cbd, thc, min_price, etc. idk if ranges are sortable... looks like you can
					`, params)).rows

[8.10275]

[8.10581]

					response_ID: request_ID,
					data: (await pool.query(`select * from get_products(${Number.isInteger(s) ? '$' + params.push(s) : ''})`, params)).rows

Replacement in server.js at line 554 [22.590]

B:BD[30.419] → [30.419:605]

∅:D[30.605] → [15.755:763]

B:BD[15.755] → [15.755:763]

B:BD[15.763] → [30.606:679]

B:BD[30.679] → [34.269:284]

∅:D[34.284] → [30.711:907]

B:BD[30.711] → [30.711:907]

B:BD[30.961] → [30.961:1411]

					let query = '';
					if(Number.isInteger(s)) {
						query += `, 'disabled', (select bool_and(store != $${params.push(s)}) from menu_item where menu_item.variant = variant.id)`;
					}
					let response = (await pool.query(`
						select
							producer,
							brand,
							product.display_name as name,
							type,
							strain,
							(select strain_family.display_name from strain_family where strain_family.id = product.strain_family) as strain_family,
							coalesce((select json_agg(destination) from product_image where product_image.product = product.id), '[]') as images,
							coalesce((select json_agg(json_build_object(
								'id', id,
								'portions', portions,
								'quantity', quantity${query}
							)) from variant where variant.product = product.id), '[]') as variants
						from
							product
						where
							product.id = $1
						`,
						params
					)).rows;

[30.419]

[30.1411]

					let response = (await pool.query(`select * from get_product_static($1${Number.isInteger(s) ? ', $' + params.push(s) : ''})`, params)).rows;

Deletion in server.js at line 619 [22.590]

∅:D[30.3103] → [15.2136:2229]

B:BD[36.4099] → [15.2136:2229]

B:BD[15.2229] → [27.1005:1068]

∅:D[27.1068] → [15.2292:2337]

B:BD[15.2292] → [15.2292:2337]

B:BD[15.2337] → [27.1069:1149]

∅:D[27.1149] → [15.2443:2494]

B:BD[15.2443] → [15.2443:2494]

B:BD[15.2494] → [27.1150:1226]

∅:D[27.1226] → [15.2596:2604]

B:BD[15.2596] → [15.2596:2604]

B:BD[15.2630] → [15.2630:2741]

B:BD[15.2741] → [27.1227:1250]

∅:D[27.1250] → [15.2754:2772]

B:BD[15.2754] → [15.2754:2772]

B:BD[15.2772] → [27.1251:1267]

B:BD[27.1267] → [30.3104:3154]

∅:D[27.1301] → [15.2787:2819]

∅:D[30.3154] → [15.2787:2819]

B:BD[15.2787] → [15.2787:2819]

B:BD[15.2819] → [27.1302:1480]

∅:D[27.1480] → [15.2819:2833]

B:BD[15.2819] → [15.2819:2833]

B:BD[15.2833] → [27.1481:1524]

∅:D[27.1524] → [15.2849:2896]

B:BD[15.2849] → [15.2849:2896]

			case 'product_select'://search for menu_item(s) given variant and / or store and return.
					let params = [parameters.product];
					let query = '';
					if(parameters.variant !== undefined) {
						query += ' and menu_item.variant = $' + params.push(parameters.variant);
					}
					if(parameters.store !== undefined) {
						query += ' and menu_item.store = $' + params.push(parameters.store);
					}
					ws.send(JSON.stringify({
						response_ID: request_ID,
						data: (await pool.query(`
							select
								menu_item.id,
								variant,
								store,
								store.URL || path as url,
								stock
							from
								menu_item
								inner join store on menu_item.store = store.id
								inner join variant on menu_item.variant = variant.id
								inner join product on variant.product = product.id
							where
								product.id = $1
								${query}
						`, params)).rows
					}));
				break;

Replacement in server.js at line 713 [22.590]

B:BD[37.1348] → [37.1348:1501]

							//whenever store_owner lands: update menu_item set where store = (select store from user_account where id = $1) and id = $2 or keep store in ws?

[37.1348]

[37.1501]

							//whenever store_owner lands: update menu_item set where store = (select store from usr where id = $1) and id = $2 or keep store in ws?

Replacement in server.js at line 748 [22.590]

B:BD[36.4978] → [38.807:1104]

					let cart = (await pool.query(`insert into cart (user_account, store) values ($1, (select store from menu_item where id = $2)) on conflict (user_account, store) where status is null or status = 'placed' or status = 'ready' do nothing returning id`, [ws.user_ID, parameters.menu_item])).rows;

[36.4978]

[36.5993]

					let cart = (await pool.query(`insert into cart (user_id, store_id) values ($1, (select store from menu_item where id = $2)) on conflict (user_id, store_id) where status <= 'ready' do nothing returning id`, [ws.user_ID, parameters.menu_item])).rows;

Replacement in server.js at line 750 [22.590]

B:BD[36.6069] → [38.1105:1306]

						let kart = (await pool.query(`select id from cart where user_account = $1 and store = (select store from menu_item where id = $2) and status is null`, [ws.user_ID, parameters.menu_item])).rows;

[36.6069]

[39.1940]

						let kart = (await pool.query(`select id from cart where user_id = $1 and store = (select store from menu_item where id = $2) and status is null`, [ws.user_ID, parameters.menu_item])).rows;

Replacement in server.js at line 791 [22.590]

B:BD[38.2157] → [38.2157:2332]

				await pool.query('delete from line_item using cart where line_item.cart = cart.id and cart.user_account = $1 and line_item.id = $2', [ws.user_ID, parameters.line_item]);

[38.2157]

[38.2332]

				await pool.query('delete from line_item using cart where line_item.cart = cart.id and cart.user_id = $1 and line_item.id = $2', [ws.user_ID, parameters.line_item]);

Replacement in server.js at line 824 [22.590]
B:BD[39.3585] → [39.3585:3616]
```
								user_account = $1 and
```
[39.3585]
[39.3616]
```
								user_id = $1 and
```

Replacement in server.js at line 863 [22.590]

B:BD[39.5075] → [39.5075:5146]

									cart.user_account = $1 and
									cart.status is null and

[39.5075]

[39.5146]

									cart.user_id = $1 and
									cart.status = 'active' and

Replacement in server.js at line 926 [22.590]

B:BD[24.4641] → [24.4641:4759]

			await pool.query(`update user_account set token_expiry = now() + interval '14 day' where id = $1`, [ws.user_ID]);

[24.4641]

[24.4759]

			await pool.query(`update usr set token_expiry = now() + interval '14 day' where user_id = $1`, [ws.user_ID]);