[mythtv-users] How to copy 'frontend' portion of mysql database
Jeffrey J. Kosowsky
mythtv at kosowsky.org
Thu Jan 7 17:46:15 UTC 2010
Ronald Frazier wrote at about 11:53:06 -0500 on Thursday, January 7, 2010:
> > That being said, it does seem like one would want to copy those over
> > too.
> > Why wouldn't the following work (adopting your earlier scripts and
> > assuming I understood the syntax)
> >
> > DELETE FROM settings WHERE hostname ='<NEWHOSTNAME>';
> > INSERT INTO settings(name, hostname, profilegroupid) SELECT name,
> > "<NEWHOSTNAME>", profilegroupid FROM settings WHERE hostname = "<OLDHOSTNAME>"
> >
> > Or is there a problem if you duplicate profilegroupids?
>
> Well, you can do that, but the result is that you'd have 2 systems
> sharing the same group id. Modify the profile on one system and it
> would change on the other. Of course, maybe that's desirable but it
> could also be problematic. I'm not sure if the frontend ever
> regenerates its list in such a way that a given id could later end up
> pointing to a different profile.
>
Probably desirable for me...
> That said, it's a trivial problem to solve in a custom script...I'm
> just not sure how to handle it with simple mysql queries. The only way
> I could think would be to query the max(id) in the groups table, store
> that in a mysql variable, and then use that in a calculation in the
> followup query. My MySQL variable skills are a bit rusty, but
> something like:
>
> SELECT @idoffset := max(id)+1 FROM Table1;
> INSERT INTO Table1(id, host, data) SELECT id+ at idoffset, "newhost",
> data FROM Table1 WHERE host="oldhost";
> INSERT INTO Table2(id, data) SELECT id+ at idoffset, data FROM Table2
> WHERE id IN (SELECT id FROM Table1 WHERE host="oldhost");
>
> Don't recall if that's exactly the correct syntax and would work.
>
I assume that here: Table1 is displayprofilegroups
Table2 is displayprofiles
and that instead of id/host/data, I would substitute in the actual
table column names. (just asking because I know almost nothing about
sql...)
But if so, there is another wrinkle in that displayprofiles also has a
profileid that is monotonically increasing, so code would need to use
two different offsets, presumably looking like the following if I am
understanding correctly:
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?
More information about the mythtv-users
mailing list