Re: [sqlite] More built-in functions for basic math

[Keith Christian]

We all know this but it bears repeating:

At some point it's time to use a different database engine or offload
to other code.  Sqlite could easily burgeon to the size of the other
databases if everything asked for was included.  Where, then, would we
get a small but still functional SQL engine?

On Thu, Mar 9, 2017 at 6:49 AM, Eric Grange  wrote:
> A bonus of having them defined in the core is that it avoids the minor
> inconsistencies that are bound to arise in custom implementations (starting
> with the name of math functions)
>
> Main downside is probably not going to be the size, but that it reserves
> more names, and may conflict with existing custom implementations.
>
> Eric
>
> On Thu, Mar 9, 2017 at 1:16 PM, R Smith  wrote:
>
>> I second this - Been having a hard time making basic queries with a simple
>> x^y function in SQL for SQLite since there is no guarantee what the
>> end-user's system will have it compiled-in. I can version-check or
>> version-enforce easily, but compile-option check or enforce is a no-go.
>>
>> If we can shift the basic "Auto-included" feature set a few notches up, we
>> can still have hardcore minimalist users compile their own (as they
>> probably already do), but it would be nice to know a query running on a
>> standard linux or Apple OS on the included SQLite will support some wider
>> functions as a rule[1] without having to keep track. I realize this will
>> take a time to permeate through the world, but it would be great to start
>> asap.
>>
>> [1] Yes, there are threads on this same forum where I myself kicked
>> against bloating SQLite with unneeded functionality as a rule, but perhaps
>> the definition of "needed" needs revisiting. I think good math and string
>> functions certainly qualify.
>>
>> Cheers,
>> Ryan
>>
>>
>>
>> On 2017/03/09 11:45 AM, Dominique Devienne wrote:
>>
>>> I find that I'm often missing basic mathematical functions in the default
>>> shell.
>>> Many SQLite clients add many, but given that the official SQLite shell
>>> misses
>>> them you can't use them in views for predefined "reports" within the DB
>>> file itself, for example.
>>>
>>> There's [1] which is 50KB, but only a tiny part of that is for math
>>> functions, so math functions are only a few KBs away.
>>>
>>> Adding basic math functions and stddev, median, variance, etc... wouldn't
>>> add much,
>>> and they could be added to the shell at least, if deemed too big for the
>>> amalgamation,
>>> but given that many things can be turned on/off in the amalgamation, that
>>> would be just
>>> one more IMHO.
>>>
>>> The goal here would be to move the "minimum expectations" of what can be
>>> done with the official shell, out-of-the-box, w/o the need to resort to
>>> .load of an extension which is not readily available in compiled form for
>>> many non-programmer users.
>>>
>>> And IMHO, the ability to use math functions in views is why "moving the
>>> baseline" is necessary,
>>> since without those being built-in, the views will just error out in the
>>> official shell.
>>>
>>> My $0.02, despite the upcoming chorus about lite or do-it-in-your-app
>>> naysayers. Thanks, --DD
>>>
>>> PS: Sure SQLite's primary use case is as an *embedded* DB, so the host-app
>>> can add
>>> whatever it wants/needs in terms of UDFs, but I also think the
>>> "standalone"
>>> use of SQLite independently of the app that generated the DB file is
>>> important, and we should raise the
>>> bar of the minimum number of built-in functions, starting with the
>>> official
>>> shell.
>>>
>>> [1] https://www.sqlite.org/contrib/download/extension-functions.c?get=25
>>> ___
>>> 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-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] SQLite Bug Disclosure

[Keith Christian]

Thank you, Dr. Hipp!

On Sun, Jan 22, 2017 at 9:40 AM, Richard Hipp  wrote:
> Thanks for sending the bug report, Ian.  Unfortuantely, the repro
> script did not work for me.  I think you sent the wrong file.
> Nevertheless, I was able to track down and fix the problem based on
> your description.  A fix is on trunk.
>
> To the rest of the world:  the problem was in the command-line shell
> program in one of the "dot-commands", not in the SQLite core.  An
> uninitialized variable got passed as a string argument to printf() and
> then (depending on what random location that uninitialized variable
> pointed to) printf() tried to get the string length and ran off the
> edge of the world.  Fixed now on trunk.  I was not actually able to
> repro the problem.  But that I mean, I was never able to come up with
> a command-line shell input that would actually leave the variable
> uninitialized. I think it might require linking a recent version of
> SQLite command-line shell code against an older and/or customized
> shared library of the SQLite core.  Whatever the cause, the variable
> is now always initialized and so there should not be any further
> problems.
>
> On 1/21/17, ian  wrote:
>> To Whom it may Concern:
>>
>> While performing some work with SQLite3, our team discovered a set of
>> inputs that, when passed to SQLite3, would trigger a segmentation fault.
>> I have attached both a write up of our findings while investigating the
>> fault (SQLite3 Writeup.docx), and a minimized version of the inputs that
>> were used to trigger the fault (minimal_crash.sql). Feel free to contact
>> me for any additional details.
>>
>> Regards,
>>
>> Ian
>>
>>
>
>
> --
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

[Keith Christian]

Thanks for all of the responses.  Duplicates are OK, the date time
stamps are part of a log file that I am trying to develop a query for.
I couldn't get the LIMIT/OFFSET part of the query right after several
attempts so I thought I'd ask the experts here.

I appreciate your replies and suggestions.

Keith

On Mon, Jul 11, 2016 at 11:12 PM, J Decker <d3c...@gmail.com> wrote:
> SELECT DISTINCT date_time_stamp FROM general ORDER BY date_time_stamp DESC
> LIMIT 2
>
> isn't it simply to use DISTINCT?
>
> On Mon, Jul 11, 2016 at 4:25 PM, Keith Christian <keith1christ...@gmail.com>
> wrote:
>
>> A table has a column of dates and times that look like this:
>>
>> 2015-10-02 07:55:02
>> 2015-10-02 07:55:02
>> 2015-10-02 10:00:03
>> 2015-10-02 10:05:02
>> 2015-10-02 10:10:02
>>
>>
>> Schema:
>> CREATE TABLE general ( id integer primary key autoincrement, server
>> text, date_time_stamp text);
>>
>>
>> Would like to get the latest two dates and times, kept in ascending
>> order, e.g. the query should return these two values:
>>
>> 2015-10-02 10:05:02
>> 2015-10-02 10:10:02
>>
>>
>> Is there a way to store the number of values in the date_time_stamp
>> column and use the count minus N to get the largest N values in the
>> column?
>>
>>
>> Query:
>> select date_time_stamp a from general, c as count(a) from general
>> where date_time_stamp!='date_time_stamp_isempty' order by a limit c,2;
>>
>>
>> Error: incomplete SQL: select date_time_stamp a from general, c as
>> count(a) from general where date_time_stamp!='_isempty' order by a
>> limit c,2
>> ___
>> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query question: order by ascending, return the two largest values in ascending order

[Keith Christian]

A table has a column of dates and times that look like this:

2015-10-02 07:55:02
2015-10-02 07:55:02
2015-10-02 10:00:03
2015-10-02 10:05:02
2015-10-02 10:10:02


Schema:
CREATE TABLE general ( id integer primary key autoincrement, server
text, date_time_stamp text);


Would like to get the latest two dates and times, kept in ascending
order, e.g. the query should return these two values:

2015-10-02 10:05:02
2015-10-02 10:10:02


Is there a way to store the number of values in the date_time_stamp
column and use the count minus N to get the largest N values in the
column?


Query:
select date_time_stamp a from general, c as count(a) from general
where date_time_stamp!='date_time_stamp_isempty' order by a limit c,2;


Error: incomplete SQL: select date_time_stamp a from general, c as
count(a) from general where date_time_stamp!='_isempty' order by a
limit c,2
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

[Keith Christian]

On Tue, Nov 10, 2015 at 3:35 PM, Scott Robison  
wrote:
> On Nov 10, 2015 2:19 PM, "John McKown"  
> wrote:
>>
>> On Tue, Nov 10, 2015 at 3:15 PM, Keith Christian <
> keith1christian at gmail.com>
>> wrote:
>>
>> > A great thing for Windows users.
>> >
>> > My only caveat is: Beware corporate pressure.  Stay Free.
>> >
>> >
>> I agree. I'm not any kind of MS fan. I simply don't trust them _at all_.
>> They like to "embrace" ... "extend" ... "eliminate".
>
> Considering that SQLite is public domain, they could take the code and do
> anything they want with it. Well, most of it, not counting things like SEE
> & CEROD. I don't think there is any conceivable way this is bad or
> worrisome news.


Scott,

Right.  On second thought, it was probably was a silly comment.

SQLite is an integral part in numerous other commercial software systems.

Keith

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

[Keith Christian]

A great thing for Windows users.

My only caveat is: Beware corporate pressure.  Stay Free.

On Tue, Nov 10, 2015 at 12:41 PM, Richard Hipp  wrote:
> http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Feedback request: JSON support in SQLite

[Keith Christian]

Pardon me, but:  At what point does the code required for the
inclusion of a multitude of supported data formats exceed the core
purpose of the executable?

At some point, obtaining a desired output format (from the potentially
dozens available) might be offloaded to a different executable that
performs that function, rather than bloating program X with a thousand
input/output formats.

This is an unsolicited opinion.

The decision rests with the good Dr. Hipp, who selflessly volunteers
his time and skills.


Keith


On Fri, Sep 11, 2015 at 11:26 AM, Dominique Devienne
 wrote:
> On Fri, Sep 11, 2015 at 5:58 PM, Richard Hipp  wrote:
>
>> Your feedback is encouraged.
>>
>
> https://www.sqlite.org/draft/c3ref/value_subtype.html does not say what
> happens or which value we get,
> should one call sqlite3_value_subtype on a sqlite_value* which which
> no sqlite3_result_subtype()
> was made.
>
> Can we have some guarantee, like always 0, or MAX_INT, or something?
> Thanks, --DD
>
> PS: Thanks for this json1 experimental extension!
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to load a blob from the shell?

[Keith Christian]

Richard,

Agreed, glad you wrote sar as a save format.  Will have to look into
it further.  Only wanted to mention a name collision.

Further, thank you for all of the great work you do on SQLite, it's
great time saver and productivity tool.

Keith


On Mon, Apr 7, 2014 at 5:13 PM, Richard Hipp <d...@sqlite.org> wrote:
> On Mon, Apr 7, 2014 at 6:56 PM, Keith Christian
> <keith1christ...@gmail.com>wrote:
>
>>
>> However, on production *nix machines, the path to the SQLite 'sar'
>> will probably have to be absolute, or else the native 'sar' (System
>> Activity Reporter) will run instead.
>>
>
> Huh.  Never heard of it.  It is not installed on my Ubuntu desktop.
>
> Realize the my whole purpose in writing "sar" was to demonstrate that
> SQLite could serve at least as well as an application's "save-file format"
> as does a ZIP archive.  Note that ZIP is used as the file format for ePUB
> and ODT.
>
> Additional information:  http://www.sqlite.org/appfileformat.html
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to load a blob from the shell?

[Keith Christian]

Hi Richard,

'SAR' looks like a great utility!

However, on production *nix machines, the path to the SQLite 'sar'
will probably have to be absolute, or else the native 'sar' (System
Activity Reporter) will run instead.  Perhaps add a caveat to the docs
for SQLite's 'sar?'


Keith

On Sun, Apr 6, 2014 at 7:43 PM, Richard Hipp  wrote:
> On Sun, Apr 6, 2014 at 4:01 PM,  wrote:
>
>> I haven't figured out how to load a blob (e.g., image) from the shell.  I
>> would think there should be something like this but can't find anything:
>>
>> insert into table values(file('path_to_binary_file'));
>>
>> Are blobs only loadable by using SQLite from C?
>>
>> Any ideas?
>>
>
> At http://www.sqlite.org/sar there is a utility program that generates an
> "SQLite Archive", similar to a ZIP archive but using SQLite as the file
> format instead of the ZIP format.  You can use that utility (with the
> just-added "-n" option to disable compression) to load one or more images
> into a database.  Then in the shell, transfer those images out of the "SAR"
> table where the "sar" utility puts them and into the field and table of
> your choice.  Use the reverse procedures to extract the BLOBs.  This is
> more work (more commands) but has the advantage of being able to load many
> thousands of BLOBs all at once, instead of one at a time.  The "sar"
> utility works on unix.  I have made no effort to make it work on Windows,
> but I will accept patches if that is important to you.
>
> Larray Brasfield's extention to shell.c to support ".blobextract" and
> ".blobreplace" commands apparently uses the incremental BLOB I/O interface
> to avoid the need to load entire images into memory.  ("sar" does not do
> this, btw.  It loads each image into memory.)  That is nice, but on a
> modern workstation with many GB of RAM, is it really necessary?  Maybe in
> some obscure cases.  But in the common case of a smaller BLOB (a few
> megabytes) I think custom functions would work better:
>
> INSERT INTO sometable(x) VALUES(fromfile('data/myblob.gif'));
> UPDATE sometable SET x=fromfile('data/myblob.gif') WHERE rowid=123;
> SELECT tofile('data/blob-out.gif', x) FROM sometable WHERE rowid=123;
>
> -- Extract all blobs:
> SELECT tofile('data/blob-out-'||rowid||'.gif', x) FROM sometable;
>
> I'm open to the possibility of adding fromfile() and tofile() as extension
> functions in shell.c.  Maybe tomorrow sometime.
>
> Another idea is to create a virtual table that wraps the filesystem:
>
> CREATE VIRTUAL TABLE temp.fs AS fs;
> INSERT INTO sometable(x) SELECT content FROM fs WHERE
> name='data/myblob.gif';
> UPDATE sometable SET x=(SELECT content FROM fs WHERE
> name='data/myblob.gif)
>  WHERE rowid=123;
> REPLACE INTO fs(name, content) SELECT 'data/blob-out.gif', x FROM
> sometable
>  WHERE rowid=123;
>
> REPLACE INTO fs(name, content SELECT 'data/blob-out'||rowid||'.gif, x
> FROM sometable;
>
> The virtual table is potentially a much more powerful abstraction, but as
> you can see from the examples above, it requires a little more work to
> actually use.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3.exe file not produced by sqlite-autoconf-3080400.tar.gz on Cygwin

[Keith Christian]

Jan,

Good catch: I was in the tea/ directory, building the DLL for use with
TEA on Cygwin at the outset of this thread.  When I ran my test TCL
script with the 'load libsqlite3.8.4.dll "SQLite3"' line, the dll was
there, but sqlite3.exe wasn't.

So, I started looking over things to see why the sqlite3.exe
executable was missing and found changes in the Makefile from 3.8.3 to
3.8.4.

At that point, I decided to post this message and ask.  Richard Hipp
suggested that I download the amalgamation and run a gcc command  that
he provided.  Thatproduced a sqlite3.exe.  I'd like to track down why
the Makefile changes in 3.8.4 don't produce a sqlite3.exe file when
compiling in the tea/ directory.


Thanks for the new 3.8.4.1 for Cygwin.


Keith



On Tue, Mar 11, 2014 at 2:54 PM, Jan Nijtmans <jan.nijtm...@gmail.com> wrote:
> 2014-03-11 17:45 GMT+01:00 Keith Christian <keith1christ...@gmail.com>:
>> Will have to troubleshoot the details of the makefile changes between
>> the autoconf version from 3.8.3 to 3.8.4, to see why on this Cygwin
>> environment a "sqlite3.exe" wasn't created.  That will be awhile,
>> pretty busy at $WORK (not a bad thing.)
>
> Don't know, but in my Cygwin enviroment everything works fine. The
> diff's in your first mail indicate the changes in the TEA makefiles,
> which were adapted to the latest versions in this commit:
><http://www.sqlite.org/src/info/b81ba7a4bc>
> those are used to build the sqlite3 Tcl extension, apparently
> not what you are after. It works fine for me.
>
> Anyway, I just uploaded the SQLite 3.8.4.1 build for Cygwin, it
> just depends on the mirrors how fast they pick it up.
>
> Regards,
>  Jan Nijtmans
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3.exe file not produced by sqlite-autoconf-3080400.tar.gz on Cygwin

[Keith Christian]

Hick, Tim,

Thanks, I thought it was something like that.  Couldn't remember the
exact gcc switches as to whether the debug code may have been included
or not in Richard's command line, which was my first thought.  But at
least with Richard's command line I have a working, up to date sqlite3
executable!

Will have to troubleshoot the details of the makefile changes between
the autoconf version from 3.8.3 to 3.8.4, to see why on this Cygwin
environment a "sqlite3.exe" wasn't created.  That will be awhile,
pretty busy at $WORK (not a bad thing.)


Keith

On Tue, Mar 11, 2014 at 10:23 AM, Tim Streater <t...@clothears.org.uk> wrote:
> On 11 Mar 2014 at 16:11, Keith Christian <keith1christ...@gmail.com> wrote:
>
>> The resulting file is quite large, almost 14 times the size of the
>> sqlite3 version 3.8.3 packages with Cygwin:
>>
>> ls -l /usr/bin/sqlite3.exe
>> -rwxr-xr-x 1 kchris Domain Users 60957 Feb  4 04:45 /usr/bin/sqlite3.exe
>>
>> ls -l ./sqlite3.exe
>> -rwxr-xr-x 1 kchris Domain Users 845353 Mar 11 10:05 ./sqlite3.exe
>
> That's about what I get under OS X if I link to an amalgamation instead of 
> the OS-supplied .dylib. The advantage is that you know which library you are 
> using, unlike if you dynamically link to a library. The application uses just 
> as much memory once linked, either way. These days the extra disk space used 
> for the app is of no consequence (unless you have millions of apps, I 
> suppose).
>
> --
> Cheers  --  Tim
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3.exe file not produced by sqlite-autoconf-3080400.tar.gz on Cygwin

[Keith Christian]

Richard,

Thanks for the reply.  Your instructions produced a working
sqlite3.exe in the Cygwin environment, using
sqlite-amalgamation-3080401.zip.

The resulting file is quite large, almost 14 times the size of the
sqlite3 version 3.8.3 packages with Cygwin:


ls -l /usr/bin/sqlite3.exe
-rwxr-xr-x 1 kchris Domain Users 60957 Feb  4 04:45 /usr/bin/sqlite3.exe

ls -l ./sqlite3.exe
-rwxr-xr-x 1 kchris Domain Users 845353 Mar 11 10:05 ./sqlite3.exe


But it works!

./sqlite3.exe
SQLite version 3.8.4.1 2014-03-11 15:27:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .quit


Keith



On Tue, Mar 11, 2014 at 8:30 AM, Richard Hipp <d...@sqlite.org> wrote:
> On Tue, Mar 11, 2014 at 9:46 AM, Keith Christian
> <keith1christ...@gmail.com>wrote:
>
>> A few days ago, I successfully compiled the previous version of the
>> autoconf tar package (sqlite-autoconf-3080300.tar.gz) and a
>> sqlite3.exe file was produced on a Cygwin environment.
>>
>> This morning, I downloaded sqlite-autoconf-3080400.tar.gz, unpacked,
>> ran 'make clean' and 'make', but no sqlite3.exe file was produced.
>>
>
>
> I'm sorry you are having trouble.
>
> I don't know what is wrong because everything works fine when I try it.
>
> As an alternative, consider downloading sqlite-amalgamation-3080400.zip and
> then running:
>
>  gcc -o sqlite3.exe -I. sqlite3.c shell.c
>
>
>
>
>
>>
>> I believe the issue is in some changes in the Makefiles produced
>> between 3080300 and 3080400.
>>
>> Makefile.am and Makefile.in are identical between 3080300 and 3080400.
>>
>> The cygwin environment on the machine has not been updated since
>> 3080300 was compiled on 7 Mar.
>>
>> See the output of 'wdiff' below.  Recent versions of the VIM editor
>> have a nice syntax mode for wdiff, copy the output below into VIM and
>> then type "syn on" and "set filetype=wdiff" at the colon prompt.
>>
>>
>>
>> wdiff -s ../../sqlite-autoconf-3080300/tea/Makefile
>> ../../sqlite-autoconf-3080400/tea/Makefile|grep "\[-.*-\]"|sed 'G;G'
>>
>>
>> PKG_LIB_FILE= [-libsqlite3.8.3.dll-] {+libsqlite3.8.4.dll+}
>>
>>
>> PKG_STUB_LIB_FILE = [-libsqlitestub3.8.3.a-] {+libsqlitestub3.8.4.a+}
>>
>>
>> INSTALL = [-/usr/bin/install-] {+$(SHELL)
>> $(srcdir)/tclconfig/install-sh+} -c
>>
>>
>> PACKAGE_VERSION = [-3.8.3-] {+3.8.4+}
>>
>>
>> #DEFS   = $(TCL_DEFS) -DPACKAGE_NAME=\"sqlite\"
>> -DPACKAGE_TARNAME=\"sqlite\" [--DPACKAGE_VERSION=\"3.8.3\"-]
>> {+-DPACKAGE_VERSION=\"3.8.4\"+} -DPACKAGE_STRING=\"sqlite\ [-3.8.3\"-]
>> {+3.8.4\"+} -DPACKAGE_BUGREPORT=\"\" -DPACKAGE_URL=\"\"
>> {+-DBUILD_sqlite=/\*\*/+} -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
>> -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
>> -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1
>> -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DNO_VALUES_H=1 -DHAVE_LIMITS_H=1
>> -DHAVE_SYS_PARAM_H=1 -DUSE_SYSTEM_SQLITE=1 -DUSE_THREAD_ALLOC=1
>> -D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1
>> -DMODULE_SCOPE=extern [--DNO_VIZ=1-] {+-DHAVE_NO_SEH=1
>> -DHAVE_CAST_TO_UNION=1+} -DTCL_WIDE_INT_TYPE=long\ long
>> -DUSE_TCL_STUBS=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1
>> $(PKG_CFLAGS)
>>
>>
>> DEFS= -DPACKAGE_NAME=\"sqlite\"
>> -DPACKAGE_TARNAME=\"sqlite\" [--DPACKAGE_VERSION=\"3.8.3\"-]
>> {+-DPACKAGE_VERSION=\"3.8.4\"+} -DPACKAGE_STRING=\"sqlite\ [-3.8.3\"-]
>> {+3.8.4\"+} -DPACKAGE_BUGREPORT=\"\" -DPACKAGE_URL=\"\"
>> {+-DBUILD_sqlite=/\*\*/+} -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
>> -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
>> -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1
>> -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DNO_VALUES_H=1 -DHAVE_LIMITS_H=1
>> -DHAVE_SYS_PARAM_H=1 -DUSE_SYSTEM_SQLITE=1 -DUSE_THREAD_ALLOC=1
>> -D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1
>> -DMODULE_SCOPE=extern [--DNO_VIZ=1-] {+-DHAVE_NO_SEH=1
>> -DHAVE_CAST_TO_UNION=1+} -DTCL_WIDE_INT_TYPE=long\ long
>> -DUSE_TCL_STUBS=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1
>> $(PKG_CFLAGS)
>>
>>
>>
>> Keith
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3.exe file not produced by sqlite-autoconf-3080400.tar.gz on Cygwin

[Keith Christian]

A few days ago, I successfully compiled the previous version of the
autoconf tar package (sqlite-autoconf-3080300.tar.gz) and a
sqlite3.exe file was produced on a Cygwin environment.

This morning, I downloaded sqlite-autoconf-3080400.tar.gz, unpacked,
ran 'make clean' and 'make', but no sqlite3.exe file was produced.

I believe the issue is in some changes in the Makefiles produced
between 3080300 and 3080400.

Makefile.am and Makefile.in are identical between 3080300 and 3080400.

The cygwin environment on the machine has not been updated since
3080300 was compiled on 7 Mar.

See the output of 'wdiff' below.  Recent versions of the VIM editor
have a nice syntax mode for wdiff, copy the output below into VIM and
then type "syn on" and "set filetype=wdiff" at the colon prompt.



wdiff -s ../../sqlite-autoconf-3080300/tea/Makefile
../../sqlite-autoconf-3080400/tea/Makefile|grep "\[-.*-\]"|sed 'G;G'


PKG_LIB_FILE= [-libsqlite3.8.3.dll-] {+libsqlite3.8.4.dll+}


PKG_STUB_LIB_FILE = [-libsqlitestub3.8.3.a-] {+libsqlitestub3.8.4.a+}


INSTALL = [-/usr/bin/install-] {+$(SHELL)
$(srcdir)/tclconfig/install-sh+} -c


PACKAGE_VERSION = [-3.8.3-] {+3.8.4+}


#DEFS   = $(TCL_DEFS) -DPACKAGE_NAME=\"sqlite\"
-DPACKAGE_TARNAME=\"sqlite\" [--DPACKAGE_VERSION=\"3.8.3\"-]
{+-DPACKAGE_VERSION=\"3.8.4\"+} -DPACKAGE_STRING=\"sqlite\ [-3.8.3\"-]
{+3.8.4\"+} -DPACKAGE_BUGREPORT=\"\" -DPACKAGE_URL=\"\"
{+-DBUILD_sqlite=/\*\*/+} -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
-DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
-DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1
-DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DNO_VALUES_H=1 -DHAVE_LIMITS_H=1
-DHAVE_SYS_PARAM_H=1 -DUSE_SYSTEM_SQLITE=1 -DUSE_THREAD_ALLOC=1
-D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1
-DMODULE_SCOPE=extern [--DNO_VIZ=1-] {+-DHAVE_NO_SEH=1
-DHAVE_CAST_TO_UNION=1+} -DTCL_WIDE_INT_TYPE=long\ long
-DUSE_TCL_STUBS=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1
$(PKG_CFLAGS)


DEFS= -DPACKAGE_NAME=\"sqlite\"
-DPACKAGE_TARNAME=\"sqlite\" [--DPACKAGE_VERSION=\"3.8.3\"-]
{+-DPACKAGE_VERSION=\"3.8.4\"+} -DPACKAGE_STRING=\"sqlite\ [-3.8.3\"-]
{+3.8.4\"+} -DPACKAGE_BUGREPORT=\"\" -DPACKAGE_URL=\"\"
{+-DBUILD_sqlite=/\*\*/+} -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
-DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
-DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1
-DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DNO_VALUES_H=1 -DHAVE_LIMITS_H=1
-DHAVE_SYS_PARAM_H=1 -DUSE_SYSTEM_SQLITE=1 -DUSE_THREAD_ALLOC=1
-D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1
-DMODULE_SCOPE=extern [--DNO_VIZ=1-] {+-DHAVE_NO_SEH=1
-DHAVE_CAST_TO_UNION=1+} -DTCL_WIDE_INT_TYPE=long\ long
-DUSE_TCL_STUBS=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1
$(PKG_CFLAGS)



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

Re: [sqlite] Compiling libtclsqlite3.so on Cygwin

[Keith Christian]

OK Warren, you've helped greatly, thanks again for the assistance, will
check other forums if the shared object / dll problems continue.

Keith


On Tue, May 14, 2013 at 10:53 AM, Warren Young <war...@etr-usa.com> wrote:

> On 5/14/2013 09:14, Keith Christian wrote:
>
>>
>> couldn't load file "./tclsqlite3.o": Exec format error
>>
>
> From the Tcl manual: 
> http://tmml.sourceforge.net/**doc/tcl/load.html<http://tmml.sourceforge.net/doc/tcl/load.html>
>
> "...such as a .so file under Solaris or a DLL under Windows."
>
> Not *.o!
>
> My Tcl is awfully rusty, but I managed to get it to load with:
>
> load libsqlite3.7.16.2.dll "SQLite3"
>
> I have no idea what to do with it having loaded it.  (Again, the rust.)
>
> You're rapidly getting off-topic here, though.  There's a good chance that
> if you have further questions that they belong on a Cygwin or Tcl forum.
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling libtclsqlite3.so on Cygwin

[Keith Christian]

Warren, thanks, that built the tclsqlite3.o correctly, and a libsqlite3
file also:

-rw-r--r--  1 kchristian Domain Users  39279 May 14 08:47 tclsqlite3.o
-rwxr-xr-x  1 kchristian Domain Users 173214 May 14 08:47
libsqlite3.7.16.2.dll



Another question, with a small test script, this error is output script:

couldn't load file "./tclsqlite3.o": Exec format error
while executing
"load ./tclsqlite3.o"
(file "./test_tclsqlite3.tcl" line 2)


=TEST SCRIPT START==
#!/usr/bin/tclsh
load ./tclsqlite3.o

sqlite3 db1 ./tcl_interface.db

db1 eval {CREATE TABLE IF NOT EXISTS t1(a int, b text)}
db1 eval {INSERT INTO t1 VALUES('1','hello')}
db1 eval {INSERT INTO t1 VALUES('2','goodbye')}
db1 eval {INSERT INTO t1 VALUES('3','howdy!')}

set x [db1 eval {SELECT * FROM t1 ORDER BY a}]

db1 eval {SELECT * FROM t1 ORDER BY a} values {
parray values
puts ""
}
=TEST SCRIPT END


Any suggestions for this one?  (Had to chmod u+x tclsqlite3.o, otherwise
got: 'couldn't load file "./tclsqlite3.o": Permission denied' error.)

Thanks again.





On Mon, May 13, 2013 at 8:10 PM, Warren Young <war...@etr-usa.com> wrote:

> On 5/13/2013 16:36, Keith Christian wrote:
>
>> gcc -o libtclsqlite3.so -shared tea/generic/tclsqlite3.c -lpthread -ldl
>> -ltcl
>>
>
> Don't build it that way.  It appears that the TEA build system sees Cygwin
> and thinks "oh, this is Windows, so it must be VC++ or MinGW". That
> prevents it from linking to the Cygwin SQLite library.
>
> This seems to do the right thing:
>
> ... at the top-level of the source tree:
> $ cd tea
> $ ./configure --with-system-sqlite
> $ make
>
> Do a "make clean" before the last step if you have any *.o files laying
> around.  The shipped build system isn't smart enough to rebuild everything
> when you reconfigure with a very different set of compile and link options.
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compiling libtclsqlite3.so on Cygwin

[Keith Christian]

I have the latest version of Cygwin installed (CYGWIN_NT-6.1-WOW64
1.7.18(0.263/5/3) 2013-04-19 10:39 i686 Cygwin) and the latest autoconf
archive (sqlite-autoconf-3071602.tar.gz) because it had the required
tclsqlite3.c file in order to build the libtclsqlite3.so shared object.

I ran the configure / make commands from the top level subdirectory
(sqlite-autoconf-3071602.)

Configure / make were successful.



Here is the command line attempted for compiling tclsqlite3.c, and the
results:

gcc -o libtclsqlite3.so -shared tea/generic/tclsqlite3.c -lpthread -ldl
-ltcl

/tmp/ccnyhcg8.o:tclsqlite3.c:(.text+0x7fc2b): undefined reference to
`__imp__Sqlite3_Init'
/tmp/ccnyhcg8.o:tclsqlite3.c:(.text+0x7fc54): undefined reference to
`__imp__Sqlite3_Init'
/tmp/ccnyhcg8.o:tclsqlite3.c:(.text+0x7fc69): undefined reference to
`__imp__Sqlite3_Init'
/usr/lib/gcc/i686-pc-cygwin/4.5.3/../../../../i686-pc-cygwin/bin/ld:
/tmp/ccnyhcg8.o: bad reloc address 0x100 in section `.data'
collect2: ld returned 1 exit status



I plan to use the libtclsqlite3.so object with the 3.7.16.2 version of
Sqlite3 that ships with Cygwin (same version as here.)

Any ideas of how to get tclsqlite3.c to compile and produce
libtclsqlite3.so?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select statements using date function such as date('-1 day')

[Keith Christian]

On Fri, Dec 28, 2012 at 12:26 PM, Igor Tandetnik  wrote:
> You forgot to tell date() function which date it's supposed to subtract one
> day from. Make it date('now', '-1 day')


Thanks, Igor, that fixes the problem, both of these work!

select * from general where foodate=date('now', '-1 day') limit 10;

select * from general where foodate=date('now', '-2 day') limit 10;


I appreciate the quick response.


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

[sqlite] Select statements using date function such as date('-1 day')

[Keith Christian]

Running SQLite version 3.7.13 2012-06-11 02:05:22.

A table has a date field with entries in this range: 2012-10-25
through 2012-12-27.

2735080|2012-12-27
2735081|2012-12-27
2735083|2012-12-27
2735084|2012-12-27
2735085|2012-12-27
2735086|2012-12-27
2735087|2012-12-27
2735088|2012-12-27
2735089|2012-12-27

Is it possible to use the 'date' function to select a date using the
date function as below?


select * from general where foobar_date=date('-1 day');
E.g. return foobar_date of 2012-12-27 if today's date is 2012-12-28.
select * from general where foobar_date=date('-2 days');
 E.g. return foobar_date of 2012-12-26 if today's date is 2012-12-28.

Neither of these return any output.  Date ranges in the table start at
2012-10-25 and go until 2012-12-27, so I was hoping one of the two
queries above would return something.

Or is it necessary to explicitly state the value desired, e.g.

select * from general where foobar_date='2012-12-27';


Thanks for any suggestions.

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

Re: [sqlite] sqlite3_analyzer

[Keith Christian]

Richard, thank you for the details of the compilation instructions.
I'll give that a try.

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

Re: [sqlite] sqlite3_analyzer

[Keith Christian]

I found the links mentioned above at the bottom of the Downloads page,
and went to this site:

http://www2.sqlite.org/cgi/src

I logged in to the SQLite timeline using anonymous and a random
password as instructed.  Once there, I downloaded
SQLite-256cdbdc810cae23.tar.gz, 3.8 mb in size.

After extracting SQLite-256cdbdc810cae23.tar.gz, I could find no
source code for a "sqlite analyzer."

All I found was this reference:

grep -i analyzer SQLite-256cdbdc810cae23/Make*
SQLite-256cdbdc810cae23/Makefile.in:sqlite3_analyzer.c: sqlite3.c
$(TOP)/src/test_stat.c $(TOP)/src/tclsqlite.c
$(TOP)/tool/spaceanal.tcl

Should the referenced "sqlite3_analyzer.c" file exist in the .tar.gz
file?  Or is it not included in the source?  Should I look elsewhere
for it?

Thanks.

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

Re: [sqlite] Widen output of file field for .databases CLI command

[Keith Christian]

On Thu, Aug 25, 2011 at 6:47 PM, Clark Christensen  wrote:
> Hi Keith,
>
> How about
>
> pragma database_list;
>
>  -Clark


I'll try it!  Thanks!


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

[sqlite] Widen output of file field for .databases CLI command

[Keith Christian]

Is there a parameter that will widen the "file" column when
".databases" is typed at the sqlite> prompt?  Occasionally the
database is several directories deep and the filename is lost or
truncated if the complete path is more than 58 characters wide.

If no parameter exists, could the "file" field be widened?

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

Re: [sqlite] Trapping errors in the TCL interface to sqlite (libtclsqlite3.so)

[Keith Christian]

On Fri, Jun 3, 2011 at 3:01 PM, Simon Slavin  wrote:
> However, there are ways to do many things the dot commands do.  For instance,
>
> http://www.sqlite.org/pragma.html#pragma_database_list

Got it, Simon, this works:

set dblist [db1 eval {pragma database_list;}]
puts ""
puts "$dblist"
puts ""


0 main /home/kchristian/./tcl_interface.db



Thank you very much.

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

Re: [sqlite] Trapping errors in the TCL interface to sqlite (libtclsqlite3.so)

[Keith Christian]

On Fri, Jun 3, 2011 at 2:42 PM, Simon Slavin  wrote:
> Use the form
>
> CREATE TABLE IF NOT EXISTS ...
>
> http://www.sqlite.org/lang_createtable.html


Thank you, Simon.

I have another question about the TCL interface:  What is the syntax
to execute the commands available at the sqlite> prompt?

Suppose I want to run the ".databases" command, usually executed from
the sqlite> prompt.

This throws an error:

db1 eval {.databases}

near "".databases"": syntax error
while executing
"db1 eval {".databases"}"
(file "./sqlite_tcl_so_test.tcl" line 22)


Thanks again.

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

[sqlite] Trapping errors in the TCL interface to sqlite (libtclsqlite3.so)

[Keith Christian]

I've successfully compiled libtclslqite3.so on CentOS 5.5 Linux and
have run the example sqlite3 code from
http://www.sqlite.org/tclsqlite.html.

The TCL script below is almost verbatim from the above web page's examples.

Problem - After the first pass, it obviously attempts to CREATE TABLE
again, even though the table already exists (Lines 22-25 below.)

Is there a function provided by libtclslqite3.so that could either
list the existing tables, or trap this error?  Would it be TCL's
"catch" function?  I'd like the TCL script to continue after throwing
this error.

 1  #!/usr/bin/tclsh
 2  #load ./tclsqlite3.o
 3  load ./libtclsqlite3.so
 4  
 5  sqlite3 db1 ./tcl_interface.db
 6  
 7  db1 eval {CREATE TABLE t1(a int, b text)}
 8  
 9  db1 eval {INSERT INTO t1 VALUES('1','hello')}
10  db1 eval {INSERT INTO t1 VALUES('2','goodbye')}
11  db1 eval {INSERT INTO t1 VALUES('3','howdy!')}
12  
13  set x [db1 eval {SELECT * FROM t1 ORDER BY a}]
14  
15  db1 eval {SELECT * FROM t1 ORDER BY a} values {
16  parray values
17  puts ""
18  }
19  
20  
21  [kchristian@linux1 ~]$ ./sqlite_tcl_so_test.tcl
22  table t1 already exists
23  while executing
24  "db1 eval {CREATE TABLE t1(a int, b text)}"
25  (file "./sqlite_tcl_so_test.tcl" line 8)
26  [kchristian@linux1 ~]$


Thanks!

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

[sqlite] Can't download the gzip archives from the list info page

[Keith Christian]

Hello Sqlite List,

Clicking a Gzip'd Text xxxKB link on the archives page
(http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users) displays
a page with this message:

No such list 2011-june.txt.gz

What is the procedure to obtain the list archives?

Thanks.

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