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>