From: dpavlin <dpavlin@8392b6e1-25fa-0310-8288-cc32f8e212ea>
Date: Sat, 22 Oct 2005 15:40:38 +0000 (+0000)
Subject:  r8653@llin:  dpavlin | 2005-10-21 00:07:36 +0200
X-Git-Url: http://git.rot13.org/?p=BackupPC.git;a=commitdiff_plain;h=00b36d44b9970eba39bc2975cb5e4623a854c7c5

 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
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
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
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
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
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]'