[sqlite] Is it possible that keep SQLite in normal lockingmodewithout mmap?

[sanhua.zh]

It?s a mistake ofexpression.
I mean,
1. readconcurrently indifferent threads.
or
2. write in one thread andread inother threads.
noconcurrently writing happened.


Sorry about this. I come from a not-English-speaking country, it's hard for me 
to express myself.



???:Simon Slavinslavins at bigfraud.org
???:General Discussion of SQLite Databasesqlite-users at mailinglists.sqlite.org
:2015?10?9?(??)?20:38
??:Re: [sqlite] Is it possible that keep SQLite in normal lockingmodewithout 
mmap?


On 9 Oct 2015, at 1:29pm, sanhua.zh sanhua.zh at foxmail.com wrote:  2. 
multithread-reading-and-writing, not serial It is not possible for two 
different operations, whether different threads or different processes, to 
write to a database at the same time. No matter what system you use this just 
isn't possible. To prove this to yourself, imagine that you had a table with a 
UNIQUE index, and that two threads/processes tried to write rows with the same 
key. The correct way for the API to respond would be to allow one to write its 
row, and to return an error to the other. This cannot happen if the system 
accepts both write requests at the same time. Writing to a database /must/ be 
serial. There is a work-around for this which involve writing new rows by 
appending the data to a text file (entirely outside SQL) and having another 
process read its way through the text file updating the database file. 
Appending to a text file is faster than updating a SQLite database. There are 
other work-arounds too. But they all involve writing new data to some place 
which is not the live database. Simon. 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it possible that keep SQLite in normal locking modewithout mmap?

[sanhua.zh]

Hello,
I am using SQLite on iOS developing.
Now I need the better performance of multithread-reading-and-writing and the 
robustness ofWAL journal mode without mmap.
As I know,WALjournal mode without mmap requires EXCLUSIVE locking mode, which 
may prevent multithread-reading-and-writing. But mmap may increase 
theprobability of database corruption. While my app has a huge number of 
users(more than ten-million), I have to consider more about robustness.
see also:
http://www.sqlite.org/wal.html#noshm
http://www.sqlite.org/pragma.html#pragma_locking_mode


As a conclusion, I wonder, in SQLite, whether is possible for
1. WAL mode
2. multithread-reading-and-writing, not serial
3. without mmap
If yes, can you tell me a general idea for implementing this. No matter it 
needs to modify the source code or how difficult it is.


Thank you for your time and I appreciate your reply.

[sqlite] Proposed new version numbering scheme for SQLite - Feedback requested

[Klaas V]

On Thu, Oct 8, 2015 at 8:36 AM, Simon Slavin  wrote:

>
> On 8 Oct 2015, at 2:38pm, Richard Hipp  wrote:
>
> > If accepted, the new policy will cause the next release to be 3.9.0
> > instead of 3.8.12.  And the second number in the version will be
> > increased much more aggressively in future releases.
>
> I approve of this particular release changing the Y value (i.e. being
> 3.9.0) since it allows SQLite to create and change databases to a format
> which can't be opened with previous versions.
>

I don't care a rat's behind what number changes next in the naming convention 
3vvuupp 
where vv = 'version', uu = 'update' ; pp = patch 

As someone else noted what is the criterion to call a revision major/minor 
version and is one of them applicable in the transition 3081101 to
 309 respectively 3081200. 

If the incompatibilities are as great as feared I'd go to for a new vv, if as 
small as hoped, let's feel free and modest call it "just an" update.

My ?0.02

Kind regards |?Cordiali saluti | Vriendelijke groeten | Freundliche Gr?sse,
Klaas `Z4us` V ?- LinkedIn 437429414

[sqlite] compiling sqlite3 with FTS5 support

[Dan Kennedy]

On 10/09/2015 10:18 AM, chromedout64 at yahoo.com wrote:
> I noticed there was talk in the other thread of including FTS5 support in the 
> amalgamation. That would be great and it seems like that might solve my 
> problem. Is that correct?
>
> Also, I noticed what may be a couple typos in the FTS5 documentation at 
> https://www.sqlite.org/fts5.html.
> In the external content table section, the example states:
> CREATE TABLE fts USING fts5(a, c, content=tbl, content_rowid=d);
> I think it should be CREATE VIRTUAL TABLE instead of CREATE TABLE.
>
> Also, in the overview section, the text says:
> The expression to the right of the MATCH operator must be the nameof the FTS5 
> table. The expression on the left must be a text value specifyingthe term to 
> search for.I think the right and left's are backwards.

Thanks for these. Now fixed.

Dan.

[sqlite] Problem with sqlite3_db_filename

[Rowan Worth]

Suspect you are running into more VBA<->C issues. The db path, journal path
and wal path are stored sequentially in memory, so if you were to skip the
NUL terminators you'd see all three paths.

But I'm not exactly sure how that might happen without resulting in a
segfault, so I could be missing something.

-Rowan

On 9 October 2015 at 16:18, Bart Smissaert  wrote:

> All working fine now, but noticed that for the attached database I get not
> just the file path of the attached database, but also the journal
> file path and wal file path:
>
> C:\Test\NewDB2.db3C:\Test\NewDB2.db3-journalC:\Test\NewDB2.db3-wal
>
> There is actually no journal file or wal file that I can see in that
> folder.
>
> For main I only get the file path of that main database.
>
> Is this behaviour as expected?
> Is it documented somewhere?
>
>
> RBS
>
>
>
> On Thu, Oct 8, 2015 at 5:18 PM, Bart Smissaert 
> wrote:
>
> > Ignore this as I know what the problem was.
> > I was passing a pointer to the Unicode string, but should be pointer to
> > 8bit ASCII string.
> >
> > RBS
> >
> > On Thu, Oct 8, 2015 at 9:58 AM, Bart Smissaert  >
> > wrote:
> >
> >> OK, thanks, at least I know that the function works fine then in
> >> sqlite3.dll.
> >> Problem must be on my side then.
> >>
> >> This is the code in the Std_Call dll:
> >>
> >> SQLITE3_STDCALL_API const char * __stdcall
> sqlite3_stdcall_db_filename(sqlite3
> >> *pDb, const char *zDbName)
> >> {
> >> return sqlite3_db_filename(pDb, zDbName);
> >> }
> >>
> >> And this is the Declare in VBA:
> >>
> >> Public Declare Function sqlite3_stdcall_db_filename Lib
> "SQLite3_StdCall"
> >> Alias "_sqlite3_stdcall_db_filename at 8" (ByVal hDBHandle As Long, ByVal
> >> lPtrAttachedDBName As Long) As Long
> >>
> >> Anything wrong with either of these?
> >>
> >>
> >> RBS
> >>
> >>
> >>
> >>
> >> On Thu, Oct 8, 2015 at 9:40 AM, Dan Kennedy 
> >> wrote:
> >>
> >>> On 10/08/2015 03:51 AM, Bart Smissaert wrote:
> >>>
>  As I understand it this should produce a filepointer to the filepath
> of
>  the
>  attached database, given the database handle of file the other
> database
>  was
>  attached to and the database name of the attached database. I checked
>  all
>  the return values and also did a select involving tables in both
>  databases and all goes fine, so I can be sure that the other database
> is
>  attached OK.
>  All I get from sqlite3_db_filename is zero, so no valid file pointer.
> No
>  error messages though.
> 
>  I am accessing sqlite3.dll (Windows 7) via a std_call dll as I am
>  working
>  in VBA here.
> 
>  Any suggestions what could be the problem?
> 
>  I am running 3.8.11.1
> 
> 
> >>> The program below works here.
> >>>
> >>> I'm seeing full paths for databases "main" and "aux", and a zero-length
> >>> nul-terminated string for "next" (the in-memory database).
> >>>
> >>> Dan
> >>>
> >>> -
> >>>
> >>>
> >>>
> >>> #include 
> >>> #include 
> >>> #include 
> >>>
> >>> int main(int argc, char **argv){
> >>>   int rc;
> >>>   sqlite3 *db;
> >>>
> >>>   rc = sqlite3_open("test.db", );
> >>>   if( rc!=SQLITE_OK ){
> >>> fprintf(stderr, "sqlite3_open: %s\n", sqlite3_errmsg(db));
> >>> exit(1);
> >>>   }
> >>>
> >>>   rc = sqlite3_exec(db, "ATTACH 'other.db' AS 'aux'", 0, 0, 0);
> >>>   if( rc!=SQLITE_OK ){
> >>> fprintf(stderr, "sqlite3_exec: %s\n", sqlite3_errmsg(db));
> >>> exit(1);
> >>>   }
> >>>
> >>>   rc = sqlite3_exec(db, "ATTACH ':memory:' AS 'next'", 0, 0, 0);
> >>>   if( rc!=SQLITE_OK ){
> >>> fprintf(stderr, "sqlite3_exec: %s\n", sqlite3_errmsg(db));
> >>> exit(1);
> >>>   }
> >>>
> >>>   printf("main  db is: %s\n", sqlite3_db_filename(db, "main"));
> >>>   printf("aux   db is: %s\n", sqlite3_db_filename(db, "aux"));
> >>>   printf("next  db is: %s\n", sqlite3_db_filename(db, "next"));
> >>>
> >>>   return 0;
> >>> }
> >>>
> >>> ___
> >>> 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] Ideas on how to use backup API for remote, incremental backup?

[David Barrett]

Thanks Richard, this is exactly what I was thinking.  One question on this:

On Tue, Oct 6, 2015 at 10:22 PM, Richard Hipp  wrote:

> (2) For the source database connection of the backup, use the same
> database connection that is used for writing to the database.  That
> means that when changes are made to the source database, the backup
> mechanism can go back and resend only those pages that actually
> changed.  If the database is modified by any database connection other
> than the one used for backup, then the backup has no way of knowing
> which pages changed, and so it has to start over again at the
> beginning and rewrite every page.
>

Hm, that's interesting.  I was thinking that when the backup started, all
new writes would just accumulate in the WAL file -- and then be
checkpointed after the backup completed.  Thus the backup would be of the
state of the database at the moment the backup was *started*.  However,
based on your description it sounds like the backup API will copy over
changes that have occurred throughout the backup process itself. Our
database is modified about 40 times per second, so my fear is the backup
API would never actually complete because it would be continuously
re-backing up the same pages.  Am I understanding this correctly?  Is there
any way to obtain the behavior I initially assumed?

Thanks!

-david

[sqlite] Is it possible that keep SQLite in normal lockingmodewithout mmap?

[Simon Slavin]

On 9 Oct 2015, at 1:46pm, sanhua.zh  wrote:

> It?s a mistake ofexpression.
> I mean,
> 1. readconcurrently indifferent threads.
> or
> 2. write in one thread andread inother threads.

Okay.  That is what WAL mode does very well.  There's nothing better you can do 
for this than to use WAL mode.

Once you have decided to use WAL mode there are other settings you can change, 
but they are mostly just decisions about which you value most: speed or safety. 
 If you demand that your database is very robust and will resist corruption, 
then you cannot have very fast speed as well.

iOS phones are extremely fast.  Unless you have already written your program 
and found it is too slow, you may be worrying about this problem unnecessarily. 
 The normal guidance for SQLite programmers is to first write their application 
without using any special PRAGMAs and then to see how fast it is.

Simon.

[sqlite] Is it possible that keep SQLite in normal locking modewithout mmap?

[Simon Slavin]

On 9 Oct 2015, at 1:29pm, sanhua.zh  wrote:

> 2. multithread-reading-and-writing, not serial

It is not possible for two different operations, whether different threads or 
different processes, to write to a database at the same time.  No matter what 
system you use this just isn't possible.

To prove this to yourself, imagine that you had a table with a UNIQUE index, 
and that two threads/processes tried to write rows with the same key.  The 
correct way for the API to respond would be to allow one to write its row, and 
to return an error to the other.  This cannot happen if the system accepts both 
write requests at the same time.  Writing to a database /must/ be serial.

There is a work-around for this which involve writing new rows by appending the 
data to a text file (entirely outside SQL) and having another process read its 
way through the text file updating the database file.  Appending to a text file 
is faster than updating a SQLite database.  There are other work-arounds too.  
But they all involve writing new data to some place which is not the live 
database.

Simon.

[sqlite] Order with another sequence

[Hick Gunter]

It should be possible with a custom collation sequence.

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Cecil 
Westerhof
Gesendet: Freitag, 09. Oktober 2015 12:08
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Order with another sequence

When I use ORDER BY an ? comes after a z. Is it possible to make an ? come 
after a z?

If it is important I am using SQLite 3.8.6 and Python 3.4.1.

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Problem with sqlite3_db_filename

[Bart Smissaert]

Thanks, will sort this out.

RBS

On Fri, Oct 9, 2015 at 10:16 AM, Graham Holden 
wrote:

> From memory of VB6, a string returned from a DLL call doesn't have its
> length set - you ideally need the call to return the length and do x$ =
> left$( x$, xlength ) before continuing. If the length isn't available, you
> might be able to search for a zero-byte in VB(A), or you may have to write
> your own C wrapper that returns strlen() of the string.
>
> Graham.
>
>
> Sent from Samsung Mobile
>
>  Original message 
> From: Rowan Worth 
> Date: 09/10/2015  09:52  (GMT+00:00)
> To: General Discussion of SQLite Database <
> sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Problem with sqlite3_db_filename
>
> Suspect you are running into more VBA<->C issues. The db path, journal path
> and wal path are stored sequentially in memory, so if you were to skip the
> NUL terminators you'd see all three paths.
>
> But I'm not exactly sure how that might happen without resulting in a
> segfault, so I could be missing something.
>
> -Rowan
>
> On 9 October 2015 at 16:18, Bart Smissaert 
> wrote:
>
> > All working fine now, but noticed that for the attached database I get
> not
> > just the file path of the attached database, but also the journal
> > file path and wal file path:
> >
> > C:\Test\NewDB2.db3C:\Test\NewDB2.db3-journalC:\Test\NewDB2.db3-wal
> >
> > There is actually no journal file or wal file that I can see in that
> > folder.
> >
> > For main I only get the file path of that main database.
> >
> > Is this behaviour as expected?
> > Is it documented somewhere?
> >
> >
> > RBS
> >
> >
> >
> > On Thu, Oct 8, 2015 at 5:18 PM, Bart Smissaert  >
> > wrote:
> >
> > > Ignore this as I know what the problem was.
> > > I was passing a pointer to the Unicode string, but should be pointer to
> > > 8bit ASCII string.
> > >
> > > RBS
> > >
> > > On Thu, Oct 8, 2015 at 9:58 AM, Bart Smissaert <
> bart.smissaert at gmail.com
> > >
> > > wrote:
> > >
> > >> OK, thanks, at least I know that the function works fine then in
> > >> sqlite3.dll.
> > >> Problem must be on my side then.
> > >>
> > >> This is the code in the Std_Call dll:
> > >>
> > >> SQLITE3_STDCALL_API const char * __stdcall
> > sqlite3_stdcall_db_filename(sqlite3
> > >> *pDb, const char *zDbName)
> > >> {
> > >> return sqlite3_db_filename(pDb, zDbName);
> > >> }
> > >>
> > >> And this is the Declare in VBA:
> > >>
> > >> Public Declare Function sqlite3_stdcall_db_filename Lib
> > "SQLite3_StdCall"
> > >> Alias "_sqlite3_stdcall_db_filename at 8" (ByVal hDBHandle As Long,
> ByVal
> > >> lPtrAttachedDBName As Long) As Long
> > >>
> > >> Anything wrong with either of these?
> > >>
> > >>
> > >> RBS
> > >>
> > >>
> > >>
> > >>
> > >> On Thu, Oct 8, 2015 at 9:40 AM, Dan Kennedy 
> > >> wrote:
> > >>
> > >>> On 10/08/2015 03:51 AM, Bart Smissaert wrote:
> > >>>
> >  As I understand it this should produce a filepointer to the filepath
> > of
> >  the
> >  attached database, given the database handle of file the other
> > database
> >  was
> >  attached to and the database name of the attached database. I
> checked
> >  all
> >  the return values and also did a select involving tables in both
> >  databases and all goes fine, so I can be sure that the other
> database
> > is
> >  attached OK.
> >  All I get from sqlite3_db_filename is zero, so no valid file
> pointer.
> > No
> >  error messages though.
> > 
> >  I am accessing sqlite3.dll (Windows 7) via a std_call dll as I am
> >  working
> >  in VBA here.
> > 
> >  Any suggestions what could be the problem?
> > 
> >  I am running 3.8.11.1
> > 
> > 
> > >>> The program below works here.
> > >>>
> > >>> I'm seeing full paths for databases "main" and "aux", and a
> zero-length
> > >>> nul-terminated string for "next" (the in-memory database).
> > >>>
> > >>> Dan
> > >>>
> > >>> -
> > >>>
> > >>>
> > >>>
> > >>> #include 
> > >>> #include 
> > >>> #include 
> > >>>
> > >>> int main(int argc, char **argv){
> > >>>   int rc;
> > >>>   sqlite3 *db;
> > >>>
> > >>>   rc = sqlite3_open("test.db", );
> > >>>   if( rc!=SQLITE_OK ){
> > >>> fprintf(stderr, "sqlite3_open: %s\n", sqlite3_errmsg(db));
> > >>> exit(1);
> > >>>   }
> > >>>
> > >>>   rc = sqlite3_exec(db, "ATTACH 'other.db' AS 'aux'", 0, 0, 0);
> > >>>   if( rc!=SQLITE_OK ){
> > >>> fprintf(stderr, "sqlite3_exec: %s\n", sqlite3_errmsg(db));
> > >>> exit(1);
> > >>>   }
> > >>>
> > >>>   rc = sqlite3_exec(db, "ATTACH ':memory:' AS 'next'", 0, 0, 0);
> > >>>   if( rc!=SQLITE_OK ){
> > >>> fprintf(stderr, "sqlite3_exec: %s\n", sqlite3_errmsg(db));
> > >>> exit(1);
> > >>>   }
> > >>>
> > >>>   printf("main  db is: %s\n", sqlite3_db_filename(db, "main"));
> > >>>   printf("aux   db is: %s\n", sqlite3_db_filename(db, "aux"));
> > >>>   printf("next  db is: %s\n", 

[sqlite] Problem with sqlite3_db_filename

[Bart Smissaert]

Thanks for that. Must indeed be a C > VBA problem and will sort this out.

RBS

On Fri, Oct 9, 2015 at 9:52 AM, Rowan Worth  wrote:

> Suspect you are running into more VBA<->C issues. The db path, journal path
> and wal path are stored sequentially in memory, so if you were to skip the
> NUL terminators you'd see all three paths.
>
> But I'm not exactly sure how that might happen without resulting in a
> segfault, so I could be missing something.
>
> -Rowan
>
> On 9 October 2015 at 16:18, Bart Smissaert 
> wrote:
>
> > All working fine now, but noticed that for the attached database I get
> not
> > just the file path of the attached database, but also the journal
> > file path and wal file path:
> >
> > C:\Test\NewDB2.db3C:\Test\NewDB2.db3-journalC:\Test\NewDB2.db3-wal
> >
> > There is actually no journal file or wal file that I can see in that
> > folder.
> >
> > For main I only get the file path of that main database.
> >
> > Is this behaviour as expected?
> > Is it documented somewhere?
> >
> >
> > RBS
> >
> >
> >
> > On Thu, Oct 8, 2015 at 5:18 PM, Bart Smissaert  >
> > wrote:
> >
> > > Ignore this as I know what the problem was.
> > > I was passing a pointer to the Unicode string, but should be pointer to
> > > 8bit ASCII string.
> > >
> > > RBS
> > >
> > > On Thu, Oct 8, 2015 at 9:58 AM, Bart Smissaert <
> bart.smissaert at gmail.com
> > >
> > > wrote:
> > >
> > >> OK, thanks, at least I know that the function works fine then in
> > >> sqlite3.dll.
> > >> Problem must be on my side then.
> > >>
> > >> This is the code in the Std_Call dll:
> > >>
> > >> SQLITE3_STDCALL_API const char * __stdcall
> > sqlite3_stdcall_db_filename(sqlite3
> > >> *pDb, const char *zDbName)
> > >> {
> > >> return sqlite3_db_filename(pDb, zDbName);
> > >> }
> > >>
> > >> And this is the Declare in VBA:
> > >>
> > >> Public Declare Function sqlite3_stdcall_db_filename Lib
> > "SQLite3_StdCall"
> > >> Alias "_sqlite3_stdcall_db_filename at 8" (ByVal hDBHandle As Long,
> ByVal
> > >> lPtrAttachedDBName As Long) As Long
> > >>
> > >> Anything wrong with either of these?
> > >>
> > >>
> > >> RBS
> > >>
> > >>
> > >>
> > >>
> > >> On Thu, Oct 8, 2015 at 9:40 AM, Dan Kennedy 
> > >> wrote:
> > >>
> > >>> On 10/08/2015 03:51 AM, Bart Smissaert wrote:
> > >>>
> >  As I understand it this should produce a filepointer to the filepath
> > of
> >  the
> >  attached database, given the database handle of file the other
> > database
> >  was
> >  attached to and the database name of the attached database. I
> checked
> >  all
> >  the return values and also did a select involving tables in both
> >  databases and all goes fine, so I can be sure that the other
> database
> > is
> >  attached OK.
> >  All I get from sqlite3_db_filename is zero, so no valid file
> pointer.
> > No
> >  error messages though.
> > 
> >  I am accessing sqlite3.dll (Windows 7) via a std_call dll as I am
> >  working
> >  in VBA here.
> > 
> >  Any suggestions what could be the problem?
> > 
> >  I am running 3.8.11.1
> > 
> > 
> > >>> The program below works here.
> > >>>
> > >>> I'm seeing full paths for databases "main" and "aux", and a
> zero-length
> > >>> nul-terminated string for "next" (the in-memory database).
> > >>>
> > >>> Dan
> > >>>
> > >>> -
> > >>>
> > >>>
> > >>>
> > >>> #include 
> > >>> #include 
> > >>> #include 
> > >>>
> > >>> int main(int argc, char **argv){
> > >>>   int rc;
> > >>>   sqlite3 *db;
> > >>>
> > >>>   rc = sqlite3_open("test.db", );
> > >>>   if( rc!=SQLITE_OK ){
> > >>> fprintf(stderr, "sqlite3_open: %s\n", sqlite3_errmsg(db));
> > >>> exit(1);
> > >>>   }
> > >>>
> > >>>   rc = sqlite3_exec(db, "ATTACH 'other.db' AS 'aux'", 0, 0, 0);
> > >>>   if( rc!=SQLITE_OK ){
> > >>> fprintf(stderr, "sqlite3_exec: %s\n", sqlite3_errmsg(db));
> > >>> exit(1);
> > >>>   }
> > >>>
> > >>>   rc = sqlite3_exec(db, "ATTACH ':memory:' AS 'next'", 0, 0, 0);
> > >>>   if( rc!=SQLITE_OK ){
> > >>> fprintf(stderr, "sqlite3_exec: %s\n", sqlite3_errmsg(db));
> > >>> exit(1);
> > >>>   }
> > >>>
> > >>>   printf("main  db is: %s\n", sqlite3_db_filename(db, "main"));
> > >>>   printf("aux   db is: %s\n", sqlite3_db_filename(db, "aux"));
> > >>>   printf("next  db is: %s\n", sqlite3_db_filename(db, "next"));
> > >>>
> > >>>   return 0;
> > >>> }
> > >>>
> > >>> ___
> > >>> 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 

[sqlite] Order with another sequence

[Cecil Westerhof]

When I use ORDER BY an ? comes after a z. Is it possible to make an ? come
after a z?

If it is important I am using SQLite 3.8.6 and Python 3.4.1.

-- 
Cecil Westerhof

[sqlite] version 3.9.0 doc errors

[Niall O'Reilly]

On Thu, 08 Oct 2015 21:12:51 +0100,
R.Smith wrote:
> 
> 
> *** Correction ***

  It's not, but you don't want to get me started. 8-)

> On 2015-10-08 10:03 PM, R.Smith wrote:
> > 
> > To clarify, when used as an adverb to modify a verb, you may well
> > add the s - such as saying "I'm moving backwards" or "It's a
> > forwards marching army."//...
> 
> "Marching" is of course an adverb here, not a verb. A more correct
> example would be: "He's forwards marching".
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Version 3.8.12 cancelled. Next release is 3.9.0.

[Richard Hipp]

The next release of SQLite will now be version 3.9.0.  The sources has
been updated to use "semantic versioning" and the JSON1 and FTS5
extensions have been incorporated into the amalgamation.

Draft documentation is available at:

 https://www.sqlite.org/draft/releaselog/3_9_0.html
 https://www.sqlite.org/draft/index.html

A snapshot of the latest amalgamation that includes JSON1 and FTS5 can
be found at the top link on:

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

Compile the snapshot above using SQLITE_ENABLE_JSON1 and/or
SQLITE_ENABLE_FTS5 to enable the new extensions.

Please try out this and subsequent snapshots if you are able and
report any problems to this mailing list, or directly to me, as
quickly as possible, so that we will have time to get fixes into the
official 3.9.0 release.

A new release check-list is available at:

 https://www.sqlite.org/checklists/309/index

We will spend the next day or two running preliminary checks on the
new code and cleaning up the documentation.  Expect to see items of
the checklist going green early next week.  The 3.9.0 release will
occur when the checklist goes completely green.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Proposed new version numbering scheme for SQLite - Feedback requested

[Jan Nijtmans]

2015-10-08 15:38 GMT+02:00 Richard Hipp :
> Several users have proposed that SQLite adopt a new version numbering
> scheme.  The proposed change is currently uploaded on the "draft"
> website:
>
> https://www.sqlite.org/draft/versionnumbers.html
> https://www.sqlite.org/draft/releaselog/3_9_0.html
> https://www.sqlite.org/draft/
>
> If accepted, the new policy will cause the next release to be 3.9.0
> instead of 3.8.12.  And the second number in the version will be
> increased much more aggressively in future releases.
>
> Your feedback on the proposed policy change is appreciated.  We will
> delay the next release until there is a semblance of consensus on the
> new policy.

Reading the other reactions, there seems to be consensus on
the next release being 3.9.0, not 3.8.12. So I hope the delay
will not be that much. Details on the exact definition of
X/Y/Z is not that important to me, but since you ask

One idea could be to lower the number of 'major' releases
to about twice a year. This means that Linux distributions,
like Ubuntu and Fedora can know in advance which
SQLite release will match their release.
Ubuntu: 
Fedora: 
(everyone seems to think twice a year is optimal, don't know why)

If there is a desire for new features to be released in between,
this could be done by intermediate 9x releases, at will. e.g.:

3.9.0 -  okt 2015
3.9.1 -  nov 2015  (performance improvement/bugfix only)
3.9.90   -  dec 2015  (well-tested, new feature 1 added + bugfixes)
3.9.2 -  jan 2016  (bugfixes only, without feature 1)
3.9.91   -  feb 2016  (well-tested, new feature 2 added + bugfixes)
3.10.0   -  april 2016 (well-tested, contains feature 1 + 2 + more)
3.11.0   -  okt 2016

3.79.0   -  okt 2050

3.99.0   -  okt 2060;-)

Advantage:
1) less 'major' releases gives the signal to managers that apparently
the software is more stable (even though we know that SQLite's
trunk is very stable always).
2) No limitation when/what to release. It can be fully driven by the
desire of SQLite consortium members: Whenever a new feature
is implemented and ready to be released, it can always be done
in an official 3.x.9y release, outside of the half-yearly schedule.
3) No need to adapt the tarball filename.
4) All 3.x.0 and 3.x.9y releases can be done directly from trunk,
as done now. 3.x.[1-9]+ will generally be done from a branch.
Disadvantage:
1) 3.x.9y releases will give the signal to managers being less
stable than 3.x,y releases. We know that's not necessarily
true, but that's the price for advantage 1)

Just my 2c.

Regards,
   Jan Nijtmans

[sqlite] Problem with sqlite3_db_filename

[Graham Holden]

[sqlite] Order with another sequence

[Kevin Benson]

> On Fri, Oct 9, 2015 at 5:08 AM, Cecil Westerhof 
> wrote:
>
> > When I use ORDER BY an ? comes after a z. Is it possible to make an ?
> come
> > after a z?
> >
> > If it is important I am using SQLite 3.8.6 and Python 3.4.1.
>

Perhaps you might garner some insight from looking at other's code?
I did a search on GitHub of APSW (Another Python SQLite wrapper) by Roger
Binns:

https://github.com/rogerbinns/apsw/search?utf8=%E2%9C%93=collation

--
   --
  --
 --???--
K e V i N

[sqlite] Problem with sqlite3_db_filename

[Bart Smissaert]

All working fine now, but noticed that for the attached database I get not
just the file path of the attached database, but also the journal
file path and wal file path:

C:\Test\NewDB2.db3C:\Test\NewDB2.db3-journalC:\Test\NewDB2.db3-wal

There is actually no journal file or wal file that I can see in that folder.

For main I only get the file path of that main database.

Is this behaviour as expected?
Is it documented somewhere?


RBS



On Thu, Oct 8, 2015 at 5:18 PM, Bart Smissaert 
wrote:

> Ignore this as I know what the problem was.
> I was passing a pointer to the Unicode string, but should be pointer to
> 8bit ASCII string.
>
> RBS
>
> On Thu, Oct 8, 2015 at 9:58 AM, Bart Smissaert 
> wrote:
>
>> OK, thanks, at least I know that the function works fine then in
>> sqlite3.dll.
>> Problem must be on my side then.
>>
>> This is the code in the Std_Call dll:
>>
>> SQLITE3_STDCALL_API const char * __stdcall 
>> sqlite3_stdcall_db_filename(sqlite3
>> *pDb, const char *zDbName)
>> {
>> return sqlite3_db_filename(pDb, zDbName);
>> }
>>
>> And this is the Declare in VBA:
>>
>> Public Declare Function sqlite3_stdcall_db_filename Lib "SQLite3_StdCall"
>> Alias "_sqlite3_stdcall_db_filename at 8" (ByVal hDBHandle As Long, ByVal
>> lPtrAttachedDBName As Long) As Long
>>
>> Anything wrong with either of these?
>>
>>
>> RBS
>>
>>
>>
>>
>> On Thu, Oct 8, 2015 at 9:40 AM, Dan Kennedy 
>> wrote:
>>
>>> On 10/08/2015 03:51 AM, Bart Smissaert wrote:
>>>
 As I understand it this should produce a filepointer to the filepath of
 the
 attached database, given the database handle of file the other database
 was
 attached to and the database name of the attached database. I checked
 all
 the return values and also did a select involving tables in both
 databases and all goes fine, so I can be sure that the other database is
 attached OK.
 All I get from sqlite3_db_filename is zero, so no valid file pointer. No
 error messages though.

 I am accessing sqlite3.dll (Windows 7) via a std_call dll as I am
 working
 in VBA here.

 Any suggestions what could be the problem?

 I am running 3.8.11.1


>>> The program below works here.
>>>
>>> I'm seeing full paths for databases "main" and "aux", and a zero-length
>>> nul-terminated string for "next" (the in-memory database).
>>>
>>> Dan
>>>
>>> -
>>>
>>>
>>>
>>> #include 
>>> #include 
>>> #include 
>>>
>>> int main(int argc, char **argv){
>>>   int rc;
>>>   sqlite3 *db;
>>>
>>>   rc = sqlite3_open("test.db", );
>>>   if( rc!=SQLITE_OK ){
>>> fprintf(stderr, "sqlite3_open: %s\n", sqlite3_errmsg(db));
>>> exit(1);
>>>   }
>>>
>>>   rc = sqlite3_exec(db, "ATTACH 'other.db' AS 'aux'", 0, 0, 0);
>>>   if( rc!=SQLITE_OK ){
>>> fprintf(stderr, "sqlite3_exec: %s\n", sqlite3_errmsg(db));
>>> exit(1);
>>>   }
>>>
>>>   rc = sqlite3_exec(db, "ATTACH ':memory:' AS 'next'", 0, 0, 0);
>>>   if( rc!=SQLITE_OK ){
>>> fprintf(stderr, "sqlite3_exec: %s\n", sqlite3_errmsg(db));
>>> exit(1);
>>>   }
>>>
>>>   printf("main  db is: %s\n", sqlite3_db_filename(db, "main"));
>>>   printf("aux   db is: %s\n", sqlite3_db_filename(db, "aux"));
>>>   printf("next  db is: %s\n", sqlite3_db_filename(db, "next"));
>>>
>>>   return 0;
>>> }
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>

[sqlite] Ideas on how to use backup API for remote, incremental backup?

[Richard Hipp]

On 10/9/15, David Barrett  wrote:
> Thanks Richard, this is exactly what I was thinking.  One question on this:
>
> On Tue, Oct 6, 2015 at 10:22 PM, Richard Hipp  wrote:
>
>> (2) For the source database connection of the backup, use the same
>> database connection that is used for writing to the database.  That
>> means that when changes are made to the source database, the backup
>> mechanism can go back and resend only those pages that actually
>> changed.  If the database is modified by any database connection other
>> than the one used for backup, then the backup has no way of knowing
>> which pages changed, and so it has to start over again at the
>> beginning and rewrite every page.
>>
>
> Hm, that's interesting.  I was thinking that when the backup started, all
> new writes would just accumulate in the WAL file -- and then be
> checkpointed after the backup completed.  Thus the backup would be of the
> state of the database at the moment the backup was *started*.  However,
> based on your description it sounds like the backup API will copy over
> changes that have occurred throughout the backup process itself. Our
> database is modified about 40 times per second, so my fear is the backup
> API would never actually complete because it would be continuously
> re-backing up the same pages.  Am I understanding this correctly?  Is there
> any way to obtain the behavior I initially assumed?
>

Yes - run the backup using a separate "source" database connection and
do the entire backup within a read transaction.  Note that having this
long-running read transaction going in a separate database connection
will prevent checkpoint operations from resetting the WAL file, and so
the WAL file will continue to grow until the backup completes.  So you
may want to take steps to ensure that the backup doesn't take *too*
long.


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Order with another sequence

[John McKown]

On Fri, Oct 9, 2015 at 5:08 AM, Cecil Westerhof 
wrote:

> When I use ORDER BY an ? comes after a z. Is it possible to make an ? come
> after a z?
>
> If it is important I am using SQLite 3.8.6 and Python 3.4.1.
>
> --
> Cecil Westerhof
>
>
?I am unsure of how to do this _exactly_ as you want, but I am fairly sure
what you need is the COLLATE phrase in the ORDER BY:

SELECT column_name FROM table
ORDER BY colum_name COLLATE collation_name ASC?

The difficulty is in determining what "collation_name" needs to be. You may
need to create your own. Some pages on the SQLite site which might help:

?http://sqlite.org/lang_select.html
?http://sqlite.org/syntax/ordering-term.html?
https://www.sqlite.org/datatype3.html#collation
https://www.sqlite.org/c3ref/create_collation.html

I don't have the slightest idea if this sort of thing can be used with the
supplied "sqlite3" program. It seems to be designed to be used by C
programs. I've never used SQLite from Python.


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] version 3.9.0 doc errors

[Dominique Pellé]

R.Smith  wrote:

> More draft doc errors:
> https://www.sqlite.org/draft/versionnumbers.html

An additional typo near the end of above page:

=== BEGIN QUOTE ===
Again, the *destinction* (-> distinction) between "major" and "minor"
is subjective.
=== END QUOTE ===

Regards
Dominique

[sqlite] compiling sqlite3 with FTS5 support

[chromedou...@yahoo.com]

I noticed there was talk in the other thread of including FTS5 support in the 
amalgamation. That would be great and it seems like that might solve my 
problem. Is that correct?

Also, I noticed what may be a couple typos in the FTS5 documentation at 
https://www.sqlite.org/fts5.html.
In the external content table section, the example states:
CREATE TABLE fts USING fts5(a, c, content=tbl, content_rowid=d);
I think it should be CREATE VIRTUAL TABLE instead of CREATE TABLE.

Also, in the overview section, the text says:
The expression to the right of the MATCH operator must be the nameof the FTS5 
table. The expression on the left must be a text value specifyingthe term to 
search for.I think the right and left's are backwards.

[sqlite] Proposed new version numbering scheme for SQLite - Feedback requested

[Darren Duncan]

Jan, I see no merit to your proposal and plenty of downsides.  SQLite's current 
release schedule works quite well, there is no good reason to formally do 
feature releases just twice a year, especially with that terrible terrible 9x 
kludge.  There's also no reason to pander to guesses about what Linux 
distribution managers think about project stability, their knowing version 
numbers in advance has no value, and they can be explicitly told or read SQLite 
announcements to know what is stable or not.  In reality, distro managers will 
cut releases on their own schedule, and use whatever's the newest SQLite at the 
time, and SQLite itself should be released on its own schedule.  Also, while 
some projects like 6-month feature releases, that is far from a concensus.  I 
know a bunch that like annual releases, Postgres and Perl for example, which 
work well. -- Darren Duncan

On 2015-10-09 1:51 AM, Jan Nijtmans wrote:
> 2015-10-08 15:38 GMT+02:00 Richard Hipp :
>> Several users have proposed that SQLite adopt a new version numbering
>> scheme.  The proposed change is currently uploaded on the "draft"
>> website:
>>
>>  https://www.sqlite.org/draft/versionnumbers.html
>>  https://www.sqlite.org/draft/releaselog/3_9_0.html
>>  https://www.sqlite.org/draft/
>>
>> If accepted, the new policy will cause the next release to be 3.9.0
>> instead of 3.8.12.  And the second number in the version will be
>> increased much more aggressively in future releases.
>>
>> Your feedback on the proposed policy change is appreciated.  We will
>> delay the next release until there is a semblance of consensus on the
>> new policy.
>
> Reading the other reactions, there seems to be consensus on
> the next release being 3.9.0, not 3.8.12. So I hope the delay
> will not be that much. Details on the exact definition of
> X/Y/Z is not that important to me, but since you ask
>
> One idea could be to lower the number of 'major' releases
> to about twice a year. This means that Linux distributions,
> like Ubuntu and Fedora can know in advance which
> SQLite release will match their release.
>  Ubuntu: 
>  Fedora: 
> (everyone seems to think twice a year is optimal, don't know why)
>
> If there is a desire for new features to be released in between,
> this could be done by intermediate 9x releases, at will. e.g.:
>
>  3.9.0 -  okt 2015
>  3.9.1 -  nov 2015  (performance improvement/bugfix only)
>  3.9.90   -  dec 2015  (well-tested, new feature 1 added + bugfixes)
>  3.9.2 -  jan 2016  (bugfixes only, without feature 1)
>  3.9.91   -  feb 2016  (well-tested, new feature 2 added + bugfixes)
>  3.10.0   -  april 2016 (well-tested, contains feature 1 + 2 + more)
>  3.11.0   -  okt 2016
>  
>  3.79.0   -  okt 2050
>  
>  3.99.0   -  okt 2060;-)
>
> Advantage:
> 1) less 'major' releases gives the signal to managers that apparently
>  the software is more stable (even though we know that SQLite's
>  trunk is very stable always).
> 2) No limitation when/what to release. It can be fully driven by the
>  desire of SQLite consortium members: Whenever a new feature
>  is implemented and ready to be released, it can always be done
>  in an official 3.x.9y release, outside of the half-yearly schedule.
> 3) No need to adapt the tarball filename.
> 4) All 3.x.0 and 3.x.9y releases can be done directly from trunk,
>  as done now. 3.x.[1-9]+ will generally be done from a branch.
> Disadvantage:
> 1) 3.x.9y releases will give the signal to managers being less
>  stable than 3.x,y releases. We know that's not necessarily
>  true, but that's the price for advantage 1)
>
> Just my 2c.
>
> Regards,
> Jan Nijtmans

[sqlite] Can this be done in SQLite?

[R.Smith]

On 2015-10-09 12:55 AM, K. P. wrote:
> Brilliant - thanks.Though I still do not understand my 
> errordistinct(t.LastName || ', ' || t.FirstName),Seems to me that I am 
> passing a single argument in parentheses to distinct

You had a separator (DISTINCT ..   , '; ') as a second argument. I 
removed this and added it to the aggregate name. Hope that clears it up!


>
>> To: sqlite-users at mailinglists.sqlite.org
>> From: rsmith at rsweb.co.za
>> Date: Fri, 9 Oct 2015 00:38:10 +0200
>> Subject: Re: [sqlite] Can this be done in SQLite?
>>
>>
>>
>> On 2015-10-09 12:22 AM, K. P. wrote:
>>> I tried this, of course, before asking, but:
>>> group_concat(distinct(t.LastName || ', ' || t.FirstName), '; ') As Teachers,
>>> gives
>>> [15:19:32] Error while executing SQL query on database 'test': DISTINCT 
>>> aggregates must have exactly one argument
>> As the error suggests, it can't have more than one argument (parameter),
>> so this should work:
>>
>> group_concat(distinct (t.LastName || ', ' || t.FirstName || '; ')) As 
>> Teachers,
>>
>>
>> ___
>> 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] Can this be done in SQLite?

[R.Smith]

On 2015-10-09 12:22 AM, K. P. wrote:
> I tried this, of course, before asking, but:
> group_concat(distinct(t.LastName || ', ' || t.FirstName), '; ') As Teachers,
> gives
> [15:19:32] Error while executing SQL query on database 'test': DISTINCT 
> aggregates must have exactly one argument

As the error suggests, it can't have more than one argument (parameter), 
so this should work:

group_concat(distinct (t.LastName || ', ' || t.FirstName || '; ')) As Teachers,

[sqlite] Can this be done in SQLite?

[R.Smith]

On 2015-10-08 11:47 PM, K. P. wrote:
> Am using SQLiteStudio - which I really like - though I have wondered if it 
> passes all its knowledge around errors onto the user...

I hope it does. Try some other ones too, just to get a wider idea. 
Perhaps http://www.sqlc.rifin.co.za/ or http://www.sqliteexpert.com/ if 
on Windows, or https://github.com/sqlitebrowser/sqlitebrowser if on 
Linux/Mac.  If any of them do things that seem odd, and you are 
comfortable using command line tools, the sqlite3 CLI is best for 
checking/testing SQL at http://www.sqlite.org/download.html

As to your distinct question, sure, use like this:

SELECT group_concat(DISTINCT fullName) FROM sometable WHERE 1;

[sqlite] Can this be done in SQLite?

[R.Smith]

On 2015-10-08 11:30 PM, K. P. wrote:
> It does indeed work - had to make myself a simpler query than the real life 
> one to see this. I probably had some other syntax error that I mistook for 
> it...
> Thanks!

Nice.
Are you logging the SQLite errors and error descriptions? It's usually 
quite good at explaining where exactly your query goes wrong.

[sqlite] Can this be done in SQLite?

[R.Smith]

On 2015-10-08 10:54 PM, K. P. wrote:
> Thanks for that.I'd need something along the lines of
>
> group_concat(c.LastName || ', ' || c.FirstName, ';') as ClientName,
>
>
> which in itself does not seem to be supported.

This would be quite a normal group concatenation and works perfectly well...

Why do you believe it doesn't work or isn't supported?


> c.LastName || ', ' || c.FirstName, ';') as FullName,
> group_concat(FullName, ';') as ClientName,

Ok, this won't work, but not for the reasons you think. An alias cannot 
be referenced in the same select header as it is instantiated. You can 
however achieve this with a CTE (among other ways), something like this 
(I made up A and B here cause I don't know the rest of your schema):

WITH cte1(A, B, FullName) AS (
   SELECT c.A, c.B, c.LastName || ' ' || c.FirstName FROM someTable AS 
cWHERE 1
)
SELECT A, B, group_concat(FullName, '; ')
FROM cte1
  WHERE 1
  GROUP BY A, B
  ORDER BY A, B

Perhaps you don't wish to have names repeated, in which case this will 
work better:

WITH cte1(A, B, FullName) AS (
   SELECT c.A, c.B, c.LastName || ' ' || c.FirstName FROM someTable AS 
cWHERE 1
)
SELECT A, B, group_concat(DISTINCT FullName)
FROM cte1
  WHERE 1
  GROUP BY A, B
  ORDER BY A, B



If you have some SQL that doesn't seem to work, kindly post your table 
schema and the full SQL you are trying to do, that way we can form a 
better idea of what you aim to do and provide more complete answers.

Cheers!
Ryan