db functies

12 files changed, 192 insertions, 22 deletions

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