In PostgreSQL I have implemented a materialized path tree (ltree) where each node has an an additional parent_id column besides the path.
These nodes can be associated with custom content types such as eg. folder and image.
The home directory for all these files are ./media
So right now i save the url file system reference inside the database like:
tbl_node:
id bigint
name character varying
path ltree (eg. 'nodeid.nodeid.nodeid.etc')
parent_id bigint
node_type int (document, media, template, etc)
tbl_content:
id bigint
node_id bigint
meta json (eg. {alt:"alt text here", caption: "etc", url:"/media/folder/(subfolders)/../image.jpg"})
tab_node.name = filename.filetype or foldername (in filesystem)
If PostgreSQL just had an equivalent to MSSQL's FILESTREAM or if DATALINK was implemented, then my problems would probably be solved, but I need somewhat the same functionality as these would have given me.
So my problem is... my node structure and file structure should always be kept in sync. However I can assume all renaming and moving of files and folders is done through my GUI and not directly in the filesystem.
That means whenever i rename eg. a parent-parent folder node in the database, the sub-sub image node's meta -> url must also reflect this new parent folder name (and of course the filesystem must change as well), so I guess I need to come up with a different solution, instead of saving a reference to the file (filesystem path) in a column inside the node.
Of course if I rename the node (image node) itself, I can easily change the url to point to the new name, and rename the image .jpg file during the db transaction in my golang model (node name = filename is my convention, as well as node hierarchy = folder hierarchy). The problem is when I change a parent node for eg. the parent (or parent-parent-...) folder - since I store the whole url on the children nodes to that image file, and the path has now changed.
Would a good solution to make a trigger fire when the path changes? I hope there's a better way.
What other approaches do exist, now that PostgreSQL does not have the FILESTREAM nor DATA LINK datatypes to help keeping my file system db nodes/tree synchronized with my media file system / directory structure?
Ps. Since I guess Postgres does not have a clever solution to this, maybe it could be relevant to mention that I'm coding in Go (Golang), if you somehow have some way of coding around this from the application itself.
Thoughts and pseudocode so far:
1) Consider this tree that maps to this file system
Tree, DB:
- sample image folder (node.name)
- image.jpg (node.name)
Filesystem:
- /media/sample image folder
- /media/sample image folder/image.jpg
2) First we rename the "sample image folder" node.name to "renamed image folder" (and of course it's corresponding content record's meta->url to "/media/renamed image folder" from the CMS GUI.
3) When renaming the parent folders node.name column and content records meta->url, during the same transaction rename the corresponding folder in the filesystem
4) Later in that same transaction select all children by node path (ltree, materialized path)
5) And by referencing on the node.id and content.node_id, update the corresponding content record's meta->"url" (file system reference) substring(0,lastindexof("/")) - before the filename. - for ALL the child nodes of the folder/node-folder we renamed.
Hopefully all this gives you somewhat a clearer idea of what I'm trying to achieve. :)
