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 $limit = 0;  # no limit
#$limit = 1000;

my $bar = Term::ProgressBar->new({
        count => 1000,
#       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
};

$sql .= qq{ limit $limit } if ($limit);

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