CREATE TABLE `users` (
`user_id` int(7) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(45) NOT NULL,
`user_mail` varchar(64) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `username` (`user_name`),
UNIQUE KEY `usermail` (`user_mail`)
) ENGINE=InnoDB AUTO_INCREMENT=5 CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `users` VALUES ('1', 'Mike', 'mike@test.de');
CREATE TABLE `posts` (
`post_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(7) unsigned NOT NULL,
`post` varchar(45) COLLATE utf8_bin NOT NULL,
`content` text COLLATE utf8_bin,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`post_id`,`user_id`),
KEY `post_id` (`post_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=952 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `posts` VALUES ('1', '1', 'bene 100', 0x416C697175616D2076656C6974206F64696F2C20706F73756572652065742072686F6E6375732069642C207068617265747261207574206C616375732E4D617572697320636F6E76616C6C69732C2076656C697420696420646170696275732070756C76696E61722C206D61676E6120646F6C6F72207361676974746973206C6F72656D2C2076656C20637572737573206F64696F206C6967756C6120696E206C656F2E, '2009-06-11 20:12:01');
CREATE TABLE `tp_posts` (
`ancestor` bigint(20) unsigned NOT NULL,
`descendant` bigint(20) unsigned NOT NULL,
`depth` tinyint(3) NOT NULL DEFAULT '0',
`weight` tinyint(3) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`ancestor`,`descendant`),
KEY `ancestor` (`ancestor`),
KEY `descendant` (`descendant`),
CONSTRAINT `ancestor` FOREIGN KEY (`ancestor`) REFERENCES `posts` (`post_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `descendant` FOREIGN KEY (`descendant`) REFERENCES `posts` (`post_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `tp_posts` VALUES ('1', '1', '0', '1');
|