Group execution
Action type:	NLS missing message: OperationCommand_action_execute in: org.eclipse.datatools.sqltools.result.PluginResources
Profile name:	csns_PostgreSQL
Database:	cs520stu12
Execute time:	10/1/09 8:20 AM

-- DB Creation Script for CSNS
--
-- $Author: cysun $
-- $Date: 2009-09-25 14:22:27 -0700 (Fri, 25 Sep 2009) $
-- $Revision: 1 $

create sequence hibernate_sequence minvalue 2000000
---------------------
-- users and roles --
---------------------

create table roles (
    id          integer primary key,
    name        varchar(255) not null unique,
    description varchar(4092)
)
create table users (
    id                      integer primary key,
    username                varchar(255) not null unique,
    password                varchar(255) not null,
    enabled                 boolean not null,
    date_created            timestamp not null,
    primary_role            integer not null references roles(id),
    cin                     varchar(255) not null unique,
    cin_encrypted           boolean not null,
    last_name               varchar(255) not null,
    first_name              varchar(255) not null,
    middle_name             varchar(255),
    email                   varchar(255) not null unique,
    address1                varchar(255),
    address2                varchar(255),
    city                    varchar(255),
    state                   varchar(255),
    zip                     varchar(255),
    phone                   varchar(255),
    mft_score               integer,
    mft_date                timestamp,
    show_favorite_forums    boolean not null default 't',
    num_of_forum_posts      integer not null default 0
);

create index user_names_index on users ( lower(last_name||first_name) );

create table authorities (
    user_id     integer not null references users(id),
    role_id     integer not null references roles(id),
  primary key (user_id, role_id)
);

-----------
-- files --
-----------

create table files (
    id          integer primary key,
    name        varchar(255) not null,
    version     integer not null,
    type        varchar(255),
    size        bigint,
    date        timestamp,
    public      boolean not null default 'f',
    owner_id    integer not null references users(id)
);

------------
-- grades --
------------

create table grades (
    id          integer primary key,
    symbol      varchar(255) not null unique,
    value       real,
    description varchar(1022)
);

-----------------------------------------
-- courses, sections, and encrollments --
-----------------------------------------

create table courses (
    id                  integer primary key,
    code                varchar(255) not null,
    name                varchar(255) not null,
    units               integer not null default 4,
    description         integer unique references files(id),
    coordinator_id      integer references users(id),
    is_graduate_course  boolean not null default 'f',
    obsolete            boolean not null default 'f'
);

create table sections (
    id              integer primary key,
    quarter         integer not null,
    course_id       integer not null references courses(id),
    number          integer not null,
  unique (quarter, course_id, number)
);

create table instructors (
    section_id          integer not null references sections(id),
    instructor_id       integer not null references users(id),
    instructor_order    integer not null,
  primary key (section_id, instructor_order)
);

create table enrollments (
    id              integer primary key,
    section_id      integer not null references sections(id),
    student_id      integer not null references users(id),
    grade           integer references grades(id),
    comments        text,
    grade_mailed    boolean not null default 'f',
  unique (section_id, student_id)
);

-----------------
-- assignments --
-----------------

create table assignments (
    id                      integer primary key,
    name                    varchar(255) not null,
    short_name              varchar(255) not null,
    total_points            varchar(255),
    section_id              integer references sections(id),
    due_date                timestamp not null,
    allowed_max_file_size   bigint,
    allowed_file_extensions varchar(255)
);

create table assignment_templates (
    id                      integer primary key,
    course_id               integer references courses(id),
    name                    varchar(255) not null,
    short_name              varchar(255) not null,
    total_points            varchar(255),
    allowed_max_file_size   bigint,
    allowed_file_extensions varchar(255),
    description             varchar(4000),
    creator_id              integer references users(id),
    date                    timestamp default current_timestamp
);

create table submissions (
    id              integer primary key,
    student_id      integer not null references users(id),
    assignment_id   integer not null references assignments(id),
    grade           varchar(255),
    comments        text,
    notes           varchar(8000),
    grade_mailed    boolean not null default 'f'
);

create table submission_files (
    submission_id   integer not null references submissions(id),
    file_id         integer not null unique references files(id),
  primary key (submission_id, file_id)
);

----------------
-- assessment --
----------------

create table course_journals (
    id              integer primary key,
    section_id      integer not null unique references sections(id),
    syllabus        integer unique references files(id),
    submit_date     timestamp,
    approve_date    timestamp
);

create table course_journal_handouts (
    course_journal_id   integer not null references course_journals(id),
    file_id             integer not null references files(id),
    handout_order       integer not null,
  primary key (course_journal_id, handout_order)
);

create table course_journal_assignments (
    course_journal_id   integer not null references course_journals(id),
    file_id             integer not null references files(id),
    assignment_order    integer not null,
  primary key (course_journal_id, assignment_order)
);

create table course_journal_enrollment_samples (
    course_journal_id   integer not null references course_journals(id),
    enrollment_id       integer not null references enrollments(id),
  primary key (course_journal_id, enrollment_id)
);

create table stored_queries (
    id                  integer primary key,
    name                varchar(255) not null unique,
    query               varchar(8000) not null,
    date                timestamp default current_timestamp,
    author_id           integer references users(id),
    chart_title         varchar(255),
    chart_x_axis_label  varchar(255),
    chart_y_axis_label  varchar(255),
    enabled             boolean not null default 'f'
);

create table skills (
    id          integer primary key,
    name        varchar(255) not null unique,
    description varchar(4000)
);

create table skillsets (
    course_id   integer not null references courses(id),
    skill_id    integer not null references skills(id),
  primary key (course_id, skill_id)
);

create table skill_evaluations (
    id          integer primary key,
    section_id  integer references sections(id),
    student_id  integer not null references users(id),
    skill_id    integer not null references skills(id),
    value       integer check( 1 <= value and value <= 5 ),
    removed     boolean not null default 'f',
  unique (section_id, student_id, skill_id)
);

create table mft_assessment_summaries (
    id      integer primary key,
    date    timestamp not null unique,
    ai1     integer,
    ai2     integer,
    ai3     integer,
    deleted boolean not null default 'f'
);

create table mft_distribution_types (
    id          integer primary key,
    name        varchar(255) not null,
    alias       varchar(255) not null unique,
    min         integer not null,
    max         integer not null,
    value_label varchar(255)
);

create table mft_distributions (
    id              integer primary key,
    type_id         integer references mft_distribution_types(id),
    from_date       timestamp,
    to_date         timestamp,
    num_of_samples  integer,
    mean            double precision,
    median          double precision,
    stdev           double precision,
    p5              integer,
    p10             integer,
    p15             integer,
    p20             integer,
    p25             integer,
    p30             integer,
    p35             integer,
    p40             integer,
    p45             integer,
    p50             integer,
    p55             integer,
    p60             integer,
    p65             integer,
    p70             integer,
    p75             integer,
    p80             integer,
    p85             integer,
    p90             integer,
    p95             integer,
    deleted         boolean not null default 'f'
);

-------------
-- surveys --
-------------

create table surveys (
    id              integer primary key,
    name            varchar(255) not null unique,
    description     varchar(4092),
    creator_id      integer not null references users(id),
    create_date     timestamp not null default current_timestamp,
    publish_date    timestamp,
    close_date      timestamp,
    type            integer not null default 0 check( 0 <= type and type <= 2 ),
    enabled         boolean
);

create table survey_question_sections (
    id              integer primary key,
    survey_id       integer references surveys(id),
    section_index   integer,
    description     varchar(4092),
  unique(survey_id, section_index)
);

create table survey_questions (
    id                          integer primary key,
    question_type               varchar(255) not null,
    description                 varchar(4092),
    min_selections              integer,
    max_selections              integer,
    min_rating                  integer,
    max_rating                  integer,
    text_length                 integer,
    survey_question_section_id  integer references survey_question_sections(id),
    container_id                integer references survey_questions(id),
    question_index              integer
);

create table survey_question_choices (
    question_id     integer not null references survey_questions(id),
    choice          varchar(4092),
    choice_index    integer not null,
  primary key (question_id, choice_index)
);

create table completed_surveys (
    id          integer primary key,
    survey_id   integer not null references surveys(id),
    user_id     integer,
    timestamp   timestamp default current_timestamp
);

create table survey_answer_sections (
    id                  integer primary key,
    completed_survey_id integer not null references completed_surveys(id),
    section_index       integer not null,
  unique(completed_survey_id, section_index)
);

create table survey_answers (
    id                          integer primary key,
    answer_type                 varchar(255) not null,
    survey_answer_section_id    integer not null references survey_answer_sections(id),
    answer_index                integer not null,
    question_id                 integer references survey_questions(id),
    rating                      integer,
    text                        varchar(8092),
    container_id                integer references survey_answers(id)
);

create table survey_answer_selections (
    answer_id   integer not null references survey_answers(id),
    selection   integer
);

create table surveys_taken (
    user_id     integer not null references users(id),
    survey_id   integer not null references surveys(id),
  primary key (user_id, survey_id)
);

-------------------
-- subscriptions --
-------------------

create table subscriptions (
    id                  integer primary key,
    subscribable_id     integer not null,
    subscriber_id       integer not null references users(id),
    subscription_type   integer not null default 1,
    date                timestamp not null default current_timestamp,
    status              integer not null default 0,
  unique (subscribable_id, subscriber_id)
);

------------------
-- mailinglists --
------------------

create table mailinglists (
    id          integer primary key,
    name        varchar(255) not null unique,
    description varchar(4092),
    date        timestamp not null default current_timestamp,
    owner_id    integer not null references users(id)
);

create table mailinglist_messages (
    id              integer primary key,
    subject         varchar(255),
    content         varchar(8092),
    date            timestamp,
    author_id       integer not null references users(id),
    mailinglist_id  integer references mailinglists(id)
);

create table mailinglist_message_attachments (
    mailinglist_message_id  integer not null references mailinglist_messages(id),
    file_id                 integer not null unique references files(id),
  primary key (mailinglist_message_id, file_id)
);

alter table mailinglist_messages add column tsv tsvector;

create function mailinglist_messages_ts_trigger_function() returns trigger as $$
declare
    author users%rowtype;
begin
    select * into author from users where id = new.author_id;
    new.tsv :=
        setweight( to_tsvector('pg_catalog.english', coalesce(author.first_name,'')), 'A') ||
        setweight( to_tsvector('pg_catalog.english', coalesce(author.last_name,'')), 'A' ) ||
        setweight( to_tsvector('pg_catalog.english', coalesce(new.subject,'')), 'A') ||
        setweight( to_tsvector('pg_catalog.english', coalesce(new.content,'')), 'D' );
    return new;
end
$$ language plpgsql;

create trigger mailinglist_messages_ts_trigger
    before insert or update
    on mailinglist_messages
    for each row
    execute procedure mailinglist_messages_ts_trigger_function();

create index mailinglist_messages_ts_index
    on mailinglist_messages
    using gin(tsv);

------------
-- forums --
------------

create table forums (
    id                      integer primary key,
    name                    varchar(80) not null unique,
    description             varchar(255),
    date                    timestamp default current_timestamp,
    owner_id                integer references users(id),
    num_of_topics           integer not null default 0,
    num_of_posts            integer not null default 0,
    last_post_id            integer unique,
    course_id               integer unique references courses(id),
    notification_enabled    boolean not null default 't'
);

create table forum_moderators (
    forum_id    integer not null references forums(id),
    user_id     integer not null references users(id),
  primary key (forum_id, user_id)
);

create table forum_favorites (
    user_id     integer not null references users(id),
    forum_id    integer not null references forums(id),
  primary key (user_id, forum_id)
);

create table forum_topics (
    id                      integer primary key,
    pinned                  boolean not null default 'f',
    num_of_views            integer not null default 0,
    first_post_id           integer,
    last_post_id            integer,
    forum_id                integer not null references forums(id),
    notification_enabled    boolean not null default 't'
);

create table forum_posts (
    id          integer primary key,
    subject     varchar(255),
    content     text,
    date        timestamp,
    author_id   integer not null references users(id),
    topic_id    integer references forum_topics(id),
    edited_by   integer references users(id),
    edit_date   timestamp
);

create table forum_post_attachments (
    forum_post_id   integer not null references forum_posts(id),
    file_id         integer not null unique references files(id),
  primary key (forum_post_id, file_id)
);

alter table forums add constraint fk_forum_last_post
    foreign key (last_post_id) references forum_posts(id);

alter table forum_topics add constraint fk_forum_topic_first_post
    foreign key (first_post_id) references forum_posts(id);

alter table forum_topics add constraint fk_forum_topic_last_post 
    foreign key (last_post_id) references forum_posts(id);

alter table forum_posts add column tsv tsvector;

create function forum_posts_ts_trigger_function() returns trigger as $$
declare
    author users%rowtype;
begin
    select * into author from users where id = new.author_id;
    new.tsv :=
        setweight( to_tsvector('pg_catalog.english', coalesce(author.username,'')), 'A') ||
        setweight( to_tsvector('pg_catalog.english', coalesce(new.subject,'')), 'A') ||
        setweight( to_tsvector('pg_catalog.english', coalesce(new.content,'')), 'D' )
return new
end
$$ language plpgsql
create trigger forum_posts_ts_trigger
    before insert or update
    on forum_posts
    for each row
    execute procedure forum_posts_ts_trigger_function()
create index forum_posts_ts_index
    on forum_posts
    using gin(tsv)


Elapsed Time:  0 hr, 0 min, 0 sec, 0 ms.
---------------------------------------------------------------------------------------

	
	=======================================================================================
	-- DB Creation Script for CSNS
	--
	-- $Author: cysun $
	-- $Date: 2009-09-25 14:22:27 -0700 (Fri, 25 Sep 2009) $
	-- $Revision: 1 $
	
	create sequence hibernate_sequence minvalue 2000000
	
	
	
	Elapsed Time:  0 hr, 0 min, 0 sec, 0 ms.
	---------------------------------------------------------------------------------------
	
	(0 rows affected)
		
	=======================================================================================
	---------------------
	-- users and roles --
	---------------------
	
	create table roles (
	    id          integer primary key,
	    name        varchar(255) not null unique,
	    description varchar(4092)
	)
	
	
	
	Elapsed Time:  0 hr, 0 min, 0 sec, 0 ms.
	---------------------------------------------------------------------------------------
	
	(0 rows affected)
		
	=======================================================================================
	create table users (
	    id                      integer primary key,
	    username                varchar(255) not null unique,
	    password                varchar(255) not null,
	    enabled                 boolean not null,
	    date_created            timestamp not null,
	    primary_role            integer not null references roles(id),
	    cin                     varchar(255) not null unique,
	    cin_encrypted           boolean not null,
	    last_name               varchar(255) not null,
	    first_name              varchar(255) not null,
	    middle_name             varchar(255),
	    email                   varchar(255) not null unique,
	    address1                varchar(255),
	    address2                varchar(255),
	    city                    varchar(255),
	    state                   varchar(255),
	    zip                     varchar(255),
	    phone                   varchar(255),
	    mft_score               integer,
	    mft_date                timestamp,
	    show_favorite_forums    boolean not null default 't',
	    num_of_forum_posts      integer not null default 0
	);
	
	create index user_names_index on users ( lower(last_name||first_name) );
	
	create table authorities (
	    user_id     integer not null references users(id),
	    role_id     integer not null references roles(id),
	  primary key (user_id, role_id)
	);
	
	-----------
	-- files --
	-----------
	
	create table files (
	    id          integer primary key,
	    name        varchar(255) not null,
	    version     integer not null,
	    type        varchar(255),
	    size        bigint,
	    date        timestamp,
	    public      boolean not null default 'f',
	    owner_id    integer not null references users(id)
	);
	
	------------
	-- grades --
	------------
	
	create table grades (
	    id          integer primary key,
	    symbol      varchar(255) not null unique,
	    value       real,
	    description varchar(1022)
	);
	
	-----------------------------------------
	-- courses, sections, and encrollments --
	-----------------------------------------
	
	create table courses (
	    id                  integer primary key,
	    code                varchar(255) not null,
	    name                varchar(255) not null,
	    units               integer not null default 4,
	    description         integer unique references files(id),
	    coordinator_id      integer references users(id),
	    is_graduate_course  boolean not null default 'f',
	    obsolete            boolean not null default 'f'
	);
	
	create table sections (
	    id              integer primary key,
	    quarter         integer not null,
	    course_id       integer not null references courses(id),
	    number          integer not null,
	  unique (quarter, course_id, number)
	);
	
	create table instructors (
	    section_id          integer not null references sections(id),
	    instructor_id       integer not null references users(id),
	    instructor_order    integer not null,
	  primary key (section_id, instructor_order)
	);
	
	create table enrollments (
	    id              integer primary key,
	    section_id      integer not null references sections(id),
	    student_id      integer not null references users(id),
	    grade           integer references grades(id),
	    comments        text,
	    grade_mailed    boolean not null default 'f',
	  unique (section_id, student_id)
	);
	
	-----------------
	-- assignments --
	-----------------
	
	create table assignments (
	    id                      integer primary key,
	    name                    varchar(255) not null,
	    short_name              varchar(255) not null,
	    total_points            varchar(255),
	    section_id              integer references sections(id),
	    due_date                timestamp not null,
	    allowed_max_file_size   bigint,
	    allowed_file_extensions varchar(255)
	);
	
	create table assignment_templates (
	    id                      integer primary key,
	    course_id               integer references courses(id),
	    name                    varchar(255) not null,
	    short_name              varchar(255) not null,
	    total_points            varchar(255),
	    allowed_max_file_size   bigint,
	    allowed_file_extensions varchar(255),
	    description             varchar(4000),
	    creator_id              integer references users(id),
	    date                    timestamp default current_timestamp
	);
	
	create table submissions (
	    id              integer primary key,
	    student_id      integer not null references users(id),
	    assignment_id   integer not null references assignments(id),
	    grade           varchar(255),
	    comments        text,
	    notes           varchar(8000),
	    grade_mailed    boolean not null default 'f'
	);
	
	create table submission_files (
	    submission_id   integer not null references submissions(id),
	    file_id         integer not null unique references files(id),
	  primary key (submission_id, file_id)
	);
	
	----------------
	-- assessment --
	----------------
	
	create table course_journals (
	    id              integer primary key,
	    section_id      integer not null unique references sections(id),
	    syllabus        integer unique references files(id),
	    submit_date     timestamp,
	    approve_date    timestamp
	);
	
	create table course_journal_handouts (
	    course_journal_id   integer not null references course_journals(id),
	    file_id             integer not null references files(id),
	    handout_order       integer not null,
	  primary key (course_journal_id, handout_order)
	);
	
	create table course_journal_assignments (
	    course_journal_id   integer not null references course_journals(id),
	    file_id             integer not null references files(id),
	    assignment_order    integer not null,
	  primary key (course_journal_id, assignment_order)
	);
	
	create table course_journal_enrollment_samples (
	    course_journal_id   integer not null references course_journals(id),
	    enrollment_id       integer not null references enrollments(id),
	  primary key (course_journal_id, enrollment_id)
	);
	
	create table stored_queries (
	    id                  integer primary key,
	    name                varchar(255) not null unique,
	    query               varchar(8000) not null,
	    date                timestamp default current_timestamp,
	    author_id           integer references users(id),
	    chart_title         varchar(255),
	    chart_x_axis_label  varchar(255),
	    chart_y_axis_label  varchar(255),
	    enabled             boolean not null default 'f'
	);
	
	create table skills (
	    id          integer primary key,
	    name        varchar(255) not null unique,
	    description varchar(4000)
	);
	
	create table skillsets (
	    course_id   integer not null references courses(id),
	    skill_id    integer not null references skills(id),
	  primary key (course_id, skill_id)
	);
	
	create table skill_evaluations (
	    id          integer primary key,
	    section_id  integer references sections(id),
	    student_id  integer not null references users(id),
	    skill_id    integer not null references skills(id),
	    value       integer check( 1 <= value and value <= 5 ),
	    removed     boolean not null default 'f',
	  unique (section_id, student_id, skill_id)
	);
	
	create table mft_assessment_summaries (
	    id      integer primary key,
	    date    timestamp not null unique,
	    ai1     integer,
	    ai2     integer,
	    ai3     integer,
	    deleted boolean not null default 'f'
	);
	
	create table mft_distribution_types (
	    id          integer primary key,
	    name        varchar(255) not null,
	    alias       varchar(255) not null unique,
	    min         integer not null,
	    max         integer not null,
	    value_label varchar(255)
	);
	
	create table mft_distributions (
	    id              integer primary key,
	    type_id         integer references mft_distribution_types(id),
	    from_date       timestamp,
	    to_date         timestamp,
	    num_of_samples  integer,
	    mean            double precision,
	    median          double precision,
	    stdev           double precision,
	    p5              integer,
	    p10             integer,
	    p15             integer,
	    p20             integer,
	    p25             integer,
	    p30             integer,
	    p35             integer,
	    p40             integer,
	    p45             integer,
	    p50             integer,
	    p55             integer,
	    p60             integer,
	    p65             integer,
	    p70             integer,
	    p75             integer,
	    p80             integer,
	    p85             integer,
	    p90             integer,
	    p95             integer,
	    deleted         boolean not null default 'f'
	);
	
	-------------
	-- surveys --
	-------------
	
	create table surveys (
	    id              integer primary key,
	    name            varchar(255) not null unique,
	    description     varchar(4092),
	    creator_id      integer not null references users(id),
	    create_date     timestamp not null default current_timestamp,
	    publish_date    timestamp,
	    close_date      timestamp,
	    type            integer not null default 0 check( 0 <= type and type <= 2 ),
	    enabled         boolean
	);
	
	create table survey_question_sections (
	    id              integer primary key,
	    survey_id       integer references surveys(id),
	    section_index   integer,
	    description     varchar(4092),
	  unique(survey_id, section_index)
	);
	
	create table survey_questions (
	    id                          integer primary key,
	    question_type               varchar(255) not null,
	    description                 varchar(4092),
	    min_selections              integer,
	    max_selections              integer,
	    min_rating                  integer,
	    max_rating                  integer,
	    text_length                 integer,
	    survey_question_section_id  integer references survey_question_sections(id),
	    container_id                integer references survey_questions(id),
	    question_index              integer
	);
	
	create table survey_question_choices (
	    question_id     integer not null references survey_questions(id),
	    choice          varchar(4092),
	    choice_index    integer not null,
	  primary key (question_id, choice_index)
	);
	
	create table completed_surveys (
	    id          integer primary key,
	    survey_id   integer not null references surveys(id),
	    user_id     integer,
	    timestamp   timestamp default current_timestamp
	);
	
	create table survey_answer_sections (
	    id                  integer primary key,
	    completed_survey_id integer not null references completed_surveys(id),
	    section_index       integer not null,
	  unique(completed_survey_id, section_index)
	);
	
	create table survey_answers (
	    id                          integer primary key,
	    answer_type                 varchar(255) not null,
	    survey_answer_section_id    integer not null references survey_answer_sections(id),
	    answer_index                integer not null,
	    question_id                 integer references survey_questions(id),
	    rating                      integer,
	    text                        varchar(8092),
	    container_id                integer references survey_answers(id)
	);
	
	create table survey_answer_selections (
	    answer_id   integer not null references survey_answers(id),
	    selection   integer
	);
	
	create table surveys_taken (
	    user_id     integer not null references users(id),
	    survey_id   integer not null references surveys(id),
	  primary key (user_id, survey_id)
	);
	
	-------------------
	-- subscriptions --
	-------------------
	
	create table subscriptions (
	    id                  integer primary key,
	    subscribable_id     integer not null,
	    subscriber_id       integer not null references users(id),
	    subscription_type   integer not null default 1,
	    date                timestamp not null default current_timestamp,
	    status              integer not null default 0,
	  unique (subscribable_id, subscriber_id)
	);
	
	------------------
	-- mailinglists --
	------------------
	
	create table mailinglists (
	    id          integer primary key,
	    name        varchar(255) not null unique,
	    description varchar(4092),
	    date        timestamp not null default current_timestamp,
	    owner_id    integer not null references users(id)
	);
	
	create table mailinglist_messages (
	    id              integer primary key,
	    subject         varchar(255),
	    content         varchar(8092),
	    date            timestamp,
	    author_id       integer not null references users(id),
	    mailinglist_id  integer references mailinglists(id)
	);
	
	create table mailinglist_message_attachments (
	    mailinglist_message_id  integer not null references mailinglist_messages(id),
	    file_id                 integer not null unique references files(id),
	  primary key (mailinglist_message_id, file_id)
	);
	
	alter table mailinglist_messages add column tsv tsvector;
	
	create function mailinglist_messages_ts_trigger_function() returns trigger as $$
	declare
	    author users%rowtype;
	begin
	    select * into author from users where id = new.author_id;
	    new.tsv :=
	        setweight( to_tsvector('pg_catalog.english', coalesce(author.first_name,'')), 'A') ||
	        setweight( to_tsvector('pg_catalog.english', coalesce(author.last_name,'')), 'A' ) ||
	        setweight( to_tsvector('pg_catalog.english', coalesce(new.subject,'')), 'A') ||
	        setweight( to_tsvector('pg_catalog.english', coalesce(new.content,'')), 'D' );
	    return new;
	end
	$$ language plpgsql;
	
	create trigger mailinglist_messages_ts_trigger
	    before insert or update
	    on mailinglist_messages
	    for each row
	    execute procedure mailinglist_messages_ts_trigger_function();
	
	create index mailinglist_messages_ts_index
	    on mailinglist_messages
	    using gin(tsv);
	
	------------
	-- forums --
	------------
	
	create table forums (
	    id                      integer primary key,
	    name                    varchar(80) not null unique,
	    description             varchar(255),
	    date                    timestamp default current_timestamp,
	    owner_id                integer references users(id),
	    num_of_topics           integer not null default 0,
	    num_of_posts            integer not null default 0,
	    last_post_id            integer unique,
	    course_id               integer unique references courses(id),
	    notification_enabled    boolean not null default 't'
	);
	
	create table forum_moderators (
	    forum_id    integer not null references forums(id),
	    user_id     integer not null references users(id),
	  primary key (forum_id, user_id)
	);
	
	create table forum_favorites (
	    user_id     integer not null references users(id),
	    forum_id    integer not null references forums(id),
	  primary key (user_id, forum_id)
	);
	
	create table forum_topics (
	    id                      integer primary key,
	    pinned                  boolean not null default 'f',
	    num_of_views            integer not null default 0,
	    first_post_id           integer,
	    last_post_id            integer,
	    forum_id                integer not null references forums(id),
	    notification_enabled    boolean not null default 't'
	);
	
	create table forum_posts (
	    id          integer primary key,
	    subject     varchar(255),
	    content     text,
	    date        timestamp,
	    author_id   integer not null references users(id),
	    topic_id    integer references forum_topics(id),
	    edited_by   integer references users(id),
	    edit_date   timestamp
	);
	
	create table forum_post_attachments (
	    forum_post_id   integer not null references forum_posts(id),
	    file_id         integer not null unique references files(id),
	  primary key (forum_post_id, file_id)
	);
	
	alter table forums add constraint fk_forum_last_post
	    foreign key (last_post_id) references forum_posts(id);
	
	alter table forum_topics add constraint fk_forum_topic_first_post
	    foreign key (first_post_id) references forum_posts(id);
	
	alter table forum_topics add constraint fk_forum_topic_last_post 
	    foreign key (last_post_id) references forum_posts(id);
	
	alter table forum_posts add column tsv tsvector;
	
	create function forum_posts_ts_trigger_function() returns trigger as $$
	declare
	    author users%rowtype;
	begin
	    select * into author from users where id = new.author_id;
	    new.tsv :=
	        setweight( to_tsvector('pg_catalog.english', coalesce(author.username,'')), 'A') ||
	        setweight( to_tsvector('pg_catalog.english', coalesce(new.subject,'')), 'A') ||
	        setweight( to_tsvector('pg_catalog.english', coalesce(new.content,'')), 'D' )
	
	ERROR: unterminated dollar-quoted string at or near "$$
	declare
	    author users%rowtype;
	begin
	    select * into author from users where id = new.author_id;
	    new.tsv :=
	        setweight( to_tsvector('pg_catalog.english', coalesce(author.username,'')), 'A') ||
	        setweight( to_tsvector('pg_catalog.english', coalesce(new.subject,'')), 'A') ||
	        setweight( to_tsvector('pg_catalog.english', coalesce(new.content,'')), 'D' )"
	  Position: 74
	
	
	Elapsed Time:  0 hr, 0 min, 0 sec, 0 ms.
	---------------------------------------------------------------------------------------
	
		
	=======================================================================================
	return new
	
	ERROR: syntax error at or near "return"
	  Position: 1
	
	
	Elapsed Time:  0 hr, 0 min, 0 sec, 0 ms.
	---------------------------------------------------------------------------------------
	
		
	=======================================================================================
	end
	$$ language plpgsql
	
	ERROR: unterminated dollar-quoted string at or near "$$ language plpgsql"
	  Position: 6
	
	
	Elapsed Time:  0 hr, 0 min, 0 sec, 0 ms.
	---------------------------------------------------------------------------------------
	
		
	=======================================================================================
	create trigger forum_posts_ts_trigger
	    before insert or update
	    on forum_posts
	    for each row
	    execute procedure forum_posts_ts_trigger_function()
	
	ERROR: relation "forum_posts" does not exist
	
	
	Elapsed Time:  0 hr, 0 min, 0 sec, 0 ms.
	---------------------------------------------------------------------------------------
	
		
	=======================================================================================
	create index forum_posts_ts_index
	    on forum_posts
	    using gin(tsv)
	
	ERROR: relation "forum_posts" does not exist
	
	
	Elapsed Time:  0 hr, 0 min, 0 sec, 0 ms.
	---------------------------------------------------------------------------------------