diff options
Diffstat (limited to 'db_demo/sql')
-rw-r--r-- | db_demo/sql/drop.sql | 8 | ||||
-rw-r--r-- | db_demo/sql/funcs/cr_func_insert_person.sql | 27 | ||||
-rw-r--r-- | db_demo/sql/funcs/cr_func_insert_results.sql | 61 | ||||
-rw-r--r-- | db_demo/sql/funcs/cr_func_insert_test.sql | 35 | ||||
-rw-r--r-- | db_demo/sql/funcs/cr_func_insert_turn.sql | 31 | ||||
-rw-r--r-- | db_demo/sql/init.sql | 6 | ||||
-rw-r--r-- | db_demo/sql/refresh.sql | 8 | ||||
-rw-r--r-- | db_demo/sql/soft_drop.sql | 10 | ||||
-rw-r--r-- | db_demo/sql/tables/cr_tbl_person.sql | 7 | ||||
-rw-r--r-- | db_demo/sql/tables/cr_tbl_test.sql | 5 | ||||
-rw-r--r-- | db_demo/sql/tables/cr_tbl_turn.sql | 12 | ||||
-rw-r--r-- | db_demo/sql/views/cr_vw_person.sql | 4 |
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
|