INDIGOplotD -- a plot tool for indigo

A Plugin to create Plots from INDIGO data and other sources
Post Reply
User avatar
kw123
Posts: 8705
Joined: Sun May 12, 2013 4:44 pm
Location: Dallas, TX
Contact:
Contact kw123

Re: INDIGOplotD -- a plot tool for indigo

Post by kw123 »

Devildog0331:

could you open terminal and run (copy and paste) the attached code?

Code: Select all

psql indigo_history postgres -c "SELECT id,ts, zoneState.faulted from device_history_129866978 t1 WHERE t1.id > 0 and t1.id <= 30000 and NOT EXISTS ( SELECT id, ts, zoneState.faulted t1 FROM device_history_129866978 t2 WHERE t2.ts = t1.ts and t2.id = t1.id -1 and t2.zoneState.faulted = t1.zoneState.faulted ) LIMIT 20000;"
if that works its in the sql code, if not, it could be your database.


Adam:
the error message traces:

Code: Select all

... tents/Server Plugin/dataAccess/indigosql.py", line 294, in ExecuteSQL IS AT:    self.sqlCursor.execute(command, subArgs)  
... tents/Server Plugin/dataAccess/bpgsql.py", line 1014, in execute  :   IS AT:   self.description, self.__rows, self.messages = self.connection._execute(cmd, args)
...tents/Server Plugin/dataAccess/bpgsql.py", line 799, in _execute  :   IS AT:   raise DatabaseError, result.error
Top
Devildog0331
Posts: 72
Joined: Wed Jan 22, 2014 11:44 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by Devildog0331 »

Had two issues.
1) The code you referenced below relies on the postgres user (which I'm not using) and the database name is the one that SQL Logger creates by default (and wasn't what I was using).
Terminal Output: psql: Fatal: role "postgres" does not exist
I can switch the database back over to the default and create that role if those are the dependencies... just please let me know. However....

2) When I changed the code to: psql indigo_data indigo -c "SELECT..... this is the return:

ERROR: missing FROM-clause entry for table "zonestate"
Line 1: Select id,ts, zoneState.faulted from device_history_12986697...
Top
Devildog0331
Posts: 72
Joined: Wed Jan 22, 2014 11:44 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by Devildog0331 »

If it helps.... I can PM you the details and temp creds to reach the postgreSQL database via a webpage.
Top
User avatar
kw123
Posts: 8705
Joined: Sun May 12, 2013 4:44 pm
Location: Dallas, TX
Contact:
Contact kw123

Re: INDIGOplotD -- a plot tool for indigo

Post by kw123 »

Ok then this is an SQL syntax issue I guess. ... Beyond my level.


Sent from my iPhone using Tapatalk
Top
User avatar
RogueProeliator
Posts: 2538
Joined: Tue Nov 13, 2012 3:54 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by RogueProeliator »

zoneState.faulted
Hold up, what exactly is this... is there a column in the table with a period in the name? If so, this is likely the issue...

Adam
Top
Devildog0331
Posts: 72
Joined: Wed Jan 22, 2014 11:44 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by Devildog0331 »

Something else... I know when you go with the Server version of OS X (at least in 10.8 and 10.9), the connection to postgreSQL can be tricky since the OS also uses it.

Connecting to MY part of the database using pgAdmin3, I have to use localhost has the Hostname. If I use 127.0.0.1, it won't connect and tells me that it's not listening on port 5433.
Top
Devildog0331
Posts: 72
Joined: Wed Jan 22, 2014 11:44 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by Devildog0331 »

Yes... That is how it's referenced in the plugin. I'll reset it back to the default setting and create a define a new device that doesn't have a period in the state.
Top
Devildog0331
Posts: 72
Joined: Wed Jan 22, 2014 11:44 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by Devildog0331 »

Getting better... I replaced the . for a _ in the sql command and I received the output you were looking for.

psql indigo_data indigo -c "SELECT id,ts, zoneState_faulted from device_history_129866978 t1 WHERE t1.id > 0 and t1.id <= 30000 and NOT EXISTS ( SELECT id, ts, zoneState_faulted t1 FROM device_history_129866978 t2 WHERE t2.ts = t1.ts and t2.id = t1.id -1 and t2.zoneState_faulted = t1.zoneState_faulted ) LIMIT 20000;"

Will advise on the other in just a sec. I wanted to post that much before I did anything else.
Top
Devildog0331
Posts: 72
Joined: Wed Jan 22, 2014 11:44 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by Devildog0331 »

ok... I reset the plugin to the default settings then reloaded it. From there I defined a Z-Wave on/off switch. After I saved my settings, I got the dreaded red text...
Getting closer though!!!

Code: Select all

Jun 19, 2014, 4:07:31 PM
  INDIGOplotD                     sqlLastID [0]
  INDIGOplotD                     sqlLastID [0]
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     read     device/prop from SQL db:  0:00:00.026044   get firstID  0
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                      sqlcmd=  SELECT id,ts, onOffState from device_history_745566283 t1  WHERE   t1.id > 0 and   t1.id <= 30000 and  NOT EXISTS ( SELECT  id, ts, onOffState t1 FROM  device_history_745566283 t2 WHERE t2.ts = t1.ts and   t2.id = t1.id -1   and   t2.onOffState = t1.onOffState )  LIMIT 20000
  INDIGOplotD                      ret= [[1, '2014-06-17 21:22:23.142786', False], [2, '2014-06-17 21:41:22.11516', False], [3, '2014-06-17 21:43:26.324752', True], [4, '2014-06-17 21:48:26.309973', False], [5, '2014-06-17 21:59:56.324306', False], [6, '2014-06-18 18:35:33.695111', True], [7, '2014-06-18 19:06:50.967978', False], [8, '2014-06-18 19:22:03.796874', True], [9, '2014-06-18 19:27:04.033178', False], [10, '2014-06-18 19:28:05.500836', True], [11, '2014-06-18 19:33:06.035489', False], [12, '2014-06-18 19:34:22.814873', True]
  INDIGOplotD                     [[1, '2014-06-17 21:22:23.142786', False], [2, '2014-06-17 21:41:22.11516', False], [3, '2014-06-17 21:43:26.324752', True], [4, '2014-06-17 21:48:26.309973', False], [5, '2014-06-17 21:59:56.324306', False], [6, '2014-06-18 18:35:33.695111', True], [7, '2014-06-18 19:06:50.967978', False], [8, '2014-06-18 19:22:03.796874', True], [9, '2014-06-18 19:27:04.033178', False], [10, '2014-06-18 19:28:05.500836', True], [11, '2014-06-18 19:33:06.035489', False], [12, '2014-06-18 19:34:22.814873', True]
  INDIGOplotD                     read     device/prop from SQL db:  0:00:00.055287   data base records read  16   firstID  0   lastID  16
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                      sqlcmd=  SELECT id,ts, onOffState from device_history_745566283 t1  WHERE   t1.id > 16 and   t1.id <= 30016 and  NOT EXISTS ( SELECT  id, ts, onOffState t1 FROM  device_history_745566283 t2 WHERE t2.ts = t1.ts and   t2.id = t1.id -1   and   t2.onOffState = t1.onOffState )  LIMIT 20000
  INDIGOplotD                      ret= []
  INDIGOplotD                     []
  INDIGOplotD                     Convert1 device/prop from SQL db:  0:00:00.080642 Switch, Work Bench-onOffState   Total data base records 16
  INDIGOplotD                     nrec into check  theProperty dType average onOffState   16
  INDIGOplotD                     nrec out of  check    17 rejects    0
  INDIGOplotD                     Convert1 device/prop from SQL db:  0:00:00.080760 Switch, Work Bench-onOffState-average            records read/accepted:       16/     16
  INDIGOplotD Error               Error in plugin execution runConcurrentThread:

Traceback (most recent call last):
  File "plugin.py", line 3130, in runConcurrentThread
  File "plugin.py", line 4229, in getSQLdataDynamic
  File "plugin.py", line 3815, in fillHistogramFromSQL
<type 'exceptions.ValueError'>: invalid literal for int() with base 10: '20140617212223.1427'

  INDIGOplotD Error               plugin runConcurrentThread function returned or failed (will attempt again in 10 seconds)
Top
User avatar
RogueProeliator
Posts: 2538
Joined: Tue Nov 13, 2012 3:54 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by RogueProeliator »

That made it past the database -- so definitely better. However, the error appears to be in the handling of the date/time between the two databases. These can be tricky sometimes when dealing with different database systems.

Karl, what does the code where you are reading in the timestamp field look like?

Adam
Top
User avatar
kw123
Posts: 8705
Joined: Sun May 12, 2013 4:44 pm
Location: Dallas, TX
Contact:
Contact kw123

Re: INDIGOplotD -- a plot tool for indigo

Post by kw123 »

ok that looks like mine..
Top
User avatar
kw123
Posts: 8705
Joined: Sun May 12, 2013 4:44 pm
Location: Dallas, TX
Contact:
Contact kw123

Re: INDIGOplotD -- a plot tool for indigo

Post by kw123 »

it looks as if sqLite returns 2014-06-17 21:22:23, while postgre returns 2014-06-17 21:22:23.1427 for timestamps
thats an easy fix.


Now how would the terminal psql command look like in general?

I have put in for the "batch" version: /usr/local/bin/psql psql indigo_data indigo -c .... for the time being.. is the database and the name a free variable deepening on postgre installation?


Karl
Top
User avatar
kw123
Posts: 8705
Joined: Sun May 12, 2013 4:44 pm
Location: Dallas, TX
Contact:
Contact kw123

Re: INDIGOplotD -- a plot tool for indigo

Post by kw123 »

https://www.dropbox.com/s/i8i1rndpyu276 ... -3-0-6.zip 3-0-6

this should fix the int() issue. and the batch version "should" work too for Devildog0331's postgreSQL config with his id and DB name.

Karl
Top
Devildog0331
Posts: 72
Joined: Wed Jan 22, 2014 11:44 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by Devildog0331 »

Sweet... I'm leaving my office now and will give it a go tonight after dinner. Thanks!
Top
User avatar
RogueProeliator
Posts: 2538
Joined: Tue Nov 13, 2012 3:54 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by RogueProeliator »

I have put in for the "batch" version: /usr/local/bin/psql psql indigo_data indigo -c .... for the time being.. is the database and the name a free variable deepening on postgre installation?
Not quite sure what you are asking here, but the username and password will be in that database config that is read in from the SQL Logger configuration (when you are creating the database). Are you calling that on the command line as opposed to using the database connection classes that you got from me? If so, be aware that the path could be different for some people...

Adam
Top
Post Reply

Return to “INDIGOplotD”