boogerlad/mj - Change ZBUC3VBT42SDAG42OCUX7MTT6GNPX2GEGWU2QIUYAY4NMX33IGLAC

move image persistance to common.js. move insert or select thing returning id into plpgsql function and loop as per https://dba.stackexchange.com/a/213625/ all that's left to do is variant identifier and menu_item and test. seems there are new ocs inconsistencies to fix too...

Created by kevin on March 17, 2020

ZBUC3VBT42SDAG42OCUX7MTT6GNPX2GEGWU2QIUYAY4NMX33IGLAC

Dependencies

In channels

main

Change contents

Insertion in initialize.sql at line 420 [6.17]

[2.454]

[5.18338]

--ios means insert or select
create or replace function ios_store_id(_name text, _url text, _region citext, _country citext, out _store_id integer) as $$
begin
	loop
		select store_id
		from store
		where
			type = 'online' and
			name = _name and
			url = _url and
			region = _region and
			country = _country
		into _store_id;
		exit when found;
		insert into store (
			name,
			url,
			region,
			country,
			type
		) values (
			_name,
			_url,
			_region,
			_country,
			'online'
		) on conflict do nothing
		returning store_id
		into _store_id;
		exit when found;
	end loop;
end
$$ language plpgsql;
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 $$
begin
	loop
		select product_id
		from product
		where
			producer is not distinct from _producer and
			brand is not distinct from _brand and
			joined_name is not distinct from _joined_name and
			type = _type
		into _product_id;
		exit when found;
		insert into product (
			producer,
			brand,
			display_name,
			joined_name,
			type,
			strain,
			strain_family_id,
			description,
			show,
			terpenes,
			dosage
		) values (
			_producer,
			_brand,
			_display_name,
			_joined_name,
			_type,
			_strain,
			(select strain_family_id from strain_family where joined_name = _strain_family),
			_description,
			_show,
			_terpenes,
			_dosage
		) on conflict do nothing
		returning product_id
		into _product_id;
		exit when found;
	end loop;
end
$$ language plpgsql;
create or replace function ios_variant_id(_product_id integer, _portions integer, _quantity integer, _flavor citext, _gram_equivalency decimal, out _variant_id integer, out g integer) as $$
begin
	loop
		select
			variant_id,
			gram_equivalency
		from variant
		where
			product_id = _product_id and
			quantity = _quantity and
			portions = _portions and
			flavor is not distinct from _flavor
		into _variant_id, g;
		exit when found;
		insert into variant (
			product_id,
			portions,
			quantity,
			flavor,
			gram_equivalency
		) values (
			_product_id,
			_portions,
			_quantity,
			_flavor,
			_gram_equivalency
		) on conflict do nothing
		returning variant_id
		into _variant_id;
		exit when found;
	end loop;
end
$$ language plpgsql;

Replacement in scrape/common.js at line 505 [7.4897]

B:BD[7.10412] → [8.482:544]

∅:D[8.544] → [7.10448:10627]

B:BD[7.10448] → [7.10448:10627]

	async return_key(query, values) {//this function should die
		return (await postgres.query({
			text: query,
			values: values,
			rowMode: 'array'
		})).rows;
	}//abstract out db call out of ocs.ca.js before working on other stores

[7.10412]

[9.45]

	async images(pics/*type set*/, productID) {
		for(let pic of pics) {
			let product_images = (await postgres.query(`select product_image_id, case when now() - system_time_start > '30 days' then destination else null end as destination from product_image where product_id = $1 and source = $2`, [productID, pic])).rows;
			if(product_images.length === 0) {
				let destination;
				if(process.env.CLOUDINARY_URL === 'fake') {
					//https://stackoverflow.com/a/13108449/
					let name = pic.split('/');
					name = name.pop() || name.pop();
					name += name.substring(name.lastIndexOf('.'));
					destination = "https://res.cloudinary.com/the-eternal-tao/image/upload/" + name;
				} else {
					let response = (await exec('./image.sh ' + pic)).stdout.trim();
					if(response.startsWith('/tmp/')) {
						destination = (await cloudinary.uploader.upload(response, {public_id: response.substring(5), unique_filename: false})).secure_url;
					} else {
						continue;
					}
				}
				let img_insert = (await postgres.query(
					`insert into product_image (
						product_id,
						source,
						destination
					) values (
						$1,
						$2,
						$3
					) on conflict do nothing
					returning ID`,
					[productID, pic, destination]
				)).rows;
				if(img_insert.length === 0) {
					console.error("how the FUCK", productID, pic, destination);
				} else if(img_insert.length > 1) {
					console.error('kys');
				}
			} else if(product_images.length === 1) {
				if(product_images[0].destination !== null && process.env.CLOUDINARY_URL !== 'fake') {
					let response = (await exec(`./image.sh ${pic} ${product_images[0].destination}`)).stdout.trim();
					if(response.startsWith('/tmp/')) {
						await postgres.query('update product_image set destination = $1 where product_image_id = $2', [
							(await cloudinary.uploader.upload(response, {public_id: response.substring(5), unique_filename: false})).secure_url,
							product_images[0].product_image_id
						]);
					}
				}
			} else {
				console.error('data integrity issue', product_images);
				//product match, source match, but destinations are different, which should be unpossible
			}
		}
	}

Replacement in scrape/ocs.ca.js at line 74 [7.11674]

B:BD[7.13467] → [7.13467:14301]

	let storeID = await return_key(
		`insert into store (
			name,
			URL,
			region,
			country
		) values (
			$1, $2, $3, $4
		) on conflict do nothing
		returning ID`,
		[
			'Ontario Cannabis Store',
			url,
			'ontario',
			'canada'
		]
	);
	if(storeID.length === 0) {
		storeID = await return_key(
			`select
				ID
			from
				store
			where
				name = $1 and
				URL = $2 and
				region = $3 and
				country = $4 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`,
			[
				'Ontario Cannabis Store',
				url,
				'ontario',
				'canada'
			]
		);
		if(storeID.length === 0) {
			console.error("why can't I find the store???");
			process.exit(1);
		}
	}
	storeID = storeID[0][0];

[7.13467]

[7.14301]

	let storeID = (await postgres.query(`select ios_store_id('Ontario Cannabis Store', $1, 'ontario', 'canada')`, [url])).rows[0].ios_store_id;

Replacement in scrape/ocs.ca.js at line 180 [7.11674]

B:BD[7.17036] → [10.5546:5564]

			let ppams = [

[7.17036]

[10.5564]

			let productID = (await postgres.query(`select ios_product_id($1, $2, $3, $4, $5, $6, $7, $8, true, $9, $10)`, [

Replacement in scrape/ocs.ca.js at line 189 [7.11674]

B:BD[10.5744] → [10.5744:5764]

∅:D[10.5764] → [7.17036:17102]

B:BD[7.17036] → [7.17036:17102]

B:BD[7.17102] → [8.1148:1197]

∅:D[8.1197] → [7.17122:17153]

B:BD[7.17122] → [7.17122:17153]

B:BD[7.17194] → [7.17194:17208]

B:BD[7.17208] → [8.1198:1219]

∅:D[8.1219] → [7.17208:17227]

B:BD[7.17208] → [7.17208:17227]

B:BD[7.17277] → [7.17277:17289]

B:BD[7.17289] → [10.5765:5818]

∅:D[10.5818] → [7.17304:17320]

B:BD[7.17304] → [7.17304:17320]

B:BD[7.17320] → [3.0:154]

∅:D[3.154] → [7.17372:17422]

∅:D[10.5971] → [7.17372:17422]

∅:D[8.1328] → [7.17372:17422]

B:BD[7.17372] → [7.17372:17422]

B:BD[7.17422] → [10.5972:5983]

∅:D[10.5983] → [7.17627:17667]

B:BD[7.17627] → [7.17627:17667]

B:BD[7.17667] → [8.1454:1644]

∅:D[8.1644] → [7.17667:17764]

B:BD[7.17667] → [7.17667:17764]

B:BD[7.17764] → [8.1645:1845]

∅:D[8.1845] → [7.17875:17883]

B:BD[7.17875] → [7.17875:17883]

B:BD[7.18072] → [7.18072:18231]

				terpenes
			]
			let productID = await return_key(
				`insert into product (
					producer,
					brand,
					display_name,
					joined_name,
					type,
					strain,
					strain_family,
					description,
					show,
					terpenes${brand === 'dosist' ? ',dosage' : ''}
				) values (
					$1, $2, $3, $4, $5, $6, (select id from strain_family where joined_name = $7), $8, true, $9${brand === 'dosist' ? `,$${ppams.push('.00225')}` : ''}
				) on conflict do nothing
				returning ID`,
				ppams
			);
			if(productID.length === 0) {
				//console.log(productID, `${url}/products/${e[i][0].handle}`) //on empty db, useful to find products that are actually variants of something we inserted earlier. good job ocs dumbfucks
				productID = await return_key(
					`select
						ID
					from
						product
					where
						producer is not distinct from $1 and
						brand is not distinct from $2 and
						joined_name is not distinct from $3 and
						type = $4`,
					[producer, brand, join_name, e[i][0].type]
				);
				if(productID.length === 0) {
					console.error("why can't I find the product???");
					process.exit(1);
				}
			}
			productID = productID[0][0];

[10.5744]

[11.2707]

				terpenes,
				brand === 'dosist' ? '.00225' : null
			])).rows[0].ios_product_id;

Replacement in scrape/ocs.ca.js at line 193 [7.11674]

B:BD[11.2744] → [11.2744:3867]

				let pics = new Set((await request({
					url: `${url}/products/${e[i][0].handle}.json`,
					headers: {'User-Agent': "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.87 Safari/537.36"}
				}).then(JSON.parse)).product.images.map(({src}) => {
					let questionIndex = src.indexOf('?');
					if(questionIndex === -1) {
						return src;
					} else {
						return src.substring(0, questionIndex);
					}
				}));
				for(let pic of pics) {
					let product_images = (await postgres.query(`select id, case when now() - system_time_start > '30 days' then destination else null end as destination from product_image where product = $1 and source = $2`, [productID, pic])).rows;
					if(product_images.length === 0) {
						let destination;
						if(process.env.CLOUDINARY_URL === 'fake') {
							//https://stackoverflow.com/a/13108449/
							let name = pic.split('/');
							name = name.pop() || name.pop();
							name += name.substring(name.lastIndexOf('.'));
							destination = "https://res.cloudinary.com/the-eternal-tao/image/upload/" + name;

[11.2744]

[11.3867]

				images(
					new Set((await request({
						url: `${url}/products/${e[i][0].handle}.json`,
						headers: {'User-Agent': "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.87 Safari/537.36"}
					}).then(JSON.parse)).product.images.map(({src}) => {
						let questionIndex = src.indexOf('?');
						if(questionIndex === -1) {
							return src;

Replacement in scrape/ocs.ca.js at line 202 [7.11674]

B:BD[11.3883] → [11.3883:4184]

							let response = (await exec('./image.sh ' + pic)).stdout.trim();
							if(response.startsWith('/tmp/')) {
								destination = (await cloudinary.uploader.upload(response, {public_id: response.substring(5), unique_filename: false})).secure_url;
							} else {
								continue;
							}

[11.3883]

[11.4184]

							return src.substring(0, questionIndex);

Replacement in scrape/ocs.ca.js at line 204 [7.11674]

∅:D[11.4193] → [7.18954:19202]

B:BD[7.18954] → [7.18954:19202]

B:BD[7.19202] → [11.4194:4232]

∅:D[11.4232] → [7.19242:19289]

B:BD[7.19242] → [7.19242:19289]

B:BD[7.19289] → [11.4233:4301]

∅:D[11.4301] → [7.19359:19440]

B:BD[7.19359] → [7.19359:19440]

B:BD[7.19440] → [11.4302:5045]

∅:D[11.5045] → [7.19440:19448]

B:BD[7.19440] → [7.19440:19448]

B:BD[7.19825] → [7.19825:19832]

						let img_insert = await return_key(
							`insert into product_image (
								product,
								source,
								destination
							) values (
								$1,
								$2,
								$3
							) on conflict do nothing
							returning ID`,
							[productID, pic, destination]
						);
						if(img_insert.length === 0) {
							console.error("how the FUCK", productID, pic, destination);
						} else if(img_insert.length > 1) {
							console.error('kys');
						}
					} else if(product_images.length === 1) {
						if(product_images[0].destination !== null && process.env.CLOUDINARY_URL !== 'fake') {
							let response = (await exec(`./image.sh ${pic} ${product_images[0].destination}`)).stdout.trim();
							if(response.startsWith('/tmp/')) {
								await postgres.query('update product_image set destination = $1 where id = $2', [
									(await cloudinary.uploader.upload(response, {public_id: response.substring(5), unique_filename: false})).secure_url,
									product_images[0].id
								]);
							}
						}
					} else {
						console.error('data integrity issue', product_images);
						//product match, source match, but destinations are different, which should be unpossible
					}
				}

[11.4193]

[7.19832]

					})),
					productID
				);

Replacement in scrape/ocs.ca.js at line 238 [7.11674]

∅:D[10.6132] → [7.21791:22398]

∅:D[12.885] → [7.21791:22398]

B:BD[7.21791] → [7.21791:22398]

B:BD[7.22398] → [4.4743:4796]

∅:D[4.4796] → [7.22488:23050]

B:BD[7.22488] → [7.22488:23050]

				let variantID = await return_key(
					`insert into variant (
						product,
						portions,
						quantity,
						flavor,
						gram_equivalency
					) values (
						$1, $2, $3, $4, $5
					)
					on conflict do nothing
					returning ID`,
					[productID, portions, quantity, e[i][j].options.flavour, gram_equivalency]
				);
				if(variantID.length === 0) {
					let parameters = [productID, quantity];
					variantID = await return_key(
						`select
							ID,
							gram_equivalency
						from
							variant
						where
							product = $1 and
							quantity = $2 and
							portions = $${parameters.push(portions)} and
							flavor ${e[i][j].options.flavour === undefined ? 'is null' : '= $' + parameters.push(e[i][j].options.flavour)}`,
						parameters
					);
					if(gram_equivalency !== variantID[0][1]) {
						console.log('warning: gram_equivalency does not match', gram_equivalency, variantID[0][1], productID, variantID[0][0], `${url}/products/${e[i][j].handle}?variant=${e[i][j].objectID}`);
					}
					if(variantID.length === 0) {
						console.error("why can't I find the variant???");
						process.exit(1);
					}
				}
				variantID = variantID[0][0];

[12.885]

[7.23050]

				console.log('gram equivalency', gram_equivalency);
				let variantID = (await postgres.query(`select ios_variant_id($1, $2, $3, $4, $5)`, [
					productID,
					portions,
					quantity,
					e[i][j].options.flavour,
					gram_equivalency
				])).rows[0];
				console.log('vid', variantID);// => '(1,)'