r8653@llin: dpavlin | 2005-10-21 00:07:36 +0200

 experiment with Berkeley DB XML

git-svn-id: svn+ssh://llin/home/dpavlin/private/svn/BackupPC/trunk@221 8392b6e1-25fa-0310-8288-cc32f8e212ea

diff --git a/dbxml/Makefile b/dbxml/Makefile
new file mode 100644 (file)
index 0000000..f27ba8e
--- /dev/null
+++ b/dbxml/Makefile
@@ -0,0 +1,13 @@
+
+dbxml=/rest/unix/database/dbxml-2.1.8/install/bin/dbxml
+opt=-v -v
+
+all:
+       $(dbxml) $(opt) -h dbxml -s total.q
+
+create:
+       rm -Rf dbxml/*
+       $(dbxml) $(opt) -h dbxml -s create.q
+
+convert:
+       ./convert_pgsql_xmldb.pl

diff --git a/dbxml/convert_pgsql_xmldb.pl b/dbxml/convert_pgsql_xmldb.pl
new file mode 100755 (executable)
index 0000000..fa859b9
--- /dev/null
+++ b/dbxml/convert_pgsql_xmldb.pl
@@ -0,0 +1,131 @@
+#!/usr/bin/perl -w
+
+# Dumps database structure into XML to fill SleepyCat's XMLDB
+
+
+use strict;
+use warnings;
+use Sleepycat::DbXml 'simple';
+use DBI;
+use XML::Simple;
+use Data::Dumper;
+use Term::ProgressBar;
+
+
+
+my $xmldb_path = './dbxml';    # -h option for tools
+my $xmldb_container = 'backuppc.dbxml';
+
+my $connect = "DBI:Pg:dbname=backuppc";
+
+my $commit_every = 100;
+
+my $bar = Term::ProgressBar->new({
+       count => 100,
+#      fh    => \*STDOUT,
+#      name  => 'thingy',
+       ETA     => 'linear',
+});
+
+sub _debug {
+       $bar->message(join(" ",@_));
+       $bar->update();
+       return;
+}
+
+_debug("connecting to $connect\n");
+my $dbh = DBI->connect($connect,"","") || die $DBI::errstr;
+_debug("connected");
+
+# open a container in the db environment
+my $env = new DbEnv(0);
+$env->set_cachesize(0, 64 * 1024, 1);
+
+$env->open($xmldb_path,
+            Db::DB_INIT_MPOOL|Db::DB_CREATE|Db::DB_INIT_LOCK|Db::DB_INIT_LOG|Db::DB_INIT_TXN);
+my $theMgr = new XmlManager($env);
+
+my $containerTxn = $theMgr->createTransaction();
+_debug("openContainer $xmldb_container\n");
+my $container = $theMgr->openContainer($containerTxn, $xmldb_container);
+_debug("commit");
+$containerTxn->commit();
+
+#  Get an XmlUpdateContext. Useful from a performance perspective.
+my $updateContext = $theMgr->createUpdateContext();
+
+# myDbEnv and myXmlContainer open with transactions. All subsequent
+# writes to them must also be performed inside a transaction.
+
+# Get a transaction
+my $txn = $theMgr->createTransaction();
+
+# Add the documents
+my $myXMLDoc = $theMgr->createDocument();
+
+my $sql = qq{
+select
+       files.id as unique_id,
+       hosts.name as host,
+       shares.name as share,
+       backupnum as num,
+       backups.date as backup_date,
+       backups.type as type,
+       files.path as path,
+       files.date as date,
+       files.size as size,
+       backups.size as backup_size
+from files
+join shares on files.shareid = shares.id
+join hosts on shares.hostid = hosts.id
+join backups on shares.hostid = backups.hostid
+       and files.backupnum = backups.num
+       and shares.id = backups.shareid
+order by backups.date
+};
+
+_debug("prepare");
+my $sth = $dbh->prepare($sql) || die $dbh->errstr();
+_debug("execute");
+$sth->execute() || die $sth->errstr();
+
+_debug( $sth->rows . ' rows returned');
+$bar->target( $sth->rows );
+my $i = 1;
+my $next_update = 0;
+
+while (my $row = $sth->fetchrow_hashref() ) {
+
+       my $xml = XMLout( $row );
+       if ($xml) {
+               # Set the XmlDocument to the relevant string and then put it 
+               #  into the container.
+               $myXMLDoc->setContent( "$xml" );
+               $container->putDocument($txn, $myXMLDoc, $updateContext, DbXml::DBXML_GEN_NAME);
+       } else {
+               _debug("skip $i: ".Dumper($row));
+       }
+
+       $i++;
+       $next_update = $bar->update( $i ) if ( $i > $next_update );
+
+       if ($i % $commit_every == 0) {
+               _debug("commit");
+               $txn->commit();
+               $txn = $theMgr->createTransaction();
+               $bar->message(Dumper($xml));
+       }
+
+}
+
+# Normally we would use a try/catch block to trap any exceptions.
+#  In the catch, we should call txn->abort() to avoid leaving the
+#  database in an indeterminate state in the event of an error.
+#  However, this simple example avoids error handling so as to 
+#  highlite basic concepts, so that step if omitted here as well.
+
+# Commit the writes. This causes the container write operations
+#   to be saved to the container.
+_debug("commit");
+$txn->commit();
+

diff --git a/dbxml/create.q b/dbxml/create.q
new file mode 100644 (file)
index 0000000..93d6009
--- /dev/null
+++ b/dbxml/create.q
@@ -0,0 +1 @@
+createContainer backuppc.dbxml n

diff --git a/dbxml/dbxml b/dbxml/dbxml
new file mode 120000 (symlink)
index 0000000..9b321fc
--- /dev/null
+++ b/dbxml/dbxml
@@ -0,0 +1 @@
+/x/dbxml/
\ No newline at end of file

diff --git a/dbxml/total.q b/dbxml/total.q
new file mode 100644 (file)
index 0000000..d7b6130
--- /dev/null
+++ b/dbxml/total.q
@@ -0,0 +1,9 @@
+
+setVerbose 2 2
+openContainer backuppc.dbxml
+
+
+queryPlan 'collection("backuppc.dbxml")/opt[host]'
+addIndex "" host node-element-presence-none
+queryPlan 'collection("backuppc.dbxml")/opt[host]'
+query 'collection("backuppc.dbxml")/opt[host]'