MySQL table structure

[jcampion]

Hi!

I'm seriously considering moving to vBulletin, and am contemplating moving my data from one forum software (UltraBoard 2000) to vB.

Can anyone provide me with any kind of list of tables and their structures, so I can attempt to tell how hard this will be?

I looked at the lite version, but obviously there are going to be significant differences between that and the full version!

Thanks!

[TechTalk]

Not sure if I should do this or not (although I dont see it hurting anything) Hopefully this will help you in your decision to purchase a vb.

werehere: if you feel this isnt appropriate please remove it.

vbulletin's table structure:

# --------------------------------------------------------
#
# Table structure for table 'announcement'
#

CREATE TABLE announcement (
announcementid smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
title varchar(50) NOT NULL,
userid smallint(5) unsigned DEFAULT '0' NOT NULL,
startdate int(10) unsigned DEFAULT '0' NOT NULL,
enddate int(10) unsigned DEFAULT '0' NOT NULL,
pagetext mediumtext NOT NULL,
forumid smallint(6) DEFAULT '0' NOT NULL,
PRIMARY KEY (announcementid)
);


# --------------------------------------------------------
#
# Table structure for table 'bbcode'
#

CREATE TABLE bbcode (
bbcodeid smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
findcode text NOT NULL,
replacecode text NOT NULL,
PRIMARY KEY (bbcodeid)
);


# --------------------------------------------------------
#
# Table structure for table 'category'
#

CREATE TABLE category (
categoryid smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
title char(100) NOT NULL,
displayorder smallint(6) DEFAULT '0' NOT NULL,
PRIMARY KEY (categoryid)
);


# --------------------------------------------------------
#
# Table structure for table 'forum'
#

CREATE TABLE forum (
forumid smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
title char(100) NOT NULL,
description char(250) NOT NULL,
active smallint(6) DEFAULT '0' NOT NULL,
displayorder smallint(6) DEFAULT '0' NOT NULL,
replycount int(10) unsigned DEFAULT '0' NOT NULL,
lastpost int(11) DEFAULT '0' NOT NULL,
lastposter char(50) NOT NULL,
threadcount mediumint(8) unsigned DEFAULT '0' NOT NULL,
categoryid smallint(5) unsigned DEFAULT '0' NOT NULL,
allowposting smallint(6) DEFAULT '0' NOT NULL,
daysprune smallint(5) unsigned DEFAULT '0' NOT NULL,
forumtemplate char(100) NOT NULL,
headertemplate char(100) NOT NULL,
useadvheader smallint(6) DEFAULT '0' NOT NULL,
footertemplate char(100) NOT NULL,
useadvfooter smallint(6) DEFAULT '0' NOT NULL,
forumbittemplate char(100) NOT NULL,
threadtemplate char(100) NOT NULL,
threadbittemplate char(100) NOT NULL,
replytemplate char(100) NOT NULL,
newthreadtemplate char(100) NOT NULL,
edittemplate char(100) NOT NULL,
rulestemplate char(100) NOT NULL,
titleimage char(100) NOT NULL,
replyimage char(100) NOT NULL,
newthreadimage char(100) NOT NULL,
closedthreadimage char(100) NOT NULL,
newpostemail char(250) NOT NULL,
newthreademail char(250) NOT NULL,
moderatenew smallint(6) DEFAULT '0' NOT NULL,
allowbbcode smallint(6) DEFAULT '0' NOT NULL,
allowimages smallint(6) DEFAULT '0' NOT NULL,
allowhtml smallint(6) DEFAULT '0' NOT NULL,
allowsmilies smallint(6) DEFAULT '0' NOT NULL,
PRIMARY KEY (forumid)
);


# --------------------------------------------------------
#
# Table structure for table 'forumpermission'
#

CREATE TABLE forumpermission (
forumpermissionid smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
forumid smallint(5) unsigned DEFAULT '0' NOT NULL,
usergroupid smallint(5) unsigned DEFAULT '0' NOT NULL,
canview smallint(6) DEFAULT '0' NOT NULL,
cansearch smallint(6) DEFAULT '0' NOT NULL,
canemail smallint(6) DEFAULT '0' NOT NULL,
canpostnew smallint(6) DEFAULT '0' NOT NULL,
canreply smallint(6) DEFAULT '0' NOT NULL,
canadminedit smallint(6) DEFAULT '0' NOT NULL,
canedit smallint(6) DEFAULT '0' NOT NULL,
candelete smallint(6) DEFAULT '0' NOT NULL,
canopenclose smallint(6) DEFAULT '0' NOT NULL,
canmove smallint(6) DEFAULT '0' NOT NULL,
PRIMARY KEY (forumpermissionid)
);


# --------------------------------------------------------
#
# Table structure for table 'icon'
#

CREATE TABLE icon (
iconid smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
title char(100) NOT NULL,
iconpath char(100) NOT NULL,
PRIMARY KEY (iconid)
);

# --------------------------------------------------------
#
# Table structure for table 'post'
#

CREATE TABLE post (
postid int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
threadid int(10) unsigned DEFAULT '0' NOT NULL,
username varchar(50) NOT NULL,
userid int(10) unsigned DEFAULT '0' NOT NULL,
title varchar(100) NOT NULL,
dateline int(11) DEFAULT '0' NOT NULL,
pagetext mediumtext,
allowsmilie smallint(6) DEFAULT '0' NOT NULL,
email smallint(6) DEFAULT '0' NOT NULL,
signature smallint(6) DEFAULT '0' NOT NULL,
ipaddress varchar(16) NOT NULL,
iconid smallint(5) unsigned DEFAULT '0' NOT NULL,
visible smallint(6) DEFAULT '0' NOT NULL,
PRIMARY KEY (postid),
KEY idxdisp (threadid, dateline)
);


# --------------------------------------------------------
#
# Table structure for table 'replacement'
#

CREATE TABLE replacement (
replacementid smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
findword text NOT NULL,
replaceword text NOT NULL,
PRIMARY KEY (replacementid)
);


# --------------------------------------------------------
#
# Table structure for table 'session'
#

CREATE TABLE session (
sessionid mediumint(8) unsigned DEFAULT '0' NOT NULL auto_increment,
userid smallint(5) unsigned DEFAULT '0' NOT NULL,
ipaddress char(20) NOT NULL,
lastactivity int(10) unsigned DEFAULT '0' NOT NULL,
location char(250) NOT NULL,
PRIMARY KEY (sessionid)
);


# --------------------------------------------------------
#
# Table structure for table 'smilie'
#

CREATE TABLE smilie (
smilieid smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
title char(100) NOT NULL,
smilietext char(10) NOT NULL,
smiliepath char(100) NOT NULL,
PRIMARY KEY (smilieid)
);


# --------------------------------------------------------
#
# Table structure for table 'template'
#

CREATE TABLE template (
templateid smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
title varchar(100) NOT NULL,
template mediumtext NOT NULL,
PRIMARY KEY (templateid)
);


# --------------------------------------------------------
#
# Table structure for table 'thread'
#

CREATE TABLE thread (
threadid int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
title varchar(100) NOT NULL,
lastpost int(10) unsigned DEFAULT '0' NOT NULL,
forumid smallint(5) unsigned DEFAULT '0' NOT NULL,
open smallint(6) DEFAULT '0' NOT NULL,
replycount int(10) unsigned DEFAULT '0' NOT NULL,
postusername varchar(50) NOT NULL,
lastposter varchar(50) NOT NULL,
dateline int(10) unsigned DEFAULT '0' NOT NULL,
views smallint(5) unsigned DEFAULT '0' NOT NULL,
iconid smallint(5) unsigned DEFAULT '0' NOT NULL,
notes varchar(250) NOT NULL,
visible smallint(6) DEFAULT '0' NOT NULL,
subjectindex varchar(100) NOT NULL,
threadindex mediumtext NOT NULL,
userindex mediumtext NOT NULL,
PRIMARY KEY (threadid)
);


# --------------------------------------------------------
#
# Table structure for table 'user'
#

CREATE TABLE user (
userid int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
usergroupid smallint(5) unsigned DEFAULT '0' NOT NULL,
username varchar(50) NOT NULL,
password varchar(50) NOT NULL,
email varchar(50) NOT NULL,
parentemail varchar(50) NOT NULL,
coppauser smallint(6) DEFAULT '0' NOT NULL,
homepage varchar(100) NOT NULL,
icq varchar(20) NOT NULL,
aim varchar(20) NOT NULL,
yahoo varchar(20) NOT NULL,
biography mediumtext NOT NULL,
signature mediumtext NOT NULL,
adminemail smallint(6) DEFAULT '0' NOT NULL,
showemail smallint(6) DEFAULT '0' NOT NULL,
invisible smallint(6) DEFAULT '0' NOT NULL,
usertitle varchar(250) NOT NULL,
customtitle smallint(6) DEFAULT '0' NOT NULL,
joindate int(10) unsigned DEFAULT '0' NOT NULL,
canpost smallint(6) DEFAULT '0' NOT NULL,
cookieuser smallint(6) DEFAULT '0' NOT NULL,
daysprune smallint(6) DEFAULT '0' NOT NULL,
lastvisit int(10) unsigned DEFAULT '0' NOT NULL,
lastactivity int(10) unsigned DEFAULT '0' NOT NULL,
lastpost int(10) unsigned DEFAULT '0' NOT NULL,
posts smallint(5) unsigned DEFAULT '0' NOT NULL,
timezoneoffset smallint(6) DEFAULT '0' NOT NULL,
emailnotification smallint(6) DEFAULT '0' NOT NULL,
PRIMARY KEY (userid),
KEY idxgroups (username, usergroupid)
);


# --------------------------------------------------------
#
# Table structure for table 'usergroup'
#

CREATE TABLE usergroup (
usergroupid smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
title char(100) NOT NULL,
usertitle char(100) NOT NULL,
canview smallint(6) DEFAULT '0' NOT NULL,
cansearch smallint(6) DEFAULT '0' NOT NULL,
canemail smallint(6) DEFAULT '0' NOT NULL,
canpostnew smallint(6) DEFAULT '0' NOT NULL,
canreply smallint(6) DEFAULT '0' NOT NULL,
canadminedit smallint(6) DEFAULT '0' NOT NULL,
canedit smallint(6) DEFAULT '0' NOT NULL,
candelete smallint(6) DEFAULT '0' NOT NULL,
canopenclose smallint(6) DEFAULT '0' NOT NULL,
canmove smallint(6) DEFAULT '0' NOT NULL,
cancontrolpanel smallint(6) DEFAULT '0' NOT NULL,
PRIMARY KEY (usergroupid)
);


# --------------------------------------------------------
#
# Table structure for table 'usertitle'
#

CREATE TABLE usertitle (
usertitleid smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
minposts smallint(5) unsigned DEFAULT '0' NOT NULL,
title char(250) NOT NULL,
PRIMARY KEY (usertitleid)
);


HTH

~Chris

[Freddie Bingham]

If this is not allowed (?), you could always find the table structure anyway from posts where users have listed there indexes (along with the table structure).

[jcampion]

Thanks! It definitely uses MySQL much better than UltraBoard 2000, which keeps all the data in MySQL, except the posts, which are in flatfiles, so it seems to me like it has to do twice the work.

The problem is converting. It won't be simple because of that dual-structure format.

I'm going to try, though, so I have decided to purchase vB.

[doron]

the mysql table alone should not be a big secret, it's the code that is important.

[TechTalk]

Originally posted by jcampion
Thanks! It definitely uses MySQL much better than UltraBoard 2000, which keeps all the data in MySQL, except the posts, which are in flatfiles, so it seems to me like it has to do twice the work.

The problem is converting. It won't be simple because of that dual-structure format.

I'm going to try, though, so I have decided to purchase vB.

Mark another sale up for me :D Hey james, when do commission checks go out? ;)

~Chis

[chrispadfield]

probably sometime after the next release ... so march .. :) (just kidding there)

[jcampion]

I bought it, now all I've got to do is learn PHP and MySQL, and write a script to convert UB2K...Oh boy...long nights ahead...Why didn't I buy vB to start with?

[Edited by jcampion on 09-14-2000 at 08:56 PM]

[chrispadfield]

it is not easy, i tried to do the same for boardpower but dismally failed. I had to resort to copy and paste! Good luck though

[jcampion]

Well, I'm cautiously optimistic. UB2K stores most of its data in MySQL, and each posts' text is in an individual file. I'm hoping it won't be too hard.