Migrating to database-driven design...

I've just migrated my site content from normal files to a MySQL
database. The process was less painful than expected, although I'm still
at a "hybrid" stage, where individual PHP files for each document call
the document generator.

The next stages will be:

1) get rid of the actual files, and use something like mod_rewrite to
redirect requests to the document generator whilst keeping my URLs clean
and logical

2) dynamic menu generation based on database fields showing the links
between documents

Performance is excellent - this is something I was worried about, as my
web server is a multi-function 1995-era P100. Loading is not noticeably
more than with static files.

I can now make site-wide changes to the non-content bits of the site
(headers, footers, menu structure etc) just by editing the PHP document
generation script - before, this was limited to CSS modifications of the
existing elements.

Database content is written in XHTML-1.1, with PHP search-and-replace
functions modifying the empty tag format (<br> instead of <br />) and
the language attribute (lang or xml:lang) to generate the HTML-4.01. I
also s&r <abbr> with <acronym> for IE.

I'll document my findings when I'm done. I intend to keep the URL
structure with no file extensions, and automatic HTML/XHTML selection
based on proper (non-trivial) parsing of the Accept header.

http://tranchant.plus.com/

Comments? Questions? Advice from anyone who's already done this?

--
Mark.

Brian wrote:

Mark Tranchant wrote:

I've just migrated my site content from normal files to a MySQL database.

Performance is excellent
Loading is not noticeably more than with static files.

Perhaps, on first load. But on reload, I must wait for the file to be
regenerated. This is not terribly slow, but it seems rather
unnecessary for your content.

If you experience a delay, that's not due to regeneration but transfer
delays. Even the longest page regenerates in *well* under a second
unless the server's doing other stuff.

No last-modified header, thus there's no chance for a 304 response,
even where the document has not been modified.

Hmmm. There used to be an ETag header. I wonder what's happened to that.
Thanks for pointing this out.

May I ask why, in addition to skipping last-modified header, you have
chosen to include cache-control: no-cache? Is this to make your
content-type picker work through the proxy?

Yes, although note that "skipping" the last-modified header isn't
through choice.

Not site-wide, no. But I have created individual php pages that rely
on a MySQL db. For them, I wrote a php script to query the db for a
last-modified date, and modified a php script to process
if-modified-since and if-none-match headers and send a 304 as
appropriate.

Thanks for the pointer. I'll take a look at this when I get a moment.

--
Mark.
http://tranchant.plus.com/

Brian wrote:

Mark Tranchant wrote:
I've just migrated my site content from normal files to a MySQL database.
Performance is excellent
Loading is not noticeably more than with static files.

Perhaps, on first load. But on reload, I must wait for the file to be
regenerated. This is not terribly slow, but it seems rather
unnecessary for your content.

The regeneration is very quick, *well* under a second under normal
condition. Any delay you experience is transfer-related.

http://tranchant.plus.com/

No last-modified header, thus there's no chance for a 304 response,
even where the document has not been modified.

Now implemented, thanks to your helpful pointer below. I can't get it to
work for IE - this page suggests I'm not going to succeed:

http://www.sitepoint.com/forums/show...hreadid=158442

Questions?

May I ask why, in addition to skipping last-modified header, you have
chosen to include cache-control: no-cache? Is this to make your
content-type picker work through the proxy?

Yes. The Last-Modified header is now added, so 304 responses happen for
me with Firefox. I use a TIMESTAMP column in the database that
automatically updates whenever I edit the content.

Advice from anyone who's already done this?

Not site-wide, no. But I have created individual php pages that rely
on a MySQL db. For them, I wrote a php script to query the db for a
last-modified date, and modified a php script to process
if-modified-since and if-none-match headers and send a 304 as
appropriate.

Ta. I now issue a timestamp-related ETag for each page, as well as a
Last-Modified header. I use any incoming If-Modified-Since headers to
compare against the last modified date and send either 304 or
200+content as appropriate.

I haven't done anything with If-None-Match yet - I need to look into
that a bit more.

Thanks for your help, as usual.

--
Mark.

Brian wrote:

There used to be an ETag header. I wonder what's happened to that.

Well, with static pages on Apache, ETag is automatic. If you go with a
db/php implementatino, you'll have to create an ETag through the
script. Same for Last-Modified: you'll have to determine that and
create the header.

Yep, I realize that now.

May I ask why, in addition to skipping last-modified header, you
have chosen to include cache-control: no-cache? Is this to make
your content-type picker work through the proxy?

Yes, although note that "skipping" the last-modified header isn't
through choice.

Ok, I'm curious. What does this mean? Did someone threaten you?
("Listen mate: You keep sendin' a Last-Modified header, and my boys
here'll break yur caps-lock key -- in the down position!") :-)

I see the emoticon, but I'll answer anyway. I meant that I did not
choose to omit the Last-Modified header: the omission happened as a
result of my actions, and I didn't realise.

Again, is this related to the application/xml v. text/html switcher
thingy you have created? And is that why you have cache-control:
no-cache set?

As I said, yes and yes. For some reason, the proxy at work does not
appear to be honouring the Vary header. This means that if someone looks
at a page with Firefox, then with IE, the proxy feeds IE with the
application/xhtml+xml page with predictable outcome...

If anyone can work out a way around this without ditching my
extensionless content negotiation, I'll be a very happy bunny.

--
Mark.

Brian wrote:

Err, your welcome. (What pointer? I didn't have a chance to show you
the code I wrote to do it. And by the way, how in the HELL did you get
your conditional GET up and running so quickly?! It took me days to
implement it here. <sob> If there was any doubt -- I suppose there
should not have been -- we now know who the better coder is!)

Didn't take too long, once I knew what needed doing. $line["lm"] is the
timestamp from the database (see below):

$lm = $line["lm"];
$year = substr($lm,0,4);
$month = substr($lm,4,2);
$date = substr($lm,6,2);
$hour = substr($lm,8,2);
$min = substr($lm,10,2);
$sec = substr($lm,12,2);
$lmts = mktime($hour,$min,$sec,$month,$date,$year);
$headers = getallheaders();

if(isset($headers["If-Modified-Since"])) {
$imsts = strtotime($headers["If-Modified-Since"]);
if ($imsts >= $lmts) {
header('HTTP/1.1 304 Not Modified');
exit();
}
}

I use a TIMESTAMP column in the database that automatically updates
whenever I edit the content.

I assume this is the timestamp for the table as a whole, right? Or did
you create a timestamp for individual records? If the latter, and it
is updated automatically, then my curiousity is perked.

It's a per-record timestamp. Simply create a column of type TIMESTAMP
and set it to NULL. It then auto-updates whenever something it done to
the record.

http://dev.mysql.com/doc/mysql/en/Da...ime_types.html

I now issue a timestamp-related ETag for each page, as well as a
Last-Modified header. I use any incoming If-Modified-Since headers
to compare against the last modified date and send either 304 or
200+content as appropriate.

I haven't done anything with If-None-Match yet - I need to look
into that a bit more.


Here's what I started with for my conditional GET:

http://simon.incutio.com/archive/200...conditionalGet

I changed it a bit for the photography site, and added a function to
get the last-modified from the MySQL table, but the basic
if-modified-since and if-none-match stuff I more or less took from
that blog entry.

Ta. Much what I'm doing above, but better written. I need to get into
the detail a bit more, as I'm sure my code is riddled with problems.

Incidentally, I wanted to post my code on the web somewhere, so that
others might use it to provide cache-friendly php/MySQL sites. Alas, I
have to permanent home for such a thing. Perhaps you'll put something
up on your site?

When I'm done, probably.

--
Mark.

Brian wrote:

I hate to be a fly in the ointment, but it seems like the cost of the
xhtml/html switcher is rather high for the small benefit you receive.
From where I sit, you'd be better off going with HTML only. You could
always leave a demo page up to demonstrate your programming prowess on
this project. (It really is a neat implementation!)

No! Argh! Don't make me lose my switcher!

Actually, although the potential cost is high, the real-world cost is
quite low. My site doesn't generate massive amounts of traffic, and now
(with your advice) I've got conditional GETs half-working, I don't
believe the cost is high at all.

Most visitors have a browse around, hopefully finding what they want,
then leave again. Repeat page views aren't all that common.

--
Mark.

Brian <usenet3 (AT) julietremblay (DOT) com.invalid> writes:

Mark Tranchant wrote:
$line["lm"] is the timestamp from the database (see below):
$lm = $line["lm"];
$year = substr($lm,0,4);
$month = substr($lm,4,2);
$date = substr($lm,6,2);
$hour = substr($lm,8,2);
$min = substr($lm,10,2);
$sec = substr($lm,12,2);
$lmts = mktime($hour,$min,$sec,$month,$date,$year);

I think there's an easier way to get this. Sadly, my local copy is on

Assuming that it's a MySQL database (sorry, haven't been following the
thread too closely) then:
mysql_query("SELECT UNIX_TIMESTAMP(lm) AS lmts FROM ...")
....
$lmts = $line["lmts"];

Probably similar functions in postgres, etc.

--
Chris

On Fri, 11 Jun 2004, Brian wrote:

that os component, as A. Flavell has dubbed it.

Not I! To the best of my knowledge, I'm only passing-on the evidence
which MS testified in court. (So "don't shoot the messenger" ;-)