This is a little historic explanation how I took over COMPET-N, Doom speedruning site that was abandoned for ~7 years.
Two years ago I asked BahdKo if it’s possible to revive COMPET-N site or at least update demos in database. I was limited with time, but the main problem was that when I saw existing CGI code for database I knew that I will not be able to handle it since I didn’t have my own server where I could do whatever I want. So I just gave up, and said to myself, “Some other time.”.
I was not aware that Anders and Ocelot also wanted to do the same thing at almost the same time!
Well, five months ago (5/2012) I was kinda bored while I was on maternity leave with my daughter Jelena while she was sleeping during the day, so I decided to take a look again and asked BahdKo if I could take another look into COMPET-N source and that I’ll let her know after few days what I’m going to do with it, if anything at all.
This time I had my own Debian dedicated servers, where I have all my webpages (this one also runs on top of one), ZDaemon servers and other neat things I always wanted to have and learn. So, after I’ve downloaded BahdKo’s COMPET-N tar.gz and uploaded source to one of my servers I found out that original COMPET-N webpage is working just fine without any modifications (http://www.doom.com.hr/compet-n/index.cgi).
Next step was database and that didn’t work at all. Database is C++ code run as CGI script, and since I never did anything with C++ as CGI I did a Google search and found out I have to recompile it since I run 64bit sytem with simple make clean and make and make binaries executable with chmod +x. Voila! It worked flawless and simple after all.
“So, now I have a COMPET-N mirror.” – I said to myself. That’s good, now let’s see can I modify database easy. I found some big *.log files with players, countries and records, so.. “That’s it!” I said to myself and tried to add some new records to see will it effect the database output (http://www.doom.com.hr/compet-n/database/cn.cgi) , but it didn’t work so I was messing around with this for an hour, looking into code (.h and .c) and .log files and there were many similar .log files, so I decided to remove one by one to see when it will affect database. I found out that I edited the right .log file but still there were no changes in output. Doh!
It must be something else, so I looked further into code and saw in cn.c internalyear=2005 , after modifying this to 2012 and doing make (not sure if that had to be done but I did it) again database output changed! Yay!
I was really happy that it all worked out in about two hours. At that moment I decided to ask BahdKo if I can revive COMPET-N as I figured out how it’s all working. After some talk with her and facts about COMPET-N, I started fixing links and putting things back to it’s place. Next day I posted on Doomworld forums about my intentions and got pretty good reactions from old COMPET-N players and even from some new ones. Later joined irc.quakenet.org, channel #nightmare, nick fx02 to see if our old hideout is still alive and I was kindly surprised to see there some old COMPET-N bastards. 🙂
COMPET-N was few months later incorporated with doom.com.hr page for easier maintenance.
In short that was the history of reviving COMPET-N. Now let’s see how old database structure looked like, how it was modified (at least what I think it was done) and what I decided to do.
—
Adam Hegyi’s old database was structured like this: records.log, maps.log, categories.log, players.log and country.log. I’ve never did anything with MySQL in my life but after doing some Google search I’ve decided to create tables: cn_records, cn_players, cn_maps, cn_wads, cn_category, cn_country, cn_continent.
STRUCTURE
I’ve taken almost all data from log files, but with some additional columns:
Country old: code, name
Country new: code, name, full name, iso3, num_code, continentPlayers old: nickname, full name, country, email, cheater
Players new: id, first_name, last_name, nickname, date of birth, country, email, www, cheater, info
I’ve had lots of problems with deciding how maps table should be formatted. I think I’ve changed that table like four or five times, and when I changed maps table data I had to change maps data in all records too.
Maps old: 01:02:03…
Maps new: id, name, full name, prboom time, nomo timeCategory old: category, type, full name
Category new: id, name, full name, points, path
The most important was records.log which was formatted like this:
Records old: udoom:11:speed:e1m1-015:0:15:25:04:1995:widlake:
Records new: id, wad id, category id, map id, player1 id, player2 id, player3 id, player4 id, time, filename, date, record
I’ve converted old table to csv with notepad++ to something like this:
"";"1";"1";"111";"143";"";"";"";"15";"e1m1-015";"1995-04-25";"";
Also I needed two new tables:
Wads: id, short name, name, full name, filename, path
Continents: id, name
Since I was a total newb with MySQL tables I’ve asked my friends to help/criticize/suggest and finally got structure like this:
CREATE TABLE IF NOT EXISTS cn_records ( record_id int(6) NOT NULL auto_increment, wad_id int(2) NOT NULL, category_id int(2) NOT NULL, map_id int(3) NOT NULL, player1_id int(3) NOT NULL, player2_id int(3) NOT NULL, player3_id int(3) NOT NULL, player4_id int(3) NOT NULL, record_time time NULL, filename varchar(50) COLLATE utf8_bin NOT NULL, record_date date NOT NULL, current_record int(1) NOT NULL, PRIMARY KEY (record_id)) CHARSET=utf8 COLLATE=utf8_bin;
That was the first step and took me ~two weeks (~1 hour per day) to figure out everything (including database conversion).
We finally have database in order, let’s start with some php/mysql examples!
to be continued…