Re: [sqlite] demovfs question

[Neo Anderson]

Thanks.


> From: d...@sqlite.org
> Date: Wed, 20 Mar 2013 12:48:45 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] demovfs question
>
> On Wed, Mar 20, 2013 at 12:45 PM, Neo Anderson wrote:
>
> > Thanks for your detailed explanation. I think I've got my answers for my 2
> > questions.
> >
> > But I've got another question:
> >
> > What happens if there is a power failure when the VFS is in middle of
> > writing journal data to disk but has not yet finished?
> >
> > Let's say the VFS has finished writing a half of integer field and now
> > power is down. Can sqlite handle this?
> >
>
> Yes. See http://www.sqlite.org/testing.html#crashtesting and
> http://www.sqlite.org/atomiccommit.html for further insight into how this
> works.
>
>
> --
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] SQLite version 3.7.16

[Simon Slavin]

On 20 Mar 2013, at 10:31pm, Petite Abeille  wrote:

> On Mar 20, 2013, at 11:19 PM, Simon Slavin  wrote:
> 
>> What on earth is spellfix ?  I don't remember having heard of it before.  
>> googling doesn't seem to turn up any page which says "This is what spellfix 
>> is.".
> 
> http://www.sqlite.org/draft/spellfix1.html

Neat.  Thanks.

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

Re: [sqlite] [sqlite-dev] SQLite version 3.7.16

[Petite Abeille]

On Mar 20, 2013, at 11:19 PM, Simon Slavin  wrote:

> What on earth is spellfix ?  I don't remember having heard of it before.  
> googling doesn't seem to turn up any page which says "This is what spellfix 
> is.".

http://www.sqlite.org/draft/spellfix1.html

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

Re: [sqlite] [sqlite-dev] SQLite version 3.7.16

[Simon Slavin]

On 20 Mar 2013, at 8:18pm, Petite Abeille  wrote:

> gems like FTS and spellfix are really what make SQLite truly outstanding

What on earth is spellfix ?  I don't remember having heard of it before.  
googling doesn't seem to turn up any page which says "This is what spellfix 
is.".

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

Re: [sqlite] pragma table_info(database.table) not supported

[Simon Slavin]

On 20 Mar 2013, at 6:52pm, Petite Abeille  wrote:

> On Mar 20, 2013, at 6:53 PM, Jay A. Kreibich  wrote:
> 
>> If there is any change I'd like to see, it is that all the PRAGMAs
>> that return tabular data should really be system catalog tables.
> 
> Triple hurray for that! SQLite deserves a proper data dictionary, no question 
> asked.
> 
> What about adopting/adapting the information schema and call it a day?
> 
> http://en.wikipedia.org/wiki/Information_schema

Me three.  I feel that this would be a better way to get details about the 
currently connected database(s).  It should replace all the PRAGMAs which 
return details about tables, indexes, foreign keys, etc..  I do remember 
someone posted code here a year or two ago which executed lots of PRAGMAs and 
used the results to build such tables.  It may have been this, or related to it:



These tables could either be updated on any schema change, or could be left 
uncalculated until a query on them is executed, or could be created/updated on 
execution of a special command like ANALYSE.


On 20 Mar 2013, at 6:30pm, Jay A. Kreibich  wrote:

>  Somewhere I have a VT that will build a table out of any SQL
>  statement.  That sounds kind of dumb, since that's either redundant,
>  or something you could do with a VIEW, but it can also be used for
>  any PRAGMA.

You mean something that would take apart a SELECT statement and identify the 
JOINS, the WHERE clause, etc ?  That in interesting idea I've never seen 
before.  It would be a sort of precursor to EXPLAIN QUERY PLAN.

Simon.

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

Re: [sqlite] sqlite3_bind_text destructor

[Marv Cook]

I am truly thrilled you are not in the office.  Think of the problems you
didn't cause

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bill McCormick
Sent: Wednesday, January 25, 2012 2:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite3_bind_text destructor

Stephan Beal wrote, On 1/25/2012 11:36 AM:
> On Wed, Jan 25, 2012 at 6:33 PM, Bill
McCormickwrote:
>
>> http://www.sqlite.org/**capi3ref.html#sqlite3_bind_**blob> sqlite.org/capi3ref.html#sqlite3_bind_blob>
>> Ha! That's the full description that left me with my questions in the 
>> first place!!
>
> Lol! Fair enough.
>
>
>> http://www.adp-gmbh.ch/sqlite/**bind_insert.html> ch/sqlite/bind_insert.html> Ha!! That example doesn't use the 
>> destructor arg.
>>
> It does, actually: it uses SQLITE_STATIC, which is a no-op function 
> with the signature void(*)(void *).
>
OK. Thanks. Nothing like having to dig deep for the deep truth.


___
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-dev] SQLite version 3.7.16

[Petite Abeille]

On Mar 19, 2013, at 11:25 PM, Richard Hipp  wrote:

> Try static linking.  Example of how to build a shell that contains spellfix1:

For the record, aside from being a great little SQL engine, gems like FTS and 
spellfix are really what make SQLite truly outstanding. Thanks for that :)

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

Re: [sqlite] System.Data.SQLite and sqlite3_prepare()

[Richard Hipp]

On Wed, Mar 20, 2013 at 2:04 PM, Eric Sink  wrote:

>
> It looks like System.Data.SQLite uses sqlite3_prepare() instead of
> sqlite3_prepare_v2().
>
> Is there a technical reason for this?
>

I think this is just an historical accident.  There is no technical reason.

-- 
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] [FINAL EDIT] What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE? Thank you

[Frank Chang]

Good evening Michael Black and Igor Tandetnik and sqlite-users group[FINAL
EDIT],
May I ask what is the reason for SQLITE UPSERT performance improvement
with UNIQUE
ON CONFLICT IGNORE?

In *Test #1*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).

In *Test #1*, we use the following UPSERT:

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length) VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT
Count+1 FROM FREQMARY WHERE Value='Braintree Road'), 1),14)

--

In *Test #2*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)

--

In *Test #2*, we use the same UPSERT as *Test#1*,

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length) VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT
Count+1 FROM FREQMARY WHERE Value='Braintree Road'), 1),14)

--

With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.

May I ask what the reason for SQLITE UPSERT *huge* performance improvment
with UNIQUE ON CONFLICT IGNORE?

Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma table_info(database.table) not supported

[Petite Abeille]

On Mar 20, 2013, at 6:53 PM, Jay A. Kreibich  wrote:

>  If there is any change I'd like to see, it is that all the PRAGMAs
>  that return tabular data should really be system catalog tables.

Triple hurray for that! SQLite deserves a proper data dictionary, no question 
asked.

What about adopting/adapting the information schema and call it a day?

http://en.wikipedia.org/wiki/Information_schema

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

[sqlite] [LAST EDIT] What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

[Frank Chang]

Good afternoon,
What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON
CONFLICT IGNORE?

In *Test #1*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).

In *Test #1*, we use the following UPSERT:

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length) VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT
Count+1 FROM FREQMARY WHERE Value='Braintree Road'), 1),14)

--

In *Test #2*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)

--

In *Test #2*, we use the same UPSERT as *Test#1*,

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length) VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT
Count+1 FROM FREQMARY WHERE Value='Braintree Road'), 1),14)

--

With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.

May I ask what the reason for SQLITE UPSERT *huge* performance improvment
with UNIQUE ON CONFLICT IGNORE?

Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma table_info(database.table) not supported

[Jay A. Kreibich]

On Wed, Mar 20, 2013 at 07:00:29PM +0100, Stephan Beal scratched on the wall:
> On Wed, Mar 20, 2013 at 6:53 PM, Jay A. Kreibich  wrote:
> 
> >   That way I can use WHERE on them.  In the past I've used virtual
> >   tables to wrap the PRAGMAs into something that looked and acted more
> >   like a real table.
> 
> can you give us an example of how that is done?


  Chapter 10:  http://shop.oreilly.com/product/9780596521196.do

  Also see the "Download Example Code" link on that page.
  ".../ch10/dblist.c" shows how to wrap "PRAGMA database_list" in a VT.
  It should be pretty easy to modify this so that it would work with
  any PRAGMA.

  There is nothing fancy going on here... the virtual table still calls
  the PRAGMA and sifts through the results.  It just gets abstracted a bit.


  Somewhere I have a VT that will build a table out of any SQL
  statement.  That sounds kind of dumb, since that's either redundant,
  or something you could do with a VIEW, but it can also be used for
  any PRAGMA.  No idea where that code is off the top of my head
  however.

   -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] Efficient way to store counters

[David King]

> Actually my understanding would suggest that INSERT OR REPLACE should
> execute slower than UPDATE + INSERT (or INSERT + UPDATE).


After some experimentation, I think that they turn out to be a wash in my case. 
INSERT OR REPLACE is implemented in sqlite as INSERT OR DELETE THEN INSERT, 
which due to my access patterns puts the really common (k1, k2) pairs together 
at the end of the database (or at least with the highest row ids, which tend to 
group together), which increases the chances to share pages for those common 
pairs.

> And about journal size: imagine that you've got "lucky" and
> all those 94k rows are each in it's own page in the counters table.
> SQLite will have to save each of that pages in the journal which will
> give journal size of about 94k * 4096 ~ 400M.


Maybe it makes sense to drop my page size then all the way down to the disc's 
sector size. That reduces the changes that a given set of pairs shares pages 
but clearly that's already very low. It would at least reduce the number of 
bytes needing to be written, even if the number of seeks stays essentially the 
same.

But since it's seeks that are probably dominating the I/O, maybe it makes sense 
to increase the transaction batch size so that we're overwriting most of the DB 
on every commit rather than overwriting (say) 10%, 10% as often. Does that make 
sense?


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

[sqlite] System.Data.SQLite and sqlite3_prepare()

[Eric Sink]

It looks like System.Data.SQLite uses sqlite3_prepare() instead of 
sqlite3_prepare_v2().


Is there a technical reason for this?

Just curious.

--
E

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

Re: [sqlite] pragma table_info(database.table) not supported

[Stephan Beal]

On Wed, Mar 20, 2013 at 6:53 PM, Jay A. Kreibich  wrote:

>   That way I can use WHERE on them.  In the past I've used virtual
>   tables to wrap the PRAGMAs into something that looked and acted more
>   like a real table.
>

Hi, Jay,

can you give us an example of how that is done?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma table_info(database.table) not supported

[Jay A. Kreibich]

On Wed, Mar 20, 2013 at 05:57:12PM +0100, Staffan Tylen scratched on the wall:
> >PRAGMA table_info(database.tablename)
> >
> > Any chance that SQLite4 might change this, or perhaps accept both forms ?

> In addition, how about a pragma to get the tables in a database? We
> currently have pragma(database_list), pragma(index_list), and
> pragma(table_info), but no pragma(table_list) -  a SELECT against
> sqlite_master is necessary to get hold of the table names. IMHO it's not
> what one would expect.

  I would expect exactly the opposite.

  Part of the core philosophy of Relational systems is that all
  information, including self-inspection information, is available via
  tables-- specifically via the SELECT command.  These are the "system
  catalogs" found in most DBs including Oracle, MySQL, Postgres, and
  many others.

  If there is any change I'd like to see, it is that all the PRAGMAs
  that return tabular data should really be system catalog tables.
  That way I can use WHERE on them.  In the past I've used virtual
  tables to wrap the PRAGMAs into something that looked and acted more
  like a real table.
 
   -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] pragma table_info(database.table) not supported

[Staffan Tylen]

>PRAGMA table_info(database.tablename)
>
> Any chance that SQLite4 might change this, or perhaps accept both forms ?
>
>
In addition, how about a pragma to get the tables in a database? We
currently have pragma(database_list), pragma(index_list), and
pragma(table_info), but no pragma(table_list) -  a SELECT against
sqlite_master is necessary to get hold of the table names. IMHO it's not
what one would expect.

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

Re: [sqlite] demovfs question

[Richard Hipp]

On Wed, Mar 20, 2013 at 12:45 PM, Neo Anderson wrote:

> Thanks for your detailed explanation. I think I've got my answers for my 2
> questions.
>
> But I've got another question:
>
> What happens if there is a power failure when the VFS is in middle of
> writing journal data to disk but has not yet finished?
>
> Let's say the VFS has finished writing a half of integer field and now
> power is down. Can sqlite handle this?
>

Yes.  See http://www.sqlite.org/testing.html#crashtesting and
http://www.sqlite.org/atomiccommit.html for further insight into how this
works.


-- 
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] demovfs question

[Neo Anderson]

Thanks for your detailed explanation. I think I've got my answers for my 2 
questions.

But I've got another question:

What happens if there is a power failure when the VFS is in middle of writing 
journal data to disk but has not yet finished?

Let's say the VFS has finished writing a half of integer field and now power is 
down. Can sqlite handle this?


> From: slav...@bigfraud.org
> Date: Wed, 20 Mar 2013 14:02:53 +
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] demovfs question
>
>
> On 20 Mar 2013, at 10:17am, Neo Anderson  wrote:
>
> > Sorry, what do you mean on question 2?
> >
> > Can I use the buffered fopen family functions or not?
>
> I believe Dan is concerned about some issues with ACID and crash-recovery 
> which will be introduced if you add an extra level of buffering.
>
> To ensure data integrity is is vital that the bits on the disk are a perfect 
> reflection of the data in the database. This ensures that two processes 
> trying to access the same data agree on what the data is, and also that if a 
> process crashes while it's writing data to the database, the files on the 
> disk (which are what's going to be found when the database is next opened) 
> correctly reflect the history of committed transactions and are as up-to-date 
> as possible.
>
> By buffering file operations you are introducing a set of delays to your 
> operations. Consequently some of the 'state' of your data is neither 
> committed nor uncommitted, but stuck somewhere in buffers. A DBMS which takes 
> great pains to be as much ACID
>
> 
>
> as possible is one place where you would want to avoid buffering.
>
> So yes, technically, SQLite won't throw a fit if you use buffered calls. But 
> using them will break ACID in cases of simultaneous access and recovery from 
> crashes, power loss, broken cables, etc.. So it shouldn't be used in anything 
> except conditions where the programmers knows everything about where their 
> program will be used -- perhaps a one-off program which will only ever be run 
> on the programmers own personal hardware.
>
> If you have written unbuffered code and it is /too/ slow to be useful, by all 
> means get back to us and we can suggest things to tackle. But if rather than 
> /too/ slow, it is just a bit slower than you think it could be, then 
> buffering is not one of the best things to look at.
>
> Simon.
> ___
> 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] [EDIT] What is the reason for the SQLITE performance increate with CREATE TABLE UNIQUE CONFLICT IGNORE

[Frank Chang]

Michael Black and Igor Tandetnik, Thank you for your reply. We wiil get
back you to tomorrow with the confirmation of your answers,
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting a CURRENT_TIMESTAMP value into a BIG INT column seems to work

[Simon Slavin]

On 20 Mar 2013, at 12:17pm, Dominique Devienne  wrote:

> CURRENT_TIMESTAMP is of text type. Simon's strftime() is also of text type,
> but easily convertible (explicitly via cast, or implicitly via type
> affinity) to an integer. --DD
> 
> C:\Users\DDevienne>sqlite3
> SQLite version 3.7.15.2 2013-01-09 11:53:05
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite>
> sqlite> select CURRENT_TIMESTAMP;
> 2013-03-20 12:12:19
> sqlite> select typeof(CURRENT_TIMESTAMP);
> text
> sqlite> select strftime('%s','now');
> 1363781564
> sqlite> select typeof(strftime('%s','now'));
> text
> sqlite> select cast(strftime('%s','now') as int);
> 1363781631
> sqlite> select typeof(cast(strftime('%s','now') as int));
> integer

Absolutely correct.  However, if the result of the strftime() is stored in an 
INTEGER column of a table, the affinity of that column makes sure that it is 
converted before storage:

SQLite version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE myTable (a INTEGER, b TEXT);
sqlite> INSERT INTO myTable VALUES (strftime('%s','now'),strftime('%s','now'));
sqlite> SELECT typeof(a),a,typeof(b),b FROM myTable;
integer|1363788461|text|1363788461

so there's no need to do a CAST if you're immediately storing the value.  On 
the other hand a fussy programmer might prefer to do it anyway.

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

Re: [sqlite] pragma table_info(database.table) not supported

[Simon Slavin]

On 20 Mar 2013, at 11:03am, Richard Hipp  wrote:

> The syntax for PRAGMA is:
> 
>PRAGMA database.table_info(tablename);

Out of interest, is there some internal reason for this non-standard form ?  I 
can understand it if it makes some internal part of SQLite easier to write, but 
my instinctive form for these PRAGMAs is the same as the OPs:

   PRAGMA table_info(database.tablename)

Any chance that SQLite4 might change this, or perhaps accept both forms ?

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

Re: [sqlite] demovfs question

[Simon Slavin]

On 20 Mar 2013, at 10:17am, Neo Anderson  wrote:

> Sorry, what do you mean on question 2?
> 
> Can I use the buffered fopen family functions or not?

I believe Dan is concerned about some issues with ACID and crash-recovery which 
will be introduced if you add an extra level of buffering.

To ensure data integrity is is vital that the bits on the disk are a perfect 
reflection of the data in the database.  This ensures that two processes trying 
to access the same data agree on what the data is, and also that if a process 
crashes while it's writing data to the database, the files on the disk (which 
are what's going to be found when the database is next opened) correctly 
reflect the history of committed transactions and are as up-to-date as possible.

By buffering file operations you are introducing a set of delays to your 
operations.  Consequently some of the 'state' of your data is neither committed 
nor uncommitted, but stuck somewhere in buffers.  A DBMS which takes great 
pains to be as much ACID



as possible is one place where you would want to avoid buffering.

So yes, technically, SQLite won't throw a fit if you use buffered calls.  But 
using them will break ACID in cases of simultaneous access and recovery from 
crashes, power loss, broken cables, etc..  So it shouldn't be used in anything 
except conditions where the programmers knows everything about where their 
program will be used -- perhaps a one-off program which will only ever be run 
on the programmers own personal hardware.

If you have written unbuffered code and it is /too/ slow to be useful, by all 
means get back to us and we can suggest things to tackle.  But if rather than 
/too/ slow, it is just a bit slower than you think it could be, then buffering 
is not one of the best things to look at.

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

Re: [sqlite] [EDIT]What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

[Igor Tandetnik]

On 3/20/2013 9:21 AM, Michael Black wrote:

I can tell you the explain plan is notably different between those two
inserts.  This is with 3.7.14.1
If only I knew more about the details of what the plan meansone thing I
noted is that keyinfo is opened twice for write on Test#2


That's because there are two (redundant) indexes on the same column that 
need to be updated - one explicitly created, and another implicit in 
UNIQUE constraint.

--
Igor Tandetnik

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

Re: [sqlite] [EDIT]What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

[Michael Black]

I can tell you the explain plan is notably different between those two
inserts.  This is with 3.7.14.1
If only I knew more about the details of what the plan meansone thing I
noted is that keyinfo is opened twice for write on Test#2
Test 1:
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 45000
2 OpenWrite  1 2 0 5  00
3 OpenWrite  2 3 0 keyinfo(1,BINARY)  00
4 NewRowid   1 2 000
5 String80 3 0 Braintree Road  00
6 Null   0 4 000
7 Null   0 5 000
8 Once   0 25000
9 Null   0 8 000
10Integer1 9 000
11OpenRead   0 2 0 4  00
12OpenRead   3 3 0 keyinfo(1,BINARY)  00
13String80 100 Braintree Road  00
14SeekGe 3 23101  00
15IdxGE  3 23101  01
16IdxRowid   3 11000
17Seek   0 11000
18Column 0 3 11   00
19Integer1 13000
20Add131112   00
21Move   128 100
22IfZero 9 23-1   00
23Close  0 0 000
24Close  3 0 000
25SCopy  8 6 000
26NotNull6 28000
27Integer1 6 000
28Integer147 000
29SCopy  3 14000
30SCopy  2 15000
31MakeRecord 142 1 ad 00
32SCopy  2 11000
33IsUnique   2 391114 00
34NotExists  1 3911   00
35Rowid  1 15000
36Column 1 0 14   00
37IdxDelete  2 14200
38Delete 1 0 000
39IdxInsert  2 1 000
40MakeRecord 3 5 11aeadd  00
41Insert 1 112 FREQMARY   0b
42Close  1 0 000
43Close  2 0 000
44Halt   0 0 000
45Transaction0 1 000
46VerifyCookie   0 4 000
47TableLock  0 2 1 FREQMARY   00
48Goto   0 2 000
Test 2:
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 64000
2 OpenWrite  1 4 0 5  00
3 OpenWrite  2 6 0 keyinfo(1,BINARY)  00
4 OpenWrite  3 5 0 keyinfo(1,BINARY)  00
5 NewRowid   1 3 000
6 String80 4 0 Braintree Road  00
7 Null   0 5 000
8 Null   0 6 000
9 Once   0 26000
10Null   0 9 000
11Integer1 10000
12OpenRead   0 4 0 4  00
13OpenRead   4 6 0 keyinfo(1,BINARY)  00
14String80 110 Braintree Road  00
15SeekGe 4 24111  00
16IdxGE  4 24111  01
17IdxRowid   4 12000
18Seek   0 12000
19Column 0 3 12   00
20Integer1 14000
21Add141213   00
22Move   139 100
23IfZero 1024-1   00
24Close  0 0 000
25Close  4 0 000
26SCopy  9 7 000
27NotNull7 29000
28Integer1 7 000

[sqlite] [EDIT]What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

[Frank Chang]

Good morning,
What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON
CONFLICT IGNORE?

In *Test #1*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).

In *Test #1*, we use the following UPSERT:

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)

--

In *Test #2*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).

--

In *Test #2*, we use the same UPSERT as *Test#1*,

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)

--

With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.

May I ask what the reason for SQLITE UPSERT *huge* performance improvment
with UNIQUE ON CONFLICT IGNORE?

Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IMPORT fails INTEGER column but lets same data sail through as INT

[Richard Hipp]

On Wed, Mar 20, 2013 at 8:37 AM, rol...@logikalsolutions.com <
rol...@logikalsolutions.com> wrote:

> In this case they should be exactly the same.  Obviously it is a bug in
> 3.7.9
> which got fixed in 3.7.15
>

I cannot reproduce the problem in the official SQLite 3.7.9 either.  Nor on
3.7.8 or 3.7.10 or 3.5.9 or 3.3.4.  I can't seem to find a version of
SQLite that exhibits the reported anomalous behavior.

Has the 3.7.9 you are using been patched in any way?
-- 
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] What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

[Igor Tandetnik]

On 3/20/2013 8:29 AM, Frank Chang wrote:

Good morning,
What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON
CONFLICT IGNORE?

In *Test #1*, we have a

CREATE TABLE TESTMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON TESTMARY(VALUE). In test #1, we use the
following UPSERT:
INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)


What's the relationship between TESTMARY and FREQMARY? Why do you show 
the schema of one, when you are concerned with inserts into the other?



With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.


Check whether OR REPLACE part actually works in the second case - 
whether Count gets incremented as expected. I suspect that, with ON 
CONFLICT IGNORE clause, conflicts may in fact be ignored. Then it works 
much faster simply because it makes much fewer writes.

--
Igor Tandetnik

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

Re: [sqlite] IMPORT fails INTEGER column but lets same data sail through as INT

[rol...@logikalsolutions.com]

> On March 20, 2013 at 7:18 AM Simon Davies 
> wrote:
>

> > Makes absolutely no sense. INT and INTEGER should be exactly the same. For
> > this limited set of data both should have worked.
>
> INT and INTEGER are not exactly the same:
> http://www.sqlite.org/lang_createtable.html#rowid
>
> In version 3.7.15.1 your example does not produce the "datatype mismatch"
> error.
>

In this case they should be exactly the same.  Obviously it is a bug in 3.7.9
which got fixed in 3.7.15

Roland Hughes, President
Logikal Solutions
(630)-205-1593 - cell
http://www.theminimumyouneedtoknow.com
http://www.infiniteexposure.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

[Frank Chang]

Good morning,
What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON
CONFLICT IGNORE?

In *Test #1*, we have a

CREATE TABLE TESTMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON TESTMARY(VALUE). In test #1, we use the
following UPSERT:
INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)

--

In *Test #2*, we have a

CREATE TABLE TESTMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON TESTMARY(VALUE).

--

In *Test #2*, we use the same UPSERT as *Test#1*,

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)

--

With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.

May I ask what the reason for SQLITE UPSERT *huge* performance improvment
with UNIQUE ON CONFLICT IGNORE?

Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] SQLite version 3.7.16

[Dominique Devienne]

On Tue, Mar 19, 2013 at 11:25 PM, Richard Hipp  wrote:
>
> On Tue, Mar 19, 2013 at 6:18 PM, Petite Abeille wrote:
> > On Mar 19, 2013, at 1:33 PM, D. Richard Hipp  wrote:
> > >   http://www.sqlite.org/releaselog/3_7_16.html
> >
> > • Enhance the spellfix1 extension so that the edit distance cost table
can
> > be changed at runtime by inserting a string like 'edit_cost_table=TABLE'
> > into the "command" field.
> >
> > Is there an example of how to compile & load that spellfix1 extension?
On
> > Mac OS X possibly?
> >
> Try static linking.  Example of how to build a shell that contains
> spellfix1:
>
> www.sqlite.org/src/artifact/a9c34a606e2e52

FWIW, several defines from that script do not appear documented at
http://www.sqlite.org/compile.html:

   -DSQLITE_ENABLE_VFSTRACE \
   -DSQLITE_ENABLE_REGEXP \
   -DSQLITE_ENABLE_SPELLFIX \
   -DSQLITE_CORE=1 \

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

Re: [sqlite] IMPORT fails INTEGER column but lets same data sail through as INT

[Simon Davies]

On 19 March 2013 15:08, rol...@logikalsolutions.com
 wrote:
> version 3.7.9 2011-11-01 00:52:41
> KUbuntu 12.04 LTS 32-bit
>
> ex1.sql
>
> CREATE TABLE table_ex1(
> Groupid INTEGER PRIMARY KEY,
> GroupName TEXT
> );
> .separator '|'
> .import ex1.csv table_ex1
> .quit
>
> ex1.csv
> 1|NONE
> 2|TWO
> 3|THREE
> 4|FOUR
> 5|FIVE
>
> sqlite> .read ex1.sql
> Error: datatype mismatch
> sqlite> .quit
>
> ex2.sql
>
> CREATE TABLE table_ex1(
> Groupid INT PRIMARY KEY,
> GroupName TEXT
> );
> .separator '|'
> .import ex1.csv table_ex1
> .quit
>
>
> sqlite> drop table table_ex1;
> sqlite> .read ex2.sql
> sqlite> .quit
>
>
> Makes absolutely no sense.  INT and INTEGER should be exactly the same.  For
> this limited set of data both should have worked.

INT and INTEGER are not exactly the same:
http://www.sqlite.org/lang_createtable.html#rowid

In version 3.7.15.1 your example does not produce the "datatype mismatch" error.

>
> Roland Hughes, President

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

Re: [sqlite] IMPORT fails INTEGER column but lets same data sail through as INT

[Clemens Ladisch]

rol...@logikalsolutions.com wrote:
> sqlite> .read ex1.sql
> Error: datatype mismatch

Works for me with 3.7.15.2.

> INT and INTEGER should be exactly the same.

INTEGER PRIMARY KEY is a special case:
http://www.sqlite.org/autoinc.html


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

Re: [sqlite] Inserting a CURRENT_TIMESTAMP value into a BIG INT column seems to work

[Dominique Devienne]

On Tue, Mar 19, 2013 at 12:51 PM, Philipp Kursawe wrote:

> INSERT INTO test VALUES(CURRENT_TIMESTAMP)
>
> This goes through without an error and the physical db file then really
> contains the current timestamp as a string.


CURRENT_TIMESTAMP is of text type. Simon's strftime() is also of text type,
but easily convertible (explicitly via cast, or implicitly via type
affinity) to an integer. --DD

C:\Users\DDevienne>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> select CURRENT_TIMESTAMP;
2013-03-20 12:12:19
sqlite> select typeof(CURRENT_TIMESTAMP);
text
sqlite> select strftime('%s','now');
1363781564
sqlite> select typeof(strftime('%s','now'));
text
sqlite> select cast(strftime('%s','now') as int);
1363781631
sqlite> select typeof(cast(strftime('%s','now') as int));
integer
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] IMPORT fails INTEGER column but lets same data sail through as INT

[rol...@logikalsolutions.com]

version 3.7.9 2011-11-01 00:52:41
KUbuntu 12.04 LTS 32-bit

ex1.sql

CREATE TABLE table_ex1(
Groupid INTEGER PRIMARY KEY,
GroupName TEXT
);
.separator '|'
.import ex1.csv table_ex1
.quit

ex1.csv
1|NONE
2|TWO
3|THREE
4|FOUR
5|FIVE

sqlite> .read ex1.sql
Error: datatype mismatch
sqlite> .quit

ex2.sql

CREATE TABLE table_ex1(
Groupid INT PRIMARY KEY,
GroupName TEXT
);
.separator '|'
.import ex1.csv table_ex1
.quit


sqlite> drop table table_ex1;
sqlite> .read ex2.sql
sqlite> .quit


Makes absolutely no sense.  INT and INTEGER should be exactly the same.  For
this limited set of data both should have worked.


Roland Hughes, President
Logikal Solutions
(630)-205-1593 - cell
http://www.theminimumyouneedtoknow.com
http://www.infiniteexposure.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma table_info(database.table) not supported

[Staffan Tylen]

Ooops! Thank :)


On Wed, Mar 20, 2013 at 12:03 PM, Richard Hipp  wrote:

> On Wed, Mar 20, 2013 at 6:54 AM, Staffan Tylen  >wrote:
>
> > I've just found out that adding a database name as a table prefix is not
> > supported by the pragma table_info function, giving a syntax error. The
> > documentation of the ATTACH DATABASE commands says:
> >
> > Tables in an attached database can be referred to using the syntax *
> > database-name.table-name*.
> >
> > This doesn't seem to be true for the table_info pragma. Can this by any
> > chance be added to the pragma, or is there an alternative way to retrieve
> > the information without parsing the information in the sqlite_master
> table?
> >
>
> The syntax for PRAGMA is:
>
> PRAGMA database.table_info(tablename);
>
>
> --
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] demovfs question

[Dan Kennedy]

On 03/20/2013 05:17 PM, Neo Anderson wrote:

Sorry, what do you mean on question 2?

Can I use the buffered fopen family functions or not?


You probably can. I don't think you should though.






Date: Wed, 20 Mar 2013 17:13:26 +0700
From: danielk1...@gmail.com
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] demovfs question

On 03/20/2013 05:00 PM, Neo Anderson wrote:

I'm studying the demovfs listed on http://www.sqlite.org/vfs.html.

I've got a few questions:

1. By reading the comments, it seems that file read/write operations
must be buffered if it is a journal file. Is this required by sqlite
design?


No. Not required.


2. The demo uses nonbuffered open/read/write function to talk with
OS. Can I use the buffered fopen/fread/fwrite functions and thus I
don't bother with buffering management?


The user-space buffering seems quite likely to introduce confusing
bugs. Probably easiest not to.
___
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] pragma table_info(database.table) not supported

[Richard Hipp]

On Wed, Mar 20, 2013 at 6:54 AM, Staffan Tylen wrote:

> I've just found out that adding a database name as a table prefix is not
> supported by the pragma table_info function, giving a syntax error. The
> documentation of the ATTACH DATABASE commands says:
>
> Tables in an attached database can be referred to using the syntax *
> database-name.table-name*.
>
> This doesn't seem to be true for the table_info pragma. Can this by any
> chance be added to the pragma, or is there an alternative way to retrieve
> the information without parsing the information in the sqlite_master table?
>

The syntax for PRAGMA is:

PRAGMA database.table_info(tablename);


-- 
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] pragma table_info(database.table) not supported

[Clemens Ladisch]

Staffan Tylen wrote:
> I've just found out that adding a database name as a table prefix is not
> supported by the pragma table_info function, giving a syntax error.

:

PRAGMA mydatabase.table_info(mytable);


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

[sqlite] pragma table_info(database.table) not supported

[Staffan Tylen]

I've just found out that adding a database name as a table prefix is not
supported by the pragma table_info function, giving a syntax error. The
documentation of the ATTACH DATABASE commands says:

Tables in an attached database can be referred to using the syntax *
database-name.table-name*.

This doesn't seem to be true for the table_info pragma. Can this by any
chance be added to the pragma, or is there an alternative way to retrieve
the information without parsing the information in the sqlite_master table?

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

Re: [sqlite] demovfs question

[Neo Anderson]

Sorry, what do you mean on question 2?

Can I use the buffered fopen family functions or not?


> Date: Wed, 20 Mar 2013 17:13:26 +0700
> From: danielk1...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] demovfs question
>
> On 03/20/2013 05:00 PM, Neo Anderson wrote:
> > I'm studying the demovfs listed on http://www.sqlite.org/vfs.html.
> >
> > I've got a few questions:
> >
> > 1. By reading the comments, it seems that file read/write operations
> > must be buffered if it is a journal file. Is this required by sqlite
> > design?
>
> No. Not required.
>
> > 2. The demo uses nonbuffered open/read/write function to talk with
> > OS. Can I use the buffered fopen/fread/fwrite functions and thus I
> > don't bother with buffering management?
>
> The user-space buffering seems quite likely to introduce confusing
> bugs. Probably easiest not to.
> ___
> 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] demovfs question

[Dan Kennedy]

On 03/20/2013 05:00 PM, Neo Anderson wrote:

I'm studying the demovfs listed on http://www.sqlite.org/vfs.html.

I've got a few questions:

1. By reading the comments, it seems that file read/write operations
must be buffered if it is a journal file. Is this required by sqlite
design?


No. Not required.


2. The demo uses nonbuffered open/read/write function to talk with
OS. Can I use the buffered fopen/fread/fwrite functions and thus I
don't bother with buffering management?


The user-space buffering seems quite likely to introduce confusing
bugs. Probably easiest not to.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] demovfs question

[Neo Anderson]

I'm studying the demovfs listed on http://www.sqlite.org/vfs.html.

I've got a few questions:

1. By reading the comments, it seems that file read/write operations must be 
buffered if it is a journal file. Is this required by sqlite design?

2. The demo uses nonbuffered open/read/write function to talk with OS. Can I 
use the buffered fopen/fread/fwrite functions and thus I don't bother with 
buffering management? 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users