-- 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 */;