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	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	dpavlin	224	my $limit = 0; # no limit
23			#$limit = 1000;
24	dpavlin	221
25			my $bar = Term::ProgressBar->new({
26	dpavlin	224	count => 1000,
27	dpavlin	221	# 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	dpavlin	224	$sql .= qq{ limit $limit } if ($limit);
90
91	dpavlin	221	_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	dpavlin	223	my $xml = XMLout( $row, NoAttr => 1, RootName => 'file' );
104	dpavlin	221	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	dpavlin	223	_debug("final commit");
134	dpavlin	221	$txn->commit();
135