Tabel structuur aangemaakt

21 files changed, 195 insertions, 2 deletions

diff --git a/db_demo/db.c b/db_demo/db.c
index ea82019..6f793a1 100644
--- a/db_demo/db.c
+++ b/db_demo/db.c
@@ -217,7 +217,7 @@ int db_output(void)
     printf("\n");

   }

 

-  printf("\nResults: %d\n\n", l_iNTuples);

+  printf("(%d rows)\n\n", l_iNTuples);

 

   return DB_OK;

 

diff --git a/db_demo/db_demo.ncb b/db_demo/db_demo.ncb
index 8215619..763bcc0 100644
--- a/db_demo/db_demo.ncb
+++ b/db_demo/db_demo.ncb
diff --git a/db_demo/db_demo.suo b/db_demo/db_demo.suo
index a05627e..f966b4b 100644
--- a/db_demo/db_demo.suo
+++ b/db_demo/db_demo.suo
diff --git a/db_demo/main.c b/db_demo/main.c
index 2120be9..4ae8dbb 100644
--- a/db_demo/main.c
+++ b/db_demo/main.c
@@ -4,7 +4,7 @@
 #include "db.h"
 
 // prints a new line
-void newline(void) { printf("%s> ", DBNAME); }
+void newline(void) { printf("%s=> ", DBNAME); }
 
 int main(int argc, char *argv[])
 {
diff --git a/db_demo/sql/data/cr_data_agegroup.sql b/db_demo/sql/data/cr_data_agegroup.sql
new file mode 100644
index 0000000..deee698
--- /dev/null
+++ b/db_demo/sql/data/cr_data_agegroup.sql
@@ -0,0 +1,3 @@
+INSERT INTO const_agegroup (key, description) VALUES (100, 'Age < 20');

+INSERT INTO const_agegroup (key, description) VALUES (101, '20 < age < 30');

+INSERT INTO const_agegroup (key, description) VALUES (102, '30 < age');

diff --git a/db_demo/sql/data/cr_data_block.sql b/db_demo/sql/data/cr_data_block.sql
new file mode 100644
index 0000000..ea2c86b
--- /dev/null
+++ b/db_demo/sql/data/cr_data_block.sql
@@ -0,0 +1,4 @@
+INSERT INTO const_block (key, description) VALUES (100, 'Square');

+INSERT INTO const_block (key, description) VALUES (101, 'Circle');

+INSERT INTO const_block (key, description) VALUES (102, 'Triangle');

+INSERT INTO const_block (key, description) VALUES (103, 'Cross');

diff --git a/db_demo/sql/data/cr_data_boxsize.sql b/db_demo/sql/data/cr_data_boxsize.sql
new file mode 100644
index 0000000..01bb3e4
--- /dev/null
+++ b/db_demo/sql/data/cr_data_boxsize.sql
@@ -0,0 +1,3 @@
+INSERT INTO const_boxsize (key, description) VALUES (100, 'Small');

+INSERT INTO const_boxsize (key, description) VALUES (101, 'Medium');

+INSERT INTO const_boxsize (key, description) VALUES (102, 'Large');
\ No newline at end of file
diff --git a/db_demo/sql/data/cr_data_gametype.sql b/db_demo/sql/data/cr_data_gametype.sql
new file mode 100644
index 0000000..61e95ff
--- /dev/null
+++ b/db_demo/sql/data/cr_data_gametype.sql
@@ -0,0 +1,39 @@
+-- no headtracking / no stereo / no shadow

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (100, 100, 'f', 'f', 'f');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (101, 101, 'f', 'f', 'f');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (102, 102, 'f', 'f', 'f');

+

+-- with headtracking / no stereo / no shadow

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (103, 100, 't', 'f', 'f');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (104, 101, 't', 'f', 'f');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (105, 102, 't', 'f', 'f');

+

+-- no headtracking / with stereo / no shadow

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (106, 100, 'f', 't', 'f');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (107, 101, 'f', 't', 'f');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (108, 102, 'f', 't', 'f');

+

+-- with headtracking / with stereo / no shadow

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (109, 100, 't', 't', 'f');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (110, 101, 't', 't', 'f');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (111, 102, 't', 't', 'f');

+

+-- no headtracking / no stereo / with shadow

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (112, 100, 'f', 'f', 't');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (113, 101, 'f', 'f', 't');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (114, 102, 'f', 'f', 't');

+

+-- with headtracking / no stereo / with shadow

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (115, 100, 't', 'f', 't');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (116, 101, 't', 'f', 't');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (117, 102, 't', 'f', 't');

+

+-- no headtracking / with stereo / with shadow

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (118, 100, 'f', 't', 't');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (119, 101, 'f', 't', 't');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (120, 102, 'f', 't', 't');

+

+-- with headtracking / with stereo / with shadow

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (121, 100, 't', 't', 't');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (122, 101, 't', 't', 't');

+INSERT INTO const_gametype (key, boxsizeid, headtracking, stereo, shadow) VALUES (123, 102, 't', 't', 't');

diff --git a/db_demo/sql/drop.sql b/db_demo/sql/drop.sql
new file mode 100644
index 0000000..9553629
--- /dev/null
+++ b/db_demo/sql/drop.sql
@@ -0,0 +1,20 @@
+-- drop views (3)

+DROP VIEW vw_turn;

+DROP VIEW vw_test;

+DROP VIEW vw_person;

+

+-- drop sequences (3)

+DROP SEQUENCE seq_person;

+DROP SEQUENCE seq_test;

+DROP SEQUENCE seq_turn;

+

+-- drop user tables (3)

+DROP TABLE tbl_turn;

+DROP TABLE tbl_test;

+DROP TABLE tbl_person;

+

+-- drop constant tables (4)

+DROP TABLE const_gametype;

+DROP TABLE const_block;

+DROP TABLE const_boxsize;

+DROP TABLE const_agegroup;

diff --git a/db_demo/sql/init.sql b/db_demo/sql/init.sql
new file mode 100644
index 0000000..3da1e33
--- /dev/null
+++ b/db_demo/sql/init.sql
@@ -0,0 +1,21 @@
+-- create constant tables (4)

+\i tables/cr_const_block.sql

+\i tables/cr_const_boxsize.sql

+\i tables/cr_const_gametype.sql

+\i tables/cr_const_agegroup.sql

+

+-- create user tables (3)

+\i tables/cr_tbl_person.sql

+\i tables/cr_tbl_test.sql

+\i tables/cr_tbl_turn.sql

+

+-- create constant data (4)

+\i data/cr_data_block.sql

+\i data/cr_data_boxsize.sql

+\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
diff --git a/db_demo/sql/refresh.sql b/db_demo/sql/refresh.sql
new file mode 100644
index 0000000..5109827
--- /dev/null
+++ b/db_demo/sql/refresh.sql
@@ -0,0 +1,4 @@
+-- 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
diff --git a/db_demo/sql/tables/cr_const_agegroup.sql b/db_demo/sql/tables/cr_const_agegroup.sql
new file mode 100644
index 0000000..b27217a
--- /dev/null
+++ b/db_demo/sql/tables/cr_const_agegroup.sql
@@ -0,0 +1,4 @@
+CREATE TABLE const_agegroup (

+  key            integer PRIMARY KEY,   -- primary key

+  description    text NOT NULL          -- group description

+);
\ No newline at end of file
diff --git a/db_demo/sql/tables/cr_const_block.sql b/db_demo/sql/tables/cr_const_block.sql
new file mode 100644
index 0000000..4b86667
--- /dev/null
+++ b/db_demo/sql/tables/cr_const_block.sql
@@ -0,0 +1,4 @@
+CREATE TABLE const_block (

+  key            integer PRIMARY KEY,   -- primary key

+  description    text NOT NULL          -- block description

+);
\ No newline at end of file
diff --git a/db_demo/sql/tables/cr_const_boxsize.sql b/db_demo/sql/tables/cr_const_boxsize.sql
new file mode 100644
index 0000000..5f5be90
--- /dev/null
+++ b/db_demo/sql/tables/cr_const_boxsize.sql
@@ -0,0 +1,4 @@
+CREATE TABLE const_boxsize (

+  key            integer PRIMARY KEY,   -- primary key

+  description    text NOT NULL          -- size description

+);
\ No newline at end of file
diff --git a/db_demo/sql/tables/cr_const_gametype.sql b/db_demo/sql/tables/cr_const_gametype.sql
new file mode 100644
index 0000000..d9e9a99
--- /dev/null
+++ b/db_demo/sql/tables/cr_const_gametype.sql
@@ -0,0 +1,7 @@
+CREATE TABLE const_gametype (

+  key            integer PRIMARY KEY,                             -- primary key

+  boxsizeid      integer NOT NULL REFERENCES const_boxsize(key),  -- boxsize foreign key

+  headtracking   boolean NOT NULL,                                -- headtracking on / off

+  stereo         boolean NOT NULL,                                -- stereo vision on / off

+  shadow         boolean NOT NULL                                 -- shadow on / off

+);
\ No newline at end of file
diff --git a/db_demo/sql/tables/cr_tbl_person.sql b/db_demo/sql/tables/cr_tbl_person.sql
new file mode 100644
index 0000000..95e01b7
--- /dev/null
+++ b/db_demo/sql/tables/cr_tbl_person.sql
@@ -0,0 +1,8 @@
+CREATE SEQUENCE seq_person START 1;

+

+CREATE TABLE tbl_person (

+  key            integer 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

+);
\ 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
new file mode 100644
index 0000000..15e94d0
--- /dev/null
+++ b/db_demo/sql/tables/cr_tbl_test.sql
@@ -0,0 +1,7 @@
+CREATE SEQUENCE seq_test START 1;

+

+CREATE TABLE tbl_test (

+  key            integer 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

+);
\ 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
new file mode 100644
index 0000000..f0b7cc3
--- /dev/null
+++ b/db_demo/sql/tables/cr_tbl_turn.sql
@@ -0,0 +1,9 @@
+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

+);
\ 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
new file mode 100644
index 0000000..0f8a973
--- /dev/null
+++ b/db_demo/sql/views/cr_vw_person.sql
@@ -0,0 +1,14 @@
+CREATE OR REPLACE VIEW vw_person AS

+SELECT

+  p.key AS personid,

+  p.c_experience,

+  p.w_experience,

+

+  a.key AS agegroupid,

+  a.description AS agegroup

+FROM

+  tbl_person p,

+  const_agegroup a

+WHERE

+  p.agegroupid = a.key

+;
\ No newline at end of file
diff --git a/db_demo/sql/views/cr_vw_test.sql b/db_demo/sql/views/cr_vw_test.sql
new file mode 100644
index 0000000..3996188
--- /dev/null
+++ b/db_demo/sql/views/cr_vw_test.sql
@@ -0,0 +1,24 @@
+CREATE OR REPLACE VIEW vw_test AS

+SELECT

+  p.*,

+

+  t.key AS testid,

+

+  g.key AS gametypeid,

+  g.headtracking,

+  g.stereo,

+  g.shadow,

+

+  b.key AS boxsizeid,

+  b.description AS boxsize

+

+FROM

+  tbl_test t,

+  vw_person p,

+  const_gametype g,

+  const_boxsize b

+WHERE

+  t.personid   = p.personid AND

+  t.gametypeid = g.key AND

+  g.boxsizeid  = b.key

+;
\ No newline at end of file
diff --git a/db_demo/sql/views/cr_vw_turn.sql b/db_demo/sql/views/cr_vw_turn.sql
new file mode 100644
index 0000000..93485bf
--- /dev/null
+++ b/db_demo/sql/views/cr_vw_turn.sql
@@ -0,0 +1,18 @@
+CREATE OR REPLACE VIEW vw_turn AS

+SELECT

+  ts.*,

+

+  t.key AS turnid,

+  t.elapsedtime,

+  t.holeposition,

+

+  b.key AS blockid,

+  b.description AS block  

+FROM

+  tbl_turn t,

+  vw_test ts,

+  const_block b

+WHERE

+  t.testid  = ts.testid AND

+  t.blockid = b.key

+;
\ No newline at end of file