parsing data blobs in sqlite to/from max
I'm looking at designing a database, using sqlite in max. The database of compositions would contain tables such as piece name, piece (midi file), composer, composer photo (jpg), and other tags/information as necessary.
So Max would be an interface to the database, where media can be added to the database and displayed and played back.
I was thinking of having all the media (jpg photos, and midi files) as sqlite blobs.
Question 1: is this possible?
If so, Question 2: how would I parse the media to and from database via max?
I'm thinking drag/drop photo into max->convert image to jitter matrix-> somehow put matrix as blob into sql via js wrapper
MIDI file would be: open in detonate->parse into coll (each entry in coll is a MIDI event)-> into sqlite as text blob via js wrapper
then accessing would be:
photo: convert blob to matrix in js, then write matrix as rgb image to file (outside of database, so it can be used by another application to display the photo)
midi: read text blob from database, refer coll to it, then playback using coll referencing
This all seems rather convoluted-- would anyone have better ideas?
( I have some experience with js in max, but haven't really used database applications before
Thanks
I think storing an image in sqlLite is very inneficient over a certain size. Of what i understand, it's ok for thumb images max 64x64 as blobs.
it will be better to write the image on your disk, and store a ref url/path of this image in your database.
I'm trying to avoid file paths as a reference. If files are stored externally to the database, and a file gets moved then the database would need to be updated, so I'm thinking of keeping things simple. So the database would be sealed up and not accessible except via my max patch (or another database application). I'm also looking at portability-- I would just have to move the database file to a different computer rather than replicating a whole directory structure.
This page https://www.sqlite.org/intern-v-extern-blob.html indicates that if I keep blobs below 20K in size then sqlite is actually faster to access internal blobs than externally stored files; so yes 20K isn't very big-- as you say, thumbnail size for uncompressed rgb, but maybe if I could parse jpg or png files in max without converting to uncompressed rgb matrix files then I could use standard portrait resolution shots and still keep it fast and efficient...?
It would be mostly single user-- so it wouldn't be processing more than one or two accesses a second, although I might have some scripting to automate some processes to build the database initially