Android Question Storing and Retrieving images / SQLite

[aklisiewicz]

The data is created on the server through PHP App which works with SQLite.

I have database which stores image name / path in the tbale (say MyImages)
So the column that holds an image is: ImagePath

I have a subfolder ie. /home_html/app_folder/image_folder/... where all the images are stored in subsequent folders: /RecID05/... then /RecID06/....


There is a fixed path so the user uploads the image and enters the name into the database.
the App (in necessary creates subfolder like /RecID06/ and saves the file in that folder

I need to download the images along the the SQLite database to the device and use same approach to display the record with the ImageFilename and when the user clicks a record display an image.

There will be no editing on the device, however freequent updates will take place. I assume I will let the user download updated SQLite file along with other files (there will be other files which will be updated frequently. i.e. HTML files, MP3 files etc.) so I suspect the same schematics cold be applied.

Since there will be lots of files, the images will be stored in folders and subfolders. Each folder is named by the RecordID so it is easy to reference an image.
i.e.
/RecID02/imge01.jpg , image02.jpg
/RecID03/image45.jpg , image54.jpg , image43.jpg



QUESTION:
What is the best solution to do this ?

Would it make any difference if I store the images in BLOB instead ?


Obviously I can have all images compressed to the ZIP file (on the server) then downloaded, but perhaps better solution exists. At the moment to make it simple I will download the whole database along with the all images folder. Later I might improve the performance by downloading only new stuff.

would appreciate any suggestions, or examples
Thanks - Arthur

 

[Erel]

How many images are there? What is the average size of each image?

 

[aklisiewicz]

I have Parent/Child relationship. The parent table holds RecordID alon with other Informaction. The child table hods RecordID, ImageID, ImageFilename. In theory this structure would allow to have unlimited number of images linked to the ParentRecord, however in reality there should not be more than up to 10 images per parent record. Since the Parent Table might have roughly up to 5.000 records there could be up to 50.000 images.
In reality it should be about 20.000. The image size is not set yet, so this would actually be my additional question what image size would be recommended for this to work efficiently (I was thinking of about 50-60k) but the size limit is open.

Would it make sense to store an image in BLOB ?

Thanks - Arthur

 

[Erel]

20,000 images * 50k = 1Gb. This sounds too much to be downloaded to a device (as multiple files or in a database).

 

[aklisiewicz]

ok, so what would be suggested image size in your opinion (so it still can be displayed in full screen mode and preserve quality) ?
I can limit number of images to 3 per parent record so it will cut down the size of the download. Would it be more efficient to keep images in BLOB instead ?

I need to implement similar functionality with MP3 files (but only 1 per ParentRecord). shall I use BLOB ?

I can eventually shrink down the main parent table to about 500 records.

Arthur

 

[Erel]

It is hard to say. You will need to run some tests. You should also consider working with only a small local database and have the full data online.