Re: [sqlite] Will a read-only transaction cause writes to the WAL?

[Virgilio Alexandre Fornazin]

 Just curious why you wrap your SELECT statement into a 'TRANSACTION' ...

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Martin C.
Sent: quinta-feira, 16 de setembro de 2010 14:16
To: sqlite-users@sqlite.org
Subject: [sqlite] Will a read-only transaction cause writes to the WAL?

Hi,

I am using SQLite on a NAND based embedded system, and to ensure the
lifetime of the NAND I need to optimize writes to the NAND.

Can you tell me, if a read-only transaction, by which I mean
   BEGIN TRANSACTION
   SELECT * from SomeTable;
   COMMIT
will cause any writes to the WAL?

What about the -shm file, is it likely that the transaction will cause
a write to the NAND in this case?

Is there any statistics about actual writes I can query to analyze
write-behavior of my software?

Thanks in advance!

Best regards,
Martin
___
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] Query critique

[Virgilio Alexandre Fornazin]

Calling UPPER() for each field/value while querying is a pain, if you can
assume the names are all in uppercase 
(forcing this in your program before passing them to SQlite), you could
speed up things a lot. But you need to 
analyze what´s happening inside SQlite to check if indices are used, etc.

SQlite command line app has a command that does this (does not remember if
it´s this:
".explain ON|OFFTurn output mode suitable for EXPLAIN on or off.\n")

'ANALYZE' SQlite command also performs some kind of prefetching of indexes
and loads results in a internal 
table, can help also to speed up.

Also, try to avoid JOINS like this

'SELECT a.* FROM globalRankingTable a, friendTable b WHERE upper(b.player) =
upper('?') AND upper(b.friend) = 
upper(a.name))'

It´s better to explicit use LEFT JOIN / INNER JOIN always.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns
Sent: sábado, 10 de julho de 2010 17:39
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query critique

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/09/2010 01:54 AM, Ian Hardingham wrote:
> And here is my query (written in a script language):

Are you actually getting correct answers from this?

>  upper('?') 

Unless your script language includes a SQL parser, that does not do what you
think it does.  It is seeing if the value matches upper cased question mark.
 It looks like you wanted to supply bound parameters so the question mark
should not be in quotes.

I'd also suggest using a better wrapper or scripting language since you are
providing three bindings but the query is only using one of them.  It should
complain.

> - yes I know I'm an idiot with the upper stuff, a refactor is needed on 
> that - I know all of my "name" fields should really be integers.

Or look into indices and collations.

In any event you should always post your exact actual query so we don't have
to figure out issues like the question marks are intentional or
transcription errors.

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

iEYEARECAAYFAkw42kcACgkQmOOfHg372QSgxQCfTtgfYFMZ9a4nHtbJn8gI7XEK
7wYAn3ghSgc3wPgl0bFGLq8oA2moTJcs
=aCUo
-END PGP SIGNATURE-
___
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] Null character problem

[Virgilio Alexandre Fornazin]

>> My question came purely from a mild curiosity.  I was wondering about 
>> the behavior of sqlite call sqlite3_bind_text when it is passed a range 
>> of BYTES that includes nulls.

See this snipper for documentation:

"The third argument is the value to bind to the parameter.

In those routines that have a fourth argument, its value is the number of
bytes in the parameter. To be clear: the value is the number of bytes in the
value, not the number of characters. If the fourth parameter is negative,
the length of the string is the number of bytes up to the first zero
terminator.

The fifth argument to sqlite3_bind_blob(), sqlite3_bind_text(), and
sqlite3_bind_text16() is a destructor used to dispose of the BLOB or string
after SQLite has finished with it. If the fifth argument is the special
value SQLITE_STATIC, then SQLite assumes that the information is in static,
unmanaged space and does not need to be freed. If the fifth argument has the
value SQLITE_TRANSIENT, then SQLite makes its own private copy of the data
immediately, before the sqlite3_bind_*() routine returns."

http://sqlite.org/c3ref/bind_blob.html

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Eric Smith
Sent: sexta-feira, 9 de julho de 2010 14:54
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Null character problem

Simo Slavin wrote: 

> (according to your earlier post) 

I'm not OP.  I'm Eric.  OP was someone else.  In this context, I don't 
care about blobs or about the right way of doing anything.

> Read the documentation for memset().  

I know quite well how memset works.  I know character!=byte.  These 
matters are irrelevant to my question.  

My question came purely from a mild curiosity.  I was wondering about 
the behavior of sqlite call sqlite3_bind_text when it is passed a range 
of BYTES that includes nulls.

-- 
Eric A. Smith

It's up.  It sorta works.  That's a start.
-- BJ Premore
___
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] is this a bug?

[Virgilio Alexandre Fornazin]

It´s good to try to reproduce all conditions that this problem happens, to
help with creating test-cases
and with bug fix.

[]'s

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor
Sent: sábado, 19 de dezembro de 2009 00:38
To: General Discussion of SQLite Database
Subject: Re: [sqlite] is this a bug?

On Fri, Dec 18, 2009 at 8:23 PM, Valerio Aimale  wrote:
> Hello all,
>
> I've run into an interesting situation; when duplicating parenthesis
> around a 'in ()' subquery, only the first row is returned.
>
> This is not my real-life query, but a test that replicates the problem.
>
> Thanks,
>
> Valerio
>
> $ sqlite3 --version
> 3.6.16
>
> prepare some dummy data:
>
> create table test ( id INT );
> insert into test VALUES(1);
> insert into test VALUES(2);
> insert into test VALUES(3);
> insert into test VALUES(4);
> insert into test VALUES(5);
> insert into test VALUES(6);
> insert into test VALUES(7);
> insert into test VALUES(8);
> insert into test VALUES(9);
> insert into test VALUES(10);
>
> sqlite> select id from test where (id > 5);
> 6
> 7
> 8
> 9
> 10
> [Good]
>
> sqlite> select id from test where id in (select id from test where (id >
> 5));
> 6
> 7
> 8
> 9
> 10
> [Still Good]
>
> Now let's duplicate parenthesis around the subquery:
>
> sqlite> select id from test where id in ((select id from test where (id
>  > 5)));
> 6
>
> Why only one value returned when parenthesis are duplicated?
>
> Same with triple parenthesis enclosing:
>
> sqlite> select id from test where id in (((select id from test where (id
>  > 5;
> 6
>


Given the above table

sqlite> SELECT id FROM test WHERE id IN (6, 7, 8, 9, 10);
id
--
6
7
8
9
10
sqlite> SELECT id FROM test WHERE id IN ((6, 7, 8, 9, 10));
SQL error: near ",": syntax error
sqlite>

Seems like IN expects a comma separated list, and nothing else within
a single set of parens.




-- 
Puneet Kishor
___
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] is this a bug?

[Virgilio Alexandre Fornazin]

This is probably a bug in SQL parser, that doesn't reduced to one
parenthesis, 
causing the side effect in VDBE engine, that doesn't returned all ID´s to IN

evaluator in first select.

This should be easily reproduced, but the fix you must wait for Richard or
other
that have knowledge on VDBE instruction debugging.


I've tested the same thing on SQL Server 2008, correct results appeared:

---
create database x
go

use x
go

create table test (id int not null, primary key(id))
go

insert into test values (1)
insert into test values (2)
insert into test values (3)
insert into test values (4)
insert into test values (5)
insert into test values (6)
insert into test values (7)
insert into test values (8)
insert into test values (9)
insert into test values (10)
go

select id from test where id in (select id from test where id > 5)
go

id
---
6
7
8
9
10

(5 row(s) affected)

select id from test where id in (select id from test where (id > 5))
go

id
---
6
7
8
9
10

(5 row(s) affected)

select id from test where id in ((select id from test where (id > 5)))
go

id
---
6
7
8
9
10

(5 row(s) affected)


[]'s


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Valerio Aimale
Sent: sábado, 19 de dezembro de 2009 00:23
To: sqlite-users@sqlite.org
Subject: [sqlite] is this a bug?

Hello all,

I've run into an interesting situation; when duplicating parenthesis 
around a 'in ()' subquery, only the first row is returned.

This is not my real-life query, but a test that replicates the problem.

Thanks,

Valerio

$ sqlite3 --version
3.6.16

prepare some dummy data:

create table test ( id INT );
insert into test VALUES(1);
insert into test VALUES(2);
insert into test VALUES(3);
insert into test VALUES(4);
insert into test VALUES(5);
insert into test VALUES(6);
insert into test VALUES(7);
insert into test VALUES(8);
insert into test VALUES(9);
insert into test VALUES(10);

sqlite> select id from test where (id > 5);
6
7
8
9
10
[Good]

sqlite> select id from test where id in (select id from test where (id > 
5));
6
7
8
9
10
[Still Good]

Now let's duplicate parenthesis around the subquery:

sqlite> select id from test where id in ((select id from test where (id 
 > 5)));
6

Why only one value returned when parenthesis are duplicated?

Same with triple parenthesis enclosing:

sqlite> select id from test where id in (((select id from test where (id 
 > 5;
6


___
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] [ANN] SQLJet 1.0.0 released

[Virgilio Alexandre Fornazin]

Question 1 is a very good question, why pay for a partial copy if you can
use the full version for free?

Also, what is the sense of using SQLite database without SQL support? (this
remember Clipper/dBase GOTO LOCATE APPEND...)

I can't get the point... if you can't use a native SQLite in your platform,
why do not use another pure-java DB?

Just my 2 cents...


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kosenko Max
Sent: quinta-feira, 17 de setembro de 2009 11:14
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] [ANN] SQLJet 1.0.0 released



Alexander Kitaev-3 wrote:
> 
> We're glad to announce that SQLJet 1.0.0 has been released and available
> for download at http://sqljet.com/ web site.
> 

Hi.

Several questions:
1. Are there any real reasons for having dual-licensed commercial partial
reimplementation of SQLite in Java? Any examples?
2. When do you expecting to have SQL API?
3. Are you targeting for having 100% SQLite unit-tests passes?
4. Have you ever thought about collaborating with and contributing to
http://code.google.com/p/csharp-sqlite/ since it's easier to port C#->Java
than C->Java?
5. Any benchmarks comparing to native?

Thanks.

Max.

-
Best Regards.
Max Kosenko.
-- 
View this message in context:
http://www.nabble.com/-ANN--SQLJet-1.0.0-released-tp25458690p25491910.html
Sent from the SQLite mailing list archive at Nabble.com.

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

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

Re: [sqlite] Is it Possible in SQL...

[Virgilio Alexandre Fornazin]

'Not using SQL directly' means you create a series data access interface
like an ISeries interface that browse in a cursor-style through your series
data, then implement it as a concrete class like DatabaseSeries that does
the SQL job for you.

Talking about scaling issues means that you could do some kind of data
manipulation best on processing by itself instead of executing SQL
statements (example: an data analysis study that take a series and output 3
new series with heterogeneous values, based on first data series, you can
calculate the resulting 3 series fetching data one time when you iterate
through you ISeries interface; executing 3 SQL statements will perform 3
read cycles, you can perform fast and also stop wasting CPU resources).

[]'s

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
Sent: sexta-feira, 10 de julho de 2009 23:10
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Is it Possible in SQL...

#>I was trying to figuring out if you are doing something of 
#>graph data analysis, I do it almost everyday in our Stock 
#>Trader applications...
#>I never did this way (direct SQL), cause our graph series 
#>data sources are implement throught a common interface, that 
#>could be a SQL query, a stream, a XML, whatever.
#>
#>Just a tip: implementing specific and well designed 
#>interfaces for series data manipulation should be the right 
#>way for you, avoid scaling issues cause of possible SQL 
#>limitations in the ways those series can/should be 
#>manipulated in some cases.

This specific application requires very little user input. The user selects
a market (stock/futures) from a list of available data files and that is it.
The program then performs all kinds of different things on the dataset
selected and for the most part presents its results in the form of values.
There is one procedure, however, that will produce a 'graph' if the user
clicks on a button. That's pretty much it. 

When you said you never use 'direct SQL', are you saying that you never use
SQL that is hard coded in your program?

If so, perhaps in the case of my application requiring virtually no
interaction that it is acceptable for some of the internal procedures?

What are "scaling issues"?

Thanks. :-)
Rick







___
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] Is it Possible in SQL...

[Virgilio Alexandre Fornazin]

I was trying to figuring out if you are doing something of graph data
analysis, I do it almost everyday in our Stock Trader applications...

I never did this way (direct SQL), cause our graph series data sources are
implement throught a common interface, that could be a SQL query, a stream,
a XML, whatever.

Just a tip: implementing specific and well designed interfaces for series
data manipulation should be the right way for you, avoid scaling issues
cause of possible SQL limitations in the ways those series can/should be
manipulated in some cases.

Just my 2 cents...

[]'s

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
Sent: sexta-feira, 10 de julho de 2009 17:32
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Is it Possible in SQL...

Seems there was a question in your reply I didn't catch the first time.

>
#>What do you mean by "previous one"? Records in a table don't 
#>have any implicit ordering. Do you have some kind of a 
#>timestamp field that imposes the order?

The table, each time, has been in order from oldest Date to latest Date.
There is also the Primary ID field that starts from 1 to ...

The table is created by loading in a Metastock data file. The table name is
that of the market loaded. Records are never deleted or inserted within this
table, as is simply houses the complete price data loaded for that specific
market. So the order stays as when first created. It is never modified. From
oldest date to last price data date. For simplicity, call this MarketTable.

What I've been working on are the recordsets created from MarketTable, or a
temp table (TmpTable) that was created from a recordset derived from the
MarketTable.

#>update taxTable set DIRECTION =
#>(select case when taxTable.TAX < prevTax then 'down' when 
#>taxTable.TAX > prevTax then 'up' else null end  from (select 
#>t2.TAX as prevTax from taxTable t2 where t2.Date < 
#>taxTable.Date order by Date desc limit 1));
#>
#>Igor Tandetnik 


After examining the above, it appears that what this does is modify the
table itself. So I suppose then that it is not possible to create a
recordset instead that meets what I'm trying to do. If this is the case,
I'll have to make a copy of this table first as I don't want to modify the
original. 



Here's what is going on:

Most of my functions and procedures works off a table called TmpTable. This
was created from a recordset derived from the main MarketTable. TmpTable was
created because it needed to be somewhat modified from the original price
data table (MarketTable).

The current procedure that prompted my original question needs only to
determine the DIRECTION of values from one record to the next. Once this
procedure has completed this task and plotted (graphically), it is no longer
needed.

So modifying TmpTable, which will still be needed for other procedures, is
not preferred. It would be great if a recordset could be derived from it
instead that contains the DIRECTION results. Once the procedure exits, the
recordset would just go away.

My original plan was to create the recordset from TmpTable, with the added
DIRECTION column.

Ex: 0 as Direction FROM TmpTable

Then, either loop through the recordset doing the comparisons and filling in
DIRECTION, or make an array copy of the recordset and then fill in the info.

Of course, it is preferred that it be already accomplished by way of the
query.

I hope this makes sense. I won't be surprised if it does not. :-b

Thanks.

Rick




___
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] GROUPY BY alias backward incompatibility

[Virgilio Alexandre Fornazin]

Curious... even it does not make sense, it pass also on oracle 11g (sql
server actively refused to run)

SQL Server 2008:

SELECT (SELECT COUNT(*) FROM T t_inner GROUP BY t_outer.c) FROM T t_outer
-- Msg 164, Level 15, State 1, Line 1
-- Each GROUP BY expression must contain at least one column that is not an
outer reference.

SELECT (SELECT COUNT(*) FROM T t_inner GROUP BY t_inner.c) FROM T t_outer
---
(0 row(s) affected)


Oracle 11g:

SQL> create table t (c int);
Table created

SQL> select (select count(*) from t t_inner group by t_inner.c) from t
t_outer;
No rows selected

SQL> select (select count(*) from t t_inner group by t_outer.c) from t
t_outer;
No rows selected


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Junker
Sent: quinta-feira, 28 de maio de 2009 11:30
To: General Discussion of SQLite Database
Subject: Re: [sqlite] GROUPY BY alias backward incompatibility

At 15:37 28.05.2009, D. Richard Hipp wrote:

>Have you tried these two queries on other SQL database engines besides  
>SQLite?  What do PostgreSQL and MySQL make of them?

MySQL (5.0.21) reports no erros on either of both queries:

  select
(select count(*) from t t_inner
 group by t_outer.c) -- t_outer !!!
  from t t_outer;

  select
(select count(*) from t t_inner
 group by t_inner.c) -- t_inner !!!
  from t t_outer;

I do not have access to PostgreSQL right now.

The SQLite help [1] says: "The expressions in the GROUP BY clause do not
have to be expressions that appear in the result." Reading this, I'd expect
that both queries should run - even if the 1st one does not make much sense.
Opinions?

Ralf

[1] http://www.sqlite.org/lang_select.html  

___
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] SQLite version 3.6.14 and async vfs

[Virgilio Alexandre Fornazin]

This break purpose of VFS, all VFS should work in same way, you must not
know if your VFS is asynchronous
or not. VFS close method should wait for all file I/O on this database
handle (not all databases) to 
finalize before returning, providing compatibility with all other existing
VFS implementations.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ken
Sent: quinta-feira, 7 de maio de 2009 13:47
To: General Discussion of SQLite Database; Pavel Ivanov
Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs


I see the confusion with the word "Shutdown".

How about but a call that would block until the async thread completes all
operations that are enqueued. Effectively a Close of the async thread/queue
and db. The call could be sqlite3Async_close.

Hope that clarifies my intent. 


--- On Thu, 5/7/09, Pavel Ivanov <paiva...@gmail.com> wrote:

> From: Pavel Ivanov <paiva...@gmail.com>
> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs
> To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite Database"
<sqlite-users@sqlite.org>
> Date: Thursday, May 7, 2009, 11:10 AM
> Shutdown is not an option at all. I
> need vfs to continue working on
> other databases but to be notified (or have possibility to
> check) when
> one particular database is no longer opened.
> 
> Pavel
> 
> On Thu, May 7, 2009 at 12:00 PM, Ken <kennethinbox-sql...@yahoo.com>
> wrote:
> >
> > --- On Thu, 5/7/09, Virgilio Alexandre Fornazin
<virgilioforna...@gmail.com>
> wrote:
> >
> >> From: Virgilio Alexandre Fornazin <virgilioforna...@gmail.com>
> >> Subject: Re: [sqlite] SQLite version 3.6.14 and
> async vfs
> >> To: "'General Discussion of SQLite Database'"
> <sqlite-users@sqlite.org>
> >> Date: Thursday, May 7, 2009, 10:50 AM
> >> Close should wait for all file
> >> operations complete to meet that needs.
> >> I think asynchronous VFS should take care of
> waiting in
> >> sqlite3_close()
> >> call.
> >>
> >> -Original Message-
> >> From: sqlite-users-boun...@sqlite.org
> >> [mailto:sqlite-users-boun...@sqlite.org]
> >> On Behalf Of Pavel Ivanov
> >> Sent: quinta-feira, 7 de maio de 2009 12:33
> >> To: General Discussion of SQLite Database
> >> Subject: Re: [sqlite] SQLite version 3.6.14 and
> async vfs
> >>
> >> Hi!
> >>
> >> It's great to hear about performance improvements
> and
> >> especially about
> >> asynchronous I/O extension. Thank you very much
> for your
> >> work!
> >>
> >> I have one question though: taking quick look at
> the
> >> sources of async
> >> vfs I've noticed that even closing the file is
> just a task
> >> in the
> >> async queue and thus after closing sqlite
> connection file
> >> remains
> >> opened for some time. It sounds pretty reasonable,
> but here
> >> stands the
> >> question: what if I want to do something with the
> database
> >> file after
> >> I close sqlite connection to it (e.g. move to the
> archive
> >> directory,
> >> zip it etc.)? With sync vfs I could be sure that
> after
> >> closing
> >> connection file is closed and I can do with it
> whatever I
> >> want. Is
> >> there a way to catch the moment of actual file
> closing with
> >> async vfs?
> >>
> >> And another question just to be sure that I
> understand it
> >> correctly:
> >> async vfs holds only one queue for all opened
> database
> >> files, right?
> >>
> >> Pavel
> >>
> >> On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp
> <d...@hwaci.com>
> >> wrote:
> >> > SQLite version 3.6.14 is now available on the
> SQLite
> >> website
> >> >
> >> >     http://www.sqlite.org/
> >> >
> >> > Version 3.6.14 contains performance enhances
> in the
> >> btree and pager
> >> > subsystems.  In addition, the query
> optimizer now
> >> knows how to take
> >> > advantage of OR and IN operators on columns
> of a
> >> virtual table.
> >> >
> >> > A new optional extension is included that
> implements
> >> an asynchronous I/
> >> > O backend for SQLite on either windows or
> unix.  The
> >> asynchronous I/O
> >> > backend processes all writes using a
> background
> >> thread.  This

Re: [sqlite] SQLite version 3.6.14 and async vfs

[Virgilio Alexandre Fornazin]

Close should wait for all file operations complete to meet that needs.
I think asynchronous VFS should take care of waiting in sqlite3_close()
call.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: quinta-feira, 7 de maio de 2009 12:33
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs

Hi!

It's great to hear about performance improvements and especially about
asynchronous I/O extension. Thank you very much for your work!

I have one question though: taking quick look at the sources of async
vfs I've noticed that even closing the file is just a task in the
async queue and thus after closing sqlite connection file remains
opened for some time. It sounds pretty reasonable, but here stands the
question: what if I want to do something with the database file after
I close sqlite connection to it (e.g. move to the archive directory,
zip it etc.)? With sync vfs I could be sure that after closing
connection file is closed and I can do with it whatever I want. Is
there a way to catch the moment of actual file closing with async vfs?

And another question just to be sure that I understand it correctly:
async vfs holds only one queue for all opened database files, right?

Pavel

On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp  wrote:
> SQLite version 3.6.14 is now available on the SQLite website
>
>     http://www.sqlite.org/
>
> Version 3.6.14 contains performance enhances in the btree and pager
> subsystems.  In addition, the query optimizer now knows how to take
> advantage of OR and IN operators on columns of a virtual table.
>
> A new optional extension is included that implements an asynchronous I/
> O backend for SQLite on either windows or unix.  The asynchronous I/O
> backend processes all writes using a background thread.  This gives
> the appearance of faster response time at the cost of durability and
> additional memory usage.  See http://www.sqlite.org/asyncvfs.html for
> additional information.
>
> This release also includes many small bug fixes and documentation
> improvements.
>
> As always, please let me know if you encounter any difficulties.
>
> D. Richard Hipp
> d...@hwaci.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

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

Re: [sqlite] Large SQLite3 Database Memory Usage

[Virgilio Alexandre Fornazin]

Try to run those queries on sqlite3 program.
Then compare memory working sets between your IIS Process and sqlite3 shell.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: terça-feira, 5 de maio de 2009 16:25
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 I am using SQLite ODBC Driver. How can I find if its leak in my provider???

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Virgilio Alexandre
Fornazin
Sent: Tuesday, May 05, 2009 12:23 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

Are you using ADO or plain sqlite dll ?
May this be a leak in your provider ?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: terça-feira, 5 de maio de 2009 16:13
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage


  I am running the query with CursorLocation = 2,  CursorType = 2, LockType
= 3 to handle 610MB database with 2259207 records in table.
 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: Tuesday, May 05, 2009 11:45 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 How much RAM is needed to handle 610MB database with 2259207 records in
table.Initially I was running Windows web server
 2008 with 512 MB RAM,now I upgraded to 1GB RAM but still the same ..If I
run my application once the query returns results in 80 sec and  if run it
again , memory usage starts growing and reaches nearly 100% ..then I get
more hard page faults , the application responds slow, or system just hangs.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: Tuesday, May 05, 2009 11:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 Thanks for your response. The query runs faster than mine but still facing
problem after running the page multiple times.So I think I should try with
1GB of RAM.

Thanks,

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BareFeet
Sent: Tuesday, May 05, 2009 9:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

Hi Kalyani,

> I have 2259207 records in table using SQLite3 database. I am running 
> the select query to retrive records from DB
>
> SELECT ID, EventClassName, EventClassRef, TransitionTime, Message, 
> MonitoredRef, EventRef,ToState,Priority,Acked from Events  WHERE 
> Events.NotificationTime >= {ts '2009-05-04 14:44:10'}  Order By ID 
> DESC LIMIT 100
>
> If I run my application once the query returns results in 80 sec and 
> if run it again , memory usage starts growing and reaches nearly 100% 
> ..then I get more hard page faults , the application responds slow, or

> system just hangs. I am running my application on Windows web server
> 2008 with 512 MB RAM. How can I optimize the query ? I have indexes on

> ID and notificationtime (datatype timestamp)

Using this is very inefficient: order by ID desc limit 100 since SQLite
still has to build and sort the entire result table before limiting it.
You are better off to filter using the search (ie where).

For instance, if ID is the integer primary key and you don't expect deletes,
you could use:

SELECT ID, EventClassName, EventClassRef, TransitionTime, Message,
MonitoredRef, EventRef, ToState, Priority, Acked from Events where Events.ID
>= (select max(ID) from Events) - 100
and Events.NotificationTime >= {ts '2009-05-04 14:44:10'} order by
ID desc

HTH,
Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

___
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
___
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] Large SQLite3 Database Memory Usage

[Virgilio Alexandre Fornazin]

Are you using ADO or plain sqlite dll ?
May this be a leak in your provider ?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: terça-feira, 5 de maio de 2009 16:13
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage


  I am running the query with CursorLocation = 2,  CursorType = 2,
LockType = 3 to handle 610MB database with 2259207 records in table.
 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: Tuesday, May 05, 2009 11:45 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 How much RAM is needed to handle 610MB database with 2259207 records in
table.Initially I was running Windows web server
 2008 with 512 MB RAM,now I upgraded to 1GB RAM but still the same ..If
I run my application once the query returns results in 80 sec and  if
run it again , memory usage starts growing and reaches nearly 100%
..then I get more hard page faults , the application responds slow, or
system just hangs.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke
Sent: Tuesday, May 05, 2009 11:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

 Thanks for your response. The query runs faster than mine but still
facing problem after running the page multiple times.So I think I should
try with 1GB of RAM.

Thanks,

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BareFeet
Sent: Tuesday, May 05, 2009 9:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large SQLite3 Database Memory Usage

Hi Kalyani,

> I have 2259207 records in table using SQLite3 database. I am running 
> the select query to retrive records from DB
>
> SELECT ID, EventClassName, EventClassRef, TransitionTime, Message, 
> MonitoredRef, EventRef,ToState,Priority,Acked from Events  WHERE 
> Events.NotificationTime >= {ts '2009-05-04 14:44:10'}  Order By ID 
> DESC LIMIT 100
>
> If I run my application once the query returns results in 80 sec and 
> if run it again , memory usage starts growing and reaches nearly 100% 
> ..then I get more hard page faults , the application responds slow, or

> system just hangs. I am running my application on Windows web server
> 2008 with 512 MB RAM. How can I optimize the query ? I have indexes on

> ID and notificationtime (datatype timestamp)

Using this is very inefficient: order by ID desc limit 100 since SQLite
still has to build and sort the entire result table before limiting it.
You are better off to filter using the search (ie where).

For instance, if ID is the integer primary key and you don't expect
deletes, you could use:

SELECT ID, EventClassName, EventClassRef, TransitionTime, Message,
MonitoredRef, EventRef, ToState, Priority, Acked from Events where
Events.ID >= (select max(ID) from Events) - 100
and Events.NotificationTime >= {ts '2009-05-04 14:44:10'} order
by ID desc

HTH,
Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

___
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
___
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] A memvfs for loading/saving database from buffer

[Virgilio Alexandre Fornazin]

Where we can get the code ?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of stephen liu
Sent: terça-feira, 28 de abril de 2009 22:24
To: sqlite-users@sqlite.org
Subject: [sqlite] A memvfs for loading/saving database from buffer

Hi,

The attachment is a memvfs implementation for sqlite.

With the memvfs, we can loading/saving sqlite database from buffer.

There also includes a demo to show how to use it.

Cheers,

Stephen Liu

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

Re: [sqlite] Using SQLite3 on On-Time RTOS ...

[Virgilio Alexandre Fornazin]

Maybe RTOS kernel does not implement Wide-Char functions... 

Windows VFS must guard them with a SQLITE_WINDOWS_NO_UNICODE 
macro or something like that at compile time, but you should
do it yourself... then you can contribute it back to SQLite.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kent Dahl
Sent: segunda-feira, 27 de abril de 2009 09:58
To: General Discussion of SQLite Database
Subject: [sqlite] Using SQLite3 on On-Time RTOS ...

Hi.

I wondered whether anyone is using SQLite3 on the On-Time RTOS platform,
and if so, if there are any patches or tips to help others along?

We've been giving the SQLite 3.6.10 amalgamation source code for Windows
a try against RTOS 5.14 and it compiled out of the box. However, we ran
into some linker and run-time errors. After a fair amount of
experimenting, we got it up and running, but only towards an in-memory
database. We still have problems opening existing or creating database
files.

I'll outline what we ran into and what we've tried so far.

The linker errors were primarily towards wide char APIs that didn't
exist on RTOS. Most of these could easily be ifdef'd away. There was
alternate Win95/98/ME code we could trigger if we changed "isNT" to 0,
which helped a bit. Some, like AreFileApisANSI we just defined to 1.

We then ran into a crasher caused by convertUtf8Filename returning NULL,
so we changed that to use a fallback. Just strdup-ing the input string
if the conversion failed. Debugging this took a little while, because
using the amalgamation source meant that the RTOS compiler crossed a 64k
symbols limit, meaning breakpoints and backtraces broke badly.

Finally we had something that linked and ran, but only towards in-memory
database. When we tried to open an existing database (or create a new
one) using the sqlite3_open_v2 API, it kept returning SQLITE_NOMEM(7).
When I tried debugging this, I got as far as the sqlite3BtreeFactory
call, but because of the breakpointing problems I didn't get much
further.

So, my questions then are:
* Is anyone using or have used SQLite3 on RTOS?
* Are the older Win95/98/ME code paths still actively used, tested and
found to be working? (Or should I expect a few inches of legacy dust and
bugs in them?)
* Is there a good way to get more debug information? (SQLITE_DEBUG seems
more targetted at debugging SQL statements.)
* Am I barking up any of the wrong trees?

Hope someone out there has some ideas or pointers to help me get
motivated enough to give another stab at it. :)


= Additional information =

=== Linker errors ===

Example linker error:
"Error: DLL dependency in CDP.EXE: KERNEL32.dll.DeleteFileW"

These APIs also gave linker errors:
- LockFileEx 
- GetTempPathW 
- GetFullPathNameW 
- GetDiskFreeSpaceW 
- AreFileApisANSI 

=== References ===

* On-Time RTOS - http://www.on-time.com/


-- 
Mvh/Regards,

Kent Dahl
Software Developer

Industrial Control Design AS



Phone: +47 93 07 32 30

Breivika Industriveg 63
N-6018 Ålesund
Norway

k...@icd.no

www.icd.no


The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon this information by persons or
entities other than the intended recipient is prohibited. If you
received this in error, please contact the System Manager i...@icd.no and
delete the material from any computer.
___
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] Sqlite replication

[Virgilio Alexandre Fornazin]

SQLite is file-based (no server behind DB), you must provide your own 
synchronization (copying file, executing SQL on both databases, etc).

I don't know if there´s an application/library that does it for you
automatically.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Wade Williams
Sent: quinta-feira, 11 de dezembro de 2008 14:32
To: sqlite-users@sqlite.org
Subject: [sqlite] Sqlite replication

I'm looking for an honest assessment from someone that may have made  
this decision in the past.

I'm considering using an embedded database for an upcoming  
application.  Operation rate is high 20,000-60,000 per day. (Those  
will mostly be selects, but some smaller percentage will be inserts).

Our choices appear to be SQLite or Berkley DB.  An RDBMS isn't really  
an option due to the administrative cost.

My first inclination was to use SQLite.  From what I've seen of the  
performance numbers, it should be able to support that rate without  
much trouble.

However, a key feature is disaster recovery.  If the primary machine  
goes down we've got to quickly switch to another machine (quickly  
meaning within minutes if not seconds).

In my research it appears SQLite may not be a good option, since the  
only replication appears to be "lock the database and copy the file to  
the new machine."  Berkeley DB seems to have the advantage of having  
replication built-in.  However, I have no idea how useful the  
replication is and of course the API is much more inscrutable.  I've  
also certainly heard all the Berkley DB corruption horror stories.

I'm OK with stepping off the deep end into Berkeley DB, but I'd prefer  
SQLite.  However, I'm certainly not looking to shoot myself in the foot.

I'd appreciate input from anyone on this subject, especially tales  
from replication projects.

Thanks,

Wade
___
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] Vista frustrations

[Virgilio Alexandre Fornazin]

Sure. I just tell to do this test to check if the bug is related to this
component, since it debuted on Vista.
 
 
Virgilio Alexandre Fornazin
High performance and realtime systems development

Rua Brigadeiro Vicente Faria Lima, 268
Bela VistaLeme-SPCEP 13611-485
Phone: +55 19 3571-5573
Cell: +55 19 8111-4053
+55 11 8357 1491
Mail: [EMAIL PROTECTED]
Web: http://www.fornazinconsultoria.com.br



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jeffrey Becker
Sent: quarta-feira, 17 de setembro de 2008 13:41
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

SQLite in general and the .Net provider in particular are most often
shipped as components of other applications.  I dont think having
developers tell their end users to disable superfetch is a viable
solution.  As much as I hate to propose this maybe a runtime check is
in order to see what the OS version is and not use the flag where it's
known to be problematic.

On Wed, Sep 17, 2008 at 12:14 PM, Virgilio Alexandre Fornazin
<[EMAIL PROTECTED]> wrote:
> Could not this bug be related with Vista feature called 'Superfetch' ?
> It tries to keep in memory the most accessed files for user, avoiding
> disk for read access.
>
> If you disable (or stop) this service, the problem remains or not ?
>
>
>
>
> Virgilio Alexandre Fornazin
> High performance and realtime systems development
>
> Rua Brigadeiro Vicente Faria Lima, 268
> Bela VistaLeme-SPCEP 13611-485
> Phone: +55 19 3571-5573
> Cell: +55 19 8111-4053
> +55 11 8357 1491
> Mail: [EMAIL PROTECTED]
> Web: http://www.fornazinconsultoria.com.br
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Jay A. Kreibich
> Sent: quarta-feira, 17 de setembro de 2008 13:01
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Vista frustrations
>
> On Wed, Sep 17, 2008 at 01:17:51AM -0700, Roger Binns scratched on the
wall:
>> Robert Simpson wrote:
>> > To me this seems like an obvious bug in Vista,
>>
>> Actually I'd argue that it is behaving as designed.
>
>  You could argue it is behaving as designed, but I'd still argue it is
>  behaving poorly.
>
>  Further, if a system component who's sole purpose is to increase
>  performance-- as all cache systems are-- has the overall effect of
>  decreasing performance, not only of the process it is trying to speed
>  up, but of the whole system, it is pretty easy to argue that's a serious
>  functional bug.
>
>
>  Given the speed of most storage systems, filesystem cache management
>  is an important component of overall system performance.  However, if
>  the cache system is grabbing so much physical memory (and, apparently,
>  refusing to let go of it) that processes are forced to aggressively
>  page and the net result is a massive performance loss, then something
>  isn't right.
>
>  As with so many things, cache management (and, indeed, the whole
>  concept of caches) tends to be a huge web of compromises.  It is
>  extremely difficult, if not impossible, to cover all cases.  But
>  these things are not exactly new, and it should be easy enough to
>  never get in a situation where things are actually made worse--
>  especially that they're not made worse for the whole system.
>
>> Generally
>> filesystem code will try to detect what is going on under the hood.  In
>> particular if it looks like you are doing sequential access(*) then they
>> will start doing read ahead, whereas read ahead is a waste for random
>> access.
>
>  Not to get into a whole argument about cache strategies, but this
>  often not true.  If we assume free memory isn't a big concern,
>  when a process opens a file for random-access we can either
>  read-ahead the whole thing or we can read blocks here and there until
>  (if the process touches the majority of the file) we have the whole
>  thing in memory.  Both systems, in the end, will result in the same
>  memory usage.
>
>  However, if I'm going to be doing random access on a file of moderate
>  or smaller size, it is much cheaper for the OS to just suck the whole
>  thing into memory via one bulk read operation than it is to grab it
>  piecemeal.
>
>  The whole trick is defining "moderate" both in terms of first-return
>  read times (time to return the block the process actually asked for,
>  which might not be the first block pulled off disk) vs how likely the
>  process is to touch the majority of file blocks (something that is
>  somewhat less likely as the file gets bigger).
>
>  As the file gets larger, there is also the real-world issue of how
>  much RAM the system has, and how much of it is ac

Re: [sqlite] Vista frustrations

[Virgilio Alexandre Fornazin]

Note that Windows Server 2008 use the same 'core' as Windows Vista.
If you´re detecting and redirecting by using GetVersion() or other
approach you might test for Server 2008 too.
 
 
Virgilio Alexandre Fornazin
High performance and realtime systems development

Rua Brigadeiro Vicente Faria Lima, 268
Bela VistaLeme-SPCEP 13611-485
Phone: +55 19 3571-5573
Cell: +55 19 8111-4053
+55 11 8357 1491
Mail: [EMAIL PROTECTED]
Web: http://www.fornazinconsultoria.com.br



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Robert Simpson
Sent: quarta-feira, 17 de setembro de 2008 13:30
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Vista frustrations

I've run the tests with superfetch and prefetch disabled and enabled.
Results are consistent with or without these running.  The only thing that
has any affect is the FILE_FLAG_RANDOM_ACCESS flag.  And only on Vista.

For now I'm thinking of overriding the default Windows VFS and redirecting
the open function just for Vista so it doesn't use that flag.

Robert



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Virgilio Alexandre
Fornazin
Sent: Wednesday, September 17, 2008 9:14 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Vista frustrations

Could not this bug be related with Vista feature called 'Superfetch' ?
It tries to keep in memory the most accessed files for user, avoiding
disk for read access.

If you disable (or stop) this service, the problem remains or not ?


 
 
Virgilio Alexandre Fornazin
High performance and realtime systems development

Rua Brigadeiro Vicente Faria Lima, 268
Bela VistaLeme-SPCEP 13611-485
Phone: +55 19 3571-5573
Cell: +55 19 8111-4053
+55 11 8357 1491
Mail: [EMAIL PROTECTED]
Web: http://www.fornazinconsultoria.com.br


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jay A. Kreibich
Sent: quarta-feira, 17 de setembro de 2008 13:01
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

On Wed, Sep 17, 2008 at 01:17:51AM -0700, Roger Binns scratched on the wall:
> Robert Simpson wrote:
> > To me this seems like an obvious bug in Vista,
> 
> Actually I'd argue that it is behaving as designed.

  You could argue it is behaving as designed, but I'd still argue it is
  behaving poorly.

  Further, if a system component who's sole purpose is to increase
  performance-- as all cache systems are-- has the overall effect of
  decreasing performance, not only of the process it is trying to speed
  up, but of the whole system, it is pretty easy to argue that's a serious
  functional bug.


  Given the speed of most storage systems, filesystem cache management
  is an important component of overall system performance.  However, if
  the cache system is grabbing so much physical memory (and, apparently,
  refusing to let go of it) that processes are forced to aggressively
  page and the net result is a massive performance loss, then something
  isn't right.

  As with so many things, cache management (and, indeed, the whole
  concept of caches) tends to be a huge web of compromises.  It is
  extremely difficult, if not impossible, to cover all cases.  But
  these things are not exactly new, and it should be easy enough to
  never get in a situation where things are actually made worse--
  especially that they're not made worse for the whole system.

> Generally
> filesystem code will try to detect what is going on under the hood.  In
> particular if it looks like you are doing sequential access(*) then they
> will start doing read ahead, whereas read ahead is a waste for random
> access. 

  Not to get into a whole argument about cache strategies, but this
  often not true.  If we assume free memory isn't a big concern,
  when a process opens a file for random-access we can either
  read-ahead the whole thing or we can read blocks here and there until
  (if the process touches the majority of the file) we have the whole
  thing in memory.  Both systems, in the end, will result in the same
  memory usage.

  However, if I'm going to be doing random access on a file of moderate
  or smaller size, it is much cheaper for the OS to just suck the whole
  thing into memory via one bulk read operation than it is to grab it
  piecemeal.

  The whole trick is defining "moderate" both in terms of first-return
  read times (time to return the block the process actually asked for,
  which might not be the first block pulled off disk) vs how likely the
  process is to touch the majority of file blocks (something that is
  somewhat less likely as the file gets bigger).  

  As the file gets larger, there is also the real-world issue of how
  much RAM the system has, and how much of it is actually in-use with
  process and OS pages.  This is true of both sequential AND random
  access, although memory usage is gen

Re: [sqlite] Vista frustrations

[Virgilio Alexandre Fornazin]

Could not this bug be related with Vista feature called 'Superfetch' ?
It tries to keep in memory the most accessed files for user, avoiding
disk for read access.

If you disable (or stop) this service, the problem remains or not ?


 
 
Virgilio Alexandre Fornazin
High performance and realtime systems development

Rua Brigadeiro Vicente Faria Lima, 268
Bela VistaLeme-SPCEP 13611-485
Phone: +55 19 3571-5573
Cell: +55 19 8111-4053
+55 11 8357 1491
Mail: [EMAIL PROTECTED]
Web: http://www.fornazinconsultoria.com.br


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jay A. Kreibich
Sent: quarta-feira, 17 de setembro de 2008 13:01
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

On Wed, Sep 17, 2008 at 01:17:51AM -0700, Roger Binns scratched on the wall:
> Robert Simpson wrote:
> > To me this seems like an obvious bug in Vista,
> 
> Actually I'd argue that it is behaving as designed.

  You could argue it is behaving as designed, but I'd still argue it is
  behaving poorly.

  Further, if a system component who's sole purpose is to increase
  performance-- as all cache systems are-- has the overall effect of
  decreasing performance, not only of the process it is trying to speed
  up, but of the whole system, it is pretty easy to argue that's a serious
  functional bug.


  Given the speed of most storage systems, filesystem cache management
  is an important component of overall system performance.  However, if
  the cache system is grabbing so much physical memory (and, apparently,
  refusing to let go of it) that processes are forced to aggressively
  page and the net result is a massive performance loss, then something
  isn't right.

  As with so many things, cache management (and, indeed, the whole
  concept of caches) tends to be a huge web of compromises.  It is
  extremely difficult, if not impossible, to cover all cases.  But
  these things are not exactly new, and it should be easy enough to
  never get in a situation where things are actually made worse--
  especially that they're not made worse for the whole system.

> Generally
> filesystem code will try to detect what is going on under the hood.  In
> particular if it looks like you are doing sequential access(*) then they
> will start doing read ahead, whereas read ahead is a waste for random
> access. 

  Not to get into a whole argument about cache strategies, but this
  often not true.  If we assume free memory isn't a big concern,
  when a process opens a file for random-access we can either
  read-ahead the whole thing or we can read blocks here and there until
  (if the process touches the majority of the file) we have the whole
  thing in memory.  Both systems, in the end, will result in the same
  memory usage.

  However, if I'm going to be doing random access on a file of moderate
  or smaller size, it is much cheaper for the OS to just suck the whole
  thing into memory via one bulk read operation than it is to grab it
  piecemeal.

  The whole trick is defining "moderate" both in terms of first-return
  read times (time to return the block the process actually asked for,
  which might not be the first block pulled off disk) vs how likely the
  process is to touch the majority of file blocks (something that is
  somewhat less likely as the file gets bigger).  

  As the file gets larger, there is also the real-world issue of how
  much RAM the system has, and how much of it is actually in-use with
  process and OS pages.  This is true of both sequential AND random
  access, although memory usage is generally easier to control in
  sequential patterns.

  This is where Vista appears to be breaking down and making very poor
  decisions.  It seems to be giving cache pages more priority than
  process and OS system pages, and generally that should never happen.
  If we're correctly understanding what is going on, Vista might very
  well be paging out SQLite's internal page cache to fit a few extra file
  blocks in RAM.  How much sense does that make?

> By using the sequential or random flags you are explicitly
> telling the filesystem to ignore its heuristics and do as you say only.

  Even if that's true (most APIs present the flags as "hints" not
  absolute truths), the worst an incorrect flag should do is hurt the
  file access performance of the process that provided the hint.  Even
  then, the lower end should be the same performance one would expect
  if there was no cache (e.g. constant misses).
 
  A poor or incorrect flag is no excuse to be overly aggressive with
  holding pages in RAM and killing the whole system.  Even if a flag
  alters the read-ahead policy or cache replacement strategy, a flag
  should never override the decisions the cache system has to face when
  the system starts to run thin on free physical RAM-- especially on a
  file that is larger than than the RAM footprint 

Re: [sqlite] SQLite and Threadsafety (again)

[Virgilio Alexandre Fornazin]

I'm a bit curious why it wouldn't work. I use the same approach right here to 
have a exclusive access to a database table
in the same model I told you. Also, I never used other locking mode that 
exclusive, because if want to write to the database
the write lock should be granted exclusively by a single thread (similar to 
pthreads multiple reader single writer lock scheme).

Also, DRH told in other answer in this thread the problem of using only one 
connection shared with many threads: you cannot 
guarantee that last_insert_rowid() is correct, because someone other can 
started a transaction and write a record to any table,
modifying the last_insert_rowid() result, which lead you to produce bugs if you 
depend on this feature.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher
Sent: quarta-feira, 21 de maio de 2008 14:15
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite and Threadsafety (again)

The problem with the approach you suggest as that that does not work
when PRAGMA locking_mode = EXCLUSIVE, which we use in many places to
improve performance.  Additionally, it's my understanding that when
using the shared cache, that transactions are grouped across
connection objects (it's possible I misread a past e-mail though).  We
use the shared cache for every database connection (although, I've
been wondering as of late if it's really worthwhile).

It is unfortunate that we'll lose the ability to do multiple reads at
the same time, however.  I'm open to suggestions on a better way to
fix this problem.

Cheers,

Shawn

On Wed, May 21, 2008 at 1:05 PM, Virgilio Alexandre Fornazin
<[EMAIL PROTECTED]> wrote:
> Not to putting flame in question, but why not use any connection per thread
> ? At this way you can guarantee:
>
> - Correct transaction processing;
> - Avoid waiting on R/W locks, allowing more than one read to run
> concurrently;
>
> We also use this model with ODBC / ADO database layers.
>
> You don't need to take care if your database drivers provides thread safety,
> handle multiple active result sets
> (client-side cursors), last insert row id concurrency, etc.
>
> We tried to use a single connection per process, but after changed to one
> connection per thread model, the
> gains we got avoiding synchronization was bigger than we imaginated.
>
> To get this changes working best, we created a database connection pool,
> that we use to get the connections by their
> ID´s (yes, it´s a key-value dictionary). The pool also 'recycle' connections
> that will not be used by any thread to
> avoid resource leaking.
>
> Is this case, assuming that the unique ID of the database is the file name
> (SQLite database file name), you can get
> this behaviour to work transparently for your consumers (I assume you´re not
> using directly the sqlite3_* calls inside
> your program, you have some kind of high-level abstraction to use them).
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher
> Sent: quarta-feira, 21 de maio de 2008 13:08
> To: General Discussion of SQLite Database
> Subject: [sqlite] SQLite and Threadsafety (again)
>
> Hey all,
>
> I've come to the sad realization that we need to make our sqlite
> wrapper threadsafe so it can be used on multiple threads without
> consumers having to worry about threadsafety themselves.  So, I wanted
> to make sure all my assumptions about sqlite data structures are
> correct so I don't introduce issues before undertaking this task.
>
> First, I know that the sqlite3 object can be accessed on multiple
> threads, but it must only be used by one thread of control at a time.
> It is also my understanding that this same constraint applies to
> sqlite3_stmt objects - they can only be used by one thread of control
> at a time but accessed on multiple ones.  What I am not so sure about,
> however, is if I have to protect the sqlite3 object that "owns" the
> statement when I'm calling methods on it such as sqlite3_bind_*
> interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize.
> Conservatively, I'm assuming yes on all of the above, but I hope I'm
> wrong for at least some of those.  I would, however, expect to have to
> protect the sqlite3 object when calling sqlite3_prepare_v2.
>
> Clarification on this would be greatly appreciated.
>
> Cheers,
>
> Shawn Wilsher
> Mozilla Developer
> ___
> 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/sql

Re: [sqlite] SQLite and Threadsafety (again)

[Virgilio Alexandre Fornazin]

Not to putting flame in question, but why not use any connection per thread
? At this way you can guarantee:

- Correct transaction processing;
- Avoid waiting on R/W locks, allowing more than one read to run
concurrently;

We also use this model with ODBC / ADO database layers. 

You don't need to take care if your database drivers provides thread safety,
handle multiple active result sets 
(client-side cursors), last insert row id concurrency, etc.

We tried to use a single connection per process, but after changed to one
connection per thread model, the
gains we got avoiding synchronization was bigger than we imaginated.

To get this changes working best, we created a database connection pool,
that we use to get the connections by their
ID´s (yes, it´s a key-value dictionary). The pool also 'recycle' connections
that will not be used by any thread to
avoid resource leaking.

Is this case, assuming that the unique ID of the database is the file name
(SQLite database file name), you can get
this behaviour to work transparently for your consumers (I assume you´re not
using directly the sqlite3_* calls inside
your program, you have some kind of high-level abstraction to use them).


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher
Sent: quarta-feira, 21 de maio de 2008 13:08
To: General Discussion of SQLite Database
Subject: [sqlite] SQLite and Threadsafety (again)

Hey all,

I've come to the sad realization that we need to make our sqlite
wrapper threadsafe so it can be used on multiple threads without
consumers having to worry about threadsafety themselves.  So, I wanted
to make sure all my assumptions about sqlite data structures are
correct so I don't introduce issues before undertaking this task.

First, I know that the sqlite3 object can be accessed on multiple
threads, but it must only be used by one thread of control at a time.
It is also my understanding that this same constraint applies to
sqlite3_stmt objects - they can only be used by one thread of control
at a time but accessed on multiple ones.  What I am not so sure about,
however, is if I have to protect the sqlite3 object that "owns" the
statement when I'm calling methods on it such as sqlite3_bind_*
interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize.
Conservatively, I'm assuming yes on all of the above, but I hope I'm
wrong for at least some of those.  I would, however, expect to have to
protect the sqlite3 object when calling sqlite3_prepare_v2.

Clarification on this would be greatly appreciated.

Cheers,

Shawn Wilsher
Mozilla Developer
___
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] Proposed SQLite C/C++ interface behavior change.

[Virgilio Alexandre Fornazin]

A good and new safe could be a sqlite3_close_v2() call prototyped like

int sqlite3_close_v2(sqlite3 * db, int closePendingStatements);

and current sqlite3_close() call could become

int sqlite3_close(sqlite3 * db)
{
return sqlite3_close_v2(db, 0);
}

In this way, current running code does not need to be changed, and
developers that use v2 interface with close = 1 are aware of what
they are doing.

This not only mantains backward compatibility but also give more
control of sqlite behaviour.
 
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: terça-feira, 13 de maio de 2008 20:51
To: General Discussion of SQLite Database
Subject: [sqlite] Proposed SQLite C/C++ interface behavior change.

The currently documented behavior of sqlite3_close() is that  when it  
called on a database connection that has unfinalized prepared  
statements is to return SQLITE_BUSY and fail to close the connection.   
The rational is that we did not want a call to sqlite3_close() to  
destroy sqlite3_stmt* pointers out from under other subsystems.  But  
for version 3.6.0 we are considering a behavior change in which a call  
to sqlite3_close() will silently and automatically call  
sqlite3_finalize() on all outstanding prepared statements.

This is, technically, an incompatible change and we strive to avoid  
incompatible changes. But we think it unlikely that this change will  
cause any problems, and in fact we suspect it will likely fix more  
bugs than it will induce.  But before we move forward, it seems good  
to submit the idea to the community of SQLite users and programmers.

Does anybody have any thoughts on this proposed behavior changes for  
the sqlite3_close() interface?

D. Richard Hipp
[EMAIL PROTECTED]

___
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] multiple writers for in-memory datastore

[Virgilio Alexandre Fornazin]

Imagine the following cenario (I assume you know c++ stdlib)

A map of strings (filenames) to in-memory file handlers (the objects that
will handle the shared memory or heap files).

These files handlers will exists until the process exists and do not receive
a delelefile() vfs call.

File handlers can synchronize RW-Locks using internal mutex/criticat
sections/semaphores/spin locks, etc.

When you create a new file in vfs, a new handler is created and assigned to
that filename and registered in this map.






-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of James Gregurich
Sent: sábado, 19 de abril de 2008 17:02
To: General Discussion of SQLite Database
Subject: Re: [sqlite] multiple writers for in-memory datastore


I don't immediately see how that would solve the problem.

The limitation of interest here (based on my perhaps limited  
understanding) is that locking has file-level granularity. I don't  
immediately see how a VST implementation would allow for changing the  
locking granularity of the overall system.

-James

On Apr 19, 2008, at 12:03 PM, Virgilio Fornazin wrote:

> what about creating a VFS for such task ? Can be accomplished in  
> many ways,
> using heap memory, shared memory... not so easy to do, but not much
> complicated too... locking can be provided by multiple-readers
> single-writers locks strategies, etc...
>
> On Sat, Apr 19, 2008 at 2:29 PM, James Gregurich <[EMAIL PROTECTED]>
> wrote:
>
>>
>> oh good! That isn't the version that ships with Leopard, but I can
>> live with deploying my own version as part of my app.
>>
>> Will l get the writer parallelism I'm after as long as each thread
>> writes exclusively into its own attached db?
>>
>>
>> in other wordstwo bulk insert operations going on simultaneously
>> on the same connection but each insert operation going into a
>> different attached in-memory db.
>>
>>
>> On Apr 19, 2008, at 9:20 AM, Dan wrote:
>>
>>>
>>> On Apr 19, 2008, at 6:06 AM, James Gregurich wrote:
>>>

 I'll ask this question. The answer is probably "no," but I'll ask  
 it
 for the sake of completeness.


 Suppose I created an in-memory db. I use the attach command to
 associate an additional in-memory db. Suppose I assign the main  
 db to
 thread 1 and the associated db to thread 2. Can I share the
 connection
 across the 2 threads if each thread works exclusively in its own  
 db?

 I am aware that the connection is generally not threadsafe, but  
 will
 it work if the two threads don't operate on the same db at the same
 time?
>>>
>>> As of 3.5, sqlite connections are threadsafe by default. With
>>> earlier versions, this trick will not work.
>>>
>>> Dan.
>>>
>>>
>>> ___
>>> 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

___
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] segmentation fault

[Virgilio Alexandre Fornazin]

You must have to do a run inside gdb to get sqlite shell working then you
can get your segfault 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of dark0s dark0s
Sent: domingo, 13 de abril de 2008 11:00
To: sqlite-users@sqlite.org
Subject: [sqlite] segmentation fault

How must I interpret this output:

bash-3.1# gcc -O0 -g -shared labsinf.c -o soundex.so
bash-3.1# gdb sqlite3
GNU gdb 6.6
Copyright (C) 2006 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain
conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB.  Type "show warranty" for details.
This GDB was configured as "i486-slackware-linux"...
Using host libthread_db library "/lib/libthread_db.so.1".
(gdb) select load_extension('/root/soundex.so');
No symbol "load_extension" in current context.
(gdb) select soundex('saverio');
No symbol "soundex" in current context.
(gdb) 


   
-
Inviato da Yahoo! Mail.
La casella di posta intelligente.
___
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] segmentation fault

[Virgilio Alexandre Fornazin]

gdb is your friend here. compile with:

gcc -O0 -g -shared labsinf.c -o soundex.so

then run sqlite with gdb

gdb sqlite3

()
gdb> run

then you can get the backtrace of your exception





-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of dark0s dark0s
Sent: domingo, 13 de abril de 2008 10:46
To: sqlite-users@sqlite.org
Subject: [sqlite] segmentation fault

This is last suggestion that I tell, where is the problem now for
segmentation fault:

bash-3.1# gcc -shared labsinf.c -o soundex.so
bash-3.1# sqlite3
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> select load_extension('/root/soundex.so');

sqlite> select soundex('saverio');


S010
Segmentation fault
bash-3.1# 


   
-
Inviato da Yahoo! Mail.
La casella di posta intelligente.
___
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] algorithm for adding columns to a table

[Virgilio Alexandre Fornazin]

Yes. I did this in my custom version of SQLite. If statement is ALTER TABLE,
and SQLite returns error, I check if it´s ALTER TABLE (t) MODIFY COLUMN ou
DROP COLUMN, doing the exact flow you did.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Derrell Lipman
Sent: terça-feira, 18 de março de 2008 11:26
To: General Discussion of SQLite Database
Subject: [sqlite] algorithm for adding columns to a table

I could use another set of eyes or three to verify that this algorithm
makes sense.  I have legacy sqlite2 databases for which I need a
generic function to add columns to tables.  This is the pseudocode for
the function.  Am I missing anything?

In this pseudocode, the parameters are:

  :tableName:
 The table name being altered

  :newColumnDefinition:
 The complete text of the new column description, as if it were in a
 CREATE TABLE command, e.g. "t TIMESTAMP DEFAULT '2008-03-18 10:08:47'"

  :newColumnValue:
The value to insert into the new column as we add the new column
to the table.

Of course, all queries need error checking which is not included in
the pseudocode.  Errors cause an immediate rollback.

Pseudocode follows...


// If anything fails, ensure we can get back to our original
query("begin;")

// Get the sql to generate the table
tableDef = query(
   "SELECT sql
  FROM sqlite_master
  WHERE tbl_name == :tableName:
AND type = 'table';"
)

// Get the indexes associated with this table, excluding automatic indexes
indexes = query(
   "SELECT sql
  FROM sqlite_master
  WHERE tbl_name == :tableName:
AND type = 'index'
AND length(sql) > 0;"
)

// Get the triggers associated with this table
triggers = query(
   "SELECT sql
  FROM sqlite_master
  WHERE tbl_name == :tableName:
AND type = 'trigger'
AND length(sql) > 0;"
)

// Copy all of the data to a temporary table
query("CREATE TEMPORARY TABLE __t AS SELECT * FROM :tableName:;")

// Drop the table being altered
query("DROP TABLE :tableName:;")

// Copy the original table definition so we can modify it
sql = tableDef.sql;

// Find the trailing right parenthesis in the original table definition
p = strrchr(sql, ')');

// Where the right parenthesis was, append a comma and new column definition
*p++ = ',';
strcpy(p, :newColumnDefinition:);
strcat(p, ");");

// Recreate the table using the new definition
query(sql);

// Copy the data from our temporary table back into this table.
query("INSERT INTO :tableName: SELECT *, :newColumnValue: FROM __t;")

// We don't need the temporary table anymore
query("DROP TABLE __t;")

// Recreate the indexes
foreach index in indexes
{
query(index.sql)
}

// Recreate the triggers (after having copied the data back to the table!)
foreach trigger in triggers
{
query(trigger.sql)
}

query("commit;")


Thanks for any comments you can provide!

Derrell
___
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] Proposed sqlite3_initialize() interface

[Virgilio Alexandre Fornazin]

gcc support this, msvc++ and other compilers does not.

-Original Message-
From: Russell Leighton [mailto:[EMAIL PROTECTED] 
Sent: terça-feira, 30 de outubro de 2007 23:32
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Proposed sqlite3_initialize() interface


On Oct 30, 2007, at 10:18 AM, [EMAIL PROTECTED] wrote:

>
> To accomodate this need, we are considering an incompatible
> API change to SQLite.  We are thinking of requiring that an
> application invoke:
>
> int sqlite3_initialize(...);
>

I am not sure about the systems that you are trying to support, but for 
gnu tool chain you can do:

 gcc -shared  -Wl,-init=sqlite3_initialize ...

which will run the function at library load time and for static linking 
( I think you can use this for dynamic linking too but I am not sure):

__attribute__((constructor)) void sqlite3_initialize(void)

So the init function would not need to be a public function and no API 
change would be
needed (assuming the target platforms have similar capability).



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

RE: [sqlite] how to get file handle from sqlite3 object?

[Virgilio Alexandre Fornazin]

There is a better workaround: get the code from the .dump command of sqlite3
utility and use it... 
it creates a fresh copy of your database like using

sqlite3 dbold .dump | sqlite3 newdb (not sure about the syntax, there´s a
example of this
case on internet)


-Original Message-
From: Ronny Dierckx [mailto:[EMAIL PROTECTED] 
Sent: quarta-feira, 10 de outubro de 2007 13:55
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] how to get file handle from sqlite3 object?


I think a possible solution is to lock the database with a "BEGIN EXCLUSIVE"
statement and then copy the database file.

- Original Message - 
From: "Cyrus Durgin" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, October 10, 2007 6:41 PM
Subject: Re: [sqlite] how to get file handle from sqlite3 object?


> Agreed - it seems like this would be useful enough functionality that I'm
> not sure everyone who needs it should be reinventing the wheel...
>
> So is it fair to say that the sqlite3_file API methods are not useful for
> this purpose?  The docs are a bit sparse regarding their intended 
> purposes.
>
> On 10/10/07, John Stanton <[EMAIL PROTECTED]> wrote:
>>
>> There is a good case to have an Sqlite API call to take a snapshot of a
>> database.  It would integrate with the locking logic and secure an
>> exclusive lock before taking the snapshot.  That is a safer and handier
>> approach than extracting a file descriptor and perhaps creating mayhem.
>>
>> Cyrus Durgin wrote:
>> > Maybe it would help to state my use case: without this functionality,
>> what
>> > is the proper way to copy a database using the C API without 
>> > introducing
>> a
>> > race condition?
>> >
>> > On 10/9/07, Robert Simpson <[EMAIL PROTECTED]> wrote:
>> >



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

RE: [sqlite] how to iterate on SELECT query results

[Virgilio Alexandre Fornazin]

SQLite use the 'cursor' style like SQL Server / ORACLE in stored procedures:
you fetch in a unidirectional (forward only) way. 

To achieve bi-directional support you must have to store the results in
memory as you fetch them (not so difficult to accomplish if you have enough
memory to do it, a bit complicated if not).

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: terça-feira, 4 de setembro de 2007 17:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] how to iterate on SELECT query results

Use sqlite3_step to read each row in sequence.

Babu, Lokesh wrote:
> Dear all,
> 
> After doing some SELECT operation on a TABLE, say we get 100 result
> items out of 1000 records, Is there any way where I can iterate
> through this result set. i.e., Get N items out of 100, say get
> previous 10, get next 10, etc,
> 
> This should be done without creating a temporary table or virtual
> tables. As there is overhead of space and time.
> 
> If it is possible to use VIEWs then how can I? As I think VIEWs are
> little better than temp table or virtual table. Please correct me if
> I'm wrong.
> 
> please reply, thanks in advance.
> 
>

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

-
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

RE: [sqlite] VFS in upcoming 3.5.0

[Virgilio Alexandre Fornazin]

You can create a 'shared memory VFS' to share a memory database against
other thread / processes, and you can also 'copy' the RAW bytes of your
memory with memcpy from/to another storage to accomplish the serialize /
load you want to wire transfer SQLite memory databases. But this is not a
simple code, may be after some development, it could be give to public
domain and merged into SQLite if it prove to be robust and safe enough.

-Original Message-
From: Olaf Schmidt [mailto:[EMAIL PROTECTED] 
Sent: sábado, 1 de setembro de 2007 12:15
To: sqlite-users@sqlite.org
Subject: [sqlite] VFS in upcoming 3.5.0


Hi,

first - congrats to the planned changes in the new, upcoming 
version of SQLite. The new shared-cache behaviour sounds
promising and also the new VFS-option.

A few questions to VFS.
1. As known, an InMemory-DB is currently not (much) faster
   than working against a File.
   With the new VFS I think, that much faster InMemory-
   DB-Handling should be possible, is that right?

2. If so, is it planned, to automatically instantiate an appropriate
   (already built in) InMemory-VFS, if one sets the Filename-Param 
   to ':memory:' in an Open-Call, so that InMemory DBs work against 
   this implicite created MemVFS - meaning that the "Default-SQLite-
   engine" already implements such an "InMemory-VFS" for us "Wrapper-
   developers" (because you know best, how to do it in the fastest 
   possible way and because of my following "feature-request" below)? ;-)

3. If you plan something like this, it would be very nice, if
   you could include (now that many new interfaces are coming in
   either way) an additional API-enhancement, wich would allow,  
   to get the current "Byte-Content" of an InMemory-DB, wich
   makes use of this new (built in) InMemory-VFS?

The background for these questions is, that we use SQLite
behind an Appserver wich is currently able, to get Resultset-
Objects at the serverside (done over our wrapper) and after 
retrieving such an Resultset, to serialize its "Query-Content" 
into a ByteArray, wich is then transferred over sockets back 
to the client.

At the clientside we are able, to deserialize the Bytes
and "materialize" a new Resultset-Object appropriately.

With a built in InMemory-VFS (and its new "Dump-Interface")
we could achieve many nice things in only one roundtrip.
At the serverside we could attach an empty InMemory-DB 
(implicitely using the new MemVFS) to an already pooled 
SQLite-Connection and perform a bunch of "Insert Into-Queries",
to create a small snapshot of e.g. a midsized "Master-Detail-
Scenario".
Now we could dump not only a "single-query-content" (as
with our Resultsets currently), but could write a complete, 
related scenario (containing the prepared "InMemory-Tables") 
to a ByteArray and transfer *this* to the client.

Now it would be great, if we could use the new MemVFS-
interface, to create *and* initialize a new InMemory-DB at 
the clientside with the received ByteContent.
This way, we could perform related queries (Joins, Filters,
etc.) against the InMemory-DB (containing the midsize
Master-Detail-Set) without doing any extra-roundtrips 
over the server.

An already builtin InMemory-VFS would ease the burden
of all wrapper-developers, to implement such kind of animal
themselfes - and maybe such an implementation would
help to cleanup (and speedup) the already contained 
InMemory-DB-Handling of the sqlite-engine too.

What's your opinion on this feature-request?

Best regards,

Olaf Schmidt
(developer of dhSQLite and dhRPCServer)


-- 
View this message in context:
http://www.nabble.com/VFS-in-upcoming-3.5.0-tf4364818.html#a12441170
Sent from the SQLite mailing list archive at Nabble.com.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

RE: [sqlite] Table locked - why?

[Virgilio Alexandre Fornazin]

Did you closed the cursor opened at 'select *...' ?
Thats probably the reason you have getting a 'table is locked' error.

-Original Message-
From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] 
Sent: quinta-feira, 30 de agosto de 2007 19:24
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Table locked - why?


Pls see if u hv an open sqlite3 terminal.sometimes this can also cause a
prob with begin tx and just kept it open.

regrads
ragha

**
 This email and its attachments contain confidential information from
HUAWEI, which is intended only for the person or entity whose address is
listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure, reproduction,
or dissemination) by persons other than the intended recipient(s) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!
 

*

- Original Message -
From: Yves Goergen <[EMAIL PROTECTED]>
Date: Friday, August 31, 2007 3:41 am
Subject: Re: [sqlite] Table locked - why?

> On 30.08.2007 23:00 CE(S)T, [EMAIL PROTECTED] wrote:
> > Probably another thread or process is reading from the database
> > and does not want you to drop the table out from under it.
> 
> Surely not. This database is there for testing and development 
> purposesonly and my application is the only one that opens it. It 
> doesn't use
> multiple threads so there also can't be another thread locking it.
> 
> -- 
> Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
> Visit my web laboratory at http://beta.unclassified.de
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
> 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

RE: [sqlite] In memory database question

[Virgilio Alexandre Fornazin]

I'm thinking in a more generic way... using /dev/shm works on linux, but not
on windows.
Also, r/w support is a must have, so this approach cannot solve the problem.
Since we have 'drivers' for windows / linux / etc, a 'memory' driver would
be enought,
simulating file opening / closing / deleting, creating the possibility of
managing 
various in-memory databases at same time usable by the other threads of the
process.

There´s some planning to support a thing like this? It´s a bit crazy but a
lot interesting.


-Original Message-
From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] 
Sent: terça-feira, 28 de agosto de 2007 12:56
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] In memory database question


Hi,

I did tried something crazy like this (it worked, for read only DB only).
I changed pread to preadCustom api.Then in preadCustom maintained a static
fdArray. if fd is not listed then i mmap the whole file and the use the
memcpy to return the data. There was significant performance again as there
was no context switch.
U can overload all the os wrappers to your custom impl and then share it
with multiple threads.

I had to do this as even mounting the file on /dev/shm there was no
significant performance gain.

Just a possible direction,

regards
ragha


**
 This email and its attachments contain confidential information from
HUAWEI, which is intended only for the person or entity whose address is
listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure, reproduction,
or dissemination) by persons other than the intended recipient(s) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!
 

*

- Original Message -
From: Virgilio Alexandre Fornazin <[EMAIL PROTECTED]>
Date: Tuesday, August 28, 2007 8:42 pm
Subject: [sqlite] In memory database question

> Hi
> 
> 
> 
> There´s possible to share a sqlite3 handle to a memory database in all
> threads of application?
> 
> Or there´s a way to ?duplicate? the handle (sqlite_open() or 
> something like
> that)?
> 
> 
> 
> 
> 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

[sqlite] In memory database question

[Virgilio Alexandre Fornazin]

Hi

 

There´s possible to share a sqlite3 handle to a memory database in all
threads of application?

Or there´s a way to ‘duplicate’ the handle (sqlite_open() or something like
that)?