Buffyverse Dialogue Database

Nov 27, 2002 (from my LJ)

So the other day things were slow, I was bored, and I thought to myself: hey, you know that conversion of the BuffyDB to PHP you've been thinking about in that vague way that tends to indicate it's not really going to happen? Why not start now? And I thought to myself: self... that's a good idea! I had no real plans to make any solid progress, just a little dabble here and there to get the ball rolling. That ball was a slick one though, it got rather a lot further than I would have expected. This PHP thing's not so tough!

First a little background. As the system currently exists, I have a local database (Access) and a set of editing scripts written in Cold Fusion, also running locally. I have another set of scripts that dives into the DB, grabs the data, and formats it into static HTML pages, which are then transferred by FTP to the server. This gives me some of the primary the advantages of a DB-based site without the need to fork over for CF hosting, which was the only server-side scripting language I knew at the time.

I still think this is a solid way to go for a small site, cheap, reliable, easy. Unfortunately, BuffyDB is by no stretch of the imagination a small site. There are something in the neighborhood of 4,000 pages. Transferring new versions of all those static files takes around 45 minutes via ftp. I managed to cut down on that by modifying my uploading script so that it only uploads new versions of pages that it thinks have changed, but that's always a little iffy 'cause the algorithm for selecting "modified" pages is very complex (for example, let's say I edit a clip record. That means that the arc page for any arc that that clips was part of might potentially change, as well as the episode page, and any other page that references that clip. And that's a simple example). So once a month, I do a full sweep and upload new versions of *all* the pages, and that takes forever and a year. But the kicker is the load count/votes. I like to keep track of those, but a generic log analyser's not going to cut it. So I learned a smattering of cgi and was able to write a couple of little scripts that keep counters going, stored in one text file for each clip. Every month, these are all reset. But all these little text files have to be downloaded and then parsed out so that they can be inserted into the database. Every.Time.I.Want.Current.Stats. Takes a *long* time. And have you ever had a zillion tiny text files in one directory? Windows really doesn't like that.

All this to say, a change is needed, I've outgrown the old system. Enter PHP. My Knight in Shiny Armor.

My new PHP-enabled web-space comes with MySQL rather than access, so I can't just ftp my local db up to the server and live happily ever after. I don't use a gui interface to the db, preferring to do all my manipulations via SQL statements (surpise, no?), so importing the data required some trickiness. The simplest thing to do (and it's not that simple!) is to write yet another local script that outputs the entire contents of the Access database to a text file. The trick is to format the data into new sql statements which I can run on the server to insert the data into the MySQL database. Got that? Don't worry, I'll come back to it. But what do I do when I need to make changes? I'd have to build a new set of editing pages in PHP on the server. And during the month or so that I anticipate running both the current version and a beta of the PHP, I'd have to make the changes to *both* databases. NUH-and-let-me-emphatically-add-UH. And what about back-ups? I don't have server access to the mysql database, so back-ups means writing a php script to output the database contents into some format that I could in theory import into a new database. Which is how I do it with Tangled Synthesis. But given how much work has gone into gathering the data for the BuffyDB, I really don't like the idea of my primary copy of the database living off in cyberspace.

So I'm taking this idea one step further. Instead of writing a one shot export/import script, I've written a sync script. Sync script will check the local database for changes. Any records that have been modified or added since the last update will be exported to an update file in the format of a SQL statement that updates all the fields in that record, with accompanying PHP to run that SQL statement. This update file is ftped to the server, where I then run it by loading the URL to it. The PHP is executed, the SQL statements are run against the MySQL database, and the records in the MySQL database are thus updated to match the contents of the local Access database. Once I confirm that all the statements have run successfully, I reset the flags on the local database by clicking on a link, and go about my business until the next batch of changes. Pretty slick, if I do say so myself.

The coolest part is that I've actually writing in 3 nested languages. I used Cold Fusion scripting to output text which happens to be PHP scripting. And within the PHP scripting are bits of SQL scripting to manipulate the database. I'll freak you out by demonstrating:

<CFOUTPUT query=getrecords>
$s="SELECT * from #attributes.phptablename# where ID=#caller.ID#";
print "CHECK QUERY: " . $s; 
$result=mysql_query($s); 
print mysql_error(); 

if (mysql_num_rows($result)==0)
{
// insert

$s="INSERT INTO #attributes.phptablename# (ID,#attributes.numeric#
<CFIF len(attributes.numeric) and len(attributes.nonnumeric)>,</cfif>#attributes.nonnumeric#)
VALUES (#caller.id#,<CFLOOP list="#attributes.numeric#" index="c">
<CFSEt x=evaluate("caller.#c#")><CFIF len(x)>#x#<CFELSE>NULL</cfif>
<CFIF not c is listlast(attributes.numeric)>,</cfif></cfloop>
<CFIF len(attributes.numeric) and len(attributes.nonnumeric)>,</cfif>
<CFLOOP list="#attributes.nonnumeric#" index="c">
<CFSEt x=evaluate("caller.#c#")><CFSEt x=replace(x,"'","''","all")>
<CFSEt x=replace(x,"""","\""","all")><CFIF len(x)>'#x#'<CFELSE>NULL</cfif>
<CFIF not c is listlast(attributes.nonnumeric)>,</cfif></cfloop>)";

}
else 
{
//update
<CFINCLUDE template="phpupdate.cfm">
};
</cfoutput>

The parts in tags that start with "CF" are Cold Fusion tags, and the variables surrounded with #s are Cold Fusion variables. When run, the cf if statements will be evaluated, and the variables will be replaced, and this bit of script will thus output valid PHP, including one statement where the variable s ("$" denotes variables in PHP) is set to a text string that just happens to be a sql statement.

So how's this better than the old export-to-static method? Well for one thing, the "what has changed since last time" algorithms is super easy. I just have a field in each table called "synccode". Synccode is 1 by default. I've modified every editing script so that whenever a record is updated, syncode is set to 0. The sync script just looks for records with synccodes of 0. After a successful sync, the code is reset to 1. And because all the pages draw from the record, all I have to do is update the database, not figure out what pages might make reference to that particular record, like I do with the old version. Also, with the export-to-static method, the same data is outputted in static form on all sorts of pages, which is inefficient from a file-storage perspective. For example, each clip is on its own page and most are on an arc page too. That means that space-wise, I'm taking up twice as much room with a static version for each clip as I would just having one copy in the database. And uploads of the update script are much quicker than updates of the static html, because there's just the raw data, not all the formatting, to worry about. (that'll add to the dramatic decrease in space needed to store the site, since the formatting will also be added on the fly, from a single copy of the code) *AND* I won't have to write my stat counters to text file, I can just use a table in the database.

So, yesterday I finished up scripts for formatting the PHP/SQL statements to update each of the 11 tables, and all my data is now ported over to the MySQL database. I have to work on the stats/votes scripting, but most of the rest is going to be pulling the from the database and formatting, which shouldn't be too bad, especially since I already know what I want, I just need to translate my existing CF to PHP. And then, the extra fun part, take advantage of the new tools at my disposal now that I can actually do live scripting. First on my list is to write my own search engine. That will be able to group results by episode, and give you various advanced search options, including requiring that certain speakers are in the quote, and anything else I can dream up. Suggestions welcome!

How long is this going to take me? No idea. I feel like with the data snyc in (apparently) happy working order, the worst is over, which may make me lazy and slow about getting to the rest. I would however like to have a beta version released in time for a solid 2-3 weeks of use before I add the next new ep of the new year (so a week after it debuts) so that I can catch any glitchiness. On a side note, the php hosting has not been as reliable as the cstone hosting, so I will probably leave the last static version up indefinitely as a back-up, just like I did with the tripod version of TS. Also to catch all those links pointed to various clips and arcs that are currently floating out in cyberspace.

And there my friends is my own personal brand of Too Much Information ;-)

Comments, suggestions for new features, questions, corrections, inspired ramblings, etc are always welcome via the contact form.

Oh, and btw, if you're looking to start a site of your own, you may be interested to know that this site is hosted by Powweb. Get 1000 MBS of space, 150 G monthly transfer, lots of e-mail, PHP/MySQL, 30 day trail, etc for $7.77/mo. Add domain registration costs, that's still only around $9/mo. And if you sign up through this link, I get a credit to my account, so if you feel like supporting this site, please consider powweb ;-) [/END SHAMELESS PLUGGING] ALso, if you're interested in learning more about PHP/MySQL sites like this one, learn how to build your own at WebMonkey.

About this site & other misc stuff

Banners & Buttons

Awards & Honors.