[sqlite] unused thread-related options in configure

2010-09-02 Thread Lapo Luchini 
configure and Makefile have ways to define SQLITE_THREAD_OVERRIDE_LOCK=1
and SQLITE_ALLOW_XTHREAD_CONNECT=1 but those flags are used nowhere in
the source code.
Couldn't them be removed from configure/Makefile too, for the sake of
avoiding confusion?

-- 
Lapo Luchini - http://lapo.it/

“The future is not google-able.” (William Gibson, 2004-02-05)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Pinarejos

2010-09-02 Thread Vicente Minguez Gzbarda 
Hello good first  introduced me in this  mailing list, I'm new  here.  I 
wish somebody would help me with  SQLite for S60 Nokia  phones.  Thanks in 
advance  and greetings to  everyone.

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

Re: [sqlite] Pinarejos

2010-09-02 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/02/2010 03:28 PM, Vicente Minguez Gzbarda wrote:
> Hello good first  introduced me in this  mailing list, I'm new  here.  I 
> wish somebody would help me with  SQLite for S60 Nokia  phones.  Thanks in 
> advance  and greetings to  everyone.

http://catb.org/esr/faqs/smart-questions.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkx/eCsACgkQmOOfHg372QT+aQCfQ9zfZXMUWpnJAmj7S/SaM/7T
x0sAoINwOKenPcp8ZnBJFGnUhyRQxIaE
=WdON
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] trouble when i insert during long time

2010-09-02 Thread andres felipe tamayo cortes 

Hi i made a program on C language who inserts some data on sqlite database, it 
works well but after a while (10,15 minutes) it stops to work and throws me 
this mistake:

on console:
:11373): Gtk-WARNING **: Error loading theme icon 'gtk-ok' for stock: 
No se ha podido cargar el módulo de carga de imágenes: 
/usr/lib/gtk-2.0/2.10.0/loaders/svg_loader.so: 
/usr/lib/gtk-2.0/2.10.0/loaders/svg_loader.so: no se puede abrír el archivo de 
objeto compartido: Demasiados archivos abiertos

on a window:
unable to open database file Unable to fetch row

i dont know what im making wrong, or if maybe is it a mitake of sqlite?


this its part of my code:




QSqlQuery inserta;
inserta.exec("insert into t1 (voltaje) values (1);");
 inserta.prepare("insert into t1 (voltaje, direccion) 
VALUES(:voltaje,:direccion)");//primero preparas la query asi
   //  inserta.bindValue(":voltaje",dest16[i]);//aca asignas la 
variable
inserta.bindValue(":voltaje",gua);//aca asignas la variable
inserta.bindValue(":direccion",addr1);
addr1=addr1+1;
if(!inserta.exec())
   {
QMessageBox::critical(this,"Error al grabar","No se pudo 
grabar en la base de datos:\n"+inserta.lastError().text());
   }
i hope you can helpme

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

Re: [sqlite] Feature request: copying vacuum

2010-09-02 Thread Shawn Wilsher 
On Wed, Sep 1, 2010 at 12:46 PM, Jay A. Kreibich  wrote:
>  On a personal level, I don't think it is worth it.  In the end, you're
>  still hoping the OS and filesystem will make smart choices about block
>  allocations.  An application shouldn't need to be spending a lot
>  of time worrying about this level of filesystem performance.  No
>  matter what, you're just hinting and setting up conditions that
>  should allow the filesystem driver to do something smart and fast.
>  It may, or it may not, actually do so.
Right, but giving it more hints means it's more likely to do the smart
and fast thing.  For what it's worth, Taras is working on improving
the performance of SQLite in Firefox.  He has data that shows that
this can dramatically reduce the number of page faults (Taras, please
correct me if I'm misrepresenting things) when loading the database.
These leads to faster startup times of Firefox.

Cheers,

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

[sqlite] Tcl bindings are a little problematic.

2010-09-02 Thread Paweł Salawa 
Hi,

It's pretty hard to make a SELECT from same table, but using two different 
aliases for the table and at the same time also getting column names in 
results - using Tcl bindings.

The SQL query would be:

select a1.txt, a2.txt 
  from a a1 
  join a a2
   using (ref) 
 where a1.lang = 'french' 
   and
   a2.lang = 'english'; 

We can either use:
db eval $query arr {
  parray arr
}

But then we would have arr(*) = a.txt a.txt
but only one entry: arr(a.txt) = something
which can fail (see thread on forum I mentioned below).

Other way is to use:
set results [db eval $query]
foreach cellValue $results {
  puts $cellValue
}

But then we lose informations about column names in results.

The problem was discussed here (escpecially first and last post in the 
thread): http://forum.sqlitestudio.one.pl/viewtopic.php?f=4&t=3596

One of possible solutions would be to add some prefix or suffix to array 
indexes.

Other one would be to provide column names in results of [db eval $query], 
using for example flag, like: [db eval $query -withColumnNames] or something 
like that. The results would be: [list $columnList $tableDataList]

I hope I explained it clearly enough. In case of any questions don't hesistate 
to ask.

Regards,
-- 
Paweł Salawa
p...@bitrock.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] upstreaming Chromium patches for file handle passing support

2010-09-02 Thread Shawn Wilsher 
On Wed, Sep 1, 2010 at 5:57 PM, Paweł Hajdan, Jr.
 wrote:
> Additionally, browsers seem to be moving to the multi-process architecture
> (Chrome, Firefox, WebKit2), so I wouldn't be surprised if you get more
> questions about this in the future, or just more forked copies.
FWIW, this won't be a concern for Mozilla.

Cheers,

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

[sqlite] sqlite3 default client pager ?

2010-09-02 Thread Arthur Rance 

Hello.

Is there a pager for sqlite3 default client ?

sqlite> select count(*) from mytable;

37899
sqlite> select * from mytable;
...
(too much entries, can't read anything)
...
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tcl bindings are a little problematic.

2010-09-02 Thread Richard Hipp 
On Wed, Sep 1, 2010 at 6:11 PM, Paweł Salawa  wrote:

> Hi,
>
> It's pretty hard to make a SELECT from same table, but using two different
> aliases for the table and at the same time also getting column names in
> results - using Tcl bindings.
>
> The SQL query would be:
>
> select a1.txt, a2.txt
>  from a a1
>  join a a2
>   using (ref)
>  where a1.lang = 'french'
>   and
>   a2.lang = 'english';
>
> We can either use:
> db eval $query arr {
>  parray arr
> }
>
> But then we would have arr(*) = a.txt a.txt
> but only one entry: arr(a.txt) = something
> which can fail (see thread on forum I mentioned below).
>

The column names on queries that do not have AS clauses on the columns are
undefined and are subject to change.  To get specific column names, use AS:

select a1.txt AS a1, a2.txt AS a2 

Then your arr() will contain entries a(*), a(a1), and a(a2).


>
> Other way is to use:
> set results [db eval $query]
> foreach cellValue $results {
>  puts $cellValue
> }
>
> But then we lose informations about column names in results.
>
> The problem was discussed here (escpecially first and last post in the
> thread): http://forum.sqlitestudio.one.pl/viewtopic.php?f=4&t=3596
>
> One of possible solutions would be to add some prefix or suffix to array
> indexes.
>
> Other one would be to provide column names in results of [db eval $query],
> using for example flag, like: [db eval $query -withColumnNames] or
> something
> like that. The results would be: [list $columnList $tableDataList]
>
> I hope I explained it clearly enough. In case of any questions don't
> hesistate
> to ask.
>
> Regards,
> --
> Paweł Salawa
> p...@bitrock.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] sqlite3 default client pager ?

2010-09-02 Thread Simon Davies 
On 2 September 2010 11:24, Arthur Rance  wrote:
>
> Hello.
>
> Is there a pager for sqlite3 default client ?
>
> sqlite> select count(*) from mytable;
>
> 37899
> sqlite> select * from mytable;
> ...
> (too much entries, can't read anything)
> ...

Not built in to the shell.
But you can do:
sqlite3 tst.db "select * from myTable;" | more

Or look into "SELECT ... LIMIT x OFFSET y"

>

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

[sqlite] unable to open database file/Disc I/O error

2010-09-02 Thread ferdinand . kraemer 

Hi,
we have a java application ( with sqlitejdbc-v056/windows vista)  which
runs only once on every PC to fill a new database.
On most machines everything works perfectly, but on some machines the
application fails with either "Unable to open database  file"
or " disk I/O error".
The database is of course there .
This errors occur on different locations in the code(with execute() or
executeBatch()), even when previous calls to this functions were
successfull..
In most cases the problems vanish when we run  the apllication again (but
this is not  really an option we have).
I searched the internet  and found that this errors might be caused by
locks on temporary files(we run Kaspersky Anti Virus on every machine)
Are there any other explanations for this behaviour?

Thanx


Ferdinand Krämer



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

[sqlite] memory wasted shm mapped file (3.7.2)

2010-09-02 Thread Michele Pradella 
  Hi,
I found a strange behavior of the sqlite 3.7.2 with WAL journal mode.
Yesterday I found my application DB with a -wal file of 1,5GB and a -shm 
file of few MB (about 9MB) with a DB file of 1,2GB: in this
situation I got the process memory wasted by "mapped file" of the -shm 
file. It seams that the file is mapped a lot of times in memory so the 
process memory become 2GB and it can't allocate more memory. In that 
situation operation made on the DB cause I/O disk errors probably due to 
the wasted memory.
I'm doing some other test to reproduce the problem, but I think that 
could be when I got a lot of operation between a BEGIN; COMMIT;
So is it ok to use the BEGIN; COMMIT; with the WAL journal activated?
is there some kind of limit in the number of operation between a BEGIN; 
COMMIT; statement?
I try to use the PRAGMA wal_checkpoint; to try resolve this situation, 
but seams that command was ignored by sqlite because the -wal file does 
not change in size, even the DB file.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

2010-09-02 Thread Richard Hipp 
On Thu, Sep 2, 2010 at 8:34 AM, Michele Pradella  wrote:

>  Hi,
> I found a strange behavior of the sqlite 3.7.2 with WAL journal mode.
> Yesterday I found my application DB with a -wal file of 1,5GB and a -shm
> file of few MB (about 9MB) with a DB file of 1,2GB: in this
> situation I got the process memory wasted by "mapped file" of the -shm
> file. It seams that the file is mapped a lot of times in memory so the
> process memory become 2GB and it can't allocate more memory. In that
> situation operation made on the DB cause I/O disk errors probably due to
> the wasted memory.
>

By coincidence, the SQLite developers were just discussing this problem
earlier this morning.  There are technical issues with windows that make a
solution difficult.  We are trying to come up with a work-around.  (The
problem you describe is specific to the windows backend and does not come up
in unix.)


> I'm doing some other test to reproduce the problem, but I think that
> could be when I got a lot of operation between a BEGIN; COMMIT;
> So is it ok to use the BEGIN; COMMIT; with the WAL journal activated?
> is there some kind of limit in the number of operation between a BEGIN;
> COMMIT; statement?
>

SQLite will not checkpoint the journal until you commit your transaction.
So if you leave the transaction open too long, the WAL file and the -shm
file will grow excessively large.  WAL works best with many smaller
transactions.  If you have one or two big transactions, then using a
traditional rollback-journal mode works better.



> I try to use the PRAGMA wal_checkpoint; to try resolve this situation,
> but seams that command was ignored by sqlite because the -wal file does
> not change in size, even the DB file.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] memory wasted shm mapped file (3.7.2)

2010-09-02 Thread Michele Pradella 
  ok, I'll wait for the walk around.
I always use a BEGIN; COMMIT; transaction but often, after a COMMIT; the 
-wal file does not change in size, it seams it's not checkponted.
Anyway do you think that with WAL journal mode I should continue to use 
BEGIN; COMMIT; statement? or not?


Il 02/09/2010 14.43, Richard Hipp ha scritto:
> On Thu, Sep 2, 2010 at 8:34 AM, Michele Pradella> wrote:
>>   Hi,
>> I found a strange behavior of the sqlite 3.7.2 with WAL journal mode.
>> Yesterday I found my application DB with a -wal file of 1,5GB and a -shm
>> file of few MB (about 9MB) with a DB file of 1,2GB: in this
>> situation I got the process memory wasted by "mapped file" of the -shm
>> file. It seams that the file is mapped a lot of times in memory so the
>> process memory become 2GB and it can't allocate more memory. In that
>> situation operation made on the DB cause I/O disk errors probably due to
>> the wasted memory.
>>
> By coincidence, the SQLite developers were just discussing this problem
> earlier this morning.  There are technical issues with windows that make a
> solution difficult.  We are trying to come up with a work-around.  (The
> problem you describe is specific to the windows backend and does not come up
> in unix.)
>
>
>> I'm doing some other test to reproduce the problem, but I think that
>> could be when I got a lot of operation between a BEGIN; COMMIT;
>> So is it ok to use the BEGIN; COMMIT; with the WAL journal activated?
>> is there some kind of limit in the number of operation between a BEGIN;
>> COMMIT; statement?
>>
> SQLite will not checkpoint the journal until you commit your transaction.
> So if you leave the transaction open too long, the WAL file and the -shm
> file will grow excessively large.  WAL works best with many smaller
> transactions.  If you have one or two big transactions, then using a
> traditional rollback-journal mode works better.
>
>
>
>> I try to use the PRAGMA wal_checkpoint; to try resolve this situation,
>> but seams that command was ignored by sqlite because the -wal file does
>> not change in size, even the DB file.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>


-- 
Selea s.r.l.


Michele Pradella R&D


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Detecting storage class from C

2010-09-02 Thread Andrew Wood 
Yes but none of those functions return the class that the declared type 
maps to (SQLITE_TEXT, SQLITE_FLOAT etc), they just return the SQL type 
as a string, of which there are umpteen possibilities.


Presumably then Im going to have to map them to the class myself? My 
question was, is this the case, or is there some other function in the 
API that will do this for me that Ive overlooked?

My reason for doing this is, if a field is null, I still need to know 
what class it 'should' have been if it had been storing a value.


On 31/08/10 02:58, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 08/31/2010 06:33 AM, Andrew Wood wrote:
>
>> I need to detect the declared storage class of a field rather than the
>> actual type stored, but as I understand it sqlite3_column_decltype
>> returns the SQL type not the SQLite storage class? Is there a way to get
>> this info?
>>  
> http://www.sqlite.org/c3ref/column_blob.html
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkx8Yb0ACgkQmOOfHg372QTp4wCeKIQzs1RD0E51ZT94bUoMMRWA
> rqcAnjID6Fklsj1cPBQwCVHjXQxLZuu+
> =Bscw
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> __
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> __
>

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

Re: [sqlite] Detecting storage class from C

2010-09-02 Thread Pavel Ivanov 
> My reason for doing this is, if a field is null, I still need to know
> what class it 'should' have been if it had been storing a value.

Why do you need that? No matter what you declare field can store any
type of data. And in SQLite there's no "declared storage class". You
are talking either about "affinity", or "declared column type", or
"actual data type stored'. So which one of those 3 exactly you want to
know and why?


Pavel

On Thu, Sep 2, 2010 at 9:22 AM, Andrew Wood  wrote:
> Yes but none of those functions return the class that the declared type
> maps to (SQLITE_TEXT, SQLITE_FLOAT etc), they just return the SQL type
> as a string, of which there are umpteen possibilities.
>
>
> Presumably then Im going to have to map them to the class myself? My
> question was, is this the case, or is there some other function in the
> API that will do this for me that Ive overlooked?
>
> My reason for doing this is, if a field is null, I still need to know
> what class it 'should' have been if it had been storing a value.
>
>
> On 31/08/10 02:58, Roger Binns wrote:
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 08/31/2010 06:33 AM, Andrew Wood wrote:
>>
>>> I need to detect the declared storage class of a field rather than the
>>> actual type stored, but as I understand it sqlite3_column_decltype
>>> returns the SQL type not the SQLite storage class? Is there a way to get
>>> this info?
>>>
>>     http://www.sqlite.org/c3ref/column_blob.html
>>
>> Roger
>> -BEGIN PGP SIGNATURE-
>> Version: GnuPG v1.4.10 (GNU/Linux)
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>>
>> iEYEARECAAYFAkx8Yb0ACgkQmOOfHg372QTp4wCeKIQzs1RD0E51ZT94bUoMMRWA
>> rqcAnjID6Fklsj1cPBQwCVHjXQxLZuu+
>> =Bscw
>> -END PGP SIGNATURE-
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> __
>> This email has been scanned by the MessageLabs Email Security System.
>> For more information please visit http://www.messagelabs.com/email
>> __
>>
>
> ___
> 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] Detecting storage class from C

2010-09-02 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/02/2010 06:52 PM, Andrew Wood wrote:
> Yes but none of those functions return the class that the declared type 
> maps to (SQLITE_TEXT, SQLITE_FLOAT etc), they just return the SQL type 
> as a string, of which there are umpteen possibilities.

> http://www.sqlite.org/c3ref/column_blob.html

If you are talking about the functions on that page then you are
incorrect.  They all operate on the current row, and the contents and
types of each column can differ for each row.

sqlite3_column_type() returns an int (effectively an enum) not a string
and says what the underlying type is for that column in this result row.

The other functions let you retrieve the column value as a variety of C
based types.  Typically if the value is SQLITE_FLOAT then you'd use
sqlite3_column_double etc, but you can call any of the routines and they
will convert as appropriate.  For example if the value is SQLITE_FLOAT
and you call sqlite3_column_int then the value will be converted to an
int, sqlite3_column_text will convert it to text etc.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkx/rFwACgkQmOOfHg372QRnmACgvZHcWpSk/2YfBZP8Ok/Fh/xr
RUcAoLBIR143KR71X3xqf38NlH07StSP
=esYO
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

2010-09-02 Thread Marcus Grimm 

Michele Pradella wrote:
>   ok, I'll wait for the walk around.
> I always use a BEGIN; COMMIT; transaction but often, after a COMMIT; the 
> -wal file does not change in size, it seams it's not checkponted.
> Anyway do you think that with WAL journal mode I should continue to use 
> BEGIN; COMMIT; statement? or not?

as Richard mentioned, the wal mode is not intended to work well
for bulk-insert kind of actions. You may try to split your insert
cycles into smaller pieces.

However, that might not help if you do sql statements which involve
a huge implicit transaction, for example "CREATE INDEX .." on a huge table.
At least on windows it can fail with IO error on a GB sized db.

Btw, I think the wal file doesn't shrink because sqlite doesn't truncate
that file after completing the checkpoint. That's by design I guess.

Marcus



> 
> 
> Il 02/09/2010 14.43, Richard Hipp ha scritto:
>> On Thu, Sep 2, 2010 at 8:34 AM, Michele Pradella>> wrote:
>>>   Hi,
>>> I found a strange behavior of the sqlite 3.7.2 with WAL journal mode.
>>> Yesterday I found my application DB with a -wal file of 1,5GB and a -shm
>>> file of few MB (about 9MB) with a DB file of 1,2GB: in this
>>> situation I got the process memory wasted by "mapped file" of the -shm
>>> file. It seams that the file is mapped a lot of times in memory so the
>>> process memory become 2GB and it can't allocate more memory. In that
>>> situation operation made on the DB cause I/O disk errors probably due to
>>> the wasted memory.
>>>
>> By coincidence, the SQLite developers were just discussing this problem
>> earlier this morning.  There are technical issues with windows that make a
>> solution difficult.  We are trying to come up with a work-around.  (The
>> problem you describe is specific to the windows backend and does not come up
>> in unix.)
>>
>>
>>> I'm doing some other test to reproduce the problem, but I think that
>>> could be when I got a lot of operation between a BEGIN; COMMIT;
>>> So is it ok to use the BEGIN; COMMIT; with the WAL journal activated?
>>> is there some kind of limit in the number of operation between a BEGIN;
>>> COMMIT; statement?
>>>
>> SQLite will not checkpoint the journal until you commit your transaction.
>> So if you leave the transaction open too long, the WAL file and the -shm
>> file will grow excessively large.  WAL works best with many smaller
>> transactions.  If you have one or two big transactions, then using a
>> traditional rollback-journal mode works better.
>>
>>
>>
>>> I try to use the PRAGMA wal_checkpoint; to try resolve this situation,
>>> but seams that command was ignored by sqlite because the -wal file does
>>> not change in size, even the DB file.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
> 
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] iCalendar (*.ics) import ?

2010-09-02 Thread Dan White 
I have Google-ed references to a utility called ical2sqlite, which claims to be 
able to make a database out of an iCalendar data file.

Anyone on this list have any experience with it ?

“Sometimes I think the surest sign that intelligent life exists elsewhere in 
the universe is that none of it has tried to contact us.”
Bill Waterson (Calvin & Hobbes)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

2010-09-02 Thread Richard Hipp 
On Thu, Sep 2, 2010 at 9:59 AM, Marcus Grimm wrote:

>
> Michele Pradella wrote:
> >   ok, I'll wait for the walk around.
> > I always use a BEGIN; COMMIT; transaction but often, after a COMMIT; the
> > -wal file does not change in size, it seams it's not checkponted.
> > Anyway do you think that with WAL journal mode I should continue to use
> > BEGIN; COMMIT; statement? or not?
>
> as Richard mentioned, the wal mode is not intended to work well
> for bulk-insert kind of actions. You may try to split your insert
> cycles into smaller pieces.
>
> However, that might not help if you do sql statements which involve
> a huge implicit transaction, for example "CREATE INDEX .." on a huge table.
> At least on windows it can fail with IO error on a GB sized db.
>

We are working on that problem.  In the meantime, your workaround is to
switch to journal_mode=DELETE before creating large indices.


>
> Btw, I think the wal file doesn't shrink because sqlite doesn't truncate
> that file after completing the checkpoint. That's by design I guess.
>

Correct.  The -wal file is deleted when the last connection to the database
is closed.  But prior to that, the WAL file is kept open and is not
truncated.  This is a performance optimization.  Most filesystems are faster
at overwriting an existing file than they are at appending to the end of a
file.  (Note the qualifier "Most" in the previous sentence.  There are
exceptions to the rule.  We try to optimize for the common case.)


>
> Marcus
>
>
>
> >
> >
> > Il 02/09/2010 14.43, Richard Hipp ha scritto:
> >> On Thu, Sep 2, 2010 at 8:34 AM, Michele Pradella<
> michele.prade...@selea.com
> >>> wrote:
> >>>   Hi,
> >>> I found a strange behavior of the sqlite 3.7.2 with WAL journal mode.
> >>> Yesterday I found my application DB with a -wal file of 1,5GB and a
> -shm
> >>> file of few MB (about 9MB) with a DB file of 1,2GB: in this
> >>> situation I got the process memory wasted by "mapped file" of the -shm
> >>> file. It seams that the file is mapped a lot of times in memory so the
> >>> process memory become 2GB and it can't allocate more memory. In that
> >>> situation operation made on the DB cause I/O disk errors probably due
> to
> >>> the wasted memory.
> >>>
> >> By coincidence, the SQLite developers were just discussing this problem
> >> earlier this morning.  There are technical issues with windows that make
> a
> >> solution difficult.  We are trying to come up with a work-around.  (The
> >> problem you describe is specific to the windows backend and does not
> come up
> >> in unix.)
> >>
> >>
> >>> I'm doing some other test to reproduce the problem, but I think that
> >>> could be when I got a lot of operation between a BEGIN; COMMIT;
> >>> So is it ok to use the BEGIN; COMMIT; with the WAL journal activated?
> >>> is there some kind of limit in the number of operation between a BEGIN;
> >>> COMMIT; statement?
> >>>
> >> SQLite will not checkpoint the journal until you commit your
> transaction.
> >> So if you leave the transaction open too long, the WAL file and the -shm
> >> file will grow excessively large.  WAL works best with many smaller
> >> transactions.  If you have one or two big transactions, then using a
> >> traditional rollback-journal mode works better.
> >>
> >>
> >>
> >>> I try to use the PRAGMA wal_checkpoint; to try resolve this situation,
> >>> but seams that command was ignored by sqlite because the -wal file does
> >>> not change in size, even the DB file.
> >>> ___
> >>> 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
>



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

[sqlite] ADOdb with SQLite-3.7.2

2010-09-02 Thread Rich Shepard 
   I've not before dealt with ADOdb, but a CMS I'm considering using to
change my company web site uses that. My Google search found one reference
(from 2009) suggesting that ADOdb does not play well with sqlite3. Is this
true (still)?

   If I can use it, I'll take a deeper look at the CMS.

Thanks,

Rich

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

[sqlite] Fwd: Question about SQLite

2010-09-02 Thread Richard Hipp 
-- Forwarded message --
From: Leran Liu 
Date: Thu, Sep 2, 2010 at 12:06 PM
Subject: Question about SQLite
To: d...@hwaci.com


Hi, I have question about the SQLite, do I need to install the exe in order
to use it in the client side?   Is there any alternatives just by deploying
the System.data.Sqlite?



Many thanks



*Leran Liu (Jason)*

* *

*Software Development Manager***

Phone: (800) 842-5758 x 1007

Direct:  (706) 629-3744 x 1007

Fax:   (972)-518-0958

www.phranywhere.com



*CONFIDENTIALITY NOTICE:** This e-mail and attachments, if any, may contain
confidential information, which is privileged and protected from disclosure
by Federal and State confidentiality laws, rules or regulations. This e-mail
and attachments, if any, are intended for the designated addressee only.  If
you are not the designated addressee, you are hereby notified that any
disclosure, copying, or distribution of this e-mail and its attachments, if
any, may be unlawful and may subject you to legal consequences. If you have
received this e-mail and attachments in error, please contact Health One
Alliance immediately at 800-842-5758 and delete the e-mail and its
attachments from your computer*





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

Re: [sqlite] trouble when i insert during long time

2010-09-02 Thread Eric Smith 
My Spanish isn't perfect but it looks like you're running out of file 
descriptors.  I.e. you (or a library or module you are using) is creating file 
descriptors using e.g. open(2) and never close(2)ing them. 

Please forgive typos--I sent this from my smart phone.

On Sep 1, 2010, at 15:34, andres felipe tamayo cortes 
 wrote:

> 
> Hi i made a program on C language who inserts some data on sqlite database, 
> it works well but after a while (10,15 minutes) it stops to work and throws 
> me this mistake:
> 
> on console:
> :11373): Gtk-WARNING **: Error loading theme icon 'gtk-ok' for 
> stock: No se ha podido cargar el módulo de carga de imágenes: 
> /usr/lib/gtk-2.0/2.10.0/loaders/svg_loader.so: 
> /usr/lib/gtk-2.0/2.10.0/loaders/svg_loader.so: no se puede abrír el archivo 
> de objeto compartido: Demasiados archivos abiertos
> 
> on a window:
> unable to open database file Unable to fetch row
> 
> i dont know what im making wrong, or if maybe is it a mitake of sqlite?
> 
> 
> this its part of my code:
> 
> 
> 
> 
> QSqlQuery inserta;
>   inserta.exec("insert into t1 (voltaje) values (1);");
>inserta.prepare("insert into t1 (voltaje, direccion) 
> VALUES(:voltaje,:direccion)");//primero preparas la query asi
>  //  inserta.bindValue(":voltaje",dest16[i]);//aca asignas la 
> variable
>   inserta.bindValue(":voltaje",gua);//aca asignas la variable
>   inserta.bindValue(":direccion",addr1);
>   addr1=addr1+1;
>   if(!inserta.exec())
>  {
>   QMessageBox::critical(this,"Error al grabar","No se pudo 
> grabar en la base de datos:\n"+inserta.lastError().text());
>  }
> i hope you can helpme
> 
> thanks!!
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Trigger Questions

2010-09-02 Thread Michael Graßl 
  Hello



I tried to update 2 colums of my recently added row in my table sites. 
The colums are createTime and updateTime, but sqlite only sets 
updateTime, the other colum stay plain. Sqlite doesn't complain about 
anything so I don't know whats wron.


CREATE TRIGGER insert_sites_createTime AFTER INSERT ON sites
BEGIN

UPDATE sites SET createTime = DATETIME('NOW') AND updateTime = 
DATETIME('NOW')
  WHERE rowid = new.rowid;

END;

best regards,
Michael Grassl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger Questions

2010-09-02 Thread Pavel Ivanov 
The correct syntax is

UPDATE sites SET createTime = DATETIME('NOW'), updateTime =
DATETIME('NOW')
         WHERE rowid = new.rowid;

So I wonder if you made a typo and actually only createTime were
updated when updateTime remained unchanged.


Pavel

On Thu, Sep 2, 2010 at 3:11 PM, Michael Graßl  wrote:
>  Hello
>
>
>
> I tried to update 2 colums of my recently added row in my table sites.
> The colums are createTime and updateTime, but sqlite only sets
> updateTime, the other colum stay plain. Sqlite doesn't complain about
> anything so I don't know whats wron.
>
>
> CREATE TRIGGER insert_sites_createTime AFTER INSERT ON sites
> BEGIN
>
> UPDATE sites SET createTime = DATETIME('NOW') AND updateTime =
> DATETIME('NOW')
>          WHERE rowid = new.rowid;
>
> END;
>
> best regards,
> Michael Grassl
> ___
> 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] Trigger Questions

2010-09-02 Thread Michael Graßl 
  Am 02.09.10 21:18, schrieb Pavel Ivanov:
> The correct syntax is
>
> UPDATE sites SET createTime = DATETIME('NOW'), updateTime =
> DATETIME('NOW')
>   WHERE rowid = new.rowid;

Thanks this works.

> So I wonder if you made a typo and actually only createTime were
> updated when updateTime remained unchanged.
>
>
Hm, I don't understand what you meaning, but this trigger get started 
when a new row is added, and there is a second trigger which updates 
only updateTime when a row gets updated



Michael


> Pavel
>
> On Thu, Sep 2, 2010 at 3:11 PM, Michael Graßl  wrote:
>>   Hello
>>
>>
>>
>> I tried to update 2 colums of my recently added row in my table sites.
>> The colums are createTime and updateTime, but sqlite only sets
>> updateTime, the other colum stay plain. Sqlite doesn't complain about
>> anything so I don't know whats wron.
>>
>>
>> CREATE TRIGGER insert_sites_createTime AFTER INSERT ON sites
>> BEGIN
>>
>> UPDATE sites SET createTime = DATETIME('NOW') AND updateTime =
>> DATETIME('NOW')
>>   WHERE rowid = new.rowid;
>>
>> END;
>>
>> best regards,
>> Michael Grassl
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Trigger Questions

2010-09-02 Thread Igor Tandetnik 
Pavel Ivanov  wrote:
> The correct syntax is
> 
> UPDATE sites SET createTime = DATETIME('NOW'), updateTime =
> DATETIME('NOW')
> WHERE rowid = new.rowid;
> 
> So I wonder if you made a typo and actually only createTime were
> updated when updateTime remained unchanged.

The original statement is valid, it just doesn't mean what the OP thought it 
meant:

UPDATE sites SET createTime =
(DATETIME('NOW') AND 
 (updateTime = DATETIME('NOW'))
WHERE rowid = new.rowid;

This says: set createTime to 1 if a) DATETIME('NOW') is logically true (always 
the case, since it produces a non-empty string) and b) updateTime is equal to 
DATETIME('NOW') (possible though unlikely). Otherwise, set createTime to 0 - 
which is likely what happened most of the time.
-- 
Igor Tandetnik


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

Re: [sqlite] Trigger Questions

2010-09-02 Thread Igor Tandetnik 
Michael Graßl  wrote:
>   Am 02.09.10 21:18, schrieb Pavel Ivanov:
>> The correct syntax is
>> 
>> UPDATE sites SET createTime = DATETIME('NOW'), updateTime =
>> DATETIME('NOW')
>>   WHERE rowid = new.rowid;
> 
> Thanks this works.
> 
>> So I wonder if you made a typo and actually only createTime were
>> updated when updateTime remained unchanged.
>> 
>> 
> Hm, I don't understand what you meaning, but this trigger get started
> when a new row is added, and there is a second trigger which updates
> only updateTime when a row gets updated

Since your INSERT trigger performs an update, it causes UPDATE trigger to fire. 
That's how updateTime got set. Now, you are setting it twice. If you are very 
unlucky, it is possible for createTime and updateTime to be different right 
after insert (if the second just happens to change right between the two 
triggers firing).

Igor Tandetnik

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

Re: [sqlite] Asynchronous I/O Module For SQLite vs Single T hread mode

2010-09-02 Thread Gilles 
Hi,

How pertinent is this source code (are the calls made in the right order?,...)?
Could this source code be a rather good basis for "Asynchronous I/O modules for
SQLite"?

Thanks a lot,

Best regards,

Gilles


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

Re: [sqlite] Trigger Questions

2010-09-02 Thread Max Vlasov 
On Fri, Sep 3, 2010 at 12:29 AM, Igor Tandetnik  wrote:

> > The correct syntax is
> >
> > UPDATE sites SET createTime = DATETIME('NOW'), updateTime =
> > DATETIME('NOW')
> > WHERE rowid = new.rowid;
> >
>

It's interesting, I once did a similar error with UPDATE (typing field1=..
and field2=... instead of field1=..,field2=) and it took me some time to
figure out why the results were different to what I expected. Most general
languages distinguish assignment and equality, but in SQL they're the same.
This gives it good aesthetics, but leads to bad cases like this. Sure it's
too late to change anything in the SQL, I just wish the creators changed
"SET=" to "SET TO"...

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

Re: [sqlite] Feature request: copying vacuum

2010-09-02 Thread Jay A. Kreibich 
On Thu, Sep 02, 2010 at 05:42:17AM +0200, Ben Danper scratched on the wall:
> 
> On Wed, Sep 1, 2010 at 12:46 PM, Jay A. Kreibich  wrote:
> > There is no reason to assume the filesystem
> > will over-write the existing allocations, rather than just create new
> > ones, especially if the pages are shuffled in groups...
> 
> Actually there's no reason to do the opposite,
> as it would fragment files that were contiguous in the first place.

  If a filesystem is asked to write out a buffer that is large enough
  to consist of several allocation blocks, it makes sense to write that
  buffer out as a contiguous set of blocks and reassign the allocations
  if the existing blocks are already fragmented.  That wouldn't change
  a file that is already reasonable contiguous, but would tend to bring
  a file back together.  Such a behavior would almost never fully
  defragment the file, but it would help... assuming at least a few
  writes are large, logically contiguous writes.  I'm guessing SQLite
  doesn't tend to do that.
  
  There are also flash-optimized filesystems that move just about every
  write in an attempt to even out the flash write cycles.

> > Maybe there would be some way to pre-populate the rollback journal
> > with the full contents of the original database. Then the file could
> > be truncated before the copy-back procedure. That would make it
> > clear to the OS that it is free to allocate whatever file blocks it
> > wants, hopefully in better patterns. The copy back could also be
> > done in very large chunks.
> 
> This is a fantastic idea! Not only truncate - since you know the new
> size, you could also set the size beforehand before you start
> copying the pages (similar to SQLITE_FCNTL_CHUNK_SIZE). Most
> filesystems will try very hard to place it contiguously.

  Good idea.

> A more involved idea that would improve efficiency (two copies instead
> of three, and twice the database size instead of three times) would
> be to use the journal file directly as the new database

  That does sound a lot more involved.  You would more or less need
  to rewrite the whole pager to deal with two different file formats.
  The VACUUM copy process is not write-only, it logically rebuilds
  the database from the ground up using SQL commands.  That means it
  does stuff like issue "CREATE INDEX..." commands on fresh tables.
  You would need full read/write/update support for the "journal pager."

   -j

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

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

Re: [sqlite] Trigger Questions

2010-09-02 Thread Michael Graßl 
  Thanks to all,


no I only update createTime at the first Trigger and the updateTime get 
(as Igor said) set with the update Trigger which get performed with the 
create trigger.


Best regards,
Michael



Am 02.09.10 22:32, schrieb Igor Tandetnik:
> Michael Graßl  wrote:
>>Am 02.09.10 21:18, schrieb Pavel Ivanov:
>>> The correct syntax is
>>>
>>> UPDATE sites SET createTime = DATETIME('NOW'), updateTime =
>>> DATETIME('NOW')
>>>WHERE rowid = new.rowid;
>> Thanks this works.
>>
>>> So I wonder if you made a typo and actually only createTime were
>>> updated when updateTime remained unchanged.
>>>
>>>
>> Hm, I don't understand what you meaning, but this trigger get started
>> when a new row is added, and there is a second trigger which updates
>> only updateTime when a row gets updated
> Since your INSERT trigger performs an update, it causes UPDATE trigger to 
> fire. That's how updateTime got set. Now, you are setting it twice. If you 
> are very unlucky, it is possible for createTime and updateTime to be 
> different right after insert (if the second just happens to change right 
> between the two triggers firing).
>
> Igor Tandetnik
>
> ___
> 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