Re: [sqlite] Trigger Steps

2008-03-25 Thread drh
"Noah Hart" <[EMAIL PROTECTED]> wrote:
> The documentation for Triggers defines a trigger step as follows:
> 
> trigger-step ::=  update-statement | insert-statement |
> delete-statement | select-statement
> 
> What would be an example of the select-statement?
> 

SELECT statements are useful for the side-effects of functions
that the SELECT statement might call.  For example:

  CREATE TRIGGER r1 AFTER UPDATE ON t1 BEGIN
SELECT raise(ABORT, 'x is too large') WHERE x>5;
  END;

You might also have application-defined functions that cause
side effects that you want to issue:

  CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
SELECT notify_other_process(new.x);
  END;

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

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


Re: [sqlite] Problems Compiling Sqlite

2008-03-25 Thread drh
Jus GoodFun <[EMAIL PROTECTED]> wrote:
>
>   I tried the DSQLITE_MUTEX_NOOP and then a rebuild but still 
> got the following compile errors, I'm sure I'm missing something
> simple...

Try compiling with -DSQLITE_THREADSAFE=0

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

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


[sqlite] Lit

2008-03-24 Thread drh
"Rob Richardson" <[EMAIL PROTECTED]> wrote:
> I'm thinking whether this is a memory leak or not sort of depends on
> your definition.  If a process is designed to remain open for long
> periods of time with little activity, and it ends up taking up 1
> gigabyte of memory, that looks an awful lot like a leak to me.  There
> are likely to be at least three instances of this application running,
> and after they all run for a month, they're likely to be consuming 5
> gigabytes of memory.  This is not acceptable.  If SQLite's sorted
> query is taking up 2.5 megabytes of memory every time this piece of
> the application is invoked, I need to know how to ensure that that
> memory is released.

The test harness for SQLite counts the number of mallocs and frees
and prints an error if the two do not match.  This happens every
time we run a test.  We also run the test suite through valgrind 
prior to each release. The test scripts provide close to 100% test 
coverage.  About 70% of the SQLite source code is devoted to testing.

Because of the extensive testing outlined above, memory leaks in
SQLite have never been a problem.  Historically, whenever somebody
comes forward with claims of memory leaks in SQLite, it generally
works out to be memory leaks in their application.

Based on prior experience and our extensive tests, claims that
SQLite leaks 2.5MB of RAM every time it sorts are generally met
with great skepticism.  I won't say that it is impossible that
SQLite is leaking memory, though it does seem unlikely.  Certainly
the claim that SQLite leaks 2.5MB on every sort is an extraordinary
one, and extradinary claims require extraordinary proof.

If you would like to submit a script or a short program that
appears to cause SQLite to leak memory, we will be happy to
look into the problem.  But until I see an actual demonstration
of the problem, I'm going to assume that the problem is really
outside of SQLite and pay the matter no further attention.

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

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


Re: [sqlite] performance under load

2008-03-24 Thread drh
"Sam Carleton" <[EMAIL PROTECTED]> wrote:
> My application is an apache based kiosk system that displays images.
> The SQLite database is used by PHP to track user info, who is logged
> in and what they have selected.  SQLite is NOT managing anything about
> the files.  I have a few customers that are running with 40 to 50
> kiosks.
> 
> >From the web site's "Appropriate Uses for SQLite" it says that "any
> site that gets fewer than 100K hits/day should work fine with SQLite".
>  I did the math and that looks to be around 69 hits a second.

Actually, it works out to about 69 hits per *minute* or
about 1.16 hits per second.


> Considering the SQLite database is used on page loads and when users
> tag images, but NOT when requesting images, I believe that 69 hits a
> second is more then fast enough.  Am I correct or is there something
> else I need to be taking into consideration?
> 
> As I am developing the software, is there anything I need to keep in
> mind to help optimize the database usage to achieve the million hits a
> day the "Appropriate Uses for SQLite"
> 
> Sam
> ___
> 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] Help with sqlite3_blob_open()

2008-03-24 Thread drh
"A.J.Millan" <[EMAIL PROTECTED]> wrote:
> Hello all:
> 
> Using Windows XP:
> 
> D:\Z\Zator5>sqlite3 zdb1
> SQLite version 3.5.4
> Enter ".help" for instructions
> sqlite> .tables
> AgEfHolder  AgEfemerAgVtHolder  AgVtos  Usr lnk
> AgEfIDt AgPdHolder  AgVtIDt Block   atm prm
> AgEfKlv AgPdIDt AgVtPre FreqUse blb
> 
> sqlite> .schema blb
> CREATE TABLE 'blb' (ID INTEGER PRIMARY KEY, Nm INTEGER);
> sqlite>
> 
> I'm having troubles with this pseudo code:
> 
> > begin transaction
> 
> > some successful operations on tables atm and blb
> 
> > sqlite3_blob* pBlob;
> 
> > // open Blob for read/write access
> > int res = sqlite3_blob_open (dbPtr, "D:\\Z\\zDB1", "blb", "nm", Id, 1, 
> > );
> 
> Here res = 1, and I get the following error:
> 
> SQL error: no such table: D:\Z\Zator5\zDB1.blb
> 

The "database name" is not the same thing as the name of the
file tht contains the database.  The database name is the name
assigned to the database when you ATTACH it.  Or, for the original
database, then name is "main".  Or for TEMP tables, the name is
"temp".

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

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


Re: [sqlite] Is it safe to ...

2008-03-24 Thread drh
"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> Hello,
> Is it safe to use this algorithm:
> 
> open_db
> fork()
> sql_do() // both parent and child executes sql statements
> close_db
> 
> I am not familiar with locking mechanism and I am afraid that if
> parent and child will use the same DB handlers it can cause of DB
> corruptions
> 

It is not safe to carry an open SQLite database connection
across a fork.  The documentation says this somewhere, IIRC,
but I don't remember exactly where.  I should probably state
this fact in the documentation for sqlite3_open()...

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

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


Re: [sqlite] Moving port from 3.5.1 to 3.5.7...

2008-03-21 Thread drh
Mark Spiegel <[EMAIL PROTECTED]> wrote:
> I'm looking to jump my code port forward from 3.5.1 to 3.5.7. 
> 
> Clearly I have some memory management work to do since 
> SQLITE_OMIT_MEMORY_ALLOCATION support has been dropped.  None of the 
> existing allocation implementations look acceptable so I'll have to roll 
> my own,

What do you need that none of mem[12345].c provide?

> but that looks pretty straight forward.
> 
> Two questions:
> 
> 1) Has the VFS interface changed from 3.5.1 to 3.5.7?

No.

> 2) Is the SQLITE_MUTEX_APPDEF #define still supported in the same manner 
> from 3.5.1 to 3.5.7?  (It appears that it is, but it never hurts to ask.)

Yes.


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

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


Re: [sqlite] New crashes with SQLite 3.5.7

2008-03-21 Thread drh
"Peter Weilbacher" <[EMAIL PROTECTED]> wrote:
> On Thu, 20 Mar 2008 17:52:21 UTC, drh wrote:
> 
> > Any additional information you can send, such as the size of
> > the database file at the point of failure, or the exact line
> > on which the problem occurs, will be appreciated.  (I know the
> > bug report gives a line-number, but line numbers shift from
> > one amalgamation to another, and I don't know which amalgamation
> > you are using - I want the text of the line on which the problem
> > occurs.)
> 
> I didn't know there were different amalgamations, I just picked the 
> tarball from the SQLite download page...

Well, there is the one on the download page, obviously.  But
you might also have pulled a different version of SQLite from
the CVS tree and built the amalgamation yourself.  Many users
will sometimes pull down a standard amalgamation but add their
on private header comment to explain where the code came from -
thus throwing off the line numbering.  If you can tell me that
the amalgamation in the bug report is *exactly* the same as the
one on the download.html page, that is all I need to know.

> 
> Looking at the call stacks of the crashes that Shawn pointed to, they 
> seem to be different between Windows and MacOSX. On Linux it doesn't 
> happen.

I spent some time writing a whole bunch of new tests for the
Bitvec module in SQLite.  It is a new module, so it immediately
comes under suspicion.  But the module is used on *every* transaction
and millions of transactions are run by the SQLite test suite and
they all work perfectly on all platforms.  And the Bitvec module
has its own set of unit tests which also work perfectly.  And
yesterday, I wrote up a whole new set of Bitvec tests and they
all work perfectly too.  I also observe that all the Bitvec tests,
and indeed all SQLite tests, work perfectly and report no errors
when running under valgrind.

But I also observe that line 22783 is common between the two
failures.  I'll focus on writing new tests to further exercise
that line of code, and reanalyze the code looking for problems.
If you have additional failures, please send me stack traces,
as this might provide additional clues.

> 
> Windows has this at the top:
> 0 sqlite3BitvecSet   mozilla/db/sqlite3/src/sqlite3.c:22783   
> return sqlite3BitvecSet(p->u.apSub[bin], i);
> 1 sqlite3BitvecSet   mozilla/db/sqlite3/src/sqlite3.c:22800   if( 
> aiValues[j] ) rc |= sqlite3BitvecSet(p, aiValues[j]);
> 2 sqlite3PagerDontRollback   mozilla/db/sqlite3/src/sqlite3.c:27282   
> sqlite3BitvecSet(pPager->pInJournal, pPg->pgno);
> [etc.]
> 
> For MacOSX it is:
> 0 sqlite3BitvecSetmozilla/db/sqlite3/src/sqlite3.c:22769   if( 
> p->iSize<=BITVEC_NBIT ){
> 1 sqlite3BitvecSetmozilla/db/sqlite3/src/sqlite3.c:22783   return 
> sqlite3BitvecSet(p->u.apSub[bin], i);
> 2 allocateBtreePage   mozilla/db/sqlite3/src/sqlite3.c:27282   
> sqlite3BitvecSet(pPager->pInJournal, pPg->pgno);
> 
> I pasted the code from the lines pointed to by the line number for the 
> topmost entries, perhaps that helps.
> 

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

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


Re: [sqlite] Malformed database schema with SQLite version > 3.5.x

2008-03-20 Thread drh
Kees Nuyt <[EMAIL PROTECTED]> wrote:
> 
> >I know I can't create an invalid view,
> >because SQLite refuses to create it,
> >but this seems a problem to me... could SQLite just open the database, and
> >complain only on the invalid views (i.e. for instance when I open the view
> >to query the data in it)?
> 
> If you define views the way you are supposed to define
> them, with SQL:
> 
> CREATE VIEW viewname AS 
>   SELECT .. 
> ;
> 
> they are validated against the schema immediately, and
> rejected if they are not valid. Views that refer to
> tables or columns that no longer exist won't give a
> schema error, but an error about what's missing when
> the view is executed, just like an invalid SELECT
> statement would do.
> 
> So, it is not a problem until you create your own
> problem by using undocumented, unsupported backdoors.
> Consider it the same as patching table pages by
> hex-editing the database file.
> 
> Feel free to do it, but don't expect a safety net.
> 

I think there must have been a bug in older versions of
SQLite that allowed some invalid VIEWs to be inserted into
the sqlite_master table.  I don't think Marco was messing
around with the writable_schema pragma in order to insert
the invalid VIEWs.  He just happened to have the misfortune
of using a version of SQLite that failed to completely validate
his input.

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

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


Re: [sqlite] New crashes with SQLite 3.5.7

2008-03-20 Thread drh
"Shawn Wilsher" <[EMAIL PROTECTED]> wrote:
> Hey all,
> 
> Mozilla has recently upgraded to sqlite 3.5.7, and we've suddenly
> gotten a lot of crashes.  The mozilla bug report is here:
> https://bugzilla.mozilla.org/show_bug.cgi?id=424163
> 
> We haven't looked into it to much, but I figured I'd point it out to
> so you were aware of it.
> 

The whole Bitvec thing is new to 3.5.7, but I thought it was
thoroughly tested.  I'll have a look.

Any additional information you can send, such as the size of
the database file at the point of failure, or the exact line
on which the problem occurs, will be appreciated.  (I know the
bug report gives a line-number, but line numbers shift from
one amalgamation to another, and I don't know which amalgamation
you are using - I want the text of the line on which the problem
occurs.)

A reproducible test case would, of course, be ideal.

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

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


Re: [sqlite] Sorting and Descending Index

2008-03-19 Thread drh
Martin Engelschalk <[EMAIL PROTECTED]> wrote:
> Hello All,
> 
> I have to select data from a large table (several million records) in 
> descending order and created an index for that purpose. However, sqlite 
> seems not to use this index for selecting the data.
> 
> In the documentation of the "create index" - statement, i found the 
> following sentence:
> 
> "Each column name can be followed by one of the "ASC" or "DESC" keywords 
> to indicate sort order, but the sort order is ignored in the current 
> implementation. Sorting is always done in ascending order."
> 
> However, the news for Version 3.3.0 of Jan 2006 says:
> 
> "Version 3.3.0 adds support for   DESC indices".
> 
> Is this a contradiction? Can sqlite use an index for order by ... desc - 
> clauses? Or am i doing sonething wrong?
> 

You have to enable decending indices using a pragma:

   PRAGMA legacy_file_format=OFF;

But a SELECT will use an index to sort the results in
decending order regardless of whether or not the index
is in decending order.  So that isn't your problem.
You are doing something else wrong.

Post your query, your table schema, and your index
definitions and we will have a look.

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

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


Re: [sqlite] Malformed database schema with SQLite version > 3.5.x

2008-03-19 Thread drh
MarcoN <[EMAIL PROTECTED]> wrote:
> Hello, everybody.
> 
> I have the following problem: I have an old project that uses a database
> created with an older SQLite library version.
> Now, since I updated SQLite to 3.5.5, I can't use the database anymore,
> because any query on the database tables returns:
> 
> SQLite error 11 - Malformed database schema - near ")": syntax error
> 

There is a syntax error in many of your VIEW definitions.
A typical example is _TestViewExtra where you have:

 MonthType IN (1, 2, )

There is an extra comma after the "2".  To fix this, I suggest
dropping all views from the database as follows:

   (1) Start the CLI:  sqlite3 baddatabase.db

   (2) Enter:  "PRAGMA writable_schema=ON".
   
   (3) Enter:  "select * from sqlite_master".  Ignore the error.

   (4) Enter:  "DELETE FROM sqlite_master WHERE type='view'"

   (5) Exit the CLI

Then go back and recreate your views using valid syntax.

There was apparently a bug in older versions of SQLite that allowed
the incorrect syntax to get through without raising an error.  That
bug has now been fixed, which made your database unreadable.

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

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


Re: [sqlite] How to get the minutes number only

2008-03-18 Thread drh
Joanne Pham <[EMAIL PROTECTED]> wrote:
> Hi All
> I ran the following statement:
> select datetime(startTime *60, 'unixepoch','-8 hours') , bytesIn from 
> wanPerfTable where appId = 30 and remoteWXId = 200;
> and below is my output.
>  
> 2007-12-03 11:00:00  20
>  2007-12-03 11:01:00  5
> 2007-12-03 11:02:00  100
> 2007-12-03 11:03:00 50
> 2007-12-03 11:58:00 50
> 
> 
> I would like to return only the number of minutes as below
> 0 20
> 1 5
> 2 100
> 3 50
> 58 25
>  

 strftime('%M', startTime*60, 'unixepoch', '-8 hours');

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

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


Re: [sqlite] SQL logic error or missing database in version 3.5.6 (Bug???)

2008-03-18 Thread drh
"Steve Topov" <[EMAIL PROTECTED]> wrote:
> Hello,
>  
> Recently I upgraded SQLite to version 3.5.6 and discovered that my
> program can’t work anymore with some database files. Sqlite3_open
> returns OK, but when I am trying to execute any SQL statement it returns
> “SQL logic error or missing database”. 
> For example sqlite3_prepare returns 1 instead of 0. Same for
> sqlite3_execute.
> I do not think it is my code that causes the problem because I can just
> switch SQLite dll from version 3.5.6 to version 3.3.5 and everything
> works fine. 
> I do not know the version of SQLite the database file in question was
> created with. Few database files created with version 3.3.5 does not
> have this problem.
> I tried to upgrade to the version 3.5.7 – same result. 
>  


SQLite versions 3.5.6 and 3.5.7 are suppose to be able to read
and write any database written by any prior version of SQLite
going back to version 3.0.0.

Perhaps you have a version 2 database file?

If you database file is named XYZ.db, what does this command
show you:

od -c XYZ.db | head

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

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


Re: [sqlite] Malformed database schema with SQLite version > 3.5.x

2008-03-18 Thread drh
MarcoN <[EMAIL PROTECTED]> wrote:
> Yes, of course I can send you the DB: the file is under 1MB, about 100K if
> compressed via .zip
> Can I send it to you via e-mail?
> Thanks very much for the support
> 

Please send the database directly to my email address shown below.
--
D. Richard Hipp <[EMAIL PROTECTED]>

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


Re: [sqlite] Malformed database schema with SQLite version > 3.5.x

2008-03-18 Thread drh
MarcoN <[EMAIL PROTECTED]> wrote:
> I actually don't know how to export it, because "SQLite Database browser"
> (that is able to open the database and execute the query) has no way to
> export it; SQLiteSpy will not open the database because it is compiled with
> a new SQLIte library version
> Maybe I can try to find an older version of SQLiteSpy on my archives, but I
> need more time for this...
> 

Can you send me the entire database file?  How big is it?
--
D. Richard Hipp <[EMAIL PROTECTED]>

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


Re: [sqlite] Meaning of the following code

2008-03-17 Thread drh
"Rich Rattanni" <[EMAIL PROTECTED]> wrote:
> All:
> I am able to consistently cause the following message during a integrity check
> 
> Page xxx is never used
> 
> This seems non-critical, since a vacuum clears this up.  If someone
> has the time could you explain the meaning (besides the obvious),
> causes, and dangers of receiving this message during a integrity
> check?
> 

It is not critical.

This can happen if another process appends something to the
end of your database.  It can also happen if the "ftruncate()"
does not work on your system.

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

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


Re: [sqlite] Malformed database schema with SQLite version > 3.5.x

2008-03-17 Thread drh
MarcoN <[EMAIL PROTECTED]> wrote:
> Hello, everybody.
> 
> I have the following problem: I have an old project that uses a database
> created with an older SQLite library version.
> Now, since I updated SQLite to 3.5.5, I can't use the database anymore,
> because any query on the database tables returns:
> 
> SQLite error 11 - Malformed database schema - near ")": syntax error
> 
> The strange point is that I downloaded "SQLite Database Browser" from
> sourceforge, and I can actually open the database with this tool. In the
> help, it is saying that "SQLite Database Browser" is using version 3.3.5 of
> the database engine. With this tool, I can browse the data in any table, and
> a "pragma integrity_check" returns "ok".
> 
> Any idea / help on this would be greatly appreciated.

Please send the following output:

   SELECT sql FROM sqlite_master;

Please do so quickly.  We are scheduled to release 3.5.7 in about
30 minutes.  If this is a bug in 3.5.x, we'd like to identify it
before then.

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

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


Re: [sqlite] Efficiency Question - Value Or Liability for Indexing of This Table?

2008-03-14 Thread drh
"Lee Crain" <[EMAIL PROTECTED]> wrote:
> I am debating the performance gains to be realized, if any, by indexing a
> particular table in a system implementation for which I am responsible. 
> 

You are getting way ahead of yourself.

Stop trying to speculate about whether or not an index
or indices will be useful.  Write your application first.
Then test it.  Measure the speed.  Then add an index and
test and measure again.  Compare the measurements.  Add 
a different index or combination indices.  Repeat until 
done.

Premature optimization leads to bad design.  Build your
application first, then measure it to see where optimization
is needed.

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


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


Re: [sqlite] Testing the rowid algorithm. Was: Generating new rowid alg o

2008-03-11 Thread drh
Nicolas Williams <[EMAIL PROTECTED]> wrote:
> 
> Slightly OT: The current SQLite3 test code base makes use of internal
> interfaces, which means you can't necessarily test the bits that you
> want to install.  It'd be nice to be able to test the bits actually
> installed. 

See http://www.sqlite.org/cvstrac/wiki?p=ToDo and especially
the 3rd item, second bullet.  We are working on this.  It will
take time.

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

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


[sqlite] Testing the rowid algorithm. Was: Generating new rowid algo

2008-03-11 Thread drh
"Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
> "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
> > So is it possible
> > that SQLITE will give an error when attempting to insert a record even
> > if there are free ROWID's?
> 
> Yes, in theory. If you have close to 2^63-1 rows, probing at random for 
> a ROWID to reuse may just happen to keep hitting occupied ROWIDs. If I 
> remember correctly, after a certain number of unsuccessful attemps 
> SQLite gives up and returns an error.

You remember correctly.  Up to 100 random rowids are attempted.
If no unused rowid is found, SQLITE_FULL is returned.  

As an aside, I note that this logic presents some interesting
problems for testing.  How does one verify that random rowid
algorithm works and that it really does stop after 100 tries
and return SQLITE_FULL?  How does one construct a test that 
collides for 100 randomly chosen rowids?  We can force the 
random-rowid logic to run simply by inserting a row with a 
rowid of 9223372036854775807. But how can we get collisions 
to happen 100 times in a row without inserting 
18446744073709551616 distinct rows?

Our solution to this problem is that we allow the test script
to monkey with the state of the Pseudo-Random Number Generator
(PRNG) that SQLite uses to generate random rowids.  On a test
build, we have special APIs that will save the current state
of the PRNG and that will restore the state of the PRNG to its
most recently saved state.

So the test runs like this:

   *  Create a table and insert rowid=9223372036854775807
   *  Save the current state of the PRNG
   *  Loop 101 times:
   +  Restore the state of the PRNG
   +  Insert a new row with a randomly chosen rowid

Sure enough - the loop fails with an SQLITE_FULL error on the
last iteration.

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

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


Re: [sqlite] Basic system setup for tracing?

2008-03-10 Thread drh
"software.simian" <[EMAIL PROTECTED]> wrote:
> 
> Just step-in; I'm studying the sqlite code and it would be easier if I could
> see the internal in action.
> 

  *  Compile with -DSQLITE_DEBUG=1
  *  PRAGMA vdbe_trace=ON;
  *  PRAGMA vdbe_listing=ON;

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

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


Re: [sqlite] Impossible to "catch locked state" (SQLite 3.5.6 & TCL)

2008-03-08 Thread drh
Zbigniew Baniewski <[EMAIL PROTECTED]> wrote:
> I'm afraid, I've discovered a bug: there's no possibility to "catch" the
> error "database is locked". Even, when one's trying something like:
> 
> #v+
>   if { [catch {sqlite3 dbcomm $fullPathToDatabaseFile} err] } {
> puts $err
>   }
> #v-
> 
> There'll be no $err output, because - as it seems - "catch" won't return
> a value > 0. There'll be following lines printed on the console instead:
> 
>   database is locked
>   while evaluating {source ./main.tcl}
> 

The error is not occurring until later, the first time you
try to access the database using the dbcomm object.

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

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


Re: [sqlite] [C] Linker Error

2008-03-08 Thread drh
Clay Dowling <[EMAIL PROTECTED]> wrote:
> Severin Müller wrote:
> > I tried to include sqlite3 in my current C Project. =
> 
> > =
> 
> > I downloaded the precompiled library sqlite-3.5.6.so and put it in my pro=
> ject.
> > Then, i downloaded the sqlite source and added sqlite3.h to my project. =
> 
> > =
> 
> > Now, when i try to compiler, i get the following error message:
> > =
> 
> > /home/fish-guts/workspace/Debug/lib/sqlite-3.5.6.so: undefined reference =
> to `dlsym'
> > /home/fish-guts/workspace/Debug/lib/sqlite-3.5.6.so: undefined reference =
> to `dlerror'
> > /home/fish-guts/workspace/Debug/lib/sqlite-3.5.6.so: undefined reference =
> to `dlopen'
> > /home/fish-guts/workspace/Debug/lib/sqlite-3.5.6.so: undefined reference =
> to `dlclose'
> 
> Rather than use the precompiled library, I recommend one of two options:
> 
> 1. Download the latest version that is in your package manager and use
>that, if it is sufficiently recent.
> 
> 2. Download the source and build it.  It is a very easy to build
>package, and you would then have the option to build a static version if
>you wanted (the .a library)
> 

I agree with Clay.  Get a copy of the amalagamation.  It is a single
file of C code named sqlite3.c.  Compile it and statically link it 
with your application.

But even then, you're going to get the same linker errors because you
have omitted the "-ldl" option when you link.

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

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


Re: [sqlite] Can I automatically create a 'disambiguation' number in a second field of a primary key?

2008-03-03 Thread drh
"Paul Hilton" <[EMAIL PROTECTED]> wrote:
> 
> Here is the problem: I want Slot created to disambiguate the Primary Key, So
> that for every value of Group the value of Slot starts at 1 and counts up.

These are two different things:

   (1) Slot needs to disambiguate the PRIMARY KEY
   (2) Slot needs to start at 1 and count upwards

Do you really need (2)?  If not - if (1) is all you really need -
then one approach is to fill the slot with a random number.  Depending
on how many slots you have and how often you fill them, you might
be able to get away with always using a random 64-bit integer and
never checking for collisions because collisions will be much so
rare that random computer explosions are much more likely.  Whether
or not this is true depends on your application, the reliability
of your hardware, and the consequences of a collision.  Do the 
math.  If in doubt, you might use a 128-bit or longer random 
blob instead of an integer.

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

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


Re: [sqlite] Does sqlite3_prepare() clean up after itself if itfails?

2008-03-03 Thread drh
"Scott Hess" <[EMAIL PROTECTED]> wrote:
> 
> True, but my code snippet didn't check for NULL.  If, for some reason,
> SQLite returned a partial statement handle with an error code, then
> I'd expect you would want to pass it back to sqlite3_finalize().
> Since sqlite3_finalize() explicitly handles NULL, I think you can
> safely just pump the statement handle from sqlite3_prepare() back to
> sqlite3_finalize(), regardless of what it is.
> 

I have modified the documentation so that SQLite now guarantees
that it will never require a call to sqlite3_finalize() if
sqlite3_prepare() returns anything other than SQLITE_OK.
See the latest CVS check-in.

   http://www.sqlite.org/cvstrac/timeline

So, Scott, your extra sqlite3_prepare() call is harmless
and probably a good safety precaution.  But as of the latest
check-in it is no longer necessary.

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

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


Re: [sqlite] Transaction log writing performance

2008-02-22 Thread drh
Marco Bambini <[EMAIL PROTECTED]> wrote:
> 
> So, is safe so to set PRAGMA synchronous=NORMAL; under MacOS X?
>

I am told probably not, though Apple has never issued a definitive
statement on the question.

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

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


Re: [sqlite] Transaction log writing performance

2008-02-22 Thread drh
"Brian Smith" <[EMAIL PROTECTED]> wrote:
> When SQLite writes to the log file, it 
>
>  (1) writes all the data,
>  (2) fsyncs, then
>  (3) updates the page count in the header, and finally
>  (4) fsyncs again.
>
> Isn't it possible to change SQLite so that the steps 3
> and 4 are unnecessary?
> 

That depends on your filesystem.  On many modern file
systems you can safely omit 3 and 4.  And if the
xDeviceCharacteristics() method of the VFS implementation
for a particular filesystem reports SQLITE_IOCAP_SAFE_APPEND,
then SQLite skips steps 3 and 4.  Steps 3 and 4 are
also skipped if you set

   PRAGMA synchronous=NORMAL;

instead of the default

   PRAGMA synchronous=FULL;

It has been reported to us that by omitting steps 3 and
4 you get about a 30% speed improvement on MacOS X.

But without steps 3 and 4 and on some filesystems, a
power failure that occurs while the journal is being
written can result in database corruption.  The damage
happens like this:  The journal is written to the disk
out-of-order so that later parts of the journal are
written before some earlier parts.  And the power failure
occurs before the entire journal is written.  So you are
left with something like this:

 

Where "." indicates valid data, and "*" indicates random
trash that just happen to be on the disk - not information
that was written by SQLite.  Once power is restore, the
next SQLite process to open the file would see the "hot"
journal, notice that both the header and the "end mark"
are valid, but the stuff in the middle is not.  That
invalid stuff in the middle will get played back into
the database, corrupting it.

It is true that a sufficiently strong checksum might
detect the corruption in the middle.  SQLite does do
some checksumming to try to detect this sort of problem
when you specify PRAGMA synchronous=NORMAL.  But checksums
are not 100%.

The SAFE_APPEND attribute means that the filesystem does
not extend the size of the file until the content has been
safely written to oxide.  For filesystems that support
SAFE_APPEND, there is no possibility of getting corrupt
data in the file during a power failure.  I suspect that
most modern journalling filesystems are SAFE_APPEND, but
I'll continue to assume the worst until I know for sure.

Early versions of SQLite2 always assumed SAFE_APPEND. 
Then we got some reports from the field of corruption
following power loss that was ultimately traced back to the
scenario described above.  That's why we added the extra
fsyncs and checksums.

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

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


Re: [sqlite] Most widely deployed?

2008-02-21 Thread drh
Dennis Cote <[EMAIL PROTECTED]> wrote:
>
> Nokia sold 350 million cell phones last year alone. Each of those phones 
> probably had some embedded database on them. It may even be SQLite for 
> all I know, but if it is not, then it substantially increases the number 
> of non-SQLite database deployments.
> 

I am not privy to the inner workings of Nokia cellphones, but I
believe any non-SQLite databases they are using do not understand
SQL.  The other products might still be a database, but they are
not an *SQL* database.

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

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


Re: [sqlite] Data Recovery - Unvacumed DB - OS X Address Book

2008-02-21 Thread drh
Joshua Galvez <[EMAIL PROTECTED]> wrote:
> Any help would be appreciated. Even as much as, "No, there really is  
> no way to do this."
> 

No, there really is no way to do this, at least not without some
internal knowledge of what kinds of information Apple is storing
in the data.syncdb file.  Maybe you could reverse engineer it.

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

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


Re: [sqlite] Execute PRAGMA max_page_count command

2008-02-21 Thread drh
"Raviv Shasha" <[EMAIL PROTECTED]> wrote:
> Although I limit the sqlite database to 32768 (32K), the db file which
> retrieved is equal to 470K.
> 
> What regrading the operations sequence ? Is it correct to first
> initialize the sqlite database and then to execute the PRAGMA
> max_page_count command or not?
> 
> What can cause this problem?
> 

The max_page_count pragma limits the number of database "pages"
not bytes.  32768 pages is 32MiB, assuming a 1KiB page (the default.)

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

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


Re: [sqlite] Unrecognized "Z" UTC time zone signifier

2008-02-21 Thread drh
Ralf Junker <[EMAIL PROTECTED]> wrote:
> SQLite does not recognize "Z" as the zero offset time zone specifier. 

SQLite does not currently accept any timezone specifiers, other
than a hard-coded timezone offset:

   1981-04-06T14:45:15+01:00

If we start accepting any symbolic timezone names, seems like we
would then need to start accepting them all.  If am reluctant to
open the floodgates

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

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


Re: [sqlite] Access to SQLite via two languages in one process

2008-02-20 Thread drh
Nicolas Williams <[EMAIL PROTECTED]> wrote:
> On Wed, Feb 20, 2008 at 04:23:29PM +, [EMAIL PROTECTED] wrote:
> > Nicolas Williams <[EMAIL PROTECTED]> wrote:
> > > does SQLite know about POSIX file locks on the same files from
> > > other instances of itself?
> > 
> > Yes.  That's the whole point of POSIX advisory locking.
> 
> src/os_unix.c says:
> 
> ** Here is the dirt on POSIX advisory locks:  ANSI STD 1003.1 (1996)
> ** section 6.5.2.2 lines 483 through 490 specify that when a process
> ** sets or clears a lock, that operation overrides any prior locks set
> ** by the same process.  It does not explicitly say so, but this implies
> ** that it overrides locks set by the same process using a different
> ** file descriptor.  Consider this test case:
> 
> ** To work around the problem, SQLite has to manage file locks internally
> ** on its own.  Whenever a new database is opened, we have to find the
> ** specific inode of the database file (the inode is determined by the
> ** st_dev and st_ino fields of the stat structure that fstat() fills in)
> ** and check for locks already existing on that inode.  When locks are
> ** created or removed, we have to look at our own internal record of the
> ** locks to see if another thread has previously set a lock on that same
> ** inode.
> 
> 
> Now, SQLite maintains an internal hash table indexed by st_dev and
> st_ino, but if there are two _distinct_ copies of SQLite in the same
> process, how can those two instances of SQLite share that hash table?
> 
> The hash table is a static, after all, so each instance of SQLite will
> have its own instance of that hash table, which will mean they won't
> know about each other's locks.
> 

Private messages on this subject suggest that I have misunderstood
the question.  What exactly do you mean by "instances" of SQLite?

If you create two or more connections (two or more calls to
sqlite3_open()) within the same process, they all share the same
st_dev hash table and hence they all know about each others locks.

If you create connections to the same databases from different
processes, then they don't share the same st_dev hash, but posix
advisory locking works in that case.

In either case you are covered.

Even if you are accessing SQLite from two different languages,
you should only be linked against a single instance of the SQLite
library (otherwise you would get linkage errors) meaning that you
only have a single copy of the hash table.  If you know of a way
to link two copies of the same library into the same process at
the same time, such that each copy of the library has its own
set of static variables, then the conditions above break down
and you cannot use SQLite safely.  But on the other hand, I don't
know of much software that is likely to survive such a scenario.
And I am unclear how such a scenario is even possible.
--
D. Richard Hipp <[EMAIL PROTECTED]>

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


Re: [sqlite] Access to SQLite via two languages in one process

2008-02-20 Thread drh
Nicolas Williams <[EMAIL PROTECTED]> wrote:
> does SQLite know about POSIX file locks on the same files from
> other instances of itself?

Yes.  That's the whole point of POSIX advisory locking.

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

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


Re: [sqlite] Composite primary key?

2008-02-19 Thread drh
Rael Bauer <[EMAIL PROTECTED]> wrote:
> Hi,
>
>   
>   1. with sqlite Is it possible to have a primary key made up of 2 fields? 
> (If so how...)
>   (from firebird:
>   ALTER TABLE "table1" ADD CONSTRAINT PK_TABLE1 PRIMARY KEY 
> ("field1","field2");)
>
>   
>   2. Is it possible to add a primary key with an "ALTER" statement like:
>   ALTER TABLE "table1" ADD CONSTRAINT PK_TABLE1 PRIMARY KEY ("field1");
>

You can have a composite primary key in SQLite, but you
have to create the key when you create the table:

   CREATE TABLE example1(
  field1 FLOAT,
  field2 TEXT,
  PRIMARY KEY(field1, field2)
   );

You cannot create the primary key after the fact using ALTER TABLE.

On the other hand, you can create a UNIQUE INDEX after the fact
which has essentially the same effect as a PRIMARY KEY:

   CREATE UNIQUE INDEX pk_index ON "table1"("field1","field2");

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


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


[sqlite] Please test lastest CVS using WinCE

2008-02-19 Thread drh
Can somebody with the ability to compile and test for wince
please test check-in [4802] for me.

   http://www.sqlite.org/cvstrac/chngview?cn=4802

For that matter, is there anybody out there who would like
to become the official wince maintainer for SQLite?  If you
are able to compile, test, and debug SQLite for wince and
would like to take on this task, we will be happy to welcome
you to the official SQLite development team.  Benefits include:

   *  CVS check-in privileges
   *  Access to the private SQLite developers chatroom
   *  A prestigious [EMAIL PROTECTED] email alias
   *  Praise and admiration from your peers

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

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


Re: [sqlite] Using a UUID as a key

2008-02-19 Thread drh
"David A. Cobb" <[EMAIL PROTECTED]> wrote:
> It appears that, given SQLite's Manifest Typing, the appropriate type of 
> a UUID -- 16bytes, binary -- would be BLOB.  One would not want to try 
> converting the ID to anything, if only for the cost in time.
> 
> Can a BLOB be the Primary Key for a table?
> 

Yes, a BLOB can be a primary key in SQLite.

I, however, usually use a hex encoding since that easier
to deal with when debugging the database using text-based
tools.  I typically create UUIDs as follows:

  INSERT INTO demo1(uuid) VALUES(hex(randomblob(16)));

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

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


Re: [sqlite] SQLite Like Query Optimization

2008-02-18 Thread drh
"Kalyani Phadke" <[EMAIL PROTECTED]> wrote:
> I have not recompiled sqlite 3 before. I am having trouble to find
> documentation.
> 
> Could anyone pls tell me how can I compile SQLite3 source code on
> windows xp machine. Do I need to download FTS3 files ? Where can I find
> those files? How can I add this extension to my sqlite???
> 

  (1)  Download the amalgamation.
  (2)  Compile with -DSQLITE_ENABLE_FTS3=1

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

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


Re: [sqlite] Sqlite Version 3.2.2

2008-02-18 Thread drh
"Walt" <[EMAIL PROTECTED]> wrote:
> Is it feasible to a DataBase on one computer and have multiple
> users on a LAN accessing the DataBase at the same time?
> 

This can be made to work.  But you will probably be much happier
with a client/server database such as MySQL or PostgreSQL.  They
are designed for concurrent network access from multiple clients.

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

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


Re: [sqlite] PRIMARY KEY? Date and time datatypes?

2008-02-18 Thread drh
Gilles Ganault <[EMAIL PROTECTED]> wrote:
> On Sat, 16 Feb 2008 11:29:29 +1100, BareFeet
> <[EMAIL PROTECTED]> wrote:
> > If you designate an integer column as also being the primary key,
> > then SQLite will auto assign its  value incrementally each time
> > you insert a new row, unless you assign  a value explicitly.
> 
> In this case, why do we need to use "PRIMARY KEY AUTOINCREMENT"?
> Shouldn't "PRIMARY KEY" be enough to have SQLite auto-increment this
> column if it's NULL?

The AUTOINCREMENT keyword prevents an primary key from being
reused even after it is deleted.  Consider:

   /* 1 */  INSERT INTO table(pk, x) VALUES(NULL, 'row one');
   /* 2 */  DELETE FROM table WHERE x='row one';
   /* 3 */  INSERT INTO table(pk, x) VALUES(NULL, 'row two');

If pk is just a PRIMARY KEY (without AUTOINCREMENT) then the inserts
at 1 and 3 will both create rows with the same primary key value.  But
if the AUTOINCREMENT keyword is used, then the insert at 3 will have
a distinct primary key value from the insert at 1.

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

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


Re: [sqlite] SQLite Like Query Optimization

2008-02-16 Thread drh
"Evans, Mark (Tandem)" <[EMAIL PROTECTED]> wrote:
> snip...
> 
> >
> > LIKE operators cannot use indices unless the index is case
> > insensitive.  Use GLOB for case sensitive fields.
> >
> 
> Richard - i'm not sure i understand "unless the index is case insensitive."
> How does that relate to:
> 
> sqlite> create table t (a varchar(10) primary key, b, c);
> sqlite> pragma CASE_SENSITIVE_LIKE=OFF;
> sqlite> explain query plan select * from t where a like 'a%';
> 0|0|TABLE t
> sqlite> pragma CASE_SENSITIVE_LIKE=ON;
> sqlite> explain query plan select * from t where a like 'a%';
> 0|0|TABLE t WITH INDEX sqlite_autoindex_t_1
> sqlite>
> 
> Dumb question:  Is CASE_SENSITIVE_LIKE a different concept
> from "case sensitive index"?
> 

Yes it is.  By default ('A' LIKE 'a') is true.  But if you enable
case-sensitive like then ('A' LIKE 'a') is false.

The case sensitivity of your LIKE operator must match the 
case sensitivity of your indices in order for the index
to be usable as an optimization.  

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

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


Re: [sqlite] expose sqlite3_put_Varint & sqlite3_get_varint

2008-02-15 Thread drh
Ken <[EMAIL PROTECTED]> wrote:
> Any reason why the functions above could not be exposed as part of the 
> sqlite3 api suite?
> 

The usually reasons apply:  To expose them as part of the sqlite3 api
suite means that they would have to be supported, documented, and 
tested forever and it also means that we never be allowed to change
their interface again in the future.

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

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


Re: [sqlite] SQLite Like Query Optimization

2008-02-15 Thread drh
"Kalyani Phadke" <[EMAIL PROTECTED]> wrote:
> I am using Sqlite 3 as my database. One of my table contains 1280010
> rows. Db file size is 562,478KB. I am running DB on Windows XP pro-P4
> CPU 3.20GHz 3.19Hz ,2.00GB of RAM )
>  
> CREATE TABLE TableA 
> (
> ID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
>column1 VARCHAR (50) NOT NULL,
>column2 VARCHAR (50)  NOT NULL,
>column3 TIMESTAMP NOT NULL DEFAULT  (CURRENT_TIMESTAMP),
>column4  VARCHAR (128) NULL, 
>column5 VARCHAR (255)NULL,
>column6 VARCHAR ( 128 )   NULL,
>column7  TEXT NULL,
>column8  TEXT NULL
> )
> I have select query which looks like
> select ID from TableA where column2 like '%test%'  or column4like
> '%test%' or column5 like '%test%' or column6 like '%test%' or column7
> like '%test%'  or column8 like '%test%' order by column3 desc;
>  

LIKE operators cannot use indices unless the index is
case insensitive.  Use GLOB for case sensitive fields.

LIKE and GLOB operators cannot use indices if the pattern
begins with a wildcard.  

Nothing in SQLite will use an index if you are connecting
terms using OR.

It looks like what you really want to use here is a full-text
index.  Please read about the FTS3 support in SQLite.  That
seems to be what you are trying to accomplish.

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

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


Re: [sqlite] Blob incremental i/o via Python

2008-02-14 Thread drh
"Samuel Neff" <[EMAIL PROTECTED]> wrote:
> If the images you're storing are larger than the defined page size for the
> database (which is most likely the case) then you can get better performance
> and reduced memory consumption by storing the images in the file system and
> store only paths to the files in the database.  This means reading the large
> amount of data directly from the file system instead of from sqlite's
> linked-list of pages and bypassing the page caching layer (which you
> probably don't want for images anyways) and freeing up more of the page
> cache for real database data.
> 

One would think.  And yet experiments suggest otherwise.  It
turns out to be faster to read images directly out of SQLite
BLOBs until the image gets up to about 15KB on windows and
up to about 60KB on linux.  And even for much larger images,
the performance difference between reading from SQLite and
reading from a file is not that great, so it is a reasonable
thing to do to read from SQLite if transactions are important
to you or if it is just more convenient.

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

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


Re: [sqlite] Function hex

2008-02-14 Thread drh
Mau Liste <[EMAIL PROTECTED]> wrote:
> 
> results in: SQL error: no such function: hex
> 

Added by version 3.3.13, one year ago yesterday.

   http://www.sqlite.org/releaselog/3_3_13.html

What version are you running?

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

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


Re: [sqlite] indexing

2008-02-13 Thread drh
Kasper Daniel Hansen <[EMAIL PROTECTED]> wrote:
> I have a table with two variables, say A and B (both integers). The  
> table is rather large - around 2.9 GB on disk. Every combination of  
> (A,B) occurs only once. I am creating a unique index as
>CREATE UNIQUE INDEX ABidx ON abtable (A,B)
> It seems that the (A,B) index is created much slower than the (B,A)  
> index. I am wondering about the reason for this. My - very limited -  
> understanding was that sqlite needs to search over the whole database  
> for every (A,B) combination. Could someone give a quick pointer to  
> where the index strategy is described? Would this be quicker if I fit  
> the whole database into memory?
> 

Creating an index on A,B is equivalent to sorting on A,B.

The sorting algorithm currently used by SQLite requires
O(NlogN) comparisons, which is optimial.  But it also requires
O(N) disk seeks, which is very suboptimal.  You don't notice
all these seeks if your database fits in cache.  But when you
get into databases of about 3GB, the seeking really slows you
down.

A project on our to-do list is to implement a new sorter
that uses O(1) seeks.  We know how to do this.  It is just
finding time to do the implementation.

If creating an index on B,A is much faster than creating an
index on A,B, that probably means that B,A is initially closer
to being in sorted order than A,B is.  The initial order of the
entries does not effect the number of comparisons in a sort,
but it does reduce the number of seeks if the values are 
initially close to being sorted.

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


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


Re: [sqlite] blob incremental i/o constraints

2008-02-13 Thread drh
"Igor Sereda" <[EMAIL PROTECTED]> wrote:
> The questions around sqlite3_blob_xxx methods that Roger brought up a couple
> of months ago are very interesting for me too, and I haven't seen any reply
> to Roger's message. (Roger - do you have any update?)
> 
> As far as I can gather from the cited description of the problem, we should
> manually acquire SHARED db lock before reading a blob, and RESERVED lock
> before writing a blob. Can someone confirm that?

Transactions and locks are created and close automatically
by sqlite3_blob_open() and sqlite3_blob_close().  No other
interaction by the application is required.

> 
> I guess I should have run some experiments
> 

That sounds like a good plan...

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

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


Re: [sqlite] Updatable views

2008-02-11 Thread drh
John Stanton <[EMAIL PROTECTED]> wrote:
> That ia a nice idea.  To have a pragma which specied the dialect.  There 
> could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. 
>   It would give tighter control over hard to track annoying minor syntax 
> errors.
> 

And, it would multiple exponentially the number of test cases
we have to write and maintain in order to adequately test the
parser ;-)

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

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


Re: [sqlite] Innovative examples / user stories

2008-02-11 Thread drh
Lars Aronsson <[EMAIL PROTECTED]> wrote:
> Is there any documentation of how people use SQLite in odd ways in 
> their everyday activities? 

Did you see

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

The document above is not exactly what you are asking for
since it does not list real-world examples, but it does contain
several suggestions on how to best use SQLite.

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

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


Re: [sqlite] VFS memory leak : During lock / unlock operations

2008-02-08 Thread drh
Dennis Cote <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> > 
> > There are no lock leaks.  But on the other hand, there is not
> > a one-to-one mapping of lock to unlock calls.  unlock is called
> > more often than lock and there are often attempts to unlock files
> > that have never been locked, irrc.  This is harmless on windows
> > and unix.
> > 
> 
> Richard,
> 
> The OP said they were measuring an excess of lock calls. That would 
> imply that SQLite is locking files it has already locked. Is that 
> possible with the POSIX APIs?
> 

That might also be possible.  It has been a while since I looked
into this, but I think SQLite might be acquiring (for example)
multiple read locks then releasing them all with a single unlock.

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

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


Re: [sqlite] VFS memory leak : During lock / unlock operations

2008-02-08 Thread drh
<[EMAIL PROTECTED]> wrote:
> Hi..
>  
>we are using sqlite3.3.4 with Integrity OS. we are facing a problem
> where in the VFS memory is getting exhausted due
>to large lock/unlock calls made by sqlite. Integrity support team
> said that,  for each file lock call made by sqlite,  a definite amount
>of memory is allocated, this memory is released only after the unlock
> or when the file is closed. And they are claiming that
>number of file unlock calls are not same as the number of file lock
> calls. they have put traces and identified that for 1000 lock calls
>there are only 950 unlock calls, which is a shortage of 50 unlock
> calls. This will leak considerable amount of memory if the
>system is left for long hours, with continuous sqlite operations
> being made. 
>  
> can any one throw some light on this problem, is there any known
> issue like this. Any information on this will be very helpful

The os_unix.c backend to SQLite makes no attempt to match lock/unlock
calls, because posix does not requires such.  If you are running
on an operating system that does require matching lock/unlock calls,
you will probably need to modify the os_unix.c layer in order for it
to work properly on your system.


>  
> thanks
> murthy
> 
> Please do not print this email unless it is absolutely necessary. Spread 
> environmental awareness.
> 
> The information contained in this electronic message and any attachments to 
> this message are intended for the exclusive use of the addressee(s) and may 
> contain proprietary, confidential or privileged information. If you are not 
> the intended recipient, you should not disseminate, distribute or copy this 
> e-mail. Please notify the sender immediately and destroy all copies of this 
> message and any attachments. 
> 
> WARNING: Computer viruses can be transmitted via email. The recipient should 
> check this email and any attachments for the presence of viruses. The company 
> accepts no liability for any damage caused by any virus transmitted by this 
> email. 
> 
> www.wipro.com
> ___
> 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] VFS memory leak : During lock / unlock operations

2008-02-08 Thread drh
Brad House <[EMAIL PROTECTED]> wrote:
> >> they have put traces and identified that for 1000 lock calls
> >> there are only 950 unlock calls, which is a shortage of 50 unlock
> >> calls. 
> > The os_unix.c backend to SQLite makes no attempt to match lock/unlock
> > calls, because posix does not requires such.  If you are running
> > on an operating system that does require matching lock/unlock calls,
> > you will probably need to modify the os_unix.c layer in order for it
> > to work properly on your system.
> 
> I'm not sure what you're getting at here.  Are you saying that it's
> possible that some mutexes will _never_ be unlocked?  In what circumstance
> does that occur? Is there a case where a mutex could be destroyed while
> a lock is held?  I know my linux 'man pthread_mutex_destroy' says
> "Attempting to destroy a locked mutex results in undefined behavior".
> 
> Under what circumstances does this "lock leakage" occur exactly?

There are no lock leaks.  But on the other hand, there is not
a one-to-one mapping of lock to unlock calls.  unlock is called
more often than lock and there are often attempts to unlock files
that have never been locked, irrc.  This is harmless on windows
and unix.


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

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


[sqlite] Bug found in the new register-based VM

2008-02-06 Thread drh
I had been saying in release announcements that no bugs have
been found in the new register-based virtual machine introduced
in SQLite version 3.5.5.  That changed with ticket #2927.  We 
have now observed our first register-VM bug.

  http://www.sqlite.org/cvstrac/tktview?tn=2927

There will likely be a new release (version 3.5.7) within a few
days in order to fix the problem discovered by ticket #2927.

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

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


Re: [sqlite] SQLite Web Site

2008-02-06 Thread drh
"L. S." <[EMAIL PROTECTED]> wrote:
> The page never loads. I get the message, "The page download could not
> be completed. Please try again later."
> 
> Here is the User-Agent header string:
> Mozilla/4.0 (compatible; MSIE 6.0; Windows 98; PalmSource/Palm-D052;
> Blazer/4.5) 16;320x320
> 

The webserver on www.sqlite.org specifically excludes clients
claiming to be "Windows 98".  We were getting a lot of abuse
coming out of China - people would download thousands of copies
of tarballs and use up gigabytes of bandwidth.  Since excluding
Windows98 clients, we have not had any of the abuse problems.
We were not able to detect any change in the number of daily
visitors when we implemented the no-win98 policy, from
which we concluded that most people had upgraded to at least
win2k by the we instituted the change.

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

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


Re: [sqlite] SQLite Web Site

2008-02-06 Thread drh
"L. S." <[EMAIL PROTECTED]> wrote:
> I've been ignoring this issue for a while; the world won't end over this 
> but...
> There is some issue with the newly-designed SQLite web site that
> prevents access from my Palm Treo. It used to be accessible, with the
> old design.
> --
> Often I surf, email, research, read, and download things using
> wireless access on my Treo web browser (and even store a copy of the
> SQLite source on my phone) because it's highly portable, has all day
> battery life, and I can Bluetooth-transfer stored files to my laptop.
> 
> The site is no longer found on this platform. Why is this?

What do you mean "no longer found"?  Do you mean that that
you cannot see anything at all, or that the new design is such
that it is not displayed correctly?

What does the User-Agent header string from your web browser
say on the Palm Treo?  

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

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


Re: [sqlite] when to analyze?

2008-02-06 Thread drh
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Suppose we have a table with some 10 million rows and this table was
> analysed, so sqlite_stat1 has the stats of this table then is it worth it to
> analyze again after adding say 1000 more rows? The indexing is still the
> same, so no indexes are dropped or created. Also the data of the added rows
> won't be dramatically different from the existing rows.
> My guess it is not worth the time it will take and I could fine out by
> experimenting, but maybe somebody has some thoughts about this and could
> tell me.
> 

Briefly:  Your guess is correct

For additional background into why ANALYZE exists and what it
accomplishes for you, please see

http://www.sqlite.org/cvstrac/wiki?p=QueryPlans

SQLite rarely needs to do ANALYZE at all.  You can usually get
it to pick efficient query plans without having to ANALYZE.

The recommended use of ANALYZE, if you use it at all, is to
run it once during development on a dataset that is characteristic
of the kinds of data your application will store.  Retain the
results of this ANALYZE as they are found in the sqlite_stat1
table.  Then, when you deploy your application and create a new 
database, run ANALYZE once as soon as the schema is loaded but
before any data is added.  Such an ANALYZE will take almost no
time because your database is empty.  Then delete all of
the information from the newly created sqlite_stat1 table and
replace it with the data you saved from the ANALYZE you ran
during development on your sample dataset.

The result of this will be that SQLite will plan queries with
an eye toward optimizing databases that are typical for your
application.

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

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


Re: [sqlite] SQLite and signal handlers

2008-02-06 Thread drh
Felix Radensky <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> What is a recommended way of handling signals in daemon 
> processes using sqlite database ?  Is calling sqlite3_interrupt()
> in SIGTERM handler considered good enough ?
> 

sqlite3_interrupt() was originally create for the specific
purpose of handling SIGINT when you press Control-C in the
CLI.  So it is probably what you want.

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

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


[sqlite] New SQLite mailing lists.

2008-02-06 Thread drh
Two new SQLite mailing lists have been established:

sqlite-announce
sqlite-dev

Additional information and links to pages where you can
sign up can be found at

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

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

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


[sqlite] SQLite version 3.5.6

2008-02-06 Thread drh
SQLite version 3.5.6 is now available on the website.

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

There is very little change from version 3.5.5.  The reason
for this release is that there was a regression in the virtual
table mechanism that prevented virtual tables from being used
in LEFT OUTER JOINs.  And there were some updates to the OS/2
driver.  Also, I bungled the release of version 3.5.5 such that
some users downloaded copies of 3.5.4 that were labeled 3.5.5.

We have still not encountered any bugs in new register-based
virtual machine.  You might recall that this was a huge change
moving from version 3.5.4 to 3.5.5 and there was some concern
that the change my destablize the core.  But such concern is
now past.  Version 3.5.6 is recommended for all users and is
consider stable and ready for production release.

As always, please report any problems to this mailing list,
or directly to me.

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

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


Re: [sqlite] Mailing List Changes

2008-02-04 Thread drh
Nicolas Williams <[EMAIL PROTECTED]> wrote:
> On Tue, Feb 05, 2008 at 01:30:01AM +, [EMAIL PROTECTED] wrote:
> > And yet nearly everyone I know loaths that behavior.  The
> > overwhelming majority of users prefer mailing list replies
> > to go back to the mailing list *only*.
> 
> Users need to learn to do "list reply" or "reply all" :)
> 
> Getting dups (reply all) is no big deal (and even desirable!).
> 
> There's a mailing list I'm on where someone tried to respond privately
> to another poster, but didn't realize that reply-to was set, and the
> reply went to the list; that reply was a bit embarrassing.  I don't
> recommend that.
> 

The much, much more common mistake is that users press reply
instead of reply-all and they message does not go back to the
list.  This happens with alarming regularity.  And when the
reply does not go back to the list, their contribution to the
conversation is lost from the archive.  This is bad.

Furthermore, reply-all causes the original sender to get two
copies of the message instead of one.  My mailbox is full enough
already without getting two of everything.

In the very rare case where you want to respond to the individual
rather than to the list, it is easy enough to change the To: field
of your email.  But responding to an individual should be the
exception, rather than the rule.  Remember, we want all responses
to be on-list because usually when one person asks a question, there
are a dozen others that have the same question but have not yet
asked it.  If people reply off-list, then the same question gets
asked and answered over, and over again.  But if the answer is
on-list, then multiple people can benefit from the answer.

The common case is responding to the list.  Without Reply-To munging,
if you press reply-all, then you have to go up and manually remove
the original senders name from the To: field.  This is extra work
in the common case.  We perfer to optimize the common case, rather
than the exceptional case.

A better way of avoiding embarrassment from public revelation of
secretive correspondence is to deal openly and honestly with 
everybody in the first place.

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

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


Re: [sqlite] Mailing List Changes

2008-02-04 Thread drh
Doug Currie <[EMAIL PROTECTED]> wrote:
> 
> > Please set the list so default reply is to the list.
> 
> http://www.unicom.com/pw/reply-to-harmful.html
> 

One finds various screeds such as the one Doug references
above.  And on the configuration screen for GNU mailman,
it "strongly recommends" that replys be to the author and
not to the list.

And yet nearly everyone I know loaths that behavior.  The
overwhelming majority of users prefer mailing list replies
to go back to the mailing list *only*.

I think we have things configured now so that replies go
back to the list instead of the to original author.  There
are likely other settings that will need to be adjusted as
we move forward.  Please let me know if you see anything
unusual.

In a semi-related rant: Setting up a new mailing list is
*way* harder than it needs to be.  Way, Way harder.  In order
to go from ezmlm to GNU mailman, we had to prototype the
setup on a separate machine, then spend a day debugging
the setup after transferring it to the production machine.
A day.  For a mailing list.  And this is with mailing list
software that is suppose to be *easy* to configure.  I
tremble to thing what the difficult-to-configure software
must be like.

To tie this back to the original question, when people who
write mail handling and mailing list software get their
programs to the point where I can set up a new mail system
and a new mailing list manager on a system in 15 minutes or
less with reasonable assurance that I have not opened major
security holes in the system, then, perhaps, I will be in a
better mood to listen to their polemics on Reply-To field 
munging.

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


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


Re: [sqlite] open statements upon close

2008-02-04 Thread drh
Ken <[EMAIL PROTECTED]> wrote:
> Hi all, 
> 
> I'm getting a sqlite error when running sqlite3_close.  The error indicates 
> that there are open statements. 
> 
> To the best of my knowledge all statements are closed. I loooked at the 
> sqlite3_close function and call to close the virtual tables appears to be 
> causing this issue. 
> 
> My question: Are there any means in sqlite to get a listing of what it thinks 
> are open statements?
> 

There is no published way to do this.  If you run in a debugger,
you can look at the linked list of "struct Vdbe" objects that
sqlite3.pVdbe points to.  This is the list of open statements
in the current implementation (and subject to change without 
notice).

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

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


Re: [sqlite] 3.5.5 Compilation failure / Bug

2008-02-04 Thread drh
Ken <[EMAIL PROTECTED]> wrote:
> Amalgamation fails to compile when -DSQLITE_OMIT_VIEW is defined.
> 

Neither the nor the preprocessed sources work with -DSQLITE_OMIT macros. 
If you need to use -DSQLITE_OMIT, then you will need to compile on
Unix using the original source code.

Both the amalgamation and the preprocessed sources contain
generated C code and the code generators have to know about
the -DSQLITE_OMIT macros.

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

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


Re: [sqlite] Version 3.5.5 Released

2008-02-02 Thread drh
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> Got the
> sqlite3.exe version 3.5.5 but .explain still doesn't seem to work.
> Maybe it's me, could someone post a simple example of .explain working?
> 


C:\ sqlite3
SQLite version 3.5.5
Enter ".help" for instructions
sqlite> create table t1(x);
sqlite> .explain
sqlite> EXPLAIN SELECT * FROM t1;

[explain output follows the above].

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

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


Re: [sqlite] Precompiled binaries for Windows Not Version 3.5.5

2008-02-01 Thread drh
"Zarko Popovski" <[EMAIL PROTECTED]> wrote:
> I see also SQLite 5.5.4 version compiled, is there SQLite 3.5.5 precompiled
> version for M$ Windows or if there is not drh. please compile it :)
> 

I fixed that problem this morning.  When did you last look?

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


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



Re: [sqlite] SQLite 3.5.5 on OpenBSD running on HPPA (HP9000) make test

2008-02-01 Thread drh
Rob Sciuk <[EMAIL PROTECTED]> wrote:
> Dear DRH,
> 
> I post the previous (FreeBSD/Ubuntu) and this *ONLY* to exercise the new
> 3.5.5 bits given the wholsale changes.  I hope you find them useful, but
> AFAICT, 3.5.5 looks pretty good from a perf/stability standpoint.
> 
> In the obscure machine/OS category, I lit this up on an HP9000 K200 
> running not HP-UX, but rather OpenBSD 4.2 (heh heh).  Build was 
> uneventful, and testfixture ran to completion with the following output.
> 

We do not understand the async3 problem and cannot reproduce it.
All the other issues have been fixed in CVS HEAD.

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


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



Re: [sqlite] Using LIKE to check the first digits?

2008-01-31 Thread drh
Gilles <[EMAIL PROTECTED]> wrote:
> At 15:40 31/01/2008 -0800, James Dennett wrote:
> > > WHERE col LIKE '123%' or WHERE substr(col,1, 3) = '123'
> >
> >The optimizer has a decent chance of using an index for LIKE '123%' but 
> >I'd be surprised (and impressed) if it looks inside function calls suchas 
> >substr for opportunities to use indexes.
> 
> Thanks guys. I'll remember to index the column, for higher performance.
> 

Indices won't help with LIKE unless the column as a NOCASE
collation.  Use GLOB instead:

   ... WHERE number GLOB '1234*';

Note that "*" is the wildcard character with GLOB, not "%"
as in LIKE.  The above will use an index on the number column
if it is available.
--
D. Richard Hipp <[EMAIL PROTECTED]>


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



Re: [sqlite] Solaris make test compilation error

2008-01-31 Thread drh
Ken <[EMAIL PROTECTED]> wrote:
> Solaris 5.8 (solars 8)
> make test
> 
> Undefined   first referenced
>  symbol in file
> sched_yield /var/tmp//cckDMcyL.o
> ld: fatal: Symbol referencing errors. No output written to .libs/testfixture
> collect2: ld returned 1 exit status
> make: *** [testfixture] Error 1
> 

So how does a thread yield its timeslice on solaris?

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


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



Re: [sqlite] Broken links to "sqlite3" on website

2008-01-31 Thread drh
"David Baird" <[EMAIL PROTECTED]> wrote:
> The "sqlite3" link is broken from multiple web pages:
> 
> http://www.sqlite.org/c3ref/objlist.html
> http://www.sqlite.org/c3ref/intro.html
> 
> Also, links to "sqlite3_stmt" are broken.  It seems that any link
> containing a %20 is broken, e.g.
> 
> http://www.sqlite.org/c3ref/database%20connection.html
> http://www.sqlite.org/c3ref/prepared%20statement.html
> 
> and I suspect that they are supposed to be these instead:
> 
> http://www.sqlite.org/c3ref/sqlite3.html
> http://www.sqlite.org/c3ref/stmt.html
> 

Tnx.  Please try it again now.
--
D. Richard Hipp <[EMAIL PROTECTED]>


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



Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

2008-01-31 Thread drh
"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> Here is my "make test" on Mac OS 10.5.1:
> 
> 8 errors out of 38117 tests
> Failures on these tests: lock4-1.3 vtab6-2.2 vtab6-2.4 vtab6-2.5
> vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2
> All memory allocations freed - no leaks
> Maximum memory usage: 14161966 bytes
> Current memory usage: 0 bytes
> make: *** [test] Error 1
> 

We tested on Mac OS 10.4.11.  No errors there.

Once again, there is not much I can do to work on this problem
without seeing the text of the error messages

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


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



Re: [sqlite] Version 3.5.5 Released

2008-01-31 Thread drh
"Noah Hart" <[EMAIL PROTECTED]> wrote:
> With SQLITE_OMIT_VIRTUALTABLE defined, I get a linker fault under VC++
> 
> It claims that:
> error LNK2001: unresolved external symbol sqlite3VtabBeginParse

That call *is* surrounded by #ifndef SQLITE_OMIT_VIRTUALTABLE.  But
the #ifndef is located in parse.y.  You are probably trying to build
off of parse.c, which is a generated file.  You have to tell "lemon"
about the SQLITE_OMIT_VIRTUALTABLE macro or it will generate code
to call sqlite3VtabBeginParse().

In general, you cannot use SQLITE_OMIT macros if you are compiling
from the amalgamation or from the preprocessed C code.  You have
to compile from canonical sources, an in particular you must compile
starting with parse.y, not parse.c.

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


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



Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

2008-01-31 Thread drh
"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> How to get error messages from test? Scroll up and copy-paste? Or
> there is some other more handy method?

make test | tee testout.txt

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


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



Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

2008-01-31 Thread drh
Rob Sciuk <[EMAIL PROTECTED]> wrote:
> Ran the tests on Freebsd, the make test summary follows:
> [delenda => ... Ok]
> 
> 16 errors out of 38961 tests
> Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24 
> printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 
> vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2
> All memory allocations freed - no leaks
> Maximum memory usage: 14161974 bytes
> Current memory usage: 0 bytes
> *** Error code 1
> 

We get zero test failures on Linux.  I don't know if these are
significant or not without seeing the test error messages that
accompany each failure.

Probably this is things where your system is printing 3.4e+05
whereas SQLite is looking for 3.4e+005, in which case the errors
are benign.  But without seeing the actual errors, I cannot say
for sure.
--
D. Richard Hipp <[EMAIL PROTECTED]>


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



[sqlite] Version 3.5.5 Released

2008-01-31 Thread drh
SQLite version 3.5.5 is now available for download from the
SQLite website:

   http://www.sqlite.org/

The big change from version 3.5.4 is that the internal virtual
machine was reworked to use operands in registers rather than
pulling operands from a stack.  The virtual machine stack has
now been removed.  The removal of the VM stack will help prevent
future stack overflow bugs and will also facilitate new optimizations
in future releases.

There should be no user-visible changes to the operation of SQLite
in this release, except that the output of EXPLAIN looks different.

In order to make this change, about 8.5% of the core SQLite code
had to be reworked.  We thought this might introduce instability.
But we have done two weeks of intensive testing, during which time
we have increased the statement test coverage to 99% and during
which we have found and fixed lots of minor bugs (mostly things
like leaking memory following a malloc failure).  But for all of
that testing, we have not detected a single bug in the new 
register-based VM.  And for that reason, we believe the new
VM, and hence version 3.5.5, is stable and ready for production
use.

As usual, please report any problems to this mailing list, or
directly to me.

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


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



Re: [sqlite] sqlite3_free_table and sqlite3_close

2008-01-31 Thread drh
Torsten Blix <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> is it safe to do the following in a C program while working with SQLite? I
> tried searching the docs but couldn't find any answer.
> 
> sqlite3_open();
> sqlite3_get_table(...result);
> sqlite3_close();
> sqlite3_free_table(result);
> 
> i.e. freeing the table AFTER the closing of the db_handle?
> 

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


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



[sqlite] SQLite website outage & mailing list upgrade, 2008-02-02

2008-01-30 Thread drh
We are going to be upgrading the server that runs
the SQLite website this comming Saturday, if all goes
according to plan.  We are planning to changes the
operating system from Debian to Ubuntu and the
mailing list manager from ezmlm to GNU mailman.

If everything goes as planned, the outage will only
last a few minutes.  And we expect to have all mailing
list users transferred over to the new mailing list
manager automatically.  But, of course, these things
rarely go according to plan

If you desparately need to access the on-line docs
during the outage, visit the back-up site:

   http://www.hwaci.com/sw/sqlite/

If you mysteriously stop getting messages from this
mailing list on Saturday, then you might have been
dropped accidently.  Poke around on the website and
figure out how to sign up again.  (I'd tell you how
to do that in this email message, but I don't know
yet myself.)

I'm told that GNU mailman will work much better for
us than ezmlm.  We should soon add new mailing lists
like sqlite-dev and sqlite-announce, assuming everything
goes well.  Stay tuned (or visit the website) for
additional announcements.

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


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



Re: [sqlite] How Does NOT NULL produce NULLs?

2008-01-29 Thread drh
"Lee Crain" <[EMAIL PROTECTED]> wrote:
> I did expect SQLite to enforce the NOT NULL portion of the SQL
> creation statements, no matter what.

SQLite *does* enforce NOT NULL no matter what.  I think your
pointers are getting turned into NULLs someplace else, perhaps
somewhere in the QT layer.

A NULL can get inserted for NOT NULL columns for non-matching
rows of an OUTER JOIN.  Are you doing OUTER JOINs?  The NOT NULL
applies to the table, not to query results from the table.

Also, if you request a invalid column (the column number is too
large or too small, or the last call to sqlite3_step() did not
return SQLITe_ROW), then the SQLite interfaces will return a 
NULL pointer.

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


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



Re: [sqlite] How Does NOT NULL produce NULLs?

2008-01-29 Thread drh
"Lee Crain" <[EMAIL PROTECTED]> wrote:
> I've created a table with several fields, 3 of which are created using
> these SQL statements:
> 
> [description] [varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
> 
> [keywords][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE,
> 
> [metadata][varchar](255) NOT NULL DEFAULT ('') COLLATE NOCASE);
> 
> __
> 
> Subsequent data insertions of empty strings produce the following data:
> 
> (null)|(null)|(null)  
> 

I am unable to replicate the problem.  Are you sure you don't
have a bug in *your* code?

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


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



Re: [sqlite] Why does the FAQ example use 2 copies for adding/deleting a column

2008-01-28 Thread drh
Lothar Scholz <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> The example for question 11 in the FAQ has this code for dropping an
> existing table column 'c'
> 
> BEGIN TRANSACTION;
> CREATE TEMPORARY TABLE t1_backup(a,b);
> INSERT INTO t1_backup SELECT a,b FROM t1;
> DROP TABLE t1;
> CREATE TABLE t1(a,b);
> INSERT INTO t1 SELECT a,b FROM t1_backup;
> DROP TABLE t1_backup;
> COMMIT;
> 
> This is copying the whole table twice. Is there any reason why it
> shouldn't be:
> 
> BEGIN TRANSACTION;
> CREATE TEMPORARY TABLE t1_backup(a,b);
> INSERT INTO t1_backup SELECT a,b FROM t1;
> DROP TABLE t1;
> ALTER TABLE t1_backup RENAME TO t1;
> COMMIT;
> 
> Or is this just that ALTER TABLE RENAME did not exist when the FAQ was
> created?
> 

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


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



Re: [sqlite] SQLite and OS X - Can I have multiple versions?

2008-01-28 Thread drh
"P Kishor" <[EMAIL PROTECTED]> wrote:
> 
> Most folks though install their own version of SQLite under the
> /usr/local tree ...

Why do people feel like they need SQLite to be a seperately
library?  It is *designed* to be statically linked.

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


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



Re: [sqlite] SQLite 3.4.x to 3.5.x drop-in compatibility?

2008-01-28 Thread drh
Carl Gundel <[EMAIL PROTECTED]> wrote:
> Hey all,
> 
> I'm thinking about upgrading to v3.5.4 the latest SQLite from v3.4.0 for the
> next release of Run BASIC, and I'm wondering about a couple of things:
> 
> 1) Do I need to change the way I make calls to the database engine?  Is the
> API the same?  I probably wouldn't have too much trouble figuring this out
> myself except that I'm using someone else's library code to interface with
> SQLite.
> 
> 2) Is there any difference between the database file formats for these two
> versions?
> 

The API and file format are the same.

There are some difference in the way SQLite interfaces to the
underlying operating system.  So if you are using SQLite on
a custom embedded device of some kinds with an unusual operating
system, you might have a few changes to make.  Otherwise, 
everything is the same.

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


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



Re: [sqlite] How to specify regular expression in a query?

2008-01-28 Thread drh
Nicolas Williams <[EMAIL PROTECTED]> wrote:
> On Mon, Jan 28, 2008 at 10:07:01AM +0100, Ralf Junker wrote:
> > Hello Bharath Booshan L,
> > 
> > >>select * from t where filepath regexp '/MyMovie(\.[^\.]+)*$';
> > >
> > >Will this query use index, if we had one, on filepath?
> > 
> > No. It will do a full table scan.
> 
> But it could, no?
> 
> I suppose that to make this generic so that users can replace the
> regexp, like, and glob functions would require some new interfaces.
> SQLite would have to be able to extract a constant prefix from the
> pattern in order to be able to use an idex in this case.
> 

Or, you could say:

   SELECT * FROM t
WHERE filepath REGEXP '/MyMovie(\.[^\.]+)*$'
  AND filepath GLOB '/MyMovie.*'

The second GLOB term will do the indexing.  Then the
REGEXP is applied to the all the terms that the GLOB
matches to do a second layer of filtering.
--
D. Richard Hipp <[EMAIL PROTECTED]>


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



Re: [sqlite] Number of elements in IN clause

2008-01-28 Thread drh
"Samuel R. Neff" <[EMAIL PROTECTED]> wrote:
> I don't think it is standard SQL.  At the very least, it doesn't work in
> MSSQL.  Standard is
> 
>   SELECT * FROM maintable WHERE key IN (select x from stuff);
> 
> SQLite shortened version is much nicer.. wish it was standard.
> 

SQLite also accepts the more verbose version shown above,
of course.  The two statements do *exactly* the same thing.

I cannot believe that I would have put in the short-hand
notation without having seen it somewhere else first.
Does PostgreSQL support the short-hand version?

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


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



Re: [sqlite] Number of elements in IN clause

2008-01-28 Thread drh
Felix Radensky <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> Is there any limitation on the number of elements in IN clause ?
> Can one have, e.g. thousands of elements ? Also, can having 
> to many elements become inefficient at some point and one
> has to use some other technique, i.e. comparing elements one
> by one in a loop ?
> 

You can create a table that contains the elements that you would
normally put in your IN clause:

   CREATE TEMP TABLE stuff(x);
   INSERT INTO stuff VALUES('one');
   INSERT INTO stuff VALUES('two');
   
   INSERT INTO stuff VALUES('one million');

Then run your query this way:

   SELECT * FROM maintable WHERE key IN stuff;

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


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



Re: [sqlite] SQLite omit using index

2008-01-23 Thread drh
"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> Hello All,
> I've found that SQLite-3.5.4 doesnt use index in this situation:
> 
> sqlite> create table t1 (id int primary key, val int);
> sqlite> create table t2 (id unique, val int primary key);
> sqlite> explain query plan update t1 set val = (select t2.val from t2
> where t1.id = t2.id);
> 0|0|TABLE t1
> 0|0|TABLE t2
> 
> In this case, SQLite should takes value from t2 via unique id INDEX,
> but it doesn't
> 

The t2.id field has no datatype specified.  That means it has
an affinity of NONE.  (See http://www.sqlite.org/datatypes3.html
paragraph 2.1 bullet 3.)  That means that if you insert a string
into t2.id it goes in as a string:

   INSERT INTO t2(id) VALUES('123');
   SELECT typeof(id) FROM t2 WHERE rowid=last_insert_rowid();
 --> answer "text"

Or if you insert an integer, it goes in as an integer:

   INSERT INTO t2(id) VALUES(123);
   SELECT typeof(id) FROM t2 WHERE rowid=last_insert_rowid();
 --> answer "integer"

But the t1.id column to which you are comparing t2.id has
an affinity of INTEGER.  (paragraph 2.1 bullet 1.)  That means
if you insert a string it is converted into an integer if it
looks like an integer.

   INSERT INTO t1(id) VALUES('123');
   SELECT typeof(id) FROM t1 WHERE rowid=last_insert_rowid();
 --> answer "integer"

Now, the index on t2(id) also uses NO-affinity because the
affinity of the column is NONE.  So the index stores separate
entries in separate places for '123' and 123.  But the value
you are comparing against is always an integer, because it is
coming out of t1.id which has integer affinity.  So if you
look up the entry using just the integer value 123, you will
miss the '123' entry.  That is unacceptable.  Hence, you cannot
use a value with INTEGER-affinity as the key to an index 
with NO-affinity.

Hence the index on t2.id cannot be used to speed the search.

You can get the index to work by saying:

   create table t1(id int primary key, val int);
   create table t2(id INT unique, val int primary key);

Note the added INT in the definition of t2.id, thus
giving it integer affinity.  You'll still be able to store
text in t2.id if you want to, but if that text looks like
an integer, it is converted into an integer.

Please also not that INT PRIMARY KEY is not the same
thing as INTEGER PRIMARY KEY.  You probably want
to use INTEGER PRIMARY KEY in this context, not what
you have - but that is a whole other issue.

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


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



Re: [sqlite] Performance degradation after 3.3.17 -> 3.5.4 upgrade

2008-01-23 Thread drh
"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> On Jan 21, 2008 12:58 AM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote:
> > On Jan 20, 2008 11:32 PM,  <[EMAIL PROTECTED]> wrote:
> > > "Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> > > > Hello everyone.
> > > > I've discover performance degradation due to update 3.3.17 -> 3.5.4.
> > > > This SQL query work very slowly:
> > > >
> > > > DELETE FROM
> > > >   population_stamp
> > > > WHERE
> > > >   town_id IN (
> > > > SELECT DISTINCT town_id FROM population_stamp
> > > > EXCEPT
> > > > SELECT id FROM town
> > > >   );
> > > >
> > >
> > > I'll bet it will go a lot faster in both 3.3.17 and 3.5.4 if
> > > you omit the DISTINCT operator.  EXCEPT implies DISTINCT anyway,
> > > so you are not losing anything.  But SQLite does not optimize
> > > out redundant DISTINCTs so it is computing the DISTINCT twice.
> >
> > It's faster only for 3.5.4, but still slowly for 3.3.17 (time in
> > seconds: us - user, sy - system)
> > 3.5.4  +DISTINCT: 5.474us 0.287sy
> > 3.5.4  -DISTINCT: 3.397us 0.259sy
> > 3.3.17 -DISTINCT: 4.129us 0.228sy
> > 3.3.17 +DISTINCT: 2.959us 0.180sy
> >
> > These is timing example for my small testing database. With real
> > database difference will be more visible.
> 
> D. Richard Hipp, do you have any ideas why 3.3.17 version with
> DISTINCT works faster that new 3.5.4 without DISTINCT? In my test case
> difference is greater that 0.5 second.
> 

I have not researched it.  Somewhere along the line, somebody sent
me a patch that caused DISTINCT to be converted into a GROUP BY
and processed that way.  The claim was this made the processing
faster in most cases.  Perhaps you have discovered the case where
it actually slows things down.

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


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



Re: [sqlite] Query problem

2008-01-23 Thread drh
[EMAIL PROTECTED] wrote:
> 
> > This appears to be slightly different than normal *nix globbing since
> > SQLite uses '^' rather than '!' for the set inversion (if my reading of
> > the source is correct).
> 
> GLOB is suppose to exactly mimic Unix, except that SQLite does not
> break pattern matching at / boundaries the way the shell does.
> So if the previous statement is true, it is a bug.
> 

Experiments using bash indicate that either ^ or ! is accepted
as the negation of a character set.  Hence,

ls -d [^tu]*
ls -d [!tu]*

both return the same thing - a list of all files and directories
in the current directory whose names do not begin with "t" or "u".

SQLite only supports ^, not !.  I wonder if this is something I
should change?  It would not be much trouble to get GLOB to support
both, must like the globber in bash.

Anybody have an old Bourne shell around?  An authentic C-shell?
What do they do?

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



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



Re: [sqlite] Journal Files

2008-01-23 Thread drh
"Mark Riehl" <[EMAIL PROTECTED]> wrote:
> I'm working with version 3.5.2 under Linux.  I've got a database that
> is being shared between two processes and I'm running into issues with
> the journal file that doesn't go away.  When that happens, one process
> appears to have the lock on the database and the other process is
> essentially locked out.
> 
> A few questions for you:
> 
> 1. Is there a way to look at the contents of the journal file so that
> I know which insert statements are currently queued up?  This way, I
> can try and work backwards from there and see what the issue is.

If you are storing text data, I guess you could use "strings" too
see bits and pieces of it.  But it won't be in any recognizable
order.

> 
> 2. Is there a way to determine the current database state (unlocked,
> shared, pending, reserved, exclusive) ?
> 

http://www.sqlite.org/c3ref/c_fcntl_lockstate.html

For additional background information one what SQLite does
with locking and journal files, please see

http://www.sqlite.org/atomiccommit.html
http://www.sqlite.org/lockingv3.html

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


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



Re: [sqlite] Query problem

2008-01-23 Thread drh
Dennis Cote <[EMAIL PROTECTED]> wrote:
>> 
> SQLite seems to do the following:
> 
> The glob syntax supports the following patterns:
>   ? - matches any single character
>   * - matches zero or more characters
>   [seq] - matches any single character in seq
>   [!seq] - matches any single character not in seq
> 
> seq is one or more characters, such as abc. You may specify character 
> ranges using a dash. For example, a-z0-9 specifies all of the characters 
> in the English alphabet and the decimal digits 0 through 9.
> 
> This appears to be slightly different than normal *nix globbing since 
> SQLite uses '^' rather than '!' for the set inversion (if my reading of 
> the source is correct).

GLOB is suppose to exactly mimic Unix, except that SQLite does not
break pattern matching at / boundaries the way the shell does.
So if the previous statement is true, it is a bug.

> 
> It is not clear how you should escape these characters if you need to 
> match them literally. It may not be possible, since these characters are 
> not allowed in filenames and hence wouldn't need to be matched by *nix 
> commands.

Unix allows *any* characters in filenames except \000 and /.  You
can escape characters using [..].  To match a * anywhere in a string,
for example:

x GLOB '*[*]*'

The [..] pattern must contain at least one internal character.  So
to match a "]" you can use the pattern

x GLOB '*[]]*'

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


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



Re: [sqlite] May one software write to the SQLite database while a other read the same SQLite database ?

2008-01-23 Thread drh
Pierre8r <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> One SQLite database on my PC.
> Two softwares.
> May one software write to the SQLite database while a other read the 
> same SQLite database  ?
> 

Your programs cannot be reading and writing at exactly the
same instant in time.  But both programs can have the database
open for reading and writing.  While one program is writing,
the other is blocked from reading.  But the write normally 
only takes a few dozen milliseconds.  Surely your reader can
wait that long.

The waiting is handled for you automatically if you set

sqlite3_busy_timeout()

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


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



Re: [sqlite] Multiple databases

2008-01-23 Thread drh
"Mina R Waheeb" <[EMAIL PROTECTED]> wrote:
> Hi,
>I have few questions regarding the limitation of multiple databases with
> SQLite.
> 
> I have a large number of SQLite DB files with the same structure i
> need to query them all (looking for speed), I have tried ATTACH method
> and its working fine but there is a limitation on the number of
> attached files
> 
> in sqlitelimit.h
> /*
> ** The maximum number of attached databases.  This must be at least 2
> ** in order to support the main database file (0) and the file used to
> ** hold temporary tables (1).  And it must be less than 32 because
> ** we use a bitmask of databases with a u32 in places (for example
> ** the Parse.cookieMask field).
> */
> 
> - Is there is any way or patch to increase the number of attached
> files more than 32?

You could change the unsigned 32-bit integer used for the mask into
an unsigned 64-bit integer.  This will be tricky, though, as a lot
of things will need to change.

> 
> - is SQLite open FD to each attached database or open it on request?

Actually, 3 file descriptors per attached database.

> 
> - Once the SQLite library loaded in memory, How much the new
> connection cost of the system resources mainly in the memory?

That depends on the size of the database schema.

> 
> - If the schema is standard (Will not change), Is there is anyway just
> switch between the FD to avoid reparse on startup? sorry maybe this is
> a stupid question but i dunno much about SQLite internals.
> 

No.


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


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



Re: [sqlite] SQLite character comparisons

2008-01-21 Thread drh
John Elrick <[EMAIL PROTECTED]> wrote:
> 
> If we are ignoring trailing spaces, then by definition:
> 
> ' ' = ''
> 
> and for that matter:
> 
> '  ' = '  '
> 

Good point.  I added these as test cases for the new RTRIM
collation.  http://www.sqlite.org/cvstrac/chngview?cn=4735

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


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



Re: [sqlite] SQLite character comparisons

2008-01-20 Thread drh
"Fowler, Jeff" <[EMAIL PROTECTED]> wrote:
> I'm curious to know how many of you agree with Darren's 
> sentiments on this issue

Changing the behavior of SQLite to ignore trailing 
spaces is not an option for SQLite version 3, since
to do so would result in a incompatible file format
All indices created before the change would be invalid 
since they would use a different collation.  There 
are multiple thousands of SQLite applications and
hundreds of millions of existing SQLite database files
that depend on this backwards compatibility.  To make 
this change would therefore require bumping the version 
number up to SQLite 4.0.

>  
> Our app creates SQLite tables dynamically based on the output
> from user-defined queries that run against data warehouses 
> (of practically any "flavor") we have no control over, and
> we insert the results into SQLite. Sure - we can handle this
> situation by writing more code looking for spaces everywhere
> they might occur. But to me (and maybe only to me?), it makes
> sense for SQLite -- where reasonably possible -- to attempt
> to follow clear ANSI guidelines
>  

Check-in [4732] implements a built-in RTRIM collating sequence
that provides the ignore-spaces comparison semantics that you
desire.  All you have to do is add "COLLATE RTRIM" to the declarations
of text columns in your SQLite schema and SQLite will thereafter
ignore trailing spaces on comparisons involving those columns.
If you do SQL comparisons that do not involve columns, you can
put "COLLATE RTRIM" after the comparison operator itself to get
this behavior.  Example (an actual screen capture):

[EMAIL PROTECTED]:~/sqlite/bld> ./sqlite3
SQLite version 3.5.4
Enter ".help" for instructions
sqlite> SELECT 'abc'='abc   ';
0
sqlite> SELECT 'abc'='abc   ' COLLATE RTRIM;
1

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



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



Re: [sqlite] Performance degradation after 3.3.17 -> 3.5.4 upgrade

2008-01-20 Thread drh
"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> Hello everyone.
> I've discover performance degradation due to update 3.3.17 -> 3.5.4.
> This SQL query work very slowly:
> 
> DELETE FROM
>   population_stamp
> WHERE
>   town_id IN (
> SELECT DISTINCT town_id FROM population_stamp
> EXCEPT
> SELECT id FROM town
>   );
> 

I'll bet it will go a lot faster in both 3.3.17 and 3.5.4 if
you omit the DISTINCT operator.  EXCEPT implies DISTINCT anyway,
so you are not losing anything.  But SQLite does not optimize
out redundant DISTINCTs so it is computing the DISTINCT twice.

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


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



Re: [sqlite] SQLITE_MAX_EXPR_DEPTH

2008-01-18 Thread drh
Jerry Krinock <[EMAIL PROTECTED]> wrote:
> My query:
> 
> DELETE FROM `table1` WHERE (`id`=1 OR `id`=2 OR `id`=3 OR ... OR `id`=N)
> 
> using the C API.  When N exceeds 999, I get an error stating that the  
> maximum depth of 1000 has been exceeded, and this is documented in 
> http://www.sqlite.org/limits.html 
> , item 5.
> 
> Of course, I could fix this by doing multiple queries when N>999, but  
> my code is very nicely encapsulated as is, and that change would make  
> it all yucky.  So I'd like other alternatives.

The WHERE expression is parsed as follows:

   (...(((id=1 OR id=2) OR id=3) OR id=4) OR ...) OR id=N)

If you draw this as a tree, you find that, indeed, it is N
levels deep.  But by explicit use of parentheses, you can
force a balanced tree with a depth of only logN.

   (...((id=1 OR id=2) OR (id=3 OR id=4)) OR (...)...)

But instead of all that trouble, why not just say:

   id IN (1,2,3,4,5,...,N)

The latter is not only more efficient, but easier for human
readers to understand as well.

> 
> 1.  I would describe my query as "1000 clauses wide".  I'm not nesting  
> anything "1000 levels deep".  Is there a way to rewrite my query and  
> make it work?
> 
> 2.  Documentation implies that I can change the parameter  
> SQLITE_MAX_EXPR_DEPTH from the default of 1000.  But I can't find  
> SQLITE_MAX_EXPR_DEPTH in sqlite3.h.  Seems to be neither a compiler  
> macro nor a global.  (Mac OS X 10.5, sqlite 3.4.0).  Where is it?
> 

Look in sqliteLimit.h

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


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



Re: [sqlite] No way to dump a database with large BLOB's ?

2008-01-18 Thread drh
Lothar Scholz <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> Dumping a database with images i run into the 1 million byte per
> SQL statement limit. I thought that the usual way to backup a database
> is the sqlite.exe and dump and eval method.
> 
> Shouldn't this limit be dynamic instead of hard wired into a compile
> constant?
> 

The bytes/statement limit is removed by default beginning with
check-in [4636] on 2007-12-17.

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


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



Re: [sqlite] What is the precise definition of an identifier?

2008-01-18 Thread drh
Lothar Scholz <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> I have to write an sqlite syntax highligher for an editor
> and at the moment i use the following token BNF syntax.
> 
> ident := '_' | letter ( letter | '_' | digit )*
> 
> Is this correct?

This is correct depending on your definition of "letter"
and "digit".  SQLite understands letter to be any unicode
charater in the range 65-90, 97-122, or 128 and greater.
SQLite understands digit (in this context only) to be
unicode characters 48-57 and also the character 36.
See ticket number #1066 for information about the use
of character 36 as a digit.

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


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



Re: [sqlite] Why are strings in hexadecimal notation always blobs?

2008-01-18 Thread drh
Lothar Scholz <[EMAIL PROTECTED]> wrote:
> 
> Another question, how would you realiable represent contrl characters
> in the range 1-31 in a string? It is not really good to add them as
> plain code in text files and SQLite does not have C like backslash
> quoting. Especially the automatic %R%N->%N conversions might be a huge
> problem. And i don't think we should restrict the TEXT data type to
> anything more then non zero bytes.
> 

  CAST(x'0102030405' AS text)

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


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



Re: [sqlite] Date arithmetic question

2008-01-17 Thread drh
Gerry Snyder <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> >
> > So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00') should
> > return 1 even though the difference is really only 1 second?  Seems
> > goofy to me
> >
> >   
> 
> I have been staring at this until I'm getting goofy.
> 
> Written as it is, isn't the time interval 1 second short of two days?
> 
> If you want an interval of 1 second shouldn't it be
> 
> datediff('month', '2008-02-01 00:00:00','2008-01-31 23:59:59')
> 
> ?
> 
> 
> Gerry, more confused than usual (as usual)
> 

Yeah.  I got it backwards.  Sorry.
--
D. Richard Hipp <[EMAIL PROTECTED]>


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



Re: [sqlite] sqlite3 performace

2008-01-17 Thread drh
"Philip Nick" <[EMAIL PROTECTED]> wrote:
> Greetings,
> 
> Currently I am using sqlite3 in a multi-process/multi-threaded server
> setting.
> I use a Mutex to ensure only one process/thread can access the database at
> one time.
> 
> The current flow of events:
> Get Mutex
> Open Database connection
> Run Query
> Close Database connection
> Release Mutex
> 

SQLite does the mutexing automatically (assuming you are using
version 3.5.0 or later).
--
D. Richard Hipp <[EMAIL PROTECTED]>


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



  1   2   3   4   5   6   7   8   9   10   >