print SQL data in log file

Posted on
Sat Sep 12, 2015 3:19 pm
autolog offline
Posts: 2305
Joined: Sep 10, 2013
Location: West Sussex

Re: show SQLite data in log file

kw123 wrote:
... In the menu input type command-v to enter a tab character. ...


Isn't that just a Paste command? Not quite understanding what you mean :?

Posted on
Sat Sep 12, 2015 3:41 pm
kw123 offline
User avatar
Posts: 5519
Joined: May 12, 2013
Location: Dallas, TX

Re: show SQLite data in log file

I guess I had a tab in the buffer and command v pasted it.
What works is copy and paste a tab


Sent from my iPhone using Tapatalk

Posted on
Sat Sep 12, 2015 8:16 pm
kw123 offline
User avatar
Posts: 5519
Joined: May 12, 2013
Location: Dallas, TX

Re: print SQL data in log file

new version (0.7.3) posted where you can use the word "tab" to set the separator to tab, see screen shot

Karl
Attachments
Screen Shot 2015-09-12 at 9.15.09 PM.png
Screen Shot 2015-09-12 at 9.15.09 PM.png (72.39 KiB) Viewed 2222 times

Posted on
Sun Sep 13, 2015 1:00 am
autolog offline
Posts: 2305
Joined: Sep 10, 2013
Location: West Sussex

Re: print SQL data in log file

Hi Karl,
This is great - I am making progress :D

It seems that to open a tab separated file in OS X Numbers 3.5.3 you have to have a filetype of txt - it doesn't work correctly if the filetype is csv.

I now have the fields in separate cells which is good but I note that they have leading spaces. If I use a $ (dollar sign) as a separator you will see what I mean:
Code: Select all
2785$ 2015-09-13 06:19:14.176493$ $ 23.1$ 23.1 °C
The SQL output equivalent is:
Code: Select all
  id  |             ts             | onoffstate | sensor value | sensorvalue_ui
------+----------------------------+------------+-------------+----------------
 2736 | 2015-09-09 13:04:42.304044 |            |        23.9 | 23.9 °C

It would also be useful to be able to have an optional header row output with the with the field names if that was possible?

Thanks again for the updates :)

Posted on
Sun Sep 13, 2015 1:18 am
kw123 offline
User avatar
Posts: 5519
Joined: May 12, 2013
Location: Dallas, TX

Re: print SQL data in log file

yes filetype txt works.

dont understand the spaces.

if you choose tab as separator, that is a space. are you seeing the tab+ space?

could you check the file with textedit to see if there are actually spaces?

I could do a strip(" "), that would remove leading and trailing space.

as for the headers. yes that should be a quick addition.

Karl
going to bed now

Posted on
Sun Sep 13, 2015 11:05 am
kw123 offline
User avatar
Posts: 5519
Joined: May 12, 2013
Location: Dallas, TX

Re: print SQL data in log file

v 0.8.1:
added field header= yes/no
and removed any trailing or leading space around each state

see page one of the thread for download of the new version and the full explanation of the options.

Karl

Posted on
Sun Sep 13, 2015 11:48 am
autolog offline
Posts: 2305
Joined: Sep 10, 2013
Location: West Sussex

Re: print SQL data in log file

I have installed V0.8.1 and I tried out the new features; selectable header and leading/trailing space suppression which works fine :D

I am having a slight oddity with variables. I have a variable which according to Valentina Studio has 5243 records with a time stamps from 2015-06-14 to today.
When I do a selection for 100 records I am getting record ids 19246 to 19274 and 20393 to 20463 rather than the last 100. The ids that are missed are present and the last id is 21337.

Any ideas on this?

Posted on
Sun Sep 13, 2015 12:51 pm
kw123 offline
User avatar
Posts: 5519
Joined: May 12, 2013
Location: Dallas, TX

Re: print SQL data in log file

switch sql debug on
run you query
copy the sql statement from the log file and paste into a terminal session
remove the LIMIT xxxx xx x and run it.

should look like:
Code: Select all
/Library/PostgreSQL/bin/psql indigo_history postgres   -A -F '; ' -c  "SELECT id, to_char(ts,'YYYY-mm-dd-HH24:MI:ss'),value from variable_history_1040551329 ;"

execute it in a terminal
it will create a lot of output in the terminal.

but thats the raw data.

karl

Posted on
Sun Sep 13, 2015 1:07 pm
autolog offline
Posts: 2305
Joined: Sep 10, 2013
Location: West Sussex

Re: print SQL data in log file

Done a quick test - the order of the raw data isn't in ID order which is where the problem may be?

I'm away for a while now - back later :)

Posted on
Sun Sep 13, 2015 1:22 pm
kw123 offline
User avatar
Posts: 5519
Joined: May 12, 2013
Location: Dallas, TX

Re: print SQL data in log file

YES!!!! how did you create that??

many other things (in my plugins would not work !!) they all assume that the records are stored in increasing ID# order = time order

if you just use ID> x and no LIMIT the output should be correct , but not in the correct time order

We could add an order by id, but that will need to wait.. I will be out for ~ 3 weeks starting Wednesday.

Karl

Posted on
Sun Sep 13, 2015 2:34 pm
autolog offline
Posts: 2305
Joined: Sep 10, 2013
Location: West Sussex

Re: print SQL data in log file

kw123 wrote:
YES!!!! how did you create that??

Just ran the query you specified and that was the order it came out in i.e not in order :)

kw123 wrote:
We could add an order by id ...l
That would resolve the problem I think :)

Seems like it might have been a :idea: light bulb moment :D

Posted on
Sun Sep 13, 2015 3:36 pm
kw123 offline
User avatar
Posts: 5519
Joined: May 12, 2013
Location: Dallas, TX

Re: print SQL data in log file

If the records are not sorted with the id, the id is actually useless. A statement " id> nn" does not make sense. Id is then is just a random (but unique) number.
The only order we could use is the date-time column.

You should really look into how to fix that in your database. A little bit above my knowledge level, But I would love to learn it.

someting like:
stop indigo
add a serial id2
delete id
rename id2 to id
start indigo

Karl


something like this ..
CREATE SEQUENCE user_id_seq;
ALTER TABLE user ALTER id2 SET DEFAULT NEXTVAL('user_id_seq');

Posted on
Sun Sep 13, 2015 3:50 pm
autolog offline
Posts: 2305
Joined: Sep 10, 2013
Location: West Sussex

Re: print SQL data in log file

The IDs are in date order it is just that the query is returning the data in a random order because the order hasn't been specified.
Ordering on ID should be fine, I think :)

Posted on
Sun Sep 13, 2015 3:58 pm
kw123 offline
User avatar
Posts: 5519
Joined: May 12, 2013
Location: Dallas, TX

Re: print SQL data in log file

Jon,

ALTER TABLE device_history_xxx drop COLUMN id
ALTER TABLE device_history_xxx ADD COLUMN id SERIAL;
UPDATE device_history_xxx SET id = DEFAULT;
ALTER TABLE device_history_xxx ADD PRIMARY KEY (id);


this might do the trick (for each xxx ) .. just browsing on the web pages..


Karl

Posted on
Sun Sep 13, 2015 4:04 pm
autolog offline
Posts: 2305
Joined: Sep 10, 2013
Location: West Sussex

Re: print SQL data in log file

I have just taken the liberty of adding in the following statement after line 364 in plugin.py:
Code: Select all
         sqlCommandText+= " order by ID "
The output is now coming out correctly. :D

I don't want to have to play around with tables created by Indigo's SQL Logger - I am not sure that would be wise :)

Who is online

Users browsing this forum: No registered users and 1 guest