SQlite not logging much

I'm using SQLite Manager to look at the sqlite_db but all I get is a few entries for device_history_basic. I change a variable to see if it is logged and it doesn't nor is there anything in log_event_changes.

I have this in the conf. file.

log_device_changes = 1
log_variable_changes = 1
log_event_changes = 1

and I have Log Device state history to SQLite selected and in the conf file the database is sqlite_db

Am I doing something wrong?

bob

[matt (support)]

Did you try closing the database in SQLite Manager and re-opening it? The SQLite browser application I tried (SQLite Database Browser) does not dynamically show changes to the SQLite database as they occur. You have to close the database file and re-open it to see the changes.

Also note changes are only logged if the Device State actually changes.

Regards,
Matt

hello Matt,

Yes I opened and closed the database. I just opened it now with SQLite Data Base Browser and it shows exactly the same information as it did when I opened it last night and many devices and variables have changed during this period but they are not being logged into the database. It has only logged 9 items between Jun 1 and last night, there is nothing for today although there have been many device state and variable changes. There is nothing in eventlog_history or variable_history.

Is there anything I can do to check or test ?

regards,

bob

[matt (support)]

The Event Log should show, whenever the Indigo Server starts up, that it has connected to the SQLite client and is logging to the database. Do you see that? Are there any errors?

And is there any pattern to what has been logged versus what has not bee logged? For example, are only certain devices or device types being logged?

Matt

Matt,

It looks like SQLite starts up but then disconnects as you can see in the log.

2008-06-10 22:28:35 Application SQLite client authenticated
2008-06-10 22:28:35 SQLite client connected to Indigo Server
2008-06-10 22:28:35 SQLite client connected to sqlite_db
2008-06-10 22:28:35 SQLite logging all device state changes
2008-06-10 22:28:35 SQLite logging all variable value changes
2008-06-10 22:28:35 SQLite logging all event log messages
2008-06-10 22:28:35 Application SQLite client disconnected
2008-06-10 22:28:36 Application Connected to SmartHome PowerLinc USB E
2008-06-10 22:28:37 Application PowerLinc address 05.FD.DD, firmware version 2.13
2008-06-10 22:28:37 Application WebServer client authenticated

What would cause SQLite to disconnect?

Regards,

bob

[matt (support)]

You are correct in that is the problem. What does your IndigoSqlClient.conf file look like exactly?

You might also try deleting the database file altogether (or move it to the desktop where Indigo won't find it), then restarting the Indigo Server. It will recreate a new empty database file.

Also, do you have the database file opened in your SQL browser? Maybe it is locking it as read-only, which is causing the IndigoSqlClient to fail.

Matt

Matt,

I'll try deleting the file, meanwhile here is the conf file

Regards,

bob

#######################################################
[client_options]

# The client supports connecting to either a SQLite database or to
# a PostgreSQL database. SQLite requires Mac OS X 10.5 or higher;
# PostgreSQL requires a separate PostgreSQL installer.
#
# valid options: sqlite or postgres
#
database_type = sqlite

# Choose which changes you want logged to the database:
#
log_device_changes = 1
log_variable_changes = 1
log_eventlog = 1

#######################################################
[sqlite_client]

# The options in this section are only used if the sqlite option is
# chosen as the database_type above (in the [client_options] section).

# The name of the SQLite database file located inside the IndigoSqlClint
# folder. It is recommended that you do NOT change this, so that other
# plugins can easily find and connect to the database.
#
database = sqlite_db

#######################################################
[postgres_client]

# The options in this section are only used if the postgres option is
# chosen as the database_type above (in the [client_options] section).

# Below you can override the default postgres username and password.
# The host should remain 127.0.0.1 (localhost) unless you are trying
# to connect to a remote PostgreSQL database.
#
host = 127.0.0.1
user = postgres
password =

# The database name below can be changed if needed. Note that the connection
# code will automatically create the database if it doesn't exist and will
# automatically insert the appropriate tables (eventlog_history,
# device_history, variable_history, etc.).
#
database = indigo_database

Matt,

No joy. I deleted the database file and when I restarted Indigo it make a new one, then disconnected again.

SQLite client authenticated
SQLite client connected to Indigo Server
SQLite client connected to sqlite_db
SQLite creating new table: eventlog_history
SQLite creating new table: device_history_basic
SQLite creating new table: device_history_hvac
SQLite creating new table: device_history_sprinkler
SQLite creating new table: device_history_multi_io
SQLite creating new table: variable_history
SQLite logging all device state changes
SQLite logging all variable value changes
SQLite logging all event log messages
SQLite client disconnected
WebServer client authenticated
WebServer started on port 8176 -- authentication disabled

What could cause SQLite to disconnect? Is there a way I can start it form the Terminal to see if it stays running?

regards,

bob

Matt,

I ran the test db on from the documentation on the SQlite website http://www.sqlite.org/quickstart.html and it created the table and I was able to read it OK. It seems then that Indigo is disconnecting SQlite.

Is there anything I can do?

bob

[matt (support)]

From the Terminal, after Indigo Server is running, enter both of these commands (copy/paste individually and press return):

Code: Select all

cd /Library/Application\ Support/Perceptive\ Automation/Indigo\ 2/IndigoSqlClient/devhelpers

indigosqlstart

If it doesn't return an error, then turn some devices ON and OFF to see if you can get it to fail. Also watch the Event Log to see what it shows.

Copy/paste what the Terminal shows as a result. Also copy/paste what you see in the Event Log window. Sounds like a bug is lurking in there somewhere...

Regards,
Matt

Matt,

Here is the Terminal error;

min:~ min$ cd /Library/Application\ Support/Perceptive\ Automation/Indigo\ 2/IndigoSqlClient/devhelpers
min:devhelpers min$ indigosqlstart
-bash: indigosqlstart: command not found
min:devhelpers min$

The log doesn't show anything because SQlite is disconnected.

I upgraded to 3.0.2 and got the same log error

SQLite client authenticated
SQLite client connected to Indigo Server
SQLite client connected to sqlite_db
SQLite logging all device state changes
SQLite logging all variable value changes
SQLite client disconnected


regards,

bob

[matt (support)]

Try these commands instead:

Code: Select all

cd /Library/Application\ Support/Perceptive\ Automation/Indigo\ 2/IndigoSqlClient/devhelpers

./indigosqlstart

Matt

Matt

I see an error but now sure what it means. I don't see any errors in the log.

bob

min:~ min$ cd /Library/Application\ Support/Perceptive\ Automation/Indigo\ 2/IndigoSqlClient/devhelpers
min:devhelpers min$ ./indigosqlstart
./indigosqlstart: line 37: indigosqlstop: command not found
Starting IndigoSqlClient
min:devhelpers min$ connecting to Indigo server
client connected to Indigo Server
client connected to sqlite_db
logging all device state changes
logging all variable value changes
Traceback (most recent call last):
File "/Library/Application Support/Perceptive Automation/Indigo 2/IndigoSqlClient/IndigoSqlClient.py", line 185, in <module>
CreateIndigoConnection(indigoPortNum, indigoDebug)
File "/Library/Application Support/Perceptive Automation/Indigo 2/IndigoSqlClient/IndigoSqlClient.py", line 105, in CreateIndigoConnection
myIndigoConn, myIndigoDb, myIndigoSql, kSqlConfigFile, _WriteLog
File "/Library/Application Support/Perceptive Automation/Indigo 2/IndigoSqlClient/indigopy/indigohooks.py", line 163, in __init__
self._LogDeviceElem(device)
File "/Library/Application Support/Perceptive Automation/Indigo 2/IndigoSqlClient/indigopy/indigohooks.py", line 201, in _LogDeviceElem
""", (device.name, device.inputBinaryStates, device.inputAnalogStates, device.inputSensorStates, device.outputBinaryStates))
File "/Library/Application Support/Perceptive Automation/Indigo 2/IndigoSqlClient/indigopy/indigosql.py", line 213, in ExecuteWithSubstitution
self.sqlCursor.execute(command, subArgs)
sqlite3.IntegrityError: device_history_multi_io.input_sensor_states may not be NULL

Hello Matt,

Is the error listed in my last post the culprit? "sqlite3.IntegrityError: device_history_multi_io.input_sensor_states may not be NULL"

Can I fix it or is it a bug in the code?

thanks,

bob

[matt (support)]

Stop the Indigo Server (not just the client), then open this file in a text editor:

Code: Select all

/Library/Application Support/Perceptive Automation/Indigo 2/IndigoSqlClient/indigopy/indigohooks.py

Modify these lines from this:

Code: Select all

input_binary_states      VARCHAR(255) NOT NULL,
input_analog_states      VARCHAR(255) NOT NULL,
input_sensor_states      VARCHAR(255) NOT NULL,
output_binary_states   VARCHAR(255) NOT NULL

to not have the "NOT NULL" clause:

Code: Select all

input_binary_states      VARCHAR(255),
input_analog_states      VARCHAR(255),
input_sensor_states      VARCHAR(255),
output_binary_states   VARCHAR(255)

Note you'll want to just remove the " NOT NULL" part and not copy/paste the above lines. If you copy/paste the above, then the tab indention won't be correct and Python will give you a syntax error.

Next, delete your database file (or delete the device_history_multi_io table) and then restart Indigo Server.

Let me know if that works for you. If it does, then I'll roll that change into the next build. Thanks!

Regards,
Matt