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