Re: [sqlite] WAL mode

[Niti Agarwal]

Yes, I am closing the DB before program exits. Like this: db.Close().
I am using Sqlite3 with Golang.
Regards,
Niti

On Wed, Feb 8, 2017 at 11:08 PM, Jens Alfke  wrote:

>
> > On Feb 8, 2017, at 9:21 AM, Niti Agarwal  wrote:
> >
> > According to the SQLite documentation, the shm and wal files are supposed
> > to be deleted upon completion of the program.
>
> Are you explicitly closing the database before your program exits?
> (In other words, I think saying “upon completion of the program” is
> incorrect; it’s really “upon closing the database”.)
>
> —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] sqlite3_complete

[James K. Lowden]

On Thu, 9 Feb 2017 00:41:34 +0200
R Smith  wrote:

> In the end, accurately judging the effect and validity of a statement 
> without actually running it to completion, is just not useful.

Agreed.  Since you have to test the return code from preparing the
statement anyway, what's the point in testing before testing?  

> some complicated statements can take minutes to simply prepare on a
> large DB

I doubt that.  I've never seen it, and I see no reason it should be
true.  A huge SQL query might be 1000 tokens.  Why should preparing it
take 1 second, let alone minutes?  

--jkl



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

Re: [sqlite] sqlite3_complete

[R Smith]

On 2017/02/08 8:47 PM, x wrote:

Thanks, it certainly looks more useful with that additional info. I sometimes 
have to pass SQL to my own currently unsophisticated parser before I can send 
it to prepare and hoped it would help with that. Given what you’ve told me It 
will help to a degree but when I first saw it I hoped it would check the sql 
keywords without checking the validity of the columns, tables etc.


Yes, that would be nice, but that requires the full-blown parser.
You can achieve the very same by breaking the SQL into single statements 
(perhaps using the sqlite3_complete() function to assist) and then 
simply preparing-and-releasing the single statements one by one (You'd 
have to access SQLite via the C API for this though) and then see if any 
of it errors out or not, and use the error code and description to 
report back on what's wrong with the SQL. This WILL however check the 
validity of columns.


Note that depending on the SQL, this can take more than a few 
milliseconds - some complicated statements can take minutes to simply 
prepare on a large DB.


Also, this method is severely flawed in that if the list of statements 
is a transaction, and tables get created/altered during the transaction, 
subsequent statements involving that table and/or its new columns will 
fail if the SQL that was supposed to create/alter the table never ran to 
completion. It is also flawed in that a statement might succeed 
perfectly to prepare, and only running it to completion reveals the flaw 
- such as a unique constraint failing late in an update run.


A trick some DB management engines use (as did I) is to run the entire 
statement list inside a transaction (if it isn't already in one) and 
then just roll it back at the end in stead of committing. It's a 
horrible waste of time and resources, and might write-lock the DB for 
the duration, but useful if you want to see HOW and IF a script succeeds 
without altering the database. This is typically done during development 
phases and most devs are aware enough to not do that on a terabyte-sized 
database.


In the end, accurately judging the effect and validity of a statement 
without actually running it to completion, is just not useful.




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

Re: [sqlite] Beginning of release testing for version 3.17.0

[Cecil Westerhof]

2017-02-08 16:49 GMT+01:00 Richard Hipp :

> On 2/8/17, Cecil Westerhof  wrote:
> >
> > ​I use SQLite, but only for relative simple things. Is there a way I can
> > help with testing, or do you need a ‘heavy’ application to do that?
> >
>
> All tests are welcomed.
>

​OK, glad to help. What should I do?

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

Re: [sqlite] Help with Backup API please

[Barry]

Ok. My bad for singling out SQLite, I should have rather mentioned that the
problem exists with all memory allocation routines (malloc/free or
new/delete), at least on windows.

A workaround is to pass around allocator / deallocator function pointers
with any data structure which contains pointers which might be free'd or
realloc'd in a different module. My assumption was that SQLite does not do
this, since being Lite is a main priority and if one is using multiple
modules then option to dynamically link to SQLite avoids the problem.

My reason for assuming memory corruption (or invalid call to free(...)) was
the fact that Brett is encountering an exception rather than an invalid
return code.

On 8 February 2017 at 14:16, Richard Hipp  wrote:

> On 2/8/17, Barry Smith  wrote:
> >
> > I believe SQLite doesn't use the standard memory allocation routines, but
> > instead has its own routines.
>
> SQLite has its on memory allocation routines (if you use the right
> compile-time and start-time options) but it uses system
> malloc()/realloc()/free() by default.
>
> There are internal wrappers routines for the memory allocator that
> dispatch requests to the appropriate implementation.  Again, unless
> you take special actions to change it, memory allocation request are
> satisfied by malloc().
>
> --
> 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] sqlite3_complete

[x]

Thanks, it certainly looks more useful with that additional info. I sometimes 
have to pass SQL to my own currently unsophisticated parser before I can send 
it to prepare and hoped it would help with that. Given what you’ve told me It 
will help to a degree but when I first saw it I hoped it would check the sql 
keywords without checking the validity of the columns, tables etc.



Sent from Mail for Windows 10



From: R Smith
Sent: 08 February 2017 17:12
To: 
sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] sqlite3_complete



On 2017/02/08 6:45 PM, x wrote:
> OK, thanks Richard. When I first discovered the function I was hoping it 
> would do more than it said on the tin.
>
Do more?
Like what?

Perhaps Richard's effort to be brief disguised the true power of the
function. If I can put it in a more descriptive manner, this function
checks (and ensures) that, within the passed text:
- All strings are complete strings,
- All comments are complete comments,
- All Quoted Identifiers are complete,
- All of the above can be contained in the statement and the statement
would enter the parser without failing on string-incompleteness,
- The statement is terminated and together with all the above
constitutes a fully parse-able SQL text string.

It seems to me that anything more you wish to check would be something
the SQL engine is better suited at checking - such as syntax, validity,
identifiers etc.

Do you know of something that can be checked more than what the
sqlite3_complete() achieves, but not high-level enough for the SQL
parser to be invoked via a prepare? Or put another way, can you think of
anything else that might make the parser say "This is an invalid sql
text and I cannot parse it" which would slip past the sqlite3_complete()
check?

If you can, it would definitely be worth the effort to add it to the
completeness check, but I would be surprised if something is found.


> Examples:
>
> sqlite3_complete("x;")  -> true
> sqlite3_complete(" 'x; ") -> false
> sqlite3_complete(" 'x'; ") -> true
> sqltie3_complete(" /* x; ") -> false
> sqlite3_complete(" CREATE TRIGGER xyz blah blah; yada yada; ") -> false;
> sqlite3_complete(" CREATE TRIGGER xyz blah blah; END; ") -> true;
> --
>

___
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] WAL mode

[Simon Slavin]

On 8 Feb 2017, at 6:26pm, Jens Alfke  wrote:

> On Feb 8, 2017, at 9:46 AM, Simon Slavin  wrote:
> 
>> Does your program execute sqlite3_shutdown() and check to see whether it 
>> returns an error code ?
> 
> Never noticed that function before … the docs say it’s "designed to aid in 
> process initialization and shutdown on embedded systems. Workstation 
> applications using SQLite normally do not need to invoke either of these 
> routines.”
> 
> Are you suggesting calling this just for troubleshooting why the side files 
> don’t get deleted?

Right.  Troubleshooting only.

One doesn’t usually bother with _shutdown().  If you’ve closed all your SQLite 
files correctly then SQLite doesn’t use many resources, and the ones it does 
use will be correctly released by program termination.  But if closing a file 
isn’t doing what it should do, trying a 'super-close' might do something 
interesting.

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

Re: [sqlite] WAL mode

[Jens Alfke]

> On Feb 8, 2017, at 9:46 AM, Simon Slavin  wrote:
> 
> Does your program execute sqlite3_shutdown() and check to see whether it 
> returns an error code ?

Never noticed that function before … the docs say it’s "designed to aid in 
process initialization and shutdown on embedded systems. Workstation 
applications using SQLite normally do not need to invoke either of these 
routines.”

Are you suggesting calling this just for troubleshooting why the side files 
don’t get deleted?

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

Re: [sqlite] WAL mode

[Simon Slavin]

On 8 Feb 2017, at 5:21pm, Niti Agarwal  wrote:

> The databases I'm working with are write ahead logging (WAL) databases.
> According to the SQLite documentation, the shm and wal files are supposed
> to be deleted upon completion of the program. However, these files are
> still there after execution of this program.

Does your program check whether _close() returns an error code ?

Does your program execute sqlite3_shutdown() and check to see whether it 
returns an error code ?

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

Re: [sqlite] WAL mode

[Jens Alfke]

> On Feb 8, 2017, at 9:21 AM, Niti Agarwal  wrote:
> 
> According to the SQLite documentation, the shm and wal files are supposed
> to be deleted upon completion of the program.

Are you explicitly closing the database before your program exits?
(In other words, I think saying “upon completion of the program” is incorrect; 
it’s really “upon closing the database”.)

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

Re: [sqlite] WAL mode

[R Smith]

On 2017/02/08 7:21 PM, Niti Agarwal wrote:

The databases I'm working with are write ahead logging (WAL) databases.
According to the SQLite documentation, the shm and wal files are supposed
to be deleted upon completion of the program. However, these files are
still there after execution of this program. The documentation says the
files might still be there if the program does not complete cleanly, but
I'm just not seeing that. Any insight?


This might be unrelated to your specific problem and you don't mention 
the OS - but I have seen this happen on combinations of Windows 
virtualisation and/or Anti-Virus holding open the newly appearing files 
for checking so it can't be deleted in a short time-frame. Ensure your 
DB is in a non-system-protective folder (such as my documents) and 
anti-virus is killed, then try again - if it still happens, we need to 
check more.



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

[sqlite] WAL mode

[Niti Agarwal]

The databases I'm working with are write ahead logging (WAL) databases.
According to the SQLite documentation, the shm and wal files are supposed
to be deleted upon completion of the program. However, these files are
still there after execution of this program. The documentation says the
files might still be there if the program does not complete cleanly, but
I'm just not seeing that. Any insight?
Thanks,
Niti
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with Backup API please

[Richard Hipp]

On 2/8/17, Barry Smith  wrote:
>
> I believe SQLite doesn't use the standard memory allocation routines, but
> instead has its own routines.

SQLite has its on memory allocation routines (if you use the right
compile-time and start-time options) but it uses system
malloc()/realloc()/free() by default.

There are internal wrappers routines for the memory allocator that
dispatch requests to the appropriate implementation.  Again, unless
you take special actions to change it, memory allocation request are
satisfied by malloc().

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

Re: [sqlite] Help with Backup API please

[Barry Smith]

Hi Brett,

I believe SQLite doesn't use the standard memory allocation routines, but 
instead has its own routines. These (might) use global variables. If each 
module of your application statically links to the SQLite source rather than 
having SQLite in a common DLL, then each module will have its own set of global 
variables. As such, passing raw SQLite pointers across module boundaries can 
result in undefined behaviour. I suspect other issues could also occur with 
other global variables in SQLite.

Note that passing the raw pointer is not the real culprit, but rather having 
different modules attempt to work on the pointer. For example:, the following 
should work fine:
sqlite3* pDb = DLL1GetDb();
DLL1DoSomething(pDb);

But either of the following could fail:
sqlite3* pDb = DLL1GetDb();
DLL2DoSomething(pDb);
// or, if the calling module is not DLL1
sqlite3_exec(pDb,...);

Personally, to enforce this I do not expose (either in arguments or return 
values) raw sqlite objects (dbs, statements, or anything else) in exported 
functions.

If you already have SQLite in a common DLL, or you perform all sqlite3_* calls 
from the same module, I apologise for wasting your time.

Also, SQLite is written in C so it uses error return codes rather than throwing 
exceptions. The exception you are catching would have to be thrown by another 
library that SQLite is calling. This would mean that the output of 
sqlite3_errmsg is likely unrelated to the exception since SQLite would never 
have intercepted it. Perhaps your debugging environment could tell you more 
details of the real proximate cause of the error (unless the you mean that the 
error message of the exception, by coincidence, was the same as a standard 
SQLite error message)

Cheers,

 - Barry 

> On 8 Feb 2017, at 9:30 AM, Brett Goodman  wrote:
> 
> Hello Sqlite users.  I have a problem I can't solve.  I have a C++ DLL 
> project in which I compile the Sqlite amalgamation code. The purpose is to 
> wrap the key Sqlite functions with exported functions I can call from another 
> C++ COM DLL.   I'm using VS2003 for this because its part of a legacy 
> product.  I've been using this project for years, updating the Sqlite source 
> code from time to time with no problems.  I can call functions like 
> sqlite3_open_v2, sqlite3_prepare, sqlite3_step, etc. with no problems.  Now 
> I'm trying to integrate the Backup API.  When I call sqlite3_backup_init it 
> throws this error: _/"library /__/
> /__/routine called out of sequence"/_.  I want to step into the Sqlite code 
> to find out why but for some reason my debugger won't allow me to step into 
> the C code from my C++ code.  So I'm hoping someone can help me.  Below is my 
> C++ implementation which I modeled from the sample on this page: 
> sqlite.org/backup.html.  My calling code is from a VB6 project which simply 
> creates/opens a memory DB and calls the below function (via my COM DLL).  Any 
> help is much appreciated.
> 
> Thanks,
> BrettG
> 
> 
> SQLITE_DLL_API int backup( /*sqlite3*/ void* pdb, const WCHAR *pFilename, 
> BOOL toFile )
> {
>  int rc;
>  sqlite3 *pFileDb;
>  sqlite3_backup *pBackup;
>  sqlite3 *pTo;
>  sqlite3 *pFrom;
> 
>  CTextConverter c(CP_UTF8);
>  //rc = sqlite3_open_v2( c.U2M(pFilename), , SQLITE_OPEN_READWRITE, 
> NULL);
>  rc = sqlite3_open( c.U2M(pFilename), );
> 
>  if( rc==SQLITE_OK )
>  {
>try
>{
>sqlite3 *pMemoryDb = (sqlite3*) pdb;
>pFrom = (toFile ? pMemoryDb : pFileDb);
>pTo   = (toFile ? pFileDb : pMemoryDb);
> 
>// this line throws exception:  "library routine called out of 
> sequence"
> *pBackup = sqlite3_backup_init(pTo, "main", pFrom, "temp");*
>if( pBackup )
>{
>sqlite3_backup_step(pBackup, -1);
>sqlite3_backup_finish(pBackup);
>}
>rc = sqlite3_errcode(pTo);
>sqlite3_close(pFileDb);
>}
>catch(...)
>{
>sqlite3_close(pFileDb);
>const char* err = sqlite3_errmsg( (sqlite3*) pdb );
>throw err;
>}
>  }
>  return rc;
> }
> 
> ___
> 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] sqlite3_complete

[R Smith]

On 2017/02/08 6:45 PM, x wrote:

OK, thanks Richard. When I first discovered the function I was hoping it would 
do more than it said on the tin.


Do more?
Like what?

Perhaps Richard's effort to be brief disguised the true power of the 
function. If I can put it in a more descriptive manner, this function 
checks (and ensures) that, within the passed text:

- All strings are complete strings,
- All comments are complete comments,
- All Quoted Identifiers are complete,
- All of the above can be contained in the statement and the statement 
would enter the parser without failing on string-incompleteness,
- The statement is terminated and together with all the above 
constitutes a fully parse-able SQL text string.


It seems to me that anything more you wish to check would be something 
the SQL engine is better suited at checking - such as syntax, validity, 
identifiers etc.


Do you know of something that can be checked more than what the 
sqlite3_complete() achieves, but not high-level enough for the SQL 
parser to be invoked via a prepare? Or put another way, can you think of 
anything else that might make the parser say "This is an invalid sql 
text and I cannot parse it" which would slip past the sqlite3_complete() 
check?


If you can, it would definitely be worth the effort to add it to the 
completeness check, but I would be surprised if something is found.




Examples:

sqlite3_complete("x;")  -> true
sqlite3_complete(" 'x; ") -> false
sqlite3_complete(" 'x'; ") -> true
sqltie3_complete(" /* x; ") -> false
sqlite3_complete(" CREATE TRIGGER xyz blah blah; yada yada; ") -> false;
sqlite3_complete(" CREATE TRIGGER xyz blah blah; END; ") -> true;
--



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

Re: [sqlite] Virtual table vs real table query performance

[Dimitris Bil]

I see, so in the native implementation you already have the whole table in 
memory and only use the clustered b-tree index to search for tuples. So I would 
not  expect a large improvement from the virtual table implementation, but the 
virtual table being 5 times slower is strange. Maybe not the correct data 
structure used?


By the way, I had tried adding a virtual table in the sqlite amalgamation and I 
did not see observable difference. On the other hand, I have seen improvement 
in the virtual table utilization using the latest version of sqlite (in 
comparison to a release about a year ago).



From: sqlite-users  on behalf of 
Bob Friesenhahn 
Sent: Wednesday, February 8, 2017 4:09 PM
To: SQLite mailing list
Subject: Re: [sqlite] Virtual table vs real table query performance

On Wed, 8 Feb 2017, Dimitris Bil wrote:

> Do you perform the benchmark on the native database table using cold
> cache or warm cache? Also, can you briefly describe what the
> benchmark does and give the schema for the native database table?

My benchmark repeatedly reads all of the columns one by one given row
id and column name.  The table is read many (e.g. 100) times so this
is a warm cache test.

The schema is not terribly important but the table we are trying to
optimize (with 1800 or less rows) contains a 64-bit rowid, five
integer values, and two short text string values.

   int64, uint32, uint32, text[16], uint8, text[16], text[18], uint8,
   uint32

What I am looking for is expected average virtual table performance vs
native table performance for repeated column reads.

Due to being a generic implementation (supporting many virtual
tables), our virtual implementation uses programmed/dynamic
marshalling rather that compiled marshalling.  The schema definition
is also dynamically generated.

There are implementation overheads and it is useful to know what
performance is possible (e.g. compared to native table performance)
in order to know when the implementation is about as good as it can
be.

Bob
--

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

Re: [sqlite] sqlite3_complete

[x]

OK, thanks Richard. When I first discovered the function I was hoping it would 
do more than it said on the tin.



Sent from Mail for Windows 10



From: Richard Hipp
Sent: 08 February 2017 16:36
To: SQLite mailing list
Subject: Re: [sqlite] sqlite3_complete



On 2/8/17, Richard Hipp  wrote:
> On 2/8/17, x  wrote:
>>
>> It checks the input is appended with a semi-colon? Surely I’m missing
>> something?
>>
>
> It also verifies that the semicolon at the end is not part of a
> string, or comment, nor in the middle of a CREATE TRIGGER statement.

Examples:

   sqlite3_complete("x;")  -> true
   sqlite3_complete(" 'x; ") -> false
   sqlite3_complete(" 'x'; ") -> true
   sqltie3_complete(" /* x; ") -> false
   sqlite3_complete(" CREATE TRIGGER xyz blah blah; yada yada; ") -> false;
   sqlite3_complete(" CREATE TRIGGER xyz blah blah; END; ") -> true;
--
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] sqlite3_complete

[Richard Hipp]

On 2/8/17, Richard Hipp  wrote:
> On 2/8/17, x  wrote:
>>
>> It checks the input is appended with a semi-colon? Surely I’m missing
>> something?
>>
>
> It also verifies that the semicolon at the end is not part of a
> string, or comment, nor in the middle of a CREATE TRIGGER statement.

Examples:

   sqlite3_complete("x;")  -> true
   sqlite3_complete(" 'x; ") -> false
   sqlite3_complete(" 'x'; ") -> true
   sqltie3_complete(" /* x; ") -> false
   sqlite3_complete(" CREATE TRIGGER xyz blah blah; yada yada; ") -> false;
   sqlite3_complete(" CREATE TRIGGER xyz blah blah; END; ") -> true;
-- 
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

Re: [sqlite] Bulk Insert in Sqlite3

[Warren Young]

On Feb 6, 2017, at 10:36 PM, Niti Agarwal  wrote:

> I read about SQLITE_MAX_SQL_LENGTH,

If this is why you’re making many transactions, there’s no requirement that all 
of the SQL that’s part of a single transaction be in a single SQL string given 
to the DB.  You can execute a bare “BEGIN TRANSACTION” SQL statement, do your 
10 million separate insert statements, and then execute a COMMIT statement, at 
which time all of the rows will be visible to other users of that DB.

This is simpler, still fast, and doesn’t require that you do all that manual 
batching-up, or worry about internal SQLite buffer sizes.

> Also, read about sql bind feature, but not very sure how to do in Golang?

There are a bunch of SQLite wrappers for Go:

   http://go-lang.cat-v.org/library-bindings

Which one are you using?

The first one on that list appears to be MIA, but the second one includes this 
module, which includes a Bind() function:

   https://github.com/kuroneko/gosqlite3/blob/master/query_parameter.go

Seems straightforward to me.

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

Re: [sqlite] sqlite3_complete

[Richard Hipp]

On 2/8/17, x  wrote:
>
> It checks the input is appended with a semi-colon? Surely I’m missing
> something?
>

It also verifies that the semicolon at the end is not part of a
string, or comment, nor in the middle of a CREATE TRIGGER statement.

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

[x]

Determine If An SQL Statement Is Complete
int sqlite3_complete(const char *sql);
int sqlite3_complete16(const void *sql);
These routines are useful during command-line input to determine if the 
currently entered text seems to form a complete SQL statement or if additional 
input is needed before sending the text into SQLite for parsing. These routines 
return 1 if the input string appears to be a complete SQL statement. A 
statement is judged to be complete if it ends with a semicolon token and is not 
a prefix of a well-formed CREATE TRIGGER statement.

sqlite3_complete(“x”) returns false but sqlite3_complete(“x;”) doesn’t because 
it has a semi-colon at the end.

It checks the input is appended with a semi-colon? Surely I’m missing something?


Sent from Mail for Windows 10

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

Re: [sqlite] Virtual table vs real table query performance

[Dominique Devienne]

On Wed, Feb 8, 2017 at 4:50 PM, Hick Gunter  wrote:

> This is with a generic, user definable table and index structure capable
> virtual table implementation; it is not a "one module per table" statically
> typed heavily optimized implementation.
>

Ah, that makes complete sense then. I didn't want the OP to think virtual
tables were slower
than native tables in the general case, especially since he mentioned
memory arrays in C code.

And indeed the virtual-table advantage I mentioned is with a different
statically-typed vtable impl/module per vtable,
with statically defined indexes, where the table structure is hard-coded in
the vtable impl itself, and corresponds
to a native "row" data structure. In that config one leverages the
"front-end" of SQLite (parser and VDBE engine)
and very little of the "back-end" (pager and btree), except when SQLite
decides to make temporary tables for
query processing I guess. FWIW. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual table vs real table query performance

[Bob Friesenhahn]

On Wed, 8 Feb 2017, Dimitris Bil wrote:

Do you perform the benchmark on the native database table using cold 
cache or warm cache? Also, can you briefly describe what the 
benchmark does and give the schema for the native database table?


My benchmark repeatedly reads all of the columns one by one given row 
id and column name.  The table is read many (e.g. 100) times so this 
is a warm cache test.


The schema is not terribly important but the table we are trying to 
optimize (with 1800 or less rows) contains a 64-bit rowid, five 
integer values, and two short text string values.


  int64, uint32, uint32, text[16], uint8, text[16], text[18], uint8,
  uint32

What I am looking for is expected average virtual table performance vs 
native table performance for repeated column reads.


Due to being a generic implementation (supporting many virtual 
tables), our virtual implementation uses programmed/dynamic 
marshalling rather that compiled marshalling.  The schema definition 
is also dynamically generated.


There are implementation overheads and it is useful to know what 
performance is possible (e.g. compared to native table performance) 
in order to know when the implementation is about as good as it can 
be.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual table vs real table query performance

[Hick Gunter]

This is with a generic, user definable table and index structure capable 
virtual table implementation; it is not a "one module per table" statically 
typed heavily optimized implementation.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Mittwoch, 08. Februar 2017 16:42
An: SQLite mailing list 
Betreff: Re: [sqlite] Virtual table vs real table query performance

On Wed, Feb 8, 2017 at 4:30 PM, Hick Gunter  wrote:

> Values are for retrieving 100.000 rows with a where clause not
> satisfiable from the index but true for alle rows
>
> asql> select count() from ;
> CPU Time: user 0.092986 sys 0.00
>
> asql> select count() from  where =4;
> CPU Time: user 0.189971 sys 0.00
> CPU Time: user 0.199969 sys 0.00
> CPU Time: user 0.199970 sys 0.00
>
> asql> select count() from  where =4;
> CPU Time: user 0.086987 sys 0.010998
> CPU Time: user 0.085987 sys 0.00
> CPU Time: user 0.076988 sys 0.002000
>


Frankly I'm surprised it's slower than "native" SQLite.

In bulk-insert, random lookup, and table-delete timings we did in 2009 between 
native in-memory SQLite, and pure-C++ virtual tables accessing pure C++ data 
structures (i.e. vm/reflection/introspection/dynamic lookup as in Python for 
example, but direct addressing of statically typed data), the virtual tables 
was always faster, and not by a small margin.

Admittedly it was a long time ago, and SQLite is getting faster all the time 
for sure, but you can't beat static typing of memory addressable structures, vs 
scanning pages of table data and dynamically/serially decoding variable sizes 
rows within those pages.

So something like "non-native" code or something "dynamic" is hiding in the 
virtual table impl, no? --DD ___
sqlite-users mailing list
sqlite-users@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: h...@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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Beginning of release testing for version 3.17.0

[Richard Hipp]

On 2/8/17, Cecil Westerhof  wrote:
>
> ​I use SQLite, but only for relative simple things. Is there a way I can
> help with testing, or do you need a ‘heavy’ application to do that?
>

All tests are welcomed.

The extensive SQLite test suite probably already covers most of the
"simple things".  But you never know what might have been missed.

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

Re: [sqlite] Virtual table vs real table query performance

[Dominique Devienne]

On Wed, Feb 8, 2017 at 4:30 PM, Hick Gunter  wrote:

> Values are for retrieving 100.000 rows with a where clause not satisfiable
> from the index but true for alle rows
>
> asql> select count() from ;
> CPU Time: user 0.092986 sys 0.00
>
> asql> select count() from  where =4;
> CPU Time: user 0.189971 sys 0.00
> CPU Time: user 0.199969 sys 0.00
> CPU Time: user 0.199970 sys 0.00
>
> asql> select count() from  where =4;
> CPU Time: user 0.086987 sys 0.010998
> CPU Time: user 0.085987 sys 0.00
> CPU Time: user 0.076988 sys 0.002000
>


Frankly I'm surprised it's slower than "native" SQLite.

In bulk-insert, random lookup, and table-delete timings we did in 2009
between native in-memory SQLite, and pure-C++ virtual tables accessing
pure C++ data structures (i.e. vm/reflection/introspection/dynamic lookup
as in Python for example, but direct addressing of statically typed data),
the virtual tables was always faster, and not by a small margin.

Admittedly it was a long time ago, and SQLite is getting faster all the time
for sure, but you can't beat static typing of memory addressable structures,
vs scanning pages of table data and dynamically/serially decoding variable
sizes
rows within those pages.

So something like "non-native" code or something "dynamic" is hiding in the
virtual table impl, no? --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual table vs real table query performance

[Hick Gunter]

Values are for retrieving 100.000 rows with a where clause not satisfiable from 
the index but true for alle rows

asql> select count() from ;
CPU Time: user 0.092986 sys 0.00

asql> select count() from  where =4;
CPU Time: user 0.189971 sys 0.00
CPU Time: user 0.199969 sys 0.00
CPU Time: user 0.199970 sys 0.00

asql> select count() from  where =4;
CPU Time: user 0.086987 sys 0.010998
CPU Time: user 0.085987 sys 0.00
CPU Time: user 0.076988 sys 0.002000

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Bob Friesenhahn
Gesendet: Mittwoch, 08. Februar 2017 15:39
An: SQLite mailing list 
Betreff: Re: [sqlite] Virtual table vs real table query performance

On Wed, 8 Feb 2017, Hick Gunter wrote:

> Having imlemented a memory-based virtual table complete with indices,
> full table scan and direct access via rowid (which happens to be the
> memory address of the row) I can do a batch delete of
> 100.000 rows (in a table with 1 composite index) in about 2 seconds

The case I am interested is pure read performance of a single column element at 
a time given properly implemented xBestIndex and xFilter support.  Rows are not 
being added/removed using sqlite.

It is possible that native tables can be faster since the implementation is not 
limited to the rigid set of callback functions provided for virtual tables to 
use and of course the amalgamation is optimized by the compiler as one source 
module.

By tracing the callbacks, we do see that our implementation is not invoking the 
callbacks more times than necessary (which was not the case before xBestIndex 
and xFilter support was added).  Due to the requirements of the implementation, 
POSIX reader/writer locks are used so there is some low-contention locking 
overhead.

Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@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: h...@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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

[Clyde Eisenbeis]

I have a solution, thanks to Alex Smith.  This works:

sqliteCmd.CommandText = "INSERT INTO "

System.Data.SQLite.SQLiteTransaction sqliteTran =
sqliteConnection.BeginTransaction();
sqliteCmd.ExecuteNonQuery();
long lKeyID = sqliteConnection.LastInsertRowId;
sqliteTran.Commit();
stKeyID = lKeyID.ToString();

This does not require SQLite3.  Thanks to all on the SQLite Mailing List!

Clyde


On Tue, Feb 7, 2017 at 6:17 PM, Kevin Benson  wrote:
> On Tue, Feb 7, 2017 at 4:11 PM, Clyde Eisenbeis  wrote:
>
>> int iKeyID = (int)sqliteCmd.ExecuteScalar();
>>
>
> I believe the type of last_insert_rowid() is *always* INT64
>
> --
>--
>   --
>  --Ö¿Ö--
> K e V i N
> ___
> 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] Virtual table vs real table query performance

[Dimitris Bil]

Hello,


Do you perform the benchmark on the native database table using cold cache or 
warm cache? Also, can you briefly describe what the benchmark does and give the 
schema for the native database table?


thanks



From: sqlite-users  on behalf of 
Bob Friesenhahn 
Sent: Wednesday, February 8, 2017 2:39 PM
To: SQLite mailing list
Subject: Re: [sqlite] Virtual table vs real table query performance



The case I am interested is pure read performance of a single column
element at a time given properly implemented xBestIndex and xFilter
support.  Rows are not being added/removed using sqlite.

It is possible that native tables can be faster since the
implementation is not limited to the rigid set of callback functions
provided for virtual tables to use and of course the amalgamation is
optimized by the compiler as one source module.

By tracing the callbacks, we do see that our implementation is not
invoking the callbacks more times than necessary (which was not the
case before xBestIndex and xFilter support was added).  Due to the
requirements of the implementation, POSIX reader/writer locks are used
so there is some low-contention locking overhead.

Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
GraphicsMagick Image Processing System
www.graphicsmagick.org
GraphicsMagick is a robust collection of tools and libraries to read, write, 
and manipulate an image in any of the more popular image formats including GIF, 
JPEG, PNG ...



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

Re: [sqlite] Beginning of release testing for version 3.17.0

[Cecil Westerhof]

2017-02-07 15:32 GMT+01:00 Richard Hipp :

> In a few days, we will begin the official release testing for SQLite
> version 3.17.0.
>
> A recent snapshot of the code can be found on the
> https://www.sqlite.org/download.html page or directly from Fossil at
> https://www.sqlite.org/src/timeline
>
> A draft change log can be seen at
> https://www.sqlite.org/draft/releaselog/3_17_0.html
>
> Please try the new code, as you are able, and report any issues to
> this mailing list or directly to me at the email shown below.
>

​I use SQLite, but only for relative simple things. Is there a way I can
help with testing, or do you need a ‘heavy’ application to do that?

I have installed openSUSE, Debian and Ubuntu. I could also install another
OS in a VirtualBox if useful.

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

Re: [sqlite] Beginning of release testing for version 3.17.0

[jose isaias cabrera]

Just thinking out loud for those of us that use the DLL provided in your
site... 

It would be nice that this email would be accompanied by ready made DLLs
also.  Perhaps under, 

https://www.sqlite.org/draft/ [1]download.html [2] 

This is because some of us may not have the time to create the DLLs, but
we can download and drop the DLL in the folder and run our applications
and do some test.  Actually even run the application under the test DLL.
 I don't expect a daily creation of DLLs, but at least the original
"Please test" and the "Beginning of release..." emails.  I know this may
be extra work, but the testing will have broader exposure. 

Thanks for the wonderful tool... 

On 2017-02-07 09:32, Richard Hipp wrote:

> In a few days, we will begin the official release testing for SQLite
> version 3.17.0.
> 
> A recent snapshot of the code can be found on the
> https://www.sqlite.org/download.html page or directly from Fossil at
> https://www.sqlite.org/src/timeline
> 
> A draft change log can be seen at
> https://www.sqlite.org/draft/releaselog/3_17_0.html
> 
> Please try the new code, as you are able, and report any issues to
> this mailing list or directly to me at the email shown below.
 

Links:
--
[1] https://www.sqlite.org/draft/releaselog/3_17_0.html
[2] https://www.sqlite.org/download.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual table vs real table query performance

[Bob Friesenhahn]

On Wed, 8 Feb 2017, Hick Gunter wrote:

Having imlemented a memory-based virtual table complete with 
indices, full table scan and direct access via rowid (which happens 
to be the memory address of the row) I can do a batch delete of 
100.000 rows (in a table with 1 composite index) in about 2 seconds


The case I am interested is pure read performance of a single column 
element at a time given properly implemented xBestIndex and xFilter 
support.  Rows are not being added/removed using sqlite.


It is possible that native tables can be faster since the 
implementation is not limited to the rigid set of callback functions 
provided for virtual tables to use and of course the amalgamation is 
optimized by the compiler as one source module.


By tracing the callbacks, we do see that our implementation is not 
invoking the callbacks more times than necessary (which was not the 
case before xBestIndex and xFilter support was added).  Due to the 
requirements of the implementation, POSIX reader/writer locks are used 
so there is some low-contention locking overhead.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with Backup API please

[Clemens Ladisch]

Brett Goodman wrote:
> When I call sqlite3_backup_init it throws this error: _/"library /__/
> /__/routine called out of sequence"/_.

To you get an error code, or an exception?
In the first case, try calling sqlite3_errmsg().

The documentation says:
| A call to sqlite3_backup_init() will fail, returning NULL, if there is
| already a read or read-write transaction open on the destination
| database.


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

[sqlite] Help with Backup API please

[Brett Goodman]

Hello Sqlite users.  I have a problem I can't solve.  I have a C++ DLL 
project in which I compile the Sqlite amalgamation code. The purpose is 
to wrap the key Sqlite functions with exported functions I can call from 
another C++ COM DLL.   I'm using VS2003 for this because its part of a 
legacy product.  I've been using this project for years, updating the 
Sqlite source code from time to time with no problems.  I can call 
functions like sqlite3_open_v2, sqlite3_prepare, sqlite3_step, etc. with 
no problems.  Now I'm trying to integrate the Backup API.  When I call 
sqlite3_backup_init it throws this error: _/"library /__/
/__/routine called out of sequence"/_.  I want to step into the Sqlite 
code to find out why but for some reason my debugger won't allow me to 
step into the C code from my C++ code.  So I'm hoping someone can help 
me.  Below is my C++ implementation which I modeled from the sample on 
this page: sqlite.org/backup.html.  My calling code is from a VB6 
project which simply creates/opens a memory DB and calls the below 
function (via my COM DLL).  Any help is much appreciated.


Thanks,
BrettG


SQLITE_DLL_API int backup( /*sqlite3*/ void* pdb, const WCHAR 
*pFilename, BOOL toFile )

{
  int rc;
  sqlite3 *pFileDb;
  sqlite3_backup *pBackup;
  sqlite3 *pTo;
  sqlite3 *pFrom;

  CTextConverter c(CP_UTF8);
  //rc = sqlite3_open_v2( c.U2M(pFilename), , 
SQLITE_OPEN_READWRITE, NULL);

  rc = sqlite3_open( c.U2M(pFilename), );

  if( rc==SQLITE_OK )
  {
try
{
sqlite3 *pMemoryDb = (sqlite3*) pdb;
pFrom = (toFile ? pMemoryDb : pFileDb);
pTo   = (toFile ? pFileDb : pMemoryDb);

// this line throws exception:  "library routine called out of 
sequence"

*pBackup = sqlite3_backup_init(pTo, "main", pFrom, "temp");*
if( pBackup )
{
sqlite3_backup_step(pBackup, -1);
sqlite3_backup_finish(pBackup);
}
rc = sqlite3_errcode(pTo);
sqlite3_close(pFileDb);
}
catch(...)
{
sqlite3_close(pFileDb);
const char* err = sqlite3_errmsg( (sqlite3*) pdb );
throw err;
}
  }
  return rc;
}

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

Re: [sqlite] SQLITE_ENABLE_UPDATE_DELETE_LIMIT (Was: Patch Etiquette)

[Jan Nijtmans]

2017-02-06 23:25 GMT+01:00 Ziemowit Laski:
> Here is my approach to the SQLITE_ENABLE_UPDATE_DELETE_LIMIT problem.  [If
> the attachment does not arrive intact, please let me know.]

Hi Ziemowit,

I tried your patch, and it works fine! Below is an additional patch, which makes
the resulting amalgamation much smaller (from 202734 to 202415 lines, while
the original amalgamation was 201287 lines). The reason for this reduction is
that your amalgamation contains 321 segments which look like:
#ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
#line 3288 "parse.c"
#else /* SQLITE_ENABLE_UPDATE_DELETE_LIMIT */
#line 3290 "parse.c"
#endif /* SQLITE_ENABLE_UPDATE_DELETE_LIMIT */
This is not really useful, since the only difference is the line
number. If both "parse.c"
versions contain the same line numbering, that helps in the diff size.

Thank you for your idea!

Regards,
Jan nijtmans


Index: src/parse.y
==
--- src/parse.y
+++ src/parse.y
@@ -748,10 +748,11 @@
 %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
 cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
   sqlite3WithPush(pParse, C, 1);
   sqlite3SrcListIndexedBy(pParse, X, );
   sqlite3DeleteFrom(pParse,X,W);
+  /* for alignment */
 }
 %endif

 %type where_opt {Expr*}
 %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);}
@@ -776,10 +777,11 @@
 where_opt(W).  {
   sqlite3WithPush(pParse, C, 1);
   sqlite3SrcListIndexedBy(pParse, X, );
   sqlite3ExprListCheckLength(pParse,Y,"set list");
   sqlite3Update(pParse,X,Y,W,R);
+  /* for alignment */
 }
 %endif

 %type setlist {ExprList*}
 %destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New pre-release snapshot with performance enhancements

[Jake Thaw]

Hi Dan,

I can confirm that the current snapshot works for me as expected. I was
linking against sqlite-snapshot-201701170010, which predates the session
enhancement.

Thank you for your time.

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

Re: [sqlite] Virtual table vs real table query performance

[Hick Gunter]

Having imlemented a memory-based virtual table complete with indices, full 
table scan and direct access via rowid (which happens to be the memory address 
of the row) I can do a batch delete of 100.000 rows (in a table with 1 
composite index) in about 2 seconds (3.7 seconds with the condition) while 
running linux (RH 5.6 x86_64 VM) on a virtual machine. Deleting all rows of a 
native SQLite table (while checking for the value of a non-indexed field to 
avoid SQLite just dropping an re-creating the table) takes about 1 second.



Note that both operations require a full table scan to fill a „rowset“ (= 
SQLite internal temporary table) and that the virtual table function VUpdate 
expects the virtual table code to handle index deletetion which is explicitly 
coded in the native table case.



asql> explain delete from ;

addr  opcode p1p2p3p4 p5  comment

  -        -  --  -

0 Trace  0 0 000  NULL

1 Goto   0 18000  NULL

2 Integer0 1 000  NULL

3 Null   0 2 000  NULL

4 VOpen  0 0 0 vtab:187BE588:2ACC1FDC4990  00  NULL

5 Integer1 4 000  NULL

6 Integer0 5 000  NULL

7 VFilter0 12400  NULL

8 Rowid  0 3 000  NULL

9 RowSetAdd  2 3 000  NULL

10AddImm 1 1 000  NULL

11VNext  0 8 000  NULL

12Close  0 0 000  NULL

13RowSetRead 2 16300  NULL

14VUpdate0 1 3 vtab:187BE588:2ACC1FDC4990  02  NULL

15Goto   0 13000  NULL

16ResultRow  1 1 000  NULL

17Halt   0 0 000  NULL

18VBegin 0 0 0 vtab:187BE588:2ACC1FDC4990  00  NULL

19Goto   0 2 000  NULL



asql> explain delete from  where =4;

addr  opcode p1p2p3p4 p5  comment

  -        -  --  -

0 Trace  0 0 000  NULL

1 Goto   0 31000  NULL

2 Integer0 1 000  NULL

3 Null   0 2 000  NULL

4 OpenRead   0 215   0 7  00  

5 Rewind 0 13000  NULL

6 Column 0 6 400  .

7 Integer4 5 000  NULL

8 Ne 5 124   collseq(BINARY)  6c  NULL

9 Rowid  0 3 000  NULL

10RowSetAdd  2 3 000  NULL

11AddImm 1 1 000  NULL

12Next   0 6 001  NULL

13Close  0 0 000  NULL

14OpenWrite  0 215   0 8  00  

15OpenWrite  1 1362  0  Keyinfo(5,BINARY,BINARY)  00  

16RowSetRead 2 27300  NULL

17NotExists  0 26300  NULL

18Rowid  0 11000  NULL

19Column 0 1 600  .

20Column 0 2 700  .

21Column 0 3 800  .

22Column 0 4 900  .

23Column 0 5 10   00  .

24IdxDelete  1 6 600  NULL

25Delete 0 1 000  NULL

26Goto   0 16000  NULL

27Close  1 1362  000  NULL

28Close  0 0 000  NULL

29ResultRow  1 1 000  NULL

30Halt   0 0 000  NULL

31Transaction0 1 000  NULL

32VerifyCookie   0 1191  000  NULL

33TableLock  0 215   100  NULL

34Goto   0 2 000  NULL



-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Bob Friesenhahn
Gesendet: Dienstag, 07. Februar 2017 22:06
An: SQLite mailing list 
Betreff: [sqlite] Virtual table vs real table query performance



We are 

Re: [sqlite] SQLITE_ENABLE_UPDATE_DELETE_LIMIT (Was: Patch Etiquette)

[Ziemowit Laski]

Hello,

I checked the message archive and it appears that the attachment got stripped 
from my previous message.  So I'm plastering it inline below.

Thank you,

--Zem


diff -C5 sqlitesrc2/sqlite-src-3160200/main.mk 
sqlitesrc3/sqlite-src-3160200/main.mk
*** sqlitesrc2/sqlite-src-3160200/main.mk   2017-01-06 08:52:10.0 
-0800
--- sqlitesrc3/sqlite-src-3160200/main.mk   2017-02-06 14:04:14.725992100 
-0800
***
*** 626,642 
  opcodes.h:parse.h $(TOP)/src/vdbe.c $(TOP)/tool/mkopcodeh.tcl
cat parse.h $(TOP)/src/vdbe.c | \
tclsh $(TOP)/tool/mkopcodeh.tcl >opcodes.h

  # Rules to build parse.c and parse.h - the outputs of lemon.
  #
  parse.h:  parse.c

  parse.c:  $(TOP)/src/parse.y lemon $(TOP)/tool/addopcodes.tcl
cp $(TOP)/src/parse.y .
rm -f parse.h
!   ./lemon -s $(OPTS) parse.y
mv parse.h parse.h.temp
tclsh $(TOP)/tool/addopcodes.tcl parse.h.temp >parse.h

  sqlite3.h:$(TOP)/src/sqlite.h.in $(TOP)/manifest.uuid $(TOP)/VERSION 
$(TOP)/ext/rtree/sqlite3rtree.h
tclsh $(TOP)/tool/mksqlite3h.tcl $(TOP) >sqlite3.h
--- 626,654 
  opcodes.h:parse.h $(TOP)/src/vdbe.c $(TOP)/tool/mkopcodeh.tcl
cat parse.h $(TOP)/src/vdbe.c | \
tclsh $(TOP)/tool/mkopcodeh.tcl >opcodes.h

  # Rules to build parse.c and parse.h - the outputs of lemon.
+ # NB: To build parse.c, we run lemon twice - once with
+ # -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT and once without -
+ # and then diff the results.  This way, SQLITE_ENABLE_UPDATE_DELETE_LIMIT
+ # can be used directly with the amalgamation.
  #
  parse.h:  parse.c

  parse.c:  $(TOP)/src/parse.y lemon $(TOP)/tool/addopcodes.tcl
cp $(TOP)/src/parse.y .
rm -f parse.h
!   ./lemon -s $(OPTS:SQLITE_ENABLE_UPDATE_DELETE_LIMIT=_UNUSED) \
!parse.y
!   mv parse.c parse.c.1.temp
!   mv parse.out parse.out.1.temp
!   ./lemon -s $(OPTS:SQLITE_ENABLE_UPDATE_DELETE_LIMIT=_UNUSED) \
!   -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT parse.y
!   mv parse.c parse.c.2.temp
!   mv parse.out parse.out.2.temp
!   -diff -d -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT parse.c.1.temp 
parse.c.2.temp >parse.c
mv parse.h parse.h.temp
tclsh $(TOP)/tool/addopcodes.tcl parse.h.temp >parse.h

  sqlite3.h:$(TOP)/src/sqlite.h.in $(TOP)/manifest.uuid $(TOP)/VERSION 
$(TOP)/ext/rtree/sqlite3rtree.h
tclsh $(TOP)/tool/mksqlite3h.tcl $(TOP) >sqlite3.h
diff -C5 sqlitesrc2/sqlite-src-3160200/Makefile.in 
sqlitesrc3/sqlite-src-3160200/Makefile.in
*** sqlitesrc2/sqlite-src-3160200/Makefile.in   2017-01-06 08:52:10.0 
-0800
--- sqlitesrc3/sqlite-src-3160200/Makefile.in   2017-02-06 14:06:04.625426600 
-0800
***
*** 940,956 

  opcodes.h:parse.h $(TOP)/src/vdbe.c $(TOP)/tool/mkopcodeh.tcl
cat parse.h $(TOP)/src/vdbe.c | $(TCLSH_CMD) $(TOP)/tool/mkopcodeh.tcl 
>opcodes.h

  # Rules to build parse.c and parse.h - the outputs of lemon.
  #
  parse.h:  parse.c

  parse.c:  $(TOP)/src/parse.y lemon$(BEXE) $(TOP)/tool/addopcodes.tcl
cp $(TOP)/src/parse.y .
rm -f parse.h
!   ./lemon$(BEXE) $(OPT_FEATURE_FLAGS) $(OPTS) parse.y
mv parse.h parse.h.temp
$(TCLSH_CMD) $(TOP)/tool/addopcodes.tcl parse.h.temp >parse.h

  sqlite3.h:$(TOP)/src/sqlite.h.in $(TOP)/manifest.uuid $(TOP)/VERSION
$(TCLSH_CMD) $(TOP)/tool/mksqlite3h.tcl $(TOP) >sqlite3.h
--- 940,969 

  opcodes.h:parse.h $(TOP)/src/vdbe.c $(TOP)/tool/mkopcodeh.tcl
cat parse.h $(TOP)/src/vdbe.c | $(TCLSH_CMD) $(TOP)/tool/mkopcodeh.tcl 
>opcodes.h

  # Rules to build parse.c and parse.h - the outputs of lemon.
+ # NB: To build parse.c, we run lemon twice - once with
+ # -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT and once without -
+ # and then diff the results.  This way, SQLITE_ENABLE_UPDATE_DELETE_LIMIT
+ # can be used directly with the amalgamation.
  #
  parse.h:  parse.c

  parse.c:  $(TOP)/src/parse.y lemon$(BEXE) $(TOP)/tool/addopcodes.tcl
cp $(TOP)/src/parse.y .
rm -f parse.h
!   ./lemon$(BEXE) 
$(OPT_FEATURE_FLAGS:SQLITE_ENABLE_UPDATE_DELETE_LIMIT=_UNUSED) \
!$(OPTS:SQLITE_ENABLE_UPDATE_DELETE_LIMIT=_UNUSED) parse.y
!   mv parse.c parse.c.1.temp
!   mv parse.out parse.out.1.temp
!   ./lemon$(BEXE) 
$(OPT_FEATURE_FLAGS:SQLITE_ENABLE_UPDATE_DELETE_LIMIT=_UNUSED) \
!   $(OPTS:SQLITE_ENABLE_UPDATE_DELETE_LIMIT=_UNUSED) \
!   -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT parse.y
!   mv parse.c parse.c.2.temp
!   mv parse.out parse.out.2.temp
!   -diff -d -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT parse.c.1.temp 
parse.c.2.temp >parse.c
mv parse.h parse.h.temp
$(TCLSH_CMD) $(TOP)/tool/addopcodes.tcl parse.h.temp >parse.h

  sqlite3.h:$(TOP)/src/sqlite.h.in $(TOP)/manifest.uuid $(TOP)/VERSION
$(TCLSH_CMD) $(TOP)/tool/mksqlite3h.tcl $(TOP) >sqlite3.h
diff 

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] FOREIGN KEY question

[Clemens Ladisch]

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


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