Login   Register  
PHP Classes
elePHPant
Icontem

File: sample.sql

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of Tom Schaefer  >  Closure Table  >  sample.sql  >  Download  
File: sample.sql
Role: Auxiliary data
Content type: text/plain
Description: sample db layout
Class: Closure Table
Manage transitive closure tables stored in MySQL
Author: By
Last change: change
Date: 2009-06-28 04:25
Size: 2,055 bytes
 

Contents

Class file image Download
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');