boogerlad/mj - Change CMOE5TZ3HY6BG6KB56HMIJ6BU5J6D3NE7VFK2NSAX2XEXAPEKJQQC

automatically merge adjacent and overlapping ranges for store times instead of erroring out on exclude constraint thanks to range_agg also if close time is less than or equal to open time, don't error out!! that just means loops through to next week, or means always open consider sunday 5pm to monday 12am or 2am 6 * 24 * 60 + 17 * 60 = 9660 to 0 or 120 should be valid! also remove first_last_agg polyfill. if you can't install extensions, you're fucked anyways

Created by boogerlad on June 7, 2020

CMOE5TZ3HY6BG6KB56HMIJ6BU5J6D3NE7VFK2NSAX2XEXAPEKJQQC

Dependencies

In channels

main

Change contents

Replacement in initialize.sql at line 18 [6.17]

∅:D[3.41] → [5.413:479]

∅:D[7.47] → [5.413:479]

B:BD[5.413] → [5.413:479]

B:BD[5.479] → [2.0:145]

∅:D[2.145] → [5.612:656]

B:BD[5.612] → [5.612:656]

B:BD[5.656] → [2.146:278]

∅:D[2.278] → [5.773:841]

B:BD[5.773] → [5.773:841]

B:BD[5.841] → [2.279:423]

∅:D[2.423] → [5.973:1017]

B:BD[5.973] → [5.973:1017]

B:BD[5.1017] → [2.424:554]

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

[3.41]

[8.0]

create extension if not exists range_agg;

Insertion in production.md at line 95 [4.16]

[4.4903]

git clone https://github.com/pjungwir/range_agg.git
make -C range_agg install

Deletion in server.js at line 31 [11.590]

B:BD[9.6296] → [10.4701:4801]

let days_of_week = ['monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday'];

Replacement in server.js at line 334 [11.590]

B:BD[10.6123] → [10.6123:6728]

B:BD[10.6728] → [12.6617:6705]

∅:D[12.6705] → [10.6810:7167]

B:BD[10.6810] → [10.6810:7167]

B:BD[10.7167] → [12.6706:6756]

∅:D[12.6756] → [10.7214:7264]

B:BD[10.7214] → [10.7214:7264]

							if(Array.isArray(parameters.times) && parameters.times.length) {
								let times = parameters.times.map((time, idx) => {
									let ts = time.time_start.split(':');
									ts = Number(time.day_start) * 1440 + Number(ts[0]) * 60 + Number(ts[1]);
									let te = time.time_end.split(':');
									te = Number(time.day_end) * 1440 + Number(te[0]) * 60 + Number(te[1]);
									if(te <= ts) {//
										return `time ${idx}: close time(${days_of_week[time.day_end]} ${time.time_end}) must be later than open time(${days_of_week[time.day_start]} ${time.time_start})`;//
									}//
									return `((select store_id from insert1), $${params.push(`[${ts}, ${te})`)})`;
								});
								let time_errors = times.filter(time => time.startsWith('time'));//
								if(time_errors.length) {//
									ws.send(JSON.stringify({//
										response_ID: request_ID,//
										data: time_errors//
									}));//
									break;//
								}//to overcome postgres error shortcoming
								query += `, insert${++q} as (
									insert into store_time (store_id, open)
									values ${times.join(', ')}
								)`;

[10.6123]

[10.7264]

							if(Array.isArray(parameters.times)) {
								let fuck = [];
								for(let i = 0; i < parameters.times.length; ++i) {
									try {
										let ts = parameters.times[i].time_start.split(':');
										ts = Number(parameters.times[i].day_start) * 1440 + Number(ts[0]) * 60 + Number(ts[1]);
										let te = parameters.times[i].time_end.split(':');
										te = Number(parameters.times[i].day_end) * 1440 + Number(te[0]) * 60 + Number(te[1]);
										if(te <= ts) {
											fuck.push(`(int4range($${params.push(ts)}, 10080)),(int4range(0, $${params.push(te)}))`);
											//oops possibly 0,0 empty range. nvm good job range_agg
										} else {
											fuck.push(`(int4range($${params.push(ts)}, $${params.push(te)}))`);
										}
									} catch(e) {}
								}
								if(fuck.length) {
									query += `, insert${++q} as (
										insert into store_time (store_id, open)
										select store_id, unnest from insert1 cross join (select unnest(range_agg(column1, true, true)) from (values ${fuck.join(',')}) as ex) as idc
									)`;
								}