Re: [sqlite] Continuous recovery of journal

[J Decker]

On Sat, Apr 1, 2017 at 10:22 PM, Jeffrey Mattox  wrote:

> >> On Apr 1, 2017, at 10:43 PM, J Decker  wrote:
> >
> > I can add an idle sweep to close connections when nothing has been in
> progress for a while but only on sqlite connections which complicates
> things...
>
> Why don't you do as Simon suggested ("the application should have the
> SQLite database open only while it’s being accessed.")?   That is, open a
> connection to the DB when you need to access it and then immediately close
> the connection?
>
> have you never watched the hoops operating systems go through to open
files?  That's a LOT of work that takes a million inserts a second to a few
10k...
oh - you mean on a higher level?  Oh because my api just allows doing sql
commands without specifying a connection; so typically I don't know when
it's 'done'... the connection is either availalble or it's not... but then
again most normal programs just gracefully exit and don't just cease
functioning.
I actually already had that avaiable, I just needed to enable it.


> Jeff
>
> ___
> 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] Continuous recovery of journal

[J Decker]

On Sat, Apr 1, 2017 at 6:42 PM, Simon Slavin  wrote:

>
> On 2 Apr 2017, at 2:16am, J Decker  wrote:
>
> > there's no corruption.
>
> There was corruption.  The message you reported:
>
> > Sqlite3 Err: (283) recovered 6942 frames from WAL file
>
> is exactly a message reporting that the database was corrupt.  This
> corruption appears, in this case, to be the result of the database not
> having been closed correctly.
>
> Sorry to argue that; the integrity check was Ok and that's what I was
basing my statement on.


> > Under kill -9 or TerminateProcess (taskmgr, end process) , the
> application
> > has no chance to do anything, it's not that the application is doing
> > something incorrectly.
>
> Are you routinely using "kill -9" to terminate that application ?  In
> other words, is that what the application should expect, rather than
> receiving a "term" message ?
>
>
No I don't expect that; yes in this app because it's ending up with an
abort and signal handlers aren't catching it either... and noticed it
causes an anomaly in sqlite; so I thought I would report it.
I'm glad there is a workaround;  I could even just on getting the 283
message setup to later commit the commited transactions.  (probably
shouldn't do it in the warning message handler?)
Though it does end up doing a little bit of nothing that causes a lot of to
do about nothing progressively even...


> If so, the application should have the SQLite database open only while
> it’s being accessed.  That’s the best way, though not perfect, to ensure
> that this problem does not happen again.
>
> > but the operations happen in the first 120ms of the program and never
> > later, so if the program runs for more than 1 second sqlite is stable and
> > has 0 active statements.
>
> SQLite depends on having its database closed correctly using
> sqlite3_close().  If that’s not happening, you may experience more problems
> like the one you reported.
>
> having this stressed as 'must implement close in order to actually commit
transactions(?)' [I don't know; that's what it is to me] but anyway; I can
add an idle sweep to close connections when nothing has been in progress
for a while but only on sqlite connections which complicates things...


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

Re: [sqlite] Continuous recovery of journal

[J Decker]

On Sat, Apr 1, 2017 at 3:53 PM, Simon Slavin  wrote:

>
> On 1 Apr 2017, at 8:11pm, J Decker  wrote:
>
> > It finally hit its crash point and restarted
> >
> > 11:02:30.850|~Sqlite3 Err: (283) recovered 6942 frames from WAL file
> > C:\eQube-Tools\flashboard\server\option.db-wal
> >
> > I changed the setting from 'set option' to 'default option' so it doesn't
> > re-write if it's already there; so I didn't get 2 new frames, just same
> > 6942 frames
> >
> > I just wouldn't expect a journal to continuously recover itself when
> > there's really nothing to do anyway?  I guess maybe I don't expect
> > 'recovered' to me 'reloaded' or 'continuing with existing'
>
> You are not seeing what SQLite does.  You are seeing what that application
> does, not using SQLite correctly.  The application is not closing the
> database correctly when it quits.  Therefore corruption is not being fixed
> in the version saved to disk.  So the next time you open the database file,
> it’s still corrupt.
>
> You have two options.  The preferred one is to recover a backup of the
> database from before the Flashboard tool started issuing those error
> messages.  However, if you’re willing to work with possible corruption
> introduced into the database then ...
>
> 1. Take a backup copy of the database in case anything goes wrong.
> 2. Open the database in the SQLite shell tool.
> 3. Execute the VACUUM command.
> 4. Quit the SQLite shell tool using the ".quit" command.
>
>
there's no corruption.
> I added a wal_checkpoint as part of connection, so now the wal stays at 2
frames..

Under kill -9 or TerminateProcess (taskmgr, end process) , the application
has no chance to do anything, it's not that the application is doing
something incorrectly.

but the operations happen in the first 120ms of the program and never
later, so if the program runs for more than 1 second sqlite is stable and
has 0 active statements.




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

Re: [sqlite] Continuous recovery of journal

[J Decker]

On Sat, Apr 1, 2017 at 9:54 AM, J Decker  wrote:

>
> On Fri, Mar 31, 2017 at 11:32 PM, Simon Slavin 
> wrote:
>
>>
>> On 1 Apr 2017, at 5:04am, J Decker  wrote:
>>
>> > Sqlite3 Err: (283) recovered 6942 frames from WAL file
>> > C:\eQube-Tools\flashboard\server\option.db-wal
>> >
>> > pretty much every time I restart the program now (espcially if it
>> > segfaults).
>> > There are no frames to recover... really... I mean it should have
>> recovered
>> > them any of the previous 10 times, no?
>>
>> If the database is corrupt then it’s possible that the journal file will
>> get corrupted every time the database is closed.  Use the SQLite shell tool
>> to run
>>
>> PRAGMA integrity_check;
>>
>>
> C:\eQube-Tools\flashboard\server>tools\sqlite3.exe option.db
> SQLite version 3.18.0 2017-03-28 18:48:43
> Enter ".help" for usage hints.
> sqlite> pragma integrity_check
>...> ;
> ok
> sqlite>
>
> current execution started yesterday; I ended up clearing the older logs...
> 18:10:58.868|7D612684~Launching user process...
> 18:11:01.183|7D60E478~18:11:01.182|031C9270~Sqlite3 Err: (283)
> recovered 6942 frames from WAL file C:\eQube-Tools\flashboard\
> server\option.db-wal
>
> there's only 3 tables in use
> select count(*) from option4_map union select count(*) from option4_name
> union select count(*) from option4_values;
> 50
> 66
> 74
>
> ---
> On a different process
>
> 09:47:26.720~Sqlite3 (283) recovered 2 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:29.893~Sqlite3 Err: (283) recovered 4 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:34.251~Sqlite3 Err: (283) recovered 6 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:38.201~Sqlite3 Err: (283) recovered 8 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:41.615~Sqlite3 Err: (283) recovered 10 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
>
> integrity is OK.
>
> just a final note...

It finally hit its crash point and restarted

11:02:30.850|~Sqlite3 Err: (283) recovered 6942 frames from WAL file
C:\eQube-Tools\flashboard\server\option.db-wal

I changed the setting from 'set option' to 'default option' so it doesn't
re-write if it's already there; so I didn't get 2 new frames, just same
6942 frames

I just wouldn't expect a journal to continuously recover itself when
there's really nothing to do anyway?  I guess maybe I don't expect
'recovered' to me 'reloaded' or 'continuing with existing'


> The update statements are these...
>
> 09:47:41.617~Do Command[00E8CC9C:./option.db]: BEGIN TRANSACTION
> 09:47:41.617~Do Command[00E8CC9C:./option.db]: replace into option4_values
> (`option_id`,`string`,`segment` ) values ('c9e60551-fd35-11e6-a38c-
> b0c09031be57','172.31.0.200',0)
> 09:47:41.618~Do Command[00E8CC9C:./option.db]: delete from option4_values
> where `option_id`='c9e60551-fd35-11e6-a38c-b0c09031be57' and segment > 0
> 09:47:41.618~Do Command[00E8CC9C:./option.db]: COMMIT
>
> (2 frames per run?)  There are never segments greater than 0 to delete; I
> just don't know if I'm replacing a very very long option with a shorter
> one; and the code is MySQL compatible that doesn't have inifite varchar
> fields so options poentially have multiple 256 character segments
>
> So really - after frames are recovered, can't they be cleared?
> I could understand if it was always 2 frames but 2, 4, 6, 8, 10 on
> subsequent runs (with terminate process inbetween) seems wrong somehow.
>
> It's more like 'NOT recovering 2 frames.'
>
>
>
>> on that database.  Tell us whether it reports problems.
>>
>> Whether or not corruption is reported, quit the shell tool then run
>> flashboard again and see whether it reports the same problem with the file.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Continuous recovery of journal

[J Decker]

On Sat, Apr 1, 2017 at 10:04 AM, J Decker  wrote:

>
>
> On Sat, Apr 1, 2017 at 9:59 AM, Richard Hipp  wrote:
>
>> On 4/1/17, J Decker  wrote:
>> >
>> > So really - after frames are recovered, can't they be cleared?
>>
>> Is see.  You want a "checkpoint".  That will happen automatically when
>> the size of the WAL file reaches 1000 frames.  But you can force it to
>> happen sooner by running "PRAGMA wal_checkpoint".
>>
>>
> the other wall is up to 6000 frames (I think I had an error in the script
> for a bit and the process kept restarting very very quickly but... it was
> probably just 2 frames per time to get to 6000 )
>

I added a wal_checkpoint as part of connection, so now the wal stays at 2
frames... recovered into WAL state I guess? not actually recovered... it
was commited.

>
>
>> --
>> 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] Continuous recovery of journal

[J Decker]

On Sat, Apr 1, 2017 at 9:59 AM, Richard Hipp  wrote:

> On 4/1/17, J Decker  wrote:
> >
> > So really - after frames are recovered, can't they be cleared?
>
> Is see.  You want a "checkpoint".  That will happen automatically when
> the size of the WAL file reaches 1000 frames.  But you can force it to
> happen sooner by running "PRAGMA wal_checkpoint".
>
>
the other wall is up to 6000 frames (I think I had an error in the script
for a bit and the process kept restarting very very quickly but... it was
probably just 2 frames per time to get to 6000 )


> --
> 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] Continuous recovery of journal

[J Decker]

On Sat, Apr 1, 2017 at 9:54 AM, J Decker  wrote:

>
> On Fri, Mar 31, 2017 at 11:32 PM, Simon Slavin 
> wrote:
>
>>
>> On 1 Apr 2017, at 5:04am, J Decker  wrote:
>>
>> > Sqlite3 Err: (283) recovered 6942 frames from WAL file
>> > C:\eQube-Tools\flashboard\server\option.db-wal
>> >
>> > pretty much every time I restart the program now (espcially if it
>> > segfaults).
>> > There are no frames to recover... really... I mean it should have
>> recovered
>> > them any of the previous 10 times, no?
>>
>> If the database is corrupt then it’s possible that the journal file will
>> get corrupted every time the database is closed.  Use the SQLite shell tool
>> to run
>>
>> PRAGMA integrity_check;
>>
>>
> C:\eQube-Tools\flashboard\server>tools\sqlite3.exe option.db
> SQLite version 3.18.0 2017-03-28 18:48:43
> Enter ".help" for usage hints.
> sqlite> pragma integrity_check
>...> ;
> ok
> sqlite>
>
> current execution started yesterday; I ended up clearing the older logs...
> 18:10:58.868|7D612684~Launching user process...
> 18:11:01.183|7D60E478~18:11:01.182|031C9270~Sqlite3 Err: (283)
> recovered 6942 frames from WAL file C:\eQube-Tools\flashboard\
> server\option.db-wal
>
> there's only 3 tables in use
> select count(*) from option4_map union select count(*) from option4_name
> union select count(*) from option4_values;
> 50
> 66
> 74
>
> ---
> On a different process
>
> 09:47:26.720~Sqlite3 (283) recovered 2 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:29.893~Sqlite3 Err: (283) recovered 4 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:34.251~Sqlite3 Err: (283) recovered 6 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:38.201~Sqlite3 Err: (283) recovered 8 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
> 09:47:41.615~Sqlite3 Err: (283) recovered 10 frames from WAL file
> M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
>
> integrity is OK.
>
> The update statements are these...
>
> 09:47:41.617~Do Command[00E8CC9C:./option.db]: BEGIN TRANSACTION
> 09:47:41.617~Do Command[00E8CC9C:./option.db]: replace into option4_values
> (`option_id`,`string`,`segment` ) values ('c9e60551-fd35-11e6-a38c-
> b0c09031be57','172.31.0.200',0)
> 09:47:41.618~Do Command[00E8CC9C:./option.db]: delete from option4_values
> where `option_id`='c9e60551-fd35-11e6-a38c-b0c09031be57' and segment > 0
> 09:47:41.618~Do Command[00E8CC9C:./option.db]: COMMIT
>
> (2 frames per run?)  There are never segments greater than 0 to delete; I
> just don't know if I'm replacing a very very long option with a shorter
> one; and the code is MySQL compatible that doesn't have inifite varchar
> fields so options poentially have multiple 256 character segments
>
> So really - after frames are recovered, can't they be cleared?
> I could understand if it was always 2 frames but 2, 4, 6, 8, 10 on
> subsequent runs (with terminate process inbetween) seems wrong somehow.
>
> It's more like 'NOT recovering 2 frames.'
>
>
Oh maybe it is recovering 2 frames,b ut the act of recovery creates 2
frames again?  so then it has the new 2 from the recovery before the next
one to get 4?



>
>
>> on that database.  Tell us whether it reports problems.
>>
>> Whether or not corruption is reported, quit the shell tool then run
>> flashboard again and see whether it reports the same problem with the file.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Continuous recovery of journal

[J Decker]

On Fri, Mar 31, 2017 at 11:32 PM, Simon Slavin  wrote:

>
> On 1 Apr 2017, at 5:04am, J Decker  wrote:
>
> > Sqlite3 Err: (283) recovered 6942 frames from WAL file
> > C:\eQube-Tools\flashboard\server\option.db-wal
> >
> > pretty much every time I restart the program now (espcially if it
> > segfaults).
> > There are no frames to recover... really... I mean it should have
> recovered
> > them any of the previous 10 times, no?
>
> If the database is corrupt then it’s possible that the journal file will
> get corrupted every time the database is closed.  Use the SQLite shell tool
> to run
>
> PRAGMA integrity_check;
>
>
C:\eQube-Tools\flashboard\server>tools\sqlite3.exe option.db
SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
sqlite> pragma integrity_check
   ...> ;
ok
sqlite>

current execution started yesterday; I ended up clearing the older logs...
18:10:58.868|7D612684~Launching user process...
18:11:01.183|7D60E478~18:11:01.182|031C9270~Sqlite3 Err: (283)
recovered 6942 frames from WAL file
C:\eQube-Tools\flashboard\server\option.db-wal

there's only 3 tables in use
select count(*) from option4_map union select count(*) from option4_name
union select count(*) from option4_values;
50
66
74

---
On a different process

09:47:26.720~Sqlite3 (283) recovered 2 frames from WAL file
M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
09:47:29.893~Sqlite3 Err: (283) recovered 4 frames from WAL file
M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
09:47:34.251~Sqlite3 Err: (283) recovered 6 frames from WAL file
M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
09:47:38.201~Sqlite3 Err: (283) recovered 8 frames from WAL file
M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal
09:47:41.615~Sqlite3 Err: (283) recovered 10 frames from WAL file
M:\eqube\Vegas\trunk\javascript\eqube\flashboard\server\option.db-wal

integrity is OK.

The update statements are these...

09:47:41.617~Do Command[00E8CC9C:./option.db]: BEGIN TRANSACTION
09:47:41.617~Do Command[00E8CC9C:./option.db]: replace into option4_values
(`option_id`,`string`,`segment` ) values
('c9e60551-fd35-11e6-a38c-b0c09031be57','172.31.0.200',0)
09:47:41.618~Do Command[00E8CC9C:./option.db]: delete from option4_values
where `option_id`='c9e60551-fd35-11e6-a38c-b0c09031be57' and segment > 0
09:47:41.618~Do Command[00E8CC9C:./option.db]: COMMIT

(2 frames per run?)  There are never segments greater than 0 to delete; I
just don't know if I'm replacing a very very long option with a shorter
one; and the code is MySQL compatible that doesn't have inifite varchar
fields so options poentially have multiple 256 character segments

So really - after frames are recovered, can't they be cleared?
I could understand if it was always 2 frames but 2, 4, 6, 8, 10 on
subsequent runs (with terminate process inbetween) seems wrong somehow.

It's more like 'NOT recovering 2 frames.'



> on that database.  Tell us whether it reports problems.
>
> Whether or not corruption is reported, quit the shell tool then run
> flashboard again and see whether it reports the same problem with the file.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Continuous recovery of journal

[J Decker]

On Sat, Apr 1, 2017 at 2:38 AM, Richard Hipp  wrote:

> On 4/1/17, J Decker  wrote:
> > I get this ... from sqlite error log callback
> >
> > Sqlite3 Err: (283) recovered 6942 frames from WAL file
> > C:\eQube-Tools\flashboard\server\option.db-wal
> >
> > pretty much every time I restart the program now (espcially if it
> > segfaults).
>
> This is because the previous process to access the database did not
> call sqlite3_close() prior to exiting, and so the WAL file was not
> cleaned up properly.
>

Right; I understand that; but on the next open, couldn't the wal get
cleaned after recovery?   or something...


> --
> 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

[sqlite] Continuous recovery of journal

[J Decker]

I get this ... from sqlite error log callback

Sqlite3 Err: (283) recovered 6942 frames from WAL file
C:\eQube-Tools\flashboard\server\option.db-wal

pretty much every time I restart the program now (espcially if it
segfaults).
There are no frames to recover... really... I mean it should have recovered
them any of the previous 10 times, no?

I'm pretty sure that I'm not even doing a modification now; previously I
was possibly updating a table's value to the same value.


If my program segfaults I don't get a chance to graceful close all
outstanding statements and connections; so the -wal and -shm file's are
never deleted.

I can understand keeping them open; but when they're done; can't you at
least; I dunno truncate them?  in my VFS that's not a very expensive
operation... other than of course re-expanding when you need the space; but
at least you're not doing a directory search to get a handle to it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

[J Decker]

On Thu, Mar 23, 2017 at 9:01 AM, Dan Kennedy  wrote:

> On 03/23/2017 10:00 PM, Subramaniyan, Ganesan (G.) wrote:
>
>> Hi,
>> We are facing dynamic memory pool corruption issue while using SQLite.
>> Based on our investigation we suspect SQLite freeing memory bypassing the
>> memory management API'S registered as below.
>>
>> We have registered the memory allocation routines as below.
>> static const sqlite3_mem_methods mqxmem = {
>>  mqxMalloc,
>>  mqxFree,
>>  mqxRealloc,
>>  mqxSize,
>>  mqxRoundup,
>>  mqxInit,
>>  mqxShutdown,
>>  NULL
>>  };
>> sqlite3_config(SQLITE_CONFIG_MALLOC, &mqxmem);
>>
>> Despite this, is there any sort of configurations by which SQLite might
>> still use its own or standard library APIs to allocate/reallocate or free
>> the memory, bypassing the memory management APIs registered above?
>>
>
>
> No. Following a successful call to sqlite3_config() SQLite allocates and
> frees memory using the configured routines exclusively[1]. There are no
> direct calls to malloc()/free() or similar in the library.
>
> Are you checking the return value of sqlite3_config()? If
> sqlite3_initialize() or any other sqlite3_*() function has already been
> called when sqlite3_config() is invoked it will fail and the memory
> allocation routines will not be configured.
>
>   https://sqlite.org/c3ref/config.html
>
> What symptoms are you seeing that suggest SQLite is bypassing your memory
> allocation functions?
>

There is an internal check to see if sqlite has alerady 'initialized' the
config needs to be set before it's initialized.  There's a error thrown

void errorLogCallback(void *pArg, int iErrCode, const char *zMsg){
printf( "Sqlite3 Err: (%d) %s", iErrCode, zMsg);
}

sqlite3_config( SQLITE_CONFIG_LOG, errorLogCallback, 0);

that is sqlite3_config is called late it logs an 'invalid operation' sort
of message.

>
> Dan.
>
> [1] It may also make use of static buffers supplied by calls to
> sqlite3_config(SQLITE_CONFIG_HEAP), CONFIG_PAGECACHE or CONFIG_SCRATCH,
> but I'm guessing you have made no such calls. Most apps do not.
>
>
>
>
>
> We are using SQLite version 3.7.10 running on Freescale MQX hosted on ARM
>> A5 Cortex processor.
>> We have enabled SQLITE_ENABLE_MEMORY_MANAGEMENT configuration and using
>> sqlite3_release_memory()
>> very often, after every fetch/write operations.
>>
>> Thanks & Regards,
>> Ganesan.S
>> ___
>> 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] confused getting started

[J Decker]

On Mon, Mar 6, 2017 at 8:40 PM, Jens Alfke  wrote:

>
> > On Mar 6, 2017, at 7:57 PM, J Decker  wrote:
> >
> > Pretty silly since it is sqlite, and a mmap'ed database is pretty much
> > sync
>
> Not really. The data still has to be paged in off the disk, which can take
> arbitrarily long (seconds, if the system is under heavy load), and complex
> queries can end up doing a lot of CPU-intensive extra work that takes time
> to run. Although yes, if you’re not super concerned about performance, it’s
> overkill.
>
> (I spent a year or two once working on Chrome, which has a rule that no
> synchronous I/O may take place on the UI thread, because it can impair
> responsiveness. That applies to database queries too.)
>

yup; caveat developor.
and if you are concerned than there's other things at play than a job doing
what it needs to do; and doing nothing otherwise.  Some systems will even
spin-lock for you :)
give me enough rope and I can play cats  cradle with it.
not all things that happen synchronously are because a user did it; and
things that aren't shouldn't have to pretend they were.

>
> —Jens
> ___
> 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] confused getting started

[J Decker]

If Python is appealing then 

Node.js + sqlite adapter makes sqlite very easy.
I have my own https://www.npmjs.com/package/sack.vfs
Needs better docs on the Sqlite output I guess

A more proper package that's all promises and async stuff...
https://www.npmjs.com/package/sqlite

Pretty silly since it is sqlite, and a mmap'ed database is pretty much
sync
and array of arbitrarily shaped objects;

But javascript in the browser maybe?  Well there's electron which is chrome
with node.js support also; which gives you all the support of command line
node.js.

For a general editor there's things like VIsual Studio Code and Atom (
atom.io) (and electron is electron.atom.io ) .  Although Notepad++ is still
pretty good :).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Massively multithreaded SQLite queries

[J Decker]

in my curiosity since any spin should be scheduled until something wake it;
I wondered what __raw_spin_lock was and if sqlite could use it
differently...
it appears to be a linux kernel thing, wrapped around read/write accesses
to devices probably the bottleneck is actually IO rather than
contention.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[J Decker]

On Fri, Mar 3, 2017 at 3:58 PM, Yuri  wrote:

> On 03/01/2017 23:41, Clemens Ladisch wrote:
>
> It would certainly be possible to add your own user-defined SQL function
>> to call sqlite3_blob_write().
>>
>
>
> I think this should be added to sqlite itself. Writing a portion of blob
> is very much like updating a field. There is currently no way to do this in
> SQL fashion in an efficient way. So such command can be chained with other
> SQL statements and make the client code much simpler.
>
>
All blobs I've used in SQL are themselves atomic and partial updates would
never be useful.  (images, fingerprints, etc.)  If you're updating parts of
blobs then probably you mean that blob to be in multiple parts or broken
into separate fields.


> Yuri
>
>
> ___
> 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] UNION

[J Decker]

On Tue, Feb 28, 2017 at 5:40 PM,  wrote:

> # SELECT * FROM processors UNION SELECT * FROM storage;
> Error: SELECTs to the left and right do not have the same number of
> result columns.
>
> All tables that I created in my database have differing column names,
> values, and amounts of columns with the noted exception of the one
> column which is common (board). I've no idea what to do now.
>
> Why is this an error?
>

1   6770
2  3770

1  samsung 960   250G
2  seagate5T
3

what should it use for fields, if the query first encounters 2 columns, and
starts allocating rows for it, then runs into data with 3, then it shouln't
just drop some arbitrary data, so it throws an error.
If you want them really to merge... on the first select *,"nothing" from X
untion select * from Y
(I think sqlite allows * as first or last argument)
or sepcifically name some number from X and the same number from Y and
it'll put them together
Whether that actually has any meaning is up to the application





>
> Thank you,
> David
> ___
> 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] foreign key cardinality

[J Decker]

On Tue, Feb 28, 2017 at 6:01 PM, J Decker  wrote:

>
>
> On Tue, Feb 28, 2017 at 1:41 PM, James K. Lowden  > wrote:
>
>> On Tue, 28 Feb 2017 17:13:30 +
>> Hick Gunter  wrote:
>>
>> > Let's assume you have a parent table P (color, shape, style, ...)
>> > that is unique on the three named fields. Let's assume you have a
>> > child table C (id, ...,color, shape, ...) that references P
>> > (color,shape)
>>
>> As far as SQL goes, Kees provided the relevant text, so we know SQLite
>> conforms to the standard in this regard.  Thank you, Kees.
>>
>> To answer your questions, supposing UNIQUE were not required for a
>> FK referent:
>>
>> > Which, if any, of the P rows can you safely delete without losing
>> > referential integrity?
>>
>
> Then how would you properly find the children?  Or find any information
> about their parent, the child would have to in turn be deleted.
>
> foreign keys are as good as pointers.  If the parent structure pointing at
> a child is no longer, how is the child valid for anything other than
> garbage collection?
>
> okay but I guess that's what on delete SETNULL is for... so you can keep
the child as a special 'NULL' record for later searching just by file


> If the unique; (memory address) key contains multiple parts, then you have
> to reference all aprts.  If there was a part that was itself unique then
> you wouldn't really need the second part; otherwise you do in order to
> differentiate (1,1) from (1,2)
>
> perhaps what you intend is to use a graph database, where a single 'child'
> might be referred to from mulitple parents (links) ; but then your model
> should be something more self recursive like...
> (pseudoSQL)
> create table inode( iNode char PRIMARY KEY, moreColumns char,  )
> create table inodeLinks( someInode char, someOtherInode char, FK(someinode
> references inode.iNode), FK(someOtheriNode references inode.iNode) )
>
> create table inodeFileData( iNode char , fileDataIfAny char,  )
> create table inodeNameData( iNode char, theNameOfThisPoint char,  )
> create table inodeFileData( iNode char, attributes char,  )
>
> Such that any directory might also contain some data, but any file can
> contain other files since each thing is named separatly from it's key.
>
>
>
>
> Instead of having the file records themselves having their directory, why
> not just make a separate table
> ( directory char , hasFiles char );
>
> CTE queries can make this even tolerable for linear queries
> 
> Sorry I wandered into an alternative solution instead of the other
> representation...
> in .NET you have DataSet, whichi contains DataTables, and between DTs are
> Relation(s).  When you fill a dataset with all properly keyed things, the
> row in one table (files).getChildRows( "filesInDirectory" ) (returns more
> rows)
>
> And it's really a direct reference, it doesn't have to go additionally
> search through the index in the child table to get to the rows by looking
> up a key, it just points to it.  Much like working with a graph, your
> master records just point at the children, and you never have to search for
> anything...
>
>
>
>> delete from P where not exists (
>> select 1 from C where color = P.color and shape = P.shape );
>>
>> > Assuming ON DELETE CASCADE, when do you delete the rows from C?
>>
>> delete from C where exists (
>> select 1 from P where color = C.color and shape = C.shape
>> and /* ... P criteria ... */
>> );
>>
>> From a theoretical standpoint, for relations
>>
>> P{A,B}
>> and
>> C{C,B} with C{B} referencing P{B}
>>
>> without loss of information we may add
>>
>> B{B}
>>
>> and constraints
>>
>> P{B} references B{B} and C{B} references B{B}
>>
>> But, having added B, we have not added any information.  We have
>> merely added another relation that is the projection of P{B}.
>>
>> It is *usually* true that B will have other attributes, in which case
>> of course a B table would be needed to hold them.  Maybe that, or
>> practical considerations, or both, motivated the SQL rule.
>>
>> --jkl
>> ___
>> 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] foreign key cardinality

[J Decker]

On Tue, Feb 28, 2017 at 1:41 PM, James K. Lowden 
wrote:

> On Tue, 28 Feb 2017 17:13:30 +
> Hick Gunter  wrote:
>
> > Let's assume you have a parent table P (color, shape, style, ...)
> > that is unique on the three named fields. Let's assume you have a
> > child table C (id, ...,color, shape, ...) that references P
> > (color,shape)
>
> As far as SQL goes, Kees provided the relevant text, so we know SQLite
> conforms to the standard in this regard.  Thank you, Kees.
>
> To answer your questions, supposing UNIQUE were not required for a
> FK referent:
>
> > Which, if any, of the P rows can you safely delete without losing
> > referential integrity?
>

Then how would you properly find the children?  Or find any information
about their parent, the child would have to in turn be deleted.

foreign keys are as good as pointers.  If the parent structure pointing at
a child is no longer, how is the child valid for anything other than
garbage collection?

If the unique; (memory address) key contains multiple parts, then you have
to reference all aprts.  If there was a part that was itself unique then
you wouldn't really need the second part; otherwise you do in order to
differentiate (1,1) from (1,2)

perhaps what you intend is to use a graph database, where a single 'child'
might be referred to from mulitple parents (links) ; but then your model
should be something more self recursive like...
(pseudoSQL)
create table inode( iNode char PRIMARY KEY, moreColumns char,  )
create table inodeLinks( someInode char, someOtherInode char, FK(someinode
references inode.iNode), FK(someOtheriNode references inode.iNode) )

create table inodeFileData( iNode char , fileDataIfAny char,  )
create table inodeNameData( iNode char, theNameOfThisPoint char,  )
create table inodeFileData( iNode char, attributes char,  )

Such that any directory might also contain some data, but any file can
contain other files since each thing is named separatly from it's key.




Instead of having the file records themselves having their directory, why
not just make a separate table
( directory char , hasFiles char );

CTE queries can make this even tolerable for linear queries

Sorry I wandered into an alternative solution instead of the other
representation...
in .NET you have DataSet, whichi contains DataTables, and between DTs are
Relation(s).  When you fill a dataset with all properly keyed things, the
row in one table (files).getChildRows( "filesInDirectory" ) (returns more
rows)

And it's really a direct reference, it doesn't have to go additionally
search through the index in the child table to get to the rows by looking
up a key, it just points to it.  Much like working with a graph, your
master records just point at the children, and you never have to search for
anything...



> delete from P where not exists (
> select 1 from C where color = P.color and shape = P.shape );
>
> > Assuming ON DELETE CASCADE, when do you delete the rows from C?
>
> delete from C where exists (
> select 1 from P where color = C.color and shape = C.shape
> and /* ... P criteria ... */
> );
>
> From a theoretical standpoint, for relations
>
> P{A,B}
> and
> C{C,B} with C{B} referencing P{B}
>
> without loss of information we may add
>
> B{B}
>
> and constraints
>
> P{B} references B{B} and C{B} references B{B}
>
> But, having added B, we have not added any information.  We have
> merely added another relation that is the projection of P{B}.
>
> It is *usually* true that B will have other attributes, in which case
> of course a B table would be needed to hold them.  Maybe that, or
> practical considerations, or both, motivated the SQL rule.
>
> --jkl
> ___
> 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] 'misuse at line...' sqlite3_config

[J Decker]

I have this bit of code I've had around forever, I recently enabled
SQLITE_CONFIG_LOG and have been getting a misuse because I'm calling
sqlite3_config after initialization.

The one I'm calling is SQLITE_CONFIG_GETMALLOC which should read the
internal routines already in use; I then override some of those in the
sqlite3_mem_methods structure and set sqlite3_db_config to set those on a
specific connection.

The first thing in sqlite3_config is 'if( sqlite3GlobalConfig.isInit )
return SQLITE_MISUE-BKPT'

shouldn't I be able to use config to read things?  There is no check to see
if the op is a write config or a read config
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FOREIGN KEY question

[J Decker]

On Wed, Feb 8, 2017 at 12:24 AM, Clemens Ladisch  wrote:

> Igor Korot wrote:
> > Does SQLite supports the FK name?
> > If yes, what is the proper syntax?
>
>   CREATE TABLE t (
> x PRIMARY KEY,
> y,
> CONSTRAINT this_is_the_name FOREIGN KEY (y) REFERENCES t(x)
>   );
>
> or
>
>   CREATE TABLE t (
> x PRIMARY KEY,
> y CONSTRAINT this_is_the_name REFERENCES t(x)
>   );
>
> But it is ignored:
>
>   > PRAGMA foreign_key_list(t);
>   0|0|t|y|x|NO ACTION|NO ACTION|NONE
>   > INSERT INTO t VALUES (1, 2);
>   Error: FOREIGN KEY constraint failed
>
> the value 2 you set for Y doesn't exsit as an X in t.


>
> Regards,
> Clemens
> ___
> 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] Retrieve INTEGER PRIMARY KEY

[J Decker]

http://data.sqlite.org/c3ref/last_insert_rowid.html

there is an api call to get it; or you can use select and get it

the .net library has it as a connection property  LastInsertRowId

https://www.crestron.com/reference/simpl_sharp/html/P_Crestron_SimplSharp_SQLite_SQLiteConnection_LastInsertRowId.htm

On Sun, Feb 5, 2017 at 5:54 AM, Simon Slavin  wrote:

>
> On 5 Feb 2017, at 1:26pm, Clyde Eisenbeis  wrote:
>
> > The compiler complains about "SELECT last_insert_rowid()" ... which
> > appears to be limited to SQLite3.
>
> The compiler should never have got that string.  The string is executed
> when the program is already compiled, just like any other SELECT command.
>
> Find some context where you can execute any other SELECT command, and use
> "last_insert_rowid()" as a column name.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key error...

[J Decker]

as an alternative you can use the same ID - nessecitating prefiixing the
primary id with the table name... but then natural join can work.
 (Although common words like 'name' should also be table-name prefixed).

Having a 'id' as the primary key disallows the using() shorthand also.

If all column names that are meant to be joined are the same name, database
tools can extrapolate relationships; although with defined foriegn keys
they should be able to manage with mismatched naming.

But I suspect that the example from the sqlite documentation that you're
starting with uses different names to illustrate what is actually meant to
be used in which positions for the foriegn key definition.



On Sun, Jan 8, 2017 at 4:57 AM, Keith Medcalf  wrote:

>
> On Sunday, 8 January, 2017 05:05, Ken Wagner  wrote:
>
> > Keith,
> >
> > Ahh, better to be specific and avoid simplistic assumptions.
> >
> > For foreign keys which is better: 'trackerartist' or 'artistid' in the
> > track file? Does it matter? Is it personal preference?
>
> It is a matter of personal preference.  Personally, I use the same name
> for the same data, and do not add useless prefaces, and usually do not
> preface the id with the table name.  For example:
>
> create table artists (
>  id integer primary key,
>  name text collate nocase unique
> );
> create table albums (
>  id integer primary key,
>  name text collate nocase unique,
>  artistid integer references artists
> );
> create table tracks (
>  id integer primary key,
>  seq integer,
>  name text collate nocase,
>  artistid integer references artists,
>  albumid integer references albums,
>  unique (albumid, seq)
> );
>
> select albums.name as albumname,
>albumartists.name as albumartist,
>tracks.seq as trackno,
>tracks.name as trackname,
>trackartists.name as trackartist
>   from albums, tracks, artists as albumartists, artists as trackartists
>  where tracks.artistid = trackartists.id
>and tracks.albumid = albums.id
>and albums.artistid = albumartists.id;
>
> Of course, this does not allow the same track on multiple albums.  For
> that you need another table to do the N:M mapping:
>
> create table artists (
>  id integer primary key,
>  name text collate nocase unique
> );
> create table albums (
>  id integer primary key,
>  name text collate nocase unique,
>  artistid integer references artists
> );
> create table tracks (
>  id integer primary key,
>  name text collate nocase,
>  artistid integer references artists,
> );
> create table albumtracks (
>  id integer primary key,
>  albumid integer references albums,
>  trackid integer references tracks,
>  seq integer,
>  unique (albumid, seq),
>  unique (albumid, trackid),
>  unique (trackid, albumid)
> );
>
> select albums.name as albumname,
>albumartists.name as akbumartist,
>albumtracks.seq as trackno,
>tracks.name as trackname,
>trackartists.name as trackartist
>   from albums, albumtracks, tracks, artists as albumartists, artists as
> trackartists
>  where tracks.artistid = trackartists.id
>and albumtracks.albumid = albums.id
>and albumtracks.trackid = tracks.id
>and albums.artistid = albumartists.id;
>
> > On 01/08/2017 05:46 AM, Keith Medcalf wrote:
> > > ... join ... using (column) has nothing whatever to do with foreign
> > keys.
> > >
> > > "FROM a JOIN b USING (c) is "syntactic sugar" (meaning that it is
> > sweetening and full of calories, but has no nutritional value) for the
> > expression "FROM a, b WHERE a.c = b.c"
> > >
> > > This is so, for example, if you use really long stupid names it saves
> > considerable space and typing:
> > >
> > > SELECT * FROM TheFirstTableToBeJoined JOIN TheSecondTableToBeJoined
> > USING (TheCommonColumnNameBetweenTheFirstAndTheSecondTable);
> > > -vs-
> > > SELECT * FROM TheFirstTableToBeJoined, TheSecondTableToBeJoined WHERE
> > TheFirstTableToBeJoined.TheCommonColumnNameBetweenTheF
> irstAndTheSecondTabl
> > e =
> > TheSecondTableToBeJoined.TheCommonColumnNameBetweenTheF
> irstAndTheSecondTab
> > le;
> > >
> > >
> > >> -Original Message-
> > >> From: sqlite-users [mailto:sqlite-users-
> > boun...@mailinglists.sqlite.org]
> > >> On Behalf Of Ken Wagner
> > >> Sent: Sunday, 8 January, 2017 04:04
> > >> To: SQLite mailing list
> > >> Subject: Re: [sqlite] Foreign key error...
> > >>
> > >> Thanks, Kees,
> > >>
> > >> The message is helpful as a warning.
> > >>
> > >> select artistname, trackname from artist inner join track on
> > >> trackartist = artistid;  works just fine.
> > >>
> > >> But isn't the efficiency of 'using (artistid)' more desirable?
> > >>
> > >> Is the use of a 'trackerartist' as the foreign key used because it is
> > >> more informative?
> > >>
> > >> I.e., wherever it is seen it shows the track-artist link? But is more
> > >> demanding when coding:
> > >>
> > >>   'on trackerartist = artistid' vs 'using (artistid)'
> > >>
> > >> Best or preferred SQLite3 practice for using which

Re: [sqlite] Anybody know why mono SQLite uses SqliteConnection but Windows uses SQLiteConnection?

[J Decker]

mono install.exe often works.

On Fri, Nov 18, 2016 at 9:16 PM, Jim Henderson 
wrote:

> Thanks for the suggestion, Bill.
>
> Will this run on a Linux system? The zip file has Install.exe and Test.exe
> inside it.
>
> Jim
>
>
> On 18-Nov-2016 7:44 AM, Drago, William @ CSG - NARDA-MITEQ wrote:
>
>> Do any of you know why the two different versions of SQLite have different
>>> case in the method names?
>>>
>>> Thanks,
>>> Jim
>>>
>>> Is there a reason why you can't use this one which should have the
>> correct case?
>>
>> https://system.data.sqlite.org/index.html/doc/trunk/www/down
>> loads.wiki#sqlite-netFx451-binary-Mono-2013
>>
>> --
>> Bill Drago
>> Staff Engineer
>> L3 Narda-MITEQ
>> 435 Moreland Road
>> Hauppauge, NY 11788
>> 631-272-5947 / william.dr...@l-3com.com
>>
>> CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any
>> attachments are solely for the use of the addressee and may contain
>> information that is privileged or confidential. Any disclosure, use or
>> distribution of the information contained herein is prohibited. In the
>> event this e-mail contains technical data within the definition of the
>> International Traffic in Arms Regulations or Export Administration
>> Regulations, it is subject to the export control laws of the
>> U.S.Government. The recipient should check this e-mail and any attachments
>> for the presence of viruses as L-3 does not accept any liability associated
>> with the transmission of this e-mail. If you have received this
>> communication in error, please notify the sender by reply e-mail and
>> immediately delete this message and any attachments.
>> ___
>> 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] Changing ID's to UUID

[J Decker]

Re Integer shorness... UUID does seem wasteful - to have to ccompare such a
long value.
But the string compare will often fail within the first 4 bytes, or less,
making many comparisons less 'work' than an integer of the same... since
sqlite stores everything as text if you are in 123,000,000.. 123,000,001
 takes a lot of comparison to find difference.

yes it's 'inconveneitnt' I can't remember the code for item X is 99 so
just select where id=99 - easy to type, instead I have to expect my
database browser to support copy and paste.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changing ID's to UUID

[J Decker]

https://en.wikipedia.org/wiki/Birthday_problem

In probability theory ,
the *birthday problem* or *birthday paradox
*[1]
 concerns the
probability  that, in a set
of {\displaystyle
n}[image: n] randomly  chosen people,
some pair of them will have the same birthday
. By the pigeonhole principle
, the probability
reaches 100% when the number of people reaches 367 (since there are only
366 possible birthdays, including February 29
). However, 99.9% probability is
reached with just 70 people, and 50% probability with 23 people. These
conclusions are based on the assumption that each day of the year (except
February 29) is equally probable for a birthday.

UUID https://en.wikipedia.org/wiki/Universally_unique_identifier
Out of a total of 128 bits, Type 4 UUIDs have 6 reserved bits (4 for the
version and 2 other reserved bits), so randomly generated UUIDs have 122
random bits. The chance of two such UUIDs having the same value can be
calculated using probability theory (birthday problem
). Using the approximation

To put these numbers into perspective, the annual risk of a given person
being hit by a meteorite is estimated to be one chance in 17 billion,[5]
 which
means the probability is about 0.006 (6 × 10−11), equivalent to the
odds of creating a* few tens of trillions *of UUIDs in a year and having
one duplicate. In other words, only after generating 1 billion UUIDs every
second for the next 100 years, the probability of creating just one
duplicate would be about 50%.

-
Generating a recordset on client side - you'll have that ID in one place,
if it needs to change (on insert fails) you generate a new one and insert
again - everything else is likely related to that first field in the first
place.
If it's a case of merging datasets - how many of a type of record are you
really generating? 4? 5?

if it's a generation of millions of opints - than probably there should be
a different unique ID associated that increases them to a full unique uuid
space each.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL pragma question

[J Decker]

Also if you have any connections open, the journal may exist, so it doesn't
have to constantly open and close it.


On Fri, Oct 28, 2016 at 2:21 AM, Dan Kennedy  wrote:

> On 10/28/2016 03:16 AM, David Raymond wrote:
>
>> I'm playing around with WAL mode here for the first time, along with some
>> of the pragmas, and I'm getting some weird results. I was hoping someone
>> could let me know if I'm missing something, or if yes, it is indeed weird.
>>
>> For starters, I'm looking at the journal_size_limit pragma.
>> http://www.sqlite.org/pragma.html#pragma_journal_size_limit
>> In its description it does say that it works for WAL mode. "To always
>> truncate rollback journals and WAL files to their minimum size, set the
>> journal_size_limit to zero." So I create a new database, turn on WAL mode,
>> set that pragma, create a table, and insert some stuff into it. Then I
>> check the file sizes, and the -wal file hasn't shrunk at all. I made sure
>> it was large enough to go over the wal_autocheckpoint threshold and it
>> didn't shrink. I ran a manual "pragma wal_checkpoint;" and it didn't
>> shrink. I tried with specifying passive, full, and restart and it didn't
>> shrink. It seems that I can only get it to shrink by doing an explicit
>> "pragma wal_checkpoint(truncate);" But if that's the only way to shrink the
>> file down, then what's the point of the pragma here?
>>
>> Or, as is more likely, what obvious thing is my brain missing at the end
>> of the day?
>>
>
> In wal mode, the wal file is truncated according to "PRAGMA
> journal_size_limit" after the first transaction is written following a
> checkpoint. Or, technically, after a writer writes a transaction into the
> beginning of the physical file. So:
>
>   sqlite> PRAGMA journal_mode = wal;
>   wal
>   sqlite> PRAGMA journal_size_limit = 0;
>   0
>   sqlite> CREATE TABLE t1(a, b);
>   sqlite> INSERT INTO t1 VALUES(randomblob(10), randomblob(10));
>   /* WAL file is now roughly 200KiB */
>   sqlite> PRAGMA wal_checkpoint;
>   0|52|52
>   /* Still roughly 200KiB */
>   sqlite> INSERT INTO t1 VALUES(1, 1);
>   /* Now truncated to 4KiB */
>
> This is because the locking scheme only allows writers to modify the wal
> file, not checkpointers.
>
> Dan.
>
>
> ___
> 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] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[J Decker]

On Sat, Oct 15, 2016 at 11:58 AM, Keith Medcalf  wrote:

>
> I'd just sort the data into the order I wanted the result set presented in
> using an ORDER BY clause on the SELECT which retrieves the data ...
>

One other thought I saw someone else propose a while ago... treat the
number as floating piont, and you can insert at (prior+current)/2 ... 1.5,
1.25, 1.125 etc


>
> > > On Oct 15, 2016, at 11:12 AM, Keith Medcalf 
> wrote:
> > >
> > >> Is there a way to do this automagically (like a specialized INSERT
> > >> command?) in Sqlite?
> > >
> > > Unfortunately no, there is no way to do this on *ANY* database that
> uses
> > the relational database model.
> >
> > There’s no need to dive into database theory! I’ll play devil’s advocate
> > and say that this could pretty easily be done in SQLite by writing a
> > simple extension function like in inbetween(a, b) that takes two strings
> > and returns a string that sorts in between them, as I described
> > previously.
> >
> > Then you just make the table’s primary key a string and do
> >   INSERT INTO mytable (ID, …) VALUES (inbetween($firstid, $secondid),
> > …)
> > where $firstid and $secondid are the the primary keys of the two rows you
> > want to insert between.
> >
> > In real life you’d probably just implement inbetween() as part of your
> > program instead of as a SQLite function; I just wanted to prove that a
> > relational database can in fact do this.
> >
> > —Jens
>
>
>
>
> ___
> 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] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[J Decker]

On Sat, Oct 15, 2016 at 10:18 AM, Stephen Chrzanowski 
wrote:

> What you want to do is called using a linked list.  Each record knows what
> its previous record is.
>
> SQLite doesn't exactly have that capability directly.  No SQL engine that I
> know of has the capability.  Each row is unaware of any other row in that
> table.  That row is a unique entity to itself, oblivious to anything
> outside itself.  To answer your question, no, there is no SQL-FU that'll do
> what you want to do.
>
>
CTEs can handle returning linked lists as a normal recordset



> To get what you want done, you must rely on your application to handle
> this.
>
> Back in the day, when using "recordset"s and "collections", we had to rely
> on extra variables to associate what the previous and next records in our
> list were supposed to be.  So our recordset would have an extra integer to
> point to a memory location to where the next record was in memory.
>
> When I'd so this, since I knew what record I wanted to insert my new record
> after, I'd set a temporary variable to point to the old record I'd want to
> insert after, retrieve what that old records "next record" pointer is, set
> the old records "next record" to my the new record, then set my new records
> "next record" to what the old record "next record" was.
>
> You might have to do the same with this.  You know you want to insert
> something between ID 2 and 3.  So if your table had a reference field to
> what its next record was, you'd
> - retrieve row 2 to get its "next record" ID,
> - do the insert of your new data and set its "NextID" to some never to be
> used number (Like -1?)
> - figure out what record ID you put in (In this example, it'd be 6), then
> update row 2 to set its next value to 6.
>
> Then, theoretically in my head it works, when you make your call to
> retrieve your sorted list, you sort by your "NextID" field, not ID.
> Something like "order by NextID=-1,NextID".
>
>
> On Fri, Oct 14, 2016 at 9:29 AM, Thom Wharton <
> twhar...@northpointdefense.com> wrote:
>
> > Hello,
> >
> > I have a table of records in a Sqlite DB.  It contains 5 records. Each
> > record has a time-stamp which is not guaranteed to be unique.  To
> preserve
> > order (which is important in my project), I've given the table an integer
> > primary key (called ID) that is auto-increment.  Let's say I have the
> > following table...
> >
> >
> > IDDate   Type Size  Data
> >
> > 110OCT-08:13:47  Ether28sddsgsd...
> >
> > 210OCT-08:13:52  Ether77fdasfdsdsddssdg...
> >
> > 310OCT-08:13:52  Ether44zeasfkkfa...
> >
> > 410OCT-08:13:57  Ether33dartdg...
> >
> > 510OCT-08:14:03  Ether51afafsfafa...
> >
> >
> > I want to be able to programmatically insert a new record anywhere in
> that
> > table.  Let's suppose I want to create a new record between the records
> > whose ID are 2 and 3.  This new record would need to take the ID of 3,
> and
> > all subsequent records would need to have their primary keys updated.
> >
> > Is there a way to do this automagically (like a specialized INSERT
> > command?) in Sqlite?
> >
> > Thanks,
> >
> > Thom Wharton
> >
> >
> >
> > ___
> > 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] how is "pragma threads = 4" working

[J Decker]

that is a single sql statement though ; it doesn't spread itself amongst
threads, the compile option says 'limits number of threads' so if you had
an application (not sqlite shell) that had threads it could have a limit?

On Fri, Sep 16, 2016 at 5:13 AM, Adam Devita  wrote:

> Have you proven that the cpu is the bottleneck? Sorting a million rows
> seems like a lot, but even older single core cpus may be capable of 2
> billion ops per second. [I apologize if this has been sorted out
> already I've got about 2 days of history on the thread]
>
> regards,
> Adam DeVita
>
> On Fri, Sep 16, 2016 at 7:56 AM, Stephen Chrzanowski 
> wrote:
>
> > Although programmatically easily done, from the SQLite point of view,
> what
> > if that query, sans LIMIT, were a subquery, and the limit was put out on
> > the outside?  Would the inner query execute, use all the threads, then
> > return just one row on the outer?
> >
> > On Fri, Sep 16, 2016 at 7:29 AM, Richard Hipp  wrote:
> >
> > > On 8/14/16, Венцислав Русев  wrote:
> > > > My computer has 4 cores. I have compile sqlite like this "gcc
> > > > -DSQLITE_MAX_WORKER_THREADS=4 -DSQLITE_DEFAULT_WORKER_THREADS=4
> > shell.c
> > > > sqlite3.c -lpthread -ldl -o sqlite3". I made some tests and found
> that
> > > > "pragma threads = 4" doesn't decrease runtime of the query that
> sorts 1
> > > > milion records.
> > > >
> > > > SQLite version 3.8.8
> > > > sqlite> pragma threads;
> > > > 4
> > > > sqlite> CREATE TABLE event (
> > > >  ID INTEGER PRIMARY KEY NOT NULL,
> > > >  date   INTEGER NOT NULL,
> > > >  value  INTEGER NOT NULL );
> > > > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM event ORDER BY date LIMIT
> 1;
> > >
> > > The algorithm used for "ORDER BY ... LIMIT N" uses much less memory
> > > than a full-up "ORDER BY" because is only keeps track of the top N
> > > entries seen so far, discarding the rest.  But it also only uses a
> > > single thread.  If you want multiple threads to be used, you'll need
> > > to drop the LIMIT, though I imagine that would defeat your purpose,
> > > no?
> > >
> > > --
> > > 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
> >
>
>
>
> --
> --
> VerifEye Technologies Inc.
> 151 Whitehall Dr. Unit 2
> Markham, ON
> L3R 9T1
> ___
> 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] WHERE col IN tab

[J Decker]

IN is for sets, not another table.  I'm surprised sqlite didn't thrown an
error but probably what you mean is...

SELECT * FROM t1 join T2 on x=y;

SELECT * FROM t1 join (select y from t2) on x=y


select * from table where colName in (1,2,3,4)  /// woud return rows where
some column has a value of 1,2,3 or 4


On Fri, Sep 16, 2016 at 7:59 AM, Dominique Devienne 
wrote:

> Reading https://www.sqlite.org/src/tktview/0eab1ac7591f,
> (from a very recent thread) I was surprised to read that syntax.
>
> So I tried it in SQLite, and it works as shown in the ticket:
>
> C:\Users\DDevienne>sqlite3
> SQLite version 3.10.2 2016-01-20 15:27:19
> sqlite> CREATE TABLE t1(x INTEGER PRIMARY KEY NOT NULL, a,b,c,d);
> sqlite> CREATE TABLE t2(y INT);
> sqlite> insert into t1 values (1, 1,1,1,1), (2, 2,2,2,2);
> sqlite> insert into t2 values (2);
> sqlite> SELECT * FROM t1 WHERE x IN t2;
> 2|2|2|2|2
> sqlite> SELECT * FROM t1 WHERE x IN (select y from t2);
> 2|2|2|2|2
>
> But when I try the same thing with Oracle in SQL Dev OTOH, it fails:
>
> create table t (x number primary key, a number)
> create table tt (y number)
> SELECT * FROM t WHERE x IN tt
> ORA-00904: "TT": invalid identifier
> SELECT * FROM t WHERE x IN (select y from tt)
> OK (no rows)
>
> Is that <> SQL standard?
> Or an SQLite specific extension? Available in other RDBMs?
>
> Just curious. Thanks, --DD
> ___
> 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] SpeedTest1 Comparison of 32 vs 64 bit on Windows 10 13483.15

[J Decker]

CPU Core at 100% for entire test, x64 code is ~10% faster than x32 code.

So; IO bound things 32/64 doesn't matter so much; but compute bound,
through data already in memory gets significant improvments because of
addtional general purpose registers and optimal calling conventions for
things of fewer than 4 parameters. yields a better usage of your CPU?

and at 10% I said I'd be surprised if more than 5%; but potentially more if
I have a thing that fits in cache and I scan a lot?

subquery in a select was slower oddly enough... which was mentioned already
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CREATE FUNCTION

[J Decker]

And every other scripting language you might suggest for the question 'if
It did allow creating a function, what flavor of scripting would you like
it to be?'  has hooks for sqlite ... well except maybe TSQL.  (could I
script all my database access to a sqlite3 db from SQL Server?  Or as the
original poster suggested PLPGSQL? :)
I concur that C/C++ is already a pretty powerful scripting language...
though somewhat prohibits later changes on the fly as customer demands are
clarified... (nothing dynamic libraries and a good design can't overcome I
suppose)

On Mon, Aug 22, 2016 at 12:42 PM, Richard Hipp  wrote:

> On 8/22/16, Clemens Ladisch  wrote:
> >
> > But it should be mentioned that SQLite is an _embedded_ database, i.e.,
> it
> > is meant to be part of a program written in a 'real' programming
> language,
> > so with sqlite3_create_function(), there is no use case for the feature
>
> I was about to type the same.  SQLite has sqlite3_create_function()
> which is more powerful than CREATE FUNCTION and which is a better fit
> for the embedded database model.
>
> --
> 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] Will the next release include support for VS 2015 Update 3?

[J Decker]

On Thu, Aug 18, 2016 at 12:37 PM, Brian Fairservice <
brianfairserv...@gmail.com> wrote:

> Greetings,
>
> I need to work with a project that uses SQLite as an ADO data source.  I
> have visual studio 2015 Update 3.  I see the binaries on
> https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki are
> for update 2, and they don't work.


How don't they work?
Updates rarely break old things from working just make new things not
work the old way... not the other way around.


> Looking at the timeline, it appears
> that support for update 3 has been added on July 2nd.  The release
> scheduled for sometime this August doesn't seem to include support for
> update 3, judging by the notes on the release history.  Will support for vs
> 2015 update 3 be included?  Also, any idea if the release is scheduled for
> sometime in the next few days, or is it a matter of weeks?
>
> Thanks
> ___
> 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] 64-bit SQLite3.exe

[J Decker]

If you use cygwin (which noone ever should) make sure you to specify
-mno-cygwin or any output is significantly slower than any other build.
 (oh, I see that was deprecated several years ago, apparently now you just
get the slow code automatically in static format.)

(for instance one cause of slowness)
http://stackoverflow.com/questions/28410852/startup-is-really-slow-for-all-cygwin-applications

the runtime for file system access had heavy registry access because of
/cygdrive/xxx/  mapping support...



On Sat, Aug 13, 2016 at 10:17 AM, Random Coder 
wrote:

> On Sat, Aug 13, 2016 at 8:39 AM, Rousselot, Richard A
>  wrote:
> > Thanks, this does allow the library to load and process.  The thing is
> your build is by far the slowest I have received, slower even than the
> 32-bit version.
>
> Sorry about that, it was a debug build.
>
> https://new-bucket-2a9cf983.s3.amazonaws.com/sqlite64_release.zip
>
> Might be faster.
>
> > Could you tell me what version of SQLite you compiled, your compiler and
> the settings you used on each build?
>
> SQLite 3.14.0, built with MSVC 18.00.30723 for x64.  The debug version
> was built with /Od, this version is built with /O2 /Ot /Ox.  Both
> versions are built with the SQLITE options of SQLITE_ENABLE_FTS3
> SQLITE_ENABLE_STAT2 SQLITE_ENABLE_RTREE.
> ___
> 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] 64-bit SQLite3.exe

[J Decker]

I'm actually kind of surprised more people aren't like 'ya, why isn't 64
bit just available?' ( *pounds on tables* "We Want 64 Bit!", no? )
being fairly low level developers I'd think many of you would know 64 bit
mode has more general purpose registers to carry values and the default
calling ABI is improved to be more of a register centric model.
Both windows and linux 64 bit programs do run a hair faster than their same
32 bit builds.  It's not like night and day and I'd be surprised if it's as
high as 5% gain. Plus, who doesn't have more than 2G of memory and a 64 bit
system that's already responded?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit SQLite3.exe

[J Decker]

On Tue, Aug 9, 2016 at 10:08 PM, David Empson  wrote:

>
> > On 10/08/2016, at 3:30 PM, Rousselot, Richard A  centurylink.com> wrote:
> >
> > As I said, I am not a software engineer.  I could spend a few hours
> figuring this out and be fine but it will be painful for me.
> >
> > I see no downsides in a 64-bit CLI.  The last 32-bit Intel CPU was the
> PIII in 2004, no supported Windows OS requires 32-bit CPUs, the file size
> may be marginally bigger but who cares on a PC.  The 64-bit version will, I
> assume, happily work on DBs created in the 32-bit version.  And for those
> that need 32-bit for their applications and drivers still have access to
> the 32-bit DLL.  What am I missing?  Are windows command line tools 32-bit
> only?
>
> A 32-bit installation of Windows cannot run 64-bit executables (ignoring
> VM solutions).
>
> Because of the large installed base of 32-bit Windows, the Windows command
> line tools for SQLite needs to be available as 32-bit versions. If 64-bit
> versions were provided, they would need to be in addition to the 32-bit
> versions.
>
> There are an awful lot of 32-bit installations of Windows. This includes a
> lot of 32-bit installations of Windows on 64-bit processors, which exist
> for many reasons including defaults offered by the manufacturer, lack of
> 64-bit drivers, corporate policy decisions, reduced memory footprint in
> limited machines, or the user requiring 32-bit Windows in order to be able
> to run legacy 16-bit software (again, ignoring VM solutions).
>
>
If you're going that way; Android just pulled x86 support for their dev
tools.  Turns out noone in QA had a 32 bit computer; and when they posted a
message about it there wasn't a lot of 'no wait! I have x86!' actually not
one reply; just me saying 'ya, I can see most development machines are 64
bit; although; you shuold provide a patch now; but retooling the QA dept
for 10% of the market doesn't really make sense
see their numbers aren't 90% 32 bit, but rather 90% 64 bit, so really it
shuld be the 64 bit that's provided,  and MAYBE the 32 bit in addition to
it.


> ___
> 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

[J Decker]

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 
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

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

[J Decker]

Okay if this should change, I would recommand a new standard for all
libraries;  and since standards are so important maybe make them know about
it too...

do ... ORG_DOMAIN_APPLICATION_LIBRARY_MODULE_SOURCE_INCLUDED

where each piece becomes unqiqe so there's no collision.  I'd expect
compilers these days would support more than 32 characters required by the
standard (goodbye Borland BCC 3.1).

#ifndef ORG_SQLITE_SQLITE_SQLITE_INCLUDED

and copY and paste one other time...
I mean what editor doesnt' support double click to mark a word (the above
is within the genaral defitition of a 'word' ) to copy and click somewhere
to paste?

I mean ; how many times have I had a symbol in a header that collided with
sqlite? or ffmpeg? or zlib? or... wait like never.

someone probably just never considered deprecation of reservation as a
'law' and at least demote to 'recommended practice' because some obscure
compiler somewhere would start failing?




On Sat, Jul 9, 2016 at 5:50 PM, dandl  wrote:

> > Obviously the standard is broken/incorrect or your interpretation of it
> is
> > broken/incorrect.
>
> No, and the standard was very carefully written to say this, and it's easy
> to find references to back up this interpretation if you care to look for
> them. Or ask a question on SO.
>
> > Most API headers do the same thing.
>
> Yes, this is quite a common breach of the standard. That doesn't make it
> right.
>
> > Even the standard library does it, in
> > most compilers.
>
> Almost universally I would say. That is the entire point: these identifiers
> are reserved 'for the implementation', that is for the standard library to
> use, and no-one else.
>
> > Not all of them add the trailing _, but several do.  Whether
> > and particular one does or not seems to depend on whether the entropy of
> the
> > multiverse was odd or even at the time the API was generated.
>
> Irrelevant. The use of leading underscore followed by upper-case letter is
> in violation of the C standard S 7.1.3. And the standard has been unchanged
> since its first release, which is well before Sqlite was even thought of.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.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] Bug: SQLite's include guards are reserved identifiers

[J Decker]

On Fri, Jul 8, 2016 at 4:56 PM, Daniel Seither  wrote:

> Using clang 3.8 with -Wreserved-id-macro (enabled by -Weverything), I
> just noticed that SQLite uses include guards with a leading underscore,
> for example _SQLITE3_H_ in the amalgamation. According to the C
> standard, this is a reserved identifier, leading to undefined behavior:
>
> > All identifiers that begin with an underscore and either an uppercase
> > letter or another underscore are always reserved for any use. [...]
> > If the program declares or defines an identifier in a context in
> > which it is reserved (other than as allowed by 7.1.4), or defines a
> > reserved identifier as a macro name, the behavior is undefined.
>
> sqlite isn't a program :) it's a library.



> (Source: the C11 standard, Section 7.1.3, see [1] for the latest draft
> of C11 before publication, which should be identical to the finished text)
>
> I guess that means that the include guards should be changed to no
> longer use leading underscores. Any opinions on that?
>
>
That has been in the standard since K&R.
http://stackoverflow.com/questions/22027252/what-define-identifiers-are-reserved-to-the-preprocessor
"

(C99, 7.1.3.p1) "All identifiers that begin with an underscore and either
an uppercase letter or another underscore are always reserved for any use."

Now regarding K&R book 2nd edition it is written:

(K&R 2nd edition, 2.1) "Don't begin variable names with underscore,
however, since library routines often use such names."

And if CRM actually refers to CARM (C A Reference Manual by Harbison &
Steele):

(CARM 5th edition, 10.1.1 Reserved Library Identifiers): "The identifiers
that are reserved for C implementations to use include: [...] for macros,
keywords, or global variables, identifiers beginning with _ and and either
a second _ or an uppercase letter (except _ _ STDC_...)"




http://stackoverflow.com/questions/228783/what-are-the-rules-about-using-an-underscore-in-a-c-identifier

could make it _sQLITE3_H




> Daniel
>
> [1] http://www.open-std.org/jtc1/sc22/wg14/www/docs/n1570.pdf
> ___
> 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] Currency & SQLite

[J Decker]

On Tue, Jul 5, 2016 at 5:14 PM, Darren Duncan 
wrote:

> Ertan,
>
> You should read https://www.sqlite.org/datatype3.html#section_2 if you
> haven't already.
>
> SQLite has exactly 1 floating-point type, which is what you get no matter
> if you say float/double/etc, and it is 8 bytes, which corresponds to a
> Double in most programming languages.
>
>
the ALU on the CPU has exactly one type too - it uses double internally.

C# has 'decimal' which is a fixed 4 decimal points(err I guess it's
different)
http://stackoverflow.com/questions/618535/difference-between-decimal-float-and-double-in-net

but you're actually in delphi to the database so that doesn't help.
It would be best to keep it in scaled precision of maximum places you want
to keep ( pennies + 4 = 6 )... x * 1 000 000

0.97 * 10  != 0.97 + 0.97 + ... (10 times) ...

... if you count from 0.01 to 1.00' and test each one you'll find at least
one that fails.

But since SQL math is done as IEEE floating point it's not going to be the
same result as the machine nessecarily.



You can't change what SQLite uses other than explicitly not using a
> floating-point type; you have integer, float, blob, text, and that's it.
>
> Therefore, if you use anything other than a double in your app, you will
> be converting when storing or retrieving, which makes things slower and
> introduces loss, hence I say use Double everywhere.
>
> -- Darren Duncan
>
>
> On 2016-07-05 4:34 PM, Ertan Küçükoğlu wrote:
>
>> Hardware that the application will run is ~1Ghz Atom CPU, ~1GB RAM,
>> regular Laptop HDD (no SSD). Time to time, there will be calculations,
>> Network file transfer, and DLL function calls (all three) will be
>> simultaneously running. Application has Timers and Socket listening
>> components on it. My concern is to have a situation which "seems" like an
>> application freeze.
>>
>> On the other hand, will it be any help to increase accuracy to use
>> Currency (8 bytes fixed-point data type) in my application for Price and
>> Total fields, leave Amount field as Double?
>>
>> Thanks.
>>
>> -Original Message-
>> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:
>> sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Darren Duncan
>> Sent: Wednesday, July 6, 2016 1:20 AM
>> To: SQLite mailing list 
>> Subject: Re: [sqlite] Currency & SQLite
>>
>> On 2016-07-05 3:02 PM, Ertan Küçükoğlu wrote:
>>
>>> I am using Delphi 10 for programming. Current variables are defined as
>>> Double (8 bytes real), saved in Database using something
>>> "DataSet.FieldByName('Amount').AsFloat := My_double_variable" However, I am
>>> now considering to convert all Double datatypes to Single (4 bytes real)
>>> for performance issues.
>>>
>>
>> Do NOT change to using Single, always use Double, end to end.
>>
>> First of all, Double is what SQLite uses internally for floats, per its
>> documentation:
>>
>> "The value is a floating point value, stored as an 8-byte IEEE
>> floating point number."
>>
>> So if you're already using Double in your app then staying that way
>> should maintain accuracy, especially when you want accuracy for more than
>> say 3-5 decimal digits in total.
>>
>> Secondly, for financial applications, accuracy trumps performance.
>> Converting to Single at any time is just going to cause you trouble.
>>
>> And really, are you sure using Single would actually help performance?
>> Have you measured it in a real workload?  More likely on modern devices it
>> won't be any faster and you will have lost your accuracy for nothing.
>>
>> -- Darren Duncan
>>
>
> ___
> 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] Question about C# with SQLite

[J Decker]

if it was built with debug mode; probably the debug runtime doesn't exist
there.  Otherwise it's because the visual studio runtime required isn't
available.

On Mon, Jul 4, 2016 at 6:52 AM, Shouwei Li  wrote:

> Hi there,
>
> I have a project developed with .net 2015 and C#. I use SQLite as the
> server-less database. It works very well in my workstation. But it can not
> run on other PC. The error indicates:
>
> Unable to load DLL "SQLite.Intero.dll": The specified module could not be
> found.
>
> I already attach this dll with my program.
>
> I want to ask is there a manual talk about how to deploy a program to
> customer when we use the SQLite database.
>
> Thanks for your reading.
>
> --
> Best Regards!
> Shouwei Li
> ___
> 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] UNIQUE constraint violation

[J Decker]

Duplication can also result as part of the - in process - moving of rows.
To change the order of [1,2,3,4] to
[1,3,2,4]  there is(can be) a state that is [1,2,2,4] before the second
part that sets three back into 2.

This is a point where NULL can be useful to make the transition

[1,2,NULL,4]
[1,3,NULL,4]
[1,3,2,4]

( in which case NOT NULL is NOT recommended :)  )  It can be some other
unique value like -1; but in either case; some databases only allow one
NULL in unique anyway... and if you have two processes that are both in
partial steps of execution you can conflict that way too.

wrapping that in a transaction should suspend constraint checks until a
commiit   (IMO don't know if that's fact)

(or partial duplicates can happen if you get an exception that faults and
fails to complete the operation)


On Tue, Jun 28, 2016 at 2:18 PM, Joe Pasquariello  wrote:

> Firstly, the Index i_udatetime is Superfluous, you can remove it for
>> some efficiency points. (The Unique constraint already offers an Index
>> with udatetime as the left-most or main Index).
>>
>> All I can still suggest or ask is: Are you sure there are no duplicates?
>>
>> The correct SQL to check would be:
>>
>> SELECT A.*
>>FROM EventLog AS A
>>JOIN EventLog AS B ON B.udatetime=A.udatetime AND B.device=A.device
>> AND B.code=A.code AND B.type=A.type
>>   WHERE B.row_id <> A.row_id
>>
>> Any row that shows up is a duplicate that would violate that Unique Index.
>>
>> Further to this, I'm not sure if you omitted the other fields for
>> brevity or to explain the problem, but that INSERT query should never
>> work since you specify all those columns as NOT NULL, then you do not
>> give it DEFAULT values, and then you omit them from the INSERT, which
>> means it must fail on the NOT NULL constraint.
>>
>> If this is not the case, and the above query doesn't show up any rows,
>> and the constraint still fails - mind letting us have a copy of the DB
>> file that produces that violation?
>>
>>
>> Cheers,
>> Ryan
>>
>>
> Thank you, Ryan. I'm embarassed to say I don't know how to reply to your
> response. When I posted my original question, I simply sent email to the
> mailing list. I viewed your response on Nabble, but it doesn't seem to
> allow me to reply. How can I view your response and reply somewhere other
> than Nabble? To create this email, I copied and pasted from Nabble. I'm
> afraid it will show up as a new topic. There has to be a better way.
>
> To the point, your query to find duplicate records showed there were some
> duplicates after all.
>
> I removed the duplicates and the SQL as shown in my original post worked.
> I don't know what you mean about the insert statement being incorrect. It
> explicitly sets all fields in the new table except rowid. My understanding
> was that since rowid is INTEGER PRIMARY KEY, inserts to the new table would
> get new rowid values, in the order inserted, which is what I want, and it
> seems to be doing that correctly.
>
> The query I was using to find duplicates is one that I found on
> StackOverflow. I'm very new to SQL. Can you tell me why this query returns
> no records while yours does? If I change the last clause to having
> count(*)=1, the result has the same number of rows as the table. I thought
> that meant it was doing what I wanted, but obviously it doesn't.
>
> select udatetime,device,localtime,code,type,text,0 as status, count(*) as
> the_count
> from eventlog
> group by udatetime,device,code,type,status
> having count(*) > 1
>
> >
>
>
>
>
>> On 2016/06/28 9:37 PM, Joe Pasquariello wrote:
>>
>> > Hello,
>> >
>> > I'm a novice user of Sqlite, and could use some help. The code below
>> > is intended to update a simple table to a new format. The changes
>> > include reordering columns, dropping one column, and adding a new
>> > "status" column (which is always 0). A constraint violation is being
>> > reported on UNIQUE constraint for the new table. I assume this means
>> > there is a row in the existing table that violates the constraint
>> > imposed on the new table, but I've queried the existing table for rows
>> > that would violate the constraint, and I don't find any. Is there
>> > something else that could cause the violation?
>> >
>> > Thanks,
>> >
>> > Joe
>> >
>> > BEGIN TRANSACTION;
>> > DROP TABLE IF EXISTS TempEventLog;
>> > ALTER TABLE EventLog RENAME TO TempEventLog;
>> >
>> > CREATE TABLE IF NOT EXISTS EventLog(
>> >   rowid INTEGER PRIMARY KEY,
>> >   udatetime INTEGERNOT NULL,
>> >   deviceCHAR(16)   NOT NULL   COLLATE NOCASE,
>> >   localtime CHAR(32)   NOT NULL   COLLATE NOCASE,
>> >   code  INTEGERNOT NULL,
>> >   type  CHAR(16)   NOT NULL   COLLATE NOCASE,
>> >   text  CHAR(64)   NOT NULL   COLLATE NOCASE,
>> >   statusINTEGERNOT NULL,
>> >   UNIQUE(udatetime,device,code,type,status)
>> > );
>> >
>> > CREATE INDEX IF NOT EXISTS i_udatetime ON Ev

Re: [sqlite] Order of applying subquery in SELECT statement

[J Decker]

On Mon, Jun 20, 2016 at 5:18 PM, Keith Medcalf  wrote:

>
> You have all ill-phrased correlated subquery.  Only by putting c.id in
> the select list and adding c.id to the group go do you have a properly
> phrased correlated subquery.
>
> However,
>
> The projection (join) will be performed first using whatever order and
> indexes the optimizer decides to use to do that.  Whenever it is going to
> "pop out" a result the subquery will be executed to provide that data.
> That means that if there are multiple values of c.id to go with a single
> value of a.id, you will get a result for "one of" those c.id values.
> Which one is an implementation detail but is entirely deterministic.
>
>
saying [some] 'one of' mean it isn't deterministic .   As data changes,
id's get added, analyzes and optimizations are performed... the results may
change with time.  If you're collapsing a whole branch of a tree into a
single line that has one of the values from somewhere on the branch, you
might want to think again about what you're doing.  as long as the dataset
is exactly the same or is modified in ways that don't break the index
lookup ordering you'll be 'stable'

I dunno it's not deterministic for all time





> > -Original Message-
> > From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> > boun...@mailinglists.sqlite.org] On Behalf Of Harmen de Jong - CoachR
> > Group B.V.
> > Sent: Monday, 20 June, 2016 09:37
> > To: General Discussion of SQLite Database
> > Subject: [sqlite] Order of applying subquery in SELECT statement
> >
> > I have a query with a subquery (using an aggregate function) in the
> SELECT
> > statement. Will this subquery be executed before or after the GROUP BY?
> > For example:
> > SELECT a.id, (SELECT max(orderno) FROM b WHERE b.c_id=c.id) as
> maxorderno
> > FROM a INNER JOIN c on c.a_id=a.id
> > GROUP BY a.id
> >
> > Best regards,
> > Harmen
> > ___
> > 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] Order of applying subquery in SELECT statement

[J Decker]

On Mon, Jun 20, 2016 at 8:37 AM, Harmen de Jong - CoachR Group B.V. <
har...@coachr.com> wrote:

> I have a query with a subquery (using an aggregate function) in the SELECT
> statement. Will this subquery be executed before or after the GROUP BY?
> For example:
> SELECT a.id, (SELECT max(orderno) FROM b WHERE b.c_id=c.id) as maxorderno
> FROM a INNER JOIN c on c.a_id=a.id
> GROUP BY a.id
>
>
that parens would suggest that the subs-slect is executed first. before
anything else...
but the reamining select also happens before the group by; it would never
be AFTER the group by, unless the group by was on the subselect, then it
would run between some of the select.s


Best regards,
> Harmen
> ___
> 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] Managing SQLite indices.

[J Decker]

sqlite supports 'natural join' which allows you to omit the on clause for
well structured databases.

My database model is really a C# DataSet ( which contains DataTable, which
contain DataColumn and DataRow, (and this is superfluous, but row has a
reference to it's table and therefore the columns to relate to the array of
elements it contains) DataRelations are in DataSets between DataTables, and
they are foriegn key definitions; with OnUpdate, OnDelete, et al behaviors
definable.

I have a module that's open source that takes a data table and many common
operators and generates create table and constratint thigns for DataSets.
The DataSet CAN be designed using the dataset designer (it's OK; nothing
that you couldn't find better of in the 90's).


table1
   table1 + _id  --automatic primary key; I use object in the datatable now
as the type so the key can be GUID or int autoincrement.
   table1 + _name -- common text field that this ID might be printable in
part as... could be _description, _text, _ToString ?

table2
table2 + _id
table1 + _id

DataRelation( table2, table1.TableName+"_id", table1, table2.TableName
).onDelete = (?rule enum).Cascade. *shrug* too specific I know

which can be wrapped in something ilke makeDataRelation( table2, table1 );
automatically and consistently.

The problem becomes self-recursive keys which should themselves have just
tablename_id,  but would conflict with themselves...

map1
 map1_id int auto_increment PRIMARY KEY,
 parent_map1_id int
 node_info_id int

where it joins  ( parent_map1_id = map1_id )  ...

---
I did learn in school something like how to make words plural

public static string StripPlural( string s )
{
if ( ( s.Length > 3 ) && String.Compare( s, s.Length - 3,
"ies", 0, 3 ) == 0 )
return s.Substring( 0, s.Length - 3 ) + "y";
if ( ( s.Length > 3 ) && String.Compare( s, s.Length - 3,
"ses", 0, 3 ) == 0 )
return s.Substring( 0, s.Length - 3 ) + "s";
if ( ( s.Length > 1 ) && s[s.Length - 1] == 's' )
return s.Substring( 0, s.Length - 1 );
return s;
}


So I did add rules so I could take a plural table name like "Players" and
make Player_id, Player_name, etc...


And many tables had an ID that was an _info record or a _decription of
something and "member_info" to be member_id, etc...

public static string StripInfo( string s )
{
int trim = s.IndexOf( "_info" );
if( trim > 0 )
return s.Substring( 0, trim );
trim = s.IndexOf( "_description" );
if( trim > 0 )
return s.Substring( 0, trim );
return s;
}

We did try to suffix tables with sort of a data type; kind of the table
name is it's object-type  maybe there's no justification for chopping
it, and in correctness the full tablename shoudl be used so players and
player_info and player_desc don't all collide at some point; I could blame
it on having to conform to an existing system?  The new system was
certainly easy to automate and even work with by hand.


prefixes can be more useful a DataSet can have a prefix that gets
applied to all tables in it, so if you had a small cluster of information
like user_permissions, which has a grouping of tables, they could all share
a similar prefix and be located together when browsing.

(there are no relations between datasets, but table names would never
include their prefix when referencing them... )

Please do feel free to rip giant holes in anything I've said :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[J Decker]

On Wed, May 18, 2016 at 2:39 AM, Cecil Westerhof  
wrote:
> I would be interested what you find wrong about Git and is better in your
> version control system.
>

git blows; monotone forever!

> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite incompatibility with Postgres

[J Decker]

On Sun, May 15, 2016 at 10:02 PM, Darren Duncan  
wrote:
> On 2016-05-15 9:56 PM, J Decker wrote:
>>
>> On Sun, May 15, 2016 at 9:29 PM, Darren Duncan 
>> wrote:
>>>
>>> On 2016-05-15 12:35 AM, Simon Slavin wrote:
>>>>
>>>>
>>>> All true.  But it brings up a question.  Suppose the following:
>>>>
>>>> first   second
>>>> -   --
>>>> MarkSpark
>>>> Emily   Spark
>>>> MarySoper
>>>> Brian   Soper
>>>>
>>>> SELECT first,second FROM members ORDER BY second LIMIT 3
>>>>
>>>> Without looking up either a standards document for SQL or the
>>>> documentation for your favourite implementations of SQL, answer this
>>>> question:
>>>>
>>>> Does the documentation for your favourite implementation of SQL state
>>>> that
>>>> you'll get the same rows every time you execute the above "SELECT" ?
>>>
>>>
>>> I think a proper solution for this then is to treat the LIMIT as
>>> approximate
>>> rather than exact; it indicates a desire rather than a promise.
>>>
>>> In the scenario you describe, the query should return either 2 rows or 4
>>> rows, so that ALL of the rows whose second field value of "Spark" are, or
>>> are not, returned.  Projecting this to there not being an ORDER BY
>>> clause,
>>> either all rows are returned or zero rows are returned.  Thus the result
>>> is
>>> deterministic.
>>
>>
>> even if it did for 'spark' rows (which either case you suggest would
>> be horrible) 'soper' would still be non-deterministic, and rebuilding
>> indexes could reorder the results.
>
>
> No, it is still deterministic.
>
> The ORDER BY clause specified a partial order of the results, not a total
> order.
>
> What I specified returns only complete groups of rows where within each
> group the rows are unordered but the groups as a whole are ordered relative
> to each other.
>
> The fact this is deterministic would probably be more clear if the result
> rows were nested, one outer row per "group" that I mentioned.  But even if
> not, the caller knew that they were only ordering by second but selecting
> first, so if they see multiple rows with the same second value, they know
> that those rows are not sorted between themselves, only that rows with
> different second values are sorted relative to each other.
>
> So fully deterministic.
>

'SELECT first,second FROM members ORDER BY second LIMIT 3' (that's
mysql format right?)

I don't see a full set as a requirement (such that the output would be
2 or 4 records and not the 3 I asked for...) .  the query implies 3
rows, not 3 sets.

SELECT first,second FROM members ORDER BY second LIMIT 3,3 (for the
next 3 lines I'm displaying on a form for instance)


and specifying that the result set includes a first name, the result
sets taken as a hole are not guaranteed equal (procedurally and in
practice they may be, but pessimistically...).



>
> -- Darren Duncan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite incompatibility with Postgres

[J Decker]

On Sun, May 15, 2016 at 9:29 PM, Darren Duncan  
wrote:
> On 2016-05-15 12:35 AM, Simon Slavin wrote:
>>
>> On 15 May 2016, at 6:04am, Darren Duncan  wrote:
>>
>>> You seem to be forgetting the fact that LIMIT/OFFSET is not its own
>>> clause, rather it is an extension to the ORDER BY clause and only has
>>> meaning within the context of the ORDER BY it is part of.
>>
>>
>> All true.  But it brings up a question.  Suppose the following:
>>
>> first   second
>> -   --
>> MarkSpark
>> Emily   Spark
>> MarySoper
>> Brian   Soper
>>
>> SELECT first,second FROM members ORDER BY second LIMIT 3
>>
>> Without looking up either a standards document for SQL or the
>> documentation for your favourite implementations of SQL, answer this
>> question:
>>
>> Does the documentation for your favourite implementation of SQL state that
>> you'll get the same rows every time you execute the above "SELECT" ?
>
>
> I think a proper solution for this then is to treat the LIMIT as approximate
> rather than exact; it indicates a desire rather than a promise.
>
> In the scenario you describe, the query should return either 2 rows or 4
> rows, so that ALL of the rows whose second field value of "Spark" are, or
> are not, returned.  Projecting this to there not being an ORDER BY clause,
> either all rows are returned or zero rows are returned.  Thus the result is
> deterministic.

even if it did for 'spark' rows (which either case you suggest would
be horrible) 'soper' would still be non-deterministic, and rebuilding
indexes could reorder the results.

>
> Whether returning above or below the limit is done, is a separate thing to
> decide, though I suggest returning above is better.
>
> -- Darren Duncan
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite incompatibility with Postgres

[J Decker]

Someone should wake the SQL standard committee and nominate sqlite
being the broadest used so should fill some gaps in specification with
it. :)  and conversely blame the others for their incompatibility :)

[sqlite] 64bit DLL vs 32bit

[J Decker]

sorry that is LISTDLLS  no ' or space ... and not singular.

On Tue, May 10, 2016 at 7:02 PM, J Decker  wrote:
> In general...
>
> while sqlite tool in question is running one could run listdll's in an
> admin console window and see... listdll takes a executable name to
> filter its list...
>
> maybe you have another compatible one in the path it's finding?
> Because it's not Any CPU.

[sqlite] 64bit DLL vs 32bit

[J Decker]

In general...

while sqlite tool in question is running one could run listdll's in an
admin console window and see... listdll takes a executable name to
filter its list...

maybe you have another compatible one in the path it's finding?
Because it's not Any CPU.

[sqlite] 64bit DLL vs 32bit

[J Decker]

On Tue, May 10, 2016 at 2:23 AM, Jeremy Nicoll
 wrote:
> On Mon, 9 May 2016, at 15:48, jicman at barrioinvi.net wrote:
>
>> Well, I can not use the SQLite 64bit DLL in a 64bit environment with a
>> 32bit application.
>
> I was under the impression that I'm using the 64-bit DLL on a W8.1
> 64-bit system,
> with the 32-bit tools.  Does that mean that there's soe clever trick in
> the tools to make
> that work?
No it means you're using 64 bit tools or 32 bit dll.

>
> --
> Jeremy Nicoll - my opinions are my own.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 64bit DLL vs 32bit

[J Decker]

There are reasons for 64bit to be faster - more registers to work with.

(from https://en.wikipedia.org/wiki/X86-64 )
In addition to increasing the size of the general-purpose registers,
the number of named general-purpose registers is increased from eight
(i.e. eax, ebx, ecx, edx, ebp, esp, esi, edi) in x86 to 16 (i.e. rax,
rbx, rcx, rdx, rbp, rsp, rsi, rdi, r8, r9, r10, r11, r12, r13, r14,
r15).

Generally the ABI
(https://msdn.microsoft.com/en-us/library/ms235286.aspx) passes most
arguments by register instead of by stack 

"The x64 Application Binary Interface (ABI) is a 4 register fast-call
calling convention, with stack-backing for those registers. There is a
strict one-to-one correspondence between arguments in a function, and
the registers for those arguments. Any argument that doesn?t fit in 8
bytes, or is not 1, 2, 4, or 8 bytes, must be passed by reference."

32 bit apps are a tiny percent slower than 64 ... or rather the same
speed they always were and 64 bit is a tiny percent faster because of
the above reasons.

[sqlite] Fastest way to backup/copy database?

[J Decker]

On Thu, May 5, 2016 at 9:38 AM, Rob Willett
 wrote:
> Mmmm?. Initial tests are not conclusive, it does look as if using rsync
> ?-inplace does speed things up but nowhere near as much as we anticipated.
>
> Testing consisted of a 6GB test database which is a backup copy from a few
> months ago.
>
> We timed copying the database over using cp
>
> # time cp tfl.sqlite.backup t1
>
> real2m30.528s
> user0m0.052s
> sys 0m10.403s
>
> We then edited the database and deleted the contents of a table that would
> have changed over the lifetime of the database. We will freely admit we have
> no idea where this table is in the database file and have no intention of
> finding out. Thats SQLites problem :) The file had 65,000 lines or so and
> would have been updated regularly at the start of the database and over the
> last few months would have had small daily updates but they would be getting
> fewer and fewer.
>
> We then did
>
> # time rsync -av --inplace --no-whole-file tfl.sqlite.backup test_db
> sending incremental file list
> tfl.sqlite.backup
>
>

was the file already in place with sending changes?  Or is it a full
copy of the whole thing all the time?   if the later... well cp is
gonna really be as good as it gets

>
> sent 564,081,269 bytes  received 623,255 bytes  4,805,995.95 bytes/sec
> total size is 6,067,933,184  speedup is 10.75
>
> real1m57.689s
> user2m4.947s
> sys 0m8.952s
>
> The ?no-while-file is apparently needed as well as ?inplace.
>
> We checked the md5sums at the end
>
> # md5sum tfl.sqlite.backup test_db t1
> b5bd91cc9b49ee1f54a8a2d013005586  tfl.sqlite.backup
> b5bd91cc9b49ee1f54a8a2d013005586  test_db
> b5bd91cc9b49ee1f54a8a2d013005586  t1
>
> So we can see that the file integrity has been preserved which is what we
> expect but its always good to check. Yes we know that md5sum is not perfect
> but for this purpose its fine. However the rsync vs cp time is not as big a
> difference as we expected. Its still taking 80% of the time of the cp.
>
> Our first thoughts are that 80% is still better than 100% so thats good, our
> second thoughts are that we need to test this on a more representative set
> of changes to the database. Deleting the contents of a table that has been
> changed over the lifetime of the database may be the worst-case scenario as
> it might well touch many, many pages in the database. We are certainly not
> expert enough to comment on this assumption of how rows are distributed in
> the database and if anybody would like to chip in, please do so.
>
> Our intention now is to take a more recent and representative database, run
> a days and a weeks set of database transactions through it which is easy
> enough, though takes time, and see how that compares.
>
> Thanks for the suggestion,
>
> Rob,
>
> On 5 May 2016, at 16:42, J Decker wrote:
>
>> Instead of cp, rsync might help it is able to send delta changes.
>>
>> On Wed, May 4, 2016 at 10:55 AM, Rob Willett
>>  wrote:
>>>
>>> Scott,
>>>
>>> OK, We can see how to do this (I think). Our app is written in Perl and
>>> we?d
>>> just need to capture the command we write down. The only issue I can
>>> think
>>> of is the prepare statement and making sure we capture the right SQL
>>> command. W
>>>
>>> We?ll dig into it and have a look,
>>>
>>> Thanks for taking the time to reply.
>>>
>>> Rob
>>>
>>>
>>> On 4 May 2016, at 18:52, Scott Robison wrote:
>>>
>>>> On Wed, May 4, 2016 at 11:47 AM, Rob Willett
>>>> 
>>>> wrote:
>>>>
>>>>> Scott,
>>>>>
>>>>> Thats an interesting idea. Is there an option in SQLite to do this for
>>>>> us,
>>>>> or do we have to write a small shim in our app?
>>>>>
>>>>> I like the idea of this as its simple and elegant.
>>>>
>>>>
>>>>
>>>>
>>>> It would require a little extra work on your part. Nothing built into
>>>> the
>>>> system that would accomplish this directly. However, I've done similar
>>>> things and they don't involve a ton of overhead. You could use another
>>>> SQLite database as the append only log, or a simple text file.
>>>>
>>>> I'm not aware of a free lunch solution, sadly.
>>>>
>>>>
>>>>>
>>>>>
>>>>> Rob
>>>>>
>>>>>
>>>>> On 4 May 2016, at 16:51, Scott R

[sqlite] Fastest way to backup/copy database?

[J Decker]

Instead of cp, rsync might help it is able to send delta changes.

On Wed, May 4, 2016 at 10:55 AM, Rob Willett
 wrote:
> Scott,
>
> OK, We can see how to do this (I think). Our app is written in Perl and we?d
> just need to capture the command we write down. The only issue I can think
> of is the prepare statement and making sure we capture the right SQL
> command. W
>
> We?ll dig into it and have a look,
>
> Thanks for taking the time to reply.
>
> Rob
>
>
> On 4 May 2016, at 18:52, Scott Robison wrote:
>
>> On Wed, May 4, 2016 at 11:47 AM, Rob Willett
>> 
>> wrote:
>>
>>> Scott,
>>>
>>> Thats an interesting idea. Is there an option in SQLite to do this for
>>> us,
>>> or do we have to write a small shim in our app?
>>>
>>> I like the idea of this as its simple and elegant.
>>
>>
>>
>> It would require a little extra work on your part. Nothing built into the
>> system that would accomplish this directly. However, I've done similar
>> things and they don't involve a ton of overhead. You could use another
>> SQLite database as the append only log, or a simple text file.
>>
>> I'm not aware of a free lunch solution, sadly.
>>
>>
>>>
>>>
>>> Rob
>>>
>>>
>>> On 4 May 2016, at 16:51, Scott Robison wrote:
>>>
>>> This is going to become a bigger problem for us as the database will
>>
>> only get bigger so any advice welcomed.
>>
>
 Perhaps, rather than backing up the live data, you create an append only
 log of each and every query you send to the database. Should you need to
 restore, you replay the log of statements. Or at the appointed backup
 time,
 you replay the day's log of statements into another database. No need to
 ever take the live database offline at the cost of slightly longer
 running
 commands during the day to handle the append operation.
 ___
 sqlite-users mailing list
 sqlite-users at mailinglists.sqlite.org
 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> Scott Robison
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Illegal SQL not rejected

[J Decker]

On Fri, Apr 29, 2016 at 10:00 AM, Jann Roder  
wrote:
> Hi,
> It seems like a too obvious omission to not be intentional. But I wonder why 
> a query like
>
> SELECT SUM(A)
> FROM TABLE
> GROUP BY B
> HAVING A <> 0
>
> Is not rejected. MS SQL server gives you this error message in this case:
>
> Column ?A? is invalid in the HAVING clause because it is not contained in 
> either an aggregate function or the GROUP BY clause.
>
> It's not even clear to me what SQLite does with a query like that.
>

OMG MSSQL should not be the standard for 'error'
There is certainly nothing wrong logically with that statement, nor abiguity.


Like select option_id from option_map join option_value on
option_map.option_id=option_value.option_id

ERROR: abiguous option_id  .  IF it chose either one aribrarily just
on a 'command not found, shall I fake it?' sort of methodology, it's
the same value.

or adding an order by requires me to specifuy each and every column
explcitly instead of allowing *'  (sorry this explation if vague, I
don't really understand why it's an error, I just give up and do it
the way MSSQL wants, MySQL would certainly have no issue with it, nor
I suspect does sqlite.



> Jann
>
>
>
>

[sqlite] How to register System.Data.SQLite as a data source?

[J Decker]

Those look like the ODBC provider list...

http://www.ch-werner.de/sqliteodbc/

On Wed, Apr 27, 2016 at 3:55 PM, Joe Mistachkin  
wrote:
>
> Drago, William wrote:
>>
>> I did look at Installer.cs, but I'm not knowledgeable enough to
>> understand what it's doing.
>>
>
> It modifies the registry and several .NET configuration files in
> order to "install" the ADO.NET provider for System.Data.SQLite.
>
>>
>> I noticed "what-if" mode and maybe now I'm not so averse to
>> running the setups.
>>
>
> That feature is dual purpose:  1) For being able to test the tool
> without making any system changes and 2) Permitting end-users to
> see what system changes would be made by the tool without actually
> making any system changes.
>
>>
>> Back to my question... Some Windows applications claim to work
>> with any database as long as you choose the appropriate
>> "provider" from the drop down list. On my computer the providers
>> listed are:
>>
>
> I suspect this is the list of ADO.NET providers managed by the .NET
> Framework itself, via one of the aforementioned configuration files.
>
> In that case, the installer tool is what you are looking for.  It
> is possible to run the tool in such a way that it will only modify
> those configuration files and nothing else.  Although, now that I
> think about it, I think putting the System.Data.SQLite assembly in
> the GAC may also be required for this.
>
> Offhand, I'm not sure exactly what command line arguments you'll
> need; however, they are all handled by the FromArgs() method.
> They all set various fields of that class in order to alter the
> behavior of the actual installation phase.  Here are some
> (untested) command line arguments to try (all on one line):
>
> -install true -wow64 true -installFlags
> "CoreGlobalAssemblyCache DbProviderFactory"
> -tracePriority Lowest -verbose true -noCompact true
> -noNetFx20 true -noNetFx35 true -noNetFx45 true
> -noNetFx451 true -noNetFx452 true -noNetFx46 true
> -noNetFx461 true -noVs2005 true -noVs2008 true
> -noVs2012 true -noVs2013 true -noVs2015 true
> -confirm true
>
> I've purposely omitted the -whatIf option from the above example
> command line.  However, it would be required, with a value of
> "false", if you actually wanted to make changes to the system.
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3 command line, read-only

[J Decker]

On Tue, Apr 26, 2016 at 10:56 PM, J Decker  wrote:
> https://www.sqlite.org/wal.html
>
> "5. It is not possible to open read-only WAL databases. The opening
> process must have write privileges for "-shm" wal-index shared memory
> file associated with the database, if that file exists, or else write
> access on the directory containing the database file if the "-shm"
> file does not exist."
>
> second, I'm pretty sure that WAL mode is written to the database, so
> it remembers what journal mode it was using... which if it wasn't WAL
> would require writing to the database.

more from wal.html

"Persistence of WAL mode

Unlike the other journaling modes, PRAGMA journal_mode=WAL is
persistent. If a process sets WAL mode, then closes and reopens the
database, the database will come back in WAL mode. In contrast, if a
process sets (for example) PRAGMA journal_mode=TRUNCATE and then
closes and reopens the database will come back up in the default
rollback mode of DELETE rather than the previous TRUNCATE setting.

The persistence of WAL mode means that applications can be converted
to using SQLite in WAL mode without making any changes to the
application itself. One has merely to run "PRAGMA journal_mode=WAL;"
on the database file(s) using the command-line shell or other utility,
then restart the application.

The WAL journal mode will be set on all connections to the same
database file if it is set on any one connection."

[sqlite] sqlite3 command line, read-only

[J Decker]

https://www.sqlite.org/wal.html

"5. It is not possible to open read-only WAL databases. The opening
process must have write privileges for "-shm" wal-index shared memory
file associated with the database, if that file exists, or else write
access on the directory containing the database file if the "-shm"
file does not exist."

second, I'm pretty sure that WAL mode is written to the database, so
it remembers what journal mode it was using... which if it wasn't WAL
would require writing to the database.

[sqlite] Is it possible that dropping a big table takes very long

[J Decker]

If you upload a file to google drive and later try to replace it with
a different version google drive often corrupts it.  Always delete and
upload a new version.

On Wed, Apr 20, 2016 at 4:04 PM, jungle Boogie  
wrote:
> On 20 April 2016 at 14:55, Cecil Westerhof  wrote:
>> This one I download, unpacked and tried. It worked. So it should be
>> correct now:
>> https://drive.google.com/file/d/0BzW5q7uL-6z0UjFhRWZWSENnZW8/
>
>
> Windows 8.1 4 gigs of RAM, dell latitude with i5 process from a few
> years ago, lots of tabs and apps open, here's some results.
>
> SQLite version 3.12.1 2016-04-08 15:09:49
>
> sqlite> drop table testuniqueuuid;
> Run Time: real 9.763 user 1.015625 sys 2.031250
>
> sqlite> delete from testuniqueuuid;
> Run Time: real 19.149 user 1.156250 sys 2.109375
>
> I didn't bother with secure deletes in my case.
>
> This whole thread sounds like a good use case for flame graphs:
> http://brendangregg.com/flamegraphs
>
>
> --
> ---
> inum: 883510009027723
> sip: jungleboogie at sip2sip.info
> xmpp: jungle-boogie at jit.si
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Primary key values can be NULL

[J Decker]

I would total expect any column I created without NOT NULL (double
negative) to allow NULL whether INDEX or UNIQUE or PRIMARY is applied
additionallywhat database does otherwise?  MSSQL?

On Thu, Apr 14, 2016 at 2:56 PM, R Smith  wrote:
>
>
> On 2016/04/14 10:23 PM, Cecil Westerhof wrote:
>>
>> 2016-04-14 22:10 GMT+02:00 Richard Hipp :
>>
>>> On 4/14/16, Cecil Westerhof  wrote:

 Yes that makes sense. But could not a type of PRAGMA be used? So if the
 PRAGMA is not defined the old functionality and your historical data is
 save. And if the PRAGMA is defined the new functionality.

>>> Easier:  Just declare the columns in question as NOT NULL.  Or use a
>>> WITHOUT ROWID table which *does* enforce NOT NULL.
>>>
>> The problem is that you do not expect that values in the primary key can
>> be NULL, so you do not use NOT NULL. I now know that I need to do this and
>> I do it now, but the unaware will be bitten, just as I was. It was not a
>> big problem, because I just started playing with SQLite, but when you have
>> a very big database which gets a lot of data every day and a year later
>> you
>> find out you are bitten by this ?
>
>
> What makes you "expect" this?  Nothing in the SQLite documentation I hope.
> Is it perhaps habit based on how some other DBs do it?
>
> How will "those who don't know about NOT NULL" be better serviced by a
> pragma which they also don't know about?
> The documentation is very clear on the matter, even a cursory glance would
> educate "the unaware".
>
> Making more things with more documentation to read, in order to help people
> with other documentation they didn't read - not a salient course of action I
> think.
>
> Cheers,
> Ryan
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] autoincrement

[J Decker]

Yes, you can get the create statement from sqlite_master table

On Wed, Apr 13, 2016 at 4:54 AM, Igor Korot  wrote:
> Hi,
> Is it possible to get whether the column is set to autoincrement or not?
>
> PRAGMA table_info() does not give such info...
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compiling SQLite dll 64 bits in MSVS

[J Decker]

On Tue, Apr 5, 2016 at 2:09 PM, Bart Smissaert  
wrote:
> I am not using .def files, but use commandline settings from VS:
>
> All options:
>
> /GS /TC /W3 /Zc:wchar_t /Zi /Gm- /O2 /Fd"x64\Release\vc120.pdb" /fp:precise
> /D "WIN32" /D "NDEBUG" /D "_WINDOWS" /D "_USRDLL" /D
> "SQLITE3_STDCALL_EXPORTS" /D "_WINDLL" /errorReport:prompt /WX-
> /Zc:forScope /Gz /MD /Fa"x64\Release\" /EHsc /nologo /Fo"x64\Release\"
> /Fp"x64\Release\SQLite3_StdCall.pch"
>
> Additional Options:
>
> sqlite3.c /Gz -DSQLITE_API=__declspec(dllexport) -O2
> -DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_SYSTEM_MALLOC=1
> -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_EXPLAIN_COMMENTS=1
> -DSQLITE_SQLITE_ENABLE_RTREE=1-DSQLITE_ENABLE_FTS4=1 -link -dll
> -out:SQLite3_StdCall.dll
>
>

add

SQLITE_API=__declspec(dllexport)

from like line 12...

http://pavel.cernohorsky.name/blog/2015/09/sqlite_64-bit_binaries_for_windows_and_how_to_build_them_with_visual_studio_2015.php

You missed the important part :)

> RBS
>
> On Tue, Apr 5, 2016 at 10:05 PM, J Decker  wrote:
>
>> def files are obsolete.
>> the instructions include setting SQLITE_API to __declspec(dllexport)
>> which should do the job... unless there was a typeo
>>
>> On Tue, Apr 5, 2016 at 2:03 PM, GB  wrote:
>> > Strange. Runs fine here (also Win 7 Pro x64). But version 2.1 may have
>> > problems with post-XP OS. Try V2.2 from here:
>> > http://www.heise.de/download/dependency-walker.html (German site, but
>> > downloads should be easy to spot).
>> >
>> > But if I remember this right, you need to include the sqlite3.def file
>> from
>> > the binary dll-package into your project to create the exports.
>> Depending on
>> > your build flags you may need to comment out some exports.
>> >
>> >
>> >
>> > Bart Smissaert schrieb am 05.04.2016 um 22:01:
>> >>
>> >> I downloaded the 64 bit version of DependencyWalker from here:
>> >> https://support.microsoft.com/en-us/kb/256872
>> >> but it didn't run on my machine although it is Win 7 Pro 64 bits.
>> >> Will have a look on the MSVS 2013 installation disc.
>> >>
>> >> RBS
>> >>
>> >>
>> >>
>> >> On Tue, Apr 5, 2016 at 8:17 PM, GB  wrote:
>> >>
>> >>> Dependency Walker comes in two different flavors, one for x86 and one
>> for
>> >>> x64. You need to use the appropriate one for your DLL.
>> >>>
>> >>>
>> >>>
>> >>> Bart Smissaert schrieb am 05.04.2016 um 09:50:
>> >>>
>> >>>> Trying to compile SQLite 64 bits in MSVS 2013 from the amalgation.
>> >>>> Using instructions from:
>> >>>>
>> >>>>
>> >>>>
>> >>>>
>> http://pavel.cernohorsky.name/blog/2015/09/sqlite_64-bit_binaries_for_windows_and_how_to_build_them_with_visual_studio_2015.php
>> >>>>
>> >>>> All compiles well, so no warnings and get a dll of about 700 kB.
>> >>>> Nil shows however in Dependency Walker, so it looks nil is exported.
>> >>>>
>> >>>> Do I need to change anything in the amalgation source to compile 64
>> bits
>> >>>> or
>> >>>> could it be that Dependency Walker doesn't work well with 64 bit
>> dll's?
>> >>>> The machine is Win 7 64 bits.
>> >>>> I have problem at all compiling 32 bits.
>> >>>> Thanks for any advice.
>> >>>>
>> >>>> RBS
>> >>>> ___
>> >>>> sqlite-users mailing list
>> >>>> sqlite-users at mailinglists.sqlite.org
>> >>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >>>>
>> >>>>
>> >>> ___
>> >>> sqlite-users mailing list
>> >>> sqlite-users at mailinglists.sqlite.org
>> >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >>>
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users at mailinglists.sqlite.org
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> >
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users at mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compiling SQLite dll 64 bits in MSVS

[J Decker]

def files are obsolete.
the instructions include setting SQLITE_API to __declspec(dllexport)
which should do the job... unless there was a typeo

On Tue, Apr 5, 2016 at 2:03 PM, GB  wrote:
> Strange. Runs fine here (also Win 7 Pro x64). But version 2.1 may have
> problems with post-XP OS. Try V2.2 from here:
> http://www.heise.de/download/dependency-walker.html (German site, but
> downloads should be easy to spot).
>
> But if I remember this right, you need to include the sqlite3.def file from
> the binary dll-package into your project to create the exports. Depending on
> your build flags you may need to comment out some exports.
>
>
>
> Bart Smissaert schrieb am 05.04.2016 um 22:01:
>>
>> I downloaded the 64 bit version of DependencyWalker from here:
>> https://support.microsoft.com/en-us/kb/256872
>> but it didn't run on my machine although it is Win 7 Pro 64 bits.
>> Will have a look on the MSVS 2013 installation disc.
>>
>> RBS
>>
>>
>>
>> On Tue, Apr 5, 2016 at 8:17 PM, GB  wrote:
>>
>>> Dependency Walker comes in two different flavors, one for x86 and one for
>>> x64. You need to use the appropriate one for your DLL.
>>>
>>>
>>>
>>> Bart Smissaert schrieb am 05.04.2016 um 09:50:
>>>
 Trying to compile SQLite 64 bits in MSVS 2013 from the amalgation.
 Using instructions from:



 http://pavel.cernohorsky.name/blog/2015/09/sqlite_64-bit_binaries_for_windows_and_how_to_build_them_with_visual_studio_2015.php

 All compiles well, so no warnings and get a dll of about 700 kB.
 Nil shows however in Dependency Walker, so it looks nil is exported.

 Do I need to change anything in the amalgation source to compile 64 bits
 or
 could it be that Dependency Walker doesn't work well with 64 bit dll's?
 The machine is Win 7 64 bits.
 I have problem at all compiling 32 bits.
 Thanks for any advice.

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


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

[sqlite] Article about pointer abuse in SQLite

[J Decker]

On Tue, Mar 22, 2016 at 6:58 AM, Adam Devita  wrote:
> It may be pedantic, but VS2016 will stop complaining if you edit your
> definition of s to
> large_struct s=new large_struct();  //set s to an actual instance of
> large_struct. c people can think of s as a pointer, and in c# the
> members are set to their default values.
>
The point was, the structure had some 20 members, and 90% of the time
the conditions don't exist for it to be initialized.  So rather than
initialize it 90% of the time for no use, I added checks to optimize
the object's creation.

> J Decker's point could also have been made by using int x in place of
> large_struct s . and sub x for s.x  , since it is a contrived example
> anyway.  The only way to use x is if another conditional on another
> variable that follows it in code and it is initialized.
>
> if one writes
> const bool arbitrary_true_false = true;   //note the const as Scott
> Doctor implied, makes the error go away.
>

It's not a const though, it's a variable the changes during runtime
and allows for the creation of such an object. It's not 'contrived' it
was an example that I ran into while developing (several times in
fact).

similarly soemthing like (I haven't run it though compilers, so don't
know if this is nested enough to cause the same issue... but it's easy
to see how a compiler/error checker would similarly be confused.

void f() { int a, b;
   for( a = 0; a < 2; a++ ) {
   if(  a == 0 ) b = 1234;
   }
   printf( "b is never uniniialized here : %d", b );
}

> -
> This discussion on the nature of undefined behaviour code is
> interesting.  I don't know the reasoning, but it seems that VS6 often
> initialized things to 0xcd in debug mode and (usually) had memory
> uninitialized to 0x00 when complied in Release (perhaps 0x00 just
> happens to be what was on the stack or heap).  I presume this wasn't
> just to make people suffer  when things don't work the same in debug
> vs release mode.
>
> Does the tool help (in the sqlite in practice) point out things that
> could be problematic?  Is it a compiler's variant of  "hay,  you are
> depending on implemented, not documented behaviour" ?
>
> regards,
> Adam DeVita
>
>
> On Tue, Mar 22, 2016 at 7:27 AM, Scott Doctor  
> wrote:
>>
>> It is uninitialized. you are setting an initial value within an if
>> statement. For the compiler, the code has NOT actually executed. so it does
>> not use the value of the variable arbitrary_true_false. If it was a #define
>> then it would use the value but still give an error because it is not a
>> compiler directive #if but a code if.
>>
>> The logic is that the first instance of assignment is within a conditional.
>> That is a particularly nasty kind of bug and should be reported as an error.
>> because if later you decide to change arbitrary_true_false to false, then
>> s.x would not be initialized before use. the compiler is correct to issue
>> the warning. Give s.x a value after/at initialization, but before the if
>> statement to give it a desired initial value then recompile, that should fix
>> the error.
>>
>> Compilers only set the code to initialize the variable at declaration, not
>> actually use the values during compile. If it was declared as a constant
>> using a compiler directive such as #define, then the compiler would use the
>> value in the logic and still give an error, but a different one because the
>> conditional would always evaluate true (or false depending on what it was
>> set to)
>>
>>
>> On 03/21/2016 21:31, J Decker wrote:
>>>
>>> On Mon, Mar 21, 2016 at 8:40 PM, Scott Doctor 
>>> wrote:
>>>>
>>>> you are missing
>>>>
>>>> using System;
>>>
>>> whatever.  It still fails because it says the variable is
>>> uninitilalized.  THe only thing that doesn't is actually running it.
>>>
>>> That same pattern not matter what the language triggers warning/error
>>> checkers
>>>>
>>>> 
>>>> Scott Doctor
>>>> scott at scottdoctor.com
>>>> --
>>>>
>>>>
>>>> On 3/21/2016 5:21 PM, J Decker wrote:
>>>>>
>>>>> So far I just see analysis tools fail for the same sorts of valid
>>>>> code...
>>>>>
>>>>> this is a bit of C# but the same idea causes the same warnings and
>>>>> there's nothign tecniclally wrong with this.
>>>>>
>>>>>
>&

[sqlite] Article about pointer abuse in SQLite

[J Decker]

On Tue, Mar 22, 2016 at 4:27 AM, Scott Doctor  wrote:
>
> It is uninitialized. you are setting an initial value within an if
> statement. For the compiler, the code has NOT actually executed. so it does
> not use the value of the variable arbitrary_true_false. If it was a #define
> then it would use the value but still give an error because it is not a
> compiler directive #if but a code if.
>
> The logic is that the first instance of assignment is within a conditional.
> That is a particularly nasty kind of bug and should be reported as an error.
> because if later you decide to change arbitrary_true_false to false, then
> s.x would not be initialized before use. the compiler is correct to issue
> the warning. Give s.x a value after/at initialization, but before the if
> statement to give it a desired initial value then recompile, that should fix
> the error.
>
> Compilers only set the code to initialize the variable at declaration, not
> actually use the values during compile. If it was declared as a constant
> using a compiler directive such as #define, then the compiler would use the
> value in the logic and still give an error, but a different one because the
> conditional would always evaluate true (or false depending on what it was
> set to)
>

The usage is never uninitinalized.  You can pass false all you want,
once you make it ot passing it true, the initialized gets set, and the
thing IS initialized.
There is no cause for warning or error in either of these cases.
There is NEVER a time when it will be used and be unassigned.
See - it's too complex for even human minds to reason through how
could a dumb tool have any hope?
>
> On 03/21/2016 21:31, J Decker wrote:
>>
>> On Mon, Mar 21, 2016 at 8:40 PM, Scott Doctor 
>> wrote:
>>>
>>> you are missing
>>>
>>> using System;
>>
>> whatever.  It still fails because it says the variable is
>> uninitilalized.  THe only thing that doesn't is actually running it.
>>
>> That same pattern not matter what the language triggers warning/error
>> checkers
>>>
>>> 
>>> Scott Doctor
>>> scott at scottdoctor.com
>>> --
>>>
>>>
>>> On 3/21/2016 5:21 PM, J Decker wrote:
>>>>
>>>> So far I just see analysis tools fail for the same sorts of valid
>>>> code...
>>>>
>>>> this is a bit of C# but the same idea causes the same warnings and
>>>> there's nothign tecniclally wrong with this.
>>>>
>>>>
>>>>
>>>> class test
>>>> {
>>>>  struct large_struct { public int x; }
>>>>  bool arbitrary_true_false = true;
>>>>  void method()
>>>>  {
>>>> bool initialized = false;
>>>> large_struct s;
>>>> if( arbitrary_true_false )
>>>> {
>>>>initialized = true;
>>>>s.x = 1;
>>>> }
>>>> if( initialized )
>>>> {
>>>>Console.WriteLine( "this fails(during compile) as
>>>> uninitialized: {0}", s.x );
>>>> }
>>>>  }
>>>> }
>>>>
>>>> On Mon, Mar 21, 2016 at 4:35 PM, James K. Lowden
>>>>  wrote:
>>>>>
>>>>> On Mon, 21 Mar 2016 13:48:06 -0700
>>>>> Scott Perry  wrote:
>>>>>
>>>>>> Compilers allow you to choose your standard; --std=c11 means
>>>>>> something very specific (and unchanging)
>>>>>
>>>>> They do.  And that covers what the standard covers.  The standard also
>>>>> has limits.  It includes constructs that are syntactically permitted
>>>>> but whose behavior is left undefined, known by the scarred as "UB" for
>>>>> "undefined behavior". An example from Clang's discussion is
>>>>>
>>>>>   int i = 10 << 31;
>>>>>
>>>>> The standard says << is a shift operator.  It places no limit on the
>>>>> number of bits to be shifted.  If that number is so large that the
>>>>> product cannot be represented by the assigned variable, that is *not*
>>>>> an error.  The standard allows the compiler to do anything or nothing
>>>>> with it.  As you may imagine, the varieties of anything and nothing are
>>>>> many.
>>>>>
>>>>> Compiler writers ar

[sqlite] Article about pointer abuse in SQLite

[J Decker]

On Mon, Mar 21, 2016 at 8:40 PM, Scott Doctor  wrote:
> you are missing
>
> using System;

whatever.  It still fails because it says the variable is
uninitilalized.  THe only thing that doesn't is actually running it.

That same pattern not matter what the language triggers warning/error checkers
>
> 
> Scott Doctor
> scott at scottdoctor.com
> --
>
>
> On 3/21/2016 5:21 PM, J Decker wrote:
>>
>> So far I just see analysis tools fail for the same sorts of valid code...
>>
>> this is a bit of C# but the same idea causes the same warnings and
>> there's nothign tecniclally wrong with this.
>>
>>
>>
>> class test
>> {
>> struct large_struct { public int x; }
>> bool arbitrary_true_false = true;
>> void method()
>> {
>>bool initialized = false;
>>large_struct s;
>>if( arbitrary_true_false )
>>{
>>   initialized = true;
>>   s.x = 1;
>>}
>>if( initialized )
>>{
>>   Console.WriteLine( "this fails(during compile) as
>> uninitialized: {0}", s.x );
>>}
>> }
>> }
>>
>> On Mon, Mar 21, 2016 at 4:35 PM, James K. Lowden
>>  wrote:
>>>
>>> On Mon, 21 Mar 2016 13:48:06 -0700
>>> Scott Perry  wrote:
>>>
>>>> Compilers allow you to choose your standard; --std=c11 means
>>>> something very specific (and unchanging)
>>>
>>> They do.  And that covers what the standard covers.  The standard also
>>> has limits.  It includes constructs that are syntactically permitted
>>> but whose behavior is left undefined, known by the scarred as "UB" for
>>> "undefined behavior". An example from Clang's discussion is
>>>
>>>  int i = 10 << 31;
>>>
>>> The standard says << is a shift operator.  It places no limit on the
>>> number of bits to be shifted.  If that number is so large that the
>>> product cannot be represented by the assigned variable, that is *not*
>>> an error.  The standard allows the compiler to do anything or nothing
>>> with it.  As you may imagine, the varieties of anything and nothing are
>>> many.
>>>
>>> Compiler writers are well aware that "nothing" is faster done than
>>> "something".  Over time, they have gotten more aggressive in simply
>>> deleting UB code.  As a consequence, programmers who thought they wrote
>>> standards-conforming code get burned when they upgrade/change
>>> compilers.  Mysterious and sometimes subtle errors are introduced by
>>> the compiler for the user's benefit.
>>>
>>> Your googlefu will turn up lots of discussion.  One I liked that wasn't
>>> on Page 1:
>>>
>>>
>>> http://blog.frama-c.com/index.php?post/2013/10/09/Overflow-float-integer
>>>
>>> --jkl
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Article about pointer abuse in SQLite

[J Decker]

So far I just see analysis tools fail for the same sorts of valid code...

this is a bit of C# but the same idea causes the same warnings and
there's nothign tecniclally wrong with this.



class test
{
   struct large_struct { public int x; }
   bool arbitrary_true_false = true;
   void method()
   {
  bool initialized = false;
  large_struct s;
  if( arbitrary_true_false )
  {
 initialized = true;
 s.x = 1;
  }
  if( initialized )
  {
 Console.WriteLine( "this fails(during compile) as
uninitialized: {0}", s.x );
  }
   }
}

On Mon, Mar 21, 2016 at 4:35 PM, James K. Lowden
 wrote:
> On Mon, 21 Mar 2016 13:48:06 -0700
> Scott Perry  wrote:
>
>> Compilers allow you to choose your standard; --std=c11 means
>> something very specific (and unchanging)
>
> They do.  And that covers what the standard covers.  The standard also
> has limits.  It includes constructs that are syntactically permitted
> but whose behavior is left undefined, known by the scarred as "UB" for
> "undefined behavior". An example from Clang's discussion is
>
> int i = 10 << 31;
>
> The standard says << is a shift operator.  It places no limit on the
> number of bits to be shifted.  If that number is so large that the
> product cannot be represented by the assigned variable, that is *not*
> an error.  The standard allows the compiler to do anything or nothing
> with it.  As you may imagine, the varieties of anything and nothing are
> many.
>
> Compiler writers are well aware that "nothing" is faster done than
> "something".  Over time, they have gotten more aggressive in simply
> deleting UB code.  As a consequence, programmers who thought they wrote
> standards-conforming code get burned when they upgrade/change
> compilers.  Mysterious and sometimes subtle errors are introduced by
> the compiler for the user's benefit.
>
> Your googlefu will turn up lots of discussion.  One I liked that wasn't
> on Page 1:
>
> 
> http://blog.frama-c.com/index.php?post/2013/10/09/Overflow-float-integer
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Pronunciation

[J Decker]

more like sequel-ite

On Wed, Mar 16, 2016 at 11:38 AM,   wrote:
> Hello,
>
> Please grant me some leeway here since as someone who has
> not been in an academic school for years and is mainly self
> taught. I have Mainly deriving information from reading
> manuals and occasionally viewing some videos.
>
> Maybe I'm wrong, but according to Wikepedia SQLite appears
> to be pronounced the same has it is spelled,
> (ˈsiːkwəl.laɪt).
> Maybe not a long A there perhaps.
>
> Where as I first heard Microsoft's MSSQL pronounce (sequent),
> which I have also heard in academic videos by professors.
> Following that logic, SQLite, (sequent.light)?
>
> Dana Proctor
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] hexadecimal conversion on select query

[J Decker]

maybe ?
https://www.sqlite.org/lang_corefunc.html

hex(X)The hex() function interprets its argument as a BLOB and returns
a string which is the upper-case hexadecimal rendering of the content
of that blob.

On Sat, Mar 12, 2016 at 12:18 PM, jungle Boogie  
wrote:
> Hello All,
>
> I'm curious to know if there's a better way to run a query and convert
> the results to hexadecimal.
>
>  This is what I have so far:
> SELECT printf("%x %d",code,code), printf("%x", denom)  FROM sidemeters
> order by denom
>
> The data is already in decimal so I don't necessarily need to include
> the extra %d, but it's nice for comparison.
>
>
> Thanks for any input!
>
> --
> ---
> inum: 883510009027723
> sip: jungleboogie at sip2sip.info
> xmpp: jungle-boogie at jit.si
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Windows A and W APIs dual support

[J Decker]

On Fri, Feb 12, 2016 at 8:00 PM, Igor Tandetnik  wrote:
> On 2/12/2016 10:44 PM, J Decker wrote:
>>
>> On Fri, Feb 12, 2016 at 7:37 PM, Igor Tandetnik 
>> wrote:
>>>
>>> It performs the conversion it is documented to perform. It indeed doesn't
>>> perform the conversion that you, for reasons unclear, expect it to
>>> perform.
>>> In other words, you engage in wishful thinking, and then blame the
>>> messenger
>>> for failure of your wishes to materialize.
>>
>>
>> I expect it to take any string
>
>
> What is the basis of this expectation, other than wishful thinking?
I don't think expectation and wishful thinknig have anything to do
with each other.

Though I expect standards would look at what the world really needs
and implement core functionality?  That's hardly wishful thinking.
Well I guess it is, because I repeatedly have found myself
disappointed in the lack of considerations in standards.  Yes there
are even unicode libraries for posix; but it's a huge expense for a
couple hundred lines of code.  and if it's something I needed for
interop, why doesn't everyone?

>
> Again, if you need to convert specifically between UTF-16 and UTF-8, there
> are API functions that are documented to do that, and they do work. They are
> WideCharToMultiByte and MultiByteToWideChar. wcstombs and mbstowcs are not
> documented to do that, and, quite unsurprisingly, they don't work for that.
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Windows A and W APIs dual support

[J Decker]

On Fri, Feb 12, 2016 at 8:00 PM, Igor Tandetnik  wrote:
> On 2/12/2016 10:44 PM, J Decker wrote:
>>
>> On Fri, Feb 12, 2016 at 7:37 PM, Igor Tandetnik 
>> wrote:
>>>
>>> It performs the conversion it is documented to perform. It indeed doesn't
>>> perform the conversion that you, for reasons unclear, expect it to
>>> perform.
>>> In other words, you engage in wishful thinking, and then blame the
>>> messenger
>>> for failure of your wishes to materialize.
>>
>>
>> I expect it to take any string
>
>
> What is the basis of this expectation, other than wishful thinking?
>
> Again, if you need to convert specifically between UTF-16 and UTF-8, there
> are API functions that are documented to do that, and they do work. They are
> WideCharToMultiByte and MultiByteToWideChar. wcstombs and mbstowcs are not
> documented to do that, and, quite unsurprisingly, they don't work for that.
>
and what exists for platforms other than windows?
doesn't matter though.  I have a solution that works on all platforms
that's the same name and doesn't require some #ifdef to work.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Windows A and W APIs dual support

[J Decker]

On Fri, Feb 12, 2016 at 7:37 PM, Igor Tandetnik  wrote:
> On 2/12/2016 10:14 PM, J Decker wrote:
>>
>> mbstowcs( out, utf8, 5 );
>
>
> mbstowcs expects the string in the codepage of the current locale - which is
> never UTF-8.
>
>> for( n = 0; n < 5; n++ )
>> printf( "%04x ", out[n] );  // output is 00f0 0090 0080 0081; expect d800
>> dc01
>
>
> Why do you expect that? It appears your system uses Western European
> codepage (aka Latin-1). You pass a character "\xf0" which, when taken to be
> encoded in that codepage, is quite properly converted to U+00F0.
>
>> for( n = 0; n < 5; n++ )
>>printf( "%02x ", chout[n] );  // output is 00 00 00 00
>
>
> U+10001 is (unsurprisingly) not representable in your current ANSI codepage,
> so wcstombs call fails (I can't help but notice that you aren't checking any
> calls for failure) and leaves the output buffer unchanged.
>
>> so it does no useful conversion either way :)
>
>
> It performs the conversion it is documented to perform. It indeed doesn't
> perform the conversion that you, for reasons unclear, expect it to perform.
> In other words, you engage in wishful thinking, and then blame the messenger
> for failure of your wishes to materialize.

I expect it to take any string such as

???
or
?  ??? ? 

and give me a char * representation of it that's useful... or
conversely take the char* version of said strings and give me wchar_t
* that makes can be used.


> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Windows A and W APIs dual support

[J Decker]

On Fri, Feb 12, 2016 at 7:05 PM, Warren Young  wrote:
> On Feb 12, 2016, at 4:42 PM, Scott Robison  wrote:
>>
>> I find it kind of interesting that Microsoft takes a lot
>> of (deserved) flack for not adhering to standards, yet UTF-8 came about
>> specifically because some didn't want to use UCS-2
>
> ?for good reason.  UCS-2/UTF-16 isn?t compatible with C strings.  I know you 
> know this, but it?s a huge consideration.  Outside of Mac OS Classic and a 
> few even smaller enclaves, C and its calling standards were the lingua franca 
> of the computing world when Unicode first came on the scene, and those 
> enclaves are now all but gone.
>
> We?ll be living with the legacy of C for quite a long time yet.  Until C is 
> completely stamped out, we?ll have to accommodate 0-terminated strings 
> somehow.
>

and Go.  Which is purely UTF8.

>
> Simple and straightforward.
>
> Too bad it will never happen.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Windows A and W APIs dual support

[J Decker]

On Fri, Feb 12, 2016 at 5:32 PM, Igor Tandetnik  wrote:
> On 2/12/2016 7:24 PM, J Decker wrote:
>>

> What character in what ANSI codepage ends up converted by mbstowcs to an
> unpaired surrogate?
>
> What character in what ANSI codepage requires a surrogate pair to represent
> (that is, corresponds to a Unicode character outside of BMP), and triggers
> failure when passed to mbstowcs?
>
> With all due respect, I find your claims difficult to believe.
>
> In any case, MultiByteToWideChar and WideCharToMultiByte are perfectly
> capable of converting between UTF-8 and UTF-16.
> --
> Igor Tandetnik
>

Okay; I'd forgotten.  It does worse than I expected...

//--

int main( void )
{
char utf8[5] = "\xf0\x90\x80\x81";
char utf82[5] = "\xed\xa0\x81";
   char utf8tmp[5];
wchar_t out[5];
wchar_t out2[5];
wchar_t utf16[5] = L"\xd800\xdc01";
wchar_t real_out[25];
   char chout[5];
int n;

memset( out, 0, sizeof( out ) );
   memset( out2, 0, sizeof( out2 ) );
   memset( chout, 0, sizeof( chout ) );

mbstowcs( out, utf8, 5 );
mbstowcs( out2, utf82, 5 );
wcstombs( chout, utf16, 5 );

for( n = 0; n < 5; n++ )
printf( "%04x ", out[n] );  // output is 00f0 0090 0080 0081; expect d800 dc01
   printf( "\n" );
for( n = 0; n < 5; n++ )
  printf( "%04x ", out2[n] ); // output is 00ed 00a0 0081; expect d801
   printf( "\n" );
for( n = 0; n < 5; n++ )
  printf( "%02x ", chout[n] );  // output is 00 00 00 00
}

//--

so it does no useful conversion either way :)  (but at least I ended
up fixing a boundary issue while testing)

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

[sqlite] Windows A and W APIs dual support

[J Decker]

well mbstowc and vice versa only understand 16 bit encodings, and do
not generate codpairsand do generate d800-dfff characters which
are entirely illegal in utf-16 (without corresponding pair)

But; fortunately, they do end up supporting utf-8 since it's just a
stream of bytes with a nul terminator in most cases.  But for display
I defiantly had to do my own 'getCodepoint' and then index the font..
I'd imagine that applications like IE handle it internally too... but
definitely the console has some issues.

On Fri, Feb 12, 2016 at 3:42 PM, Scott Robison  
wrote:
> On Fri, Feb 12, 2016 at 4:05 PM, J Decker  wrote:
>
>> windows W is wide-char not utf-16.
>> as much as A is ansi and isn't utf-8
>>
>
> Has Windows ever supported a wide character set that was not UCS-2 or
> UTF-16? I've always understood Microsoft embraced UCS-2 specifically so
> that it would not have to deal with future encoding changes. Obviously it
> failed to an extent when UCS-2 was deprecated in favor of UTF-16, but since
> UTF-16 is backward compatible as long as you don't need surrogate pairs, it
> wasn't too painful of a transition. Especially when compared to the
> plethora of 8 bit multibyte encodings.
>
> Note: I know Windows has supported DBCS for various encodings / code pages,
> but those are never passed to wide functions.
>
> I find it kind of interesting that Microsoft takes a lot
> of (deserved) flack for not adhering to standards, yet UTF-8 came about
> specifically because some didn't want to use UCS-2 (then simply known as
> UCS, the one and only true flavor of the Universal Character Set). Had
> Microsoft come up with it first, I'm sure they'd be crucified by some of
> the same people who today are critical of them for using wide characters
> instead of UTF-8!
>
> Note: I still wish they supported UTF-8 directly from the API.
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Windows A and W APIs dual support

[J Decker]

windows W is wide-char not utf-16.
as much as A is ansi and isn't utf-8

On Fri, Feb 12, 2016 at 1:07 PM, Warren Young  wrote:
> On Feb 12, 2016, at 1:49 PM, Clemens Ladisch  wrote:
>>
>> Olivier Mascia wrote:
>>> Are there Windows platforms, supported by SQLite source code of course, 
>>> where the 'W' version of the APIs are not available?
>>
>> Once upon a time, SQLite supported Windows 95/98/Me.
>
> The DOS-based versions of Windows still have the ?W? functions for binary 
> compatibility with the NT-based versions, but for the most part they treat 
> their arguments according to the 8-bit code page or MBCS rules, which means 
> you generally get garbage output when you feed in UCS-2.
>
> There are a few exceptions: https://support.microsoft.com/en-us/kb/210341
>
> Note that Windows didn?t move from UCS-2 to UTF-16 until Windows 2000, which 
> is effectively after the development time of the DOS-based versions of 
> Windows.  (There?s a tiny overlap there with Windows ME, but that?s last-gasp 
> stuff.)
>
> I assume if you pass strings using characters beyond the BMP to the ?16? APIs 
> in SQLite, they would do the wrong thing on Windows NT 3.x and 4.x systems, 
> too.
>
> I doubt there would be much crying if SQLite dropped the ?A? support.  I 
> suspect the only reason SQLite still has it is that it?s more work to remove 
> it than to leave it alone.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What's the reason for alias names not being visible in subqueries?

[J Decker]

On Mon, Feb 8, 2016 at 3:38 AM, Paul  wrote:
> I see, thank you for pointing out.
>
> I wanted to use it on table with conditional relations with 3 different child 
> tables.
> Though I could use a trick and fit data selection into one query, efficiently.
> Alas I am forced to stick to 2 queries.
>

Might still be able to collapse it into one CTE query but I don't
know what the other part is to demo that...

https://www.sqlite.org/lang_with.html

> Thank you!
>
> 8 February 2016, 12:08:26, by "Clemens Ladisch" :
>
>>   Paul wrote:
>> > I am curious what is a particular reason that aliased columns in a query 
>> > not visible to sub-queries?
>>
>> Because the SQL standard says so.
>>
>> > Of course it is possible to duplicate expression in sub-query ...
>> > But this would not be as efficient as to access result of already 
>> > evaluated expression.
>>
>> An alias refers not to an expression's value but to the expression
>> itself, so it would not be any more efficient.  (SQLite's optimizer
>> typically is not smart enough to detect and remove the duplication.)
>>
>>
>> As a workaround, you have to move the expression into a subquery in
>> the FROM clause:
>>
>> SELECT super_id,
>>(SELECT qux FROM baz WHERE id = super_id)
>> FROM (SELECT id as super_id FROM foo);
>>
>>
>> Regards,
>> Clemens
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What's the reason for alias names not being visible in subqueries?

[J Decker]

On Mon, Feb 8, 2016 at 12:47 AM, Paul  wrote:
> Hello!
>
> I am curious what is a particular reason that aliased columns in a query not 
> visible to sub-queries?
>
> CREATE TABLE foo(
> id INTEGER,
> bar INTEGER
> );
>
> INSERT INTO foo VALUES(1, 2), (3, 4);
>
> SELECT 1 as super_id, (SELECT bar FROM foo WHERE id = super_id);
>
This looks like a simple select

select 1 as super_id,bar from foo where id=super_id

> Gives an error:
> Error: no such column: super_id
>
> On the other hand, we can easily refer to values of a table that participates 
> in a query.
>
> CREATE TABLE baz(
> id INTEGER,
> qux INTEGER
> );
>
> INSERT INTO baz VALUES(1, 6), (2, 8);
>
> SELECT id, (SELECT qux FROM baz WHERE id = foo.id) FROM foo;
>
this looks like a join

select foo.id,qux from foo join baz on baz.id=foo.id

> id  (SELECT qux FROM baz WHERE id = foo.id)
> --  ---
> 1   6
> 3
>
> But still not though alias:
>
> SELECT id as super_id, (SELECT qux FROM baz WHERE id = super_id) FROM foo;
> Error: no such column: super_id

And this
select foo.id as super_id,qux from foo join baz on baz.id=super_id


>
> 
>
> Why would this be useful?
>
> Sometimes you need a sub-select by a result of an expression.
>
> SELECT ..., , (SELECT ... FROM FOO where id =  expression X>) ..;
>
> Of course it is possible to duplicate expression in sub-query
>
> SELECT ..., , (SELECT ... FROM FOO where id = ) 
> ..;
>

and both of those

> But this would not be as efficient as to access result of already evaluated 
> expression.
> Especially if  itself is a sub-select. Also imagine a chain of 
> sub-queries.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] json_group_array

[J Decker]

I think maybe it would be more appropriate to return a blank element.
It would fill an index point, but be skipped and unused in cases of
iterating the loop...

http://www.2ality.com/2013/07/array-iteration-holes.html

Was thinking that javascript forEach et al. methods skipped null (or
undefined actually).

Hmm... but then again

-
var array = [1,2,,3];
console.log( JSON.stringify( array ) );
-
outut : [1,2,null,3]

--

Or more completely
--

var array = [1,2,,3];
console.log( JSON.stringify( array ) );
var result = JSON.parse( JSON.stringify( array ) );
array.forEach( (n)=>{ console.log(n) } ) ;
result.forEach( (n)=>{ console.log(n) } ) ;
---
output
[1,2,null,3]

1
2
3

1
2
null
3

---

So seems like having null in array for JSON is perfectly expected.



On Thu, Feb 4, 2016 at 11:55 PM, R Smith  wrote:
>
>
> On 2016/02/05 6:34 AM, TJ O'Donnell wrote:
>>
>> I can't argue for the correctness of including nulls in aggregate
>> functions
>> or not.
>> It truly is an arbitrary decision meant for standards-makers.  Yet, most
>> aggregate
>> function do not include nulls.  Interestingly, some SQL's do include them
>> in count()
>> but sqlite does not.  In my example table, select count(a) from x returns
>> 3,
>> but select count(*) from x returns 4 even though a is the only column.
>> I haven't tried every sqlite agg, but I think they all exclude null,
>> except
>> json_group_array
>> and json_group_object.
>
>
> I think you are mistaken in your understanding. While JSON has some rules
> and some conventions, when used inside an SQL engine, the rules of SQL needs
> to be adhered to before any "convention" of JSON.
> Not showing Null values in JSON is a convention, not a rule. (Else, why else
> would json even need the 'NULL' construct?)
>
> Further to this, in SQL, how would you know how many elements are present in
> a json array and which of them are null if there is no way to output them?
> Agreed, sometimes it isn't needed to know, but then you are welcome to
> exclude them via the WHERE clause.
>
> You are also mistaken about the SQL convention and SQLite-specific
> operations re. Nulls - If I have a table t with one single column "a" with 3
> rows (2 text values and one null value) then doing SELECT COUNT(a) FROM t;
> will show 2 and SELECT COUNT(*) FROM t; will show 3, as it should - yes,
> even though a is the only column. The * doesn't mean "a", even if the only
> column is "a". It means "all the DB rows" and so include nulls. (The
> standard might be hazy on this, I didn't check, but this is definitely how
> SQLite works, and not as you suggested).
>
> This is also very important. Sometimes we'd want to know how many rows are
> in the DB, not JUST which non-null rows are in the only column in the DB -
> that is why we can decide to use either COUNT(a) or COUNT(*), or more
> deliberate with an explicit GROUP BY clause. I would never want this
> convention to be altered.
>
>
>> As a side issue here, but important still I think, what should json(null)
>> mean?
>> In my table x, select json(a) from x returns valid json integers for
>> non-null rows,
>> but return a sql null (a blank from command-llne sqlite) not a json null
>> (which would
>> be the string null) when a is null.  In other words, json(null) returns
>> null,
>> not 'null'.
>
>
> Here I'm with you - the null should output 'null'
> (Devs: I'm guessing this might be an oversight in the CLI rather than the
> SQL engine?)
>
>> I know the json stuff is new in sqlite, but I think it's worth getting
>> these issues worked
>> out, considering how useful json has become.
>
>
> Right you are, but first the issues need discovery - which is what is
> happening in this very thread. :)
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Find out how many times does SQLite hit the disk?

[J Decker]

On Sat, Jan 30, 2016 at 8:09 PM, J Decker  wrote:
> could use a tool like ProcMon and filter to disk activity on a
> specified file to see...
> https://technet.microsoft.com/en-us/sysinternals/processmonitor.aspx?f=255&MSPPError=-2147217396

Might not actually be useful though; if the file is memory mapped (WAL
Journal?) then you won't see those accesses...

then you'd end up having to hook into VFS stuff...
>
> On Sat, Jan 30, 2016 at 3:21 PM, Simon Slavin  wrote:
>>
>> On 30 Jan 2016, at 9:31pm, dpb  wrote:
>>
>>> On windows, is there a way to find out how many times does my SQLite DB hit
>>> the disk?
>>>
>>> This will help me in deciding if moving to In-memory SQLite will improve my
>>> application performance.
>>
>> You will find that tools which examine process statistics will tell you how 
>> many read and write operations the process does.
>>
>> To do it in SQLite you might add your own code to the standard VFS for your 
>> OS just to total up accesses to disk.
>>
>> <https://www.sqlite.org/vfs.html>
>>
>> However, I am not certain that counting disk accesses is actually going to 
>> help you figure anything one.
>>
>>> I am done with adding indexes to my tables in
>>> SQLite DB.
>>
>> Are you sure you're adding the /right/ indexes ?  I see many people adding 
>> indexes to commonly-used columns without making up a index designed 
>> specifically to help a particular SELECT command.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Find out how many times does SQLite hit the disk?

[J Decker]

could use a tool like ProcMon and filter to disk activity on a
specified file to see...
https://technet.microsoft.com/en-us/sysinternals/processmonitor.aspx?f=255&MSPPError=-2147217396

On Sat, Jan 30, 2016 at 3:21 PM, Simon Slavin  wrote:
>
> On 30 Jan 2016, at 9:31pm, dpb  wrote:
>
>> On windows, is there a way to find out how many times does my SQLite DB hit
>> the disk?
>>
>> This will help me in deciding if moving to In-memory SQLite will improve my
>> application performance.
>
> You will find that tools which examine process statistics will tell you how 
> many read and write operations the process does.
>
> To do it in SQLite you might add your own code to the standard VFS for your 
> OS just to total up accesses to disk.
>
> 
>
> However, I am not certain that counting disk accesses is actually going to 
> help you figure anything one.
>
>> I am done with adding indexes to my tables in
>> SQLite DB.
>
> Are you sure you're adding the /right/ indexes ?  I see many people adding 
> indexes to commonly-used columns without making up a index designed 
> specifically to help a particular SELECT command.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Recover corrupt database

[J Decker]

On Wed, Jan 27, 2016 at 6:28 AM, Jay Kreibich  wrote:
>
> The backup API is not designed to recover corrupted databases.
>
> Generally speaking, there is no standard way to recover from a corrupt 
> database.  The best policy is not to corrupt it in the first place.  In 
> practice, if you follow the rules, it is very very hard to corrupt a 
> database.  Just be sure you?re not breaking one of the rules; if the database 
> is inside another database, I question how locks and matched auxiliary files 
> (journal, for example) are being handled.
>
It's not in another database, but rather a encrypted vfs.  I assume it
happened while debugging and stopping at a breakpoint and aborting the
process; in general use I don't expect this to happen.

*sigh* guess I get to implement the 'hard' way then.

> https://www.sqlite.org/howtocorrupt.html
>
>   -j
>
>
> On Jan 27, 2016, at 7:47 AM, J Decker  wrote:
>
>> I was wondering if the backup api (sqlite3_backup_init, et al. ) will
>> help to recover a corrupt DB?  All the other mechanisms are an export
>> and re-import into a new DB...
>>
>> The database exists in a encrypted database that it's really difficult
>> to get the keys outside of the program and I don't look forward to
>> doing something that iterates through sqlite_master to get all the
>> tables do all the selects and re-insert... so I browsed the API and
>> found the _backup_ stuff.. but; I'm starting to think that it will
>> just be a faithful page replication and the result will still be
>> corrupted.
>> ( I don't know what the corruption is)
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but 
> showing it to the wrong people has the tendency to make them feel 
> uncomfortable." -- Angela Johnson
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Recover corrupt database

[J Decker]

I was wondering if the backup api (sqlite3_backup_init, et al. ) will
help to recover a corrupt DB?  All the other mechanisms are an export
and re-import into a new DB...

The database exists in a encrypted database that it's really difficult
to get the keys outside of the program and I don't look forward to
doing something that iterates through sqlite_master to get all the
tables do all the selects and re-insert... so I browsed the API and
found the _backup_ stuff.. but; I'm starting to think that it will
just be a faithful page replication and the result will still be
corrupted.
( I don't know what the corruption is)

[sqlite] sqldiff.c : 2 benign warnings in 64 bits builds

[J Decker]

should be (size_t) instead of (int) though... since size_t will retain
the precision... and then back propagate the change to the function
return type and the things receiving the return... then you don't need
the cast anyway.

On Tue, Jan 26, 2016 at 3:37 AM, Richard Hipp  wrote:
> On 1/26/16, Olivier Mascia  wrote:
>> Hello,
>>
>> Using (3.10.2), as was the case with 3.10.0, there are two warnings (VS.2015
>> at least, /W3 only) compiling in 64 bits:
>>
>
> Fixed 12 days ago on trunk.  https://www.sqlite.org/src/info/74808a79ea3194f6
>
> --
> 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] SQLite crashing

[J Decker]

On Mon, Jan 25, 2016 at 8:31 AM, Teg  wrote:
> Hello Igor,

>
> extern "C" __declspec(dllexport) void DestroyObject(Database *db)
> {
>   delete db;
> }
>
> It was my impression that each DLL got it's own heap so, memory
> allocated inside the DLL needs to be free'd inside the DLL. I use
> Sqlite in a static lib in my applications.
>

That's not the general case.  DLLs all have the same memory spaces...
although in your next part, it may be true that if you're loading DLLs
that are linked to different runtimes they may have used different
allocators and require different deallocations... but typically (and
in programs with least issues) they will use multi-threaded shared
library runtime so they all use the same allocation functions.

> I  treat  memory  allocated  in  DLL's as being owned by the DLL so, I
> typically  pass  it  back  to the DLL to be cleaned up.  It believe it
> depends  on what run time library you're using though. If you're using
> an RTL where all the DLL's end up using a DLL supplied allocator, this
> probably isn't an issue. I tend to dynamic load my DLL's so they don't
> all use the same allocator.
>
Are you sure you're not somehow double-freeing the sqlite handle?
Especially at close I've seen exit() end up calling atexit() methods
multiple times in some circumstances...

> C
>
>
>
> Monday, January 25, 2016, 11:16:57 AM, you wrote:
>
> IK> Hi, Peter,
>
> IK> On Mon, Jan 25, 2016 at 10:50 AM, Peter Aronson  
> wrote:
>>> Igor,
>>>
>>> You can't safely pass a SQLite handle between different SQL DLLs that way if
>>> they're both built with their own copy of the amalgamation (or link to
>>> things built with different copies). SQLite uses a handful of global
>>> variables, but each DLL has its own copy of each of these global variables
>>> and they can and will have different values, which can mess things up.  I
>>> ran into a version of this problem when I tried to load a 2nd DLL built with
>>> its own copy of the sqlite3.c amalgamation.  I fixed that by exposing the
>>> SQLite3 entrypoints in the first DLL and linking the second DLL against it
>>> so there was only one copy of the amalgamation used for that SQLite3 handle.
>
> IK> The SQLite is built only once and with just one version of the code.
>
> IK> Consider following pseudo-code:
>
> IK> In DLL:
>
> IK> BOOL APIENTRY DLLMain()
> IK> {
> IK> }
>
> IK> extern "C" __declspec(dllexport) Database *CreateObject(Database *db)
> IK> {
> IK> db = new SQLiteDatabase();
> IK> db->Connect();
> IK> return db;
> IK> }
>
> IK> In the main application:
>
> IK> mainframe.h:
>
> IK> class MainFrame
> IK> {
> IK> public:
> IK>  MainFrame();
> IK>  ~MainFrame();
> IK>  void ConnectToDb();
> IK> private:
> IK>  Database *m_db;
> IK> };
>
> IK> mainframe.cpp:
>
> IK> void MainFrame::ConnectToDb()
> IK> {
> IK> Database *db = NULL;
> IK> LoadLibrary();
> IK> func = GetProcAddress();
> IK> m_db = func( db );
> IK> }
>
> IK> MainFrame::~MainFrame()
> IK> {
> IK> delete m_db;  // this is where the crash happens
> IK> }
>
> IK> The pointer address are the same in DLL and main application MainFrame 
> class.
> IK> And as I said the crash occurs when it tries to acquire the mutex lock.
>
> IK> Thank you.
>
>>>
>>> Peter
>>>
>>>
>>>
>>>
>>> On 1/24/2016 10:18 PM, Igor Korot wrote:

 Hi, ALL,
 I have a strange problem.

 I am trying to use sqlite in my program. It has a main application and
 couplef DLLs.

 I am getting the connection in one of the DLL, then the pointer is passed
 up
 to the main application.

 Upon exiting from the application I'm trying to close the connection and
 delete all the memory.

 Unfortunately upon exiting the application it crashes inside
 sqlite3_mutex_enter().
 The comment above the function says:

 [quote]
 /*
 ** Obtain the mutex p. If some other thread already has the mutex, block
 ** until it can be obtained.
 */
 [/quote]

 The DLL does not start any threads, in fact the application will be 1
 thread only.
 So is there some compile-time switch I should use to mitigate the issue?

 Moreover I don't understand why am I getting the assertion - there is no
 MT
 involved.

 Can someone shed some lights?

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

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

[sqlite] Malloc getting segmentation fault in sqlite

[J Decker]

Yes when I free'd memory I shouldn't have and kept a reference to it
in other places... or that after freeing I had modified memory.  But
not from sqlite itself.

On Thu, Dec 24, 2015 at 2:02 AM, santosh dasimanth
 wrote:
> Hi All,
> I am working on Sqlite in multi threaded environment on ARM v7 platform.
>
> I am facing problems with malloc() function returning segmentation fault.
> The problem is not frequent but out of 100 times am getting this once.
>
> The backtrace is pasted below.
>
> (gdb) bt
> #0  0x4038eb18 in malloc () from /lib/libc.so.0
> #1  0x401e0758 in sqlite3MallocRaw () from /pfrm2.0/lib/libsqlite3.so.0
> #2  0x401e08a8 in sqlite3Malloc () from /pfrm2.0/lib/libsqlite3.so.0
> #3  0x401e6254 in sqlite3VdbeCreate () from /pfrm2.0/lib/libsqlite3.so.0
> #4  0x401d99cc in sqlite3GetVdbe () from /pfrm2.0/lib/libsqlite3.so.0
> #5  0x401bd780 in sqlite3FinishCoding () from /pfrm2.0/lib/libsqlite3.so.0
> #6  0x401d2464 in sqlite3Parser () from /pfrm2.0/lib/libsqlite3.so.0
> #7  0x401dd664 in sqlite3RunParser () from /pfrm2.0/lib/libsqlite3.so.0
> #8  0x401d650c in sqlite3Prepare () from /pfrm2.0/lib/libsqlite3.so.0
> #9  0x401d69a4 in sqlite3_prepare () from /pfrm2.0/lib/libsqlite3.so.0
> #10 0x401ed5c0 in sqlite3_exec () from /pfrm2.0/lib/libsqlite3.so.0
> #11 0x401d5dbc in sqlite3InitCallback () from /pfrm2.0/lib/libsqlite3.so.0
> #12 0x401ed6f8 in sqlite3_exec () from /pfrm2.0/lib/libsqlite3.so.0
> #13 0x401d6184 in ?? () from /pfrm2.0/lib/libsqlite3.so.0
>
> The traces are pointing to different functions when I hit the issue at
> times.
> Please let me know if anyone of people faced this problem before with
> sqlite.
>
> Regards,
> Santosh
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] No Sqlite.dll for VS 2015 C# console application

[J Decker]

you'll need system.data.sqlite.dll not just sqlite3.dll.
I prefer to use the static version so system.data.sqlite.dll contains
the sqlite3.lib so it's standalone other than the runtime used.  (I've
sometimes had to copy msvc*d.dll (debug version) because I'm sometimes
lazy and know that it has worked under debug mode)

On Fri, Dec 18, 2015 at 12:51 AM, Nils Westerberg  wrote:
> Hello,
> I have a Universal app with SQLite using sqlite-net and referencing SQLite 
> for Universal App Platform that works. I am using VS 2015 update 1, Windows 
> 10.
> I am trying to make a console application using sqlite-net but I cannot find 
> a valid reference to SQLite. I have installed SQLite for Universal Platform, 
> for Windows Runtime and for Windows Runtime (8.1) via Visual Studio 
> Tools.Extensions and Updates... The References.Add Reference... does not show 
> any SQLite Package as it does for the Universal app.
> I used References.Manage NuGet Packages to install System.Data.SQLite which 
> updated the references. Running the application gives: "Unable to load DLL 
> sqlite3: The specified module could not be found."I have tried to reference 
> SQLite in other downloads from ??System.Data.SQLite but I get the same error.
>
> Adding sqlite3.dll to the load path gives: "Unable to load DLL sqlite3: This 
> operation is only valid in the context of an app container."
> What can I do?  I have asked the question on Microsoft Developer Network as I 
> thought to be more a VS problem but a moderator (Fred Bao) suggested that I 
> ask the SQLite forum.
>
> Nils
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Obscure peculiarity with 3.9.2

[J Decker]

Once upon a time, journals would delete when all statements were
closed.  I find now to get all journals to delete you must also close
all connections(opens). Figure at some point someone decided it was
more optimal to leave journals around instead of recreating them on
every statement (which it is).

On Wed, Dec 9, 2015 at 1:05 PM, R Smith  wrote:
>
>
> On 2015/12/09 10:55 PM, Richard Hipp wrote:
>>
>> On 12/9/15, R Smith  wrote:
>>>
>>> One of our systems suddenly started to play up after upgrading to 3.9.2
>>> from 3.8.8 (so I am not sure exactly when the oddity was introduced).
>>>
>>> SQLite:
>>> v 3.9.2 linked on WIndows (various versions) in a 32 bit application via
>>> the exact 32bit DLL published on the downloads page on sqlite.org.
>>>
>>> The error:
>>> During an update to an attached DB, the statement fails and reports
>>> "Disk I/O Error" and leaves a hot journal (even though the statement
>>> wasn't in an explicit transaction).
>>>
>> We can make a script to do this.  And we get this error (when we
>> enable logging - https://www.sqlite.org/errlog.html - which you really
>> should do too).
>>
>> {SQLITE_IOERR_DELETE {os_win.c:39624: (32)
>> winDelete(C:\Users\drh\sqlite\sqlite\
>> test2.db-journal) - The process cannot access the file because it is being
>> used
>> by another process.}
>>
>> The problem is that one connection is trying to delete the journal
>> file that the other connection is holding open.
>>
>> The thing is this:  We can reproduce this going all the way back to
>> version 3.8.0 (which is as far back as I looked.)  So I don't know how
>> you managed to get it working on your 3.8.8 version.
>
>
> This is slightly confusing - I think perhaps other actions we do allowed it
> to slip through (or perhaps complete the deletion before the other file
> access) in our code. But that is just me guessing now.
>
> Also, apologies for the laziness - I would have switched the logger on (we
> do have that of course) but I simply stepped through the code, saw the
> return value, went back to the connection origins, saw the different modes,
> corrected it and then things worked, so I did not do a full analysis, but
> decided it's probably worth reporting it.
>
> I'll do better next time before reporting :)
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_errmsg and wide char

[J Decker]

On Fri, Nov 27, 2015 at 12:17 AM, Scott Robison  
wrote:
> On Thu, Nov 26, 2015 at 11:42 PM, Igor Korot  wrote:
>
>> Hi, Scott,
>>
>> On Fri, Nov 27, 2015 at 1:01 AM, Scott Robison 
>> wrote:
>> > On Thu, Nov 26, 2015 at 10:13 PM, Igor Korot  wrote:
>> >>
>> >> Hi,
>> >> Is there any way to have "sqlite3_errmsg" function return a wide char
>> > string?
>> >> Or do a conversion in a portable way?
>> >>
>> >> Thank you.
>> >
>> > The portable way would be to use the mbstowcs function from stdlib.h,
>> > though it depends on what locales are supported by the system, so maybe
>> not
>> > as portable as you would like.
>>
>> I should've been more explicit here, sorry.
>> I'm developing in C++ and need to convert to std::wstring.
>>
>> Or maybe I can just generate my own error message based on the sqlite
>> return code?
>>
>
> Most of the comments I made previously still apply, since wstring is just a
> basic_string using wchar_t as the character type. C++ doesn't mandate the
> character set that wchar_t encompasses or the encoding it uses for that
> character set any more than C does. I like the C++11 char16_t & char32_t
> types (when available) since they are explicitly designed for unicode.
>
> Some useful answers can be found at
> http://stackoverflow.com/questions/2573834/c-convert-string-or-char-to-wstring-or-wchar-t

or ...
http://stackoverflow.com/questions/7153935/how-to-convert-utf-8-stdstring-to-utf-16-stdwstring

don't see what converting utf8 to utf16 has to do with locale ...
or why in the last decade a useful standard function hasn't been
included in standard libraries...

> ...
>
> A C++-ish way of converting a string to a wstring (assuming the characters
> in string are all ASCII or Latin-1 and wchar_t is unicode):
>
> // horribly inefficient but illustrative
> void copy_narrow_to_wide(std::wstring& dst, const char* src)
> {
>   while (*src) dst.push_back((unsigned char)(*(src++)));
> }
>
> Not that I'd necessarily recommend that, but it can work. The link above in
> particular is useful if all you need are to convert narrow strings to wide
> strings for internal usage (logging or such).
>
> However, I think you hit the nail on the head with your last thought
> (especially if you need to display these in potentially localized text):
> generate your own error messages based on the sqlite return code.
>
>
>>
>> Thank you.
>>
>> >
>> > There isn't really a truly portable way of converting from char to
>> wchar_t
>> > based strings, given that there is no real guarantee about what exactly
>> > wchar_t *is*. The ISO C90 standard merely defined it as "an integral type
>> > whose range of values can represent distinct codes for all members of the
>> > largest extended character set specified among the supported locales". In
>> > theory, wchar_t could be a typedef of char if a platform only supported
>> an
>> > 8 bit character set. Unicode was being designed at the time of C
>> > standardization, but the first version of Unicode wasn't published for a
>> > year or more after the C90 standard. Thus the wchar_t type doesn't have
>> to
>> > be Unicode,
>> >
>> > In practical terms, I generally consider wchar_t to be a pre-2011 method
>> of
>> > storing Unicode. Even here there are no portability guarantees. Microsoft
>> > went all in on Unicode in the early 1990s, back when it was only a two
>> byte
>> > encoding (UCS-2), so Microsoft compilers treat wchar_t as a two byte
>> type.
>> > Unicode 2.0 extended the Unicode character set in 1996 (I think) and
>> > introduced UTF-16 as a compromise way of allowing systems that embraced
>> > Unicode 1.0 (when it "guaranteed" a 16 bit character space) to support
>> the
>> > full space of Unicode code points from U+ to U+10 via surrogate
>> > pairs. Modern posix systems (as far as I know) define wchar_t to be a 32
>> > bit type, so you can't really convert to wchar_t in a portable way,
>> because
>> > you have to handle surrogate pairs on Windows vs simple code points in
>> > posix (though this should be handled by mbstowcs if the platform supports
>> > wchar_t as unicode).
>> >
>> > If you only use ASCII or Latin-1 8 bit characters in your code, or are
>> > willing to treat all char objects as ASCII or Latin-1, then you can
>> convert
>> > char strings to wchar_t strings by simply zero extending each character
>> > while copying it. Something like this (without any error checking):
>> >
>> > void copy_narrow_to_wide(wchar_t* dst, const char* src)
>> > {
>> >   while (*src) *(dst++) = (unsigned char)(*(src++));
>> >   *dst = 0;
>> > }
>> >
>> > --
>> > Scott Robison
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users at mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Array or set type or some other indexable data?

[J Decker]

> So something like "select value from option_map_view where path is  set type>"?
> A path name like '/system/device/com port/1' is used as an array of names
> here. Only the indexing with intst and substr is laborious. Maybe some
> future SQLite version includes a group_split function to make this easier.
> It seems impossible to me in plain SQL to write an efficient view for a
> query like this. As you say below the whole map need to be walked at each
> query to find a match.
> User defined functions, virtual tables or function based indexes may offer
> an efficient solution.
>

Hmm a user function could be interesting; hard to see a threadsafe
version but could keep the array of values internally and and return
them as 'getOption(n)' as I descend each layer...

was thinking that 'select * from something where name in (a,b,c,d,e)'
could be used...(syntactically semi-appealing, using the depth to
select the member of the set)  but there's no access to the 'where'
parameters as input values for the CTE...

suppose I can see a virtual table solution; to stuff level/option_name
and join on that to get names...

but as it is; would still be hard to use as a view even; since the
select options are part of the select (value)...

but still kinda useless given no stored procedures... which might make
it more usable on other databases...

[sqlite] Array or set type or some other indexable data?

[J Decker]

So... I guess something like this works; other than the triplication
of the initial path string. works for read-only access... doesn't
return where it failed (although can with leaf == 0 )


option4_map ( option_id,parent_option_id,name_id )
option4_names ( name_id, name )
option4_values ( option_id, string_segment, segment_number )

-- rpath is remaining path

with option (option_id,path,rpath, leaf) as (\
select option_id   \
,'./DEFAULT/jackpot_sign/sack/PSI/Frame border/Height'  as path\
, substr( './DEFAULT/jackpot_sign/sack/PSI/Frame border/Height' \
, instr( './DEFAULT/jackpot_sign/sack/PSI/Frame
border/Height', '/' ) + 1 ) as rpath \
, 0 as leaf \
 from option4_map \
 join option4_name on option4_map.name_id=option4_name.name_id   \
 where option_id='----'\
   and name=substr(path,0,instr(path,'/')) \
union all select option4_map.option_id, name, ''as path,  substr(
rpath, instr( rpath, '/' ) + 1 )   \
 , instr(rpath,'/') == 0 as leaf   \
   from option4_map\
   join option4_name on option4_map.name_id=option4_name.name_id  \
   join option on option.option_id=option4_map.parent_option_id\
   where option4_map.parent_option_id != option4_map.option_id\
and ( name= substr( rpath, 0, instr( rpath, '/' )
)  or name = rpath ) \
)  \
select * from option
join option4_values on option.option_id=option4_values.option_id
where leaf=1   -- added leaf indicator to just select final value.
order by segment_number



-- Yes; given such a large name_id, it would less data to just store
the name usually in the option4_map entry... used to just be an int
but it evolved... guess I should revise a version 5...

CREATE TABLE `option4_name` (
`name_id` char(36) NOT NULL
,`name` varchar(255) NOT NULL default ''
CONSTRAINT `name` UNIQUE)
 index,sqlite_autoindex_option4_name_1,option4_name,3,NULL
(index on name_id)

CREATE TABLE `option4_map` (
 `option_id` char(36) NOT NULL
 ,`parent_option_id` char(36) NOT NULL default '0'
 ,`name_id` char(36) NOT NULL default '0'
 ,`description` tinytext
 ,CONSTRAINT `parent_key2` UNIQUE
(`parent_option_id`,`name_id`) ON CONFLICT REPLACE
 ,CONSTRAINT `FK_map_map` FOREIGN KEY (`parent_option_id`)
  REFERENCES `option4_map`(`option_id`)ON UPDATE
CASCADE ON DELETE CASCADE
 ,CONSTRAINT `FK_name_map` FOREIGN KEY (`name_id`)
  REFERENCES `option4_name`(`name_id`)ON UPDATE
CASCADE ON DELETE CASCADE)

index,sqlite_autoindex_option4_map_1,option4_map,5,NULL
(index on option_id)

CREATE TABLE `option4_values` (
  `option_id` char(36) default '0'
  ,`string` varchar(100) default NULL
  ,`segment` int(11) default 0
  ,CONSTRAINT `value_id` UNIQUE (`option_id`,`segment`) ON
CONFLICT REPLACE
 ,CONSTRAINT `FK_map_values` FOREIGN KEY (`option_id`)
  REFERENCES `option4_map`(`option_id`)ON UPDATE
CASCADE ON DELETE CASCADE)

  index,sqlite_autoindex_option4_values_1,option4_values,11,NULL
(index on option_id)


On Thu, Nov 12, 2015 at 8:35 AM, J Decker  wrote:
> On Thu, Nov 12, 2015 at 7:16 AM, E.Pasma  wrote:
>> 12 nov 2015, om 07:02, J Decker:
>>
>>
>>> So I've used CTE to solve a simple problem... I'm tempted to use it to
>>> fix more problems... but I'm wondering how to select different values
>>> at different levels.  I know there's like 'select * from table where
>>> column in ( set,of,things) ' but can I index into the set?  is there
>>> some sort of way I can specify an array of values?
>>>
>>> would like to do something like ' select value from option_map where
>>> name is ["system","device","com port", "1" ] '  where the program
>>> statement would look more like
>>>
>>> GetOptionValue ( "/system/device/com port/1" ) where the name is
>>> really sort of registry like and variable in length...
>>>
>>> I could probably do some sort of indexing passing that exact string
>>> through and parsing in SQL the substrings of interest based on the
>>> level of the query... but would rather preparse the string.
>>
>>
>>
>> Below is another possible answer. This uses a recursive cte to split an
>> input full path name into seperate names.
>> create table map (id, name, parent);
>> insert into map values
>

[sqlite] Array or set type or some other indexable data?

[J Decker]

On Thu, Nov 12, 2015 at 7:16 AM, E.Pasma  wrote:
> 12 nov 2015, om 07:02, J Decker:
>
>
>> So I've used CTE to solve a simple problem... I'm tempted to use it to
>> fix more problems... but I'm wondering how to select different values
>> at different levels.  I know there's like 'select * from table where
>> column in ( set,of,things) ' but can I index into the set?  is there
>> some sort of way I can specify an array of values?
>>
>> would like to do something like ' select value from option_map where
>> name is ["system","device","com port", "1" ] '  where the program
>> statement would look more like
>>
>> GetOptionValue ( "/system/device/com port/1" ) where the name is
>> really sort of registry like and variable in length...
>>
>> I could probably do some sort of indexing passing that exact string
>> through and parsing in SQL the substrings of interest based on the
>> level of the query... but would rather preparse the string.
>
>
>
> Below is another possible answer. This uses a recursive cte to split an
> input full path name into seperate names.
> create table map (id, name, parent);
> insert into map values
> (0, '',''),-- level 0
> (1, 'system', 0),-- level 1
> (2, 'device', 1),-- level 2
> (3, 'com port', 2),-- level 3
> (4, '1', 3), -- level 4
> (5, '2', 3)  -- level 4
> ;
> with walk as (
> select  0 as parent, --root
> '/system/device/com port/1' as path ,
  0 as level
> union
> select  id,
> substr(path,length(name)+2) ,
  level+1 as level
> fromwalk
> joinmap using(parent)
> where   name = substr(path,2,instr(substr(path,2)||'/','/')-1)  --oef
> )
> select parent as id from walk
> where   path='' -- nothing left
> ;

(something like that)

was hoping to not have to do the substr part in the query
and would like the path to be more on the external usage of 'walk' in
this case than inside the expression

would be better than building up the address to get a comparison at
the end since the whole map would have to be walked.

TO answer other's questions; 'level' refers to the dept of the tree
searched... level indiciators added as comments to the insert above...

[sqlite] Array or set type or some other indexable data?

[J Decker]

So I've used CTE to solve a simple problem... I'm tempted to use it to
fix more problems... but I'm wondering how to select different values
at different levels.  I know there's like 'select * from table where
column in ( set,of,things) ' but can I index into the set?  is there
some sort of way I can specify an array of values?

would like to do something like ' select value from option_map where
name is ["system","device","com port", "1" ] '  where the program
statement would look more like

GetOptionValue ( "/system/device/com port/1" ) where the name is
really sort of registry like and variable in length...

I could probably do some sort of indexing passing that exact string
through and parsing in SQL the substrings of interest based on the
level of the query... but would rather preparse the string.

[sqlite] Select after update get new or old values

[J Decker]

which values does it have?
By definition it should be old values.

On Thu, Sep 10, 2015 at 9:05 AM, Nguyen Dang Quang  wrote:
> Dear all,
>
> I am using System.Data.SQLite for my .Net 2.0 application.
>
> In the app, I always run a sequence like that:
>
> 1.  Open connection to sqlite and apply pragma: SYNCHRONOUS = OFF ;
> JOURNAL_MODE = MEMORY
>
> 2.  Open a transaction
>
> 3.  Update a row
>
> 4.  Select the updated row in (3) to further processing.
>
> 5.  Loop to (3) until no data to update
>
> 6.  Commit the transation.
>
> In (4) the selected rows will have old or new values?
>
> Please help.
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] journal files not always removed

[J Decker]

Okay let's restart... maybe I misunderstood that Richard was able to
reproduce this.

I have figured out a simple script that with 3.8.11.1 windows (using
binary download sqlite3.exe) causes the problem.

running just 'sqlite3.exe'

--- script ---
.open test.db
pragma journal_mode=wal;
.open test.db
create table test(id int);
.quit

this always leaves -shm and -wal files laying around.
it's when there's multiple opens.


tested on linux and test.db-shm and -wal files stay around until the
program closes.  But they should normally be gone at the end of the
create table?

[root at tower2 ~]# sqlite3
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open /tmp/test.db
sqlite> pragma journal_mode=WAL;
wal
sqlite> .open /tmp/test.db
sqlite> create table test(id int);
sqlite> (ctrl-z)
[4]+  Stopped sqlite3
[root at tower2 ~]# ls /tmp
 test.db
 test.db-shm
 test.db-wal

they do get deleted after the .quit on linux.

[sqlite] journal files not always removed

[J Decker]

On Sat, Aug 15, 2015 at 1:59 PM, Richard Hipp  wrote:
> On 8/15/15, Ashwin Hirschi  wrote:
>>
>>> But I also see that behavior dates back to 3.7.6 and maybe earlier. It
>>> isn't something new.
>>> (Testing on Ubuntu).
>>
>> Also, are you sure you testing things right? I thought URI support was
>> only added in 3.7.7... If that's true, your little script should actually
>> throw an error for 3.7.6!
>>
>
> My mistake.  Checked my shell history and in fact the problem goes
> back to 3.7.8, not 3.7.6 as I originally reported.  The point is: It
> goes back a long time.  This is on Ubuntu though.  Maybe something
> changed on Windows.

was there some progress on this?
I was noticing that -shm and -wal files are left around, database is
not readonly mode; I thought it was because I had a bad
(half-functional) virtual file driver for it; but I checked today and
it's not triggering using that code; although simple tests with
sqlite3 command line tool I wasn't able to make it happen with what I
thought was the cause... I will dig into it more later I guess

> --
> 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] order by not working in combination with random()

[J Decker]

On Wed, Aug 26, 2015 at 2:47 AM, Graham Holden  wrote:
> And while "SELECT random() AS rr ORDER BY rr" is slightly contrived, the 
> example from J Decker:
>
contrived? cause I copied it from the original poster's first message?
it was used in MySQL as a way to shuffle a deck of cards, and worked there too.

[sqlite] order by not working in combination with random()

[J Decker]

On Tue, Aug 25, 2015 at 7:16 PM, Stephen Chrzanowski
 wrote:
> Somewhat of a devils advocate here, but I'm not sure one can order based on
> JUST data.  Take the DATE function, for example.  If, by your words, ORDER
> BY should only act on the data, consider this kind of query:
>
> select ItemName,SoldDate from SoldItemDetails order by
> SoldDate
> [untested, but here's the theory]
>
> What this theoretical query would do is list all items in the table
> grouping items sold in the past month at the top of the list, then, order
> by the date in ascending order.  Considering DATE isn't part of the data,
> you now have to open up the door to judging what should and shouldn't be
> considered to be evaluated by ORDER BY.
>
but, your order by is the only place that uses the date function... it
would have to be reversed as
elect ItemName,SoldDate, date('now','-1 month') as z from
SoldItemDetails order by
 SoldDate On Tue, Aug 25, 2015 at 10:00 PM, James K. Lowden  schemamania.org>
> wrote:
>
>>
>> ORDER BY should evaluate the *data* only.  In the case of random(),
>> like any other function it gets invoked once, by SELECT, and produces a
>> value.  That value is the one that ORDER BY should operate on.
>>
>> --jkl
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[J Decker]

On Fri, Aug 21, 2015 at 10:04 AM, sqlite-mail  
wrote:
> Then do you think this is a bug ?
>>  Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" 
>>Subject: Re: [sqlite] Is this a bug ? How to rename a table and all
>>dependencies ?
while it may be considered a nice thing; it's not common practice to
rename tables, so few (none?) sql implementations automatically update
references to tables when they are renamed.  Not a bug; more like out
of scope.

It wouldn't know if you were moving a source table for archival
purposes and going to replace it with another empty one or moving
because you didn't like your original schema.
>>
>>  On 21 Aug 2015, at 12:20pm, sqlite-mail  
>> wrote:
>>
>>
>>>Does anybody knows how to rename a table and all it's dependencies in one
>>>go
>>> ?
>>>
>
>>  Can't be done. Sorry.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users