Databases

Introduction

Mathematica provides access to standard SQL relational database systems such as Oracle and MySQL via its DatabaseLink Package.  A different sort of database application is provided in the WorkLife FrameWork™.

Mathematica's DatabaseLink provides an interface to the SQL language used by most relational database systems (and achieves this through using Java and other technologies behind the scenes) and it allows the programming of these interactions to be cast in the Mathematica programming language.  The sorts of database tables that can be created are those that are supported by the SQL database in question and, often, entries into tables must adhere to the specific types allowed in the table fields. DatabaseLink is a very powerful technology for extending the reach of Mathematica into the world's databases and extending the ability for applications to do arbitrarily sophisticated calculations through Mathematica.

On the other hand, the WorkLife FrameWork's™ database functionality is quite different.  It is entirely written in Mathematica and does not require any interface with external systems. There are no necessary restrictions on the types that are placed into its tables, and the tables are explicit Mathematica lists—therefore these lists can be used independently of having the WorkLife FrameWork™.  It is a very useful and accessible tool for tasks that are not enterprise level.

The WorkLife FrameWork's™ database tool is used by the WorkLife FrameWork™  itself to store and access data on your use of it and your use of Mathematica.

The databases that you create with the WorkLife FrameWork's™ database tool are simple rectangular tables, and relationships between tables can be programmatically queried in the way that one usually writes programmatic queries in Mathematica.

In the following sections we will show basic and then more sophisticated use of the WorkLife FrameWork's™ database tool.

The Database Palette

For the buttons and executable commands that are described n this section to work it is assumed that you have installed the WorkLife FrameWork™ and have loaded it.  This can be done either from the Load WorkLife Framework button on the supplied palette, by executing the command Needs["Diary`Diary`"], or by clicking on the following button:

The Palette

The simplest interface to databases created in the WorkLife FrameWork™ is through the Databases palette.  This can be accessed via its button on the All Palettes Palette, or by executing

after the WorkLife FrameWork™ has been loaded.

The Palette looks like

The Databases Palette

The Databases Palette provides administrative buttons for managing Databases in the WorkLife FrameWork™ Package.

In this Palette each of the buttons below the     Databases     button, when clicked on, loads the indicated Database.

Although each Databases  on this Palette is associated with a specific Diary (or group of Diaries that live in the same directory) the Databases listed in the Databases Palette are ones that are known to the WorkLife FrameWork™ Package.  These are contained in the list $Databases.  If a Database is not listed on this palette it can be loaded using the function LoadDatabase.

The Palette Buttons

The WorkLife FrameWork™ Package has a global directory where Databases useful for the function of the package are located—the Package DB Directory button opens up this directory.

The Diary DB Directory button opens the Databases subdirectory of the current Diary's directory that contains the directories of individual Dairies.

This button opens a popup menu containing buttons listing the known databases that have been created. Clicking on one of these buttons will open the directory that that Database resides in.

This button opens a dialog for creating a New Database.  See "Creating a Database" below.

This button opens a popup menu containing buttons listing the known databases that have been created. Clicking on one of these buttons will load that database.

This button opens a popup menu containing buttons listing the known databases that have been created. Clicking on one of these buttons will open a dialog Notebook that allows you enter a new record to add to the chosen database.  When a button in the popup is clicked on the database will first be loaded if it has not been loaded yet.

This button opens a popup menu containing buttons listing the known databases that have been created. Clicking on one of these buttons will open a dialog Notebook that allows you enter information to base a search on for the chosen database.  When a button in the popup is clicked on the database will first be loaded if it has not been loaded yet.

Creating a Database

For the buttons and executable commands that are described n this section to work it is assumed that you have installed the WorkLife FrameWork™ and have loaded it.  This can be done either from the Load WorkLife Framework button on the supplied palette, by executing the command Needs["Diary`Diary`"], or by clicking on the following button:

...Via the Databases Palette

This button opens a dialog for creating a New Database.  See "Creating a Database" below.

This dialog looks like:

The First New Database Dialog

After the number of Database fields is entered into this dialog and the Next⇒ button is pressed, a second dialog allows you to name the Database and enter the field names. In the following illustration of this second dialog the number of Database fields has been chosen to be 3.

The Second New Database Dialog

In this dialog you supply a name for the Database and Field Names for the indicated fields.

The boxes with "None" are just placeholders and generally should not be modified, though doing so will have no effect on the creation of the Database..

When the Create a New Database button is clicked the new Database will be created in the Databases subdirectory of the current Diary's directory.

...Via the Function Interface

Although a database can be created via the Databases Palette and the dialogs that are presented after clicking on the New Database button, it's worth understanding the underlying functions that achieve these tasks so that you can understand the elements of a database.

To create a Database the WorkLife FrameWork™ must have been loaded and a Diary must be selected as the current Diary.

Databases are placed in the Databases subdirectory of a Diary's Directory.  Hence, a Diary has to have been chosen as the current Diary in order for a Database to be created.  Although a given database is located in a specific Diary's Databases subdirectory, all Databases that the WorkLife FrameWork™ is aware of are listed in the Databases Palette.

The function to create a Database is CreateDatabase:

CreateDatabase[name, {records...}, fieldNames] creates a database with the name "name" containing the records, {records...} The field names are given by fieldNames, and this must be a list of distinct strings with length equal to the number of fields (columns) in the database. This database is placed in a directory called "name" in the Database subdirectory of the current Diary directory. To create a new database you can use CreateDatabase[name, data, fieldNames] with data containing a single record. Alternatively you can use CreateDatabase[name,fieldNames] to create a database with zero records.

Usage Message for CreateDatabase

To use this function you need to

Name the database

Decide how many fields its records will have, and

Provide names for those records

As an example we will create a database called UserSurvey  

Note that, in CreateDatabase, the Database's name is a symbol, but the FieldNames are all Strings. Be careful to not use, as a Database name,  symbol that you either have already defined or that you plan to use.  When the Database is created any earlier values for that symbol will be removed.

The call to CreateDatabase, creates the database UserSurvey but does not add any records to it. The alternative form CreateDatabase[name, {records...}, fieldNames] seeds the database with the provided records.  If the lengths of the records are not all the same, or if they are not the same length as the list of Field Names, then an error message is generated and the Database is not created.

In this example we have used the option FieldTypes to specify a list of patterns that each of the fields in a database record must match.  The default, if the FieldTypes option is not specified, is for all of the field types to simply be the universal pattern _.

Interacting With a Database: Palette Interface

For the buttons and executable commands that are described n this section to work it is assumed that you have installed the WorkLife FrameWork™ and have loaded it.  This can be done either from the Load WorkLife Framework button on the supplied palette, by executing the command Needs["Diary`Diary`"], or by clicking on the following button:

Loading the Database

When you click on the Load a Database button on the Databases Palette, a popup menu opens that lists the known databases that have been created. Clicking on one of these buttons will load that database.  A database is known if it is contained in the list give by the parameter $Databases.

To load a database that is not listed in $Databases use the function LoadDatabase described below in the "Interacting With a Database: Function Interface" section of this document.

Adding records

When you click on the Add a Record button on the Databases Palette, a popup menu opens buttons listing the known databases that have been created.  A database is known if it is contained in the list give by the parameter $Databases.

Clicking on one of these buttons will open a dialog Notebook that allows you enter a new record to add to the chosen database.  

When a button in the popup is clicked on, the database will first be loaded if it has not been loaded yet.

For the example of the Database UserSurvey that we created in the preceding section, when the Add a Record button is clicked the following interface is opened:

An Example of the Interface to Add a Record to a Database

In this interface the FieldNames are listed along the left column.  The boxed input fields accept the data for the respective field.  And the right hand column shows the patterns that were assigned as the FieldTypes as a guide to what the form should be of the material entered into the input fields.

Note that, when a field's pattern is _String, you should not use the quotation characters around the field's entry in this dialog box interface.  The entry will be assumed to be a string. If you do include quotation characters, they will be assumed to be part of the string itself.

Searching a Database

When you click on the Search a Database button on the Databases Palette, a popup menu opens buttons listing the known databases that have been created.  A database is known if it is contained in the list give by the parameter $Databases.

Clicking on one of these buttons will open a dialog Notebook that allows you enter a new record to add to the chosen database.  

When a button in the popup is clicked on, the database will first be loaded if it has not been loaded yet.

For the example of the Database UserSurvey that we created in the preceding section, when the Search a Database button is clicked the following interface is opened:

An Example of the Interface to Search for Records in a Database

The layout of this interface is similar to that for adding a record to a database with the columns having the same meaning.  Database FieldNames are on the left, the input fields are in the boxes in the center, and the FieldTypes, as guidelines for your search, are in the column on the right.

Note again that, when a field's pattern is _String, you should not use the quotation characters around the field's entry in this dialog box interface.  The entry will be assumed to be a string. If you do include quotation characters, they will be assumed to be part of the string itself.

In this example we are looking for records where the user's first name is the String "Emily".  The matching records are displayed in a notebook. For the UserSurvey database (with the records that are placed in this database in the next section) there is one record that matches this.  Here is an example of the search results interface:

An Example of the Interface that Displays Search Results for a Database.

In this interface the buttons along the top, when clicked on, will open a notebook with the results for just the indicated field.

Interacting With a Database: Function Interface

For the buttons and executable commands that are described n this section to work it is assumed that you have installed the WorkLife FrameWork™ and have loaded it.  This can be done either from the Load WorkLife Framework button on the supplied palette, by executing the command Needs["Diary`Diary`"], or by clicking on the following button:

Loading the Database

For a Database that has already been created, such as the  UserSurvey Database that we created in the earlier section on Creating a Database, you load it using the LoadDatabase command.

LoadDatabase[name,file] loads a database residing in the file "file" and assigns it the name "name."  LoadDatabase[name] loads the database with the name "name" if it exists and is listed in $Databases. "name" must be a symbol without a value.

Usage Message for LoadDatabase

Here we load the UserSurvey Database:

Unloading the Database

If a database is already loaded and you want to unload it you can use the UnloadDatabase command.

UnloadDatabase[name] clears (unloads) the database "name" if it has been loaded. Database records are not affected.

Usage Message for UnloadDatabase

Here we unload the UserSurvey Database:

Reloading the Database

If a database is already loaded and you want to unload it you can use the ReloadDatabase command.

ReloadDatabase[name] reloads the database name if it has already been loaded.

Usage Message for ReloadDatabase

Here we load the UserSurvey Database and then Reload it:

Generally there is no need to Reload a Database.   However, reloading a Database has the effect of consolidating records that have been newly added and cleaning out any residual deleted records from the various files that make up the Database. In essence it has the effect of "cleaning house" for that Database.

Adding records

To add a record (or multiple records) to a Database use the AddDatabaseRecords function

AddDatabaseRecords[name,{records...}] adds the records to the database "name". These records are listed in NewRecords[name] until the database is reloaded, whereupon the records are added to the database file. Prior to that the new records are stored in an auxiliary file in DatabaseDirectory[name].

Usage Message for AddDatabaseRecords

Since the Database UserSurvey is already loaded, we can add records to it.  Here, for example, is a Record for Anne Gables:

{"UserFirstName","UserLastName","UserAge","RecordDate","Rating"}

Note several things

The second argument to AddDatabaseRecords is a List of Records.  In the case above we only added a single record.  If we had (incorrectly used the form AddDatabaseRecords[UserSurvey,{"Anne","Gables",17,Date[],3}]—i.e., with out one of the sets of {} brackets—then an error message would have been generated.

AddDatabaseRecords evaluates the list of Records before placing them in the Database.  We took advantage of this to automatically insert into the Record's last field the date when the Record was added to the Database. This is because the function Date[] is evaluated by Mathematica at the point when AddDatabaseRecords is evaluated.

When Database Records are found— for example by using DatabaseFind—the result that is returned is evaluated.

If you want to add material to a Database without evaluating it you can wrap it in Unevaluated, Hold, or other similar functions. In these cases the results of, for example, DatabaseFind will also return unevaluated for those Database field entries that are wrapped in this way.

The return value from the AddDatabaseRecords function is the list of Records that were added to the Database.

Deleting records

To delete a record (or multiple records) from a Database use the DeleteDatabaseRecords function.

DeleteDatabaseRecords[name,{records...}] deletes the records from the database "name". These records are stored in DeletedRecords[name] until the database is reloaded, whereupon the records are deleted from the database file. Prior to that the deleted records are stored in an auxiliary file in DatabaseDirectory[name].  

Usage Message for DeleteDatabaseRecords

Here we delete the Anne Gables record that we added earlier.

Note several things

The records that are supplied to DeleteDatabaseRecords must be the exact records that you want to Delete from the Database.  If we had supplied {"Anne","Gables",17,Date[],3} then the record would not have been deleted because the date field would have a different value.  If you want to delete records that fit a particular pattern, then you should first DatabaseFind to locate those records and then supply them to DeleteDatabaseRecords.

Just like AddDatabaseRecords, DeleteDatabaseRecords evaluates its second argument.  Therefore if the final argument had been instead of 3, the Record would have still been deleted.

The return value from the DeleteDatabaseRecords function is the list of Records that were deleted from the Database.

Modifying records

To modify an existing record in  a Database use the ModifyDatabaseRecord function.

ModifyDatabaseRecord[name,originalRecord,replacementRecord] replaces the record originalRecord with replacementRecord in the named database. If there is more than one copy of originalRecord in the database then all of them are replaced with replacementRecord.

Usage Message for ModifyDatabaseRecord

As an example first add a record to the database:

Here we use ModifyDatabaseRecord to change the "RecordDate" and "Rating" fields in the original record.

Note several things

ModifyDatabaseRecord modifies a single Record at a time.  To modify multiple records you need to write a simple function to perform that task.

ModifyDatabaseRecord evaluates its second and third arguments.

The return value from the ModifyDatabaseRecord function is the updated Record.

Adding Records when the Database is not loaded

There is an additional function that allows you to add records to a database even if it has not been loaded using LoadDatabase.   Cleverly enough, this function is called AddDatabaseRecordsToClosedDatabase.  This function is useful when you want to write an application that only needs to place data in a Database, but not to access that data.

Information about Database

The WorkLife FrameWork™ provides several functions that give you information about a Database.

Databases

The function Databases gives you a list of Databases in the current Diary's Databases subdirectory along with the path to the database file.

Note that the file that is given is not the only file that comprises the Database.  For example, it doesn't contain information on records that have been added or deleted since the last time the Database was loaded (or reloaded).

Databases[] gives a list of the databases in the current Diary's Database directory.  The list is in the same form as the list generated by $Databases. It is in the form of a list with each element a list of length two. The first element of each entry is the name of the database and the second entry is the full path name to the database. Databases[]  is equivalent to Databases[Diary]. The databases in the package database directory can be listed by executing Databases[Default]. Backup databases are not listed.

Usage Message for Databases

DatabaseFieldNames

The function DatabaseFieldNames gives you a list of the FieldNames in the given Database.

DatabaseFieldNames[name] gives the list of names of the fields in the given database if it has been loaded. DatabaseFieldNames[name,i] gives the ith field name of the database. If i>RecordLength[name] then DatabaseFieldNames[name,i] returns $Failed.

Usage Message for DatabaseFieldNames

DatabaseFile

The function DatabaseFile gives you a list of Databases in the current Diary's Databases subdirectory along with the path to the database file.

DatabaseFile["directory"] gives the full path to a database file if it exists within the directory.  If there is no such file in the directory then the value None is returned. A database file is one of the form "*DB.m".

Usage Message for DatabaseFile

DatabaseDirectory

The function DatabaseDirectory gives you a list of Databases in the current Diary's Databases subdirectory along with the path to the database file.

DatabaseDirectory[] gives the database directory in the current Diary directory. DatabaseDirectory[name] gives the directory in which the database "name" resides.

NumberOfDatabaseFields

The function NumberOfDatabaseFields gives you a list of Databases in the current Diary's Databases subdirectory along with the path to the database file.

NumberOfDatabaseFields[name] gives the number of fields in the database.  The field names can be obtained from DatabaseFieldNames[name].

Usage Message for NumberOfDatabaseFields

NumberOfDatabaseRecords

The function NumberOfDatabaseRecords gives you a list of Databases in the current Diary's Databases subdirectory along with the path to the database file.

NumberOfDatabaseRecords[name] gives the number of records in the database.

Usage Message for NumberOfDatabaseRecords

DatabaseFileInDirectoryQ

The function DatabaseFileInDirectoryQ gives you a list of Databases in the current Diary's Databases subdirectory along with the path to the database file.

DatabaseFileInDirectoryQ["directory"] determines whether a database file exists within the directory. A database file is one of the form "*DB.m".

Usage Message for DatabaseFileInDirectoryQ

Searching a Database

Initialization: Populating the Database

You can retrieve the material stored in a Database by using the function DatabaseFind and making use of any of the found Records as input to any appropriate Mathematica commands.

DatabaseFind[name,item,"fieldName"] looks for item in the field of the database "name" with the field name "fieldName" and returns all database records that have a match in that field. DatabaseFind[name,item,j] looks for item in the jth field of the database "name". DatabaseFind[name,pattern] looks for matches to the pattern in the form of a list that is NumberOfDatabaseFields[name] long. DatabaseFind[name, Function|DatabasePattern] looks for database records according to a pure function or a DatabasePattern.

Usage Message for DatabaseFind

There are a number of different ways in which DatabaseFind can be used. To show examples of these we first load and then populate our example Database UserSurvey.

First Load the database

Next, add records to the database

This is the total number of Database Records in the UserSurvey Database.  It includes the Records that we just added to the Database along with the one record that we added earlier.

Simple Search of the Database

The simplest use of the DatabaseFind function is to search for an exact match to the value in a specific Field position in Records.  

It is easy to remind oneself what the field names are with the function DatabaseFieldNames:

Here we look for all records where the age of the survey respondent is 34

An alternative version of this is to use the index of the FieldName (i.e., its position in the Record).

Here again we look for all records where the age of the survey respondent is 34, but indicate the Field that we are looking in by its place in the record

Search of the Database Based on a Record Pattern

Rather than looking for an exact match in a specific Field of the Database's Records, you can look more generally for Records that match a a specific pattern.  As an example of this we look for Records where the user is older than 30 and whose Rating is less than or equal to 6.

This looks for Records where the user is older than 30 and whose Rating is less than or equal to 6.

This looks for Records where the user's first and last names contain the lower case letter "a".

Search of the Database Based on a Pure Function

Another approach that does not require writing out a Mathematica pattern that represents a full Database Record is to use a Pure Function.  In the preceding examples suing a Record Pattern, Pure Functions naturally appeared in the pattern's specification.  In this alternative approach the Pure Functions that are used have formal parameters (#1,#2,...) that correspond to each of the Database Fields respectively.

Again, as an example of this approach, we look for Records where the user is older than 30 and whose Rating is less than or equal to 6.

This looks for Records where the user is older than 30 and whose Rating is less than or equal to 6. Note that the Pure Function that is used must evaluate to True for those Records that you want to find.  As a general rule the Pure Function should be a Boolean expression.

This is another way to write this that is slightly easier to read.

Here we search for cases where the cube of the user's rating is less than the user's age

Search of the Database Based on a Database Pattern

One further approach that sometimes is easier to read is through the use of Database Patterns.  These are similar to Pure Functions, except that in stead of formal parameters such as #1, #2, and so on..., the Database's field names are used explicitly.  To do this each of the field names are placed in a FieldName wrapper and the full Database Pattern

As an example, the case above where the Pure Function was

In terms of a DatabasePattern this becomes

So, rather than  DatabaseFind[UserSurvey,Function[((#3>30)&&(#5≤6))], in terms of a DatabasePattern the search becomes

Note that, as always, FieldNames must be Strings.

Choosing the Entire Database

The form DatabaseFind[name,All] returns the full Database.

This returns the full database

Using Mathematica's Part function you can easily extract columns of the Database.

This gives the age—rating pairs

One can visualize this data using Mathematica's ListPlot. There is a clear qualitative relationship between Age and Rating.

The Output of DatabaseFind as a Notebook

DatabaseFind has an option, GenerateNotebook, that when set to True causes the records that were found to be displayed in a Notebook.

Backing up a Database

To back up the current state of a Database you can use the function BackupDatabase.

BackupDatabase[name] backs up the given database. The database must be currently loaded to do this backup.  

Usage Message for BackupDatabase

BackupDatabase creates a copy of the Database in the same directory as the original Database.  The copy's directory name is the same as that of the original Database, but with the characters "BU" appended along with an integer that represents a time stamp.

Copyright ©, 2005→2009, Scientific Arts, LLC