Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
Retiring, March 2020 - sorry, you have missed our final public course.
The Coronavirus situation has lead us to suspend public training - which was on the cards anyway, with no plans to resume

Please ask about private 'maintenance' training for Python, Tcl, Perl, PHP, Lua, etc
Happily continuing private consultancy / programming work
Files or Databases? MySQL, SQLite, or Oracle?

Why not use plain files rather than a databaseAre 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!

The application pyramidMarkets 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.

Elements of an SQLite based systemSQLite - 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.

Elements of a daemon based SQL systemMySQL - 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)

Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
S151 - Introduction to SQL and MySQL
  [3928] Storing your intermediate data - what format should you you choose? - (2012-11-20)
  [2786] Factory methods and SqLite in use in a Python teaching example - (2010-05-29)
  [2567] Extra MySQL course dates (2 day course, UK) - (2010-01-08)
  [2561] The future of MySQL - (2010-01-03)
  [2134] Oracle take over Sun who had taken over MySQL - (2009-04-21)
  [924] The LAMP Cookbook - Linux, Apache, MySQL, PHP / Perl - (2006-11-13)
  [918] Databases needn't be frightening, hard or expensive - (2006-11-08)
  [691] Testing you Perl / PHP / MySQL / Tcl knowledge - (2006-04-19)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [515] MySQL - an FAQ - (2005-12-03)
  [444] Database or Progamming - which to learn first? - (2005-09-13)
  [382] Central London Courses - Perl, PHP, Python, Tcl, MySQL - (2005-07-18)
  [175] Worthwhile - (2005-01-11)
  [85] Present and future MySQL - (2004-10-12)
  [84] MySQL - nuggets - (2004-10-11)

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

Back to
Baby Pictures
Previous and next
Horse's mouth home
Forward to
SQL - Data v Metadata, and the various stages of data selection
Some other Articles
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
4759 posts, page by page
Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96 at 50 posts per page

This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2020: 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/3269_Fil ... acle-.html • PAGE BUILT: Sat May 27 16:49:10 2017 • BUILD SYSTEM: WomanWithCat