print SQL data in log file

[autolog]

... 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

[kw123]

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

[kw123]

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

Karl

[autolog]

Hi Karl,
This is great - I am making progress

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

[kw123]

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

[kw123]

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

[autolog]

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

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?

[kw123]

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

[autolog]

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

[kw123]

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

[autolog]

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

We could add an order by id ...l

That would resolve the problem I think

Seems like it might have been a light bulb moment

[kw123]

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');

[autolog]

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

[kw123]

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

[autolog]

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.

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