Created
December 21, 2011 15:30
-
-
Save chanmix51/1506433 to your computer and use it in GitHub Desktop.
product reference generator in pl/pgsql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- PostgreSQL database dump | |
| -- | |
| SET statement_timeout = 0; | |
| SET client_encoding = 'UTF8'; | |
| SET standard_conforming_strings = off; | |
| SET check_function_bodies = false; | |
| SET client_min_messages = warning; | |
| SET escape_string_warning = off; | |
| -- | |
| -- Name: gobai; Type: SCHEMA; Schema: -; Owner: - | |
| -- | |
| CREATE SCHEMA gobai; | |
| SET search_path = gobai, pg_catalog; | |
| -- | |
| -- Name: reference; Type: DOMAIN; Schema: gobai; Owner: - | |
| -- | |
| CREATE DOMAIN reference AS character varying | |
| CONSTRAINT must_be_alphanum CHECK (((VALUE)::text ~ '^[A-Z0-9]{4,}$'::text)); | |
| -- | |
| -- Name: before_insert_on_product(); Type: FUNCTION; Schema: gobai; Owner: - | |
| -- | |
| CREATE FUNCTION before_insert_on_product() RETURNS trigger | |
| LANGUAGE plpgsql | |
| AS $$ | |
| DECLARE | |
| reference VARCHAR; | |
| BEGIN | |
| -- slugification | |
| IF NEW.slug IS NULL THEN | |
| NEW.slug := slugify(NEW.name); | |
| END IF; | |
| -- reference building | |
| reference := generate_reference(NEW.tags); | |
| NEW.reference := reference||substring(to_char(nextval('product_reference_seq'), '0000') FROM 2); | |
| RETURN NEW; | |
| END; | |
| $$; | |
| -- | |
| -- Name: before_insert_on_product_pack(); Type: FUNCTION; Schema: gobai; Owner: - | |
| -- | |
| CREATE FUNCTION before_insert_on_product_pack() RETURNS trigger | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| IF NOT is_composite FROM product p WHERE p.reference = NEW.parent_ref THEN | |
| RAISE 'Product % is not a composite product.', NEW.parent_ref USING ERRCODE = 'integrity_constraint_violation'; | |
| END IF; | |
| RETURN NEW; | |
| END | |
| $$; | |
| -- | |
| -- Name: before_insert_on_product_type(); Type: FUNCTION; Schema: gobai; Owner: - | |
| -- | |
| CREATE FUNCTION before_insert_on_product_type() RETURNS trigger | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| -- if there are all mandatory tags | |
| IF check_mandatory_reference(NEW.tags) THEN | |
| -- reference building | |
| NEW.reference := generate_reference(NEW.tags); | |
| RETURN NEW; | |
| END IF; | |
| RAISE 'Missing mandatory tags in "%".', array_to_string(NEW.tags, ', ') USING ERRCODE = 'integrity_constraint_violation'; | |
| END; | |
| $$; | |
| -- | |
| -- Name: before_insert_or_update_on_buyer(); Type: FUNCTION; Schema: gobai; Owner: - | |
| -- | |
| CREATE FUNCTION before_insert_or_update_on_buyer() RETURNS trigger | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| IF TG_OP = 'UPDATE' THEN | |
| IF NEW.password = OLD.password THEN | |
| RETURN NEW; | |
| END IF; | |
| END IF; | |
| NEW.password := crypt(NEW.password, gen_salt('bf')); | |
| RETURN NEW; | |
| END; | |
| $$; | |
| -- | |
| -- Name: check_mandatory_reference(public.ltree[]); Type: FUNCTION; Schema: gobai; Owner: - | |
| -- | |
| CREATE FUNCTION check_mandatory_reference(tags public.ltree[]) RETURNS boolean | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| RETURN | |
| array_agg(given_tags.tag) @> array_agg(mandatory_tags.tag) | |
| FROM | |
| ( | |
| SELECT DISTINCT | |
| subpath(v.tag, 0, 1) AS tag | |
| FROM | |
| variation v | |
| WHERE | |
| mandatory | |
| ) mandatory_tags, | |
| ( | |
| SELECT DISTINCT | |
| subpath(v.tag, 0 ,1) AS tag | |
| FROM | |
| ( | |
| SELECT | |
| unnest(tags) AS tag | |
| ) v | |
| ) given_tags; | |
| END; | |
| $$; | |
| -- | |
| -- Name: check_one_tag_per_category(public.ltree[]); Type: FUNCTION; Schema: gobai; Owner: - | |
| -- | |
| CREATE FUNCTION check_one_tag_per_category(tags public.ltree[]) RETURNS boolean | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| RETURN NOT EXISTS | |
| ( | |
| SELECT | |
| tags1.tag AS category, | |
| count(*) AS category_count | |
| FROM | |
| ( | |
| SELECT | |
| subpath(p.tag, 0, 1) AS tag | |
| FROM | |
| ( | |
| SELECT | |
| unnest(tags) AS tag | |
| ) p | |
| ) tags1 | |
| GROUP BY | |
| tags1.tag | |
| HAVING | |
| count(*) > 1 | |
| ); | |
| END; | |
| $$; | |
| -- | |
| -- Name: cut_nicely(character varying, integer); Type: FUNCTION; Schema: gobai; Owner: - | |
| -- | |
| CREATE FUNCTION cut_nicely(my_string character varying, my_length integer) RETURNS character varying | |
| LANGUAGE plpgsql | |
| AS $$ | |
| DECLARE | |
| my_pointer INTEGER; | |
| BEGIN | |
| my_pointer := my_length; | |
| WHILE my_pointer < length(my_string) AND transliterate(substr(my_string, my_pointer, 1)) ~* '[a-z]' LOOP | |
| my_pointer := my_pointer + 1; | |
| END LOOP; | |
| RETURN substr(my_string, 1, my_pointer); | |
| END; | |
| $$; | |
| -- | |
| -- Name: generate_reference(public.ltree[]); Type: FUNCTION; Schema: gobai; Owner: - | |
| -- | |
| CREATE FUNCTION generate_reference(tags public.ltree[]) RETURNS text | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| RETURN | |
| array_to_string(array_agg(t.reference), '') | |
| FROM | |
| ( | |
| SELECT | |
| v.reference, | |
| v.tag | |
| FROM | |
| variation v, | |
| ( | |
| SELECT | |
| unnest(tags) AS tag | |
| ) p | |
| WHERE | |
| p.tag = v.tag | |
| ORDER BY | |
| v.rank ASC | |
| ) t | |
| ; | |
| END; | |
| $$; | |
| SET default_tablespace = ''; | |
| SET default_with_oids = false; | |
| -- | |
| -- Name: buyer; Type: TABLE; Schema: gobai; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE buyer ( | |
| email character varying NOT NULL, | |
| password character varying NOT NULL, | |
| first_name character varying, | |
| last_name character varying, | |
| CONSTRAINT buyer_email_check CHECK (is_email(email)) | |
| ); | |
| -- | |
| -- Name: product; Type: TABLE; Schema: gobai; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE product ( | |
| reference reference NOT NULL, | |
| description character varying NOT NULL, | |
| name character varying NOT NULL, | |
| slug character varying, | |
| tags public.ltree[] NOT NULL, | |
| price numeric(5,2) NOT NULL, | |
| stock integer DEFAULT 0 NOT NULL, | |
| image bytea, | |
| is_composite boolean DEFAULT false, | |
| CONSTRAINT check_positive_stock CHECK ((stock >= 0)), | |
| CONSTRAINT product_price_check CHECK ((price > (0)::numeric)) | |
| ); | |
| -- | |
| -- Name: product_pack; Type: TABLE; Schema: gobai; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE product_pack ( | |
| parent_ref reference NOT NULL, | |
| children_ref reference NOT NULL | |
| ); | |
| -- | |
| -- Name: product_reference_seq; Type: SEQUENCE; Schema: gobai; Owner: - | |
| -- | |
| CREATE SEQUENCE product_reference_seq | |
| START WITH 1 | |
| INCREMENT BY 1 | |
| NO MAXVALUE | |
| NO MINVALUE | |
| CACHE 1; | |
| -- | |
| -- Name: product_type; Type: TABLE; Schema: gobai; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE product_type ( | |
| reference reference NOT NULL, | |
| designation character varying NOT NULL, | |
| tags public.ltree[] NOT NULL, | |
| price numeric(5,2) NOT NULL, | |
| image bytea, | |
| CONSTRAINT check_tags CHECK (((tags[1] OPERATOR(public.~) 'bijou.*'::public.lquery) AND (tags[2] OPERATOR(public.~) 'collection.*'::public.lquery))), | |
| CONSTRAINT product_type_price_check CHECK ((price > (0)::numeric)) | |
| ); | |
| -- | |
| -- Name: variation; Type: TABLE; Schema: gobai; Owner: -; Tablespace: | |
| -- | |
| CREATE TABLE variation ( | |
| reference character(2) NOT NULL, | |
| tag public.ltree NOT NULL, | |
| mandatory boolean DEFAULT false, | |
| rank integer | |
| ); | |
| -- | |
| -- Name: buyer_pkey; Type: CONSTRAINT; Schema: gobai; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY buyer | |
| ADD CONSTRAINT buyer_pkey PRIMARY KEY (email); | |
| -- | |
| -- Name: product_pack_pkey; Type: CONSTRAINT; Schema: gobai; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY product_pack | |
| ADD CONSTRAINT product_pack_pkey PRIMARY KEY (parent_ref, children_ref); | |
| -- | |
| -- Name: product_pkey; Type: CONSTRAINT; Schema: gobai; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY product | |
| ADD CONSTRAINT product_pkey PRIMARY KEY (reference); | |
| -- | |
| -- Name: product_type_pkey; Type: CONSTRAINT; Schema: gobai; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY product_type | |
| ADD CONSTRAINT product_type_pkey PRIMARY KEY (reference); | |
| -- | |
| -- Name: variation_pkey; Type: CONSTRAINT; Schema: gobai; Owner: -; Tablespace: | |
| -- | |
| ALTER TABLE ONLY variation | |
| ADD CONSTRAINT variation_pkey PRIMARY KEY (reference); | |
| -- | |
| -- Name: before_insert_on_product_pack_trig; Type: TRIGGER; Schema: gobai; Owner: - | |
| -- | |
| CREATE TRIGGER before_insert_on_product_pack_trig | |
| BEFORE INSERT ON product_pack | |
| FOR EACH ROW | |
| EXECUTE PROCEDURE before_insert_on_product_pack(); | |
| -- | |
| -- Name: before_insert_on_product_trig; Type: TRIGGER; Schema: gobai; Owner: - | |
| -- | |
| CREATE TRIGGER before_insert_on_product_trig | |
| BEFORE INSERT ON product | |
| FOR EACH ROW | |
| EXECUTE PROCEDURE before_insert_on_product(); | |
| -- | |
| -- Name: before_insert_on_product_type_trig; Type: TRIGGER; Schema: gobai; Owner: - | |
| -- | |
| CREATE TRIGGER before_insert_on_product_type_trig | |
| BEFORE INSERT ON product_type | |
| FOR EACH ROW | |
| EXECUTE PROCEDURE before_insert_on_product_type(); | |
| -- | |
| -- Name: before_insert_or_update_on_buyer_trig; Type: TRIGGER; Schema: gobai; Owner: - | |
| -- | |
| CREATE TRIGGER before_insert_or_update_on_buyer_trig | |
| BEFORE INSERT OR UPDATE ON buyer | |
| FOR EACH ROW | |
| EXECUTE PROCEDURE before_insert_or_update_on_buyer(); | |
| -- | |
| -- Name: product_pack_children_ref_fkey; Type: FK CONSTRAINT; Schema: gobai; Owner: - | |
| -- | |
| ALTER TABLE ONLY product_pack | |
| ADD CONSTRAINT product_pack_children_ref_fkey FOREIGN KEY (children_ref) REFERENCES product(reference) ON DELETE RESTRICT; | |
| -- | |
| -- Name: product_pack_parent_ref_fkey; Type: FK CONSTRAINT; Schema: gobai; Owner: - | |
| -- | |
| ALTER TABLE ONLY product_pack | |
| ADD CONSTRAINT product_pack_parent_ref_fkey FOREIGN KEY (parent_ref) REFERENCES product(reference) ON DELETE CASCADE; | |
| -- | |
| -- PostgreSQL database dump complete | |
| -- |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment