From e2b5d9764888e0f307ebcdf9c20835b197a0f63c Mon Sep 17 00:00:00 2001 From: Wilrik de Loose 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