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
limit 1000
};

_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	dpavlin	221	#!/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	dpavlin	223	limit 1000
86	dpavlin	221	};
87
88			_debug("prepare");
89			my $sth = $dbh->prepare($sql) \|\| die $dbh->errstr();
90			_debug("execute");
91			$sth->execute() \|\| die $sth->errstr();
92
93			_debug( $sth->rows . ' rows returned');
94			$bar->target( $sth->rows );
95			my $i = 1;
96			my $next_update = 0;
97
98			while (my $row = $sth->fetchrow_hashref() ) {
99
100	dpavlin	223	my $xml = XMLout( $row, NoAttr => 1, RootName => 'file' );
101	dpavlin	221	if ($xml) {
102			# Set the XmlDocument to the relevant string and then put it
103			# into the container.
104			$myXMLDoc->setContent( "$xml" );
105			$container->putDocument($txn, $myXMLDoc, $updateContext, DbXml::DBXML_GEN_NAME);
106			} else {
107			_debug("skip $i: ".Dumper($row));
108			}
109
110			$i++;
111			$next_update = $bar->update( $i ) if ( $i > $next_update );
112
113			if ($i % $commit_every == 0) {
114			_debug("commit");
115			$txn->commit();
116			$txn = $theMgr->createTransaction();
117			$bar->message(Dumper($xml));
118			}
119
120			}
121
122			# Normally we would use a try/catch block to trap any exceptions.
123			# In the catch, we should call txn->abort() to avoid leaving the
124			# database in an indeterminate state in the event of an error.
125			# However, this simple example avoids error handling so as to
126			# highlite basic concepts, so that step if omitted here as well.
127
128			# Commit the writes. This causes the container write operations
129			# to be saved to the container.
130	dpavlin	223	_debug("final commit");
131	dpavlin	221	$txn->commit();
132