Date: Wed, 01 Aug 2012 21:47:52 +0000 From: tzabal@FreeBSD.org To: svn-soc-all@FreeBSD.org Subject: socsvn commit: r240001 - soc2012/tzabal/server-side/akcrs-setup Message-ID: <20120801214752.D6109106564A@hub.freebsd.org>
next in thread | raw e-mail | index | archive | help
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 ###########################################################
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20120801214752.D6109106564A>