Are plain files a bad idea? Should you use a database instead?

Plain text files are a great way of saving data in some circumstances ... but in other circumstances they have "issues":

1. If you're saving columns of data in a plain text file, you need to have some sort of separator between the columns, which means that you've then got to manage / deal with any occurrences of that character withing your fields

2. If two processes want to update the file at the same time, there's all sorts of fancy things you need to o to lock the file to ensure that the changes don't get mixed up with each other

3. Where a change is going to shorten or lengthen a record within a plain text file, the whole of the rest of the file has to be moved up. You can squeeze an extra person on a bus - "move up there - there's room for another" but you can't squeeze 513 bytes on a 512 byte disc sector.

4. If each line of a text file is a complete record, that will lead to an awful lot of repetition of data. Once you've written the fact that Melksham's nearest airport is Bristol in one row, you really don't want to have to keep repeating that in every subsequent row for a Melksham business!


Markets are like Pyramids

There are simple jobs, and there are complicated ones. In software terms, packages tend to be written to perform as many jobs as possible - both the simple and the complicated ones, but in practise far more people need to do the simple stuff. So you can draw a scale from "simple" at the bottom to "complex" at the top ... but then look at the volume of a pyramid and show that the volume of tasks that needs the most complex quarter is actually tiny - less that 0.5% (1/256th)

For many, many database applications, SQLite will do a fine job for you. It's free to download, got a very open license, and doesn't require the level of complexity of a separate daemon to run. For sure, it's not a distributed database across a number of hosts, and it lacks some to the real clever stuff, but if you're looking for straightforward table management of normal sized data sets, that's not really going to be an issue. Moving up to more complex and larger database tasks, you'll want to look at something like MySQL - free to download, but more complex to set up and run with a daemon, a very much tighter license. Nice product, though! And beyond MySQL you're looking at the big stuff like Oracle - very much has its place but, let's face it, I don't really need an installation of Oracle to look after the table of around 1000 members of The CoffeeShop.


SQLite - no daemon

Most databases are run through daemons (Unix / linux term) or services (Windows term) - processes running on the hosting computer which take all requests for data transactions and manage them. These daemons start up when the computer is started, and stay running all the time; administrators need to take a degree of action to ensure they're robust, resourced, etc, and take (one hopes very occasional) remedial action if they cease running. Daemons offer a number of advantages on medium sized and larger systems - they allow all requests to be funnelled through a single gateway, reducing problems with concurrent and conflicting requests, and they allow for network connection to services via TCP ports. However, on smaller systems these advantages are solutions to problems that simply don't exist and a daemon approach is unnnecceasitly complex.

The diagram here shows the setup for SQLite, which does NOT use a daemon. Code is built in to the application to directly access the disc files in which database information is stored, and clever flags within that data and its format provide an alterative and lightweight solution to the issue of concurrent / conflicting requests. The admin's cut right down - no need to have startup and shutdown scripts for daemons, nor to keep an eye on them while the system is running 24 x 7.


MySQL - daemons structure

MySQL runs through a daemon. It's known as "mysqld" and it's multithreaded to allow for parallel but cooperating requests. Typically, it's being run and monitored by a script called "mysqld_safe" which provides an automated monitor, ensuring that issues with the daemon are picked up as necessary without operator intervention. You'll see mysqld in this diagram, with an indication that it's a separate process that's typically listening on port 3306 for instuctions given to it in SQL (Structured Query Language).

Although "mysqld" is the main element of the MySQL product, it downloads with a whole series of other elements. The program called "mysql" (which you might have expected to be the main component) is actually just a small program that connects your keyboard and screen (or STDIN and STDOUT) to the port on the daemon, allowing SQL instructions to by typed and run directly. "Mysqldump" is a database backup program which takes the data stored in your MySQL database tables (and the structure of those tables too), and creates a text file backup of them - in the form of instructions which can by used via the mysql program to recreate the table and data from scratch if need be. And "Mysqladmin" is a program which issues serieses of SQL directives to mysqld to perform common admin tasks (such as changing a login password, and also reloading mysqld's internal tables to put that new password into effect for subsequent logins).

Client side libraries are also included with the Mysql download. They're not programs in their own right - they're sections of code than need buiding into applications that use databases managed by mysqld - providing the code elemenmts for third party programs which want to talk to MySQL (and without which MySQL would be ineffective). My diagram shows an example with the library built into PHP, which itself if built into the Apache httpd web server.


Examples from this week's MySQL training course run at Well House Manor by Well House Consultants.
(written 2011-04-28, updated 2011-04-29)

 S200 - MySQL - SQLite
  [2561] The future of MySQL - (2010-01-03)
  [2744] PyQt (Python and Qt) and wxPython - GUI comparison - (2010-04-28)
  [2745] Connecting Python to sqlite and MySQL databases - (2010-04-28)
  [2746] Model - View - Controller demo, Sqlite - Python 3 - Qt4 - (2010-04-29)
  [2786] Factory methods and SqLite in use in a Python teaching example - (2010-05-29)
  [3099] Perl - database access - DBD, DBI and DBIx modules - (2010-12-22)
  [3139] Steering our Python courses towards wxPython, SQLite and Django - (2011-01-19)
  [4006] Ruby / SQLite3 example program, showing JOIN v LEFT JOIN - (2013-02-16)
  [4007] Which database should I use? MySQL v SQLite - (2013-02-16)
  [4024] SQL databases from Python - an SQLite example - (2013-03-02)

S151 - Introduction to SQL and MySQL
  [84] MySQL - nuggets - (2004-10-11)
  [85] Present and future MySQL - (2004-10-12)
  [175] Worthwhile - (2005-01-11)
  [382] Central London Courses - Perl, PHP, Python, Tcl, MySQL - (2005-07-18)
  [444] Database or Progamming - which to learn first? - (2005-09-13)
  [515] MySQL - an FAQ - (2005-12-03)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [691] Testing you Perl / PHP / MySQL / Tcl knowledge - (2006-04-19)
  [918] Databases needn't be frightening, hard or expensive - (2006-11-08)
  [924] The LAMP Cookbook - Linux, Apache, MySQL, PHP / Perl - (2006-11-13)
  [2134] Oracle take over Sun who had taken over MySQL - (2009-04-21)
  [2567] Extra MySQL course dates (2 day course, UK) - (2010-01-08)
  [3928] Storing your intermediate data - what format should you you choose? - (2012-11-20)

Back to Baby Pictures

Previous and next or Horse's mouth home

Forward to SQL - Data v Metadata, and the various stages of data selection

Wanted - a look to the future
Melksham Car Parking - current charges and limits
The importance of feedback
SQL - Data v Metadata, and the various stages of data selection
Files or Databases? MySQL, SQLite, or Oracle?
Baby Pictures
Reybridge, Easter Monday
Easter on the Canal - near Melksham, Wiltshire
Alternative Vote (AV) - cutting the crap
Alternative Vote (AV) - explaining and an example