-- MySQL dump 10.13  Distrib 5.1.31, for Win32 (ia32)
--
-- Host: localhost    Database: tlb
-- ------------------------------------------------------
-- Server version	5.1.31-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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


--
-- Table structure for table `activesession`
--

DROP TABLE IF EXISTS `activesession`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `activesession` (
  `posterid` int(11) DEFAULT NULL,
  `logindate` datetime DEFAULT NULL,
  `gooduntildate` datetime DEFAULT NULL,
  `uuid` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `flagcodes`
--

DROP TABLE IF EXISTS `flagcodes`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `flagcodes` (
  `flagId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `flagName` varchar(25) NOT NULL,
  `threshold` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`flagId`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

/*!40000 ALTER TABLE `flagcodes` DISABLE KEYS */;
INSERT INTO `flagcodes` VALUES   (1,'Best Of',15);
INSERT INTO `flagcodes` VALUES   (2,'Helpful',15);
INSERT INTO `flagcodes` VALUES   (3,'Spam',15);
INSERT INTO `flagcodes` VALUES   (4,'NSFW',15);
INSERT INTO `flagcodes` VALUES   (5,'TOS Violation',15);
/*!40000 ALTER TABLE `flagcodes` ENABLE KEYS */;





--
-- Table structure for table `moniker`
--

DROP TABLE IF EXISTS `moniker`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `moniker` (
  `monikerId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `posterId` int(10) unsigned DEFAULT NULL,
  `name` varchar(45) NOT NULL,
  `tagLine` varchar(45) DEFAULT NULL,
  `postCount` int(10) unsigned DEFAULT NULL,
  `showCounter` bit(1) DEFAULT NULL,
  `display` bit(1) DEFAULT NULL,
  PRIMARY KEY (`monikerId`)
) ENGINE=InnoDB AUTO_INCREMENT=1593 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `post`
--

DROP TABLE IF EXISTS `post`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `post` (
  `postId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `postDate` datetime DEFAULT NULL,
  `lft` int(10) unsigned NOT NULL,
  `rgt` int(10) unsigned NOT NULL,
  `threadId` int(10) unsigned DEFAULT NULL,
  `treeId` int(10) unsigned DEFAULT NULL,
  `tos` tinyint(1) NOT NULL DEFAULT '0',
  `nsfw` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `spam` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`postId`),
  KEY `PostKeyCols` (`threadId`,`treeId`)
) ENGINE=InnoDB AUTO_INCREMENT=62994 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `postcontent`
--

DROP TABLE IF EXISTS `postcontent`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `postcontent` (
  `postId` int(10) unsigned NOT NULL,
  `monikerId` int(10) unsigned NOT NULL,
  `title` varchar(63) DEFAULT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`postId`),
  FULLTEXT KEY `PC_FT_IDX` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `poster`
--

DROP TABLE IF EXISTS `poster`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `poster` (
  `posterId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userName` varchar(12) NOT NULL,
  `password` varchar(41) NOT NULL,
  `active` bit(1) DEFAULT NULL,
  `primaryMonikerId` int(10) unsigned DEFAULT NULL,
  `userLevelId` tinyint(1) NOT NULL DEFAULT '0',
  `nsfw` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `spam` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `salt` tinyint(3) unsigned NOT NULL,
  `inlineReply` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`posterId`)
) ENGINE=InnoDB AUTO_INCREMENT=1056 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
SET character_set_client = @saved_cs_client;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `t_poster_bi` BEFORE INSERT ON `poster` FOR EACH 
ROW SET new.password = password(concat(new.salt, new.password)) */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Table structure for table `postflag`
--

DROP TABLE IF EXISTS `postflag`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `postflag` (
  `postFlagId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `postId` int(10) unsigned NOT NULL,
  `flagDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `flagId` tinyint(3) unsigned NOT NULL,
  `posterId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`postFlagId`)
) ENGINE=InnoDB AUTO_INCREMENT=3862 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `privatemessage`
--

DROP TABLE IF EXISTS `privatemessage`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `privatemessage` (
  `pmId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fromMonikerId` int(10) unsigned NOT NULL,
  `fromPosterId` int(10) unsigned NOT NULL,
  `toMonikerId` int(10) unsigned NOT NULL,
  `sentDate` datetime NOT NULL,
  `title` varchar(45) NOT NULL,
  `message` varchar(2000) NOT NULL,
  PRIMARY KEY (`pmId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `sticky`
--

DROP TABLE IF EXISTS `sticky`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `sticky` (
  `stickyId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `threadId` int(10) unsigned NOT NULL,
  `startDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `endDate` datetime DEFAULT NULL,
  `active` bit(1) NOT NULL DEFAULT b'0',
  `posterId` int(11) DEFAULT NULL,
  PRIMARY KEY (`stickyId`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `userlevel`
--

DROP TABLE IF EXISTS `userlevel`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `userlevel` (
  `userLevelId` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `levelDesc` varchar(25) NOT NULL,
  PRIMARY KEY (`userLevelId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


/*!40000 ALTER TABLE `userlevel` DISABLE KEYS */;
INSERT INTO `userlevel` VALUES   (1,'Poster');
INSERT INTO `userlevel` VALUES   (2,'Mod');
INSERT INTO `userlevel` VALUES   (3,'Admin');
/*!40000 ALTER TABLE `userlevel` ENABLE KEYS */;




--
-- Definition of procedure `addnewpost`
--

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `addnewpost`(
  in p_monikerid int,
    in p_title varchar(63),
    in p_content text,
    in p_nsfw boolean
  )
begin
      insert into post (postdate, lft, rgt, nsfw) values (date_add(now(), interval -5 hour), 1, 2, p_nsfw);
      set @newpostid := last_insert_id();
      update post set threadid = @newpostid, treeid = @newpostid where postid = @newpostid;
      insert into postcontent (postid, monikerid, title, content) values (@newpostid, p_monikerid, p_title, p_content);
      update moniker set postcount = postcount + 1 where monikerid = p_monikerid;
    select @newpostid as threadid;
  commit;
end $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `addpost`
--

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `addpost`(
in p_threadid int,
  in p_treeid int,
  in p_parent int,
  in p_monikerid int,
  in p_content text,
  in p_nsfw boolean
)
begin
  start transaction;
  preposter:begin
  select
    @parentlft := p.lft,
    @parentrgt := p.rgt
  from
    post p
  where
    p.postid = p_parent;
  if(@parentlft is null or @parentlft < 1 or @parentlft >= @parentrgt or p_treeid is null)
  then
    leave preposter;
  end if;
  if(p_treeid = 0)  
  then
    insert into post (threadid, postdate, lft, rgt, nsfw) values (p_threadid, date_add(now(), interval -5 hour), 1, 2, 
p_nsfw);
    set @newpostid := last_insert_id();
    update post set treeid = @newpostid where postid = @newpostid;
    insert into postcontent (postid, monikerid, content, title) values (@newpostid, p_monikerid, p_content, null);
  else
    if (@parentlft + 1 = @parentrgt)
    then
      update post set rgt = rgt + 2 where threadid = p_threadid and treeid = p_treeid and rgt >= @parentlft;
      update post set lft = lft + 2 where threadid = p_threadid and treeid = p_treeid and lft > @parentlft;
      insert into post (threadid, treeid, postdate, lft, rgt, nsfw) values(p_threadid, p_treeid, date_add(now(), interval -5 
hour), @parentlft + 1, @parentlft + 2, p_nsfw);
    else
      update post set rgt = rgt + 2 where threadid = p_threadid and treeid = p_treeid and rgt >= @parentrgt;
      update post set lft = lft + 2 where threadid = p_threadid and treeid = p_treeid and lft > @parentrgt;
      insert into post (threadid, treeid, postdate, lft, rgt, nsfw) values (p_threadid, p_treeid, date_add(now(), interval 
-5 hour), @parentrgt, @parentrgt + 1, p_nsfw);
    end if;
    insert into postcontent (postid, monikerid, content, title) values (last_insert_id(), p_monikerid, p_content, null);
  end if;
  update moniker set postcount = postcount + 1 where monikerid = p_monikerid;
  end;
  commit;
end $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;








-- Dump completed on 2009-08-22 17:32:04