From owner-svn-soc-all@FreeBSD.ORG Wed Aug 1 21:47:55 2012 Return-Path: Delivered-To: svn-soc-all@FreeBSD.org Received: from socsvn.FreeBSD.org (unknown [IPv6:2001:4f8:fff6::2f]) by hub.freebsd.org (Postfix) with SMTP id D6109106564A for ; Wed, 1 Aug 2012 21:47:52 +0000 (UTC) (envelope-from tzabal@FreeBSD.org) Received: by socsvn.FreeBSD.org (sSMTP sendmail emulation); Wed, 01 Aug 2012 21:47:52 +0000 Date: Wed, 01 Aug 2012 21:47:52 +0000 From: tzabal@FreeBSD.org To: svn-soc-all@FreeBSD.org MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Message-Id: <20120801214752.D6109106564A@hub.freebsd.org> Cc: Subject: socsvn commit: r240001 - soc2012/tzabal/server-side/akcrs-setup X-BeenThere: svn-soc-all@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: SVN commit messages for the entire Summer of Code repository List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 01 Aug 2012 21:47:55 -0000 Author: tzabal Date: Wed Aug 1 21:47:52 2012 New Revision: 240001 URL: http://svnweb.FreeBSD.org/socsvn/?view=rev&rev=240001 Log: Changes to the database schema and the installation guide. Modified: soc2012/tzabal/server-side/akcrs-setup/database.sql soc2012/tzabal/server-side/akcrs-setup/setup Modified: soc2012/tzabal/server-side/akcrs-setup/database.sql ============================================================================== --- soc2012/tzabal/server-side/akcrs-setup/database.sql Wed Aug 1 21:32:52 2012 (r240000) +++ soc2012/tzabal/server-side/akcrs-setup/database.sql Wed Aug 1 21:47:52 2012 (r240001) @@ -3,44 +3,47 @@ * for the PostgreSQL DBMS */ ---DROP TABLE Submitters; ---DROP TABLE Bugs; ---DROP TABLE Reports; +DROP TABLE Reports; +DROP TABLE Bugs; +DROP TABLE Submitters; CREATE TABLE Submitters ( - id SERIAL, + id serial NOT NULL, email varchar(254) NOT NULL, - passwd varchar(12) NOT NULL, + password varchar(64) NOT NULL, - PRIMARY KEY (id) + CONSTRAINT submitters_pkey PRIMARY KEY (id) ); CREATE TABLE Bugs ( - id SERIAL, + id serial NOT NULL, state varchar(10) NOT NULL, reported integer NOT NULL, - PRIMARY KEY (id) + CONSTRAINT bugs_pkey PRIMARY KEY (id) ); CREATE TABLE Reports ( - id SERIAL, + id serial NOT NULL, bug_id integer NOT NULL, submitter_id integer NOT NULL, received_date date DEFAULT CURRENT_DATE, - crash_type text, - crash_date date, + confirmed boolean DEFAULT false, + crashtype text, + crashdate text, hostname text, ostype text, osrelease text, version text, machine text, + panic text, + backtrace text, ps_axl text, vmstat_s text, vmstat_m text, @@ -63,7 +66,9 @@ kernelconfig text, ddbcapturebuffer text, - PRIMARY KEY (id), - FOREIGN KEY (bug_id) REFERENCES Bugs(id), - FOREIGN KEY (submitter_id) REFERENCES Reports(id) + CONSTRAINT reports_pkey PRIMARY KEY (id), + CONSTRAINT reports_bug_id_fkey FOREIGN KEY (bug_id) REFERENCES Bugs (id), + CONSTRAINT reports_submitter_id_fkey FOREIGN KEY (submitter_id) REFERENCES Submitters (id) ); + +INSERT INTO Bugs (id, state, reported) VALUES (-1, 'Unknown', 0); Modified: soc2012/tzabal/server-side/akcrs-setup/setup ============================================================================== --- soc2012/tzabal/server-side/akcrs-setup/setup Wed Aug 1 21:32:52 2012 (r240000) +++ soc2012/tzabal/server-side/akcrs-setup/setup Wed Aug 1 21:47:52 2012 (r240001) @@ -26,6 +26,9 @@ # Give ownership of the directory to reporter chown reporter:reporter /var/spool/crashreports +# Create an auxiliary directory used for various actions +mkdir /tmp/crashreports + # Make sure that the OpenSSH daemon is enabled cat /etc/rc.conf | grep 'sshd_enable="YES"' @@ -84,14 +87,23 @@ # Start the database server /usr/local/etc/rc.d/postgresql start -# Swith to the (system) user that owns the PostgreSQL Server (or remain as root) +# Swith to the (system) user that owns the PostgreSQL Server (or stay as root) su pgsql # Create a new PostgreSQL user (role) that will NOT be superuser, create databases and create roles createuser -P akcrs # Create a Unicode database that our PostgreSQL user will has access to -/usr/local/bin/createdb akcrs_db -O akcrs --encoding=UNICODE +/usr/local/bin/createdb akcrsdb -O akcrs --encoding=UNICODE + +# Using psql connect to the database as the user that owns it +psql -d akcrsdb -U akcrs + +# Execute the SQL script that creates the database schema +\i /home/tzabal/akcrs/server-side/akcrs-setup/database.sql + +# Exit psql +\q ###########################################################