This is a larger extract of my db schema
CREATE OR REPLACE FUNCTION manage_object() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE object_entity_name TEXT; begin IF (TG_OP = 'INSERT') THEN IF NEW.id IS NULL THEN insert into object (entity_name) values (TG_RELNAME) RETURNING id INTO NEW.id; ELSE select entity_name into object_entity_name from object where id=NEW.id; IF FOUND THEN IF EXISTS (select * from information_schema.tables where table_catalog=CURRENT_CATALOG and table_schema=CURRENT_SCHEMA and table_name=object_entity_name) THEN EXECUTE 'select * from ' || object_entity_name || ' where id=$1' USING NEW.id; IF FOUND THEN RAISE EXCEPTION 'The object id is alreasy assigned to another object.'; END If; ELSE IF (object_entity_name != TG_RELNAME) THEN update object set entity_name=TG_RELNAME where id=NEW.id; END IF; END IF; ELSE insert into object (id, entity_name) values (NEW.id, TG_RELNAME); END IF; END IF; RETURN NEW; ELSIF (TG_OP = 'UPDATE') THEN update object set id=NEW.id, entity_name=TG_RELNAME where id=OLD.id; return NEW; ELSIF (TG_OP = 'DELETE') THEN delete from object where id=OLD.id; return OLD; END IF; end; $$; CREATE OR REPLACE FUNCTION manage_actor() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE actor_entity_name TEXT; begin IF (TG_OP = 'INSERT') THEN IF NEW.id IS NULL THEN insert into actor (entity_name) values (TG_RELNAME) RETURNING id INTO NEW.id; ELSE select entity_name into actor_entity_name from actor where id=NEW.id; IF FOUND THEN IF EXISTS (select * from information_schema.tables where table_catalog=CURRENT_CATALOG and table_schema=CURRENT_SCHEMA and table_name=actor_entity_name) THEN EXECUTE 'select * from ' || actor_entity_name || ' where id=$1' USING NEW.id; IF FOUND THEN RAISE EXCEPTION 'The actor id is alreasy assigned to another actor.'; END If; ELSE IF (actor_entity_name != TG_RELNAME) THEN update actor set entity_name=TG_RELNAME where id=NEW.id; END IF; END IF; ELSE insert into actor (id, entity_name) values (NEW.id, TG_RELNAME); END IF; END IF; RETURN NEW; ELSIF (TG_OP = 'UPDATE') THEN update actor set id=NEW.id, entity_name=TG_RELNAME where id=OLD.id; return NEW; ELSIF (TG_OP = 'DELETE') THEN delete from actor where id=OLD.id; return OLD; END IF; end; $$; CREATE TABLE module ( name varchar(100), title varchar(255) NOT NULL, description varchar(255) NOT NULL, status smallint not null, PRIMARY KEY (name) ); CREATE TABLE object ( id serial, entity_name varchar(255) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE actor ( id serial, entity_name varchar(255) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE email_address ( email_address varchar(50) NOT NULL, actor_id integer NOT NULL, status smallint NOT NULL, create_time timestamp NOT NULL, update_time timestamp DEFAULT NULL, PRIMARY KEY (email_address) ); alter table email_address add constraint fk_email_address_actor foreign key (actor_id) references actor(id) on delete cascade on update cascade; CREATE TABLE email_address_confirmation ( email_address varchar(50) NOT NULL, code varchar(32) NOT NULL, create_time timestamp NOT NULL, update_time timestamp DEFAULT NULL, PRIMARY KEY (email_address) ); alter table email_address_confirmation add constraint fk_email_address_confirmation_email_address foreign key (email_address) references email_address(email_address) on delete cascade on update cascade; CREATE TABLE person ( id integer not null, email_address varchar(50) NOT NULL, username varchar(32) DEFAULT NULL, password varchar(32) NOT NULL, status smallint NOT NULL, create_ip varchar(39) NOT NULL, create_time timestamp NOT NULL, update_time timestamp DEFAULT NULL, UNIQUE (email_address), UNIQUE (username), PRIMARY KEY (id) ); alter table person add constraint fk_actor foreign key (id) references actor(id) on delete cascade on update cascade; create table person_profile ( person_id integer not null, primary key(person_id) ); alter table person_profile add constraint fk_person_profile_person foreign key (person_id) references person(id) on delete cascade on update cascade; CREATE TRIGGER manage_person BEFORE INSERT OR UPDATE OR DELETE ON person FOR EACH ROW EXECUTE PROCEDURE manage_actor(); CREATE TABLE person_password_reset ( person_id integer NOT NULL, code varchar(32) NOT NULL, create_time timestamp NOT NULL, update_time timestamp DEFAULT NULL, PRIMARY KEY (person_id) ); alter table person_password_reset add constraint fk_person_password_reset_person foreign key (person_id) references person(id) on delete cascade on update cascade; CREATE TABLE contact_group ( id serial, person_id integer NOT NULL, name varchar(255) not null, create_time timestamp NOT NULL, update_time timestamp default NULL, PRIMARY KEY (id), UNIQUE (person_id,name) ); alter table contact_group add constraint fk_contact_group_person foreign key (person_id) references person(id) on delete cascade on update cascade; CREATE TABLE contact ( id serial, person_id integer NOT NULL, person2_id integer NOT NULL, status smallint not null, create_time timestamp NOT NULL, PRIMARY KEY (id), UNIQUE (person_id,person2_id) ); alter table contact add constraint fk_contact_person foreign key (person_id) references person(id) on delete cascade on update cascade, add constraint fk_contact_person2 foreign key (person2_id) references person(id) on delete cascade on update cascade; CREATE TABLE contact_group_contact ( contact_group_id integer NOT NULL, contact_id integer not null, PRIMARY KEY (contact_group_id, contact_id) ); alter table contact_group_contact add constraint fk_contact_group_contact_contact foreign key (contact_id) references contact(id) on delete cascade on update cascade, add constraint fk_contact_group_contact_contact_group foreign key (contact_group_id) references contact_group(id) on delete cascade on update cascade; CREATE TABLE activity_type ( type varchar(50) NOT NULL, module_name varchar(100) not null, template varchar(255) default null, create_time timestamp NOT NULL, update_time timestamp DEFAULT NULL, PRIMARY KEY (type) ); alter table activity_type add constraint fk_activity_type_module foreign key (module_name) references module(name) on delete cascade on update cascade; CREATE TABLE activity ( id serial, actor_id integer NOT NULL, activity_type varchar(50) NOT NULL, create_time timestamp NOT NULL, update_time timestamp DEFAULT NULL, PRIMARY KEY (id) ); alter table activity add constraint fk_activity_activity_type foreign key (activity_type) references activity_type(type) on delete cascade on update cascade, add constraint fk_activity_actor foreign key (actor_id) references actor(id) on delete cascade on update cascade; CREATE TABLE activity_object ( activity_id integer NOT NULL, object_id integer NOT NULL, PRIMARY KEY (activity_id,object_id) ); alter table activity_object add constraint fk_activity_object_activity foreign key (activity_id) references activity(id) on delete cascade on update cascade, add constraint fk_activity_object_object foreign key (object_id) references object(id) on delete cascade on update cascade; CREATE TABLE activity_param ( activity_id integer NOT NULL, name varchar(50) NOT NULL, value varchar(255) NOT NULL, PRIMARY KEY (activity_id,name) ); alter table activity_param add constraint fk_activity_param_activity foreign key (activity_id) references activity(id) on delete cascade on update cascade; CREATE TABLE activity_setting ( id serial, activity_type varchar(50) NOT NULL, actor_id integer not null, publish smallint not null, UNIQUE (activity_type, actor_id), PRIMARY KEY (id) ); alter table activity_setting add constraint fk_activity_setting_activity_type foreign key (activity_type) references activity_type(type) on delete cascade on update cascade, add constraint fk_activity_setting_actor foreign key (actor_id) references actor(id) on delete cascade on update cascade; CREATE TABLE comment ( id integer not null, actor_id integer NOT NULL, object_id integer NOT NULL, content text NOT NULL, create_time timestamp NOT NULL, update_time timestamp DEFAULT NULL, PRIMARY KEY (id) ); alter table comment add constraint fk_comment_actor foreign key (actor_id) references actor(id) on delete cascade on update cascade, add constraint fk_comment_object foreign key (object_id) references object(id) on delete cascade on update cascade, add constraint fk_comment foreign key (id) references object(id) on delete cascade on update cascade; CREATE TRIGGER manage_comment BEFORE INSERT OR UPDATE OR DELETE ON comment FOR EACH ROW EXECUTE PROCEDURE manage_object(); create table conversation ( id serial, subject varchar(255) not null, create_time timestamp not null, update_time timestamp DEFAULT null, primary key(id) ); create table conversation_participant ( conversation_id integer not null, actor_id integer not null, status smallint not null, primary key(conversation_id, actor_id) ); alter table conversation_participant add constraint fk_conversation_participant_conversation foreign key (conversation_id) references conversation (id) on delete cascade on update cascade, add constraint fk_conversation_participant_actor foreign key (actor_id) references actor(id) on delete cascade on update cascade; create table conversation_message ( id serial, conversation_id integer not null, actor_id integer not null, content text not null, create_time timestamp not null, update_time timestamp DEFAULT null, primary key(id) ); alter table conversation_message add constraint fk_conversation_message_conversation foreign key(conversation_id) references conversation(id) on delete cascade on update cascade, add constraint fk_conversation_message_recipient foreign key (actor_id) references actor(id) on delete cascade on update cascade; create table conversation_message_recipient ( message_id integer not null, actor_id integer not null, status smallint not null, primary key(message_id, actor_id) ); alter table conversation_message_recipient add constraint fk_conversation_message_recipient_conversation_message foreign key (message_id) references conversation_message(id) on delete cascade on update cascade, add constraint fk_conversation_message_recipient_actor foreign key (actor_id) references actor(id) on delete cascade on update cascade; create table conversation_message_object ( message_id integer not null, object_id integer not null, primary key(message_id, object_id) ); alter table conversation_message_object add constraint fk_conversation_message_object_conversation_message foreign key (message_id) references conversation_message(id) on delete cascade on update cascade, add constraint fk_conversation_message_object_object foreign key (object_id) references object(id) on delete cascade on update cascade; CREATE TABLE object_like ( id integer not null, actor_id integer NOT NULL, object_id integer NOT NULL, type smallint not null, create_time timestamp NOT NULL, update_time timestamp default NULL, PRIMARY KEY (id), UNIQUE (actor_id,object_id) ); alter table object_like add constraint fk_object_like_actor foreign key (actor_id) references actor(id) on delete cascade on update cascade, add constraint fk_object_like_object foreign key (object_id) references object(id) on delete cascade on update cascade, add constraint fk_object_like foreign key (id) references object(id) on delete cascade on update cascade; CREATE TRIGGER manage_object_like BEFORE INSERT OR UPDATE OR DELETE ON object_like FOR EACH ROW EXECUTE PROCEDURE manage_object(); CREATE TABLE mention ( id integer not null, actor_id integer NOT NULL, object_id integer NOT NULL, create_time timestamp NOT NULL, update_time timestamp default NULL, PRIMARY KEY (id), UNIQUE (actor_id,object_id) ); alter table mention add constraint fk_mention_actor foreign key (actor_id) references actor(id) on delete cascade on update cascade, add constraint fk_mention_object foreign key (object_id) references object(id) on delete cascade on update cascade, add constraint fk_mention foreign key (id) references object(id) on delete cascade on update cascade; CREATE TRIGGER manage_mention BEFORE INSERT OR UPDATE OR DELETE ON mention FOR EACH ROW EXECUTE PROCEDURE manage_object(); CREATE TABLE link ( id integer not null, actor_id integer NOT NULL, title varchar(255) NOT NULL, url varchar(255) NOT NULL, summary text, create_time timestamp NOT NULL, update_time timestamp DEFAULT NULL, PRIMARY KEY (id), UNIQUE (actor_id,title,url) ); alter table link add constraint fk_link_actor foreign key (actor_id) references actor(id) on delete cascade on update cascade, add constraint fk_link foreign key (id) references object(id) on delete cascade on update cascade; CREATE TRIGGER manage_link BEFORE INSERT OR UPDATE OR DELETE ON link FOR EACH ROW EXECUTE PROCEDURE manage_object(); CREATE TABLE notification_type ( type varchar(50) NOT NULL, module_name varchar(100) not null, template varchar(255) default null, create_time timestamp NOT NULL, update_time timestamp DEFAULT NULL, PRIMARY KEY (type) ); alter table notification_type add constraint fk_notification_type_module foreign key (module_name) references module(name) on delete cascade on update cascade; CREATE TABLE notification ( id serial, target_id integer NOT NULL, object_id integer default NULL, notification_type varchar(50) NOT NULL, status smallint NOT NULL, create_time timestamp NOT NULL, update_time timestamp DEFAULT NULL, PRIMARY KEY (id) ); alter table notification add constraint fk_notification_target foreign key (target_id) references actor(id) on delete cascade on update cascade, add constraint fk_notification_object foreign key (object_id) references object(id) on delete cascade on update cascade, add constraint fk_notification_notification_type foreign key (notification_type) references notification_type(type) on delete cascade on update cascade; CREATE TABLE notification_subject ( notification_id integer NOT NULL, actor_id integer NOT NULL, PRIMARY KEY (notification_id,actor_id) ); alter table notification_subject add constraint fk_notification_subject_notification foreign key (notification_id) references notification(id) on delete cascade on update cascade, add constraint fk_notification_subject_actor foreign key (actor_id) references actor(id) on delete cascade on update cascade; CREATE TABLE notification_param ( notification_id integer NOT NULL, name varchar(50) NOT NULL, value varchar(255) NOT NULL, PRIMARY KEY (notification_id,name) ); alter table notification_param add constraint fk_notification_param_notification foreign key (notification_id) references notification(id) on delete cascade on update cascade; CREATE TABLE notification_setting ( id serial, notification_type varchar(50) NOT NULL, actor_id integer not null, status smallint not null, UNIQUE (notification_type, actor_id), PRIMARY KEY (id) ); alter table notification_setting add constraint fk_notification_setting_notification_type foreign key (notification_type) references notification_type(type) on delete cascade on update cascade, add constraint fk_notification_setting_actor foreign key (actor_id) references actor(id) on delete cascade on update cascade; CREATE TABLE post ( id integer not null, actor_id integer NOT NULL, target_actor_id integer default NULL, target_object_id integer default NULL, content text NOT NULL, create_time timestamp NOT NULL, update_time timestamp DEFAULT NULL, PRIMARY KEY (id) ); alter table post add constraint fk_post_actor foreign key (actor_id) references actor(id) on delete cascade on update cascade, add constraint fk_post_target_actor foreign key (target_actor_id) references actor(id) on delete cascade on update cascade, add constraint fk_post_target_object foreign key (target_object_id) references object(id) on delete cascade on update cascade, add constraint fk_post foreign key (id) references object(id) on delete cascade on update cascade; CREATE TRIGGER manage_post BEFORE INSERT OR UPDATE OR DELETE ON post FOR EACH ROW EXECUTE PROCEDURE manage_object(); CREATE TABLE post_object ( post_id integer NOT NULL, object_id integer NOT NULL, PRIMARY KEY (post_id,object_id) ); alter table post_object add constraint fk_post_object_post foreign key (post_id) references post(id) on delete cascade on update cascade, add constraint fk_post_object_object foreign key (object_id) references object(id) on delete cascade on update cascade; CREATE TABLE share ( id integer not null, actor_id integer NOT NULL, object_id integer NOT NULL, content text default null, create_time timestamp NOT NULL, update_time timestamp DEFAULT NULL, PRIMARY KEY (id) ); alter table share add constraint fk_share_actor foreign key (actor_id) references actor(id) on delete cascade on update cascade, add constraint fk_share_object foreign key (object_id) references object(id) on delete cascade on update cascade, add constraint fk_share foreign key (id) references object(id) on delete cascade on update cascade; CREATE TRIGGER manage_share BEFORE INSERT OR UPDATE OR DELETE ON share FOR EACH ROW EXECUTE PROCEDURE manage_object(); -- If you want to report a vulnerability issue on symfony, please send it to security at symfony-project.com You received this message because you are subscribed to the Google Groups "symfony developers" group. To post to this group, send email to symfony-devs@googlegroups.com To unsubscribe from this group, send email to symfony-devs+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/symfony-devs?hl=en
