Will Sesame 3 have a "blob" data type that allows storing random data with no change (such as an MP3 audio file, Excel spreadsheet, executable file, etc.)?

Will Sesame 3 have a data type than can store and display formatted text? (Most likely an RTF format, since that is pretty much universally understood by programs that handle formatted text, and with the ability to retain basic formatting when copying and pasting between programs, such as copying text from an email and pasting into Word and vice-versa.)

Quote:


I'm thinking, for instance of what FoxPro had--and we did have this discussion before, but not specifically in the Sesame 3 context.

Regarding display, because any type of data could be stored there normally there is no provision to display the data.  If you "look" at the field it only displays some type of "Empty/Not Empty" status indicator.

Regarding entering and retrieving the data, the only thing the program can do is store something to the field, (usually either the contents of the clipboard, a variable, a field, or a binary file), set a variable, a field or the clipboard to the contents of the field, use the field like other fields would be used, e.g., MyData = @Left(BlobField, 1000), or write the contents of the file to a binary file.  Considering that the field content could be 10-100MB or more, you want the ability to read and write files directly without having to use a variable in memory.

I think I may have seen some programs that allow a text-box-like entry "window".  But frankly, I don't see that all the extra work to include that functionality would be useful.  The user could create an unbound text box and then set the blob contents to it.


Regarding, "valuable database space," with 2TB drives commonly available for well under $100, even a 100GB database really wouldn't be that big a deal.  I could easily see storing things like audio files or hi-def photos--even in multiple formats and at multiple resolutions.  For instance, awhile back I was looking into creating a "personal information" database with FoxPro that would have scans of diplomas, tax returns, photos of places I lived, and all sorts of information.  Even though FoxPro is limited to 2GB per DBF file, that still would store a heck of a lot of data as long as I don't include any videos.  Quicken, for example, has an "attachment" feature that lets you attach images of checks, invoices, etc., to an individual entry.


One problem with the "mass update", tar/zip, etc., approach is that it assumes someone fairly knowledgeable will be readily available. From what I have seen, it looks like Sesame's clientele are mainly Windows power users working at companies that can't/won't spend the money to have an outside firm write a custom application from scratch. Or an outside consultant writes the application and an in-house power user handles most of the sysadmin work and can do things like mass updates.  In such cases, as long as the power user who works with Sesame is available, the mass update/archive approach may be viable. But one problem is it does clutter up the system with perhaps hundreds or even thousands of extra files. And then there is also the security issue, making sure users don't access such files. Another problem is the various indexing programs. You either have to make a huge list of "exclude" folders or search results show up hundreds of irrelevant files.  In theory a sysadmin can handle that.  But in a company where the sysadmin is really just a power user or one person is expected to handle everything, those things usually fall by the wayside.  Also, most programmers and sysadmins hate having a huge number of mixed file types.  If they are going to have "associated files", normal practice would be to put all wordprocessing documents in one folder, audios in another, PDF's in another, etc.  And then those probably would wind up being categorized more, e.g., by client, year, decade, etc., with audio/PDF/video folders for each client.  That basically would be unnecessary if the data is in the Sesame table.

Another problem with the mass update/archive approach is old records.  Records from 2+ years ago are removed from the database and archived.  Subsequently, for whatever reason, someone decides to move all the current "associated" "blob" files and does a mass update.  I guarantee you no one is going to remember to reload all the old records, unzip the archived "associated" files, move the "archived associated" to the new folders, mass update everything, and then rearchive and re-remove the old records. And, of course, sooner or later someone is going to need the archived records because an old client is being reactivated, etc.  It's a lot easier to do like Quicken and many other programs--when you exit it asks "Backup database?"  You press OK and 30-60 seconds later it says "Done". That's a lot easier if everything is in a handful of table files.

Realistically, most binary files won't be large. Yes, someone might store a 120-page scanned PDF or videos or executables. But Sesame isn't an "industrial-strength database". Chances are the majority of files would be less than 10MB--probably similar to what typically can be attached to an email.

Rick_R wrote on Jul 16^th, 2011 at 3:37am:


No, that is not expensive. But nor is it "built in". Sesame can also invoke a PDF interpreter/renderer that is external to Sesame. Either invoke it directly and use the file name as an argument, or use the OS's ability to establish a file association between that file type and the application you (or your user) chooses to view it. Take a look at ASynchShell and the other process management commands.

Plug in technology like you are referring to generally relies on one (or more) of three approaches: DDE/OLE/COM, .NET, or a prefabricated DLL enablement.

In the last case, the people that built the application have specifically built it to act as a plugin, using rules they designed - no standards, no uniformity, no predictability. In this case, we would need to write custom code to manage each "plug-in" that we wished to support. Sesame 3 has the ability to call code in a DLL using SBasic, so this could be accomplished by a SBasic programmer, but it is non-trivial.

If provided, DDE/OLE/COM, provides predictability and uniformity. Unfortunately, these are rag-tag pseudo-standards promoted by MS without reasonable support for real life reliability and are being phased-out rapidly both in the field and by MS. I'm afraid that creating an object framework based on the "cut and paste" library developed during the DOS to Windows transition and then misusing the already badly flawed Window's messaging system, and adding poorly understood object orientation as a superficial layer on top of the chaos stew, resulted in less than reliable code.

.NET, as a clone of the Java/XML approach to object management, has some decent chops. Unfortunately, like most of MS technology, it a framework rather than a addable resource and would require a total rewrite of the client. And, of course, like the other two approaches, is not cross-platform.

Over in Unix land, there is support for MONO (like .net), Corba (like COM/DCOM), and in the real old days, there is a way to simply "steal" an X11 Window from one process and embed it in another. This last option may be usable in both Windows and Unix, but it is a half-measure and not terribly elegant.

Invoking the application recommended by the desktop to interpret/display/edit the format provided, and allowing that application to open and display a window for that purpose, while mildly old fashioned, is flexible, works with any set of applications without need for a predictable interface, doesn't clutter the database with unsearchable formats, follows a standard that exists on all modern desktops, is compliant with each user's preferences, and doesn't rely on ever-changing pseudo-standards nor use of special languages, libraries or frameworks.

On the other hand, there are cross platform PDF interpreter libraries available that can display PDF formatted documents. I suspect that the Adobe licensing may only apply to editing/creation. I'll look into whether terms of the licenses are acceptable for inclusion in a commercial product. I would always prefer to base something like this on an open standard, like XML. There are XML formats for text formatting, much like xHTML/CSS, but the Windows world seems to much prefer proprietary approaches.

Regarding "cluttering the database with unsearchable formats", if the data is stored outside of Sesame, it becomes completely inaccessible to Sesame. Deciding whether to put the data into the database or keep it separate would be the developer's choice. Not every piece of data needs to be searchable--audio, video, images, etc., normally aren't.  The developer could include related Keyword, Tags, Description, Author, etc., fields if they feel such information is important.

However, I would suggest that a "Blob" data type have two separate components--a place where the actual data is stored, and a searchable metadata section editable by the programmer or user. That would be a lot more useful than only being able to store the actual data.  Plus, since so many data formats now include metadata, it is very likely someone wanting to store a video, image, etc., would want to extract the file's metadata and have that available in the database.  Perhaps the metadata could be stored in a stringarray whose name is somehow associated with the blob field. MyVar = BlobData("BlobLayoutElement", "Metadata").  That way the developer could have different metadata elements for different blobs fields.

Also BlobContents = BlobData(Location).  If Location is the name of a LayoutElement, the data will be pulled from the record.  If Location is a file location, it will be pulled from there.  The developer could distinguish by preceding a file location with "file://" or something similar.

By the way, I think GetBlobData() and SetBlobData() would be clearer names.


To a lot of programmers (me included), "we can call up other programs and store the data as a bunch of external files" sounds like the cobbled-together "I have a cousin who's a computer genius" approach of many freelancers the early 1990's.  At work we have QA, with a conversion program run from a DOS prompt that simply displays a text file as a menu and then returns to a DOS prompt, then the user presses 1, 2, 3 or 4 and that runs a batch file.  That runs a QBasic file.  Then the person copies that file to another folder, then manually loads it into the QA program, etc. There was a completely unrelated program with DOS Procomm to receive one download a day from one client.    For my own use, when I found out that Sesame doesn't have a "blob" data type, I immediately eliminated it from possible use for the "personal information" database I was looking to set up, since I wanted to move the data from thousands of files to a single location.  (I do genealogy as a hobby, so I have a lot more information than just "important papers".)

I just checked the specs for Visual FoxPro and they say the blob data type is limited to "available RAM or 2GB file size".  Realistically, I can't see that anyone would really need to ever store something more than about 10MB.  Most likely that would be something like a very hi-res photo.  It's pretty unlikely anyone would be storing a lot of large blob fields in a single record.

You could poll the various users here and on Facebook about a size cap and if people start complaining that they are regularly exceeding the cap, change it.  Or maybe have the cap set in the ini file. It might be a "hard" cap or just trigger a warning.  Or allow a setting in the ini file to decide whether it is a hard cap or just triggers a warning.  The ini file could also include a default file path for external files being accessed by GetBlobData() and SetBlobData().


Should I implement BLOBs, of any kind, you still have not answered my original question:

How do you want them specified and edited?

I don't recall how FoxPro does it for blob fields but, for example, if you include a memo field in a spreadsheet-type browse display it just displays the word "memo".  If the field is empty it is "memo" in lowercase and if it has content it displays as "MEMO". You have to click on the field to open the edit window (similar to F6 in Sesame). I would say just an "Empty"/"Content" indicator.  If the programmer wants to display the data with a particular program, it would be up to him/her to handle it.  (And since many data files contain a type indicator near the beginning, the programmer could read a small set of the characters and determine the data type.  For instance, all WordPerfect documents contain WPC about 3-4 characters from the start of the file.)

I have not seen any "blob" type that permits editing.  The only exception is that you can either replace the contents or append to the existing contents. I would say that editing should be handled by the programmer--write the blob to a scratch file, call up the appropriate program to edit it, save the modified scratch file, and then back in Sesame replace the existing blob and delete the scratch file.

Loading the data would have to be more programmatic than Sesame's usual form-input paradigm. For instance, the programmer calls up the File Picker dialog and sets the blob field to the contents of the chosen file.  Or the form contains an unbound text box and when the user exits the box some On Exit code sets the blob field to the text box contents.  Or the user can press a "Save Clipboard" command button and the blob will be set to the contents of the clipboard.

Would the element be limited to specifying a filename and then Sesame would copy that file into the database file?

I would say the data source is set as a "DataSource" attribute of the LE.  Most likely it would be Variable (name); some other LE's value; FilePicker; FileName; Clipboard.  If the programmer wants to provide choices (e.g., FilePicker or Clipboard), I assume they could set up a menu, radio buttons, etc., and then change the DataSource LE element on the fly.


Should the same data (file) apply to multiple records, do you want Sesame to make copy of the file internally for each record that refers to it? Without an external file reference, Sesame would have no way of knowing that it is simply duplicating the same data over again for each record that refers to that data.

Actually, yes, that would be necessary. You can't assume that because the data starts the same it will remain the same. For instance, if I have to send the same type of document to several people, I will routinely make an original and then make duplicate copies of the file under various names. I might not get around to changing the contents of the other copies for days.  I could easily see, for instance, someone puts the same image in 3 records, and then for records 2 and 3 copies the blob to the clipboard, pastes it into a graphics editor, changes it from color to grayscale or changes the size or resolution, copies the image back to the clipboard and then copies the clipboard back to the blob.

Whether to duplicate or not would also be to a certain extent up to the user.  For instance, most of our work involves hospital patient accounts, sometimes for the same patient, sometimes not (e.g., multiple patients in the same car accident).  Sometimes in the "history" file the person will simply put "See Account 12345".  Other times they'll retype almost the entire text.  "See Account 1245" does get to be a pain when the main account settles and is removed from the system.

If the programmer is concerned about excessive duplication, it would be fairly easy to write a report that calculates a checksum on the blob contents and then counts how many records have duplicate content.  That could also help the design in that if there is a lot of duplication, maybe there needs to be a command button that will do various things automatically that users have been doing manually each time. Or, it might indicate that something is a regular activity or recurring problem and every time it has come up people have been treating it as if it is a one-time event.


If it maintains the file path, then it would be necessary to also retain the external file, and storing it internally simply becomes wasteful and redundant.

That should be determined by examination or deciding how the users will use the data.  If the users are going to use the same 5 form letters about 30 times a week, the programmer should know that and not put the data in a blob field. But if the contents are likely to routinely vary, some duplication isn't significant.

In the example I gave before where GetBlobData(Location) is a file location, I would assume that GetBlobData() is really two separate functions internally.  If Location is a filename, GetBlobData() would simply read content from that file. If Location is a Layout Element, then the data would be pulled from that blob field in the database.  From the programmer's perspective, s/he would not have to use one set of functions to read/write data to a blob field in the database and a different set to read/write to an external file.

Instead of indicating that Location is an external file by adding something like "file://" to the path name, an alternative might be to include an "Internal/External" attribute. However, adding something to the name could be more flexible by allowing full-blown URL's.

By the way, if SetBlobData(Location) can write an external file, it probably would be a good idea to include an attribute for read-only/change/write/delete permissions.

Rick, I am still unconvinced that this type has enough value to be worth the cost. Can you outline the advantages that this provides other than "not appearing old fashioned" over using an external file system vector system. I see two:

1. It provides a way to keep all of your eggs in one basket, which while useful for moving files does also cause additional risk: one bad block and all your critical "files" are toast.

2. It prevents external removing / renaming from affecting a previously edited record.

In address to your belief that it is "cobbled", I was able to find several hundred recent "white papers" from MS, IBM, Oracle, and other prominent software/database companies debating the merits of each approach under different circumstances. Clearly, both approaches are current and in different cases useful. That any system could be "cobbled" is more a reflection of the limitations of its application level design, than a reflection of its era. Both approaches have been with us since the 1960s and both have been used, tested, metered, and debated since then.