Storing images directly in a database (in RAM)

Very early versions of Panorama had a Picture field, which allowed images to be stored directly inside the database, with no external file. This feature was removed a long time ago because it was very awkward and wasted memory. I can’t even remember how long ago it was removed, I don’t think Panorama 6 included this.

However, every now and then someone will ask for this feature. I got another such request this weekend. I said “no, you can’t do that, and you don’t really want to do that.” I still think you don’t want to do this. But, I got to thinking about it, and I realized that maybe it could be done - with the existing version of Panorama X! I decided that this was my idea of a fun Sunday afternoon challenge (I know, right?). Well, it turns out it can be done, and it didn’t take too long. Here’s a sample database I just made. It contains 8 pictures embedded in the data itself.

So if anyone is interested, let me explain how this works. The database has two fields, a Notes field and a binary field called Picture. The Notes field is completely ordinary, I won’t discuss it any further. The Picture field will contain the actual binary data of the stored images. As you can see, the images don’t display in the data sheet, you just see the number of bytes. If you double click a binary field to edit it, Panorama will ignore you. I called the field “Picture”, but you can call it anything you like. (Note: I left the Picture field visible, but you probably would want to hide it in the data sheet.)

There are 8 procedures in this database. However, don’t be intimidated, they are all quite short, several of them are only a line or two, and the Reveal Temporary Images and Delete Temporary Images are not really necessary. In fact, you could do this with just two short procedures, Load Image File and tempimagepath.

In the form, there is an Image Display object that displays the picture.

Now usually the formula associated with an Image Display object would calculate the path to the file containing the image to be displayed. But there is no separate file - the image is in memory! So instead, the formula uses the call( function to call the procedure tempimagepath. Somehow that must return a path! Let’s see how it works:

// Usage: let variable = call("","tempimagepath",<ImageData>)
//        ImageData must be binary data containing an image, usually in a Binary data field
//        returns full path to temporary file containing image (creating the image file if necessary)

let pictureData = parameter(1)
if pictureData=""
    functionvalue ""
    return
endif

let imagefolder = call("","tempimagefolder")
let imagename = replace(dbname()," ","")+"_"+sha1(pictureData)

let imagefullpath = imagefolder+imagename

if not fileexists(imagefullpath)
    filesave imagefullpath,pictureData
endif

functionvalue imagefullpath

This procedure starts by getting the binary image data that contains the image. This is in the Picture field, so the formula in the Image Display object included that field as a parameter. If you use a different name for the Picture field, you’ll need to change that formula.

Next, we create an imagefolder variable and load it with the path to a folder that we can use to temporarily store files. Don’t worry about this for the moment, I’ll explain where that folder comes from in a minute.

The next line sets up the imagename variable. To do this, it uses the sha1( function to cryptographically compute a unique 40 digit identifier for this image. This identifier will be unique for each image. But the identifier is not random - it will be the same every time you compute it for a particular image. The result of this calculation is going to be a unique name for each image, something like this:

PictureDatabase_32fe46c2f8db0774f24a42da8885018ec364250f

Next, the code checks to see if there is already a file with this name in the temporary folder. If not, it saves the picture data into a file with this name.

So now, voila!, there IS an external file with the picture data. Don’t worry, this file is only temporary, we just need it long enough to display the image. The final step returns the path to this external file to the Image Display object, which displays it!!

The Temporary Folder

I skipped over where the temporary folder came from. Let’s take a look at the tempimagefolder procedure.

// Usage: let variable = call("","tempimagefolder")
//        returns path of temporary folder, creating that folder if necessary

let tfolder = tempfolder()+"panorama_temp_images/"
makefolder tfolder
functionvalue tfolder

This code uses the tempfolder() function to find out the spot that macOS has reserved for temporary files. We could just keep the temporary images files directly in that folder, but I chose to make a subfolder inside it. You don’t have to do that.

The nice thing about this temporary folder is that macOS will automatically delete the files in it for you. It doesn’t do that right away though, only after a day or two. That’s handy because if we display an image more than once, we won’t have to take the time to save it to disk every time. But if we don’t use the database for a couple of days, all of the temporary image files will be deleted automatically - you don’t have to explicitly trash them. In fact, you’ll have a hard time finding them, macOS keeps these temporary files in a somewhat secret spot (but I’ll show you how to find them later if you want).

You don’t have to use the macOS temporary folder. For example, you could change the code to this:

let tfolder = dbfolder()+"temp_images/" // store temp images in subfolder with database
makefolder tfolder
functionvalue tfolder

With this variation the temporary files would be kept in the same folder as your database. However, in that case you would be responsible for explicitly deleting them.

Loading Image Files into the Database

Ok, so how do images get loaded into the database in the first place. There are two procedures for doing this. The Load Image Files… allows you to select one or more image files and load them in.

local fileChoices
choosefileDialog fileChoices,".jpg.gif.png","Multiple",true()
if fileChoices=""
    return  /* the user pressed the CANCEL button */
endif
let pictureField = call("","picturefield")

looparray fileChoices,cr(),fileChoice
    addrecord
    set pictureField, fileload(fileChoice)
endloop

The first section uses the choosefiledialog statement to allow one or more image files to be selected. The result is a carriage return separated list of image files, including the complete path of each image.

The next line sets up the pictureField variable with the name of the field that will contain the binary image data. This line could have been:

let pictureField = "Picture"

But I split it out into a subroutine, so that if the name of the field changed, you’d only have to change it in one place. I’ll explain further below.

The final section loops over each selected image. For each image, it:

  • adds a new record
  • uses the fileload( function to get the binary data of the image
  • uses the set statement to store the binary data in the picture field

That’s it!

Computing the Picture Field

I built a special procedure to calculate the name of the picture field. Most likely, you’ll only have one binary data field in your database. So this procedure finds out the name of that binary data field and returns it. If there’s more than one, it returns the first one.

/ Usage: let variable = call("","picturefield")
//        returns the name of the picture field
//        if the database has more than one binary field, 
//           you may want to customize this function

let binaryFields = dbinfo("binaryfields","")
// assume first binary field is picture field
let pictureField = firstline(binaryFields)
functionvalue pictureField

If you have more than one binary data field in your database, and you don’t want to use the first one as your picture field, then just modify this procedure to something like this:

functionvalue "My Picture Field"

Loading an Image from the Clipboard

Another way to get an image is to copy it. For example, you could take a screenshot to get that image into the clipboard. The Load Image from Clipboard procedure will load an image from the clipboard into the database.

let imagefolder = call("","tempimagefolder")
let clipimage = imagefolder+"temporary_clip.png"

clipboardimagesave clipimage
let pictureField = call("","picturefield")
addrecord
set pictureField, fileload(clipimage)
filetrash clipimage

The first line gets the temporary folder location. Can you see why I made that a subroutine - that way I don’t have to repeat the code that gets that location.

We’ll need a temporary file to hold the image for a moment. I simply called this *temporary_clip.png", as shown in the second line. Then the third line saves the image on the clipboard into a file with this name in the tempoary folder.

The next line gets the name of the picture field. Again, see why this is in a subroutine? That way, the picture field name is only specified in one place.

Next step is to add a record. Then fileload( is used to get the binary image data, and set is used to save that data into the picture field.

The final step is to erase the temporary file (using filetrash). This isn’t strictly necessary, macOS will eventually delete the file for me if I’m using the macOS temporary folder. But I know that I’m absolutely done with this file, so I might as well trash it now.

Revealing the Temporary Image Files

There’s no reason why you would ever need to look at the temporary files, but maybe you might want to, so I made a procedure to open this folder in the Finder.

let tempImages = call("","listtemporaryimages")
revealinfinder firstline(tempImages)

Most of the work is in the listtemporaryimages procedure.

// Usage: let variable = call("","listtemporaryimages")
//        returns CR separated array of images with full path

let imagefolder = call("","tempimagefolder")
let tempImages = filecatalog(imagefolder,
    "wildcard","*/"+replace(dbname()," ","")+"_*",
    "shallow","true")
functionvalue tempImages

I say “most of the work”, but it’s only two lines. First, get the temporary folder location, and then use filecatalog( to get a list of the files. Here’s what my temporary folder looks like:

Explicitly Deleting the Temporary Image Files

Again, there’s no real reason why you would need this, but I wrote a procedure to explicitly delete all of the temporary files.

let tempImages = call("","listtemporaryimages")
looparray tempImages,cr(),tempImage
    filetrash tempImage
endloop
nsnotify "Deleted "+pattern(linecount(tempImages),"# temporary image~")

It simply gets the list of temporary images, then loops of each image, deleting them one by one.

Kind of a fun exercise is to delete all of the images, then watch the files get recreated automatically as you display each image. Remember, the temporary images are just that - the actual permanent image data is stored in the database, in RAM.

Wrap Up

Well that’s it. The picture field feature is back! That explanation may have seemed a bit long winded, but it was actually only 47 lines of code. In fact, it took just about as long to write up this post as it took to write the code. Here’s the final result, showing the Action menu (which I renamed to Images). I also added buttons for loading, revealing and deleting images, you can see the icons above the image.

Let me repeat that it’s probably not a great idea to embed images in the database. Images take up a lot of space, and you can’t search, sort or calculate them, so you don’t get any speed benefit from having them in RAM. On the other hand, you do get the benefit that if everything is in RAM, there are no extra files to worry about. It’s all in one single file. If you don’t have too many images, and they aren’t very big, perhaps this could be a reasonable solution. It does work a lot better than the old Panorama 1.0 picture field feature.

Great stuff! I followed your instructions, and it works very well.

In my test I used a folder with 126 thumbnail pictures that I had used for a member database before. The thumbnaiils folder was 1.2 MB on disk; the database with the binaries is just 936 kB in memory.

I only added one line to the code, to fill the Notes field with the file names:

looparray fileChoices,cr(),fileChoice
    addrecord
    set pictureField, fileload(fileChoice)
    Notes = filename(fileChoice)
endloop

As you said before: I would use it for small amounts of pictures only. For greater numbers or for bigger pictures it is certainly better to use paths to pictures on disk.

Thanks for the idea and the instructions — as well as for 40 years of brilliance! I have been a whitness of it since 1985.

Wow, I did not realize you had been using the software that long. Of course back in 1985 we had no internet, so unless you came to MacWorld Expo, I wouldn’t have been aware of you. Thanks for your long patronage!