1.  Syncing a Tree with an Sql Table

The *sqlsync utility uses trace and notify, to keep a tree in sync a database table. This maps a tree to all or part of a database table such that changes to the tree automatically reflect back into the database. eg.

  set t [*sqlsync new $db cust]
  $t update bob Age 31

Data is loaded using sqlload. Then the traces setup to ensure that all tree changes are driven back into the database. The newly created tree is returned.

Subsequently, any failure in a database update is promoted to a tree operation failure.

You can even view/edit the table using:

  *sqlsync new $db cust -view 1

This invokes *treeview allowing interactive editing of the table, with changes mirrored back to the database. The fascinating thing here is that TreeView uses traces to notify tree of changes, while tree uses additional traces to notify sqlite of changes.

There are of course limitations. Large tables (say over 100k rows) aren't so practical. Tree storage is best suited to applications that are read mostly, where updates trickle in. Only the one application can be changing data in the database. And care is needed with triggers. However, this probably describes most applications used by normal users.

The implication is clear. Applications can be written using tree, and then retrofitted to be database based backed, with essentially no changes.

2.  SqlSync and Arrays

Sqlsync extends sqlites usefulness in another way. It adds fields with arrays, eg.

  # UPDATE SET CLASS = "A 1 B 2" WHERE rowid == $id
  $t incr $id class(A) 4

Just think of the data compaction possibilities when all fields in a database can be arrays.

To do the equivalent in SQL would require:

  array set class [$db eval {SELECT class FROM mytbl where rowid=$id}]
  incr class(A)
  set val [array get A]
  $db eval {UPDATE SET class = $val WHERE rowid == $id}

With sqlsync, the update occurs automatically as valuse are modified. And sqlsync avoids the select part because data is already cached in the tree.

© 2008 Peter MacDonald