Re: [sqlite] Emulate right-join

[Simon Slavin]

On 6 Dec 2017, at 6:07pm, R Smith  wrote:

> You mean make SQLite less Lite, but with Zero computational advantage, by 
> simply adding syntactic sugar bloat? - I'm going to have to vote No on that. 
> (Luckily my vote counts extremely little.)

I would normally vote with you, but …

> I think the reason SQLite never implemented it is precisely because of the 
> fact that it simply amounts to syntactic specialization and no real 
> computational advantage. 

I did not know how to use LEFT JOIN to do FULL OUTER JOIN.  And I didn’t think 
it was something simple.  Had I ever needed to do FULL OUTER JOIN I’d probably 
have done it in my programming language plus two or more SQLite commands.

Supporting RIGHT JOIN, at least, looks to be very simple given that LEFT JOIN 
already works.  And as SQLite is said to support a lot of SQL92, then 
implementing something so simple might be desirable.

Here’s the SQL92 listing for JOIN syntax:

 ::=
INNER
  |  [ OUTER ]
  | UNION

  ::=
LEFT
  | RIGHT
  | FULL

Which reduces to

INNER JOIN
UNION JOIN
LEFT  [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL  [OUTER] JOIN

Here’s SQLite syntax for join-operator:

[NATURAL] INNERJOIN
[NATURAL] CROSSJOIN
[NATURAL] LEFT [OUTER] JOIN

Should be easy to add RIGHT.  Though a lot more work (especially once you 
consider testing the numerous possibilities) to add FULL.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Emulate right-join

[R Smith]

On 2017/12/06 6:35 PM, Christian Schmitz wrote:

Actually, the left outer join is sufficient to execute all the outer

join operators:

- right outer join: just swap the "from" arguments

- full outer joins: union of left and right outer joins

Couldn’t SQLite implement that and do the swap for us?
As well as the union thing?


You mean make SQLite less Lite, but with Zero computational advantage, 
by simply adding syntactic sugar bloat? - I'm going to have to vote No 
on that. (Luckily my vote counts extremely little.)


I think the reason SQLite never implemented it is precisely because of 
the fact that it simply amounts to syntactic specialization and no real 
computational advantage. That said, I'm not against adding those joins, 
just perhaps implemented in a most-efficient way rather than a simple 
transcription of my lazy-code. (Unless of course that ends up being the 
most efficient way to do it.)


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Emulate right-join

[Christian Schmitz]

>> 
>>> Actually, the left outer join is sufficient to execute all the outer
>> join operators:
>>> 
>>> - right outer join: just swap the "from" arguments
>>> 
>>> - full outer joins: union of left and right outer joins
>> 

Couldn’t SQLite implement that and do the swap for us?
As well as the union thing?

Sincerely
Christian

-- 
Read our blog about news on our plugins:

http://www.mbsplugins.de/


___
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

[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

[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

[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] Emulate right-join

[Stephen Chrzanowski]

Thanks all for the input.  My knowledge from nearly 20 years ago is a bit
rusty when it came to material like this.  Just adding "LEFT JOIN" instead
of just "JOIN" has my query working as I need it to.  This type of result
set isn't something I regularly perform.


On Wed, Dec 6, 2017 at 4:41 AM, Simon Slavin  wrote:

> On 6 Dec 2017, at 9:36am, Jean-Luc Hainaut 
> wrote:
>
> > Actually, the left outer join is sufficient to execute all the outer
> join operators:
> >
> > - right outer join: just swap the "from" arguments
> >
> > - full outer joins: union of left and right outer joins
>
> I never realised that.  It’s clever.  And it’s also a cheap way to
> implement FULL JOINs.
>
> Not optimized, but it simple, and changes "can’t do" to "can do".  Thanks.
>
> Simon.
> ___
> 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

Re: [sqlite] How to store as integer

[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

[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

[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

Re: [sqlite] Cross-compiling fails.

[Alastair Growcott]

I just can't let some things go. I am such a bad boy.

See attachment. It has a great big TODO when working out BUILD_EXEEXT 
that still needs implementing. Note that you need to change Makefile.in 
as well to change "@TARGET_EXEEXT@" to "@EXEEXT@".


Tested as working on my system and configuration. Needs additional 
testing once properly implemented the TODO.



Senior Software Engineer
H Scientific Ltd
023 9226 7607
www.h-scientific.co.uk

On 06/12/2017 08:24, Richard Hipp wrote:

On 12/6/17, Alastair Growcott  wrote:

To avoid confusion I recommend that you remove the option to
cross-compile

I'm cool with that approach.  Can you suggest a specific edit to the
configure.ac file that will accomplish what you describe?


Also, I am not sure your suggestion of generating sqlite3.c natively and
then cross-compiling it separately will work. The generated sqlite3.c
has some platform dependent stuff (selective header inclusion as per
above) in it.


The generated sqlite3.c file should be byte-for-byte identical on all
host platforms.  We test for that.  And we publish the SHA3 hash of
the generated sqlite3.c file in our release notes.  If you find a pair
of host platforms that build different "sqlite3.c" files from the same
sources, then that is a bug.  Please report it.





---
This email has been checked for viruses by AVG.
http://www.avg.com
#
# The build process allows for using a cross-compiler.  But the default
# action is to target the same platform that we are running on.  The
# configure script needs to discover the following properties of the 
# build and target systems:
#
#srcdir
#
#The is the name of the directory that contains the
#"configure" shell script.  All source files are
#located relative to this directory.
#
#bindir
#
#The name of the directory where executables should be
#written by the "install" target of the makefile.
#
#program_prefix
#
#Add this prefix to the names of all executables that run
#on the target machine.  Default: ""
#
#ENABLE_SHARED
#
#True if shared libraries should be generated.
#
#BUILD_CC
#
#The name of a command that is used to convert C
#source files into executables that run on the build
#platform.
#
#BUILD_CFLAGS
#
#Switches that the build compiler needs in order to construct
#command-line programs.
#
#BUILD_LIBS
#
#Libraries that the build compiler needs in order to construct
#command-line programs.
#
#BUILD_EXEEXT
#
#The filename extension for executables on the build
#platform.  "" for Unix and ".exe" for Windows.
#
#TCL_*
#
#Lots of values are read in from the tclConfig.sh script,
#if that script is available.  This values are used for
#constructing and installing the TCL extension.
#
#TARGET_READLINE_LIBS
#
#This is the library directives passed to the target linker
#that cause the executable to link against the readline library.
#This might be a switch like "-lreadline" or pathnames of library
#file like "../../src/libreadline.a".
#
#TARGET_READLINE_INC
#
#This variables define the directory that contain header
#files for the readline library.  If the compiler is able 
#to find  on its own, then this can be blank.
#
#EXEEXT
#
#The filename extension for executables on the
#host platform.  "" for Unix and ".exe" for windows.
#
# This configure.in file is easy to reuse on other projects.  Just
# change the argument to AC_INIT().  And disable any features that
# you don't need (for example BLT) by erasing or commenting out
# the corresponding code.
#
AC_INIT(sqlite, m4_esyscmd([cat VERSION | tr -d '\n']))

dnl Make sure the local VERSION file matches this configure script
sqlite_version_sanity_check=`cat $srcdir/VERSION | tr -d '\n'`
if test "$PACKAGE_VERSION" != "$sqlite_version_sanity_check" ; then
AC_MSG_ERROR([configure script is out of date:
 configure \$PACKAGE_VERSION = $PACKAGE_VERSION
 top level VERSION file = $sqlite_version_sanity_check
please regen with autoconf])
fi

#
# Programs needed
#
AC_PROG_LIBTOOL
AC_PROG_INSTALL

#
# Enable large file support (if special flags are necessary)
#
AC_SYS_LARGEFILE

#
# Check for needed/wanted data types
AC_CHECK_TYPES([int8_t, int16_t, int32_t, int64_t, intptr_t, uint8_t,
uint16_t, uint32_t, uint64_t, uintptr_t])

#
# Check for needed/wanted headers
AC_CHECK_HEADERS([sys/types.h stdlib.h stdint.h inttypes.h malloc.h])

#
# Figure out whether or not we have these functions
#
AC_CHECK_FUNCS([fdatasync gmtime_r isnan localtime_r localtime_s 
malloc_usable_size strchrnul usleep utime pread pread64 pwrite pwrite64])

#
# By default, we use the amalgamation (this may be changed below...)
#
USE_AMALGAMATION=1

#
# See whether we can 

Re: [sqlite] Cross-compiling fails.

[Alastair Growcott]

Trying to get it to work correctly:

BUILD_CC is correctly determined, but BUILD_EXEEXT is not (unless you 
have a Cygwin system). On closer examination, the system is in fact 
natively building lemon and mksourceid but because the extension is 
wrong, you get an error like:


   make: *** No rule to make target 'lemon', needed by 'fts5parse.c'. Stop.

Simply calculating BUILD_EXEEXT correctly should make this bit work, and 
cross-compilation would be nice to have. I have now spent way way too 
long, while being paid to do other stuff, looking at this. Sorry I can't 
do more.


Also TARGET_EXEEXT is calculated correctly. In fact because you are not 
building a compiler this value is not needed - simple substitute EXEEXT 
everywhere you use TARGET_EXEEXT. E.g. change:


   if test x"$cross_compiling" = xno; then
  TARGET_EXEEXT=$BUILD_EXEEXT
   else
  TARGET_EXEEXT=$config_TARGET_EXEEXT
   fi
   if test "$TARGET_EXEEXT" = ".exe"; then
  SQLITE_OS_UNIX=0
  SQLITE_OS_WIN=1
  CFLAGS="$CFLAGS -DSQLITE_OS_WIN=1"
   else
  SQLITE_OS_UNIX=1
  SQLITE_OS_WIN=0
  CFLAGS="$CFLAGS -DSQLITE_OS_UNIX=1"
   fi

to:

   if test "$EXEEXT" = ".exe"; then
  SQLITE_OS_UNIX=0
  SQLITE_OS_WIN=1
  CFLAGS="$CFLAGS -DSQLITE_OS_WIN=1"
   else
  SQLITE_OS_UNIX=1
  SQLITE_OS_WIN=0
  CFLAGS="$CFLAGS -DSQLITE_OS_UNIX=1"
   fi

And in Makefile.in change @TARGET_EXEEXT@ to @EXEEXT@. These changes are 
untested (no time).




Trying to remove cross-compilation:

At the top of configure.ac is the following text that I recommend you 
edit for starters:


# The build process allows for using a cross-compiler.  But the default
# action is to target the same platform that we are running on.  The
# configure script needs to discover the following properties of the
# build and target systems:


Search the configure.ac for "cross_compiling". Change everything that 
needs changing, or remove it. I notice that there is code there to 
distinguish the build executable extension from the host one, but it 
doesn't work. I spent a while that I should have spent on paid work 
trying to figure out how to get that to work and in conclusion, it doesn't.


As far as the autoconf stuff goes, from the configure options it looks 
like you have AC_CANONICAL_HOST used, but I only see it in aclocal.m4. I 
don't know enough about autoconf to help here - I don't really know what 
aclocal.m4 is used for, what it does.



Having re-examined sqlite3.c I think you are right that we can compile 
it separately. I think the information needs to be put in a README 
somewhere. It will need instructions on which defines and flags need to 
be set. Normally configure sets that all up for you.




The way I built it in the end was as follows:

   mkdir sqlite_linux
   cd sqlite_linux
   ../sqlite/configure
   Interrupt the build early, once lemon and mksourceid have been built
   cd ..
   mkdir sqlite_mingw32
   cd sqlite_mingw32
   Edit ../sqlite/configure around line 270 to replace:

   if test x"$cross_compiling" = xno; then
  TARGET_EXEEXT=$BUILD_EXEEXT
   else
  TARGET_EXEEXT=$config_TARGET_EXEEXT
   fi

   with something like:

   TARGET_EXEEXT=$EXEEXT

   $config_TARGET_EXEEXT is not set.

   ../sqlite/configure --host=i686-w64-mingw32
   cp ../sqlite_linux/lemon .
   cp ../sqlite_linux/mksourceid .
   make

However it does look like all I really needed to do was to create a 
symlink or something from lemon to lemon.exe and the same for 
mksourceid, plus of course fixing the calculation of TARGET_EXEEXT which 
just needs to be the same as EXEEXT, or even better just use EXEEXT and 
not TARGET_EXEEXT.



Senior Software Engineer
H Scientific Ltd
023 9226 7607
www.h-scientific.co.uk

On 06/12/2017 08:24, Richard Hipp wrote:

On 12/6/17, Alastair Growcott  wrote:

To avoid confusion I recommend that you remove the option to
cross-compile

I'm cool with that approach.  Can you suggest a specific edit to the
configure.ac file that will accomplish what you describe?


Also, I am not sure your suggestion of generating sqlite3.c natively and
then cross-compiling it separately will work. The generated sqlite3.c
has some platform dependent stuff (selective header inclusion as per
above) in it.


The generated sqlite3.c file should be byte-for-byte identical on all
host platforms.  We test for that.  And we publish the SHA3 hash of
the generated sqlite3.c file in our release notes.  If you find a pair
of host platforms that build different "sqlite3.c" files from the same
sources, then that is a bug.  Please report it.




---
This email has been checked for viruses by AVG.
http://www.avg.com

___
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

[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] Emulate right-join

[Simon Slavin]

On 6 Dec 2017, at 9:36am, Jean-Luc Hainaut  wrote:

> Actually, the left outer join is sufficient to execute all the outer join 
> operators:
> 
> - right outer join: just swap the "from" arguments
> 
> - full outer joins: union of left and right outer joins

I never realised that.  It’s clever.  And it’s also a cheap way to implement 
FULL JOINs.

Not optimized, but it simple, and changes "can’t do" to "can do".  Thanks.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Emulate right-join

[Jean-Luc Hainaut]

Actually, the left outer join is sufficient to execute all the outer 
join operators:


- right outer join: just swap the "from" arguments

- full outer joins: union of left and right outer joins

Examples (classical "supplier-part-supply" example):

create table S(SN,NAME);
create table P(PN,COLOR);
create table SP(SN,PN,Q);
insert into S values ('S1','SMITH'),('S2','JONES'),('S3','BLAKE');
insert into P values ('P1','red'),('P2','green'),('P3','blue');
insert into SP values 
('S1','P1',30),('S2','P1',30),('S2','P2',40),('S3','P4',10);


- Left outer join S --> SP --> P:
  --

select S.SN,S.NAME,SP.PN,P.COLOR,SP.Q
from S left join SP using (SN)
left join P  using (PN);

++---++---++
| SN | NAME  | PN | COLOR | Q  |
++---++---++
| S1 | SMITH | P1 | red   | 30 |
| S2 | JONES | P1 | red   | 30 |
| S2 | JONES | P2 | green | 40 |
| S3 | BLAKE | P4 | --| 10 |
++---++---++

- Right outer join P --> SP --> S:
  --

select S.SN,S.NAME,P.PN,P.COLOR,SP.Q
from P left join SP using (PN)
   left join S  using (SN);

++---++---++
| SN | NAME  | PN | COLOR | Q  |
++---++---++
| S1 | SMITH | P1 | red   | 30 |
| S2 | JONES | P1 | red   | 30 |
| S2 | JONES | P2 | green | 40 |
| -- | --| P3 | blue  | -- |
++---++---++

- Full outer join P <--> SP <--> S:
  ---

select S.SN,S.NAME,SP.PN,P.COLOR,SP.Q
from S left join SP using (SN)
   left join P  using (PN)
  union
select S.SN,S.NAME,P.PN,P.COLOR,SP.Q
from P left join SP using (PN)
   left join S  using (SN);

- Full outer join with a "union all" (may be faster but may include 
duplicates):

  -

select S.SN,S.NAME,SP.PN,P.COLOR,SP.Q
from S left join SP using (SN)
   left join P  using (PN)
  union all
select S.SN,S.NAME,P.PN,P.COLOR,SP.Q
from P left join SP using (PN)
   left join S  using (SN)
where Q is null;

++---++---++
| SN | NAME  | PN | COLOR | Q  |
++---++---++
| -- | --| P3 | blue  | -- |
| S1 | SMITH | P1 | red   | 30 |
| S2 | JONES | P1 | red   | 30 |
| S2 | JONES | P2 | green | 40 |
| S3 | BLAKE | P4 | --| 10 |
++---++---++

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cross-compiling fails.

[Alastair Growcott]

Thanks for the reply.

To answer your question using autoconf/configure terminology, my build 
system is Linux and my host system is MinGW32. That is I am building on 
Linux to generate libraries/executables to run under MinGW (on Windows 
essentially). I would however face exactly the same issue if I was 
building on Linux to run on, say, a RaspberryPi or an Android system.


To avoid confusion I recommend that you remove the option to 
cross-compile unless you seriously intend to support it in which case 
you will need to make some serious changes to your configure.ac script. 
In any case you should remove the "--target" option as that is only used 
when building compilers (I am building on system A (build) a compiler 
which will run on system B (host) which will generate binaries that run 
on system C (target)). The determination of the executable extension is 
also broken with some confusion over "build" system extension and 
"target" system extension. The script is incorrectly determining that 
the "build" system extension is ".exe" by building an executable using 
the cross-compiler. It then incorrectly determines that the target 
extension is non-existent because it seems to somehow be assigning the 
build executable extension to that variable. It then goes on to decide 
that because of this the target OS is *nix and then SQLite fails to 
compile because some platform compatibility code tries to include a *nix 
specific header file. I solved this by simply deleting chunks of code in 
the configure script and setting the target extension to be the same as 
the build extension. However this is a hack and not a proper solution.


Also, I am not sure your suggestion of generating sqlite3.c natively and 
then cross-compiling it separately will work. The generated sqlite3.c 
has some platform dependent stuff (selective header inclusion as per 
above) in it.



Senior Software Engineer
H Scientific Ltd
023 9226 7607
www.h-scientific.co.uk

On 06/12/2017 02:10, Richard Hipp wrote:

On 12/4/17, Alastair Growcott  wrote:

Cross-compiling sqlite3 fails due to the use of binaries in the build
process that are generated during the build process.

I recommend that you do

 ./configure; make sqlite3.c

or

 nmake /f makefile.msc sqlite3.c

depending on whether your host system is unix or windows.  Then
manually compile the resulting sqlite3.c for your target platform.

What are the host and target systems for the build you are trying to do?




---
This email has been checked for viruses by AVG.
http://www.avg.com

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cross-compiling fails.

[Richard Hipp]

On 12/6/17, Alastair Growcott  wrote:
>
> To avoid confusion I recommend that you remove the option to
> cross-compile

I'm cool with that approach.  Can you suggest a specific edit to the
configure.ac file that will accomplish what you describe?

>
> Also, I am not sure your suggestion of generating sqlite3.c natively and
> then cross-compiling it separately will work. The generated sqlite3.c
> has some platform dependent stuff (selective header inclusion as per
> above) in it.
>

The generated sqlite3.c file should be byte-for-byte identical on all
host platforms.  We test for that.  And we publish the SHA3 hash of
the generated sqlite3.c file in our release notes.  If you find a pair
of host platforms that build different "sqlite3.c" files from the same
sources, then that is a bug.  Please report it.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users