camper/deploy/edit_campsite_type.sql

43 lines
2.0 KiB
MySQL
Raw Permalink Normal View History

-- Deploy camper:edit_campsite_type to pg
-- requires: roles
-- requires: schema_camper
-- requires: campsite_type
-- requires: company
-- requires: campsite_type__check_in_out
-- requires: campsite_type__overflow_allowed
-- requires: campsite_type__ask_zone_preferences
begin;
set search_path to camper, public;
drop function if exists edit_campsite_type(uuid, integer, text, text, text, text, text, text, text, text, integer, boolean, boolean, boolean, boolean);
create or replace function edit_campsite_type(slug uuid, media_id integer, name text, spiel text, info text, facilities text, description text, additional_info text, check_in text, check_out text, max_campers integer, bookable_nights int4range, overflow_allowed boolean, ask_zone_preferences boolean, active boolean) returns uuid as
$$
update campsite_type
set name = edit_campsite_type.name
, spiel = xmlparse(content edit_campsite_type.spiel)
, description = xmlparse(content edit_campsite_type.description)
, info = xmlparse(content edit_campsite_type.info)
, additional_info = xmlparse(content edit_campsite_type.additional_info)
, check_in = edit_campsite_type.check_in
, check_out = edit_campsite_type.check_out
, facilities = xmlparse(content edit_campsite_type.facilities)
Add cover media to campsite types This is the image that is shown at the home page, and maybe other pages in the future. We can not use a static file because this image can be changed by the customer, not us; just like name and description. I decided to keep the actual media content in the database, but to copy this file out to the file system the first time it is accessed. This is because we are going to replicate the database to a public instance that must show exactly the same image, but the customer will update the image from the private instance, behind a firewall. We could also synchronize the folder where they upload the images, the same way we will replicate, but i thought that i would make the whole thing a little more brittle: this way if it can replicate the update of the media, it is impossible to not have its contents; dumping it to a file is to improve subsequent requests to the same media. I use the hex representation of the media’s hash as the URL to the resource, because PostgreSQL’s base64 is not URL save (i.e., it uses RFC2045’s charset that includes the forward slash[0]), and i did not feel necessary write a new function just to slightly reduce the URLs’ length. Before checking if the file exists, i make sure that the given hash is an hex string, like i do for UUID, otherwise any other check is going to fail for sure. I moved out hex.Valid function from UUID to check for valid hex values, but the actual hash check is inside app/media because i doubt it will be used outside that module. [0]: https://datatracker.ietf.org/doc/html/rfc2045#section-6.8
2023-09-10 01:04:18 +00:00
, media_id = coalesce(edit_campsite_type.media_id, campsite_type.media_id)
, max_campers = edit_campsite_type.max_campers
, bookable_nights = edit_campsite_type.bookable_nights
, overflow_allowed = edit_campsite_type.overflow_allowed
, ask_zone_preferences = edit_campsite_type.ask_zone_preferences
, active = edit_campsite_type.active
where slug = edit_campsite_type.slug
returning slug;
$$
language sql
;
revoke execute on function edit_campsite_type(uuid, integer, text, text, text, text, text, text, text, text, integer, int4range, boolean, boolean, boolean) from public;
grant execute on function edit_campsite_type(uuid, integer, text, text, text, text, text, text, text, text, integer, int4range, boolean, boolean, boolean) to admin;
commit;