read/write TOP imagedata to SQLite blob

Beginners
#1

What is the best way to store an image from a TOP as blob in a SQLite database? (images are <50kb)

Currently I can do it by using the Save method from the TOP Class, saving to a tmp file on disk, and reading this tmp file and saving it as a BLOB in my SQLite database.
It works, but is not very efficient.

And, the other way around, if I want to read lots of images from SQLite and use them in TD, is there a better approach than saving the BLOB’s as tmp files on disk and opening these with python & MOVIE IN?

As I use SQLite as storage I would prefer to skip all the tmpfiles on disk.

#2

Hello Mr. Nettoyeur!
I have seen you around these parts for the past several years and I have always respected your TD prowess and forum helpfulness. It is interesting to see this unanswered question that you had way back in 2014 about SQLite databases.

I came here today wondering about creating visualization of various types of datasets. Being an electrical engineer I have always put my information in spreadsheets/tables and accessed them with python, but 2D spreadsheets break down when you start getting more complex datasets with interdependencies, etc. I honestly know nothing about SQL nor any other databases. I have a passing familiarity with JSON.

I would like to ask you …now that it is 2021Feb do you still use SQLite for storage or have you settled on other data storage solutions? What is your current preference for storage of “complex” data sets that need to be efficiently visualized in TD?

Bonus question: you mentioned in your original question that your images were <50kb…but what if there are media assets (audio files, video files, etc) that are not small, but fairly hefty in size/resolution? Do you store the media in the database? …or just references to the media that lives somewhere else like in a folder?

Sorry if this question is too vague, but I am just trying to shape how I move forward creating my own data sets in TD for visualization…speed/efficiency will be important.

Thanks!
VoltVisionFrenchy aka Steve French
voltvision.com

#3

Well aint this a blast from the past :wink:
And thanks for your kind words @VoltVisionFrenchy.

After all these years I would say there is no universal storage solution that satisfies each project’s needs. Some projects require very complex storage retrieval features, other only speed, others need to be finished tomorrow. Which means apart from the project needs, it also depends on what experience the developer has.

That said, for large complex sets with inter-dependencies, not many things beat an old fashioned relational database. In that field, SQLite is still extremely stable (it’s used in almost every cellphone in the world), zero effort to deploy/maintain, and extremely fast, suitable for very large complex datasets. For small datablobs like thumbnail images they claim it’s ~35% faster than your regular filesystem.
If your files are larger than a small thumbnail, you should indeed only store the reference to the file path in your database. SQLite is either memory or disk based, but it’s meant to be used on the same machine without having to start an external db server.
If you have multiple machines / processes and need a central database I would use something like a Redis server probably.

But if you don’t need advanced database features (or coding for databases is not your thing) , there are many other ways in TD. You can store your information simply in DATs, which makes it easy to edit, and you can save/load them to .dat or .csv file format.
Very often I use Python objects like dicts, which you can load/save to JSON fileformat.
Whatever format you choose, the management code is best written in a TD Extension.

Also these days you don’t need tmp files on disk anymore to work with TOPs, now you can get bytes from a TOP directly, using the TOP’s class numpyArray() or saveByteArray() methods.
And you can load bytes directly in the Script TOP using copyNumpyArray, copyCUDAMemory or loadByteArray.

Hope this gives you some inspiration pointers!
cheers, idzard