subject:"\[sqlite\] How to store as integer"

Re: [sqlite] How to store as integer

2017-12-06 Thread Cecil Westerhof

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

2017-12-06 Thread 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" 
 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 Thread Cecil Westerhof

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

2017-12-06 Thread 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.

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 Thread Cecil Westerhof

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

2017-12-06 Thread 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.

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

[sqlite] How to store as integer

2017-12-06 Thread Cecil Westerhof

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

Re: [sqlite] How to store as integer

Re: [sqlite] How to store as integer

Re: [sqlite] How to store as integer

Re: [sqlite] How to store as integer

Re: [sqlite] How to store as integer

Re: [sqlite] How to store as integer

[sqlite] How to store as integer

7 matches

Site Navigation

Mail list logo

Footer information