[mythtv-users] How to copy 'frontend' portion of mysql database

Ronald Frazier ron at ronfrazier.net
Fri Jan 8 13:26:07 UTC 2010


> SELECT @groupidoffset := max(profilegroupid)+1 FROM displayprofilegroups;
> INSERT INTO displayprofilegroups (name, hostname, profilegroupid)
>         SELECT name, "newhost", profilegroupid+ at groupidoffset, data
>         FROM displayprofilegroups WHERE host="oldhost";
>
> SELECT @idoffset  := max(profileid)+1 FROM displayprofiles;
> INSERT INTO displayprofiles(profilegroupid, profileid, value, data)
>         SELECT profilegroupid+ at groupidoffset, profileid+ at idoffset, value, data
>         FROM displayprofiles
>         WHERE profilegroupid IN
>     (SELECT profilegroupid FROM displayprofilegroups WHERE host="oldhost");
>
> Again I don't know anything about sql queries or internal mythtv
> database structures -- I'm just trying to build on your work...
>
> Is this right?

Mostly...you just missed a few fieldnames. host should be hostname,
and you can remove the data field from the first subquery. Do that and
it should work.

That said, the obsessive-compulsive DB developer side of me is
cringing at the artificially created gaps in the numbering space (they
don't really cause a problem unless you run this process quite a few
times...just annoy me). For instance, say your displayprofilegroups
table has records with profilegroups ranging from 1 to 10. The records
for the host you are trying to duplicate are numbered 3,5,8 (the other
7 records are for other hosts you aren't trying to duplicate). The
first time you run the copy, you will now have rows numbered
1,2,3,4,5,6,7,8,9,10,14,16,19. Run it again to create another host and
now your numbering is 1,2,3,4,5,6,7,8,9,10,14,16,19,23,25,28. So you
get the idea. Not a problem until you do it enough that you start
running out of numbers in the 32-bit space.

So, that obsessive-compulsive part of me would like a better way to do
it. The solution: temporary tables.

CREATE TEMPORARY TABLE GroupIDMap (oldid INT UNSIGNED , newid INT UNSIGNED);
SELECT @lastgroupid := max(profilegroupid) FROM displayprofilegroups;
INSERT INTO GroupIDMap(oldid, newid) SELECT DISTINCT profilegroupid,
@lastgroupid := @lastgroupid+1 FROM displayprofilegroups WHERE
hostname="<OLDHOSTNAME>";

CREATE TEMPORARY TABLE ProfileIDMap (oldid INT UNSIGNED, newid INT UNSIGNED);
SELECT @lastprofileid := max(profileid) FROM displayprofiles;

INSERT INTO ProfileIDMap(oldid, newid) SELECT DISTINCT profileid,
@lastprofileid := @lastprofileid+1 FROM displayprofiles WHERE
profilegroupid IN
    (SELECT oldid FROM GroupIDMap);

INSERT INTO displayprofilegroups (name, hostname, profilegroupid)
SELECT name, "<NEWHOSTNAME>", newid FROM displayprofilegroups INNER
JOIN GroupIDMap ON profilegroupid=oldid;

INSERT INTO displayprofiles(profilegroupid, profileid, value, data)
SELECT G.newid, P.newid, value, data
FROM displayprofiles INNER JOIN GroupIDMap G ON profilegroupid=G.oldid
INNER JOIN ProfileIDMap P ON profileid=P.oldid;


There....now I can rest happily knowing I've efficiently used the
numbering space :)


-- 
Ron


More information about the mythtv-users mailing list