[Mifos-developer] Storing pictures in MySql or Just a link/url? Brainstorm

[John Woodlock]

Hi all,

Does anyone have opinions or experience on this topic.

Mifos isn't an 'images' system but, when creating/editing customers, Mifos allows you to upload and store a picture (in a separate table called customer_picture).  Mifos puts a limit on uploaded file size (which might be configurable, not sure).  I'm pretty sure it only gets displayed on the client details page.

Is there likely to be a noticeable performance or administration problem if this functionality really gets used to its max e.g. 1 million customers each having a picture? Or is it small stuff and the only mitigation is to put the picture on a linked page (if even that)?

In previous systems I've been involved with that had lots of pics (real estate stuff) only the link (or url) to the piccy and piccy information was stored in the db and that worked ok.  I'm biased towards that and I've never even bothered to build a system with pictures (BLOB type) stored in the Db so I haven't experienced the other side.

I've done a bit of a google survey and opinion seems divided.  Some people think have images in the db simplifies administration (e.g. if restoring or replicating you don't have to ensure the db matches up with a filesystem).  Others think it complicates administration... bigger database to backup/restore.

John

[Angshuman Sarkar]

+1 on storing images as a link.
- what would you do if you want to  migrate from one db to another (say postgre)? Each db stores BLOB differently!
- limitation on size as imposed by many dbs!
- with links, I can even store images on external repositories that are better managed. maybe even cache them :)
- significant amount of storage is lost, for data that ought not to be managed in databases!
 
call it prejudice, but such binary data on db is no-no for me.
regards
~angshu
 

------------------------------------------------------------------------------

[Adam Feuer]

On Fri, Apr 30, 2010 at 7:19 PM, Angshuman Sarkar
<ang...@thoughtworks.com> wrote:
> +1 on storing images as a link.

+1 from me too. BLOB == bad.

If we keep the picture feature - we should do it right. There must be
a framework for efficient storage and retrieval of files on a file
system. Maybe some research is necessary here? GK will be storing tens
of thousands or hundreds of thousands of pictures.

-adam
--
Adam Feuer <adamf at pobox dot com>

------------------------------------------------------------------------------

--
You received this message because you are subscribed to the Google Groups "Mifos Developer" group.
To post to this group, send email to mifosde...@googlegroups.com.
To unsubscribe from this group, send email to mifosdevelope...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mifosdeveloper?hl=en.

[Adam Monsen]

On Fri, 2010-04-30 at 19:25 -0700, Adam Feuer wrote:
> There must be a framework for efficient storage and retrieval of files
> on a file system. Maybe some research is necessary here?

I've implemented (or used? I forget) a pretty simple and performant
system as follows:

* checksum (say, MD5) of image is stored in the database
* actual image file is stored on a filesystem directly accessible from
the Web front-end/application (it is either the same filesystem or a
mounted filesystem, or--see below--a separate image store served by a
dedicated HTTP server)
* directory structure with images is separated based on the first five
characters of the image's hash. For instance, the image with a checksum
d41d8cd98f00b204e9800998ecf8427e would be stored at this location:

images/d/4/1/d/8/d41d8cd98f00b204e9800998ecf8427e

* the former is to guard against limits on the number of files in a
directory, and can be tuned as/if necessary
* if any image manipulation is necessary (ie: shrink or make a thumbnail
on the fly), the Web front-end/application can load the image directly
(via Java I/O) and make the necessary modifications
* small versions / thumbnails can also be generated upon upload
* images can be also served from a dedicated lightweight Web server that
serves only static content
* since the MD5 sums are basically guaranteed to describe unique images,
the image store can be shared across multiple Mifos instances (ie: a
multi-tenant to multi-server situation)

There may be something off-the-shelf that does all this, but this was
kinda fun and not complex.

[Angshuman Sarkar]

I have used Apache file upload quite sometime back.
http://commons.apache.org/fileupload/
Note, the info on virus scanning! I had to deal with a crazy QA, but he did get his point across!
regs
~angshu

------------------------------------------------------------------------------

[Keith Pierce]

I support storing images in files. But I agree with Adam F that we must do this within a robust, maintainable framework. As a trivial example, but disastrous if done wrong, is deciding on the format of the text of the image link stored in the database. We must ensure platform independence.

Keith

------------------------------------------------------------------------------

[Adam Feuer]

On Sun, May 2, 2010 at 11:35 PM, Angshuman Sarkar
<ang...@thoughtworks.com> wrote:
> I have used Apache file upload quite sometime back.
> http://commons.apache.org/fileupload/

Angshu,

I think File Upload only handles the actual upload from the browser -
and doesn't handle the actual disk storage organization of the files.
Or am I wrong?

We're looking for a nice, cross platform way to store and organize
these pictures on the filesystem.

[Emily Tucker]

Great thread!

Not sure if I followed the entire conversation-- but is the current
thinking we would try to find an open source file system and connect
Mifos to that? If so, a couple of other data points on GK's
requirements:

(*) GK also has the requirements of collecting scanned images of things
they use for age and identity validation-- scans of passports, voter
cards, etc-- and attaching these to the client record.
(*) Once these images are captured and attached to the client's record,
they want to have an "review and approval process" for these
documents... so that they can set up a "back office" system at the head
office during which people confirm that the scanned images do, in fact,
correlate to the client's data.... ie, that the birthdate entered in
mifos matches the passport scan, the name in the identify proof matches
the name of the client's record in mifos, etc. Didn't get all the
details, but they want some type of process flow where newly uploaded
docs would be in "pending validation" state, and then once they were
checked, they could move into "confirmed validation" state or something
like that....

Perhaps given those broader requirements, a file system is even more of
an obvious answer. Several other MFIs (although none of our current
customers) have asked for functionality allowing them to attach things
like msword doc to a client.

Also: given this conversation-- should we remove the upload photo
capability in mifos until we implement it correctly? I don't know of
any Mifos users are currently using this feature...

Emily.

[Adam Feuer]

Here's a page on document-oriented databases:

http://en.wikipedia.org/wiki/Document-oriented_database

Like business intelligence, this appears to be a whole sector by
itself. Therefore we probably should look into existing systems before
doing one ourselves. It may be easier to do a Document Management
plugin for Mifos based on Jackrabbit or Nuxeo (or something else)
rather than building it from scratch ourselves.

Here's Apache Jackrabbit, an Apache-licensed document database written in Java:

http://en.wikipedia.org/wiki/Apache_Jackrabbit
http://jackrabbit.apache.org/

Here's Nuxeo, an Enterprise Content Management based on Jackrabbit:

http://en.wikipedia.org/wiki/Nuxeo
http://www.nuxeo.org

cheers

adam
--
Adam Feuer <adamf at pobox dot com>

[Adam Feuer]

On Mon, May 3, 2010 at 10:32 AM, Emily Tucker
<ETu...@grameenfoundation.org> wrote:
> If so, a couple of other data points on GK's requirements:

Emily,

Great requirements! Thank you!

You didn't mention virus checking of documents - is that also a requirement?

> Also: given this conversation-- should we remove the upload photo
> capability in mifos until we implement it correctly?  I don't know of
> any Mifos users are currently using this feature...

Hmmmm. I have no idea how it actually works. If people do start using
it, we will have to migrate them.

So I vote yes for now. What are your thoughts?

-adam
--
Adam Feuer <adamf at pobox dot com>

[Emily Tucker]

> > Also: given this conversation-- should we remove the upload photo
> > capability in mifos until we implement it correctly?  I don't know of
> > any Mifos users are currently using this feature...
>
> Hmmmm. I have no idea how it actually works. If people do start using
> it, we will have to migrate them.
>
> So I vote yes for now. What are your thoughts?

My recommendation is also to remove this functionality until we can implement it properly and in a way that will scale.

If we think that's the right approach, I'll post something to the mifos users list to get feedback from the community.

Emily.

[Angshuman Sarkar]

Hi Adam,
For files/resource management usually a CMS (content management system) is used.
e.g : joomla (http://joomla.org)
regards
~angshu

P.S: FileUpload is just a mechanism for uploading file.  The reason that I sent across the link,  were issues that are often related with file uploading and "storage using links" that we often tend to overlook. like a virus effected file that is uploaded. And there are quite a few things to look out for. this should give some info http://commons.apache.org/fileupload/using.html
If mifos already handles all that, then just ignore my comments please. 

[Udai Gupta]

Hi Angshuman,

> P.S: FileUpload is just a mechanism for uploading file.  The reason that I
> sent across the link,  were issues that are often related with file
> uploading and "storage using links" that we often tend to overlook. like a
> virus effected file that is uploaded. And there are quite a few things to
> look out for. this should give some info
> http://commons.apache.org/fileupload/using.html
> If mifos already handles all that, then just ignore my comments please.

No, Mifos does not handle the virus issues (or other vulnerability)
related to unrestricted file uploads for now. But, it's a improvement
issue which has been logged for future work. And we think this
vulnerability is covered for now by the user policy.

http://mifosforge.jira.com/browse/MIFOS-2531

Apart from image upload there are other areas where the file upload
takes place. (under admin tab)
1) Reports and Admin docs upload.
2) Import export (may be for this it doesn't matter as we parse the
file and discard it after getting results to be uploaded)

Udai

[Adam Feuer]

On Mon, May 3, 2010 at 11:52 AM, Angshuman Sarkar
<ang...@thoughtworks.com> wrote:
> For files/resource management usually a CMS (content management system) is
> used.
> e.g : joomla (http://joomla.org)

Angshu,

Ack! Joomla is PHP.

Also, it's mainly for websites - not enterprise document management.

> If mifos already handles all that, then just ignore my comments please.

No, I don't think we handle the upload part of - and for that it looks
good! But what I meant was that it's only part of of what's required,
and doesn't include storage and retrieval.

-adam
--
Adam Feuer <adamf at pobox dot com>

------------------------------------------------------------------------------

[John Woodlock]

It seems pretty clear (especially when you add a need for extra image functions and document functions) that the conversation has moved on from whether to use a mysql BLOB or just a link... to how much does mifos need to know about images and documents.

I'd imagine the answer would be 'as little as possible, let systems that are already proven do that'.  The picture (sorry) I'm seeing is that there would be (hopefully open source) system(s) for managing images and documents and that system(s) does the hard yards and there just needs to be a shared reference (client number or global num or something shared) to connect to mifos clients.

Core mifos could just support the ability to 'review and approve' things (very simple) and the client view/management web pages should just include the ability to link/embed to the system(s) that supports images and documents.

I know it's pretty simple and obvious but is that the way people would see it?

John

[Keith Pierce]

I'd think that the Mifos application would manage any metadata about the images that Mifos business functions require (date, version, who, what, where, why, etc). If this is a valid assumption, then I'm not sure why Mifos would need some image/document management system other than the file system.

Keith

------------------------------------------------------------------------------

[John Woodlock]

Keith,

That might be how it ends up okay.  However, it'd be even better if mifos didn't manage the data you mentioned... if image and document management could be seen as add-ons that can be brought into (or not) the mifos platform (maybe too grand a word).

Maybe reports would need to pull in data from mifos and associated systems but in general these seem pretty separate.

john

[Keith Pierce]

When I wrote about Mifos managing the metadata, I didn't mean to preclude the option that a separate plug-in would manage it. Rather, my experience with monolithic, expensive, everything-but-the-kitchen-sink document-management systems colored my wording a bit negatively. A lightweight wrapper around the OS file system should server Mifos's purposes.

Keith

------------------------------------------------------------------------------

[Adam Feuer]

On Mon, May 3, 2010 at 7:33 PM, Keith Pierce <krp...@gmail.com> wrote:
> When I wrote about Mifos managing the metadata, I didn't mean to preclude
> the option that a separate plug-in would manage it. Rather, my experience
> with monolithic, expensive, everything-but-the-kitchen-sink
> document-management systems colored my wording a bit negatively. A
> lightweight wrapper around the OS file system should server Mifos's
> purposes.

Keith,

Yeah, and having written those "lightweight wrappers around the
filesystem" I know that storing and retrieving documents is more
complex than that.

I also don't want to go super heavy-weight either - I don't like
monolithic document management systems. But I am imagining that other
people have solved this problem already for us.

I'm thinking there are libraries or java services out there that can
handle storing, searching, and retrieving documents (BLOBs) of various
types, based on metadata that is stored with the document. The wiki
page on document management systems lists several.

In the meantime, let's rip out or disable the current "upload image"
functionality until we can get a handle on how to do this in a good
way - where MFIs can store and query hundreds of thousands of photos.
And perhaps other documents (word, pdf, etc.)

[Ryan Whitney]

Just as FYI, requests from MFI's for a document management system do come up
occasionally (Marie would probably have more comments on that). If we're
going this far (as it appears beyond just capturing photos), then yes, I
would agree we look first strongly at integration options.

Ryan

On 5/4/10 1:38, "Adam Feuer" <ad...@pobox.com> wrote:

> Here's a page on document-oriented databases:
>
> http://en.wikipedia.org/wiki/Document-oriented_database
>
> Like business intelligence, this appears to be a whole sector by
> itself. Therefore we probably should look into existing systems before
> doing one ourselves. It may be easier to do a Document Management
> plugin for Mifos based on Jackrabbit or Nuxeo (or something else)
> rather than building it from scratch ourselves.
>
> Here's Apache Jackrabbit, an Apache-licensed document database written in
> Java:
>
> http://en.wikipedia.org/wiki/Apache_Jackrabbit
> http://jackrabbit.apache.org/
>
> Here's Nuxeo, an Enterprise Content Management based on Jackrabbit:
>
> http://en.wikipedia.org/wiki/Nuxeo
> http://www.nuxeo.org
>
> cheers
> adam

--

Ryan Whitney
Mifos Technical Program Manager
rwhi...@grameenfoundation.org
Mifos - Technology that Empowers Microfinance (www.mifos.org)
Our mission is to enable the poor, especially the poorest, to create a world
without poverty.
<http://grameenfoundation.org/take-action/ingenuity-fund-challenge/>
P please consider the environment before printing this e-mail.

[Adam Monsen]

Emily wrote:
> My recommendation is also to remove this functionality
> until we can implement it properly and in a way that
> will scale.

Yes, two thumbs up!

Emily, thank you for quickly identifying and distilling an awesome
example of how we can make Mifos great by pushing out "non-core"
functionality... this will allow us to worry about less and do a
better job at it.

Woo hoo!!

[Sam Birney]

Another +1 on avoiding BLOB and TEXT fields... Speaking of which did
you know account.collateral_note is a TEXT field? We noticed that
because there seems to be no way to show it in a BIRT report... It
seems like a normal varchar would suffice, hopefully you will consider
changing it.

And +1 on not managing images in Mifos, maybe just a reference/link to
them in another system. I would recommend Gallery open source
software as a good option to look at for that.

http://gallery.menalto.com/

[Emily Tucker]

> Speaking of which did
> you know account.collateral_note is a TEXT field? We noticed that
> because there seems to be no way to show it in a BIRT report... It
> seems like a normal varchar would suffice, hopefully you will consider
> changing it.

Hi Sam,

Thanks for noticing and reporting this. I added this enhancement in the
issue tracker:
http://mifosforge.jira.com/browse/MIFOS-2905

Emily

[Ed Cable]

Hello all,

I wanted to reinvigorate this thread to see how much effort it would take to extend the current client photo upload capabilities to allow for the attachment of files and documents to the client record.  The functionality would be pretty simple and not require sophisticated document management, file editing, searching etc.

In the case of one potential Mifos user, this functionality would be useful because they authorize their loans from a remote location where the files are collected.  If they could scan in their documents and then upload and attach the client record - these documents could easily be accessible from one centralized client interface.

I've created an issue to track this at: https://mifosforge.jira.com/browse/MIFOS-5739

Could any other users interested in this functionality please add your requirements/comments to this issue? 

Jakub could you please provide an estimate. 

Miguel - could you add additional details for what use cases you would need to be supported? Would this be at the client level or at loan account level too? 

On Tuesday, May 4, 2010 9:43:52 AM UTC-7, Emily Tucker wrote:

> Speaking of which did
> you know account.collateral_note is a TEXT field?  We noticed that
> because there seems to be no way to show it in a BIRT report...  It
> seems like a normal varchar would suffice, hopefully you will consider
> changing it.

Hi Sam,

Thanks for noticing and reporting this.  I added this enhancement in the
issue tracker:
http://mifosforge.jira.com/browse/MIFOS-2905

Emily

------------------------------------------------------------------------------

--
You received this message because you are subscribed to the Google Groups "Mifos Developer" group.

To post to this group, send email to mifosdeveloper@googlegroups.com.
To unsubscribe from this group, send email to mifosdeveloper+unsubscribe@googlegroups.com.

[Jakub Sławiński]

Hi Ed,

I added my estimate to this ticket.


Regards,
Jakub.

>> To post to this group, send email to mifosde...@googlegroups.com.

>> To unsubscribe from this group, send email to

>> mifosdevelope...@googlegroups.com.

>> For more options, visit this group at
>> http://groups.google.com/group/mifosdeveloper?hl=en.
>>
>>
>>
>>

>> ------------------------------------------------------------------------------
>> Live Security Virtual Conference
>> Exclusive live event will cover all the ways today's security and
>> threat landscape has changed and how IT managers can respond. Discussions
>> will include endpoint security, mobile security and the latest in malware
>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>>
>>
>> Mifos-developer mailing list
>> mifos-d...@lists.sourceforge.net
>> Unsubscribe or change settings at:
>> https://lists.sourceforge.net/lists/listinfo/mifos-developer


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
Mifos-developer mailing list
mifos-d...@lists.sourceforge.net
Unsubscribe or change settings at:
https://lists.sourceforge.net/lists/listinfo/mifos-developer