1ZeXRUnC

1/*
2select the column titles for this type of worksheet and the associated data
3
4desired result - resultset with column names as per titles stored in worksheet_types and data as per worksheet_data for those columns
5
6worksheet_type_lookup is for mapping the worksheet id to a type.
7
8*/
9
10
11
12
13
14# Dump of table worksheet_data
15# ------------------------------------------------------------
16
17DROP TABLE IF EXISTS `worksheet_data`;
18
19CREATE TABLE `worksheet_data` (
20  `id` int(11) NOT NULL AUTO_INCREMENT,
21  `worksheet_id` int(11) NOT NULL,
22  `date` datetime NOT NULL,
23  `col_1` text NOT NULL,
24  `col_2` text NOT NULL,
25  `col_3` text NOT NULL,
26  `col_4` text NOT NULL,
27  `col_5` text NOT NULL,
28  `col_6` text NOT NULL,
29  `col_7` text NOT NULL,
30  `col_8` text NOT NULL,
31  PRIMARY KEY (`id`)
32) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
33
34LOCK TABLES `worksheet_data` WRITE;
35/*!40000 ALTER TABLE `worksheet_data` DISABLE KEYS */;
36INSERT INTO `worksheet_data` (`id`,`worksheet_id`,`date`,`col_1`,`col_2`,`col_3`,`col_4`,`col_5`,`col_6`,`col_7`,`col_8`)
37VALUES
38	(1,1,'2009-07-31 07:02:48','thinking about life','Life is complicated','frustration','dobedobedo','dobedobedo','dobedobedo','','');
39
40/*!40000 ALTER TABLE `worksheet_data` ENABLE KEYS */;
41UNLOCK TABLES;
42
43
44# Dump of table worksheet_type_lookup
45# ------------------------------------------------------------
46
47DROP TABLE IF EXISTS `worksheet_type_lookup`;
48
49CREATE TABLE `worksheet_type_lookup` (
50  `id` int(11) NOT NULL AUTO_INCREMENT,
51  `worksheet_id` int(11) NOT NULL,
52  `worksheet_type_id` int(11) NOT NULL,
53  PRIMARY KEY (`id`)
54) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
55
56LOCK TABLES `worksheet_type_lookup` WRITE;
57/*!40000 ALTER TABLE `worksheet_type_lookup` DISABLE KEYS */;
58INSERT INTO `worksheet_type_lookup` (`id`,`worksheet_id`,`worksheet_type_id`)
59VALUES
60	(1,1,1);
61
62/*!40000 ALTER TABLE `worksheet_type_lookup` ENABLE KEYS */;
63UNLOCK TABLES;
64
65
66# Dump of table worksheet_types
67# ------------------------------------------------------------
68
69DROP TABLE IF EXISTS `worksheet_types`;
70
71CREATE TABLE `worksheet_types` (
72  `id` int(11) NOT NULL AUTO_INCREMENT,
73  `title` text NOT NULL,
74  `col1_title` text NOT NULL,
75  `col2_title` text NOT NULL,
76  `col3_title` text NOT NULL,
77  `col4_title` text NOT NULL,
78  `col5_title` text NOT NULL,
79  `col6_title` text NOT NULL,
80  `col7_title` text NOT NULL,
81  `col8_title` text NOT NULL,
82  PRIMARY KEY (`id`)
83) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
84
85LOCK TABLES `worksheet_types` WRITE;
86/*!40000 ALTER TABLE `worksheet_types` DISABLE KEYS */;
87INSERT INTO `worksheet_types` (`id`,`title`,`col1_title`,`col2_title`,`col3_title`,`col4_title`,`col5_title`,`col6_title`,`col7_title`,`col8_title`)
88VALUES
89	(1,'Worksheet Type 1','Situation','Thoughts','Feelings','title','title','title','','');
90
91/*!40000 ALTER TABLE `worksheet_types` ENABLE KEYS */;
92UNLOCK TABLES;
93
94
95# Dump of table worksheets
96# ------------------------------------------------------------
97
98DROP TABLE IF EXISTS `worksheets`;
99
100CREATE TABLE `worksheets` (
101  `id` int(11) NOT NULL AUTO_INCREMENT,
102  `type_id` int(11) NOT NULL,
103  `user_id` int(11) NOT NULL,
104  `title` varchar(255) NOT NULL,
105  PRIMARY KEY (`id`)
106) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
107
108LOCK TABLES `worksheets` WRITE;
109/*!40000 ALTER TABLE `worksheets` DISABLE KEYS */;
110INSERT INTO `worksheets` (`id`,`type_id`,`user_id`,`title`)
111VALUES
112	(1,1,0,'Test Worksheet');
113
114/*!40000 ALTER TABLE `worksheets` ENABLE KEYS */;
115UNLOCK TABLES;
116
117
118
119
120
121/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
122/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
123/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
124/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
125/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
126/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
127/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;