From dc6caf529031dba2dd595b64ee008ffda39737b6 Mon Sep 17 00:00:00 2001
From: Wilrik de Loose <wilrik@wilrik.nl>
Date: Thu, 8 May 2008 19:07:50 +0000
Subject: Tabel structuur aangemaakt

---
 db_demo/db.c                             |   2 +-
 db_demo/db_demo.ncb                      | Bin 887808 -> 887808 bytes
 db_demo/db_demo.suo                      | Bin 19968 -> 19968 bytes
 db_demo/main.c                           |   2 +-
 db_demo/sql/data/cr_data_agegroup.sql    |   3 +++
 db_demo/sql/data/cr_data_block.sql       |   4 ++++
 db_demo/sql/data/cr_data_boxsize.sql     |   3 +++
 db_demo/sql/data/cr_data_gametype.sql    |  39 +++++++++++++++++++++++++++++++
 db_demo/sql/drop.sql                     |  20 ++++++++++++++++
 db_demo/sql/init.sql                     |  21 +++++++++++++++++
 db_demo/sql/refresh.sql                  |   4 ++++
 db_demo/sql/tables/cr_const_agegroup.sql |   4 ++++
 db_demo/sql/tables/cr_const_block.sql    |   4 ++++
 db_demo/sql/tables/cr_const_boxsize.sql  |   4 ++++
 db_demo/sql/tables/cr_const_gametype.sql |   7 ++++++
 db_demo/sql/tables/cr_tbl_person.sql     |   8 +++++++
 db_demo/sql/tables/cr_tbl_test.sql       |   7 ++++++
 db_demo/sql/tables/cr_tbl_turn.sql       |   9 +++++++
 db_demo/sql/views/cr_vw_person.sql       |  14 +++++++++++
 db_demo/sql/views/cr_vw_test.sql         |  24 +++++++++++++++++++
 db_demo/sql/views/cr_vw_turn.sql         |  18 ++++++++++++++
 21 files changed, 195 insertions(+), 2 deletions(-)
 create mode 100644 db_demo/sql/data/cr_data_agegroup.sql
 create mode 100644 db_demo/sql/data/cr_data_block.sql
 create mode 100644 db_demo/sql/data/cr_data_boxsize.sql
 create mode 100644 db_demo/sql/data/cr_data_gametype.sql
 create mode 100644 db_demo/sql/drop.sql
 create mode 100644 db_demo/sql/init.sql
 create mode 100644 db_demo/sql/refresh.sql
 create mode 100644 db_demo/sql/tables/cr_const_agegroup.sql
 create mode 100644 db_demo/sql/tables/cr_const_block.sql
 create mode 100644 db_demo/sql/tables/cr_const_boxsize.sql
 create mode 100644 db_demo/sql/tables/cr_const_gametype.sql
 create mode 100644 db_demo/sql/tables/cr_tbl_person.sql
 create mode 100644 db_demo/sql/tables/cr_tbl_test.sql
 create mode 100644 db_demo/sql/tables/cr_tbl_turn.sql
 create mode 100644 db_demo/sql/views/cr_vw_person.sql
 create mode 100644 db_demo/sql/views/cr_vw_test.sql
 create mode 100644 db_demo/sql/views/cr_vw_turn.sql

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
Binary files a/db_demo/db_demo.ncb and b/db_demo/db_demo.ncb differ
diff --git a/db_demo/db_demo.suo b/db_demo/db_demo.suo
index a05627e..f966b4b 100644
Binary files a/db_demo/db_demo.suo and b/db_demo/db_demo.suo differ
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
-- 
cgit v0.12

