Re: Import scripts

[Jeff Kowalczyk <..hidden..>]

On Thu, 21 Aug 2008 11:59:43 +0200, Dave Coventry wrote:
> I'll insert one of the items and use pgadmin3 to see which columns are
> populated and then structure my query to suit.

To get a complete picture of how LedgerSMB updates the database, examine a
diff of the plaintext dump before and immediately after performing some
action in the UI:

$ pg_dump --format=p -c -U postgres mydatabase --file=before.sql

(do something in LedgerSMB)

$ pg_dump --format=p -c -U postgres mydatabase --file=after.sql

$ diff -u before.sql after.sql

Caveat: pg_dump COPY statement rows are not ordered, so there can be some
movement of unaltered rows. In practice, the diff is usually sensible and
informative.


Versioned Backups with DVCS
---------------------------

To streamline this process and also gain the benefit of compressed fully
versioned backups, put the dump file mydatabase.sql under (distributed)
version control.

I use git:

$ git init mydatabase

$ cd mydatabase

convenience script backup.sh: 
pg_dump --format=p -c -U postgres mydatabase --file=mydatabase.sql

$ ./backup.sh

$ git diff    # see your changes, initial commit will be large,
              # subsequent commits are diff-only

$ git status  # see which files have changed i.e. mydatabase.sql

$ git add mydatabase.sql  # add to the index, i.e. 'the next commit'

$ git commit -m 'applied so-and-so operation on invoice foo'

$ git log  # see history

$ git show HEAD # see a specific revision

$ git reset --hard MYSHA1  # roll back to a specific revision

convenience script restore.sh:
psql -U postgres -d mydatabase -f mydatabase.sql

$ ./restore.sh  # can now do-over in LedgerSMB from MYSHA1 point in time

And so on. The tig (console) or gitk (TCL gui) are a great convenience.

The availability of diffs before and after any operation in LedgerSMB
really helped me understand how the LedgerSMB-1.2 system operated, and
observe opportunites for automating bulk imports.

Going forward, this (attaching small diffs around specific operations)
might become a good way to communicate questions and bug behavior to the
developers.

Jeff