| Author |
|
device
Newbie
Joined: May 26 2009
Online Status: Offline
Posts: 33
|
| Posted: May 27 2009 at 18:39 | IP Logged
|
|
|
As mentioned in another posting I am new to VBScript, SQL, and Powerhome over the past couple of weeks. I wanted to create presence management logic which allows the notion of virtual device state (desired state), a real device device state and a method for deciding when to "unify" (make real state equal virtual state) based on presence. Also there I implemented an arbitrarily stackable (don't have to unstack in the same order as stacked) set of device state snapshots for functions like "Blink the lights where my daughter commonly hangs out plugged into her IPod so I don't have to search for her but return them to their previous state when done" or other transitory activities. I tried using SQL ALTER TABLE to add columns and CREATE TABLE to create tables but got rejected with security errors. I am currently using global variables as surrogates for additional columns though a naming convention but I'd like to manage presence using other groupings by location (e.g. by activity) so I'd really need tables without recreating more function than I want to do via globals. Anyone ever tried using ALTER or CREATE TABLE with Powerhome?
Thanks in advance,
D
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: May 28 2009 at 11:04 | IP Logged
|
|
|
D,
Unfortunately, the runtime version of ASA (Adaptive Server Anywhere) does not allow any schema modifications. This is usually not a problem in everyday use (not often does anyone want to modify the underlying PH tables  ). A bigger problem for me is that the runtime doesnt even allow any stored procedures or triggers...makes some of the development a bit more difficult. But its quick, relational, SQL, (and free) so works well.
I do have a single table within the database though called USERDATA1 which is structured for this type of purpose. This table is unused and in a format that it could be easily joined to other tables for storing a variety of additional information. I currently only have the single table but it would be easy to create other blank tables for specific needs.
Let me know if any of this works for you or not.
Dave.
|
| Back to Top |
|
| |
device
Newbie
Joined: May 26 2009
Online Status: Offline
Posts: 33
|
| Posted: May 28 2009 at 14:56 | IP Logged
|
|
|
I wasn't sure what USERDATA1 was used for. The little VBScript I wrote tells me the names of all the tables and columns but doesn't tell me the uses. For my particular application I'd prefer to have three such tables (at the moment). Personally, I like tables with a unique primary key which is a long and increments with each row insertion - essentially a row serial number. There are adavantages to normalizing table relationships through such abstractions rather than data and a single unique long creates a fast index. Of course, as a general template you can't tell the database how to enforce referential integrity on these tables.
Anyway design philosophy aside, I think it would be useful to have several such tables (say 10 like a lot of other things in Powerhome).
In terms of row definition - the USERDATA1 rows would be usable but it would be nice to have multiple columns in each row defined as long, char(25) and date to allow for more efficient queries. The non-queried data can be stuffed into the char(1024) as a BLOB.
I you decide to make the schema changes, I'd be glad to post my code as an example after I got it working.
Thanks,
D
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: June 02 2009 at 22:04 | IP Logged
|
|
|
D,
Not sure if you've found the details on the USERDATA1 table so I figured I'd document it here so others can benefit as well.
type integer (4 bytes)
idstring char(25)
idnum integer
iddate timestamp
valstring char(1024)
valnum double
valdate timestamp
2 indexes:
type, idstring, idnum, iddate (non-unique, ascending)
type, idstring, idnum, iddate, valstring, valnum, valdate (unique, ascending)
Nulls allowed in all columns. The thinking behind this structure was to use the idstring to store one of the various ID's of the other PowerHome tables with the type column being a numeric to represent the type of the data for this particular row and the idnum being an extra column to further differentiate the data. Of course, the val fields would be used to store whatever data you wanted to store.
I played around with the ASA runtime to find out what is allowed and isnt allowed and I can indeed create an autoincrement field of type integer (4 bytes).
So, Im thinking of a new USERDATA table with the following layout:
uid integer autoincrement (unique primary key)
type integer
idstring char(25)
idnum integer
iddate
valstring1 char(25)
valstring2 char(25)
valstring3 char(25)
valstring4 char(1024)
valnum1 long
valnum2 long
valnum3 long
valnum4 double
valdate1 timestamp
valdate2 timtstamp
valdate3 timestamp
valdate4 timestamp
lastchange timestamp (default value lastchange)
The lastchange field is similar to the autoincrement field so that with every insert or update, the timestamp is automatically updated with the current time. Just a nice to have.
Let me know what you think of this layout (and any others you think might be useful) and I'll add these new tables to the database for the next beta.
Thanks,
Dave.
|
| Back to Top |
|
| |
device
Newbie
Joined: May 26 2009
Online Status: Offline
Posts: 33
|
| Posted: June 03 2009 at 11:33 | IP Logged
|
|
|
Dave,
This looks like it would work fine for everything I am currently thinking about. I realize I may be the only oddball looking at custom tables and I appreciate your responsiveness. In terms of other layouts, some of the folks who are comfortable with SQL might have other suggestions - they may not have previously contemplated being able to define a new table to make a Powerhome application easier. From thinking about more general uses briefly, what comes to mind as a potential basic need that would still be a pain with this layout would be storing larger objects (bigger than 1024 bytes), I don't know if the 1024 byte limitation has been a issue for folks in real applications and the data could be chopped up but that would make storage and retrieval really painful. I can imagine an alternate layout in a separate table for folks would want to store objects which can be longer than 1024 bytes.
Thanks again,
D
|
| Back to Top |
|
| |
grif091
Super User
Joined: March 26 2008
Location: United States
Online Status: Offline
Posts: 1357
|
| Posted: June 03 2009 at 16:17 | IP Logged
|
|
|
Let me play devils advocate for a minute. Are we not losing sight of what PowerHome is. If an application requires a database, it should use a database. Is PowerHome going to be a full function alternate API to SQL or some other database that the next person would prefer over SQL. And the question is not limited to database. Dave is a great programmer and does his best to implement anything anyone asks for but is that really what PowerHome is meant to be?
__________________
Lee G
|
| Back to Top |
|
| |
device
Newbie
Joined: May 26 2009
Online Status: Offline
Posts: 33
|
| Posted: June 03 2009 at 21:40 | IP Logged
|
|
|
It is a valid point that Powerhome is not a database product and probably should not attempt to become one. But, I am not sure exactly what particular aspect of the discussion you are objecting to. Dave had left a table defined for use by users and we were, in effect, discussing how the format of that and possibly other "template" tables might be generalized to better serve the motivation caused him to define a table intended for user data in the first place. I can only respect Dave's choices about what he chooses to implement or not implement. Even if Dave decides not to make the discussed schema change to his database, this discussion got the existing user data table better documented for others who may like to store adjunct data inside PH.
D
|
| Back to Top |
|
| |
Active Topics 
Memberlist 
Search 
Help
Register 
Login
PowerHome Messageboard : PowerHome Programming
Topic: SQL ALTER TABLE or CREATE TABLE
