Database

[CloneNum3]

Can someone tell me where and which database file the aprontest -l is polling from?

aprontest uses the database as the backend.

Side note, the following resets a hub back to factory...

Code: Select all

aprontest --zwave_controller_reset
killall -9 monit hub aprond ZigBeeHACoord lutron-core
rm /database/apron.db
rm /database/lutron-db.sqlite

then pull the plug, or reboot, or whatever you want, you're done.

What is the program used to view Sqllite databases?

As for connecting to the database...

Code: Select all

[root@flex-dvt ~]# cd /database
[root@flex-dvt database]# sqlite3 apron.db
SQLite version 3.8.4.1 2014-03-11 15:27:36
Enter ".help" for usage hints.
sqlite>

Showing tables

Code: Select all

sqlite> .tables
lutronAttribute     zigbeeCluster       zigbeeGroupState    zwaveGroupMembers
lutronDevice        zigbeeDevice        zwaveAttribute      zwaveGroups       
lutronDeviceState   zigbeeDeviceState   zwaveCCSupport      zwaveProfiles     
masterDevice        zigbeeDeviceType    zwaveCmdClass     
versionHistory      zigbeeGroup         zwaveDevice       
zigbeeAttribute     zigbeeGroupMembers  zwaveDeviceState 
sqlite>

Listing zigbee devices devices

Code: Select all

sqlite> select * from zigbeeDevice;
1|8999639012788821485|39204|1|257|0|4316|52797
sqlite>

List all available attributes

Code: Select all

sqlite> select * from zigbeeAttribute;
6|1|On_Off|ATTRIBUTE|STRING|R/W
8|2|Level|ATTRIBUTE|UINT8|R/W
4|4|NameSupport|ATTRIBUTE|UINT8|R
257|5|LockState|ATTRIBUTE|UINT8|R/W
257|6|LockType|ATTRIBUTE|UINT8|R
257|7|ActuatorEnabled|ATTRIBUTE|BOOL|R
257|8|DoorState|ATTRIBUTE|UINT8|R
257|9|DoorOpenEvents|ATTRIBUTE|UINT8|R
257|10|DoorClosedEvents|ATTRIBUTE|UINT8|R
257|11|OpenPeriod|ATTRIBUTE|UINT8|R
257|12|LockDoor|COMMAND|UINT8|W
257|13|UnlockDoor|COMMAND|UINT8|W
0|61440|ZCLVersion|ATTRIBUTE|UINT8|R
0|61441|ApplicationVersion|ATTRIBUTE|UINT8|R
0|61442|StackVersion|ATTRIBUTE|UINT8|R
0|61443|HWVersion|ATTRIBUTE|UINT8|R
0|61444|ManufacturerName|ATTRIBUTE|STRING|R
0|61445|ModelIdentifier|ATTRIBUTE|STRING|R
0|61446|DateCode|ATTRIBUTE|STRING|R
0|61447|PowerSource|ATTRIBUTE|UINT8|R
1|127008|BatteryVoltage|ATTRIBUTE|UINT8|R
1|127029|BatteryAlarmMask|ATTRIBUTE|UINT8|R/W
1|127030|BatteryVoltageMinThreshold|ATTRIBUTE|UINT8|R/W
1|127031|BatteryVoltageThreshold1|ATTRIBUTE|UINT8|R/W
1|127032|BatteryVoltageThreshold2|ATTRIBUTE|UINT8|R/W
1|127033|BatteryVoltageThreshold3|ATTRIBUTE|UINT8|R/W
25|1699842|ZB_CurrentFileVersion|ATTRIBUTE|UINT32|R
513|33681408|ZB_LocalTemperature|ATTRIBUTE|FLOAT|R
513|33681411|ZB_AbsMinHeatSetpointLimit|ATTRIBUTE|FLOAT|R
513|33681412|ZB_AbsMaxHeatSetpointLimit|ATTRIBUTE|FLOAT|R
513|33681413|ZB_AbsMinCoolSetpointLimit|ATTRIBUTE|FLOAT|R
513|33681414|ZB_AbsMaxCoolSetpointLimit|ATTRIBUTE|FLOAT|R
513|33681417|ZB_HVACSystemConfiguration|ATTRIBUTE|UINT8|R/W
513|33681425|ZB_OccupiedCoolingSetpoint|ATTRIBUTE|FLOAT|R/W
513|33681426|ZB_OccupiedHeatingSetpoint|ATTRIBUTE|FLOAT|R/W
513|33681433|ZB_MinSetpointDeadBand|ATTRIBUTE|INT8|R/W
513|33681435|ZB_ControlSequenceOfOperation|ATTRIBUTE|UINT8|R/W
513|33681436|ZB_SystemMode|ATTRIBUTE|UINT8|R/W
513|33681438|ZB_ThermostatRunningMode|ATTRIBUTE|UINT8|R
513|33681480|ZB_RevValveActiveCool|ATTRIBUTE|BOOL|R/W
513|33681481|ZB_OverrideTemperature|COMMAND|FLOAT|R/W
513|33681482|ZB_OverrideTemperatureTimeout|ATTRIBUTE|UINT16|R/W
1029|67497984|ZB_RelativeHumidityMeasuredValue|ATTRIBUTE|FLOAT|R
1029|67497985|ZB_RelativeHumidityMinMeasuredValue|ATTRIBUTE|FLOAT|R
1029|67497986|ZB_RelativeHumidityMaxMeasuredValue|ATTRIBUTE|FLOAT|R
1280|83947520|ZoneState|ATTRIBUTE|UINT8|R
1280|83947521|ZoneType|ATTRIBUTE|UINT16|R
1280|83947522|ZoneStatus|ATTRIBUTE|UINT16|R
1280|83947536|IasCieAddress|ATTRIBUTE|UINT64|R
1280|83947537|ZoneID|ATTRIBUTE|UINT8|R
1794|117633024|ZB_CurrentSummationDelivered|ATTRIBUTE|UINT64|R
1794|117633536|ZB_Status|ATTRIBUTE|UINT8|R
1794|117633792|ZB_UnitOfMeasure|ATTRIBUTE|UINT8|R
1794|117633793|ZB_Multiplier|ATTRIBUTE|UINT32|R
1794|117633794|ZB_Divisor|ATTRIBUTE|UINT32|R
1794|117633795|ZB_SummationFormatting|ATTRIBUTE|UINT8|R
1794|117633798|ZB_MeteringDeviceType|ATTRIBUTE|UINT8|R
64512|4227919872|ZB_AccelerometerEnable|ATTRIBUTE|BOOL|R/W
64512|4227919873|ZB_SetpointIncrementValue|ATTRIBUTE|UINT8|R/W
64512|4227919874|ZB_XAxisThreshold|ATTRIBUTE|UINT8|R/W
64512|4227919875|ZB_YAxisThreshold|ATTRIBUTE|UINT8|R/W
64512|4227919876|ZB_AcclerometerFactoryReset|ATTRIBUTE|BOOL|R/W
64512|4227919877|ZB_NetworkReportAddress|ATTRIBUTE|UINT16|R/W
64544|4230017024|Mode|ATTRIBUTE|UINT8|R/W
64544|4230017025|LED Fast Flash Rate|ATTRIBUTE|UINT8|R/W
64544|4230017026|LED Slow Flash Rate|ATTRIBUTE|UINT8|R/W
64544|4230017027|LED Error Flash Rate|ATTRIBUTE|UINT8|R/W

List zigbee device groups

Code: Select all

sqlite> select * from zigbeegroup;
1|nameofgroup

And a big one... what's the schema aka entire layout of the database?

Code: Select all

sqlite> .schema
CREATE TABLE masterDevice(
       deviceId INTEGER PRIMARY KEY,
       interconnect VARCHAR(32), -- One of 'ZWAVE', 'ZIGBEE', 'LUTRON'
       userName VARCHAR(256)     -- User-defined identifiers, such as "Kitchen Dimmer" or "Foyer Light"
);
CREATE TABLE zwaveCmdClass(
       cmdClass INTEGER PRIMARY KEY,   -- Corresponds to z-wave class ID
       className VARCHAR(256)          -- Name of the command class (ex. COMMAND_CLASS_BASIC, COMMAND_CLASS_ALARM )
);
CREATE TABLE zwaveDevice(
       masterId INTEGER,
       nodeId   INTEGER,
       endpoint INTEGER,                -- Multichannel devices will have a positive endpoint, otherwise 0.
       multiParent INTEGER,             -- 0 == normal node; 1 == multichannel parent with no masterId
       secure   INTEGER,                -- 0 == non-secure device; 1 == support COMMAND_CLASS_SECURITY
       crc16    INTEGER,                -- Boolean (0 or 1): Support CRC16 encap?
       sleeper  INTEGER,                -- Boolean (0 or 1): Supports COMMAND_CLASS_WAKE_UP?
       basicType INTEGER,               -- Zwave basic device type.
       genericType INTEGER,             -- Zwave generic type.
       specType INTEGER,                -- Zwave Specific type.
       manufacturerNum INTEGER,         -- Zwave MSR Manufacturer ID
       productType INTEGER,             -- Zwave MSR Product Type
       productNum INTEGER,              -- Zwave MSR Product ID
       versionLibraryType INTEGER,      -- Zwave Version: Zwave Library Type (see ZW_basis_api.h; ZW_LIB_*)
       versionProtoMajor INTEGER,       -- Zwave Version: Zwave Protocol Major
       versionProtoMinor INTEGER,       -- Zwave Version: Zwave Protocol Minor
       versionAppMajor INTEGER,         -- Zwave Version: Application Major
       versionAppMinor INTEGER,         -- Zwave Version: Application Minor
       PRIMARY KEY (nodeId, endpoint),
       FOREIGN KEY (masterId) REFERENCES masterDevice (deviceId )
);
CREATE TABLE zwaveAttribute(
       cmdClass INTEGER, 
       attributeId INTEGER PRIMARY KEY,
       attr_cmd VARCHAR(10) NOT NULL,    -- Denotes this entry as either an "ATTRIBUTE" or "COMMAND"
       description VARCHAR(256),         -- Attribute Descriptor
       dataType    VARCHAR(32),          /* Zwave Data type. In zwave this is usually overly broad,
                                          * such as "BYTE", or "CONST". We may be able to get away
                                          * with inserting our own meaningful values like "bool"
                                          * or "int", but this will need to be implemented and
                                          * maintained manually. */
       read_write  VARCHAR(3),           -- Either "R", "W", or "R/W" to denote read/write capabilities.
       commandKey  INTEGER NOT NULL,     -- This is the identifier that zwave uses to identify a specific command within a command class.
       FOREIGN KEY (cmdClass) REFERENCES zwaveCmdClass( cmdClass )
);
CREATE TABLE zwaveDeviceState(
       nodeId   INTEGER,
       endpoint INTEGER,
       attributeId INTEGER,
       value_get VARCHAR(256), -- value_get and value_set will need to be a string value that can be converted to
       value_set VARCHAR(256), setValueChangedFlag BOOLEAN NOT NULL DEFAULT FALSE, -- integers and whatnot as needed.
       FOREIGN KEY (nodeId,endpoint) REFERENCES zwaveDevice( nodeId, endpoint ),
       FOREIGN KEY (attributeId) REFERENCES zwaveAttribute(attributeId)
);
CREATE TABLE zwaveCCSupport(
        cmdClass    INTEGER,
        genericType INTEGER,
        specType    INTEGER,
        profileId   INTEGER,
        description VARCHAR(128),
        FOREIGN KEY (cmdClass) REFERENCES zwaveCmdClass (cmdClass)
        );
CREATE TABLE zwaveProfiles(
        cmdClass    INTEGER,
        profileId   INTEGER,
        attributeId INTEGER,
        FOREIGN KEY (attributeId) REFERENCES zwaveAttribute (attributeId)
        );
CREATE TABLE zwaveGroups(
       GroupId      INTEGER PRIMARY KEY,
       GroupName    VARCHAR(64)
       );
CREATE TABLE zwaveGroupMembers(
       GroupId     INTEGER,
       NodeId      INTEGER,
       PRIMARY KEY ( GroupId, NodeId ),      /* Enforces only one DB entry per GroupId/NodeId pair */
       FOREIGN KEY (GroupID) REFERENCES zwaveGroups (GroupId)
       );
CREATE TABLE zigbeeCluster(
       clusterId INTEGER PRIMARY KEY,   -- Corresponds to a cluster (a set of commands/attributes)
       clusterName VARCHAR(256)         -- Name of the cluster (e.g. OnOffCluster, DoorLockCluster )
);
CREATE TABLE zigbeeAttribute(
       clusterId INTEGER, 
       attributeId INTEGER PRIMARY KEY,
       description VARCHAR(256),       -- Attribute Descriptor
       attr_cmd VARCHAR(10) NOT NULL,    -- Denotes this entry as either an "ATTRIBUTE" or "COMMAND"
       dataType VARCHAR(32),             /* Zigbee Data type. This is usually overly broad,
                                          * such as "BYTE", or "CONST". We may be able to get away
                                          * with inserting our own meaningful values like "bool"
                                          * or "int", but this will need to be implemented and
                                          * maintained manually. */
       read_write  VARCHAR(3),           -- Either "R", "W", or "R/W" to denote read/write capabilities.
       FOREIGN KEY (clusterId) REFERENCES zigbeeCluster(clusterId)
);
CREATE TABLE zigbeeDevice(
       masterId   INTEGER,               -- abstract device ID
       globalId   INTEGER,               -- globally unique ID/MAC address/IEEEaddress
       networkId  INTEGER,               -- network address
       endpointId INTEGER,               -- Unique per globalId. 
       deviceType INTEGER,               -- Zigbee device type for this endpoint. 
       isSleepy   INTEGER, manufacturerCode INTEGER NOT NULL DEFAULT 0, productId INTEGER NOT NULL DEFAULT 0,               -- Is this a sleepy device (impacts timeouts, message devliery, etc) [0, 1] 
       FOREIGN KEY (masterId) REFERENCES masterDevice(deviceId)
);
CREATE TABLE zigbeeDeviceType(
        deviceType  INTEGER PRIMARY KEY,
        deviceDesc  STRING
);
CREATE TABLE zigbeeDeviceState(
       globalId    INTEGER,      -- Zigbee long address
       endpointId  INTEGER,      -- Endpoint ID for this logical device
       clusterId   INTEGER,      -- Cluster ID supported on this endpoint
       attributeId INTEGER,      -- Attribute ID reference
       value_get   VARCHAR(256), -- value_get and value_set will need to be a string value that can be converted to
       value_set   VARCHAR(256), setValueChangedFlag BOOLEAN NOT NULL DEFAULT FALSE, -- integers and whatnot as needed.
       FOREIGN KEY (globalId) REFERENCES zigbeeDevice(globalId),
       FOREIGN KEY (endpointId) REFERENCES zigbeeDevice(endpointId),
       FOREIGN KEY (clusterId) REFERENCES zigbeeCluster(clusterId),
       FOREIGN KEY (attributeId) REFERENCES zigbeeAttribute(attributeId)
);
CREATE TABLE lutronDevice(
       masterId INTEGER,
       lNodeId  INTEGER PRIMARY KEY,
/*      lutronType VARCHAR(32) NOT NULL, */
       deviceClass INTEGER,   -- Lutron DeviceClass is of type uint32_t
       objectId INTEGER,      -- Object ID used for GoToLevel commands
       modelName VARCHAR(64), -- String matching the DeviceClass.  From the Lutron DB
       FOREIGN KEY (masterId) REFERENCES masterDevice( deviceId )
);
CREATE TABLE lutronAttribute(
       attributeId INTEGER PRIMARY KEY,
       attr_cmd VARCHAR(10) NOT NULL,    -- Denotes this entry as either an "ATTRIBUTE" or "COMMAND"
       description VARCHAR(256),         -- Attribute Descriptor
       dataType    VARCHAR(32),
       read_write  VARCHAR(3)
);
CREATE TABLE lutronDeviceState(
       lNodeId INTEGER,
       attributeId INTEGER,
       value_get INTEGER,
       value_set INTEGER,
       FOREIGN KEY (attributeId) REFERENCES lutronAttribute( attributeId )
);
CREATE TABLE versionHistory(
    version INTEGER
);
CREATE TABLE zigbeeGroup(
       groupId INTEGER PRIMARY KEY,   
       groupName VARCHAR(16)         
);
CREATE TABLE zigbeeGroupMembers(
       groupId     INTEGER,
       nodeId      INTEGER,
       PRIMARY KEY ( groupId, nodeId ),     
       FOREIGN KEY (groupId) REFERENCES zigbeeGroup (groupId)
       );
CREATE TABLE zigbeeGroupState(
       groupId     INTEGER,     
       clusterId   INTEGER,     
       attributeId INTEGER,     
       value_get   VARCHAR(256),
       value_set   VARCHAR(256),
       FOREIGN KEY (groupId) REFERENCES zigbeeGroup(groupId),
       FOREIGN KEY (attributeId) REFERENCES zigbeeAttribute(attributeId),
       FOREIGN KEY (clusterId) REFERENCES zigbeeCluster(clusterId)
);
sqlite>

[Middyeasy]

Very interesting and a bit disappointing as I have a zigbee PEQ motion sensor coming Monday and from the list of zigbee attributes I don't see presence listed. unless it uses on or off instead..

It will be interesting to see if inserting a custom attribute/therefore support for a new device is a matter of manipulating the database only since they claim to be zigbee complaint the signal protocol should still be logged..

thanks for the monitoring post above ill be able to see now !!

[CloneNum3]

Very interesting and a bit disappointing as I have a zigbee PEQ motion sensor coming Monday and from the list of zigbee attributes I don't see presence listed. unless it uses on or off instead..

It will be interesting to see if inserting a custom attribute/therefore support for a new device is a matter of manipulating the database only since they claim to be zigbee complaint the signal protocol should still be logged..

thanks for the monitoring post above ill be able to see now !!

I'm not sure you'll even need to enter the new attributes into the database. The script monitors the communication, so you may be able to have an attribute response/message from the zigbee PEQ motion sensor to the hub and act on it even if the aprond/wink ignores the information.

I'd need one to verify, I'm interested to hear what you find.

[Middyeasy]

That is a very valid point indeed considering I'm going to rely on OpenHab to handle the device in the end.. what concerns me is the delay time you mentioned using the monitoring method..

the idea behind the PEQ motion sensor is two fold.. one for security.. so if it takes a second or two longer to notify me that there is an intruder its not that big a deal.. but I had hoped to also use it in the wee hours in my bathroom as a presence sensor.. so after 10pm make a rule to dimly turn on the lights so I can go and yet not be fully bright and do so upon my entering and then turn off the lights after X time no presence. With the delay I'm concerned it will diminish the experience because two seconds is a long time when your walking into something in the dark.


Also just realized.. doesn't the device have to pair with something first before it operates in expected mode? other wise its just in pairing mode no? and so monitor would just catch that traffic.

[CloneNum3]

Also just realized.. doesn't the device have to pair with something first before it operates in expected mode? other wise its just in pairing mode no? and so monitor would just catch that traffic.

Yes, I believe it would have to be paired... but what I'm thinking is if the wink hub does not fully support all the attributes of the paired device, you can probably still watch for the device sending the hub. Even if the hub ignores the traffic, you can still use the response as a trigger for the monitor script.

[Middyeasy]

Came across a great post I wanted to share...
http://forum.xda-developers.com/general ... 205/page13

We can add support for our own devices. I've been playing around with the apron.db file and added the Wattage readout for my Aeon Labs power switch. It originally didn't report the number of watts used (as seen in an earlier post). I used the following insert and found that the power is reported via the multilevel sensor instead of the meter attribute. This may be due to the meter attribute not existing before I added it and may not be supported in the software. In any case:

insert into zwavedevicestate (nodeid, endpoint,attributeid) select 2, 0, attributeid from zwaveattribute;

You'll then want to run aprontest -e -m#
I then removed all the blank values with

delete from zwavedevicestate where nodeid=2 and value_get is null;


[root@flex-dvt ~]# aprontest -l -m2
Gang ID: 0x00000002
Generic/Specific device types: 0x10/0x01
Manufacturer ID: 0x0086 Product Type: 0x0003 Product Number: 0x0006
Device has 6 attributes...
New POWER_SWITCH_BINARY
ATTRIBUTE | DESCRIPTION | TYPE | MODE | GET | SET
1 | GenericValue | UINT8 | R/W | 255 | 255
7 | On_Off | BOOL | R | TRUE | TRUE
8 | Level | FLOAT | R | 674.033 | 674.033
9 | Unit | STRING | R | Watt | Watt
18 | ConfigurationParameter_1 | UINT32 | R/W | 0 | 0
2 | On_Off | BOOL | R/W | TRUE | TRUE


My attribute 2 is at the bottom as I had to remove the duplicate and add it back in.

[JustinAiken]

Anybody know the difference between `value_get` and `value_set` in the DeviceState tables?

[evq]

Anybody know the difference between `value_get` and `value_set` in the DeviceState tables?

I believe value_set is the value as last set, for example by aprontest. value_get on the other hand is the last confirmed state of the device. If the hub is unable to communicate to the device properly there could be a disparity between the two. Maybe

[GaryFunk]

From a programmers perspective, value_get will get the value and vakue_set will set the value.

I'd have to look at the code to know for sure.

[CloneNum3]

From a programmers perspective, value_get will get the value and vakue_set will set the value.

I'd have to look at the code to know for sure.

As another guess, I would think the set value would be set immediately when a change is requested, and any set and get values that don't match will trigger the device to change to the set value, once confirmed, the get value will change to reflect the change making them match.

In other words...

When get=set, nothing needs to be done.
When get!=set, the hub will send the set value then update the get value to match the set value.