Hello,

I recently picked up a simple poll script, written in php, that stores its results in, what I think is called a "flat file" , a text page that has been made writable.

I've never used them but they are quite simple (much simpler than db queries) and they save having to query the db.

But never having used them, I was wondering what the limitations of flat files were.

One thought I had was to partially convert the categories of a directory I have to a flat file. Since they rarely change, why have the script always query and download the same ones over and over? With a flat file of the last updated categories the script could just build the directory from it.

It could contain a version number as a way to update itself if neccessary. For example, it checks the version number on the server (one quick, short query) and if not changed just run the script from the data in the flat file. If it has changed, download the data from the db, update the flat file, and run the script.

How much data is it practical to handle this way? Also, in larger categories lists thelist is paginated. Would it be possible to pull only portions of a flat file like the "select" command does in a query? It doesn't look like it to me, so it would mean multiple text files? That would get confusing.

So I guess the size of the db is what determines where the limit is but I'm sort of looking for ball park figures of where that threshold is?

The disadvanages of flat file DB:
1. Searching is a nightmare - especially if the file is dynamically generated. You could do regular expression matching to search the text in the files, but thats not really an optimal solution, IMO - you still need to know basically what you're looking for to return results.
2. For large files (not sure how large exactly - the wiki engine I use uses flat files, and its been running for > year with no issues, but I've never bothered to check the file size of the files), you're going to run into issues with read/write speed.
3. Flat files are easy to mess up - no rolling back changes to a text file (though mysql has barely gotten around to doing real transaction rollbacks anyway). Hopefully you'll be the only one messing with the code to read/write these files, and the only one changing the code that does so.


Overrall, flat files are actually a Good Thing (TM) for systems tat don't require complicated queries or relational, normalized data. RDBMS just add a lot of uneeded complexity, and have much higher costs (in hardware, CPU cycles).
Here's something that might really solve your problems, in both directions - SQLIte - its built into php and already has a lot of developer writing libraries for it. Check it out, it might just be what you're looking for.

__________________
Jeff
admin@dcvegas.com
Diversified Computation
http://www.dcvegas.com

Thanks Jeff,

SQLLIte is quite impressive but, if I am understanding it right, it is simply a replacement (and faster) for MySQl, correct? That might be useful at some future date, but for awhile I will be continuing in a hosted/shared server situation. If I understand SQLLite I would need to upgrade to a dedicated server and install it as I wouldn't be able to install it on a shared server that already has Mysql installed.

Re: flat files again, I am envisioning using a combination flat file/mysql db approach. The current app would be to a directory as mentioned. The first page of the directory rarely, if ever, changes so it would be a prime candidate for a flat file.

One of the categories on it is "Realestate" so its link opens a new directory page with all the real estate categories. They too, probably won't change often, but probably more than the first page. So the real estate directory page would be a good candidate for a flat file.

So I am thinking about efficiency mostly. It's not a busy server, so it is just an exercise in planning and layout but it seems the more queries you could put to a flat file the better. The script would run faster, with less selecting and sorting.

SQLite is a replacement for MySQL, yes - however the SQLite PHP library replaces the DB entirely. That is, SQLite, both the DB engine and the interface, is part of PHP itself, when you use the SQLite library.
Here is the explanation from php.net:
"SQLite is not a client library used to connect to a big database server. SQLite is the server. The SQLite library reads and writes directly to and from the database files on disk."

Here's a really friendly introduction to SQLite:
http://devzone.zend.com/node/view/id/760

__________________
Jeff
admin@dcvegas.com
Diversified Computation
http://www.dcvegas.com

An additional, related point here that is especially relevant to the Web is concurrent file access. You should expect to have multiple instances of the same script reading and writing your flat file simultaneously. In this scenario, you have to implement careful file handling to avoid completely corrupting the file almost immediately.

PHP provides a file locking mechanism (flock()), but it's problematic at best. It's advisory only, which means that scripts that choose not to implement an flock() call won't even know the file is locked. It also doesn't work right on all file systems. There are work-arounds, but I have never felt completely safe with any of them. One of the great advantages of using an RDBMS is that it manages all these concurrency issues efficiently and safely.

I'm afraid I have to disagree with you here, Jeff. Yes, RDBMSs can add costs, but they don't "just" add costs. They provide huge performance benefits on large data sets, resolve concurrency issues, and provide amazing flexibility over flat file management. In fact, I'd argue that an RDBMS can actually reduce complexity because they have built-in capabilities that text files don't.

__________________
Fergus Gibson
realestatewebmasters.com

What limitations do you see in the current MySQL transaction and rollback implementation? I assume we're talking about InnoDB here since I think that's the only MySQL storage engine that implements transactions.

__________________
Fergus Gibson
realestatewebmasters.com

Good point - I agree that MySQL (just as an example) has roughly a bazillion advantages over flat files. However, those advantages really lose their shine when:
1. Your working with small data sets.
2. You only need 1 user to access the data at a time ( and if the purpose is just to save cycles by not making SQL calls, then you're good with using flat files or just using caching :P - read out of the file, print out table headers, viola )
3. You don't need flexibility - say I have a directory script that parses a text file for links submitted by a contact form - sure, I could insert those into mysql and query/sort/etc to them later, but if I dont want of that, a few lines of code with print out your HTML.

I think my main point is that in this situation, even MS Access is more trouble than its worth. That being said, I still think SQLite fits the bill - you're going to be writing code to access this data anyway, why not have your code BE the DB?
InnoDB in MySQL 5.x is great, as far as I'm concerned - I've seen it used on tables with 85 million + rows, and it worked like a charm - at least until we switched back to MyISAM because it was so much faster and took up less disk space.

__________________
Jeff
admin@dcvegas.com
Diversified Computation
http://www.dcvegas.com

I think this falls under that common computer category of there is no one single solution that is always going to be best. Everything is a trade-off.

__________________
Fergus Gibson
realestatewebmasters.com

After looking at SQLLite I believe I am heading that way. They say it can be quite a bit faster, in certain cases, so I will be studying to see if my situation is one of those. At present, though, the performance boost would not be a factor by itself but I hope it will be in the future (from more traffic :-). Another reason I say "heading that way" is I am on a shared server and can't get it yet unless I get a dedicated server. I did point out to the host that reducing server load is in their best interest too, so perhaps giving time for that seed to grow will have it available before I migrate to a dedicated.

Re: flat files being a trade-off, that was why I was sort of surpised by my discovery. It is a trade off and in some cases seems to make a lot of sense, but, and maybe I missed it, but I had never really noticed the strategy written about before. I always just sent everything to mysql.