Looking for example of linked image sqlite DB

[Matadormac]

Good day the list.

After a multi-year absence I am back working with LiveCode (I began long ago when it was runrev). I have a desktop electronic parts inventory app using sqlite to keep track of the parts. I want to add several images for each part entry. I was researching LiveCode examples and found one which I think was a personnel or possibly a photographic inventory LiveCode database that was available for study.

Stupidly I did not bookmark that example. I have been through the LiveCode guides and tutorials and haven't found what I think I need nor have I found that particular example. So, as a last resort if what I am looking for rings a bell with anyone please let me know.

What I wish to do with my project is that given a "zillion" small electronic parts scrolling through a parts list with multiple images for each part would slow down or terminally bog down the app. So a separate databse just for the images but with a connecting unique identifier shared with the primary parts database, when a part is selected in the parts database, its corresponding images if any are searched for in the image database and show.

I have the primary database in LiveCode and sqlite done and working well.

Regards
Mark MacKenzie

[bogs]

Maybe I'm missing something in your description.

You have already a db with the parts listed and (I'm assuming) keys to those assigned.

You want to create a second db as an image look up, where multiple images are assigned to each part.

Seems to me you'd have to issue 2 calls separately to the 2 db's, and use the primary key lookup for the part as the primary key look up for the row with the images for that part, each image in it's own column.

*If* I have it right so far, what is the question? How to stick an image into a db?

[Matadormac]

Thank you Bogs for helping.

Before I answer your question, let me ask a perhaps more primary question which pertains to the problem.

If I have 300 to 500 electronic parts in an sqlite LiveCode desktop application, would putting 1 to 5 small images (perhaps no more than 60 Kb each in size) in that same database cause the whole thing to run slowly or poorly when doing queries and showing multiple results? If the answer is possibly not, then I should scrap the second co-joined db and just go with one sqlite db.

In that case and to answer your question. I want to add from 1 to 5 small (as in not much larger than thumbnail sized) images per piece of electronic gear and store them all in the sqlite database.

I understand that storing the images would be as binary files.

Regards

[bogs]

If I have 300 to 500 electronic parts in an sqlite LiveCode desktop application, would putting 1 to 5 small images (perhaps no more than 60 Kb each in size) in that same database cause the whole thing to run slowly or poorly when doing queries and showing multiple results? If the answer is possibly not, then I should scrap the second co-joined db and just go with one sqlite db.

Heh, well, there are a LOT of variables we aren't covering in that question. Probably the most important is how the information is pulled / displayed .

I assume you know how to pull in parts, i.e. your not pulling the whole db in one shot and loading it all into a table field or dg, but instead pulling say, 20-100 records at a clip and only if needed. IF that is the case, I don't see pulling images of the size your talking about being a super slow process.

However, there is a lot to be said for keeping the images separate as well, or even outside of a db altogether. For one thing your parts db is already setup and working, for another, it isn't that much extra code to pull from a second db and if there is a problem, your trouble shooting just the particular issue, which will most likely be the 2nd db.

Storing the images outside of a db altogether makes it even more dead simple, simply put the images into a folder with the name of the unique record key and display them when that record is pulled. At that point, there is no conversion needed, and if the images are stored on a server, for instance, they are accessible from anywhere.

I understand that storing the images would be as binary files.

Well, as a blob, but not necessarily as binary. I believe the recommended way would be to textencode/decode the images on insert/pull, but someone else might be better able to answer that one.

[Matadormac]

Thank you.

I was mulling over the use of a companion image folder and will try that first.

Mark Mackenzie