Wcp Schema

Here's a minimally edited PostgreSql pg_dump of the database schema behind WikiChangeProposal.

Comments: surprisingly I need only 6 tables. I will only add one in the current iteration some form of page_links(referrer, referred) where pages are probably referred by the full key in wiki_pages_versions. There will always be the team "0" that will not be editable and the user "admin". The team "o" represents the RawMaterialsWiki?. The current model is based on inclusion, a team specifies the members part of it, I'm thinking of maybe another table for exclusion based team (meaning all users can edit a version except those explicitly denied).

The tuple (page_name, team, version) is extracted from the database and use to retrieve the actual content from a module called WikiStorage that abstracts how page content is stored.

---

 CREATE TABLE wiki_pages (
    page_name character varying(200) NOT NULL
 );

 CREATE TABLE wiki_teams (
    team_name character varying(100) NOT NULL,
    team_creator character varying(100) NOT NULL
 );

 CREATE TABLE wiki_pages_versions (
    page_name character varying(100) NOT NULL,
    team_name character varying(100) NOT NULL,
    last_version integer NOT NULL
 );


 CREATE TABLE wiki_users (
    username character varying(100) NOT NULL,
    is_anonymous boolean DEFAULT false NOT NULL -- anonymous means IP or hostname is stored as username
 );


 CREATE TABLE team_membership (
    team_name character varying(100) NOT NULL,
    username character varying(100) NOT NULL
 );


 CREATE TABLE change_log (
    page_name character varying(100) NOT NULL,
    team_name character varying(100) NOT NULL,
    username character varying(100) NOT NULL,
    version integer NOT NULL,
    change_date timestamp without time zone NOT NULL,
    change_comment character varying(200)
 );

 ALTER TABLE ONLY change_log
    ADD CONSTRAINT change_log_pk PRIMARY KEY (page_name, team_name, username, version);



 ALTER TABLE ONLY team_membership
    ADD CONSTRAINT team_membership_pk PRIMARY KEY (team_name, username);


 ALTER TABLE ONLY wiki_pages
    ADD CONSTRAINT wiki_pages_pk PRIMARY KEY (page_name);


 ALTER TABLE ONLY wiki_pages_versions
    ADD CONSTRAINT wiki_pages_versions_pk PRIMARY KEY (page_name, team_name);


 ALTER TABLE ONLY wiki_teams
    ADD CONSTRAINT wiki_teams_pk PRIMARY KEY (team_name);




 ALTER TABLE ONLY wiki_users
    ADD CONSTRAINT wiki_users_pk PRIMARY KEY (username);


 ALTER TABLE ONLY change_log
    ADD CONSTRAINT change_log_fk1 FOREIGN KEY (team_name) REFERENCES wiki_teams(team_name) ON UPDATE RESTRICT ON DELETE RESTRICT;

 ALTER TABLE ONLY change_log
    ADD CONSTRAINT change_log_fk2 FOREIGN KEY (page_name) REFERENCES wiki_pages(page_name) ON UPDATE RESTRICT ON DELETE RESTRICT;


 ALTER TABLE ONLY change_log
    ADD CONSTRAINT change_log_fk3 FOREIGN KEY (username) REFERENCES wiki_users(username) ON UPDATE RESTRICT ON DELETE RESTRICT;


 ALTER TABLE ONLY team_membership
    ADD CONSTRAINT team_membership_fk1 FOREIGN KEY (team_name) REFERENCES wiki_teams(team_name) ON UPDATE CASCADE ON DELETE CASCADE;


 ALTER TABLE ONLY team_membership
    ADD CONSTRAINT team_membership_fk2 FOREIGN KEY (username) REFERENCES wiki_users(username) ON UPDATE CASCADE ON DELETE CASCADE;

 ALTER TABLE ONLY wiki_pages_versions
    ADD CONSTRAINT wiki_pages_versions_fk1 FOREIGN KEY (page_name) REFERENCES wiki_pages(page_name) ON UPDATE CASCADE ON DELETE CASCADE;


 ALTER TABLE ONLY wiki_pages_versions
    ADD CONSTRAINT wiki_pages_versions_fk2 FOREIGN KEY (team_name) REFERENCES wiki_teams(team_name) ON UPDATE CASCADE ON DELETE CASCADE;

 ALTER TABLE ONLY wiki_teams
    ADD CONSTRAINT wiki_teams_fk1 FOREIGN KEY (team_creator) REFERENCES wiki_users(username) ON UPDATE RESTRICT ON DELETE RESTRICT;

---

CategoryWikiChangeProposal