Re: [sqlite] How people test db failures

[Stephen Chrzanowski]

You'll have to explain to the customer that "no matter what" carries too
much weight.  Are you going to protect against hardware failures?  Fire?
Floods?  Under what level would an acceptable failure be, or an
unacceptable failure?  What specifically is the user looking to break?  If
the user gets smart enough and starts modifying your schemas, all bets are
off, so, does that mean you have to start encrypting the database to keep
their fingers out of the cookie jar?  If files are being moved around and
the database is pushed off somewhere else, do you keep a secret backup of
the database somewhere else on the computer?  Upload it to the cloud behind
the users back?  Do you somehow write the database directly onto the EXE
and just make the user guess where the file is?

You can protect your software as best as you can acknowledging that users
are inventive and will break your software, so the best you can do is
defend against at least data entry issues, and perhaps, if you want to go
the extra mile, DO save out to the cloud somewhere else for backup
purposes.  But there is a limit to what you can do without building a
virtual Fort Knox around a simple phone book database application, and even
then you can't save the data in case of a meteor strike.  So sanitize your
data on any database transaction, ensure that anything being put in and
pulled out matches that of the constraint that the application expects (IE:
User enters a letter instead of a number.  Since SQLite will accept that,
when reading the data out, your app may freak out).

It is absolutely impossible to protect against EVERY contingency (Which is
what "No Matter What" means), so definitions are going to have to be laid
out about what the expectations are to be for failure defenses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How people test db failures

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/05/13 22:31, Igor Korot wrote:
> Or the simplest one - the database is moved ...

That is sufficient to break the database.  A SQLite database is not just
the database file, it is also the journal and WAL.  If you don't move all
as a single unit then you break the database.  Often you'll get lucky ...

The only safe way to move a database is to use the backup api to make a
new copy with the new name.  If you do it outside of SQLite then great
care has to be taken, and errors won't necessarily be immediately apparent
or even show up under pragma check.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlGDT0QACgkQmOOfHg372QSkKwCgysgQWSsCWgSN1zMS0p9uOOWb
TL8An1MuVaYjysden5anmCnKk2G3+b5w
=8HDa
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How people test db failures

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/05/13 20:03, Igor Korot wrote:
> How do people prove to their customers that no matter what the
> software will either finish gracefully or will not break, crash or
> anything to that matter?

That is easy - use SQLite.  Put your work inside transactions.  You can
use savepoints to nest transactions.  A transaction either fully
completes, or has no effect.  Even if your app is forcefully killed this
will still hold.  (The database will be cleaned up the next time it is
opened removing information about the transaction that was under way.)

On the other hand your code interacting with SQLite could have bugs.  At a
simple level you could ignore the error code returned by functions and
assume they succeeded.

The only way to test that is to add in hooks to cause various pieces to
fail as needed, and use coverage analysis to help ensure you have tested a
reasonable amount of code.  An example of how thorough you can be is the
SQLite testing:

  http://www.sqlite.org/testing.html

All that aside, there is still no protection from a hostile environment.
An overclocked/overheated cpu can make arithmetic errors.  Cosmic rays can
change bits in memory.  Filesystems without checksums can have bit flips
on the storage or in transmission.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlGDTf0ACgkQmOOfHg372QSuhgCfZaiQPow1ioBbZnez569/oVPt
3s0AoLdwcTPuEB8apk5Dv1VNpLS2bMXC
=VYvk
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How people test db failures

[Igor Korot]

Keith,

On Thu, May 2, 2013 at 9:44 PM, Keith Medcalf  wrote:

>
> > My question was a little different.
>
> > I am developing desktop application  which utilizes SQLite. At some point
> > in time my customer will want to know what happen when the DB operation
> > breaks.
>
> This depends on your definition of "breaks".
>

Let me give you an example.
I start the transaction, do couple of inserts and then I get an I/O error.
In this case I need to rollback tell the user about failure and go back to
the screen I started the transaction from.

Or the simplest one - the database is moved and inaccessible. If it happens
on the start, just tell the user and exit the program. If it happens during
the program execution, again tell the user that current operation failed
and go back to the screen that originated the operation.


>
> > How do people prove to their customers that no matter what the software
> > will either finish gracefully or will not break, crash or anything to
> that
> > matter?
>
> Oftentimes, it is preferable to "break" or "crash" rather than do
> something in error -- or worse yet fail to detect the error and proceed
> merrily but incorrectly.
>
> > So far the guy tested the program and he made sure that working with DB
> > was OK if everything succeeds. But now I want to let him know that he can
> > setup the environment and try check what happen if something fails.
>
> The answer to this depends on what failed, and if you can anticipate such
> failure being a valid outcome of what you are doing.
>
> > How do you achieve something like this?
>
> If everything succeeds, you are fine.  If it fails in an anticipated way,
> then you deal with it in accordance with your design.  If the failure is
> unanticipated, you vomit noisily presenting sufficient information to
> diagnose and repair the problem.  If appropriate, you add specific handling
> for the error that you didn't anticipate happening once you know the
> circumstance under which it occurs and can validate that the specific
> circumstance has occurred so that your fault handler is dealing with the
> fault correctly.
>
> Of course, if the "error condition" is anticipated, then it is not an
> error, is it?
>
> Having something which guarantees consistency in the face of unexpected
> failure, in other words ACID, makes the issue not really much of an issue
> at all since it should be impossible for sudden "de-rugging" (ie, crash or
> killing the process) of the application at any time to cause corruption and
> inconsistency (if it can, then you have designed your application badly).
>
> You can handle it in the same way that Apple does:  crash with a little
> comic bomb on the screen, then display a picture of a little application
> with a thermometer in its mouth, then after a time simple restart yourself
> over cleanly.
>

Well I'm handling it.
Problem is, how do I prove to the customer/user that it is working as
expected no matter what?

Thank you.


>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How people test db failures

[Keith Medcalf]

> My question was a little different.

> I am developing desktop application  which utilizes SQLite. At some point
> in time my customer will want to know what happen when the DB operation
> breaks.

This depends on your definition of "breaks".

> How do people prove to their customers that no matter what the software
> will either finish gracefully or will not break, crash or anything to that
> matter?

Oftentimes, it is preferable to "break" or "crash" rather than do something in 
error -- or worse yet fail to detect the error and proceed merrily but 
incorrectly.

> So far the guy tested the program and he made sure that working with DB
> was OK if everything succeeds. But now I want to let him know that he can
> setup the environment and try check what happen if something fails.

The answer to this depends on what failed, and if you can anticipate such 
failure being a valid outcome of what you are doing.
 
> How do you achieve something like this?

If everything succeeds, you are fine.  If it fails in an anticipated way, then 
you deal with it in accordance with your design.  If the failure is 
unanticipated, you vomit noisily presenting sufficient information to diagnose 
and repair the problem.  If appropriate, you add specific handling for the 
error that you didn't anticipate happening once you know the circumstance under 
which it occurs and can validate that the specific circumstance has occurred so 
that your fault handler is dealing with the fault correctly.

Of course, if the "error condition" is anticipated, then it is not an error, is 
it?

Having something which guarantees consistency in the face of unexpected 
failure, in other words ACID, makes the issue not really much of an issue at 
all since it should be impossible for sudden "de-rugging" (ie, crash or killing 
the process) of the application at any time to cause corruption and 
inconsistency (if it can, then you have designed your application badly).

You can handle it in the same way that Apple does:  crash with a little comic 
bomb on the screen, then display a picture of a little application with a 
thermometer in its mouth, then after a time simple restart yourself over 
cleanly.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

Re: [sqlite] How people test db failures

[Igor Korot]

Hi, guys,

On Thu, May 2, 2013 at 6:52 PM, Keith Medcalf  wrote:

> > >> My pet peeve is the lousy error message like Microsoft used to produce
> > >> "dll not found".  What DLL?  What error?  E.g. File not found or
> > permission
> > >> problem?
> > >
> > > My favorite is from the service manager:
> > >
> > > "The Service could not be started because the file could not be found".
> > >
> > > Wouldn't it be nice to tell me (a) what service or (b) what file and
> (c)
> > where you were looking.
> >
> > In a previous version of Mac OS X sometimes deleting a file worked
> > properly but triggered an error message anyway.  The error message said
> > something like
> >
> > The file "" could not be deleted because the file no longer exists.
> >
> > .  The filename inside the quotes was always blank because it couldn't
> > find out the name because the file no longer existed.  It's really hard
> > explaining to users why it won't tell them what the problem is or what
> > file caused it.
>
> Of course the file name is known -- how else would it know that it no
> longer exists?
> Failing to use the correct variable in the error message is a defect in
> the processing logic.
>
> BTW, the bug you point out is quite common and exists in many operating
> systems (and applications).
>

This is all good, but...

My question was a little different.
I am developing desktop application  which utilizes SQLite. At some point
in time my customer will want to know what happen when the DB operation
breaks.
How do people prove to their customers that no matter what the software
will either finish gracefully or will not break, crash or anything to that
matter?

So far the guy tested the program and he made sure that working with DB was
OK if everything succeeds. But now I want to let him know that he can setup
the environment and try check what happen if something fails.

How do you achieve something like this?

Thank you.


> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How people test db failures

[Keith Medcalf]

> >> My pet peeve is the lousy error message like Microsoft used to produce
> >> "dll not found".  What DLL?  What error?  E.g. File not found or
> permission
> >> problem?
> >
> > My favorite is from the service manager:
> >
> > "The Service could not be started because the file could not be found".
> >
> > Wouldn't it be nice to tell me (a) what service or (b) what file and (c)
> where you were looking.
> 
> In a previous version of Mac OS X sometimes deleting a file worked
> properly but triggered an error message anyway.  The error message said
> something like
> 
> The file "" could not be deleted because the file no longer exists.
> 
> .  The filename inside the quotes was always blank because it couldn't
> find out the name because the file no longer existed.  It's really hard
> explaining to users why it won't tell them what the problem is or what
> file caused it.

Of course the file name is known -- how else would it know that it no longer 
exists? 
Failing to use the correct variable in the error message is a defect in the 
processing logic.

BTW, the bug you point out is quite common and exists in many operating systems 
(and applications).

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org





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

Re: [sqlite] sequential row numbers from query

[Simon Slavin]

On 3 May 2013, at 2:22am, James K. Lowden  wrote:

> I considered writing such a function, too.  I didn't because relations
> have no order.  
> 
> One might be tempted to say that's solved by ORDER BY.  But 
> 
> 1.  It's not clear that the rows are presented to the UDF in ORDER BY
> order.  There's no reason they should be, and a few they shouldn't,
> including
> 
> 2.  Subqueries can't have ORDER BY, making the UDF non-deterministic by
> definition.  

Also, ORDER BY can be on a non-unique field.  So even with an ORDER BY you can 
sometimes get two different orders.  Theoretically.

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

Re: [sqlite] How people test db failures

[Simon Slavin]

On 3 May 2013, at 1:49am, Keith Medcalf  wrote:

>> My pet peeve is the lousy error message like Microsoft used to produce
>> "dll not found".  What DLL?  What error?  E.g. File not found or permission
>> problem?
> 
> My favorite is from the service manager:
> 
> "The Service could not be started because the file could not be found".
> 
> Wouldn't it be nice to tell me (a) what service or (b) what file and (c) 
> where you were looking.

In a previous version of Mac OS X sometimes deleting a file worked properly but 
triggered an error message anyway.  The error message said something like

The file "" could not be deleted because the file no longer exists.

.  The filename inside the quotes was always blank because it couldn't find out 
the name because the file no longer existed.  It's really hard explaining to 
users why it won't tell them what the problem is or what file caused it.

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

Re: [sqlite] How people test db failures

[Keith Medcalf]

> My pet peeve is the lousy error message like Microsoft used to produce
> "dll not found".  What DLL?  What error?  E.g. File not found or permission
> problem?

My favorite is from the service manager:

 "The Service could not be started because the file could not be found".

Wouldn't it be nice to tell me (a) what service or (b) what file and (c) where 
you were looking.

All three would be best.  And this isn't a "used too".  This is the current 
state-of-the-art in Microsoft error messages.  Completely and utterly useless.




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

Re: [sqlite] sequential row numbers from query

[Yongil Jang]

Hi, all.

How about this approach?
I just implemented a simple code(User Defined Function) that returns a
number as like as row number.

It was worked pretty good with my simple SQL test cases.

sqlite> insert into test values ('first record');
sqlite> insert into test values ('second record');
sqlite> insert into test values ('third record');
sqlite> select rownum(0), * from test;
1 | first record
2 | second record
3 | third record

Note. parameter value of 0 is not necessary, but it should be exist to work
properly to use aux data in UDF.

Below shows my code.
It is registered by calling sqlite3_create_function() after database is
opened.

typedef struct ROWNUM_t ROWNUM_t;
struct ROWNUM_t{
  int nNumber;
};

static void rownum_free(void *p){
  sqlite3_free(p);
}

static void rownum(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  ROWNUM_t* pAux;

  pAux = sqlite3_get_auxdata(context, 0);

  if(!pAux) {
pAux = (ROWNUM_t*)sqlite3_malloc(sizeof(ROWNUM_t));
if(pAux) {
  pAux->nNumber = 0;
  sqlite3_set_auxdata(context, 0, (void*)pAux, rownum_free);
}
else {
  sqlite3_result_error(context, "sqlite3_malloc failed", -1);
  return;
}
  }
  pAux->nNumber++;

  sqlite3_result_int(context, pAux->nNumber);
}

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

Re: [sqlite] Opening an in-memory database in two connections, one read-write, one read-only.

[Richard Hipp]

On Sat, Apr 27, 2013 at 12:54 AM, Scott Hess  wrote:

> Someone over here was trying to use a shared-cache in-memory database with
> two connections, one a read-write connection for purposes of populating
> things, the other a read-only connection for purposes of letting a
> (trusted) user make queries.  They were surprised to find out that they
> could run write queries against the read-only handle (the insert query in
> my example).  Also, if you flip the order of the opens so that the
> read-only open happens first, then the create statement fails
> with SQLITE_READONLY.
>

Readonly-ness is tracked at the pager level since it can vary from one
ATTACHed database to the next, and the pager is shared between two
connections with shared cache because the cache is part of the pager, so
the readonly setting is only honored for the first connection to open.  The
second connection gets whatever the first connection had.



>
> [Aside: I suggested that it might be more appropriate to use an authorizer,
> rather than try to open an in-memory database read-only.]
>
> A couple minutes in the code makes me think that the sense of
> "cache=shared" is implemented at the btree.c layer, so it is somewhat
> unlikely that this is reasonable to actually support.  That said, it is
> unexpected.  It seems like the library should throw something like
> SQLITE_MISUSE when you request a shared-cache open incompatible with
> previous opens.  WDYT?
>
> The same basic problem also appears to happen for on-disk databases, which
> makes me wonder if we're just doing something wrong.
>
> Thanks,
> scott
>
> ---
> /* gcc -g -o sqlmem sqlmem.c sqlite3.c */
>
> #include "sqlite3.h"
> #include 
>
> int main(int argc, char** argv) {
>   sqlite3* rw;
>   sqlite3* ro;
>   int rc;
>   char* err;
>
>   rc = sqlite3_open_v2("file::memory:?cache=shared", ,
>SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI, NULL);
>   if (rc!=SQLITE_OK) {
> fprintf(stderr, "rw rc==%d\n", rc);
> return 1;
>   }
>
>   rc = sqlite3_open_v2("file::memory:?cache=shared", ,
>SQLITE_OPEN_READONLY | SQLITE_OPEN_URI, NULL);
>   if (rc!=SQLITE_OK) {
> fprintf(stderr, "ro rc==%d\n", rc);
> return 1;
>   }
>
>   rc = sqlite3_exec(rw, "CREATE TABLE t (a TEXT)", NULL, NULL, );
>   if (rc!=SQLITE_OK) {
> fprintf(stderr, "rw create error: %d/%s\n", rc, err);
> return 1;
>   }
>
>   rc = sqlite3_exec(ro, "SELECT 1", NULL, NULL, );
>   if (rc!=SQLITE_OK) {
> fprintf(stderr, "ro select error: %d/%s\n", rc, err);
> return 1;
>   }
>
>   rc = sqlite3_exec(ro, "INSERT INTO t (a) VALUES ('a')", NULL, NULL,
> );
>   if (rc!=SQLITE_OK) {
> fprintf(stderr, "ro insert error: %d/%s\n", rc, err);
> return 1;
>   } else {
> fprintf(stderr, "ro insert succeeeded?\n");
> return 1;
>   }
> }
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Opening an in-memory database in two connections, one read-write, one read-only.

[Scott Hess]

No comment?  Do I need to recast the problem or something?

Thanks,
scott


On Fri, Apr 26, 2013 at 9:54 PM, Scott Hess  wrote:
> Someone over here was trying to use a shared-cache in-memory database with
> two connections, one a read-write connection for purposes of populating
> things, the other a read-only connection for purposes of letting a (trusted)
> user make queries.  They were surprised to find out that they could run
> write queries against the read-only handle (the insert query in my example).
> Also, if you flip the order of the opens so that the read-only open happens
> first, then the create statement fails with SQLITE_READONLY.
>
> [Aside: I suggested that it might be more appropriate to use an authorizer,
> rather than try to open an in-memory database read-only.]
>
> A couple minutes in the code makes me think that the sense of "cache=shared"
> is implemented at the btree.c layer, so it is somewhat unlikely that this is
> reasonable to actually support.  That said, it is unexpected.  It seems like
> the library should throw something like SQLITE_MISUSE when you request a
> shared-cache open incompatible with previous opens.  WDYT?
>
> The same basic problem also appears to happen for on-disk databases, which
> makes me wonder if we're just doing something wrong.
>
> Thanks,
> scott
>
> ---
> /* gcc -g -o sqlmem sqlmem.c sqlite3.c */
>
> #include "sqlite3.h"
> #include 
>
> int main(int argc, char** argv) {
>   sqlite3* rw;
>   sqlite3* ro;
>   int rc;
>   char* err;
>
>   rc = sqlite3_open_v2("file::memory:?cache=shared", ,
>SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI, NULL);
>   if (rc!=SQLITE_OK) {
> fprintf(stderr, "rw rc==%d\n", rc);
> return 1;
>   }
>
>   rc = sqlite3_open_v2("file::memory:?cache=shared", ,
>SQLITE_OPEN_READONLY | SQLITE_OPEN_URI, NULL);
>   if (rc!=SQLITE_OK) {
> fprintf(stderr, "ro rc==%d\n", rc);
> return 1;
>   }
>
>   rc = sqlite3_exec(rw, "CREATE TABLE t (a TEXT)", NULL, NULL, );
>   if (rc!=SQLITE_OK) {
> fprintf(stderr, "rw create error: %d/%s\n", rc, err);
> return 1;
>   }
>
>   rc = sqlite3_exec(ro, "SELECT 1", NULL, NULL, );
>   if (rc!=SQLITE_OK) {
> fprintf(stderr, "ro select error: %d/%s\n", rc, err);
> return 1;
>   }
>
>   rc = sqlite3_exec(ro, "INSERT INTO t (a) VALUES ('a')", NULL, NULL, );
>   if (rc!=SQLITE_OK) {
> fprintf(stderr, "ro insert error: %d/%s\n", rc, err);
> return 1;
>   } else {
> fprintf(stderr, "ro insert succeeeded?\n");
> return 1;
>   }
> }
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interrupting SQLite execution (mainly FTS)

[Dan Kennedy]

On Thu, 2 May 2013 16:58:01 +0200
"Jan Slodicka"  wrote:

> Hi Dan
> 
> > What are your settings for pragmas "cache_size", "journal_mode" and
> > "synchronous"?
> 
> cache_size/synchronous - default values
> 
> Don't remember, which journal_mode was used for testing. Should be
> WAL, but I might have been lazy to write needed code. The source code
> was meanwhile modified, but I can write it again if necessary.

Maybe the delay at the end of the operation was an automatic 
checkpoint. I think that would explain why sqlite3_interrupt() 
and the others did not help.

Maybe you can run the checkpoint in a background thread after
rebuilding the FTS index or something.

If you don't mind locking the database, you could also try the
rebuild in rollback mode. It might be a little slower overall,
but it might also be more responsive as far as 
sqlite3_interrupt() goes.


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

Re: [sqlite] Interrupting SQLite execution (mainly FTS)

[Jan Slodicka]

Hi Dan

> What are your settings for pragmas "cache_size", "journal_mode" and 
> "synchronous"?

cache_size/synchronous - default values

Don't remember, which journal_mode was used for testing. Should be WAL, but I 
might have been lazy to write needed code.
The source code was meanwhile modified, but I can write it again if necessary.

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

Re: [sqlite] ORDER of export on a SELECT

[Gert Van Assche]

Thanks for the advice, both. In the mean time we know that it indeed
something else causing the problem.

gert


2013/5/2 Simon Slavin 

>
> On 2 May 2013, at 9:26am, Gert Van Assche  wrote:
>
> > I have a table with 2 fields that need to be exported to 2 TXT files but
> > the order of the lines in the export should be exactly the same as in the
> > table.
>
> Tables do not have order.  Really.  A table is a set of rows, not an
> ordered set of rows.  If you do not specify ORDER BY on a unique set of
> values, the same SELECT can returns the same rows in different orders.
>
> > I tried to do it like this:
> > SELECT [FieldA] FROM [T1] ORDER BY rowid;
> > and
> > SELECT [FieldB] FROM [T1] ORDER BY rowid;
> > but the rowid order is not followed. The two TXT files that are created
> are
> > not in sync.
>
> If you have unique values in rowid, and really are doing those two
> commands in the same transaction (so nothing can write to the table between
> them), and getting rows returned in different orders, something is weird
> about your setup.
>
> In your software do
>
> SELECT [FieldA],[FieldB] FROM [T1] ORDER BY rowid
>
> Use your software to make one text file from the first values and another
> from the second values.  It will be faster too !
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: Generate 'INSERT' command

[Igor Tandetnik]

On 5/2/2013 10:45 AM, Simon Slavin wrote:

I know of the reluctance among the SQLite programmers to add features to the 
intentionally 'lite' product, but I am coming to the conclusion that having 
this feature in the standard code base will be of great convenience to a lot of 
users.

I would like the introduction of a command which changes a SELECT the same way 
EXPLAIN QUERY PLAN changes a SELECT.  This one should take the SELECT and 
instead of producing a table, sometimes with many columns, produces a 1 column 
table with the SELECTed data shown as INSERT commands, with values correctly 
single-quoted where necessary.


Why? What's the point of the exercise? How would you use this beast if 
you had it?


In any case, it's not clear why the implementation must live inside the 
engine. SQLite API provides sufficient information for you to be able to 
pull this off within the host application, if you are so inclined.



Alternatively a completely different syntax could be used which looks nothing 
like a SELECT statement, one where you had to specify a table, and could 
optionally specify a list of columns, something like

LIST INSERTS FOR myTable FOR COLUMNS *
LIST INSERTS FOR myTable FOR COLUMNS (id, player, team, time)


Are you perhaps looking for .dump command of sqlite3 console?
--
Igor Tandetnik

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

Re: [sqlite] Feature request: Generate 'INSERT' command

[Jay A. Kreibich]

On Thu, May 02, 2013 at 03:45:16PM +0100, Simon Slavin scratched on the wall:

> I would like the introduction of a command which changes a SELECT the
> same way EXPLAIN QUERY PLAN changes a SELECT.  This one should take
> the SELECT and instead of producing a table, sometimes with many
> columns, produces a 1 column table with the SELECTed data shown
> as INSERT commands, with values correctly single-quoted where necessary.

  You know the sqlite3 program already does this, right?

.mode insert

  If you need the functionality built into your application, just rip
  out the code and use it.

  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Feature request: Generate 'INSERT' command

[Simon Slavin]

I know of the reluctance among the SQLite programmers to add features to the 
intentionally 'lite' product, but I am coming to the conclusion that having 
this feature in the standard code base will be of great convenience to a lot of 
users.

I would like the introduction of a command which changes a SELECT the same way 
EXPLAIN QUERY PLAN changes a SELECT.  This one should take the SELECT and 
instead of producing a table, sometimes with many columns, produces a 1 column 
table with the SELECTed data shown as INSERT commands, with values correctly 
single-quoted where necessary.

The simplest implementation, and possibly all that's needed, would be one which 
works only on 'SELECT *' with no JOINs.  It should pay attention to 'WHERE', 
'ORDER BY', 'GROUP BY', LIMIT and OFFSET.

The next complication up would be to allow the selection of certain fields in a 
specific order rather than working only for '*'.

One proposal for syntax would be to prefix the SELECT statement with 'LIST 
INSERTS FOR '.

Alternatively a completely different syntax could be used which looks nothing 
like a SELECT statement, one where you had to specify a table, and could 
optionally specify a list of columns, something like

LIST INSERTS FOR myTable FOR COLUMNS *
LIST INSERTS FOR myTable FOR COLUMNS (id, player, team, time)

which didn't have a way of specifying any SELECT clauses.

What does the team think ?  Are any of you familiar with ways other SQL engines 
do this ?

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

[sqlite] Performance HELP

[peter korinis]

Thank you for your responses.

I was hoping for an easy hardware solution, like more memory or a faster HD
. but it looks like indices and table design are where I need to focus.

peterK

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

Re: [sqlite] Interrupting SQLite execution (mainly FTS)

[Simon Slavin]

On 2 May 2013, at 2:57pm, Jan Slodicka  wrote:

> LIKE is used at the moment, but it has its perf limits as well. To prove it,
> here is a desktop benchmark:
> 
> Desktop: W7, x64, Intel i5, 2.4 GHz
> 116 MB email table containing 1 html-formatted emails.
> SELECT ... LIKE...'%xxx%'... command took 35.5 sec. (Producing 9 results)
> SELECT ... MATCH ... 'xxx*' command took 0.00 sec, i.e. somewhere between
> 0-7 msec. (By chance producing the same results)
> 
> The funny thing was that the creation of the email FTS index took 22-23 sec,
> i.e. was faster than a single LIKE statement.

Thank you for this interesting and surprising information.  The resource and 
time cost of doing any writing on a mobile device is usually far greater than 
simply doing processing in memory.  It appears that this is not true in your 
case.  Hmm.

> My plan is to implement FTS search as an optional feature:
> - Several FTS indexes grouped into multi-indexes (Example: people names may
> be in one of 3 tables: accounts, contacts, leads. These tables would
> contribute to PeopleSearch activity.)
> - FTS index is built on demand (when the user tries to use it)
> - The build procedure must be cancellable
> - Once built, the index will be maintained using triggers (for small data
> changes)
> - For large data changes (happens during synchronization when the server
> sends a lot of data) the FTS index is dropped
> 
> Do you see any risks with this scheme?

It appears that you are aware of the issues and have thought this through.  
Sorry for troubling you.

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

Re: [sqlite] Interrupting SQLite execution (mainly FTS)

[Dan Kennedy]

On Thu, 2 May 2013 11:16:06 +0200
"Jan Slodicka"  wrote:

> I was testing various ways how to interrupt rebuilding of an FTS
> index. Ability to abort is vital as the app is running on a
> smartphone and has to meet responsivity requirements.
> 
> Here is what SQLite offers (AFAIK):
> - Progress handler (with low nOps value): Relatively good with only
> occasional longer periods without progress handler invocation. High
> nOps values perform badly.
> - Authorizer: Does not help much in itself, but improves a bit the
> progress handler as it is called at different occasions.
> - sqlite3_interrupt(): Tested once per sqlite3VdbeExec() call (also
> during SQL parsing, sqlite3Step()...) => insufficient.
> 
> Suggestion:
> The progress handler is called when {operation_counter=i*nOps; i>0},
> whereby the counter is reset at entry to sqlite3VdbeExec() and then
> incremented for each VDBE operation. This reset causes irregular
> behavior with occasional long periods without progress handler
> invocation. So the suggestion is: Make the counter global per DB
> connection and do not reset it.
> 
> The real problem is that even if I use all above tools (progress
> handler with nOps=1), there are relatively long periods when the
> execution cannot be interrupted. For example for a 30MB table (the
> user data may be larger) there is roughly 0.5 sec pause at the end
> (measured on the desktop!), maybe caused by journal maintenance.

What are your settings for pragmas "cache_size", "journal_mode" and
"synchronous"?

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

Re: [sqlite] Interrupting SQLite execution (mainly FTS)

[Jan Slodicka]

Hi Simon.

> I have much love for FTS but it chews up storage space, processing power,
and therefore battery life, something fierce.  You may end up with a working
app but your users will find it reduces their battery life to an hour.

Sounds unbelievable. Can you bring some example, please?

In the past we worked for example on smartphone backup solutions, i.e. long
running apps taking full processor power and writing a lot to SD cards
(which in my opinion drains battery more), but did not observe such dramatic
effect. (Or even worse battery "drainer": playing mp3 streamed over BT.)

> Do you really need to /build/ an FTS database on a phone app ?  Can you
not (A) do what you want to do with GLOB and LIKE or (B) build the database
externally before it's moved to the phone ?

The app serves as a CRM client with full editing abilities, synchronization
etc. Hence the data is live and cannot be pre-built.

LIKE is used at the moment, but it has its perf limits as well. To prove it,
here is a desktop benchmark:

Desktop: W7, x64, Intel i5, 2.4 GHz
116 MB email table containing 1 html-formatted emails.
SELECT ... LIKE...'%xxx%'... command took 35.5 sec. (Producing 9 results)
SELECT ... MATCH ... 'xxx*' command took 0.00 sec, i.e. somewhere between
0-7 msec. (By chance producing the same results)

The funny thing was that the creation of the email FTS index took 22-23 sec,
i.e. was faster than a single LIKE statement.



My plan is to implement FTS search as an optional feature:
- Several FTS indexes grouped into multi-indexes (Example: people names may
be in one of 3 tables: accounts, contacts, leads. These tables would
contribute to PeopleSearch activity.)
- FTS index is built on demand (when the user tries to use it)
- The build procedure must be cancellable
- Once built, the index will be maintained using triggers (for small data
changes)
- For large data changes (happens during synchronization when the server
sends a lot of data) the FTS index is dropped

Do you see any risks with this scheme?

Jan


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

Re: [sqlite] How people test db failures

[Michael Black]

There are several ways people handle errors.

#1 Path of least resistanceyou assume all is well and ignore thembad
idea but too common
#2 Catch as catch can...you put in error handling as you experience
themalso a bad idea as it's too similar to #1
#3 Catch with careful thought about what could happencan be difficult
but this is what experienced people do...can still miss things.
#4 Catch all...check all return codes and print out somethingthen do #3
for known error and graceful/proper handling...this is how you learn.
#5 Force all possible errors to occur...there's a lot.  I don't know anybody
that does that.

A combo of #3 and #4 is best IMHO.

If you want to be really conservative you could write a wrapper around every
sqlite function to randomly return one of its possible error codes.
That's would take a lot of time though and I've never heard of anybody doing
that.

My pet peeve is the lousy error message like Microsoft used to produce "dll
not found".  What DLL?  What error?  E.g. File not found or permission
problem?
Or the very common "cannot open file".  What file, why not?  Error messages
need to be explicit.
SQlite is pretty good about it.  Developers not so much.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Korot
Sent: Wednesday, May 01, 2013 2:00 PM
To: General Discussion of SQLite Database
Subject: [sqlite] How people test db failures

Hi, ALL,
I wrote a code that executes fine. There is no memory leaks and no
issues.

However, trying to check whether my error handling is written correctly, I
set
breakpoint before accessing db and when I hit it I manually changes the
value returned.
This is not how it should be done and I feel that the person I'm working
with will need to check it
at some point.

So, how people check whether db failure result in graceful program
termination/proper flow?

I'm working on the desktop application under Windows and am giving the
release version of the program
compiled under MSVC 2010.

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

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

Re: [sqlite] ORDER of export on a SELECT

[Simon Slavin]

On 2 May 2013, at 9:26am, Gert Van Assche  wrote:

> I have a table with 2 fields that need to be exported to 2 TXT files but
> the order of the lines in the export should be exactly the same as in the
> table.

Tables do not have order.  Really.  A table is a set of rows, not an ordered 
set of rows.  If you do not specify ORDER BY on a unique set of values, the 
same SELECT can returns the same rows in different orders.

> I tried to do it like this:
> SELECT [FieldA] FROM [T1] ORDER BY rowid;
> and
> SELECT [FieldB] FROM [T1] ORDER BY rowid;
> but the rowid order is not followed. The two TXT files that are created are
> not in sync.

If you have unique values in rowid, and really are doing those two commands in 
the same transaction (so nothing can write to the table between them), and 
getting rows returned in different orders, something is weird about your setup.

In your software do

SELECT [FieldA],[FieldB] FROM [T1] ORDER BY rowid

Use your software to make one text file from the first values and another from 
the second values.  It will be faster too !

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

Re: [sqlite] Interrupting SQLite execution (mainly FTS)

[Simon Slavin]

On 2 May 2013, at 10:16am, "Jan Slodicka"  wrote:

> I was testing various ways how to interrupt rebuilding of an FTS index.
> Ability to abort is vital as the app is running on a smartphone and has to
> meet responsivity requirements.

Sorry, I don't know an answer to your question, but I do have an observation.

I have much love for FTS but it chews up storage space, processing power, and 
therefore battery life, something fierce.  You may end up with a working app 
but your users will find it reduces their battery life to an hour.

Do you really need to /build/ an FTS database on a phone app ?  Can you not (A) 
do what you want to do with GLOB and LIKE or (B) build the database externally 
before it's moved to the phone ?

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

Re: [sqlite] ORDER of export on a SELECT

[Richard Hipp]

On Thu, May 2, 2013 at 4:26 AM, Gert Van Assche  wrote:

> All, I
>
> I have a table with 2 fields that need to be exported to 2 TXT files but
> the order of the lines in the export should be exactly the same as in the
> table.
> I tried to do it like this:
>  SELECT [FieldA] FROM [T1] ORDER BY rowid;
> and
>  SELECT [FieldB] FROM [T1] ORDER BY rowid;
> but the rowid order is not followed. The two TXT files that are created are
> not in sync.
>

"ORDER BY rowid" should always be honored.  If you have a case where it is
not, that is a bug.  Please send us sufficient information to reproduce the
problem and we will look into it.



>
> Is this normal or is there another way to address this?
>
> thanks for your help,
>
> Gert
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] amatch extension

[Ralf Junker]

I am unsure about how the amatch extension works. According to the
documentation, I believe that the SQL below should return at least one
record, but it does not. No errors are reported either.

I compiled the shell executable with the 201304290917 draft amalgamation
and today's amatch.c from Fossil trunk.

Is there something I am missing?

Ralf



DROP TABLE IF EXISTS f;
DROP TABLE IF EXISTS c;
DROP TABLE IF EXISTS v;

CREATE TABLE v (w TEXT, l INTEGER);
CREATE INDEX v_index ON v(w);
INSERT INTO v VALUES ('abc', 0);
INSERT INTO v VALUES ('bcd', 0);
INSERT INTO v VALUES ('def', 0);

CREATE TABLE c(iLang INTEGER, cFrom TEXT, cTo TEXT, Cost INTEGER);
INSERT INTO c VALUES(0, '', 'a', 100);
INSERT INTO c VALUES(0, 'b', '', 87);
INSERT INTO c VALUES(0, 'o', 'oe', 38);
INSERT INTO c VALUES(0, 'oe', 'o', 40);

INSERT INTO c VALUES(0, '?', '', 97);
INSERT INTO c VALUES(0, '', '?', 98);
INSERT INTO c VALUES(0, '?', '?', 99);

CREATE VIRTUAL TABLE f USING approximate_match(
   vocabulary_table=v,
   vocabulary_word=w,
   vocabulary_language=l,
   edit_distances=c
);

SELECT * FROM f WHERE word MATCH 'abc';
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.7.17 Preview

[Ralf Junker]

Current trunk still does not compile with SQLITE_OMIT_WAL #defined.

Details here:

http://www.mail-archive.com/sqlite-users@sqlite.org/msg76672.html

Ralf

On 26.04.2013 17:34, Richard Hipp wrote:

> Please visit http://www.sqlite.org/draft/releaselog/3_7_17.html to see the
> proposed enhancements and changes in SQLite version 3.7.17.  Your comments,
> criticisms and suggestions are welcomed and encouraged.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: query parser should trigger an error if a subquery selects unknown columns from table

[Hick Gunter]

Because then you would lose automatic matching of unique field names everywhere 
else...

-Ursprüngliche Nachricht-
Von: Thomas Krueger [mailto:tom.krue...@gmail.com]
Gesendet: Donnerstag, 02. Mai 2013 13:15
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Bug report: query parser should trigger an error if a 
subquery selects unknown columns from table

I ran into a similar issue:

DELETE FROM ATable  WHERE EXISTS(SELECT 1 FROM  TMPTable AS  t WHERE id =
t.id) ;

Syntactically I was expecting id to be the ATable.id as I had aliased the 
TMPTable with t. But the result was a non-correlated subquery, id = t.id was 
always true. The fix is clear, yet a bit unintuitive in this circumstance: Why 
not request that all aliased tables use their table alias?



On Thu, May 2, 2013 at 12:41 AM, Richard Hipp  wrote:

> On Wed, May 1, 2013 at 3:23 PM, Anderson Medeiros Gomes
> wrote:
>
> > Hi. I think I found a bug in SQLite, so I'm reporting it in this message.
> >
> > The print screen I have attached shows a query that SQLite executes
> > and brings no results. I believe SQLite should trigger an error
> > while parsing my input, because I used an unknown column in the subquery.
> >
>
> SQLite is giving the correct response here.  The "foocolumn" in the
> subquery refers out to the containing query.  We say that the subquery
> is a "correlated subquery" because it contains references to the outer query.
>
> In your case, the query is logically equivalent to:
>
>SELECT foocolumn FROM footable WHERE 123 NOT IN (SELECT 123 FROM
> bartable);
>
> Since bartable is not empty, the NOT EXISTS is always false and the
> query returns no rows.
>
>
>
> >
> > This is the print screen's textual representation:
> >
> > $ sqlite3 /tmp/test.sqlite
> > SQLite version 3.7.16.2 2013-04-12 11:52:43 Enter ".help" for
> > instructions Enter SQL statements terminated with a ";"
> > sqlite> CREATE TABLE footable (foocolumn INTEGER); CREATE TABLE
> > sqlite> bartable (barcolumn INTEGER); INSERT INTO footable
> > sqlite> (foocolumn) VALUES (1); INSERT INTO bartable (barcolumn)
> > sqlite> VALUES (2); *SELECT foocolumn FROM footable WHERE foocolumn
> > sqlite> NOT IN (SELECT
> > foocolumn FROM bartable);*
> > sqlite> exit
> >...> ;
> > Error: near "exit": syntax error
> > sqlite> .quit
> >
> >
> >
> > --
> > Anderson Medeiros Gomes
> > amg1...@gmail.com
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Potential problem in SQLite 3.7.16.2

[Mario M. Westphal]

Thank you for providing this pre-release amalgamation ;-)
I downloaded it immediately and compiled it into my software.
 
The problem has been ++resolved++ and the performance is at least as good as
with previous versions of SQLite.
It even feels a bit faster, although I only could try it with a 20% sample
of the typical data volume.
 
Thank you for the excellent support. Much appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: query parser should trigger an error if a subquery selects unknown columns from table

[Thomas Krueger]

I ran into a similar issue:

DELETE FROM ATable  WHERE EXISTS(SELECT 1 FROM  TMPTable AS  t WHERE id =
t.id) ;

Syntactically I was expecting id to be the ATable.id as I had aliased the
TMPTable with t. But the result was a
non-correlated subquery, id = t.id was always true. The fix is clear, yet a
bit unintuitive in this circumstance: Why
not request that all aliased tables use their table alias?



On Thu, May 2, 2013 at 12:41 AM, Richard Hipp  wrote:

> On Wed, May 1, 2013 at 3:23 PM, Anderson Medeiros Gomes
> wrote:
>
> > Hi. I think I found a bug in SQLite, so I'm reporting it in this message.
> >
> > The print screen I have attached shows a query that SQLite executes and
> > brings no results. I believe SQLite should trigger an error while parsing
> > my input, because I used an unknown column in the subquery.
> >
>
> SQLite is giving the correct response here.  The "foocolumn" in the
> subquery refers out to the containing query.  We say that the subquery is a
> "correlated subquery" because it contains references to the outer query.
>
> In your case, the query is logically equivalent to:
>
>SELECT foocolumn FROM footable WHERE 123 NOT IN (SELECT 123 FROM
> bartable);
>
> Since bartable is not empty, the NOT EXISTS is always false and the query
> returns no rows.
>
>
>
> >
> > This is the print screen's textual representation:
> >
> > $ sqlite3 /tmp/test.sqlite
> > SQLite version 3.7.16.2 2013-04-12 11:52:43
> > Enter ".help" for instructions
> > Enter SQL statements terminated with a ";"
> > sqlite> CREATE TABLE footable (foocolumn INTEGER);
> > sqlite> CREATE TABLE bartable (barcolumn INTEGER);
> > sqlite> INSERT INTO footable (foocolumn) VALUES (1);
> > sqlite> INSERT INTO bartable (barcolumn) VALUES (2);
> > sqlite> *SELECT foocolumn FROM footable WHERE foocolumn NOT IN (SELECT
> > foocolumn FROM bartable);*
> > sqlite> exit
> >...> ;
> > Error: near "exit": syntax error
> > sqlite> .quit
> >
> >
> >
> > --
> > Anderson Medeiros Gomes
> > amg1...@gmail.com
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Interrupting SQLite execution (mainly FTS)

[Jan Slodicka]

I was testing various ways how to interrupt rebuilding of an FTS index.
Ability to abort is vital as the app is running on a smartphone and has to
meet responsivity requirements.

Here is what SQLite offers (AFAIK):
- Progress handler (with low nOps value): Relatively good with only
occasional longer periods without progress handler invocation. High nOps
values perform badly.
- Authorizer: Does not help much in itself, but improves a bit the progress
handler as it is called at different occasions.
- sqlite3_interrupt(): Tested once per sqlite3VdbeExec() call (also during
SQL parsing, sqlite3Step()...) => insufficient.

Suggestion:
The progress handler is called when {operation_counter=i*nOps; i>0}, whereby
the counter is reset at entry to sqlite3VdbeExec() and then incremented for
each VDBE operation. This reset causes irregular behavior with occasional
long periods without progress handler invocation.
So the suggestion is: Make the counter global per DB connection and do not
reset it.

The real problem is that even if I use all above tools (progress handler
with nOps=1), there are relatively long periods when the execution cannot be
interrupted. For example for a 30MB table (the user data may be larger)
there is roughly 0.5 sec pause at the end (measured on the desktop!), maybe
caused by journal maintenance.

In my opinion I would not have this problem if the (external content) FTS
index could be placed into an attached database. Then
a) The FTS DB could be set up for highest performance / lowest safety. (No
journal, for example)
b) The long actions could be performed in a thread that could be killed if
necessary.

However,  FTS design doesn't seem to enable that. (Would deserve separate
discussion. Main problem for me is that the triggers cannot refer to an
attached DB.)

Any advice?

Thanks in advance,
Jan Slodicka


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

[sqlite] ORDER of export on a SELECT

[Gert Van Assche]

All, I

I have a table with 2 fields that need to be exported to 2 TXT files but
the order of the lines in the export should be exactly the same as in the
table.
I tried to do it like this:
 SELECT [FieldA] FROM [T1] ORDER BY rowid;
and
 SELECT [FieldB] FROM [T1] ORDER BY rowid;
but the rowid order is not followed. The two TXT files that are created are
not in sync.

Is this normal or is there another way to address this?

thanks for your help,

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

Re: [sqlite] How to link the packet in C file then store in sqlite database?

[Newbie89]

Fehmi Noyan ISI wrote
> I cannot understand "So I need to create a table Packet right?", but you
> do need to create a table first.
> 
> You may need to bother yourself by reading the API reference for the
> functions I gave.

ok...can help me check my code?some part I not sure...thanks

link.c

>   # include 
> 
>   # include 
> 
>   # include 
> 
>  
>   int main(void) 
>   {
>   sqlite3 *conn;
>   sqlite3_stmt *res;
>   int error = 0;
>   int rec_count = 0;
>   const char *errMSG;
>   const char *tail;
> char sql_lite[900]=" ";
> int read_packet=0;
>   error = sqlite3_open("link.db", );
>   if (error) 
> {
>   printf("Can not open database");
>  
>   }
> 
>   
>   while(read_packet) 
>   {
> ProcessPack() 
>   sprintf(sql_lite, "insert into Packet
> (No,Dated,Time,Src_MAC,Dest_MAC,Net_P,Trans_P,Src_IP,Dest_IP,Src_Port,Dest_Port,Cap_Bytes)
> values ();",);
>   error = sqlite3_exec(conn, sql_lite, 0, 0, 0);
>   }
>   
>   error = sqlite3_prepare_v2(conn, "select * from Packet order by 
> No",1000,
> , ); 
> 
>   
>   if (error != SQLITE_OK) 
>   {
>   printf("We did not get any data!");
> exit(0);
>   
>   }
> 
>  
> printf("\n");
> 
>   while (sqlite3_step(res) == SQLITE_ROW) 
> {
> printf("%d|", sqlite3_column_int(res, 0));
>   printf("%s|", sqlite3_column_text(res, 1));
>   printf("%s|", sqlite3_column_text(res, 2));
>   printf("%c|", sqlite3_column_char(res, 3));
>   printf("%c|", sqlite3_column_char(res, 4));
> printf("%c|", sqlite3_column_char(res, 5));
> printf("%c|", sqlite3_column_char(res, 6));
> printf("%c|", sqlite3_column_char(res, 7));
>   printf("%c|", sqlite3_column_char(res, 8));
> printf("%d|", sqlite3_column_int(res, 9));
> printf("%d|", sqlite3_column_int(res, 10));
>   printf("%d\n", sqlite3_column_int(res, 11));
>   
>
> 
>   rec_count++;
>   }
> 
>  
> printf("=\n");
>   printf("We received %d records.\n", rec_count);
> 
>   sqlite3_finalize(res);
>   
>   sqlite3_close(conn);
> 
>   return 0;
>   }
>   }

link.sql







--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-link-the-packet-in-C-file-then-store-in-sqlite-database-tp68593p68615.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users