Active TopicsActive Topics  Display List of Forum MembersMemberlist  Search The ForumSearch  HelpHelp
  RegisterRegister  LoginLogin
PowerHome General
 PowerHome Messageboard : PowerHome General
Subject Topic: exporting and parsing the database Post ReplyPost New Topic
Author
Message << Prev Topic | Next Topic >>
jeffw_00
Super User
Super User


Joined: June 30 2007
Online Status: Offline
Posts: 935
Posted: November 14 2009 at 13:25 | IP Logged Quote jeffw_00
I would like to find a way to export the database so that it can be parsed by, say, a perl script. I want to use this to compare 2 databases and look for differences.

I had been exporting the database using this script...

insert into macroheader values ('DBEXPORT','DB Export',0,0,1);
insert into macrodetail values ('DBEXPORT',1,15,'[LOCAL2]',NULL,'"c:\jeff\insteon\db\exporteddb_" + string(today(),"yyyy-mm-dd")',0,'');
insert into macrodetail values ('DBEXPORT',2,15,'[LOCAL3]',NULL,'1',0,'');
insert into macrodetail values ('DBEXPORT',3,26,'',NULL,'LOOP',0,'');
insert into macrodetail values ('DBEXPORT',4,15,'[LOCAL1] ',NULL,'ph_regexdiff1(",",",",",macroheader,macrodetail,inst eondevices,timedevents,triggers,exit,",1, [LOCAL3],0,0,0)',0,'');
insert into macrodetail values ('DBEXPORT',5,16,'',NULL,'if("[LOCAL1]" = "exit",999,1)',0,'');
insert into macrodetail values ('DBEXPORT',6,38,'',0,'ph_sqlselect(1,"select * from [LOCAL1] order by 1,2") + ph_writefile("[LOCAL2].htm",0,ph_gethtmldata(1)) + ph_sqldestroy(1) + ph_addtovar(1,3,1)',0,'');
insert into macrodetail values ('DBEXPORT',7,27,'',NULL,'"LOOP"',0,'');

Which, to be honest, I'm not sure I understand. I think I got it from a thread that suggested that this data could be used to re-construct a corrupted database, so now when I do a backup I run this script in addition to generating a backup file, but now I'm wondering if the output file is useful for anything except viewing (which might have been the original purpose, not sure).

Anyway, if I try to convert this HTML to text, I get tables with multi-word headers and blank table entries, so it's impossible to parse a row because the number of tokens varies so widely.

I guess I'm wondering if there's a way to write the database out in something like CSV format, and also whether there's a way to export the entire database that will allow easy import (i.e., an ASCII backup).


Thanks!
/j

Edited by jeffw_00 - November 14 2009 at 13:29
Back to Top View jeffw_00's Profile Search for other posts by jeffw_00
 
jeffw_00
Super User
Super User


Joined: June 30 2007
Online Status: Offline
Posts: 935
Posted: December 05 2009 at 14:51 | IP Logged Quote jeffw_00
bump?
Back to Top View jeffw_00's Profile Search for other posts by jeffw_00
 
jeffw_00
Super User
Super User


Joined: June 30 2007
Online Status: Offline
Posts: 935
Posted: December 20 2009 at 17:27 | IP Logged Quote jeffw_00
aw, come on guys - it's a legitimate question 8-}
/j
Back to Top View jeffw_00's Profile Search for other posts by jeffw_00
 
kev21986
Senior Member
Senior Member
Avatar

Joined: April 04 2007
Location: United States
Online Status: Offline
Posts: 199
Posted: December 21 2009 at 12:17 | IP Logged Quote kev21986
This is an interesting question. There should be a way to
dump the database to sql. You could then parse the sql find
and fix the problem in the sql script. Then clear the
database and run the script. This is all theory though. No
idea how to go about doing it in PH.

I would be interested in a way to backup and restore the
database. There is a lot of time put into setup and it would
be really sad if it was all lost.

__________________
Kevin Smith
@respectTheCode
Back to Top View kev21986's Profile Search for other posts by kev21986 Visit kev21986's Homepage
 
grif091
Super User
Super User


Joined: March 26 2008
Location: United States
Online Status: Offline
Posts: 1357
Posted: December 21 2009 at 13:10 | IP Logged Quote grif091
Backing up the PH database is as easy as making a copy of pwrhome.db   All the macros, triggers, device definitions, link information are contained in that one file.

__________________
Lee G
Back to Top View grif091's Profile Search for other posts by grif091
 
BeachBum
Super User
Super User
Avatar

Joined: April 11 2007
Location: United States
Online Status: Offline
Posts: 1880
Posted: December 21 2009 at 13:46 | IP Logged Quote BeachBum
You also need to backup the INI file.

__________________
Pete - X10 Oldie
Back to Top View BeachBum's Profile Search for other posts by BeachBum
 
jeffw_00
Super User
Super User


Joined: June 30 2007
Online Status: Offline
Posts: 935
Posted: December 21 2009 at 13:50 | IP Logged Quote jeffw_00
Yup - I wrote a perl script that copies and renames both these files with a date extension. Then I wrote a macro to call the perl script. Then I created a control center button to call the macro. Push the button, get a backup.

However, when I code in my day job, I always like, when I'm done, to compare what I did with what I started with. There's no easy way to do this within PH. If there was a reasonable way to write out the database, a little perl could do a compare and DIFF the parts that changed...
/j
Back to Top View jeffw_00's Profile Search for other posts by jeffw_00
 
BeachBum
Super User
Super User
Avatar

Joined: April 11 2007
Location: United States
Online Status: Offline
Posts: 1880
Posted: December 21 2009 at 13:56 | IP Logged Quote BeachBum
I do a

PH_BACKUPDB ( "C:\POWERHOME\BACKUP\PH_CHC.DB", 1)

Then

ph_copyfile("C:\POWERHOME\BACKUP\PH_CHC.DB", "C:\POWERHOME\BACKUP\PH-CHC"   + " " + string (month (today())) + "-" + string (day (today()))+ " " + string (hour(today())) + string (minute(today())) + ".DB", 9)

This is in a macro that fire every night.


__________________
Pete - X10 Oldie
Back to Top View BeachBum's Profile Search for other posts by BeachBum
 
TonyNo
Moderator Group
Moderator Group
Avatar

Joined: December 05 2001
Location: United States
Online Status: Offline
Posts: 2889
Posted: December 21 2009 at 21:58 | IP Logged Quote TonyNo
I agree that an export option would be nice, but it would be HUGE! lol
Back to Top View TonyNo's Profile Search for other posts by TonyNo Visit TonyNo's Homepage
 
jeffw_00
Super User
Super User


Joined: June 30 2007
Online Status: Offline
Posts: 935
Posted: December 21 2009 at 22:10 | IP Logged Quote jeffw_00
huge = big file? or
huge = big effort?

I actually doubt the latter. I'd be willing to bet there's an SQL command that would do a nice job.
/j
Back to Top View jeffw_00's Profile Search for other posts by jeffw_00
 
jeffw_00
Super User
Super User


Joined: June 30 2007
Online Status: Offline
Posts: 935
Posted: December 21 2009 at 22:11 | IP Logged Quote jeffw_00
hey beachbum- why do it every night? I do it only after I make a change - am I missing something?
/j
Back to Top View jeffw_00's Profile Search for other posts by jeffw_00
 
BeachBum
Super User
Super User
Avatar

Joined: April 11 2007
Location: United States
Online Status: Offline
Posts: 1880
Posted: December 21 2009 at 23:09 | IP Logged Quote BeachBum
I make changes all the time as I am playing so I never know…

__________________
Pete - X10 Oldie
Back to Top View BeachBum's Profile Search for other posts by BeachBum
 
TonyNo
Moderator Group
Moderator Group
Avatar

Joined: December 05 2001
Location: United States
Online Status: Offline
Posts: 2889
Posted: December 22 2009 at 08:12 | IP Logged Quote TonyNo
jeffw_00 wrote:
huge = big file? or
huge = big effort?

Big file.
Back to Top View TonyNo's Profile Search for other posts by TonyNo Visit TonyNo's Homepage
 
jeffw_00
Super User
Super User


Joined: June 30 2007
Online Status: Offline
Posts: 935
Posted: December 22 2009 at 09:49 | IP Logged Quote jeffw_00
my PC has a TB drive, I think I can handle it 8-}
Back to Top View jeffw_00's Profile Search for other posts by jeffw_00
 
TonyNo
Moderator Group
Moderator Group
Avatar

Joined: December 05 2001
Location: United States
Online Status: Offline
Posts: 2889
Posted: December 22 2009 at 22:36 | IP Logged Quote TonyNo
Ya got me there.
Back to Top View TonyNo's Profile Search for other posts by TonyNo Visit TonyNo's Homepage
 
RichardL
Senior Member
Senior Member


Joined: December 29 2008
Location: United States
Online Status: Offline
Posts: 165
Posted: December 29 2009 at 12:09 | IP Logged Quote RichardL
Is there a problem if a backup is executed while the application is running and the database is open?

I shutdown PH, do the backup and restart PH...

Thanks.
Back to Top View RichardL's Profile Search for other posts by RichardL
 
BeachBum
Super User
Super User
Avatar

Joined: April 11 2007
Location: United States
Online Status: Offline
Posts: 1880
Posted: December 29 2009 at 13:09 | IP Logged Quote BeachBum
I do mine in the middle of the night from a macro. So the answer would be no.

__________________
Pete - X10 Oldie
Back to Top View BeachBum's Profile Search for other posts by BeachBum
 
hotcoffee76
Newbie
Newbie


Joined: December 04 2009
Location: Canada
Online Status: Offline
Posts: 12
Posted: December 30 2009 at 14:13 | IP Logged Quote hotcoffee76
There are lots of tools out there that can compare databases. I'm sure there is something for < $100. The cost may be worth it depending on how much time it will take to write the parsing and compare routines.
Back to Top View hotcoffee76's Profile Search for other posts by hotcoffee76
 

If you wish to post a reply to this topic you must first login
If you are not already registered you must first register

  Post ReplyPost New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum