Re: [sqlite] How to store as integer
2017-12-06 15:46 GMT+01:00 Peter Da Silva : > I’d recommend expr {double($temp)} so the bytecode compiler can optimize > the expression. > > On 12/6/17, 8:40 AM, "sqlite-users on behalf of Cecil Westerhof" < > sqlite-users-boun...@mailinglists.sqlite.org on behalf of > cldwester...@gmail.com> wrote: > > return [expr double(${temp})] > Not very important in this case (it is only executed once a minute), but it is good to pick up good habits, so I changed it. Thanks. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to store as integer
I’d recommend expr {double($temp)} so the bytecode compiler can optimize the expression. On 12/6/17, 8:40 AM, "sqlite-users on behalf of Cecil Westerhof" wrote: return [expr double(${temp})] ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to store as integer
2017-12-06 14:58 GMT+01:00 Simon Slavin : > > > On 6 Dec 2017, at 1:19pm, Cecil Westerhof wrote: > > >message NOT NULL > > Given thqt you want the "message" stored as REAL, you should be defining > this column as REAL. This is necessary, though not sufficient. I only want to store it as a real in this case (or other cases where it is a real). In other cases I want to store it as text. (Probably most cases.) But I found the solution. I just rewrote getCPUTemp to: proc getCPUTemp {} { if {1 != [regexp -all -line {^CPU_TEMP: +\+([0-9.]+)°C } [exec sensors] -> temp]} { error {Did not get exactly a single temperature line from [exec sensors] output} } return [expr double(${temp})] } In the return statement I changed the string to double. And who-la it is stored as real. I updated the about 3.000 records with: UPDATE messages SETmessage = CAST(message AS REAL) WHERE TYPEOF(message) = 'text' AND type = 'cpu-temp' -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to store as integer
On 6 Dec 2017, at 1:19pm, Cecil Westerhof wrote: >message NOT NULL Given thqt you want the "message" stored as REAL, you should be defining this column as REAL. This is necessary, though not sufficient. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to store as integer
2017-12-06 13:34 GMT+01:00 Darko Volaric : > How it's stored depends on how the messages table is defined (which type > the message column has been given), which you haven't shown, and whether > storeMessage quotes the message argument when forming the string. My advice > is to remove any column type and make sure numbers are not quoted when they > are inserted into the database. > I should have added those also: CREATE TABLE messages( messageID INTEGER PRIMARY KEY AUTOINCREMENT, dateTEXT NOT NULL DEFAULT CURRENT_DATE, timeTEXT NOT NULL DEFAULT CURRENT_TIME, typeTEXT NOT NULL, message NOT NULL ) As you see message does not have any type. storeMessage: proc storeMessage {type message} { db eval " INSERT INTO messages (type, message) VALUES (:type, :message) " } I changed it to: proc storeMessage {type message} { db eval { INSERT INTO messages (type, message) VALUES (:type, :message) } } But that does not make a difference. On Wed, Dec 6, 2017 at 11:09 AM, Cecil Westerhof > wrote: > > > I have the following tcl script: > > #!/usr/bin/env tclsh > > > > ### Improvements > > # Get database from conf-file > > > > > > package require sqlite3 > >CREATE TABLE messages( > messageID INTEGER PRIMARY KEY AUTOINCREMENT, > dateTEXT NOT NULL DEFAULT CURRENT_DATE, > timeTEXT NOT NULL DEFAULT CURRENT_TIME, > typeTEXT NOT NULL, > message NOT NULL > ) > > > > proc getCPUTemp {} { > > if {1 != [regexp -all -line {^CPU_TEMP: +\+([0-9.]+)°C } [exec > > sensors] -> temp]} { > > error {Did not get exactly a single temperature line from > [exec > > sensors] output}CREATE TABLE messages( > messageID INTEGER PRIMARY KEY AUTOINCREMENT, > dateTEXT NOT NULL DEFAULT CURRENT_DATE, > timeTEXT NOT NULL DEFAULT CURRENT_TIME, > typeTEXT NOT NULL, > message NOT NULL > ) > > } > > return ${temp} > > } > > > > proc storeCPUTemp {} { > > storeMessage cpu-temp [getCPUTemp] > > } > > > > proc storeMessage {type message} { > > db eval " > > INSERT INTO messages > > (type, message) > > VALUES > > (:type, :message) > > " > > }CREATE TABLE messages( > messageID INTEGER PRIMARY KEY AUTOINCREMENT, > dateTEXT NOT NULL DEFAULT CURRENT_DATE, > timeTEXT NOT NULL DEFAULT CURRENT_TIME, > typeTEXT NOT NULL, > message NOT NULL > ) > > > > proc storeSwap {} { > > storeMessage swap-usage [exec swapon --noheadings --show] > > } > > > > if {$argc != 1} { > > error "Error: ${argv0} DATABASE" > > } > > sqlite db [lindex $argv 0] > > db timeout 1 > > while {true} { > > after [expr {1000 * (60 - [clock seconds] % 60)}] > > set currentMinute [clock format [clock seconds] -format %M] > > db transaction { > > storeCPUTemp > > # At the whole hour we save swap usage > > if {${currentMinute} == "00"} { > > storeSwap > > } > > } > > } > > # Not really necessary because the above loop never ends > > # But I find this more clear and is robuster against change > > db close > > > > If I enter: > > SELECT date > > , message > > , TYPEOF(message) > > FROM messages > > WHERE type = 'cpu-temp' > >AND date = '2017-12-06' > > > > I see that the temperature is saved as text. > > In the past I had a script like this in Python who would save the > > temperature as real. What do I need to change to let this script save it > as > > real also? > > > > -- > > Cecil Westerhof > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to store as integer
How it's stored depends on how the messages table is defined (which type the message column has been given), which you haven't shown, and whether storeMessage quotes the message argument when forming the string. My advice is to remove any column type and make sure numbers are not quoted when they are inserted into the database. On Wed, Dec 6, 2017 at 11:09 AM, Cecil Westerhof wrote: > I have the following tcl script: > #!/usr/bin/env tclsh > > ### Improvements > # Get database from conf-file > > > package require sqlite3 > > > proc getCPUTemp {} { > if {1 != [regexp -all -line {^CPU_TEMP: +\+([0-9.]+)°C } [exec > sensors] -> temp]} { > error {Did not get exactly a single temperature line from [exec > sensors] output} > } > return ${temp} > } > > proc storeCPUTemp {} { > storeMessage cpu-temp [getCPUTemp] > } > > proc storeMessage {type message} { > db eval " > INSERT INTO messages > (type, message) > VALUES > (:type, :message) > " > } > > proc storeSwap {} { > storeMessage swap-usage [exec swapon --noheadings --show] > } > > if {$argc != 1} { > error "Error: ${argv0} DATABASE" > } > sqlite db [lindex $argv 0] > db timeout 1 > while {true} { > after [expr {1000 * (60 - [clock seconds] % 60)}] > set currentMinute [clock format [clock seconds] -format %M] > db transaction { > storeCPUTemp > # At the whole hour we save swap usage > if {${currentMinute} == "00"} { > storeSwap > } > } > } > # Not really necessary because the above loop never ends > # But I find this more clear and is robuster against change > db close > > If I enter: > SELECT date > , message > , TYPEOF(message) > FROM messages > WHERE type = 'cpu-temp' >AND date = '2017-12-06' > > I see that the temperature is saved as text. > In the past I had a script like this in Python who would save the > temperature as real. What do I need to change to let this script save it as > real also? > > -- > Cecil Westerhof > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users