Exporting Device Templates - ignoring sqlCVS for the moment

[alx9r]

I know this is digging up an old topic but I've been trying to use this feature to backup my TV and Receiver device templates in 0810b2 and I'm getting some errors and no apparent output file.

This may be an old thread, but I think it's still quite an important topic.  I think it's important to be able to import/export/share device templates without the permanence of sqlCVS.  To explain what I mean:

 - device template import/export would be for moving around experimental, untested, unproven, half-working, questionable quality device templates between installations
 - sqlCVS is for permanently storing and sharing well-tested, proven-working templates

Right now, I'm pretty sure this script is the closest thing to the former that exists.

I'm thinking of doing a fresh install and would love to backup these templates first.  Is this working for anyone else right now?

I did get the script working for one of my many templates, however, it didn't work for most.  I can't remember if in the end it was failing out on execution, or if it was garbling the ruby code -- although I have seen it do both of those things.

This script is exactly the right idea, cheers to everyone who has got it this far.  It just needs to be progressed to the point where it works reliably.

Alex

[phenigma]

I did some playing and a little php/html learning and have altered the script to the point where it outputs a file I can save.  This part was pretty easy.  I don't know if the output is correct as I am not very familiar with the database.  I am unable to import the resulting file back into the database as I am getting sql errors on insert statements.  I am looking into this and will post any progress I make.

J.

[phenigma]

I think this is an important abiltity to have and a presents a great learning opportunity to become more familiar with LinuxMCE, PHP, & MySQL.  Over the last couple days I've got the script more or less working to export and import basic user generated device templates.  I have tested this with a few IR A/V devices and I have also tried the PLCBUS template, programmed IR codes and ruby commands are exporting and importing .  The following is a list of some updates and issues:

Changes:
 * - fixed - quotes around field names are gone
 * - fixed - stores all rows from related tables rather than last row only
 * - fixed - updates DeviceTemplate FK's for new related table PK entries after inserts are done
 * - fixed - strings are escaped before inserting to db (ruby looks okay to me)
 * - added - preview insert/update statements before importing to DB
 * Known Issues:
 * - when previewing these are placeholder values PK_DeviceTemplate=-3, PK_InfraredGroup=-2
 * - does not set FK_Manufacturer, FK_DeviceCategory properly in related tables if new
 *   Manufacturer or DeviceCategory entries are inserted by the script
 * - does not export/import DHCPDevice PnpDetectionScript scripts - only DB entries
 * - does not import DTs marked 'old', use &forceExportType=new during export to be able to import
 * - no validation of data can create duplicate rows in tables when importing 'old' as 'new'
 * - I'm sure there's more...

Thanks posde and merkur2k I'm learning lots.  It has a ways to go to deal with all possibilities, and could be a little prettier, but it's in a state where it is worth doing some testing with.  I'd like to rename it at some point (posde?) as it no longer generates a PHP script.  DeviceTemplateRevolver (DTR) or DeviceTemplateXchanger (DTX)... or something.  I expect to tackle the issues mentioned above, feel free to add to the list if you find specific issues.  Backup your data before using.

Use:
 Exporting - the script will display the serialized data and ask you to confirm the export after which it will present the file download dialog.
 Importing - View or Import.  Default is View.  Choosing view will parse the selected file and display the generated insert/update statements without executing any of them.  Choosing Import will immediately execute the insert/update statements and display them at the same time.  Choose view the first time, verify the insert/update statements, hit the back button and choose Import to import the device.

Grab the updated script from: http://filebin.ca/mftsyp/DeviceTemplate2php.tar.gz

J.

[Marie.O]

phenigma,

could you attach the script to this thread, please.

[phenigma]

Sure.  Here's a slightly updated version exporting and import viewing work well.  I need to scrub my virtual machine and test some more imports with the updates.  Simple IR based AV devices seem to work on native databases.

Changes:
 * 2010-03-06 - Changes by phenigma phenigma@hotmail.com
 * - fixed updates all entries' FK's for new PK entries after initial inserts
 * - fixed string escaping using mysql function (utf friendly?)
 * - fixed tables gets a unique fakeID when previewing rather than all related tables=-3
 * Known Issues:
 * - does not export/import any files/scripts - only DB entries
 * - does not import DTs marked 'old', use &forceExportType=new during export to test importing
 * - exports redundant data with &forceExportType=new
 * - may create duplicate rows when importing 'old' DTs exported with &forceExportType=new
 * - I'm sure there's lots more...
 
As I learn more about the database/sqlCVS and php/mysql I'm finding more to implement and better ways of doing things... 

J.

[phenigma]

It has occurred to me that when inserting devicetemplates into a new system that records should be linked on psc_id, where it exists, not by PK_ as the PK_ could be different where the psc_id should be identical across systems.  The script is still maintaining links by PK_ which could create problems where a DT depends on a table row added by an sqlCVS update.  I'll change this functionality to work on psc_id where it exists.

I'm only testing on 0810 but can I assume that psc_id's are not reused and a psc_id for a device under 0710 will still describe the same device in 0810?

J.

[Marie.O]

The link between tables is ALWAYS by PK and FK.

[phenigma]

Hi posde, yes I realize that, the link is always by PK and FK.  So let me explain what I mean.  The PK for a related table row (eg. Manufacturer) could be different on the system it's being imported to than it was on the exporting system because of sqlCVS updates.  To get around this the psc_id could be used to find the proper PK on the import machine (if it's different).

Example (Follow me here):
1.User1 installs a fresh 0810 system and creates a DeviceTemplate and then exports it and gives it to User2.  This device template refers to a manufacturer that was recently added to sqlCVS.

2.User2's system is older than User1's and User2 has created a new manufacturer before doing an sqlCVS update.  The sqlCVS update adds the manufacturer that User1's exported template is referring to.  Both users now have the same Manufacturer in their databases but with different PK's for that Manufacturer but the same psc_id.  This is how sqlCVS works isn't it?

3.Importing the DT into a new system by original PKs will link the imported Device to the incorrect Manufacturer on User2's system. 

The script will need to check the psc_id of the manufacturer and use it to select the the import machine's PK for that Manufacturer to properly link rows on the import machine... 

J.

[Marie.O]

What needs to be remembered is: The device template exporter was never meant as a replacement for sqlCVS, but more of a last straw tool, when one has to re-install and hasn't had a chance to sqlCVS commit templates. I would not rely on the psc_id at all.

imho, the device template exporter should only be used locally, and not as a replacement for the central sqlCVS repo.

[phenigma]

Gotcha. 

J.

[golgoj4]

So whats the current disposition of this?

[phenigma]

I had done more work on it and had it working quite well and was moving my device templates around with ease from install to install.  Then I forgot to back it up one day and lost some of my recent work.  I'll have to dig it out again and see what state it's in.

J.

[golgoj4]

Sure.  Here's a slightly updated version exporting and import viewing work well.  I need to scrub my virtual machine and test some more imports with the updates.  Simple IR based AV devices seem to work on native databases.

Changes:
 * 2010-03-06 - Changes by phenigma phenigma@hotmail.com
 * - fixed updates all entries' FK's for new PK entries after initial inserts
 * - fixed string escaping using mysql function (utf friendly?)
 * - fixed tables gets a unique fakeID when previewing rather than all related tables=-3
 * Known Issues:
 * - does not export/import any files/scripts - only DB entries
 * - does not import DTs marked 'old', use &forceExportType=new during export to test importing
 * - exports redundant data with &forceExportType=new
 * - may create duplicate rows when importing 'old' DTs exported with &forceExportType=new
 * - I'm sure there's lots more...
 
As I learn more about the database/sqlCVS and php/mysql I'm finding more to implement and better ways of doing things... 

J.

Is this the most recent version?

Thanks to everyone who's worked on this btw, its a big deal when needing to recreate templates housewide.

-golgoj4

[phenigma]

That's likely the most recent I have.  I had done more work and fixed a few more things and cleaned up the code a bunch as I learned more but I havn't had a chance to see if I've got anything newer backed up anywhere.  I'll have to look.

The actual 'insert' code may be commented out in the posted version of the script to avoid accidentally screwing up a database, you may have to uncomment it at the end of the .php file to get it to actually insert records into your database.  When you are viewing the insert statements it will show negative numbers as place holders for new Primary Key IDs that will be created when the actual insert statements are run.

J.

[pw44]

I'm trying to export two templates i've created, but no luck.
The export shows errors and i cant find any file. http://www.pastebin.org/115322 shows it.
Question: how to export the templates and where are they stored, so i can backup them and submit to svn?
TIA