0

I have to write Java application working with Postgres, where I will be storing Text files. I need to have functionality to do gitlike commit/revert, so I created first goals to achieve:

  • commit from program will write document with info about latest change owner, date and create something like log or log file in database - HERE IS MY QUESTION :)
  • revert will have parameter (commit ID) and using log/diff file it will revert all changes

So, my question is

  1. how can I create meaningful, not so poor performance system like this? I was thinking about creating PL/pgSQL function as trigger on UPDATE/DELETE in my table. Log would be created in function and stored in auxiliary table. It is good solution or sooo simple and performance poor?
  2. Can you advice me any good solution for creating difference log? I mean something easy to look at by human (like in GIT) and easy to use by revert functionality.

My thoughts of last few minutes:

CREATE TABLE logs(
    operationID int NOT NULL UNIQUE,
    operationType text NOT NULL,
    applyData timestamp NOT NULL,
    comment text,
    userID int NOT NULL,
    logText NOT NULL,
    --etc.
);

CREATE TRIGGER logChanges
    AFTER UPDATE OR DELETE ON myAwesomeTable
    (for affectedRow) ROW EXECUTE EXECUTE PROCEDURE loggingProcedure();
Izzy
  • 755
  • 2
  • 9
  • 17
  • Calculating difference won't be easy/simple at all. If i'm not mistaken, git also stores complete objects, not differences -- You may be interested in the `spi` module's time travel functionality http://www.postgresql.org/docs/current/static/contrib-spi.html#AEN159579 – pozs Jan 26 '15 at 11:43
  • Have you considered just keeping the last version in Postgres and keeping the older versions in Git? Git is completely optimized for storing these fast and efficiently and offers a lot of options for displaying the data as well. – Wolph Jan 26 '15 at 11:45
  • @pozs Reverting one row at one time (expecially textFile field, because I can re-create rest) using GUI/command from Java application should be enought. That's why good diff 'files' could be enough for my present objectives. Of course if it is possible, mayby I will develop this, but I guess implementating big piece of git utilities exacly would be hard and take a loooot of time. Hmm... intresting functionality but if I undestood it will multiply data and store all columns/tupples in database, yes? (so it could be sad for my database after few mounts/years) – Izzy Jan 26 '15 at 12:02
  • @ Wolph Git as database with full API would be super for me. Yea, I considered it, but I need something like "branch" in my database and as I know it is impossible to create application working with git repository and possibility to search file by name in many branches (I know it may looks strange but few people needs to do it sometimes). I can easily implement this in database adding do my table 'branchnameorwhatever' column. – Izzy Jan 26 '15 at 12:08
  • @user3426003 it won't multiply *all* data, it only stores a row, whenever it changes, like git does with files. -- why do you need an rdbms for this task? it looks like a simple git could manage it more reliably. – pozs Jan 26 '15 at 12:09
  • Thanks. I have to read more about it, then :) For example I will need method like - File[] getAllUniqueFiles(string fileName), by File I mean long Text field, because file paths would be not sufficient for diff. Is effective searching in many branches available for any git implementation? – Izzy Jan 26 '15 at 12:49

0 Answers0