• (The basics: storing simple data records in an existing table
• (Treating text fields as attachments
• (Creating and dropping tables on the fly
• (Creating and dropping databases on the fly
• (Indexing a directory in MySQL

So finally we get to manage data in a system made to manage data efficiently. Any scalable solution will be built on relational databases, but it's easier to test prototypes (including the prototype of the wftk itself) in an environment based on files and directories, so files and directories are where I started. It's also very common to find small amounts of important data in files -- as long as the quantity is manageable, this is a perfectly good strategy, as it makes it easier to write scripts or to edit data by hand.

But at some point, of course, you just have to have a database management system. The first one we'll look at is MySQL, because it's ubiquitous. Others will be left as an exercise for the reader, or lumped together into 02-f Other storage, or handled by the generic DBI interface.

(The basics: storing simple data records in an existing table

The basic functionality of MySQL storage class is exactly the same as any other list. So let's run down that first, shall we?

   use Workflow::wftk;

   my $wftk = Workflow::wftk->new();

   # Create a list stored in 'test_d':TCH
   my $list = $wftk->open('mylist', 'dir:test_d');

   # Add a record the easy way, with a hashref.
   my $key = $list->add ( {field1 => 'value1', field2 => 'value2'} );

   # Look at the fields in the list again:
   @fields = $list->fields();          # Returns ('field1', 'field2') now, just like a memory list.

   my $key2 = $list->add ( ['different value', 'value2'] );
   my $key3 = $list->add ( ['third value', 'value2'] );

   # Get a list of keys:
   my @keys = $list->keys();

   # Let's check what we just did:
   opendir IN, 'test';
   my @files = grep !/^\./, readdir (IN);
   closedir IN;

   # @keys and @files should be the same thing.

   # Iterate through the list:
   for (my $key = $list->first_key(); $key; $key = $list->next_key()) {
      # ...
   }

   # Get a record:
   $rec = $list->get($key2);

   # Get a value:
   print "Value is " . $rec->get('field2');

   # Modify an existing record.
   $rec->put ('field1', 'new value 1');
   $list->mod ($key2, $rec);

   # Delete a record.
   $list->del ($key3);

   # Note that $list->as_text WON'T WORK, because the contents of the records are not cached in memory
   # (they are presumed to be too large).  Use an index if you need contents all at once.

   # Display the contents of the file:

(Treating text fields as attachments

It's often convenient to avoid loading long text fields in the initial record. In this case, we can mark the long field as an attachment, so that it's only read when specifically requested.

+head2 (Using an attachment as a ``rest of the record'' field

There are cases where we're given a MySQL table that has specific records already defined, but where our application could really use some extra ones. In this case, we can define an attachment to be a ``rest of the record'' field. This attachment will automatically be read when the record is retrieved, and its contents will be merged into the record as though they were already in the top-level list. Conversely, anything put into a record that isn't stored in the underlying SQL table record will also be split off into this ``rest of the record'' attachment.

This is especially handy when using wftk-specific functionality that doesn't fit into an SQL record, like multiple levels of fields, history or enactment within a particular record, and so on.

(Creating and dropping tables on the fly

The wftk can create and drop tables as needed, if told to do so, even determining the table fields on the fly if necessary.

(Creating and dropping databases on the fly

With a little more configuration work, the wftk can also manage the databases themselves in MySQL, given sufficient adminstrative privileges in its userid.

(Indexing a directory in MySQL

This is a practical recipe, and as such it's redundant with sections elsewhere in this tutorial, but it's convenient to put it here as well. MySQL plus directory storage of large files is a convenient basic document management system that makes sense in a lot of different contexts. It bears repeating.