From e2b5d9764888e0f307ebcdf9c20835b197a0f63c Mon Sep 17 00:00:00 2001
From: Wilrik de Loose <wilrik@wilrik.nl>
Date: Tue, 27 May 2008 09:50:18 +0000
Subject: db functies

---
 db_demo/sql/drop.sql                         |  8 ++--
 db_demo/sql/funcs/cr_func_insert_person.sql  | 27 ++++++++++++
 db_demo/sql/funcs/cr_func_insert_results.sql | 61 ++++++++++++++++++++++++++++
 db_demo/sql/funcs/cr_func_insert_test.sql    | 35 ++++++++++++++++
 db_demo/sql/funcs/cr_func_insert_turn.sql    | 31 ++++++++++++++
 db_demo/sql/init.sql                         |  6 +--
 db_demo/sql/refresh.sql                      |  8 +++-
 db_demo/sql/soft_drop.sql                    | 10 +++++
 db_demo/sql/tables/cr_tbl_person.sql         |  7 ++--
 db_demo/sql/tables/cr_tbl_test.sql           |  5 ++-
 db_demo/sql/tables/cr_tbl_turn.sql           | 12 +++---
 db_demo/sql/views/cr_vw_person.sql           |  4 +-
 12 files changed, 192 insertions(+), 22 deletions(-)
 create mode 100644 db_demo/sql/funcs/cr_func_insert_person.sql
 create mode 100644 db_demo/sql/funcs/cr_func_insert_results.sql
 create mode 100644 db_demo/sql/funcs/cr_func_insert_test.sql
 create mode 100644 db_demo/sql/funcs/cr_func_insert_turn.sql
 create mode 100644 db_demo/sql/soft_drop.sql

diff --git a/db_demo/sql/drop.sql b/db_demo/sql/drop.sql
index 9553629..4ea9cf7 100644
--- a/db_demo/sql/drop.sql
+++ b/db_demo/sql/drop.sql
@@ -1,7 +1,5 @@
--- drop views (3)
-DROP VIEW vw_turn;
-DROP VIEW vw_test;
-DROP VIEW vw_person;
+-- drop views and functions
+\i soft_drop.sql
 
 -- drop sequences (3)
 DROP SEQUENCE seq_person;
@@ -17,4 +15,4 @@ DROP TABLE tbl_person;
 DROP TABLE const_gametype;
 DROP TABLE const_block;
 DROP TABLE const_boxsize;
-DROP TABLE const_agegroup;
+DROP TABLE const_agegroup;
\ No newline at end of file
diff --git a/db_demo/sql/funcs/cr_func_insert_person.sql b/db_demo/sql/funcs/cr_func_insert_person.sql
new file mode 100644
index 0000000..762bf42
--- /dev/null
+++ b/db_demo/sql/funcs/cr_func_insert_person.sql
@@ -0,0 +1,27 @@
+CREATE OR REPLACE FUNCTION insert_person (
+  boolean,   -- cexperience
+  boolean,   -- wexperience
+  integer    -- agegroupid
+) RETURNS integer AS $$
+DECLARE
+  par_cexperience  ALIAS FOR $1;
+  par_wexperience  ALIAS FOR $2;
+  par_agegroupid   ALIAS FOR $3;
+
+  var_personid     integer;
+BEGIN
+  INSERT INTO tbl_person (
+    cexperience,
+    wexperience,
+    agegroupid
+  ) VALUES (
+    par_cexperience,
+    par_wexperience,
+    par_agegroupid
+  );
+
+  SELECT INTO var_personid seq_person.last_value;
+
+  RETURN var_personid;
+END;
+$$ LANGUAGE plpgsql;
\ No newline at end of file
diff --git a/db_demo/sql/funcs/cr_func_insert_results.sql b/db_demo/sql/funcs/cr_func_insert_results.sql
new file mode 100644
index 0000000..5e405c8
--- /dev/null
+++ b/db_demo/sql/funcs/cr_func_insert_results.sql
@@ -0,0 +1,61 @@
+CREATE OR REPLACE FUNCTION insert_results (
+  boolean,     -- cexperience
+  boolean,     -- wexperience
+  integer,     -- agegroupid
+  boolean[],   -- headtracking
+  boolean[],   -- stereo
+  boolean[],   -- shadow
+  integer[],   -- boxsizeid
+  time[][],    -- elapsedtime
+  integer[][], -- holeposition
+  integer[][]  -- blockid
+) RETURNS integer AS $$
+DECLARE
+  par_cexperience  ALIAS FOR $1;
+  par_wexperience  ALIAS FOR $2;
+  par_agegroupid   ALIAS FOR $3;
+  par_headtracking ALIAS FOR $4;
+  par_stereo       ALIAS FOR $5;
+  par_shadow       ALIAS FOR $6;
+  par_boxsizeid    ALIAS FOR $7;
+  par_elapsedtime  ALIAS FOR $8;
+  par_holeposition ALIAS FOR $9;
+  par_blockid      ALIAS FOR $10;
+
+  i                integer;
+  j                integer;
+  var_ntests       integer;
+  var_nturns       integer;
+  var_personid     integer;
+  var_testid       integer;
+BEGIN
+  i := 0;
+
+  SELECT INTO var_personid insert_person(par_cexperience,
+                                         par_wexperience,
+                                         par_agegroupid);
+
+  var_ntests = array_upper(par_headtracking, 1);
+
+  WHILE i < var_ntests LOOP
+    SELECT INTO var_testid insert_test(var_personid,
+                                       par_headtracking,
+                                       par_stereo,
+                                       par_shadow,
+                                       par_boxsizeid);
+    
+    var_nturns = array_upper(elapsedtime[i], 1);
+    j := 0;
+
+    WHILE j < var_nturns LOOP
+      PERFORM insert_turn(var_testid,
+                          par_elapsedtime,
+                          par_holeposition,
+                          par_blockid);
+    END LOOP;
+
+  END LOOP;
+
+  RETURN var_ntests * var_nturns;
+END;
+$$ LANGUAGE plpgsql;
\ No newline at end of file
diff --git a/db_demo/sql/funcs/cr_func_insert_test.sql b/db_demo/sql/funcs/cr_func_insert_test.sql
new file mode 100644
index 0000000..0015c2c
--- /dev/null
+++ b/db_demo/sql/funcs/cr_func_insert_test.sql
@@ -0,0 +1,35 @@
+CREATE OR REPLACE FUNCTION insert_test (
+  integer,   -- personid
+  boolean,   -- headtracking
+  boolean,   -- stereo
+  boolean,   -- shadow
+  integer    -- boxsizeid
+) RETURNS integer AS $$
+DECLARE
+  par_personid     ALIAS FOR $1;
+  par_headtracking ALIAS FOR $2;
+  par_stereo       ALIAS FOR $3;
+  par_shadow       ALIAS FOR $4;
+  par_boxsizeid    ALIAS FOR $5;
+
+  var_testid       integer;
+BEGIN
+  INSERT INTO tbl_test (
+    personid,
+    headtracking,
+    stereo,
+    shadow,
+    boxsizeid
+  ) VALUES (
+    par_personid,
+    par_headtracking,
+    par_stereo,
+    par_shadow,
+    par_boxsizeid
+  );
+
+  SELECT INTO var_testid seq_test.last_value;
+
+  RETURN var_testid;
+END;
+$$ LANGUAGE plpgsql;
\ No newline at end of file
diff --git a/db_demo/sql/funcs/cr_func_insert_turn.sql b/db_demo/sql/funcs/cr_func_insert_turn.sql
new file mode 100644
index 0000000..de6620d
--- /dev/null
+++ b/db_demo/sql/funcs/cr_func_insert_turn.sql
@@ -0,0 +1,31 @@
+CREATE OR REPLACE FUNCTION insert_turn (
+  integer,   -- testid
+  time,      -- elapsedtime
+  integer,   -- holeposition
+  integer    -- blockid
+) RETURNS integer AS $$
+DECLARE
+  par_testid       ALIAS FOR $1;
+  par_elapsedtime  ALIAS FOR $2;
+  par_holeposition ALIAS FOR $3;
+  par_blockid      ALIAS FOR $4;
+
+  var_turnid       integer;
+BEGIN
+  INSERT INTO tbl_turn (
+    testid,
+    elapsedtime,
+    holeposition,
+    blockid
+  ) VALUES (
+    par_testid,
+    par_elapsedtime,
+    par_holeposition,
+    par_blockid
+  );
+
+  SELECT INTO var_turnid seq_turn.last_value;
+
+  RETURN var_turnid;
+END;
+$$ LANGUAGE plpgsql;
\ No newline at end of file
diff --git a/db_demo/sql/init.sql b/db_demo/sql/init.sql
index 3da1e33..583b09b 100644
--- a/db_demo/sql/init.sql
+++ b/db_demo/sql/init.sql
@@ -15,7 +15,5 @@
 \i data/cr_data_gametype.sql
 \i data/cr_data_agegroup.sql
 
--- create views (3)
-\i views/cr_vw_person.sql
-\i views/cr_vw_test.sql
-\i views/cr_vw_turn.sql
\ No newline at end of file
+-- create views and functions by refreshing
+\i refresh.sql
\ No newline at end of file
diff --git a/db_demo/sql/refresh.sql b/db_demo/sql/refresh.sql
index 5109827..4ef1ff0 100644
--- a/db_demo/sql/refresh.sql
+++ b/db_demo/sql/refresh.sql
@@ -1,4 +1,10 @@
 -- create views (3)
 \i views/cr_vw_person.sql
 \i views/cr_vw_test.sql
-\i views/cr_vw_turn.sql
\ No newline at end of file
+\i views/cr_vw_turn.sql
+
+-- create functions (4)
+\i funcs/cr_func_insert_person.sql
+\i funcs/cr_func_insert_test.sql
+\i funcs/cr_func_insert_turn.sql
+\i funcs/cr_func_insert_results.sql
\ No newline at end of file
diff --git a/db_demo/sql/soft_drop.sql b/db_demo/sql/soft_drop.sql
new file mode 100644
index 0000000..2a8490c
--- /dev/null
+++ b/db_demo/sql/soft_drop.sql
@@ -0,0 +1,10 @@
+-- drop views (3)
+DROP VIEW vw_turn;
+DROP VIEW vw_test;
+DROP VIEW vw_person;
+
+-- drop functions (4)
+DROP FUNCTION insert_person (boolean, boolean, integer);
+DROP FUNCTION insert_results (boolean, boolean, integer, boolean[], boolean[], boolean[], integer[], time[][], integer[][], integer[][]);
+DROP FUNCTION insert_test (integer, boolean, boolean, boolean, integer);
+DROP FUNCTION insert_turn (integer, time, integer, integer);
diff --git a/db_demo/sql/tables/cr_tbl_person.sql b/db_demo/sql/tables/cr_tbl_person.sql
index 95e01b7..87fc5bd 100644
--- a/db_demo/sql/tables/cr_tbl_person.sql
+++ b/db_demo/sql/tables/cr_tbl_person.sql
@@ -1,8 +1,9 @@
 CREATE SEQUENCE seq_person START 1;
 
 CREATE TABLE tbl_person (
-  key            integer PRIMARY KEY,                                 -- primary key
+  key            integer DEFAULT nextval('seq_person') PRIMARY KEY,   -- primary key
   agegroupid     integer NOT NULL REFERENCES const_agegroup(key),     -- age group foreign key
-  c_experience   boolean NOT NULL,                                    -- computer experience
-  w_experience   boolean NOT NULL                                     -- wiimote experience
+  cexperience    boolean NOT NULL,                                    -- computer experience
+  wexperience    boolean NOT NULL,                                    -- wiimote experience
+  inserttime     timestamp DEFAULT current_timestamp                  -- time of insertion
 );
\ No newline at end of file
diff --git a/db_demo/sql/tables/cr_tbl_test.sql b/db_demo/sql/tables/cr_tbl_test.sql
index 15e94d0..63b861b 100644
--- a/db_demo/sql/tables/cr_tbl_test.sql
+++ b/db_demo/sql/tables/cr_tbl_test.sql
@@ -1,7 +1,8 @@
 CREATE SEQUENCE seq_test START 1;
 
 CREATE TABLE tbl_test (
-  key            integer PRIMARY KEY,                             -- primary key
+  key            integer DEFAULT nextval('seq_test') PRIMARY KEY, -- primary key
   personid       integer NOT NULL REFERENCES tbl_person(key),     -- person foreign key
-  gametypeid     integer NOT NULL REFERENCES const_gametype(key)  -- gametype foreign key
+  gametypeid     integer NOT NULL REFERENCES const_gametype(key), -- gametype foreign key
+  inserttime     timestamp DEFAULT current_timestamp              -- time of insertion
 );
\ No newline at end of file
diff --git a/db_demo/sql/tables/cr_tbl_turn.sql b/db_demo/sql/tables/cr_tbl_turn.sql
index f0b7cc3..10218e2 100644
--- a/db_demo/sql/tables/cr_tbl_turn.sql
+++ b/db_demo/sql/tables/cr_tbl_turn.sql
@@ -1,9 +1,11 @@
 CREATE SEQUENCE seq_turn START 1;
 
 CREATE TABLE tbl_turn (
-  key            integer PRIMARY KEY,                             -- primary key
-  testid         integer NOT NULL REFERENCES tbl_test(key),       -- test foreign key
-  blockid        integer NOT NULL REFERENCES const_block(key),    -- block foreign key
-  elapsedtime    time NOT NULL,                                   -- time in ms.
-  holeposition   integer NOT NULL                                 -- position of the hole
+  key            integer DEFAULT nextval('seq_turn') PRIMARY KEY,   -- primary key
+  testid         integer NOT NULL REFERENCES tbl_test(key),         -- test foreign key
+  blockid        integer NOT NULL REFERENCES const_block(key),      -- block foreign key
+  elapsedtime    time NOT NULL,                                     -- time in ms.
+  holeposition   integer NOT NULL,                                  -- position of the hole
+  inserttime     timestamp DEFAULT current_timestamp                -- time of insertion
+  CONSTRAINT     valid_hole CHECK (holeposition >= 0 AND holeposition < 16)
 );
\ No newline at end of file
diff --git a/db_demo/sql/views/cr_vw_person.sql b/db_demo/sql/views/cr_vw_person.sql
index 0f8a973..352e2e9 100644
--- a/db_demo/sql/views/cr_vw_person.sql
+++ b/db_demo/sql/views/cr_vw_person.sql
@@ -1,8 +1,8 @@
 CREATE OR REPLACE VIEW vw_person AS
 SELECT
   p.key AS personid,
-  p.c_experience,
-  p.w_experience,
+  p.cexperience,
+  p.wexperience,
 
   a.key AS agegroupid,
   a.description AS agegroup
-- 
cgit v0.12

