trunk/dbxml/convert_pgsql_xmldb.pl

#!/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();

1	#!/usr/bin/perl -w
2
3	# Dumps database structure into XML to fill SleepyCat's XMLDB
4
5
6	use strict;
7	use warnings;
8	use Sleepycat::DbXml 'simple';
9	use DBI;
10	use XML::Simple;
11	use Data::Dumper;
12	use Term::ProgressBar;
13
14
15
16	my $xmldb_path = './dbxml'; # -h option for tools
17	my $xmldb_container = 'backuppc.dbxml';
18
19	my $connect = "DBI:Pg:dbname=backuppc";
20
21	my $commit_every = 100;
22
23	my $bar = Term::ProgressBar->new({
24	count => 100,
25	# fh => \*STDOUT,
26	# name => 'thingy',
27	ETA => 'linear',
28	});
29
30	sub _debug {
31	$bar->message(join(" ",@_));
32	$bar->update();
33	return;
34	}
35
36	_debug("connecting to $connect\n");
37	my $dbh = DBI->connect($connect,"","") \|\| die $DBI::errstr;
38	_debug("connected");
39
40	# open a container in the db environment
41	my $env = new DbEnv(0);
42	$env->set_cachesize(0, 64 * 1024, 1);
43
44	$env->open($xmldb_path,
45	Db::DB_INIT_MPOOL\|Db::DB_CREATE\|Db::DB_INIT_LOCK\|Db::DB_INIT_LOG\|Db::DB_INIT_TXN);
46	my $theMgr = new XmlManager($env);
47
48	my $containerTxn = $theMgr->createTransaction();
49	_debug("openContainer $xmldb_container\n");
50	my $container = $theMgr->openContainer($containerTxn, $xmldb_container);
51	_debug("commit");
52	$containerTxn->commit();
53
54	# Get an XmlUpdateContext. Useful from a performance perspective.
55	my $updateContext = $theMgr->createUpdateContext();
56
57	# myDbEnv and myXmlContainer open with transactions. All subsequent
58	# writes to them must also be performed inside a transaction.
59
60	# Get a transaction
61	my $txn = $theMgr->createTransaction();
62
63	# Add the documents
64	my $myXMLDoc = $theMgr->createDocument();
65
66	my $sql = qq{
67	select
68	files.id as unique_id,
69	hosts.name as host,
70	shares.name as share,
71	backupnum as num,
72	backups.date as backup_date,
73	backups.type as type,
74	files.path as path,
75	files.date as date,
76	files.size as size,
77	backups.size as backup_size
78	from files
79	join shares on files.shareid = shares.id
80	join hosts on shares.hostid = hosts.id
81	join backups on shares.hostid = backups.hostid
82	and files.backupnum = backups.num
83	and shares.id = backups.shareid
84	order by backups.date
85	};
86
87	_debug("prepare");
88	my $sth = $dbh->prepare($sql) \|\| die $dbh->errstr();
89	_debug("execute");
90	$sth->execute() \|\| die $sth->errstr();
91
92	_debug( $sth->rows . ' rows returned');
93	$bar->target( $sth->rows );
94	my $i = 1;
95	my $next_update = 0;
96
97	while (my $row = $sth->fetchrow_hashref() ) {
98
99	my $xml = XMLout( $row );
100	if ($xml) {
101	# Set the XmlDocument to the relevant string and then put it
102	# into the container.
103	$myXMLDoc->setContent( "$xml" );
104	$container->putDocument($txn, $myXMLDoc, $updateContext, DbXml::DBXML_GEN_NAME);
105	} else {
106	_debug("skip $i: ".Dumper($row));
107	}
108
109	$i++;
110	$next_update = $bar->update( $i ) if ( $i > $next_update );
111
112	if ($i % $commit_every == 0) {
113	_debug("commit");
114	$txn->commit();
115	$txn = $theMgr->createTransaction();
116	$bar->message(Dumper($xml));
117	}
118
119	}
120
121	# Normally we would use a try/catch block to trap any exceptions.
122	# In the catch, we should call txn->abort() to avoid leaving the
123	# database in an indeterminate state in the event of an error.
124	# However, this simple example avoids error handling so as to
125	# highlite basic concepts, so that step if omitted here as well.
126
127	# Commit the writes. This causes the container write operations
128	# to be saved to the container.
129	_debug("commit");
130	$txn->commit();
131