Saving Documents in PG vs File system

[Bastiaan Olij]

Hey Mike,


The approach we took is going down the WebDAV route. We wrote our own WebDAV server that queries our database for the meta data but then saves the actual files on disk. This ensures that users can't suddenly start moving files around and when things change in our database the WebDAV server magically gets updated. 


Now WebDAV is a bit of a conundrum, you can mount WebDAV servers as drives to give users direct access through the OS but support for this is dwindling. Apple Finder gets completely overloaded when you have large repositories (not really a WebDAV issue, also happens on normal file servers) and Microsoft was once a WebDAV stronghold but now has some weird 250 character maximum path length issue and limits the file size of WebDAV stored files to 500Mb due to security issues they had with publicly available WebDAV servers.
The WebDAV framework we've build our solution on (http://sabre.io) has a web interface that does a good job for access outside of our application so we rarely use the mount option. Yes it's PHP based but it's incredibly easy to build your own backend solution into this. It's really a joy to work with.


I'm simplifying but in broad strokes we expose a directory structure to the user that is totally virtual. So the user will see a list of folders, one for each client, go into a clients folder and we see a list of folders for the clients jobs, and inside of that folder sees the files they have uploaded for that job. That folder structure does not exist, we are creating it from the actual clients and jobs in our database. There is a table joined to our jobs table that contains the meta data for the files such as the name, location on disk, etc.


Say you do mount the WebDAV as a drive in your OS you will see this as a network drive with all the folders and stuff. When you copy a file in what happens on the background is that this becomes and HTTP upload of the file, our web server than moves that file into a nice location we manage and creates a new entry in our database so we know where the file is stored. The folder structure on the actual server can be anything.

>From within Omnis we use HTTP directly to communicate with the WebDAV server. We had to write our own XComp because in Studio 4 days Omnis didn't have the support but I think in Studio 8 the new HTTP workers have all the needed enhancements as they are similar to the improvements needed for REST. 

Kindest Regards,


Bastiaan Olij
Head of development - Instinct Systems: The JobBag People
Ground Floor, 48 Chandos Street
St Leonards NSW 2065
Australia


Phone +61 2 8115 8000
Mobile +61 4 321 44833
bastiaan.olij at instinctsystems.com.au
http://www.jobbag.com



 From:   Michael Matthews via omnisdev-en <omnisdev-en at lists.omnis-dev.com> 
 To:   OmnisDev List - English <omnisdev-en at lists.omnis-dev.com> 
 Cc:   Michael Matthews <omnis at lineal.co.uk> 
 Sent:   11/15/2018 2:25 AM 
 Subject:   Saving Documents in PG vs File system 

Hello All,

A question for those of you who store documents in your Omnis system.  We currently do, and we have a separate DB for the storage, and keep the metadata in the main database.

However, in certain cases, the PG database size is now over 100gb when exported.  And 99% of that data is static, so a waste of time in backing it up etc every day.

1     I could split the database into 2 parts as well, 1 for new items, which will periodically move items to the 2nd archive database.

2     But I want to explore storing the files in the file system, and passing them to the user when asked for.  Can PostGres save files locally, and then read team and send them back to a user within a SQL call?

3     I also remember Marten Verhoven showing how he uses Symbolic links to files that would normally be hidden AND inaccessible unless given.

So what do others do at this point with numbers of docs exceeding 100Gb now?

Thank you

Mike

          <http://www.lineal.co.uk/>         

 <https://twitter.com/linealsoftware>  <https://www.facebook.com/linealsoftware?fref=ts&ref=br_tf>  <https://plus.google.com/112982815000763473226/about>  <https://www.linkedin.com/company/1859834?trk=tyah&trkInfo=clickedVertical%3Acompany%2Cidx%3A1-1-1%2CtarId%3A1437729256906%2Ctas%3Alineal%20s>          
Mike Matthews
omnis at lineal.co.uk <mailto:omnis at lineal.co.uk>
Lineal Software Solutions 
01271 375999 
Commercial House, The Strand
Barnstaple, Devon, EX31 1EU 
www.lineal.co.uk <http://www.lineal.co.uk/>

 <http://www.lineal.co.uk/windows-support/>  <http://www.lineal.co.uk/microsoft-office-365/>  <http://www.lineal.co.uk/wifi-networks/>  <http://www.lineal.co.uk/wifi-networks/>  <http://www.lineal.co.uk/it-systems/>  <http://www.lineal.co.uk/gamma-horizon-voip/>  <http://www.lineal.co.uk/kerio/>  <http://www.lineal.co.uk/apple-upgrades-repairs/>  <http://www.lineal.co.uk/eset-security-software/>  <http://www.lineal.co.uk/shoretel/>



_____________________________________________________________
Manage your list subscriptions at http://lists.omnis-dev.com
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com