Hi all!
After converting SQLite Trac database to MySQL I've got a lot of troubles. Googling showed me that it hadn't been just my problems (with primary keys and so)... So, I came out with a solution for this stuff. I've created a simple MySQL database scheme for Trac with more accurate data types (the main thing is using MySQL VARCHARs instead of "ugly" SQLite TEXT that caused a lot of problems). My MySQL dump is attached and accessible via Web: http://share.auditory.ru/2009/Dmitry.Shurupov/trac/trac_mysql_scheme.sql (Warning: it's not a really GOOD scheme -- it's just a quick hack that really helped me for a few times.) I think it would be a good idea to include such kind of dump into the Trac default installation. -- Dmitry Shurupov, CJSC TrueOffice (www.trueoffice.ru) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Trac Development" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/trac-dev?hl=en -~----------~----~----~----~------~----~------~--~---
-- MySQL (MyISAM) database scheme for Trac -- -- First revision of this SQL script has been made in 2008 -- by Dmitry Shurupov (www.shurupov.ru) from TrueOffice (www.trueoffice.ru) -- -- WARNING: this SQL script drops all the Trac tables before creating the new ones SET character_set_client = utf8; -- -- Table structure for table `attachment` -- DROP TABLE IF EXISTS `attachment`; CREATE TABLE `attachment` ( `type` varchar(64) collate utf8_bin NOT NULL, `id` int(10) collate utf8_bin NOT NULL, `filename` varchar(200) collate utf8_bin NOT NULL, `size` int(11) default NULL, `time` int(11) default 0, `description` text collate utf8_bin, `author` varchar(64) collate utf8_bin, `ipnr` varchar(16) collate utf8_bin, PRIMARY KEY (`type`, `id`, `filename`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `auth_cookie` -- DROP TABLE IF EXISTS `auth_cookie`; CREATE TABLE `auth_cookie` ( `cookie` char(32) collate utf8_bin NOT NULL, `name` varchar(64) collate utf8_bin NOT NULL, `ipnr` varchar(16) collate utf8_bin NOT NULL, `time` int(11) default 0, PRIMARY KEY (`cookie`, `ipnr`, `name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `component` -- DROP TABLE IF EXISTS `component`; CREATE TABLE `component` ( `name` varchar(128) collate utf8_bin NOT NULL, `owner` varchar(64) collate utf8_bin, `description` text collate utf8_bin, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `enum` -- DROP TABLE IF EXISTS `enum`; CREATE TABLE `enum` ( `type` varchar(32) collate utf8_bin NOT NULL, `name` varchar(64) collate utf8_bin NOT NULL, `value` tinyint collate utf8_bin, PRIMARY KEY (`type`, `name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `milestone` -- DROP TABLE IF EXISTS `milestone`; CREATE TABLE `milestone` ( `name` varchar(200) collate utf8_bin NOT NULL, `due` int(11) default 0, `completed` int(11) default 0, `description` text collate utf8_bin, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `node_change` -- DROP TABLE IF EXISTS `node_change`; CREATE TABLE `node_change` ( `rev` varchar(32) collate utf8_bin NOT NULL, `path` varchar(200) collate utf8_bin NOT NULL, `node_type` varchar(32) collate utf8_bin, `change_type` varchar(32) collate utf8_bin NOT NULL, `base_path` varchar(200) collate utf8_bin, `base_rev` varchar(32) collate utf8_bin, PRIMARY KEY (`rev`, `path`, `change_type`), KEY `node_change_rev_idx` (`rev`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `permission` -- DROP TABLE IF EXISTS `permission`; CREATE TABLE `permission` ( `username` varchar(64) collate utf8_bin NOT NULL, `action` varchar(64) collate utf8_bin NOT NULL, PRIMARY KEY (`username`, `action`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `report` -- DROP TABLE IF EXISTS `report`; CREATE TABLE `report` ( `id` int(10) unsigned NOT NULL auto_increment, `author` varchar(64) collate utf8_bin, `title` varchar(200) collate utf8_bin, `query` text collate utf8_bin, `description` text collate utf8_bin, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `revision` -- DROP TABLE IF EXISTS `revision`; CREATE TABLE `revision` ( `rev` varchar(32) collate utf8_bin NOT NULL, `time` int(11) default 0, `author` varchar(64) collate utf8_bin, `message` text collate utf8_bin, PRIMARY KEY (`rev`), KEY `revision_time_idx` (`time`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `session` -- DROP TABLE IF EXISTS `session`; CREATE TABLE `session` ( `sid` varchar(64) collate utf8_bin NOT NULL, `authenticated` boolean NOT NULL default FALSE, `last_visit` int(11) default 0, PRIMARY KEY (`sid`, `authenticated`), KEY `session_last_visit_idx` (`last_visit`), KEY `session_authenticated_idx` (`authenticated`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `session_attribute` -- DROP TABLE IF EXISTS `session_attribute`; CREATE TABLE `session_attribute` ( `sid` varchar(64) collate utf8_bin NOT NULL, `authenticated` boolean NOT NULL default FALSE, `name` varchar(64) collate utf8_bin NOT NULL, `value` text collate utf8_bin, PRIMARY KEY (`sid`, `authenticated`, `name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `system` -- DROP TABLE IF EXISTS `system`; CREATE TABLE `system` ( `name` varchar(128) collate utf8_bin NOT NULL, `value` varchar(128) collate utf8_bin, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `ticket` -- DROP TABLE IF EXISTS `ticket`; CREATE TABLE `ticket` ( `id` int(10) unsigned NOT NULL auto_increment, `type` varchar(64) collate utf8_bin, `time` int(11) default 0, `changetime` int(11) default 0, `component` varchar(128) collate utf8_bin, `severity` varchar(64) collate utf8_bin, `priority` varchar(64) collate utf8_bin, `owner` varchar(64) collate utf8_bin, `reporter` varchar(64) collate utf8_bin, `cc` text collate utf8_bin, `version` varchar(64) collate utf8_bin, `milestone` varchar(200) collate utf8_bin, `status` varchar(64) collate utf8_bin, `resolution` varchar(64) collate utf8_bin, `summary` text collate utf8_bin, `description` text collate utf8_bin, `keywords` text collate utf8_bin, PRIMARY KEY (`id`), KEY `ticket_time_idx` (`time`), KEY `ticket_status_idx` (`status`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `ticket_change` -- DROP TABLE IF EXISTS `ticket_change`; CREATE TABLE `ticket_change` ( `ticket` int(11) NOT NULL default '0', `time` int(11) NOT NULL default 0, `author` varchar(64) collate utf8_bin, `field` varchar(64) collate utf8_bin NOT NULL, `oldvalue` text collate utf8_bin, `newvalue` text collate utf8_bin, PRIMARY KEY (`ticket`, `time`, `field`), KEY `ticket_change_ticket_idx` (`ticket`), KEY `ticket_change_time_idx` (`time`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `ticket_custom` -- DROP TABLE IF EXISTS `ticket_custom`; CREATE TABLE `ticket_custom` ( `ticket` int(11) NOT NULL default '0', `name` varchar(128) collate utf8_bin NOT NULL, `value` text collate utf8_bin, PRIMARY KEY (`ticket`,`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `version` -- DROP TABLE IF EXISTS `version`; CREATE TABLE `version` ( `name` varchar(128) collate utf8_bin NOT NULL, `time` int(11) default 0, `description` text collate utf8_bin, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- -- Table structure for table `wiki` -- DROP TABLE IF EXISTS `wiki`; CREATE TABLE `wiki` ( `name` varchar(128) collate utf8_bin NOT NULL, `version` int(11) NOT NULL default '0', `time` int(11) default 0, `author` varchar(64) collate utf8_bin, `ipnr` varchar(16) collate utf8_bin, `text` text collate utf8_bin, `comment` text collate utf8_bin, `readonly` boolean default NULL, PRIMARY KEY (`name`, `version`), KEY `wiki_time_idx` (`time`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
