DB maintenance with 'lib/too.l'

For example, I have a relation (rel nm (+Ref +String)) which I want to change to (rel nm (+Sn +Idx +String)). Is there an way of reindexing the relation?

Yes. In "lib/too.l" there is a function 'rebuild'. It takes a list of objects and a relation specification. The tricky thing is how to get the list of objects.

The easiest (and typical) case is that you can locate those objects via another relation. Let's say that you also have a numeric key on those objects (rel nr (+Key +String)):

   (rebuild (collect 'nr '+Cls) 'nm '+Cls)

In other cases, you may collect the relevant objects via Links from related objects.

Or, you can use the 'nm' property itself. Then you must first start with the old db model (+Ref +String), save a list of all objects:

   (out "saved" (println (collect 'nm '+Cls)))

Then start with the new model (+Sn +Idx +String) and load the list

   (rebuild (in "saved" (read)) 'nm '+Cls)



Also, is there a way of "garbage collect" the whole db file(s) including blobs to get rid of stale objects?

The function 'dbgc' does this (also in "lib/too.l").

It should not be called, however, while other processes or users are operating on the db.

We usually start it once per night in a cron job, which also does other things, like consistency checks and backups. A typical "crontab" line looks like this

   12 1 * * * ./p lib/http.l -'client "localhost" 8080 (pack "app/back.l?*PW=" (pw))' -bye

with "app/back.l" containing

   (unless (= *PW (pw))   # Check 'pw' to avoid illegal accesses
      (quit "back.l" *PW) )

   (out 2
      (prinl "+ Backup " (stamp))

      (tell 'bye 2)   # Terminate all other possibly running child processes

      (when (lock)   # Get an exclusive lock
         (quit "Can't lock DB" @) )

      (for (F . @) *Dbs   # Do a low-level db check
         (when (dbck F)
            (quit "DB Check" (cons F @)) ) )

      (call "sh" "-c" "tar cfz app$(date +%a).tgz db/app/ blob/app/")
      (call "sh" "-c" "cp -p app$(date +%a).tgz app$(date +%b%y).tgz")

      (when (dbgc)   # Garbage collection
         (println 'dbgc @) )

      (prinl "- Backup " (stamp)) )



If a nightly cronjob for garbage collection is not an option (perhaps because the server must be available 24/7), there is also a concurrent garbage collector in "lib/conDbgc.l". You can simply start it from your main program (e.g. in the 'go' function) as

   (load "lib/conDbgc.l")

and it will slowly collect garbage at a low rate (may take days or weeks for a large database).

I have one more question: I started with one db file and now thinking about spliting it into more files. How can I move some objects/relations to another db file?

Again, with "lib/too.l" ;-)

After changing the list in '*Dbs' (e.g. the 'dbs' call in "er.l"), you can use 'dbfMigrate' for that. I did that for all old customers some time ago.

If the old model uses a single db file, which is named like "db/xxx", you need to move it to "db/xxx/1", to fit for the new pattern.

Start the application in single user mode, e.g.

   $ pil xxx/main.l lib/too.l -'pool "db/xxx/" *Dbs' +

then call 'dbfMigrate' (with the same arguments as 'pool')

   : (dbfMigrate "db/xxx/" *Dbs)

I hope you made a backup of the database before ;-)

'dbfMigrate' can also be used at some later time, when you modify the db model again (e.g. change the block sizes of some db files, or move indices to other files).

https://picolisp.com/wiki/?dbmaintenance

19mar16    erik