socsvn commit: r240001 - soc2012/tzabal/server-side/akcrs-setup

[tzabal at FreeBSD.org]

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
 
 
 ###########################################################