view release on metacpan or  search on metacpan

Examples/helloworld/create_tables.sql  view on Meta::CPAN

ALTER TABLE ONLY errors
    ADD CONSTRAINT rbserrors_pk PRIMARY KEY (error_id);



ALTER TABLE ONLY users
    ADD CONSTRAINT rbsusers_pk PRIMARY KEY (username);



ALTER TABLE ONLY users_settings
    ADD CONSTRAINT rbsusers_settings_pk PRIMARY KEY (username, name);




CREATE INDEX documents_detxtidx ON documents USING gin (german_tsearch);



CREATE INDEX documents_entxtidx ON documents USING gin (english_tsearch);




CREATE INDEX documents_idx1 ON documents USING btree (username, is_public);



CREATE INDEX documents_idx2 ON documents USING btree (filename);


CREATE TRIGGER documents_search_update
    BEFORE INSERT OR UPDATE ON documents
    FOR EACH ROW
    EXECUTE PROCEDURE documents_search_trigger();



ALTER TABLE ONLY documents
    ADD CONSTRAINT "documents/users" FOREIGN KEY (username) REFERENCES users(username) ON UPDATE CASCADE ON DELETE CASCADE;




ALTER TABLE ONLY users_settings
    ADD CONSTRAINT rbsusers_settings_fk1 FOREIGN KEY (username) REFERENCES users(username) ON UPDATE CASCADE ON DELETE CASCADE;



REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


CREATE TABLE memcachedb
(
  mckey text NOT NULL,
  mcdata text NOT NULL,
  CONSTRAINT memcachedb_pk PRIMARY KEY (mckey) USING INDEX TABLESPACE "RBS_INDEX1"
)
WITH (
  FILLFACTOR=20, 
  OIDS=FALSE
)
TABLESPACE "RBS_DATA";
ALTER TABLE memcachedb OWNER TO "MAPLAT_Server";


CREATE OR REPLACE FUNCTION merge_memcachedb(mc_key text, mc_data text)
  RETURNS void AS
$BODY$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE memcachedb SET mcdata = mc_data WHERE mckey = mc_key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO memcachedb (mckey, mcdata) VALUES (mc_key, mc_data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION merge_memcachedb(text, text) OWNER TO "MAPLAT_Server";