Menu
▾
▴
Diff of /cocoma.sql [000000] .. [r1] Maximize Restore
Switch to side-by-side view
--- a +++ b/cocoma.sql @@ -0,0 +1,620 @@ +-- MySQL Administrator dump 1.4 +-- +-- ------------------------------------------------------ +-- Server version 5.0.67-community + + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; + +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; + + +-- +-- Create schema ccm_cake2 +-- + +CREATE DATABASE IF NOT EXISTS ccm_cocoma; +USE ccm_cocoma; + +-- +-- Definition of table `ccm_acos` +-- + +DROP TABLE IF EXISTS `ccm_acos`; +CREATE TABLE `ccm_acos` ( + `id` int(10) NOT NULL auto_increment, + `parent_id` int(10) default NULL, + `model` varchar(255) collate latin1_general_ci default NULL, + `foreign_key` int(10) default NULL, + `alias` varchar(255) collate latin1_general_ci default NULL, + `lft` int(10) default NULL, + `rght` int(10) default NULL, + PRIMARY KEY (`id`) +) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; + +-- +-- Dumping data for table `ccm_acos` +-- + +/*!40000 ALTER TABLE `ccm_acos` DISABLE KEYS */; +INSERT INTO `ccm_acos` (`id`,`parent_id`,`model`,`foreign_key`,`alias`,`lft`,`rght`) VALUES + (1,NULL,NULL,NULL,'GLOBAL',1,38), + (2,1,NULL,NULL,'PAGE',2,5), + (3,1,NULL,NULL,'COURSE',6,15), + (22,18,NULL,NULL,'CONTENT:delete',32,33), + (5,3,NULL,NULL,'COURSE:view',7,8), + (6,3,NULL,NULL,'COURSE:create',9,10), + (7,3,NULL,NULL,'COURSE:edit',11,12), + (8,3,NULL,NULL,'COURSE:delete',13,14), + (9,1,NULL,NULL,'TOPIC',16,37), + (23,18,NULL,NULL,'CONTENT:approveRevision',34,35), + (11,9,NULL,NULL,'TOPIC:view',17,18), + (12,9,NULL,NULL,'TOPIC:create',19,20), + (13,9,NULL,NULL,'TOPIC:edit',21,22), + (14,9,NULL,NULL,'TOPIC:delete',23,24), + (15,9,NULL,NULL,'TOPIC:insertSlide',25,26), + (18,9,NULL,NULL,'CONTENT',27,36), + (19,18,NULL,NULL,'CONTENT:addRev',28,29), + (20,18,NULL,NULL,'CONTENT:edit',30,31), + (21,2,NULL,NULL,'PAGE:index',3,4), + (26,9,NULL,NULL,'TOPIC:importSlides',18,28); +/*!40000 ALTER TABLE `ccm_acos` ENABLE KEYS */; + + +-- +-- Definition of table `ccm_aros` +-- + +DROP TABLE IF EXISTS `ccm_aros`; +CREATE TABLE `ccm_aros` ( + `id` int(10) NOT NULL auto_increment, + `parent_id` int(10) default NULL, + `model` varchar(255) collate latin1_general_ci default NULL, + `foreign_key` int(10) default NULL, + `alias` varchar(255) collate latin1_general_ci default NULL, + `test` varchar(255) collate latin1_general_ci NOT NULL, + `lft` int(10) default NULL, + `rght` int(10) default NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `alias` (`alias`) +) ENGINE=MyISAM AUTO_INCREMENT=34 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; + +-- +-- Dumping data for table `ccm_aros` +-- + +/*!40000 ALTER TABLE `ccm_aros` DISABLE KEYS */; +INSERT INTO `ccm_aros` (`id`,`parent_id`,`model`,`foreign_key`,`alias`,`test`,`lft`,`rght`) VALUES + (1,NULL,'Group',1,'ROOT','',1,26), + (2,1,'Group',2,'GLOBAL','',2,9), + (3,1,'Group',3,'COURSE','',10,17), + (4,1,'Group',4,'TOPIC','',18,25), + (5,2,'Group',5,'GLOBAL:Guests','',3,8), + (6,5,'Group',6,'GLOBAL:Users','',4,7), + (7,6,'Group',7,'GLOBAL:Admins','',5,6), + (8,3,'Group',8,'COURSE:Guests','',11,16), + (9,8,'Group',9,'COURSE:Students','',12,15), + (10,9,'Group',10,'COURSE:Profs','',13,14), + (11,4,'Group',11,'TOPIC:Guests','',19,24), + (12,11,'Group',12,'TOPIC:Students','',20,23), + (13,12,'Group',13,'TOPIC:Profs','',21,22);/*!40000 ALTER TABLE `ccm_aros` ENABLE KEYS */; + + +-- +-- Definition of table `ccm_aros_acos` +-- + +DROP TABLE IF EXISTS `ccm_aros_acos`; +CREATE TABLE `ccm_aros_acos` ( + `id` int(10) NOT NULL auto_increment, + `aro_id` int(10) NOT NULL, + `aco_id` int(10) NOT NULL, + `_create` varchar(2) collate latin1_general_ci NOT NULL default '0', + `_read` varchar(2) collate latin1_general_ci NOT NULL default '0', + `_update` varchar(2) collate latin1_general_ci NOT NULL default '0', + `_delete` varchar(2) collate latin1_general_ci NOT NULL default '0', + PRIMARY KEY (`id`), + UNIQUE KEY `ARO_ACO_KEY` (`aro_id`,`aco_id`) +) ENGINE=MyISAM AUTO_INCREMENT=145 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; + +-- +-- Dumping data for table `ccm_aros_acos` +-- + +/*!40000 ALTER TABLE `ccm_aros_acos` DISABLE KEYS */; +INSERT INTO `ccm_aros_acos` (`id`,`aro_id`,`aco_id`,`_create`,`_read`,`_update`,`_delete`) VALUES + (1,7,1,'1','1','1','1'), + (2,5,2,'1','1','1','1'), + (138,10,7,'1','1','1','1'), + (120,13,23,'1','1','1','1'), + (113,13,22,'1','1','1','1'), + (141,12,19,'1','1','1','1'), + (144,13,15,'1','1','1','1'), + (10,9,5,'1','1','1','1'), + (126,7,7,'1','1','1','1'), + (105,13,12,'1','1','1','1'), + (143,13,14,'1','1','1','1'), + (142,13,20,'1','1','1','1'), + (106,13,13,'1','1','1','1'), + (55,8,5,'1','1','1','1'), + (125,7,6,'1','1','1','1'), + (124,7,5,'1','1','1','1'), + (68,11,11,'1','1','1','1'), + (127,7,8,'1','1','1','1'), + (128,7,11,'1','1','1','1'), + (129,7,12,'1','1','1','1'), + (130,7,13,'1','1','1','1'), + (132,7,19,'1','1','1','1'), + (133,7,20,'1','1','1','1'), + (134,7,22,'1','1','1','1'), + (135,7,23,'1','1','1','1'), + (136,7,14,'1','1','1','1'), + (145,7,26,'1','1','1','1'), + (146,10,26,'1','1','1','1'), + (147,13,26,'1','1','1','1'); + +/*!40000 ALTER TABLE `ccm_aros_acos` ENABLE KEYS */; + + +-- +-- Definition of table `ccm_comments` +-- + +DROP TABLE IF EXISTS `ccm_comments`; +CREATE TABLE `ccm_comments` ( + `id` int(10) unsigned NOT NULL auto_increment, + `content_id` int(10) unsigned NOT NULL, + `created_by` int(10) unsigned NOT NULL, + `comment` text collate latin1_general_ci NOT NULL, + `subject` varchar(255) collate latin1_general_ci NOT NULL, + PRIMARY KEY (`id`), + KEY `created_by` (`created_by`), + KEY `content_id` (`content_id`), + CONSTRAINT `ccm_comments_ibfk_1` FOREIGN KEY (`content_id`) REFERENCES `ccm_content` (`id`), + CONSTRAINT `ccm_comments_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `ccm_users` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; + +-- +-- Dumping data for table `ccm_comments` +-- + +/*!40000 ALTER TABLE `ccm_comments` DISABLE KEYS */; +/*!40000 ALTER TABLE `ccm_comments` ENABLE KEYS */; + + +-- +-- Definition of table `ccm_content` +-- + +DROP TABLE IF EXISTS `ccm_content`; +CREATE TABLE `ccm_content` ( + `id` int(10) unsigned NOT NULL auto_increment, + `topic_id` int(11) unsigned NOT NULL, + `parent_id` int(11) default NULL, + `title` tinytext character set latin1, + `subtitle` tinytext character set utf8, + `description` mediumtext character set latin1, + `created_by` int(10) unsigned NOT NULL, + `created_on` datetime NOT NULL default '0000-00-00 00:00:00', + `content` longtext character set latin1, + `deleted` tinyint(1) NOT NULL default '0', + `position` int(10) unsigned NOT NULL default '0', + PRIMARY KEY (`id`), + KEY `parent_id` (`parent_id`), + KEY `topic_id` (`topic_id`), + KEY `created_by` (`created_by`), + CONSTRAINT `ccm_content_ibfk_1` FOREIGN KEY (`topic_id`) REFERENCES `ccm_topics` (`id`), + CONSTRAINT `ccm_content_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `ccm_users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; + +-- +-- Dumping data for table `ccm_content` +-- + +/*!40000 ALTER TABLE `ccm_content` DISABLE KEYS */; +INSERT INTO `ccm_content` (`id`,`topic_id`,`parent_id`,`title`,`subtitle`,`description`,`created_by`,`created_on`,`content`,`deleted`,`position`) VALUES + (1,1,NULL,'test1111111',NULL,'desc 1',1,'2007-11-04 23:55:03','<p>neu 2</p>',0,1), + (2,1,NULL,'test','','desc 2',1,'2007-11-04 23:55:04','<p>new2</p>',0,2), + (4,1,NULL,'test 4','','desc 4',1,'2007-11-04 23:55:06','<h1 class=\"default\" style=\"text-align: center\">Collaborative Content Manipulation</h1>\r\n<p style=\"text-align: center\"><span style=\"font-family: arial,helvetica,sans-serif;\"> </span> first video-conference<br /> \"PHP-division\" <br /> <br />gtest 15.04.08</p>',0,4), + (5,2,0,'dasdsa',NULL,'desc 1',1,'2007-11-04 23:55:05','fadsadsadsdasdsadasdsad',0,2), + (6,2,4,'rev 4',NULL,'desc 4',1,'2007-11-04 23:55:06','<h1>next steps\r\n(ODF)-<br />\r\n</h1>\r\n<br />\r\n<ul class=\"incremental\">\r\n <li>further research on ODF-structure and ODF-API</li>\r\n <li>server-component allows to use OpenOffice as service provider in other applications</li>\r\n <li>evaluate server-based alternatives: e.g. NX-Client</li>\r\n</ul>',0,5), + (7,2,4,'rev 4_2',NULL,'desc 4',1,'2007-11-04 23:55:06','<h1>next steps\r\n(ODF)-<br />\r\n</h1>\r\n<br />\r\n<ul class=\"incremental\">\r\n <li>further research on ODF-structure and ODF-API</li>\r\n <li>server-component allows to use OpenOffice as service provider in other applications</li>\r\n <li>evaluate server-based alternatives: e.g. NX-Client</li>\r\n</ul>',0,5), + (8,2,99,'dasdsadas',NULL,NULL,1,'0000-00-00 00:00:00','test8',1,2), + (9,2,89,'test234',NULL,NULL,2,'0000-00-00 00:00:00','fsadsadas',0,0), + (67,2,NULL,'fdsadsa',NULL,NULL,1,'0000-00-00 00:00:00','fsadsadsa',0,12), + (68,2,NULL,'new','','',1,'0000-00-00 00:00:00','<p>test 2</p>',0,11), + (69,2,85,'69','','',1,'0000-00-00 00:00:00','<p>test</p>',0,10), + (70,2,NULL,'original','','',1,'0000-00-00 00:00:00','<p>2,4</p>',0,6), + (71,2,NULL,'fasdsad',NULL,NULL,1,'0000-00-00 00:00:00','afsadsadsadadsadadddddddddddddddddddddddddddd',0,8), + (72,2,NULL,'test',NULL,NULL,1,'0000-00-00 00:00:00','4213213213',1,9), + (73,2,70,'revision 1','','',1,'0000-00-00 00:00:00','<p>2,4</p>\r\n<p>test edit</p>',0,6), + (78,2,70,'revision','','',1,'0000-00-00 00:00:00','<p>2,4</p>\r\n<p class=\"incremental\">test test test test edit</p>\r\n<p> </p>\r\n<p> </p>',0,6), + (80,2,70,'test3','','',1,'0000-00-00 00:00:00','<p>2,4</p>\r\n<p>test edit</p>',0,6), + (85,2,NULL,'85','','',1,'0000-00-00 00:00:00','<p>test</p>',0,10), + (86,2,85,'86','','',1,'0000-00-00 00:00:00','<p>test2</p>',0,10), + (87,2,85,'87','','',1,'0000-00-00 00:00:00','<p>test</p>',0,10), + (88,2,68,'new','','',1,'0000-00-00 00:00:00','<p>revision</p>',0,11), + (89,2,NULL,'test234',NULL,NULL,2,'0000-00-00 00:00:00','<p>fsadsadas</p>',0,0), + (90,1,2,'test','','desc 2',1,'2007-11-04 23:55:04','<p>new2</p>',0,2), + (91,2,NULL,'test','','',2,'0000-00-00 00:00:00','<p>new</p>',1,7), + (92,2,NULL,'test','','',2,'0000-00-00 00:00:00','<p>test</p>',1,4), + (93,2,NULL,'test','','',2,'0000-00-00 00:00:00','<p>test</p>',1,3), + (94,2,NULL,'new','','',2,'0000-00-00 00:00:00','<p>new</p>',1,1), + (95,2,NULL,'test','','',2,'0000-00-00 00:00:00','<p>test</p>',1,13), + (96,2,NULL,'test','','',2,'0000-00-00 00:00:00','<p>test</p>',0,14), + (97,15,NULL,'test','','',2,'0000-00-00 00:00:00','<p>test</p>',1,0), + (98,22,NULL,'test','','',2,'0000-00-00 00:00:00','<p>test</p>',1,0), + (99,2,NULL,'new','','',1,'0000-00-00 00:00:00','<p>test8</p>',1,2), + (100,23,NULL,'new','','',3,'0000-00-00 00:00:00','<p>new</p>',1,0), + (101,1,4,'test 4','','desc 4',1,'2007-11-04 23:55:06','<h1 class=\"default\" style=\"text-align: center\">Collaborative Content Manipulation</h1>\r\n<p style=\"text-align: center\"><span style=\"font-family: arial,helvetica,sans-serif;\"> </span> first video-conference<br /> \"PHP-division\" <br /> <br />gtest 15.04.08</p>',0,4), + (102,1,1,'test1111111',NULL,'desc 1',1,'2007-11-04 23:55:03','<p>neu 2_2</p>',0,1), + (103,1,NULL,'neu','','',2,'0000-00-00 00:00:00','<p>neu</p>',0,3); +/*!40000 ALTER TABLE `ccm_content` ENABLE KEYS */; + + +-- +-- Definition of trigger `ccm_content_insert` +-- + +DROP TRIGGER /*!50030 IF EXISTS */ `ccm_content_insert`; + +DELIMITER $$ + +CREATE DEFINER = `root`@`localhost` TRIGGER `ccm_content_insert` AFTER INSERT ON `ccm_content` FOR EACH ROW BEGIN + INSERT INTO ccm_content_caches + (id, topic_id, parent_id, title, subtitle, description, created_by, created_on, content, deleted, position) + VALUES + (NEW.id, NEW.topic_id, NEW.parent_id, NEW.title, NEW.subtitle, NEW.description, NEW.created_by, NEW.created_on, NEW.content, NEW.deleted, NEW.position); + END $$ + +DELIMITER ; + +-- +-- Definition of trigger `ccm_content_update` +-- + +DROP TRIGGER /*!50030 IF EXISTS */ `ccm_content_update`; + +DELIMITER $$ + +CREATE DEFINER = `root`@`localhost` TRIGGER `ccm_content_update` AFTER UPDATE ON `ccm_content` FOR EACH ROW BEGIN + UPDATE ccm_content_caches SET + topic_id = NEW.topic_id, parent_id = NEW.parent_id, title = NEW.title, subtitle = NEW.subtitle, + description = NEW.description, created_by = NEW.created_by, created_on = NEW.created_on, + content = NEW.content, deleted = NEW.deleted, position = NEW.position + WHERE id = NEW.id; + END $$ + +DELIMITER ; + +-- +-- Definition of trigger `ccm_content_delete` +-- + +DROP TRIGGER /*!50030 IF EXISTS */ `ccm_content_delete`; + +DELIMITER $$ + +CREATE DEFINER = `root`@`localhost` TRIGGER `ccm_content_delete` AFTER DELETE ON `ccm_content` FOR EACH ROW BEGIN + DELETE FROM ccm_content_caches WHERE id = OLD.id; + END $$ + +DELIMITER ; + +-- +-- Definition of table `ccm_content_caches` +-- + +DROP TABLE IF EXISTS `ccm_content_caches`; +CREATE TABLE `ccm_content_caches` ( + `id` int(10) unsigned NOT NULL auto_increment, + `topic_id` int(11) unsigned NOT NULL, + `parent_id` int(11) default NULL, + `title` varchar(600) collate latin1_general_ci default NULL, + `subtitle` varchar(1500) collate latin1_general_ci default NULL, + `description` varchar(6000) collate latin1_general_ci default NULL, + `created_by` int(10) unsigned NOT NULL, + `created_on` datetime NOT NULL default '0000-00-00 00:00:00', + `content` varchar(55430) collate latin1_general_ci default NULL, + `deleted` tinyint(1) NOT NULL default '0', + `position` int(10) unsigned NOT NULL default '0', + PRIMARY KEY (`id`), + KEY `parent_id` (`parent_id`), + KEY `topic_id` (`topic_id`), + KEY `created_by` (`created_by`) +) ENGINE=MEMORY AUTO_INCREMENT=104 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; + +-- +-- Dumping data for table `ccm_content_caches` +-- + +/*!40000 ALTER TABLE `ccm_content_caches` DISABLE KEYS */; +/*!40000 ALTER TABLE `ccm_content_caches` ENABLE KEYS */; + + +-- +-- Definition of table `ccm_courses` +-- + +DROP TABLE IF EXISTS `ccm_courses`; +CREATE TABLE `ccm_courses` ( + `id` int(10) unsigned NOT NULL auto_increment, + `title` varchar(255) collate latin1_general_ci NOT NULL, + `description` text collate latin1_general_ci NOT NULL, + `deleted` tinyint(1) NOT NULL default '0', + `created_on` datetime NOT NULL, + `created_by` int(11) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; + +-- +-- Dumping data for table `ccm_courses` +-- + +/*!40000 ALTER TABLE `ccm_courses` DISABLE KEYS */; +INSERT INTO `ccm_courses` (`id`,`title`,`description`,`deleted`,`created_on`,`created_by`) VALUES + (1,'default course','',0,'2008-06-22 22:12:59',1), + (2,'Course 2','test2',0,'2008-06-22 22:12:59',1), + (3,'course 3','',0,'2008-06-22 22:12:59',1), + (4,'course 4','',0,'2008-06-22 22:12:59',1); +/*!40000 ALTER TABLE `ccm_courses` ENABLE KEYS */; + + +-- +-- Definition of table `ccm_group_user_course` +-- + +DROP TABLE IF EXISTS `ccm_group_user_course`; +CREATE TABLE `ccm_group_user_course` ( + `id` int(10) unsigned NOT NULL auto_increment, + `user_id` int(10) unsigned NOT NULL, + `group_id` int(10) unsigned NOT NULL, + `course_id` int(10) unsigned NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `user_id` (`user_id`,`course_id`), + KEY `group_id` (`group_id`), + KEY `course_id` (`course_id`), + CONSTRAINT `ccm_group_user_course_ibfk_12` FOREIGN KEY (`user_id`) REFERENCES `ccm_users` (`id`), + CONSTRAINT `ccm_group_user_course_ibfk_13` FOREIGN KEY (`group_id`) REFERENCES `ccm_groups` (`id`), + CONSTRAINT `ccm_group_user_course_ibfk_14` FOREIGN KEY (`course_id`) REFERENCES `ccm_courses` (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=268 DEFAULT CHARSET=utf8; + +-- +-- Dumping data for table `ccm_group_user_course` +-- + +/*!40000 ALTER TABLE `ccm_group_user_course` DISABLE KEYS */; +INSERT INTO `ccm_group_user_course` (`id`,`user_id`,`group_id`,`course_id`) VALUES + (6,2,9,3), + (7,2,8,4), + (80,3,8,3), + (252,2,10,2), + (253,3,9,2), + (254,12,8,2), + (264,2,10,1), + (265,3,10,1), + (266,4,9,1), + (267,11,8,1); +/*!40000 ALTER TABLE `ccm_group_user_course` ENABLE KEYS */; + + +-- +-- Definition of table `ccm_group_user_topic` +-- + +DROP TABLE IF EXISTS `ccm_group_user_topic`; +CREATE TABLE `ccm_group_user_topic` ( + `id` int(10) unsigned NOT NULL auto_increment, + `user_id` int(10) unsigned NOT NULL, + `group_id` int(10) unsigned NOT NULL, + `topic_id` int(10) unsigned NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `user_id` (`user_id`,`topic_id`), + KEY `group_id` (`group_id`), + KEY `topic_id` (`topic_id`), + CONSTRAINT `ccm_group_user_topic_ibfk_12` FOREIGN KEY (`user_id`) REFERENCES `ccm_users` (`id`), + CONSTRAINT `ccm_group_user_topic_ibfk_13` FOREIGN KEY (`group_id`) REFERENCES `ccm_groups` (`id`), + CONSTRAINT `ccm_group_user_topic_ibfk_14` FOREIGN KEY (`topic_id`) REFERENCES `ccm_topics` (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=utf8; + +-- +-- Dumping data for table `ccm_group_user_topic` +-- + +/*!40000 ALTER TABLE `ccm_group_user_topic` DISABLE KEYS */; +INSERT INTO `ccm_group_user_topic` (`id`,`user_id`,`group_id`,`topic_id`) VALUES + (24,2,13,3), + (25,3,13,3), + (26,4,12,3), + (69,2,13,2), + (70,3,13,2), + (78,3,13,1), + (79,4,12,1), + (80,2,13,1); +/*!40000 ALTER TABLE `ccm_group_user_topic` ENABLE KEYS */; + + +-- +-- Definition of table `ccm_groups` +-- + +DROP TABLE IF EXISTS `ccm_groups`; +CREATE TABLE `ccm_groups` ( + `id` int(10) unsigned NOT NULL auto_increment, + `parent_id` int(10) unsigned default NULL, + `title` varchar(255) collate latin1_general_ci NOT NULL, + `description` text collate latin1_general_ci NOT NULL, + `abstract` tinyint(1) NOT NULL default '0', + PRIMARY KEY (`id`), + KEY `parent_id` (`parent_id`) +) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; + +-- +-- Dumping data for table `ccm_groups` +-- + +/*!40000 ALTER TABLE `ccm_groups` DISABLE KEYS */; +INSERT INTO `ccm_groups` (`id`,`parent_id`,`title`,`description`,`abstract`) VALUES + (1,NULL,'ROOT','abstract root',1), + (2,1,'application scope','',1), + (3,1,'course scope','abstract root (course roles)',1), + (4,1,'topic scope','abstract root (topic roles)',1), + (5,2,'Guests','',0), + (6,5,'Users','',0), + (7,6,'Admins','',0), + (8,3,'Guests','dadsada',0), + (9,8,'Students','',0), + (10,9,'Profs','',0), + (11,4,'Guests','',0), + (12,11,'Students','',0), + (13,12,'Profs','',0); +/*!40000 ALTER TABLE `ccm_groups` ENABLE KEYS */; + + +-- +-- Definition of table `ccm_topics` +-- + +DROP TABLE IF EXISTS `ccm_topics`; +CREATE TABLE `ccm_topics` ( + `id` int(10) unsigned NOT NULL auto_increment, + `title` tinytext character set latin1, + `description` mediumtext character set latin1, + `created_by` int(10) unsigned NOT NULL default '0', + `created_on` datetime NOT NULL, + `course_id` int(10) unsigned NOT NULL, + `deleted` tinyint(1) NOT NULL default '0', + PRIMARY KEY (`id`), + KEY `course_id` (`course_id`), + KEY `created_by` (`created_by`), + CONSTRAINT `ccm_topics_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `ccm_users` (`id`), + CONSTRAINT `ccm_topics_ibfk_3` FOREIGN KEY (`course_id`) REFERENCES `ccm_courses` (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; + +-- +-- Dumping data for table `ccm_topics` +-- + +/*!40000 ALTER TABLE `ccm_topics` DISABLE KEYS */; +INSERT INTO `ccm_topics` (`id`,`title`,`description`,`created_by`,`created_on`,`course_id`,`deleted`) VALUES + (1,'S5 introduction','description',1,'2007-11-04 22:24:44',1,0), + (2,'Collaborative Content Manipulation','nix',1,'2007-11-04 22:24:44',2,0), + (3,'test',NULL,1,'0000-00-00 00:00:00',1,1), + (4,'test2','',1,'0000-00-00 00:00:00',1,1), + (5,'test2','test',1,'0000-00-00 00:00:00',1,1), + (6,'test','',2,'0000-00-00 00:00:00',2,0), + (7,'sadsdsad','',2,'0000-00-00 00:00:00',2,1), + (8,'test','',2,'0000-00-00 00:00:00',2,1), + (9,'dasdad','',2,'0000-00-00 00:00:00',2,1), + (10,'dasdad','',2,'0000-00-00 00:00:00',2,1), + (11,'dasdad','',2,'0000-00-00 00:00:00',2,1), + (12,'dasdad','',2,'0000-00-00 00:00:00',2,1), + (13,'dasdad','',2,'0000-00-00 00:00:00',2,1), + (14,'dasdad','',2,'0000-00-00 00:00:00',2,1), + (15,'dasdad','',2,'0000-00-00 00:00:00',2,1), + (16,'dasdad','',2,'0000-00-00 00:00:00',2,1), + (17,'test','',2,'0000-00-00 00:00:00',2,1), + (18,'test','',2,'0000-00-00 00:00:00',2,1), + (19,'test2','',2,'0000-00-00 00:00:00',2,1), + (20,'test2','',2,'0000-00-00 00:00:00',2,1), + (21,'test','',2,'0000-00-00 00:00:00',2,1), + (22,'test2','',2,'0000-00-00 00:00:00',1,1), + (23,'test2','',3,'0000-00-00 00:00:00',2,1); +/*!40000 ALTER TABLE `ccm_topics` ENABLE KEYS */; + + +-- +-- Definition of table `ccm_users` +-- + +DROP TABLE IF EXISTS `ccm_users`; +CREATE TABLE `ccm_users` ( + `id` int(10) unsigned NOT NULL auto_increment, + `group_id` int(10) unsigned NOT NULL, + `username` varchar(20) collate latin1_general_ci NOT NULL, + `password` char(50) collate latin1_general_ci NOT NULL, + `name` varchar(255) collate latin1_general_ci NOT NULL, + `first_name` varchar(255) collate latin1_general_ci NOT NULL, + `member_since` date NOT NULL default '0000-00-00', + `picture_url` varchar(255) collate latin1_general_ci default NULL, + `email` varchar(255) collate latin1_general_ci NOT NULL default 'test@test.de', + `day_of_birth` date default NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `username` (`username`), + UNIQUE KEY `username_2` (`username`), + KEY `group_id` (`group_id`), + CONSTRAINT `ccm_users_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `ccm_groups` (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; + +-- +-- Dumping data for table `ccm_users` +-- + +/*!40000 ALTER TABLE `ccm_users` DISABLE KEYS */; +INSERT INTO `ccm_users` (`id`,`group_id`,`username`,`password`,`name`,`first_name`,`member_since`,`picture_url`,`email`,`day_of_birth`) VALUES + (1,7,'admin','cc03e747a6afbbcbf8be7668acfebee5','','','2008-03-29',NULL,'',NULL), + (2,6,'testprof1','cc03e747a6afbbcbf8be7668acfebee5','test','user','2008-03-29','','','2008-05-17'), + (3,6,'testprof2','cc03e747a6afbbcbf8be7668acfebee5','a','b','2028-01-01','','','2008-05-17'), + (4,6,'testuser1','cc03e747a6afbbcbf8be7668acfebee5','a','b','2028-01-01','','','2008-05-17'), + (8,6,'test','cc03e747a6afbbcbf8be7668acfebee5','a','b','2028-01-01','','test@test.de','2008-05-17'), + (9,6,'user123','cc03e747a6afbbcbf8be7668acfebee5','','','0000-00-00',NULL,'test@test.de',NULL), + (10,6,'user234','cc03e747a6afbbcbf8be7668acfebee5','','','0000-00-00',NULL,'test@test.de',NULL), + (11,6,'user567','cc03e747a6afbbcbf8be7668acfebee5','','','0000-00-00',NULL,'test@test.de',NULL), + (12,6,'user456','cc03e747a6afbbcbf8be7668acfebee5','','','0000-00-00',NULL,'test@test.de',NULL); +/*!40000 ALTER TABLE `ccm_users` ENABLE KEYS */; + + +-- +-- Definition of function `ccm_content_dispatcher` +-- + +DROP FUNCTION IF EXISTS `ccm_content_dispatcher`; + +DELIMITER $$ + +/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$ +CREATE DEFINER=`root`@`localhost` FUNCTION `ccm_content_dispatcher`(topicID int) RETURNS int(11) +BEGIN + + DECLARE cacheNum INTEGER; + DECLARE topicNum INTEGER; + + #check if already cached + SELECT COUNT(*) INTO cacheNum FROM ccm_content_caches WHERE topic_id = topicID; + IF cacheNum > 0 THEN + RETURN 1; + ELSE + #if not cached load content to cache + SELECT COUNT(*) INTO cacheNum FROM ccm_content_caches; + IF cacheNum > 1000 THEN + #delete old stuff from cache + SELECT topic_id INTO topicNum FROM ccm_content_caches ORDER BY id ASC limit 1; + DELETE FROM ccm_content_caches WHERE topic_id = topicNum; + + END IF; + + #write data to caching table + INSERT INTO ccm_content_caches SELECT * FROM ccm_content WHERE topic_id = topicID; + END IF; + + RETURN 1; + +END $$ +/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$ + +DELIMITER ; + + + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;