| Author |
|
jeffw_00
Super User
Joined: June 30 2007
Online Status: Offline
Posts: 929
|
| Posted: November 29 2007 at 20:25 | IP Logged
|
|
|
Ok - I have everything set up the way I want it now, except for one last change. So far, I have approx. 65 timed events, and (coincidentally) about 65 triggers, all calling about 80 macros, some nested 2 or 3 levels deep. But the structure is very elegant and logical.
Now (sigh) I realize that I want to pass whether a macro is called by a timed event, or a trigger, all the way down to the lowest level of macro. This appears to require tedious hand-recoding of many dozens of "macro" entries into "Raw formulas" in order to pass a parameter.
[One thing I miss about PH compared to regular coding - when I make regular coding changes to a problem, I can diff results to eyeball just my changes. I don't think PH can do this.]
Anyway, before I embark on this tedious, error-prone task (admittedly made of my own lack of foresight)...
Is there a better way?
(perhaps a global I can test?)
Thanks!
/j
Ps - scary as it sounds, I almost wish i could just write the database out to a text file. Then I could write a perl script to do the mods and let it suck the file back in. Point is, the mods would either -all- be wrong, or -all- be right... (and I could diff the results 8-})
Edited by jeffw_00 - November 29 2007 at 20:30
|
| Back to Top |
|
| |
BeachBum
Super User
Joined: April 11 2007
Location: United States
Online Status: Offline
Posts: 1880
|
| Posted: November 29 2007 at 21:09 | IP Logged
|
|
|
Have you tried "Reports"--"Database Where Used"
__________________
Pete - X10 Oldie
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: November 29 2007 at 21:24 | IP Logged
|
|
|
Jeff,
Sounds as if you've made ALOT of progress. Glad to hear it's working well so far.
If I read your post correctly, you're looking for an easy way to change your existing triggers and timed event action types from "Macro" to "Raw Formula" so you can use the ph_macroparm function to pass a parameter whether the macro is called via timed event or trigger. If so, then there is a very easy way to do this using the power of SQL.
The first thing I did was open the PowerHome Multi-Editor and switch to SQL mode using shift-F5. Since I cant remember every detail of the database, the first thing I did was type a SQL select statement to show me the raw data of the Timed Events table. I did this using the following SQL statement:
select * from timedevents
and pressing the Execute key (the little blue guy running on the toolbar). The Editor is able to give you a list of PowerHome and system tables. The above SQL statement gives me the complete data of the Timed Event table in a report that I can then export from File|Export into a variety of formats including Excel, CSV, Text, or SQL. The main reason I did this though was to see the raw values of the "Type" and "Action" fields. I quickly see that type=0 is for macros and type=2 is for raw formulas. Using these two pieces of information, I don't even need to export, do a search and replace, and then import...I can just write a quick SQL statement to do the update for me.
Before proceeding, I would probably make a backup of the database because it is VERY easy to completely screw up the database.
Now, to convert Timed Events that have an action type of Macro to an action type of Raw Formula and call a macro using ph_macroparm, just use the following SQL update statement:
update timedevents set type = 2,action = 'ph_macroparm("' || action || '",1,0,0,0,0)' where type = 0
You can see from the statement above, Im changing the type field to a value of 2 (raw formula) and the action field to a ph_macroparm function with the macro name. You may have to edit this slightly since Im passing a value of 1 in the [LOCAL1] variable with [LOCAL2] thru [LOCAL5] all being blank and this may not mesh with the actual formula you would be using. The last part of the statement says to ONLY update timed events whose type = 0 (macro). This statement is very important because without the *where* clause, I would update *every* line and thats not what we would want to do. After typing this line, just press the execute button and your timed events should be converted.
To update the triggers, I used the
select * from triggers
to determine the value of columns and actual column names. Lo and behold, it's identical to the timed events table. So, the SQL to update the triggers is just as easy:
update triggers set action_type = 2,action = 'ph_macroparm("' || action || '",1,0,0,0,0)' where action_type = 0
Just a slight difference in naming for the action type column.
Two simple SQL statements and you've updated your 65 triggers and 65 Timed Events.
Hope this helps,
Dave.
|
| Back to Top |
|
| |
jeffw_00
Super User
Joined: June 30 2007
Online Status: Offline
Posts: 929
|
| Posted: November 29 2007 at 21:46 | IP Logged
|
|
|
Thanks Dave - Yes, i've done a lot of configuring, and PH has solved some problems that have annoyed me for years.
I appreciate the quick SQL primer (perhaps you can point me to a resource to learn -just a little- more?).
I'm a little too burnt tonight to try your suggestions, but I think I get the idea. Could you check a couple of assumptions for me?
1) if I'm going to pass a parameter that's always "1" when it's passed from a trigger, and always "0" from a timed event, I think I don't need to touch the timed events because all the LOCALx parameters are passed as 0 by default in a regular Macro call.
2) How would you convert a macro call in a MACRO from type Macro to type Raw Formula macroparm passing "[LOCAL1]" as the first parameter? (I'm concerned I'm going to get the "s and 's wrong...)
I think I can deduce the rest from these.
Thanks!
/j
Edited by jeffw_00 - November 29 2007 at 22:16
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: November 29 2007 at 22:21 | IP Logged
|
|
|
Jeff,
I'll do a little research and see if I can find a good, basic SQL primer on the web.
1)Yes, if you're going to use a [LOCAL1] var of 0 to represent timed events, then you shouldnt have to change them. You could for consistency sake, but it's not necessary.
2)Below is a SQL that will change a standard macro command of "MACRO" to macro command of "FORMULA" that calls a function of ph_macroparm with [LOCAL1] in the first parameter. It's slightly more complicated than the above formulas, but still very easy.
update macrodetail set type = 38,send_keys = 'ph_macroparm("' || type_id || '",[LOCAL1],0,0,0,0)',type_id = '' where type = 1
Again, I first queried the database using select * from macrodetail so I knew what number values to use.
As long as you make a backup of your database before playing, you're pretty safe.
Let me know how it goes,
Dave.
|
| Back to Top |
|
| |
jeffw_00
Super User
Joined: June 30 2007
Online Status: Offline
Posts: 929
|
| Posted: November 30 2007 at 10:12 | IP Logged
|
|
|
ok - so now I have a weekend project 8-}
thanks
/j
|
| Back to Top |
|
| |
jeffw_00
Super User
Joined: June 30 2007
Online Status: Offline
Posts: 929
|
| Posted: November 30 2007 at 20:09 | IP Logged
|
|
|
Hi Dave (or anyone)...
Hi Dave - this all worked. Thanks.
You mentioned that I could export and then re-import? Is it easy to tell me how this is done? Much as I'd like to learn enough SQL myself (so I don't have to bug you), it's awfully tempting to just write quickie perl scripts to do any massive changes (as I'm way more comfortable in perl).
Thanks on both counts.
/j
Edited by jeffw_00 - November 30 2007 at 21:55
|
| Back to Top |
|
| |
jeffw_00
Super User
Joined: June 30 2007
Online Status: Offline
Posts: 929
|
| Posted: November 30 2007 at 23:39 | IP Logged
|
|
|
dhoward wrote:
Jeff,
1)Yes, if you're going to use a [LOCAL1] var of 0 to represent timed events, then you shouldnt have to change them. You could for consistency sake, but it's not necessary.
Dave.
|
|
|
Hey Dave - It appears you may be incorrect. the macros in their original form (Type=macro) all FAILED until I converted them to ph_macroparm("<macro>",0,0,0,0,0).
now - perhaps ph_macro("<macro>") would have worked but I didn't try.
Or perhaps I'm missing something that's giving me both problems. How do we generate 'default' values for [LOCALn]?
/j
Edited by jeffw_00 - November 30 2007 at 23:39
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: December 01 2007 at 15:48 | IP Logged
|
|
|
Jeff,
I was misinformed, partially (can you believe it  ). Variables all default to an empty string. So if you use variable substitution such as [LOCAL1], then [LOCAL1] will be substituted to an empty string. If, however, you use the function ph_getvar_n(1,1) to reference LOCAL1, then it will default to 0. Variable substitution will substitute the actual value currently contained in the variable. The ph_getvar??? functions will *interpret* the currently stored value and will return an appropriate value for the function. In the case of a ph_getvar_n function, an empty string value will be converted to a 0. Sooo, if you reference the LOCAL1 variable within your macro as a variable substitution ([LOCAL1]) then you'll get an empty string. If you reference using ph_getvar_n(1,1), then you'll get a 0.
Concerning export/import...SQL is easier, but you can do it via export/import if you like. Say you want to work with the Macro Detail table. The first thing you'll need to do is generate the export. In the Multi-Editor, type the following SQL select:
select * from macrodetail
and execute it. When the resulting report is displayed, go to the File menu and select Export. You'll need to export as "SQL". Make sure that you name the file macrodetail.sql (not my requirement...it's the development environment). Once the file is saved, go ahead and open it in notepad. You'll see at the very beginning, a create table statement. You'll want to just delete this statement since the table is already created and leaving it there will cause an error. The rest of the file consists of all of the rows of the macro detail table as SQL insert statements. The reason why you had to name the file macrodetail.sql (the actual name of the table) is because the name of the file determines the name of the table that are in the insert statements. This is the file that you will have to try and edit using Perl.
Once edited, you'll need to reinsert the data. Since it already exists in the table, you'll get an error if you attempt to do this. So the first thing you'll have to do is delete the data in the table. From the Multi-Editor, type the following SQL:
delete from macrodetail
and execute. Your macrodetail table will now be completely empty (careful, a mistype could destroy your database). Once the macrodetail table is empty, you're ready to reimport your edited macrodetail.sql file. Once again from the Multi-Editor, just directly open the macrodetail.sql file or you can copy and paste the contents of it directly into the Editor. Execute it, and if there are no errors, you should have your macrodetail table back.
One other thing, check the very end of the macrodetail.sql file. If there is a commit statement at the end, go ahead and delete that as well since the multi-editor commit's automatically.
HTH,
Dave.
|
| Back to Top |
|
| |
jeffw_00
Super User
Joined: June 30 2007
Online Status: Offline
Posts: 929
|
| Posted: December 01 2007 at 16:26 | IP Logged
|
|
|
so what you're saying, I think, is that the file that I expert is really a "script" (or list of commands) that I can edit and then "import" by executing them in the Multi-editor?
cool!
/j
Edited by jeffw_00 - December 01 2007 at 16:27
|
| Back to Top |
|
| |
jeffw_00
Super User
Joined: June 30 2007
Online Status: Offline
Posts: 929
|
| Posted: December 01 2007 at 17:36 | IP Logged
|
|
|
It actually worked - thanks! (I edited the notepad file with simple Find/Replace).
8-}
/j
|
| Back to Top |
|
| |
|
|
Active Topics 
Memberlist 
Search 
Help
Register 
Login
PowerHome Messageboard : PowerHome General
Topic: There has to be a better way...
