Session Three: lonsql (Gerd)

This section describes issues associated with LON-CAPA and a SQL database.

The SQL database in LON-CAPA is used for catalog searches against resource metadata only. The authoritative version of the resource metadata is Ð as discussed Ð an XML-file on the normal file system (same file name as resource plus Ò.metaÓ). The SQL-database is a cache of these files, and can be reconstructed from the XML files at any time.

The current database is implemented assuming a non-adjustable architecture involving these data fields (specific to each version of a resource).

1.     title

2.     author

3.     subject

4.     notes

5.     abstract

6.     mime

7.     language

8.     creationdate

9.     lastrevisiondate

10.  owner

11.  copyright

Purpose within LON-CAPA

LON-CAPA is meant to distribute A LOT of educational content to A LOT of people. It is ineffective to directly rely on contents within the ext2 filesystem to be speedily scanned for on-the-fly searches of content descriptions. (Simply put, it takes a cumbersome amount of time to open, read, analyze, and close thousands of files.)

The solution is to hash-index various data fields that are descriptive of the educational resources on a LON-CAPA server machine. Descriptive data fields are referred to as "metadata". The question then arises as to how this metadata is handled in terms of the rest of the LON-CAPA network without burdening client and daemon processes. I now answer this question in the format of Problem and Solution below.

PROBLEM SITUATION: If Server A wants data from Server B, Server A uses a lonc process to send a database command to a Server B lond process.

    lonc= loncapa client process    A-lonc= a lonc process on Server A

    lond= loncapa daemon process

                 database command

    A-lonc  --------TCP/IP----------------> B-lond

The problem emerges that A-lonc and B-lond are kept waiting for the MySQL server to "do its stuff", or in other words, perform the conceivably sophisticated, data-intensive, time-sucking database transaction.  By tying up a lonc and lond process, this significantly cripples the capabilities of LON-CAPA servers.

While commercial databases have a variety of features that ATTEMPT to deal with this, freeware databases are still experimenting and exploring with different schemes with varying degrees of performance stability.

THE SOLUTION: A separate daemon process was created that B-lond works with to handle database requests.  This daemon process is called "lonsql".

  So,

                database command

  A-lonc  ---------TCP/IP-----------------> B-lond =====> B-lonsql

         <---------------------------------/                |

           "ok, I'll get back to you..."                    |

                                                            |

                                                            /

  A-lond  <-------------------------------  B-lonc   <======

           "Guess what? I have the result!"

Of course, depending on success or failure, the messages may vary, but the principle remains the same where a separate pool of children processes (lonsql's) handle the MySQL database manipulations.