[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