Re: [sqlite] insertion time

[Lloyd]

Hi,
  When this 3M inserts are performed, it takes 130 more seconds compared
to the actual application running time. The actual application run time
is 20 seconds but when this 3M inserts are performed it takes 150
seconds.

Is there any way to reduce the time ?

Thnaks,
  Lloyd.



On Mon, 2006-10-23 at 08:44 -0600, Dennis Cote wrote:
> Lloyd wrote:
> > Hi,
> >   How much time it may take to "insert or ignore into query" for 30
> > lakhs times. It adds more than 130 sec overhead compared to the 20 sec
> > of execution time. All these are done inside a single transaction. And
> > these insertions are made on three tables (on each table 10 lakhs) with
> > a combined primary key of text and integer.
> >
> > Is this the expected performance? Can I improve it anyway? (I am using
> > the wxSQLite wrapper class)
> >
> > Thanks,
> >   Lloyd.
> >
> >
> > __
> >   
> All,
> 
> FYI, a lakh is defined as:
> 
> A lakh (also spelled lac or laksha) is a unit in a traditional number 
> system, still widely used in India and Bangladesh, equal to a hundred 
> thousand. A hundred lakhs make a crore.
> 
> So Lloyd is doing 3M insert or ignores.
> 
> Lloyd,
> 
> What do you mean by "130 seconds of overhead"?
> 
> Dennis Cote
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] blob vs. string in bound parameters

[Dennis Cote]

[EMAIL PROTECTED] wrote:


There is good reason for this, actually.  


And that pretty definitively answers the question of whether or not this 
is a bug. :-)


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] database corrouption during power failure

[Dennis Cote]

jayanth KP wrote:

   But how do we read the current  synchronous value using C interface. Plz 
reply.

  

Jayanth,

Try this:

   sqlite3_stmt* s;
   sqlite3_prepare(db, "PRAGMA SYNCHRONOUS", -1, , NULL);
   sqlite3_step(s);
   int sync = sqlite3_column_int(s, 1);
   sqlite3_finalize(s);

   switch (sync) {
   case 0: // off
   case 1: // normal
   case 2: // full
   }


It simply executes the PRAGMA and collects the result.

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] blob vs. string in bound parameters

[Andy Ross]

D. Richard Hipp wrote:
> Have you looked at the TCL bindings?  The above would be
>
>   db eval {select * from X where Y=$parameter} {... code here ...}

I do have a wrapper that uses a hash literal for exactly this
(although in practice I find it actually easier to read a bunch of
positional parameters on the argument list).  It's actually possible
in Nasal for a function to inspect the namespace of its caller,
although I haven't bothered to implement something like that here.

> At runtime, SQLite determines the internal data representation of
> the $parameter variable, then uses bind_text, bind_blob, bind_int,
> or bind_whatever as appropriate.

I'm not quite sure I see how.  This is actually exactly what I'm
doing: Nasal has two meaningful datatypes in this context: double
precision numbers and "strings", which are internally arbitrary byte
arrays.  A double gets set using bind_double(), and the string using
bind_blob() (originally: now bind_text()).  But if the *column* (Y, in
your example above) against which the parameter is compared has type
"text" and not "blob", that comparison always fails.

And TCL (the last I used it, which was in the mid-90's) has exactly
the same data representation as Nasal: there is no internal
distinction between a string and a byte array.  It would have exactly
the same issue that I am seeing; I suspect that the only reason you
don't hit more often it is that the TCL bindings picked the opposite
convention to the one I (originally) did: they call bind_text() for
all strings instead of bind_blob().

> Well, I suppose we might have made that choice when we were defining
> the interface for SQLite version 3 - if you had brought it up then.

Is the implication then that no planning is being done for version 4? :)

This is a straw man argument you are making: I consider this issue a
bug, not an interface change, and made that case as such.  You
disagree, and of course your opinion takes precedence.  But to argue
that *all* such changes to query behavior are "interface changes" and
therefore unfixable seems to stretch the point of what "compatibility"
means past a reasonable breaking point.  Clearly you have and will
continue to make changes that cause the results from queries to
change; you simply call them "fixes" instead of "changes".  And
honestly, your implication that my asking that this issue be fixed
implies a lack of understanding about compatibility and release
management is a little insulting.

> I would rather support the thousands of applications that I know are
> already using SQLite successfully than break all those other
> application

Um, break *all* of them?  Now you are simply exagerating.  I find it
difficult to believe that there are thousands of applications whose
correct behavior depends on blobs being != to text when used with
bound parameters.  I can't even think of even a *theoretical*
situation where this might happen.  Absence of evidence is not
evidence of absence, obviously, but nonetheless, I find it more likely
that the actual number of vulnerable applications is zero.  You
actually make the case yourself:

> You are the first person to complain about this in 2.5 years since
> the beginning of version 3 and I am aware of at least 2 dozen other
> language bindings that already exist and are fully functional

I strongly suspect those other languages made the same choice that the
TCL bindings did: they use bind_text() always, and never bind_blob()
except where the language or exposed API makes the distinction
explicit.  And because almost no one writes schemas where comparing
blobs is required, none of these hit the issue.  But for whatever
reason, I picked blob as the default; and as described, blob works
very poorly as a default, because your strings won't compare against
other strings already in the database.

I'm actually reasonably happy with this workaround from Dennis, so I'm
going to disappear from the list and not argue the case further.  But
I will admit to being a little puzzled that you seem to understand the
issue yet don't find it worth fixing, even in a future verison.

Andy

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: bind with select?

[Jay Sprenkle]

On 10/23/06, Dave Dyer <[EMAIL PROTECTED]> wrote:


You don't appear to be using BIND in the manner I was hoping for.  You're
using BIND to replace variables in the query.  I want to use BIND
(or something like it) eliminate the need for callback functions
to consume the results of a select.


Yes, you're correct. Bind is used to pass parameters to the executed sql
not to retrieve the results. You must 'pull' all the results when you execute
a query. Having sqlite 'push' them would only work if you have a single
row as a result set or unless you provide it with a callback so it can tell
you when a row is fully pushed.

--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] blob vs. string in bound parameters

[drh]

Andy Ross <[EMAIL PROTECTED]> wrote:
> 
>  > SQLite takes the approach of always making every BLOB larger than
>  > every TEXT string.  That is simple and unambiguous.
> 
> but it's a disaster for people like me trying to actually take
> advantage of the manifest typing feature.  I have an (IMHO really
> pleasing) API that looks like this:
> 
>sqlite.exec(db, "select * from X where Y = ?", ...bind params...)
> 
> This obviously presumes that types are convertable at runtime, which
> in SQLite they *mostly* are; I can pass in the string "12" and get a
> valid comparison to an integer or real value, etc...  But I *can't*
> know when binding the parameter whether the context in which it will
> be used is a blob or a string.  So what I do is use bind_blob()
> universally for all string objects.
>

Have you looked at the TCL bindings?  The above would be

   db eval {select * from X where Y=$parameter} {... code here ...}

Notice that the variable to be bound is specify by name directly
in the SQL.  So if you are binding multiple parameters (as I
often do) there is no danger of miscounting the number of "?"
and getting the bind parameters wrong on the end.  And when
reading, you do not have to move your eye out to the "bind parameters"
section to figure out what the "?" means.

At runtime, SQLite determines the internal data representation
of the $parameter variable, then uses bind_text, bind_blob,
bind_int, or bind_whatever as appropriate.  And this works very,
very well in practice.

>  I was led to believe by your
> documentation on manifest typing and by analogy to your automatic
> numeric conversions that this sort of conversion was legal.

I am sorry that the documentation misled you.  I'll work on
improving it.

> 
> Seriously: what's wrong with just (1) converting a string to a blob by
> exposing the literal byte in whatever encoding it was stored in, and
> (2) converting a blob to a string by interpreting the bytes literally
> in the current "pragma encoding" environment?  Sure, the user can
> shoot herself in the foot with that, but it works unambiguously in the
> only sane case: where the user-side string environment and the
> database schema are written to use the same encoding.
> 

Well, I suppose we might have made that choice when we were
defining the interface for SQLite version 3 - if you had
brought it up then.  But we did not. And the interface is 
now frozen is not going to change regardless of whether or 
not your system is better than the one that is implemented.  
SQLite takes backwards compatibility very seriously, and 
so we are not going to make a change of this magnitude 
without a very good reason.

You might not think SQLite's backwards compatibility
pledge is a good thing now, but if you continue using
SQLite then someday you will likely thank me.

> Do you have any other suggestions, or is the clean/simple API choice 
> above just not something you want to suppot with SQLite?  It seems 
> like many other language bindings are going to have the same issue... 

I would rather support the thousands of applications that
I know are already using SQLite successfully than break 
all those other application in order to support a single 
language that I have never heard of before.  You are the 
first person to complain about this in 2.5 years since 
the beginning of version 3 and I am aware of at least 2 
dozen other language bindings that already exist and are 
fully functional, so I really do not think  it will become 
a widespread problem.

> I think what I'll have to do in the interrim is default to bind_text() 
> instead of bind_blob().  Does that work if the data has embedded nuls? 

I believe you can bind_text with embedded '\000' characters,
as long as you explicitly specify the length of the text, of
course.  This is not something that is covered in the regression
tests, that I recall, so you might run into problems.  But if
you do, I would consider them bugs and will fix them. 

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] blob vs. string in bound parameters

[John Stanton]

Actually I was talking about an application language which lets users
incorporate their own SQL and which binds to the SQL at run time rather
than being like embedded SQL.

It doesn't need to parse SQL, it just uses the existing SQL API which 
provides all the necessary capability.


The application language compiler does not need to parse the SQL.
When I was writing the compiler I thought of looking at the SQL but soon 
realized that it was not necessary, the Sqlite API had enough

functionality to make that unecessary.

As I said before, the users of my application language declare Sqlite
columns to be anything their application would like to see.  The binding 
logic retrieves that declared type (e.g. DECIMAL(5,2)) and gets the 
actual type (which may be TEXT) and performs the appropriate move, in 
that case a TEXT string of digits into a fixed point, display format 
number.


Sqlite's "manifest typing" has been carefully thought through and is 
almost all things for all people.


Andy Ross wrote:

John Stanton wrote:
 > The method I wrote about earlier is part of a language binding.
 > I can show you the code if you are interested.

I think we must be talking about different things, then.  A language
binding that allows the user to write their own SQL cannot
automatically insert cast() calls into the queries without parsing the
SQL and making a runtime determination about the context in which each
bound parameter will be evaluated.  And that's the halting problem. :)

Maybe you're talking about something like rails, which abstracts away
the SQL from the user API and generates its own queries?  Then sure.
But that's not the level of abstraction I'm working with.

But regardless: sure, I'd be curious to see how you are dealing with
the same issue.  What I've done for now is just change the default
from bind_blob() to bind_text(), and add some language to the docs
warning users that they need to add a cast-to-text when writing
expressions involving blob columns.  Since comparing blob values is
obviously rare, this seems to me like an acceptable compromise.

Andy

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Extra functions - New Project?

[Joe Wilson]

This is not directly related to this extra functions case, but 
I think that a few authors of such "drive by patches" would 
invest more time into refining their patches if they had some 
feedback as to whether they might be considered for inclusion in 
the main tree. Even if you marked the patch in CVSTrac as 
'Rejected' with a reason why, this would be useful for the person 
volunteering the patch.

Having said that, I'm find many drive-by patches quite useful 
even though they may never be candidates for the main tree
or lack sufficient test cases or documentation.

- Original Message 
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, October 23, 2006 7:08:56 AM
Subject: Re: [sqlite] Extra functions - New Project?

Mikey C <[EMAIL PROTECTED]> wrote:
> 
> I sent the source code to DRH with the extra functions.  I don't myself have
> the time now to incorporate the extra functions into SQLite.
> 

Writing code a chunk of code is only a small fraction of
the work needed to support the code in a maintained product
such as SQLite.  Writing the code is, in fact, the easy
part.  After the code is written, somebody then has to 
develop regression tests that provide near 100% code
coverage.  The code has to be documented.  Then it has
to be maintained for years.  By my estimate, writing code
is perhaps 15% of the total work.

The code for the extra functions was submitted to me with
the promise that the author would provide no help in completing
the work of integration.  In other words, the author did
about 15% of the work and left the other 85% to me.  Such
a submission is often referred to as a "drive by patch".

I'm happy to have help on SQLite.  But if you contribute
code, you should finish the job.  That means providing test
cases that give 100% code coverage, documentation, and being
available to support your code for years in the future.
If you write a bunch of code and toss it over the wall,
then please do not be disappointed if nobody picks it up.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] blob vs. string in bound parameters

[Andy Ross]

John Stanton wrote:
> The method I wrote about earlier is part of a language binding.
> I can show you the code if you are interested.

I think we must be talking about different things, then.  A language
binding that allows the user to write their own SQL cannot
automatically insert cast() calls into the queries without parsing the
SQL and making a runtime determination about the context in which each
bound parameter will be evaluated.  And that's the halting problem. :)

Maybe you're talking about something like rails, which abstracts away
the SQL from the user API and generates its own queries?  Then sure.
But that's not the level of abstraction I'm working with.

But regardless: sure, I'd be curious to see how you are dealing with
the same issue.  What I've done for now is just change the default
from bind_blob() to bind_text(), and add some language to the docs
warning users that they need to add a cast-to-text when writing
expressions involving blob columns.  Since comparing blob values is
obviously rare, this seems to me like an acceptable compromise.

Andy

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] blob vs. string in bound parameters

[John Stanton]

The method I wrote about earlier is part of a language binding.

I can show you the code if you are interested.

Andy Ross wrote:

Dennis Cote wrote:
 > The following log shows that sqlite does indeed distinguish
 > between a text field and a blob with the same content. It also
 > shows you a workaround. You simply need to cast your fields to
 > blobs before you compare them to a variable that is bound to a
 > blob.

But I can't do that.  I'm writing a language binding, not an
application*, so changes to the query text are outside the realm
of what I can play with.  But the fact that it exists is actually
really helpful, because I can just write it into the docs as a
known gotcha without chucking my design entirely, and especially
because it focuses my earlier question better:

If [1] SQLite automatically casts between types as needed in most
cases (e.g. real to/from string) and [2] the cast() syntax
already works to convert between strings and blobs in
the "obvious" way, then: why is the cast automatic for numberic
values, but not for strings/blobs?  Isn't that a
non-orthogonality design flaw?

Andy

* ..er, well, I'm writing a language binding *and* an app at the
  moment.  But I'm much more concerned about the problems with
  the bindings.  As I mentioned earlier, I can already work
  around the issue by making sure all columns that will be
  queried with parameters are specified as blobs.

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] blob vs. string in bound parameters

[John Stanton]

Sqlite has declared types and actual types.  Both can be accessed 
through the API.


What I do is look at the declared type, which defines the data and then 
look at the actual type to determine how to process it.


Youn can declare the type to be anything you want.  A name such a GEORGE 
or INVOICE-DATE are each accpetable.


Andy Ross wrote:

I'm working with the sqlite3 bindings to my "nasal" languages
(http://plausible.org/nasal, if anyone is curious) and I'm having a
problem with type conversion.

Take a look at the attached sample code.  What it basically does is to
try to extract a row from a table with a bound parameter:

  "select val from tab where val = ?"

The val column in the table is declared as a "text" column.  But
because I'm working in a language that doesn't distinguish between
strings and byte arrays, I have to do the binding with
sqlite3_bind_blob() instead of sqlite3_bind_text().

Problem is, the *blob* value of "testval" does not, apparently, equal
the *text* value of "testval" in the database.

The workaround right now is to always define the columns as type blob,
never text.  But this strikes me as pretty unambiguously a bug.
Clearly an ASCII string should be equal in either representation: what
possible blob value of "testval" could there be except a 7 byte
string: {'t','e','s','t','v','a','l'}?

Any ideas, or have I misunderstood something?

Andy




#include 
#include 
#include 

// Initialize with: (note column "val" has type "text"):
//
//   rm -f test.db
//   echo "create table tab (val text);" | sqlite3 test.db
//   echo "insert into tab values ('testval');" | sqlite3 test.db
//
// Validate: (prints 'testval' as expected)
//
//   echo "select val from tab where val = 'testval';" | sqlite3 test.db
//
// Test: (note column "val" is bound with sqlite3_bind_blob()):
//
//   gcc -o test test.c -lsqlite3 && ./test
//
// Result:
//
// The blob apparently tests as not equal to the identitcal string,
// and the query returns zero rows.

#define DB "test.db"
#define QUERY "select val from tab where val = ?"
#define FIELD "testval"

#define PERR(msg) { printf(msg); printf("%s\n", sqlite3_errmsg(db)); }

int main()
{
int stat, cols, i;
sqlite3 *db;
sqlite3_stmt *stmt;
const char *tail;

if(sqlite3_open(DB, )) {
PERR("open failure\n");
return 1;
}

if(sqlite3_prepare(db, QUERY, strlen(QUERY), , )) {
PERR("prepare failure\n");
return 1;
}

// Calling bind_text() here works, bind_blob() does not:
if(sqlite3_bind_blob(stmt, 1, FIELD, strlen(FIELD), SQLITE_TRANSIENT)) {
PERR("bind failure\n");
return 1;
}

while((stat = sqlite3_step(stmt)) != SQLITE_DONE) {
cols = sqlite3_column_count(stmt);
for(i=0; i

Re: [sqlite] blob vs. string in bound parameters

[Andy Ross]

Dennis Cote wrote:
> The following log shows that sqlite does indeed distinguish
> between a text field and a blob with the same content. It also
> shows you a workaround. You simply need to cast your fields to
> blobs before you compare them to a variable that is bound to a
> blob.

But I can't do that.  I'm writing a language binding, not an
application*, so changes to the query text are outside the realm
of what I can play with.  But the fact that it exists is actually
really helpful, because I can just write it into the docs as a
known gotcha without chucking my design entirely, and especially
because it focuses my earlier question better:

If [1] SQLite automatically casts between types as needed in most
cases (e.g. real to/from string) and [2] the cast() syntax
already works to convert between strings and blobs in
the "obvious" way, then: why is the cast automatic for numberic
values, but not for strings/blobs?  Isn't that a
non-orthogonality design flaw?

Andy

* ..er, well, I'm writing a language binding *and* an app at the
  moment.  But I'm much more concerned about the problems with
  the bindings.  As I mentioned earlier, I can already work
  around the issue by making sure all columns that will be
  queried with parameters are specified as blobs.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] blob vs. string in bound parameters

[Andy Ross]

D. Richard Hipp wrote:
> There is good reason for this, actually.

But I'm not sorting, nor doing a < or > comparsion.  In fact none of
this has to do with comparison at all, but *identity*, which is a much
simpler test.  Are you really arguing that there are situations where
a string value (in arbitrary encoding -- whatever is stored) and a
blob cannot be tested for equality unambiguously?

> The second point is that TEXT can be converted from UTF-8, UTF-16be,
> and UTF-16le.

Sure.  But as I mentioned it's UTF-8.  Certainly it can't be a problem
to compare a blob and a string for bytewise equality?  Do you have a
real world use case for a situation where this actually causes
unwanted behavior?  Because:

> SQLite takes the approach of always making every BLOB larger than
> every TEXT string.  That is simple and unambiguous.

...but it's a disaster for people like me trying to actually take
advantage of the manifest typing feature.  I have an (IMHO really
pleasing) API that looks like this:

  sqlite.exec(db, "select * from X where Y = ?", ...bind params...)

This obviously presumes that types are convertable at runtime, which
in SQLite they *mostly* are; I can pass in the string "12" and get a
valid comparison to an integer or real value, etc...  But I *can't*
know when binding the parameter whether the context in which it will
be used is a blob or a string.  So what I do is use bind_blob()
universally for all string objects.  I was led to believe by your
documentation on manifest typing and by analogy to your automatic
numeric conversions that this sort of conversion was legal.

But if that does not work, then this whole API design is shot.  I'd
have to expose the *explicit* typing of all the values to the
user-level API, so they can make the call as to whether to query for a
blob or a string.  That seems like a mess to me, and very much *out*
of keeping with the manifest typing philosophy.

Seriously: what's wrong with just (1) converting a string to a blob by
exposing the literal byte in whatever encoding it was stored in, and
(2) converting a blob to a string by interpreting the bytes literally
in the current "pragma encoding" environment?  Sure, the user can
shoot herself in the foot with that, but it works unambiguously in the
only sane case: where the user-side string environment and the
database schema are written to use the same encoding.

Do you have any other suggestions, or is the clean/simple API choice
above just not something you want to suppot with SQLite?  It seems
like many other language bindings are going to have the same issue...
I think what I'll have to do in the interrim is default to bind_text()
instead of bind_blob().  Does that work if the data has embedded nuls?

Also, can you be more specific about exactly why you made this design
choice?  This seems to me like a situation where simplicity of
implementation (punting on the issue of comparing strings and blobs)
got mixed up with simplicity of design (making user-visible type
conversion as automatic and error-proof as possible).  I've hit that
on a bunch of occasions with Nasal, and sometimes it helps to step
back a bit and look at the problem from the user's perspective.

Andy



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] blob vs. string in bound parameters

[Dennis Cote]

Andy Ross wrote:

Andy Ross wrote:

Problem is, the *blob* value of "testval" does not, apparently, equal
the *text* value of "testval" in the database.


Just to head off the inevitable reply: no, this isn't an encoding issue.
The result of "pragma encoding" on the database file is UTF-8, and 
obviously

the transformation between UTF-8 and an ASCII string is the identity
transform.


Andy,

You have discovered a "quirk" in SQLite. I'm not sure if it should be 
called a bug, or not.


The following log shows that sqlite does indeed distinguish between a 
text field and a blob with the same content. It also shows you a 
workaround. You simply need to cast your fields to blobs before you 
compare them to a variable that is bound to a blob.


   sqlite> create table t (a text);
   sqlite> insert into t values (X'74657374');
   sqlite> insert into t values ('test');
   sqlite> select * from t;
   test
   test
   sqlite> select count(*) from t where a = X'74657374';
   1
   sqlite> select count(*) from t where a = X'7465737400';
   0
   sqlite> select count(*) from t where a = 'test';
   1
   sqlite> select typeof(a) from t;
   blob
   text
   sqlite> select count(*) from t where cast(a as blob) = 'test';
   0
   sqlite> select count(*) from t where cast(a as blob) = X'74657374';
   2

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] blob vs. string in bound parameters

[drh]

Andy Ross <[EMAIL PROTECTED]> wrote:
> 
> Problem is, the *blob* value of "testval" does not, apparently, equal
> the *text* value of "testval" in the database.
> 

There is good reason for this, actually.  TEXT values
can sort in many different orders, depending on what
collating sequence is used.  Different languages use
different collating sequences.  Some languages have
multiple collating sequences.  I'm told, for example,
that there are two common ways to short Chinese and
several other less common ways.  The database needs
to be able to handle all of these.  But BLOBs always
sort in memcmp() order.

The second point is that TEXT can be converted from
UTF-8, UTF-16be, and UTF-16le.  So, for example, if
the a user stores a UTF-16le string on an x86 machine,
the moves the database file over to a ppc machine and
reads out the same string, it comes out as UTF-16be.
Or if one user writes a UTF-8 string and another requests
a UTF-16 string, the conversion is automatic.  But 
with a BLOB, no conversions ever occur.

Because TEXT does not have a uniform representation,
and because the sort order can be different for each
column, it is difficult to know how to compart TEXT
and BLOBs.  So SQLite takes the approach of always
making every BLOB larger than every TEXT string.
That is simple and unambiguous.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: bind with select?

[Dennis Cote]

Dave Dyer wrote:

You don't appear to be using BIND in the manner I was hoping for.  You're
using BIND to replace variables in the query.  I want to use BIND 
(or something like it) eliminate the need for callback functions

to consume the results of a select.

--

At 05:42 PM 10/20/2006, Jay Sprenkle wrote:
  

On 10/16/06, Dave Dyer <[EMAIL PROTECTED]> wrote:


I can't find an example, but it seems like there ought to be
syntax to use bind to inline selection variables, instead of
having to have a callback function.

Something like:

char *forval,*barval;
sqlite_prepare(db,"select ?foo,?bar from table");
sqlite_bind("?foo",);
sqlite_bind("?bar",);

while () { sqlite_step()
   // foo and var bound to current values
   }

can someone point me to an example, or good documentation?
  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Dave,

What you are looking for can't be done directly using the C API 
functions. As far as I know, the only language binding for SQLite that 
does what you are looking for directly is Richard's TCL binding.


You can do the following using the C API functions.

   sqlite3_stmt* s;
   sqlite3_prepare(db,"select foo, bar from table", -1, s, NULL);

   while (sqlite_step(s) == SQLITE_ROW) {
   char* fooval = sqlite3_column_text(s, 1);
   char* barval = sqlite3_column_text(s, 2);
   // foo and var bound to current values

   }

   sqlite3_finalize(s);

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] blob vs. string in bound parameters

[Andy Ross]

Andy Ross wrote:

Problem is, the *blob* value of "testval" does not, apparently, equal
the *text* value of "testval" in the database.


Just to head off the inevitable reply: no, this isn't an encoding issue.
The result of "pragma encoding" on the database file is UTF-8, and obviously
the transformation between UTF-8 and an ASCII string is the identity
transform.

Andy



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] blob vs. string in bound parameters

[Andy Ross]

I'm working with the sqlite3 bindings to my "nasal" languages
(http://plausible.org/nasal, if anyone is curious) and I'm having a
problem with type conversion.

Take a look at the attached sample code.  What it basically does is to
try to extract a row from a table with a bound parameter:

  "select val from tab where val = ?"

The val column in the table is declared as a "text" column.  But
because I'm working in a language that doesn't distinguish between
strings and byte arrays, I have to do the binding with
sqlite3_bind_blob() instead of sqlite3_bind_text().

Problem is, the *blob* value of "testval" does not, apparently, equal
the *text* value of "testval" in the database.

The workaround right now is to always define the columns as type blob,
never text.  But this strikes me as pretty unambiguously a bug.
Clearly an ASCII string should be equal in either representation: what
possible blob value of "testval" could there be except a 7 byte
string: {'t','e','s','t','v','a','l'}?

Any ideas, or have I misunderstood something?

Andy
#include 
#include 
#include 

// Initialize with: (note column "val" has type "text"):
//
//   rm -f test.db
//   echo "create table tab (val text);" | sqlite3 test.db
//   echo "insert into tab values ('testval');" | sqlite3 test.db
//
// Validate: (prints 'testval' as expected)
//
//   echo "select val from tab where val = 'testval';" | sqlite3 test.db
//
// Test: (note column "val" is bound with sqlite3_bind_blob()):
//
//   gcc -o test test.c -lsqlite3 && ./test
//
// Result:
//
// The blob apparently tests as not equal to the identitcal string,
// and the query returns zero rows.

#define DB "test.db"
#define QUERY "select val from tab where val = ?"
#define FIELD "testval"

#define PERR(msg) { printf(msg); printf("%s\n", sqlite3_errmsg(db)); }

int main()
{
int stat, cols, i;
sqlite3 *db;
sqlite3_stmt *stmt;
const char *tail;

if(sqlite3_open(DB, )) {
PERR("open failure\n");
return 1;
}

if(sqlite3_prepare(db, QUERY, strlen(QUERY), , )) {
PERR("prepare failure\n");
return 1;
}

// Calling bind_text() here works, bind_blob() does not:
if(sqlite3_bind_blob(stmt, 1, FIELD, strlen(FIELD), SQLITE_TRANSIENT)) {
PERR("bind failure\n");
return 1;
}

while((stat = sqlite3_step(stmt)) != SQLITE_DONE) {
cols = sqlite3_column_count(stmt);
for(i=0; i

Re: [sqlite] Re: bind with select?

[John Stanton]

Use sqlite3_step.

Dave Dyer wrote:

You don't appear to be using BIND in the manner I was hoping for.  You're
using BIND to replace variables in the query.  I want to use BIND 
(or something like it) eliminate the need for callback functions

to consume the results of a select.

--

At 05:42 PM 10/20/2006, Jay Sprenkle wrote:


On 10/16/06, Dave Dyer <[EMAIL PROTECTED]> wrote:


I can't find an example, but it seems like there ought to be
syntax to use bind to inline selection variables, instead of
having to have a callback function.

Something like:

char *forval,*barval;
sqlite_prepare(db,"select ?foo,?bar from table");
sqlite_bind("?foo",);
sqlite_bind("?bar",);

while () { sqlite_step()
  // foo and var bound to current values
  }

can someone point me to an example, or good documentation?




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: bind with select?

[Igor Tandetnik]

Dave Dyer <[EMAIL PROTECTED]> wrote:

You don't appear to be using BIND in the manner I was hoping for.
You're
using BIND to replace variables in the query.  I want to use BIND
(or something like it) eliminate the need for callback functions
to consume the results of a select.


Use sqlite3_prepare, sqlite3_step, sqlite3_column_* to iterate over the 
resultset. You don't need any callbacks.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: bind with select?

[Dave Dyer]

You don't appear to be using BIND in the manner I was hoping for.  You're
using BIND to replace variables in the query.  I want to use BIND 
(or something like it) eliminate the need for callback functions
to consume the results of a select.

--

At 05:42 PM 10/20/2006, Jay Sprenkle wrote:
>On 10/16/06, Dave Dyer <[EMAIL PROTECTED]> wrote:
>>
>>I can't find an example, but it seems like there ought to be
>>syntax to use bind to inline selection variables, instead of
>>having to have a callback function.
>>
>>Something like:
>>
>> char *forval,*barval;
>> sqlite_prepare(db,"select ?foo,?bar from table");
>> sqlite_bind("?foo",);
>> sqlite_bind("?bar",);
>>
>> while () { sqlite_step()
>>// foo and var bound to current values
>>}
>>
>>can someone point me to an example, or good documentation?


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] any data access and retrieval engine?

[John Stanton]

Sarah, email me off forum at [EMAIL PROTECTED]

BTW, all my programs are ANSI standard plain vanilla C.

Regards,

John S

Sarah wrote:
> Hi, John Stanton
> 
> I really really appreciate your warm help.
> That's great if you can send me the codes of B tree and B+ tree. 
> Many thanks in advance.
> 
> My requirements for data access are as follows:
> -all the data are stored in non-volatile memory instead of volatile memory
> -the footprint of the DARE should be better less than 100KB
> -when executing, the memory occupation should be better less than 20KB
> -no need for relational access, just key-value retrieval is ok
> -all the create, insert, update work can be done outside, however, pretty 
> fast retrieval is needed
> 
> If there is some open-source DARE(as excellent as SQLite) suitable for my 
> platform, that will be great.
> Orelse, I would try to write a simple one.
> 
> Sarah
> 
> 
> - Original Message - 
> From: "John Stanton" <[EMAIL PROTECTED]>
> To: 
> Sent: Monday, October 23, 2006 5:54 AM
> Subject: Re: [sqlite] any data access and retrieval engine?
> 
> 
> 
>>Clay Dowling wrote:
>>
>>>Sarah wrote:
>>>
>>>
Hi,all

First of all, I want to thank all the guys on this mailing list for their 
warm help.

After 1 more month of work, I finally make SQLite work on my embedded 
environment. SQLite is really great! Many thanks,Drh.

But, due to the extremely heavy hardware constraints, I have to give up 
SQLite finally. 

So I'm trying to find a much simpler data access and retrieval engine. 

Could anyone give me some help on this issue?(some guidance on how to make 
a DARE or is there any open-source one available?) 

thanks in advance. 
>>>
>>>
>>>The Berkeley DB engine and it's related engines might be suitable for
>>>your situation.  They don't give relational access, but they do give
>>>fast key=>value retreival and that might be suitable.  The SleepyCat DB
>>>engine from SleepyCat Software is probably the best, but for a
>>>commercial application the licensing fees mean that you have to be well
>>>funded and expect a good return on the product.
>>>
>>>Clay Dowling
>>
>>Berkely DB is still quite bloated.  What do you require for data access?
>>For an embedded system you might find something which matches your
>>needs very well and has a tiny footprint.
>>
>>I can give you some B* Tree code which is suitable for a high
>>performance simple and lightweight embedded application or some AVL tree
>>code which would suit a simpler smaller scale memory resident embedded
>>data access application.  You would have to adapt it to your
>>application, but could expect to get your database access in 20K or less
>>of executable image.  Of course you have no SQL.
>>
>>-
>>To unsubscribe, send email to [EMAIL PROTECTED]
>>-
> 
>>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite Performance Issues

[John Stanton]

I built and use an application server which embeds Sqlite and processes 
web traffic.  It is multi-threaded and can handle very many connections. 
 It is very fast because it uses no IPC channels or process creation. 
It caches threads and reuses them rather than creating and killing them. 
 It can use sendfile/TransmitFile access to the internet with maximum 
efficiency.  It sounds to be similar to what you propose.


The simplicity of Sqlite does not come free.  You have to be aware of
the way it single streams access.  Only one user can write to the 
database at a time, but many users can read.  You have to design your 
application and synchronization skilfully to handle that constraint. 
You may consider breaking up your data into seperate databases.


If your design handles the synchronization well your emedded Sqlite 
database will handily outperform MySql or PostgreSQL.  If you don't want 
to be involved at that design level, use PostgreSQl.


James Mills wrote:

Hi Folks,

I'm wanting to use SQLite in an embedded web application
that will serve as a proxy and possible serve up many
connections at once. I'm talking here of high-traffic
through this web app.

Question is, how will SQLite perform under these kinds
of conditions ? I've been speaking to a few of the Trac
developers, and they inform me that SQLite uses a global
writer meaning that only a single thread can write at
any one point in time. This would explain why trac-hacks.org
is so slow at times to load up.

Thoughts/Comments ?

(btw) I really don't want to be using a server-client
rdbms such as MySQL or Postgresql because of the small
overheads in having a server.

cheers
James




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] IF EXISTS when using DROP TABLE

[Alexandre Guion]

You can use the PRAGMA query to test if a table exists or not.

"PRAGMA table_info(`tablename`)"

--
Alex Guion
Software Engineer
OrbNetworks, Inc.
www.orb.com


[EMAIL PROTECTED] wrote:


Thank you.  My version does not.  I had used one of the archives to search for 
this issue, but the thread I found did not have an answer to the question.

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

-- Original message --
From: "Adriano Ferreira" <[EMAIL PROTECTED]>
 


On 10/23/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
   

If I use the syntax "DROP TABLE tablename" everything is good, except of 
 

course I get a "no such table" error if the table does not exist.  However, if I 
use the syntax "DROP TABLE IF EXISTS tablename", then I get the following error: 
'near "EXISTS": syntax error'.  Anyone have a thought as to what's going on?  Is 
this a known issue?


It looks like the same issue on the thread "Trouble with ALTER
TABLE/ADD", answered by drh. Are you sure your version of SQLite
supports "DROP TABLE IF EXISTS"?

The URL http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql says

   IF EXISTS function, e.g. "DROP TABLE IF EXISTS temp;"

   Added in 3.3

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

   





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

 

Re: [sqlite] IF EXISTS when using DROP TABLE

[epankoke]

Thank you.  My version does not.  I had used one of the archives to search for 
this issue, but the thread I found did not have an answer to the question.

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

 -- Original message --
From: "Adriano Ferreira" <[EMAIL PROTECTED]>
> On 10/23/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > If I use the syntax "DROP TABLE tablename" everything is good, except of 
> course I get a "no such table" error if the table does not exist.  However, 
> if I 
> use the syntax "DROP TABLE IF EXISTS tablename", then I get the following 
> error: 
> 'near "EXISTS": syntax error'.  Anyone have a thought as to what's going on?  
> Is 
> this a known issue?
> 
> It looks like the same issue on the thread "Trouble with ALTER
> TABLE/ADD", answered by drh. Are you sure your version of SQLite
> supports "DROP TABLE IF EXISTS"?
> 
> The URL http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql says
> 
> IF EXISTS function, e.g. "DROP TABLE IF EXISTS temp;"
> 
> Added in 3.3
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] IF EXISTS when using DROP TABLE

[Adriano Ferreira]

On 10/23/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

If I use the syntax "DROP TABLE tablename" everything is good, except of course I get a "no such 
table" error if the table does not exist.  However, if I use the syntax "DROP TABLE IF EXISTS 
tablename", then I get the following error: 'near "EXISTS": syntax error'.  Anyone have a thought as to 
what's going on?  Is this a known issue?


It looks like the same issue on the thread "Trouble with ALTER
TABLE/ADD", answered by drh. Are you sure your version of SQLite
supports "DROP TABLE IF EXISTS"?

The URL http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql says

   IF EXISTS function, e.g. "DROP TABLE IF EXISTS temp;"

   Added in 3.3

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] IF EXISTS when using DROP TABLE

[epankoke]

If I use the syntax "DROP TABLE tablename" everything is good, except of course 
I get a "no such table" error if the table does not exist.  However, if I use 
the syntax "DROP TABLE IF EXISTS tablename", then I get the following error: 
'near "EXISTS": syntax error'.  Anyone have a thought as to what's going on?  
Is this a known issue?

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Trouble with ALTER TABLE/ADD

[Isaac Raway]

Thank, I will look at that (away from my dev machine for the day).

One other related question, are there plans to expand the functionality of
ALTER TABLE? I am working on a feature that could benefit greatly from
REMOVE/RENAME COLUMN. As it stands, I am going to have to simulate this by
using generic column names and mapping them to a list of the "actual" names.
It would be *very* nice to see these features added to sqlite before I
finish this feature, but I imagine this has been requested before...

Isaac


On 10/23/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


"Isaac Raway" <[EMAIL PROTECTED]> wrote:
>
> ALTER TABLE topic ADD COLUMN type_id integer;
>
> This works fine when I run it on the sqlite3 command line, but fails in
the
> Delphi units. Any thoughts? Has anyone ahd trouble running ALTER TABLE
from
> the Delphi bindings?
>

Perhaps the delphi code is statically linked against an
older version of SQLite.  ADD COLUMN was added in version
3.2.0.  You can find what version delphi uses by executing

   SELECT sqlite_version();

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
Isaac Raway
Entia non sunt multiplicanda praeter necessitatem.

http://blueapples.org - blog
http://stonenotes.com - personal knowledge management

Re: [sqlite] Extra functions - New Project?

[Mikey C]

I entirely agree.  I had the functions coded because I needed them for my own
project.  I never intended to do the other 85% of the work required to make
them a supported part of SQLite.  



drh wrote:
> 
> Mikey C <[EMAIL PROTECTED]> wrote:
>> 
>> I sent the source code to DRH with the extra functions.  I don't myself
>> have
>> the time now to incorporate the extra functions into SQLite.
>> 
> 
> Writing code a chunk of code is only a small fraction of
> the work needed to support the code in a maintained product
> such as SQLite.  Writing the code is, in fact, the easy
> part.  After the code is written, somebody then has to 
> develop regression tests that provide near 100% code
> coverage.  The code has to be documented.  Then it has
> to be maintained for years.  By my estimate, writing code
> is perhaps 15% of the total work.
> 
> The code for the extra functions was submitted to me with
> the promise that the author would provide no help in completing
> the work of integration.  In other words, the author did
> about 15% of the work and left the other 85% to me.  Such
> a submission is often referred to as a "drive by patch".
> 
> I'm happy to have help on SQLite.  But if you contribute
> code, you should finish the job.  That means providing test
> cases that give 100% code coverage, documentation, and being
> available to support your code for years in the future.
> If you write a bunch of code and toss it over the wall,
> then please do not be disappointed if nobody picks it up.
> 
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Extra-functions---New-Project--tf1674436.html#a6956723
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Trouble with ALTER TABLE/ADD

[drh]

"Isaac Raway" <[EMAIL PROTECTED]> wrote:
> 
> ALTER TABLE topic ADD COLUMN type_id integer;
> 
> This works fine when I run it on the sqlite3 command line, but fails in the
> Delphi units. Any thoughts? Has anyone ahd trouble running ALTER TABLE from
> the Delphi bindings?
> 

Perhaps the delphi code is statically linked against an
older version of SQLite.  ADD COLUMN was added in version
3.2.0.  You can find what version delphi uses by executing

   SELECT sqlite_version();

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Trouble with ALTER TABLE/ADD

[Isaac Raway]

Hi, this is my first question on this list. First, a brief introduction:
I've been using sqlite for about the past year or so and so far I'm very
happy with it. As far as databases, I have experience with MySQL, SQL Server
and of course Access. My language skills include Delphi, PHP, Tcl, and VB
(top 4).

Okay, my question...I have this table in the first version of an application
I have created:

CREATE TABLE topic(id integer primary key, title string, namespace integer,
content blob, meta blob, x integer, y integer, w integer, h integer);

In order to upgrade existing databases I run various commands to add the
needed tables and store the current version of the database

ALTER TABLE topic ADD COLUMN type_id integer;

This works fine when I run it on the sqlite3 command line, but fails in the
Delphi units. Any thoughts? Has anyone ahd trouble running ALTER TABLE from
the Delphi bindings?

--
Isaac Raway
Entia non sunt multiplicanda praeter necessitatem.


--
Isaac Raway
Entia non sunt multiplicanda praeter necessitatem.

http://blueapples.org - blog
http://stonenotes.com - personal knowledge management

Re: [sqlite] SQLite Performance Issues

[Christian Smith]

James Mills uttered:


Hi Richard,

When I mean high-traffic I would imagine more than 1 hit/s.

I do want to clear something up though (if you could):

If a site using sqlite takes 700ms to load and there are
two simultaneous (as simultaneous as one can get) hits
to the site, say user A and user B. What happens ?

Does user A get the site displayed while user B waits ?



If you have two simultaneous sessions loading, so long as the sessions are 
read only, they will open in parallel (assuming you have a threaded 
server.)


This is probably not a SQLite issue, rather a webapp issue.




Obviously this is a really simple scenario and with high
volume websites this could become a problem.

Also can you suggest any tips and things to think about
when designing a web app with SQLite ?



What are you using the database for? Caching? Session state management?

If you want per-session writable data, you might want to partition 
session data from global data, and have the session data in separate 
per-session databases. The webapp opens the global database, which 
hopefully will be mostly read-only, and then ATTACHes a per-session 
database for session updates.


If your global data is going to be the write bottleneck, then you may be 
boned and will have to look at something like PostgreSQL. One possible way 
around this is to use group commit to marshal multiple sessions' writes 
to the database in a single transaction.


For further suggestions, you'll have to let people know exactly how you 
intend to use the database.






Thanks again,

cheers
James




Christian


--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] insertion time

[Dennis Cote]

Lloyd wrote:

Hi,
  How much time it may take to "insert or ignore into query" for 30
lakhs times. It adds more than 130 sec overhead compared to the 20 sec
of execution time. All these are done inside a single transaction. And
these insertions are made on three tables (on each table 10 lakhs) with
a combined primary key of text and integer.

Is this the expected performance? Can I improve it anyway? (I am using
the wxSQLite wrapper class)

Thanks,
  Lloyd.


__
  

All,

FYI, a lakh is defined as:

A lakh (also spelled lac or laksha) is a unit in a traditional number 
system, still widely used in India and Bangladesh, equal to a hundred 
thousand. A hundred lakhs make a crore.


So Lloyd is doing 3M insert or ignores.

Lloyd,

What do you mean by "130 seconds of overhead"?

Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite Performance Issues

[James Mills]

Hrmm also a couple of other things...

In order to make a decision, I would need some way of
running tests and simulations so I can come up with some
numbers. Then scale that up and use it as an indicator
for our decision. Do you have any tools that'll help with
this ?

cheers
James

-- 
--
-"Problems are Solved by Method"
-
- James Mills <[EMAIL PROTECTED]>
- HomePage: http://shortcircuit.net.au/~prologic/
- IRC: irc://shortcircuit.net.au#se

Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite Performance Issues

[James Mills]

Hi Richard,

I appreciate your feedback on the matter. I myself have
used SQLite in many of my applications in the past for quite
some years now. Most of them do indeed only write to
or read from teh database for only fractions of a second.

When I mean high-traffic I would imagine more than 1 hit/s.

I do want to clear something up though (if you could):

If a site using sqlite takes 700ms to load and there are
two simultaneous (as simultaneous as one can get) hits
to the site, say user A and user B. What happens ?

Does user A get the site displayed while user B waits ?

Obviously this is a really simple scenario and with high
volume websites this could become a problem.

Also can you suggest any tips and things to think about
when designing a web app with SQLite ?

Thanks again,

cheers
James

-- 
--
-"Problems are Solved by Method"
-
- James Mills <[EMAIL PROTECTED]>
- HomePage: http://shortcircuit.net.au/~prologic/
- IRC: irc://shortcircuit.net.au#se

Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] insertion time

[Lloyd]

Hi,
  How much time it may take to "insert or ignore into query" for 30
lakhs times. It adds more than 130 sec overhead compared to the 20 sec
of execution time. All these are done inside a single transaction. And
these insertions are made on three tables (on each table 10 lakhs) with
a combined primary key of text and integer.

Is this the expected performance? Can I improve it anyway? (I am using
the wxSQLite wrapper class)

Thanks,
  Lloyd.


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite Performance Issues

[drh]

James Mills <[EMAIL PROTECTED]> wrote:
> Hi Folks,
> 
> I'm wanting to use SQLite in an embedded web application
> that will serve as a proxy and possible serve up many
> connections at once. I'm talking here of high-traffic
> through this web app.
> 
> Question is, how will SQLite perform under these kinds
> of conditions ? I've been speaking to a few of the Trac
> developers, and they inform me that SQLite uses a global
> writer meaning that only a single thread can write at
> any one point in time. This would explain why trac-hacks.org
> is so slow at times to load up.
> 
> Thoughts/Comments ?
> 
> (btw) I really don't want to be using a server-client
> rdbms such as MySQL or Postgresql because of the small
> overheads in having a server.
> 

No question about it - a client/server database will give you
much better concurrency.  The price of that concurrency is,
of course, increased complexity and administrative overhead.
There is a tradeoff here.  You need to decide what is most
important to you - easy of setup and operation or high
concurrency.

I do not know why the Trac people are having problems with
SQLite.  I have not looked at their code.  But I find it
hard to believe that a bug tracking website could exceed the
concurrency capabilities of SQLite.  SQLite should have
plenty enough concurrency to support any bug database that
human bug-fixers are able to keep up with.

What do you mean by "high-traffic"?  

The SQLite website itself runs on a 200MHz slice of a virtual
server and handles an average of 1 hit/second on weekdays.  It
is backed by a version 2 SQLite database and seems to work just
fine.  Requests often take multple seconds to fulfill, so at
any given moment in time, there are multiple requests pending.
The trick is, not every request uses the database.  And requests
that write to the database (Wiki pages updates, creating new
tickets, etc.) are fewer still.  So SQLite has no problem handling
the load even on a rather slow virtual server slice.

The SQLite website was written by me (of course) and I understand
the concurrency issues in SQLite better than most.  So I was
careful to make sure that the website does not hold exclusive
locks on the database for more than a fraction of a second.
The fact that the Trac team has problems with SQLite suggests
to me that they have not been nearly so careful.  If you want
the freedom to do long transactions without having to worry about
locking out other clients, and you are not concerned about ease
of installation and administration (and the Trac team clearly is
not concerned about ease of administration from what I have seen)
then you should definitely consider a client/server database 
instead of SQLite.  But if you need a self-contained application
that does not require any database administration and you are
willing to take the extra care in coding your app that such a
database requires, then SQLite could well be your best choice.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Extra functions - New Project?

[drh]

Mikey C <[EMAIL PROTECTED]> wrote:
> 
> I sent the source code to DRH with the extra functions.  I don't myself have
> the time now to incorporate the extra functions into SQLite.
> 

Writing code a chunk of code is only a small fraction of
the work needed to support the code in a maintained product
such as SQLite.  Writing the code is, in fact, the easy
part.  After the code is written, somebody then has to 
develop regression tests that provide near 100% code
coverage.  The code has to be documented.  Then it has
to be maintained for years.  By my estimate, writing code
is perhaps 15% of the total work.

The code for the extra functions was submitted to me with
the promise that the author would provide no help in completing
the work of integration.  In other words, the author did
about 15% of the work and left the other 85% to me.  Such
a submission is often referred to as a "drive by patch".

I'm happy to have help on SQLite.  But if you contribute
code, you should finish the job.  That means providing test
cases that give 100% code coverage, documentation, and being
available to support your code for years in the future.
If you write a bunch of code and toss it over the wall,
then please do not be disappointed if nobody picks it up.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: Re: [sqlite] database corrouption during power failure

[jayanth KP]

Hi Dennis Cote,

   But how do we read the current  synchronous value using C interface. Plz 
reply.

Regards
Jayanth


On Mon, 16 Oct 2006 Dennis Cote wrote :
>jayanth KP wrote:
>>Thanks for ur reply. I searched in the whole source code i could not find 
>> SQLITE_NO_SYNC flag. 
>>Also i tried the following in my c code, after sqlite_open() was success.
>>sqlite_exec(pDbHandle,"PRAGMA synchronous=FULL;", NULL, NULL, );
>>
>>Even this did not seem to work. How do i check if this PRAGMA is working fine 
>>from my code.
>>
>>
>> 
>
>The pragma commands are documented at http://www.sqlite.org/pragma.html. To 
>check the current value simply execute a "pragma synchronous" command. The 
>current setting will be returned. In your case it should be 2 for FULL 
>synchronous operation.
>
>The SQLITE_NO_SYNC flag is a define that can be passed to the compiler while 
>buiding your code. Look in your makefile or your compiler's options setting 
>dialog for -DSQLITE_NO_SYNC.
>
>HTH
>Dennis Cote
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
>

Re: [sqlite] any data access and retrieval engine?

[Sarah]

Hi, John Stanton

I really really appreciate your warm help.
That's great if you can send me the codes of B tree and B+ tree. 
Many thanks in advance.

My requirements for data access are as follows:
-all the data are stored in non-volatile memory instead of volatile memory
-the footprint of the DARE should be better less than 100KB
-when executing, the memory occupation should be better less than 20KB
-no need for relational access, just key-value retrieval is ok
-all the create, insert, update work can be done outside, however, pretty fast 
retrieval is needed

If there is some open-source DARE(as excellent as SQLite) suitable for my 
platform, that will be great.
Orelse, I would try to write a simple one.

Sarah


- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>
To: 
Sent: Monday, October 23, 2006 5:54 AM
Subject: Re: [sqlite] any data access and retrieval engine?


> Clay Dowling wrote:
>> Sarah wrote:
>> 
>>>Hi,all
>>>
>>>First of all, I want to thank all the guys on this mailing list for their 
>>>warm help.
>>>
>>>After 1 more month of work, I finally make SQLite work on my embedded 
>>>environment. SQLite is really great! Many thanks,Drh.
>>>
>>>But, due to the extremely heavy hardware constraints, I have to give up 
>>>SQLite finally. 
>>>
>>>So I'm trying to find a much simpler data access and retrieval engine. 
>>>
>>>Could anyone give me some help on this issue?(some guidance on how to make a 
>>>DARE or is there any open-source one available?) 
>>>
>>>thanks in advance. 
>> 
>> 
>> The Berkeley DB engine and it's related engines might be suitable for
>> your situation.  They don't give relational access, but they do give
>> fast key=>value retreival and that might be suitable.  The SleepyCat DB
>> engine from SleepyCat Software is probably the best, but for a
>> commercial application the licensing fees mean that you have to be well
>> funded and expect a good return on the product.
>> 
>> Clay Dowling
> Berkely DB is still quite bloated.  What do you require for data access?
> For an embedded system you might find something which matches your
> needs very well and has a tiny footprint.
> 
> I can give you some B* Tree code which is suitable for a high
> performance simple and lightweight embedded application or some AVL tree
> code which would suit a simpler smaller scale memory resident embedded
> data access application.  You would have to adapt it to your
> application, but could expect to get your database access in 20K or less
> of executable image.  Of course you have no SQL.
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>