When I started Tabulas, I literally knew nothing about best programming practices. All I was interested in was to see how hard it would be make something that was better than Xanga and LiveJournal. Looking back, that naivete about writing webapps was a really good thing in keeping me focused (nowadays, all my "knowledge" about the potential pitfalls about writing/deploying a webapp really hinder my abilities to start up new projects).

So, over the past few years, I've been slowly phasing out "bad" code (written back in '04 when I knew nothing!) in favor of "good" code (code which still has a shelf-life of maybe another six months before I refactor it again). Last week, I finally disabled http://my.tabulas.com/, the old control panel. That means all users are now using the new control panel; coupled with the new front-end, this is finally the Tabulas 2.0 I wrote about back in 2004/2005 (if you're keeping count, this was the third 2.0 version that was promised). (The next step would be to start implementing the promised 2.1 features)

Now that I've finally consolidated the codebase to one single codepath (yes, it was running multiple copies all over the place before!), the next step is the database cleanup.

Right now, I have 96 tables; most which I don't know what they're doing. The naming schema for tables is all over the place, too. For example, the old users table:

CREATE TABLE `users` (
  `userid` mediumint(8) unsigned NOT NULL auto_increment,
  `username` varchar(16) NOT NULL default '',
  `user_pass` varchar(32) NOT NULL default '',
  `user_pass_temp` varchar(32) NOT NULL default '',
  `password` varchar(12) NOT NULL default '',
  `password2` varchar(32) NOT NULL default '',
  `email` varchar(64) NOT NULL default '',
  `joined` date NOT NULL default '0000-00-00',
  `code` varchar(11) NOT NULL default '0',
  `status` tinyint(1) NOT NULL default '0',
  `lastlogin` date NOT NULL default '0000-00-00',
  `totalFriends` smallint(5) unsigned NOT NULL default '0',
  `userHash` varchar(32) NOT NULL default '',
  `server` varchar(25) NOT NULL default 'jbiel.tabulas.com',
  `suspended` tinyint(1) NOT NULL default '0',
  `user_history` text NOT NULL,
  `user_cache` text NOT NULL,
  `user_option` text NOT NULL,
  `user_upgrade` tinyint(1) NOT NULL default '0',
  `user_ads` tinyint(1) NOT NULL default '0',
  `userStats` text NOT NULL,
  `userType` tinyint(1) NOT NULL default '0',
  `userHost` tinyint(1) NOT NULL default '0',
  `userSalt` varchar(32) NOT NULL default '',
  `userAccounts` text NOT NULL,
  `userIconId` int(10) unsigned default NULL,
  PRIMARY KEY  (`userid`),
  KEY `username` (`username`),
  KEY `email` (`email`)
)

Oh yeah. Notice the passwords duplication? (That was when I realized storing plaintext passwords was bad - don't worry, there hasn't been any data stored there for a while as I wiped them, but I left the columns active as I didn't want to dive into my old code and remove the column calls). Notice the complete lack of standard naming of the columns? Nice. (Not)

Fortunately, foreseeing this work item, all the current code goes through a layer of abstraction which lets me change the table/column names dynamically once in the code, so updating these tables/columns are relatively easy for me to do.

So today, I cleaned up the users table by naming everything consistently and dropping unused columns. How much data was redundant and unused?

root@host [~/mysql_backups]# ls -al tabulas.users*
-rw-r--r--  1 root root 18432778 Aug 24 21:11 tabulas.users.new.sql
-rw-r--r--  1 root root 26496439 Aug 24 20:53 tabulas.users.sql

... about 30%. crazy.gif God, I'm such a horrible developer. Here's the final structure:

CREATE TABLE `users` (
`user_id` mediumint(8) unsigned NOT NULL auto_increment,
`user_name` varchar(16) NOT NULL default '',
`user_email` varchar(64) NOT NULL default '',
`user_status` tinyint(1) NOT NULL default '0',
`user_suspended` tinyint(1) NOT NULL default '0',
`user_created` date NOT NULL default '0000-00-00',
`user_updated` date NOT NULL default '0000-00-00',
`user_host` tinyint(1) NOT NULL default '0',
`user_salt` varchar(32) NOT NULL default '',
`user_hash` varchar(32) NOT NULL default '',
`user_pass` varchar(32) NOT NULL default '',
`user_pass_temp` varchar(32) NOT NULL default '',
`user_logins` text NOT NULL,
`user_options` text NOT NULL,
`user_icon_id` int(10) unsigned default NULL,
PRIMARY KEY (`user_id`),
KEY `user_name` (`user_name`),
KEY `user_email` (`user_email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 

Expect a lot more of these posts over the coming weeks as I tackle these changes bit by bit.

And if Tabulas is throwing any errors, let me know.

Posted by roy on August 24, 2008 at 02:36 PM in Web Development, Tabulas | 8 Comments

Related Entries

Want to comment with Tabulas?. Please login.

Comment posted on August 27th, 2008 at 07:49 PM
I'm going thru a bunch of old code from 2002 too... gosh.. can't believe my programming style back then!
Comment posted on August 24th, 2008 at 08:16 PM
looks like things sped up a bit...good work
Comment posted on August 24th, 2008 at 08:04 PM
i never really thought about it, but tabulas is mostly db, huh?
Comment posted on August 24th, 2008 at 08:41 PM
mostly db? the storage engine, sure. there's a lot of logic in the app layer though. you think it's easy to make things look so nice?!
Comment posted on August 26th, 2008 at 07:50 AM
who said anything about looking nice?
Comment posted on August 24th, 2008 at 02:45 PM
but you are really great....Thanks for creating tabulas Roy. Thank you gazillion times.
Comment posted on August 24th, 2008 at 02:47 PM
i'm just glad that other people are enjoying tabulas! :) thank YOU for using it :)
Comment posted on August 24th, 2008 at 02:48 PM
If there is more to saying "i'm glad", that's exactly how I feel.

Thank you thank you for giving me a home. THANK YOU ROY!