How to store as integer

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

How to store as integer

Cecil Westerhof-5
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 10000
    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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to store as integer

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 <[hidden email]>
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 10000
>     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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to store as integer

Cecil Westerhof-5
2017-12-06 13:34 GMT+01:00 Darko Volaric <[hidden email]>:

> 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,
        date        TEXT NOT NULL DEFAULT CURRENT_DATE,
        time        TEXT NOT NULL DEFAULT CURRENT_TIME,
        type        TEXT 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 <[hidden email]>

> 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,
>         date        TEXT NOT NULL DEFAULT CURRENT_DATE,
>         time        TEXT NOT NULL DEFAULT CURRENT_TIME,
>         type        TEXT 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,
>         date        TEXT NOT NULL DEFAULT CURRENT_DATE,
>         time        TEXT NOT NULL DEFAULT CURRENT_TIME,
>         type        TEXT 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,
>         date        TEXT NOT NULL DEFAULT CURRENT_DATE,
>         time        TEXT NOT NULL DEFAULT CURRENT_TIME,
>         type        TEXT 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 10000
> >     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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to store as integer

Simon Slavin-3


On 6 Dec 2017, at 1:19pm, Cecil Westerhof <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to store as integer

Cecil Westerhof-5
2017-12-06 14:58 GMT+01:00 Simon Slavin <[hidden email]>:

>
>
> On 6 Dec 2017, at 1:19pm, Cecil Westerhof <[hidden email]> 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
    SET    message = CAST(message AS REAL)
    WHERE  TYPEOF(message) = 'text'
       AND type = 'cpu-temp'

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to store as integer

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" <[hidden email] on behalf of [hidden email]> wrote:

            return [expr double(${temp})]

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to store as integer

Cecil Westerhof-5
2017-12-06 15:46 GMT+01:00 Peter Da Silva <[hidden email]>:

> 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" <
> [hidden email] on behalf of
> [hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users