[sqlite] Has anyone used sqlite for Pocket PC development?

[Monkey Code]

Hi,

I am planning to use sqlite with VS .Net 2003 Smart device C# application.
Just wondering if anyone has blazed down this path before and has any
insights to share.

Thanks!

~CodeMonkey8

Re: [sqlite] Performance & database design

[John Stanton]

Our approach to byte order independence was fairly simple, and worked 
well with a mmap'd index.  It involved keeping the just word pointers in
a local byte ordered block if the machine were a different Endian.  The 
overhead was next to insignificant.  Our indices were all byte order 
independent.


Avoiding buffer shadowing seemed to be one big win, and the other was 
letting the OS VM management take control.

JS

Nathan Kurz wrote:

On Wed, Mar 22, 2006 at 10:41:23AM +1100, John Stanton wrote:

The mmap'd index was about three times faster than when it 
used a LRU paged cache. 



I looked fairly closely into the possibility of using mmap for the
SQLite btree backend, and realized that it would be quite difficult.
Because the SQLite file format is host byte-order independent, it's
almost impossible to use mmap without a separate cache.  If one were
to give up on the cross-platform portability, I think one could get a
significant speedup on large file access, but it would be necessary to
write/adapt the entire backend rather than just making small changes.

Nathan Kurz
[EMAIL PROTECTED]

Re: [sqlite] Performance & database design

[Nathan Kurz]

On Wed, Mar 22, 2006 at 10:41:23AM +1100, John Stanton wrote:
> The mmap'd index was about three times faster than when it 
> used a LRU paged cache. 

I looked fairly closely into the possibility of using mmap for the
SQLite btree backend, and realized that it would be quite difficult.
Because the SQLite file format is host byte-order independent, it's
almost impossible to use mmap without a separate cache.  If one were
to give up on the cross-platform portability, I think one could get a
significant speedup on large file access, but it would be necessary to
write/adapt the entire backend rather than just making small changes.

Nathan Kurz
[EMAIL PROTECTED]

[sqlite] How do I install sqlite-odbc at ARM9 target board?

[杰 张]

Hi,all
  I just want to connect ASP and SQLite at ARM9 target board,but I don't known 
how to do it . Please help me!  Thank you so much!

__
赶快注册雅虎超大容量免费邮箱?
http://cn.mail.yahoo.com

Re: [sqlite] Performance & database design

[Joe Wilson]

Some people on the list have noted that inserting pre-sorted 
rows in sub-batches into SQLite is faster than inserting 
unsorted rows. Granted, you could only do this for one index 
per table, but might this be the basis of an optimization?
(I have not looked at the insert code. Perhaps SQLite is 
already doing this for all I know.)

Would this be a bad time to ask for multi-row insert support? :-)
INSERT INTO tbl_name(a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

> The way indices work in SQLite is that there is one row
> in the index for each row in the table but the index rows
> are in index order.  If the indexed values are randomly
> distributed in the table, that means building the index
> requires inserting each row in a random spot in the middle
> of the index.  Constantly inserting things in random places
> means that there is no locality of reference and the pager
> cache does not perform well.  It is not clear to me what
> can be done about this other than to completely redesign
> how indices work.  And even then, I cannot think of an
> alternative design that would do much better.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

[sqlite] Re: - [sqlite] Incrementing the value of a column

[rbundy]

Try:

   UPDATE table SET aColumn = someNewValue, numTouched = numTouched + 1
   WHERE ;

Regards.

rayB



|-+>
| |   Tito Ciuro   |
| |   <[EMAIL PROTECTED]> |
| ||
| |   22/03/2006 11:54 |
| |   Please respond to|
| |   sqlite-users |
| ||
|-+>
  
>--|
  | 
 |
  |   To:   Forum SQLite   
 |
  |   cc:   
 |
  |   Subject:  - [sqlite] Incrementing the value of a column   
 |
  
>--|




Hello,

This is the schema: ROWID INTEGER, myText TEXT, numTouched INTEGER

Each time I update a record I'd like to increment its 'numTouched'
column. I could read the record, retrieve the numTouched value,
increment it by 1, then update the record with the new text and
touched values.

Is there a way to do that without SELECT(ing) first the record?

Thanks,

-- Tito




** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING
*
*** Confidentiality and Privilege Notice
***

This e-mail is intended only to be read or used by the addressee. It is 
confidential and may contain legally privileged information. If you are not the 
addressee indicated in this message (or responsible for delivery of the message 
to such person), you may not copy or deliver this message to anyone, and you 
should destroy this message and kindly notify the sender by reply e-mail. 
Confidentiality and legal privilege are not waived or lost by reason of 
mistaken delivery to you.

Qantas Airways Limited
ABN 16 009 661 901

Visit Qantas online at http://qantas.com

[sqlite] Different column names in callback function on Linux vs. Windows

[Iulian Popescu]

Hello,

 

I'm doing a port of our application from Windows to Linux and one of the
problems I'm facing is when executing the following statement through a call
to sqlite3_exec():

 

SELECT mytable.'mycolumn' FROM table

 

The registered callback function 4th argument (a char**) denoting the column
names contains the string mytable.'mycolumn' on Windows and the string
mycolumn on Linux. Has anyone any idea why would that be?

 

Thanks, 

 

Iulian.

 

[sqlite] Scrolling thru an index

[JP]

In a previous message:


The way indices work in SQLite is that there is one row
in the index for each row in the table but the index rows
are in index order.  

...

D. Richard Hipp   <[EMAIL PROTECTED]>


Is there a way I can scroll thru a particular index?  For example:

1. Scroll forward/backward on a given set of records
2. Start at position X
3. Start at a record that matches a criteria

All of these based on a specific index?

This is kind of the way Dbase worked (browse, seek, locate, etc.)  Given 
that the "indexes" are there, can it be done?  Has someone done anything 
similar?


jp

Re: [sqlite] Re: Select with left outer join - Am I going mad or just having a bad day?

[Ian M. Jones]

On 21 Mar 2006, at 18:11, Ed Porter wrote:


Hi Ian,

You nee to use a Full Outer Join. I don't know if SQLite has this  
function.





Nah, definitely only wanted a left outer, didn't want the results  
multiplied up or anything, just wanted every Category and any Cases  
if matching the criteria.


It was solved by moving the where clause up into an AND on the join,  
seems that the where clause choking the result set from the outer  
join. Never seen this before, but then again I've generally used "old  
style" syntax joins (in the where clause), not ANSI, so I'm a bit new  
to how the where clause can effect the results of an ANSI join. But  
I'm learning!


Thanks for your response though Ed, much appreciated.

Regards,
--
Ian M. Jones
___
IMiJ Software
http://www.imijsoft.com
http://www.ianmjones.net (blog)

RE: [sqlite] Multithread access to DB

[Drew, Stephen]

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

RESERVED 
A RESERVED lock means that the process is planning on writing to the
database file at some point in the future but that it is currently just
reading from the file. Only a single RESERVED lock may be active at one
time, though multiple SHARED locks can coexist with a single RESERVED
lock. RESERVED differs from PENDING in that new SHARED locks can be
acquired while there is a RESERVED lock.   

-Original Message-
From: Rafal Rusin [mailto:[EMAIL PROTECTED] 
Sent: 21 March 2006 15:03
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multithread access to DB

Sqlite has 3 types of locks, which is weird for me.
Could You explain what is a "reserved lock"?
Read and read/write (exclusive) locks are apparent.

http://www.sqlite.org/capi3ref.html#sqlite3_busy_handler

Best Regards,
Rafal Rusin

TouK Company
(www.touk.pl)

Re: [sqlite] help: there is some error

[Ludovic Ferrandis]

You should try with an ARM library. I think the ARM linker should not
appreciate an X86 library.

On 3/21/06, 杰 张 <[EMAIL PROTECTED]> wrote:
>
> hello everyone,
>  I just want to test the connection between C and sqlite3, When I
> cross compile my program there is an error baffled me?
>
>  There are my steps and the error:
>$arm-linux-gcc -o sqltest sqltest.c libsqlite.a
>usr/local/arm-linux/lib/libc.so.6: could not read symbols：Invalid
> operation
> collect2: ld returned 1 exit status
>
>  In my sqltest.c I included sqlite3.h and I can make sure that
> sqlite3.h and
> libsqlite.a are the same version.
>  Help me!
>
>
>
> -
> 雅虎1G免费邮箱百分百防垃圾信
> 雅虎助手-搜索、杀毒、防骚扰
>

Re: [sqlite] Multithread access to DB

[Rafal Rusin]

Sqlite has 3 types of locks, which is weird for me.
Could You explain what is a "reserved lock"?
Read and read/write (exclusive) locks are apparent.

http://www.sqlite.org/capi3ref.html#sqlite3_busy_handler

Best Regards,
Rafal Rusin

TouK Company
(www.touk.pl)

[sqlite] help: there is some error

[杰 张]

hello everyone,
 I just want to test the connection between C and sqlite3, When I cross 
compile my program there is an error baffled me?
   
 There are my steps and the error:
   $arm-linux-gcc -o sqltest sqltest.c libsqlite.a
   usr/local/arm-linux/lib/libc.so.6: could not read symbols：Invalid operation
collect2: ld returned 1 exit status
   
 In my sqltest.c I included sqlite3.h and I can make sure that sqlite3.h and
libsqlite.a are the same version.
 Help me!



-
 雅虎1G免费邮箱百分百防垃圾信
 雅虎助手-搜索、杀毒、防骚扰  

Re: [sqlite] support for table partitioning?

[drh]

"Miha Vrhovnik" <[EMAIL PROTECTED]> wrote:
> On 3/21/2006, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> 
> >"Miha Vrhovnik" <[EMAIL PROTECTED]> wrote:
> >> Hi drh and others,
> >> 
> >> Regarding the issues they appear on ML with very large tables and knowing
> >> that sqlite now enforces constraint checks on tables, is there any
> >> chances of  suporting table partitoning?
> >> 
> >
> >Put each table in a separate database file then ATTACH
> >the databases.
> >
> That's not the same. You still need to rewrite queries, where in real
> table partitioning you don't need to do that.

What changes to the queries do you think are necessary?

> The select's and inserts
> are faster because db knows where to put/search for them.
> 

The database already knows exactly where to look for each
table when all the tables are in the same file.  All it
has to do is "lseek()" to the appropriate spot.  How does
moving tables into separate files help this or make it any
faster?

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

Re: [sqlite] support for table partitioning?

[drh]

"Miha Vrhovnik" <[EMAIL PROTECTED]> wrote:
> Hi drh and others,
> 
> Regarding the issues they appear on ML with very large tables and knowing
> that sqlite now enforces constraint checks on tables, is there any
> chances of  suporting table partitoning?
> 

Put each table in a separate database file then ATTACH
the databases.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] R: [sqlite] support for table partitioning?

[Zibetti Paolo]

> The database already knows exactly where to look for each table when all
the tables are in the same file.  
> All it has to do is "lseek()" to the appropriate spot.  How does moving
tables into separate files help this or make it any faster?
>

"Table partitioning" is a technique used to improve performance of large
databases running on large machines.
With table partitioning you can configure the DB so that it stores some
fields of a record in a file and the remaining fields of the same record in
a different file.
If each record is large, placing the two files on two different disks
usually speeds things up because reading (or writing) a record requires to
read, in parallel, half the record from one disk and the other half from the
other disk.
Performance also increases if your select happens to access only the fields
stored in one of the two files: if the select requires a sequential scan of
the entire (large) table, the DB manager will have to read through only half
the mount of data.
In my opinion however table partitioning is beyond the scope of a DB like
SQLite...

Bye




 -Messaggio originale-
Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Inviato:martedì 21 marzo 2006 14.48
A:  sqlite-users@sqlite.org
Oggetto:Re: [sqlite] support for table partitioning?

"Miha Vrhovnik" <[EMAIL PROTECTED]> wrote:
> On 3/21/2006, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> 
> >"Miha Vrhovnik" <[EMAIL PROTECTED]> wrote:
> >> Hi drh and others,
> >> 
> >> Regarding the issues they appear on ML with very large tables and
knowing
> >> that sqlite now enforces constraint checks on tables, is there any
> >> chances of  suporting table partitoning?
> >> 
> >
> >Put each table in a separate database file then ATTACH
> >the databases.
> >
> That's not the same. You still need to rewrite queries, where in real
> table partitioning you don't need to do that.

What changes to the queries do you think are necessary?

> The select's and inserts
> are faster because db knows where to put/search for them.
> 

The database already knows exactly where to look for each
table when all the tables are in the same file.  All it
has to do is "lseek()" to the appropriate spot.  How does
moving tables into separate files help this or make it any
faster?

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

[sqlite] support for table partitioning?

[Miha Vrhovnik]

Hi drh and others,

Regarding the issues they appear on ML with very large tables and knowing
that sqlite now enforces constraint checks on tables, is there any
chances of  suporting table partitoning?


Regards,
Miha

Re: [sqlite] support for table partitioning?

[Miha Vrhovnik]

On 3/21/2006, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:

>"Miha Vrhovnik" <[EMAIL PROTECTED]> wrote:
>> Hi drh and others,
>> 
>> Regarding the issues they appear on ML with very large tables and knowing
>> that sqlite now enforces constraint checks on tables, is there any
>> chances of  suporting table partitoning?
>> 
>
>Put each table in a separate database file then ATTACH
>the databases.
>--
>D. Richard Hipp   <[EMAIL PROTECTED]>
>
That's not the same. You still need to rewrite queries, where in real
table partitioning you don't need to do that. The select's and inserts
are faster because db knows where to put/search for them.

Regards,
Miha

Re: [sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

[Ralf Junker]

Hello Richard,

in case of stating the obvious: Thanks for your answer! It is always my 
pleasure reading your explanations on SQLite: Quick, precise, and right up to 
the point!

Ralf

>> While I understand from the FAQ that it might be problematic to use more 
>> than one thread with SQLITE_ENABLE_MEMORY_MANAGEMENT, I wonder if SQLite 
>> should cause an AV in this case?
>
>Yes it should.

Re: [sqlite] Performance & database design

[drh]

Micha Bieber <[EMAIL PROTECTED]> wrote:
> Appending all 25*10^6 rows takes 40
> minutes on a PC with 3 GHz, 1GB memory and sufficient large harddisk.
> 

Inserting 25-million rows in a table should be quick.
Building an index (or indices) on a 25-million row table
takes much longer. 

You did not show us the schema.  No doubt you have one
or more indices, either inplicit or explicit.  Building
the indices separately after the table has been constructed
tends to be faster - but only by a constant factor.

This issue of building huge tables keeps coming up.  Is
SQLite really showing performance problems here?  Is this
something that we need to work on?  Or is it just a hard
problem?  I'm curious to know how long it takes 
PostgreSQL/MySQL/Firebird to insert 25 million rows into
the same table.

The way indices work in SQLite is that there is one row
in the index for each row in the table but the index rows
are in index order.  If the indexed values are randomly
distributed in the table, that means building the index
requires inserting each row in a random spot in the middle
of the index.  Constantly inserting things in random places
means that there is no locality of reference and the pager
cache does not perform well.  It is not clear to me what
can be done about this other than to completely redesign
how indices work.  And even then, I cannot think of an
alternative design that would do much better.

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

Re: [sqlite] forcing primary key range

[drh]

jt <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> Is there a way to force the range value taken by a primary key?

When you insert a NULL into an INTEGER PRIMARY KEY, it always
converts the NULL to be one larger than the largest existing
value.  But you can specify a specific key and skip SQLite's
automatic value picker.  

   INSERT INTO table(rowid, )
   VALUES((SELECT rowid+1 FROM table WHERE rowid<429496796), ...);

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

Re: [sqlite] Performance of VIEW with UNION

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> 
> Does SQLite have a debug function to dump its parse tree 
> in readable ASCII form?
> 

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

Re: [sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

[Ralf Junker]

Hello Rob Lohman,

SQLite is multithreaded since 3.3.1. Still, threre are restrictions:

"When shared-cache mode is enabled, a database connection may only be used by 
the thread that called sqlite3_open() to create it. If another thread attempts 
to use the database connection, in most cases an SQLITE_MISUSE error is 
returned. However this is not guaranteed and programs should not depend on this 
behaviour, in some cases a segfault may result." 
[http://www.sqlite.org/sharedcache.html]

However, my example does NOT enable shared-cache mode and should therefor work 
fine IMO.

At least, SQLite should not cause the Access Violation. Or is this a bug?

Regards,

Ralf

>If I remember correctly you cannot use a SQLite database handle
>across threads. Each thread will need to open (and close) the
>database itself.

[sqlite] Performance & database design

[Micha Bieber]

Hallo list,

I'm relatively inexperienced when it comes to databases and SQL (but to
programming). I'm using sqlite's (recent version) C API called from a
C++ application.

I'm confronted with the following situation:

Ca. 2500-3000 objects (called 'entity') everyone with 3 properties (REAL
in sqlite notation) depending on 'positions' (x,y,z). (x,y,z) are
INTEGERS, the grid size might slightly vary between the entities.

The database in a first attempt looks like:

EntityTable

key  val1 val2 
1
2
.
.
2500

MatrixTable

key  key_to_entitytable   x  y  z  prop1 prop2 prop2
110  0  0. . .
.10  0  1. . .
.10  0  2. . .
...  .  .. . .
25*10^6  ..  .  .. . .


My problem is the second table. Appending all 25*10^6 rows takes 40
minutes on a PC with 3 GHz, 1GB memory and sufficient large harddisk.

1)
On a technical level, my usage of the sqlite API might be improper. So
I'll attach the relevant code encapsulating the appends :

The sql() functions utilizes more or less sqlite_exec + error handling,
but has no impact here.

table   ... name of the sqlite table
stmtmatrix  ... vector of rows ('BindRecord's' - 25 million for the mentioned 
case)

void Database::append(const QString& table, const vector& 
stmtmatrix,
  bool skipprimarykey /*=true*/)
{
  if (stmtmatrix.empty())
return;

  QString pstr("insert into ");
  pstr += table + " values (";
  if (skipprimarykey)
pstr += "NULL, ";
  pstr += "?";
  for (unsigned i=1; i!=stmtmatrix[0].values.size(); ++i)
  {
pstr  += ",?";
  }
  pstr += ")";

  int c = 0;
  sql("begin");
  sqlite3_stmt *stmt;
  if (sqlite3_prepare(
 db_, 
 pstr.ascii(),  // stmt
-1,
,
 0
   )!= SQLITE_OK) 
  {
printf("\nCould not prepare statement.");
return;
  }
  for (unsigned i=0; i!=stmtmatrix.size(); ++i) // iterating rows
  { 
for (unsigned j = 0; j!=stmtmatrix[i].values.size(); ++j)
{
  int ERR = sqlite3_bind_text (
  stmt,
  j+1,  // Index of wildcard
  stmtmatrix[i].values[j].ascii(),
  stmtmatrix[i].values[j].length(),  // length of text
  SQLITE_STATIC
  );
  
if (ERR != SQLITE_OK) 
{
  printf("\nCould not prepare statement.");
} 
}
sqlite3_step(stmt); 
sqlite3_reset(stmt);
++c;
if (c==10)
{
  sql("commit");
  sql("begin");
  c=0;
}
  }
  sql("commit");  
  sqlite3_finalize(stmt);
}

Calling Database::.sql("PRAGMA synchronous = OFF")
before Database::append improves speed, but not beyond the 40 minutes
cited. Can someone elaborate on the code regarding performance ?

2)
Database design. Is this big table a good choice or has anyone
recommendations for better design. For example, splitting the big table
into smaller ones (assigned to a single entity) with ~10 rows and
later on building database requests utilizing joints ? Or something
completely different ?

Thank you,
Micha
-- 

Re: [sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

[drh]

Ralf Junker <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> I am using SQLite compiled with SQLITE_ENABLE_MEMORY_MANAGEMENT on Win32.
> 

Why?  What do you hope to accomplish by using MEMORY_MANAGEMENT on
Win32?  MM is designed for use on embedded devices with very tight
memory restrictions.  It is not intended for use on workstations,
which is why it is off by default.

> I execute the following psydocode, all with same DB handle:
> 
> * In application's main thread: sqlite3_open
> * Create a new thread
> * In new thread: sqlite3_close

When SQLITE_ENABLE_MEMORY_MANAGEMENT is turned on, it is an error
to move a database connection from one thread to another.

> 
> This creates an access violation in pager.c, lines 2065 to 2076:
> 
> #ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
>   /* Remove the pager from the linked list of pagers starting at 
>   ** ThreadData.pPager if memory-management is enabled.
>   */
>   if( pPager==pTsd->pPager ){
> pTsd->pPager = pPager->pNext;
>   }else{
> Pager *pTmp;
> for(pTmp = pTsd->pPager; pTmp->pNext!=pPager; pTmp=pTmp->pNext);
> pTmp->pNext = pPager->pNext;
>   }
> #endif

And here is one reason why it is an error:  When MM is enabled, 
SQLite keeps a linked list of every pager structure used in each 
thread.  (It has to do this in order to support some features of MM.)
When you close a connection it must unlink that pager from the
linked list.  But it can only do so if the connection is closed
from the same thread in which it was created.


> 
> While I understand from the FAQ that it might be problematic to use more than 
> one thread with SQLITE_ENABLE_MEMORY_MANAGEMENT, I wonder if SQLite should 
> cause an AV in this case?

Yes it should.

> 
> I even found that other SQL instructions, like INSERT, work fine when called 
> from the 2nd thread.

You were lucky.

Re: [sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

[Ralf Junker]

Hello Ludovic Ferrandis,

thanks, but this is not quite true for the latest version:

http://www.sqlite.org/faq.html#q8 reads:

"The restriction on moving database connections across threads was relaxed 
somewhat in version 3.3.1. With that and subsequent versions, it is safe to 
move a connection handle across threads as long as the connection is not 
holding any fcntl() locks. You can safely assume that no locks are being held 
if no transaction is pending and all statements have been finalized."

Opening a DB in one thread and closing it in another works fine without 
SQLITE_ENABLE_MEMORY_MANAGEMENT, so I assume that this is causing me trouble. 
Especially, since SQLite causes an AV which crashes the application instead of 
issuing an error message or just silently do nothing about the shared memory.

Ralf

>You have to call open & close in the same thread, and in each thread if
>needed.

Re: [sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

[Rob Lohman]

Hi Ralf,

If I remember correctly you cannot use a SQLite database handle
across threads. Each thread will need to open (and close) the
database itself.

Cheers,

Rob

- Original Message - 
From: "Ralf Junker" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, March 21, 2006 12:02 PM
Subject: [sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB 
in different threads




Hello,

I am using SQLite compiled with SQLITE_ENABLE_MEMORY_MANAGEMENT on Win32.

I execute the following psydocode, all with same DB handle:

* In application's main thread: sqlite3_open
* Create a new thread
* In new thread: sqlite3_close

This creates an access violation in pager.c, lines 2065 to 2076:

#ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
 /* Remove the pager from the linked list of pagers starting at
 ** ThreadData.pPager if memory-management is enabled.
 */
 if( pPager==pTsd->pPager ){
   pTsd->pPager = pPager->pNext;
 }else{
   Pager *pTmp;
   for(pTmp = pTsd->pPager; pTmp->pNext!=pPager; pTmp=pTmp->pNext);
   pTmp->pNext = pPager->pNext;
 }
#endif

While I understand from the FAQ that it might be problematic to use more 
than one thread with SQLITE_ENABLE_MEMORY_MANAGEMENT, I wonder if SQLite 
should cause an AV in this case?


I even found that other SQL instructions, like INSERT, work fine when 
called from the 2nd thread.


Could anybody help, please?

Thanks & regards,

Ralf

Re: [sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

[Ludovic Ferrandis]

You have to call open & close in the same thread, and in each thread if
needed.

" The returned sqlite3* can only be used in the same thread in which it was
created. It is an error to call
sqlite3_open()
in one thread then pass the resulting database handle off to another thread
to use. This restriction is due to goofy design decisions (bugs?) in the way
some threading implementations interact with file locks."



Hope this help

Ludovic

On 3/21/06, Ralf Junker <[EMAIL PROTECTED]> wrote:
>
> Hello,
>
> I am using SQLite compiled with SQLITE_ENABLE_MEMORY_MANAGEMENT on Win32.
>
> I execute the following psydocode, all with same DB handle:
>
> * In application's main thread: sqlite3_open
> * Create a new thread
> * In new thread: sqlite3_close
>
> This creates an access violation in pager.c, lines 2065 to 2076:
>
> #ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
>   /* Remove the pager from the linked list of pagers starting at
>   ** ThreadData.pPager if memory-management is enabled.
>   */
>   if( pPager==pTsd->pPager ){
> pTsd->pPager = pPager->pNext;
>   }else{
> Pager *pTmp;
> for(pTmp = pTsd->pPager; pTmp->pNext!=pPager; pTmp=pTmp->pNext);
> pTmp->pNext = pPager->pNext;
>   }
> #endif
>
> While I understand from the FAQ that it might be problematic to use more
> than one thread with SQLITE_ENABLE_MEMORY_MANAGEMENT, I wonder if SQLite
> should cause an AV in this case?
>
> I even found that other SQL instructions, like INSERT, work fine when
> called from the 2nd thread.
>
> Could anybody help, please?
>
> Thanks & regards,
>
> Ralf
>
>

[sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

[Ralf Junker]

Hello,

I am using SQLite compiled with SQLITE_ENABLE_MEMORY_MANAGEMENT on Win32.

I execute the following psydocode, all with same DB handle:

* In application's main thread: sqlite3_open
* Create a new thread
* In new thread: sqlite3_close

This creates an access violation in pager.c, lines 2065 to 2076:

#ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
  /* Remove the pager from the linked list of pagers starting at 
  ** ThreadData.pPager if memory-management is enabled.
  */
  if( pPager==pTsd->pPager ){
pTsd->pPager = pPager->pNext;
  }else{
Pager *pTmp;
for(pTmp = pTsd->pPager; pTmp->pNext!=pPager; pTmp=pTmp->pNext);
pTmp->pNext = pPager->pNext;
  }
#endif

While I understand from the FAQ that it might be problematic to use more than 
one thread with SQLITE_ENABLE_MEMORY_MANAGEMENT, I wonder if SQLite should 
cause an AV in this case?

I even found that other SQL instructions, like INSERT, work fine when called 
from the 2nd thread.

Could anybody help, please?

Thanks & regards,

Ralf

[sqlite] R: [sqlite] SQLITE3.DLL fails to load if address 0x60900000 occupied and DLL recompilation

[Clinco, Michele]

It is not. I tried, but does not work.

Bye, Michele

-Messaggio originale-
Da: Rob Lohman [mailto:[EMAIL PROTECTED] 
Inviato: martedì, 21. marzo 2006 10:11
A: sqlite-users@sqlite.org
Oggetto: Re: [sqlite] SQLITE3.DLL fails to load if address 0x6090 occupied 
and DLL recompilation

Hi,

According to that ticket it is fixed in the latest
download on the website. So there should be
no need to recompile yourself unless it is an
older version?

Cheers,

Rob

- Original Message - 
From: "Clinco, Michele" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, March 21, 2006 9:37 AM
Subject: [sqlite] SQLITE3.DLL fails to load if address 0x6090 occupied 
and DLL recompilation


Hallo.



After I changed my development machine, I was not able any more to load
the SQLITE3.DLL in my .Net application.



Looking around, I found this article that describes the problem.



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



The solution is to recompile the DLL with visual studio:



I created a new project with Visual Studio 2005 and I recompiled the
DLL, everything works.

There are two things I'm a bit worried about:

The first is the huge number of parameters of the compiler, maybe they
are right, maybe they are wrong, but how can we know it in a
deterministic way?

The second is the number of warnings I received in the compilation: 144.
The warnings are of two categories, deprecated functions (sprintf )
and unsafe typecast (signed/unsigned, int/double)



Is there anybody who already did this compilation?



Bye, Michele

Re: [sqlite] SQLITE3.DLL fails to load if address 0x60900000 occupied and DLL recompilation

[Rob Lohman]

Hi,

According to that ticket it is fixed in the latest
download on the website. So there should be
no need to recompile yourself unless it is an
older version?

Cheers,

Rob

- Original Message - 
From: "Clinco, Michele" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, March 21, 2006 9:37 AM
Subject: [sqlite] SQLITE3.DLL fails to load if address 0x6090 occupied 
and DLL recompilation



Hallo.



After I changed my development machine, I was not able any more to load
the SQLITE3.DLL in my .Net application.



Looking around, I found this article that describes the problem.



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



The solution is to recompile the DLL with visual studio:



I created a new project with Visual Studio 2005 and I recompiled the
DLL, everything works.

There are two things I'm a bit worried about:

The first is the huge number of parameters of the compiler, maybe they
are right, maybe they are wrong, but how can we know it in a
deterministic way?

The second is the number of warnings I received in the compilation: 144.
The warnings are of two categories, deprecated functions (sprintf )
and unsafe typecast (signed/unsigned, int/double)



Is there anybody who already did this compilation?



Bye, Michele

[sqlite] forcing primary key range

[jt]

Hello,

Is there a way to force the range value taken by a primary key?

Suppose I have a table that will never contain more than 2**20 rows.
On some occasion, I want new inserted rows to have a pkey in [0,
2**32), on another occasion they would take their value in [2**32,
2*33), etc. In my problem, I can ensure that a range will not be
overflowed.

So I need to be able to set the pkey range, back and forth depending
on the "occasion".
I tried to fool the sqlite_sequence table but it didn't work.

Is there a way to do this natively?

--
jt

[sqlite] SQLITE3.DLL fails to load if address 0x60900000 occupied and DLL recompilation

[Clinco, Michele]

Hallo.

 

After I changed my development machine, I was not able any more to load
the SQLITE3.DLL in my .Net application.

 

Looking around, I found this article that describes the problem.

 

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

 

The solution is to recompile the DLL with visual studio:

 

I created a new project with Visual Studio 2005 and I recompiled the
DLL, everything works.

There are two things I'm a bit worried about: 

The first is the huge number of parameters of the compiler, maybe they
are right, maybe they are wrong, but how can we know it in a
deterministic way?

The second is the number of warnings I received in the compilation: 144.
The warnings are of two categories, deprecated functions (sprintf )
and unsafe typecast (signed/unsigned, int/double)

 

Is there anybody who already did this compilation?

 

Bye, Michele

 

Re: [sqlite] "Database is locked" error in PHP via PDO despite setting timeout

[John Stanton]

If you want to use a lightweight DB like Sqlite and you are setting up 
your own daemon and server situation then you can place the DB 
synchronization function in the daemon around the Sqlite so that its 
action is single streamed.  In a similar situation we have installations 
which manage many hundreds of simultaneous users.


If you don't want to do that, use a DBMS like PostgreSQL which manages 
it all for you by having a DB server, not linking the DB function into 
the application.


Mark Robson wrote:

On Monday 20 March 2006 11:47, [EMAIL PROTECTED] wrote:


BTW: Lots of people have multiple processes writing to the same
SQLite database without problems - the SQLite website is a good
example.  I do not know what you are doing wrong to get the
locking problems you are experiencing.



I don't know how they manage it (unless of course, many of their writes fail 
and the txns roll back, and they don't notice or care).


On Monday 20 March 2006 11:58, Roger wrote:


I am developing a web based application in PHP/Sqlite and i am forever
getting that error. What i normally do is a simple

service httpd restart.



This is no good. I'm creating a daemon-based server application, which is 
carrying out autonomous tasks. It does not currently run under httpd, and I 
have no plans to make it do so.


I have several processes which are carrying out a fair amount of work inside a 
transaction - doing several writes, then doing some other time-consuming 
operations, then providing everything goes OK, committing these transactions.


This means that there are some relatively long-lived transactions (several 
seconds, anyway) in progress.


However, with proper locking this should NOT cause a problem - it should 
simply serialise the transactional operations (or so I thought).


As it is, I've actually tried to port this to MySQL (using Mysql5 and InnoDB), 
but I'm getting some problems there too - I think I'll have to review my use 
of transactions etc.


Regards
 Mark