lannings at who
Jul 9, 2008, 7:11 AM
Post #1 of 2
(144 views)
Permalink
|
[note: moved from devel list to users list]
On Tue, 8 Jul 2008, Scott Lanning wrote:
> It doesn't account for the fact that users will
> naturally create the "same" asset in two different servers,
> so the assets will get different UUIDs, so bric_soap will
> complain when trying to update content from one server
> to another.
>
> It doesn't matter, since I'll work around it.
This is the workaround. (for Bric version 1.10)
#!/usr/bin/perl
# This syncs UUIDs from one server to another through the database.
#
# The problem concerned here is that users can create the "same" story
# or media on two different servers (e.g. prod and training),
# then we want to update them with bric_soap and it fails because
# the "same" asset has different UUIDs on the two systems.
#
# See the CONFIGURATION section before using this.
use strict;
use warnings;
use DBI;
################### CONFIGURATION ####################
my $URI_PATTERN = '%/any/uri/%';
# $FROM_DB and $TO_DB must match one of the keys to %DB
# $FROM_DB has the UUIDs that you want to sync from,
# while the UUIDs in $TO_DB will be updated with those from $FROM_DB.
my $FROM_DB = 'prod';
my $TO_DB = 'training';
my %DB = (
prod => {
name => 'bricolage',
host => 'db.example.com',
port => 5432,
user => 'high',
pass => 'pass',
},
training => {
name => 'training',
host => 'training.example.com',
port => 5432,
user => 'low',
pass => 'pass',
},
);
############### END CONFIGURATION ####################
main();
sub main {
my %maps = (
from => uri2uuid($FROM_DB, $URI_PATTERN),
to => uri2uuid($TO_DB, $URI_PATTERN),
);
update_uuids(\%maps);
}
sub update_uuids {
my ($maps) = @_;
my $dbh = db_connect($TO_DB);
foreach my $type (qw(story media)) {
my $sql = qq{UPDATE $type SET uuid = ? WHERE uuid = ?};
my $sth = $dbh->prepare_cached($sql);
URI: foreach my $from_uri (sort keys %{ $maps->{from}{$type} }) {
my $from_uuid = $maps->{from}{$type}{$from_uri};
if (exists $maps->{to}{$type}{$from_uri}) {
my $to_uuid = $maps->{to}{$type}{$from_uri};
next URI if $to_uuid eq $from_uuid;
print "$to_uuid -> $from_uuid\n"; # confusing, eh?
$sth->execute($from_uuid, $to_uuid);
}
}
$sth->finish();
}
$dbh->disconnect();
}
sub uri2uuid {
my ($label, $url) = @_;
my (%map);
my $dbh = db_connect($label);
# story
my $sql = qq{SELECT primary_uri, uuid FROM story WHERE active = 't' AND primary_uri like ?};
my $sth = $dbh->prepare($sql);
$sth->execute($url);
while (my ($uri, $uuid) = $sth->fetchrow_array()) {
$map{story}{$uri} = $uuid;
}
$sth->finish();
# media (I hope that's right, trying to get the URI for the current version)
$sql = qq{SELECT i.uri, m.uuid
FROM media m, media_instance i
WHERE m.id = i.media__id AND m.current_version = i.version
AND m.active = 't' AND i.uri like ?
ORDER BY m.id};
$sth = $dbh->prepare($sql);
$sth->execute($url);
while (my ($uri, $uuid) = $sth->fetchrow_array()) {
$map{media}{$uri} = $uuid;
}
$sth->finish();
$dbh->disconnect();
return \%map;
}
sub db_connect {
my ($label) = @_;
my $dbh = DBI->connect("dbi:Pg:dbname=$DB{$label}{name};host=$DB{$label}{host};port=$DB{$label}{port}",
$DB{$label}{user}, $DB{$label}{pass}) or die $DBI::errstr;
return $dbh;
}
|
