Posted: January 14 2004 at 15:33 | IP Logged

The number of macros, globals, X-10 house and unit codes etc. is starting to get unwieldy in my PH application. 

I would like to cross-reference all these items, i.e. what macros call x macro, what  macros are called by x macro, what macros use y global, what macros use X-10 HC A/UC 1, etc.

I could try to manage all this separately from PH in a text document but like all attempts at documentation I'm sure it will be out-of-date by day three.

I'm assuming if I brush up my SQL I could write appropriate reports to do this and so remain in the PH environment.

Is there a published list of PH's table names? table fields? etc.

Is such information too proprietary to share?

Does anyone have an alternative way to keep track of the interdependencies of these items?

All ideas are most welcome. <SCRIPT language=javascript>postamble();

Posted: January 14 2004 at 19:53 | IP Logged

Pat,

Funny, I have the same problem .

Yes, brush up on your SQL and you can do anything with the database.  The structure is published in the back of the User Manual in Appendix D.  You can run SQL scripts from within PowerHome under the Reports Menu.  You could also connect to PowerHome using Access or similar.  I dont believe its published in the manual, but the userid and password is ph/ph.

If you come up with any good scripts, etc. and would like to share, post them here and I might even be able to incorporate them into a future release.  Its something Ive been wanting to do but its been a low priority with everything else being added to PowerHome.

Good luck,

Dave.

Posted: January 15 2004 at 00:30 | IP Logged

  Appendix D??? Now that's embarrassing. Score another one for my wife about men not reading the directions.

Thanks, <SCRIPT language=javascript>postamble();

Posted: February 24 2004 at 12:05 | IP Logged

Dave,

I had a chance to get back to this lately.  I have a working model that seems to cover the basics but I will have to explore the more complex issues of where ids are embedded in PH functionality.

I do have some serious problems  that are probably my inexperience with SQL and PH internals.

First, because SQL doesn't permit a SELECT in the LIKE clause (which would have simplified things greatly) I have been forced to using a combination of SQL and PH functions to accompolish my cross-referencing. That's OK because I'm still able to meet the objective in keeping the solution completely within the PH environment but a purely SQL solution seems more elegant.

This led me to need a special table for my use. I have not been able to "create" a new table. Is this a "feature" or am I missing something.

In order to try out my model I shanghied the "equip" table.

Second, For the purposes of actually reporting the cross-referenced ids I am constrained by the size of the Message window. Is the window resizable?

General question: If I do a SELECT in the Direct SQL Window, are the results accessible?

Thanks,  <SCRIPT language=javascript>postamble();

Posted: February 24 2004 at 12:09 | IP Logged

 By-the-way, I have no idea why my Replys have this

<SCRIPT language=javascript>postamble();

in the closing. <SCRIPT language=javascript>postamble();

Posted: February 24 2004 at 13:44 | IP Logged

Pat,

Good to hear from you.

Yep, you can't use a 'SELECT' in a 'like' clause but you might be able to get by with what Sybase calls a "derived table" (I like to call them "inline views" the way Oracle does).  Basically what this allows you to do is make a SQL SELECT statement and then use that statement in the FROM clause instead of a table.  You could then use this "derived table" in your 'like' clause.  Ive posted a little snippet of SQL below that demonstrates this (its not very useful, just demonstrates the concept)

select * from (select id from macrodetail where id like 'D%') as temptable where id like 'DS%'

The runtime version of SQL anywhere does not allow you to "CREATE" tables.  Sadly, I wish this feature was available as it would make my job a heck of a lot easier when it comes to database upgrades .

PowerHome does provide a generic user table though for just this kind of work.  The table name is userdata1.  It has a type field and ID fields (char, number, date) and value fields (char, number, date).  It may help you.

The message window is not resizeable.  However, I could probably make a new type of window that includes a multi-line edit control which would be scrollable.  I'll put it on the to-do list and we'll have the next version (not the one thats been due out any day now, but the next).

A "SELECT" in the Direct SQL window is not accessible.  You could have the results of a "SELECT" used within an UPDATE, INSERT, or DELETE however.

Concerning the javascript at the end of your messages....I did some checking and it would appear to be a bug in the forum software with signatures.  I went ahead and deleted your sig (patc) and Im guessing that will take care of it .

Let me know how it goes and if I can answer any other questions.

Dave.

Posted: February 24 2004 at 15:30 | IP Logged

Whew! Thanks for the quick response.

I'll check in periodically to let you know how I'm doing.

Posted: March 04 2004 at 13:15 | IP Logged

I promised to check in periodically to let you know how this is going.

I got back to this again for a little while. The results so far are useful but I have several issues to resolve. I am able to provide a cross-reference of ids i.e. the id of a PH entity (global, macro, trigger, etc.) and where it is used, where in the PH entity it is located and what kind of line it is in (macro, formula, send key, etc.). That's the useful part.

1. I haven't been able to make an SQL statement that would do all of the selection I need. Dave's suggestion of the derived table or inline views might work but I either misapplied the concept or still don't understand it. Here's an example of what I want to do and while its illegal in SQL I think it illustrates the idea. 

select * from macrodetail where send_keys  like '%( select id from global_variables )%'

The intent is to get a row from macrodetail for every occurance of the global_variables id in the macrodetail send_keys. Note: this could be multiple hits per macrodetail row. I'm probably now beating a dead horse anyway because this selection isn't complete. For instance, it will select incorrectly when an id is actually a substring of another id. Bottom line: probably need to do the the same parsing that PH does when interpreting the formulas, send keys, etc.

2. OK, so I'm parsing to glean the "real" ids from the various PH tables by writing macros. This results in some really l...........ong processing times. This makes it impractical to do this on the fly when a user wants the xref results. I got around this (sort of) by doing all of the selection processing and storing the results in a table. Now when a request is made for a xref it can be done very fast. But ... the long build times for this table are in my mind prohibitive. Also, the table will be out of date as soon as a change is made to PH.

3. The userdata1 table didn't have enough numeric fields so I stole the ir table for the purpose of this experiment. This of course precludes the ability to use IR functionality.

4. The reporting of xref'd ids has to be done one location at a time because the message window is limited. that's not too bad but sometimes you like to see this sort of thing displayed as a list or table. Question: can data (other than macros) be exported from PH?

I will continue to work on this because it has been an great way to get familiar with PH programming. I am thinking about posting a request in Features to include another table that is updated using the existing PH logic whenever parsing is done. That's when PH knows all about ids. If it could maintain a table of the known ids with where they are used the long process time to build the table the way I'm doing it goes away. It would then be a simple matter for users to write macros to access the table.

Posted: March 04 2004 at 14:30 | IP Logged

Pat,

Glad to hear you're making some progress.  I think I can help out a little...to simulate the "select * from macrodetail where send_keys like '%(select id from global_variables)%'" you can do the following:

select * from macrodetail md,(select id from global_variables) gv where md.send_keys like '%' || gv.id || '%'

This will pull back a list of all macros that contain a global variable userid within the send keys field.  If you want to make a little more accurate so that you pull back all macro detail lines that are referencing a global variable, something like the below could be used:

select * from macrodetail md,(select id from global_variables) gv where md.send_keys like '%"' || gv.id || '"%' or md.send_keys like '%{' || gv.id || '}%' or (md.type = 10 and md.type_id = gv.id)

You'll see that Im matching up on GV in the {GV_ID} format as well as the "GV_ID" format.  Im also matching up on lines that are a "Set Global" command and the GV ID is in the "type_id" field.  This may not be all possibilities but probably has it fairly narrowed down

I'll also get to work on the scrolling Message Box macro command for the next version.

Dave.

Posted: March 04 2004 at 15:11 | IP Logged

Dave,

Thanks for the update on the SQL. I see that I was misplacing the apostrophe and quote in my statement. I'll give it another try.

Pat <SCRIPT language=javascript>postamble();

Posted: March 04 2004 at 15:49 | IP Logged

Dave,

your select works very well and its sooooooo much faster than what I've bee doing. Thanks for explaining it also.

I guess I'll dump the populated table idea and go back to cross referencing on the fly.

Pat <SCRIPT language=javascript>postamble();

Posted: March 08 2004 at 11:41 | IP Logged

Dave,

I know this is really basic but I haven't figured out how to include single and double quotes in a text string. I know that if I want single quotes that I can enclose the string in double quotes and vice versa. I need a string that contains both single and double quotes. I thought that some escape sequence like two or three quotes (or single quotes) embedded in the string would work but doesn't seem to.

The best I've managed is to create a string in a global using the global variable maintenance screen where I don't have to use enclosing quotes to build a string. Here the double and single quotes seem to be accepted as just characters in the string.

I have a need to build a string from within a formula or macro though. Is there an escape sequence that works? <SCRIPT language=javascript>postamble();

Posted: March 08 2004 at 17:01 | IP Logged

Pat,

You've got it right.  Strings can be delimited with either single or double quotes and can contain the single quotes if you delimited with double quotes or double quotes if delimited with single quotes.

There are a couple of ways to handle strings that need to contain both single and double quotes.  You can break the string into parts and add them together like so:

"This string contains a ' (single quote)" + ' and contains a " (double quote) as well'

You could do this as much as needed.  You can also use a ~ (tilde) as an escape character.  An example is:

"This string contains a ' (single quote) and contains a ~" (double quote) as well"

The only problem with the ~ escape character is that you may have to use more than one depending upon where the string is being evaluated and how deep it is within the formula.  I know this doesnt make any sense and it may require experimentation to get it just right.  In the above example, if the above did not work, you may have to use ~~".  On the first pass, the ~~ will evaluate to ~ and then the second pass will give you ~".

HTH,

Dave.