Re: [sqlite] bug in time() ?

[Doug Currie]

sqlite> select datetime(1080701286,'unixepoch');
2004-03-31 02:48:06
sqlite> select datetime(1080701286,'localtime');
2954147-07-10 07:00:00
sqlite> select datetime(1080701286,'unixepoch','localtime');
2004-03-30 21:48:06
sqlite> select julianday('now');
2453095.66955468
sqlite> select datetime(2453095.66955468,'localtime');
2004-03-30 23:04:09

I think the problem is that unixepoch seconds are being used as Julian
Day Numbers, which they aren't.

e

Tuesday, March 30, 2004, 10:28:37 PM, you wrote:

> Kenneth Lo wrote:
>> I think the time() function is not reporting the correct time. The date part
> is OK. I also note that CVSTrac uses C codes to handle datetime i.e. not relying
> on sqlite. Is there a bug?
>> 
>> Some investigation ...
>> bash-2.05b$ date
>> Wed Mar 31 11:12:03 HKT 2004
>> bash-2.05b$ sqlite
>> SQLite version 2.8.12
>> Enter ".help" for instructions
>> sqlite> SELECT strftime('%s','now');
>> 1080701286
>> sqlite> select datetime(1080701286,'unixepoch');
>> 2004-03-31 02:48:06
>> sqlite> select datetime(1080701286,'localtime');
>> 2954147-07-10 19:59:59
>> sqlite> select datetime('now');
>> 2004-03-31 02:49:07


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Concurrency Proposal

[Doug Currie]

Monday, April 5, 2004, 3:59:50 PM, Christian Smith wrote:

> You don't have a cache of the Shadows papers, by any chance? The URLs
> referenced appear outdated.

http://citeseer.ist.psu.edu/131901.html

I also have a copy of Tatu Yolen's thesis, but I don't (yet) have his
permission to distribute it. Perhaps his web page will come back on
line soon. http://www.cs.hut.fi/~ylo

e

> Cheers,
> Christian


> On Mon, 5 Apr 2004, Doug Currie wrote:

>>Monday, April 5, 2004, 6:13:18 AM, Christian Smith wrote:
>>
>>> On Wed, 31 Mar 2004, Joel Lucsy wrote:
>>
>>>>Just for the record, for Windows machines, only NTFS v5 formatted
>>>>drives/partitions support sparse files. Windows 2000 or better are the
>>>>only ones supporting NTFS v5. This means Win9x and FAT32 drives under
>>>>2000/XP won't be able to use sparse files.
>>
>>> There are bigger problems anyway.
>>
>>> A snapshot of all old data would be required to be maintained for all
>>> active readers, even if a write transaction has finished. [...]
>>> SQLite would need to maintain a snapshot for each reader, with nothing
>>> but the filesystem to use for IPC. A difficult problem.
>>
>>This is *precisely* the problem my Shadow Pager solves.
>>http://www.sqlite.org/cvstrac/wiki?p=BlueSky
>>
>>> A snapshot might be feasible to maintain from within the same
>>> process, but that would limit concurrent readers and writer to the
>>> same process. [...]
>>
>>Indeed my Shadow Pager design is limited to one process, but I believe
>>that it can be extended to multiple processes by maintaining a list of
>>active transactions, each with a list or bitmap of freed pages, in a
>>lockable shared file.
>>
>>> This gives us the benefit of the better performance the undo based journal
>>> gives us, rather than the redo based journal would give us (redo requires
>>> at least 1 read and 2 writes per page, undo requires 1 read and 1 write
>>> per page in commit.)
>>
>>Shadow Pager requires 1 write per commit, regardless of the number of
>>pages written. There are of course other overheads during the
>>transaction, comparable to the above in cost.
>>
>>e
>>
>>>>Christian Smith wrote:
>>>>
>>>>>How about taking advantage of sparse files (most OS support sparse files)
>>>>>for the journal, and saving the redo pages in the new shadow file. Thus
>>>>>the burden of efficiently handling mapping is bumped down to the OS.
>>>>>
>>>>
>>


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Concurrency, MVCC

[Doug Currie]

> D. Richard Hipp wrote:
>> 
>> My thoughts on BlueSky have been added to the wiki page:
>>http://www.sqlite.org/cvstrac/wiki?p=BlueSky

I added some responses; I do not agree with Richard's concerns about
Shadow Paging, and I corrected some mistaken conclusions. I apologize
if my paper was not clear enough in these areas.

Thank you, Richard, for taking the time to review the Shadow Paging
option.

Regards,

e


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Concurrency, MVCC

[Doug Currie]

Thursday, April 15, 2004, 9:16:01 AM, Christian Smith wrote:

> On Wed, 14 Apr 2004, Doug Currie wrote:

>>One way to get table level locking without a great deal of pain is to
>>integrate the shadow paging ideas with BTree management. Rather than
>>using page tables for the shadow pages, use the BTrees themselves.
>>This means that any change to a BTree requires changes along the
>>entire path back to the root so that only free pages are used to store
>>new data, including the BTree itself. Writing the root page(s) of the
>>BTree(s) commits the changes to that table (these tables).

> Actually, this gets my vote. Keeps the pager layer the same,

The pager gets *much* simpler because it doesn't need to make a log
file. The log file is not necessary because writes only go to free
pages.

Well, there would be one write-ahead log. It's needed to prevent
partial updates to the page number pointers to the roots page(s) of
the BTree(s) at commit. This log is created at commit time, and is
much simpler and much smaller than the present log file.

> and only requires a cache of the root btree for each object
> (table/index) in the database to be maintained on a per-transaction
> basis

Yes, you need to cache the page number of each BTree root at
transaction start.

You'd also need a forest of free pages organized by transaction so
they can be freed at the right time (when the oldest read-transaction
that can reference them has completed).

> , reducing the complications of what to do under memory pressure
> when pages are spilled from the cache as we should be able to keep
> them in memory all the time.

Yes.

> Committing of a transaction would then be an atomic update root btree page
> number in the catalog table.

Yes, atomically for all the BTrees modified. This is probably a single
page of data (4 to 8 bytes of root page number per BTree, i.e., per
table and per index). Well, I usually assume fairly large pages
compared with SQLite's default of 1K. Using larger pages also
decreases the depth of the BTree which reduces the number of pages
written.

This design works well. It has the advantage (compared with shadow
pager) that reads are not burdened with page table indirection. It has
the potential disadvantage (compared with SQLite 2.8) that small
writes can modify several pages (based on the depth of the BTree).

I used this design in a proprietary database in the late 1980s. The
only reason I didn't consider modifying SQLite this way up until now
is that I was anticipating BTree changes for 3.0, so I confined my
efforts to the pager layer.

e


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Re: SQLite version 3 design question: '500'=500?

[Doug Currie]

Firebird 1.5

SQL> CREATE TABLE test1(a VARCHAR(100));
SQL> INSERT INTO test1 VALUES('501');
SQL> INSERT INTO test1 VALUES('  502  ');
SQL> SELECT * FROM test1 WHERE a=501;

A
===
501

SQL> SELECT * FROM test1 WHERE a=502;

A
===
  502

SQL> SELECT * FROM test1 WHERE a<'502';

A
===
501
  502

SQL> CREATE TABLE test2(b INTEGER);
SQL> INSERT INTO test2 VALUES(503);
SQL> INSERT INTO test2 VALUES(504);
SQL> SELECT * FROM test2 WHERE b='503';

   B


 503

SQL> SELECT * FROM test2 WHERE b>'503';

   B


 504

e

Thursday, May 13, 2004, 4:42:24 AM, DRH wrote:

> George Ionescu wrote:
>> 
>> However, wanting to test how the engine compares strings and numbers:
>> 
>> SELECT 'match' WHERE '500' = 500;
>> 
>> returns 'match'; also, the following statements return the same result:
>> 
>> SELECT 'match' WHERE '500' = 500;
>> SELECT 'match' WHERE '500' = 499 + 1;
>> 

> Who can tell me what other SQL database engines do with
> the following?

> CREATE TABLE test1(a VARCHAR(100));
> INSERT INTO test1 VALUES('501');
> INSERT INTO test1 VALUES('  502  ');
> SELECT * FROM test1 WHERE a=501;
> SELECT * FROM test1 WHERE a=502;
> SELECT * FROM test1 WHERE a<'502';

> Or how about this:

> CREATE TABLE test2(b INTEGER);
> INSERT INTO test2 VALUES(503);
> INSERT INTO test2 VALUES(504);
> SELECT * FROM test2 WHERE b='503';
> SELECT * FROM test2 WHERE b>'503';




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] SQLite version 3 design question: '500'=500?

[Doug Currie]

Results from Firebird 1.5 (thanks for the syntax, Andrew)...

SQL> select '500' = 500;
Statement failed, SQLCODE = -104

Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, char 14
-=
SQL> select 500 = '500';
Statement failed, SQLCODE = -104

Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, char 12
-=
SQL> select 500 = 500;
Statement failed, SQLCODE = -104

Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, char 12
-=
SQL> select  case when 5 = '5' then 1 else 0 end as bool from t1;

BOOL


   1

SQL> select  case when '5' = 5 then 1 else 0 end as bool from t1;

BOOL


   1

e


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Use of alloca() in SQLite sources

[Doug Currie]

> If I make use of alloca() in SQLite version 3, will this
> cause any extreme hardships?  Who is using a C compiler to
> build SQLite that does not support alloca()?

Warning: there are gcc bugs in the x86 optimizer related to alloca(). E.g.,
https://sourceforge.net/tracker/?func=detail=102435=940940_id=2435

Avoid -O3 and alloca().

e



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] sqlite 3.0.0 build issues

[Doug Currie]

There are several build options in the configure/make of sqlite3 that
are obsolete, and others that are missing.

ENCODING is obsolete since it is specified in the open calls now.
There are still several references to it, though, in the make and
autoconf files, and these should all be removed.

INMEMORYDB and SQLITE_OMIT_INMEMORYDB are obsolete since the red-black
tree implementation is now gone. Again, references to these should be
removed from the make and autoconf files.

INCOREFLAGS and in particular TEMP_STORE are in limbo. The values are
set but never used. E.g., INCOREFLAGS is set in Makefile but never
used there. TEMP_STORE is initialized in sqliteInt.h and documented in
several comments, but is never used in the code.

I suspect that the intention is that sqlite3BtreeFactory use
TEMP_STORE in certain circumstances, but presently it uses neither
TEMP_STORE nor the db->temp_store set by pragma. In fact, even the
flag BTREE_MEMORY which is set by sqlite3BtreeFactory when the
filename is empty is ignored by sqlite3BtreeOpen.

The THREADSAFE macro is used in all three os_xxx.c files, but is not
supported by the autoconf files (configure.ac and Makefile.in). It
would be really nice to add this when configure.ac is updated next.

It would also be nice to change configure.ac so that only one of
-DOS_WIN=1 or -DOS_UNIX=1 is specified in TARGET_CFLAGS. Only one is
necessary (the other will be set in os.h) and it would make the
console dribble from make much easier to read.

There are several SQLITE_OMIT_ macros that should also be supported by
the autoconf files.

Disclaimer: I don't use autoconf, but I'd be willing to help with the
source updates if somebody familiar with autoconf was willing to do
the configure (+ aclocal.m4 etc.) file generation and linux testing.

e



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] sqlite 3.0.0 build issues

[Doug Currie]

Replying to my own message...

I bit the bullet and installed autoconf under msys/mingw. Using this
tool I updated configure.ac and Makefile.in to address some of the
build issues identified below -- everything but the OMIT macros. Of
course, I have not modified the sqlite sources to implement the
TEMP_STORE features.

The configure file ends up with a few unrelated changes (hey, it's
autoconf, what would you expect?) but they look minor. If this causes
anyone problems, I apologize in advance. Please let me know if you
think I have broken something you depend upon.

The new options are called
  --enable-threadsafe  Support threadsafe operation
  --enable-tempstore   Use an in-ram database for temporary tables 
(never,no,yes,always)

E.g.,

../sqlite/configure --enable-tempstore=no --enable-threadsafe

e

Sunday, June 20, 2004, 12:36:56 AM, I wrote:

> There are several build options in the configure/make of sqlite3 that
> are obsolete, and others that are missing.

> ENCODING is obsolete since it is specified in the open calls now.
> There are still several references to it, though, in the make and
> autoconf files, and these should all be removed.

> INMEMORYDB and SQLITE_OMIT_INMEMORYDB are obsolete since the red-black
> tree implementation is now gone. Again, references to these should be
> removed from the make and autoconf files.

> INCOREFLAGS and in particular TEMP_STORE are in limbo. The values are
> set but never used. E.g., INCOREFLAGS is set in Makefile but never
> used there. TEMP_STORE is initialized in sqliteInt.h and documented in
> several comments, but is never used in the code.

> I suspect that the intention is that sqlite3BtreeFactory use
> TEMP_STORE in certain circumstances, but presently it uses neither
> TEMP_STORE nor the db->temp_store set by pragma. In fact, even the
> flag BTREE_MEMORY which is set by sqlite3BtreeFactory when the
> filename is empty is ignored by sqlite3BtreeOpen.

> The THREADSAFE macro is used in all three os_xxx.c files, but is not
> supported by the autoconf files (configure.ac and Makefile.in). It
> would be really nice to add this when configure.ac is updated next.

> It would also be nice to change configure.ac so that only one of
> -DOS_WIN=1 or -DOS_UNIX=1 is specified in TARGET_CFLAGS. Only one is
> necessary (the other will be set in os.h) and it would make the
> console dribble from make much easier to read.

> There are several SQLITE_OMIT_ macros that should also be supported by
> the autoconf files.

> Disclaimer: I don't use autoconf, but I'd be willing to help with the
> source updates if somebody familiar with autoconf was willing to do
> the configure (+ aclocal.m4 etc.) file generation and linux testing.

> e



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] SQLite v3.0 - compiling under Windows

[Doug Currie]

> The '%lld' is used by SQLite in non debugging code.
> For example: vdbemem.c function sqlite3VdbeMemStringify()

This is a libc/runtime issue that is common to both VC and gcc running
on windows (i.e., mingw, but perhaps not cygwin). Windows needs the
printf format spec '%I64d' instead of '%lld'.

Since my mingw build passes all tests, it appears there's a coverage
hole to be plugged in the regression tests here.

e



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Version 3.0.0 ALPHA now available

[Doug Currie]

Friday, June 18, 2004, 8:33:26 AM, DRH wrote:
> A file format and API freeze is scheduled for July 1.  Users
> are encouraged to evaluate this release and make suggestions
> on how to improve the interface prior to that date.

I ported Tiago Dionizio's LuaSQLite to version 3.0.1. Here are a
few SQLite C API comments based on that experience...

0. Overall the new API is easy to use and understand; it is a logical
extension to the version 2 API. Porting was straightforward and
offered new opportunities for BLOBs and stronger dynamic data typing.

1. Windows DLLs will need sqlite3_libversion() function.

2. Loss of the sqlite_error_string(errnum) function complicates things
a bit. Before the change a glue library could defer capturing the
error string when an error occurred. In fact, it could simply pass the
error code back to the caller, and let the caller decide if a string
was necessary. Now the onus is on the library to capture the error
string immediately. Two reasons: the caller may perform db operations
before inspecting the returned result code and deciding the string is
desired, or the library may need to do some db cleanup functions
(e.g., sqlite3_finalize) after an error occurs, and the caller will
want the string associated with the original call, not the cleanup.

3. It would be convenient to be able to get the size of an integer
before calling one of sqlite3_column_int sqlite3_column_int64 or
sqlite3_column_double. Of course, the glue library can do this with
sqlite3_column_int64 and some arithmetic; the point is that when
sqlite3_column_type returns SQLITE_INTEGER it is not apparent which of
the three above calls to retrieve the value is optimal. [In the case
of Lua the only choice is double or text; asking sqlite for a double
may lose precision if the integer is larger than 52 bits; asking for
text leads to excessive type conversions on the Lua side.]

4. It is odd that sqlite3_exec returns an error message string (well I
guess it is legacy code). It triggers the only use of sqlite3_free in
the glue library just to handle this string. [The library doesn't use
sqlite3_mprintf() or sqlite3_vmprintf() preferring
sqlite3_bind_xxx().] Note that the comment in the source above
sqlite3_free is incorrect w.r.t. sqlite3_open.

e


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] sqlite-devel-2.8.14/3.0.1 conflict

[Doug Currie]

> Uffe Jakobsen wrote:
>> 
>> Found a conflict between the sqlite-devel-2.8.14-1.rpm and
>> sqlite-devel-3.0.1-1.rpm
>> 
>> The file /usr/lib/pkgconfig/sqlite.pc exists in both packages.
>> 
>> Shouldn't it be named /usr/lib/pkgconfig/sqlite3.pc for the sqlite-3.0.1
>> package ???
>> 

> Who knows how to fix this and can send me a patch?

I have updated CVS with the required patch.

Richard: you can move sqlite.pc.in to the attic; it has been replaced
with sqlite3.pc.in ... and configure.ac and configure are updated
accordingly.

e

Re: [sqlite] cross compile under cygwin for an ARM target

[Doug Currie]

Monday, July 19, 2004, 1:41:27 PM, Jeff Dever wrote:

> I'm running cygwin on WindowsXP and need to compile a sqlite library for
> use with our ARM embedded system which uses the eCOS.  I have the 3.0.2
> sources to build from and am using the command:
> configure --host=arm
 
> After running make, the gcc cross compiler seems to be invoked
> correctly, but it errors out with the following:
> In file included from /usr/include/sys/types.h:364,
>  from /usr/include/stdio.h:46,
>  from ../src/sqliteInt.h:23,
>  from ../src/date.c:51:
> /usr/include/cygwin/types.h:29: conflicting types for `off_t'
> ../src/os_win.h:25: previous declaration of `off_t'
> make: *** [date.lo] Error 1
 
There is no eCos/ARM support in the standard distribution. The file
../src/os_win.h contains the definitions for a Windows target. You
will need to make a similar set of files for eCos and include it in
the build instead of os_win.h and os_win.c. You will also need to
modify the configure.ac script to handle eCos as well as UNIX and
Windows targets if you want support from autoconf.

e

Re: [sqlite] sqluite3.dll

[Doug Currie]

> the following exports are missing from sqlite3.def (for windows):

> sqlite3_version

Since this is DATA there are some issues around how to add this.
Various tools expect different things. I would prefer to see the
sqlite3_libversion function added to the C API.

> sqlite3_get_auxdata
> sqlite3_set_auxdata

These have been added to sqlite3.def in cvs.

e

Re: [sqlite] Versions 2.8.15 and 3.0.3 available

[Doug Currie]

Monday, July 26, 2004, 12:33:41 PM, Roger Reghin wrote:

> I also use EMPTY_RESULT_CALLBACKS for the same reason Nuno does. And my
> software also needs FULL_COLUMN_NAMES as well. So, no 3.x for me... =(

In 3.x column names are available as soon as the query is prepared.
See the C API reference at
http://www.sqlite.org/capi3ref.html#sqlite3_column_name

> const char *sqlite3_column_name(sqlite3_stmt*,int);
> const void *sqlite3_column_name16(sqlite3_stmt*,int);
> 
> The first parameter is a prepared SQL statement. This function returns
> the column heading for the Nth column of that statement, where N is
> the second function parameter. The string returned is UTF-8 for
> sqlite3_column_name() and UTF-16 for sqlite3_column_name16().

In other words, there is no need for the pragma.

e

> - Original Message - 
> From: "Nuno Lucas" <[EMAIL PROTECTED]>
> To: "sqlite" <[EMAIL PROTECTED]>
> Sent: Monday, July 26, 2004 12:33 PM
> Subject: Re: [sqlite] Versions 2.8.15 and 3.0.3 available


>> D. Richard Hipp, dando pulos de alegria, escreveu :
>> > The following pragmas are scheduled to be removed in the future:
>> >
>> > EMPTY_RESULT_CALLBACKS
>> > FULL_COLUMN_NAMES
>> > SHORT_COLUMN_NAMES
>> > COUNT_CHANGES
>> >
>>
>> What would be the equivalent to EMPTY_RESULT_CALLBACKS=TRUE then?
>>
>> I use it to get the column names list to display in a grid, even when
>> the result is empty. I think it is better to the user to see them, even
>> if no results are present.
>>
>> Regards,
>> ~Nuno Lucas
>>
>>

Re: [sqlite] Versions 2.8.15 and 3.0.3 available

[Doug Currie]

Monday, July 26, 2004, 5:46:48 PM, Nuno Lucas wrote:
> Doug Currie, dando pulos de alegria, escreveu :
>> In 3.x column names are available as soon as the query is prepared.
>> See the C API reference at
>> http://www.sqlite.org/capi3ref.html#sqlite3_column_name

> This wasn't implemented in 3.0.2, right? (I didn't test 3.0.3 yet)

> I believe I tried that (exactly because I understood that from the docs)
> but found it didn't work for empty tables.

Please try it again; I just tried it with an sqlite3 I built on June
24, and it is working...

Lua 5.0.2  Copyright (C) 1994-2004 Tecgraf, PUC-Rio
> require"sqlite3"
> db=sqlite.open"foo.db"
> db:exec"create table baz (a,b,c);"
> vm=db:compile"select * from baz;"
> t=vm:get_names()
> for i,n in pairs( t) do print (i,n) end
1   a
2   b
3   c
n   3

e

Re: [sqlite] LEMON Examples

[Doug Currie]

Monday, August 2, 2004, 12:49:50 AM, John Cohen wrote:

> My problem is the fact I cannot use the start token on the right hand of the
> rule.  [...]

> How can I get this to accept things such as:
> 5 + 5 + 5
> 5 + 6
> ?

[I have never used lemon, but perhaps something like...]

expr := term | ...
term := int | int PLUS term | int MINUS term

e

Re: [sqlite] New SQL Function.

[Doug Currie]

Wednesday, August 18, 2004, 1:45:43 PM, Federico  wrote:

> [...]

> gcc -g -O2 -DTHREADSAFE=1 -DOS_UNIX=1 -DOS_WIN=0 -DHAVE_USLEEP=1 -I.
> -I../sqlite/src -DHAVE_READLINE=0 
> -o .libs/sqlite ../sqlite/src/shell.c  ./.libs/libsqlite.so
> ./.libs/libsqlite.so: undefined reference to `sqrt'
> collect2: ld returned 1 exit status
> make: *** [sqlite] Error 1

You must link with a math library that includes sqrt.

Perhaps adding -lm (assuming you have libm.a and it has sqrt) to the
end of the TCC or LTLINK lines in the Makefile will help.

e

Re: [sqlite] host parameters / bind variables - further workouts

[Doug Currie]

Earlier I said (to Dennis and the list):
> I still have trouble running the tests (error 128 from msys at odd
> times that may be tcl subst related) but at least you can build
> testfixture and run some tests.

I have also reported privately to DRH a problem running tests
bigrow-2.2 & bigrow-2.3

Since upgrading from gcc 3.4.0 to gcc 3.4.1 all of these problems seem
to have gone away.

e

Re: [sqlite] trying to compile SQLite

[Doug Currie]

Tuesday, August 31, 2004, 5:40:15 PM, Dennis Cote wrote:

> I have also filed a ticket with attached patches to have the SQLite
> makefiles (both sqlite 2 and 3) produce GCC compatible import libraries for
> sqlite.dll in addition to the Borland and MSVC import libraries.

> So now you can build applications statically linked to libsqlite.a or
> dynamically linked to sqlite.dll through libsqlitedll.a

GCC doesn't need libsqlitedll.a -- you can just link to sqlite.dll and
gcc understands what to do.

e

Re: [sqlite] Shadow Pager

[Doug Currie]

Wednesday, September 15, 2004, 9:36:00 PM, sankarshana rao wrote:

> Has anyone implemented the shadow pager for sqlite???
> Any info regarding this will be very helpful..

I began an implementation last winter, and set it aside once the
sqlite3 effort was announced. Presently I am busy with other things,
and don't know when/if I will return to it.

e

Re: [sqlite] sqlite3 DEF file

[Doug Currie]

Monday, September 20, 2004, 5:11:48 AM, Mike wrote:

> this file is incomplete, and has been for some time. here is the correct
> version. would somebody check it in, since I dont have CVS access handy:

The only additional symbol I see is sqlite3_version which is not a
function. The sqlite3_libversion symbol is a function, and provides
access to the version info.

Accessing data directly from a DLL is fraught with peril since
different development environments have different rules about the
levels of indirection to the data. So, we use functional access only
to sqlite.

e

Re: [sqlite] Problems compiling SQLite 3.0.7 with MinGW

[Doug Currie]

Tuesday, September 21, 2004, 6:04:53 AM, Peter wrote:

> Hello all, I've been using MinGW to compile SQLite for
> over a year already and encountered a problem I've never
> seen before when trying to compile SQLite 3.0.7.

There is a problem in the Makefile in 3.0.7, fixed in CVS yesterday.

Windows build doesn't need pthread library at all. Just change

LIBPTHREAD=-lpthread

to

LIBPTHREAD=

e


> The error message is as follows:
> $ mingw32-make
> ./libtool --mode=link gcc -g -O2 -DOS_WIN=1 -I. -I../src -DNDEBUG
> -DHAVE_READLINE=1 -I/mingw/include/readline -lpthread \
>  -o sqlite3 ../src/shell.c libsqlite3.la -lreadline
> gcc -g -O2 -DOS_WIN=1 -I. -I../src -DNDEBUG -DHAVE_READLINE=1
> -I/mingw/include/readline -o sqlite3 ../src/shell.c 
> ./.libs/libsqlite3.a -lpthread -lreadline
> c:\msys\mingw\bin\..\lib\gcc\mingw32\3.4.1\..\..\..\..\mingw32\bin\ld.exe: cannot 
> find -lpthread
> collect2: ld returned 1 exit status
> c:\msys\mingw\bin\mingw32-make.exe: *** [sqlite3.exe] Error 1

Re: [sqlite] cross-compile sqlite

[Doug Currie]

Thursday, September 23, 2004, 6:01:40 PM, Daniel wrote:

> [...] I want to use the
> configure-script. Can you give me a hint?
> I tried --prefix=($PREFIX) --host=cris-axis-linux-gnu
> --target=cris-axis-linux-gnu --build=cris-axis-linux-gnu.

--host=cris-axis-linux-gnu ???

> [...]
 
> The last lines from the output:

> [...]
> gcc-cris -isystem
> /var/axis/axis/devboard_82/target/cris-axis-linux-gnu/include
> -mlinux -g -O2 -o temp temp.c
> ./temp >config.h
> /bin/sh: line 1: ./temp: cannot execute binary file

./temp must be compiled for the target, but is run on the host.

This seems unlikely to work in general!

Perhaps you can create a config.h file manually and remove it as a
target of the Makefile.

e

Re: [sqlite] minor problems with 3.08 source package

[Doug Currie]

Friday, October 15, 2004, 11:31:42 AM, Eli wrote:

> I may be in a minority here, but I needed to build sqlite3: threadsafe,
> no tcl, and as a static lib.
> With both 3.06 and 3.08, this required fiddling as the configure script
> does not create libsqlite3.a.

The configure script makes .libs/libsqlite3.a for me, without
arguments or fiddling.

I use
  ../sqlite/configure --enable-threadsafe
to build the threadsafe, no tcl, and as a static lib version.

Can you tell me what fiddling you did to "fix" it?

e

Re: [sqlite] Ticket #924 - bug in mingw

[Doug Currie]

Thursday, September 30, 2004, 10:34:50 AM, DRH wrote:

> The problem reported by ticket #924 appears to be mingw brain damage,
> not a bug in SQLite.  Can somebody who uses a recent version of
> mingw (I'm still using a version from 3 or 4 years ago - a version
> that works) please suggest a reasonable workaround.

>http://www.sqlite.org/cvstrac/tktview?tn=924

Below is some background on the issue.

Summary:

SQLite uses Standard C Library off_t for the size of a file offset.

SQLite on Windows uses the Win32 API for file access, not the
Standard C Library. The Win32 API provides for 64 bit file offsets, so
we take full advantage of that.

MinGW (Win32 API) has off_t as 32 bits for backward binary
Standard C Library compatibility. Recent MinGW runtimes also have
off64_t.

[I sent this earlier to DRH only...]

MinGW has, in 

typedef long off_t;
typedef long long off64_t;

SQLite uses off_t in several places (see below) and it should be 64
bits on Windows (where we don't use the C runtime for I/O; we use
Win32 API functions).

We have hacked around this for a while with a nested conditional in
os_win.h -- but it is ugly and now failing. It is ugly because it is
breaking the C runtime API. It is now failing because...

Newer MinGW runtimes have added 64 bit i/o support, and so more
headers include  to get these off_t type-definitions.

io.h:20:#include   /* To get time_t.  */
process.h:18:#include 
stdio.h:396:#include 
sys\param.h:12:#include 
sys\stat.h:24:#include 
sys\timeb.h:16:#include 
sys\utime.h:21:#include 
wchar.h:50:#include 

The best solution is to transition from using off_t in SQLite to some
local version, as is done with i64 u64 etc.

The next best solution (retaining the ugliness described earlier) is
to make sure that os.h in included before any header that might
include , such as .

e


os.h:182:int sqlite3OsSeek(OsFile*, off_t offset);
os.h:184:int sqlite3OsTruncate(OsFile*, off_t size);
os.h:185:int sqlite3OsFileSize(OsFile*, off_t *pSize);
os_mac.c:347:int sqlite3OsSeek(OsFile *id, off_t offset){
os_mac.c:348:  off_t curSize;
os_mac.c:407:int sqlite3OsTruncate(OsFile *id, off_t nByte){
os_mac.c:423:int sqlite3OsFileSize(OsFile *id, off_t *pSize){
os_mac.h:23:typedef SInt64 off_t;
os_mac.h:25:typedef SInt32 off_t;
os_test.c:152:static off_t osTell(OsTestFile *pFile){
os_test.c:170:off_t filesize;
os_test.c:204:  off_t offset;
os_test.c:298:  off_t offset;   /* The current offset from the start of the file */
os_test.c:299:  off_t end;  /* The byte just past the last byte read */
os_test.c:343:  off_t offset;   /* The current offset from the start of the file */
os_test.c:344:  off_t end;  /* The byte just past the last byte written */
os_test.c:406:int sqlite3OsTruncate(OsFile *id, off_t nByte){
os_test.c:415:int sqlite3OsFileSize(OsFile *id, off_t *pSize){
os_test.c:445:int sqlite3OsSeek(OsFile *id, off_t offset){
os_unix.c:665:int sqlite3OsSeek(OsFile *id, off_t offset){
os_unix.c:736:int sqlite3OsTruncate(OsFile *id, off_t nByte){
os_unix.c:745:int sqlite3OsFileSize(OsFile *id, off_t *pSize){
os_win.c:296:int sqlite3OsSeek(OsFile *id, off_t offset){
os_win.c:332:int sqlite3OsTruncate(OsFile *id, off_t nByte){
os_win.c:345:int sqlite3OsFileSize(OsFile *id, off_t *pSize){
os_win.c:350:  *pSize = (((off_t)upperBits)<<32) + lowerBits;
os_win.h:22:  typedef __int64 off_t;
os_win.h:25:  typedef long long off_t;
pager.c:193:  off_t stmtJSize;/* Size of journal at stmt_begin() */
pager.c:231:  off_t journalOff;   /* Current byte offset in the journal file */
pager.c:232:  off_t journalHdr;   /* Byte offset to previous journal header */
pager.c:233:  off_t stmtHdrOff;   /* First journal header written this 
statement */
pager.c:234:  off_t stmtCksum;/* cksumInit when statement was started */
pager.c:402:  off_t szJ;
pager.c:472:  off_t offset = 0;
pager.c:473:  off_t c = pPager->journalOff;
pager.c:565:  off_t journalSize,
pager.c:874:sqlite3OsSeek(>fd, (pgno-1)*(off_t)pPager->pageSize);
pager.c:914:  off_t nMasterJournal; /* Size of master journal file */
pager.c:999:  sqlite3OsSeek(>fd, pPager->pageSize*(off_t)(pPg->pgno-1));
pager.c:1026:  return sqlite3OsTruncate(>fd, pPager->pageSize*(off_t)nPage);
pager.c:1083:  off_t szJ;   /* Size of the journal file in bytes */
pager.c:1218:  off_t szJ;   /* Size of the full journal */
pager.c:1219:  off_t hdrOff;
pager.c:1227:off_t os_szJ;
pager.c:1569:  off_t n;
pager.c:1855:off_t jSz;
pager.c:1952:sqlite3OsSeek(>fd, (pList->pgno-1)*(off_t)pPager->pageSize);
pager.c:2229:  sqlite3OsSeek(>fd, (pgno-1)*(off_t)pPager->pageSize);
pager.c:2234:off_t fileSize;
test2.c:500:  off_t offset;

-=-

Re: [sqlite] Ticket #924 - bug in mingw

[Doug Currie]

Thursday, September 30, 2004, 10:34:50 AM, DRH wrote:

> The problem reported by ticket #924 appears to be mingw brain damage,
> not a bug in SQLite.  Can somebody who uses a recent version of
> mingw (I'm still using a version from 3 or 4 years ago - a version
> that works) please suggest a reasonable workaround.

>http://www.sqlite.org/cvstrac/tktview?tn=924

Here is a solution that affects 4 files...

In os.h after the include of the platform specific headers, add:

#ifndef OFF_T
#define OFF_T off_t
#endif

In the rest of os.h and in os_win.c pager.c test2.c
replace: off_t
   with: OFF_T

In os_win.h replace:

#if defined(_MSC_VER) || defined(__BORLANDC__)
  typedef __int64 off_t;
#else
# if !defined(_CYGWIN_TYPES_H)
  typedef long long off_t;
#   if defined(__MINGW32__)
# define_OFF_T_
#   endif
# endif
#endif

with:

#if defined(_MSC_VER) || defined(__BORLANDC__)
#define OFF_T __int64
#else
#define OFF_T long long
#endif

or

#if defined(_MSC_VER) || defined(__BORLANDC__)
  typedef __int64 w32_off_t;
#else
  typedef long long w32_off_t;
#endif
#define OFF_T w32_off_t

e

Re: [sqlite] version-info same for sqlite2 and sqlite3

[Doug Currie]

> Both sqlite2 and sqlite3 use --version-info "8:6:8" when creating
> libsqlite.la & libsqlite3.la.   I would think the version # needs to be
> changed?

Is "9:0:0" the right value?
http://sources.redhat.com/autobook/autobook/autobook_91.html

e

Re: [sqlite] sqlite3_temp_directory

[Doug Currie]

Thursday, October 7, 2004, 9:17:28 AM, Kenneth wrote:

> Changing a process wide variable like that seems a bit drastic. I'd be
> concerned about what effect that might have on all the other things running
> under iis.  For example, some of them might be running as different users
> who might not have access to the path.

Your program can set the TMP environment variable for just your
process.

http://msdn.microsoft.com/library/en-us/dllproc/base/setenvironmentvariable.asp?frame=true

> The SetEnvironmentVariable function sets the contents of the
> specified environment variable for the current process.
> 
> BOOL SetEnvironmentVariable(
>   LPCTSTR lpName,
>   LPCTSTR lpValue
> );
> 
> Parameters
> 
> lpName
> [in] Pointer to a null-terminated string that specifies the name of the 
> environment variable. The operating system creates the environment variable if it 
> does not exist and lpValue is not NULL.
> lpValue
> [in] Pointer to a null-terminated string that specifies the contents of the 
> environment variable. An environment variable has a maximum size limit of 32,767 
> characters, including the trailing null terminator.
> 
> If this parameter is NULL, the variable is deleted from the current process's 
> environment.
>
> Return Values
> 
> If the function succeeds, the return value is nonzero.
> 
> If the function fails, the return value is zero. To get extended
> error information, call GetLastError.
>
> Remarks
> 
> This function has no effect on the system environment variables or the environment 
> variables of other processes. 

e

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: 07 October 2004 13:56
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] sqlite3_temp_directory

> MacLeod, Kenneth wrote:
>> Is there any way to change the sqlite3_temp_directory global?  I'm running
>> sqlite under iis and the user doesn't have write access to their temp
>> directory.
>> 

> On windows you can leave sqlite3_temp_directory alone (leave it as NULL)
> and set the TMP environment variable to the directory you want to use.

Re: [sqlite] $ in table and column names

[Doug Currie]

On Jan 10, 2005, at 6:00 PM, D. Richard Hipp wrote:

> A user has reported a bug saying that SQLite does
> not allow the '$' in the middle of indentifiers
> (without quoting).  The bug reports says that
> statements like this:
>
>CREATE TABLE ex$1( col$abc INTEGER );
>
> are legal and work fine in other database engines.
> This seems very odd to me.  Can anybody else
> confirm the use of '$' in the middle of identifier
> names in other database engines?

Firebird (and Interbase) use RDB$ as a prefix for many built-in names.

The Interbase 6 Language Reference says

> When an applications programmer or end user creates a database
> object or refers to it by name, case is unimportant. The following
> limitations on naming database objects must be observed:

> Start each name with an alphabetic character (A–Z or a–z).

> Restrict object names to 31 characters, including dollar signs
> ($), underscores (_), 0 to 9, A to Z, and a to z. Some objects, such
> as constraint names, are restricted to 27 bytes in length.

My reading of the SQL-92 spec leads me to believe that this is an
extension. The spec only seems to require 
followed by  |  where  is (A–Z or a–z). On the other hand, other characters are
permitted based on a  if the
character is "identified as a letter," "syllable," or "ideograph"; the
only preclusion is that the :
> shall not include a  character or other 

e

Re: [sqlite] Version 3.1.0

[Doug Currie]

Friday, January 21, 2005, 1:33:35 PM, DRH wrote:

> Version 3.1.0 (alpha) of SQLite is now available on the website.

Compiling with MinGW MSYS on WinXP...

1. I had to modify my lib/tclConfig.sh to have
  TCL_LIB_SPEC='-L/mingw/lib -ltcl84'
instead of
  TCL_LIB_SPEC=''
or else testfixture wouldn't build. Dunno why my tclConfig.sh was broken.

2. autovacuum-ioerr2.4.0 and onward fail...

autovacuum-ioerr2.4.0...
Error: error copying "test.db" to "backup.db": no such file or directory
autovacuum-ioerr2-4.1.1...
Error: error copying "backup.db" to "test.db": no such file or directory
autovacuum-ioerr2-4.1.2... Ok
autovacuum-ioerr2-4.1.3... Ok
c:\dev\sqlite3\bld\testfixture.exe: invalid command name "db"
while executing
"db close"
(file "../sqlite/test/autovacuum_ioerr2.test" line 189)
invoked from within
"source $testfile"
("foreach" body line 4)
invoked from within
"foreach testfile [lsort -dictionary [glob $testdir/*.test]] {
  set tail [file tail $testfile]
  if {[lsearch -exact $EXCLUDE $tail]>=0} continue
  so..."
(file "../sqlite/test/quick.test" line 44)
make: *** [test] Error 1


e

Re: [sqlite] Version 3.1.0

[Doug Currie]

Friday, January 21, 2005, 5:41:00 PM, Dan wrote:

>> autovacuum-ioerr2.4.0...
>> Error: error copying "test.db" to "backup.db": no such file or
>> directory autovacuum-ioerr2-4.1.1...
>> Error: error copying "backup.db" to "test.db": no such file or
>> directory autovacuum-ioerr2-4.1.2... Ok
>> autovacuum-ioerr2-4.1.3... Ok
>> c:\dev\sqlite3\bld\testfixture.exe: invalid command name "db"
>>while executing
>> "db close"

> I had the same problem. It's caused by a bug in the version of TCL
> that comes with mingw. If you upgrade TCL (I used ActiveTcl 8.4.9)
> it should work.

Thank-you Dan and Dennis... I installed Tcl 8.4.9 from source and now
'make test' yields...

  1 errors out of 18124 tests
  Failures on these tests: bind-4.1

as expected.

e

Re: [sqlite] bogus output for strftime('%s', 'now')

[Doug Currie]

> create table each_transaction(datetime int);

> insert into each_transaction values( datetime('%s', 'now'));

Perhaps you should say

  insert into each_transaction values( strftime('%s', 'now'));

?

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

e
  
> this is how I create table and insert the
> strftime('%s', 'now'). But when I did query with
> sql3_plus. 

> sqlite3::reader reader=con.executereader("select *
> from each_transaction;");
> while(reader.read())
> {cout << reader.getcolname(0) << ": " <<
> reader.getstring(0) << endl;  }


> I get output like this:

> datetime: á " <- invalid 


> Any idea?  coz If I change to
> datetime('now','localtime');
> everything is ok. I only cannot use strftime('%s',
> 'now') for "insert" statement.

> I tried out the pre-compiled command-prompt sqlite but
> doesn't faced with this problem. The sql_lite that i'm
> using to do the above test was static lib compiled by
> me using dev-cpp. I even tried out linking directly
> with .dll but the result still the same, unexpected
> output. 

> thakn you for reading and clarifying my mistake.






> __ 
> Do you Yahoo!? 
> Yahoo! Mail - Helps protect you from nasty viruses. 
> http://promotions.yahoo.com/new_mail

Re: [sqlite] SQLite 3.0.8 dates

[Doug Currie]

Friday, February 4, 2005, 3:09:59 PM, Clark Christensen wrote:

> So, my question is, true or false:, if I want to use
> SQLite's date/time functions against field values, my only
> real option is to store -MM-DD HH:MM:SS time strings. 
> Any other value, whether MMDD, julian day number,
> MM/DD/, etc. (with or without the time portion), would
> leave me with having to do all format conversions in my app
> code.

False.

You may store the date/time in any of these text formats:

 -MM-DD
 -MM-DD HH:MM
 -MM-DD HH:MM:SS
 -MM-DD HH:MM:SS.SSS
 HH:MM
 HH:MM:SS
 HH:MM:SS.SSS

or you may store the date/time as a number. The number can be a Julian
Day Number, a unixepoch, in local or gmt. There are many other numeric
options that require a bit of SQL pre-processing to be used by...

You can format the date in a variety of ways within SQL queries.

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

e

Re: [sqlite] Storing 64-Bit Addresses

[Doug Currie]

> Before binding an address to a statement using sqlite3_bind_int64() I apply an
> offset to the address to translate it to a signed value. And when reading out
> an address using sqlite3_column_int64() I reverse the process. I.e.

> dbase_value = addr_value - offset
> addr_value = dbase_value + offset

> where offset = ((uin64_t)~0 >> 1) + 1

> this works fine for simple uses where I'm just storing and retrieving the
> value. But if I want to do more complex queries involving arithmetic, for
> example:

> SELECT * From Example WHERE ? < (begin + end);

It is rather messy, but if you defer offsetting the values until you
compare them it should work (except maybe / and %).

1. Don't adjust the values when inserting into db

2. Do adjust the values on either side of a compare.

SELECT * From Example WHERE (? + 9223372036854775808) < ((begin + end) + 
9223372036854775808);

Of course, once you go this route, forget using indexes.

e

Re: [sqlite] Storing 64-Bit Addresses

[Doug Currie]

> The actual test I'm doing is something like:

> WHERE ?1 >= (base + begin) AND ?1 < (base + end)

> where ?1, base, begin, and end are all 64-bit addresses.

This is a test with a well known optimization for unsigned values:

WHERE (?1 - base - begin) < end

To make the < test unsigned in SQL use

WHERE ((?1 - base - begin) + 9223372036854775808)) < (end + 
9223372036854775808)

This is with all values stored "raw," i.e., without offset.

e

Re: [sqlite] Storing 64-Bit Addresses

[Doug Currie]

Thursday, March 31, 2005, 5:53:12 PM, you wrote:


>> The actual test I'm doing is something like:

>> WHERE ?1 >= (base + begin) AND ?1 < (base + end)

>> where ?1, base, begin, and end are all 64-bit addresses.

> This is a test with a well known optimization for unsigned values:

> WHERE (?1 - base - begin) < end

> To make the < test unsigned in SQL use

> WHERE ((?1 - base - begin) + 9223372036854775808)) < (end + 
> 9223372036854775808)

> This is with all values stored "raw," i.e., without offset.

Oops, I meant

WHERE (?1 - base - begin) < (end - begin)

To make the < test unsigned in SQL use

WHERE ((?1 - base - begin) + 9223372036854775808)) < ((end - begin) + 
9223372036854775808)

This works as long as end > begin.

e

Re: [sqlite] Using SQL or C for data analysis?

[Doug Currie]

On Mar 27, 2012, at 3:46 PM, Larry Brasfield wrote:

> A DBMS is a good way to keep your raw data.  But I highly doubt that a 
> majority of your analysis algorithms are going to be expressible in SQL 
> without going way beyond the intended purpose of the language.  You will 
> either find yourself limiting the analyses to what is convenient to express 
> in SQL, or you will spend much more time writing queries than you would spend 
> describing your data processing in a form more suited to functions.  

Yes

> […]  I expect you would find a signal processing library, such as can be 
> found in Matlab, Octave, or Scilab, to be a much better start than what you 
> might write in SQL in reasonable time.

Or use a Statistical Computing language and environment such as R with SQLite

http://www.r-project.org/

http://cran.r-project.org/web/packages/RSQLite/index.html


e

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

Re: [sqlite] VERY weird rounding error

[Doug Currie]

On Jun 17, 2012, at 12:23 PM, Keith Medcalf wrote:

> SQLITE_SIGNIFICANT_DIGITS defaults to 14, but you can override it.  No matter 
> what is requested, the maximum number of significant digits is limited to the 
> specification, and rounding is applied to the remaining bits of the 
> significand, to round to the specified number of significant digits. 

FYI, the venerable approach:

http://kurtstephens.com/files/p372-steele.pdf

ftp://ftp.ccs.neu.edu/pub/people/will/retrospective.pdf

http://www.cs.washington.edu/education/courses/cse590p/590k_02au/print-fp.pdf

http://www.cesura17.net/~will/Professional/Research/Papers/howtoread.pdf

e

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

Re: [sqlite] how to build sqlite4 (four)?

[Doug Currie]

On Jun 28, 2012, at 4:05 PM, Nico Williams wrote:

> It's also possibly a good idea to just not have autoincrement.  Let
> the application implement it, no?  After all, it can, including via
> triggers.

Or with PostgreSQL-style sequences

http://www.postgresql.org/docs/9.1/static/sql-createsequence.html

(and maybe SERIAL 
http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-SERIAL 
)

e

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

[sqlite] leap seconds

[Doug Currie]

The SQLite3 date & time functions are designed assuming

> […] that every day is exactly 86400 seconds in duration.

Before I start implementing TAI (or GPS time) to/from UTC translator plugin, 
has anyone already done this?

Why? In a device that logs data with sub-second resolution, in my case a 
medical device, timestamps need to account for leap seconds, and support 
translation to/from UTC (and local time) for human readability.

-- e

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

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Doug Currie]

On Aug 3, 2012, at 2:33 PM, Dan Kennedy  wrote:

> There was a problem similar to your description at one point, but
> it should have been fixed before the 3.7.12 release. What do you
> get from the shell command "SELECT sqlite_source_id();" on
> Mountain Lion?

e$ /usr/bin/sqlite3
SQLite version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT sqlite_source_id();
2012-04-03 19:43:07 86b8481be7e7692d14ce762d21bfb69504af


e


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

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Doug Currie]

On Aug 3, 2012, at 3:32 PM, Tobias Giesen  wrote:

> Apparently Apple prevents starting other versions of it and redirects 
> everything to
> their current version in /usr/bin.

On ML here I can launch my version in /user/local/bin just fine.

e$ which sqlite3
/usr/local/bin/sqlite3
e$ sqlite3
SQLite version 3.7.12 2012-05-14 01:41:23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT sqlite_source_id();
2012-05-14 01:41:23 8654aa9540fe9fd210899d83d17f3f407096c004
sqlite> .exit
e$ uname -mprsv
Darwin 12.0.0 Darwin Kernel Version 12.0.0: Sun Jun 24 23:00:16 PDT 2012; 
root:xnu-2050.7.9~1/RELEASE_X86_64 x86_64 i386

e


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

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Doug Currie]

On Aug 6, 2012, at 8:26 AM, Simon Slavin  wrote:

> So either Apple has made a change between versions, or we have different 
> paths.

I use fully qualified pathnames here:

~ e$ /usr/bin/sqlite3  :memory: 'SELECT sqlite_source_id()'
2012-04-03 19:43:07 86b8481be7e7692d14ce762d21bfb69504af
~ e$ /usr/local/bin/sqlite3 :memory: 'SELECT sqlite_source_id()'
2012-05-14 01:41:23 8654aa9540fe9fd210899d83d17f3f407096c004

I never had a pre-release OSX ML installed. I did update /usr/local/bin/sqlite3 
from sqlite.org.

e

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

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Doug Currie]

On Aug 6, 2012, at 4:51 PM, Simon Slavin <slav...@bigfraud.org> wrote:

> On 6 Aug 2012, at 7:48pm, Doug Currie <doug.cur...@gmail.com> wrote:
> 
>> ~ e$ /usr/local/bin/sqlite3 :memory: 'SELECT sqlite_source_id()'
>> 2012-05-14 01:41:23 8654aa9540fe9fd210899d83d17f3f407096c004
> 
> I think this copy has been installed by something else.

Yes, I installed it.

>  I don't think it comes with Apple's distribution of Mountain Lion.  If this 
> is the file which is being executed by default (in other words, if that's the 
> file reported by the command 'which sqlite3' on your system) then this may be 
> the cause of your problem.

I don't have a problem, Tobias does, and I suspect it is because the ML version 
of sqlite3 in /usr/bin (2012-04-03) predates the 3.7.12 release, and has the 
bug Dan recalls.

e

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

Re: [sqlite] Bug: SQLite's include guards are reserved identifiers

[Doug Currie]

On Sat, Jul 9, 2016 at 12:05 PM, Keith Medcalf  wrote:

>
> [...] Most API headers do the same thing.  Even the standard library does
> it, in most compilers.  [...]


Sure, that's why they're reserved! So user code and the C compiler's
library implementation don't clash. The C standard reserves those
identifiers for itself (or future versions of itself) to use.

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

Re: [sqlite] Simple web query tool

[Doug Currie]

On Wed, Feb 1, 2017 at 11:10 AM, Jay Kreibich  wrote:

> I'm looking for an *extremely* simple web tool that will allow me to
> configure a dozen or so stored queries, which people can then select and
> run on an internal server.


While I wouldn't call it extremely simple, the Fossil source has a
"translate" tool that supports embedding SQLite queries and HTML generation
inline with C source code for a cgi program.

Description from: http://fossil-scm.org/index.html/artifact/33b65539a12abd07

** SYNOPSIS:
**
** Input lines that begin with the "@" character are translated into
** either cgi_printf() statements or string literals and the
** translated code is written on standard output.
**
** The problem this program is attempt to solve is as follows:  When
** writing CGI programs in C, we typically want to output a lot of HTML
** text to standard output.  In pure C code, this involves doing a
** printf() with a big string containing all that text.  But we have
** to insert special codes (ex: \n and \") for many common characters,
** which interferes with the readability of the HTML.
**
** This tool allows us to put raw HTML, without the special codes, in
** the middle of a C program.  This program then translates the text
** into standard C by inserting all necessary backslashes and other
** punctuation.
**
** Enhancement #1:
**
** If the last non-whitespace character prior to the first "@" of a
** @-block is "=" or "," then the @-block is a string literal initializer
** rather than text that is to be output via cgi_printf().  Render it
** as such.
**
** Enhancement #2:
**
** Comments of the form:  "|* @-comment: CC" (where "|" is really "/")
** cause CC to become a comment character for the @-substitution.
** Typical values for CC are "--" (for SQL text) or "#" (for Tcl script)
** or "//" (for C++ code).  Lines of subsequent @-blocks that begin with
** CC are omitted from the output.

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

Re: [sqlite] Datatype for prices (1,500)

[Doug Currie]

On Wed, Nov 30, 2016 at 5:25 PM, Nico Williams 
wrote:

> On Wed, Nov 30, 2016 at 02:22:42PM -0600, John McKown wrote:
> >
> > [...] every RDMS "should" implement Decimal Floating Point.
>
> You could argue that every programming language needs that.  What makes
> SQL more special than the others in this regard?
>

The SQL standard (at least SQL92) specifies an exact numeric type that uses
decimal precision for rounding. Most other programming languages don't.

There are reasons we use IEEE754: it's fixed-sized, it's built-in pretty
> much everywhere, and it's usually implemented in hardware, so it's fast.


The IEEE754-2008 standard includes both base 2 (binary) and base 10
(decimal) numbers. E.g., decimal64, decimal128, as well as binary64
(typical C double) and binary32 (typical C float). There are few hardware
implementations of decimal floats; modern processors that have it include
IBM System Z and POWER6.

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

Re: [sqlite] Simplify multiple use of value in a trigger

[Doug Currie]

On Sat, Jan 7, 2017 at 9:27 AM, Gan Uesli Starling  wrote:

> So I'm trying to accumulate data for state/prov inside of USA, Canada and
> Mexico, and country for the rest of the world.
>
> Since country and state from the same update are factors in incrementing
> each of four tables,
>

You should ask yourself why you have four tables instead of one table. This
(poor database normalization) is the root cause of the problem.

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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Doug Currie]

Cezary is correct,

NULL is not equal to NULL, though NULL is NULL.

sqlite> select NULL IS NULL;

1

sqlite> select NULL = NULL;


sqlite>


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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Doug Currie]

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

Re: [sqlite] Question about Practicality of Embedding SQLite on Cortex-M4 Processor

[Doug Currie]

On Fri, Mar 2, 2018 at 2:46 PM, Obrien, John J  wrote:

> [...]
>
> To summarize, my question is regarding what direction I should ask the
> hardware vendor to take. Does it make sense for them to spend time
> optimizing the SAM4S for SQLite or should we consider another approach?
>

John, try web searching for "flash key value store." Look for one that
supports SD Card or eMMC from ARM Cortex-M4 (or -A5).

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

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

[Doug Currie]

On Fri, Jun 14, 2019 at 7:16 AM R Smith  wrote:

>
> What I was objecting to, is claiming (in service of suggesting the
> use-case for -0.0), [...]
>
> I'll be happy to eat my words if someone can produce a mathematical
> paper that argued for the inclusion of -0.0 in IEEE754 to serve a
> mathematical concept. It's a fault, not a feature.
>

David Goldberg's classic paper "What Every Computer Scientist Should Know
About Floating-Point Arithmetic" has a section on this topic, 2.2.3 Slgned
Zero, with a few use cases.

W. Kahan's early papers on standardizing floating point uses the term
"affine mode" to describe when signed zeros and infinities matter (as
opposed to "projective mode").
E.g.,
ON A PROPOSED FLOATING-POINT STANDARD
W. Kahan
University of California, Berkeley
J. Palmer
INTEL Corporation, Aloha, Oregon
October 1, 1979

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

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

[Doug Currie]

>
>
> Except by the rules of IEEE (as I understand them)
>
> -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero"
>

Except that 0.0 is also an approximation to zero, not "true zero."

Consider that 1/-0.0 is -inf whereas 1/0.0 is +int

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

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

[Doug Currie]

>
> I do not know if this is the result case in any of the programming
> languages, but in Mathematical terms that is just not true.
>

The related IEEE 754 rules are described here:
https://en.wikipedia.org/wiki/Signed_zero

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

Re: [sqlite] Deterministic random sampling via SELECT

[Doug Currie]

On Thu, Nov 7, 2019 at 4:23 PM Richard Damon 
wrote:

>
> One thought would be to generate a ‘hash’ from part of the record, maybe
> the record ID, and select records based on that value. The simplest would
> be something like id%100 == 0 would get you 1% of the records. That
> admittedly isn’t that random.
>
> Put the ID through a linear congruential generator, something like
>
> mod(a * Id + b, c) % 100 == 0
>
> And you will pretty well scramble the selection
>
>
Yes, and if a, b, and c come from a randomization table, they can be
modified to obtain a different pseudo-random set.

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